数据库性能卡顿难忍？MySQL那些让你头疼的查询优化技巧全解析

你是不是也遇到过这种情况？打开一个网页或者一个应用，那个小圈圈转啊转，就是不出数据，急得人想砸键盘，很多时候，问题的根源就出在数据库查询太慢了，而MySQL作为最常用的数据库之一，它的查询优化是每个开发者都必须面对的“必修课”，我们就来把这些让人头疼的优化技巧,用大白话捋一遍。

第一关：找到“罪魁祸首”——慢查询日志

当感觉数据库卡顿的时候，你首先得知道是哪条SQL语句在“捣乱”，MySQL自己就带了一个非常强大的工具，叫做“慢查询日志”（Slow Query Log），这个功能就像是一个监控摄像头，它会自动记录下所有执行时间超过你设定阈值的SQL语句，你可以告诉MySQL：“凡是执行超过1秒的查询，都给我记下来。”（根据MySQL 5.1官方文档，可以通过long_query_time参数设置阈值）

开启慢查询日志后，你就能拿到一份“黑名单”，里面全是需要被优化的“嫌疑犯”，你要做的就是对这些“慢查询”进行审问。

第二关：审问“嫌疑犯”——使用EXPLAIN

拿到慢查询语句后，别急着动手改，MySQL提供了一个神奇的命令叫EXPLAIN（或者在较新版本中可以使用EXPLAIN FORMAT=JSON获取更详细的信息），你只需要在SQL语句前面加上EXPLAIN，然后执行,它就会告诉你MySQL打算怎么执行这条查询。

这份“执行计划报告”里有很多关键信息，但对我们来说,最先要看的是以下几个字段：

• type列： 这代表了查询时的访问类型，从好到坏有很多种，最理想的是system和const，表示通过主键或唯一索引一次就找到了，我们最常追求的是ref或eq_ref，表示使用了高效的索引匹配，而一旦你看到ALL，那就要警惕了，这代表“全表扫描”（Full Table Scan），意思是MySQL为了找到数据，把整张表从头到尾翻了一遍，就像你在一个没有按姓名排序的花名册里找一个人，只能一页一页地找,效率极低。
• key列： 这显示了MySQL实际决定使用的索引是哪个，如果这一栏是NULL，那就意味着它没有使用任何索引，这通常是导致ALL全表扫描的直接原因。
• rows列： 这一列是MySQL估算的要找到所需数据需要检查的行数，这个数字当然是越小越好，如果这个数字非常大,那查询肯定快不了。

第三关：对症下药——创建和使用索引

通过EXPLAIN分析，如果你发现问题是“没有使用索引”（key为NULL）或者“索引使用不当”，那么解决方案通常就是创建合适的索引，索引就像是书本的目录，能让你快速定位到想看的内容,而不用翻遍整本书。

创建索引也不是随便建的,有几个常见的坑需要注意：

• 最左前缀原则： 如果你对多个列建立了一个联合索引（比如INDEX (last_name, first_name)），那么查询条件必须从最左边的列（last_name）开始使用，这个索引才会生效，如果你只用first_name作为条件，这个联合索引是派不上用场的。（这一原则在MySQL官方关于多列索引的文档中有详细阐述）
• 避免在索引列上做计算或函数操作： 你的查询条件是WHERE YEAR(create_time) = 2023，即使create_time字段上有索引，MySQL也无法使用它，因为它需要对每一行的create_time值都先计算YEAR()函数，然后再比较，正确的写法应该是WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'。
• Like查询的陷阱： 使用LIKE进行模糊查询时，如果通配符出现在开头（如LIKE '%keyword'），索引也会失效，因为索引是按照字段值的前缀排序的，不知道以keyword结尾的数据在哪里,应尽量避免这种写法。

第四关：改变思路——重构查询方式

问题不出在索引上,而是SQL语句本身写得不够好。

• **避免使用SELECT **：很多人图省事，直接写`SELECT ，这会查询出所有字段，包括你不需要的，这不仅会增加网络传输的数据量，如果某些字段是TEXT或BLOB`这样的大对象，还会严重拖慢查询速度，最好养成习惯,需要什么字段就写什么字段。
• 将复杂的查询拆解： 一个巨大无比的、连接（JOIN）了七八张表的查询，可能还不如拆分成几个简单的查询，在应用程序里分步执行来得快，因为庞大的JOIN操作可能会产生巨大的临时表，消耗大量内存，现代的应用程序和网络环境已经不像过去那样脆弱，多次简单查询”的策略反而比“一次复杂查询”更高效。（这个观点在《高性能MySQL》等经典书籍中被多次提及）
• 用好LIMIT分页： 常见的分页写法是LIMIT 10000, 20，意思是跳过10000条记录，取20条，当偏移量很大时（比如10000），MySQL需要先查询出10020条记录，然后扔掉前10000条，这个过程很浪费，一种优化方法是使用“游标分页”或者记录上一页最后一条记录的ID，然后使用WHERE id > 上一页最大ID LIMIT 20这样的方式,效率会高很多。

优化MySQL查询不是一个一蹴而就的过程，而是一个“分析-优化-验证”的循环，先打开慢查询日志找到问题查询，再用EXPLAIN命令分析执行计划，然后针对性地创建索引或重构查询语句，没有一劳永逸的银弹，只有结合具体业务场景，不断地观察和调整，才能让你的数据库查询告别卡顿,变得丝般顺滑。