likes
comments
collection
share

MySQL 索引 相关语句

作者站长头像
站长
· 阅读数 44

索引

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

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

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

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

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

B+树 类型的索引

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

MySQL 索引 相关语句

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

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

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

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

MySQL 索引 相关语句

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

MySQL 索引 相关语句

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

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

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

唯一索引

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

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

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

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

MySQL 索引 相关语句

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

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

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

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

联合索引

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

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

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

索引测试实例

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

MySQL 索引 相关语句

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

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

CREATE INDEX emp_from_key ON salaries (emp_no,from_date);

MySQL 索引 相关语句

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

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

MySQL 索引 相关语句

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

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

MySQL 索引 相关语句

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

CREATE INDEX emp_from_key ON salaries (from_date,emp_no);

MySQL 索引 相关语句

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

MySQL 索引 相关语句

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

MySQL 索引 相关语句

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

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

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

MySQL 索引 相关语句

MySQL 索引 相关语句

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

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

MySQL 索引 相关语句

MySQL 索引 相关语句

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';

MySQL 索引 相关语句

MySQL最终采用的索引：

MySQL 索引 相关语句

该查询使用普通索引进行五次查询耗时：

MySQL 索引 相关语句

该查询使用唯一索引进行五次查询耗时：

MySQL 索引 相关语句

注：测试数据来自MySQL提供，导入数据库即可使用，下载地址：

github地址：github.com/datacharmer…

gitcode地址：gitcode.net/mirrors/dat…

转载自:https://juejin.cn/post/7244470270924406839
评论
请登录