本文介紹瞭如何通過子查詢優化深度分頁查詢,以減少回表操作帶來的性能損耗。傳統的深度分頁查詢往往會面臨嚴重的性能問題,尤其在處理大量數據時更是如此。 ...
背景
假如有一張千萬級的訂單表,這張表沒有採用分區分表,也沒有使用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))
問題復現
導出數據時我們需要按照時間倒序。所以我們先執行以下sql查詢前100條
select * FROM t_order ORDER BY create_time desc LIMIT 100;
共花費210ms。執行計劃如下:
然後我們繼續執行sql,我們從第100萬行開始取100條數據:
select * FROM t_order ORDER BY create_time desc LIMIT 1000000,100;
此時耗時3分2秒,耗時明顯增加。執行計劃如下:
由執行計劃看,此時索引已經失效了。。。。
我們繼續執行sql,從第1000萬行開始取100條數據:
select * FROM t_order ORDER BY create_time desc LIMIT 10000000,100;
此時耗時4分14秒,時間真是太長了,執行計劃如下:
後面還有接近1000萬條數據沒有取出來,直接就廢了。
原因分析
當我們使用 LIMIT offset, count
查詢語句進行深度分頁查詢時,例如 LIMIT 10000000,100
,會發生以下過程:
- MySQL首先會根據給定條件從相應的索引樹中查找m+n條記錄。對於聚集索引來說,它直接找到需要的結果即丟棄前
offset
條數據,返回count
條數據並返回;而對於二級索引,則可能涉及回表操作。 - 如果使用的是二級索引,在查到m+n條記錄後還需要通過這些記錄所關聯的主鍵
ID
去聚集索引里再次搜索出完整的行數據,然後再丟棄掉前offset
條數據,返回count
條數據。因此在這個過程中可能會產生大量的“回表”操作,這將導致性能下降。
我們藉助B+ Tree Visualization演示一下這張表的索引結構:
- 聚集索引(主鍵ID)
- 二級索引(idx_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毫秒。
執行結果,執行計劃
可以看出主查詢以及子查詢都使用到了索引,回表查詢的數據記錄數也大大減少。
繼續查詢到第1000萬行時耗時接近6秒。
執行結果,執行計劃
可以看出主查詢以及子查詢都使用到了索引,回表查詢的數據記錄數也大大減少。
這種方式需要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毫秒。
執行結果,執行計劃。
繼續查詢到第1000萬行時耗時接近2秒
執行結果,執行計劃
這種方式要保證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毫秒。
執行結果,執行計劃
繼續查詢到第1000萬行時耗時244毫秒
執行結果,執行計劃
當然該種方式缺點也很明顯:只能支持連續分頁,不能支持獲取隨意頁的數據。
其他方案
- 限制查詢範圍: 在需求層面,可以限制只能查詢前100頁數據,或者規定只能獲取某個時間段內的數據,從而避免深度分頁。
- 水平分表:考慮將數據按照某個維度進行水平分表,以減小單表的數據量
- 使用ES,Hive,ClickHouse等OLAP方案
本文已收錄於我的個人博客:碼農Academy的博客,專註分享Java技術乾貨,包括Java基礎、Spring Boot、Spring Cloud、Mysql、Redis、Elasticsearch、中間件、架構設計、面試題、程式員攻略等