深耕MySQL - 使用索引查询一定能提高查询的性能吗？

面试题：

1. 为什么索引能加快查找速度？
2. 使用索引查询一定能提高查询的性能吗？为什么？
3. 什么情况下应不建或少建索引 ？
4. 哪些情况适合建索引？
5. 哪些情况不适合建索引？

1. 为什么索引能加快查找速度？

表中存放的记录很多时，需要用到很多数据页来存储这些记录，而在这些页中查找记录可以分为两个步骤，首先定位到记录所在的页，其次从所在的页内查找相应的记录。

在没有索引的情况下，无论是根据主键列还是其他列的查找，由于我们不能快速定位到记录所在的页，所以只能从第一页沿着双向链表一直往下找，在每一页中查找指定的记录。因为要遍历所有的数据页，所以这种全表扫描的方式是超级耗时的。

当我们为表中的列建立索引后就可以不用遍历所有的数据页了，InnoDB存储引擎的索引结构为B+树，在一般情况下，我们用的B+树都不会超过4层，这样一来，在通过主键值去查找某条记录时，最多只需要进行4个数据页的查找（查找3个存储目录项记录的页和1个存储用户记录的页）。又因为每个页面内存在Page Directory（页目录），所以在页面也可以通过二分法快速定位记录。

2. 使用索引查询一定能提高查询的性能吗？为什么？

首先建立一张数据库表：

create table single_table(
	id int not null auto_increment, 
	key1 varchar(100),         
	key2 int,
	key3 varchar(100),
	key_part1 varchar(100),
	key_part2 varchar(100),
	key_part3 varchar(100),
  common_field varchar(100),
	primary key(id),          # 聚簇索引
	key idx_key1(key1),       # 二级索引
	unique key uk_key2(key2), # 二级索引，而且该索引是唯一二级索引
	key idx_key3(key3),       # 二级索引
	key idx_key_part(key_part1,key_part2,key_part3) # 二级索引，也是联合索引
)Engine=InnoDB CHARSET=utf8;

对于查询语句：

select * from single_table where key1>'a' and key2<'c';

1、使用全表扫描查询

直接扫描全部的聚簇索引记录，针对每一条聚簇索引记录，都判断搜索条件是否成立，如果成立则发送到客户端，否则跳过该记录。

2、使用索引idx_key1查询

由于idx_key1索引的叶子节点存储的不是完整的用户记录，仅包含key1,id这两个列，而查询列表是*，这意味着我们需要每条二级索引记录对应的聚簇索引记录，也就是执行回表操作，在获取到完整的用户记录后再发送到客户端。

3、回表的代价

对于使用InnoDB存储引擎的表来说，索引中的数据页都必须存放在磁盘中，等到需要时再加载到内存中使用，页面中的页号对应着该页在磁盘文件中的偏移量。

idx_key1在扫描区间('a','c)中的二级索引记录所在的页面其页号是尽可能相邻的，即使这些页面的页号不相邻，但是起码一个页面可以存放很多记录，即在执行我能一次页面IO后，就可以把很多二级索引记录从磁盘加载到内存中，总之，读取在扫描区间('a','c')中的二级索引记录，付出的代价是较小的。

不过扫描区间('a','c')中的二级索引记录对应的id值的大小是毫无规律的，我们每读取一条二级索引记录，就会根据二级索引记录的id值到聚簇索引中执行回表操作。如果对应的二级索引记录所在的页面不在内存中，就需要将该页面从磁盘加载到内存中。由于要读取很多id值并不连续聚簇索引记录，而且这些聚簇索引记录分布在不同的数据页中，这些数据页的页号毫无规律，因此会造成大量的随机IO。

需要执行回表操作的记录数越多，使用二级索引进行查询的性能也就越低，某些查询宁愿使用全表扫描也不使用二级索引。

查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据或者访问表中的少量记录来计算需要执行回表操作的记录数。如果执行回表操作的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引+回表的方式。

一般情况下，可以给查询语句指定limit子句来限定查询返回的记录数，这可能会让查询优化器倾向于选择二级索引+回表的方式进行查询，原因是回表的记录越少，性能提升就越高，比如，上面的查询语句就可以改写成下面的形式：

select * from single_table where key1>'a' and key1< 'c';

添加了limit 10子句后的查询语句更容易让查询优化器采用二级索引+回表的方式来执行。

3. 哪些情况适合建索引？

一般来说，应该在这些列上创建索引：

1、经常用于查询的字段

2、经常用于连接的字段建立索引，可以加快连接的速度

3、经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

4. 什么情况下应不建或少建索引 ？

1、where条件中用不到的字段不适合建立索引

2、表记录较少

3、需要经常增删改

4、参与列计算的列不适合建索引

5、区分度不高的字段不适合建立索引，如性别等