MySQL知识点回顾：索引（二）MySQL索引的实现

最新推荐文章于 2024-05-19 03:18:57 发布

imperfectsam

最新推荐文章于 2024-05-19 03:18:57 发布

阅读量174

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_36531037/article/details/127685289

版权

一、B+树索引

B+索引在数据库中有一个特点是高扇出性，因此在数据库当中，B+树的高度一般都在2-4层，这也就是说查找某一键值的行记录时最多只需要2-4次IO。因为当前一般的机械盘每秒至少可以做100次IO，2-4次的IO意味着查询时间只需0.02-0.04秒。
数据库中的B+树索引可以分为聚集索引和辅助索引，但是不管是聚集还是辅助的索引，其内部都是B+树的，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

二、MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：
在这里插入图片描述
这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：
在这里插入图片描述
同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

三、InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
在这里插入图片描述
图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：
在这里插入图片描述
这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

三、索引使用实战

（1）部署环境

这里的实战我们首先使用MySQL官方文档中的案例Employees表：
这里是下载的地址：https://dev.mysql.com/doc/employee/en/employees-installation.html

1、首先在mysql内部观察一下允许有读写权限的文件夹路径在哪里

show global variables like "secure_file_priv";

在这里插入图片描述
这里我们可以观察到位于/var/lib/mysql-files路径下（当你是使用docker部署mysql的时候你就需要注意，此路径为docker容器内部的路径，而非虚拟机外部的路径。）

2、这里从MySQL官方文档中的文件也要进行相对应的修改
解压从github下载的test_db-master.zip，用文本编辑器打开employees.sql，把source的路径改为对应绝对路径。（即上文中的/var/lib/mysql-files）
在这里插入图片描述

3、这里我们只需要把mysql官方文档中下载到的文件转移到此路径下即可。
在这里插入图片描述

4、然后我们进入mysql内部执行命令：
首先选择你需要导入的数据库，这里我新建了employees数据库，所以我这里直接用这个数据库。

use employees

在这里插入图片描述
然后我们执行以下命令进行导入操作

source /var/lib/mysql-files/employees.sql

在这里插入图片描述

（2）最左前缀原理与相关优化

高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关，下面通过例子说明最左前缀原理。

这里先说一下联合索引的概念。在上文中，我们都是假设索引只引用了单个的列，实际上，MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数，但是这里我不想讨论太多关系代数的话题，因为那样会显得很枯燥，所以这里就不再做严格定义。另外，单列索引可以看成联合索引元素数为1的特例。

以employees.titles表为例，下面先查看其上都有哪些索引：

show index from titles;

在这里插入图片描述
从结果中可以到titles表的主索引为<emp_no, title, from_date>

情况一：全列匹配

mysql> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

在这里插入图片描述
很明显，当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。这里有一点需要注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如我们将where中的条件顺序颠倒：

mysql> EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';

在这里插入图片描述
可以看到效果都是一样的

情况二：最左前缀匹配

mysql> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';

在这里插入图片描述
当查询条件精确匹配索引的左边连续一个或几个列时，如<emp_no>或<emp_no, title>，所以可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引，但是key_len为4，说明只用到了索引的第一列前缀。

情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供。

mysql> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';

在这里插入图片描述

此时索引使用情况和情况二相同，因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一，所以不存在扫描）。如果想让from_date也使用索引而不是where过滤，可以增加一个辅助索引<emp_no, from_date>，此时上面的查询会使用这个索引。除此之外，还可以使用一种称之为“隔离列”的优化方法，将emp_no与from_date之间的“坑”填上。

情况四：查询条件没有指定索引第一列。

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';

在这里插入图片描述
由于不是最左前缀，索引这样的查询显然用不到索引。

情况五：匹配某列的前缀字符串。

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';

在这里插入图片描述
此时可以用到索引，如果通配符%不出现在开头，则可以用到索引。

情况六：范围查询

mysql> EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no < '10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31';

在这里插入图片描述
可以看到索引对第二个范围索引无能为力。

这里特别要说明MySQL一个有意思的地方，那就是仅用explain可能无法区分范围索引和多值匹配，因为在type中这两者都显示为range。同时，用了“between”并不意味着就是范围查询，例如下面的查询：

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no BETWEEN '10001' AND '10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31';

在这里插入图片描述
实际上这里的emp_no就用了多值匹配。看起来是用了两个范围查询，但作用于emp_no上的“BETWEEN”实际上相当于“IN”，也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产生困惑。

情况七：查询条件中含有函数或表达式

很不幸，如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引（虽然某些在数学意义上可以使用）。例如：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';

在这里插入图片描述
虽然这个查询和情况五中功能相同，但是由于使用了函数left，则无法为title列应用索引，而情况五中用LIKE则可以。再如：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';

在这里插入图片描述
显然这个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个表达式，MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算，转换为无表达式的查询语句。

（3）联合索引

1、联合索引的创建

联合索引是指对表上的多个列进行索引。

联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。

例如，以下代码创建了一张t表，并且索引idx_a_b是联合索引，联合的列为（a，b）。

CREATE TABLE t(
	a INT,
	b INT,
	PRIMARY KEY (a),
	KEY idx_a_b (a,b)
)ENGINE=INNODB

那么何时需要使用联合索引呢？在讨论这个问题之前，先来看一下联合索引内部的结果。从本质上来说，联合索引也是一颗B+树，不同的是联合索引的键值数量不是1而是大于等于2。接着来讨论两个整型列组成的联合索引，假定两个键值的名称分别为a、b，如下图所示：
在这里插入图片描述
从图5-22可以观察到多个键值的B+树情况。其实和之前讨论的单个键值的B+树并没有什么不同，键值都是排序的，通过叶子节点可以逻辑上顺序地读出所有的数据，就上面的例子来说，即（1，1）、（1，2）、（2、1）、（2，4）、（3，1）、（3，2）。数据按（a，b）的顺序来进行了存放。

因此，对于查询SELECT * FROM TABLE WHERE a=xxx and b=xxx，显然是可以使用这个（a，b）这个联合索引的。对于单个的a列查询SELECT * FROM TABLE WHERE a=xxx，也可以使用这个（a，b）索引。但对于b列的查询SELECT * FROM TABLE WHERE b=xxx，则不可以使用这颗B+树索引。可以发现叶子节点上的b值为1、2、1、4、1、2，显然不是排序的，因此对于b列的查询使用不到（a，b）的索引。

2、联合索引的好处

联合索引的第二个好处是已经对第二个键值进行了排序处理。例如，在很多情况下应用程序都需要查询某个用户购物情况，并按照时间进行排序，并取出最近三次的购买记录，这时使用联合索引就可以避免多一次的排序操作，因为索引本身在叶子节点已经排序了。这里来看一下例子，首先根据以下代码来创建测试表buy_log。

CREATE TABLE buy_log(
	userid INT UNSIGNED NOT NULL,
	buy_date DATE
)ENGINE=innoDB;
INSERT INTO buy_log VALUES (1,'2009-01-01');
INSERT INTO buy_log VALUES (2,'2009-01-01');
INSERT INTO buy_log VALUES (3,'2009-01-01');
INSERT INTO buy_log VALUES (1,'2009-02-01');
INSERT INTO buy_log VALUES (3,'2009-02-01');
INSERT INTO buy_log VALUES (1,'2009-03-01');
INSERT INTO buy_log VALUES (1,'2009-04-01');
ALTER TABLE buy_log ADD KEY (userid);
ALTER TABLE buy_log ADD KEY (userid,buy_date);

以上代码建立了两个索引来进行比较。两个索引都包含了userid字段。如果只对于userid进行查询，如：

explain SELECT * FROM buy_log where userid=2;

在这里插入图片描述
从上图可以发现，possible_keys中有两个索引可以提供使用，分别是userid索引和（userid，buy_date）的联合索引。因为该索引的叶子节点包含了单个键值，所以理论上一个页中能够存放的记录就会更加多。

接下来假设要取出userid为1的最近3次的购买记录，其SQL如下：

explain select * from buy_log where userid=1 order by buy_date desc limit 3;

在这里插入图片描述
同样地这里的possbile_keys既可以选择使用userid索引，也可以使用（userid，buy_date）索引，但是这次优化器使用了（userid，buy_date）的联合索引userid_2，因为在这个联合索引中buy_date已经排序好了。根据联合索引取出数据，无须再对buy_date做一次额外的排序操作。

3、索引选择性与前缀索引

通过SHOW INDEX FROM 可以观察到表employees上有1个主键索引。
在这里插入图片描述
接着来具体阐述命令SHOW INDEX 展现结果中每列的含义

Table：索引所在的表名。
Non_unique:非唯一的索引，可以看到primary key是0，因为必须是唯一的。
Key_name：索引的名字，用户可以通过这个名字来执行DROP INDEX。
Seq_in_index:索引中该列的位置。
Column_name：索引列的名称。
Collation：列以什么方式存储在索引中。可以是A或NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL了。因为Hash根据Hash桶存放索引数据，而不是对数据进行排序。
Carddinality：非常关键的值，表示索引中唯一值的数目的估计值。Cardinality表的行数应尽可能接近1，如果非常小，那么用户需要考虑是否可以删除此索引。
Sub_part：是否是列的部分被索引。如果索引整个列，则该字段为NULL。
Packed：关键字如何被压缩。如果没有被压缩，则为NULL。
Null：是否索引的列含有NULL值。
Index_type:索引的类型。InnoDB存储引擎只支持B+树索引，所以这里显示的都是BTREE。
Comment：注释。

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，通常是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：

Index Selectivity = Cardinality / #T

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。例如，上文用到的employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：
在这里插入图片描述
title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

从下图可以看到employees表只有一个索引<emp_no>，那么如果我们想按名字搜索一个人，就只能全表扫描了：
在这里插入图片描述
如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：

<first_name>显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：
在这里插入图片描述
选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：

在这里插入图片描述
这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引建上：

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

此时再执行一遍按名字查询，比较分析一下与建索引前的结果：
首先我们需要确保一下show profiles功能是否已经开启

 show variables like "%pro%";
 set profiling = 1;

然后我们就可以进行观察了：
在这里插入图片描述
此时再执行一遍按名字查询，比较分析一下与建索引前的结果：
性能的提升是显著的，查询速度提高了120多倍。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

特别鸣谢
参考文献：
MySQL索引背后的数据结构及算法原理
MYSQL技术内幕INNODB存储引擎【机械工业出版社】