mysql查询优化

一、为什么要对我们的sql进行优化

很多数据库的数据量都比较小，，导致开发的时候写了一些慢sql，数据量小的时候可能还好，一旦数据量上来了，查询效能极低，并且请求次数过多的话很可能会因为这一个慢sql把你整个系统拖垮，不能正常对外提供服务

二、带着疑问去优化

其实优化手段从业务层面上看很单一，也就是通过给字段添加索引，相信很多人都听过sql优化加索引能提高查询效率，但是很少去思考跟索引相关的一些问题，比如

1. 索引什么时候会生效？
2. 索引什么时候会失效？
3. 索引什么时候生效了，但是选择错了索引？
4. 表连接查询的时候如何利用索引来减少驱动表和被驱动表之间的比较次数？
5. 当我们用left join关键字的时候驱动表和被驱动表是如何选择的？

等等一系列的问题

三、mysql优化手段

1. 回表和覆盖索引

2. 1. 回表操作数据准备

create table t1 (id int primary key, k int, s varchar(16), index k(k)) engine=InnoDB;insert into t1 values(100,1,'aa’),(200,2,'bb’),(300,3,'cc’),(500,5,'ee’),(600,6,'ff’),(700,7,'gg');

​ b. 回表操作的sql

select * from t where k between 3 and 5;查看sql执行计划: explain select * from t1 where k between 3 and 5;回表操作的原因:因为select查询的是所有字段的值，所以会根据k这颗索引数查出来的id在去主键对应的这颗树去查询其他字段的值，这个操作叫做回表操作回表操作的步骤如下:  1、在 k 索引树上找到 k=3 的记录，取得 ID = 300；  2、再到 ID 索引树查到 ID=300 对应的 R3；  3、在 k 索引树取下一个值 k=5，取得 ID=500；  4、再回到 ID 索引树查到 ID=500 对应的 R4；  5、在 k 索引树取下一个值 k=6，不满足条件，循环结束。  回到主键索引树搜索的过程，我们称为回表。读了 k 索引树的 3 条记录，回表了两次。

​ c. 覆盖索引

select id from t1 where k between 3 and 5;这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

2. 联合索引

​ a . 准备数据

create table t2( id int(11) primary key, id_card varchar(32), name varchar(32), age int(11), ismale tinyint(1), key id_card (id_card), key name_age (name,age)) engine=InnoDB;
insert into t2 values(1, '10000', 'lisi', 20, 0),(2, '20000', 'wangwu', 10, 0),(3, '30000', 'zhangliu', 30, 1),(4, '40000', 'zhangsan', 10, 0),(5, '50000', 'zhangsan', 10, 0),(6, '60000', 'zhangsan', 20, 0);

​ b. 最左前缀原则

(name, age)联合索引当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符

​

​ c. 建立联合索引需要考虑什么？

原则一：维护索引少。已经有了 (a,b) 这个联合索引后，一般就不需要 (a) 的单字段索引，可能需要 (b) 的单字段索引原则二：占用空间少。a 字段是比 b 字段大的 ，建议创建一个 (a,b) 的联合索引和一个 (b) 的单字段索引

​ d. 索引下推

select * from t2 where name like '张%' and age=10 and ismale=1;5.6 之前只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

3. 选错索引如何解决

​ a. 数据准备

create table t3 (id int(11) primary key, a int(11), b int(11), key a (a), key b (b)) engine=InnoDB;
delimiter ;;create procedure idata_t3() begin declare i int; set i=1; while(i<=100000) do insert into t3 values(i, i, i); set i=i+1; end while; end;;delimiter ;call idata_t3();

​ b. 选错索引

explain select * from t3 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;索引 a 查询: 扫描索引 a 的前 1000 个值，然后取到对应的 id，再到主键索引上去查出每一行，然后根据字段 b 来过滤，扫描 1000 行。索引 b 查询: 扫描索引 b 的最后 50001 个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，扫描 50001 行。

​ c. 如何解决

1. 强制指定索引select * from t3 force index (a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;2. 语句优化select * from t3 where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;使用这两个索引都需要排序。因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描 1000 行的索引 a。

4. 前缀索引的影响

​ a. 数据准备

create table t4(id int(11) primary key, name varchar(32), email varchar(64)) engine=innodb;
insert into t4 values(1, 'zhangsh1234', 'zhangsh12342@xxx.com’),(2, 'zhangssxyz', 'zhangssxyz@xxx.com’),(3, 'zhangsy1998', 'zhangsy1998@aaa.com’),(4, 'zhangszhsz2', 'zhangszhsz2@aaa.com');

​ b. 前缀索引执行

select id, name,email from t4 where email=‘zhangssxyz@xxx.com’; 如何执行？
从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。——扫描1行
从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。——扫描4行

​ c. 前缀索引影响

1、可能导致查询语句读数据的次数变多使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。select count(distinct left(email,4)) as L4, count(distinct left(email,5)) as L5, count(distinct left(email,6)) as L6, count(distinct left(email,7)) as L7 from t4;
2、无法使用覆盖索引select id,email from t4 where email='zhangssxyz@xxx.com’;

5. 索引失效情况

​ a. 数据准备

create table tradelog ( id int(11) primary key, tradeid varchar(32), operator int(11), t_modified datetime, KEY tradeid (tradeid), KEY t_modified (t_modified)) engine=InnoDB default charset=utf8mb4;
insert into tradelog values(1, 'aaaaaaaa', 1000, now()), (2, 'aaaaaaab', 1000, now()),(3, 'aaaaaaac', 1000, now());

​ b. 条件字段函数操作

select count(*) from tradelog where t_modified='2018-7-1’;select count(*) from tradelog where month(t_modified)=7;对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。同理select * from tradelog where id + 1 = 10000 也不能使用id索引
解决办法:   1. 新建一列用来存储函数后的值，然后建立索引   2. 不用函数，做拆分，如下:explain select count(*) from tradelog where t_modified='2018-7-1';explain select count(*) from tradelog where month(t_modified)=7;explain select count(*) from tradelog where (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or (t_modified >= '2018-7-1' and t_modified<'2018-8-1');explain select * from tradelog where id + 1 = 10000;

​ c. 隐式类型转换

select * from tradelog where tradeid=110717;
select ’10’ > 9 的结果：如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。
等价于select * from tradelog where CAST(tradeid AS signed int) = 110717;

​ d. 隐式编码转换

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;优化：select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

1、根据 id 在 tradelog 表里找到 L2 这一行；2、从 L2 中取出 tradeid 字段的值；3、是根据 tradeid 值到 trade_detail 表中查找条件匹配的行。——为什么没走索引？
select * from trade_detail where tradeid=$L2.tradeid.value;字符集 utf8mb4 是 utf8 的超集
注意:MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。

6. 排序

​ a. 全字段排序

select city,name,age from t5 where city='hangzhou' order by name limit 1000 ;
1、初始化 sort_buffer，确定放入 name、city、age 这三个字段；2、从索引 city 找到第一个满足 city='杭州’条件的主键 id；3、到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；4、从索引 city 取下一个记录的主键 id；5、重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id ；6、对 sort_buffer 中的数据按照字段 name 做快速排序；7、按照排序结果取前 1000 行返回给客户端。

​ b. 当排序的单行长度太大

1. 会先把name和id两列的值查询出来，放入到sort_buffer中2. 根据sort_buffer中的数据进行name排序3. 在根据id去回表

​ c. 优化

1. 如果能够保证从 city 这个索引上取出来的行，就是按照 name 递增排序，是不是就不用再排序了？alter table t add index city_user(city, name);
2. 进一步优化   alter table t add index city_user_age(city, name, age);
a、从索引 (city,name,age) 找到第一个满足 city='杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
b、从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
c、重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。
——考虑索引维护的代价