美团面试官：说说MySQL的索引

从本文开始，选取牛客网上大厂的面试题，整理出相关内容的知识点。

什么是索引

小学时我们经常用到的字典里有音节索引和部首目录，当我们查字典时，常常用音节索引和部首目录帮助我们提高查找汉字的速度。MySQL中同样也有索引，当MySQL需要查找数据时，索引也会提高检索数据的速度。

索引的优缺点

创建索引的一个最重要的原因是索引能够快速检索数据，极大减少了数据检索量。创建唯一索引能够保证数据表中数据的唯一性。当我们需要进行表连接操作时，索引加速表连接操作。进行分组或排序查询时，也能够加速查询。

以上都是索引的优点，绝大部分优点都是帮助MySQL变得更快。那索引有什么缺点呢？首先索引作为数据库的一部分，本身就需要占用一定的物理空间。另外，当我们需要对数据表进行频繁插入、删除操作时，MySQL也需要动态维护索引。

有哪些常见的索引

主键索引：一张表只有一个主键索引，不允许重复，不允许为null。
唯一索引：一张表可以有多个唯一索引，不允许重复，允许为null。
普通索引：基本的索引类型，允许数据重复，允许为null。
前缀索引：前缀索引用于字符串类型，取文本的前几个字符创建索引。
全文索引：一般用于大文本数据检索，是当前搜索引擎中使用的关键技术。

以上索引中，除了主键索引外，其他四种索引统称为二级索引。

对于InnoDB引擎，一定存在主键索引。大家可能会奇怪，明明我在创建表的时候没有设置主键为什么我也能够创建成功？主要原因是InnoDB对于没有手动创建主键的表会选择一个唯一非空列作为主键，如果仍然不存在就设置一个隐藏的列作为主键。

索引的数据结构

MySQL中最多的两种索引是哈希索引和B树索引。哈希索引底层使用哈希表，在绝大部分情况下，查询单条记录使用哈希索引性能最快。B树索引是InnoDB存储引擎默认的索引实现方式，但实际底层使用的是B+树（MySQL打印表索引显示BTREE而不是B+TREE），在大部分场景下建议使用B树索引。

哈希索引

哈希索引的实现主要通过将数据库中的字段数据转换成为定长的hash值并与指向数据的指针一并放入hash表。如果发生hash碰撞，则在对应的hash键上使用拉链法进行存储。下图模拟了哈希索引的基本思路。

B树索引

上图是一棵B+树，每一个结点是一个磁盘块，结点中的深蓝色部分表示数据项、黄色部分表示指针。磁盘块1上有17和35两个数据项，还有P1、P2和P3三个指针。P1指向比17小的磁盘块，P2指向比17大比35小的磁盘块，P3指向比35大的磁盘块。磁盘块5~磁盘块11表示叶子节点。当我们要查找数据项10时，首先将磁盘块1加载到内存中，使用二分查找确定10比17小，接着加载指针P1指向的磁盘块2到内存中，同样使用二分查找找到磁盘2的P2指针指向的磁盘块6，将其加载到内存中，同时用二分查找找到数据项10。本次查询一共进行了三次IO操作，如果没有索引，每个数据项进行一次IO，那么将极大增加数据检索的成本。

最左前缀原则

最左前缀原则是指当建立了联合索引如(a,b,c)时，可以根据a/(a AND b)/(a AND b AND c)三种条件使索引进行检索，一般需要将最频繁使用的列放到最左边。当使用=或in时可以乱序a、b、c三个条件，MySQL会自动优化索引可识别的形式。
另外，当遇到范围查询（LIKE、BETWEEN、>、<）则会停止匹配。

SELECT * FROM user WHERE a=1 AND b=1 AND c>1 AND d=1

上面的SQL语句建立了(a,b,c,d)的索引，只能匹配到(a AND b)，遇到d>1直接停止使用索引匹配，不过如果建立(a,b,d,c)的索引就可以匹配到d。

聚集索引和非聚集索引

首先解释一下聚集索引又被称为聚簇索引，是指将数据和索引放到一起的索引，当找到索引也就找到了数据。在InnoDB引擎中，B+树的非叶子结点存放的都是索引，而叶子结点存放的是索引和数据。非聚集索引又被称为非聚簇索引，是将数据和索引分开存储。InnoDB的主键索引是聚集索引，MyISAM的主键索引和二级索引都是非聚集索引。InnoDB的非主键索引的叶子结点上存放着行的主键值，当找到索引数据时可能需要根据主键值回表，也就是说当查到主键后会根据主键值回到表中查询。

聚集索引的优缺点

聚集索引查询往往非常快，因为当定位到索引时，也就直接定位到了数据。但是聚集索引非常依赖有序数据，当插入或查找类似于UUID这种复杂的字符串时，往往速度很慢。还有就是聚集索引的更新代价很大，一般来说如果更新索引列数据，那么索引结构也要修改，所以主键是不建议被修改的。

非聚集索引的优缺点

非聚集索引的更新代价较小，因为叶子节点不存放数据。但非聚集索引也非常依赖有序的数据，另外非聚集索引可能需要回表。

覆盖索引

当索引中包含要查找的字段的值，那么我们称其为覆盖索引。我们用一个例子解释一下什么是覆盖索引。

SELECT age FROM user WHERE age<40

我们创建了age的索引，当我们检索到索引的时候，待查询的数据也已经存在，此时我们就不需要回表。当我们在写SQL时，要尽量只查询必要的字段，增加覆盖索引的概率。

创建索引注意事项

• 被频繁检索的字段可以考虑创建索引。
• 频繁修改的字段不适合创建索引。
• 被索引的字段不适合为null。
• where子句中的列可以考虑创建索引。
• 对于经常进行表连接和排序的字段可以创建索引。
• 避免创建冗余索引，例如(a,b)和(a)就是冗余索引，能够命中后者的索引也可以命中前者的索引。
• 一般来说没有太大区分度的列（例如性别只有男和女）就不要使用索引了。
• 尽量扩展索引而不是创建索引，例如表中已有a的索引，要加上(a,b)的索引，可以考虑扩展原来a的索引。