mysql高级---索引优化

索引分析

单表优化

查询author_id为1且comments>1,view最多的category_id

#查询author_id为1且comments>1,view最多的category_id
explain 
select id,category_id 
from bookss 
where author_id =1 and comments >1 
order by views 
limit 1;

1. 新建acv的索引

alter table bookss 
add index idx_bookss_acv(author_id,comments,views);

此时索引被使用到了，但显示using filesort。
原因：
Comments>1是一个范围值，当它在联合查询中处于中间位置时，mysql无法利用索引对后面的view部分进行检索，即range类型查询字段后面的索引无效。

2. 根据上述情况，选择绕过comments建立索引

create index idx_bookss_av on bookss(author_id,views);

此时type类型变为ref，并且没有了using filesort.

两表优化

1. 创建索引

alter table class add index idx_c(card);

alter table book add index idx_b(card);

对于左连接，
创建左表的索引对于查找的性能没有提升。Rows 12+12
创建右表的索引对查找的性能有很大的提高。Rows 12+1

如果此时交换左右表顺序进行左连接查询，查找也没有被优化。

右连接同理。
原因：
Left join条件用于确定如何从右表搜索行，左边一定有，所以右边是关键点。
因此在二表连接时，左连接对右表建立索引，右连接对左表索引。

三表优化

alter table book add index idx_b(card);
alter table phone add index idx_p(card);

当连接的两表建立了索引时，查询被优化。
三表连接时，对被连接的两表建立索引。

join语句的优化总结

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

避免索引失效

方法

案例表 staffs(id主键,name,age,pos)
索引name,age,pos

最好是全值匹配

最好是索引怎么建，就怎么用。

最佳左前缀法则

如果索引了多列，要遵守最佳左前缀法则。指查询从索引的最左列开始并且不跳过中间的列。

如果按照索引建立的顺序，1name,2age,3pos，依次查询带1，带12，带123的数据，可以看到，索引的使用都是没有问题的，key_len的长度依次增加，ref的个数也依次增加。

如果不使用建立索引顺序中最左侧的字段1name，直接使用2，23，可以看出索引并没有被用到。

如果使用了索引顺序中最左侧的字段1name，但是中间的字段没有使用。
如上图使用了13，虽然索引显示被使用了，但是ref只有一个const，说明并没有完全的成功使用索引。

不在索引列上做任何操作

包括计算、函数、自动手动类型切换

explain select * from games where left(name,3)= 'rim';

存储引擎不能使用索引中范围条件右边的列

二者key_len一致，说明后者也只检索到age>25

尽量使用覆盖索引

只访问索引的查询，索引列与查询列一直，减少select *

建立name age的索引，id为主键，
因此，id，name，age，都满足覆盖索引，此时使用%开头，索引不会失效
而*，email这种不符合覆盖索引的情况，会导致失效

Mysql在使用不等于!= <>时无法使用索引会导致全表扫描

Is null，is not null 也无法使用索引

like以通配符开头 %abc 索引失效会变成全表扫描

Like 虽然是范围但是和in > < 不一样，在不用%开头的情况下可以使用到c

字符串记得加单引号

少用or连接

案例

Create index idx_test03_c1234 on test03(c1,c2,c3,c4);

顺序影响

如上，全部使用时可以不按顺序，optimizer会自动检测，但是最好按顺序。

order by

C3用于排序，没有用于查找

C3用于排序后，C4不会被使用到

跳过c3，使用c4排序会产生using filesort

使用索引时最好按照顺序
前者按照索引顺序排序，后者没有按照顺序所以mysql只能自己排序。

虽然没有按照顺序，但是排序时c2已经是一个常量了
相当于
order by 字段c3,字段c2 —> order by 字段c3,准确的值

group by

案例1 2用于查找，所以2之后的都没有被用到 只使用了1
案例2 不按顺序

查询优化

永远小表驱动大表

小的数据集驱动大的数据集

select * from A where id in (select id from B);

B表的数据集小于A时，用in优于exists

select * from A where exists (select 1 from B where B.id = A .id);

A表的数据集小于B时，exists优于in

• exists 只返回true或false，因此子查询中的select * 也可以时select
  1或其他，实际执行时会忽略select清单，因此没有区别
• 子查询的实际执行过程可能经过了优化，可进行实际检验以确定是否有效率问题
• 子查询也可以使用条件表达式

order by 关键字优化

mysql支持两种方式排序，filesort 和 index

Index方式

index效率高 指扫描索引本身完成排序

• 尽量使用index 方式排序，避免使用filesort方式排序
• 尽可能在索引列上完成排序操作，遵守索引建立的最佳左前缀规则
• 排序的顺序保持一致，都升序或都降序

案例
表tblA(age,birth)
索引（age,birth）

12没有最左前缀
4一个升序一个降序

filesort 方式

filesort 方式效率较低，有两种算法。

双路排序

扫描两次磁盘，最终得到数据。
读取行指针和orderby列，对他们排序，扫描排序好的表，按照列表中的值重新在列表中读取对应的数据输出。

单路排序

从磁盘读取查询所有需要的列，按照orderby列在缓存中进行排序，然后输出。
效率更快，避免了二次读取数据。
把随机I/O变成了顺序I/O，但会使用更多的空间。

结论

单路是后出的，所以比双路优秀。
但单路也存在问题
当使用单路排序的方法时，有可能取出的数据的总大小超出了sort_buffer的容量，导致每次取出sort_buffer容量大小的数据进行排序，反而产生多次I/O。

优化策略

增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置

group by 关键字优化

• Group by与order by 相似
• Group by实质时先排序后进行分组，遵照索引建立的最佳左前缀规则。
• 当无法使用索引列，增大sort_buffer_size参数的设置，增大max_length_for_sort_data参数的设置。
• where高于having，能写在where限定的条件就不要写在having。