我家小花儿

这个屌丝很懒，什么也没留下！

热门标签

数据库架构设计——索引结构设计_把id、日期、订单id结合作为主键索引要怎么设计

作者：我家小花儿 | 2024-04-25 08:01:00

踩

把id、日期、订单id结合作为主键索引要怎么设计

摘要

当单表的设计不足以支撑业务上线，接下来需要考虑的是索引设计优化。通过分析索引的基本原理，层层推进到索引的创建和优化，最后触达复杂 SQL 索引的设计与调优，比如多表 JOIN、子查询、分区表的问题。希望学完这部分内容之后，你能解决线上所有的 SQL 问题，不论是 OLTP 业务，还是复杂的 OLAP 业务。

一、mysql的索引原理

索引是提升查询速度的一种数据结构。

索引之所以能提升查询速度，在于它在插入时对数据进行了排序（显而易见，它的缺点是影响插入或者更新的性能）。所以，索引是一门排序的艺术，有效地设计并创建索引，会提升数据库系统的整体性能。在目前的 MySQL 8.0 版本中，InnoDB 存储引擎支持的索引有 B+ 树索引、全文索引、R 树索引。这一讲我们就先关注使用最为广泛的 B+ 树索引。

1.1 B+树索引结构

B+ 树索引是数据库系统中最为常见的一种索引数据结构，几乎所有的关系型数据库都支持它。

那为什么关系型数据库都热衷支持 B+树索引呢？因为它是目前为止排序最有效率的数据结构。像二叉树，哈希索引、红黑树、SkipList，在海量数据基于磁盘存储效率方面远不如 B+ 树索引高效。所以，上述的数据结构一般仅用于内存对象，基于磁盘的数据排序与存储，最有效的依然是 B+ 树索引。

B+树索引的特点是： 基于磁盘的平衡树，但树非常矮，通常为 3~4 层，能存放千万到上亿的排序数据。树矮意味着访问效率高，从千万或上亿数据里查询一条数据，只用 3、4 次 I/O。

又因为现在的固态硬盘每秒能执行至少 10000 次 I/O ，所以查询一条数据，哪怕全部在磁盘上，也只需要 0.003 ~ 0.004 秒。另外，因为 B+ 树矮，在做排序时，也只需要比较 3~4 次就能定位数据需要插入的位置，排序效率非常不错。

B+ 树索引由根节点（root node）、中间节点（non leaf node）、叶子节点（leaf node）组成，其中叶子节点存放所有排序后的数据。当然也存在一种比较特殊的情况，比如高度为 1 的B+ 树索引：

上图中，第一个列就是 B+ 树索引排序的列，你可以理解它是表 User 中的列 id，类型为 8 字节的 BIGINT，所以列 userId 就是索引键（key），类似下表：


CREATE TABLE User (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  sex CHAR(6) NOT NULL,
  registerDate DATETIME NOT NULL,
  ...
)

所有 B+ 树都是从高度为 1 的树开始，然后根据数据的插入，慢慢增加树的高度。你要牢记：索引是对记录进行排序， 高度为 1 的 B+ 树索引中，存放的记录都已经排序好了，若要在一个叶子节点内再进行查询，只进行二叉查找，就能快速定位数据。

可随着插入 B+ 树索引的记录变多，1个页（16K）无法存放这么多数据，所以会发生 B+ 树的分裂，B+ 树的高度变为 2，当 B+ 树的高度大于等于 2 时，根节点和中间节点存放的是索引键对，由（索引键、指针）组成。

索引键就是排序的列，而指针是指向下一层的地址，在 MySQL 的 InnoDB 存储引擎中占用 6 个字节。下图显示了 B+ 树高度为 2 时，B+ 树索引的样子：

可以看到，在上面的B+树索引中，若要查询索引键值为 5 的记录，则首先查找根节点，查到键值对（20，地址），这表示小于 20 的记录在地址指向的下一层叶子节点中。接着根据下一层地址就可以找到最左边的叶子节点，在叶子节点中根据二叉查找就能找到索引键值为 5 的记录。

1.2 优化 B+ 树索引的插入性能

B+ 树在插入时就对要对数据进行排序，但排序的开销其实并没有你想象得那么大，因为排序是 CPU 操作（当前一个时钟周期 CPU 能处理上亿指令）。真正的开销在于 B+ 树索引的维护，保证数据排序，这里存在两种不同数据类型的插入情况。

数据顺序（或逆序）插入： B+ 树索引的维护代价非常小，叶子节点都是从左往右进行插入，比较典型的是自增 ID 的插入、时间的插入（若在自增 ID 上创建索引，时间列上创建索引，则 B+ 树插入通常是比较快的）。
数据无序插入： B+ 树为了维护排序，需要对页进行分裂、旋转等开销较大的操作，另外，即便对于固态硬盘，随机写的性能也不如顺序写，所以磁盘性能也会收到较大影响。比较典型的是用户昵称，每个用户注册时，昵称是随意取的，若在昵称上创建索引，插入是无序的，索引维护需要的开销会比较大。

你不可能要求所有插入的数据都是有序的，因为索引的本身就是用于数据的排序，插入数据都已经是排序的，那么你就不需要 B+ 树索引进行数据查询了。所以对于 B+ 树索引，在 MySQL 数据库设计中，仅要求主键的索引设计为顺序，比如使用自增，或使用函数 UUID_TO_BIN 排序的 UUID，而不用无序值做主键。

我们再回顾自增、UUID、UUID 排序的插入性能对比：

可以看到，UUID 由于是无序值，所以在插入时性能比起顺序值自增 ID 和排序 UUID，性能上差距比较明显。所以，我再次强调： 在表结构设计时，主键的设计一定要尽可能地使用顺序值，这样才能保证在海量并发业务场景下的性能。

1.3 MySQL 中 B+ 树索引的设计与管理

在 MySQL 数据库中，可以通过查询表 mysql.innodb_index_stats 查看每个索引的大致情况：


SELECT 
 
table_name,index_name,stat_name,
stat_value,stat_description 
FROM innodb_index_stats 
WHERE table_name = 'orders' and index_name = 'PRIMARY';
 
+----------+------------+-----------+------------+------------------+
|table_name| index_name | stat_name | stat_value |stat_description  |
+----------+-------------------+------------+------------+----------+
| orders | PRIMARY|n_diff_pfx01|5778522     | O_ORDERKEY            |
| orders | PRIMARY|n_leaf_pages|48867 | Number of leaf pages        |
| orders | PRIMARY|size        |49024 | Number of pages in the index|
+--------+--------+------------+------+-----------------------------+
 
3 rows in set (0.00 sec)

从上面的结果中可以看到，表 orders 中的主键索引，大约有 5778522 条记录，其中叶子节点一共有 48867 个页，索引所有页的数量为 49024。根据上面的介绍，你可以推理出非叶节点的数量为 49024 ~ 48867，等于 157 个页。另外，我看见网上一些所谓的 MySQL“军规”中写道“一张表的索引不能超过 5 个”。根本没有这样的说法，完全是无稽之谈。在我看来，如果业务的确需要很多不同维度进行查询，那么就该创建对应多索引，这是没有任何值得商讨的地方。真正在业务上遇到的问题是： 由于业务开发同学对数据库不熟悉，创建 N 多索引，但实际这些索引从创建之初到现在根本就没有使用过！因为优化器并不会选择这些低效的索引，这些无效索引占用了空间，又影响了插入的性能。

那你怎么知道哪些 B+树索引未被使用过呢？在 MySQL 数据库中，可以通过查询表sys.schema_unused_indexes，查看有哪些索引一直未被使用过，可以被废弃：


SELECT * FROM schema_unused_indexes
WHERE object_schema != 'performance_schema';
 
+---------------+-------------+--------------+
| object_schema | object_name | index_name   |
+---------------+-------------+--------------+
| sbtest        | sbtest1     | k_1          |
| sbtest        | sbtest2     | k_2          |
| sbtest        | sbtest3     | k_3          |
| sbtest        | sbtest4     | k_4          |
| tpch          | customer    | CUSTOMER_FK1 |
| tpch          | lineitem    | LINEITEM_FK2 |
| tpch          | nation      | NATION_FK1   |
| tpch          | orders      | ORDERS_FK1   |
| tpch          | partsupp    | PARTSUPP_FK1 |
| tpch          | supplier    | SUPPLIER_FK1 |
+---------------+-------------+--------------+

如果数据库运行时间比较长，而且索引的创建时间也比较久，索引还出现在上述结果中，DBA 就可以考虑删除这些没有用的索引。而 MySQL 8.0 版本推出了索引不可见（Invisible）功能。在删除废弃索引前，用户可以将索引设置为对优化器不可见，然后观察业务是否有影响。若无，DBA 可以更安心地删除这些索引：


ALTER TABLE t1 
 
ALTER INDEX idx_name INVISIBLE/VISIBLE;

1.4 数据库的索引原理总结

索引是加快查询的一种数据结构，其原理是插入时对数据排序，缺点是会影响插入的性能；
MySQL 当前支持 B+树索引、全文索引、R 树索引；
B+ 树索引的高度通常为 3~4 层，高度为 4 的 B+ 树能存放 50 亿左右的数据；
由于 B+ 树的高度不高，查询效率极高，50 亿的数据也只需要插叙 4 次 I/O；
MySQL 单表的索引没有个数限制，业务查询有具体需要，创建即可，不要迷信个数限制；
可以通过表 sys.schema_unused_indexes 和索引不可见特性，删除无用的索引。

二、mysql中的索引组织表

InnoDB 存储引擎是 MySQL 数据库中使用最为广泛的引擎，在海量大并发的 OLTP 业务中，InnoDB 必选。它在数据存储方面有一个非常大的特点：索引组织表（Index Organized Table）。

2.1 索引组织表

数据存储有堆表和索引组织表两种方式。

堆表中的数据无序存放， 数据的排序完全依赖于索引（Oracle、Microsoft SQL Server、PostgreSQL 早期默认支持的数据存储都是堆表结构）。

从图中你能看到，堆表的组织结构中，数据和索引分开存储。索引是排序后的数据，而堆表中的数据是无序的，索引的叶子节点存放了数据在堆表中的地址，当堆表的数据发生改变，且位置发生了变更，所有索引中的地址都要更新，这非常影响性能，特别是对于 OLTP 业务。

而索引组织表，数据根据主键排序存放在索引中，主键索引也叫聚集索引（Clustered Index）。在索引组织表中，数据即索引，索引即数据。

MySQL InnoDB 存储引擎就是这样的数据组织方式；Oracle、Microsoft SQL Server 后期也推出了支持索引组织表的存储方式。但是，PostgreSQL 数据库因为只支持堆表存储，不适合 OLTP 的访问特性，虽然它后期对堆表有一定的优化，但本质是通过空间换时间，对海量并发的 OLTP 业务支持依然存在局限性。

其就是索引组织表的方式：

表 User 的主键是 id，所以表中的数据根据 id 排序存储，叶子节点存放了表中完整的记录，可以看到表中的数据存放在索引中，即表就是索引，索引就是表。

2.2 二级索引

InnoDB 存储引擎的数据是根据主键索引排序存储的，除了主键索引外，其他的索引都称之为二级索引（Secondeary Index）， 或非聚集索引（None Clustered Index）。二级索引也是一颗 B+ 树索引，但它和主键索引不同的是叶子节点存放的是索引键值、主键值。对于 08 讲创建的表 User，假设在列 name 上还创建了索引 idx_name，该索引就是二级索引：


CREATE TABLE User (
    id BIGINT AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    sex CHAR(6) NOT NULL,
    registerDate DATETIME NOT NULL,
    ...
    PRIMARY KEY(id), -- 主键索引
    KEY idx_name(name) -- 二级索引
)

如果用户通过列 name 进行查询，比如下面的 SQL：

SELECT * FROM User WHERE name = 'David'，

通过二级索引 idx_name 只能定位主键值，需要额外再通过主键索引进行查询，才能得到最终的结果。这种“二级索引通过主键索引进行再一次查询”的操作叫作“回表”，你可以通过下图理解二级索引的查询：

索引组织表这样的二级索引设计有一个非常大的好处：若记录发生了修改，则其他索引无须进行维护，除非记录的主键发生了修改。与堆表的索引实现对比着看，你会发现索引组织表在存在大量变更的场景下，性能优势会非常明显，因为大部分情况下都不需要维护其他二级索引。前面我强调“索引组织表，数据即索引，索引即数据”。那么为了便于理解二级索引，你可以将二级索引按照一张表来进行理解，比如索引 idx_name 可以理解成一张表，如下所示：


CREATE TABLE idx_name (
    name VARCHAR(128) NOT NULL,
    id BIGINT NOT NULL,
    PRIAMRY KEY(name,id)
)

根据 name 进行查询的 SQL 可以理解为拆分成了两个步骤：


SELECT id FROM idx_name WHERE name = ?
 
SELECT * FROM User WHERE id = _id; -- 回表

当插入数据时，你可以理解为对主键索引表、二级索引表进行了一个事务操作，要么都成功，要么都不成功：


START TRANSATION;
 
INSERT INTO User VALUES (...) -- 主键索引
 
INSERT INTO idx_name VALUES (...) -- 二级索引
 
COMMIT;

当然，对于索引，还可以加入唯一的约束，具有唯一约束的索引称之为唯一索引，也是二级索引。对于表 User，列 name 应该具有唯一约束，因为通常用户注册通常要求昵称唯一，所以表User 定义更新为：


CREATE TABLE User (
 
    id BIGINT AUTO_INCREMENT,
 
    name VARCHAR(128) NOT NULL,
 
    sex CHAR(6) NOT NULL,
 
    registerDate DATETIME NOT NULL,
 
    ...
 
    PRIMARY KEY(id), -- 主键索引
 
    UNIQUE KEY idx_name(name) -- 二级索引
 
)

那么对于唯一索引又该如何理解为表呢？ 其实我们可以将约束理解成一张表或一个索引，故唯一索引 idx_name 应该理解为：


CREATE TABLE idx_name (
    name VARCHAR(128) NOT NULL,
    id BIGINT NOT NULL,
    PRIAMRY KEY(name,id)
) -- 二级索引
 
 
 
CREATE TABLE check_idx_name (
    name VARCHAR(128),
    PRIMARY KEY(name)，
) -- 唯一约束

在索引组织表中，万物皆索引，索引就是数据，数据就是索引。堆表中的索引都是二级索引，哪怕是主键索引也是二级索引，也就是说它没有聚集索引，每次索引查询都要回表。同时，堆表中的记录全部存放在数据文件中，并且无序存放，这对互联网海量并发的 OLTP 业务来说，堆表的实现的确“过时”了。

为什么通常二级索引会比主键索引慢一些。

主键在设计时可以选择比较顺序的方式，比如自增整型，自增的 UUID 等，所以主键索引的排序效率和插入性能相对较高。二级索引就不一样了，它可能是比较顺序插入，也可能是完全随机的插入，具体如何呢？来看一下比较接近业务的表 User：


CREATE TABLE User (
    id  BINARY(16) NOT NULL,
    name VARCHAR(255) NOT NULL,
    sex CHAR(1) NOT NULL,
    password VARCHAR(1024) NOT NULL,
    money BIG INT NOT NULL DEFAULT 0,
    register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    uuid CHAR(36) AS (BIN_TO_UUID(id)),
    CHECK (sex = 'M' OR sex = 'F'),
    CHECK (IS_UUID(UUID)),
    PRIMARY KEY(id),
    UNIQUE KEY idx_name(name),
    KEY idx_register_date(register_date),
    KEY idx_last_modify_date(last_modify_date)
);

可以看到，表 User 有三个二级索引 idx_name、idx_register_date、idx_last_modify_date。

通常业务是无法要求用户注册的昵称是顺序的，所以索引 idx_name 的插入是随机的， 性能开销相对较大；另外用户昵称通常可更新，但业务为了性能考虑，可以限制单个用户每天、甚至是每年昵称更新的次数，比如每天更新一次，每年更新三次。

而用户注册时间是比较顺序的，所以索引 idx_register_date 的性能开销相对较小， 另外用户注册时间一旦插入后也不会更新，只是用于标识一个注册时间。

而关于 idx_last_modify_date ， 在真实业务的表结构设计中，你必须对每个核心业务表创建一个列 last_modify_date，标识每条记录的修改时间。

这时索引 idx_last_modify_date 的插入和 idx_register_date 类似，是比较顺序的，但不同的是，索引 idx_last_modify_date 会存在比较频繁的更新操作，比如用户消费导致余额修改、money 字段更新，这会导致二级索引的更新。

由于每个二级索引都包含了主键值，查询通过主键值进行回表，所以在设计表结构时让主键值尽可能的紧凑，为的就是能提升二级索引的性能，除此之外，在实际核心业务中，开发同学还有很大可能会设计带有业务属性的主键，但请牢记以下两点设计原则：

要比较顺序，对聚集索引性能友好；
尽可能紧凑，对二级索引的性能和存储友好；

2.3 函数索引

我们的索引都是创建在列上，从 MySQL 5.7 版本开始，MySQL 就开始支持创建函数索引 （即索引键是一个函数表达式）。 函数索引有两大用处：

优化业务 SQL 性能；
配合虚拟列（Generated Column）。

先来看第一个好处，优化业务 SQL 性能。

我们知道，不是每个开发人员都能比较深入地了解索引的原理，有时他们的表结构设计和编写 SQL 语句会存在“错误”，比如对于上面的表 User，要查询 2021 年1 月注册的用户，有些开发同学会错误地写成如下所示的 SQL：


SELECT * FROM User 
 
WHERE DATE_FORMAT(register_date,'%Y-%m') = '2021-01'

或许开发同学认为在 register_date 创建了索引，所以所有的 SQL 都可以使用该索引。但索引的本质是排序， 索引 idx_register_date 只对 register_date 的数据排序，又没有对DATE_FORMAT(register_date) 排序，因此上述 SQL 无法使用二级索引idx_register_date。数据库规范要求查询条件中函数写在等式右边，而不能写在左边，就是这个原因。我们通过命令 EXPLAIN 查看上述 SQL 的执行计划，会更为直观地发现索引 idx_register_date没有被使用到：

如果线上业务真的没有按正确的 SQL 编写，那么可能造成数据库存在很多慢查询 SQL，导致业务缓慢甚至发生雪崩的场景。要尽快解决这个问题，可以使用函数索引， 创建一个DATE_FORMAT(register_date) 的索引，这样就能利用排序数据快速定位了：


ALTER TABLE User 
 
ADD INDEX 
 
idx_func_register_date((DATE_FORMAT(register_date,'%Y-%m')));

函数索引第二大用处是结合虚拟列使用。我们已经创建了表 UserLogin：


CREATE TABLE UserLogin (
    userId BIGINT,
    loginInfo JSON,
    cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
    PRIMARY KEY(userId),
    UNIQUE KEY idx_cellphone(cellphone)
);

其中的列cellphone 就是一个虚拟列，它是由后面的函数表达式计算而成，本身这个列不占用任何的存储空间，而索引 idx_cellphone 实质是一个函数索引。这样做得好处是在写 SQL 时可以直接使用这个虚拟列，而不用写冗长的函数：


-- 不用虚拟列
 
SELECT  *  FROM UserLogin
 
WHERE loginInfo->>"$.cellphone" = '13918888888'
 
-- 使用虚拟列
 
SELECT  *  FROM UserLogin 
 
WHERE cellphone = '13918888888'

对于爬虫类的业务，我们会从网上先爬取很多数据，其中有些是我们关心的数据，有些是不关心的数据。通过虚拟列技术，可以展示我们想要的那部分数据，再通过虚拟列上创建索引，就是对爬取的数据进行快速的访问和搜索。

2.4 mysql的索引的总结

索引组织表主键是聚集索引，索引的叶子节点存放表中一整行完整记录；
除主键索引外的索引都是二级索引，索引的叶子节点存放的是（索引键值，主键值）；
由于二级索引不存放完整记录，因此需要通过主键值再进行一次回表才能定位到完整数据；
索引组织表对比堆表，在海量并发的OLTP业务中能有更好的性能表现；
每种不同数据，对二级索引的性能开销影响是不一样的；
有时通过函数索引可以快速解决线上SQL的性能问题；
虚拟列不占用实际存储空间，在虚拟列上创建索引本质就是函数索引。

三、mysql组合索引原理

前两讲我举的例子都是基于一个列进行索引排序和使用，比较简单。在实际业务中，我们会遇到很多复杂的场景，比如对多个列进行查询。这时，可能会要求用户创建多个列组成的索引，如列 a 和 b 创建的组合索引，但究竟是创建（a，b）的索引，还是（b，a）的索引，结果却是完全不同的。

3.1 组合索引

组合索引（Compound Index）是指由多个列所组合而成的 B+树索引，这和我们之前介绍的B+ 树索引的原理完全一样，只是之前是对一个列排序，现在是对多个列排序。组合索引既可以是主键索引，也可以是二级索引，下图显示的是一个二级组合索引：

从上图可以看到，组合索引只是排序的键值从 1 个变成了多个，本质还是一颗 B+ 树索引。但是你一定要意识到（a，b）和（b，a）这样的组合索引，其排序结果是完全不一样的。而索引的字段变多了，设计上更容易出问题，如：

对组合索引（a，b）来说，因为其对列 a、b 做了排序，所以它可以对下面两个查询进行优化：


SELECT * FROM table WHERE a = ?
 
SELECT * FROM table WHERE a = ？ AND b = ？

上述 SQL 查询中，WHERE 后查询列 a 和 b 的顺序无关，即使先写 b = ? AND a = ？依然可以使用组合索引（a，b）。

但是下面的 SQL 无法使用组合索引（a，b），因为（a，b）排序并不能推出（b，a）排序：

SELECT * FROM table WHERE b = ?

此外，同样由于索引（a，b）已排序，因此下面这条 SQL 依然可以使用组合索引（a，b），以此提升查询的效率：

SELECT * FROM table WHERE a = ？ ORDER BY b DESC

同样的原因，索引（a，b）排序不能得出（b，a）排序，因此下面的 SQL 无法使用组合索引（a，b）：

SELECT * FROM table WHERE b = ？ ORDER BY a DESC

3.2 组合索引设计实战

避免额外排序

在真实的业务场景中，你会遇到根据某个列进行查询，然后按照时间排序的方式逆序展示。比如在微博业务中，用户的微博展示的就是根据用户 ID 查询出用户订阅的微博，然后根据时间逆序展示；又比如在电商业务中，用户订单详情页就是根据用户 ID 查询出用户的订单数据，然后根据购买时间进行逆序展示。

避免回表，性能提升10倍，即 SQL 需要通过二级索引查询得到主键值，然后再根据主键值搜索主键索引，最后定位到完整的数据。但是由于二级组合索引的叶子节点，包含索引键值和主键值，若查询的字段在二级索引的叶子节点中，则可直接返回结果，无需回表。这种通过组合索引避免回表的优化技术也称为索引覆盖（Covering Index）。

3.3 组合索引总结

组合索引也是一颗 B+ 树，只是索引的列由多个组成，组合索引既可以是主键索引，也可以是二级索引。通过今天的学习，我们可以归纳组合索引的三大优势：

覆盖多个查询条件，如（a，b）索引可以覆盖查询 a = ? 或者 a = ? and b = ?；
避免 SQL 的额外排序，提升 SQL 性能，如 WHERE a = ? ORDER BY b 这样的查询条件；
利用组合索引包含多个列的特性，可以实现索引覆盖技术，提升 SQL 的查询性能，用好索引覆盖技术，性能提升 10 倍不是难事。

四、索引的选择原理

而在实际工作中，我也经常会遇到一些同学提出这样的问题：MySQL 并没有按照自己的预想来选择索引，比如创建了索引但是选择了全表扫描，这肯定是 MySQL 数据库的 Bug，或者是索引出错了。当然不是！ 这主要因为索引中的数据犯了错。

4.1 MySQL是如何选择索引的？

在前面的表 orders 中，对于字段 o_custkey 已经创建了相关的 3 个索引，所以现在表 orders 的情况如下所示：


 CREATE TABLE `orders` (
 
  `O_ORDERKEY` int NOT NULL,
 
  `O_CUSTKEY` int NOT NULL,
 
  `O_ORDERSTATUS` char(1) NOT NULL,
 
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
 
  `O_ORDERDATE` date NOT NULL,
 
  `O_ORDERPRIORITY` char(15) NOT NULL,
 
  `O_CLERK` char(15) NOT NULL,
 
  `O_SHIPPRIORITY` int NOT NULL,
 
  `O_COMMENT` varchar(79) NOT NULL,
 
  PRIMARY KEY (`O_ORDERKEY`),
 
  KEY `idx_custkey_orderdate` (`O_CUSTKEY`,`O_ORDERDATE`),
 
  KEY `ORDERS_FK1` (`O_CUSTKEY`),
 
  KEY `idx_custkey_orderdate_totalprice` (`O_CUSTKEY`,`O_ORDERDATE`,`O_TOTALPRICE`),
 
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`O_CUSTKEY`) REFERENCES `customer` (`C_CUSTKEY`)
 
) ENGINE=InnoDB

在查询字段 o_custkey 时，理论上可以使用三个相关的索引：ORDERS_FK1、idx_custkey_orderdate、idx_custkey_orderdate_totalprice。那 MySQL 优化器是怎么从这三个索引中进行选择的呢？在关系型数据库中，B+ 树索引只是存储的一种数据结构，具体怎么使用，还要依赖数据库的优化器，优化器决定了具体某一索引的选择，也就是常说的执行计划。而优化器的选择是基于成本（cost），哪个索引的成本越低，优先使用哪个索引。

如上图所示，MySQL 数据库由 Server 层和 Engine 层组成：

Server 层有 SQL 分析器、SQL优化器、SQL 执行器，用于负责 SQL 语句的具体执行过程；
Engine 层负责存储具体的数据，如最常使用的 InnoDB 存储引擎，还有用于在内存中存储临时结果集的 TempTable 引擎。

SQL 优化器会分析所有可能的执行计划，选择成本最低的执行，这种优化器称之为：CBO（Cost-based Optimizer，基于成本的优化器）。

而在 MySQL中，一条 SQL 的计算成本计算如下所示：


Cost  = Server Cost + Engine Cost
 
      = CPU Cost + IO Cost

其中，CPU Cost 表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序……这些操作都在 Server 层完成；IO Cost 表示引擎层 IO 的开销，MySQL 8.0 可以通过区分一张表的数据是否在内存中，分别计算读取内存 IO 开销以及读取磁盘 IO 的开销。数据库 mysql 下的表 server_cost、engine_cost 则记录了对于各种成本的计算，如：

表 server_cost 记录了 Server 层优化器各种操作的成本，这里面包括了所有 CPU Cost，其具体含义如下。

disk_temptable_create_cost：创建磁盘临时表的成本，默认为20。
disk_temptable_row_cost：磁盘临时表中每条记录的成本，默认为0.5。
key_compare_cost：索引键值比较的成本，默认为0.05，成本最小。
memory_temptable_create_cost：创建内存临时表的成本：默认为1。
memory_temptable_row_cost：内存临时表中每条记录的成本，默认为0.1。
row_evaluate_cost：记录间的比较成本，默认为0.1。

可以看到， MySQL 优化器认为如果一条 SQL 需要创建基于磁盘的临时表，则这时的成本是最大的，其成本是基于内存临时表的 20 倍。而索引键值的比较、记录之间的比较，其实开销是非常低的，但如果要比较的记录数非常多，则成本会变得非常大。

而表 engine_cost 记录了存储引擎层各种操作的成本，这里包含了所有的 IO Cost，具体含义如下。

io_block_read_cost：从磁盘读取一个页的成本，默认值为1。
memory_block_read_cost：从内存读取一个页的成本，默认值为0.25。

也就是说， MySQL 优化器认为从磁盘读取的开销是内存开销的 4 倍。不过，上述所有的成本都是可以修改的，比如如果数据库使用是传统的 HDD 盘，性能较差，其随机读取性能要比内存读取慢 50 倍，那你可以通过下面的 SQL 修改成本：


INSERT INTO 
 
engine_cost(engine_name,device_type,cost_name,cost_value,last_update,comment) 
 
VALUES ('InnoDB',0,'io_block_read_cost',12.5,CURRENT_TIMESTAMP,'Using HDD for InnoDB');
 
FLUSH OPTIMIZER_COSTS;

4.2 MySQL索引出错案例分析

4.2.1 未能使用创建的索引

经常听到有同学反馈 MySQL 优化器不准，不稳定，一直在变。但是，我想告诉你的是，MySQL 优化器永远是根据成本，选择出最优的执行计划。哪怕是同一条 SQL 语句，只要范围不同，优化器的选择也可能不同。


SELECT * FROM orders
 
WHERE o_orderdate > '1994-01-01' and o_orderdate < '1994-12-31';
 
**********************************************************************
 
SELECT * FROM orders 
 
WHERE o_orderdate > '1994-02-01' and o_orderdate < '1994-12-31';

上面这两条 SQL 都是通过索引字段 o_orderdate 进行查询，然而第一条 SQL 语句的执行计划并未使用索引 idx_orderdate，而是使用了如下的执行计划：


EXPLAIN SELECT * FROM orders 
 
WHERE o_orderdate > '1994-01-01' 
 
AND o_orderdate < '1994-12-31'\G
 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: idx_orderdate
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5799601
     filtered: 32.35
        Extra: Using where

从上述执行计划中可以发现，优化器已经通过 possible_keys 识别出可以使用索引 idx_orderdate，但最终却使用全表扫描的方式取出结果。 最为根本的原因在于：优化器认为使用通过主键进行全表扫描的成本比通过二级索引 idx_orderdate 的成本要低，可以通过 FORMAT=tree 观察得到：


EXPLAIN FORMAT=tree 
 
SELECT * FROM orders 
 
WHERE o_orderdate > '1994-01-01' 
 
AND o_orderdate < '1994-12-31'\G
 
*************************** 1. row ***************************
 
EXPLAIN: -> Filter: ((orders.O_ORDERDATE > DATE'1994-01-01') and (orders.O_ORDERDATE < DATE'1994-12-31'))  (cost=592267.11 rows=1876082)
 
    -> Table scan on orders  (cost=592267.11 rows=5799601)
 
 
 
EXPLAIN FORMAT=tree 
 
SELECT * FROM orders FORCE INDEX(idx_orderdate)
 
WHERE o_orderdate > '1994-01-01' 
 
AND o_orderdate < '1994-12-31'\G
 
*************************** 1. row ***************************
 
EXPLAIN: -> Index range scan on orders using idx_orderdate, with index condition: ((orders.O_ORDERDATE > DATE'1994-01-01') and (orders.O_ORDERDATE < DATE'1994-12-31'))  (cost=844351.87 rows=1876082)

可以看到，MySQL 认为全表扫描，然后再通过 WHERE 条件过滤的成本为 592267.11，对比强制使用二级索引 idx_orderdate 的成本为 844351.87。成本上看，全表扫描低于使用二级索引。故，MySQL 优化器没有使用二级索引 idx_orderdate。

为什么全表扫描比二级索引查询快呢？ 因为二级索引需要回表，当回表的记录数非常大时，成本就会比直接扫描要慢，因此这取决于回表的记录数。所以，第二条 SQL 语句，只是时间范围发生了变化，但是 MySQL 优化器就会自动使用二级索引 idx_orderdate了，这时我们再观察执行计划：


EXPLAIN SELECT * FROM orders 
 
WHERE o_orderdate > '1994-02-01' 
 
AND o_orderdate < '1994-12-31'\G
 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: range
possible_keys: idx_orderdate
          key: idx_orderdate
      key_len: 3
          ref: NULL
         rows: 1633884
     filtered: 100.00
        Extra: Using index condition

再次强调，并不是 MySQL 选择索引出错，而是 MySQL 会根据成本计算得到最优的执行计划， 根据不同条件选择最优执行计划，而不是同一类型一成不变的执行过程，这才是优秀的优化器该有的样子。

4.2.3 索引创建在有限状态上

B+ 树索引通常要建立在高选择性的字段或字段组合上，如性别、订单 ID、日期等，因为这样每个字段值大多并不相同。但是对于性别这样的字段，其值只有男和女两种，哪怕记录数再多，也只有两种值，这是低选择性的字段，因此无须在性别字段上创建索引。但在有些低选择性的列上，是有必要创建索引的。比如电商的核心业务表 orders，其有字段 o_orderstatus，表示当前的状态。

在电商业务中会有一个这样的逻辑：即会定期扫描字段 o_orderstatus 为支付中的订单，然后强制让其关闭，从而释放库存，给其他有需求的买家进行购买。但字段 o_orderstatus 的状态是有限的，一般仅为已完成、支付中、超时已关闭这几种。通常订单状态绝大部分都是已完成，只有绝少部分因为系统故障原因，会在 15 分钟后还没有完成订单，因此订单状态是存在数据倾斜的。这时，虽然订单状态是低选择性的，但是由于其有数据倾斜，且我们只是从索引查询少量数据，因此可以对订单状态创建索引：


ALTER TABLE orders 
 
ADD INDEX idx_orderstatus(o_orderstatus)

但这时根据下面的这条 SQL，优化器的选择可能如下：


EXPLAIN SELECT * FROM orders 
 
WHERE o_orderstatus = 'P'\G
 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5799601
     filtered: 50.00
        Extra: Using where

由于字段 o_orderstatus 仅有三个值，分别为 'O'、'P'、'F'。但 MySQL 并不知道这三个列的分布情况，认为这三个值是平均分布的，但其实是这三个值存在严重倾斜：


SELECT o_orderstatus,count(1) 
 
FROM orders GROUP BY o_orderstatus;
 
+---------------+----------+
| o_orderstatus | count(1) |
+---------------+----------+
| F             |  2923619 |
| O             |  2923597 |
| P             |   152784 |
+---------------+----------+

因此，优化器会认为订单状态为 P 的订单占用 1/3 的数据，使用全表扫描，避免二级索引回表的效率会更高。然而，由于数据倾斜，订单状态为 P 的数据非常少，根据索引 idx_orderstatus 查询的效率会更高。这种情况下，我们可以利用 MySQL 8.0 的直方图功能，创建一个直方图，让优化器知道数据的分布，从而更好地选择执行计划。直方图的创建命令如下所示：


ANALYZE TABLE orders 
 
UPDATE HISTOGRAM ON o_orderstatus;

在创建完直方图后，MySQL会收集到字段 o_orderstatus 的数值分布，可以通过下面的命令查询得到：


SELECT 
 
v value, 
 
CONCAT(round((c - LAG(c, 1, 0) over()) * 100,1), '%') ratio
 
FROM information_schema.column_statistics, 
 
JSON_TABLE(histogram->'$.buckets','$[*]' COLUMNS(v VARCHAR(60) PATH '$[0]', c double PATH '$[1]')) hist
 
WHERE column_name = 'o_orderstatus';
 
+-------+-------+
| value | ratio |
+-------+-------+
| F     | 49%   |
| O     | 48.5% |
| P     | 2.5%  |
+-------+-------+

可以看到，现在 MySQL 知道状态为 P 的订单只占 2.5%，因此再去查询状态为 P 的订单时，就会使用到索引 idx_orderstatus了，如：


EXPLAIN SELECT * FROM orders 
 
WHERE o_orderstatus = 'P'\G
 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ref
possible_keys: idx_orderstatus
          key: idx_orderstatus
      key_len: 4
          ref: const
         rows: 306212
     filtered: 100.00
        Extra: Using index condition

4.3 索引的选择总结

MySQL 优化器是 CBO，即一种基于成本的优化器。其会判单每个索引的执行成本，从中选择出最优的执行计划。总结来说：

MySQL 优化器是 CBO 的；
MySQL 会选择成本最低的执行计划，你可以通过 EXPLAIN 命令查看每个 SQL 的成本；
一般只对高选择度的字段和字段组合创建索引，低选择度的字段如性别，不创建索引；
低选择性，但是数据存在倾斜，通过索引找出少部分数据，可以考虑创建索引；
若数据存在倾斜，可以创建直方图，让优化器知道索引中数据的分布，进一步校准执行计划。

五、mysql的连接查询

除了单表的 SQL 语句，还有两大类相对复杂的 SQL，多表 JOIN 和子查询语句，这就要在多张表上创建索引，难度相对提升不少。而很多开发人员下意识地认为 JOIN 会降低 SQL 的性能效率，所以就将一条多表 SQL 拆成单表的一条条查询，但这样反而会影响 SQL 执行的效率。

5.1 JOIN连接算法

MySQL 8.0 版本支持两种 JOIN 算法用于表之间的关联：

Nested Loop Join；
Hash Join。

通常认为，在 OLTP 业务中，因为查询数据量较小、语句相对简单，大多使用索引连接表之间的数据。这种情况下，优化器大多会用 Nested Loop Join 算法；而 OLAP 业务中的查询数据量较大，关联表的数量非常多，所以用 Hash Join 算法，直接扫描全表效率会更高。注意，这里仅讨论最新的 MySQL 8.0 版本中 JOIN 连接的算法，同时也推荐你在生产环境时优先用 MySQL 8.0。

5.1.1 Nested Loop Join算法

Nested Loop Join 之间的表关联是使用索引进行匹配的，假设表 R 和 S 进行连接，其算法伪代码大致如下：


for each row r in R with matching condition:
 
    lookup index idx_s on S where index_key = r
 
    if (found)
 
      send to client

在上述算法中，表 R 被称为驱动表，表 R 中通过 WHERE 条件过滤出的数据会在表 S 对应的索引上进行一一查询。如果驱动表 R 的数据量不大，上述算法非常高效。

接着，我们看一下，以下三种 JOIN 类型，驱动表各是哪张表：


SELECT ... FROM R LEFT JOIN S ON R.x = S.x WEHRE ...
 
SELECT ... FROM R RIGHT JOIN S ON R.x = S.x WEHRE ...
 
SELECT ... FROM R INNER JOIN S ON R.x = S.x WEHRE ...

对于上述 Left Join 来说，驱动表就是左表 R；Right Join中，驱动表就是右表 S。这是 JOIN 类型决定左表或右表的数据一定要进行查询。但对于 INNER JOIN，驱动表可能是表 R，也可能是表 S。

在这种场景下，谁需要查询的数据量越少，谁就是驱动表。 我们来看下面的例子：


SELECT ... FROM R INNER JOIN S 
 
ON R.x = S.x 
 
WHERE R.y = ? AND S.z = ?

上面这条 SQL 语句是对表 R 和表 S 进行 INNER JOIN，其中关联的列是 x，WHERE 过滤条件分别过滤表 R 中的列 y 和表 S 中的列 z。那么这种情况下可以有以下两种选择：

优化器一般认为，通过索引进行查询的效率都一样，所以 Nested Loop Join 算法主要要求驱动表的数量要尽可能少。所以，如果 WHERE R.y = ?过滤出的数据少，那么这条 SQL 语句会先使用表 R 上列 y 上的索引，筛选出数据，然后再使用表 S 上列 x 的索引进行关联，最后再通过 WHERE S.z = ?过滤出最后数据。


SELECT COUNT(1) 
 
FROM orders
 
INNER JOIN lineitem
 
  ON orders.o_orderkey = lineitem.l_orderkey 
 
    WHERE orders.o_orderdate >= '1994-02-01' 
 
      AND  orders.o_orderdate < '1994-03-01'

上面的表 orders 你比较熟悉，类似于电商中的订单表，在我们的示例数据库中记录总量有 600万条记录。表 lineitem 是订单明细表，比如一个订单可以包含三件商品，这三件商品的具体价格、数量、商品供应商等详细信息，记录数约 2400 万。上述 SQL 语句表示查询日期为 1994 年 2 月购买的商品数量总和，你通过命令 EXPLAIN 查看得到执行计划如下所示：


EXPLAIN: -> Aggregate: count(1)
 
 -> Nested loop inner join  (cost=115366.81 rows=549152)
 
     -> Filter: ((orders.O_ORDERDATE >= DATE'1994-02-01') and (orders.O_ORDERDATE < DATE'1994-03-01'))  (cost=26837.49 rows=133612)
 
         -> Index range scan on orders using idx_orderdate  (cost=26837.49 rows=133612)
 
     -> Index lookup on lineitem using PRIMARY (l_orderkey=orders.o_orderkey)  (cost=0.25 rows=4)

上面的执行计划步骤如下，表 orders 是驱动表，它的选择过程如下所示：

Index range scan on orders using idx_orderdate：使用索引 idx_orderdata 过滤出1994 年 2 月的订单数据，预估记录数超过 13 万。
Index lookup on lineitem using PRIMARY：将第一步扫描的结果作为驱动表，然后将驱动表中的每行数据的 o_orderkey 值，在 lineitem 的主键索引中进行查找。
Nested loop inner join：进行 JOIN 连接，匹配得到的输出结果。
Aggregate: count(1)：统计得到最终的商品数量。

但若执行的是下面这条 SQL，则执行计划就有了改变：


EXPLAIN FORMAT=tree
 
SELECT COUNT(1) 
 
FROM orders
 
INNER JOIN lineitem
 
  ON orders.o_orderkey = lineitem.l_orderkey 
 
    WHERE orders.o_orderdate >= '1994-02-01' 
 
      AND  orders.o_orderdate < '1994-03-01'
 
      AND lineitem.l_partkey = 620758
 
 
 
EXPLAIN: -> Aggregate: count(1)
 
-> Nested loop inner join  (cost=17.37 rows=2)
 
    -> Index lookup on lineitem using lineitem_fk2 (L_PARTKEY=620758)  (cost=4.07 rows=38)
 
    -> Filter: ((orders.O_ORDERDATE >= DATE'1994-02-01') and (orders.O_ORDERDATE < DATE'1994-03-01'))  (cost=0.25 rows=0)
 
        -> Single-row index lookup on orders using PRIMARY (o_orderkey=lineitem.l_orderkey)  (cost=0.25 rows=1)

上述 SQL 只是新增了一个条件 lineitem.l_partkey =620758，即查询 1994 年 2 月，商品编号为 620758 的商品购买量。这时若仔细查看执行计划，会发现通过过滤条件 l_partkey = 620758 找到的记录大约只有 38 条，因此这时优化器选择表 lineitem 为驱动表。

5.1.2 Hash Join算法

MySQL 中的第二种 JOIN 算法是 Hash Join，用于两张表之间连接条件没有索引的情况。

有同学会提问，没有连接，那创建索引不就可以了吗？或许可以，但：

如果有些列是低选择度的索引，那么创建索引在导入数据时要对数据排序，影响导入性能；
二级索引会有回表问题，若筛选的数据量比较大，则直接全表扫描会更快。

对于 OLAP 业务查询来说，Hash Join 是必不可少的功能，MySQL 8.0 版本开始支持 Hash Join 算法，加强了对于 OLAP 业务的支持。所以，如果你的查询数据量不是特别大，对于查询的响应时间要求为分钟级别，完全可以使用单个实例 MySQL 8.0 来完成大数据的查询工作。

Hash Join算法的伪代码如下：


foreach row r in R with matching condition:
 
    create hash table ht on r
 
foreach row s in S with matching condition:
 
    search s in hash table ht:
 
    if (found)
 
        send to client

Hash Join会扫描关联的两张表：

首先会在扫描驱动表的过程中创建一张哈希表；
接着扫描第二张表时，会在哈希表中搜索每条关联的记录，如果找到就返回记录。

Hash Join 选择驱动表和 Nested Loop Join 算法大致一样，都是较小的表作为驱动表。如果驱动表比较大，创建的哈希表超过了内存的大小，MySQL 会自动把结果转储到磁盘。

Hash Join，接下来，我们再来看一个 SQL：


SELECT  s_acctbal, s_name, n_name, p_partkey, p_mfgr,s_address,s_phone, s_comment
 
FROM  part, supplier, partsupp, nation, region
 
WHERE  p_partkey = ps_partkey  AND s_suppkey = ps_suppkey AND p_size = 15  AND p_type LIKE '%BRASS'  AND s_nationkey = n_nationkey  AND n_regionkey = r_regionkey   AND r_name = 'EUROPE';

上面这条 SQL 语句是要找出商品类型为 %BRASS，尺寸为 15 的欧洲供应商信息。因为商品表part 不包含地区信息，所以要从关联表 partsupp 中得到商品供应商信息，然后再从供应商元数据表中得到供应商所在地区信息，最后在外表 region 连接，才能得到最终的结果。最后的执行计划如下图所示：

上图可以发现，其实最早进行连接的是表 supplier 和 nation，接着再和表 partsupp 连接，然后和 part 表连接，再和表 part 连接。上述左右连接算法都是 Nested Loop Join。这时的结果集记录大概有 79,330 条记录

最后和表 region 进行关联，表 region 过滤得到结果5条，这时可以有 2 种选择：

在 73390 条记录上创建基于 region 的索引，然后在内表中通过索引进行查询；
对表 region 创建哈希表，73390 条记录在哈希表中进行探测；

选择 1 就是 MySQL 8.0 不支持 Hash Join 时优化器的处理方式，缺点是：如关联的数据量非常大，创建索引需要时间；其次可能需要回表，优化器大概率会选择直接扫描内表。

选择 2 只对大约 5 条记录的表 region 创建哈希索引，时间几乎可以忽略不计，其次直接选择对内表扫描，没有回表的问题。很明显，MySQL 8.0 会选择Hash Join。

了解完优化器的选择后，最后看一下命令 EXPLAIN FORMAT=tree 执行计划的最终结果：


-> Nested loop inner join  (cost=101423.45 rows=79)
 
    -> Nested loop inner join  (cost=92510.52 rows=394)
 
        -> Nested loop inner join  (cost=83597.60 rows=394)
 
            -> Inner hash join (no condition)  (cost=81341.56 rows=98)
 
                -> Filter: ((part.P_SIZE = 15) and (part.P_TYPE like '%BRASS'))  (cost=81340.81 rows=8814)
 
                    -> Table scan on part  (cost=81340.81 rows=793305)
 
                -> Hash
 
                    -> Filter: (region.R_NAME = 'EUROPE')  (cost=0.75 rows=1)
 
                        -> Table scan on region  (cost=0.75 rows=5)
 
            -> Index lookup on partsupp using PRIMARY (ps_partkey=part.p_partkey)  (cost=0.25 rows=4)
 
        -> Single-row index lookup on supplier using PRIMARY (s_suppkey=partsupp.PS_SUPPKEY)  (cost=0.25 rows=1)
 
    -> Filter: (nation.N_REGIONKEY = region.r_regionkey)  (cost=0.25 rows=0)
 
        -> Single-row index lookup on nation using PRIMARY (n_nationkey=supplier.S_NATIONKEY)  (cost=0.25 rows=1)

5.2 OLTP 业务能不能写 JOIN？

OLTP 业务是海量并发，要求响应非常及时，在毫秒级别返回结果，如淘宝的电商业务、支付宝的支付业务、美团的外卖业务等。如果 OLTP 业务的 JOIN 带有 WHERE 过滤条件，并且是根据主键、索引进行过滤，那么驱动表只有一条或少量记录，这时进行 JOIN 的开销是非常小的。比如在淘宝的电商业务中，用户要查看自己的订单情况，其本质是在数据库中执行类似如下的 SQL 语句：


SELECT o_custkey, o_orderdate, o_totalprice, p_name FROM orders,lineitem, part
 
WHERE o_orderkey = l_orderkey
 
  AND l_partkey = p_partkey
 
  AND o_custkey = ?
 
ORDER BY o_orderdate DESC
 
LIMIT 30;

我发现很多开发同学会以为上述 SQL 语句的 JOIN 开销非常大，因此认为拆成 3 条简单 SQL 会好一些，比如：


SELECT * FROM orders WHERE o_custkey = ? ORDER BY o_orderdate DESC;
 
SELECT * FROM lineitem WHERE l_orderkey = ?;
 
SELECT * FROM part WHERE p_part = ?

其实你完全不用人工拆分语句，因为你拆分的过程就是优化器的执行结果，而且优化器更可靠，速度更快，而拆成三条 SQL 的方式，本身网络交互的时间开销就大了 3 倍。所以，放心写 JOIN，你要相信数据库的优化器比你要聪明，它更为专业。上述 SQL 的执行计划如下：


EXPLAIN: -> Limit: 30 row(s)  (cost=27.76 rows=30)
 
    -> Nested loop inner join  (cost=27.76 rows=44)
 
        -> Nested loop inner join  (cost=12.45 rows=44)
 
            -> Index lookup on orders using idx_custkey_orderdate (O_CUSTKEY=1; iterate backwards)  (cost=3.85 rows=11)
 
            -> Index lookup on lineitem using PRIMARY (l_orderkey=orders.o_orderkey)  (cost=0.42 rows=4)
 
        -> Single-row index lookup on part using PRIMARY (p_partkey=lineitem.L_PARTKEY)  (cost=0.25 rows=1)

由于驱动表的数据是固定 30 条，因此不论表 orders、lineitem、part 的数据量有多大，哪怕是百亿条记录，由于都是通过主键进行关联，上述 SQL 的执行速度几乎不变。所以，OLTP 业务完全可以大胆放心地写 JOIN，但是要确保 JOIN 的索引都已添加， DBA 们在业务上线之前一定要做 SQL Review，确保预期内的索引都已创建。

MySQL 数据库中支持 JOIN 连接的算法有 Nested Loop Join 和 Hash Join 两种，前者通常用于 OLTP 业务，后者用于 OLAP 业务。在 OLTP 可以写 JOIN，优化器会自动选择最优的执行计划。但若使用 JOIN，要确保 SQL 的执行计划使用了正确的索引以及索引覆盖，因此索引设计显得尤为重要，这也是DBA在架构设计方面的重要工作之一。

六、mysql中的子查询功能

我提到了一种复杂的 SQL 情况，多表间的连接，以及怎么设计索引来提升 JOIN 的性能。除了多表连接之外，开发同学还会大量用子查询语句（subquery）。但是因为之前版本的MySQL 数据库对子查询优化有限，所以很多 OLTP 业务场合下，我们都要求在线业务尽可能不用子查询。然而，MySQL 8.0 版本中，子查询的优化得到大幅提升。所以从现在开始，放心大胆地在MySQL 中使用子查询吧！

如果让开发同学“找出1993年，没有下过订单的客户数量”，大部分同学会用子查询来写这个需求，比如：


SELECT
    COUNT(c_custkey) cnt
FROM
    customer
WHERE
    c_custkey NOT IN (
        SELECT
            o_custkey
        FROM
            orders
        WHERE
            o_orderdate >=  '1993-01-01'
            AND o_orderdate <  '1994-01-01'
	);

不过上述查询是一个典型的 LEFT JOIN 问题（即在表 customer 存在，在表 orders 不存在的问题）。所以，这个问题如果用 LEFT JOIN 写，那么 SQL 如下所示：


SELECT
    COUNT(c_custkey) cnt
FROM
    customer
        LEFT JOIN
    orders ON
            customer.c_custkey = orders.o_custkey
            AND o_orderdate >= '1993-01-01'
            AND o_orderdate < '1994-01-01'
WHERE
    o_custkey IS NULL;

可以发现，虽然 LEFT JOIN 也能完成上述需求，但不容易理解，因为 LEFT JOIN 是一个代数关系，而子查询更偏向于人类的思维角度进行理解。所以，大部分人都更倾向写子查询，即便是天天与数据库打交道的 DBA 。

不过从优化器的角度看，LEFT JOIN 更易于理解，能进行传统 JOIN 的两表连接，而子查询则要求优化器聪明地将其转换为最优的 JOIN 连接。在 MySQL 8.0 版本中，对于上述两条 SQL，最终的执行计划都是：

可以看到，不论是子查询还是 LEFT JOIN，最终都被转换成了 Nested Loop Join，所以上述两条 SQL 的执行时间是一样的。即，在 MySQL 8.0 中，优化器会自动地将 IN 子查询优化，优化为最佳的 JOIN 执行计划，这样一来，会显著的提升性能。

6.1 子查询 IN 和 EXISTS，哪个性能更好？


SELECT
    COUNT(c_custkey) cnt
FROM
    customer
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            orders
        WHERE
            o_orderdate >=  '1993-01-01'
            AND o_orderdate <  '1994-01-01'
            AND c_custkey = o_custkey
    );

你要注意，千万不要盲目地相信网上的一些文章，有的说 IN 的性能更好，有的说 EXISTS 的子查询性能更好。你只关注 SQL 执行计划就可以，如果两者的执行计划一样，性能没有任何差别。接着说回来，对于上述 NOT EXISTS，它的执行计划如下图所示：

你可以看到，它和 NOT IN 的子查询执行计划一模一样，所以二者的性能也是一样的。讲完子查询的执行计划之后，接下来我们来看一下一种需要对子查询进行优化的 SQL：依赖子查询。

6.2 依赖子查询的优化

在 MySQL 8.0 版本之前，MySQL 对于子查询的优化并不充分。所以在子查询的执行计划中会看到 DEPENDENT SUBQUERY 的提示，这表示是一个依赖子查询，子查询需要依赖外部表的关联。如果你看到这样的提示，就要警惕， 因为 DEPENDENT SUBQUERY 执行速度可能非常慢，大部分时候需要你手动把它转化成两张表之间的连接。


SELECT
    *
FROM
    orders
WHERE
    (o_clerk , o_orderdate) IN (
        SELECT
            o_clerk, MAX(o_orderdate)
        FROM
            orders
        GROUP BY o_clerk);

上述 SQL 语句的子查询部分表示“计算出每个员工最后成交的订单时间”，然后最外层的 SQL表示返回订单的相关信息。这条 SQL 在最新的 MySQL 8.0 中，其执行计划如下所示：

通过命令 EXPLAIN FORMAT=tree 输出执行计划，你可以看到，第 3 行有这样的提示：Select #2 (subquery in condition; run only once)。这表示子查询只执行了一次，然后把最终的结果保存起来了。执行计划的第 6 行Index lookup on <materialized_subquery>，表示对表 orders 和子查询结果所得到的表进行 JOIN 连接，最后返回结果。

所以，当前这个执行计划是对表 orders 做2次扫描，每次扫描约 5587618 条记录：

第 1 次扫描，用于内部的子查询操作，计算出每个员工最后一次成交的时间；
第 2 次表 oders 扫描，查询并返回每个员工的订单信息，即返回每个员工最后一笔成交的订单信息。

最后，直接用命令 EXPLAIN 查看执行计划，如下图所示：

如果是老版本的 MySQL 数据库，它的执行计划将会是依赖子查询，执行计划如下所示：

对比 MySQL 8.0，只是在第二行的 select_type 这里有所不同，一个是 SUBQUERY，一个是DEPENDENT SUBQUERY。接着通过命令 EXPLAIN FORMAT=tree 查看更详细的执行计划过程：

可以发现，第 3 行的执行技术输出是：Select #2 (subquery in condition; dependent)，并不像先前的执行计划，提示只执行一次。另外，通过第 1 行也可以发现，这条 SQL 变成了 exists 子查询，每次和子查询进行关联。所以，上述执行计划其实表示：先查询每个员工的订单信息，接着对每条记录进行内部的子查询进行依赖判断。也就是说，先进行外表扫描，接着做依赖子查询的判断。所以，子查询执行了5587618，而不是1次！！！所以，两者的执行计划，扫描次数的对比如下所示：

对于依赖子查询的优化，就是要避免子查询由于需要对外部的依赖，而需要对子查询扫描多次的情况。所以可以通过派生表的方式，将外表和子查询的派生表进行连接，从而降低对于子查询表的扫描，从而提升 SQL 查询的性能。

那么对于上面的这条 SQL ，可将其重写为：


SELECT * FROM orders o1,
(
    SELECT
        o_clerk, MAX(o_orderdate)
    FROM
        orders
    GROUP BY o_clerk
) o2
WHERE
    o1.o_clerk = o2.o_clerk
    AND o1.o_orderdate = o2.orderdate;

可以看到，我们将子查询改写为了派生表 o2，然后将表 o2 与外部表 orders 进行关联。关联的条件是：o1.o_clerk = o2.o_clerk AND o1.o_orderdate = o2.orderdate。通过上面的重写后，派生表 o2 对表 orders 进行了1次扫描，返回约 5587618 条记录。派生表o1 对表 orders 扫描 1 次，返回约 1792612 条记录。这与 8.0 的执行计划就非常相似了，其执行计划如下所示：

最后，来看下上述 SQL 的执行时间：

可以看到，经过 SQL 重写后，派生表的执行速度几乎与独立子查询一样。所以，若看到依赖子查询的执行计划，记得先进行 SQL 重写优化哦。

6.3 子查询的总结

子查询相比 JOIN 更易于人类理解，所以受众更广，使用更多；
当前 MySQL 8.0 版本可以“毫无顾忌”地写子查询，对于子查询的优化已经相当完备；
对于老版本的 MySQL，请 Review 所有子查询的SQL执行计划， 对于出现 DEPENDENT SUBQUERY 的提示，请务必即使进行优化，否则对业务将造成重大的性能影响；
DEPENDENT SUBQUERY 的优化，一般是重写为派生表进行表连接。表连接的优化就是我们12讲所讲述的内容。

七、分区表示的设计

简单来说，分区表就是把物理表结构相同的几张表，通过一定算法，组成一张逻辑大表。这种算法叫“分区函数”，当前 MySQL 数据库支持的分区函数类型有 RANGE、LIST、HASH、KEY、COLUMNS。

无论选择哪种分区函数，都要指定相关列成为分区算法的输入条件，这些列就叫“分区列”。另外，在 MySQL 分区表中，主键也必须是分区列的一部分，不然创建分区表时会失败，比如：


CREATE TABLE t (
    a INT,
    b INT,
    c DATETIME(6),
    d VARCHAR(32),
    e INT,
    PRIMARY KEY (a,b)
)
 
partition by range columns(c) (
    PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
    PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
    PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
    PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);
 
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).

上面创建了表 t，主键是复合索引，由列 a、b 组成。表 t 创建分区表的意图是根据列 c（时间列）拆分数据，把不同时间数据存放到不同分区中。

而我们可以从错误的提示中看到：分区表的主键一定要包含分区函数的列。所以，要创建基于列c 的数据分片的分区表，主键必须包含列 c，比如下面的建表语句：


CREATE TABLE t (
    a INT,
    b INT,
    c DATETIME,
    d VARCHAR(32),
    e INT,
    PRIMARY KEY (a,b,c),
    KEY idx_e (e)
)
 
partition by range columns(c) (
    PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
    PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
    PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
    PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);

创建完表后，在物理存储上会看到四个分区所对应 ibd 文件，也就是把数据根据时间列 c 存储到对应的 4 个文件中：

t#p#p0000.ibd  t#p#p2019.ibd  t#p#p2020.ibd  t#p#p9999.ibd

所以，你要理解的是：MySQL 中的分区表是把一张大表拆成了多张表，每张表有自己的索引，从逻辑上看是一张表，但物理上存储在不同文件中。

7.1 分区表注意事项：唯一索引

在 MySQL 数据库中，分区表的索引都是局部，而非全局。也就是说，索引在每个分区文件中都是独立的，所以分区表上的唯一索引必须包含分区列信息，否则创建会报错，比如：


ALTER TABLE t ADD UNIQUE KEY idx_d(d);
 
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).

你可以看到错误提示： 唯一索引必须包含分区函数中所有列。而下面的创建才能成功：

ALTER TABLE t ADD UNIQUE KEY idx_d(d,c);

但是，正因为唯一索引包含了分区列，唯一索引也就变成仅在当前分区唯一，而不是全局唯一了。那么对于上面的表 t，插入下面这两条记录都是可以的：


INSERT INTO t VALUES 
(1,1,'2021-01-01','aaa',1),
(1,1,'2020-01-01','aaa',1);
SELECT * FROM t;
+---+---+---------------------+------+------+
| a | b | c                   | d    | e    |
+---+---+---------------------+------+------+
| 1 | 1 | 2020-01-01 00:00:00 |aaa   |    1 |
| 1 | 1 | 2021-01-01 00:00:00 |aaa   |    1 |
+---+---+---------------------+------+------+

你可以看到，列 d 都是字符串‘aaa’，但依然可以插入。这样带来的影响是列 d 并不是唯一的，所以你要由当前分区唯一实现全局唯一。那如何实现全局唯一索引呢？ 和之前表结构设计时一样，唯一索引使用全局唯一的字符串（如类似 UUID 的实现），这样就能避免局部唯一的问题。

7.2 分区表的误区：性能提升

很多同学会认为，分区表是把一张大表拆分成了多张小表，所以这样 MySQL 数据库的性能会有大幅提升。这是错误的认识！如果你寄希望于通过分区表提升性能，那么我不建议你使用分区，因为做不到。分区表技术不是用于提升 MySQL 数据库的性能，而是方便数据的管理。

B+树高度与数据存储量之间的关系”：

从表格中可以看到，B+ 树的高度为 4 能存放数十亿的数据，一次查询只需要占用 4 次 I/O，速度非常快。但是当你使用分区之后，效果就不一样了，比如上面的表 t，我们根据时间拆成每年一张表，这时，虽然 B+ 树的高度从 4 降为了 3，但是这个提升微乎其微。除此之外，分区表还会引入新的性能问题，比如非分区列的查询。非分区列的查询，即使分区列上已经创建了索引，但因为索引是每个分区文件对应的本地索引，所以要查询每个分区。


SELECT * FROM t WHERE d = 'aaa'
 
******** 1. row ********
           id: 1
  select_type: SIMPLE
        table: t
   partitions: p0000,p2019,p2020,p9999
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where

通过执行计划我们可以看到：上述 SQL 需要访问 4 个分区，假设每个分区需要 3 次 I/O，则这条 SQL 总共要 12 次 I/O。但是，如果使用普通表，记录数再多，也就 4 次的 I/O 的时间。所以，分区表设计时，务必明白你的查询条件都带有分区字段，否则会扫描所有分区的数据或索引。所以，分区表设计不解决性能问题，更多的是解决数据迁移和备份的问题。

7.3 分区表在业务上的设计

以电商中的订单表 Orders 为例，如果在类似淘宝的海量互联网业务中，Orders 表的数据量会非常巨大，假设一天产生 5000 万的订单，那么一年表 Orders 就有近 180 亿的记录。

所以对于订单表，在数据库中通常只保存最近一年甚至更短时间的数据，而历史订单数据会入历史库。除非存在 1 年以上退款的订单，大部分订单一旦完成，这些数据从业务角度就没用了。

那么如果你想方便管理订单表中的数据，可以对表 Orders 按年创建分区表，如：


CREATE TABLE `orders` (
  `o_orderkey` int NOT NULL,
  `O_CUSTKEY` int NOT NULL,
  `O_ORDERSTATUS` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `O_CLERK` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `O_SHIPPRIORITY` int NOT NULL,
  `O_COMMENT` varchar(79) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`o_orderkey`,`O_ORDERDATE`),
  KEY `orders_fk1` (`O_CUSTKEY`),
  KEY `idx_orderdate` (`O_ORDERDATE`)
)
 
PARTITION BY RANGE  COLUMNS(o_orderdate)
 
(
  PARTITION p0000 VALUES LESS THAN ('1992-01-01') ENGINE = InnoDB,
  PARTITION p1992 VALUES LESS THAN ('1993-01-01') ENGINE = InnoDB,
  PARTITION p1993 VALUES LESS THAN ('1994-01-01') ENGINE = InnoDB,
  PARTITION p1994 VALUES LESS THAN ('1995-01-01') ENGINE = InnoDB,
  PARTITION p1995 VALUES LESS THAN ('1996-01-01') ENGINE = InnoDB,
  PARTITION p1996 VALUES LESS THAN ('1997-01-01') ENGINE = InnoDB,
  PARTITION p1997 VALUES LESS THAN ('1998-01-01') ENGINE = InnoDB,
  PARTITION p1998 VALUES LESS THAN ('1999-01-01') ENGINE = InnoDB,
  PARTITION p9999 VALUES LESS THAN (MAXVALUE)
）

你可以看到，这时 Orders 表的主键修改为了(o_orderkey,O_ORDERDATE)，数据按照年进行分区存储。那么如果要删除 1 年前的数据，比如删除 1998 年的数据，之前需要使用下面的 SQL，比如:


DELETE FROM Orders 
 
WHERE o_orderdate >= '1998-01-01' 
 
  AND o_orderdate < '1999-01-01'

可这条 SQL 的执行相当慢，产生大量二进制日志，在生产系统上，也会导致数据库主从延迟的问题。而使用分区表的话，对于数据的管理就容易多了，你直接使用清空分区的命令就行：


ALTER TABLE orders_par 
 
TRUNCATE PARTITION p1998

上述 SQL 执行速度非常快，因为实际执行过程是把分区文件删除和重建。另外产生的日志也只有一条 DDL 日志，也不会导致主从复制延迟问题。


# at 425
 
#210328 12:10:12 server id 8888  end_log_pos 549        Query   thread_id=9     exec_time=0     error_code=0    Xid = 10
 
SET TIMESTAMP=1619583012/*!*/;
 
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
 
ALTER TABLE orders TRUNCATE PARTITION p1998
 
/*!*/;

7.4 分区表的总结

当前 MySQL 的分区表支持 RANGE、LIST、HASH、KEY、COLUMNS 的分区算法；
分区表的创建需要主键包含分区列；
在分区表中唯一索引仅在当前分区文件唯一，而不是全局唯一；
分区表唯一索引推荐使用类似 UUID 的全局唯一实现；
分区表不解决性能问题，如果使用非分区列查询，性能反而会更差；
推荐分区表用于数据管理、速度快、日志小。

博文参考

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家小花儿/article/detail/484143