Mysql 优化调优

素颜马尾好姑娘i 2023-06-16 08:21 156阅读 0赞

**此篇作为自己调优和sql性能优化做的一些基础方案和笔记，借鉴于多篇文章和自己实用总结；**

**一、慢查询日志**  
慢查询日志是在整个库上做一定的慢执行sql抓取

（1）设置开启：SET GLOBAL slow_query_log = 1;　　　# 默认未开启，开启会影响性能，mysql重启会失效
    （2）查看日志路径和是否开启：SHOW VARIABLES LIKE '%slow_query_log%';
    （3）设置阈值：SET GLOBAL long_query_time=3;   # 执行3秒上的加入慢查询日志
    （4）查看阈值：SHOW VARIABLES LIKE 'long_query_time%';　　# 需要重连或新开一个会话
    
    查看慢查询日志记录数：SHOW GLOBAL STATUS LIKE '%Slow_queries%';
    进入路径查看日志

**二、执行计划分析**  
有了查询慢的sql后就需要进行执行计划的分析  
Explain可以模拟优化器执行SQL查询语句,语法：Explain + SQL  
![一条根据主键索引进行查询的sql][sql]  
**id：反映的是表的读取的顺序，或查询中执行select子句的顺序。**

1.  id相同，执行顺序从上至下
2.  id不同，执行顺序由大到小

**select\_type：反映的是（子）查询类型**

1.  simple：简单的select查询，查询中不包含子查询或union。
2.  primary： 复杂查询最外层
3.  subquery: 除了from之外的子查询
4.  derived： from中包含的子查询，也叫做派生表，会将查询结果放入临时表
5.  union： union连接的两个select查询 这是标记第二个select ，如果union在from里，第一个union会被标记为derived，最外层为primary
6.  union result：不需要参与查询 union结果集

**table：反映这一行数据是关于哪张表的**

**type：连接类型**  
查询效率： system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range > index >all

1.  system: 表中只有一行数据或者是空表,只能用于myisam和memory表。 如果是Innodb引擎表,type列在这个情况通常都是all或者index，这是const类型的特例，一般不会出现
2.  const：查询条件用到了常量，通过索引一次就找到，常在使用primary key或unique索引中出现。
3.  eq\_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。
4.  ref：非唯一性索引扫描，返回匹配某个单独值的所有行
5.  range: 这个连接类型使用索引返回一个范围中的行，比如使用>,<,between,in查找东西时发生的情况。
6.  index: index类型只遍历索引树,通常比all快
7.  all: 全表扫描

**possible\_keys：可能用到的索引**

**key： 实际用到的索引**

**key\_len: 索引长度**

**ref：反映哪些列或常量被用于查找索引列上的值**

**rows: MySQL所认为的它在找到正确的结果之前必须扫描的记录数**

**filtered:这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例**

**Extra: 连接查询的一些信息**:

1.  using filesort：mysql中无法利用索引完成排序，这时会mysql需要进行额外的步骤来发现如何对返回的行排序,创建索引时就会对数据先进行排序，出现using filesort一般是因为order by后的条件导致索引失效，最好进行优化,order by的排序最好和所建索引的顺序和个数一致
2.  using temporary: mysql在对查询结果排序时使用临时表,这通常发生在对不同的列集进行ORDER BY上
3.  using index: 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率好
4.  using where: 使用了where
5.  Not exists: 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

**三、避免索引失效**  
列举了一些索引失效和避免索引失效的注意点：

1.  查询从索引的最左前列开始并且不跳过索引中的列，中间跳过的值，后面的索引会失效
2.  索引列上做了计算、函数，会导致索引失效而转向全表扫描
3.  索引会对范围生效，但是范围条件右边会失效  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx_size_16_color_FFFFFF_t_70]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx_size_16_color_FFFFFF_t_70 1]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx_size_16_color_FFFFFF_t_70 2]
4.  减少select \*，尽量使用覆盖索引
5.  mysql在使用不等于（!= 或 <>）时无法使用索引，is null，is not null也无法使用索引
6.  like避免使用%%查询，全模糊。不会走索引。而半模糊 “123%”后面带%会走索引，通过覆盖索引可以解决like '%字符串%'索引失效的问题
7.  字符串不加单引号索引失效
8.  排序必须根据索引顺序![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx_size_16_color_FFFFFF_t_70 3]
9.  对于常量类型，查询优化器会自动调优SQL，顺序不影响
10. 左连接把索引建在从表的关联字段比较好，主表一定会有，从表才是检索的关键，join语句中被驱动表上join条件字段加索引可以提高效率
11. count(\*) 加上条件会变慢。  
    ![在这里插入图片描述][2019121017493352.png]

[sql]: https://img-blog.csdnimg.cn/20191126103300633.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191126140707492.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191126140725580.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191126140748781.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191126141445111.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMTIzODIx,size_16,color_FFFFFF,t_70
[2019121017493352.png]: https://img-blog.csdnimg.cn/2019121017493352.png