索引优化
索引分析
单表优化
查询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;
- 新建acv的索引
alter table bookss
add index idx_bookss_acv(author_id,comments,views);
此时索引被使用到了,但显示using filesort。
原因:
Comments>1是一个范围值,当它在联合查询中处于中间位置时,mysql无法利用索引对后面的view部分进行检索,即range类型查询字段后面的索引无效。
- 根据上述情况,选择绕过comments建立索引
create index idx_bookss_av on bookss(author_id,views);
此时type类型变为ref,并且没有了using filesort.
两表优化
- 创建索引
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。