MySQL面试必备（二）索引篇

1. 索引是什么？

索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B 树及 B+ 树实现。更通俗的说，索引相当于目录。而且索引是一个文件，它是要占据物理空间的。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个 key 的全部值的信息了。

2. 索引的优缺点

优点：

· 大大加快数据的检索速度。
· 可以在查询的过程中使用优化隐藏器，提高系统的性能。

缺点：
· 时间方面：创建索引和维护索引要耗费时间，具体的，当对表中的数据进行增加、删 除和修改的时候，索引页需要动态维护，会降低增删改的效
· 空间方面：索引需要占用物理空间。

3. MySQL有几种索引类型？

1. 从存储结构上，BTree（B树或B+树），Hash索引，full-index全文索引，R-Tree索引。索引存储时保存的形式。

2. 从应用层次：普通索引，唯一索引，复合索引。
   普通索引：即一个索引只包含单个列，一个表可以有多个单列，一个表可以有多个单列索引。
   唯一索引：索引列的值必须唯一，但允许有空值。
   复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。

3. 根据表中数据的物理顺序与键值的逻辑（索引）顺序关系：聚簇索引、非聚簇索引。
   聚簇索引（聚集索引）：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保留了B树索引和数据行。
   非聚簇索引：不是聚簇索引，就是非聚簇索引。

4. 索引的底层实现

1. Hash索引：
   基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

2. B树索引：
   能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

3. B+树索引
   是MySQL采取的数据结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B树，进行范围查找时只需查找两个节点，进行遍历即可。而B树需要获取所有节点，相比之下B+树效率更高。

   B+树性质：
   · n个树的节点包含 n 个关键字，不用来保存数据而是保存数据的索引。
   · 所有的叶子节点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子节点本身依关键字的大小自小而大顺序连接。
   · 所有的非终端点可以看作索引部分，结点中仅含其子树中的最大（最小）关键字。
   · B+树中，数据对象的插入和删除仅在叶子节点上进行。
   · B+树有两个头指针，一个是树的根节点，一个是最小关键码的叶子节点。

5. 为什么索引的默认数据结构为B+树？

B+树

1. B+树的磁盘读写代价耕地：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对于B树更小，如果把所有同一内部节点的关键字存放在同一块盘中，那么盘块所能容纳的关键字数量页越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
2. 由于B+树的数据都存储于叶子节点中，分支节点均为索引，方便扫库，只需要扫一遍叶子节点即可，但是B树因为其分支节点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更适合在区间查询。

Hash

1. 虽然可以快读定位，但没有顺序，IO复杂度高
2. 基于hash表实现，只有Memory存储引擎显示支持哈希索引。
3. 适合等值查询：如=，in()，<=>，不支持范围查询
4. 不是按照索引值顺序存储的，就不能像B+树索引一样利用索引完成排序。
5. hash索引在查询等值时非常快
6. hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找。
7. 如果有大量重复键值，哈希索引的效率很低，因为存在哈希碰撞问题

二叉树：高度不均匀，效率与树的高度有关
红黑树：树的高度随数据量增加而增加，IO代价高。

6. 聚簇索引和非聚簇索引

在InnoDB中，索引B+树的叶子节点存储了整行数据的是主键索引，也被称为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。
而索引B+树的叶子节点存储了主键的值是非主键索引，也被称为非聚簇索引。

聚簇索引和非聚簇索引的区别：

1. 非聚簇索引和聚簇索引的区别在于非聚簇索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）
2. 对于InnoDB来说，想要查找数据还需要根据主键再去聚簇索引中进行查找，这个再根据聚簇索引查找数据的过程，称为回表。第一次索引一般顺序是IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描。
3. 通常情况下，主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇）需要回表查多次。如果是覆盖索引，只需要查一次。
4. 注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引都是非聚簇索引。自己建的索引基本都是非聚簇索引

7. 非聚簇索引一定会回表查询吗？

不一定，涉及到查询语句所要求的字段是否全部命中了索引，如果命中，就不必再进行回表查询。一个索引包含（覆盖）了所有需要查询字段的值，称为“覆盖索引”。
举例：如果在员工表的年龄上建立了索引，那么当进行 select age from employee where age > 50 的查询时，在索引的叶子节点上已经包含了age，故不会回表查询。

8. 联合索引是什么，为什么要注意联合索引中的顺序？

MySQL可以使用多个字段同时建立一个索引，即联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。
MySQLS使用索引时需要索引有序，假设建立了“name”，“age”，“school”的联合索引，那么索引的排序为：name >= age >= school。
当进行查询时，此时索引仅仅按照 name 严格有序，因此必须首先使用 name 字段做等值查询，之后对于匹配到的行按照 age 严格有序，此时可以使用 age 字段用作索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序。一般情况下，将查询需求频繁或者字段选择性较高的列放在前面。此外可以根据特例的查询或者表结构进行单独调整。

9. 前缀索引是什么？

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。
使用场景：前缀的区分度比较高的情况下。
建立前缀索引的方式：

create table table_name(
	id int auto_increment primary key,
	id_card varchar(11),
	key name (first_name, last_name),
	information text,
	fulltext key (information),
	unique key (id_card)
	)

ALTER TABLE table_name ADD index(index_name(column_name))

ALTER TABLE table_name ADD KEY(column_name(prefix_length))

prefix_length参数很难确定，参数就是前缀长度的意思。通常可以使用以下方法进行确定：
先计算全列的区分度

select count(distinct column_name) / count(*) from table_name

然后计算前缀长度为多少时和全列的区分度最相似

select count(distinct left(column_name, prefix_length)) / count(*) from table_name

不断调整prefix_length的值，直到和全列计算出区分度相近。

10. MySQL的最左前缀匹配

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where 子句使用中最频繁的一列放在最左边。MySQL会一直向右匹配直到遇到范围查询（> < between like ）就会停止匹配。
举例：如对一个数据表建立 (a, b, c, d)的顺序索引。

where a = 1 and b = 1 and c = 1 and d = 1

建立了索引

where a = 1 and b = 1 and c > 1 and d = 1

遇到了范围查询 c > 1就停止匹配，就用不到索引 d。

where a = 1 and c = 1 and d = 1

只有a用到索引，因为跳过了索引b，不是从最左开始匹配的。

where b = 1 and c = 1 and d = 1

没有用到索引，因为跳过了索引a，不是最左匹配的。

where a like 'ab%'
where a like '%ab'
where a like '%ab%'

对于列为字符串的情况，只有前缀匹配可以使用索引，中缀匹配和后缀匹配只能进行全表扫描。

11. 索引在什么情况下会失效

1. 条件中有or，例如 where a=1 or b=2
2. 在索引上进行计算会导致索引失效，例如where a + 1 =2
3. 在索引的类型上进行数据类型的隐形转换，会导致索引失效，例如字符串一定要加引号，假设 where a = '1'会用到索引，而写成where a = 1则索引失效
4. 索引用 !、<=>等判断时会失效。
5. 用is null 、is not null会失效

12. 使用索引查询一定能提高查询性能吗？

索引需要空间来储存，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的 insert，update，delete将为此多付出4，5次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。索引范围查询（INDEX RANGE SCAN）适用于两种情况：

• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
• 基于唯一性索引的检索。

13.创建索引要注意什么？

• 非空字段：指定列为 NOT NULL。
• 选择区分度高的列：字段差异值越大，字段离散程度越高。
• 索引字段越小越好：以页为单位，一页储存的数据越多，一次I/O操作获取的数据越大效率越高。

持续更新中。。。