【mysql】千万级数据MySQL索引优化实例

【一】场景描述

针对千万级记录的MySQL有以下几种优化策略：
（1）分片（拆分表和数据库）
（2）创建汇总表
（3）修改查询以使用多个子查询

虑单个 MySQL 表包含数千万条记录的场景，表设计很差，业务规则不允许将 SQL 查询拆分为多个子查询，在这种情况下，开发人员可以尝试通过优化 SQL 来实现他们的查询目标。

当单个 MySQL 表包含数千万条记录时，就会出现一些特殊情况。本篇主要讨论极端情况下的SQL优化策略。

【二】生成数千万条记录

表结构如下：

create table `orders`（
  `order_id` int  NOT  NULL AUTO_INCREMENT，
  `user_id` int  DEFAULT  NULL，
  `order_date` date  NOT  NULL，
  `total_amount` decimal（10，2 ）NOT  NULL，
  PRIMARY KEY（`order_id`），
  KEY`idx_user_id`（`user_id`）使用BTREE，KEY`idx_user_amount` 
  （`user_id`，`total_amount`）使用BTREE ）ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci；创建表`users`（  `user_id` int NOT NULL AUTO_INCREMENT，  `username` varchar（50 ）COLLATE utf8mb4_general_ci NOT NULL ,   `email` varchar ( 100 ) COLLATE utf8mb4_general_ci NOT NULL ,   `created_at`时间戳NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`user_id`),   KEY `idx_user_id` (`user_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;
 

生成数据的存储过程如下：

-- 生成 1000 个用户的存储过程
CREATE DEFINER = `root`@`localhost` PROCEDURE `create_users`() 
BEGIN 
    DECLARE i INT  DEFAULT  0 ; 
    DECLARE total_users INT  DEFAULT  1000 ; -- 调整用户数量
    DECLARE rnd_username VARCHAR ( 50 ); 
    DECLARE rnd_email VARCHAR ( 100 ); 
WHILE
    i < total_users DO 
        -- 生成随机用户名和电子邮件
        SET rnd_username = CONCAT( 'User' , FLOOR ( 1  + RAND() *  10000000 )); -- 假设唯一的用户名
        SET rnd_email = CONCAT(rnd_username, '@example.com' ); -- 假设唯一的电子邮件
        -- 将数据插入用户表
        INSERT  INTO users (username, email) VALUES (rnd_username, rnd_email); SET
 i = i + 1 ; END WHILE; END
         
    

订单数据生成存储过程：

CREATE DEFINER = `root`@`localhost` PROCEDURE `generate_orders`() 
BEGIN 
    DECLARE i INT  DEFAULT  0 ; 
    DECLARE total_users INT  DEFAULT  1000 ; -- 用户数量
    DECLARE total_orders_per_user INT  DEFAULT  1000 ; -- 每个用户的订单数量
    DECLARE rnd_user_id INT ; 
    DECLARE rnd_order_date DATE ; 
    DECLARE rnd_total_amount DECIMAL ( 10 , 2 ); 
    DECLARE j INT  DEFAULT  0 ; 
WHILE
    i < total_users DO 
        -- 获取用户 ID 
        SELECT user_id INTO rnd_user_id FROM users LIMIT i, 1 ; 
WHILE
        j < total_orders_per_user DO 
            -- 生成订单日期和总金额
            SET rnd_order_date = DATE_ADD( '2020-01-01' , INTERVAL  FLOOR (RAND() *  1096 ) DAY ); -- 2020-01-01 至 2022-12-31 之间的随机日期
            SET rnd_total_amount = ROUND(RAND() *  1000 , 2 ); -- 0 到 1000 之间的随机总金额
            -- 将数据插入订单表
            INSERT  INTO orders (user_id, order_date, total_amount) VALUES (rnd_user_id, rnd_order_date, rnd_total_amount); SET
 j = j + 1 ; END WHILE; SET j = 0 ; SET i = i + 1 ; END WHILE; END

users分离和数据的生成orders允许多次调用generate_orders存储过程以利用多线程。

首先调用call create_users()，然后打开15个窗口调用订单存储过程call generate_orders()。

整个过程会产生1000个用户和1510001000，相当于1500万条订单记录。

【三】原始sql分析

这是一个非常简单的 SQL 查询，用于计算每个用户的订单总金额。

如果没有默认创建任何索引，则此查询需要超过 190 秒才能执行。

-- 第一个版本
SELECT a. * , sum (b.total_amount) as total 
FROM users a 
LEFT  JOIN orders b ON a.user_id = b.user_id 
GROUP  BY a.user_id;

解释分析如下：

可以看到，没有使用索引，类型为“ALL”，也就是全表扫描。

执行时间：191秒。

【四】第一次优化：常规索引

在 SQL 条件中使用的所有列上创建索引，包括WHERE、JOIN和SUM。

在订单 （user_id）上创建索引 idx_orders_user_id；在订单（total_amount）
上创建索引 idx_orders_total_amount ；在用户（user_id）
上创建索引 idx_users_user_id ；

SQL 查询保持不变：

-- 第一个版本
SELECT a. * , sum (b.total_amount) as total 
FROM users a 
LEFT  JOIN orders b ON a.user_id = b.user_id 
GROUP  BY a.user_id;

让我们检查一下解释结果：

类型为“index”或“ref”，所有查询都使用索引。

但结果却令人失望，执行时间增加到了460多秒。

这表明由于 MySQL 使用主键来定位关联字段的机制导致查询变慢。

【五】第二次优化：覆盖索引

覆盖索引是包含满足查询所需的所有列的索引，允许直接从索引执行查询而无需访问实际的数据行。

-- 暂时不删除常规索引
-- 删除 INDEX idx_orders_user_id ON orders; --
删除 INDEX idx_orders_total_amount ON orders 
;在orders ( total_amount, user_id ) 上
创建INDEX idx_orders_total_amount_user_id ;在 orders (user_id, total_amount) 上创建 INDEX idx_orders_user_id_total_amount;

对 1500 万条记录创建索引需要 300 多秒，因此应谨慎地进行索引。

SQL 查询保持不变：

-- 第一个版本
SELECT a. * , sum (b.total_amount) as total 
FROM users a 
LEFT  JOIN orders b ON a.user_id = b.user_id 
GROUP  BY a.user_id;

检查一下解释结果：

我们可以看到orders表的类型已经从“index”变为“ref”。

查询时间现已从 460 多秒减少到 10 秒。

这个结果证明了覆盖索引可以提高查询性能。

这里的关键观察是只有索引idx_orders_user_id_total_amount减少了查询时间，而idx_orders_total_amount_user_id没有。

这部分是由于 MySQL 关键字的执行顺序（假设的，因为没有找到明确的来源）。

MySQL执行顺序：

shell from 
on 
join 
where 
group  by 
having 
select 
distinct 
union (全部) 
order  by
 limit

覆盖索引使用函数WHERE之前的子句，与 的索引创建顺序一致。
SELECTSUMidx_orders_user_id_total_amount

删除订单上的索引idx_orders_user_id ；
删除订单上的索引 idx_orders_total_amount ；
删除订单上的索引 idx_orders_total_amount_user_id ；

删除冗余索引表明执行时间保持不变，仍为 10 秒。

因此这里的优化策略是使用覆盖索引来有效命中索引。

【六】第三次优化：减少数据量

减少数据量包括删除不必要的数据或进行架构调整。实现此目的的一种方法是拆分表。

通过这种方式，数据量可以从几千万级减少到几百万甚至几十万级，大大提高查询速度。

-- 优化三：减少数据量
SELECT a. * , sum (b.total_amount) as total 
FROM users a 
LEFT  JOIN orders b ON a.user_id = b.user_id 
WHERE a.user_id >  1033 
GROUP  BY a.user_id;

解释结果如下：

我们可以看到，该users表的类型是“范围”，过滤了一部分数据。

查询时间由原来的10秒减少到7秒，证明减少数据量是有效的。

【七】第四次优化：小表驱动大表

在MySQL中，优化器通常根据查询条件和表大小选择最合适的驱动表。

“小表驱动大表”策略涉及在连接查询中选择较小的表作为驱动表，以减少内存使用和处理时间。

基于第三次优化的结果，我们来尝试一下这个策略。

-- 第三个版本，小表驱动大表，效果不显著
SELECT a. * , sum (b.total_amount) as total 
FROM users a 
LEFT  JOIN ( SELECT user_id, total_amount FROM orders c WHERE c.user_id >  1033 ) b ON a.user_id = b.user_id 
WHERE a.user_id >  1033 
GROUP  BY a.user_id;

此更改将LEFT JOIN表修改为子查询，提前过滤一些数据。

解释结果如下：

可以看出，解释结果没有太大变化，实际执行性能保持不变。

虽然“小表驱动大表”策略在这里并不有效，但根据具体的业务逻辑，它仍然可以是一种有效的优化策略。

【八】第五次优化：强制使用索引

当该IN子句用于涉及数千万条记录的查询时，设计不良的索引可能会导致索引效率低下，影响查询性能。

正常情况下，MySQL 优化器会选择最佳的执行计划，包括合适的索引。但是，对于大数据量的IN子句查询，MySQL 可能无法有效利用索引，从而导致全表扫描或索引效率低下。

以下 SQL 演示了这一点，由于IN数据非稀疏，强制索引和不强制索引会产生类似的结果：

-- 第五版，强制索引
SELECT a. * , sum (b.total_amount) as total 
FROM users a 
LEFT  JOIN orders b FORCE INDEX (idx_orders_user_id_total_amount) ON a.user_id = b.user_id 
WHERE b.user_id IN ( 1033 , 1034 , 1035 , 1036 , 1037 , 1038 ) 
GROUP  BY a.user_id; 
-- 第五版，不强制索引
SELECT a. * , sum (b.total_amount) as total 
FROM users a 
LEFT  JOIN orders b ON a.user_id = b.user_id 
WHERE b.user_id IN ( 1033 , 1034 , 1035 , 1036 , 1037 , 1038 ) 
GROUP  BY a.user_id;

在两种情况下，查询时间都少于一秒。

在实际业务场景中，可能会出现这样的情况：尽管按照解释结果命中了索引，但查询仍然很慢。在这种情况下，可以尝试强制索引。

【九】优化策略汇总

（1）尽早命中索引，使用小表驱动大表。
（2）使用强制索引处理IN子句效率低下的问题。
（3）使用覆盖索引来避免表查找。

思路
（1）对于接近数千万的数据量，使用表分区，例如基于用户ID模数。
（2）用汇总表替换子查询来命中索引，例如生成每日或每月的汇总表。
（3）将冗余的连接字段直接放在单表中，方便单表查询。
（4）命中索引，用空间换取时间，这是本文分析的核心场景。

打索引的核心是覆盖索引，对于千万级的数据量，可能还需要强制索引。

【十】索引分析的type含义

在MySQL的EXPLAIN查询结果中，该type字段表示查询所使用的访问类型，代表查询执行时访问的方式。

根据访问类型，MySQL 的查询优化器将选择不同的执行计划。以下是该type字段的可能值及其含义：

（1）系统：最好的情况，表明查询返回单行结果，通常是通过直接访问PRIMARY KEY或唯一索引。
（2）const：表示 MySQL 在查询中发现一个常量值，该查询是在连接的第一个表中进行的。由于这是一个常量条件，因此 MySQL 只会从表中读取一行数据，例如，通过主键访问一行数据。
（3）eq_ref：与 类似const，但使用索引。此类查询使用唯一索引访问表，其中每个索引键值只有一行匹配。在使用主索引或唯一索引的连接中很常见。
（4）ref：表示查询使用非唯一索引来查找值。返回与单个值匹配的所有行，通常出现在使用非唯一索引或索引前缀的连接中。
（5）range：表示查询使用索引进行范围扫描，常见于具有范围条件的查询，例如，，，，BETWEEN等。IN()><
（6）index：表示 MySQL 将扫描整个索引来查找所需的行，通常是在没有合适索引可用时。
（7）all：表示 MySQL 将执行全表扫描来查找所需的行，这是最糟糕的情况。

通常，type字段值从好到坏的排序为system、const、eq_ref、ref、range、index，all但实际情况取决于具体查询、表结构和索引使用情况。更好的查询性能通常对应更好的type值。

【十一】MySQL 的表查找机制

在MySQL中，表查找（也称为“ref”或“Bookmark Lookup”）是指MySQL首先使用索引找到满足条件的行的位置，然后在主表中查找实际数据行的过程。

此过程通常发生在覆盖索引不能满足所有查询要求的查询中。

当索引不能完全满足查询时，MySQL 需要查找主表以获取更多信息。这通常发生在以下情况下：
（1）非覆盖索引查询：当查询需要返回索引中未包含的额外列时，MySQL 需要在主表中查找这些额外的列。
（2）使用索引范围条件：当使用范围条件（BETWEEN、、等）时，如果索引只能定位范围的开始，则 MySQL 需要检查主表以查找满足范围条件的完整行>。<
（3）使用聚集索引但所需列不在索引中：在具有聚集索引的表中，如果所需列不在聚集索引中，则 MySQL 需要在主表中查找这些列。

当 MySQL 执行表查找时，会发生额外的磁盘访问，因为它需要从主表读取数据，这可能会降低性能，尤其是在大型表或高并发环境中。

为了最大限度地减少表查找，请考虑以下事项：
（1）创建覆盖索引：确保查询所需的所有列都包含在索引中，以避免表查找。
（2）优化查询语句：尽量避免使用范围条件，或者保证所有的过滤条件都能被索引完全匹配。
（3）考虑表设计：在索引中包含经常查询的列以减少表查找的需要。