MySql语句优化

`idx_name_age_position` (`name`,`age`,`position`)

-- 关闭查询缓存
set global query_cache_size=0;
set global query_cache_type=0;

-- 没有使用到联合索引，如果查询出的结果集过大，
-- 这样会造成回表效率很低，使得mysql不会使用索引，进行全表扫描
-- 如果使用name会进行索引覆盖，type会从all升级为range
-- extra 中using where 表示没有使用where语句进行处理，并且没有覆盖到索引
EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

-- 强制走索引由于回表的数据量加大，会造成查询效率较低
-- extra 中 using index condition 在查询的时候会使用部分索引列
EXPLAIN select * FROM employees force index(idx_name_age_position) where name > 'LiLei' AND age = 22 AND position ='manager';

-- 优化原则：使用覆盖索引优化，减少回表增加查询效率
EXPLAIN select name,age,position FROM employees where name > 'LiLei' AND age = 22 AND position ='manager';

-- in \ or 在数据量较大的时候会走索引，在数据量较小的时候会选择进行全表扫描
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';

EXPLAIN SELECT * FROM employees where (name = 'LiLei' or name = 'Lucy') AND age = 22 AND position ='manager';

EXPLAIN SELECT * FROM employees_copy WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';

EXPLAIN SELECT * FROM employees_copy where (name = 'LiLei' or name = 'Lucy') AND age = 22 AND position ='manager';

-- like 'LiLei%' 的形式不管表的数据量大小都会走到索引，在Mysql5.6以后使用了索引下推
-- 索引下推：正常情况下按照最左索引原则, name like 'LiLei%' 这种情况只会走name字段索引
-- 因为根据name字段过滤后，得到的age跟position是无序的，所以无法使用索引。
-- 在MySql 5.6以前，根据like查询出来数据后会根据主键id逐个进行回表操作，到主键索引找到相应的记录
-- 在比较 age 跟 position这两个字段的值是否符合。
-- 在Mysql 5.6以后，使用了索引下推，可以在遍历索引过程中，对索引所包含的所有字段进行匹配，过滤掉
-- 不符合条件的记录之后再回表，可以减少回表次数。使用了索引下推以后，根据name匹配完以后会根据age
-- 跟position的条件进行筛选，拿着过滤完剩下的索引对应的主键id进行回表操作。
-- 在下面的例子中由于使用了索引下推，所以使用的key_len为140，key_len的计算长度为：varchar = 3n + 2
EXPLAIN SELECT * FROM employees where `name` like 'LiLei%' and age = 22 and position = 'manager';

EXPLAIN SELECT * FROM employees_copy where NAME LIKE 'LiLei' and age = 22 and position = 'manager';

-- 如果查询出来的结果集的数据量较大，有可能会进行全表扫描（因为会造成大量数据进行回表）
EXPLAIN SELECT * FROM employees where NAME > 'a';

EXPLAIN SELECT * FROM employees WHERE NAME > 'zz';

-- 可以使用覆盖索引对这种情况进行优化
EXPLAIN SELECT name,age,position from employees where NAME > 'a';

-- 开启trace
set session optimizer_trace = "enabled=on",end_markers_in_json=on;

EXPLAIN SELECT * from employees where NAME > 'a' ORDER BY position;

-- "rows_estimation": ‐‐预估表的访问成本 potential_range_indexes：可能使用的索引
-- analyzing_range_alternatives：分析辅助索引
select * from information_schema.OPTIMIZER_TRACE;

-- order、group优化
-- 该语句可以走索引，并且extra字段没有出现 using filesort(在磁盘上对数据进行排序)
-- 走索引的原因，由于 name age position 是联合索引，当LiLei确定后，使用 age进行排序是可以
-- 走索引的并且不需要进行外部排序即 using filesort
EXPLAIN SELECT * from employees where NAME = 'LiLei' AND position = 'dev' ORDER BY age;

-- 该语句会使用到 filesort，由于 order by的时候，联合索引中间缺少了一个字段，所以该语句没有走
-- 索引而是使用的using filesort
EXPLAIN SELECT * from employees where NAME = 'LiLei' ORDER BY position;

-- 该语句会使用到部分索引，没有出现filesort
EXPLAIN SELECT * FROM employees WHERE NAME = 'LiLei' ORDER BY age, position;

-- 该语句会出现filesort，因为order by的顺序与索引顺序不同
EXPLAIN SELECT * FROM employees WHERE NAME = 'LiLei' ORDER BY position, age;

-- 该语句不会出现filesort，因为age是常量，age固定以后对应的position是有序的
EXPLAIN SELECT * FROM employees WHERE NAME = 'LiLei' AND age = 20 ORDER BY position, age;

-- 该语句出现filesort，虽然age与position的位置是正确的，但是position的顺序是降序，不符合索引顺序
-- 所以出现了filesort
EXPLAIN SELECT * FROM employees WHERE NAME = 'zhuge' ORDER BY age ASC, position DESC;

-- 该语句会出现using filesort因为在使用in条件后过滤出来的age是无序的，所以无法使用索引
EXPLAIN SELECT * FROM employees WHERE NAME in('LiLei','zhuge') ORDER BY age,position;

-- 该语句会出现using filesort 因为由于name > a的数据量较大并不会走到索引
EXPLAIN SELECT * FROM employees WHERE NAME > 'a' ORDER BY NAME;

-- 优化改sql，可以使用覆盖索引
EXPLAIN SELECT name,age,position FROM employees WHERE NAME > 'a' ORDER BY NAME;

-- 单路排序、双路排序
-- 如果数据量较大，不能在sort buffer中全部存储，会使用临时文件在磁盘进行排序（number_of_temp_files）

-- sql是从employees表中从 10000行向后读取10行数据
-- 该sql会从数据库中读取 10010行数据，然后舍弃前10000行数据，只保存后10行数据
-- 因此如果一张表的数据量比较打得时候，查询效率比较低
select * from employees LIMIT 10000,10;

-- 根据自增连续主键分页查询的优化 （查询结果集为 id：90002 - 90006）
EXPLAIN SELECT * FROM employees LIMIT 90000,5;

-- 通过主键索引进行优化，但是使用场景比较少，因为如果主键空缺的话会导致数据不一致
-- 查询结果集为 id：90001 - 90005 由于在90000之前删除了数据，所以造成了数据不一致
EXPLAIN SELECT * FROM employees where id > 90000 LIMIT 5;

-- 根据非主键字段排序的分页查询(并没有走索引字段，而是全表扫描，因为索引查出来的数据太多了)
-- 查询出来的数据需要进行回表操作，所以mysql放弃了索引使用全表扫描
EXPLAIN SELECT * FROM employees ORDER BY name limit 90000,5;

-- 可以使用inner join，可以让排序时返回的字段尽可能少，然后根据主键去查询到对应的记录
-- 这样使得sql能够走到辅助索引
EXPLAIN SELECT * from employees e INNER JOIN
(SELECT id FROM employees ORDER BY `name` LIMIT 90000,5) et on e.id = et.id;


-- join查询的优化
-- PRIMARY KEY (`id`),
-- KEY `idx_a` (`a`)

-- 嵌套循环算法，连接的时候使用索引字段
-- 一次一行从驱动表中读取行，在这行数据中取到关联字段，根据关联字段在被驱动表中取出满足条件的数据
-- 然后将两张表的结果合集。
-- 在inner join中 驱动表往往是小表，被驱动表的数据量比较大，优化器一般会选择数据量比较小的表作为驱动表。
-- 当使用left join的时候左表作为驱动表，当使用right join的时候右表作为驱动表
-- 如果在 explain 的结果中如果 extra 没有使用 using join buffer 则表示使用的是NLJ算法
EXPLAIN SELECT * from t2 JOIN t1 on t1.a = t2.a;

-- 语句的执行过程
-- 整个过程会扫描 t2中所有的行数据(扫描 100行),扫描每行记录的时候会从记录中获取字段的a值
-- 根据t2 表中的 a值查询 t1表中对应的行(扫描 100次t1的索引,一次扫描可以认为最终扫描t1表的一行完整记录,也就是对t1
-- 表一共扫描了100 行)，因此整个扫描过程扫描了 200条记录。当使用的关联字段不是索引的时候，不会使用该算法
-- 因为没有索引只能进行全表扫描,最坏的情况下会扫描、比较 10000 * 100次


-- 基于块的循环嵌套连接算法,连接的时候没有使用索引字段
-- 把驱动表的数据装入join_buffer中,然后扫描被驱动表,把被驱动表的每行数据取出来与join_buffer中的数据进行比较
-- Explain 字段中的 using join buffer(Block Nested Loop)表示使用基于块的循环连接嵌套算法
EXPLAIN SELECT * FROM t2 JOIN t1 ON t2.b = t1.b;

-- 改语句的执行过程
-- 1.把 t2表中的所有数据装入join_buffer中
-- 2.查询t1 表中的所有数据，将每行数据取出来与join_buffer中所有数据进行对比
-- 3.返回满足join条件的数据
-- 在执行过程对t1 t2表都做一次全表扫描，因此扫描的总行数为 t1表的数量 + t2表的数量 = 10100次
-- 并且join_buffer中的数据是无序的，因此每次在内存中的对比次数为 100 * 10000 如果join_buffer的
-- 大小不足以存放驱动表中的数据，那么会将驱动表的数据按照join_buffer的大小分割，每次进行对比

-- 对于join关联的优化： 1、关联字段使用索引 2、如果明确驱动表可以使用straight_join来指明驱动表(是指根据条件过滤之后
-- 数据量较小的那张表)，减少选择驱动表的性能损耗


-- in、exists优化,优化原则，小表驱动大表

EXPLAIN SELECT * from t1 where id in (select id from t2);

-- 上面sql等价于

-- for (select id from t2) {select * from t1 where t1.id = t2.id }

EXPLAIN SELECT * from t1 where EXISTS (select 1 from t2 where t2.id = t1.id);

-- 上面sql等价于

-- for(select id from t1) {select * from t2 where t2.id = t1.id }

-- count优化
EXPLAIN SELECT COUNT(1) from employees;

EXPLAIN SELECT COUNT(id) from employees;

EXPLAIN SELECT COUNT(NAME) FROM employees;

EXPLAIN SELECT COUNT(*) FROM employees;

-- 如果统计name有索引 count(1) ~ count(*) > COUNT(name) > COUNT(id)
-- 如果统计name没有索引 COUNT(1) ~ count(*) > COUNT(id) > COUNT(name)
-- 因为name有索引的时候 索引大小要小于主键索引大小所以mysql统计的时候会优先选择辅助索引