优享资讯 | 索引深分页问题

掘金后端 ( ) • 2024-06-21 17:28

首先建立对应的表,插入对应数据，这里插入1000w行

import mysql.connector
import random
import string
from datetime import datetime

# 连接到MySQL数据库
conn = mysql.connector.connect(
    host='gz-968',
    user='rot',
    password='Zq',
    database='zg',
    port=""
)
cursor = conn.cursor()

# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')

# 生成随机用户名和邮箱
def generate_random_user():
    username = ''.join(random.choices(string.ascii_lowercase, k=8))
    email = f"{username}@example.com"
    return username, email


num_rows = 10000000
batch_size = 10000  # 每次插入的行数

for i in range(0, num_rows, batch_size):
    users = [(generate_random_user()[0], generate_random_user()[1], datetime.now()) for _ in range(batch_size)]
    cursor.executemany('INSERT INTO users (username, email, created_at) VALUES (%s, %s, %s)', users)
    conn.commit()
    print(f"Inserted {i + batch_size} rows")

# 关闭连接
cursor.close()
conn.close()

执行查询语句：

explain select * from users where email = '1'

由于我们没在emeil字段建立索引，所以简单查询，到那时type用的确实全盘扫描，也没有用到主键索引，我们在分析一下sql所用的时间

explain analyze select * from users where email = '1'

可以查看，主要有两个步骤，分别是扫表，和过来，扫表时间花费大概3000号毫秒，显然在1000w数据是不能接受的假如，我们在email加上索引呢，在看看

可以看到，加上索引，再次查询便可以用到了

这个时候再看的开始时间和结束时间，间隙可以忽略不计了。

alter table users
    add sex varchar(10) null after id;

create index users_sex_index
    on users (sex);

select * from users  where sex = 'man' limit 100 offset 4900000;

但是当一天执行分页的时候，也发现很慢，明明在sex上加了索引的

extra字段现在为空

SELECT u.*
FROM users u
JOIN (
    SELECT id
    FROM users
    WHERE sex = 'man'
    ORDER BY id
    LIMIT 100 OFFSET 4900000
) sub ON u.id = sub.id;

这个sql变的非常快，也达到了相同的效果

其实id=2便是里面的子查询，extra显示using index，使用了覆盖索引所以不需要再次回表，这样子避免了多次扫描表，所以减少了时间。这个也是解决LIMIT 100 OFFSET 4900000这个深分页的一些常用方法还有一些其他方法解决：

1. 使用覆盖索引

如果查询只需要特定的列，可以使用覆盖索引来避免回表。

sql复制
CREATE INDEX idx_sex_id ON users (sex, id);

SELECT id 
FROM users 
WHERE sex = 'man' 
ORDER BY id 
LIMIT 100 OFFSET 4900000;

然后根据获取的 id 列进行查询：

sql复制
SELECT * 
FROM users 
WHERE id IN (
    SELECT id 
    FROM users 
    WHERE sex = 'man' 
    ORDER BY id 
    LIMIT 100 OFFSET 4900000
);

2. 使用延迟关联（Deferred Join）

延迟关联是一种优化技术，先获取主键列表，然后再根据主键列表获取完整的行数据。

sql复制
-- 获取主键列表
SELECT id 
FROM users 
WHERE sex = 'man' 
ORDER BY id 
LIMIT 100 OFFSET 4900000;

-- 根据主键列表获取完整的行数据
SELECT * 
FROM users 
WHERE id IN (
    SELECT id 
    FROM users 
    WHERE sex = 'man' 
    ORDER BY id 
    LIMIT 100 OFFSET 4900000
);

3. 使用游标（Cursor）

在某些情况下，可以使用游标来处理深分页。游标允许你逐行处理查询结果，避免一次性加载大量数据。

sql复制
-- 创建游标
DECLARE cur CURSOR FOR 
SELECT id 
FROM users 
WHERE sex = 'man' 
ORDER BY id;

-- 打开游标
OPEN cur;

-- 获取游标数据
FETCH cur INTO @id;

-- 关闭游标
CLOSE cur;

4. 使用更高效的分页算法

如果你有一个唯一且递增的列（如 id），可以使用更高效的分页算法。假设你有一个 id 列，可以使用以下方法：

sql复制
-- 获取最后一页的最后一个ID
SELECT id 
FROM users 
WHERE sex = 'man' 
ORDER BY id 
LIMIT 1 OFFSET 4900000;

-- 使用上一个查询的结果作为起点
SELECT * 
FROM users 
WHERE sex = 'man' 
AND id > @last_id 
ORDER BY id 
LIMIT 100;