第四篇：MySQL数据库索引及执行过程

数据库索引相关基础知识

  数据库基础

 数据库索引相关概念

• 索引是数据库中用来提高数据读取性能(select、update、delete)的工具
• 通过锁定查询范围，提高数据读取性能(主要是减少IO、CPU、内存的消耗)
• 数据库索引相当于书的目录，可以借助索引有针对的查看相应数据的信息，避免了全盘检索带来的工作量

 数据库常见的索引类型⭐

类型	说明
B+Tree	默认类型索引
Hash	算法类型索引
R+Tree	空间类型索引
Fulltext	全文类型索引

 MySQL InnoDB的段、区/簇、页⭐⭐⭐

存储单位	存储大小	作用
页(Pag)	默认大小：16KB	磁盘I/O的最小单位 存储实际数据、索引、管理信息
区/簇(Extent)	固定大小：1MB	分配存储空间的基本单位，用于减少随机I/O
段(Segment)	数量不固定，随着数据增长动态分配	表空间由各个段组成 段一般分为：数据段、索引段、回滚段 索引段：存放 B + 树的非叶子节点的区的集合； 数据段：存放 B + 树的叶子节点的区的集合； 回滚段：存放的是回滚数据的区的集合，

  B+Tree算法

• 遍历算法：全表查询
• 二叉树算法：消耗的IO不平衡，增加数的高度会增加IO的消耗

 B+tree算法底层算法逻辑⭐⭐⭐⭐⭐

• 将需要存储的数据信息，均匀分配保存到对应页当中，最终数据信息均匀存储（落盘）

• 根据页节点存储的数据信息，取出页节节点最小数据信息，并将每个叶节点最小数据信息进行汇总整合，生成相应内部节点数据；

  实质上存储的是下层页节点的区间范围，以及与之对应的指针信息，最后构建出内部节点信息；

• 根据内部节点存储的数据信息，取出内部节点最小数据信息，并将每个内部节点最小值信息进行汇总整合，生成相应根节点数据；

  根节点只能有占用一个页区域，如果一个页区域空间不够，需要进行内部节点层次扩展，但是尽量要保证层次越少越好；

  实质上存储的是下层内部节点的区域范围，以及与之对应的指针信息，最后构建出独立且唯一的根节点信息；

• 整个树形结构，越向上节点存储数据的范围越大，然后依次再分发数据到下面的小范围，最终形成多叉树；

  由于出现了多叉树，就表示全部数据分布在多个链表上，避免了单条链表存储数据，同时可以实现并发的访问数据

• 对于加号表示增强，其中增强表示在整个链表上，增加了同级相邻节点之间的双向指针，从而实现相邻节点相互跳转

 等值查询⭐⭐

• 根据定义查找的数值信息，首先在根节点中获取数值所在的区间范围和相应指针信息，从而找到下层对应的内部节点信息；
• 根据定义查找的数据信息，其次在枝节点中获取数值所在的区域范围和相应指针信息，从而找到下层对应的叶子节点信息；
• 根据定义查找的数据信息，最后在叶子节点中获取最终的数据信息；

在利用BTree查找数据信息时，会结合树形层次结构，来决定查询数据的步骤过程，并且理论上每个数据查找过程步骤相同；

总结：B代表的平衡含义就是，每次查找数据消耗的IO数量是一致的，并且读取的页数量也是一致的，查找时间复杂度是一致的；

 范围查询⭐⭐

• 根据定义查找的数值信息，首先在根节点中获取首个大于指定数值的区间范围和相应指针信息，从而找到下层对应的内部节点信息；
• 根据定义查找的数据信息，其次在枝节点中获取数值所在的区域范围和相应指针信息，并且结合双向指针进行预读；
• 根据定义查找的数据信息，最后在叶子节点中获取最终的数据信息，并且结合双向指针进行预读，查询其余大于指定数值的数据；

在利用BTree查找数据信息时，由于存在双向指针概念，可以避免重复从根查找问题，减少IO消耗，结合预读快速调取数据到内存中

总结：在BTree中的双向链接增强特性和预读功能，可以根据簇（64page）读取数据，可以使数据信息的范围查找变得更加方便高效

数据库索引构建⭐⭐⭐⭐⭐

创建聚簇索引会修改数据的物理存储顺序，而创建辅助索引只会生成一个独立的排序结构，不会改变表数据的物理存储

  随机I/O vs 顺序I/O⭐⭐

特性	顺序I/O (Sequential I/O)	随机I/O (Random I/O)
工作方式	读取磁盘上连续相邻的数据块	读取磁盘上分散在不同位置的数据块
硬件效率	非常高 磁头几乎不用移动，就像一口气读完一本书的一章	非常低 磁头需要频繁移动、寻道和旋转，就像不停翻字典查不同的单词
性能代价	低	极高（比顺序I/O慢几个数量级）
典型场景	全表扫描、索引的全扫描、大型排序	辅助索引的书签查找、单行点查询（如果行分散）

  聚簇索引/主键索引/集群索引/聚集索引⭐⭐⭐⭐⭐

 聚簇索引介绍

聚簇索引：将多个簇聚集在一起就构成了所谓的聚簇索引（将数据与索引存放在一起）
聚簇索引作用：将表数据物理有序地存储在磁盘上，使主键查询无需回表且范围查询效率极高

聚簇索引的层级存储结构：
    聚簇是多个簇，簇是多个连续数据页(64个)
    页是多个连续数据块(4个)
    块是多个连续扇区(8个)
利用聚簇索引可以实现从物理上或逻辑上都能满足数据存储的连续性关系，方便进行数据查找的有序性IO

一张表只能有一个聚簇索引

 聚簇索引的构建方式⭐⭐⭐⭐⭐

• 数据表创建时，显示的构建了主键信息（pk），主键（pk）就是聚簇索引；
• 数据表创建时，没有显示的构建主键信息时，会将第一个不为空的UK的列做为聚簇索引；
• 数据表创建时，以上条件都不符合时，生成一个6字节的隐藏列作为聚簇索引；

以上图信息为例，若显示创建ID列为pk自增列：

① 按照ID逻辑顺序，在同一个区中连续的数据页上，有序存储数据行；

② 数据行所在的数据页，作为聚簇索引的leaf（叶子节点）（叶子节点就是所有数据行）；

③ 叶子节点构建完后，可以构建no-left（支节点），用于保存的是leaf节点中的ID范围和指针信息；

④ 支节点构建完后，可以构建root（根节点），用于保存的是no-leaf节点中的ID范围和指针信息；

⑤ 并且leaf节点和no-leaf相邻数据页之间都具有双向指针，从而加速数据的范围查找；

  辅助索引/一般索引⭐⭐⭐⭐⭐

 辅助索引介绍

辅助索引主要用于辅助聚簇索引查询的索引，一般按照业务查找条件，建立合理的索引信息，也可以称之为一般索引
辅助索引的存储：调取辅助索引列信息+主键列的信息，存储在特定的数据页中
             创建辅助索引时会将索引列的值进行排序来构建独立的查找结构，但不会改变原始表数据的物理顺序
辅助索引作用：将需要查询的列信息和聚簇索引信息建立有效的关联，从而使数据查询过程更高效，节省IO和CPU消耗

1.利用辅助索引与聚合索引建立的关联
2.先经过辅助索引的查询获取对应聚簇索引
3.再经过聚簇索引回表查询获取详细数据

 辅助索引的构建方式⭐⭐⭐⭐⭐

• 数据表创建时，显示的构建了一般索引信息(mul)，一般索引信息(mul)就是辅助索引；
• 数据表创建时，没有显示的构建一般索引信息时，在查询检索指定数据信息，会进行全表扫描查找数据；

结合下图信息，可以看出辅助索引组织存储数据过程与加速查询过程原理：

以上图信息为例，若显示创建name列为mul查询列：

① 调取需要建立的辅助索引列信息，并加上相应主键列的所有信息，存储在特定的内存区域中；

② 根据调取的辅助索引列信息，进行字符的顺序排序，便于形成范围查询的区间，并将排序后的数据信息存储在特定数据页中；

③ 叶子节点构建完后，可以构建no-left（支节点），用于保存的是leaf节点中的字符范围和指针信息；

④ 支节点构建完后，可以构建root（根节点），用于保存的是no-leaf节点中的字符范围和指针信息；

⑤ 找到相应辅助索引的数据信息后，在根据辅助索引与聚簇索引的对应关系，获取到相应的主键信息，从而获取相应其他数据信息

在利用聚簇索引获取其他数据信息的过程，也可以称之为回表查询过程；

 辅助索引检索数据产生回表问题分析（回表次数越少越高）⭐⭐⭐⭐⭐

# 产生问题：
  ① 在回表过程中，有可能会出现多次的回表，从而造成磁盘IOPS的升高；（回表的过程本质上就是随机IO）
      二级索引返回的主键值 可能是离散的（例如 id=3, 7, 15, ...），导致回表时需要从磁盘的不同位置读取数据块。
  ② 在回表过程中，有可能会出现多次的回表，从而造成磁盘IO量的增加；

# 解决方法：
  ① 可以建立联合索引，调整查询条件，使辅助索引过滤出更精细主键ID信息，从而减少回表查询的次数；
  ② 可以控制查询信息，实现覆盖索引（让辅助索引直接包含查询所需的所有字段，避免回表）
  ③ 优化器算法做调整（MRR-多路读功能 ICP-索引下推功能 ）

 构建索引树高度问题分析（索引树高度越低越好）⭐⭐⭐⭐⭐

根节点只能占用一个页区间

# 影响索引树高度因素：
① 数据行数量会对高度产生影响；（3层BTREE -- 可以实现一般2000万行数据索引的存储-20~30列表）
  解决方法：可以拆分表 拆分库 或者实现分布式存储；

② 索引字段长度过大会对高度产生影响；（索引键长度越长，单个页（16KB）能存储的键值越少，导致树层级增加）
    解决方法：利用前缀索引、哈希后缀索引、用自增ID代替长字符串

③ 数据类型设定会对高度产生影响；
    解决方法：列定义时，选择简短合适的数据类型；

  聚簇索引和非聚簇索引的区别⭐⭐⭐⭐⭐

# 叶节点是否存放一整行记录
  聚簇索引：将数据与索引存储在一起，索引结构的叶节点保存了行数据
  非聚簇索引：将数据与索引分开存储，索引结构的叶节点指向了数据对应的位置

# 数据存储方式
  聚簇索引决定了数据行的物理存储顺序，它就是表本身
  非聚簇索引是一个独立的结构，只保存排序的键值和指向数据位置的指针，不改变数据的物理顺序

  数据库索引应用方法⭐⭐⭐⭐⭐

索引标识	解释说明
PK（PRI）	表示为聚簇索引，也可以理解为主键索引
K（MUL）	表示为辅助索引，也可以理解为一般索引
UK	表示唯一键索引

 数据库压力测试

• 在进行索引操作之前，可以进行一个压力测试，将测试数据库数据进行备份恢复：

# 进行测试数据恢复操作：
mysql> source ~/world-db/world.sql# 进行数据库程序服务压测：
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' \
--query="select * from world.city where name='Alkmaar'" engine=innodb --number-of-queries=2000 \
-uroot -proot -h172.16.1.51 -verbose 

--concurrency=100 并发连接数（同时模拟100会话连接） 
--iterations=1 迭代次数（整个测试需要重复多少次）

--create-schema='world'  指定操作的数据库信息；
--query="select * from world.city where name='Alkmaar'"  指定压测过程具体执行了什么语句操作
--number-of-queries=2000  所有客户端在一轮迭代中总共执行的查询次数(并发客户数×每个客户端每轮迭代查询次数)
-verbose  显示详细的测试信息

• 没设置索引前压力测试结果：

• 进行索引建立优化：

mysql> alter table world.city add index idx_name(name);

• 设置索引后压力测试结果：

 查询索引信息

mysql> show index from 表名;
mysql> desc 表名;

 创建索引信息

-- 创建主键索引
  alter table 表名 add primary key(列名);
-- 创建辅助索引
  alter table 表名 add index 索引名(列名);
-- 创建唯一键索引
  alter table 表名 add UNIQUE(列名)；
-- 创建联合索引方法
  alter table 表名 add index 索引名(列名1，列名2...);
-- 创建前缀索引方法
  alter table city add index 索引名(列名(10));

 删除索引信息

-- 删除索引信息（一般索引）
  alter table 表名 drop index 索引名;  -- 唯一索引的索引名为id    

-- 删除索引信息（聚簇索引）
  alter table 表名 drop primary key;

数据库服务执行计划⭐⭐⭐⭐⭐

• sql处理流程
  • SQL层通过解析器生成解析树（多种处理方案）
  • 优化器基于解析树生成最终执行计划
  • 执行引擎按照最优执行计划运行SQL
• 执行计划本质
  • 是SQL语句的最优执行方案
  • 展示数据如何被查询、过滤和获取

  执行计划获取（explain / desc）⭐⭐⭐

序号	字段	解释说明
01列	id	表示语句执行顺序，单表查询就是一行执行计划，多表查询就会多行执行计划；
02列	select_type	表示语句查询类型，sipmle表示简单(普通)查询
03列	table	表示语句针对的表，单表查询就是一张表，多表查询显示多张表；
05列	type ***	表示索引应用类型，通过类型可以判断有没有用索引，其次判断有没有更好的使用索引
06列	possible_keys	表示可能使用到的索引信息，因为列信息是可以属于多个索引的
07列	key	表示确认使用到的索引信息
08列	key_len ***	表示索引覆盖长度，对联合索引是否都应用做判断
10列	rows	表示查询扫描的数据行数（尽量越少越好），尽量和结果集行数匹配，从而使查询代价降低
11列	fltered	表示查询的匹配度
12列	Extra ***	表示额外的情况或额外的信息

  数据库索引应用类型（索引扫描方式类型）⭐⭐⭐⭐⭐

序号	类型	解释说明
01	ALL - ok	表示全表扫描方式，没用利用索引扫描类型；
02	index	表示全索引扫描方式，需要将索引树全部遍历，才能获取查询的信息（主键index=全表扫描）
03	range	表示范围索引方式，按照索引的区域范围扫描数据，获取查询的数据信息；
04	ref（非唯一索引扫描）	表示辅助索引(非唯一索引)等值查询，精准定义辅助索引的查询条件
05	eq_ref（唯一索引扫描）	表示多表连接查询时，被驱动表的连接条件是主键或者唯一键时，获取的数据信息过程；
06	const / system	表示主键或者唯一键等值查询，精准定义索引的查询条件

  扫描类型执行计划展示效果⭐⭐⭐⭐⭐

 扫描类型：ALL

1. 查找条件没有索引

2. 查询条件使用模糊查询
  主键索引：--all
  辅助索引：
    前缀匹配：like '字符%' --range 
    后缀匹配：like '%字符' --all 
    前后通配符：like '%字符%' --all

3. 查询条件使用的了排除法（not in-只针对辅助索引，不影响主键索引）

 扫描类型：index

查询只涉及索引列（覆盖索引），但是无 WHERE 条件

 扫描类型：range

1. 查找条件是范围信息（> < >= <= between...and in or）

2. 此类型出现原因：查找条件是模糊信息（辅助索引的前缀匹配 like '字符%'）

 扫描类型：ref

辅助索引(非唯一索引)等值查询

 扫描类型：eq_ref

MySQL驱动表和被驱动表说明：https://www.cnblogs.com/oldboy666/p/16892774.html

被驱动表的链表条件是主键或唯一键时

如何判断那张表为驱动表：
    左连接查询时，前面的表是驱动表，后面的表是被驱动表
    右连接查询时，后面的表是驱动表，前面的表是被驱动表
    内连接查询时，哪张表的数据较少，哪张表就是驱动表（join on 默认为内查询）
    当连接查询有where条件时，带where条件的表是驱动表

 扫描类型：const

查询的条件是主键或唯一键，并且是精确等值查询

  数据库索引覆盖长度⭐⭐⭐

通过执行计划中key_len列，判断联合索引覆盖长度(字节)

覆盖长度越长，则匹配度越高，回表查询的次数越少

 最大预留长度影响因素

# 最大预留长度：数据库中某个字段定义时允许存储数据的最大字符数或字节数，超过次限制的数据将被截断或拒绝

# 最大预留长度影响因素
    字符类型
    字符集
        GBK：中文每个字符占用2个字节，英文每个字符占用一个字节
        UTF-8：中文每个字符占用2个字节，英文每个字符占用一个字节
    是否可以为空

 不同数据类型最大预留长度计算结果

数据类型	字符集	计算结果
char(10)	utf8mb4	最大预留长度=4*10=40
	utf8	最大预留长度=3*10=30
varcher(10)	utf8mb4	最大预留长度=4*10=40 + 2字节 =42 (1-2字节存储字符长度信息)
	utf8	最大预留长度=3*10=30 + 2字节 =32 (1-2字节存储字符长度信息)
tinyint	N/A	最大预留长度=1（大约3位数） 2的8次方=256
int	N/A	最大预留长度=4（大约10位数） 2的32次方=4294967296
bigint	N/A	最大预留长度=8（大约20位数) 2的64次方=18446744073709551616
not null	N/A	在没有设置not null时，在以上情况计算结果再+1

  数据库联合索引应用⭐⭐⭐⭐⭐

# 联合索引建立异常分析
    联合索引建立没有问题，但是查询语句书写有问题，导致联合索引应用效果不好；
    查询语句书写没有问题，但是联合索引建立有问题，导致数据查询结果性能过低；

# 联合索引应用要遵循最左原则
    建立索引的时候，最左列使用选择度高的列（cardinality-重复值少的列/唯一值多的列）
    执行查询语句时，一定包含索引最左条件

 测试数据

-- 创建测试表
CREATE TABLE index_test (
    id INT PRIMARY KEY,
    num INT,
    k1 VARCHAR(10),
    k2 VARCHAR(10),
    dt DATETIME
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 插入10条示例数据
INSERT INTO index_test (id, num, k1, k2, dt) VALUES
(1, 100, 'ab', 'CD', '2023-01-01 10:00:00'),
(2, 100, 'ac', 'CE', '2023-01-01 10:01:00'),
(3, 200, 'ad', 'CF', '2023-01-01 10:02:00'),
(4, 200, 'ae', 'CG', '2023-01-01 10:03:00'),
(5, 300, 'af', 'CH', '2023-01-01 10:04:00'),
(6, 300, 'ag', 'CI', '2023-01-01 10:05:00'),
(7, 400, 'ah', 'CJ', '2023-01-01 10:06:00'),
(8, 400, 'ai', 'CK', '2023-01-01 10:07:00'),
(9, 500, 'aj', 'CL', '2023-01-01 10:08:00'),
(10, 500, 'ak', 'CM', '2023-01-01 10:09:00');

 联合索引全覆盖

• 需要满足最左原则；（尽量）
• 需要定义条件信息时，将所有联合索引条件都引用；（必要）

# 在不满足最左原则创建联合索引
  alter table index_test add index idx_num_k1_k2(num, k1, k2);
    -- key_len的最大预留长度：4+1 + 4*10+2+1 + 4*10+2+1 = 91

进行联合索引全覆盖时，索引条件的应用顺序是无关的，因为优化器会自动优化索引查询条件应用顺序；
desc select * from index_test WHERE num=100 AND k1='ab' AND k2='CD';  -- key_len=91

在进行联合索引全覆盖查询时，最后一列不是精确匹配查询，而是采取区间范围查询，也可以实现索引全覆盖查询效果；
explain select * from index_test where num=100 AND k1='ab' AND k2>'CD';  -- key_len=91

 联合索引部分覆盖

• 需要满足最左原则；（尽量）
• 需要定义条件信息时，将所有联合索引条件部分引用；

# 联合索引部分引用
  explain select * from index_test WHERE num=100 AND k1='ab';  -- key_len=48

# 临时关闭索引下推
  set global optimizer_switch='index_condition_pushdown=off';
    -- 实现测试练习完，需要恢复开启
  show variables like '%switch%';

进行联合索引覆盖查询时，区间范围列不是最后一列，索引查询匹配只统计到区间范围匹配(不等值)列，也属于部分覆盖；
EXPLAIN SELECT * FROM index_test WHERE num=100 AND k1 > 'ab' AND k2='CC';  -- key_len=48

进行联合索引覆盖查询时，查询索引列是不连续的，索引查询匹配只统计到缺失列前，也属于部分覆盖；
EXPLAIN SELECT * FROM index_test WHERE num=100 AND k2='CD'; -- key_len=55

 联合索引完全不覆盖

• 需要定义条件信息时，将所有联合索引条件都不做引用

desc select * from index_test;

进行联合索引全不覆盖查询时，区间范围列出现在了第一列，也属于全不覆盖索引
desc select * from index_test where num<200;  --key_len=5

进行联合索引全不覆盖查询时，缺失最左列索引条件信息时，也属于全不覆盖索引
EXPLAIN SELECT * FROM index_test WHERE k1='ab';  --key_len=NULL

  数据库索引扩展信息⭐⭐⭐⭐⭐

Extar列表示额外的情况或额外的信息说明

filesort 表示涉及到额外排序操作，将严重浪费CPU资源；

 Extra常见值

Extra 值	含义	解读与建议
Using index	使用了覆盖索引 查询的列都包含在索引中，无需回表查找数据行	性能极佳的标志 说明索引设计高效，是理想的优化状态
Using where	在存储引擎层检索行后，Server 层再次进行了过滤	表示索引可能部分覆盖了查询条件，或者条件中有索引之外列的筛选 需要结合 type 和 key_len 判断索引使用效率
Using index condition	使用了索引下推 (ICP) 存储引擎层直接利用索引来执行 WHERE 子句中部分的过滤条件	性能良好的标志 减少了需要回表或传给 Server 层的数据量，是对 Using where 的一种优化
Using temporary	需要创建临时表来处理查询，常见于 GROUP BY 和 ORDER BY 子句	性能警示信号 临时表通常在磁盘创建，代价高昂 应考虑为 GROUP BY/ORDER BY 的列添加合适的索引
Using filesort	需要额外的排序操作 无法利用索引直接完成排序	性能警示信号 排序操作可能在内存或磁盘进行，效率较低 建议为 ORDER BY 子句中的列创建索引
Select tables optimized away	查询已被优化到无需执行表扫描的程度	性能极佳的标志 优化器非常聪明地利用了索引的特性
Using join buffer (Block Nested Loop)	需要使用连接缓冲区 通常发生在表连接时，驱动表没有索引可用或索引效率不高	性能警示信号 连接操作效率较低，应检查连接字段是否已添加索引

 涉及到排序操作的查询

• 情况一：查询语句中含有 order by ，表示触发式的排序；
• 情况二：查询语句中含有 group by，表示隐藏式的排序；
• 情况三：查询语句中含有 DISTINCT，表示会先进行排序后再取消重复；

 利用order by实现排序

mysql> desc select * from city where countrycode='CHN' order by population;

# 错误设想创建索引：因为本身索引构建过程就存在自动排序问题
    alter table city add index idx(population)

# 失败原因
    没有改变最终的执行计划结果
      在使用索引时，只能使用单一的索引树，不能跨越多颗索引树进行使用

# 正确优化处理方式：创建联合索引
    alter table city add index idx1(countrycode,population);

 利用group by实现排序

特殊情况说明：在order by信息出现在group by之后，是无法实现索引优化处理的

因为group by操作后，已经将数据信息存放在了临时表中，order by排序就不能再用索引了

数据库索引扩展知识 

  建立索引原则规范（DBA运维规范）⭐⭐⭐⭐⭐

• 数据表中必须要有主键索引（创建表时指定），建议是与业务无关的自增列；
  • 自增主键的顺序写入避免了页分裂和随机I/O，从而保证了极高的写入性能和紧凑的存储结构
  • 随机IO：数据写入到磁盘上不连续的物理位置，需要磁头频繁移动寻址，导致写入速度慢
  • 页分裂：当新数据无法按顺序插入到已满的数据页时，InnoDB会将原页分裂成两个半满的页，导致性能下降和存储碎片0
• 数据表中某些列若经常作为 where/order by/group by/join on/distinct条件信息，最好将相应列设置索引（产品功能/用户行为）
• 数据表中最好使用唯一值多的列作为索引，如果索引列重复值较多，可以考虑使用联合索引（最左列-减少回表次数 - 减少磁盘IO）
• 数据表中列值长度较长的索引列，建议可以使用前缀索引（防止索引树层次过高）
• 数据表中不建议建立大量索引，最好降低索引条目，不要创建无用索引，不常用的索引要定期清理（percona toolkit）
• 数据表中的索引信息做调整维护时，尽量避开业务繁忙期，或者通过软件工具做调整维护（pt-ost）
• 数据表中的联合索引创建过程要遵循索引最左原则

  索引失效情况分析与规范⭐⭐⭐⭐⭐

# 没有设置查询条件
    select * from t1;
# 查询的条件没有建立索引
    select * from t1 where non_indexed_column='value';
# 错误的条件导致索引失效
    select * from t1 where id=1001 or 1=1;  -- SQL注入
# 对索引列参与计算或函数运算
    SELECT * FROM t1 WHERE YEAR(create_time) = 2023; -- 索引失效
# 使用前导模糊查询（ like '%abc' ）
    SELECT * FROM t1 WHERE name LIKE '%abc'; -- 索引失效
    SELECT * FROM t1 WHERE name LIKE 'abc%'; -- 索引可能有效
# 不符合最左前缀原则（针对联合索引）
    索引是(a,b,c)，但查询条件是 where b='xx' and c='yy'
# 在索引上使用NOT（!= <>）
    select * from t1 where status != 'active';

  索引失效情况处理⭐⭐⭐⭐⭐

• 当频繁的对数据表中索引列值做修改、删除等操作时，会导致索引统计信息过旧或不真实，最终造成索引功能失效；
• 索引本身是有自我维护的机制能力，但并不是实时调整更新的，需要有一定的间隔时间做调整；
• 一般索引失效的表现情况为：select查询语句平常查询时很快，但突然某天执行就变慢了，就是索引失效了，统计数据不真实；

# 索引统计的信息存储位置（mysql库中相应的表）
  innodb_index_stats
  innodb_table_stats

# 当索引失效时，可以使用命令重新进行统计信息获取，使索引功能再次生效
  mysql > analyze table world.city;  -- 表示立即更新过久的统计信息（也可以将索引删除重建）

# 索引列参与计算或函数运算都会导致索引功能失效
  desc select * from world.city where id-1=9;  -- ALL（错误举例）
  desc select * from world.city where id=10;  - const（正确举例）

# 在查询数据信息过程中，出现了隐式转换也会导致索引失效
  # 创建测试数据表
    mysql> create table test (id int,name varchar(20),telno char(11));
    mysql> insert into test values (1,'a','110'),(2,'b','123'),(3,'c','120'),(4,'d','119'),(5,'e','130');
  # 创建索引信息
    mysql> alter table test add index idx(telno);
​  # 查询数据信息
    desc select * from test where telno='110';  -- ref
    desc select * from test where telno=110;  - ALL
  因为本身查询条件列的数据类型为字符类型，但是作为条件时当成了数字类型，数据库会将数值类型通过隐式转换函数转换为字符类型；
  由于，条件中若加上了函数信息，就会导致索引功能失效，所以隐式转换也会造成索引失效；

# 在查询条件过程中，应用了特殊数据匹配方法时，也会导致索引失效，一般是辅助索引失效；
  <> , not in , like "%_" 
  -- 应用以上特殊符号信息，也会导致辅助索引失效

  数据库服务索引功能特征⭐⭐⭐

 MySQL8.0后支持不可见索引

# 案例：在企业中，对某个业务中的表做了调整
    添加一个列，给该列创建索引
    批量修改列中数据，操作一半报错中断（触发索引锁机制）

# 设置所有需要跳过的索引为不可见
    ALTER TABLE your_large_table ALTER INDEX idx_1 INVISIBLE/VISIBLE;
    ALTER TABLE your_large_table ALTER INDEX idx_2 INVISIBLE/VISIBLE;

 MySQL8.0后支持倒序索引

# 在早期数据库中，所有索引列创建索引信息，都是按照从小到大顺序进行排序，在最新数据库中，可以灵活调整索引排序方式；
index(countrycode,id)
对countrycode列进行正向排序，对id列进行逆向排序
select * from city order by countrycode,id desc limit 10;

  数据库服务自主优化能力⭐⭐⭐⭐⭐

 AHI--自适应的hash索引/散列索引（索引的索引）⭐⭐⭐⭐⭐

• 用于在内存中建立索引，快速锁定内存中的热点数据索引页位置
• 对于数据库服务而言，想要读取数据信息，也是会从磁盘中读取存储页，在放入内存中被数据库服务进行访问，索引访问也是一样的；
• 对于数据库服务而言，想要读取数据信息，也是会从磁盘中读取存储页，在放入内存中被数据库服务进行访问，索引访问也是一样的；
• 但是当数据页大量的被存放在内存中后，从大量内存中的数据页找到想要的，也是比较困难的事情；
• 因此，可以对内存中经常被访问数据索引页建立一个hash索引，从而可以帮助数据库服务快速定位内存中想要找的索引数据页；

mysql> show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+

 CHANGE BUFFER（mysql 5.6）⭐⭐⭐⭐⭐

在数据表中插入 修改 删除数据时，聚簇索引树会进行同步实时更新，辅助索引树会进行异步延时更新

change buffer主要是针对辅助索引的缓冲区，属于内存结构上的应用

changerbuffer应用原理：假设现在需要插入一行数据信息
① 插入一行数据信息到表中，将会实时立即更新聚簇索引信息，因为利用聚簇索引是用来获取数据页上详细原表数据信息的；
② 插入一行数据信息到表中，不会实时立即更新辅助索引信息，因为利用辅助索引是用来获取索引页上聚簇索引数据信息的；
如果此时实时更新了辅助索引的信息，有可能会导致出现数据页分裂，造成辅助索引树结构变化，形成索引树访问阻塞(锁机制)；
③ 为了避免辅助索引树结构变更，对数据库服务并发访问的影响，可以将插入的数据信息，暂时存储在缓冲区中；
当利用辅助索引检索数据时，可以将检索到数据页范围信息调取到内存中，与缓存区数据进行合并，自然可以检索到插入的数据；

mysql> show variables like '%change_buffer%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| innodb_change_buffer_max_size           | 25    |
| innodb_change_buffering                 | all   |
+-----------------------------------------+-------+
--all：    默认值，开启buffer inserts、delete-marking operations、purges
--none：   不开启change buffer

 ICP--索引下推（mysql 5.6）⭐⭐⭐⭐⭐

主要针对联合索引起作用
# ICP的核心思想：
    ICP允许存储引擎在读取索引时，直接利用索引中的列来过滤数据，
    从而减少需要回表的次数和需要上传到Server层的数据量，降低了内存、CPU和I/O开销，提升了查询性能

# ICP应用原理：
假设创建联合索引进行数据检索 
idx(a,b,c) 
where a=10 and b like '%x%' and c=z 
  # 无ICP时（性能低下）：先回表再过滤
    引擎层检索：存储引擎根据最左前缀 a=10 在索引 idx(a,b,c) 中定位记录，进行回表查询获取满足条件的完整数据行
    全量传输：引擎将所有满足 a=10 的记录完整数据全部加载并传输到MySQL Server层的内存中。此时Server层内存中存在大量最终不需要的冗余数据。
    Server层过滤：在Server层，由SQL优化器对接收到的每一条记录，应用 b LIKE '%x%' AND c=z 条件进行过滤返回需要查询的信息
  说明：基于数据库优化器的特性，遵循联合索引引用原则，SQL层面只能检索到联合索引中的A；

  # 有ICP时（性能优化）：先过滤再回表
    引擎层检索：存储引擎同样根据最左前缀 a=10 在索引中定位记录。
    引擎层过滤（ICP核心）：不立即返回数据。存储引擎直接利用当前索引页中已经存在的 b 和 c 列的值，在引擎层内部执行 b LIKE '%x%' AND c=z 的条件过滤。
    精简传输：只有那些在引擎层就同时满足 a=10, b LIKE '%x%', c=z 的记录的主键ID，才会被返回给Server层。
    回表查询：Server层拿着这个已经大幅精简过的主键ID列表，命令存储引擎回表查询。需要回表的次数极大减少。
  说明：基于数据库优化器的特性，可以将SQL层完成不了的检索工作，下推给引擎层完成，从而减少磁盘IO消耗，以及回表策略

ICP功能配置信息

mysql> show variables like '%switch%';
mysql> set global optimizer_switch='index_condition_pushdown=off';
-- 实现测试练习完，需要恢复开启（操作可以省略）
-- 执行计划extra列显示using index condition信息，表示应用了索引下推 

 MRR（Multi-Range Read Optimization 多范围读取操作）⭐⭐⭐⭐⭐

简单来说：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。

描述说明中涉及到的问题：

① 为什么要把随机读转换为顺序读？为了极大减少磁盘I/O操作的成本（寻道时间和旋转延迟）

② 为什么顺序读就能提升读取性能？顺序读最大限度地利用了磁盘的带宽，避免了耗时的机械操作

③ 如何将随机读去转换为顺序读取？ MRR

MRR功能配置信息:
  set optimizer_switch='mrr=on';
  set global optimizer_switch='mrr_cost_based=off';

mrr=on：这是基础，允许使用 MRR 功能
mrr_cost_based=off：这个非常关键。默认是 on，意思是“优化器自己基于成本估算决定是否使用 MRR”。但优化器的估算有时会保守或错误，可能认为不值得用而放弃使用。
通过设置为 off，你是在告诉优化器：“只要能用 MRR，就给我用！不要计算成本了！” 这在对二级索引进行大量范围扫描回表时（比如你的示例），通常能获得稳定的性能提升

SET SESSION read_rnd_buffer_size = 64*1024*1024; -- 设置 64MB
read_rnd_buffer_size 是用于排序主键的缓冲区大小

查询二级索引 --> 收集主键 --> 缓存并按主键排序 --> 按序回表查询

• 查询二级索引：通过辅助索查找到满足所有条件的主键ID
• 收集主键：MySQL 不会立即为每一个主键 ID 进行回表查询，而是先将这批主键 ID 收集起来
• 缓冲与排序：将收集到的主键 ID 缓存到 read_rnd_buffer 中，并按照这些 ID 在聚簇索引中的物理存储位置（页号）进行排序（排序后的ID顺序，非常接近实际磁盘上的存储顺序）
• 按需回表查询：系统依据排好序的主键ID列表，按物理存储顺序访问聚簇索引。这一方式将原本的随机I/O转换为顺序I/O，显著减少了磁盘寻道时间，从而大幅提升查询性能