当前位置：首页 > 问答 > 正文

MYSQL用着用着遇到的那些SQL问题和解决办法大集合

歧云亭
问答
2026-01-04 04:01:15
16

常见MySQL开发与运维实践

问题：查询速度突然变慢，以前很快的。
- 现象：一个平时执行很快的查询，突然某一天需要好几秒甚至更长时间才能返回结果，数据库服务器CPU或磁盘IO很高。
- 原因分析：最常见的原因之一是执行计划改变，MySQL优化器会为SQL语句选择一个它认为最快的“执行计划”（就是先查哪个表、用哪个索引、怎么连接的方式），当表中数据量发生显著变化（比如突然导入大量数据或删除大量数据）后，原来高效的执行计划可能不再是最优的，优化器可能会选错索引，导致全表扫描。
- 解决办法：
  - 使用EXPLAIN分析：在慢查询的SQL语句前面加上EXPLAIN关键字，然后执行，看结果中的key字段是否使用了预期的索引，rows字段预估扫描的行数是否异常多。
  - 强制使用索引：如果确认是优化器选错了索引，而你明确知道用哪个索引更好，可以在SQL语句里用FORCE INDEX (索引名)来强制指定，但这只是临时方案。
  - 优化索引或SQL：可能是索引失效了，比如在查询条件的列上使用了函数（WHERE YEAR(create_time) = 2023），或者like查询以通配符开头（WHERE name LIKE '%张三'），需要调整SQL写法或建立更合适的索引（如函数索引）。
  - 分析表：使用ANALYZE TABLE 表名;命令，这会更新表的索引统计信息，帮助优化器做出更准确的判断。
问题：插入或更新数据时，报错“Deadlock found when trying to get lock; try restarting transaction”。
- 现象：在高并发的场景下，程序日志中偶尔会出现这个“死锁”错误。
- 原因分析：死锁就像两个人过独木桥，A挡住了B的路，B也挡住了A的路，谁也过不去，在数据库里，就是两个或多个事务互相等待对方释放锁资源，比如事务A先锁住了记录1，准备锁记录2；同时事务B先锁住了记录2，准备锁记录1，它们互相等待，就死锁了。
- 解决办法：
  - 重试机制：这是最常用、最有效的方法，在程序代码里捕获这个死锁异常，然后等待一个随机短时间（比如50-100毫秒），再重新执行一次这个事务，大部分情况下重试一次就能成功。
  - 保持事务短小：事务里不要做太多操作，也不要包含一些无关的查询或计算，尽快提交事务，减少锁持有的时间。
  - 约定访问顺序：在业务逻辑上，如果多个事务有可能更新相同的多行记录，尽量约定一个相同的顺序去访问这些记录（比如都按id从小到大更新），可以大大降低死锁概率。
问题：报错“Lock wait timeout exceeded; try restarting transaction”。
- 现象：执行一个更新操作，一直卡住，最后超时报错。
- 原因分析：这个错误和死锁不同，它是因为一个事务长时间占有着某条记录的锁（比如正在修改还没提交），而另一个事务也想修改同一条记录，只能等待，等的时间超过了MySQL配置的innodb_lock_wait_timeout参数（默认50秒），就报这个错，常见于大事务长时间不提交，或者事务结束后忘记提交/回滚。
- 解决办法：
  - 排查长事务：查询information_schema库中的INNODB_TRX表，可以看到当前正在运行的所有事务，找出执行时间特别长的事务，然后联系相关人员确认是否可以杀掉（KILL掉连接）。
  - 避免大事务：和解决死锁一样，要把大事务拆小。
  - 检查代码：确保数据库操作在完成后（比如在finally块中）正确关闭了连接或提交/回滚了事务。
问题：错误日志里出现“[Warning] Aborted connection ...”。
- 现象：MySQL的错误日志中大量出现这种警告，但业务似乎暂时正常。
- 原因分析：这表示应用程序和数据库的连接没有正常关闭就被中断了，常见原因有：应用程序连接池中的连接空闲时间超过了MySQL的wait_timeout设置，MySQL主动断开了连接，但应用端还以为连接是有效的，下次拿来用时发现断了就会报错；或者应用程序发生异常崩溃，没来得及关闭连接。
- 解决办法：
  - 调整超时时间：适当调整MySQL的wait_timeout和interactive_timeout参数，但要权衡资源占用。
  - 配置连接池：在应用程序的连接池配置中，设置连接有效性检查，让连接池在借出连接给程序使用前，先执行一个简单的SQL（如SELECT 1）来测试连接是否还有效，这是最关键的解决办法。
  - 优化网络：检查应用服务器和数据库服务器之间的网络是否稳定。
问题：数据莫名其妙乱码，显示问号“?”或者奇怪的字符。
- 现象：插入的中文显示不出来，变成问号，或者网页上显示为乱码。
- 原因分析：这是字符集不统一导致的，需要保证“四处合一”：数据库的默认字符集、表的字符集、字段的字符集、客户端连接使用的字符集，这四者要统一，一般推荐使用utf8mb4（因为它支持emoji等所有Unicode字符）。
- 解决办法：
  - 统一字符集：检查并确保数据库、表、列的字符集都是utf8mb4。
  - 设置连接字符集：这非常重要！在应用程序连接MySQL之后，立即执行一条SQL：SET NAMES 'utf8mb4';，或者在建立连接的URL参数中指定，如jdbc:mysql://...?characterEncoding=utf8mb4。
  - 检查终端工具：如果你在用Navicat、命令行等工具查看，也要确保工具本身的编码设置是UTF-8。
问题：磁盘空间报警，发现一个叫“ibdata1”的文件特别大。
- 现象：服务器磁盘空间不足，查看到MySQL的数据目录下，ibdata1文件占用了上百GB空间。
- 原因分析：ibdata1是InnoDB存储引擎的系统表空间文件，如果创建表时没有指定innodb_file_per_table=ON（MySQL 5.6之后默认是ON），那么所有InnoDB表的数据和索引都会放在这个文件里，即使删除了表，这部分空间也不会自动还给操作系统，只会留待后续复用。
- 解决办法：
  - 预防为主：确保MySQL配置文件中设置了innodb_file_per_table = ON，这样每张表会有自己独立的.ibd文件，删除表时空间会释放。
  - 历史问题处理：对于已经很大的ibdata1，常规操作无法缩小，通常需要备份整个数据库，然后删除数据目录，修改配置，最后重新导入数据，这是一个危险操作，需要停机维护。
问题：做模糊查询时，like '%关键字%' 用不上索引，导致全表扫描。
- 现象：搜索功能慢，EXPLAIN发现没有使用索引。
- 原因分析：B+树索引的结构就像字典的拼音检索，你只能从第一个字母开始查。LIKE '%关键字'这种写法，相当于让你在字典里找所有以“关键字”结尾的字，索引无能为力，只能一页一页翻（全表扫描）。
- 解决办法：
  - 考虑全文索引：如果搜索是核心功能，可以考虑使用MySQL自带的全文索引（FULLTEXT Index），它专门为这种文本搜索场景设计。
  - 使用搜索引擎：更专业的做法是引入Elasticsearch、Solr等外部搜索引擎，它们对海量文本的模糊搜索支持得更好。
  - 调整业务需求：和产品经理沟通，是否能改成LIKE '关键字%'的“前缀匹配”查询，这样是可以使用索引的。