
网站变慢,八成是数据库的问题。数据库变慢,八成是索引的问题。索引这事听起来专业,其实核心就几句话:该建的建上、不该建的别建、建了要用得上。这篇文章讲怎么找出慢查询、怎么判断该建什么索引、以及建索引时容易犯的错。
第一步:先找出慢在哪
不要凭感觉猜。先开慢查询日志,让数据库自己告诉你哪条语句慢。
开的方法很简单:把慢查询日志打开,设一个阈值,比如超过一秒的记录。跑一天,然后分析日志。
分析的时候不要只看单条最慢的,要看"总耗时"最高的。一条语句单次只花一秒但一天跑十万次,比一条单次花十秒但一天跑一次的危害大得多。
除了日志,还有两个现成的工具。一个是数据库自带的性能视图,能看到哪类语句消耗最多;另一个是 explain 命令,能看一条语句的执行计划。这两个配合用,定位很快。
第二步:看执行计划,找问题
explain 的输出里,几个字段最关键。
第一个,type。这个字段告诉你访问方式。从好到坏大致是:const、eq_ref、ref、range、index、all。看到 all 就说明全表扫描了,这是最该优化的。
第二个,key。实际用了哪个索引。如果是 NULL,说明没用索引。
第三个,rows。预估要扫多少行。这个数字越大越慢。
第四个,Extra。这里会告诉你额外信息,出现 Using filesort 说明排序没走索引,出现 Using temporary 说明用了临时表,这两个都常见于慢查询。
看明白这四个字段,基本就能判断问题在哪。
索引怎么建:三个原则
原则一,把 where 条件里出现的字段,按"等值在前、范围在后"的顺序建联合索引。
举个例子:查询条件是 status=1 并且 create_time 大于某个时间,按 id 倒序。这种情况建 (status, create_time) 的联合索引,比分别建两个单列索引有效得多。
因为联合索引可以只用一个索引就完成过滤,而两个单列索引数据库通常只会选一个用。
原则二,排序和分组也要考虑。如果查询里有 order by,把排序字段也放进索引,能省掉一次额外排序。
原则三,覆盖索引。如果查询需要的字段都能从索引里拿到,数据库就不用回表查原始数据,速度会快很多。做法是把 select 里的字段也加进索引,但要注意索引不能太宽。
最常见的错:索引建了但用不上
这是最浪费的情况。索引在那儿,查询还是全表扫。原因有几个。
原因一,字段上做了运算或者函数。比如 where date(create_time)=某个日期,这种写法会让索引失效,因为数据库要先对每一行算一遍函数。改成范围查询就能用上索引。
原因二,隐式类型转换。字段是字符串类型,查询时传了数字,数据库会做转换,索引失效。反过来也一样。所以类型一定要对上。
原因三,like 以通配符开头。like 后面如果以百分号开头,索引用不上。这是设计决定的,只能改成前缀匹配或者换全文检索。
原因四,联合索引的最左前缀没用上。建了 (a,b,c) 的索引,查询只用 b 和 c,索引用不上。联合索引必须从最左开始用。
原因五,or 条件。or 两边如果有一个字段没索引,整个查询可能就全表扫。改成 union 或者给另一个字段也建索引。
这五个原因覆盖了绝大多数"索引失效"的情况,排查时按顺序看。
索引不是越多越好
新手常犯的错是给每个字段都建索引。后果有三个。
第一个,写入变慢。每次插入、更新、删除,都要维护所有索引。索引多了,写入就慢。
第二个,占用空间。索引是要占磁盘的,大表上的索引可能比数据本身还大。
第三个,优化器选错。索引太多,数据库的查询优化器可能选一个不是最优的,反而更慢。
所以索引要精简。判断标准是:这个索引有没有被实际的查询用到。没用到就删掉。可以查索引的使用统计,看每个索引被读了多少次,为 0 的就是可以删的。
表大了之后的新问题
数据量小的时候,索引效果不明显,因为全表扫也很快。数据量上到几十万行之后,差距就出来了。
这时候还有几件事要做。
第一件,分页优化。limit 很大的时候(比如 limit 100000, 20),数据库要扫过前面十万行再丢掉,很慢。优化办法是用"记住上次的位置"的方式,用 where id > 上次的值 来翻页。
第二件,count 优化。统计总数在超大表上很慢。如果只是展示用,可以缓存结果,或者用近似值。
第三件,大字段分离。文章正文这种大文本字段,如果列表页不需要,可以拆到单独的表,主表只留摘要。这样主表变小,查询变快。
索引维护
索引建好不是一劳永逸。三件事要定期做。
第一件,看碎片。表在频繁增删之后会产生碎片,索引效率下降。定期做一次整理,能回收空间、提升速度。
第二件,看统计信息。数据库靠统计信息决定用哪个索引,统计信息过时会导致选错。定期更新统计信息。
第三件,复查慢日志。业务在变,慢查询也在变。每周看一次,发现新的慢查询及时处理。
一个实际的排查顺序
遇到网站慢,按这个顺序查。
第一步,确认是不是数据库的问题。看服务器负载,看数据库进程的 CPU 占用。如果不是数据库,别在索引上浪费时间。
第二步,开慢日志,抓一天,按总耗时排序,找出前五条。
第三步,对每条跑 explain,看 type 是不是 all、key 是不是 null、Extra 有没有 filesort。
第四步,根据结果决定加索引、改写法、还是拆表。
第五步,改完再跑一遍同样的语句,对比耗时。没改善就回滚,不要留着没用的索引。
这个顺序看着简单,但很多人跳过第一步,直接去调索引,结果瓶颈根本不在数据库。
最后
索引优化的本质是"让数据库少干活"。少扫行、少排序、少回表,这三点做到了,慢查询自然就降下来了。
但也要知道,索引解决不了所有问题。查询写得烂、表设计得不合理、业务逻辑在循环里查数据库,这些靠索引救不了,得改代码。
所以看到慢,先分清是"索引问题"还是"写法问题"。分清了,才能对症。
