一、索引是什么
索引是存储引擎用于快速查找表中记录的一种数据结构,这也是索引的基本功能。
可以将索引类比于书籍的目录:如果没有目录,需要查找书中的内容时,就需要从书的第一页开始顺序查找书的每一页,直到查找命中;在有目录的情况下,就可以根据目录,快速定位到要查找的内容位于书中的哪一页,极快提高查询速度。
对于存储引擎也是一样的:在没有索引的情况下,就只能逐行遍历表中数据进行查找;在有索引的情况下,就可以根据索引快速查找到相应的索引记录,然后再根据索引记录中的数据快速定位到数据库表中的行记录,完成数据的快速查找。
二、索引的优缺点
2.1、优点
- 减少需要扫描的数据量,提高查询效率
- 将随机IO变为顺序IO
- 帮助服务器避免排序和创建临时表
2.2、缺点
- 增删改操作时,需要进行维护索引的操作,降低增删改效率
- 索引需要占用存储空间
三、索引的底层数据结构
在MySQL中,索引是在存储引擎层实现的,而不是在服务器层实现的。不同存储引擎的索引的工作原理并不一定都相同。总体来说MySQL支持以下两种索引:B+树索引、Hash索引。
3.1、B+树索引
通常情况下,讨论数据库的索引时,默认都是指的B+树索引。与B树不同的是,B+树的所有数据都只会存储在叶子节点上,并且每个叶子节点都包含有指向下一个叶子节点的指针,这样可以使B+树更加高效的支持范围查找。
例:假设有如下一张表,创建的索引中包含了last_name, first_name, dob三列。
索引将会以这三列作为一个整体,下图展示了索引中数据存储的结构
索引对多个值进行排序时,首先根据last_name进行排序,last_name相同的,再根据first_name进行排序,first_name也相同时,再根据出生日志排序。也就是说,索引排序是根据创建索引时索引列的顺序来进行排序的,因此,创建索引时索引列的顺序尤其重要。
以上索引可以满足以下类型的查询需要:
1、全值匹配:查找索引中的三列与某个值完全匹配的数据,如查找姓名为Allen Cuba,出生于1960-01-01的人
2、匹配最左前缀:如查找所有last_name为Allen的人(只使用到索引的第一列)
3、匹配列前缀:如查找所有last_name以A开头的人(只使用到索引的第一列)
4、匹配范围值:如查找所有last_name在Allen和Barrymore范围内的人(只使用到索引的第一列)
5、精确匹配某一列,并对其他列进行范围查找:如查找所有last_name为Allen,first_name以K开头的人(第一列精确查找,第二列范围查找)
6、只查询索引列数据的查询:如查询last_name为Allen的所有人的last_name,first_name和dob,这些数据再索引中已经存在,不再需要访问数据库中的行
B+索引的限制:
1、如果不是按照索引的最左列开始查询,则无法使用索引:如上述索引中,无法使用索引查询first_name为Cuba的人
2、不能跳过索引中的列:无法查询last_name为Allen,dob为某一天的人。如果不指定first_name,就只能使用索引的第一列
3、如果查询中有范围查询,则其右边的列都无法使用索引优化查询:如WHERE last_name = "Allen" AND first_name LIKE "J%" AND dob = "1976-12-23"。由于LIKE是一个范围查询条件,这个查询只能使用索引的前两列。
3.2、Hash索引
Hash索引是基于Hash表实现的,只支持精确查找,不支持范围查找。
对于表中每一行的数据,存储引擎将会计算其索引列的Hash值,并将其记录在索引中,同时,索引中也会记录指向每个数据行的地址,便于根据索引查找到对应的数据。
Hash索引的限制:
- 不支持范围查找,也不支持排序
- 不支持按照部分索引列进行查找,因为Hash索引是根据所有的索引列计算Hash值的,如在列(A,B)两列上面创建Hash索引,若只查询数据列A,则无法使用索引
- 索引中只记录了Hash值和行指针,不包括行数据,所以Hash索引不能避免读取行(但是内存中计算很快,此影响可以忽略不计)
四、索引适合的场景
- 在where条件中经常使用的列上创建索引
- 在经常需要排序(order by)的列上创建索引
- 在经常需要连接(join)的列上创建索引
五、高性能的索引策略
索引对于性能非常关键,当表中数据量越大,索引对性能的影响就越明显。合适的索引能够极大提升查询效率,但是不合适的索引甚至会降低性能,因此,一定要在合适的场景创建合适的索引。
此外,索引创建之后,不当的使用的方式也无法提高查询性能,只有正确的使用索引,才能将索引的优势发挥出来。
下面介绍常见的高性能的索引策略。
1、独立的列
查询时应该将索引列单独放在where条件的一侧,而不要让索引列参与计算,否则无法利用索引。
如下,如果id列已经创建了索引,看下面的查询语句,很明显是要查询id = 4的列,但是MySQL却并不会进行这样的转换,所以就无法使用索引。
select id from table_name where id + 1 = 5;
因此,为了高效利用索引,应该将查询语句写为如下的方式:
select id from table_name where id = 5 - 1;
同样的,另外一个常见的错误是
select * from table_name where to_days(current_date) - to_days(date_clo) <= 10
应该将查询语句写为如下方式,才能有效利用索引
select * from table_name where to_days(date_clo) >= to_days(current_date) - 10
2、索引的选择性和前缀索引
索引的选择性是指,不重复的索引值的个数(也称为基数)与表中行记录的总数(T)的比值,选择性的取值范围为1/T ~ 1。对于唯一索引,不重复的索引值个数等于行记录的总数,因此它的索引选择性为1。索引的选择性越高,则索引的效率就越高,因为选择性高的索引在查找时能够过滤掉更多的行记录。
MySQL中,对于BLOB,TEXT或者长度很长的VARCHAR类型的列,必须使用前缀索引,因为MYSQL不允许索引这些列的完整长度(太耗费存储空间)。所谓的前缀索引,就是取这些列一定长度的前缀来作为索引。
创建前缀索引时,关键就在于如何决定前缀的长度。需要从两方面考虑:
- 前缀的长度不能太长,以便于解约空间。
- 前缀的长度也不能太短,要使前缀索引的选择性也要尽可能足够高。(前缀的长度越长,两行重复的概率就越低,前缀索引的选择性就越大)
前缀索引也有相应的不足:MySQL无法基于前缀索引做order by和group by操作。
3、多列索引
对索引的理解不够透彻时,容易进入一个误区:即为每个列创建单独的索引,或者按照不恰当的顺序创建了多列索引。索引顺序放在下一节单独讨论,这里主要讨论为每列创建单独索引的情况。如下表,为每一列都创建了单独的索引。
通常情况下,为每列创建单独索引并不能有效提高查询效率。假设有以下查询语句
select * from t where c1 = 1 or c2 =2;
在老版本的MySQL中,这个查询语句将无法使用索引,而不得不进行全表扫描。在5.0及以后的新版本中, MySQL引入了一种叫“索引合并”的策略,在查询时能够分别使用两个单列索引进行查询,并将查询结果进行合并。
索引合并策略是MySQL底层为了优化查询而实现的,但是,在更多时候,如果一条查询语句导致MySQL使用了索引合并策略,则说明表上的索引创建得可能并不是很恰当。
4、选择合适的索引列顺序
合适的索引列顺序与使用该索引的查询语句是强相关的,并且还需要同时考虑满足排序和分组的需求。
索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列、第三列。。。所以,索引列的顺序至关重要。对于如何选取索引列的顺序,有一个经验法则:将选择性最高的列放在索引的最前列(在不需要考虑排序和分组的情况下)。
通常情况下这一法则是有用的,但它并非在所有情况都有效,要根据查询语句来实际判断。
5、聚簇索引
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。聚簇表示数据行和相应的键值是存放在一起的。当表有聚簇索引索引时,它的数据行实际是存放在B+树的叶子节点中的。由于无法同时将数据行存放在两个地方,所以一个表只能有一个聚簇索引。(聚簇索引的作用???)
并非所有的存储引擎都支持聚簇索引,这里主要讨论InnoDB实现的聚簇索引,原理与其他存储引擎是类似的。下图显示了,聚簇索引中记录的存储方式。叶子节点中包含了行的全部数据,但非叶子节点中只包含了索引列。图中被索引的列就是主键列。
当期存储引擎不支持手动指定聚簇索引的索引列。如果没有显式定义主键列,InnoDB会选择一个唯一的非空的索引来代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引的索引列。
InnoDB将同一页(同一个叶结点)中的记录聚集在一起,但是具有相邻键值的两行记录,是有可能存放在两页中的,而这两页在磁盘中也有可能相距甚远。
聚簇索引的优点:
- 将相关数据保存在一起。例如,实现电子邮箱时,可以根据用户ID来聚集数据,这样只需要很少的磁盘IO就能将一个用户所有的电子邮件读取出来,若没有聚簇索引,有可能每一封邮件都需要进行一次磁盘IO。
- 数据访问更快。数据和索引放在一起,从聚簇索引中获取数据比从非聚簇索引中获取更快。
- 使用覆盖索引扫描的查询可以直接使用叶结点中主键值。
聚簇索引的缺点:
- 聚簇索引最大限度提高了IO密集型应用的性能,但如果数据全都放在内存中,则访问的顺序就没那么重要了,聚簇索引的优势也就没有了
- 插入速度严重依赖于插入顺序。按照主键的顺序插入时加载数据到InnoDB表中速度最快的方式。如果不是按照主键顺序插入的数据,在加载完成之后,最好使用optimize table命令重新组织一下表
- 更新聚簇索引列的代价很高,因为会强制InnoDB将每个被更新的行移动到新的位置
- 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题。当行的主键值要求必须将这一行插入到某个已满的页中时,InnoDB会将该页分裂成两个页面来容纳该行。这就是一次页分裂操作,页分裂会导致表占用更多的磁盘空间
- 聚簇索引可能会导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候
- 二级索引(非聚簇索引)可能会更大,因为二级索引的叶子节点包含了引用行的主键列
- 二级索引访问需要两次索引查找,而不是一次。因为二级索引中保存的“行指针”的实质是,二级索引叶子结点保存的不是指向行的物理位置的指针,而是行的主键值。(???)
6、覆盖索引
如果一个索引包含(或者说覆盖)所有需要查询的字段的值,就称之为覆盖索引。
既然覆盖索引中包含了所有需要查询的字段的值,则查询时就不必再去表中查询,能够极大提高查询性能。