MySQL 优化三（优化规则）（高级篇）

在看下边的内容的时候一定要保证你已经会用 explain 分析 sql 了！

索引单表优化案例：#

问题：
比如我们要查询密码 = 666 并且 age>30 的按照 username 倒叙排序的第一条记录；经过分析会发现 sql 内部优化器进行了全表扫描 type=ALL extra 当中还有 using filesort 的存在说明利用了外部的索引进行排序而没有用本表的索引进行排序 所以优化是必须要做的！

解决：
A: 我们现在给 where 后边涉及到的字段都建立上索引 比如：
create index idx_pau on admin(password,age,username);
然后再进行分析：

我们发现 type=range 避免了 type=all 这种全表扫描情况的发生 但是 在 extra 当中还是存在 using filesort 情况产生 这是因为我们创建的索引没有起作用 你会问 那不是在 key 字段当中有 idx_pau 索引说明索引起作用了啊！？我们在查的时候 where 后边是 password = 常量 age > 常量 然后 order by username,password 会用到符合索引当中的内容 age 也会用到符合索引当中的内容 但是哟一条规则就是范围之后的索引是失效了的 前提是你创建的是复合索引 目前看来是这样的额！因为我们创建索引的顺序是 password age username 因为 age 是取的范围 所以后边的索引 username 就会失效 所以才会造成 using filesort 情况还是存在的情况发生！
B. 第一种正确解决方法：
为 where 后边涉及到的每个字段都建立上索引 比如：
create index idx_p on admin(password);
create index idx_p on admin(age);
create index idx_u on admin(username);
这样我们单独为 where 后边涉及到的字段都加上索引 那么首先 type=all 的问题一定解决了的 另外 order by username 的问题 之前我们讲过 order by 后边的字段建立索引的规则 最好和 order by 前边的字段名称相同 如果不同就和前边的字段一起创建复合索引 并且保持顺序相同 详见 using filesort ! 但是这里出现另外一种情况就是单独给 order by 后边的字段创建上了索引 也没有出现 using filesort 情况的发生！
C. 第二种正确解决方法：
那就是只为 password 和 username 创建复合索引
create index idx_pu on admin(password,username);
那么 age > 0 没有走索引也就不会影响到 order by 后边的索引的使用了！那么 age > 0 没有走索引也就不会影响到 order by 后边的索引的使用了！

索引两张表关联优化：#

问题：boys 当中的 id=beauty 当中的 boyfriend_id 利用 left join 查询 我们常规的写法 然后通过 explain 进行分析得出下边的结论 发现 type = all 进行了全表扫描所以我们得要去优化一下！

如何优化呢？
总结：如果是 left join 那么给右表相应的字段添加索引 如果是 right join 那么给左表相应的字段添加索引！
所以我们这里使用 left join 那么给右边的表当中的字段添加索引 给 beauty 当中的 boyfriend_id 创建索引 然后再分析看看：

我们发现 type 当中有 ref 存在 并且 rows 的行数也减少了很多 完美的完成了优化任务！我们发现 type 当中有 ref 存在 并且 rows 的行数也减少了很多 完美的完成了优化任务！

三张表的关联优化：#

原则就是保证 join 语句中被驱动表上 join 条件字段已经被索引
left join 左边为主表也就是驱动表 右边为被驱动表
right join 相反
尽可能减少 join 语句中的循环总次数 原则就是永远用小结果集驱动大的结果集
myslq 如何优化 left join:https://www.cnblogs.com/zedosu/p/6555981.h...

索引优化正式环节#

A. 如何避免索引失效？A. 如何避免索引失效？

对于上边总结的解释：
全值匹配：
比如我们为 admin 表创建了复合索引
create index idx_upa on admin (username,password,age);// 为 username password age 三个字段创建了复合索引
执行 sql:
explain select * from admin where username=’john’ and password=8888 and age=29;

我们发现索引被充分的利用上了！最好的避免索引失效的一个方法就是全值匹配 比如我们创建了 idx_upa 复合索引 那么我们在查询的时候字段的顺序和我们创建复合索引的顺序是一样的 开始末尾中间一个都不少的全写上 这样效率是最高也是绝对可以避免索引失效的 这就叫全值匹配！
最佳左前缀法：（如果索引了多列也就是创建了复合索引 那么要遵守最左前缀法则 指的是查询从索引的最左前列开始并且不跳过索引中的列）
还是拿上边的案例来讲：
我们创建了 idx_upa 复合索引 在查询的时候不可能都是全值匹配 比如我们现在不查找 username=‘’ password=‘’ age=‘’的了 我们要查找其中的任意两个 那么就会出现索引失效的情况发生！
A. 比如：select * from admin where username=’’ and password=’’;

我们查的条件是 username=’’ and password=’’ 因为 username 和 password 和复合索引 idx_upa 当中都是按照顺序来的 u 代表 username p 代表 password 顺序一致 所以不会出现索引失效
B. 如果我们查 select * from admin where password=’’ and age=’’ 那么就会出现索引失效的情况 因为违背了左前缀法则 在找索引的时候会先匹配最左侧的 然后往后找 第一个索引可以理解成火车头 火车头都找不到那么后边的也就不能用了！

C. 如果我们查找的是 select * from admin where username=’john’ and age=29; 那么虽然不会出现索引失效的提示 key 当中也有索引的值 但是 但是 在之前索引不失效的前提下 ref 当中的 const 都是两个 const 但是现在是一个 const 说明其中有一个没有用上索引 这种情况数据量多了的话也是影响性能的 并且 key_len 的长度和我们单独去查找 username=’’的时候的长度是一致的 所以可以肯定的是后边这个没有用上索引

D. 如果我们单独查找 select * from admin where age = 29; 或者单独查找 password=8888 的这种情况也是会出现索引失效的情况的 因为我们是复合索引 越过第一个单独去找第二个第三个是绝对会失效的索引；

所以有句口诀：
带头大哥不能少！
中间兄弟不可断！
除了大哥其他兄弟单独行动都得死！
带头大哥舍弃中间几个兄弟 带着后边几个兄弟 结果就是只有带头大哥能活 其他都得死！
不在索引列上做任何操作（计算 函数 类型转换） 会导致索引实现转向全表扫描

复合索引 idx_upa 我们从火车头上 username 上 它也是索引列上使用了函数 那么直接导致索引失效！
mysql 当中不能使用索引中范围条件右边的列：
全职匹配的情况下：ref 级别 并且 ref 列有三个 const 表示常量

索引当中存在范围：（如果不创建索引那一定是全表扫描 创建完索引有的索引不起作用 ）

最后的结果是 range 范围级别 明显不如 ref 级别高 另外 ref 当中为空 说明有的索引列没起作用！ 解决方法上边讲过！
如果我们不使用复合索引而是为三个字段单独创建索引那么就变成了 ref 级别

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）减少 select *）

以上讲的都是复合索引的前提下哈！
mysql 当中使用不等于！= 或者 <> 的时候无法使用索引并且会导致全表扫描

is null in not null 也无法使用索引
并且 is not null 会进行全表扫描
所以 我们可以给字段约上默认值 这样就不会产生 null 的情况 也就避免了使用 is null 和 is not null 情况的发生！
like 以通配符开头（% adb% 或者 % abc）的情况 mysql 索引会失效并且变成全表扫描 但是 like ‘abc%’是不会造成索引失效的情况发生的
但是在开发过程当中我们经常会遇到必须是 % abc% 的情况 又该如何解决索引失效的问题呢？
那就是使用覆盖索引！什么是覆盖索引之前上边讲过
比如：
create index idx_username on admin (username); 我们给 username 列创建了索引
然后发现 select username from admin where username like ‘% h%’;

但是如果我们这么查：select * from admin where username lilke ‘% abc%’;
那么就会造成索引失效的现象并且会全表扫描 因为没有用上覆盖索引！
并且但是如果我们这么查找 select password from admin where username like ‘% abc%’; 也是会全表扫描并且索引失效
但是如果我们这么查找 select id,username from admin where username like ‘% abc%’; 就不会造成全表扫描和索引失效 因为 id 始终是主键也是从 index 索引表当中去查找相当于也是覆盖索引！
注意：如果我们创建的是复合索引 那么只要是查询的列在复合索引范围内的都是可以避免全表扫描并且避免索引失效问题产生的！
varchar 类型的列字符串如果不加单引号则索引失效
比如 varchar 类型我们平时都是存储的字符串 但是有时候迫不得已存放个 2000 或者其他数字 如果在查询的时候不注意没有加上单引号 那么 sql 优化器就会首先进行隐式的类型转换然后再去搜索并且最主要的是索引失效并且进行了全表扫描
少用 or, 用它来链接的时候会造成索引失效
如果用 or 来连接 比如 select * from admin where username=a or username=b; 这样也会造成索引失效并且造成全表扫描；解决的方式不如分开查 然后到 php 当中再去拼接成一个数组
简单小测试 配合理解一下：

面试题精讲 有助于理解更好的去使用：
前提是我们创建好了复合索引 idx_test03_c1234 c1 c2 c3 c4 这么一个顺序的复合索引哦！
A：索引顺序不同 第一个是 c1 c2 c3 c4
第二个是 c1 c2 c4 c3
第三个是 c4 c3 c2 c1
结果都是一样的 都被用上了索引 也就是说索引有没有被用到和复合索引的顺序无关

B：当中出现了范围查询 我们看到结果是 range 范围级别 那么说明 c1 c2 c3 都被用上了 但是 ref 为空 按照道理来说应该 ref 为 4 个 const 但是 ref 为 null 并且 key_len 的长度正好比四个都用上少一个的长度 那么就是说明 c4 索引失效了！这也验证了范围之后全失效的原则

C.c1=’a1’ and c2=‘a2’ and c4>’a4’ and c3 = ‘a3’
这种情况下虽然我们的 sql 指令顺序变了 但是 sql 优化器内部会按照 c1 c2 c3 c4 的索引顺序去查找 虽然 c4 是范围查找但是它是在最后被 sql 优化器找到的 所以 4 个索引都是有效果的

D：c1=’a1’ and c2=‘a2’ and c4=‘a4’ order by c3 中间兄弟不能断 看来这里明显是断开了 所以肯定的是 c1 c2 是绝对被用到了的 order by c3 其实 c3 也是被用到了的 只不过是被用到了排序上 在 explain 当中是不体现的！ c4 是绝对没有被用上的！ 如果 c3 也没有被用上 那么 extra 当中是要有 usging filesort 的 前边讲过 这种 using filesort 九死一生效率极差！

E:c1=a1 and c2=a2 order by c3 结果和 D 是一样的 c4 肯定没被用上 c3 去排序去了不在 explain 当中显示 c1 c2 是绝对被用上了的！

F:c1=a1 and c2=a1 order by c4; 结果是 c1 c2 被使用 c3 c4 不被使用 并且还产生了 using filesort

G：c1=a1 and c5=a5 order by c2，c3 结果是 c1 被使用 c2 和 c3 被使用但是去排序去了 不会产生 using filesort

H:c1=a1 and c5=a5 order by c3，c2 结果是 c1 被使用 因为索引顺序是 1234 但是 order by 的时候出现了 3 然后再 2 所以会导致 using filesort 的发生

I:c1=a1 and c2=a2 order by c2,c3 结果是 c1 c2 都被使用了 并且排序也是按照 c2 c3 顺序排序 没毛病 非常完美
c2=a2 是一个常量了已经 order by 常量，c3 那么这个常量排序也就是死的 其实就是按照 c3 排序 c3 在索引当中 所以没毛病
（前提是我们从前边查找出了 c2=a2 是一个常量）

j: c5=a5 只是用来迷惑你的 娃哈哈

K：c1=a1 and c5=a5 order by c3，c2 一定是用到了 c1 但是 c3 c2 顺序和索引不一致也会导致 filesort 的产生

L:c1=a1 and c4=a4 group by c2，c3 ； c1 一定是被用到了索引 c4 绝对没被用到 group by 当中 c2 c3 也被用到了 因为也是符合我们的索引顺序的 1234 2 3 是符合这么个顺序的 但是在 explain 当中是不显示的哦

M::c1=a1 and c4=a4 group by c3,c2 c1 一定是被使用了索引的 c4 绝对没有被使用 group by 后边 c3 c2 因为违背了我们的索引顺序 所以会产生临时表以及 using filesort 的产生 情况很不妙！

小总结：
explain 分析先看定值也就是常量 然后看范围 范围之后的索引必失效 然后看 order by order by 的顺序要和索引顺序保持一致如果不一致会产生 filesort 情况的发生 啥意思呢 我们规定了索引的顺序 但是我们在写 sql 指令的时候不是按照找个顺序执行的 那么 sql 优化器就会在内部进行产生内排序 也就是我们看到的 using filesort 情况的发生
group by 基本上都需要进行排序 如果使用的字段索引不正确也是和 order by 一样 在内部先进行排序 然后排序完成之后要么进行搬家 要么进行删除 于是乎就产生了临时表
所以索引的使用非常的重要 尤其是正确使用索引！

优化口诀：

查询优化：
永远小表驱动大表
如图所示 也许在 php 程序当中两个 for 循环的结果是一样的 但是在 mysql 当中确是不一样的 第一个会建立 5 次 mysql 的链接 而 第二个则会建立 1000 次链接 对于 mysql 来说最伤身的就是建立连接释放连接这玩意了 所以永远要小表驱动大表

优化原则：小表驱动大表 即小的数据集驱动大的数据集

in 的右边小结果集 existx 的右边是大的结果集

order by 详解：#

order by 子句 尽量使用 index 方式排序 order by 子句 尽量使用 index 方式排序 避免使用 using filesort 方式排序
对于 order by 的研究 我们主要就是看会不会产生 using filesort 出现就不好了哦
接下来看我们自己的案例：
create index idx_p_u on admin (password,username); 创建 password username 复合索引
特别注意：只要用到 order by 那么前边就不要使用 select * 而是按需查找 要什么字段就写什么字段 因为你一旦写上 select * 下边的总结就都不准了
第一种情况：
explain select username,password from admin order by username;// 有带头大哥存在 username order by 后边复合最左前缀法则所以 没毛病

第二种情况：
explain select username,password from admin where username=’john’ order by username;// 加上 where 条件也是带头大哥存在 order by 后边复合最左前缀法则 没毛病

第三种情况：
explain select username,password from admin where password > 10 order by username;// 我们索引的顺序是 username password 虽然我们在指令当中写的顺序不对 但是 sql 优化器会自动给我们对索引排序 order by 后边复合最左前缀法则

第四种情况：
explain select username,password from admin where username=’john’ order by password;// 符合 使用 where 子句与 order by 子句条件列组合满足索引最左前缀法则！

我们再来看下边这个经典案例：
默认我们创建的索引列的排序都是升序排序的 asc 虽然这里我们 order by 的顺序没有变 age birth 但是排序方式变了 birth 是 desc 方式排序 索引 sql 内部也是会进行一次外部排序 所以也会产生 filesort 情况的发生的 总结：要么同升要么同降

order by 满足两种情况 会使用 index 方式排序：
第一个的解释：order by 后边使用和索引列相同顺序的字段 比如 idx_name_age 我们 order by name 或者 order by name,age 都不会出现 filesort 情况的产生
第二个的解释：使用 where 子句与 order by 子句条件列组合 比如我们创建了 name age 的复合索引 select * from admin where name=’a’ order by age; 这就是 where 子句和 order by 子句条件列组合满足索引的最左前缀法则；
也就是说尽可能在索引列上完成排序操作 遵照索引建的最佳左前缀法则；

最终我们对 order by 做最后的总结（非常重要）：

group by 详解：#

其他的都和 order by 一样
最大的区别就是 where 高于 having 能写在 where 限定的条件就不要去 having 限定了！

本作品采用《CC 协议》，转载必须注明作者和本文链接