MySQL 索引

索引是什么

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。

通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。

索引和数据位于存储引擎中。

为什么要使用索引

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

索引是数据库性能调优技术的基础，常用于实现数据的快速检索。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1. 顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。

2. 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。

使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如，在学生基本信息表tb_students中，如果基于student_id建立了索引，系统就建立了一张索引列到实际记录的映射表。当用户需要查找student_id为 12022 的数据的时候，系统先在student_id索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

简而言之，不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

索引的优缺点

索引的优点如下：

• 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
• 可以给所有的 MySQL 列类型设置索引。
• 可以大大加快数据的查询速度，这是使用索引最主要的原因。
• 在实现数据的参考完整性方面可以加速表与表之间的连接。
• 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

增加索引也有许多不利的方面，主要如下：

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

使用索引时，需要综合考虑索引的优点和缺点。

索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

什么时候需要/不需要创建索引

什么时候适用索引

• 字段有唯一性限制的，比如商品编码；
• 经常用于WHERE查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于GROUP BY和ORDER BY的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在B+Tree中的记录都是排序好的。

什么时候不需要创建索引？

• WHERE条件，GROUP BY，ORDER BY里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

索引的分类

索引的分类和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。

我们可以按照四个角度来分类索引。

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

按数据结构分类

从数据结构的角度来看，MySQL 常见索引有B+Tree索引、HASH索引、Full-Text索引。

索引类型	InnoDB 引擎	MyISAM 引擎	Memory 引擎
B+Tree 索引	Yes	Yes	Yes
HASH 索引	No 不支持 HASH 索引，但是在内存结构中有一个自适应 HASH 索引	NO	Yes
Full-Text 索引	Yes MySQL5.6版本后支持	Yes	No

B+Tree索引

InnoDB 是在 MySQL5.5 之后成为默认的 MySQL 存储引擎，B+Tree索引类型也是 MySQL 存储引擎采用最多的索引类型。

B+Tree索引是一个典型的数据结构，其包含的组件主要有以下几个：

• 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
• 分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
• 根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

B+Tree索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行ORDER BY排序。但B+Tree树索引必须遵循左边前缀原则，要考虑以下几点约束：

• 查询必须从索引的最左边的列开始。
• 查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
• 存储引擎不能使用索引中范围条件右边的列。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
• 如果没有主键，就选择第一个不包含NULL值的唯一列作为聚簇索引的索引键（key）；
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增id列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是B+Tree索引。

B+Tree 索引的存储和查询的过程

接下来通过一个简单例子，说明一下B+Tree索引在存储数据中的具体实现。

先创建一张商品表，id为主键，如下：

CREATE TABLE `product`  (
  `id` int(11) NOT NULL,
  `product_no` varchar(20)  DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `price` decimal(10, 2) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

商品表里，有这些行数据：

这些行数据，存储在B+Tree索引时是长什么样子的？

B+Tree是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

主键索引的B+Tree如图所示：

通过主键查询商品数据的过程

比如，我们执行了下面这条查询语句：

select * from product where id= 5;

这条语句使用了主键索引查询id号为 5 的商品。查询过程是这样的，B+Tree会自顶向下逐层进行查找：

• 将 5 与根节点的索引数据(1，10，20)比较，5 在 1 和 10 之间，所以根据B+Tree的搜索逻辑，找到第二层的索引数据(1，4，7)；
• 在第二层的索引数据(1，4，7)中进行查找，因为 5 在 4 和 7 之间，所以找到第三层的索引数据(4，5，6)；
• 在叶子节点的索引数据(4，5，6)中进行查找，然后我们找到了索引值为 5 的行数据。

数据库的索引和数据都是存储在硬盘的，我们可以把读取一个节点当作一次磁盘 I/O 操作。那么上面的整个查询过程一共经历了 3 个节点，也就是进行了 3 次 I/O 操作。

B+Tree存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以B+Tree相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4 次。

通过二级索引查询商品数据的过程

主键索引的B+Tree和二级索引的B+Tree区别如下：

• 主键索引的B+Tree的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的B+Tree的叶子节点里；
• 二级索引的B+Tree的叶子节点存放的是主键值，而不是实际数据。

将前面的商品表中的product_no字段设置为二级索引，那么二级索引的B+Tree如下图。

其中非叶子的key值是product_no（图中橙色部分），叶子节点存储的数据是主键值（图中绿色部分）。

如果用product_no二级索引查询商品，如下查询语句：

select * from product where product_no = '0002';

会先检二级索引中的B+Tree的索引值（商品编码，product_no），找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的B+Tree树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个B+Tree才能查到数据。如下图：

不过，当查询的数据是能在二级索引的B+Tree的叶子节点里查询到，这时就不用再查主键索引查，比如下面这条查询语句：

select id from product where product_no = '0002';

这种在二级索引的B+Tree就能查询到结果的过程就叫作「覆盖索引」，也就是只需要查一个B+Tree就能找到数据。

哈希索引

哈希（Hash）就是把任意长度的输入通过散列算法变换成固定长度的输出，该输出就是散列值。哈希索引也称为散列索引或HASH索引。

HASH索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

• MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于B+Tree索引来说，建立哈希索引会耗费更多的时间。
• 不能使用HASH索引排序。
• HASH索引只支持等值比较，如= IN()或<=>。
• HASH索引不支持键的部分匹配，因为在计算HASH值的时候是通过整个索引值来计算的。

按物理存储分类

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）：

• 主键索引的B+Tree的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的B+Tree的叶子节点里；
• 二级索引的B+Tree的叶子节点存放的是主键值，而不是实际数据。

所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许重复或为空。

创建主键索引通常使用PRIMARY KEY关键字。不能使用CREATE INDEX语句创建主键索引。

在创建表时，创建主键索引的方式如下：

CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

唯一索引

唯一索引建立在UNIQUE字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

创建唯一索引通常使用UNIQUE关键字。

在创建表时，创建唯一索引的方式如下：

CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

建表后，如果要创建唯一索引，可以使用这面这条命令：

CREATE UNIQUE INDEX index_name ON table_name(index_column_1,index_column_2,...); 

普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为UNIQUE。

普通索引没有任何限制，唯一任务就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。

创建普通索引时，通常使用的关键字是INDEX或KEY。

在创建表时，创建普通索引的方式如下：

CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

建表后，如果要创建普通索引，可以使用这面这条命令：

CREATE INDEX index_name ON table_name(index_column_1,index_column_2,...); 

前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为char、varchar、binary、varbinary的列上。

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

在创建表时，创建前缀索引的方式如下：

CREATE TABLE table_name(
  column_list,
  INDEX(column_name(length))
); 

建表后，如果要创建前缀索引，可以使用这面这条命令：

CREATE INDEX index_name ON table_name(column_name(length)); 

按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

• 建立在单列上的索引称为单列索引，比如主键索引；
• 建立在多列上的索引称为联合索引；

单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。

单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

下面在tb_student表中的address字段上建立名为index_addr的单列索引，address字段的数据类型为VARCHAR(20)，索引的数据类型为CHAR(4)。

CREATE INDEX index_addr ON tb_student(address(4));

这样，查询时可以只查询address字段的前 4 个字符，而不需要全部查询。

联合索引

通过将多个字段组合成一个索引，该索引就被称为联合索引(多列索引)。

联合索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

比如将商品表中的product_no和name字段组合成联合索引(product_no, name)，创建联合索引的方式如下：

CREATE INDEX index_product_no_name ON product(product_no, name);

联合索引(product_no, name)的B+Tree示意图如下：

可以看到，联合索引的非叶子节点用两个字段的值作为B+Tree的key值。当在联合索引查询数据时，先按product_no字段比较，在product_no相同的情况下再按name字段比较。

也就是说，联合索引查询的B+Tree是先按product_no进行排序，然后再product_no相同的情况再按name字段排序。

因此，使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。

比如，如果创建了一个(a, b, c)联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

需要注意的是，因为有查询优化器，所以a字段在where子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2；
where c=3；
where b=2 and c=3；

上面这些查询条件之所以会失效，是因为(a, b, c)联合索引，是先按a排序，在a相同的情况再按b排序，在b相同的情况再按c排序。所以，b和c是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。

我这里举联合索引(a, b)的例子，该联合索引的B+ Tree如下。

可以看到，a是全局有序的(1, 2, 2, 3, 4, 5, 6, 7 ,8)，而b是全局是无序的(12, 7, 8, 2, 3, 8, 10, 5, 2)。因此，直接执行where b = 2这种查询条件没有办法利用联合索引的，利用索引的前提是索引里的key是有序的。

只有在a相同的情况才，b才是有序的，比如a等于 2 的时候，b的值为(7, 8)，这时就是有序的，这个有序状态是局部的，因此，执行where a = 2 and b = 7是a和b字段能用到联合索引的，也就是联合索引生效了。

联合索引范围查询

联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的B+Tree，部分字段没有用到联合索引的B+Tree的情况。

这种特殊情况就发生在范围查询。联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

范围查询有很多种，那到底是哪些范围查询会导致联合索引的最左匹配原则会停止匹配呢？

接下来，举例几个范围查例子。

Q1: select * from t_table where a > 1 and b = 2，联合索引(a, b)哪一个字段用到了联合索引的B+Tree？

由于联合索引（二级索引）是先按照a字段的值排序的，所以符合a > 1条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合a > 1条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合a > 1条件位置。所以a字段可以在联合索引的B+Tree中进行索引查询。

但是在符合a > 1条件的二级索引记录的范围里，b字段的值是无序的。比如前面图的联合索引的B+Tree里，下面这三条记录的a字段的值都符合a > 1查询条件，而b字段的值是无序的：

• a字段值为 5 的记录，该记录的b字段值为 8；
• a字段值为 6 的记录，该记录的b字段值为 10；
• a字段值为 7 的记录，该记录的b字段值为 5；

因此，我们不能根据查询条件b = 2来进一步减少需要扫描的记录数量（b字段无法利用联合索引进行索引查询的意思）。

所以在执行 Q1 这条查询语句的时候，对应的扫描区间是(2, + ∞)，形成该扫描区间的边界条件是a > 1，与b = 2无关。

因此，Q1 这条查询语句只有a字段用到了联合索引进行索引查询，而b字段并没有使用到联合索引。

我们也可以在执行计划中的key_len知道这一点，在使用联合索引进行查询的时候，通过key_len我们可以知道优化器具体使用了多少个字段的搜索条件来形成扫描区间的边界条件。

举例个例子，a和b都是int类型且不为NULL的字段，那么 Q1 这条查询语句执行计划如下，可以看到key_len为 4 字节（如果字段允许为NULL，就在字段类型占用的字节数上加 1，也就是 5 字节），说明只有a字段用到了联合索引进行索引查询，而且可以看到，即使b字段没用到联合索引，key为idx_a_b，说明 Q1 查询语句使用了idx_a_b联合索引。

通过 Q1 查询语句我们可以知道，a字段使用了>进行范围查询，联合索引的最左匹配原则在遇到a字段的范围查询（>）后就停止匹配了，因此b字段并没有使用到联合索引。

Q2: select * from t_table where a >= 1 and b = 2，联合索引(a, b)哪一个字段用到了联合索引的B+Tree？

Q2 和 Q1 的查询语句很像，唯一的区别就是a字段的查询条件「大于等于」。

由于联合索引（二级索引）是先按照a字段的值排序的，所以符合>= 1条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合>= 1条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合a >= 1条件位置。所以a字段可以在联合索引的B+Tree中进行索引查询。

虽然在符合a >= 1条件的二级索引记录的范围里，b字段的值是「无序」的，但是对于符合a = 1的二级索引记录的范围里，b字段的值是「有序」的（因为对于联合索引，是先按照a字段的值排序，然后在a字段的值相同的情况下，再按照b字段的值进行排序）。

于是，在确定需要扫描的二级索引的范围时，当二级索引记录的a字段值为 1 时，可以通过b = 2条件减少需要扫描的二级索引记录范围（b字段可以利用联合索引进行索引查询的意思）。也就是说，从符合a = 1 and b = 2条件的第一条记录开始扫描，而不需要从第一个a字段值为 1 的记录开始扫描。

所以，Q2 这条查询语句a和b字段都用到了联合索引进行索引查询。

我们也可以在执行计划中的key_len知道这一点。执行计划如下，可以看到key_len为 8 字节，说明优化器使用了 2 个字段的查询条件来形成扫描区间的边界条件，也就是a和b字段都用到了联合索引进行索引查询。

通过 Q2 查询语句我们可以知道，虽然a字段使用了>=进行范围查询，但是联合索引的最左匹配原则并没有在遇到a字段的范围查询（>=）后就停止匹配了，b字段还是可以用到了联合索引的。

Q3: SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2，联合索引(a, b)哪一个字段用到了联合索引的B+Tree？

Q3 查询条件中a BETWEEN 2 AND 8的意思是查询a字段的值在 2 和 8 之间的记录。不同的数据库对BETWEEN ... AND处理方式是有差异的。在 MySQL 中，BETWEEN包含了value1和value2边界值，类似于>= and =<。而有的数据库则不包含value1和value2边界值（类似于> and <）。

由于 MySQL 的BETWEEN包含value1和value2边界值，所以类似于 Q2 查询语句，因此 Q3 这条查询语句a和b字段都用到了联合索引进行索引查询。

我们也可以在执行计划中的key_len知道这一点。执行计划如下，可以看到key_len为 8 字节，说明优化器使用了 2 个字段的查询条件来形成扫描区间的边界条件，也就是a和b字段都用到了联合索引进行索引查询。

通过 Q3 查询语句我们可以知道，虽然a字段使用了BETWEEN进行范围查询，但是联合索引的最左匹配原则并没有在遇到a字段的范围查询（BETWEEN）后就停止匹配了，b字段还是可以用到了联合索引的。

Q4: SELECT * FROM t_user WHERE name like 'j%' and age = 22，联合索引(name, age)哪一个字段用到了联合索引的B+Tree？

由于联合索引（二级索引）是先按照name字段的值排序的，所以前缀为j的name字段的二级索引记录都是相邻的， 于是在进行索引扫描的时候，可以定位到符合前缀为j的name字段的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录的name前缀不为j为止。

所以a字段可以在联合索引的B+Tree中进行索引查询，形成的扫描区间是['j','k')。注意，j是闭区间。如下图：

虽然在符合前缀为j的name字段的二级索引记录的范围里，age字段的值是「无序」的，但是对于符合name = j的二级索引记录的范围里，age字段的值是「有序」的（因为对于联合索引，是先按照name字段的值排序，然后在name字段的值相同的情况下，再按照age字段的值进行排序）。

于是，在确定需要扫描的二级索引的范围时，当二级索引记录的name字段值为j时，可以通过age = 22条件减少需要扫描的二级索引记录范围（age字段可以利用联合索引进行索引查询的意思）。也就是说，从符合name = 'j' and age = 22条件的第一条记录时开始扫描，而不需要从第一个name为j的记录开始扫描。如下图的右边：

所以，Q4 这条查询语句a和b字段都用到了联合索引进行索引查询。

我们也可以在执行计划中的key_len知道这一点。本次例子中：

• name字段的类型是varchar(30)且不为NULL，数据库表使用了utf8mb4字符集，一个字符集为utf8mb4的字符是 4 个字节，因此name字段的实际数据最多占用的存储空间长度是 120 字节（30 x 4），然后因为name是变长类型的字段，需要再加 2 字节（用于存储该字段实际数据的长度值），也就是name的key_len为 122。
• age字段的类型是int且不为NULL，key_len为 4。

Q4 查询语句的执行计划如下，可以看到key_len为 126 字节，name的key_len为 122，age的key_len为 4，说明优化器使用了 2 个字段的查询条件来形成扫描区间的边界条件，也就是name和age字段都用到了联合索引进行索引查询。

通过 Q4 查询语句我们可以知道，虽然name字段使用了like前缀匹配进行范围查询，但是联合索引的最左匹配原则并没有在遇到name字段的范围查询（like 'j%'）后就停止匹配了，age字段还是可以用到了联合索引的。

综上所示，联合索引的最左匹配原则，在遇到范围查询（如>、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于>=、<=、BETWEEN、like前缀匹配的范围查询，并不会停止匹配，前面我也用了四个例子说明了。

索引下推

现在我们知道，对于联合索引（a, b），在执行select * from table where a > 1 and b = 2语句的时候，只有a字段能用到索引，那在联合索引的B+Tree找到第一个满足条件的主键值（ID 为 2）后，还需要判断其他条件是否满足（看b是否等于 2），那是在联合索引里判断？还是回主键索引去判断呢？

• 在 MySQL5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比b字段值。
• 而 MySQL5.6 引入的索引下推优化(index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

当你的查询语句的执行计划里，出现了Extra为Using index condition，那么说明使用了索引下推的优化。

索引区分度

另外，建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

区分度就是某个字段column不同值的个数「除以」表的总行数，计算公式如下：

比如，性别的区分度就很小，不适合建立索引或不适合排在联合索引列的靠前的位置，而 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。

因为如果索引的区分度很小，假设字段的值分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比（惯用的百分比界线是”30%”）很高的时候，它一般会忽略索引，进行全表扫描。

联合索引进行排序

针对下面这条 SQL，你怎么通过索引来提高查询效率呢？

select * from order where status = 1 order by create_time asc

给status和create_time列建立一个联合索引，因为这样可以避免 MySQL 数据库发生文件排序。

因为在查询时，如果只用到status的索引，但是这条语句还要对create_time排序，这时就要用文件排序filesort，也就是在 SQL 执行计划中，Extra列会出现Using filesort。

所以，要利用索引的有序性，在status和create_time列建立联合索引，这样根据status筛选后的数据就是按照create_time排好序的，避免在文件排序，提高了查询效率。

提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

创建索引

创建索引是指在某个表的一列或多列上建立一个索引，可以提高对表的访问速度。

基本语法

MySQL 提供了三种创建索引的方法：

1.使用 CREATE INDEX 语句

可以使用专门用于创建索引的CREATE INDEX语句在一个已有的表上创建索引，但该语句不能创建主键。

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

语法说明：

• <索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
• <表名>：指定要创建索引的表名。
• <列名>：指定要创建索引的列名。通常可以考虑将查询语句中在JOIN子句和WHERE子句里经常出现的列作为索引列。
• <长度>：可选项。指定使用列前的length个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB或TEXT类型的列也必须使用前缀索引。
• ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。

2.使用 CREATE TABLE 语句

索引也可以在创建表（CREATE TABLE）的同时创建。在CREATE TABLE语句中添加以下语句。

CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)

在CREATE TABLE语句中添加此语句，表示在创建新表的同时创建该表的主键。

KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)

在CREATE TABLE语句中添加此语句，表示在创建新表的同时创建该表的索引。

UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

在CREATE TABLE语句中添加此语句，表示在创建新表的同时创建该表的唯一性索引。

FOREIGN KEY <索引名> <列名>

在CREATE TABLE语句中添加此语句，表示在创建新表的同时创建该表的外键。

在使用CREATE TABLE语句定义列选项的时候，可以通过直接在某个列定义后面添加PRIMARY KEY的方式创建主键。而当主键是由多个列组成的多列索引时，则不能使用这种方法，只能用在语句的最后加上一个PRIMARY KRY(<列名>，…)子句的方式来实现。

2.使用 ALTER TABLE 语句

CREATE INDEX语句可以在一个已有的表上创建索引，ALTER TABLE语句也可以在一个已有的表上创建索引。在使用ALTER TABLE语句修改表的同时，可以向已有的表添加索引。具体的做法是在ALTER TABLE语句中添加以下语法成分的某一项或几项。

ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

在ALTER TABLE语句中添加此语法成分，表示在修改表的同时为该表添加索引。

ADD PRIMARY KEY [<索引类型>] (<列名>,…)

在ALTER TABLE语句中添加此语法成分，表示在修改表的同时为该表添加主键。

ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

在ALTER TABLE语句中添加此语法成分，表示在修改表的同时为该表添加唯一性索引。

ADD FOREIGN KEY [<索引名>] (<列名>,…)

在ALTER TABLE语句中添加此语法成分，表示在修改表的同时为该表添加外键。

创建普通索引

创建普通索引时，通常使用INDEX关键字。

mysql> CREATE TABLE tb_stu_info
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> INDEX(height)
    -> );
Query OK，0 rows affected (0.40 sec)
mysql> SHOW CREATE TABLE tb_stu_info\G
*************************** 1. row ***************************
       Table: tb_stu_info
Create Table: CREATE TABLE `tb_stu_info` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  KEY `height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.01 sec)

创建唯一索引

创建唯一索引，通常使用UNIQUE参数。

mysql> CREATE TABLE tb_stu_info2
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> UNIQUE INDEX(height)
    -> );
Query OK，0 rows affected (0.40 sec)
mysql> SHOW CREATE TABLE tb_stu_info2\G
*************************** 1. row ***************************
       Table: tb_stu_info2
Create Table: CREATE TABLE `tb_stu_info2` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  UNIQUE KEY `height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

查看索引

可以使用SHOW INDEX语句查看表中创建的索引。

SHOW INDEX FROM <表名> [ FROM <数据库名>]

语法说明：

• <表名>：指定需要查看索引的数据表名。
• <数据库名>：指定需要查看索引的数据表所在的数据库，可省略。比如，SHOW INDEX FROM student FROM test;语句表示查看test数据库中student数据表的索引。

mysql> SHOW INDEX FROM tb_stu_info2\G
*************************** 1. row ***************************
        Table: tb_stu_info2
   Non_unique: 0
     Key_name: height
 Seq_in_index: 1
  Column_name: height
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:

其中各主要参数说明如下：

参数	说明
Table	表示创建索引的数据表名，这里是 tb_stu_info2 数据表。
Non_unique	表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。
Key_name	表示索引的名称。
Seq_in_index	表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。
Column_name	表示定义索引的列字段。
Collation	表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。
Cardinality	索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
Sub_part	表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。
Packed	指示关键字如何被压缩。若没有被压缩，值为 NULL。
Null	用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。
Index_type	显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
Comment	显示评注。

修改和删除索引

删除索引是指将表中已经存在的索引删除掉。不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。

修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。

基本语法

当不再需要索引时，可以使用DROP INDEX语句或ALTER TABLE语句来对索引进行删除。

1. 使用 DROP INDEX 语句

语法格式：

DROP INDEX <索引名> ON <表名>

语法说明如下：

• <索引名>：要删除的索引名。
• <表名>：指定该索引所在的表名。

2. 使用 ALTER TABLE 语句

根据ALTER TABLE语句的语法可知，该语句也可以用于删除索引。具体使用方法是将ALTER TABLE语句的语法中部分指定为以下子句中的某一项。

• DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
• DROP INDEX index_name：表示删除名称为index_name的索引。
• DROP FOREIGN KEY fk_symbol：表示删除外键。

注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

删除索引

删除表tb_stu_info中的索引。

mysql> DROP INDEX height ON tb_stu_info;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE tb_stu_info\G
*************************** 1. row ***************************
       Table: tb_stu_info
Create Table: CREATE TABLE `tb_stu_info` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

删除表tb_stu_info2中名称为id的索引。

mysql> ALTER TABLE tb_stu_info2 DROP INDEX height;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE tb_stu_info2\G
*************************** 1. row ***************************
       Table: tb_stu_info2
Create Table: CREATE TABLE `tb_stu_info2` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

索引在什么情况下不会被使用

索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起作用。使用索引有几种特殊情况，在这些情况下，有可能使用带有索引的字段查询时，索引并没有起作用。

1. 查询语句中使用LIKE关键字

在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为%，索引不会被使用。如果%不是在第一个位置，索引就会被使用。

mysql> SELECT * FROM tb_student;
+----+------+------+------+
| id | name | age  | sex  |
+----+------+------+------+
|  1 | 张三 |   12 | 男   |
|  2 | 李四 |   12 | 男   |
|  3 | 王五 |   13 | 女   |
|  4 | 张四 |   13 | 女   |
|  5 | 王四 |   15 | 男   |
|  6 | 赵六 |   12 | 女   |
+----+------+------+------+

下面在查询语句中使用LIKE关键字，且匹配的字符串中含有“%”符号，使用EXPLAIN分析查询情况：

mysql>  EXPLAIN SELECT * FROM tb_student WHERE name LIKE '%四'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

mysql> CREATE INDEX index_name ON tb_student(name);
Query OK, 6 rows affected (0.13 sec)

mysql>  EXPLAIN SELECT * FROM tb_student WHERE name LIKE '李%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: range
possible_keys: index_name
          key: index_name
      key_len: 77
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

第一个查询语句执行后，rows参数的值为 6，表示这次查询过程中查询了 6 条记录；第二个查询语句执行后，rows参数的值为 1，表示这次查询过程只查询 1 条记录。同样是使用name字段进行查询，因为第一个查询语句的LIKE关键字后的字符串是以“%”开头的，所以第一个查询语句没有使用索引，而第二个查询语句使用了索引index_name。

2. 查询语句中使用多列索引

多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。

在name和age两个字段上创建多列索引，并验证多列索引的使用情况：

mysql> CREATE INDEX index_name_age ON tb_student(name,age);
Query OK, 6 rows affected (0.11 sec)

mysql> EXPLAIN SELECT * FROM tb_student WHERE name LIKE '李%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: range
possible_keys: index_name_age
          key: index_name_age
      key_len: 77
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.05 sec)

mysql> EXPLAIN SELECT * FROM tb_student WHERE age LIKE '12'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

第一条查询语句的查询条件使用了name字段，分析结果显示rows参数的值为 1，且查询过程中使用了index_name_age索引。第二条查询语句的查询条件使用了age字段，结果显示rows参数的值为 6，且key参数的值为NULL，这说明第二个查询语句没有使用索引。

因为name字段是多列索引的第一个字段，所以只有查询条件中使用了name字段才会使index_name_age索引起作用。

3. 查询语句中使用OR关键字

查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

mysql> EXPLAIN SELECT * FROM tb_student WHERE name='张三' or sex='男'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ALL
possible_keys: index_name,index_name_age
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 30.56
        Extra: Using where
1 row in set, 1 warning (0.06 sec)
mysql> EXPLAIN SELECT * FROM tb_student WHERE name='张三' or id='12'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: index_merge
possible_keys: PRIMARY,index_name,index_name_age
          key: index_name,PRIMARY
      key_len: 77,4
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using union(index_name,PRIMARY); Using where
1 row in set, 1 warning (0.01 sec)

由于sex字段没有索引，所以第一条查询语句没有使用索引；name字段和id字段都有索引，所以第二条查询语句使用了index_name和PRIMARY索引 。

使用索引查询记录时，一定要注意索引的使用情况。例如，LIKE关键字配置的字符串不能以“%”开头；使用多列索引时，查询条件必须要使用这个索引的第一个字段；使用OR关键字时，OR关键字连接的所有条件都必须使用索引。

索引的设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候应尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

1. 选择唯一性索引
   唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
2. 为经常需要排序、分组和联合操作的字段建立索引
   经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
3. 为常作为查询条件的字段建立索引
   如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
   注意：常查询条件的字段不一定是所要选择的列，换句话说，最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。
4. 限制索引的数目
   索引的数目不是“越多越好”。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长。
   如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最佳索引。
5. 尽量使用数据量少的索引
   如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
6. 数据量小的表最好不要使用索引
   由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
7. 尽量使用前缀来索引
   如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
8. 删除不再使用或者很少使用的索引
   表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

打赏