【MySql】Sql优化（三）——性能优化

ゞ浴缸里的玫瑰 2022-06-04 01:29 552阅读 0赞

# 一、前言 #

当数据库数据达到一定数量的时候，结合数据库连接池Druid的可视化监控界面，对系统中运行的sql语句进行检测，对使用频繁、执行时间长的sql语句进行优化。

# 二、优化方案原则 #

*  \[原则一:选择需要优化的SQL\]
    
    > 1,选择需要优化的SQL:不是所有的SQL都需要优化,在优化的过程中,首选更需要优化的SQL;  
    > 2，SQL运行较慢有两个影响原因，IO和CPU，明确性能瓶颈所在；  
    > 3，明确优化目标；
 *  \[原则二:从Explain和Profile入手\]
    
    > 1，任何SQL的优化，都从Explain语句开始；Explain语句能够得到数据库执行该SQL选择的执行计划；  
    > 2，首先明确需要的执行计划，再使用Explain检查；  
    > 3，使用profile明确SQL的问题和优化的结果；
 *  \[原则三:永远用小结果集驱动大的结果集\]
 *  \[原则四:在索引中完成排序\]
 *  \[原则五:使用最小Columns\]
    
    > 1，减少网络传输数据量；  
    > 2，特别是需要使用column排序的时候.为什么?MYSQL排序原理，是把所有的column数据全部取出，在排序缓存区排序，再返回结果；如果column数据量大，排序区容量不够的时候，就会使用先column排序，再取数据，再返回的多次请求方式；
 *  \[原则六:使用最有效的过滤条件\]
    
    > 1，过多的WHERE条件不一定能够提高访问性能；  
    > 2，一定要让where条件使用自己预期的执行计划；
 *  \[原则七:避免复杂的JOIN和子查询\]
    
    > 1，复杂的JOIN和子查询，需要锁定过多的资源，MYSQL在大量并发情况下处理锁定性能下降较快；  
    > 2， 不要过多依赖SQL的功能，把复杂的SQL拆分为简单的SQL；  
    > 3，MySQL子查询性能较低，应尽量避免使用；

# 三、优化思路 #

## 3.1 各个系统的Druid监控界面 ##

<table> 
 <thead> 
  <tr> 
   <th align="center">系统</th> 
   <th align="center">开发</th> 
   <th align="center">测试</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">权限</td> 
   <td align="center"><a href="http://192.168.22.201:8084/authorityManagement-service/druid/datasource.html" rel="nofollow">http://192.168.22.201:8084/authorityManagement-service/druid/datasource.html</a></td> 
   <td align="center"><a href="http://192.168.22.194:8084/authorityManagement-service/druid/datasource.html" rel="nofollow">http://192.168.22.194:8084/authorityManagement-service/druid/datasource.html</a></td> 
  </tr> 
  <tr> 
   <td align="center">单表</td> 
   <td align="center"><a href="http://192.168.22.202:8083/singleTableMaintain-service/druid/datasource.html" rel="nofollow">http://192.168.22.202:8083/singleTableMaintain-service/druid/datasource.html</a></td> 
   <td align="center"><a href="http://192.168.22.195:8083/singleTableMaintain-service/druid/datasource.html" rel="nofollow">http://192.168.22.195:8083/singleTableMaintain-service/druid/datasource.html</a></td> 
  </tr> 
  <tr> 
   <td align="center">基础</td> 
   <td align="center"><a href="http://192.168.22.202:8084/basicInfo-service/druid/sql.html" rel="nofollow">http://192.168.22.202:8084/basicInfo-service/druid/sql.html</a></td> 
   <td align="center"><a href="http://192.168.22.195:8084/basicInfo-service/druid/sql.html" rel="nofollow">http://192.168.22.195:8084/basicInfo-service/druid/sql.html</a></td> 
  </tr> 
  <tr> 
   <td align="center">权限</td> 
   <td align="center"><a href="http://192.168.22.202:8082/teachingManagement-service/druid/datasource.html" rel="nofollow">http://192.168.22.202:8082/teachingManagement-service/druid/datasource.html</a></td> 
   <td align="center"><a href="http://192.168.22.195:8082/teachingManagement-service/druid/datasource.html" rel="nofollow">http://192.168.22.195:8082/teachingManagement-service/druid/datasource.html</a></td> 
  </tr> 
  <tr> 
   <td align="center">考评</td> 
   <td align="center"><a href="http://192.168.22.201:8082/examinationEvaluation-service/druid/datasource.html" rel="nofollow">http://192.168.22.201:8082/examinationEvaluation-service/druid/datasource.html</a></td> 
   <td align="center"><a href="http://192.168.22.194:8082/examinationEvaluation-service/druid/datasource.html" rel="nofollow">http://192.168.22.194:8082/examinationEvaluation-service/druid/datasource.html</a></td> 
  </tr> 
  <tr> 
   <td align="center">成绩</td> 
   <td align="center"><a href="http://192.168.22.201:8083/achievement-service/druid/sql.html" rel="nofollow">http://192.168.22.201:8083/achievement-service/druid/sql.html</a></td> 
   <td align="center"><a href="http://192.168.22.194:8083/achievement-service/druid/sql.html" rel="nofollow">http://192.168.22.194:8083/achievement-service/druid/sql.html</a></td> 
  </tr> 
 </tbody> 
</table>

举例说明：  
      以基础为例，打开测试网址

![这里写图片描述][SouthEast]

按照sql执行次数排序，选择执行时间（单位是微妙，1秒(s)=1000毫秒(ms)），挑选执行时间过长的sql进行优化。  
优化方向从两个方向优化：1.SQL语句 2.索引优化

![这里写图片描述][SouthEast 1]

## 3.2 SQL语句优化 ##

### 3.2.1 Where子句优化 ###

*  删除不必要的括号

((a AND b) AND c OR (((a AND b) AND (c AND d))))
    -> (a AND b AND c) OR (a AND b AND c AND d)

*  持续折叠：

(a<b AND b=c) AND a=5
    -> b>5 AND b=c AND a=5

*  恒定的条件去除（因为不断的折叠）

(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6

### 3.2.2 Join优化 ###

A JOIN B：通过A表的结果集作为循环基础，一条一条的通过结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果；

JOIN的优化原则:

> 1，尽可能减少Join 语句中的Nested Loop 的循环总次数，用小结果集驱动大结果集； 注意不是：小表连接大的快，而是结果集  
> 2，优先优化Nested Loop 的内层循环； 做索引  
> 3，保证Join 语句中被驱动表上Join 条件字段已经被索引；  
> 4，扩大join buffer的大小；

举例：

![这里写图片描述][SouthEast 2]

## 3.3 explain查看执行计划 ##

Explain命令可以让我们查看MYSQL执行一条SQL所选择的执行计划；

### 3.3.1 使用方式 ###

explain SQL;

举例：

![这里写图片描述][SouthEast 3]

### 3.3.2 返回字段说明 ###

1.ID：执行查询的序列号；

2.select\_type：使用的查询类型

> 1，DEPENDENT SUBQUERY：子查询中内层的第一个SELECT，依赖于外部查询的结果集；  
> 2，DEPENDENT UNION：子查询中的UNION，且为UNION 中从第二个SELECT 开始的后面所有SELECT，同样依赖于外部查询的结果集；  
> 3，PRIMARY：子查询中的最外层查询，注意并不是主键查询；  
> 4，SIMPLE：除子查询或者UNION 之外的其他查询；  
> 5，SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集；  
> 6，UNCACHEABLE SUBQUERY：结果集无法缓存的子查询；  
> 7，UNION：UNION 语句中第二个SELECT 开始的后面所有SELECT，第一个SELECT 为PRIMARY  
> 8，UNION RESULT：UNION 中的合并结果；

3.table：这次查询访问的数据表；

4.type：对表所使用的访问方式：

> 1，all：全表扫描  
> 2，const：读常量，且最多只会有一条记录匹配，由于是常量，所以实际上只需要读一次；  
> 3，eq\_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；  
> 4，fulltext：全文检索，针对full text索引列；  
> 5，index：全索引扫描；  
> 6，index\_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读取表数据；  
> 7，index\_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或者唯一索引；  
> 8，rang：索引范围扫描；  
> 9，ref：Join 语句中被驱动表索引引用查询；  
> 10，ref\_or\_null：与ref 的唯一区别就是在使用索引引用查询之外再增加一个空值的查询；  
> 11，system：系统表，表中只有一行数据；  
> 12，unique\_subquery：子查询中的返回结果字段组合是主键或者唯一约束；

5.possible\_keys：可选的索引；如果没有使用索引，为null；

6.key：最终选择的索引；

7.key\_len：被选择的索引长度；

8.ref：过滤的方式，比如const（常量），column（join），func（某个函数）；

9.rows：查询优化器通过收集到的统计信息估算出的查询条数；

10.Extra：查询中每一步实现的额外细节信息

> 1，Distinct：查找distinct 值，所以当mysql 找到了第一条匹配的结果后，将停止该值的查询而转为后面其他值的查询；  
> 2，Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用使用；  
> 3，Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果；  
> 4，No tables：Query 语句中使用FROM DUAL 或者不包含任何FROM 子句；  
> 5，Not exists：在某些左连接中MySQL Query Optimizer 所通过改变原有Query 的组成而使用的优化方法，可以部分减少数据访问次数；  
> 6，Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段的时候，MySQL Query Optimizer 会通过索引而直接一次定位到所需的数据行完成整个查询。当然，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX（）的时候；  
> 7，Using filesort：当我们的Query 中包含ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。  
> 8，Using index：所需要的数据只需要在Index 即可全部获得而不需要再到表中取数据；  
> 9，Using index for group-by：数据访问和Using index 一样，所需数据只需要读取索引即可，而当Query 中使用了GROUP BY 或者DISTINCT 子句的时候，如果分组字段也在索引中，Extra 中的信息就会是Using index for group-by；  
> 10，Using temporary：当MySQL 在某些操作中必须使用临时表的时候，在Extra 信息中就会出现Using temporary 。主要常见于GROUP BY 和ORDER BY 等操作中。  
> 11，Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现Using where 信息；  
> 12，Using where with pushed condition：这是一个仅仅在NDBCluster 存储引擎中才会出现的信息，而且还需要通过打开Condition Pushdown 优化功能才可能会被使用。控制参数为engine\_condition\_pushdown 。

## 3.4 索引优化 ##

通过使用Explain SQL查看sql的执行计划，可以看出sql中是否是按照程序员的预想计划执行的，如果不是，需要我们再次对sql进行优化，添加索引是最快，最有效果的优化。

默认情况下,一旦创建了一个表,这个表设置了主键,那么MYSQL会自动的为这个主键创建一个unique的索引。

根据sql语句的执行计划，具体分析添加单列索引还是符合索引。

### 3.4.1 举例说明 ###

表结构如下：三列，不带索引，存储100w数据。

![这里写图片描述][SouthEast 4]

![这里写图片描述][SouthEast 5]

执行sql查询：SELECT \* FROM t\_school WHERE address =’大米时代9’  
      用时0.864s

![这里写图片描述][SouthEast 6]

通过explain命令查询执行计划：  
      分析结果：采用全表扫描，没有使用索引，查询总行数为997002行

![这里写图片描述][SouthEast 7]

对address添加索引后：

![这里写图片描述][SouthEast 8]

查询：用时0.010s，速度提升了86倍。（有一定因素是，缓存影响的）

![这里写图片描述][SouthEast 9]

使用explain分析执行计划：使用了index\_address索引，查询了1999行，查询行数明显变少

![这里写图片描述][SouthEast 10]

### 3.4.2 索引的使用限制 ###

1.BLOB 和TEXT 类型的列只能创建前缀索引

2.MySQL 目前不支持函数索引(在MYSQL中,索引只能是一个列的原始值,不能把列通过计算的值作为索引);

> 实例:请查询1981年入职的员工:
> 
>     SELECT * FROM emp WHERE year(hire_date)='1981';
> 
> 问题:查询的列是在过滤之前经过了函数运算;所以,就算hire\_date作为索引,year(hire\_date)也不会使用索引;  
> 解决方案:  
> 1,SELECT \* FROM emp WHERE hire\_date BETWEEN ‘1981-01-01’ AND ‘1981-12-31’;  
> 2,在创建一列,这列的值是year(hire\_date),然后把这列的值作为索引;

3.使用不等于（!= 或者<>）的时候MySQL 无法使用索引

4.过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引

5.Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引

6.使用LIKE 操作的时候如果条件以通配符开始（ ‘%abc…’）MySQL 无法使用索引

> 1,字符串是可以用来作为索引的;  
> 2,字符串创建的索引按照字母顺序排序;  
> 3,如果使用LIKE,实例:

SELECT * FROM userinfo WHERE realName LIKE '王%';

这种情况是可以使用索引的;但是`LIKE '_王雷'` 或者`LIKE '%王雷'`都是不能使用索引的;

7.使用非等值查询的时候MySQL 无法使用Hash 索引

# 四、参考资料 #

[http://blog.csdn.net/kevinlifeng/article/details/43233227][http_blog.csdn.net_kevinlifeng_article_details_43233227]

[http://blog.csdn.net/wangjun5159/article/details/51227831][http_blog.csdn.net_wangjun5159_article_details_51227831]

[http://www.jb51.net/article/39221.htm][http_www.jb51.net_article_39221.htm]

[https://dev.mysql.com/doc/refman/5.5/en/optimization.html][https_dev.mysql.com_doc_refman_5.5_en_optimization.html]

# 五、小结 #

汇总就是对sql语句、数据库机构、硬件方面的优化，通过各种方法提高sql操作的速度，这个就是我们要整理的。

[SouthEast]: /images/20220604/97133c98af144b1ba834ac7c53e829fb.png
[SouthEast 1]: /images/20220604/2eb5dc03ecc84d8e9733ba5f237727b0.png
[SouthEast 2]: /images/20220604/d5dbfe11fa244d1ebeccf3eed4459525.png
[SouthEast 3]: /images/20220604/bf6e865393a7463a8e711fceb4b03b18.png
[SouthEast 4]: /images/20220604/1026b7bd63d3436084d6c631daebc14d.png
[SouthEast 5]: /images/20220604/7868a21c03894339afcfab36559c5532.png
[SouthEast 6]: /images/20220604/3d750d5ab09a430c85dd92e55362d94f.png
[SouthEast 7]: /images/20220604/6dd1c3ccdfbf4a45b0fbb5512f561045.png
[SouthEast 8]: /images/20220604/0419a3cd38db42a09537e38e4ce145c8.png
[SouthEast 9]: /images/20220604/5ec06a69d84647e28cbb584c955d6f0f.png
[SouthEast 10]: /images/20220604/f18a758bfa1c47ec99784ea9f1a3264b.png
[http_blog.csdn.net_kevinlifeng_article_details_43233227]: http://blog.csdn.net/kevinlifeng/article/details/43233227
[http_blog.csdn.net_wangjun5159_article_details_51227831]: http://blog.csdn.net/wangjun5159/article/details/51227831
[http_www.jb51.net_article_39221.htm]: http://www.jb51.net/article/39221.htm
[https_dev.mysql.com_doc_refman_5.5_en_optimization.html]: https://dev.mysql.com/doc/refman/5.5/en/optimization.html