SQL优化（二）

一、优化示例：
假设有复合索引 idx_a1_a2_a3_a4
explain select a1,a2,a3,a4 from test where a1 = 1 and a2 = 2 and a3=3 and a4=4—使用了全部的符合索引
explain select a1,a2,a3,a4 from test where a1 = 1 and a2 = 2 and a4=3 order by a3 --a4 因为跨列使用 造成了该索引失效 需要回表查询 因此是using where
explain select a1,a2,a3,a4 from test where a1 =1 and a4=4 order by a3 --此sql出现了 using filesort （文件内排序）不要跨列使用 where 和 order by 不能跨列使用
总结：如果（a,b,c,d）复合索引 和使用的顺序和创建的顺序全部一致 则复合索引全部使用 如果部分一致，则使用部分索引

二、单表优化
CREATE TABLE book
( bid int(4) PRIMARY key,
name VARCHAR(20) not NULL,
authorid int(4) not null,
publicid int(4) not null,
typeid int(4) not null
);

INSERT into book VALUES (1,‘java’,1,1,2);
INSERT into book VALUES (2,‘tc’,2,1,2);
INSERT into book VALUES (3,‘wx’,3,2,1);
INSERT into book VALUES (4,‘math’,4,2,3);

1）此时 type 为ALL 优化：加索引
alter table book add index dx_bta(bid,typeid,authorid);
EXPLAIN SELECT bid from book where typeid in(2,3) and authorid = 1 order BY typeid desc

2. 还是有using filesort 还需要优化
   调整索引的顺序 alter table book add index idx_tab (typeid,authorid,bid)
   

3）此时额外的查询没了，此时将之前的索引删掉防止干扰
drop index dx_bta on book

4. 此时优化级别为index
   变更sql顺序以及索引顺序
   alter table book add index idx_atb(authorid,typeid,bid);
   explain select bid from book where authorid = 1 and typeid in(2,3) order by typeid desc
   ![在这里插入图片描述](https://img-blog.csdnimg.cn/20210517193050136.png
   

5)可以看到 index 级别提高到range

小结：
索引不能跨列使用（最佳左前缀） 保持顺序一致性
索引需要逐步优化
将含in的范围查询放到where条件的最后 防止失效
typeid虽然也在索引中 但是含in的范围查询已经使该索引失效 所有需要回原表 using where

三、两表优化
sleect * from teacher t left outer join course c on t.cid = c.cid where c.name = ‘java’
索表往哪张表加？–小表驱动大表 小表放左边 大表放右边
如 select …where 小表，x10 =达标.x300
for(int i= 0;i<小表.length;i++){
for(int j= 0;j<大表.lengthlj++){
…
}
}
可以看到大表、小表不管放左右边 总的循环参数都是一样的，但是对于双层循环俩说一般建议 将数据小的循环 放外层
这样程序运行速度较快
因此当两张表连表查询时候，将数据量小的那张表where后面条件放左边
sleect * from teacher t left outer join course c on t.cid = c.cid where c.name = ‘java’ （假设t表数据量大）

一般情况对于左外连接 给左边加索引 右外连接 给右边加索引

复合索引不能使用！= <> 或is null >等 否则自身以及右侧全部失效

四、避免索引失效原则
索引失效：可能是SQL优化器的干扰，可能回影响我们的优化

补救：尽量使用覆盖索引 using index
(a,b,c)
select a,b,c from xx where a = and b= xx and c= xx
2) like尽量以常量开头 不要以%开头 否则索引失效
如 select * from xx where name like ‘%李’ —如name 为索引 则失效
3）尽量不要使用类型转换
如：select * from teacher where name = 123 --程序地城将123转换成‘123’ 进行了类型转换
4）尽量不要使用or 否则索引失效

五、常见优化方法及慢查询
1)exist和in
如果主查询的数据集大，则使用in
如果子查询的数据集大，则使用exist

exists语法：将主查询的结果 放到子查询结果中进行条件校验（是否有数据，如果有数据则校验成功，如果符合校验，则保留数据）
select tname from teacher where exists(select * from teacher )

in语法：select * from A where id in (select id from B)

2)order by 优化
using filesort 底层有两种算法 双路排序 和单路排序（根据IO的次数）
mysql4.1之后默认使用单路排序
双路：会扫描两次磁盘 （第一次从磁盘中读取排序字段，对排序字段进行排序 ，排序动作在buffer 缓冲区进行的排序，第二次扫描查询字段）
为什么改为单路：IO消耗性能，在buffer进行排序 单路排序 不一定是一次IO 有可能是多次IO 原因：如果数据量特别大 则无法将所有字段的数据一次性
读取完毕 因此会进行分片读取
单路排序比双路排序占用更多的buffer 单路排序在使用时 如果数据两大 可以考虑调大buffer的容量大小
如果max_length_for_sort_data太小 mysql底层会自动从单路到双路

提高order by查询的策略
1.可以选择使用单路双路 调整buffer容量大小
2.避免 select * …
3.复合索引 不要跨列使用 避免出现using filesort
4.尽量保证全部的排序字段 排序的一致性 都是升序 或降序

3）如何挑出慢sql ：慢查询日志：mysql 提供的一种日志记录 用于记录mysql中相应时间超过阈值的SQL语句（long_query_time 10秒）
慢查询日志默认是关闭的，建议 开发调优是打开 而最终部署时挂壁
1检查是否开启了慢查询日志 show variables like ‘%slow_query_log%’
2临时开启 set global slow_query_log = 1
exit
service mysql restart
3.永久开启：改配置文件
4慢查询阈值修改
show variables like ‘%long_query_time%’
临时设置阈值 set global long_query_time = 5–设置完毕后 重新登录后生效
永久设置阈值：改配置文件
模拟：select sleep(4);
–查询超过阈值的SQL条数： show global status like ‘%slow_queries’
–慢查询的sql被记录在了日志中 因此可以通过查看日志 查看具体的慢SQL

4）mysqldumpslow工具
可以通过此工具查看慢SQL,通过一些过滤条件 快速查找需要定位的慢SQL: mysqldumpslow -help
–获取返回记录最多的三个SQL
mysqldumpslow -s -r -t 3 /var/lib/mysql/xxx.log
–获取访问次数最多的三个SQL
mysqldumpslow -s c -t 3 …
–按照时间排序 前十条包含left join查询语句的SQL
mysqldumpslow -s t -t 10 -g “left join” xx.log