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

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');

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 条记录，回表了两次。

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

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);

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

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

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

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();

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 行。

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。

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');

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行

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’;

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());

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;

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

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 字符集，再做比较。

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 行返回给客户端。

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

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='杭州’条件时循环结束。——考虑索引维护的代价