mysql查询优化

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 已经“覆盖了”我们的查询需求，我们称为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

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)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

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。

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. 前缀索引执行

c. 前缀索引影响

  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 

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;

  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;

  a. 全字段排序

select city,name,age from t5 where city='hangzhou' order by name limit 1000 ;

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

    c. 优化