【MySQL面试专题一】必看索引的原理和实现

什么是索引？

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

简单来说：索引本质是一种数据结构（MySQL最常见的是 B+树），是在表的列上创建的。

常见索引使用场景：

• 1.  创建表时指定的主键。
• 2.  经常需要排序、分组、查询、联合操作等字段建立索引。
• 3.  条件查询字段建立索引。

MySQL索引如何进行创建和删除：

##注意：实际操作中尽量在业务空闲期间操作

第一种方式:创建表的时候直接创建,使用index 和 key 关键字都可以创建索引
create table table_name(
 
　　属性名 数据类型，
 
　　……
    PRIMARY KEY (`id`),
　  index|key [索引名](属性名1 [长度] [ASC|DESC]),
    unique index|key [索引名](属性名1 [长度] [ASC|DESC])
    fulltext(`StudentName`)
）
 
第二种方式:对存在的某个表的某一列进行创建索引
 
create index 索引名 on table_name (属性名 [长度] [ASC|DESC]);
create unique index 索引名 on table_name (属性名 [长度] [ASC|DESC]);
create fulltext index 索引名 on table_name (属性名 [长度] [ASC|DESC]);
 
第三种方式:通过alter table的方式创建
alter table table_name add primary key ('id')
alter table table_name add index|key 索引名(属性名 [长度]  [ASC|DESC]);
alter table table_name add unique index|key 索引名(属性名 [长度] [ASC|DESC]);
alter table table_name add FULLTEXT [index] 索引名(属性名 [长度] [ASC|DESC]);
 
删除索引
drop index 索引名 on table_name;
alter table table_name drop index (indexName)
 
查看表中的索引
 
SHOW INDEX FROM table_name

举例：

假设我们有一个名为 t_student 的数据库表，这个数据库表有：id,name,age,address，数据量有数十万行。

如果我们想要查找所有名为「zhangsan」学生的详细信息，只需要写一个简单的 SQL 语句就可以搞定，相信大家都会写。

SELECT id,name,age,address FROM t_student s
WHERE name = 'zhangsan'

如果没有索引，会发生什么？

一旦我们运行了这条 SQL 查询语句，在数据库内部是如何工作的呢？数据库会搜索 t_student  表中的每一行，从而确定学生的名字（name）是否为 ‘zhangsan’。由于我们想要得到每一个名字为 zhangsan 的学生信息，在查询到第一个符合条件的行记录后，不能停止查询，因为可能还有其他符合条件的行。所以，必须一行一行的查找直到最后一行，这就意味数据库不得不检查上万行数据才能找到所有名字为zhangsan的学生。这就是所谓的全表扫描。

数据库索引如何帮助提高性能？

使用索引的目的就是通过减少表中需要检查的记录/行的数量来加速搜索查询。说的再简单点：「索引就是用来加速查询的」。

索引的数据结构是什么样的？

常见MySQL索引一般分为：Hash索引和B+树索引，InnoDB引擎中默认的是B+树。

B+树 是最常用于索引的数据结构，时间复杂度低：查找、删除、插入操作都可以可以在 logn 时间内完成。另外一个重要原因存储在 B+树 中的数据是有序的。

B+Tree

1. 非叶子节点不存储data,只存储key,可以随数据的增进行增加（存储更多的key）
2. 叶子节点不存储指针
3. 顺序访问指针,提高区间访问的性能(范围区间查找性能更好)
4. 由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

哈希表索引是如何工作的？

如果你在创建索引时指定数据结构为「哈希表」，那这些索引也可称为「哈希索引」。

哈希索引的优点非常明显，在一定场景下，检索指定值时哈希表的效率极高。比如上面我们讨论的一个查询语句：SELECT id,name,age,address FROM t_student  WHERE name = 'zhangsan'，如果在 name 列上加一个哈希索引，检索速度有可能会成倍提升。

哈系索引的工作方式是将列的值作为索引的键值（key），键值相对应实际的值（value）是指向该表中相应行的指针。因为哈希表基本上可以看作是关联数组，一个典型的数据项就像 「zhangsan=> 0x996996」，而 0x996996 是对内存中表中包含 zhangsan这一行的引用。在哈系索引的中查询一个像 zhangsan这样的值，并得到对应行的在内存中的引用，明显要比扫描全表获得值为 zhangsan的行的方式快很多。

哈希索引的缺点

上面说了哈希索引的优点，那哈希索引的缺点也是绕不过去的。

哈希表是无顺的数据结构，对于很多类型的查询语句哈希索引都无能为力。举例来说，假如你想要找出所有小于15岁的学生。你怎么使用使用哈希索引进行查询？这不可行，因为哈希表只适合查询键值对，也就是说查询相等的查询（例：like “WHERE name = ‘zhangsan’）。哈希表的键值映射也暗示其键的存储是无序的。这就是为什么哈希索引通常不是数据库索引的默认数据结构，因为在作为索引的数据结构时，其不像B+Tree那么灵活。

总结一下缺点：

• （1）不支持范围查询

• （2）不支持索引完成排序

• （3）不支持联合索引的最左前缀匹配规则

还有什么其他类型的索引？

常见的还有：R 树和位图索引。

R 树通常用来为空间问题提供帮助。例如，一个查询要求“查询出所有距离我两公里之内的麦当劳”，如果数据库表使用R树索引，这类查询的效率将会提高。

位图索引（bitmap index）， 这类索引适合放在包含布尔值(true 和 false)的列上。

索引如何提高性能？

因为索引基本上是用来存储列值的数据结构，这使查找这些列值更加快速。如果索引使用B+树数据结构，那么其中的数据是有序的，有序的列值可以极大的提升性能。

假如我们在 name 这一列上创建一个 B+树 索引，这意味着当我们用之前的SQL查找name=‘zhangsan‘时不需要再扫描全表，而是用索引查找去查找名字为‘zhangsan’的学生，因为索引已经按照按字母顺序排序。索引已经排序意味着查询一个名字会快很多，因为名字首字母为‘z’的学生都是排列在一起的。另外重要的一点是，索引同时存储了表中相应行的指针以获取其他列的数据。

数据库索引中到底存的是什么？

你现在已经知道数据库索引是创建在表的某列上的，并且存储了这一列的所有值。但是需要理解的重点是数据库索引并不存储这个表中其他列（字段）的值。举例来说，如果我们在 name 列创建索引，那么 age 列和 address 列上的值并不会存储在这个索引当中。如果我们确实把其他所有字段也存储在个这个索引中，那这样会占用太大的空间而且会十分低效。

索引还存储指向表行的指针

如果我们在索引里找到某一条记录作为索引的列的值，如何才能找到这一条记录的其它值呢？

这很简单，数据库索引同时存储了指向表中的相应行的指针。指针是指一块内存区域， 该内存区域记录的是对硬盘上记录的相应行的数据的引用。因此，索引中除了存储列的值，还存储着一个指向在行数据的索引。也就是说，索引中的name这列的某个值（或者节点）可以描述为 (“zhangsan”, 0x996996)， 0x996996 就是包含 “zhangsan”那行数据在硬盘上的地址。如果没有这个引用，你就只能访问到一个单独的值（“zhangsan”），而这样没有意义，因为你不能获取这一行记录在学生表的其他值-例如地址（address）和年龄（age）。

数据库如何知道何时使用索引？

当你运行一条查询 SQL 语句时，数据库会检查在查询的列上是否有索引。假设 name 列上确实创建了索引，数据库会接着检查使用这个索引做查询是否合理 ，因为有些场景下，使用索引比起全表扫描会更加低效。

可以强制数据库在查询中使用索引吗？

通常来说， 你不会告诉数据库什么时候使用索引，数据库自己决定。

可以把数据库索引类比成什么？

一个非常好的类比是把数据库索引看作是书的索引。

你从头到尾逐字逐行读完就是「全表扫描」；

你翻看目录挑选感兴趣的部分阅读就是走了索引。

使用数据库索引有什么代价？

既然索引优点这么多，那给所有列加上索引不就完事了，这么想就错了，加索引是有代价的。

（1）用磁盘空间，减慢了数据更新速度，增加了磁盘IO。你的表越大，索引占用的空间越大。

（2）在更新操作有性能损失。当你在表中添加、删除或者更新行数据的时候， 在索引中也会有相同的操作。

基本原则是：如果表中某列在查询过程中使用的非常频繁，那就在该列上创建索引，具体还要看SQL的执行计划。(explain)