MySQL TBO_hotels表查询缓慢及#1205锁等待超时问题求助

1. 核心问题：缺失关键索引+类型不匹配导致全表扫描 #

你的慢查询和锁超时根源在于city_code字段无索引，且查询时存在隐式类型转换：

• 表中city_code是varchar(255)类型，但查询语句用city_code =115936（数值），MySQL会把每行的city_code转成数值再匹配，完全无法使用索引，只能全表扫描（15万条数据全扫必然慢）
• 立即执行以下操作：
  1. 修改查询语句，使用字符串匹配：city_code = '115936'
  2. 给city_code添加索引，若经常按city_code+id排序，直接建复合索引（覆盖查询+排序，避免filesort）：

     ALTER TABLE TBO_hotels ADD INDEX idx_city_code_id (city_code, id);
     

  若仅需基于city_code查询，建普通索引即可：

  ALTER TABLE TBO_hotels ADD INDEX idx_city_code (city_code);
  

2. 优化示例查询语句 #

原查询中的子查询会对每行结果重复执行一次统计，效率极低，替换为以下两种高效方式：

方式一：用SQL_CALC_FOUND_ROWS获取总数 #

SELECT SQL_CALC_FOUND_ROWS *
FROM TBO_hotels
WHERE city_code = '115936'
ORDER BY id ASC
LIMIT 0,50;
-- 单独获取总数
SELECT FOUND_ROWS() AS TotalCount;

方式二：分开查询列表与总数（更可靠，避免SQL_CALC_FOUND_ROWS的潜在开销） #

-- 先查总数
SELECT COUNT(*) AS TotalCount FROM TBO_hotels WHERE city_code = '115936';
-- 再查分页数据
SELECT * FROM TBO_hotels WHERE city_code = '115936' ORDER BY id ASC LIMIT 0,50;

注：COUNT(*)比count(TBO_code)更高效，因为它直接统计行数，无需判断字段非空

3. 排查锁超时（#1205）问题 #

锁超时通常是慢查询或长事务导致锁持有时间过长引发的：

• 先解决上述慢查询问题，减少锁占用时间
• 查看当前运行的长事务，及时终止：

  SELECT * FROM information_schema.INNODB_TRX;
  

  找到trx_started时间较早的事务，用KILL [trx_mysql_thread_id];终止
• 避免在事务中执行全表扫描、批量更新/删除等操作，尽量缩小事务范围，减少锁持有时间
• 检查是否有未加索引的更新/删除语句，比如UPDATE TBO_hotels SET ... WHERE city_code = 'xxx'，无索引会导致锁全表

4. 额外优化建议 #

• 字段类型优化：city_code看起来是数字编码，可改为INT或BIGINT，减少存储空间与索引大小，提升查询效率：

  -- 先确认所有city_code都是数字，再执行修改
  ALTER TABLE TBO_hotels MODIFY city_code INT NOT NULL;
  

• 定期整理表碎片：在业务低峰期执行，重建表并整理碎片：

  OPTIMIZE TABLE TBO_hotels;
  

• 开启慢查询日志：在MySQL配置中开启慢查询日志，捕获所有慢查询语句以便进一步优化：

  slow_query_log = 1
  slow_query_log_file = /var/log/mysql/slow.log
  long_query_time = 1  # 记录执行时间超过1秒的查询
  

内容的提问来源于stack exchange，提问作者TechChef