索引优化【重中之重】

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

单表优化

案例说明：查询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全表扫描，性能较差，问题是现在给哪个表加索引，左表class？右表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 class 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优化很好效果不错。因此索引最好设置在需要经常查询的字段中

结论：Join语句的优化

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高级也不难!

案例一：索引第一个字段不能丢，否则索引失效

ALTER TABLE staffs ADD INDEX idx_ staffs nameAgePos(name, age, pos):
a、用到索引带头大哥name，索引没有失效

b、没有用到索引带头大哥name，索引失效（违背最佳左前缀法则：带头大哥不能死）

c、跳着使用索引(第一个是带头大哥)，索引不会失效，但只会使用部分索引（违背最佳左前缀法则：中间兄弟不能少)

案例二：索引列上少计算

left函数导致全表扫描ALL

案例三：范围之后全失效

案例四：尽量少使用select 、不等于(!=或者<>)、is null,is not null、%加尽量右边

小总结