MySQL 索引相关语句

2023-10-05 本文已影响0人寻心_0a46

索引

如果不使用索引，数据零散的保存在磁盘块中，查询数据需要遍历每一个磁盘块，直到找到数据为止。MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以极大提高 MySQL 的检索速度。索引的功能类似字典的目录，可以按拼音、笔画、偏旁部首等排序的目录（索引）来快速查找到需要的字（数据）。

当然索引也并非是越多越好。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引会占用磁盘空间。过多的使用索引将会造成滥用，所以通常只对常用字段创建索引（例如作为 WHERE 子句的条件的字段）。索引也会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE等操作，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。所以物极必反，什么事情都要讲究科学配比。

MySQL 中索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。不同的存储引擎实现的索引如下：

索引类型/ 存储引擎	InnoDB	MyISAM
B+树索引	✅	✅
Hash索引	❎	❎
Full-text索引	MySQL5.6.4以上 ✅	✅

B+树索引是 MySQL 中被存储引擎采用最多的索引类型。

B+树类型的索引

二叉树 -> 平衡二叉树 -> B树 -> B+树，B+树的存储结构大概经历了该系列的演化，最终成为MySQL 中被存储引擎采用最多的索引存储结构，因为B+树的结构特点可以极大的减少磁盘的 IO 次数，从而提升查询效率。

截屏2023-06-10 15.24.56.png

B+tree 结构实现数据索引具有如下优点：

1. 非叶子节点上可以存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树也就会变得更矮更胖。这样一来我们查找数据进行磁盘I/O的次数就会大大减少，数据查询的效率也会更快。
1. 所有数据记录都有序存储在叶子节点上，就会使得范围查找，排序查找，分组查找以及去重查找变得异常简单。
1. 数据页之间、数据记录之间都是通过链表链接的，有了这个结构的支持就可以方便的在数据查询后进行升序或者降序操作。

B+树类型的索引按物理存储分类分为聚集索引和非聚集索引，之后聚集索引和非聚集索引又可以按字段特性分为主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)；按索引字段个数又可分为单列索引、联合索引（也叫复合索引、组合索引）。

聚集索引
- 叶节点就是数据节点。
- 聚集索引一个表只能有一个。
- 聚集索引存储记录是物理上连续存在。
- 物理存储按照索引聚集排序，索引的键值逻辑顺序决定了表数据行的物理存储顺序。
- InnoDB引擎的表要求必须有聚集索引，默认在主键字段上建立聚集索引，在没有主键字段的情况下，表的第一个非空的唯一索引（建立在UNIQUE字段上的索引）将被建立为聚集索引，在前两者都没有的情况下，InnoDB引擎将向表自动添加一个四字节 uniqueifier 列，必要时，InnoDB引擎将向行自动添加一个 uniqueifier 值，使每个键唯一，并在此列上建立聚集索引。此列和列值供内部使用，用户不能查看或访问。

聚集索引.png

非聚集索引
- 叶节点是数据主键，可能需要回表查询。
- 非聚集索引一个表可以存在多个。
- 非聚集索引是逻辑上的连续，物理存储并不连续。
- 非聚集索引只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
- 非聚集索引不必须要求存在。

非聚集索引.png

通过非聚集索引查询数据时，如果得不到完整的数据内容，需要再次查询主键索引来获得数据内容，这样的查询过程称为回表查询。例如非聚集索引字段建立在 age 字段上，但查询的字段为 age 和 name，当根据 age 字段索引定位数据后，此时的 B+ 树的数据页中存放的仅仅是 age 关联的索引和主键索引字段，并不会存 name 字段，此时 MySQL 就会根据定位记录中的数据主键再次进行聚簇索引查找。这也导致了有时使用聚集索引查询数据比聚集索引要慢。但当合理的使用联合索引时，则可以避免回表查询的过程，提升查询效率。

对使用聚集索引或非聚集索引的选择：

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应使用	应使用
返回某范围内的数据	应使用	不应使用
一个或极少不同值	不应使用	不应使用
小数目的不同值	应使用	不应使用
大数目的不同值	不应使用	应使用
频繁更新的列	不应使用	应使用
外键列	应使用	应使用
主键列	应使用	应使用
频繁修改索引列	不应使用	应使用

唯一索引

建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突，但索引列存在多个相同的值则会发生重复冲突。

唯一索引可以比普通索引更快速地查询某条记录，如果一个字段同时存在普通索引与唯一索引，MySQL 在查询时会选用唯一索引。

MySQL 在创建表时会为添加了唯一约束的字段自动创建一个唯一索引，格式如下：

CREATE TABLE 表名 (
    字段名 字段类型 ,
    字段名 字段类型,
    CONSTRAINT UNIQUE(字段名),
    字段名 字段类型
);

截屏2023-06-14 15.35.19.png

唯一索引可以在创建表时指定，格式如下：

CREATE TABLE 表名 (
    字段名 字段类型 ,
    字段名 字段类型,
    字段名 字段类型,
    UNIQUE INDEX 索引名(创建索引的字段名)
);

唯一索引也可以在表创建后指定，格式如下：

CREATE UNIQUE INDEX 索引名 ON 表名 (创建索引的字段名);

联合索引

建立在多个列上的索引被称为联合索引，又叫复合索引、组合索引。如果多个字段构成的联合索引完全覆盖了要查询的字段，此时就形成了索引覆盖，这种情况下是不需要进行回表查询的。例如非聚集索引字段建立在 age 和 name 字段上，查询的字段也为 age 和 name。注：使用联合索引时要遵循最左前缀匹配原则，实际业务场景中创建联合索引时，应该把识别度比较高的字段放在前面，提高索引的命中率。

联合索引的建立需要进行仔细分析，允许情况下尽量考虑用单字段索引代替，创建联合索引需要考虑如下因素：

正确选择联合索引中的主列字段，一般是选择性较好的字段，如果查询没有命中主列字段索引，索引是不生效的。
联合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立联合索引；否则考虑单字段索引。
如果联合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引。
如果几个字段既有单字段索引，又有这几个字段上的联合索引，一般可以删除联合索引。

索引测试实例

联合索引的命中：使用联合索引时要遵循最左前缀匹配原则，例如在 salaries 表中有 2844047 条数据，如下字段：

截屏2023-06-13 10.22.06.png

在字段 emp_no 与字段 from_date 上创建联合索引，语法如下：

CREATE INDEX 索引名 ON 表名 (主列字段名,字段名);

CREATE INDEX emp_from_key ON salaries (emp_no,from_date);

截屏2023-06-14 11.55.10.png

当使用查询语句进行查询时，查找出685行数据，耗时0.427秒：

SELECT * FROM salaries WHERE from_date = '1998-06-02';

截屏2023-06-13 14.02.46.png

使用 EXPLAIN 指令查看查询信息，发现未命中索引，如下：

EXPLAIN SELECT * FROM salaries WHERE from_date = '1998-06-02';

截屏2023-06-13 11.01.43.png

将创建的 emp_from_key 索引删除后，重新创建索引，将emp_no 、from_date字段位置互换：

CREATE INDEX emp_from_key ON salaries (from_date,emp_no);

截屏2023-06-14 12.05.39.png

再次使用查询语句进行查询时，查找出685行数据，耗时0.0026秒：

截屏2023-06-13 14.11.07.png

使用 EXPLAIN 指令查看查询信息，命中索引，如下：

截屏2023-06-13 15.22.51.png

如果条件语句中包含多个条件，则至少有一个条件匹配最左前缀，查询语句并使用 EXPLAIN 指令查看查询信息，如下：

命中索引，查询耗时0.0038秒：

SELECT * FROM salaries WHERE salary = 43427 AND from_date = '1998-06-02';

截屏2023-06-14 12.15.21.png

截屏2023-06-14 12.16.44.png

未命中索引，查询耗时0.563秒：

SELECT * FROM salaries WHERE salary = 43427 AND emp_no = 499995;

截屏2023-06-14 12.19.09.png

截屏2023-06-14 12.20.35.png

MySQL普通索引与唯一索引的优先采用：如果一个字段同时存在普通索引与唯一索引，MySQL 在查询时会选用唯一索引。例如在 salaries 表中有 2844047 条数据，创建唯一索引与普通索引都使用相同的字段，情况如下：

/*创建普通索引*/
CREATE INDEX emp_from_key ON salaries (from_date,emp_no);
/*创建唯一索引*/
CREATE UNIQUE INDEX emp_from_key_u ON salaries (from_date,emp_no);
/*查询语句*/
SELECT * FROM salaries WHERE salary = 43427 AND from_date = '1998-06-02';
/*查看查询信息*/
EXPLAIN SELECT * FROM salaries WHERE salary = 43427 AND from_date = '1998-06-02';