持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第6天，点击查看活动详情
我有个朋友最近面试被问到慢查询问题，只知道加索引，那可不行，这道题起码得吹个十多分钟。
索引问题
- 查询条件根本没有索引。
没有索引默认走的主键索引，全盘一个一个的扫描对比那当然慢啦。 - sql不规范没走索引
即使建立了索引也不一定走索引比如
1、用!= 、 not in、not exists、or、<> 等关键字不走索引，比如
select * from t_user where username <> 'mysql'
2、对列做运算不走索引，比如
SELECTusernameFROMt_userWHERE age+10=30
3、使用函数不走索引，比如
SELECT username FROM t_user WHERE concat(username,'1') ='admin1';
4、违背最左前缀匹配原则，
like %mysql%就不走索引，但是mysql%会走索引，如果而且联合索引a,b但是查询条件时b=1 and a=2也不会走索引。
- 优化器误判不走索引
虽然写规范了，但是在sql执行前优化器会进行一次优化，如果优化器判断走索引的成本比不走索引的成本高时就会不走索引，比如查询走索引需要大量回表，那么还不如直接全盘扫描。优化器对行数的计算是预估的，因为准确扫描行数代价太大，所以可能误判行数导致不走所以，比如频繁删除等操作容易导致错误估计行数。
一般索引问题可以先用explain查看执行计划，看看表的访问方式，预计使用的索引实际使用的索引、扫出的行数、条件过滤百分比等，查看查询执行的具体情况进行优化。要么适当增加索引，要么进行sql改造。
order by和limit使用不当问题
- order by使用不当导致大量排序工作使得sql变慢。
每个线程都会分配一个固定的内存sort_buffer用于做排序的内存，如果不够用那么只能分成多个小份，之后通过借助临时文件的方式进行排序，这样会徒增多次IO，使得sql变慢。 既然是排序使用到磁盘导致的sql变慢，那么可以通过加大sort_buffer内存解决，也可以给order by字段加索引这样本来就有序了就不用排序了。
- limit使用不当，偏移量太大，导致sql执行变慢
默认排序的情况下返回的数据是根据主键排序返回的，所以查询涉及到二级索引，那么使用limit会回表排序返回，所以如果limit偏移量太大，会导致大量回表操作，影响性能。
limit offset n语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说 limit 100000,10，就会扫描100010行。
既然是因为大量回表操作导致性能不好，那么解决办法是尽量减小回表的量，
可以直接增加order by指定字段排序并且给该字段加索引，这样就会在索引里进行截断后再回表。
也可以使用自增ID作为标记，记下上次偏移量的自增ID后查询大于该自增的ID作limit代替偏移量的方式，比如where id > 100000 limit 10;其中100000是上一页的最大ID。
数据表设计不合理
- 大量join导致sql变慢
大量join会导致笛卡尔积后再筛选，所以大量join会导致大量运算使得sql变慢。
经常连表查询可以增加一个中间表做数据冗余提高查询速度。
- 数据量太大IO大导致查询太慢
在数据量比较大时，每次查询的数据太多导致大量的IO导致sql慢，可以采用分库分表将数据分到多个表或者多个库分担IO压力进行优化。
分库分表有垂直分表、垂直分库、水平分表、水平分库等方案
垂直分表主要是根据将表的字段拆开放不同的表中
垂直分表就是垂直分表后将两个表放不同的库中
水平分表主要是根据字段值不同放到不同的表中
水平分库主要是水平分表后将两个表放在不同的库中。
但分库分表会带来更加复杂的问题 比如
事务一致性问题，一般通过使用分布式事务解决
跨节点join问题，一般通过字段余沉，多次查询后组装等方法避免join。
分页查询问题，一般将两个表中都查出来后归并再取出一页。
资源问题
- 可能是数据库脏页过多，正在刷新脏页，通过适当增加缓存大小等解决
- 有可能是其他事务锁住了表或者记录在等待获取锁等。只能从设计上入手或者少用没必要的for update因为手动加了悲观锁会等待事务提交才释放。
- 可能是连接不够正在等待连接，只能适当增加连接数或者复用连接啦。
