mysql索引优化思路（优化UCP的慢SQL总结）

﹏ヽ暗。殇╰゛Y 2022-03-02 10:18 299阅读 0赞

（1）能用唯一索引不要用普通索引，能用组合索引尽量不用单列索引。  
（2）索引不会走包含有NULL的列，只要列中包含有NULL值，都将不会被包含在索引中，组合索引中只要有一列含有NULL值，那么这一列对于此组合索引就是无效的。  
（3）order by与group by后的字段需要结合where条件的字段建组合索引，并且order by的字段一般放在组合索引的最后才有效。  
（4）查询语句如果使用组合索引，组合索引的第一个字段一定要在where条件中，否则该索引是不会走的。需保证组合索引的字段顺序，若使用组合索引后面的字段，最好前面的字段也在where条件中，  
否则即使mysql筛选器使ff用了该索引，该索引后面的字段也可能没用到。  
（5）查询语句中包含时间范围的时间字段在组合索引的最后，因为只要是范围的字段，组合索引后面的字段就没用。  
（6）如果查询语句包含时间范围的时间字段，为保证mysql筛选器能筛选到时间字段的组合索引或能使用到组合索引里的时间字段，  
order by的字段尽量加上该时间字段，或者只要能保证不走order by的字段的单个索引也可。  
（7）查询语句中包含order by时，如果order by是主键字段或单列索引字段，此时需注意mysql索引筛选器可能会按主键索引或单列索引查询，导致全表扫描（type=index），CPU飙升。  
（8）若一个表索引较多，为防止mysql筛选器选择不必要的索引导致查询较慢，可以指定索引进行查询（force index强制使用索引、ignore index忽略某些索引），如无必要，尽量不要指定。  
（9）一个表的索引个数最好不要超过5~6个，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。  
（10）使用短索引（提高查询速度、节省磁盘空间和I/O操作）：尽量使用字段数据量少的索引；大字段的索引，需使用前缀索引，但最好不要在大字段上使用索引。  
（11）一个组合索引的字段数量不是越多越好，一个查询语句的组合条件会变，只要使用经常查询的字段即可，  
并且组合索引的字段在查询语句中的值区分度越高越好，比如print\_flag有0和1两个值，但是0的值特别少，用户大部分都是使用0来查询。  
（12）不鼓励使用like操作，like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。  
（13）不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的。  
（14）如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。  
（15）在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。

mysql慢日志分析工具  
mysqldumpslow -t 10 ./slowquery.log --查询时间最长的前10 SQL语句  
mysqldumpslow -s c -t 10 ./slowquery.log --访问次数最多的前10 sql语句  
mysqldumpslow -s r -t 10 ./slowquery.log --返回最多的前10 sql语句

如何通过 pt-query-digest 慢查询日志发现有问题的 sql  
1）查询次数多且每占用时间长的 sql  
通常为 pt-query-digest 分析的前几个查询  
2）IO 消耗大的 sql  
注意 pt-query-digest 分析中的 Rows examine 项  
3）为命中索引的 sql  
注意 pt-query-digest 分析中 Rows examine （扫描行数） 和 Rows sent （发送行数）的对比,如果扫描行数远远大于发送行数，则说明索引命中率并不高。