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;
1. 依次选取不同长度的前缀来看这个值，比如我们要看一下 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;
1. 设定自己可以接受的损失比例然后调整

除上述索引外,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;