MySQL索引的正确使用姿势

最新推荐文章于 2024-06-04 11:26:25 发布

gonghaiyu

最新推荐文章于 2024-06-04 11:26:25 发布

阅读量626

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/gonghaiyu/article/details/117430403

版权

MySQL 专栏收录该内容

22 篇文章 1 订阅

订阅专栏

B+树索引

索引设计是数据库设计最重要的一环。InnoDB 存储引擎支持的索引有 B+ 树索引、全文索引、R 树索引。下面重点讲下B+树索引。
那为什么关系型数据库都热衷支持 B+树索引呢？因为它是目前为止排序最有效率的数据结构。像二叉树，哈希索引、红黑树、SkipList，在海量数据基于磁盘存储效率方面远不如 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+ 树都是从高度为 1 的树开始，然后根据数据的插入，慢慢增加树的高度。你要牢记：索引是对记录进行排序，高度为 1 的 B+ 树索引中，存放的记录都已经排序好了，若要在一个叶子节点内再进行查询，只进行二叉查找，就能快速定位数据。

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

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

在这里插入图片描述

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

那一个高度为 2 的 B+ 树索引，理论上最多能存放多少行记录呢?

在 MySQL InnoDB 存储引擎中，一个页的大小为 16K，在上面的表 User 中，键值 userId 是BIGINT 类型，则：

根节点能最多存放以下多个键值对 = 16K / 键值对大小(8+6) ≈ 1100

再假设表 User 中，每条记录的大小为 500 字节，则：

叶子节点能存放的最多记录为 = 16K / 每条记录大小 ≈ 32

综上所述，树高度为 2 的 B+ 树索引，最多能存放的记录数为：

总记录数 = 1100 * 32 =  35,200

也就是说，35200 条记录排序后，生成的 B+ 树索引高度为 2。在 35200 条记录中根据索引键查询一条记录只需要查询 2 个页，一个根叶，一个叶子节点，就能定位到记录所在的页。

同理，树高度为 3 的 B+ 树索引，最多能存放的记录数为：

总记录数 = 1100（根节点） * 1100（中间节点） * 32 =  38,720,000

讲到这儿，你会发现，高度为 3 的 B+ 树索引竟然能存放 3800W 条记录。在 3800W 条记录中定位一条记录，只需要查询 3 个页。那么 B+ 树索引的优势是否逐步体现出来了呢？

不过，在真实环境中，每个页其实利用率并没有这么高，还会存在一些碎片的情况，我们假设每个页的使用率为60%，则：
在这里插入图片描述
表格显示了 B+ 树的威力，即在 50 多亿的数据中，根据索引键值查询记录，只需要 4 次 I/O，大概仅需 0.004 秒。如果这些查询的页已经被缓存在内存缓冲池中，查询性能会更快。

B+ 树的查询高效是要付出代价的，就是我们前面说的插入性能问题。

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

B+ 树在插入时就对要对数据进行排序，但排序的开销其实并没有你想象得那么大，因为排序是 CPU 操作（当前一个时钟周期 CPU 能处理上亿指令）。

真正的开销在于 B+ 树索引的维护，保证数据排序，这里存在两种不同数据类型的插入情况。

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

对于 B+ 树索引，在 MySQL 数据库设计中，仅要求主键的索引设计为顺序，比如使用自增，或使用函数 UUID_TO_BIN 排序的 UUID，而不用无序值做主键。

通过前面的表结构设计，可以看到，UUID 由于是无序值，所以在插入时性能比起顺序值自增 ID 和排序 UUID，性能上差距比较明显。

所以，我再次强调：在表结构设计时，主键的设计一定要尽可能地使用顺序值，这样才能保证在海量并发业务场景下的性能。

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 个页。

一张表的索引不能超过 5 或6个，这点我是持否定态度的。这个在《Relational Database Index Design And the Optimizers》中的前几页误区3中有讲到过。只要是利于查询的索引，都是正确的索引，但是不要创建重复的索引或者没有使用到的索引，因为这些索引占用了空间，又影响了插入的性能。

那你怎么知道哪些 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 |
+---------------+-------------+--------------+

而 MySQL 8.0 版本推出了索引不可见（Invisible）功能。在删除废弃索引前，用户可以将索引设置为对优化器不可见，然后观察业务是否有影响。若无，DBA 可以更安心地删除这些索引：

ALTER TABLE t1 
ALTER INDEX idx_name INVISIBLE/VISIBLE;

tips:
关于如何知道MySQL 数据库中每个 B+ 树索引的高度？可以参考姜总文章：https://mp.weixin.qq.com/s/1-gJLMq3RBllgaWWB149Hg

索引组织表

如下图所示，数据存储有堆表和索引组织表两种方式。堆表中的数据无序存放，数据的排序完全依赖于索引（Oracle、Microsoft SQL Server、PostgreSQL 早期默认支持的数据存储都是堆表结构）。
在这里插入图片描述
从图中可以看到，堆表的组织结构中，数据和索引分开存储。索引是排序后的数据，而堆表中的数据是无序的，索引的叶子节点存放了数据在堆表中的地址，当堆表的数据发生改变，且位置发生了变更，所有索引中的地址都要更新，这非常影响性能，特别是对于 OLTP 业务。

而索引组织表，数据根据主键排序存放在索引中，主键索引也叫聚集索引（Clustered Index）。MySQL InnoDB 存储引擎就是这样的数据组织方式；Oracle、Microsoft SQL Server 后期也推出了支持索引组织表的存储方式。但是，PostgreSQL 数据库因为只支持堆表存储，不适合 OLTP 的访问特性。

二级索引

InnoDB 存储引擎的数据是根据主键索引排序存储的，除了主键索引外，其他的索引都称之为二级索引（Secondeary Index），唯一索引也是二级索引，或非聚集索引（None Clustered Index）。

二级索引也是一颗 B+ 树索引，但它和主键索引不同的是叶子节点存放的是索引键值、主键值。二级索引一般要回表查询。索引组织表这样的二级索引设计有一个非常大的好处：若记录发生了修改，则二级索引无须进行维护，除非记录的主键发生了修改。

主键在设计时可以选择比较顺序的方式，比如自增整型，自增的 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 ，我在 03 讲就强调过，在真实业务的表结构设计中，你必须对每个核心业务表创建一个列 last_modify_date，标识每条记录的修改时间。

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

由于每个二级索引都包含了主键值，查询通过主键值进行回表，所以在设计表结构时让主键值尽可能的紧凑，为的就是能提升二级索引的性能，我在 05 讲推荐过 16 字节顺序 UUID 的列设计，这是性能和存储的最佳实践。

除此之外，在实际核心业务中，开发同学还有很大可能会设计带有业务属性的主键，但请牢记以下两点设计原则：

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

函数索引

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

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

数据库规范要求查询条件中函数写在等式右边，而不能写在左边，就是因为没有使用函数索引。

组合索引

组合索引（Compound Index）是指由多个列所组合而成的 B+树索引，组合索引既可以是主键索引，也可以是二级索引。

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

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

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

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

业务索引设计实战

使用覆盖索引（宽索引）避免二级索引回表查询。性能可以提高数十倍不等。

MySQL是如何选择索引的

决定采用哪个索引是由执行计划决定的。而执行器（优化器）对索引的选择是基于成本算法来考虑的，即：哪个索引的成本越低，优先使用哪个索引。
在这里插入图片描述
如上图所示，MySQL 数据库由 Server 层和 Engine 层组成：

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

而在 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;

MySQL索引出错案例分析

案例1：未能使用创建的索引

分析CBO第一条：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 会根据成本计算得到最优的执行计划，根据不同条件选择最优执行计划，而不是同一类型一成不变的执行过程，这才是优秀的优化器该有的样子。

案例2：索引创建在有限状态上

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

所以，低选择性，但是数据存在倾斜，通过索引找出少部分数据，可以考虑创建索引；
若数据存在倾斜，可以创建直方图，让优化器知道索引中数据的分布，进一步校准执行计划。支付中的订单可以通过下单时间来距离当前时间+是否有支付完成时间来快速刷选。

JOIN的算法

MySQL 8.0 版本支持两种 JOIN 算法用于表之间的关联，通常认为，在 OLTP 业务中，因为查询数据量较小、语句相对简单，大多使用索引连接表之间的数据。这种情况下，优化器大多会用 Nested Loop Join 算法；而 OLAP 业务中的查询数据量较大，关联表的数量非常多，所以用 Hash Join 算法，直接扫描全表效率会更高。

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 = ?过滤出最后数据。

为了深入理解优化器驱动表的选择，咱们先来看下面这条 SQL：

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 查看得到执行计划如下所示：

上面的表 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 为驱动表。

Hash Join

MySQL 中的第二种 JOIN 算法是 Hash Join，用于两张表之间连接条件没有索引的情况。
有同学会提问，没有连接，那创建索引不就可以了吗？或许可以，但：
如果有些列是低选择度的索引，那么创建索引在导入数据时要对数据排序，影响导入性能；
二级索引会有回表问题，若筛选的数据量比较大，则直接全表扫描会更快。
对于 OLAP 业务查询来说，Hash Join 是必不可少的功能，MySQL 8.0 版本开始支持 Hash Join 算法，加强了对于 OLAP 业务的支持。

所以，如果你的查询数据量不是特别大，对于查询的响应时间要求为分钟级别，完全可以使用单个实例 MySQL 8.0 来完成大数据的查询工作。

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

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

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

关于子查询

结论：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'
	);

从中可以看到，子查询的逻辑非常清晰：通过 NOT IN 查询不在订单表的用户有哪些。

不过上述查询是一个典型的 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 执行计划，这样一来，会显著的提升性能。

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

经典问题是：“ IN 和EXISTS 哪个性能更好？”要回答这个问题，我们看一个例子。
针对开篇的 NOT IN 子查询，你可以改写为 NOT EXISTS 子查询，重写后的 SQL 如下所示：

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：依赖子查询。

依赖子查询的优化

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

我们以下面这条 SQL 为例：

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 重写优化哦。

DEPENDENT SUBQUERY 的优化，一般是重写为派生表进行表连接。

哪些场景不建议分区表

分区表在13年的时候，做APT系统的时候有用过，当时每天增加的数据达到上亿级别。所以当时用了分区表来支持按时间段查询。但之后近8年中就没有用过分区表了。所以还是不建议用分区表。

简单来说，分区表就是把物理表结构相同的几张表，通过一定算法，组成一张逻辑大表。这种算法叫“分区函数”，当前 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 中的分区表是把一张大表拆成了多张表，每张表有自己的索引，从逻辑上看是一张表，但物理上存储在不同文件中。

另外，对于唯一索引的实现，可能和你原本的理解有些不同，我们接着往下看。

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

在 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);

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

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 的实现），这样就能避免局部唯一的问题。

分区表的误区：性能提升

分区表技术不是用于提升 MySQL 数据库的性能，而是方便数据的管理。分区表还会引入新的性能问题，比如非分区列的查询。非分区列的查询，即使分区列上已经创建了索引，但因为索引是每个分区文件对应的本地索引，所以要查询每个分区。接着，我们看一下这条 SQL 以及它的执行计划：

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 的时间。

所以，分区表设计时，务必明白你的查询条件都带有分区字段，否则会扫描所有分区的数据或索引。所以，分区表设计不解决性能问题，更多的是解决数据迁移和备份的问题。

业务上不建议使用mysql分区表

关于JOIN的算法：https://www.jianshu.com/p/47db8ac001ea

gonghaiyu

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
1
评论
MySQL索引的正确使用姿势

B+树索引索引设计是数据库设计最重要的一环。InnoDB 存储引擎支持的索引有 B+ 树索引、全文索引、R 树索引。下面重点讲下B+树索引。那为什么关系型数据库都热衷支持 B+树索引呢？因为它是目前为止排序最有效率的数据结构。像二叉树，哈希索引、红黑树、SkipList，在海量数据基于磁盘存储效率方面远不如 B+ 树索引高效。B+树索引的特点是：基于磁盘的平衡二叉树，但树非常矮，通常为 3~4 层，能存放千万到上亿的排序数据。树矮意味着访问效率高，从千万或上亿数据里查询一条数据，只用 3、4 次 I
复制链接

扫一扫

专栏目录