【MySQL】之 SQL 调优

Bertha 。 2022-12-07 12:04 204阅读 0赞

# 目录 #

*  一、SQL 性能问题
 *  二、SQL 执行顺序
 *  三、性能分析
    
     *  1、MySQL常见瓶颈
     *  2、索引介绍
     *  3、索引的优缺点
     *  4、MySQL索引分类
     *  5、创建索引的场景
 *  四、Explain 命令
    
     *  1、Explain 是什么
     *  2、Explain 的使用
 *  五、索引优化
    
     *  1、避免索引失效
     *  2、Show Profile
     *  3、索引优化建议
 *  六、查询优化建议

# 一、SQL 性能问题 #

--------------------

SQL 语句执行时间长，效率低的原因可能有如下几条：

*  查询语句写的烂
 *  索引失效（单值索引和复合索引的情况都有）
 *  关联查询太多 join
 *  服务器调优及各个参数设置（缓冲、线程等）

# 二、SQL 执行顺序 #

--------------------

由于 MySQL 会对 SQL 语句进行优化的原因，我们写的 SQL 语句和数据库引擎执行时的语句可能有所不能。比如我们写的 SQL 语句为：

select distinct
    	<select_list>
    from
    	<left_table> <join_type> join <right_table> on <join_condition>
    where
    	<where_condition>
    group by
    	<group_by_list>
    having
    	<having_condition>
    order by
    	<order_by_condition>
    limit <limit_number>;

而 MySQL 翻译后的语句可能为：

from <left_table> 
    on <join_condition>
    <join_type> join <right_table> 
    where <where_condition>
    group by <group_by_list>
    having <having_condition>
    select distinct <select_list>
    order by <order_by_condition>
    limit <limit_number>;

SQL 语句的执行顺序可以归结为如下图：

![在这里插入图片描述][20200915164636920.png]

# 三、性能分析 #

--------------------

### 1、MySQL常见瓶颈 ###

*  CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
 *  IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
 *  服务器硬件的性能瓶颈：top、free、iostat 和 vmstat 来查看系统的性能状态

### 2、索引介绍 ###

MySQL官方对索引的定义为：**索引（Index）是帮助MySQL高效获取数据的数据结构**。

可以得到索引的本质：**索引是数据结构**。

索引的目的在于提高查询效率，就好像是字典一样，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要 a - z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

我们可以简单理解为**排好序的快速查找结构**：

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

![在这里插入图片描述][20200915164714255.png]

左边是数据表，一共有两列七条记录，Col1 是数据记录的物理地址，Col2 是记录的数据，为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上.

我们平常所说的索引，如果没有特别指明，都是指**B+树结构组织的索引**。其中**聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引**默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

### 3、索引的优缺点 ###

**优点**

*  类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
 *  通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

**缺点**

*  实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
 *  虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
 *  索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

### 4、MySQL索引分类 ###

*  **单值索引**：即一个索引只包含单个列，一个表可以有多个单列索引
 *  **唯一索引**：索引列的值必须唯一，但允许有空值
 *  **复合索引**：即一个索包含多个列

**索引相关语句**

*  创建：
    
        CREATE  [UNIQUE ] INDEX indexName ON mytable(columnname(length)); 
        ALTER mytable ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length));
 *  删除：
    
        DROP INDEX [indexName] ON mytable;
 *  查看：
    
        SHOW INDEX FROM table_name\G;
 *  使用 ALTER 命令：
    
        -- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
        ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
        
        -- 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
        ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
        
        -- 添加普通索引，索引值可出现多次
        ALTER TABLE tbl_name ADD INDEX index_name (column_list);
        
        -- 该语句指定了索引为 FULLTEXT ，用于全文索引
        ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

### 5、MySQL索引结构 ###

MySQL 的索引结构包含有：

*  BTree索引
 *  Hash索引
 *  full-text全文索引
 *  R-Tree索引

**BTree索引的检索原理**

BTree索引的检索原理如下图所示：

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

**【初始化介绍】**

一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3。

P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

*真实的数据存在于叶子节点*即3、5、9、10、13、15、28、29、36、60、75、79、90、99，非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

**【查找过程】**

1.  如果要查找数据项为29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO；
2.  在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO；
3.  29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

### 5、创建索引的场景 ###

**需要创建索引的情况**

*  主键自动建立唯一索引
 *  频繁作为查询条件的字段应该创建索引
 *  查询中与其它表关联的字段，外键关系建立索引
 *  查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
 *  查询中统计或者分组字段
 *  单值/组合索引的选择问题：在高并发下倾向创建组合索引

**不需要创建索引的情况**

*  表记录太少
 *  经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
 *  频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引，加重了IO负担
 *  Where条件里用不到的字段不创建索引
 *  数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引（如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果）。

# 四、Explain 命令 #

--------------------

### 1、Explain 是什么 ###

Explain 即执行计划，使用 Explain 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。

使用 Explain 我们可以知道：

*  表的读取顺序
 *  数据读取操作的操作类型
 *  哪些索引可以使用
 *  哪些索引被实际使用
 *  表之间的引用
 *  每张表有多少行被优化器查询

### 2、Explain 的使用 ###

Explain 的使用方式为：Explain + SQL语句，比如：

下面我们解释一下 Explain 所包含各个字段的含义：

**1）id**

select 查询的**序列号**，包含一组数字，表示查询中执行 select 子句或操作表的顺序。id 值包含一下三种情况：

*  id相同，执行顺序由上至下
 *  id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
 *  id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

**2）select\_type**

**查询的类型**，主要是用于区别普通查询、联合查询、子查询等的复杂查询：

*  SIMPLE：简单的 SELECT 查询，查询中不包含子查询或者 UNION
 *  PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为 PRIMARY
 *  SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查询
 *  DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED (衍生)，MySQL会递归执行这些子查询, 把结果放在临时表里。
 *  UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
 *  UNION RESULT：从UNION表获取结果的SELECT

**3）table**

显示这一行的数据是关于哪张表的

**4）type**

type 显示的是**访问类型**，是较为重要的一个指标，包含以下值：

*  system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
 *  const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。比如将主键置于where列表中，MySQL就能将该查询转换为一个常量
 *  eq\_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
 *  ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
 *  range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
 *  index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
 *  all：Full Table Scan，将遍历全表以找到匹配的行

结果值从最好到最坏依次是：

system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range > index > all

一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

**5）possible\_keys**

显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

**6）key**

实际使用的索引。如果为NULL，则没有使用索引，**查询中若使用了覆盖索引，则该索引仅出现在key列表中**。

**7）key\_len**

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。

key\_len显示的值为索引字段的最大可能长度，并非实际使用长度，即 key\_len 是根据表定义计算而得，不是通过表内检索出的。

**8）ref**

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

**9）rows**

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

**10）extra**

包含不适合在其他列中显示但十分重要的额外信息：

*  Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
 *  Using temporary ：使了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
 *  USING index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
 *  Using where：表明使用了where过滤
 *  using join buffer：使用了连接缓存
 *  impossible where：where子句的值总是false，不能用来获取任何元组
 *  select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(\*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
 *  distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

重点理解**覆盖索引**：

覆盖索引（Covering Index），也可以称为索引覆盖，有一下两种理解方式：

*  理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说**查询列要被所建的索引覆盖**。
 *  理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

注意：

如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select \*，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

# 五、索引优化 #

--------------------

### 1、避免索引失效 ###

*  尽量全值匹配，对于字符串来说，全值匹配时命中索引几率是最高的
 *  遵循**最佳左前缀法则**：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且**不跳过索引中的列**。
 *  不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
 *  避免出现存储引擎不能使用索引中范围条件右边的列
 *  尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少使用 select \*
 *  mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
 *  is null，is not null 也无法使用索引
 *  like 如果以通配符开头(’%abc…’)，会导致mysql索引失效，而变成全表扫描的操作
 *  字符串不加单引号索引失效
 *  少用or，用它来连接时会索引失效

### 2、Show Profile ###

Show Profile 是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量，默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

**使用步骤**：

*  看看当前的mysql版本是否支持：
    
        mysql> show variables like 'profiling';
        +---------------+-------+
        | Variable_name | Value |
        +---------------+-------+
        | profiling     | OFF   |
        +---------------+-------+
        1 row in set, 1 warning (0.00 sec)
 *  开启功能，默认是关闭，使用前需要开启：
    
        mysql> set profiling=on;
        Query OK, 0 rows affected, 1 warning (0.00 sec)
        
        mysql> show variables like 'profiling';
        +---------------+-------+
        | Variable_name | Value |
        +---------------+-------+
        | profiling     | ON    |
        +---------------+-------+
        1 row in set, 1 warning (0.00 sec)
 *  执行 SQL 语句
 *  查看结果：
    
        mysql> show profiles;
        +----------+------------+---------------------------------+
        | Query_ID | Duration   | Query                           |
        +----------+------------+---------------------------------+
        |        1 | 0.00210675 | show variables like 'profiling' |
        |        2 | 0.00018900 | show tables                     |
        |        3 | 0.00018825 | SELECT DATABASE()               |
        |        4 | 0.00077650 | show tables                     |
        |        5 | 0.00229300 | select * from acl_user          |
        +----------+------------+---------------------------------+
        5 rows in set, 1 warning (0.00 sec)
 *  诊断 SQL：show profile cpu, block io for query 上一步的Query\_ID;
    
        mysql> show profile cpu, block io for query 5;
        +----------------------+----------+----------+------------+--------------+---------------+
        | Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
        +----------------------+----------+----------+------------+--------------+---------------+
        | starting             | 0.000056 | 0.000000 |   0.000000 |         NULL |          NULL |
        | checking permissions | 0.000006 | 0.000000 |   0.000000 |         NULL |          NULL |
        | Opening tables       | 0.001924 | 0.000000 |   0.000000 |         NULL |          NULL |
        | init                 | 0.000018 | 0.000000 |   0.000000 |         NULL |          NULL |
        | System lock          | 0.000009 | 0.000000 |   0.000000 |         NULL |          NULL |
        | optimizing           | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
        | statistics           | 0.000012 | 0.000000 |   0.000000 |         NULL |          NULL |
        | preparing            | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
        | executing            | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
        | Sending data         | 0.000155 | 0.000000 |   0.000000 |         NULL |          NULL |
        | end                  | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
        | query end            | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
        | closing tables       | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
        | freeing items        | 0.000066 | 0.000000 |   0.000000 |         NULL |          NULL |
        | cleaning up          | 0.000014 | 0.000000 |   0.000000 |         NULL |          NULL |
        +----------------------+----------+----------+------------+--------------+---------------+
        15 rows in set, 1 warning (0.02 sec)

**日常开发需要注意的结论**：

*  converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
 *  create tmp table 创建临时表，这个要注意
 *  Copying to tmp table on disk 把内存临时表复制到磁盘
 *  locked

### 3、索引优化建议 ###

*  对于单键索引，尽量选择针对当前query过滤性更好的索引
 *  在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
 *  在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
 *  尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
 *  少用 Hint 强制索引

# 六、查询优化建议 #

--------------------

#### 1、永远小表驱动大表，即小的数据集驱动大的数据集 ####

#### 2、order by关键字优化 ####

*  ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。MySQL支持二种方式的排序，FileSort和Index，Index效率高，因为它指示MySQL扫描索引本身完成排序，而FileSort方式效率较低。
 *  ORDER BY 满足两情况，会使用Index方式排序：
    
     *  1、ORDER BY 语句使用索引最左前列；
     *  2、使用Where子句与Order BY子句条件列组合满足索引最左前列。
 *  尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
 *  如果不在索引列上，filesort 有两种算法：双路排序和单路排序
    
     *  双路排序：MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
     *  单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。
 *  优化策略：
    
     *  增大max\_length\_for\_sort\_data参数的设置：不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
     *  增大sort\_buffer\_size参数的设置：提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort\_buffer\_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率

#### 3、GROUP BY关键字优化 ####

*  group by 实质是先排序后进行分组，遵照索引建的最佳左前缀
 *  当无法使用索引列，增大 max\_length\_for\_sort\_data 参数的设置 + 增大 sort\_buffer\_size 参数的设置
 *  where高于having，能写在where限定的条件就不要去having限定了。

[20200915164636920.png]: /images/20221123/88bf200584af40c3b6ee170b4f94fef9.png
[20200915164714255.png]: /images/20221123/6d58197854004ab990f40ced5981b768.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Fpd2FuZ3Rpbmd5dW4_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/00773a548ee843b598c783c4721ba806.png