首页 > 数据库 >面试官：Mysql千万级大表如何进行深度分页优化？

面试官：Mysql千万级大表如何进行深度分页优化？

时间：2024-02-01 10:13:54浏览次数：44  
标签：COMMENT 面试官 create Mysql 千万级 查询 time 100 order

背景

假如有一张千万级的订单表，这张表没有采用分区分表，也没有使用ES等技术，分页查询进行到一定深度分页之后（比如1000万行后）查询比较缓慢，我们该如何进行优化？

数据准备

订单表结构如下：

CREATE TABLE `t_order` (  
    `id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',  
    `order_no` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '订单编号',  
    `customer_no` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '客户编号',  
    `order_status` TINYINT ( 4 ) NOT NULL DEFAULT 0 COMMENT '订单状态',  
    `warehouse_code` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '发货地仓库编码',  
    `country` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '收货人国家',  
    `state` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人州',  
    `city` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人城市',  
    `street` VARCHAR ( 256 ) NOT NULL DEFAULT '' COMMENT '收货人街道',  
    `zip_code` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人邮编',  
    `contact_email` VARCHAR ( 128 ) NOT NULL DEFAULT '' COMMENT '收货人邮箱',  
    `contact_name` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人姓名',  
    `contact_mobile` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人手机号',  
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  
    `deleted` TINYINT ( 2 ) NOT NULL DEFAULT 0 COMMENT '是否已被删除',  
    PRIMARY KEY ( `id` ),  
    KEY `idx_customer` ( `customer_no`, `deleted` ),  
    KEY `idx_create_time` ( `create_time`, `deleted` )  
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = '销售订单表';

其中Mysql版本为8.0。我们使用Python脚本向表中插入2000万条数据。

import pymysql  
from faker import Faker  
import random  
from datetime import datetime  
from concurrent.futures import ThreadPoolExecutor  
  
# MySQL 连接配置  
db_config = {  
   'host': 'your_database_host', 
   'user': 'your_database_user', 
   'password': 'your_database_password', 
   'database': 'your_database_name'
}  
  
# 创建 MySQL 连接  
conn = pymysql.connect(**db_config)  
cursor = conn.cursor()  
  
# 使用 Faker 生成模拟数据  
fake = Faker() 

# 获取国家下发货仓库编码  
def generate_warehousecode(country):  
    if country == "US":  
        return "US-"+random.choice(["WEST", "EAST", "MIDDLE", "SOUTH", "NORTH"])+"-0" + str(random.choice([1, 2, 3, 4, 5]))  
    else:  
        return country + "00" + str(random.choice([1, 2, 3, 4, 5]))

# 插入 t_order 表数据（多线程并发，每个线程插入1万条，共2000个线程）  
def insert_data_thread(thread_id):  
    # 创建 MySQL 连接  
    conn = pymysql.connect(**db_config)  
    cursor = conn.cursor()  
  
    order_data = []  
    for _ in range(10000):  
        order_no = "OC"+ fake.uuid4()[:12]  # 取前16位  
        customer_no = fake.uuid4()[:16]
        order_status = random.choice([1, 2, 3, 4, 5])  
        country = random.choice(  
            ["CA", "US", "MX", "JP", "UK", "TR", "DE", "ES", "FR", "IT", "NL", "PL", "SE", "BR", "CN"])  
        warehouse_code = generate_warehousecode(country)  
        state = fake.uuid4()[:16]  
        city = fake.uuid4()[:16]  
        street = fake.uuid4()  
        zip_code = fake.uuid4()[:6]  
        contact_email = fake.email()  
        contact_name = fake.name()  
        contact_mobile = fake.phone_number()  
        create_time = fake.date_time_between(start_date=datetime(2019, 1, 1), end_date=datetime.now())  
        update_time = create_time  
        deleted = 0  # 默认未删除  
  
        cursor.execute("""  
            INSERT INTO t_order (                order_no, customer_no, order_status, warehouse_code, country,                state, city, street, zip_code, contact_email, contact_name,                contact_mobile, create_time, update_time, deleted            )            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)        """, (order_no, customer_no, order_status, warehouse_code, country,  
              state, city, street, zip_code, contact_email, contact_name,  
              contact_mobile, create_time, update_time, deleted))  
  
        order_data.append((cursor.lastrowid, order_no, customer_no, create_time))  # 保存插入的行的 ID  
    # 提交 t_order 数据插入  
    conn.commit()  
    print(thread_id+ "已经跑完10000条数据。。。。。。。。。")  
    # 关闭数据库连接  
    cursor.close()  
    conn.close()  
  
# 使用 ThreadPoolExecutor 并发插入  
with ThreadPoolExecutor(max_workers=10) as executor:  # 可以根据需要调整最大线程数  
    executor.map(insert_data_thread, range(500))

image.png

问题复现

导出数据时我们需要按照时间倒序。所以我们先执行以下sql查询前100条

select * FROM t_order ORDER BY create_time desc LIMIT 100;

共花费210ms。执行计划如下：
image.png

然后我们继续执行sql，我们从第100万行开始取100条数据：

select * FROM t_order ORDER BY create_time desc LIMIT 1000000,100;

此时耗时3分2秒，耗时明显增加。执行计划如下：
image.png
image.png
由执行计划看，此时索引已经失效了。。。。

我们继续执行sql，从第1000万行开始取100条数据：

select * FROM t_order ORDER BY create_time desc LIMIT 10000000,100;

此时耗时4分14秒，时间真是太长了，执行计划如下：
image.png

image.png

后面还有接近1000万条数据没有取出来，直接就废了。

原因分析

当我们使用 LIMIT offset, count 查询语句进行深度分页查询时，例如 LIMIT 10000000,100 ，会发生以下过程：

  1. MySQL首先会根据给定条件从相应的索引树中查找m+n条记录。对于聚集索引来说，它直接找到需要的结果即丢弃前offset条数据，返回count条数据并返回；而对于二级索引，则可能涉及回表操作。
  2. 如果使用的是二级索引，在查到m+n条记录后还需要通过这些记录所关联的主键ID去聚集索引里再次搜索出完整的行数据，然后再丢弃掉前offset条数据，返回count条数据。因此在这个过程中可能会产生大量的“回表”操作，这将导致性能下降。

我们借助B+ Tree Visualization演示一下这张表的索引结构：

  • 聚集索引（主键ID）

聚集索引（主键ID）

  • 二级索引（idx_create_time）

二级索引（create_time）

以上述例子来说，当我们查询LIMIT 10000000,100时，它会先从二级索引中查询10000000+100条记录对应的ID，然后再用这些记录的ID去聚集索引中查询ID对应的记录，然后舍弃掉前10000000条数据，返回后100条数据。

所以当offset+count量很大时，Mysql的执行器认为全表扫描的性能更由于使用索引，所以也导致索引失效。所以我们要做的尽可能的减少回表的记录数量。

解决方案

使用子查询

我们改造sql，通过一个子查询按照create_time倒排，获取第offset + 1条记录的最新的create_time，create_time直接从二级索引上可以获取，不会进行回表，然后我们再根据这个create_time传递到主查询时，取100条数据，即回表数据也仅仅只有count条即100条数据，大大减少了回表的记录数量。

SELECT * FROM t_order  
WHERE create_time <= (
SELECT create_time FROM t_order ORDER BY create_time desc LIMIT 1000000,1
)
ORDER BY create_time desc LIMIT 100;

查询第100万时耗时556毫秒。

执行结果，执行计划
image.png
image.png
可以看出主查询以及子查询都使用到了索引，回表查询的数据记录数也大大减少。

继续查询到第1000万行时耗时接近6秒。
执行结果，执行计划

image.png

image.png
可以看出主查询以及子查询都使用到了索引，回表查询的数据记录数也大大减少。

这种方式需要create_time 的分布是相对均匀的，否则可能会导致某个时间段内的数据较多，影响查询性能。

INNER JOIN

我们改造sql，create_time跟id都存储在二级索引中，我们获取这两列值不需要回表，所以我们创建一个偏移量为offset，个数为count并且包含create_time以及id的临时表，临时表中数据不需要回表。然后再跟自身通过主键ID进行关联，仅需要回表count条数据，大大减少了回表的记录格式。同时也使用了主键索引关联，效率也大大提高。

SELECT torder.* FROM  t_order torder
	INNER JOIN (
	SELECT id FROM t_order ORDER BY create_time DESC LIMIT 1000000,100 
	) tmp ON torder.id = tmp.id 
ORDER BY
	create_time DESC

查询第100万时耗时260毫秒。
执行结果，执行计划。
image.png

image.png

继续查询到第1000万行时耗时接近2秒
执行结果，执行计划
image.png
image.png

这种方式要保证INNER JOIN使用了合适的索引。

SEARCH AFTER

每次查询都保留上次的最小的create_time，然后下次查询只查询比上一页的create_time小的数据。单表查询，并且使用索引，回表数据少，不需要子查询以及关联查询，查询效率高。类似ES的SEARCH AFTER的查询方式。

-- 我们模拟连续分页到第1000000页，最小的一条数据的create_time
SELECT * FROM t_order  
ORDER BY create_time ASC  LIMIT 1000000, 1

SELECT * FROM t_order  
WHERE create_time <= '2023-01-22 00:00:00' 
ORDER BY create_time desc LIMIT 100;

查询第100万时耗时142毫秒。

执行结果，执行计划
image.png
image.png

继续查询到第1000万行时耗时244毫秒

执行结果，执行计划
image.png

image.png

当然该种方式缺点也很明显：只能支持连续分页，不能支持获取随意页的数据。

其他方案

  • 限制查询范围： 在需求层面，可以限制只能查询前100页数据，或者规定只能获取某个时间段内的数据，从而避免深度分页。
  • 水平分表：考虑将数据按照某个维度进行水平分表，以减小单表的数据量
  • 使用ES，Hive，ClickHouse等OLAP方案

本文已收录于我的个人博客：码农Academy的博客，专注分享Java技术干货，包括Java基础、Spring Boot、Spring Cloud、Mysql、Redis、Elasticsearch、中间件、架构设计、面试题、程序员攻略等

标签：COMMENT,面试官,create,Mysql,千万级,查询,time,100,order
From： https://www.cnblogs.com/coderacademy/p/17994562

相关文章

  • Hive数据线下导入Mysql
    1.背景     最近在处理一个数据量级在1亿左右的数据，没办法mysql不好处理，只能把数据放到大数据集群进行处理，处理好后再把这亿级数据导入本地Mysql。2.实践（1）把需要处理的数据手动传到集群，上传的数据只有一万条左右，经过笛卡尔积以及各种运算后，结果数据条数有一亿左右，文件大小......
  • MySQL 期末总结
    MYSQL一、对表结构进行操作1.主键1）添加主键--1.主键/* 方式一：创建表的时候在字段后面+primarykey 方式二：写完字段之后在指定主键，创建标的字段下面 [constraint<约束名>]primarykey*/usemydb1;createtableemp1( eidint, enamevarchar(20), d......
  • seatunnel-2.3.3测试excel入库mysql
    1.背景客户需要excel导入功能，同时支持导入多种数据源，尝试用seatunnel数据集成工具来实现。2.步骤2.1配置文件./config/v2.excel2mysql.configenv{#YoucansetSeaTunnelenvironmentconfigurationhereexecution.parallelism=1job.mode="BATCH"}source......
  • 数据库MySQL8.0.29安装与备份||了解和掌握MySQL的安装和简单使用和备份数据
    内容：了解和掌握MySQL的安装和简单使用：(1) 了解安装MySQL的软硬件环境和安装方法；(2) 熟悉MySQL的相关基本使用；(3) 熟悉MySQL的构成和相关工具；(4) 通过MySQL的使用来理解数据库系统的基本概念。要求：1. 在微机上安装MySQL数据库系统，为后续实验搭建实验环境，提供前期准备；2. 完成实......
  • Mysql中索引的描述设计
    Mysql中索引的描述设计1，索引是占用存储空间的2，my_myisam.myi和account.ibd存放索引3，查询效率提高，增删改效率降低；索引表以查询为主索引结构 二叉树结构一个根节点下只能有两个节点，当子节点比根节点小在左侧，当比根节点大在二叉树右侧缺点：大数据量时，检索慢，如果都比根节点小会......
  • MySQL介绍
    数据库的由来和基本概念什么是数据库？|Oracle中国【一】数据的发展史早期未出现互联网前，每个人都只可以在本地存储，且存储的数据格式都是根据每个人的需要自由定义的出现网络后，可以在局域网内进行多台计算机的数据共享，而这时，数据的格式只能由人为约束，约定俗成好一套数据存......
  • Mysql中存储引擎InnoDB,MyISAM,MEMORY比较
    Mysql中存储引擎InnoDB,MyISAM,MEMORY比较showENGINES--查看数据库支持的搜索引擎ENGINE=InnoDB--使用的InnoDB引擎CREATETABLE`user1`(`id`bigint(20)NOTNULLDEFAULT'0',`name`varchar(255)DEFAULTNULL,`age`int(11)DEFAULTNULL,`sex`varchar(255)......
  • MYSQL日志
    要开启MySQL的二进制日志（binlog），需按照以下步骤进行操作：1.打开MySQL配置文件：首先，找到MySQL的配置文件my.cnf（或my.ini，视操作系统而定）。2.找到并修改binlog相关配置：在配置文件中找到以下几个参数，如果没有则手动添加：[mysqld]log-bin=mysql-bin#开启binlog，设置binlog......
  • MySQL大表分页查询的坑以及解决方案
    最近在做一个需求，需求内容中有一个功能点是查询指定用户标签里的用户id，这里做了分页查询，分页查询是用mysql的LIMIT设置offset和size值来实现的。在程序执行过程中会发现，如果查询的用户标签数据量很大时会出现慢查询告警，这里已经对mysql表的标签名称和用户id字段都加了索引，并且limi......
  • 数据库研发人员必看的MySQL 8.0新特性
    本文汇总了MySQL8.0面向开发的新特性，总共有12个新特性，有想快速了解8.0新特性的朋友，可以看一下哈文章目录：1.公用表达式支持-CTE2.窗口函数3.表达式作为默认值:4.CHECK支持5.隐藏列-MySQL8.0.236.隐藏索引、降序索引、函数索引(MySQL-8.0.19)7.VALUES语法8.INTERSECT和EXC......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99