MySQL 各个索引的使用

旧城等待， 2022-12-17 15:56 110阅读 0赞

前言

> 这是我听老师讲课做的笔记,考试要看的。 [这是视频地址][Link 1]  
> 作者：陈运智  
> 关注我的[csdn博客][csdn]，更多Linux笔记知识还在更新  
> 本人只在csdn写博客

### 索引的使用 ###

*   *  一. 慢查询日志
     *   *   *  1.1 MySQL的日志类型
             *  1.2 认识慢查询日志
             *  1.3 如何开启慢查询日志命令
     *  二. 查询分析器——EXPLAIN
     *   *   *  2.1 explain简介
             *  2.2 explain的使用
     *  三. 索引的基本使用
     *   *   *  3.1 什么是索引
             *  3.2 常见的索引种类
             *  3.3 索引的使用
             *  3.4 索引的实战经验
     *  四. 复合索引前导列特性
     *  五. 覆盖索引
     *   *   *  5.1 什么是覆盖索引
             *  5.2 如何判断使用了覆盖索引

## 一. 慢查询日志 ##

#### 1.1 MySQL的日志类型 ####

> 日志用于**记录**数据库的运行情况，以及用户对数据库执行的各类操作。当数据库发生故障时，可以根据**日志分析**和**解决问题**，从而对数据库进行恢复。

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

#### 1.2 认识慢查询日志 ####

> 慢查询日志用于记录MySQL数据库中响应时间超过指定**阈值**的语句。慢查询日志通常也被称之为慢日志，因为它不仅仅只针对`SELECT`语句，像`INSERT、UPDATE、DELETE`等语句，只要响应时间超过所设定阈值都会记录在慢查询日志中。

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

#### 1.3 如何开启慢查询日志命令 ####

> 慢查询日志可以通过命令**临时设置**，也可以修改配置文件**永久设置**。

1.  **查看是否开启慢查询日志**

show variables like 'slow%';

1.  **临时开启慢查询日志**

set slow_query_log='ON';
    set long_query_time=1;

1.  **慢查询日志文件所在位置**

show variables like '%datadir%';

## 二. 查询分析器——EXPLAIN ##

#### 2.1 explain简介 ####

> explain命令可以查看SQL语句的**执行计划**。当explain与SQL语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了它将如何**处理语句**，包括有关如何**联接表**以及以何种顺序联接表的信息。

explain能做什么？

1.  分析出表的读取顺序
2.  数据读取操作的操作类型
3.  哪些索引可以使用
4.  哪些索引被实际使用
5.  表之间的引用
6.  每张表有多少行被优化器查询

#### 2.2 explain的使用 ####

> explain的使用很简单，只需要在SQL语句之前加上`explain`命令即可，除`select`语句外，explain也能分析`insert、update和delete`语句。

命令解释：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 2]

## 三. 索引的基本使用 ##

#### 3.1 什么是索引 ####

1.  索引是一种特殊的**数据结构**，类似于图书的目录，它能够极大地提升数据库的**查询效率**。如果没有索引，在查询数据时必须扫描表中的**所有记录**才能找出符合条件的记录，这种**全表扫描的查询效率非常低**。
2.  **总结**：提高查询效率，就好像垃圾分类一样，把一样效果的东西放在一起，找的时候好找。

#### 3.2 常见的索引种类 ####

> 索引是对数据库表中**一列或多列**的值进行排序的一种结构，使用索引可快速访问数据库表中的特定记录。

1.  数据库的**索引**好比一本书的**目录**，能够加快数据库的**查询速度**；
2.  索引是快速搜索的关键，如果不加索引，查找任何一条特定的数据都会进行一次**全表扫描**。

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

#### 3.3 索引的使用 ####

1.  **创建索引**

创建**普通**索引

CREATE INDEX indexName ON tableName(columnName(length));

创建**唯一**索引

CREATE UNIQUE INDEX indexName ON tableName(columnName(length));

创建**复合**索引

CREATE INDEX indexName ON tableName(columnName1, columnName2, …);

1.  **删除索引**

DROP INDEX [indexName] ON tableName;

1.  **查看索引**

SHOW INDEX FROM tableName;

#### 3.4 索引的实战经验 ####

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

## 四. 复合索引前导列特性 ##

**复合索引前导列特性**:在MySQL中，如果创建了**复合索引**`(name, salary, dept)`，就相当于创建了 **(name, salary, dept)、(name, salary)和(name)** 三个索引，因此在创建复合索引时应该将最常用作**查询条件的列**放在**最左边**，**依次递减**。

列：

**未使用索引**

select * from employee where salary=8800;
    select * from employee where dept='部门A';
    select * from employee where salary=8800 and dept='部门A';

**使用索引**：前面都带有name

select * from employee where name='liufeng';
    select * from employee where name='liufeng' and salary=8800;
    select * from employee where name='liufeng' and salary=8800 and dept='部门A';

## 五. 覆盖索引 ##

#### 5.1 什么是覆盖索引 ####

覆盖索引又称之为**索引覆盖**，即`select`的数据列只从索引中就能得到，不必读取数据行，也就是只需扫描索引就可以得到查询结果。

**关于覆盖索引的几点说明**：

1.  使用覆盖索引，只需要从索引中就能检索到**需要的数据**，而不要再**扫描数据表**；
2.  索引的体量往往要比数据表小很多，因此只读取索引速度会非常快，也会极大减少数据访问量；
3.  MySQL的查询优化器会在执行查询前判断，是否有一个索引可以覆盖所有的查询列；
4.  并非所有类型的索引都可以作为覆盖索引，覆盖索引必须要存储索引列的值。像哈希索引、空间索引、全  
    文索引等并不会真正存储索引列的值。

#### 5.2 如何判断使用了覆盖索引 ####

当一个查询使用了覆盖索引，在查询分析器`EXPLAIN`的**Extra列**可以看到`“Using index”` 。

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

> 本人博客：[https://blog.csdn.net/weixin\_46654114][csdn]  
> 本人b站求关注：[https://space.bilibili.com/391105864][https_space.bilibili.com_391105864]  
> 转载说明：跟我说明，务必注明来源，附带本人博客连接。

请给我点个赞鼓励我吧  
![在这里插入图片描述][20200507113619405.png_pic_center]

[Link 1]: https://edu.csdn.net/course/play/27328/370724
[csdn]: https://blog.csdn.net/weixin_46654114
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70]: /images/20221122/67ddca9ddd3d4fdcb1c1baa322cc7cb8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 1]: /images/20221122/2274c8a24a4d4e978f858ed0af0ab003.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 2]: /images/20221122/b10b558ce5904004b978d8a5d36511be.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 3]: /images/20221122/019f79a505fc4b03b2dbaa2bdabcd5ba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 4]: /images/20221122/867244d39eef4b54826f9de50b5061af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 5]: /images/20221122/10bc45d3e1004acb8a80b5e1e1939299.png
[https_space.bilibili.com_391105864]: https://space.bilibili.com/391105864
[20200507113619405.png_pic_center]: /images/20221122/376af940b55e46258909d204c2299735.png