MySQL索引（基础篇）

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

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

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

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

索引常见面试题 | 小林coding

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

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

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

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

---

实验环境

MySQL5.7、MySQL9.0.1

explain

神奇的 SQL 之 MySQL 执行计划 → EXPLAIN，让我们了解 SQL 的执行过程！MySQL explain - 掘金

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

id列

输出的是整数，用来标识整个SQL的执行顺序
值如果相同，从上往下依次执行；值不同，值越大，执行优先级越高，越先被执行
如果行引用其他行的并集结果，则该值可以为NULL

select_type列

SIMPLE：简单的SELECT查询，没有UNION或者子查询，包括单表查询或者多表JOIN查询

PRIMARY： 最外层的select查询，常见于子查询或UNION查询 ，最外层的查询被标识为PRIMARY

UNION：UNION操作的第二个或之后的SELECT，不依赖于外部查询的结果集（外部查询指的就是PRIMARY对应的SELECT）

DEPENDENT UNION：UNION操作的第二个或之后的SELECT，依赖于外部查询的结果集

UNION RESULT：UNION的结果（如果是UNION ALL则无此结果）

SUBQUERY：子查询中的第一个SELECT查询，不依赖于外部查询的结果集

DEPENDENT SUBQUERY：子查询中的第一个select查询，依赖于外部查询的结果集。

DERIVED：派生表（临时表），常见于FROM子句中有子查询的情况

type列

1. all：
   • 描述：全表扫描，即数据库引擎会扫描整个表来查找符合条件的行。
   • 原因：未命中索引或索引失效，可能是因为查询条件无法利用索引，或者表中根本没有创建索引。
   • 性能：通常较差，特别是当表的数据量很大时。
2. system：
   • 描述：当表中只有一行数据时，查询会采用这种类型。
   • 原因：表非常小，只有一个数据行。
   • 性能：非常好，因为只需要读取一行数据。
3. const：
   • 描述：当前SQL查询条件中的值是唯一的（通常是通过主键或唯一索引进行查找）。用于primary key或unique索引中。
   • 原因：查询条件直接命中了索引中的唯一值。
   • 性能：非常好，因为只需要读取一行数据，且查找速度非常快。
4. range：
   • 描述：使用索引检索给定范围内的行。前提是必须基于索引。
   • 原因：用索引来检索给定范围的行，当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，则会使用range.
   • 性能：比全表扫描好，但性能取决于索引的选择性和范围的大小。
5. eq_ref：
   • 描述：对于每个来自于前面的表的行，从该表最多只返回一条符合条件的记录。当连接使用的索引是PRIMARY KEY或UNIQUE NOT NULL时出现，非常高效
   • 原因：连接条件中使用了主键或唯一索引，且连接是等值的（即使用=操作符）。
   • 性能：较好，因为每次连接都只需要读取一行数据。
6. ref：
   • 描述：索引访问，也称索引查找，它返回所有匹配某个单个值的行。此类型通常出现在多表的JOIN查询, 针对于非UNIQUE或非PRIMARY KEY, 或者是使用了最左前缀规则索引的查询，换句话说，如果JOIN基于关键字不能选择单个行的话，则使用ref
   • 原因：查询条件中使用了普通索引，且不是唯一索引。
   • 性能：取决于索引的选择性和匹配的行数。
7. index：
   • 描述：全索引扫描，即数据库引擎会扫描整个索引来查找符合条件的行，而不是扫描整个表。
   • 原因：查询条件中的列是索引的一部分，但查询条件未能充分利用索引（例如，查询条件只包含了索引的一部分列）。
   • 性能：通常比全表扫描好，因为索引通常比表小，但性能也取决于索引的大小和选择性。
8. index_merge：该访问类型使用了索引合并优化方法

9. unique_subquery：类似于两表连接中被驱动表的eq_ref访问方式，unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键或者唯一索引进行等值匹配时，则会使用unique_subquery

10. index_subquery：index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通索引

Extra列

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

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

全表扫描

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

1. 找到本地表数据文件中的起始地址。
2. 会发生磁盘IO，第一行数据开始读，读到内存中。
3. MySQL-Server会根据SQL条件对读到的数据做判断。
4. 如果不符合条件则继续发生磁盘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

二叉搜索树

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

创建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优化器选的路。

聚簇和非聚簇索引

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

聚簇索引中，索引数据和表数据在磁盘中的位置是一起的。

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

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

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

总结一下就是：

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

单列索引和联合索引

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

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

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

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

单列实验

单列索引环境搭建

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 索引列

先记住using index走的就是覆盖索引，ref代表着索引等值查找。

 select 非索引列 where 索引列

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

个人理解：先走name索引树，随后通过name索引树叶子节点主键，找聚簇索引树随后到表中寻找数据返回。

 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是全局有序的。

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

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

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

联合索引范围查询

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

drop table t1;
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;

key_len 

> 

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

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

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

>=

 为什么多了8字节？

select f1,f2 from t1 where f1>=10 and f2=10;
包含了select f1,f2 from t1 where f1=10 and f2=10;
我们知道f1相同的时候，f2是有序的！

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

between

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

like%

drop table student;
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);

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

 stu_name 和 stu_age都用到了联合索引

数据库表使用了 utf8mb4 字符集key_len = 255 * 4 + 2(行格式compact变长字段列表长度) + 4（int）

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

主键索引

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

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

select 主键ID where 主键索引

const代表着返回唯一值 

 select 主键ID where 普通索引

index代表全索引扫描 

 select 主键ID where 无索引列

all代表全表扫描

 可以随机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 索引列

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

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

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

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

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

key_len长度（二周目）

记住这张数据表哦！ 

 

name列

 64 x 4 + 2(变长字段列表长度) = 258

name列age列 

mysql> insert into staff values(9,22222,11,27,'小李');
Query OK, 1 row affected (0.02 sec)

64 x 4 + 2(变长字段列表长度) + 4= 262

name 相同的情况，age 有序！

 name列age列city列

 64 x 4 + 2(变长字段列表长度) + 4 + 64 x 4 + 2(变长字段列表长度)= 520

第一行代码：

name 相同的情况下，age有序！

but！age 不同 age 是 < 22 导致了 city列不能使用联合索引 

因此  64 x 4 + 2(变长字段列表长度) + 4= 262

第二行代码：

name 相同的情况下，age有序！

but！age 不同 age 是 <= 22 city 在age = 22 可以过滤数据可以使用联合索引

因此  64 x 4 + 2(变长字段列表长度) + 4 + 64 x 4 + 2 = 520

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

【IT老齐252】MySQL关键特性索引下推ICP是做什么的？_哔哩哔哩_bilibili

只有二级索引才有ICP 

简单来说就算将可以利用索引筛选的where条件在存储引擎一侧进行筛选，而不是放到server端进行where筛选。

mysql> select @@optimizer_switch\G;

 环境搭建

drop table student;
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);

实验

联合索引（name , age , phone） 

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

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

Using where 回表！

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

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

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

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

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

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

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

因为where 条件里面还有一个 stu_sex 需要回到原表查看数据

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

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

 第一段代码：name phone在索引列 ，会把stu_phone条件下推放到索引判断，des 不在索引列 会通过回表查找phone和des，而有了索引下推就会把phone放在索引表中查询完以后再去回表，节约了磁盘IO。

这里有点疑问待续 

like%

第一段代码:

采用模糊查询是肯定不会使用索引的！ 

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

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

第二段代码:

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

使用索引下推，MySQL在读取完整表之前会检查stu_phone like ('1888888888%')部分，避免了读取与stu_phone条件不匹配的索引对应的完整行。

执行流程

开启索引下推

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联合索引不一定要求最左匹配？跳跃索引！_复合索引不一定走最左-优快云博客

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'

环境搭建

drop table t1;
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执行过程如下：

1. 获取f1字段第一个唯一值，也就是f1=1
2. 构造f1=1 and f2 > 40，进行范围查询
3. 获取f1字段第二个唯一值，也就是f1=2
4. 构造f1=2 and f2 > 40，进行范围查询
5. 一直扫描完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

Multi Range Read Optimization - MariaDB Knowledge Base

Multi Range Read can be used with range access

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

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

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

问题出现了！

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

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

MRR优化的设计思路：

• (1)根据索引找到满足条件的记录，看上图把比如name建了索引，把主键ID和name列存放到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)

 read_rnd_buffer里面到底有啥？

Multi Range Read Optimization - MariaDB Knowledge Base

看这篇文章我又看到MMR放的是rowid排序。

有点像order by的rowid排序，因为我没看过源码不知道read_rnd_buffer到底有什么（等哥们变强以后回来补档！）

其实我觉得只需要知道read_rnd_buffer排序后，是按照顺序访问磁盘的。 

开启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)

 总结

所有结论都需要反复测试！如果有错误欢迎指正！一起努力！

如果喜欢的话，请点个赞吧就算鼓励我一下！