根据日常的刷题笔记,总结了以下三步SQL的调优总结
第一步:根据慢日志定位慢查询SQL
1.首先检查SQL中是否使用函数,隐式类型转换(字符串转数字),隐式函数或者传入的值超过索引长度
2.SQL中字段的字符集是否一致
3.如果使用count计数,尽量使用count(*)或者count(1),count(字段)会涉及到回表操作,count(id)会全表扫描,并且count(字段)和count(id)都需要判空操作,也可以按照具体的业务选择MyISAM引擎,直接取值
4.查看当前语句的状态,是否是在等MDL锁,数据页flush,其他线程占用了行锁
5.如果是热点数据,需要控制访问资源的并发事务量,可以将一行数据改成逻辑上的多行数据
第二步:使用explain等工具分析 SQL
查看SQL是否按照理想的状态检索最少的数据行,如果没有则查看是否走了指定索引
判断优化器是否选错索引,可以通过强行选择索引或者重新统计索引信息
第三步:修改SQL或者尽量让SQL走索引
A.在数据库空闲的时候,定期进行索引统计,防止优化器选错索引,造成索引失效
B.优化手段:聚簇索引,覆盖索引,索引下推优化,联合索引
C.字符串索引:前缀索引,倒序存储,hash索引
D.如果需要业务字段做索引,必须确保是唯一索引,符合K-V结构,不需要考虑其他索引叶子节点的大小
E.尽量使用自增主键索引,每次插入新的数据都是追加操作,可以防止数据页黑洞出现,保证索引的紧凑,不涉及挪动其他数据,也不会触动叶子节点的页分裂
F.主键索引的长度不可以过长,造成其他索引树的叶子节点较大
G.在建立联合索引的时候,如果可以通过顺序少建立一个索引,则调整顺序,同时需要考虑空间占用
H.数据写多读少的时候选择普通索引,利用change buffer可以提高效率,合理设置change buffer大小,防止频繁merge
I.如果sql语句出现锁操作,尽量让锁操作最后执行,防止影响其他SQL的执行
J.如果使用长连接,在进行较大查询之后,需要重置链接,防止占用较大内存,造成数据库异常重启
K.不要删除索引,删除索引可能会造成页分裂,导致数据页出现黑洞
L.使用order by查询的时候,如果单行数据过大,会造成回表操作,可以使用联合索引,让字段本身有序