【心得分享】-- SQL执行分析以及通过索引SQL性能优化的方法

谁践踏了优雅 2023-02-23 05:20 7阅读 0赞

### 1.1 MySQL简介 ###

MySQL是一个关系型数据库，它是由MySQL AB公司主要研发的一款开源免费的数据库产品，现在隶属于Oracle，MySQL主要由：连接层、处理层、引擎层、文件系统层四个部分组成，这里需要注意的是MySQL的存储引擎是可拔插的，也就是说我们可以通过自己的需求选择合适的数据库存储引擎。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTk5MDE2_size_16_color_FFFFFF_t_70]

### 1.2 MySQL的存储引擎 ###

在MySQL5.5之前的版本，MySQL默认使用的是`MyISAM`存储引擎，在5.5之后，MySQL默认的存储引擎变更为了InnoDB。

**MyISAM与InnoDB之间有何区别？**

1.  InnoDB支持事务，而MyISAM不支持事务。
2.  InnoDB应对突发事件的恢复能力较好，而MyISAM在面对突发事件的时候容易丢失数据。
3.  MyISAM因为比较简单，所以效率比较好并且它更适合读操作比较多的软件系统。
4.  MyISAM的锁是表级别的，而InnoDB的数据库锁是行级的。

### 1.3 导致慢SQL的原因有哪些？如何优化SQL？ ###

导致SQL变慢的原因：

*  查询语句写的有问题
 *  索引失效
 *  关联查询过多
 *  数据库参数配置不正确

### 1.4 通过Explain实现SQL性能分析 ###

#### 1.4.1 什么是Explain执行计划？ ####

通过Explain可以模拟SQL优化器执行SQL语句，从而使我们更直观地了解到MySQL是如何执行我们的SQL语句的。

#### 1.4.2 Explain可以帮我们做什么？ ####

*  SQL语句的执行顺序
 *  数据读取操作的类型
 *  可能会使用到哪些索引
 *  实际使用了哪些索引
 *  表与表之间的引用关系
 *  可能会有多少行数据会被查询

#### 1.4.3 Explain关键字的使用 ####

这里我以一条简单的查询语句来展示Explain关键字的具体作用：

EXPLAIN SELECT * FROM emp

执行结果：  
![在这里插入图片描述][2020070517390147.png]  
Explain属性详解：

*  `id`：表示的是select语句的执行顺序，它有三种情况：1.id相同、2.id不同、id相同又不同
    
     *  id相同时，SQL语句按结果中的排序自上而下执行。
    
        EXPLAIN SELECT * FROM dept
        LEFT JOIN emp on dept.id=emp.dept_id
    
    执行结果：  
    ![在这里插入图片描述][20200705182748111.png]
    
     *  id不同时，id大的先执行
    
        EXPLAIN SELECT * FROM dept
        WHERE id = (SELECT dept_id FROM emp where id=1)
    
    执行结果：  
    ![在这里插入图片描述][20200705183833628.png]
    
     *  id相同又不用时，id大的先执行，id相同的按照自上而下的顺序执行
    
        EXPLAIN SELECT * FROM dept
        INNER JOIN emp ON dept.id=emp.dept_id
        WHERE emp.id=(SELECT emp_id FROM task WHERE id=1)
    
    执行结果：  
    ![在这里插入图片描述][20200705184156754.png]
 *  `select_type`：表示的是查询的类型，它共有六个值
    
     *  simple：表示当前查询是一次简单的查询
     *  primary：在多层嵌套查询中，表示的是最外层查询
     *  subQuery：表示这是一条子查询
     *  derived：表示的是子查询的衍生
     *  union 表示是合并操作
     *  union result：表示union的最终结果
 *  table：查询操作对应的表
 *  type：表示查询使用了那种类型，查询类型从最优到最差的排序为：system>const>eq\_ref>ref>range>index>all
 *  possible\_keys：表示的是查询可能会使用到的索引有哪些
 *  key：表示的是实际使用到的索引
 *  ref：表示索引列上的哪一个字段被使用，如果可能的话是一个常数
 *  rows：表示的是执行查询会检索到的数据条数
 *  Extra：展示一些其他列不方便展示但十分重要的信息。
    
     *  using filesort：执行文件内排序
     *  using where：在where条件中使用索引
     *  useing index：使用了覆盖索引

### 1.5 什么是索引？ ###

索引是一种为了满足特定查找算法进行快速数据检索的一种数据结构。

常见的索引有：BTree索引、Hash索引等  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTk5MDE2_size_16_color_FFFFFF_t_70 1]

#### 1.5.1 BTree索引 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTk5MDE2_size_16_color_FFFFFF_t_70 2]

### 1.6 通过索引优化SQL的一些建议 ###

1.  遵循最佳左前缀原则，即开头的字段不能够省略，中间的其他字段也不能省略。
2.  在使用like实现模糊查询的时候，不应当将`%`号放置在最左侧，应当将`%`号放在右侧，如果非得两侧都使用`%`，那么可以通过索引覆盖的方式避免全表扫描。
3.  尽量少使用或不使用`IS NULL`或`IS NOT NULL`。
4.  在SQL语句中，尽量不要出现函数表达式、计算、隐式类型转换，因为这可能造成索引失效
5.  尽可能少使用`>`、`<`等会照成范围查找的符号或关键字
6.  对于字符串类型的字段，查询时必须加上`'`或`"`，否则MySQL内部会进行一次隐式类型转换，最终导致索引失效

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTk5MDE2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200705172412465.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTk5MDE2,size_16,color_FFFFFF,t_70
[2020070517390147.png]: https://img-blog.csdnimg.cn/2020070517390147.png
[20200705182748111.png]: https://img-blog.csdnimg.cn/20200705182748111.png
[20200705183833628.png]: https://img-blog.csdnimg.cn/20200705183833628.png
[20200705184156754.png]: https://img-blog.csdnimg.cn/20200705184156754.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTk5MDE2_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200705234508808.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTk5MDE2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTk5MDE2_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200705234547841.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTk5MDE2,size_16,color_FFFFFF,t_70