mysql性能优化

总论

调优金字塔
- 1.硬件和os调优
- 2.mysql调优
- 3.架构调优
  - 读写分离
  - 垂直拆分，水平拆分
从上到下，效果越来越好，从下到上，成本越来越高

慢查询

慢查询的原因只有两点
- 1.是否请求了不需要的数据（sql写的不好，请求了不需要请求的数据）
  - 查询不需要的记录
  - 总是取出全部列
  - 重复查询相同的数据
- 2.是否在扫描额外的记录？（mysql为了处理你的结果，变成了全表扫描）
  - 响应时间
  - 扫描的行数和返回的行数
  - 扫描的行数和访问类型

重构查询

一个复杂查询还是多个简单查询？
- 如果是因为复杂导致查询很慢，还不如把复杂查询改成多个简单查询
切分查询
- 查询一口气返回10万条数据，切分成5000条每次
分解关联查询？（如果出现明显的性能瓶颈，考虑把关联查询切分）
- 让缓存的效率更高
  - 如果通过关联部门查询员工，完全可以把部门表的数据查询一次缓存在本地，根据业务需求进行填充
- 可以减少锁的竞争
- 更容易做到高性能和可扩展
- 减少冗余记录的查询
- 相当于在应用中实现了哈希关联

慢查询配置

show VARIABLES like ‘slow_query_log’;
- 开启慢查询，slow_query_log 启动停止技术慢查询日志
set GLOBAL slow_query_log=1;
- slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
show VARIABLES like ‘%long_query_time%’
- long_query_time 指定记录慢查询日志SQL执行时间得阈值（单位：秒，默认10秒）
show VARIABLES like ‘%log_queries_not_using_indexes%’
- log_queries_not_using_indexes 是否记录未使用索引的SQL
set global log_output=‘FILE,TABLE’
- log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】
- 推荐写文件，不写表

慢查询解读分析

慢查询日志

“Time: 2021-04-05T07:50:53.243703Z”：查询执行时间
“User@Host: root[root] @ localhost [] Id: 3”：用户名、用户的IP信息、线程ID号
“Query_time: 0.000495”：执行花费的时长【单位：毫秒】
“Lock_time: 0.000170”：执行获得锁的时长
“Rows_sent”：获得的结果行数
“Rows_examined”：扫描的数据行数
“SET timestamp”：这SQL执行的具体时间
最后一行：执行的SQL语句

慢查询分析工具–mysqldumpslow

mysqldumpslow -s r -t 10 slow-mysql.log
- -s order (c,t,l,r,at,al,ar)
  c:总次数
  t:总时间
  l:锁的时间
  r:获得的结果行数
  at,al,ar :指t,l,r平均数【例如：at = 总时间/总次数】
- -s 对结果进行排序，怎么排，根据后面所带的 (c,t,l,r,at,al,ar)，缺省为at
- -t NUM just show the top n queries：仅显示前n条查询
- -g PATTERN grep: only consider stmts that include this string：通过grep来筛选语句。
通过上述工具可以找出慢sql语句，同时还有其他的第三方工具可以用来查找出慢sql
- 参考1–https://blog.csdn.net/warybee/article/details/122637466
- 参考2–https://www.it610.com/article/1278384979105038336.htm

Explain执行计划

什么是执行计划

执行计划的语法

在SQL查询的前面加上EXPLAIN关键字就行。比如：EXPLAIN select * from order_exp;

table

EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

id

查询语句中每出现一个SELECT关键字，MySQL就会为它分配一个唯一的id值。这个id值就是EXPLAIN语句的第一个列

select_type

通过某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色
- SIMPLE：简单的select 查询,不使用 union 及子查询
- PRIMARY：最外层的 select 查询
- UNION：UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集
- UNION RESULT：UNION 结果集
- SUBQUERY：子查询中的第一个 select 查询,不依赖于外部查询的结果集
- DEPENDENT UNION：UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集
- DEPENDENT SUBQUERY：子查询中的第一个 select 查询,依赖于外部查询的结果集
- DERIVED：用于 from 子句里有子查询的情况。 MySQL 会递归执行这些子查询, 把结果放在临时表里。
- MATERIALIZED：物化子查询
- UNCACHEABLE SUBQUERY：结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估,出现极少。
- UNCACHEABLE UNION：UNION 中的第二个或随后的select 查询,属于不可缓存的子查询，出现极少。

partitions

和分区表有关，一般情况下我们的查询语句的执行计划的partitions列的值都是NULL

type

执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法/访问类型，其中的type列就表明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：
- system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
  出现比较多的是system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
例子中存在的索引
- 真正的表
  - u_idx_day_status (insert_time, order_status, expire_time)
  - idx_order_no (order_no)
  - idx_expire_time (expire_time)
- 派生出来为了解释的索引
  - u_idx_day_status (insert_time, order_status, expire_time)
  - idx_order_no (order_no)
  - idx_insert_time (insert_time)
system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
- innodb不会出现system这种级别，innodb对表中数据的统计不是精确统计，而是采用估计值，而myisam和memory是精确值
const：根据主键或者唯一二级索引列与常数进行等值匹配时，这种基本是常量时间，是很快的
eq_ref：在连接查询时，如果==被驱动表（join前面那张表）==是通过主键或者唯一二级索引列等值匹配的方式进行访问的〈如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较)，则对该被驱动表的访问方法就是eq_ref
- select * from s1 inner join s2 on s1.id = s2.id
- 先从s1表中查，然后再去s2表查，所以s1表是被驱动表，s2是驱动表，所以s1是eq_ref，s2是all
ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。
- 本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他属于查找和扫描的混合体
- EXPLAIN SELECT * FROM s1 WHERE order_no = ‘a’;
  - 如果加索引的字段离散性很低，重复的数据很多，比如性别，对于这种字段，mysql要找出一般的数据进行回表，效率必然很差，
- 如果普通二级索引是联合索引，遵循最左原则，假设其中有三个字段，前两个字段都是等值匹配，那么级别也是ref，但是第一个字段是等值匹配，第二个字段是大于，那么就很难是ref呢
fulltext 全文索引，只有innodb使用了全文检索的功能才会用到，跳过～
ref_or_null：有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来，就像这个查询：SELECT * FROM order_exp WHERE idx_order_no= ‘abc’ OR idx_order_no IS NULL;
- mysql把字段为null的排在最前面，非null的紧跟着，所以上述ref_or_null中的sql语句需要分成两个扫描区间去查询，然后再回表
- SELECT * FROM order_exp WHERE expire_time> ‘2021-03-22 18:35:09’ AND order_note = ‘abc’;
  - 假设expire_time和order_note 都有索引，mysql只能从两个索引中挑选一个去B+树上查找记录，具体是先通过expire_time查找到记录，然后因为select *所以在回表，到最初的表中找到全部数据，然后再在这些记录里面判断order_note等不等与’abc’
- 索引的作用？1.一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度。2.一个select查询语句在执行过程中一般最多能使用一个二级索引，即使在where条件中用了多个二级索引
index_merge：一般情况下对于某个表的查询只能使用到一个索引，在某些场景下可以使用索引合并的方式来执行查询
- 对于这一句EXPLAIN SELECT * FROM s1 WHERE order_no = ‘a’ OR insert_time = ‘2021-03-22 18:36:47’;mysql进行了优化，原始只有这两个字段单独的所有，最后explain出来，type是index_merge，，具体索引合并的内容在后面单表下mysql的执行原理会讲
- 索引合并至少包括三种，交集，并集，以及各种合并方式
unique_subquery：类似于两表连接中被驱动表的eg_ref访问方法,unique _subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配。
- EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 where s1.insert_time = s2.insert_time) OR order_no = ‘a’;其中s2表是unique_subquery，而s1是ALL
- show warnings\G，可以看到in查询被优化成exists
```
(<in_optimizer>(`mysqladv`.`s1`.`id`.<exists>)
```
index_subquery：index_subquery与unique_subquery类似，只不过访问?查询中的表时使?的是普通的索引
- 把上一个例子中的where id in改成where order_no in
range：如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法，一般就是在你的where语句中出现了between、<、>、in等的查询
- select * from s1 where order_no in (‘a’,‘b’,‘c’);
  
  select * from s1 where order_no > ‘a’ AND order_no <‘b’;
- 范围扫描可以限制范围，肯定比全表扫描要好
index：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index
- EXPLAIN SELECT insert_time FROM s1 WHERE expire_time = ‘2021-03-22 18:36:47’;
- insert_time和order_status和expire_time组成了一个唯一索引，除了这个索引，还有insert_time和order_no这两个单独的索引，这里mysql通过这个联合索引可以找到所有的相关记录，只不过要把这个二级索引从头扫到尾，但是不用回表，查询的结果insert_time也是直接在索引文件中，可以直接拿到
- 为什么有联合索引，还要从头扫到尾？
  
  mysql保存联合索引的时候，先按insert_time进行排序，如果相同，再按照order_status排序，如果insert_time和order_status都相同，则按expire_time排序，所有没有办法按照有序，只能全表扫
- 覆盖索引是结果，不是原因，mysql发现可以走覆盖索引就走了覆盖索引，不然就走全表扫描了
all：最熟悉的全表扫描，将遍历全表以找到匹配的行
- mysql要从B+树的第一个节点的第一条记录要扫描到最后一个节点的最后一个记录
- EXPLAIN SELECT * FROM s1;

possible_keys与key

possible_keys与key：possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引
- select insert_time from s1 where insert_time = “202_03-22 18:36:47”
- 有u_idx_day_status (insert_time, order_status, expire_time)和idx_insert_time (insert_time)索引，实际使用的是u_idx_day_status (insert_time, order_status, expire_time)

key_len

key_len：key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，用于观察联合索引大概使用了几个列
- int固定4字节
- bigint固定8字节
- varchar(100) 在utf-8下，是100*3 +2（可变长字段，mysql需要记录长度）= 302字节，如果列允许为null，还需要单独拿一个字节出来说明这列允许为null，所以是303字节

ref

ref：当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eg_ref、ref、ref_or_null、unique_sutbquery、index_subopery其中之一时,ref列展示的就是与索引列作等值匹配的是谁
- const
  - order_no = “a”
- mysqladv.s2.id
  - s1 inner join s2 on s1.id = s2.id
- func
  - s1 inner join s2 on s1.order_no = upper(s2.order_no)

rows

rows：如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引记录行数。
- select * from s1 where order_no >‘z’
  - 扫描1行，type是range
- select * from s1 where order_no >‘a’
  - 扫描10573行，type编程了ALL，变成了全表扫描

filtered

filtered：查询优化器预测有多少条记录满?其余的搜索条件
- EXPLAIN SELECT * FROM s1 WHERE id > 5890 AND order_note = ‘a’;
  - rows:5286 filtered:10.00%
  - 意思最后满足条件的是528条左右
- 一般在连接查询中这个指标更为关键
- EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.order_no = s2.order_no WHERE s1.order_note > ‘你好，李焕英’;
  - 先从s1表找，再去s2查
  - s1------rows:10573 filtered:33.33
    
    s2-------rows:1 filtered:100
    
    意思s2还要进行3000多次查询(上面下面相乘)

Extra

Extra：Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息很多，几十个，无法一一介绍，挑一些平时常见的或者比较重要的额外信息
- No tables used
  当查询语句的没有FROM子句时将会提示该额外信息。
- Impossible WHERE
  查询语句的WHERE子句永远为FALSE时将会提示该额外信息。
- No matching min/max row
  当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息。
- Using index
  当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra列将会提示该额外信息。
  
  EXPLAIN SELECT expire_time FROM s1 WHERE insert_time = '2021-03-22 18:36:47"
  
  存在索引u_idx_day_status (insert_time, order_status, expire_time)，即通过insert_time找expire_time，是索引覆盖
- Using index condition
  有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询：
  SELECT * FROM s1 WHERE order_no > ‘z’ AND order_no LIKE ‘%a’;
  MySQL进行了改进，在查询语句的执行过程中使用了索引条件下推（Index Condition Pushdown）这个特性，在Extra列中将会显示Using index condition
  - 理论上order_no LIKE '%a’不能使用索引，order_no > ‘z’ 能使用索引
  - mysql执行过程，是先通过order_no > ‘z’ 定位二级索引的相关数据，然后先不回表，先检查满足order_no > ‘z’ 的数据，如果不满足，实际就不会回表了，而回表需要随机io，很消耗性能
- Using where
  当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。
  - SELECT * FROM s1 WHERE order_note = ‘a’
    
    order_note没有索引，是全表扫描
  - SELECT * FROM s1 WHERE order_no = ‘a’ and order_note = ‘a’
    
    这里没有进行全表扫描
  - Using where跟是否进行全表扫描、是否使用了索引文件、是否回表都没有关系，仅仅表示在server层使用了where条件进行过滤
- Using join buffer (Block Nested Loop)
  在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度。
  - select * from s1 inner join s2 on s1.order_note = s2.order_note
- Not exists
  当我们使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not exists额外信息
  - select * from s1 left join s2 on s1.order_no = s2.order_no where s2.id is null
  - 被驱动表是执行explain后，第二行那张表
- Using intersect(…)、Using union(…)和Using sort_union(…)
  如果执行计划的Extra列出现了Using intersect(…)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的…表示需要进行索引合并的索引名称；如果出现了Using union(…)提示，说明准备使用Union索引合并的方式执行查询；出现了Using sort_union(…)提示，说明准备使用Sort-Union索引合并的方式执行查询。
- Zero limit
  当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息。
- Using filesort
  - 一些情况下对结果集中的记录进行排序是可以使用到索引的，因为b+树中天然是按照索引字段从小到大排序的，即 order by 索引字段，是可以用到索引的
    - select * from s1 order by order_no limit 10
  - 出现这个说明变慢
    - select * from s1 order by order_note limit 10
  - 无论是在内存中还是磁盘上排序，都统称为using filesort
- Using temporary
  在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的。经常会和using filesort成对出现
  - 出现这个说明变慢
  - select distinct order_no from s1
    - extra：using temporary
    - 开辟一张临时表，建立主键，插入的进去就不是重复的
  - select order_note, count(*) as amount from s1 group by order_note
    - extra：using temporary, using filesort
    - mysql对于group by的语句默认会进行排序，实际执行的效果相当于select order_note, count(*) as amount from s1 group by order_note order by order_note
    - 怎么让它不排序？select order_note, count(*) as amount from s1 group by order_note order by null
  - 怎么让它变快，group by 索引字段
- Start temporary, End temporary
  
  下面标黄的是mysql中半连接优化策略
  
  有子查询时，查询优化器会优先尝试将IN子查询转换成semi-join，而semi-join又有好多种执行策略，①当执行策略为DuplicateWeedout时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的Extra列将显示Start temporary提示，被驱动表查询执行计划的Extra列将显示End temporary提示。
- LooseScan
  在将In子查询转为semi-join时，②如果采用的是LooseScan执行策略，则在驱动表执行计划的Extra列就是显示LooseScan提示。
- FirstMatch(tbl_name)
  在将In子查询转为semi-join时，③如果采用的是FirstMatch执行策略，则在被驱动表执行计划的Extra列就是显示FirstMatch(tbl_name)提示

复习二级索引 + 回表

MMR（Disk-Sweep Multi-Range Read），多范围读取
- select * from order_exp where insert_time=‘2021-03-22 18:34:56’ and order_no > ‘你好，李焕英’
- 这里有两个索引，insert_time和order_no ，mysql会统计这两个索引扫描的记录行数，哪个扫描行数少就用哪个，还有一个限制条件，对于mysql，通过索引条件找到对应的数据，然后需要回表得到完整的数据，再判断另一个条件是否满足要求
- 正常情况下根据查询条件在二级索引中找到对应索引的列，就能得到相应的主键值，由于主键id在二级索引中是无序排序的，索引每找到一条，都要回表一次，就会产生一次随机io，就很消耗性能，所以mysql就提出了一次多读取几条二级索引，找到对应的主键id，然后排序，这些id有可能是相邻的，这样就能减少随机io的次数，这种优化方式就被称为mmr，这种使用由mysql控制，无法干预