SQL实战之索引优化(单表、双表、三表、索引失效)

单表优化

总体原则：建立索引并合理使用，避免索引失效

案例说明：查询category_ id 为1且comments大于1的情况下,views最多的article_ id:

传统方案：
explain select id, author_ id from article where category_ id = 1 AND comments > 1 order by views desc limit 1;

SQL性能分析01：出现全表扫描all、无索引null、以及文件外排序Using filesort，导致性能最差

优化一：新建复合索引(直接3个字段)

create index idx_ article_ CCV on article(category_ id, comments, views);

explain select id, author_ id from article where category_ id = 1 AND comments > 1 order by views desc limit 1;

SQL性能分析02: 解决了全表扫描all、无索引的问题，但是仍然存在Using filesort

原因：comments > 1导致views索引失效，出现文件内排序问题，若comments =1索引不会失效 ，哈哈

优化二：新建索引(优化为两个字段)

create index idx_ article_ CV on article (category_ id, views);
explain select id, author_ id from article where category_ id = 1 AND comments > 1 order by views desc limit 1;

总结：合理设置索引，解决了全表扫描all、无索引、Using filesorting内排序的问题

双表优化

案例说明：查看left join的查询性能以及如何加索引？

传统方案：
explain select * from class LEFT JOIN book ON class.card = book.card;

SQL性能分析01: type查询种类两个都是all全表扫描，性能较差，问题是现在给哪个表加索引，左表calss？右表book?

优化一：左表class主键加索引
create index Y class(card)
explain select * from class LEFT JOIN book ON class.card = book.card;

优化二：右表book主键加索引
create index Y class(card)
explain select * from class LEFT JOIN book ON class.card = book.card;

结论：

1. 显然给右表加索引的性能type=ref比左表性能type=range好
2. 左连接A left join B给右表B主键加索引性能更高（因为左连接，左表一定都有，性能取决于右表，所以给右表加索引）
3. 右连接A right join B给左表A主键加索引性能更高

三表优化

需求：三表连接以及如何加索引
传统方案：
explain SELECT FROM c1ass LEFT JOIN book ON c1ass .card=book .card
LEFT JOIN phone ON book.card=phone .card ;

优化一：后两个表phone、book主键加索引
create index Y phone(card)
create index X book(card)
explain SELECT FROM c1ass LEFT JOIN book ON c1ass .card=book .card
LEFT JOIN phone ON book.card=phone .card ;

后2行的type都是ref且总rows优化很好效果不错。因此索引最好设置在需要经常查询的字段中

结论

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

索引失效

1、全值匹配我最爱
2、最佳左前缀法则：指的是查询从索引的最左前列开始且不跳过索引中的列。（带头大哥不能死，中间兄弟不能断）
3、不在索引列上做任何操作(计算、函数、自动or手动类型转换)，会导致索引失效而转向全表扫描type=all（索引列上少计算）
4、不能使用索引中范围条件右边的列（范围之后全失效）
5、尽量使用覆盖索引(索引列和查询列一致)，(尽量减少使用select *)
6、like以通配符开头mysql索引失效会变成全表扫描的操作 (like %加右边)
7、字符串varchar不加单引号索引失效(字符串里留引号)
8、is null,is not null, or, != 无法使用索引会导致全表扫描SQL索引优化口诀全值匹配我最爱，最左前缀要遵守;带头大哥不能死，中间兄弟不能断;索引列上少计算，范围之后全失效;LIKE百分写最右，覆盖索引不写星;不等空值还有or，索引失效要少用;VAR引号不可丢，SQL高级也不难!