当前位置：首页 > article >正文

MySql索引（基础篇）

article 2024/12/28 21:45:22

后面也会持续更新，学到新东西会在其中补充。

建议按顺序食用，欢迎批评或者交流！

缺什么东西欢迎评论！我都会及时修改的！

感谢各位大佬写的文章让我学到很多东西！只是在各位大佬的基础加了我自己的思路！

索引常见面试题 | 小林coding

从数据页的角度看 B+ 树 | 小林coding

(四)MySQL之索引初识篇：索引机制、索引分类、索引使用与管理综述索引的分类五花八门，索引的称呼千奇百怪，对于MySQ - 掘金

(五)MySQL索引应用篇：建立索引的正确姿势与使用索引的最佳指南！索引大家都用过，但你的SQL与索引真的合格吗？本章则 - 掘金

(六)MySQL索引原理篇：深入数据库底层揭开索引机制的神秘面纱！索引的底层原理，可谓是众人知的技术点，算不上特别新颖 - 掘金

实验环境

MySQL5.7、MySQL9.0

explain

放在这里是方便查询，因为后面文章都需要用到！如果多看几遍就熟悉了！

type列

all：
- 描述：全表扫描，即数据库引擎会扫描整个表来查找符合条件的行。
- 原因：未命中索引或索引失效，可能是因为查询条件无法利用索引，或者表中根本没有创建索引。
- 性能：通常较差，特别是当表的数据量很大时。
system：
- 描述：当表中只有一行数据时，查询会采用这种类型。
- 原因：表非常小，只有一个数据行。
- 性能：非常好，因为只需要读取一行数据。
const：
- 描述：当前SQL查询条件中的值是唯一的（通常是通过主键或唯一索引进行查找）。
- 原因：查询条件直接命中了索引中的唯一值。
- 性能：非常好，因为只需要读取一行数据，且查找速度非常快。
range：
- 描述：使用索引检索给定范围内的行。
- 原因：查询条件中使用了范围操作符（如 BETWEEN、<、>、<=、>= 等）或者使用了 IN 列表。
- 性能：比全表扫描好，但性能取决于索引的选择性和范围的大小。
eq_ref：
- 描述：在多表连接查询中，对于每一个来自前面的表的行，都会从当前表中读取一行（通常是通过主键或唯一索引）。
- 原因：连接条件中使用了主键或唯一索引，且连接是等值的（即使用=操作符）。
- 性能：较好，因为每次连接都只需要读取一行数据。
ref：
- 描述：使用非唯一索引查找匹配的行。使用普通索引查询
- 原因：查询条件中使用了普通索引，且不是唯一索引。
- 性能：取决于索引的选择性和匹配的行数。
index：
- 描述：全索引扫描，即数据库引擎会扫描整个索引来查找符合条件的行，而不是扫描整个表。
- 原因：查询条件中的列是索引的一部分，但查询条件未能充分利用索引（例如，查询条件只包含了索引的一部分列）。
- 性能：通常比全表扫描好，因为索引通常比表小，但性能也取决于索引的大小和选择性。

Extra列

using index：
- 描述：查询只访问了索引，而没有访问表中的数据行。这通常被称为“覆盖索引”（covering index），即索引包含了查询所需的所有列。
- 性能：非常高，因为避免了回表查询（即访问表中的数据行）。
using where：
- 描述：查询使用了 WHERE 子句来过滤数据，但不一定意味着没有使用索引。这个状态更多地是指出查询中有过滤条件。代表着回表！
- 性能：取决于是否使用了索引以及索引的选择性。如果未使用索引，则可能导致全表扫描。
using index condition（也称为ICP，Index Condition Pushdown）：
- 描述：MySQL 5.6及以上版本支持的一种优化技术，它允许将 WHERE 子句的一部分条件推送到索引层面进行过滤，从而减少回表查询的次数。
- 性能：通常比不使用ICP更好，因为它减少了不必要的回表查询。
using temporary：
- 描述：查询需要使用临时表来存储中间结果，这通常发生在排序（ORDER BY）或分组（GROUP BY）操作中，当无法利用索引进行排序或分组时。
- 性能：通常较低，因为临时表的创建和销毁会增加额外的开销。
using filesort：
- 描述：MySQL需要额外的步骤来对结果进行排序，因为查询中的 ORDER BY 或 GROUP BY 子句无法利用索引进行排序。
- 性能：通常较低，因为排序操作会增加额外的计算开销。如果可能，尽量通过索引来避免 filesort。
select tables optimized away：
- 描述：这个状态通常出现在使用聚合函数（如 SUM()、COUNT() 等）且这些聚合函数可以直接从索引中计算得出时。在这种情况下，MySQL不需要访问表中的数据行，因为所需的信息已经包含在索引中。
- 性能：非常高，因为避免了表访问。

EXPLAIN输出中，Extra列是NULL，这通常意味着查询优化器没有为这条查询生成任何特殊的额外信息或执行策略。这并不表示查询有问题，而只是说明MySQL优化器认为这条查询按照标准的索引查找和行过滤方式执行就足够了，没有需要特别注意的地方。

全表扫描

MySQL中的全表扫描和索引树扫描 - Garrett_Wale - 博客园

【mysql】全表扫描过程 & 聚簇索引区别和联系_全表扫描走聚簇索引吗-CSDN博客

先体验有索引和没有索引的感觉！

找到本地表数据文件中的起始地址。
会发生磁盘IO，第一行数据开始读，读到内存中。
MySQL-Server会根据SQL条件对读到的数据做判断。
如果不符合条件则继续发生磁盘IO读取其他数据（如果表比较大，这里不会以顺序IO的形式走全表检索，而是会触发随机的磁盘IO）。

全表检索（全表扫描）

全表检索意味着数据库系统需要读取表中的每一行数据来满足查询需求。在理想情况下，如果数据在磁盘上是连续存储的（或者至少是按某种顺序排列的），那么读取这些数据时可能会以顺序IO的形式进行。顺序IO是指数据被连续地从磁盘读取到内存中，这种方式通常比随机IO快，因为磁盘的读写头不需要频繁地移动来定位不同的数据块。

随机磁盘IO

当数据在磁盘上的存储不是连续的，或者查询需要访问表中随机分布的行时，就会发生随机磁盘IO。在这种情况下，磁盘的读写头需要频繁移动来定位并读取不同的数据块，这会显著降低读取效率。随机IO通常比顺序IO慢得多，因为磁盘读写头的移动需要时间，而且每次移动后可能只能读取很少的数据。

大表与随机IO

对于非常大的表，全表检索（尤其是顺序IO形式的）可能会变得非常耗时和效率低下，因为即使是以顺序方式读取，也需要处理大量的数据。更重要的是，大型数据库表往往由于频繁的插入、更新和删除操作而变得碎片化，这意味着数据在磁盘上不再是连续存储的。因此，当执行全表检索时，很可能会触发随机磁盘IO，这会导致性能显著下降。

局部性原理

我们不可能一行一行数据的读，磁盘的IO是宝贵的所以需要一次性读很多。

对了就算找到所有满足的行，但是表没扫完是磁盘IO是不会结束的！

必须让表扫完才能结束，因此全表扫描很耗时。

在InnoDB引擎中，一次默认会读取16KB数据到内存。

环境搭建：

CREATE TABLE `staff` (
`id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主键id',
`id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份证号码',
`name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名',
`age` INT ( 4 ) NOT NULL COMMENT '年龄',
`city` VARCHAR ( 64 ) NOT NULL COMMENT '城市',
PRIMARY KEY ( `id`),
INDEX idx_city ( `city` )
) ENGINE = INNODB COMMENT '员工表';

insert staff value('8','43333333','小明',22,'上海');
insert staff values (9,43333333,'小李',23,'深圳');
insert staff values (10,43333333,'小刚',28,'东羡');
insert staff values (11,43333333,'小红',20,'上海');
insert staff values (12,43333333,'小芳',36,'北京');
insert staff values (13,43333333,'小莉',19,'深圳');
insert staff values (14,43333333,'小华',27,'振江');

有索引和没索引的对比：

加数据！

测试：

第一段代码：走的全表扫描扫描的实际上的主键ID，但是需要扫描每个叶子结点的data值。

第二段代码：走的主键索引扫描的实际上的主键ID值，找到ID直接可以返回数据。

B+树

Data Structure Visualization

Binary Search Tree Visualization

MySql（面试题理解B+树原理实操加大白话）_mysql b+树面试题-CSDN博客

讲一些之前没学过的！

二叉搜索树：

从动画中可以明显看到，想要查到第五条数据，需要经过五次查询，由于树结构在磁盘中存储的位置也不连续，是逻辑上连续而不是物理上。因此无法利用局部性原理读取后续的节点，所以最终需要发生五次磁盘IO才能读取到数据。

B-树

通过一次磁盘IO可以读很多数据，满足了局部性原理。一次IO可以读取6条数据。

但是！现在又变成两次磁盘IO读了，当然数据越多IO次数越多！

满足一次磁盘IO
SELECT * FROM zz_user WHERE ID BETWEEN 2 AND 5;
所有数据都挂一个结点是不可能的因此还需要大量磁盘IO
SELECT * FROM zz_user WHERE ID BETWEEN 2 AND 10000;

因此B-树也不满足。

B+树（英雄登场）：

发现叶子结点的指针了吗？通过一次磁盘IO读就可以拿到所有数据的地址！（太酷辣）

这里的指针是双向连接的

叶节点不存储数据，仅存储指向叶子节点的指针，这样做的好处在于能够让一个叶节点中存储更多的元素，从而确保树的高度不会由于数据增长而变得很高。

叶子节点会存储实际的数据，例如聚簇索引中就直接存储对应的行数据，非聚簇索引中则存储指向主键/聚簇索引的字段值。

B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

从数据页的角度看 B+ 树 | 小林coding

创建MySql索引（普通索引）

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

CREATE语句创建：

CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);

create index idx_city on staff (city)

indexName：当前创建的索引，创建成功后叫啥名字。
tableName：要在哪张表上创建一个索引，这里指定表名。
columnName：要为表中的哪个字段创建索引，这里指定字段名。
length：如果字段存储的值过长，选用值的前多少个字符创建索引。
ASC|DESC：指定索引的排序方式，ASC是升序，DESC是降序，默认ASC。

修改索引数据结构：

CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]) USING HASH;

使用ALTER语句创建：

ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);

使用DDL语句创建：

CREATE TABLE `staff` (
`id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主键id',
`id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份证号码',
`name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名',
`age` INT ( 4 ) NOT NULL COMMENT '年龄',
`city` VARCHAR ( 64 ) NOT NULL COMMENT '城市',
PRIMARY KEY ( `id`),
INDEX idx_city ( `city` )  这里这里
) ENGINE = INNODB COMMENT '员工表';

查询、删除、指定索引

查询索引：

mysql> show index from staff;

Table：当前索引属于那张表。
Non_unique：目前索引是否属于唯一索引，0代表是的，1代表不是。
Key_name：当前索引的名字。
Seq_in_index：如果当前是联合索引，目前字段在联合索引中排第几个。
Column_name：当前索引是位于哪个字段上建立的。
Collation：字段值以什么方式存储在索引中，A表示有序存储，NULL表无序。
Cardinality：当前索引的散列程度，也就是索引中存储了多少个不同的值。
Sub_part：当前索引使用了字段值的多少个字符建立，NULL表示全部。
Packed：表示索引在存储字段值时，以什么方式压缩，NULL表示未压缩，
Null：当前作为索引字段的值中，是否存在NULL值，YES表示存在。
Index_type：当前索引的结构（BTREE, FULLTEXT, HASH, RTREE）。
Comment：创建索引时，是否对索引有备注信息。

删除索引：

DROP INDEX indexName ON tableName;

指定索引：

SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....;

强制走索引这就是！不走优化器选的路。

单列索引和联合索引

索引和数据就是位于存储引擎

单列索引是指索引是基于一个字段建立的，多列索引则是指由多个字段组合建立的索引。

单列索引有唯一索引、主键索引、普通索引、全文索引等等。

多列索引是指组合索引、联合索引、复合索引。

实验：

单列索引环境搭建：

mysql> create index idx_name on staff(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from staff;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| staff |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| staff |          1 | idx_name |            1 | name        | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

select 索引列 where 索引列

select 非索引列 where 索引列

select主索引列 where 索引列

联合索引环境搭建：

mysql> create index idx_sum on staff (name,age,city);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from staff;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staff |          0 | PRIMARY  |            1 | id          | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| staff |          1 | idx_sum  |            1 | name        | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| staff |          1 | idx_sum  |            2 | age         | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| staff |          1 | idx_sum  |            3 | city        | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

根据最左匹配原则，联合索引按照name先排序，随后在name相同的时候age再排。

因此如果不遵循最左匹配原则，联合索引会失效，这样就无法利用到索引快速查询的特性了。

create index index_name on user(name,age);

select * from user where name = '小李' and age = 20;
select * from user where  age = 20 and name = '小李';

如上两条SQL都会利用到上面创建的联合索引，SQL是否走索引查询跟where后的条件顺序无关，因为MySQL优化器会优化，对SQL查询条件进行重排序。

之前说了是按照name age city 这样排序的如果我们where条件并没有name

age city 是相对于name是有序的但是和全局无关，而name是全局有序的。

因此我们利用不到最左匹配原则！

说白了就是是否有序，有序就走索引。

联合索引范围查询

范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

select f1,f2 from t1 where f1>10 and f2=10;

假如这个联合索引长成这个样子。A字段是全局有序的，但是B是无序的！

因为我们范围查找要筛选出f1 > 10的，得先计算满足f1的f2等于多少都无所谓！

a 字段用到了联合索引进行索引查询，而 b 字段并没有使用到联合索引。

select f1,f2 from t1 where f1>=10 and f2=10;

>=：

为什么多了8字节？

select f1,f2 from t1 where f1>=10 and f2=10;
包含了select f1,f2 from t1 where f1=10 and f2=10;

在f1=10的时候f2是按照f1相同的时候排序的因此可以通过f2过滤一些信息。

between:

select f1,f2 from t1 where f1 between 2 and 10 and f2 = 2;

like%:

select stu_name from student where stu_name like 'w%' and stu_age = 21;

stu_name 和 stu_age都用到了联合索引

数据库表使用了 utf8mb4 字符集key_len = 255 * 4 + 2 + 4

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

聚簇和非聚簇索引

聚簇索引：也被称为聚集索引、簇类索引
非聚簇索引：也叫非聚集索引、非簇类索引、二级索引、辅助索引、次级索引

有点像数组和链表

聚簇索引：逻辑上连续且物理空间上的连续。
非聚簇索引：逻辑上的连续，物理空间上不连续。

当然，这里的连续和数组不同，因为索引大部分都是使用B+Tree结构存储，所以在磁盘中数据是以树结构存放的，所以连续并不是指索引节点，而是指索引数据和表数据。

聚簇索引中，索引数据和表数据在磁盘中的位置是一起的，而非聚簇索引则是分开的，索引节点和表数据之间，用物理地址的方式维护两者的联系。

不过一张表中只能存在一个聚簇索引，一般都会选用主键作为聚簇索引，其他字段上建立的索引都属于非聚簇索引，或者称之为辅助索引、次级索引。但也不要走进一个误区，

虽然MySQL默认会使用主键上建立的索引作为聚簇索引，但也可以指定其他字段上的索引为聚簇索引，一般聚簇索引要求索引必须是非空唯一索引才行。

当然，主键或者说聚簇索引，一般适合采用带有自增性的顺序值。

总结一下就是：

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

索引文件

[root@RockyLinux9 mysql]#cd /var/lib/mysql/
[root@RockyLinux9 mysql]#ll
total 188460
-rw-r-----. 1 mysql mysql       56 Dec 20 10:44 auto.cnf
-rw-r-----. 1 mysql mysql      540 Dec 24 22:29 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 Dec 25 17:05 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Dec 25 17:05 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Dec 20 10:44 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Dec 25 12:48 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Dec 20 10:44 mysql
srwxrwxrwx. 1 mysql mysql        0 Dec 25 12:48 mysql.sock
-rw-------. 1 mysql mysql        5 Dec 25 12:48 mysql.sock.lock
drwxr-x---. 2 mysql mysql     8192 Dec 20 10:44 performance_schema
drwxr-x---. 2 mysql mysql     8192 Dec 20 10:44 sys
drwxr-x---. 2 mysql mysql     4096 Dec 25 17:05 test

frm：该文件中存储表的结构信息。
ibd：该文件中存储表的行数据和索引数据。

因为InnoDB引擎中，表数据和索引数据都一起放在.ibd文件中，也就代表着索引数据和表数据是处于同一块空间存储的，这符合聚簇索引的定义，因此InnoDB支持聚簇索引。

主键索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

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

select 主键ID where 主键索引

select 主键ID where 普通索引

问题1：可以随机UUID来作为索引吗？

MySQL默认的索引结构是B+Tree，也就代表着索引节点的数据是有序的。

B+ Tree Visualization

首先我按照有序的方式排了一边树的从左到右一直扩展的。

这次我没有按顺序插入，我在插入一个4和5

7和8被移动到左边给4和5提供空间，因为索引的有序性

由于主键索引是聚簇索引，因此上述案例中，当后续节点需要挪动时，也就代表着还需要挪动表数据，如果是偶尔需要移动还行，但如果主键字段值无序，那代表着几乎每次插入都有可能导致树结构要调整。

但使用自增ID就不会有这个问题，所有新插入的数据都会放到最后。

但是自增ID好像也有问题，就是信息泄漏的这个问题。

买一杯奶茶暴露所有！揭秘数据接口如何疯狂窥探个人隐私_哔哩哔哩_bilibili

最近看了一个新闻，只能说不得不防啊！

唯一索引

ALTER TABLE tableName ADD UNIQUE INDEX indexName(columnName);

alter table staff add unique index idx_uniquecard(id_card);
ERROR 1062 (23000): Duplicate entry '43333333' for key 'idx_uniquecard'

ERROR 1062 (23000): Duplicate entry '43333333' for key 'idx_uniquecard'

必须字段没有重复才能创建！

唯一索引有个很大的好处，就是查询数据时会比普通索引效率更高。

假设COLUMN_XX字段上建立了一个普通索引，此时基于这个字段查询数据时，当查询到一条COLUMN_XX = "XX"的数据后，此时会继续走完整个索引树，因为可能会存在多条字段值相同的数据。

但如果COLUMN_XX字段上建立的是唯一索引，当找到一条数据后就会立马停下检索，因此本身建立唯一索引的字段值就具备唯一性。

因此唯一索引查询数据时，会比普通索引快上一截，但插入数据时就不同了，因为要确保数据不重复，所以插入前会检查一遍表中是否存在相同的数据。但普通索引则不需要考虑这个问题，因此普通索引的数据插入会快一些。

全文索引

他只能创建在CHAR、VARCHAR、TEXT等这些文本类型字段上，而且使用全文索引查询时，条件字符数量必须大于3才生效。

MySQL版本必须要在5.7及以上

ALTER TABLE tableName ADD FULLTEXT INDEX indexName(columnName);

alter table staff add fulltext index idx_idcrad(id_card);

注意：

创建全文索引的字段，其类型必须要为CHAR、VARCHAR、TEXT等文本类型。
如果想要创建出的全文索引支持中文，需要在最后指定解析器：with parser ngram。

mysql> alter table staff add fulltext index idx_idcard(id_card) WITH PARSER NGRAM;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

show variables like '%ft%';

词语大小限制在3~84。

查询！

SELECT 
    COUNT(id_card) AS '搜索结果数量' 
FROM 
    `staff` 
WHERE 
    MATCH(id_card) AGAINST('433');

测试一下like和全文索引谁更快

mysql> show index from staff;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staff |          0 | PRIMARY    |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| staff |          1 | idx_sum    |            1 | name        | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| staff |          1 | idx_sum    |            2 | age         | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| staff |          1 | idx_sum    |            3 | city        | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| staff |          1 | idx_idcard |            1 | id_card     | NULL      |           7 |     NULL | NULL   |      | FULLTEXT   |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select count(id_card) as '结果' from staff where match(id_card) against('433');
+--------+
| 结果   |
+--------+
|      7 |
+--------+
1 row in set (0.00 sec)

mysql> select count(id_card) as id_cardsum from staff where id_card like '433%';
+------------+
| id_cardsum |
+------------+
|          7 |
+------------+
1 row in set (0.00 sec)

mysql> select count(id_card) as '结果' from staff where match(id_card) against('433');
+--------+
| 结果   |
+--------+
|      7 |
+--------+
1 row in set (0.00 sec)

mysql> select count(id_card) as id_cardsum from staff where id_card like '433%';
+------------+
| id_cardsum |
+------------+
|          7 |
+------------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+----------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                            |
+----------+------------+----------------------------------------------------------------------------------+
|        1 | 0.00091475 | select * from profiles                                                           |
|        2 | 0.00047450 | select * from profiles                                                           |
|        3 | 0.00084650 | select count(id_card) as '结果' from staff where match(id_card) against('433')   |
|        4 | 0.00049375 | select count(id_card) as id_cardsum from staff where id_card like '433%'         |
+----------+------------+----------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

like要快很多可能是我数据放少了。

mysql> alter table DICT_REGION_CITY add fulltext index idx_name(name);
Query OK, 0 rows affected, 1 warning (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> select count(name) from DICT_REGION_CITY where match(name) against('三亚市');
+-------------+
| count(name) |
+-------------+
|           1 |
+-------------+
1 row in set (0.01 sec)

mysql> select count(name) from DICT_REGION_CITY where name like '%三亚市%';
+-------------+
| count(name) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

show profiles;
                                                                    |
|     3814 | 0.00183175 | select count(name) from DICT_REGION_CITY where match(name) against('三亚市')                                                                                                                              |
|     3815 | 0.00065825 | select count(name) from DICT_REGION_CITY where name like '%三亚市%'                                                                                                                                       |

我差不多整了三百多行数据还是一样！

又加到了2000行

|     5532 | 0.00081425 | select count(name) from DICT_REGION_CITY where match(name) against('三亚市')                                                                                  |
|     5533 | 0.00195075 | select count(name) from DICT_REGION_CITY where name like '%三亚市%'

这个时候全文索引比like快很多了！

空间索引

没有用到过，用到再补充！

ALTER TABLE tableName ADD SPATIAL KEY indexName(columnName);

回表

基于ID字段先建立了一个主键索引，然后又基于name字段建立了一个普通索引，此时MySQL默认会选用主键索引作为聚簇索引，将表数据和主键索引存在同一个文件中，也就是主键索引的每个索引节点，都直接对应着行数据。而基于name字段建立的索引，其索引节点存放的则是指向聚簇索引的ID值。

索引覆盖Covering Index

为了解决回表问题！

mysql> create index idx_sum on staff(name,age,city);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from staff;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staff |          0 | PRIMARY  |            1 | id          | A         |          31 |     NULL | NULL   |      | BTREE      |         |               |
| staff |          1 | idx_sum  |            1 | name        | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| staff |          1 | idx_sum  |            2 | age         | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| staff |          1 | idx_sum  |            3 | city        | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

select 索引列 where 索引列

如有查询的列在在联合索引中完全包含，因此可以直接通过联合索引获取到数据。就不需要回表了！

就是要查询的列，在使用的索引中已经包含，被所使用的索引覆盖，这种情况称之为索引覆盖。

key_len长度不变？

通过执行计划可以看到只用到了name和age 而city没用到直接过滤出来了

mysql> show index from staff;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| staff |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| staff |          1 | idx_sum  |            1 | name        | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| staff |          1 | idx_sum  |            2 | age         | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

select 索引列 where 索引列非索引列

select 索引列非索引列 where 索引列非索引列

select 非索引列 where 索引列非索引列

索引下推Index Condition Pushdown Optimization

MySQL :: MySQL 8.0 参考手册 :: 10.2.1.6 索引条件下推优化 --- MySQL :: MySQL 8.0 Reference Manual :: 10.2.1.6 Index Condition Pushdown Optimization

✨五分钟速览✨MySQL的索引下推✨什么你竟然只知道覆盖索引不知道索引下推。本文以一个短小精美的例子，五分钟帮助你完成 - 掘金

mysql> select @@optimizer_switch\G;

环境搭建：

CREATE TABLE student (
  id INT PRIMARY KEY auto_increment,
  stu_name VARCHAR ( 255 ) NOT NULL,
  stu_age INT NOT NULL,
  stu_phone VARCHAR ( 20 ) DEFAULT NULL ,
  stu_sex VARCHAR( 20 ) NOT NULL,
  stu_des VARCHAR( 4096 ) NOT NULL
);
INSERT INTO student ( stu_name, stu_age, stu_phone, stu_sex, stu_des ) VALUES ( "Bill", 20, "18888888888", "male", "Able to endure hardship" );
INSERT INTO student ( stu_name, stu_age, stu_phone, stu_sex, stu_des ) VALUES ( "Bob", 25, "17777777777", "male", "There will be endless bitterness to eat" );
INSERT INTO student ( stu_name, stu_age, stu_phone, stu_sex, stu_des ) VALUES ( "Mary", 21, "15555555555", "female", "Able to endure hardship" );
INSERT INTO student ( stu_name, stu_age, stu_phone, stu_sex, stu_des ) VALUES ( "Bill", 22, "13333333333", "male", "There will be endless bitterness to eat" );

create index name_age_phone_index on student(stu_name,stu_age,stu_phone);

测试：

Using index 没有回表！为覆盖索引

路径：辅助索引-> MySQL Server -> 客户端

Using where 回表！

需要把最左匹配原则放在心中

stu_name和stu_sex在索引里面都找的到，但是stu_sex没在索引中进行回表！

路径：辅助索引>原数据表->MySql Server -> 客户端

where 条件其中一列不遵守最左匹配原则还有一列不在索引表中

stu_phone不符合最左匹配只有stu_name符合

但是！有了索引下推就可以把where 条件推到存储引擎中

也就是判断索引表中 stu_name 和 stu_phone 判断出的条件再返回到原表中。

因为where 条件里面还有一个 stu_sex

路径：辅助索引->原数据->MySql Server -> 客户端

减少了回表的次数！

where 条件其中一列不遵守最左匹配原则

第一段代码：name phone在索引列 des 不在索引列 phone是无序的（之前说过了）会通过回表查找phone和des，而有了索引下推就会把phone放在索引表中查询完以后再去回表，节约了磁盘IO。

like%

逻辑是这样的！

第二段代码:

第一部分stu_name = 'Bill’ 第二部分 stu_phone like ('1888888888%')不能用来限制必须扫描的行数，因此没有索引条件下推，此查询必须检索所有具有 stu_name = 'Bill’ 的人的完整表行。

所以想要有索引下推 stu_phone like ('1888888888%')先找出第二部分符合的，再索引下推找stu_name='Bill'。

第一段代码:

我是这样理解的因为第一个字段就是like 说明必须全表扫才能知道是否匹配。

type = all 代表的是全表扫描。

开启索引下推

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

跳跃索引Index Skip Scan

MySQL 8.0 索引跳跃扫描(Index Skip Scan)-数据库星球

新特性解读 | MySQL 8.0 索引特性2-索引跳跃扫描-腾讯云开发者社区-腾讯云

我滴乖乖，MySQL联合索引不一定要求最左匹配？跳跃索引！_复合索引不一定走最左-CSDN博客

MySQL :: MySQL 8.0 参考手册 :: 10.2.1.2 范围优化 --- MySQL :: MySQL 8.0 Reference Manual :: 10.2.1.2 Range Optimization

开启跳跃索引

set @@optimizer_switch = 'skip_scan=off|on'

环境搭建

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

在 MySQL 5.7 版本，上述SQL的执行主要逻辑是从索引中取出所有的记录，然后按照where条件f2>40进行过滤，最后将结果返回。

在MySQL 8.0 版本，上述SQL使用索引range扫描，代替全索引扫描，对于每一个f1字段的值，进行f2范围扫描。

对于上述官方文档给出的例子，8.0版本SQL执行过程如下：

获取f1字段第一个唯一值，也就是f1=1
构造f1=1 and f2 > 40，进行范围查询
获取f1字段第二个唯一值，也就是f1=2
构造f1=2 and f2 > 40，进行范围查询
一直扫描完f1字段所有的唯一值，最后将结果合并返回

MySQL 8.0 使用这种策略会减少访问的行数，因为会跳过不符合构造范围的行。

说了这么多实际上就是这段代码！MySQL其实内部自己把左边的列做了一次DISTINCT。

SELECT f1, f2 FROM t1 WHERE f2 > 40 and f1 = 1 
union 
SELECT f1, f2 FROM t1 WHERE f2 > 40 and f1 = 2;

范围扫描比全索引扫描更高效

如果左边distinct值比较多呢？

mysql> update t1 set f1 = 1 + RAND()*50000 where f1 > 0;
Query OK, 20480 rows affected (0.65 sec)
Rows matched: 20480  Changed: 20480  Warnings: 0
mysql> analyze table t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.01 sec)

左边一列数据distinct 增多。变成了一个基于二级索引的全表扫描。

说白了基数一变大，原型就显露了！

不遵守最左原则

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

注意：

select 选择的字段不能包含非索引字段否则跳跃索引失效！

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL,f3 INT NOT NULL);
INSERT INTO t1 VALUES
  (1,1,1), (1,2,2), (1,3,3), (1,4,4), (1,5,5),
  (2,1,1), (2,2,2), (2,3,3), (2,4,4), (2,5,5);
INSERT INTO t1 SELECT f1, f2 + 5,f3 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10,f3 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20,f3 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40,f3 + 20 FROM t1;
ANALYZE TABLE t1;

create index idx_sum on t1(f1,f2);
EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

索引跳跃是依靠MySql优化器判断的不是绝对的！

多范围读取优化Multi-Range Read Optimization

深入浅出MySQL MRR（Multi-Range Read）-阿里云开发者社区

MySQL :: MySQL 8.4 参考手册 :: 10.2.1.11 多范围读取优化 --- MySQL :: MySQL 8.4 Reference Manual :: 10.2.1.11 Multi-Range Read Optimization

MRR 是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段。

第一点：索引列中若没有完全满足where 查询的需求的时候需要回表

第二点：索引列若完全满足where 查询的需求就是覆盖索引

问题出现了！

假如一行数据代表一页的话，会来回读取。产生大量的随机IO！

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

MRR优化的设计思路：

(1)根据索引找到满足条件的记录，把ID存放到read_rnd_buffer
(2)将ID进行递增排序
(3)排序后ID数组，依次到主键ID索引中查记录，作为结果返回

这里，read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的。

如果步骤1中，read_rnd_buffer放满了，就会先执行完步骤2和3，然后清空read_rnd_buffer。之后继续找索引的下个记录，并继续循环。

mysql> show variables like '%read_rnd_buffer_size%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_rnd_buffer_size | 262144 | 256k
+----------------------+--------+
1 row in set (0.00 sec)

开启MRR

set optimizer_switch='mrr_cost_based=on';

set optimizer_switch ='mrr_cost_based=off';

mrr_cost_based 设为 off，那优化器就会通通使用 MRR。建议这个配置还是设为 on，毕竟优化器在绝大多数情况下都是正确的。

MRR 的核心思想就是通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。

满足局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。

MRR 在本质上是一种用「空间换时间」的做法。

测试：

set optimizer_switch ='mrr_cost_based=off';

set optimizer_switch ='mrr_cost_based=on';

索引区分度

建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

区分度 = count(distinct(列)) / count(*)

因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比（惯用的百分比界线是"30%"）很高的时候，它一般会忽略索引，进行全表扫描。

基数为2，像这种性别列建索引就没有意义！扫到一部分就变成了全表扫描！

主动的更新基数：

mysql> analyze table student;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| test.student | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.04 sec)