背景
假如有一張千萬級(jí)的訂單表，這張表沒有采用分區(qū)分表，也沒有使用ES等技術(shù)，分頁查詢進(jìn)行到一定深度分頁之后（比如1000萬行后）查詢比較緩慢，我們?cè)撊绾芜M(jìn)行優(yōu)化？
數(shù)據(jù)準(zhǔn)備
訂單表結(jié)構(gòu)如下：
CREATE TABLE `t_order` (
`id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
`order_no` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '訂單編號(hào)',
`customer_no` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '客戶編號(hào)',
`order_status` TINYINT ( 4 ) NOT NULL DEFAULT 0 COMMENT '訂單狀態(tài)',
`warehouse_code` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '發(fā)貨地倉庫編碼',
`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 '收貨人手機(jī)號(hào)',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
`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萬條數(shù)據(jù)。
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'
}
# 創(chuàng)建 MySQL 連接
conn = pymysql.connect(**db_config)
cursor = conn.cursor()
# 使用 Faker 生成模擬數(shù)據(jù)
fake = Faker()
# 獲取國家下發(fā)貨倉庫編碼
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 表數(shù)據(jù)（多線程并發(fā)，每個(gè)線程插入1萬條，共2000個(gè)線程）
def insert_data_thread(thread_id):
# 創(chuàng)建 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 # 默認(rèn)未刪除
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 數(shù)據(jù)插入
conn.commit()
print(thread_id+ "已經(jīng)跑完10000條數(shù)據(jù)。。。。。。。。。")
# 關(guān)閉數(shù)據(jù)庫連接
cursor.close()
conn.close()
# 使用 ThreadPoolExecutor 并發(fā)插入
with ThreadPoolExecutor(max_workers=10) as executor: # 可以根據(jù)需要調(diào)整最大線程數(shù)
executor.map(insert_data_thread, range(500))
問題復(fù)現(xiàn)
導(dǎo)出數(shù)據(jù)時(shí)我們需要按照時(shí)間倒序。所以我們先執(zhí)行以下sql查詢前100條
select * FROM t_order ORDER BY create_time desc LIMIT 100;
共花費(fèi)210ms。執(zhí)行計(jì)劃如下：
然后我們繼續(xù)執(zhí)行sql，我們從第100萬行開始取100條數(shù)據(jù)：
select * FROM t_order ORDER BY create_time desc LIMIT 1000000,100;
此時(shí)耗時(shí)3分2秒，耗時(shí)明顯增加。執(zhí)行計(jì)劃如下：
由執(zhí)行計(jì)劃看，此時(shí)索引已經(jīng)失效了。。。。
我們繼續(xù)執(zhí)行sql，從第1000萬行開始取100條數(shù)據(jù)：
select * FROM t_order ORDER BY create_time desc LIMIT 10000000,100;
此時(shí)耗時(shí)4分14秒，時(shí)間真是太長了，執(zhí)行計(jì)劃如下：
后面還有接近1000萬條數(shù)據(jù)沒有取出來，直接就廢了。
原因分析
當(dāng)我們使用?LIMIT offset, count?查詢語句進(jìn)行深度分頁查詢時(shí)，例如?LIMIT 10000000,100?，會(huì)發(fā)生以下過程：
- MySQL首先會(huì)根據(jù)給定條件從相應(yīng)的索引樹中查找m+n條記錄。對(duì)于聚集索引來說，它直接找到需要的結(jié)果即丟棄前
offset條數(shù)據(jù)，返回count條數(shù)據(jù)并返回；而對(duì)于二級(jí)索引，則可能涉及回表操作。 - 如果使用的是二級(jí)索引，在查到m+n條記錄后還需要通過這些記錄所關(guān)聯(lián)的主鍵
ID去聚集索引里再次搜索出完整的行數(shù)據(jù)，然后再丟棄掉前offset條數(shù)據(jù)，返回count條數(shù)據(jù)。因此在這個(gè)過程中可能會(huì)產(chǎn)生大量的“回表”操作，這將導(dǎo)致性能下降。
我們借助B+ Tree Visualization演示一下這張表的索引結(jié)構(gòu)：
- 聚集索引（主鍵ID）
- 二級(jí)索引（idx_create_time）
以上述例子來說，當(dāng)我們查詢LIMIT 10000000,100時(shí)，它會(huì)先從二級(jí)索引中查詢10000000+100條記錄對(duì)應(yīng)的ID，然后再用這些記錄的ID去聚集索引中查詢ID對(duì)應(yīng)的記錄，然后舍棄掉前10000000條數(shù)據(jù)，返回后100條數(shù)據(jù)。
所以當(dāng)offset+count量很大時(shí)，Mysql的執(zhí)行器認(rèn)為全表掃描的性能更由于使用索引，所以也導(dǎo)致索引失效。所以我們要做的盡可能的減少回表的記錄數(shù)量。
解決方案
使用子查詢
我們改造sql，通過一個(gè)子查詢按照create_time倒排，獲取第offset + 1條記錄的最新的create_time，create_time直接從二級(jí)索引上可以獲取，不會(huì)進(jìn)行回表，然后我們?cè)俑鶕?jù)這個(gè)create_time傳遞到主查詢時(shí)，取100條數(shù)據(jù)，即回表數(shù)據(jù)也僅僅只有count條即100條數(shù)據(jù)，大大減少了回表的記錄數(shù)量。
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萬時(shí)耗時(shí)556毫秒。
執(zhí)行結(jié)果，執(zhí)行計(jì)劃
可以看出主查詢以及子查詢都使用到了索引，回表查詢的數(shù)據(jù)記錄數(shù)也大大減少。
繼續(xù)查詢到第1000萬行時(shí)耗時(shí)接近6秒。
執(zhí)行結(jié)果，執(zhí)行計(jì)劃
可以看出主查詢以及子查詢都使用到了索引，回表查詢的數(shù)據(jù)記錄數(shù)也大大減少。
這種方式需要create_time 的分布是相對(duì)均勻的，否則可能會(huì)導(dǎo)致某個(gè)時(shí)間段內(nèi)的數(shù)據(jù)較多，影響查詢性能。
INNER JOIN
我們改造sql，create_time跟id都存儲(chǔ)在二級(jí)索引中，我們獲取這兩列值不需要回表，所以我們創(chuàng)建一個(gè)偏移量為offset，個(gè)數(shù)為count并且包含create_time以及id的臨時(shí)表，臨時(shí)表中數(shù)據(jù)不需要回表。然后再跟自身通過主鍵ID進(jìn)行關(guān)聯(lián)，僅需要回表count條數(shù)據(jù)，大大減少了回表的記錄格式。同時(shí)也使用了主鍵索引關(guān)聯(lián)，效率也大大提高。
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萬時(shí)耗時(shí)260毫秒。
執(zhí)行結(jié)果，執(zhí)行計(jì)劃。
繼續(xù)查詢到第1000萬行時(shí)耗時(shí)接近2秒
執(zhí)行結(jié)果，執(zhí)行計(jì)劃
這種方式要保證INNER JOIN使用了合適的索引。
SEARCH AFTER
每次查詢都保留上次的最小的create_time，然后下次查詢只查詢比上一頁的create_time小的數(shù)據(jù)。單表查詢，并且使用索引，回表數(shù)據(jù)少，不需要子查詢以及關(guān)聯(lián)查詢，查詢效率高。類似ES的SEARCH AFTER的查詢方式。
-- 我們模擬連續(xù)分頁到第1000000頁，最小的一條數(shù)據(jù)的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萬時(shí)耗時(shí)142毫秒。
執(zhí)行結(jié)果，執(zhí)行計(jì)劃
繼續(xù)查詢到第1000萬行時(shí)耗時(shí)244毫秒
執(zhí)行結(jié)果，執(zhí)行計(jì)劃
當(dāng)然該種方式缺點(diǎn)也很明顯：只能支持連續(xù)分頁，不能支持獲取隨意頁的數(shù)據(jù)。文章來源：http://www.zghlxwxcb.cn/news/detail-825188.html
其他方案
- 限制查詢范圍： 在需求層面，可以限制只能查詢前100頁數(shù)據(jù)，或者規(guī)定只能獲取某個(gè)時(shí)間段內(nèi)的數(shù)據(jù)，從而避免深度分頁。
- 水平分表：考慮將數(shù)據(jù)按照某個(gè)維度進(jìn)行水平分表，以減小單表的數(shù)據(jù)量
- 使用ES，Hive，ClickHouse等OLAP方案
本文已收錄于我的個(gè)人博客：碼農(nóng)Academy的博客，專注分享Java技術(shù)干貨，包括Java基礎(chǔ)、Spring Boot、Spring Cloud、Mysql、Redis、Elasticsearch、中間件、架構(gòu)設(shè)計(jì)、面試題、程序員攻略等文章來源地址http://www.zghlxwxcb.cn/news/detail-825188.html
到了這里，關(guān)于面試官：Mysql千萬級(jí)大表如何進(jìn)行深度分頁優(yōu)化？的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
