Mysql索引:从入门到茅塞顿开

站长

2023年07月05日 09:55 · 阅读数 43

InnoDB索引结构:B+树

InnoDB使用B+树（Balanced Tree）数据结构来实现索引。B+树是一种树形的数据结构，非叶子节点只存储索引值，叶子节点才存储数据的值，叶子节点从小到大有序，形成链表。

Mysql索引:从入门到茅塞顿开

这种结构有两个优点：

磁盘读取是以数据块为单位读的，B+树的结构刚好与磁盘的访问模式相适配。（这种结构能够让查询过程读尽可能少的数据块即一次IO能读取更多的数据）
平衡区间查询和等值查询，写入和读取的时间。

索引结构选型

索引结构	磁盘读取性能	范围查询	插入删除性能	优点	缺点
哈希表	弱	弱	强	查找某个key的时间复杂度为O(1)	1. 解决哈希碰撞的问题 2. 不支持高效的范围的查找
二叉树	弱	弱	中	查找时间复杂度为 log(n),支持一定程序的范围查找	极端情况下会退化为线性链表，二分查找也会退化为遍历查找，时间复杂退化为 O（N），检索性能急剧下降。
红黑树	弱	弱	中	相比二叉查找树不会存在退化为线性链表的情况	但是树并不是严格平衡，树也会出现左倾或右倾的情况且树频繁调整十分消耗性能
AVL树	弱	中	中	不错的查找性能（O（logn）），不存在极端的低效查找的情况。可以实现范围查找、数据排序。	我们每一个树节点只存储了一个数据，我们一次磁盘 IO 只能取出来一个节点上的数据加载到内存里。磁盘 IO 有个有个特点，就是从磁盘读取 1B 数据和 1KB 数据所消耗的时间是基本一样的。
B+ 树	强	强	中	优秀检索速度，时间复杂度：B 树的查找性能等于 O（h*logn），其中 h 为树高，n 为每个节点关键词的个数；尽可能少的磁盘 IO，加快了检索速度；可以支持范围查找。

索引类型

概念:聚簇索引（Clustered Index）与非聚簇索引（Secondary Index）

核心区别：聚簇索引决定数据的物理存储顺序

Mysql索引:从入门到茅塞顿开

聚簇索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚簇索引。聚簇索引的叶子节点存储的是实际的数据。因此，聚簇索引的表现形式是数据存储与索引放到了一块。

InnoDB表必须具有聚簇索引。如果没有显式定义主键，InnoDB会自动选择一个唯一非空索引作为聚簇索引，或者生成一个隐式的ROWID作为聚簇索引。

Mysql索引:从入门到茅塞顿开

非聚簇索引（Secondary Index）: 表中的顺序与实际物理顺序是不一样的，叶子节点存储的也不是实际的数据，而是主键。因此，非聚簇索引的表现形式为数据与索引是分开存储。

联合索引

MySQL中的联合索引（Compound Index），也称为复合索引或多列索引，是一种将多个列组合在一起创建的索引类型.

假设有一个用户表"users"，包含了以下几个列：id、name、age、gender、city。现在我们经常需要按照年龄和城市来查询用户信息，那么可以在这两个列上创建一个联合索引

CREATE INDEX age_city_idx ON users (age, city);

联合索引遵循最左匹配，遇到非等值判断时匹配停止。(注意，in是属于等值判断)

主键索引

主键是一种特殊的唯一索引，用于标识表中的每个记录。主键列中的值必须是唯一的，并且不能为NULL。在MySQL中，可以使用AUTO_INCREMENT关键字来为主键列生成唯一的、自增的值。

CREATE TABLE users (
  id INT unsigned NOT NULL  AUTO_INCREMENT,
  name VARCHAR(255),
  age INT,
  PRIMARY KEY (`id`),
);

我们在"id"列上创建了一个主键，并使用AUTO_INCREMENT关键字使MySQL自动生成唯一的、自增的值。

`DB_ROW_ID`

当表没有定义主键时，InnoDB 会使用 DB_ROW_ID 作为聚簇索引（主索引）的键值。

InnoDB 维护了一个全局的 dict_sys.row_id 值，所有无主键的 InnoDB 表，每插入一行数据，都将当前的 dict_sys.row_id 值作为要插入数据的 row_id，然后把 dict_sys.row_id 的值加 1。

在 InnoDB 逻辑里，申请到 row_id=N 后，就将这行数据写入表中；如果表中已经存在 row_id=N 的行，新写入的行就会覆盖原有的行。

唯一索引

唯一索引是用来保证表中某些列的唯一性，也就是说，在唯一索引列中的任何值都不能重复出现。如果重复插入相同的值，则会触发唯一性约束，导致插入失败。

与主键索引相比，主键是一种特殊的唯一索引，用于标识表中的每个记录，而唯一索引则是用来保证表中某些列的唯一性。

CREATE TABLE users (
  id INT unsigned NOT NULL  AUTO_INCREMENT,
  name VARCHAR(255),
  email VARCHAR(255),
  age INT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_email` (`email`)
);

我们在"email"列上创建了一个唯一索引，这意味着在"email"列中的任何值都不能重复出现。如果尝试插入重复的"email"值，MySQL会触发唯一性约束，导致插入失败。

唯一索引（Unique Index）不允许具有相同的非 NULL 值，但是允许有多个 NULL 值。
值为NULL的索引会被放在B+树的最左边

前缀索引

MySQL中的前缀索引是一种特殊的索引类型，它只包含列值的前缀部分，而不是整个列值。通过使用前缀索引，可以减少索引的大小，从而提高查询性能和索引效率。

mysql> alter table SUser add index index2(email(6));

在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

# 首先，你可以使用下面这个语句，算出这个列上有多少个不同的值：
mysql> select count(distinct email) as L from SUser;
# 依次选取不同长度的前缀来看这个值，比如我们要看一下 4~7 个字节的前缀索引
mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;
# 设定自己可以接受的损失比例然后调整

除上述索引外,mysql还有全文索引和空间索引,但是由于性能问题,在实践中使用较少.

在实践中，如果需要进行文本搜索和地理位置查询，通常会选择使用专门的搜索引擎，如Elasticsearch、Solr等，

索引机制

主键索引和普通索引: 普通索引需要多一次回表的操作

change buffer机制

MySQL中的Change Buffer机制是一种优化技术，用于提高写入操作的性能。它在内存中维护了一个缓存，用于暂存对非聚集索引的修改，而不是立即将这些修改写入磁盘。当查询要使用到这些修改时，Change Buffer会将缓存中的修改应用到磁盘上的索引中，从而避免了频繁的磁盘写入。

以下是Change Buffer机制的工作原理：

将修改操作缓存到Change Buffer：当执行INSERT、DELETE或UPDATE操作时，如果它们会对某个非聚集索引进行修改，那么MySQL会将这些修改操作暂存到Change Buffer中，而不是立即写入磁盘。
将缓存的修改应用到磁盘上的索引：当查询需要使用到受到Change Buffer影响的索引时，MySQL会将Change Buffer中的修改应用到磁盘上的索引中，从而保证数据的一致性。
限制Change Buffer的大小：为了避免Change Buffer占用过多内存，MySQL会限制Change Buffer的大小。如果缓存中的修改已经达到了阈值，那么MySQL会将缓存中的修改写入磁盘，以释放内存空间。

需要注意的是，Change Buffer机制只适用于非聚集索引，因为聚集索引是按照主键顺序存储的，不需要使用Change Buffer来优化写入性能。此外，Change Buffer机制也需要考虑到缓存的大小和写入磁盘的频率，以避免对查询性能造成负面影响。

索引、联合索引失效

不满足最左前缀原则

联合索引遵循最左前缀原则，即查询时必须从联合索引的最左侧列开始使用。如果查询条件没有涵盖索引的最左侧列，索引将无法使用。例如，如果有一个 (A, B, C) 的联合索引，查询条件仅包含 B 和 C 时，索引无法使用。

遇到非等值查询条件

如果联合索引中的某一列是非等值查询条件（例如 >、<、>=、<=、BETWEEN、IN 等），则该列右侧的其他列将无法使用索引。

使用 `OR` 语句：

当查询中包含 OR 语句时，MySQL 可能无法充分利用联合索引。例如，如果有一个 (A, B) 的联合索引，查询条件为 A = 1 OR B = 2，则索引可能无法充分使用。

索引列参与计算或函数：

如果查询条件中的索引列参与了计算或函数操作，索引可能无法使用。例如，如果有一个 (A, B) 的联合索引，查询条件为 A + 1 = 5 AND B = 2，则索引无法使用。

隐式类型转换

如果查询条件中的数据类型与索引列的数据类型不匹配，可能导致隐式类型转换。这种情况下，索引可能无法使用。例如，如果有一个 (A, B) 的联合索引，其中 A 列的数据类型为 INT，而查询条件为 A = '1' AND B = 2，则索引可能无法使用。

全表扫描更高效

在某些情况下，即使查询条件满足联合索引的使用，MySQL 优化器也可能认为全表扫描更高效。这种情况通常发生在大部分数据行都满足查询条件时。

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

覆盖索引（Covering Index）

覆盖索引（Covering Index）是 MySQL 中一种特殊的索引机制。它是指一个查询可以通过使用索引本身的数据而无需访问数据表中的实际数据行(回表)来获得所需的所有信息。

举个例子，假设有一个数据表 orders，包含以下列：id, customer_id, order_date, total_price。

现在有一个查询需要根据 customer_id 查找 order_date 和 total_price，可以创建一个覆盖索引：CREATE INDEX idx_customer_id_order_date_total_price ON orders(customer_id, order_date, total_price)。

这样，在执行查询时，MySQL 可以直接使用索引数据，而无需访问数据表本身。

索引下推(Index Condition Pushdown，ICP)

索引下推（Index Condition Pushdown，简称 ICP）是一种查询优化技术，可以提高使用索引扫描的查询性能。在 MySQL 5.6 及更高版本中，ICP 已成为默认的优化策略。ICP 的主要目的是在索引扫描过程中更早地过滤不满足条件的记录，从而减少需要访问的数据行数量。

在没有启用 ICP 之前，MySQL 在执行查询时通常会遵循以下步骤：

从索引中读取符合条件的记录。
根据索引指向的数据行地址，访问数据表中的数据行。
应用 WHERE 子句中的其他过滤条件。

当启用 ICP 后，MySQL 可以在第一步就应用部分 WHERE 子句中的过滤条件，减少需要访问的数据行数量。这样，ICP 可以减少磁盘 I/O 和 CPU 计算，从而提高查询性能。

需要注意的是，并非所有的查询都可以从 ICP 中受益。ICP 主要在以下场景中有效：

当查询包含多个过滤条件时，且部分条件可以通过索引进行过滤。
当使用覆盖索引的查询中，可将索引中的列作为过滤条件。
当索引列的数据类型与过滤条件中的数据类型相匹配时，以便在索引扫描过程中应用过滤条件。

Index Merge

在 MySQL 中，Index Merge 优化策略允许查询优化器使用多个索引来满足查询条件。

如果使用了索引合并,EXPLAIN 会有如下信息

对应的type列显示的值应该是index_merge，
key列显示用的到所有索引名称，
Extra列会显示具体使用了哪种类型的索引合并。

索引类型

Intersection	对多个二级索引里符合条件的主键值取交集合并
Union	对多个二级索引里符合条件的主键值去重后取并集合并
Sort Union	对多个二级索引里符合条件的主键值去重并排序后，再取并集合并

我们这里举一个简单的例子

SELECT id,a,b FROM T WHERE a>100 AND b>200;

不使用索引合并过程可能是这样的

InnoDB从idx_aB+树中获取到第一条a>100的记录，拿记录里的id值回表查询。
回表查询获取到完整的用户记录，判断b>200是否成立，成立则返回给客户端，否则丢弃该记录。
InnoDB继续从idx_aB+树中获取到下一条a>100的记录，重复前面的过程。

使用索引合并过程可能是这样的

利用idx_a索引将获取到的id集合记作id_setA。
利用idx_b索引将获取到的id集合记作id_setB。
将id_setA和id_setB取交集，记作id_set。
对id_set回表查询，将结果返回给客户端。

优化器对索引的选择

影响因素：如扫描行数，是否排序，索引区分度，回表代价等
索引的基数：采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
优化器选错索引对几种情况
1. 可能是索引的统计信息（扫描行数，基数）出现错误，可以使用 analyze table table_name 命令重新统计表的索引信息。统计出现错误的一个原因：delete一堆数据然后又马上insert mysql 来不及统计。
2. 带有order by 查询，优化器尝试使用order by 字段上的索引进行优化
3. in查询参数过多

In与索引开销计算

IN里面的个数多少，会影响MySQL优化器对开销代价的估算方法
估算方法的差异，受 eq_range_index_dive_limit 这个参数控制，参数值默认是200
- 当IN的个数小于200时，MySQL使用 index dive 方式估算开销，这种方式统计速度慢，但是比较精确；
- 当IN的个数>=200时，使用index statistics，这种方式统计速度快，但是不一定精准

explain 详解

EXPLAIN 是 MySQL 中用于分析查询执行计划的关键字, 通过使用 EXPLAIN，我们可以了解查询如何使用索引、连接表以及其它优化策略。

下面是一个简单的例子。假设我们有一个名为 employees 的表，包含以下字段：id（主键）、first_name、last_name、age 和 department_id。我们还为 department_id 和 last_name 创建了索引。

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  age INT,
  department_id INT,
  INDEX (department_id),
  INDEX (last_name)
);

现在我们想要查询年龄大于 30 的员工，且所在部门 ID 为 5 的所有员工。我们可以使用以下查询：

SELECT * FROM employees WHERE age > 30 AND department_id = 5;

为了分析这个查询的执行计划，我们可以在查询前加上 EXPLAIN 关键字：

EXPLAIN SELECT * FROM employees WHERE age > 30 AND department_id = 5;

执行这个查询后，我们可能得到类似如下的结果：

+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+--------------------------+
| id | select_type | table     | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | department_id | department_id | 5       | const | 10   |    33.33 | Using where; Using index |
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+--------------------------+

这个结果表示：

select_type：查询类型为 SIMPLE，表示这是一个简单的查询，没有子查询或者 UNION 操作。
table：正在查询的表是 employees。
type：查询使用的连接类型是 ref，表示使用了非唯一索引进行查找。
possible_keys：可能使用的索引为 department_id。
key：实际使用的索引是 department_id。
key_len：索引的长度为 5。
ref：索引的参考列是一个常量值（部门 ID）。
rows：优化器预计需要检查 10 行数据。
filtered：经过条件过滤后，大约 33.33% 的记录符合查询条件。
Extra：额外信息，这里显示了 Using where 和 Using index。Using where 表示使用了 WHERE 子句过滤数据，Using index 表示使用了覆盖索引，不需要访问表的数据行。

通过key_len 可以看出使用了索引的哪些部分
Extra里只有using index的话，不用回表

select_type

select_type	Description
SIMPLE	简单SELECT,不使用UNION或子查询等
PRIMARY	查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
UNION	UNION中的第二个或后面的SELECT语句
DEPENDENT UNION	UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT，取决于外面的查询
DERIVED	派生表的SELECT, FROM子句的子查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存，必须重新评估外链接的第一行

type

type	描述
ALL	Full Table Scan， MySQL将遍历全表以找到匹配的行
index	Full Index Scan，index与ALL区别为index类型只遍历索引树
range	只检索给定范围的行，使用一个索引来选择行
ref	表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref	类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system	当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL	MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

所有的索引字段，如果没有设置not null，则需要加一个字节。
定长字段，int占四个字节、date占三个字节、char(n)占n个字符。
对于变成字段varchar(n)，则有n个字符+两个字节。
不同的字符集，一个字符占用的字节数不同。latin1编码的，一个字符占用一个字节，gbk编码的，一个字符占用两个字节，utf8编码的，一个字符占用三个字节。

Extra

Using index: "覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
Using where:
- 如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where 信息。
- 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

参考

MySQL优化之Index Merge_程序员小潘的博客-CSDN博客_index_merge

15个必知的Mysql索引失效场景，别再踩坑了！

为什么生产环境中B+树的高度总是3-4层？

面试题：mysql 一棵 B+ 树能存多少条数据？

转载自:https://juejin.cn/post/7251895374771683383

Mysql索引:从入门到茅塞顿开

InnoDB索引结构:B+树

索引结构选型

索引类型

概念:聚簇索引（Clustered Index）与 非聚簇索引（Secondary Index）

联合索引

主键索引

DB_ROW_ID

唯一索引

前缀索引

索引机制

change buffer机制

索引、联合索引失效

不满足最左前缀原则

遇到非等值查询条件

使用 OR 语句：

索引列参与计算或函数：

隐式类型转换

全表扫描更高效

覆盖索引（Covering Index）

索引下推(Index Condition Pushdown，ICP)

Index Merge

优化器对索引的选择

In与索引开销计算

explain 详解

select_type

type

key_len

Extra

参考

概念:聚簇索引（Clustered Index）与非聚簇索引（Secondary Index）

`DB_ROW_ID`

使用 `OR` 语句：