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

InnoDB索引结构:B+树

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

这种结构有两个优点：

索引结构选型

索引类型

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

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

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

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

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

索引、联合索引失效

不满足最左前缀原则

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

遇到非等值查询条件

如果联合索引中的某一列是非等值查询条件（例如 >、=、100 AND b>200;


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

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

优化器对索引的选择

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 |
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+--------------------------+

这个结果表示：

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

select_type

type

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+ 树能存多少条数据？