MySQL日记——索引优化

索引优化最简单的一个法则

左连接建在右表，右连接建在左表
例如

select * from book left join category on category.class=book.class

这时应该使用下述SQL语句把索引建在category.class

create index on category z(class)

又如

select * from book right join category on category.class=book.class

这时应该使用下述SQL语句把索引建在book.class

create index on book z(class)

Join优化几个基本准则

1. 尽量减少Join语句中的NestLoop的循环总次数：“永远用小结果集驱动大结果集”
2. 优化先优化NestLoop的内层循环
3. 保证Join语句被驱动表上Join条件字段已经被索引
4. 当无法保证被驱动表的Join条件字段被检索 且内存资源充足的前提下，不要太吝啬JoinBuffer的设置

避免索引失效

1. 最好使用全值匹配（索引都用在where后作为条件）
2. 最佳左前缀法则（指的是从索引的左前列开始并且不跳过索引的列）
3. 不要在索引上做任何操作（计算，函数，类型转换），会导致索引失效而转向全表扫描
4. 储存引擎不能使用索引中范围条件右边的列（用了>,<,like等右边的索引全部失效）
5. 尽量使用覆盖索引（只访问索引的查询），减少select*
6. mysql在使用不等于（！=或<>）的时候无法使用索引会导致全表扫描
7. is null，is not null也无法使用索引
8. like以通配符开头（'%abc....')mysql索引失效会变成全表扫描的操作（%尽量放在右边，要使用%开头则要使用覆盖索引）
9. 字符串不加单引号索引失效
10. 少用or，用它来连接时会索引失效

一般性建议

1. 对于单键索引，尽量选择针对当前query过滤性更好的索引
2. 在选择组合索引的时候，当前的query中过滤性最好的字段在索引字段顺序中，位置越靠左越好
3. 在选择组合索引的时候，尽量选择可以包含当前query中的where子句中更多字段的索引
4. 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

优化总结口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难；