MySQL索引的那些事儿

我不是女神ヾ 2022-12-30 01:25 96阅读 0赞

索引是mysql数据库中比较重要的知识点，相信很多人都有一定的了解，在这里我做一下总结，建立一下mysql索引知识体系。

## 一，索引概念和索引模型 ##

**Q1: 你能说说什么是索引吗？**

A: 索引是一种能够帮助我们快速检索数据库中数据的数据结构。

**Q2: 索引具体采用哪种数据结构呢？**

A: mysql默认的存储引擎是InnoDB；InnoDB存储引擎，默认的是B+树。

拓展：

B+Tree索引和Hash索引区别？  
哈希索引适合等值查询，但是无法进行范围查询；  
哈希索引没办法利用索引完成排序 ；  
哈希索引不支持多列联合索引的最左匹配规则；  
如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

## 二，聚簇索引，覆盖索引 ##

**Q1：刚刚我们聊到B+Tree，那你知道B+Tree的叶子节点都可以存哪些东西吗？**

A: InnoDB的B+Tree可能存储的是整行数据，也可能是主键的值。存储整行数据的是主键索引，也被称之为聚簇索引。存储主键值的是非主键索引，也被称之为非聚簇索引。

**Q2: 聚簇索引和非聚簇索引，在查询数据的时候有区别吗？**

A: 使用聚簇索引查询可直接获取所需的整行数据；而非聚簇索引是先查到叶子节点的主键值，如果所需数据从索引中就能够取得，也就是覆盖索引，则直接返回；否则用主键值再进行一次查询，获取整行数据。

## 三，联合索引，最左前缀匹配 ##

**Q1: 你们在创建索引的时候会考虑哪些因素呢？**

A: mysql索引创建的原则有：

1．选择唯一性索引  
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。  
2．为经常需要排序、分组和联合操作的字段建立索引  
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。  
3．为常作为查询条件的字段建立索引  
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。  
4．限制索引的数目  
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。  
5．尽量使用数据量少的索引  
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。  
6．尽量使用前缀来索引  
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。  
7．删除不再使用或者很少使用的索引  
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。  
   
注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

**Q2: 在创建联合索引的时候，多个字段之间的顺序你们是如何选择的？**

A: 在创建多列索引时，我们根据业务需求，where子句中使用最频繁的一列放在最左边，因为mysql索引查询会遵循最左前缀匹配的原则。当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。

**Q3: 你们创建那么多索引，到底有没有生效？**

A: 可以通过explain查看SQL语句的执行计划，通过执行计划来分析索引的使用情况。

**Q4: 那什么情况下会发生明明创建了索引，但是执行的时候并没有通过索引呢？**

A: 一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。  
在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。  
这个成本最低的方案就是所谓的执行计划。优化过程大致如下：  
根据搜索条件，找出所有可能使用的索引；  
计算全表扫描的代价；  
计算使用不同索引执行查询的代价；  
对比各种执行方案的代价，找出成本最低的那一个。