索引无效回表索引失效in

转载

mob6454cc7a6087 2024-06-18 21:45:07

文章标签 索引无效回表 mysql 索引 B-Tree B+Tree 文章分类 数据仓库大数据

1.简介

1.1.什么是索引

索引是对记录集的多个字段进行排序的方法。
在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针。
数据库的索引，可以理解为字典的目录，能够帮助我们快速找到需要查询的字。

1.2.为什么需要索引

使用索引的目的就是为了提高查询效率。

假定表中存在5,000,000条记录，共需要1,000,000个磁盘块，当前查找字段为身份证号码，为无序的。

如果不使用索引，因为其无序性，则进行线性查找，即：全表扫描，共计扫描1,000,000个磁盘块。
如果使用索引，因为索引的有序性，可使用二分查找，即：共计扫描log2 (1000000) = 19.93 = 20个数据块。

实际上，索引使用的B+Tree，其查询算法比二分查找更加高效。

2.优缺点

2.1.优点

加快速度：检索、分组、排序等等。

2.2.缺点

以空间换时间，索引本身也是一种数据结构，故而会占用额外的存储空间，使用者应考虑磁盘空间是否足够。
创建索引时，会对整个数据表加锁，使用者应该考虑加锁对业务造成的影响。
当对数据表本身进行增删改时，也需要相应的对索引树进行操作，故而会降低数据表本身的增删改效率。

3.分类

3.1.主键索引(PRIMARY KEY)

主索引。
根据表主键建立的索引。
不允许为空，不允许重复。

3.2.唯一索引(UNIQUE INDEX)

为了确保某列在表中的值唯一而建立的索引。
允许为空，不允许重复。

3.3.普通索引(INDEX)

普普通通。
允许为空，允许重复。

3.4.组合索引(INDEX)

多个列组合而成的索引。
遵循最左前缀原则，依照字段的检索频率的高低排列字段。
组合索引有多重组合方式。例如有索引ci_index(col1,col2,col3)，则可用索引列组合为：col1,col2,col3，col1,col2，col1。
组合索引会导致索引键值边长，可以选择性的只对字段的前几个字符生成索引，如：ci_index(col1(5),col2,col3(5))。
允许为空，允许重复。

3.5.全文索引(FULLTEXT INDEX)

为了将列用于全文检索而在此列创建的索引。
通常用于text类型。
不同于模糊查询，全文检索有自己的语法，后续会说明。
允许为空，允许重复。

3.语法

3.1.建表时创建索引

下面的创建语句包含了的五种索引：

主键索引：PRIMARY KEY (id)
唯一索引：UNIQUE INDEX ui_isbn (ISBN)
普通索引：INDEX i_author(author), INDEX i_name(name), INDEX i_publisher(publisher), INDEX i_page(page)
组合索引：INDEX ci_author_name(name,author)
全文索引：FULLTEXT INDEX fti_summary(summary)

CREATE TABLE `book` (
	`id` bigint NOT NULL AUTO_INCREMENT COMMENT '图书ID',
	`name` varchar(50) NOT NULL COMMENT '书名',
	`author` varchar(20) NOT NULL COMMENT '作者',
	`publisher` varchar(50) NOT NULL COMMENT '出版社',
	`ISBN` varchar(16) NOT NULL COMMENT 'ISBN编码',
	`summary` text NULL COMMENT '简介',
	`page` int NOT NULL DEFAULT '1' COMMENT '页数',
	PRIMARY KEY (`id`),
	UNIQUE INDEX ui_isbn (`ISBN`),
	INDEX i_author(`author`),
	INDEX i_name(`name`),
	INDEX i_publisher(`publisher`),
	INDEX i_page(`page`),
	INDEX ci_author_name(`name`,`author`),
	FULLTEXT INDEX fti_summary(`summary`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARSET = utf8mb4 COMMENT '图书表';

3.2.建表后追加索引

CREATE TABLE `book` (
	`id` bigint NOT NULL COMMENT '图书ID',
	`name` varchar(50) NOT NULL COMMENT '书名',
	`author` varchar(20) NOT NULL COMMENT '作者',
	`publisher` varchar(50) NOT NULL COMMENT '出版社',
	`ISBN` varchar(16) NOT NULL COMMENT 'ISBN编码',
	`summary` text NULL COMMENT '简介',
	`page` int NOT NULL DEFAULT '1' COMMENT '页数'
) ENGINE = InnoDB  CHARSET = utf8mb4 COMMENT '图书表';

-- 主键索引
alter table `book` add PRIMARY KEY (`id`);
-- 唯一索引
alter table `book` add UNIQUE INDEX ui_isbn (`ISBN`);
-- 普通索引
alter table `book` add (
	INDEX i_author(`author`),
	INDEX i_name(`name`),
	INDEX i_publisher(`publisher`),
	INDEX i_page(`page`)
);
-- 组合索引
alter table `book` add INDEX ci_author_name(`name`,`author`);
-- 全文索引
alter table `book` add FULLTEXT INDEX fti_summary(`summary`);

3.3.查看索引

mysql> show index from `book`;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | PRIMARY        |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          0 | ui_isbn        |            1 | ISBN        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_author       |            1 | author      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_name         |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_publisher    |            1 | publisher   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_page         |            1 | page        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | ci_author_name |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | ci_author_name |            2 | author      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | fti_summary    |            1 | summary     | NULL      |           0 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 rows in set (0.00 sec)

3.5.查看索引是否被使用

explain {Sql Statement}

3.4.使用索引

此处只例举几种类型，关于索引的使用注意事项，后面的章节会细说。

-- 普通查询 key = ui_isbn
mysql> explain select name,author from book where ISBN = 'ISNB00001';
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | book  | const | ui_isbn       | ui_isbn | 66      | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)

-- 模糊查询 key = i_name
mysql> explain select name,author from book where name like '张%' limit 1;
+----+-------------+-------+-------+-----------------------+--------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys         | key    | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+-----------------------+--------+---------+------+------+-----------------------+
|  1 | SIMPLE      | book  | range | i_name,ci_author_name | i_name | 202     | NULL |    1 | Using index condition |
+----+-------------+-------+-------+-----------------------+--------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

-- 全文检索 key = fti_summary
mysql> explain select name,author from book where match(summary) AGAINST('new book') limit 1;
+----+-------------+-------+----------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type     | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+-------+----------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | fulltext | fti_summary   | fti_summary | 0       | NULL |    1 | Using where |
+----+-------------+-------+----------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)

3.6.删除索引

mysql> drop index ci_author_name on `book`;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from book;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | PRIMARY     |            1 | id          | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          0 | ui_isbn     |            1 | ISBN        | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_author    |            1 | author      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_name      |            1 | name        | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_publisher |            1 | publisher   | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_page      |            1 | page        | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | fti_summary |            1 | summary     | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)

或者

mysql> alter table `book` drop index ui_isbn;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from book;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | PRIMARY     |            1 | id          | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_author    |            1 | author      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_name      |            1 | name        | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_publisher |            1 | publisher   | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_page      |            1 | page        | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | fti_summary |            1 | summary     | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.01 sec)

4.原理

B+树索引是B+树在数据库中的一种实现，是数据库中使用最为频繁的一种索引。

B+树中的B代表平衡（balance），而不是二叉（binary）。

B+树是由二叉树、二叉查找树、平衡二叉树（AVL Tree）和平衡多路查找树（B-Tree）逐步演化而来的。

备注：关于B-Tree和B+Tree的内容多转自@brotherbin，因为他写的很好，我就不重复造轮子了。

4.1.二叉树

二叉树（Binary Tree）：树的每个节点最多有两个子树。

索引无效回表索引失效in_mysql

二叉树是完全无序的，不利于节点查询。因此，引出了利于查询的二叉树：二叉查找树。

4.2.二叉查找树

二叉查找树（Binary Sort Tree）又称之为二叉搜索树（Binary Search Tree）。

二叉查找树或者是空树，或者满足以下定义：

若它的左子树非空，则左子树上所有结点的值均小于根结点的值；
若它的右子树非空，则右子树上所有结点的值均大于根结点的值；
左、右子树本身又各是一棵二叉排序树。

二叉树是相对有序的，与二分查找类似，其查找的时间复杂度平均为O(Log2n)。

索引无效回表索引失效in_mysql_02

上面的两棵树都是二叉查找树：

左边的二叉查找树相对平衡，这样它的层数不会太高，查找效率接近O(Log2n)。
右边的二叉查找树十分不平衡，基本等同于链表，它的查找效率接近O(n)。

二叉查找树的平衡十分影响查询效率。因此，引入了自带平衡的二叉查找树：AVL树。

4.3.平衡二叉树（AVLTree）

AVL树中的AVL并不是什么概念缩写，其命名来源于它的发明者G.M. Adelson-Velsky 和 E.M. Landis。

AVL树，本质上是带了平衡功能的二叉查找树。

AVL树的平衡条件：每个结点的左右子树的高度之差的绝对值（平衡因子）最多为1。

平衡因子 = |左子树高度 - 右子树高度|。

AVL树的四种不平衡

AVL树的关键在于：当增删节点时，可能会导致树的不平衡，可以概括为四种情况：LL（左左）、RR（右右）、LR（左右）、RL（右左）。

索引无效回表索引失效in_索引无效回表_03

如上图所示，紫色节点为新增的节点，也是导致树不平衡的节点。

因为这个节点的新增导致了根节点的平衡因子变为了2，也就是根节点成为了不平衡节点。

LL：根节点的左孩子（Left Child）的左孩子（Left Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。
RR：根节点的右孩子（Right Child）的右孩子（Right Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。
LR：根节点的左孩子（Left Child）的右孩子（Right Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。
RL：根节点的右孩子（Right Child）的左孩子（Left Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。

AVL的旋转

如果产生了不平衡，那么AVL树是如何重新调整为平衡状态的呢？下面以LL和LR情形进行说明，RR和RL与之对称，自行理解。

首先，我们来看LR不平衡，直接看下面的图。

索引无效回表索引失效in_索引_04

上图展示了LR如何通过左子树的一次向左旋转转换成了LL。

转换完成之后，原导致不平衡的节点[5]变为了正常节点，原正常节点[1]变为了导致不平衡的节点。

因为LR可以通过上述的旋转转换为LL，所以我们继续关注LL如何变为平衡的。如下图所示。

索引无效回表索引失效in_索引_05

整个子树经过一次向右旋转，LL的树重新变成了平衡的树。

总结：

LL转换为平衡：1.整个子树进行一次向右旋转。
LR转换为平衡：1.LL的左子树进行一次向左旋转转换为LR。2.整个子树进行一次向右旋转。
RR转换为平衡：1.整个子树进行一次向左旋转。
RL转换为平衡：1.RR的右子树进行一次向右旋转转换为RL。2.整个子树进行一次向左旋转。

AVL树因其平衡性，能够保证查询效率的稳定。但是由于其是二叉的，导致每层的节点数量有限，当数据量非常大时，树一定很高。

4.4.平衡多路查找树（B-Tree）

B-Tree读作B树，不是B减树。这里的B不是二叉Binary的意思，而是平衡Balance的意思。

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

那么它是如何专门针对磁盘等外存储设备设计的呢？

我们先看看操作系统中磁盘块block的相关概念：

扇区sector是磁盘传输数据的最小单位，通常大小为512B（现在的新硬盘每个扇区有4K）。
磁盘块block是操作系统传输数据的最小单位，一个块通常对应一个或多个相邻的扇区，一个块的大小为4KB。
操作系统从磁盘块block读取数据时，位于同一个磁盘块中的数据会被一次性读取出来，而不是只取磁盘块的一部分。

再来看看MySql如何适应操作系统。

InnoDB存储引擎中有页page的概念，是其进行磁盘管理的最小单位。
一页包含一个或者多个连续的磁盘块，默认大小为16KB。
可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。
在MySql中，查询当页大小的命令是:

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

如果在查询时，一个页中的多个块中的每条数据都有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。

B-Tree的结构

本段内容以及插图主要引用自，感谢作者。

为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。

对于不同的记录，key值互不相同。一棵m阶的B-Tree有如下特性：

每个节点最多有m个孩子。
除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
若根节点不是叶子节点，则至少有2个孩子
所有叶子节点都在同一层，且不包含其它关键字信息
每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
ki(i=1,…n)为关键字，且关键字升序排序。
Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

索引无效回表索引失效in_索引无效回表_06

每个节点占用一个磁盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。

两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。

以根节点为例，关键字为17和35，P1指针指向的子树数据范围为小于17，P2指针指向的子树数据范围为17~35，P3指针指向的子树数据范围为大于35。

模拟查找关键字29的过程：

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。

由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。

而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。

B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

4.5.B+Tree

B-Tree读作B加树，+指的是优化。这里的B不是二叉Binary的意思，而是平衡Balance的意思。

本段内容以及插图主要引用自，感谢作者。

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。

而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

索引无效回表索引失效in_mysql_07

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。

因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

InnoDB存储引擎中页的大小为16KB。
一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节。
也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3）。
也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2至4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

4.6.聚簇索引与非聚簇索引

分析了MySQL的索引结构的实现原理，然后我们来看看具体的存储引擎怎么实现索引结构的。

MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引（secondary index）和聚簇索引（clustered index）。

上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。

非聚簇索引与聚集索引的区别在于：非聚簇索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。

当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

5.使用策略

5.1.建议使用索引的场景

主键索引：主键。
唯一索引：不是主键，但是对唯一性要求很高的列。
普通索引：经常作为``where查询条件、order by排序字段、join on表关联条件、聚合(count、max`)的列。
组合索引：经常共同作为查询条件的列。
全文索引：其实我用的很少，因为对中文支持不好，一般这种需求都放在了Solr或者ElasticSearch中处理。

5.2.不建议使用索引的场景

经常增删改的列不建议创建索引，因为：1.索引是为了提高检索效率。2.增删改数据也需要对索引树进行调整。
大量重复的键不建议创建索引，因为：大量重复值对快速检索没有意义。

6.失效情况

6.1.组合索引使用不当

章节3.4.组合索引(INDEX)中介绍过组合索引的使用规则.

现有组合索引ci_author_name_pub(name,author,publisher)，则可用列组合为：name,author,publisher，name,author，name,author。

正确的示例：

分表使用3种列组合查询，其索引长度key_len分别是：486、284和202。

mysql> explain select * from book where name = '书名' and author = '作者' and publisher = '出版社';
+----+-------------+-------+------+--------------------+--------------------+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys      | key                | key_len | ref               | rows | Extra                 |
+----+-------------+-------+------+--------------------+--------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | book  | ref  | ci_name_author_pub | ci_name_author_pub | 486     | const,const,const |    1 | Using index condition |
+----+-------------+-------+------+--------------------+--------------------+---------+-------------------+------+-----------------------+
1 row in set (0.09 sec)

mysql> explain select * from book where name = '书名' and author = '作者';
+----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys      | key                | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | book  | ref  | ci_name_author_pub | ci_name_author_pub | 284     | const,const |    1 | Using index condition |
+----+-------------+-------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from book where name = '书名';
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys      | key                | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | book  | ref  | ci_name_author_pub | ci_name_author_pub | 202     | const |    1 | Using index condition |
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

错误的示例

1.缺少左边的列，直接导致索引失效

mysql> explain select * from book where author = '作者' and publisher = '出版社';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL | 1217 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from book where publisher = '出版社';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL | 1217 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

2.缺少中间的列，导致索引部分有效。(下面的例子中，key_len=202表示只是单列name的索引)

mysql> explain select * from book where name = '书名' and publisher = '出版社';
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys      | key                | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | book  | ref  | ci_name_author_pub | ci_name_author_pub | 202     | const |    1 | Using index condition |
+----+-------------+-------+------+--------------------+--------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

6.2.左模糊查询

当模糊查询的最左边带有%号时，与最左前缀原则冲突，索引失效。

mysql> explain select * from book where name like '%书名';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL | 1217 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

6.3.索引列带表达式或者函数

mysql> explain select * from book where length(name) = 12;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL | 1217 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

6.4.is null或者is not null

mysql> explain select * from book where name is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

6.5.不等于、大于、小于

mysql> explain select * from book where name <> '书名';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | i_name        | NULL | NULL    | NULL | 1217 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from book where page > 32;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | i_page        | NULL | NULL    | NULL | 1217 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

6.6.字符串索引列查询条件不加单引号

注意：数值索引列查询条件加了单引号索引也是生效的。

mysql> explain select * from book where name = '32';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | book  | ref  | i_name        | i_name | 202     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from book where page = '32';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | book  | ref  | i_page        | i_page | 4       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
1 row in set (0.01 sec)

6.7.OR连接多个查询田间

第一种情况：两个条件都有索引，都生效了。

第二种情况：中间的条件没有索引，导致所有的索引都失效。

mysql> explain select * from book where name = '书名' or author = '作者';
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
| id | select_type | table | type        | possible_keys   | key             | key_len | ref  | rows | Extra                                     |
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
|  1 | SIMPLE      | book  | index_merge | i_author,i_name | i_name,i_author | 202,82  | NULL |    2 | Using union(i_name,i_author); Using where |
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
1 row in set (0.01 sec)

mysql> explain select * from book where name = '书名' or ISBN = '001' or author = '作者';
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys   | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | i_author,i_name | NULL | NULL    | NULL | 1217 | Using where |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

7.优化

7.1.组合索引优化

遵循最左前缀原则，依照字段的检索频率高低排列字段。

7.2.模糊查询优化

左模糊查询('%xx')会导致索引失效，如果可以接受，可以考虑使用全文索引。

7.3.长索引优化

通过章节4.原理可知索引长度影响着B+Tree一个节点存储的key数量，从而影响查询速度、磁盘空间和I/O次数。

如果一个列，通过前几个字符就可以区分大多数数据，则可以通过指定索引列的长度来减少索引的长度，如INDEX i_publisher(publisher(4))。

7.4.尽量使用覆盖索引

减少使用select *，尽量使用覆盖索引：查询的列属于建立索引的列。

例如，已知组合索引如果缺少左边的字段，则不会使用索引，如下：

mysql> explain select * from book where author = '作者' and publisher = '出版社';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL | 1217 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

那么，如果把查询条件select *改为select author呢？

mysql> explain select author from book where author = '作者' and publisher = '出版社';
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key                | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | book  | index | NULL          | ci_name_author_pub | 486     | NULL | 1217 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

explain结果中possible_keys为空，表示用不上索引的树形查找。

而key却有值，这表示二级索引中有要查找的数据，MySql可能会顺序遍历这个二级索引直接返回结果，不需要回表操作。

惊不惊喜？意不意外？

覆盖索引是一种二级索引，这里不过多叙述，有兴趣的可以自行了解。

参考文档

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：机器学习TFIDF计算例题 tfidf算法改进

下一篇：redis 库可以保存多少存储量 redis有多少库

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯