关于后端:MySQL千万数据查询优化之路

MySQL千万数据查问优化之路

本文次要针对 MySQL 在千万级别数据的分页查问性能进行优化, 上面是整个优化的过程.

先说论断

先说论断, MySQL 在千万级别数据的分页查问性能次要受到 2 个因素的影响:

• 查问的偏移量
• 查问的数据量

查问的偏移量优化

当 MySQL 执行查问语句分页 LIMIT 时, 有 2 个步骤须要先依照指定的排序规定对数据进行排序, 而后跳过指定的偏移量。

如果查问的偏移量比拟大, 那么排序的工夫就会比拟长(B+树 索引能够极大优化该阶段性能)

然而 B+树 在跳过指定的偏移量时, 须要程序遍历, O(n) 的复杂度, 千万级的偏移量也是比较慢

优化思路:

• 给排序的字段加上B+树索引
• 应用子查问确定查问范畴(比方, 主键的范畴, BETWEEN 等)
• 连表查问, 小表驱动大表, 通过小表的索引来确定大表的范畴, 缩小偏移量

查问的数据量优化

• 指定列代替 SELECT *
• 缩小不须要的列, 特地是大字段
• 一次尽可能按需查问较少的数据条数
• 缓存查问后果 (比方 redis) 来缩小查问次数

筹备数据

建表

CREATE TABLE `big_tables` (  `id` bigint unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(256) DEFAULT NULL,  `age` bigint DEFAULT NULL,  `data` longblob,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10010001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

导入数据

我应用的是 golang gorm 来导入数据, 代码如下:

var (    configFile = "../config-dev.yaml"    DB         *gorm.DB)type BigTable struct {    ID   uint64 `gorm:"column:id;primary_key;auto_increment"`    Name string `gorm:"column:name"`    Age  int    `gorm:"column:age"`    Data []byte `gorm:"column:data"`}func TestBitTable_InsertData(t *testing.T) {    var err error    DB.AutoMigrate(&BigTable{})    // 敞开日志    DB.Logger = logger.Default.LogMode(logger.Silent)    // 批量插入 1000w 条数据, 每次插入 10000 条    batches := 1000    size := 10_000    bigTables := make([]*BigTable, 0, size)    for i := 0; i < batches; i++ {        for i := 0; i < size; i++ {            bigTables = append(bigTables, &BigTable{                Name: utils.RandString(10),                Age:  utils.RandInt(10),                Data: utils.RandBytes(10),            })        }        var task = func(idx int, db *gorm.DB) {            err = db.CreateInBatches(bigTables, size).Error            if err != nil {                t.Error(err)            }            log.Printf("批次: %v, 实现 \n", idx)        }        task(i, DB)        // 清空        bigTables = bigTables[:0]    }    log.Printf("\n插入实现\n")}

一分钟左右就能够导入 1000w 条数据

查看导入的数据

mysql> select count(*) from big_tables;+----------+| count(*) |+----------+| 10010000 |+----------+1 row in set (1.27 sec)

一般查问

查问 offset 为 1w, 10w, 100w, 1000w 的 1 条数据

命令

select * from big_tables limit 10000, 1;select * from big_tables limit 100000, 1;select * from big_tables limit 1000000, 1;select * from big_tables limit 10000000, 1;

后果

mysql> select * from big_tables limit 10000, 1;+-------+------------+-----+------------+| id    | name       | age | data       |+-------+------------+-----+------------+| 10001 | I6pC5NBFD9 |   7 | x4zXHhnPnW |+-------+------------+-----+------------+1 row in set (0.10 sec)mysql> select * from big_tables limit 100000, 1;+--------+------------+-----+------------+| id     | name       | age | data       |+--------+------------+-----+------------+| 100001 | PzpzEZDX9G |   0 | B48IvBLlWo |+--------+------------+-----+------------+1 row in set (0.13 sec)mysql> select * from big_tables limit 1000000, 1;+---------+------------+-----+------------+| id      | name       | age | data       |+---------+------------+-----+------------+| 1000001 | 4niiNSTHtx |   5 | tdCK9VuVWJ |+---------+------------+-----+------------+1 row in set (0.52 sec)mysql> select * from big_tables limit 10000000, 1;+----------+------------+-----+------------+| id       | name       | age | data       |+----------+------------+-----+------------+| 10000001 | R0HWlAyf7R |   0 | kHDTpsmtcg |+----------+------------+-----+------------+1 row in set (5.86 sec)

表格:

能够看到 1w 到 10w 的查问工夫根本不变, 然而 100w 到 1000w 的查问工夫根本线性增长

因为 B+树 在跳过指定的偏移量时, 须要程序遍历, O(n) 的复杂度

查问 offset 为 10, 100, 1000, 10000 条数据

命令

select * from big_tables limit 100000, 10;select * from big_tables limit 100000, 100;select * from big_tables limit 100000, 1000;select * from big_tables limit 100000, 10000;

后果

mysql> select * from big_tables limit 100000, 10;# 数据太多, 省略10 rows in set (0.21 sec)mysql> select * from big_tables limit 100000, 100;# 数据太多, 省略100 rows in set (0.35 sec)mysql> select * from big_tables limit 100000, 1000;# 数据太多, 省略1000 rows in set (1.93 sec)mysql> select * from big_tables limit 100000, 10000;# 数据太多, 省略10000 rows in set (21.20 sec)

表格

能够看到, 数据量越大, 查问工夫越长. 数据 1000-10000 的查问工夫根本线性增长 (这里我的 MySQL 就在本机上, 如果是近程 MySQL 网络 IO 产生的工夫将更长)

然而个别查问的数据量不会太大, 个别都是 10 条左右

优化计划如下:

• 指定列代替 SELECT *
• 缩小不须要的列, 特地是大字段
• 一次尽可能按需查问较少的数据条数
• 缓存查问后果 (比方 redis) 来缩小查问次数

优化计划比较简单, 容易了解, 前面就不再赘述了

优化: 偏移量导致的查问慢

1. 子查问

先查问 id 的地位, 而后再依据 id 的地位查问数据

命令

select * from big_tables where id >= (    select id from big_tables limit 10000000, 1) limit 0, 1;

后果

mysql> select * from big_tables where id >= (    select id from big_tables limit 10000000, 1) limit 0, 1;+----------+------------+-----+------------+| id       | name       | age | data       |+----------+------------+-----+------------+| 10000001 | R0HWlAyf7R |   0 | kHDTpsmtcg |+----------+------------+-----+------------+1 row in set (2.69 sec)

表格

能够看到, 应用子查问后, 查问工夫缩小了50%以上.

然而还是在秒级别, 达不到毫秒级别的业务需要

2. 子查问 EXPLAIN 剖析

子查问命令

explain select * from big_tables where id >= (    select id from big_tables limit 10000000, 1) limit 0, 1;

后果

mysql> explain select * from big_tables where id >= (    select id from big_tables limit 10000000, 1) limit 0, 1;+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+|  1 | PRIMARY     | big_tables | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |   19290 |   100.00 | Using where ||  2 | SUBQUERY    | big_tables | NULL       | index | NULL          | PRIMARY | 8       | NULL | 9750719 |   100.00 | Using index |+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+2 rows in set (3.20 sec)

子查问(第二行):

• 子查问的 type 是 index, 示意应用了索引扫描全表
• 子查问的 key 是 PRIMARY, 示意应用了主键索引
• 子查问的 rows 是 9750719, 示意扫描了 9750719 行数据 (粗略计算的, 因为 MySQL 每页 16KB)
• 子查问的 Extra 是 Using index, 示意应用了笼罩索引

主查问(第一行):

• 主查问 type 是 range, 示意应用了索引范畴扫描
• 主查问 key 是 PRIMARY, 示意应用了主键索引
• 主查问 rows 是 19290, 示意扫描了 19290 行数据
• 主查问 Extra 是 Using where, 示意应用了 where 条件

从下面能够看出: 子查问应用了索引扫描全表, 扫描行数量千万级别, 所以查问工夫很长

给主键加上惟一索引

给主键加上 B+树 的惟一索引

命令

# add unique indexalter table big_tables add unique index id(id) using btree;# query2select * from big_tables where id >= (    select id from big_tables limit 10000000, 1) limit 0, 1;# explain query2explain select * from big_tables where id >= (    select id from big_tables limit 10000000, 1) limit 0, 1;

后果

mysql> # add unique indexalter table big_tables add unique index id(id) using btree;Query OK, 0 rows affected (35.82 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> # query2select * from big_tables where id >= (    select id from big_tables limit 10000000, 1) limit 0, 1;+----------+------------+-----+------------+| id       | name       | age | data       |+----------+------------+-----+------------+| 10000001 | R0HWlAyf7R |   0 | kHDTpsmtcg |+----------+------------+-----+------------+1 row in set (1.25 sec)mysql> # explain query2explain select * from big_tables where id >= (    select id from big_tables limit 10000000, 1) limit 0, 1;+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+|  1 | PRIMARY     | big_tables | NULL       | range | PRIMARY,id    | PRIMARY | 8       | NULL |   19290 |   100.00 | Using where ||  2 | SUBQUERY    | big_tables | NULL       | index | NULL          | id      | 8       | NULL | 9750719 |   100.00 | Using index |+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+2 rows in set (1.47 sec)

表格

能够看到, 给主键加上惟一索引后, 查问工夫缩小了50%以上

如果主键不是递增的, 比方是字符串, 须要用 IN 查问

因为某些 mysql 版本不反对在 in 子句中应用 limit, 所以这里多嵌套了一层子查问

select * from big_tables where id in (    select id from (        select id from big_tables limit 10000000, 1    ) as t) limit 0, 1;

如果主键是线性递增, 能够应用 WHERE 优化

下面咱们晓得, 查问的耗费次要是在索引遍历的过程中, 如果id是间断递增的, 能够应用 WHERE 来优化

# query3select * from big_tables where id >= 10000000 limit 0, 1;# explain query3explain select * from big_tables where id >= 10000000 limit 0, 1;

后果

mysql> # query3select * from big_tables where id >= 10000000 limit 0, 1;+----------+------------+-----+------------+| id       | name       | age | data       |+----------+------------+-----+------------+| 10000000 | Hey8TWX966 |   7 | kSjxDkL1qj |+----------+------------+-----+------------+1 row in set (0.08 sec)mysql> # explain query3explain select * from big_tables where id >= 10000000 limit 0, 1;+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+|  1 | SIMPLE      | big_tables | NULL       | range | PRIMARY,id    | PRIMARY | 8       | NULL | 19298 |   100.00 | Using where |+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+1 row in set (0.13 sec)

性能从原来的 5.86s 升高到了 0.08s, 晋升 73 倍.

因为 rows 扫描行数不再是千万级别, 而只有一页的大小

为什么给主键加上惟一索引查问更快

在 MySQL 中，新增的惟一索引须要查问 2 次，第一次是查问索引树，第二次是查问数据页，而主键索引是惟一索引，所以查问主键索引时，只须要查问一次索引树即可。

然而在测试中, 1000万的偏移量的查问下, 再给主键加上惟一索引查问更快, 这是为什么呢?

参考:

• https://dba.stackexchange.com/questions/290617/why-does-mysql...
• https://stackoverflow.com/questions/75937219/why-add-unique-index-to-primary-index-is-faster

本文由mdnice多平台公布