最全面的mysql索引知识大盘点

待我称王封你为后i 2022-05-11 09:03 216阅读 0赞

在我们日常的工作中，索引可谓无处不在，如果不懂索引，就会给人一种盲人摸象的印象。下面主要以mysql为基准来系统的介绍。

**目录**

1. 为何需要索引，索引是什么？

2. 有哪些分类?

2.1. B+数索引

2.2.hash索引

2.3. 全文索引

2.4 B+树索引和哈希索引的区别

3. 适合及不适合创建的索引？

4. 最左匹配原则

6. 如何分析及优化SQL语句的性能问题

7. 常见优化

8. in 和exists 优化专题

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

## 1. 为何需要索引，索引是什么？ ##

索引是对数据库表中**一列或多列的值进行排序**的一种结构。一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系，为了**方便查找**书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要**占据物理空间**的。

## 2. 有哪些分类? ##

### **2.1. B+数索引** ###

*  **主键索引**：数据列不允许重复，不允许为NULL.一个表只能有一个主键。
 *  **唯一索引**：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引，如：*ALTER TABLE table\_name ADD UNIQUE (column);*
 *  **唯一组合索引**：顾名思义多列唯一，如：*ALTER TABLE table\_name ADD UNIQUE (column1,column2);*
 *  **普通索引**：基本的索引类型，没有唯一性的限制，允许为NULL值。如：*ALTER TABLE table\_name ADD INDEX index\_name (column);*
 *  **前缀索引**：超过20 个长度的字符串，请考虑创建前缀索引，因为较小的索引涉及的磁盘  I/O  较少，更为重要的是，对  
    于较短的键值，索引高速缓存中的块能容纳更多的键值。前缀索引的长度，一般略大于本列的**平均长度**，  
    例如 colname的平均长度为 23，那么前缀取稍大一点（如24）就可以，或者根据选择性和实际访问量。前  
    缀索引兼顾索引大小和查询速度，但是其缺点是**不能用于** ORDER BY 和GROUP BY操作，也不能用于[覆盖索引][Link 1]。如：*alter table tabname add key(colname(24));*
 *  **组合索引**：*ALTER TABLE table\_name ADD INDEX index\_name(column1, column2, column3);*

注：如果你对**B+**不了解，在**[另一章中专门介绍][Link 2]**

### **2.2.hash索引** ###

InnoDB在内部利用哈希索引来实现其[自适应哈希索引][Link 3]功能，**我们干预不了**。**哈希索引就是采用一定的哈希算法**，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，而节点逐级查找带来的问题就是多次的IO访问，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

通过SHOW VARIABLES LIKE '%ap%hash\_index';查看是否支持，具体情况看[官方支持][Link 4]，MySQL5.6中的体系结构更改使其比早期版本更适合禁用自适应哈希索引功能。

适用场景：对于一些比较长的的字段，比如存储 url 的字段，这时可以使用 crc32做哈希，再在这个 hash值上建索引，性能会大幅提升。如当字段url=http://www.baidu.com时，这时可以取crc32(“http://www.baidu.com”)=110365867(建索引而非url上直接建)。

### **2.3. 全文索引** ###

全文索引是搜索引擎使用的一种关键技术。如：*ALTER TABLE table\_name ADD FULLTEXT (column);*

### **2.4 ****B+树索引和哈希索引的区别** ###

*  **如果是等值查询，那么哈希索引明显有绝对优势**，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据。
 *  **如果是范围查询检索，这时候哈希索引就毫无用武之地了**，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索。
 *  哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）。
 *  **哈希索引也不支持多列联合索引的最左匹配规则**
 *  B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，**在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题**。

## 3. 适合及不适合创建的索引？ ##

适合的场景：

*  where后面使用频繁的搜索列，但不一定是所有where 后面的列都要建索引；
 *  虽然数据库上没有维护主外关系，但如果该字段相当于某张表的外键，一般要在该字段加索引；
 *  两表join时，所匹配 on和where 的字段应建立合适的索引；
 *  group by, order by 后面字段，要考虑建索引，但不一定是全部建索引；
 *  索引要建在选择性高，重复记录少的字段上（当索引列有大量数据重复时，SQL优化可能不会去利用索引）；
 *  索引尽量建在小的字段上，如果碰到大的字段(超过20字符)，考虑使用前缀索引。
 *  尽量避免NULL：应该指定列为NOT NULL，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。

不适合的场景：

*  唯一性太差的字段不适合建立索引，如标识性字段 is\_delete，或者表示状态类型等一类的字段；
 *  只出现在select中，不会出现在 where 或者order by 条件中的字段不要建索引；
 *  一般不要出现，同一字段既是单字段索引，又出现在复合索引中；
 *  同一张表，不要建过多复合索引；
 *  '%xxx'或者'%xxx%'一类的模糊查询，不要创建普通索引，可以考虑建全文索引，但'xxx%'可以用到索引；
 *  不要过度索引，索引会影响 DML(insert、delete、update)的性能，对于 DML 操作频繁的表，一般一张表
 *  不要超过6个索引，具体还需视表的大小和业务情况适当调整；
 *  尽量避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序。

## 4. **最左匹配原则** ##

更多参阅另一篇[认识mysql组合索引的最左匹配原则][mysql]

## 6. 如何分析及优化SQL语句的性能问题 ##

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划。

![70][]

ID:

*  id相同执行顺序由上至下。
 *  id不同，id值越大优先级越高，越先被执行。
 *  id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中

select\_type:

<table> 
 <tbody> 
 <tr> 
 <td>SIMPLE:&nbsp;&nbsp; &nbsp;不包含任何子查询或union等查询</td> 
 </tr> 
 <tr> 
 <td>PRIMARY: 包含子查询最外层查询就显示为 PRIMARY</td> 
 </tr> 
 <tr> 
 <td>SUBQUERY:&nbsp;&nbsp; &nbsp;在select或 where字句中包含的查询</td> 
 </tr> 
 <tr> 
 <td>DERIVED: from字句中包含的查询</td> 
 </tr> 
 <tr> 
 <td>UNION:&nbsp;&nbsp; &nbsp;出现在union后的查询语句中</td> 
 </tr> 
 <tr> 
 <td>UNION RESULT:&nbsp;&nbsp; &nbsp;从UNION中获取结果集</td> 
 </tr> 
 </tbody> 
</table>

table:  查询的数据表

partitions: 执行计划表分区、表创建的时候可以指定通过那个列进行表分区

![70 1][]

type:

CREATE TABLE `employee` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `no` VARCHAR(10) NOT NULL,
      `name` VARCHAR(20) NOT NULL,
      `position` VARCHAR(20) NOT NULL,
      `age` VARCHAR(2) NOT NULL,
      PRIMARY KEY (`id`)
    )ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:171px;"> ALL&nbsp; &nbsp; </td> 
 <td style="vertical-align:top;width:649px;">这便是所谓的“全表扫描”,非常的耗时而且低效。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:171px;"> index </td> 
 <td style="vertical-align:top;width:649px;"> 另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据）但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。 
 <blockquote> 
 mysql&gt; explain select * from employee order by no;//全表 +----+-------------+----------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table &nbsp; &nbsp;| type | possible_keys | key &nbsp;| key_len | ref &nbsp;| rows | Extra &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| +----+-------------+----------+------+---------------+------+---------+------+------+----------------+ | &nbsp;1 | SIMPLE &nbsp; &nbsp; &nbsp;| employee | ALL &nbsp;| NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| NULL | NULL &nbsp; &nbsp;| NULL | &nbsp; &nbsp;5 | Using filesort | +----+-------------+----------+------+---------------+------+---------+------+------+----------------+ mysql&gt; explain select * from employee order by id ;//按索引列扫描 +----+-------------+----------+-------+---------------+---------+---------+------+------+-------+ | id | select_type | table &nbsp; &nbsp;| type &nbsp;| possible_keys | key &nbsp; &nbsp; | key_len | ref &nbsp;| rows | Extra | +----+-------------+----------+-------+---------------+---------+---------+------+------+-------+ | &nbsp;1 | SIMPLE &nbsp; &nbsp; &nbsp;| employee | index | NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| PRIMARY | 4 &nbsp; &nbsp; &nbsp; | NULL | &nbsp; &nbsp;5 | NULL &nbsp;| mysql&gt; explain select id from employee ; //extra列中的值为‘Using index’，那么称这种情况为 索引覆盖； +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table &nbsp; &nbsp;| type &nbsp;| possible_keys | key &nbsp; &nbsp; | key_len | ref &nbsp;| rows | Extra &nbsp; &nbsp; &nbsp; | +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ | &nbsp;1 | SIMPLE &nbsp; &nbsp; &nbsp;| employee | index | NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| PRIMARY | 4 &nbsp; &nbsp; &nbsp; | NULL | &nbsp; &nbsp;5 | Using index | 
 </blockquote> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:171px;"> range&nbsp; </td> 
 <td style="vertical-align:top;width:649px;">有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及'&gt;','&lt;'外，in和or也是索引范围扫描。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:171px;"> ref </td> 
 <td style="vertical-align:top;width:649px;"> &nbsp;查找条件列使用非唯一性索引或者唯一索引的前缀扫描。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。 
 <blockquote> 
 mysql&gt; alter table employee add key I_EMPLOYEE_NAME(`name`); mysql&gt; EXPLAIN SELECT * FROM employee WHERE NAME= '张三'; +----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+ | id | select_type | table &nbsp; &nbsp;| type | possible_keys &nbsp;| key &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| key_len | ref &nbsp; | rows | Extra &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | +----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+ | &nbsp;1 | SIMPLE &nbsp; &nbsp; &nbsp;| employee | ref &nbsp;| I_EMPLOYEE_NAM | I_EMPLOYEE_NAM | 62 &nbsp; &nbsp; &nbsp;| const | &nbsp; &nbsp;1 |&nbsp; | 
 </blockquote> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:171px;"> eq_ref </td> 
 <td style="vertical-align:top;width:649px;">ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:171px;"> ref_or_null </td> 
 <td style="vertical-align:top;width:649px;">类似REF，只是搜索条件包括：连接字段的值可以为NULL的情况，比如 where col = 2 or col is null</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:171px;"> fulltext </td> 
 <td style="vertical-align:top;width:649px;">使用全文索引</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:171px;">index_subquery</td> 
 <td style="vertical-align:top;width:649px;">在子查询中使用基于除唯一索引之外的索引进行扫描</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:171px;"> unique_subquery </td> 
 <td style="vertical-align:top;width:649px;">在子查询中基于唯一索引进行扫描( eq_ref)</td> 
 </tr> 
 </tbody> 
</table>

**possible\_keys**: 可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。  
**key**: 实际使用的索引，若没有使用索引，显示为NULL  
key\_length: 索引长度  
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值  
rows: 返回估算的结果集数目，并不是一个准确的值。  
**extra**: 见覆盖索引

## 7. 常见优化 ##

理论上sql优化，应尽量避免全表扫描、文件排序等，尤其是要警惕以下用法（会导致索引失效）：

* where子句中使用**!=**或**<>**操作符
 * where 进行 **null** 值判断（where num is null），它的问题已有补充[mysql中Null 与空值的区别][mysql_Null]
 * where 子句中使用 **or** 来连接条件，可以使用union all方案代替
 * where 中**in** 和 **not in** 也要慎用，能用 between 就不要用 in
 * where **like** 不能前置百分号
 * where 子句中**使用参数**（where num=@num）
 * where 子句中对字段进行**表达式操作**（where num/2=100）
 * where子句中对字段进行**函数操作**（where substring(name,1,3)=’abc’）
 * **order by** 涉及的**列无索引**

当然了，上面都是属于比较初中级，基于Mysql对查询SQL的自动优化来提高性能的。但mysql还提供了更高级的东西，打破这些，主要有：

我们将两个或多个表连接起来进行查询时，我们并不用关心MySQL先连哪个表，后连哪个表。而这一切都是由MySQL内部通过一系列的计算、评估，最后得出的一个连接顺序决定的。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就需要人为地干预连接的顺序，直接使用STRAIGHT\_JOIN关键字。通常是用小表驱动大表

***限制使用索引的范围***  
        有时我们在数据表里建立了很多索引，当MySQL对索引进行选择时，我们希望MySQL只考虑几个索引，而不是全部的索引，就需要用到USE INDEX对查询语句进行设置。  
        ***限制不使用索引的范围***  
      如果我们要考虑的索引很多，而不被使用的索引又很少时，可以使用IGNORE INDEX进行反向选取（选择不被考虑的索引）。  
          ***强迫使用某一个索引***  
         有时我们希望MySQL必须要使用某一个索引(由于 MySQL在查询时只能使用一个索引，因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。

当我们查询的结果集中的数据比较多时，可以通过SQL\_BUFFER\_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了)，并且可以长时间地为客户端提供大记录集。  
         SELECT SQL\_BUFFER\_RESULT \* FROM TABLE1 WHERE …  
         和SQL\_BUFFER\_RESULT.选项类似的还有SQL\_BIG\_RESULT，这个选项一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。  
        SELECT SQL\_BUFFER\_RESULT FIELD1, COUNT(\*) FROM TABLE1 GROUP BY FIELD1

1.  STRAIGHT\_JOIN:强制连接顺序
2.  干预索引使用
3.  使用临时表

也可以通过查询缓冲提高查询速度，它已超出我们本章节谈论的范围，暂时不深入研究了。

## 8. in 和exists 优化专题 ##

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

*  如果查询的两个表大小相当，那么用in和exists差别不大。
 *  如果两个表中一个较小，一个是大表，则**子查询表大的用exists**，**子查询表小的用in**。
 *  not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，**没有用到索引**；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

最后，不总结了，上面的基本上都是用法的，后面会再补充一遍原理《[mysql索引底层原理分析][mysql 1]》![84944323][]

[Link 1]: https://blog.csdn.net/alex_xfboy/article/details/82789942
[Link 2]: https://blog.csdn.net/alex_xfboy/article/details/82818753#3.2%20B%2BTree
[Link 3]: https://dev.mysql.com/doc/refman/5.6/en/innodb-adaptive-hash.html
[Link 4]: https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html
[mysql]: https://blog.csdn.net/alex_xfboy/article/details/90695401
[70]: /images/20220511/24f8a14216754c14af45aac2193a1961.png
[70 1]: /images/20220511/8fa8e23de9af45b29ea5b0afb2a03c9f.png
[mysql_Null]: https://blog.csdn.net/alex_xfboy/article/details/82805378
[mysql 1]: https://blog.csdn.net/alex_xfboy/article/details/82818753
[84944323]: /images/20220511/f2415021e9cc470a81ccf2be1ef7dfee.png