数据页

InnoDB 是如何存储数据的？

记录是按照行来存储的，但是数据库的读取并不是以行为单位，否则一次读取(一次 I/O 操作) 只能处理一行数据，效率会非常低。因此，InnoDB 的数据是按数据页
数据页为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

数据库的 I/O 操作的最小单位是页，InnoDB 数据页的默认大小是 16KB，数据库每次读写都是以 16KB 为单位，一次最少从磁盘中读取 16K 的内容到内存中或者一次最少把 16K 内容刷新到磁盘中。

数据页包括七个部分，结构如下：

7个部分的说明如下：

名称	说明
File Header	文件头，表示页的信息
Page Header	页头，表示页的状态信息
Infimum + Supremum Records	两个虚拟伪记录，分别表示页中的最小记录和最大记录
User Records	用户记录，存储行记录内容
Free Space	空闲空间，表示页中还未被使用的空间
Page Directory	页目录，存储用户记录的相对位置，对记录起到索引作用
Fil Trailer	文件尾，校验页是否完整

在 File Header 中有两个指针(FIL_PAGE_PREV, FIL_PAGE_NEXT)，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向的链表，如下图：

数据页中的 User Records 是如何组织的

User Reocrds 是用来存储用户数据的，各条记录之间通过 next_record 字段串联成了一个链表。除了用户的行记录之外，还有两条记录: Infimum(最小行) 和 Supermum(最大行)。这是 InnoDB
在创建表时自动生成的。如下图：

页目录(Page Directory)

数据页中的记录按照索引键值顺序组成单项链表，单向链表的特点是插入、删除很高效，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。因此，数据页中有一个页目录(Page Directory)，起到记录的 “索引” 作用。

页目录创建过程

将所有的记录划分成几个组，这些记录包括 Infimum 和 Supermum
每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字
页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slots），每个槽相当于指针指向了不同组的最后一个记录。

槽中可以放多少数据行

Infimum 只能包含一条记录
Supermum 可以是 [1,8] 条记录
其他的则是 [4,8] 条记录

举例说明

页目录就是多个槽组成的，槽相当于分组记录的索引。因为记录是按照索引键值顺序存放，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽(哪个记录分组)，定位到槽后，再遍历槽内的所有记录，无需从最小记录开始遍历整个页中的记录。

以上图为例，5 个槽的编号分别为 0，1，2，3，4，我想查找主键为 10 的用户记录

先二分得出槽中间位是 (0+4)/2=2 ，2号槽里最大的记录为 8。因为 10 > 8，所以需要从 2 号槽后继续搜索记录
再使用二分搜索出 2 号和 4 槽的中间位是 (2+4)/2= 3，3 号槽里最大的记录为 12。因为 10 < 12，所以键值为 10 的记录在 3 号槽里
再从 3 号槽指向的主键值为 9 记录开始向下搜索 1 次，定位到主键为 10 的记录，取出该条记录的信息即为我们想要查找的内容

此外，还必须清楚：

B+树索引本身并不能找到具体的一条记录，能找到的只是该记录所在的页。数据库把页载入内存，然后通过 Page Directory 进行二叉查找。只不过二叉查找的时间复杂度很低，同时内存中的查找很快，因此通常忽略这部分查找所用的时间。

数据结构

二分查找法

二分查找法（binary search）也称为折半查找法，先以有序数列的中点位比较对象，如果要找的元素值小于该中心元素，则将待查找序列缩小为左半部分，否则为右半部分。通过一次比较，将查找区间缩小一半。如下图所示。

前面数据页结构中，每页 Page Directory 中的槽是按照主键顺序存放的，对于某一条具体记录的查询是通过对 Page Directory 进行二分查找得到的。

二叉查找树

二叉查找树不同于普通二叉查找树，是将小于根节点的元素放在左子树，而右子树正好相反，是放大于根节点的元素。如下图

二叉查找树在在查找数据时，只需要将需要查找的元素与树节点元素进行比较，当元素大于根节点则往右子树中查找，元素小于根节点则往左子树中查找，元素如果正好是中位数那么就是正好是根节点，所以二叉查找树具备高效查询。

但是二叉树也有明显弊端，在极端情况下，如果每次插入的数据都是最小或者都是最大的元素，那么树结构会退化成一条链表，如下图所示。

当二叉查找树退化成链表时，查找效率很低，就和顺序查找差不多。如果想最大性能地构造出一个二叉树，需要这棵二叉查找树是平衡的。

平衡二叉树

平衡二叉树是来解决二叉查找树极端情况下退化为链表的问题。平衡二叉树其实就是在二叉查找树的基础上加上约束：让每个节点的左右子树高度差不能超过 1。这样可以让左右子树都保持平衡。如下图。

但是尽管是平衡二叉树，也会随着插入的元素增多，而导致树的高度变高，这同样意味着磁盘 I/O 操作次数变多，影响到整体的查询效率。

B 树

平衡二叉树本身是一个二叉树，每个节点只能有2个子节点，随着数据量的增大，节点个数越多，树的高度也会增高，增加了磁盘的 I/O 次数，影响查询效率。

B 树的出现可以解决树高度的问题。之所以是 B 树，而不是”某某二叉树”，就是它不在限制一个父节点中只能有两个子节点，而是允许 M 个子节点（M>
2）。不仅如此，B树的一个节点可以存储多个元素，相比较于前面的那些二叉树数据结构又将整体的树高度降低了。

B 树是一棵多叉树，它的每一个节点包含的最多子节点数量称为B树的阶。下图是一棵3阶的B树。

每个节点称为页，在mysql中数据读取去的基本单位是页，而也就是上面的磁盘块。P节点是指向子节点的指针。

B 树查找流程

在这个3阶B树中，查找 89 这个元素时的流程：

先从根节点出发，也就是磁盘块1，判断 89 大于 45，通过磁盘块1中的指针 p3 找到磁盘块4。还是按照原来的步骤，在磁盘块4中的65 ~ 87之间相比较，最后磁盘4的指针p3找到磁盘块11。也就找到有匹配89的键值。

B树其实已经满足了减少磁盘 I/O 操作，同时支持按区间查找。但是 B树的区间查找效率并不高。因为B树在做范围查询时，需要使用中序遍历，那么父节点和子节点也就需要不断的来回切换。会给磁盘 I/O 带来很多负担。

B+ 树

B+树从 + 可以看出是B树的升级版，MySQL 中 InnoDB 引擎中的索引底层数据结构采用的正是 B+树。

B+ 树结构

B+树相比于B树，做了这样的升级：B+树中的非叶子节点都不存储数据，而是只作为索引。由叶子节点存放整棵树的所有数据。而叶子节点之间构成一个从小到大有序的链表互相指向相邻的叶子节点，也就是叶子节点之间形成了有序的双向链表。如下图B+树的结构。

B+ 树查询

B+ 树底层是数据，上层都是按底层区间构成的索引。搜到到关键字不会直接返回，会一直走到叶子节点这一层查询数据。比如搜索 id=10，虽然在根节点中就命中了，但是全部的数据在叶子节点上，所以还要继续往下搜索，一直到叶子节点。

B+ 树插入和删除

B树没有冗余节点，删除节点时会发生复杂的树变形。B+树做了大量冗余节点，从上面可以发现父节点的所有元素都会在子节点中出现，这样当删除一个节点时，可以直接从叶子节点中删除，这样效率更快，不会涉及到复杂的树变形。

而且B+ 树的插入也是如此，最多只涉及树的一条分支路线。

估算一颗 B+ 树中的数据量


假设一条记录是 1K，一个叶子节点（数据页）可以存储 16 条记录，那非叶子节点可以存储多少个指针呢？
假设索引字段是 bigint 类型，长度是 8 字节。指针大小为 6 字节，这样一共 14 字节。非叶子节点（索引页）可以存储 16384/14 =1170 个这样的单元（键值+指针）。
树深度为2的时候，有 1170^2 个叶子节点，1170^2*16=21902400。
在查询数据时一次页的查找代表一次 IO，也就是说，一张 2000 万数据的表，查询数据最多需要访问3次磁盘。所以 在 InnoDB 中 B+ 树深度一般为 1-3 层，就能满足千万级别的数据存储。

Explain

explain 关键字可以模拟 MySQL 优化器执行 SQL 语句，可以很好的分析 SQL 语句或表结构的性能瓶颈。

key_len 计算公式

字段类型	长度	latin1	gbk	uft8	utf8mb4	允许为 null	不允许为 null	key_len
char	L	k = 1	k = 2	k = 3	k = 4	n = 1	n = 0	L*k + n
varchar	L	k = 1	k = 2	k = 3	k = 4	n = 1	n = 0	L*k + n + 2
tinyint	1					n = 1	n = 0	1 + n
smallint	2					n=1	n = 0	2 + n
mediumint	3					n=1	n = 0	3 + n
int	4					n=1	n = 0	4 + n
bigint	8					n=1	n = 0	8 + n
datetime
(mysql5.6及以后)	5					n=1	n = 0	5 + n
date	3					n=1	n = 0	3 + n
time	3					n=1	n = 0	3 + n
year	1					n=1	n = 0	1 + n
timestamp	4					n=1	n = 0	4 + n

1.整数类型，浮点数类型，时间类型的索引长度 NOT NULL=字段本身的字段长度 NULL=字段本身的字段长度+1，因为需要有是否为空的标记，这个标记需要占用1个字节 datetime 类型在5.6中字段长度是5个字节

2.字符类型 varchr(n)变长字段且允许NULL = n * (utf8mb4=4,utf8=3,gbk=2,latin1=1)+1(NULL)+2 varchr(n)变长字段且不允许NULL = n * (
utf8mb4=4,utf8=3,gbk=2,latin1=1)+2

char(n)固定字段且允许NULL = n * (utf8mb4=4,utf8=3,gbk=2,latin1=1)+1(NULL)
char(n)固定字段且允许NULL = n * (utf8mb4=4,utf8=3,gbk=2,latin1=1)

变长字段需要额外的2个字节来记录长度，所以VARCAHR索引长度计算时候要加2。固定长度字段不需要额外的字节。而null都需要1个字节的额外空间,所以索引字段最好不要为NULL，因为NULL让统计更加复杂，并且需要额外的存储空间。这个结论在此得到了证实，复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

##explain 各字段含义
explain 中各字段含义见下图：

#索引

##索引定义

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里多有记录的引用指针。使用索引可以快度找出某个或多个列中有一特定值的行，所有 MySQL 列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
##聚集索引和非聚集索引

###概括

聚集索引就是以主键创建的索引

非聚集索引就是以非主键创建的索引，也叫做二级索引

###区别

聚集索引在叶子节点存储的是表中的数据
非聚集索引在叶子节点存储的是主键和索引列
使用非聚集索引查询出数据时，先查询到叶子节点上的主键，再去主键索引中查找要找的数据(拿到主键再查找的这个过程叫做回表)

##联合索引

联合索引是指按一定顺序对表上的多个列进行索引。一个联合索引是一个有序元组 <a1, a2, …, an>。单列索引可以看成联合索引元素数为1的特例。

###好处

对于需要排序的查询，联合索引中的键值都是排序的，通过叶子节点可以逻辑上顺序读的读出所有数据，可以避免多一次的排序操作。

###最左匹配原则

假如索引列分别为 A, B, C，且顺序也为 A, B, C

如果查询的时候，查询【A】【A, B】【A, B, C】，那么可以通过索引查询。
如果查询的时候，采用【A, C】，那么 C 虽然是索引，但是由于中间缺失了B，因此C 这个索引是用不到的，只能用到 A 索引。
如果查询的时候，采用【B】【B, C】【C】，由于没有用到第一列索引，不是最左前缀，那么后面索引也是用不到的。
如果查询的时候，采用范围查询(>、<、between、like 左匹配)，并且是最左前缀，mysql 无法再使用范围列后面的其他索引列了。如果查询的时候，采用 A = 1 and b > 2 and C = 3，则会在每个节点依次命中
A、B，无法命中C。
列的排列顺序决定了可命中索引的列数。

###=、in 自动优化顺序

不需要考虑=、in 等的顺序，mysql 会自动优化这些条件的顺序，以匹配尽可能多的索引列。

如有索引 (a, b, c, d)，查询条件 c > 3 and b = 2 and a = 1 and d < 4 与 a = 1 and c > 3 and b = 2 and d < 4 等顺序都是可以的，MySQL
都会自动优化为 a = 1 and b = 2 and c > 3 and d < 4，依次命 a、b、c。

##覆盖索引

在联合索引中，存在着一种特殊的索引 - 覆盖索引。

###优势

非聚集索引的叶子节点存储的是主键 + 列值，最终还是要回表，也就是要通过主键再查找一次，这样就会比较慢。覆盖索引从二级索引中就可以得到查询的记录，不需要查询聚集索引中的记录。
覆盖索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的 I/O 操作。
能使用覆盖索引就使用。

###举例

如果现有索引 (username, age)，在查询数据的时候：select username，age from user where username = ‘Tom’ and age = 19;
很明显，where 后面的 username 和 age 是要走索引的，而且要查询的 username 和 age 也正是索引的列，这些列都存于索引的叶子节点上，所以就不用回表了。

##使用索引扫描来做排序

MySQL 有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描。如果 EXPLAIN 出来的 type 列的值为 “index”，则说明 MySQL 使用了索引扫描来做排序。

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向（倒序或顺序）都一样时，MySQL 才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当 ORDER BY
子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL 都需要执行文件排序操作，而无法利用索引排序。

##索引不是越多越好

在进行插入操作时，数据页的存放还是按主键 id
的执行顺序存放，凡是对于非聚集索引，叶子节点的插入不再是顺序的了。这时就需要离散地访问非聚集索引页，插入性能在这里变低了。然而这并不是字段上索引的错误，因为B+树的特性决定了非聚集索引插入的离散型。

##索引下推

Index Condition Pushdown 是 MySQL 5.6 开始支持的一种根据索引进行查询的优化方式。mysql 数据库会在取出索引的同时，判断是否可以进行 where 条件的过滤，也就是将 where
的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层 sql 层对记录的索取（fetch），从而提高数据库的整体性能。

索引下推优化支持 range、ref、eq_ref、ref_or_null 类型的查询。支持 MyISAM 和 InnoDB 存储引擎。当优化器选择 Index Condition Pushdown 优化时，可以在执行计划的列 Extra
看到 Using index condition 提示。

##索引不可见

不可见索引是 mysql8.0 的新特性。

在删除多余索引前，可以先隐藏一个索引，然后观察对数据库的影响。如果数据库心梗有所下降，就说明这个索引是有用的，于是将其”恢复显示” 即可。如果数据库性能看不出变化，说明这个索引是多余的，可以删除了。

##索引倾斜

一般情况下，推荐在基数高的列上创建索引；比如订单表的status字段，可能该字段有出货中，派件中，已完成三个值，一般都会觉得在该列不推荐创建索引；但是在实际情况中，比如订单表有1000万条数据，只有10w条数据的status是派件中，而实际业务中，查询派件中的订单这种需求比较多，此时就可以创建status字段的索引了，因为status
列的值分布不均，这个索引是严重倾斜的，而索引的优势就是从大量数据中找出少量数据；但是就算创建了该列的索引，mysql的优化器可能也不会用该索引，因为优化器不会知道该列存在索引倾斜，此时有可能需要人工指定索引了，explain select

from table force index() where …

查看一个索引是否是高选择性，也就是基数（cardinality）

1 2	select * from information_schema.statistics where table_schema='DBNAME' and table_name = 'TABLENAME';

在实际应用中，cardinality/n_rows_in_table 应尽可能的接近1。如果非常小，那么需要考虑是否还有必要创建这个索引。

创建复合索引规则：应该把基数高的列放在前面，这样就可以最先筛选掉大部分的数据。

##索引失效的情况

查询条件包含 or，可能导致索引失效示例
如果字段名类型是字符串，where 时一定要用引号括起来,否则不使用索引示例
使用 like 以 % 开头导致索引失效示例
不符合最左匹配原则示例
在索引列上使用 mysql 内置函数，索引失效示例
对索引列运算（如，+、-、*、/），索引失效示例
mysql 估计使用全表扫描要比使用索引快,则不使用索引示例
连接查询关联字段的字符集不一样，可能导致索引失效示例

##建立索引技巧

最左匹配原则。MySQL 会一直向右匹配到范围查询列的排列顺序决定了可命中索引的列数就停止匹配
尽量选择区分度搞的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)，表示字段不重复的比率，比率越大扫描的记录数就越少。
索引不能参与计算，尽量保持列“干净”。比如比如，FROM_UNIXTIME(create_time) = ‘2022-08-06’
就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成： create_time = UNIX_TIMESTAMP(‘
2022-08-06’)
尽可能的扩展索引，不要新建立索引。比如表中已经有了 (a) 的索引，现在要加 (a, b) 的索引，那么只需要修改原来的索引即可。
单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL会从多个单列索引中选择一个或多个（union 索引合并时）效率最高的索引。

索引举例

创建 t_user_action_log 表并插入数据

– create table CREATE TABLE customer_info (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(32) NOT NULL COMMENT ‘名字’,
province varchar(32) NOT NULL COMMENT ‘省份’,
level int(6) NOT NULL COMMENT ‘等级’,
sex varchar(10) NOT NULL COMMENT ‘性别’, PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT=’顾客信息表’;

– create sql script import random import string

with open(‘customer.sql’, ‘a’) as f:
for i in range(0, 100000):
name=’’.join(random.sample(string.ascii_lowercase, 4))

    provice = random.choice(['heilongjiang', 'jilin', 'liaoning', 'hebei', 'neimenggu', 'henan', 'xinjiang', 'gansu', 'shanxi', 'shanxi', 'shandong', 'jiangsu', 'hubei', 'sichuan', 'xizang', 'qinghai', 'anhui', 'jiangsu', 'zhejiang', 'jiangxi', 'fujian', 'hunan', 'guzhou', 'yunan', 'guangxi', 'guangdong', 'taiwan', 'hainan', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin', 'jilin'])

    level = random.choice([1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1])

    sex = random.choice(['female', 'male', 'female', 'female', 'female', 'female', 'female', 'female', 'female', 'female', 'female'])

    sql = f"INSERT INTO customer_info (name, province, level, sex) values ('{name}', '{provice}', '{level}', '{sex}');"


    f.write(sql)
    f.write('\n')

– impport sql 将数据导入表中

1，主键索引

查询 id 为 1 的记录，sql 如下：

select name, province, level, sex from customer_info where id = 1;

通过查询分析器explain分析这条查询语句：

key 都为 PRIMARY，表示使用了主键索引
ref 为 const，表示通过索引一次就找到了
rows 为 1，表示大致估算出找到所需的记录需要读取1行记录

2，非主键单列索引

添加索引前后对比

查询 province 为 zhejiang的所有记录，sql 如下：

select name, province, level, sex from customer_info where province = ‘zhejiang’;

通过查询分析器explain分析这条查询语句：

type为ALL表示要进行全表扫描。这样效率无疑是极慢的。

为province列添加索引：

alter table customer_info add index prov_idx(province);

再通过查询分析器分析：

看到这次查询就使用索引 prov_idx 了。
这次查询扫描了 1768 行，即所有 province= ‘zhejiang’ 的行。

varchar 中存的为数字时

insert into customer_info(name, province, level, sex) values (‘kd’, ‘1’, 2, ‘male’);

通过查询分析器explain分析

如果字段名类型是字符串，where 时一定要用引号括起来,否则不使用索引

在索引列上使用 mysql 内置函数

alter table customer_info add key lvl_idx(level);

通过查询分析器explain分析

在索引列上使用 mysql 内置函数，索引失效

对索引列进行运算

对索引列运算（如，+、-、*、/），索引失效

可以使用索引但未走索引

mysql 估计使用全表扫描要比使用索引快,则不使用索引

3，联合索引

删除之前创建的索引 prov_idx ， lvl_idx

alter table customer_info dorp index prov_idx, drop index lvl_idx;

查询条件：来自浙江，level 为 1 的男生。sql 如下：

select name, province, level, sex from customer_info where province = ‘zhejiang’ and level=1 and sex=’male’;

通过查询分析器explain分析这条查询语句：

type 为 ALL，全表扫描

创建一个联合索引

alter table customer_info add index prov_level_sex_idx(province, level, sex);

再查询分析器分析：

查询走了索引 prov_sex_level_idx
ref 显示命中了 prov_sex_level_idx 中的三个字段

更换查询条件顺序

更换 where 后面的查询条件再执行一次 explain：

结果和上面的一样，说明 mysql 会自动优化这些条件的顺序

查询条件变为2个

查询条件为 province 和 level 时，符合最左前缀原则，走了 prov_sex_level_idx 索引
查询条件为 province 和 sex 时，只有 province 符合最左匹配原则，所以只有 province 走了索引
查询条件为 level 和 sex 时，都不符合最左匹配原则，不能走索引

查询条件变为 1 个

和预期的一样，只有查询条件为 province 时才符合最左前缀原则，才会走索引

查询条件中有范围查询 >、<

使用了 prov_level_sex_idx 索引，根据扫描的行数知道，province 和 level 字段使用了索引
遇到 >、<范围查询，mysql 无法再使用范围列后面的其他索引列了

查询条件中有 between

从 key 看，三个字段都走了索引，但是从 filter看，并不是所有的字段都走了索引。那该如何判断？可以看下这篇文章 The range access method and why you should use EXPLAIN
JSON，这时候 explain 可以加上参数 format=json 来查看哪些字段走了索引

mysql> explain format=json select name, province, level, sex from customer_info where province = ‘zhejiang’ and level
between 3 and 7 and sex=’male’ \G
*************************** 1. row ***************************
EXPLAIN: {
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “187.21”
},
“table”: {
“table_name”: “customer_info”,
“access_type”: “range”,
“possible_keys”: [
“prov_level_sex_idx”
],
“key”: “prov_level_sex_idx”,
“used_key_parts”: [
“province”,
“level”
],
“key_length”: “176”,
“rows_examined_per_scan”: 133,
“rows_produced_per_join”: 13,
“filtered”: “10.00”,
“index_condition”: “((testdb.customer_info.province = ‘zhejiang’) and (testdb.customer_info.level between 3
and 7) and (testdb.customer_info.sex = ‘male’))”,
“cost_info”: {
“read_cost”: “184.55”,
“eval_cost”: “2.66”,
“prefix_cost”: “187.21”,
“data_read_per_join”: “4K”
},
“used_columns”: [
“name”,
“province”,
“level”,
“sex”
]
} } } 1 row in set, 1 warning (0.00 sec)

从 “used_key_parts”: [“province”, “level”] 可以看出只有 province 和 level 走了索引，sex 字段并没有走索引。
key_len 字段并不是所有走了索引的索引长度之和
使用 format=json 可以更好的查看 explain
遇到 between 范围查询，mysql 无法再使用范围列后面的其他索引列了

查询条件中有 like

前模糊不能使用索引，非前模糊可以使用索引
和 between 一样，这里通过 key_len 和 filter 判断哪些字段走了索引时有冲突，使用 format=json 重新查看 mysql> explain format=json select name,
province, level, sex from customer_info where province like ‘zhe%’ and level = 9 \G
*************************** 1. row ***************************
EXPLAIN: {
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “2476.21”
},
“table”: {
“table_name”: “customer_info”,
“access_type”: “range”,
“possible_keys”: [
“prov_level_sex_idx”
],
“key”: “prov_level_sex_idx”,
“used_key_parts”: [
“province”
],
“key_length”: “134”,
“rows_examined_per_scan”: 1768,
“rows_produced_per_join”: 176,
“filtered”: “10.00”,
“index_condition”: “((testdb.customer_info.level = 9) and (testdb.customer_info.province like ‘zhe%’))”,
“cost_info”: {
“read_cost”: “2440.85”,
“eval_cost”: “35.36”,
“prefix_cost”: “2476.21”,
“data_read_per_join”: “53K”
},
“used_columns”: [
“name”,
“province”,
“level”,
“sex”
]
} } } 1 row in set, 1 warning (0.00 sec)
从 “used_key_parts”: [“province”] 可以看出只有 province 走了索引，level 字段并没有走索引。
遇到 like 范围查询，mysql 无法再使用范围列后面的其他索引列了

查询条件中有 or

相同列使用 or：索引生效
不同列且部分列建立索引：索引失效
不同列且全部列分别建立索引：自动将 or 转为 union，索引生效
不同列建立联合索引：索引失效

查询条件中有 in

in 后面的 level 字段可以走索引

查询条件中有 order by

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，mysql 才能够使用索引来对结果做排序。

查询条件都在索引中

Extra 字段是 Using index，意思就是索引覆盖，查询的内容可以直接在索引中拿到

联表查询

两表为不同的字符集

– create table customer_info – use utf8mb4 CREATE TABLE customer_info (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(32) NOT NULL COMMENT ‘名字’,
province varchar(32) NOT NULL COMMENT ‘省份’,
level int(6) NOT NULL COMMENT ‘等级’,
sex varchar(10) NOT NULL COMMENT ‘性别’, PRIMARY KEY (id)
KEY prov (province), KEY lvl_idx (level), KEY nm (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT=’顾客信息表’;

– create table customer_info_2 – use utf8mb4 CREATE TABLE customer_info_2 (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(32) NOT NULL COMMENT ‘名字’,
province varchar(512) NOT NULL COMMENT ‘省份’,
level int(6) NOT NULL COMMENT ‘等级’,
sex varchar(10) NOT NULL COMMENT ‘性别’, PRIMARY KEY (id), KEY prov (province), KEY lvl_idx (level),
KEY nm (name),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT=’顾客信息表’;

查询

mysql> explain select * from customer_info c1 join customer_info_2 c2 on c1.name = c2.name where c1.name = ‘gdtb’;
+—-+————-+——-+————+——+—————+——+———+——-+——-+———-+—————————————————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——-+——-+———-+—————————————————-+
| 1 | SIMPLE | c1 | NULL | ref | nm | nm | 130 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | c2 | NULL | ALL | NULL |
NULL | NULL | NULL | 99986 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+—-+————-+——-+————+——+—————+——+———+——-+——-+———-+—————————————————-+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select * from customer_info c1 join customer_info_2 c2 on c1.name = c2.name where c2.name = ‘gdtb’;
+—-+————-+——-+————+——+—————+——+———+——-+——+———-+———————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——-+——+———-+———————–+
| 1 | SIMPLE | c2 | NULL | ref | nm | nm | 98 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | c1 | NULL | ref | nm | nm |
130 | func | 1 | 100.00 | Using index condition |
+—-+————-+——-+————+——+—————+——+———+——-+——+———-+———————–+
2 rows in set, 1 warning (0.00 sec)

字符集 utf8mb4 是 utf8 的超集
当 c1 为驱动表时，c1 中过滤出来的 name 字段的字符集为 utf8mb4，然后再去关联 c2 中的 name(utf8) 字段，超集关联子集，这时候 c2 中的 name 字段需要转换为 utf8mb4 字符集，即
CONVERT(c2.name USING utf8mb4)，因为在索引列上使用了函数操作，所以索引失效了
当 c2 为驱动表时，c2 中过滤出来的 name 字段的字符集为 utf8，然后再去关联 c1 的 name(utf8mb4) 字段，子集关联超集，这没问题，所以 c1 中的关联字段可以走索引

了解了是什么原因造成的，如果要暂时解决这个问题，可以在关联条件中手动将 utf8mb4 向 utf8 进行转换

mysql> explain select * from customer_info c1 join customer_info_2 c2 on CONVERT(c1.name USING utf8) = c2.name where
c1.name = ‘gdtb’;
+—-+————-+——-+————+——+—————+——+———+——-+——+———-+———————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——-+——+———-+———————–+
| 1 | SIMPLE | c1 | NULL | ref | nm | nm | 130 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | c2 | NULL | ref | nm | nm |
98 | func | 1 | 100.00 | Using index condition |
+—-+————-+——-+————+——+—————+——+———+——-+——+———-+———————–+
2 rows in set, 1 warning (0.00 sec)

当然，最好的方法还是让两张表的字符集变得一致。int 类型的不受影响。

索引加在哪个表

mysql> explain select * from customer_info_2 c2 left join customer_info c1 on c2.name = c1.name;
+—-+————-+——-+————+——+—————+——+———+——+——-+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——+——-+———-+————-+
| 1 | SIMPLE | c2 | NULL | ALL | NULL | NULL | NULL | NULL | 99986 | 100.00 | NULL | | 1 | SIMPLE | c1 | NULL | ref | nm
| nm | 130 | func | 1 | 100.00 | Using where |
+—-+————-+——-+————+——+—————+——+———+——+——-+———-+————-+
2 rows in set, 1 warning (0.00 sec)

c1 和 c2 表都有 name 字段的索引，当 c2 为驱动表时，当关联字段不是 where 的条件时，关联字段的索引只需要加在 c1 表(被驱动表)即可，c2 表(驱动表)的字段可以不加。

数据页

InnoDB 是如何存储数据的？

数据页中的 User Records 是如何组织的

页目录(Page Directory)

页目录创建过程

槽中可以放多少数据行

举例说明

数据结构

二分查找法

二叉查找树

平衡二叉树

B 树

B 树查找流程

B+ 树

B+ 树结构

B+ 树查询

B+ 树插入和删除

估算一颗 B+ 树 中的数据量

Explain

key_len 计算公式

估算一颗 B+ 树中的数据量