MySQL索引详解(手把手优化查询)

朱雀 2023-03-14 10:52 230阅读 0赞

## 1索引模块 ##

### 1.1为什么使用索引？ ###

全表扫描会查询整个表所有的数据块、数据页，速度太慢，  
使用索引能避免全表扫描，加快速度

### 1.2什么信息建立索引？ ###

主键、唯一键等让数据产生区分性的都可以建立索引

### 1.3索引的数据结构 ###

B+Tree、Hash结构（Mysql不显式支持）、BitMap（Mysql不支持）

#### B-Tree ####

##### 定义 #####

1.  根结点至少包含2个节点
2.  2<=树中每个节点孩子数<=m
3.  除根结点、叶子节点，至少有ceil（m/2）个孩子
4.  所有叶子节点均在同一层
5.  假设每个非终端节点，关键字个数为n，K代表当前层关键字，P代表孩子指针  
    a. Ki(i=1…n)为关键字，K(n-1)<K(n)  
    b. ceil(m/2)-1 <= n <= m-1  
    c. P\[1\]、P\[2\]…P\[M\], P\[1\]指向关键字小于K\[1\]的子树，P\[M\]指向关键字大于K\[M-1\]的子树，其他P\[i\]指向关键字属于(K\[i-1\],K\[i\])的子树

##### 图例： #####

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

#### B+Tree ####

##### 定义 #####

1.  非叶子节点的子树与关键字个数相同
2.  非叶子节点的子树指针P\[i\],指向关键字值(K\[i\],K\[i+1\])的子树
3.  非叶子节点仅用来索引，数据在叶子上
4.  所有叶子节点均有一个链指针指向下一个叶子节点

##### 图例： #####

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

### 1.4索引衍生问题 ###

#### a.如何定位并优化慢查询SQL ####

我们拟造一个场景，模拟一个慢查询排查的过程。**注：此场景仅为一个参考，提供一个优化查询速度的思路，具体情况还需结合场景调整**

1.  根据慢日志查找慢查询  
    首先，我们执行下面查询语句

# 开启慢查询日志，设置慢查询界限为1s
    set global slow_query_log = on;
    set global long_query_time = 1;
    # 执行查看释放开启慢查询日志
    show variables like '%query%';

![开启慢查询日志][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 2]如图，我们发现慢查询日志虽然开启，但是long\_query\_time仍然为默认的10s，我们此时需要重新连接一次数据库

> set global \*\*\*的方式重启后失效，永久设置请修改my.ini（mac修改/etc/my.cnf）

提前准备一个表，插入20W条数据，执行查询  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 3]  
执行下面语句查询慢查询sql条数

show status like '%slow_queries%';

执行结果（我执行了4次）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 4]cat 查看刚才的慢日志文件  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 5]  
2. 使用Explain语句  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 6]针对查询优化，我们重点看type、extra这两列  
**type**  
出现index、all就需要优化了

> 速度优先级system>const>eq\_ref>ref>fulltext>ref\_or\_null>index\_merge>unique\_subquery>index\_subquery>range>index>all

**extra**  
出现以下两种情况，性能大大受到影响

<table> 
 <thead> 
  <tr> 
   <th>extra</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Using filesort</td> 
   <td>表示MySQL会对查询结果使用外部索引排序，不是通过表内部按索引次序读到相关内容，而是放在内存或磁盘排序，称为‘文件排序’</td> 
  </tr> 
  <tr> 
   <td>Using temporary</td> 
   <td>表示对查询结果排序使用临时表，通常出现于group by 和order by</td> 
  </tr> 
 </tbody> 
</table>

1.  修改查询语句 | 增加索引

增加索引  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 7]再次执行查询  
![在这里插入图片描述][20200519231927285.png]美滋滋，好歹是在毫秒级别了，执行分析看到走索引了  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 8]

#### b.联合索引的最左匹配原则的成因 ####

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

#### c.索引越多越好？ ####

当然不是！

1.  数据量小的表不需要建立索引，建立索引需要额外增加索引开销
2.  数据变更需要维护索引，因此更多的索引意味着更多的维持成本
3.  索引与空间成正比

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70]: /images/20230312/a79891153fda435fa8181df5ad17c005.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 1]: /images/20230312/1379851652ef49ac827bb8e0f87bb517.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 2]: /images/20230312/61358af5a13b40858a8451fe213a2f42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 3]: /images/20230312/b5221107808b4f37aa099111f5729a04.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 4]: /images/20230312/51fabdd677734968bc717bd2f366d6b1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 5]: /images/20230312/80ac7181bc204ef99cc56ccf68f1a0b9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 6]: /images/20230312/0810d20ef3aa4bba8d9da5b7b4c246a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 7]: /images/20230312/b86153779963433ea0a900791e0ae0ef.png
[20200519231927285.png]: /images/20230312/5d2d0057a4204e61b863009b352738b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 8]: /images/20230312/814d22cecbb6469f91120534f1219f6a.png
[20200519233455991.png]: /images/20230312/3100aab1f98f4717b2fc18d14a88029e.png