索引优化（一）
# 什么是索引？
观察下面一组数字：
| 3 | 9 | 6 | 5 | 7 | 4 | 8 | 1 | 2 | 10 |
如果我想查找最后一个数字，那么我付出的最大查询成本是：查询10次，数据越多，查询代价越大。
如果我想查询某个范围的值，比如查找小于5的值，我需要从头到尾把每个值都需要对比一遍，最终挑出小于5的值。
如果我把上面这组数字变成如下图：有序的数据结构，这样就可以利用二分查找法
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
那么此时我查找某个具体的值付出的最大查询成本大概仅仅是3、4次。
如果我想查找某个范围的值，比如还是小于5，因为数据有序排列的，那么等我通过二分法确定5的位置时，那么它左边的值全部就是小于5的数据，立即就能拿到。
经过上面分析你得到什么启示：**想使用某种快速查找的算法，这前提必须是建立在某种有规律的特定的数据结构之上的。而我们创建索引的过程，就是创建为了实现快速查找算法所必须的数据结构的过程**。而在mysql中，想使用索引实现快速查找，你可以简单理解为：**必须要求索引的数据是按顺序排列的**。
----
# 使用索引和非索引的查询成本对比
整个stu表如下:
怎么查看分析查看查询成本，通过explain查看执行计划。
**执行某条sql语句**
- 不使用索引：
**分析：**
type: all 说明mysql没有走索引，走了全表扫描，8条记录全部取出。
IO成本： 8条记录（全表）
算法成本：普通查找、必须一个个的去扫描对比。
- 使用索引：
**分析：**
type=ref：ref表示使用了普通索引。1条记录被取出。
IO成本： 1记录+1列索引
算法成本：二叉树查找法，通过有规律的数据结构，快速定位到某个数据，比全表扫描快。
由此可见：**使用索引之后，在一般情况下，无论是IO成本还是计算查找成本都远低于全表扫描。**
---
# 联合索引
需求：查出班级class_id为3且年龄小于25的人
```sql
select * from stu where age >20 and class_id=3;
```
可以把age建立index索引，查询计划如下
**分析：**
IO 成本：4条记录
算法成本：当查找age>20走了索引。然后就把age>20的满足条件的记录都取了出去，然再按普通查找的方式扫描class_id=3
**但是这仅仅够么，还不够完美：**
为了尽可能的利用mysql的索引特性，我们可以建立一个联合索引。当mysql使用联合索引时，会先扫描完age列之后，然而不会再把满足age列条件的记录都取出来，而是再继续利用二叉树查找算法扫描class_id这个列，得到最终结果的索引项，取出索引中保存的地址，根据地址把表中的记录取出。
给age和 class_id建立联合索引：alter table stu add index fuhe (class_id,age);
查询后的执行计划如下：
**分析：**
IO成本：row为1，最终的IO成本变成了1
算法成本：相比上面只建立age单个索引，class_id=3这个条件的查询也利用上了索引，利用索引之后，提高了class_id=3的查找效率，最终又降低了最终从表取出数据的IO成本。
**注意：**
在当前查询语句中，建立联合索引的条件只能是(class_id,age)的顺序，不能是(age,class_id)这种顺序。因为这样的话第二列class_id索引会利用不上，最终还是走了age单个列的索引查询。
为什么？不是说联合索引中，只要使用了第一列索引，就会使用第二列索引么？
不是的，这是不对的。
试验：以这种(age,class_id)创建索引的顺序，执行下面语句：
分析：key值为fuhe ，说明使用到了索引，看似也是使用到了索引，观察rows的值，是4，并不是1，这说明一个什么问题？其实真实的情况还是只是使用了联合索引中 age这个一单列的索引，并没有使用到class_id。
为什么？
因为你不了解**联合索引的内部结构**是怎样的？
比如：以该表的(class_id,age)联合索引为例,它内部结构简单说就是下面这样排列的：
mysql创建联合索引的规则是首先会对联合索引的最左边的，也就是第一个class_id字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的age字段进行排序。其实就相当于实现了类似sql语句中，走了 order by class_id age这样一种排序规则。
所以：第一个class_id字段索引是绝对有序的，而第二字段就是无序的了。我之前说过，想利用到索引，必须要求该列索引的数据必须是有规律的特定的数据结构，也就是在这里必须是有序的。而所以通常情况下，直接使用第二个age字段进行条件判断是用不到索引的，这就是所谓的mysql为什么要强调最左前缀原则的原因。
**那么什么时候才能用到呢?**
观察可知，当然是在class_id字段是等值匹配的情况下，cid才是有序的。发现没有，观察两个class_id值为2 的age字段值是不是有序的呢。从上往下分别是15 16。
这也就是**mysql索引规则中要求联合索引要想使用第二个索引，必须先使用第一个索引的原因，而且第一列索引必须是等值匹配。**
---
# 多表查询的优化
需求：查出所有在读班级的学生
```sql
select * from stu where class_id in (select id from class);
```
执行计划如下：**分析：**
Select_type中 DEPENDENT SUBQUERY代表这个表是子查询出来的，而且是相关子查询。
执行计划中，其实它并不是先执行in子查询语句找到id，然后再去到stu中去查复合id值的。
mysql进行了子查询优化，把in子查询转换成exists相关子查询，所以它实际等同于这条sql语句：
```sql
select *
from stu
where exists(select id from class where stu.class_id=class.id );
```
而exists相关子查询的执行原理是: 循环取出外表的每一条记录与子查询中的表进行比较，比较的条件是stu.class_id=class.id 然后看外表的每条记录的class_id是否在内表的id字段存在，如果存在就行返回外表的这条记录。
是不是很类似join连接查询？
**exists查询有什么弊端？**
由exists执行原理可知，外表使用不了索引，必须全表扫描，因为是拿外表的数据到内表查。而且必须得使用外表的数据到内表中查（外表到里表中），顺序是固定死的。
**如何优化？**
建索引。但是由上面分析可知，要建索引只能在内表(class表)的id字段建，不能在外表的class_id上，因为外表是全表扫描，mysql利用不上。(当热这里class表的id字段因为是主键，已经是索引了，不用咱们创建)
**这样优化够了吗？**
引出了一个更细致的疑问：在双方两个表的字段上都建有索引时，到底是外表查内表的效率高，还是内表查外表的效率高？
**该如何进一步优化？**
把查询修改成inner join连接查询：select * from stu inner join class on stu.class_id=class.id; （但是仅此还不够，接着往下看）
**为什么不用left join 和 right join？**
这时候表之间的连接的顺序就被固定住了，比如左连接就是必须先查左表全表扫描，然后一条一条的到另外表去查询，右连接同理。仍然不是最好的选择。
**为什么使用inner join就可以？**
inner join中的两张表，如： a inner join b，但实际执行的顺序是跟写法的顺序没有半毛钱关系的，最终执行也可能会是b连接a，顺序不是固定死的。如果on条件字段有索引的情况下，同样可以使用上索引。
**那我们又怎么能知道a和b什么样的执行顺序效率更高？**
答：你不知道，我也不知道。谁知道？mysql自己知道。让mysql自己去判断（查询优化器）。具体表的连接顺序和使用索引情况，mysql查询优化器会对每种情况做出成本评估，最终选择最优的那个做为执行计划。
**在inner join的连接中,mysql会自己评估使用a表查b表的效率高还是b表查a表高，如果两个表都建有索引的情况下，mysql同样会评估使用a表条件字段上的索引效率高还是b表的。**
**而我们要做的就是：把两个表的连接条件的两个字段都各自建立上索引，然后explain 一下，查看执行计划，看mysql到底利用了哪个索引，最后再把没有使用索引的表的字段索引给去掉就行了。**
> **总结：** 子查询mysql有自己的一套优化方式，不过最好还是使用连接来替代，因为子查询需要花时间创建和销毁临时表。其中连接还需要注意使用最好的连接方式和索引。
----
# Group by 和 临时表的优化
先观察一个分组的sql语句的explain结果（在没有使用索引的情况下）：
extra结果: Using temporary; ~~Using filesort~~ 什么意思？[**(mysql 8.0 开始 group by 默认是没有排序的)**](https://blog.csdn.net/qidan3500/article/details/84563296)
表示查询使用了临时表、使用了排序。
**为什么会产生这种情况？**
**了解一下group by 执行原理：**
1. 首先mysql会把最终需要分组的结果集提取出来作为一个临时的表存放到内存空间。
~~2. 对该临时表进行排序~~ （**mysql 8.0 开始 group by 默认是没有排序的**）
3. 排序之后进行分组
alter table stu add index (class_id) 把class_id建立索引，利用索引：
extra结果:Using index 发现mysql直接走了索引覆盖（只需要在一棵索引树上就能获取SQL语句所需的所有列数据，无需回表）
【现在mysql不支持排序是不支持组间排序，但它还是需要一个临时表来完成分组，但若分组的依据是索引的话就不需要临时表了，因为索引都已经是有序的了，相当于完成分组可以直接聚合了】
仅接着对max或者min进行测试：
extra结果: ~~Using temporary; Using filesort~~ 发现~~使用了临时表、使用了排序。~~**(mysql 8.0 开始 group by 默认是没有排序的，而该例用mysql8.0测试后发现并没有使用临时表，经过了版本优化，具体如下)**
**为什么？**
因为我们最终查询的age字段并没有在索引中，Mysql无法只通过class_id这个索引字段进行分组就能求出age这个字段的统计信息。它必须还得通过class_id这个索引上地址回去取出完整的记录。
那这样的话，岂不是多次一举【**观察上图结果它没有使用索引，而8.0版本使用了，所以8.0版本就是多此一举了**】，所以它不会使用索引，还不如直接把记录都取出来，使用临时表的方式进行统计。
**怎么办？解决方案：**
建立一个复合索引，把age也利用上。（class_id,age） class_id是索引的第一列，所以class_id是有序的数据结构，能被group by 利用上。如下图：
那么此时，group by 会直接该索引进行分组，然后对索引的age列直接统计就行了。
**结果：使用上了索引，并没有产生临时表排序。**
> **总结：** group by 需要一个临时表来对数据进行分组（就是把同一类聚集到一起，例如：8811144433322等，注意没有组间排序），但如果要分组字段有索引的话，就不需要临时表了，因为该字段已有序，可直接分组。但这只是某一字段分好组了，若查询的字段不只有聚合列的话怎么办？数据库会顺着该索引的主键id回去查找相应字段。所以就会相对花费更多时间，因此，最好的方法就是创建联合索引。
>**下列操作会使用到临时表(MySQL用于存储一些中间结果集的表)：**
>1. UNION查询；
>2. 用到TEMPTABLE算法或者是UNION查询中的视图；
>3. ORDER BY和GROUP BY的子句不一样时；
>4. 表连接中，ORDER BY的列不是驱动表中的；
>5. DISTINCT查询并且加上ORDER BY时；
>6. SQL中用到SQL_SMALL_RESULT选项时；
>7. FROM中的子查询；
>8. 子查询或者semi-join时创建的表；
---
# Limit分页的优化
分页的原理：例。Select * from t limit 1000, 10; 通常情况是先取出前1010条数据，再舍弃前1000条，只保留最后10条。
由此可知，数据量越大，查询的IO的成本越大。
有人说通过id主键优化，这样查：
select * from t where id> =1000 and id< =1010
但是有个很大的缺点：必须要求id值是连续的，否则的话就肯定不对了。实际应用中，我们经常会删除某条数据，想要id值必须是连续的通常是一个理想化的情况。
最终怎么做？
我们先只查出id主键字段，再自连接查出最终的记录：
select * from (select id from t limit 1000,10) as a inner join t on a.id = t.id
而避免了全表的IO。
如果还有时间字段参与排序的话，可以把（id,time）建立一个复合索引。
---
# 多表查询中出现的临时表现象
观察下面sql结果：
**为什么会产生了临时表？**
mysql首先查了class表，然后再使用stu的class_id索引对stu表进行了join连接查询。这并没有什么问题，为什么此时产生了临时表呢？请注意我sql语句最后我利用age字段进行了排序: order by stu.age
mysql就把join后的结果作为临时表进行了排序。
在这里我们无法通过有效的索引来解决这个问题。
我们只能尽量保证让它只在内存中来进行这个过程，而不是在磁盘上。
什么叫在磁盘上操作，具体内容请往下看：磁盘临时表：
**磁盘临时表**
这个磁盘临时表跟上面讲的临时表是什么关系呢？
其实是同一个概念，无论是产生的条件还是解决的方案都是一样的。
只不过是：在mysql使用临时表的过程中，这个临时表在内存中放不下时，会自动的转换成磁盘临时表，把结果集放到磁盘上，一点一点的回读到内存中操作。这样的话，就会产生磁盘IO，那么此种的临时表效率会更加糟糕！**mysql最终采用的是内存临时表还是磁盘临时表我们无从得知，我们仍然可以采用上面讲的索引方案避免临时表的产生。**
但是，有时候情况并不是那么完美，就一定能用索引解决临时表的产生。而此时，我们应要**尽量要避免磁盘临时表的产生**，让它在内存中操作就好。
**解决方案：**
在我们的mysql中有两个参数为：
tmp_table_size (默认33.5M)
max_heap_table_size (默认16.7M)
mysql是否转化成为磁盘临时表的依据就是这两个参数，mysql会取这两个参数最小的那个作为依据，如果当前要操作的结果集超过了这个设定，就会自动转换成磁盘临时表，所以我们可以设置这两个参数，把它调大。一般初始会设置成百兆，当然根据实际情况。
怎么设置，在mysql中使用以下命令动态的改变：
set @@tmp_table_size=100*1024*1024;
set @@max_heap_table_size=100*1024*1024;
查看结果：
SELECT @@tmp_table_size;
SELECT @@max_heap_table_size;
然后不断的观察sql语句的执行时间是否有降低。
----
# 学会查看sql语句的执行的各项性能消耗
在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。
但是这个功能默认是关闭的，可以使用set profiling =1 命令开启
查看是否开启
开启之后，执行你要分析的sql语句，然后通过show profiles命令可以看到你执行过的所有sql语句的消耗时间
详细查看特定的某个语句的各项执行情况：
比如 query_id = 1的sql语句
通过 show profile for query 1 命令，可以看到该sql语句执行的每一个步骤的消耗时间
还可以查看该sql语句每执行步骤的cpu、 io、 memory 等消耗情况
比如查看cpu和io的消耗时间： show profile cpu,block io for query 1
---
# 通过慢查询日志找出需要优化的sql语句
先查看慢日志是否开启：
slow_query_log : off表示关闭的，on表示开启
Slow_query_log_file ：慢日志的存储位置
查看慢查询的时间限制，默认10秒：
这些配置的修改建议直接去配置文件修改，最终我们找到慢日志的存放位置，打开查看就行了。
----
# mysql cpu占用过高怎么解决
**1. 先看个整体的情况**
在Mysql当中，使用show [full] processlist**查看当前在mysql正在执行的sql语句**
User：发送sql语句到当前Mysql使用的是哪个用户
Host: 发送sql语句到当前mysql的主机ip和端口
Db: 连接哪个数据库
Command: 连接状态，一般是休眠空闲sleep 查询query 连接connect
Time: 连接连续时间
State: 当前sql语句执行到哪个状态
列举几个执行状态，可以看一下：
Checking table 正在检查数据表
Sending data 正在处理SELECT查询的记录，返回数据
Sorting for group 正在为GROUP BY做排序
Sorting for order 正在为ORDER BY做排序
Updating 正在搜索匹配的记录，并且修改它们
Locked 被其他查询锁住了
着重查看当前连接的执行时间和状态情况，可以多执行几次show processlist 看看有哪些sql语句还在执行当中
**2. 打开慢查询日志**
看看哪些sql语句执行时间长，尤其是那些有group by order by 等的语句，比较消耗cpu资源。针对买个sql语句，使用explain或者show profiles语句分析执行过程，逐条优化。
----
# 真实的sql优化案例
公司是做汽车服务行业SCRM门店管理系统的，其中一个功能是查出该门店的所有会员与之对应的车辆。有三张表，核心字段如下：
需求：查出某个门店下的所有会员与车辆列表（会员姓名，车辆品牌，车牌号，车标logo）
比如store_id=1的本店下的所有会员车辆列表，每次只取出20条：
- **第一次优化**
分析：需要三张表相连，其中一个会员可能有多辆车，所以车辆表必然比会员表的记录多，而会员表可通过门店store_id只筛选出本店的会员。由此可见，先查出本门店会员再与车辆表相连，再与车品牌表相连，在会员表的storeid和车辆表的mid和车品牌表brand_code上建索引，这种连接顺序，IO成本比较小，是一种不错的方案。
比如
member_base 1000条
where store_id走索引之后只真正取出100条
连接 car_base 1500条，走mid索引真正取出120条
连接 car_brand 50条 走id索引 取出1条
总IO成本为100+120+1=230
推算其他几种连接顺序方案与这种对比，IO成本都不如这种少
故使用left join 强制左表连右表：
```sql
select name,brand_name,cpai,brand_logo
from member_base as m left join car_base as c on m.id=c.mid
left join car_brand as b on c.brand_code=b.id
where m.store_id=? limit 20 ;
```
(mysql查询优化器会自动先where筛选出member_base会员表store_id本门店的会员，再join)
- **第二次优化**
有时候为了追求更好的查询效率，可在车辆表甚至会员表做字段冗余，减少join连表的。
比如在车辆表，添加需要查出的brand_name和brand_logo字段，这样我们只需要会员表与车辆表总共两张表相连即可。(sql省略)
- **第三次优化**
由于我们每次只取出limit 20条的记录，我们首次在查会员表的时候，一般该门店的会员不都只有20条记录这么少，我记得系统中很多门店的会员数量都在1千条以上，那么这个时候，再与车辆表相连的时候，会把会员表每一条记录的id都在整个车辆表查一遍，共查了1000次，这是非常不划算的。
(sql语句的执行顺序通常是先 join on 后 where 后 group by 后 统计函数 后 having 后 order by 最后才执行limit )
**解决方案：**
先在会员表取出门店的20条记录会员，再与车辆表join
```sql
select *
from ( select * from member_base where store_id=? limit 20 ) as m
left join car_base as c
on m.id=c.mid
```
最终只在车辆表查询20次即可。