关联分页查询优化

这里以图书馆图书借阅收藏信息获取的两个例子来演示sql优化的方法。
先来看一个简单的例子。

两个表关联

###优化前
sql如下

explain select b.* from bookms_book b left join bookms_user_favorite f on f.identify=b.identify where user_id=1 limit 100 offset 20000;
+----+-------------+-------+------------+--------+---------------+----------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys | key      | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+-------+------------+--------+---------------+----------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | f     | NULL       | ALL    | NULL          | NULL     | NULL    | NULL | 202810 |    10.00 | Using where           |
|  1 | SIMPLE      | b     | NULL       | eq_ref | identify      | identify | 402     | func |      1 |   100.00 | Using index condition |
+----+-------------+-------+------------+--------+---------------+----------+---------+------+--------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)

通过apache bench压测，其QPS约为17.

Concurrency Level:      10
Time taken for tests:   58.626 seconds
Complete requests:      1000
Failed requests:        0
Total transferred:      28112000 bytes
HTML transferred:       27886000 bytes
Requests per second:    17.06 [#/sec] (mean)
Time per request:       586.264 [ms] (mean)
Time per request:       58.626 [ms] (mean, across all concurrent requests)

优化1

优化分页查询的一个最简单的方法就是使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。

mysql> explain select * from bookms_book inner join(select identify from bookms_user_favorite where user_id=1 limit 100 offset 20000) as book_fav using(identify);
+----+-------------+----------------------+------------+--------+---------------+----------+---------+-------+--------+----------+-----------------------+
| id | select_type | table                | partitions | type   | possible_keys | key      | key_len | ref   | rows   | filtered | Extra                 |
+----+-------------+----------------------+------------+--------+---------------+----------+---------+-------+--------+----------+-----------------------+
|  1 | PRIMARY     | <derived2>           | NULL       | ALL    | NULL          | NULL     | NULL    | NULL  |  20100 |   100.00 | NULL                  |
|  1 | PRIMARY     | bookms_book          | NULL       | eq_ref | identify      | identify | 402     | func  |      1 |   100.00 | Using index condition |
|  2 | DERIVED     | bookms_user_favorite | NULL       | ref    | user_id       | user_id  | 4       | const | 101405 |   100.00 | NULL                  |
+----+-------------+----------------------+------------+--------+---------------+----------+---------+-------+--------+----------+-----------------------+
3 rows in set, 1 warning (0.00 sec)

通过apache bench压测QPS约为344，提升了十几倍。

Concurrency Level:      10
Time taken for tests:   2.902 seconds
Complete requests:      1000
Failed requests:        0
Total transferred:      28178000 bytes
HTML transferred:       27980000 bytes
Requests per second:    344.54 [#/sec] (mean)
Time per request:       29.024 [ms] (mean)
Time per request:       2.902 [ms] (mean, across all concurrent requests)

优化2

将join查询拆分为两条简单查询，也能显著提升查询效率。

select identify from bookms_user_favorite where user_id=1 limit 100 offset 20000
select * from bookms_book where identify in (上一条sql查询结果)

通过apache bench压测QPS约为303.

Concurrency Level:      10
Time taken for tests:   3.298 seconds
Complete requests:      1000
Failed requests:        0
Total transferred:      28178000 bytes
HTML transferred:       27980000 bytes
Requests per second:    303.26 [#/sec] (mean)
Time per request:       32.975 [ms] (mean)
Time per request:       3.298 [ms] (mean, across all concurrent requests)

再看一个复杂一点多表关联的例子

多个表关联

优化前

mysql> explain select f.id,f.user_id,f.identify,b.book_name,b.cover,b.author,bc.category_id,c.category_name,
(case when r.lend_status=0 then '可借' 
when r.lend_status=5 then '已下架' 
when r.lend_status=1 and r.user_id=1 then '正在借阅' 
when r.lend_status=1 and r.user_id<>1 then '不可借' 
end) as lend_status from bookms_book_record r 
left join bookms_book b using(identify) 
left join bookms_book_category bc using(identify) 
inner join bookms_user_favorite f using(identify) 
left join bookms_category c on bc.category_id=c.id where f.user_id=1 limit 100 offset 20000;
+----+-------------+-------+------------+--------+-----------------------------------+-----------------------------------+---------+-----------------------+--------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys                     | key                               | key_len | ref                   | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+-----------------------------------+-----------------------------------+---------+-----------------------+--------+----------+-------------+
|  1 | SIMPLE      | f     | NULL       | ALL    | idx_bookms_user_favorite_identify | NULL                              | NULL    | NULL                  | 202810 |    10.00 | Using where |
|  1 | SIMPLE      | r     | NULL       | ref    | idx_bookms_book_record_identify   | idx_bookms_book_record_identify   | 302     | bookms.f.identify     |      1 |   100.00 | NULL        |
|  1 | SIMPLE      | b     | NULL       | eq_ref | identify                          | identify                          | 402     | func                  |      1 |   100.00 | Using where |
|  1 | SIMPLE      | bc    | NULL       | ref    | idx_bookms_book_category_identify | idx_bookms_book_category_identify | 302     | bookms.f.identify     |      1 |   100.00 | NULL        |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY                           | PRIMARY                           | 4       | bookms.bc.category_id |      1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+-----------------------------------+-----------------------------------+---------+-----------------------+--------+----------+-------------+
5 rows in set, 1 warning (0.00 sec)

通过apache bench压测，QPS约为1.

Concurrency Level:      10
Time taken for tests:   91.497 seconds
Complete requests:      100
Failed requests:        0
Total transferred:      2207400 bytes
HTML transferred:       2187600 bytes
Requests per second:    1.09 [#/sec] (mean)
Time per request:       9149.661 [ms] (mean)
Time per request:       914.966 [ms] (mean, across all concurrent requests)

优化1

先通过子查询(select fav.id,fav.user_id,fav.identify from bookms_user_favorite fav where fav.user_id=1 limit 100 offset 20000)查出收藏的图书的标识信息再关联查询。

mysql> explain select f.id,f.user_id,f.identify,b.book_name,b.cover,b.author,bc.category_id,c.category_name,
(case when r.lend_status=0 then '可借' 
when r.lend_status=5 then '已下架' 
when r.lend_status=1 and r.user_id=1 then '正在借阅' 
when r.lend_status=1 and r.user_id<>1 then '不可借' 
end) as lend_status from bookms_book_record r 
left join bookms_book b using(identify) 
left join bookms_book_category bc using(identify) 
inner join (select fav.id,fav.user_id,fav.identify from bookms_user_favorite fav where fav.user_id=1 limit 100 offset 20000) f using(user_id) 
left join bookms_category c on bc.category_id=c.id where f.user_id=1 limit 100 offset 20000;
+----+-------------+------------+------------+--------+-----------------------------------+-----------------------------------+---------+-----------------------+---------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type   | possible_keys                     | key                               | key_len | ref                   | rows    | filtered | Extra                                              |
+----+-------------+------------+------------+--------+-----------------------------------+-----------------------------------+---------+-----------------------+---------+----------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref    | <auto_key0>                       | <auto_key0>                       | 4       | const                 |      10 |   100.00 | NULL                                               |
|  1 | PRIMARY     | r          | NULL       | ALL    | NULL                              | NULL                              | NULL    | NULL                  | 1004634 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | PRIMARY     | b          | NULL       | eq_ref | identify                          | identify                          | 402     | func                  |       1 |   100.00 | Using where                                        |
|  1 | PRIMARY     | bc         | NULL       | ref    | idx_bookms_book_category_identify | idx_bookms_book_category_identify | 302     | bookms.r.identify     |       1 |   100.00 | NULL                                               |
|  1 | PRIMARY     | c          | NULL       | eq_ref | PRIMARY                           | PRIMARY                           | 4       | bookms.bc.category_id |       1 |   100.00 | NULL                                               |
|  2 | DERIVED     | fav        | NULL       | ALL    | NULL                              | NULL                              | NULL    | NULL                  |  202810 |    10.00 | Using where                                        |
+----+-------------+------------+------------+--------+-----------------------------------+-----------------------------------+---------+-----------------------+---------+----------+----------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

通过apache bench压测，QPS约为41.

Concurrency Level:      10
Time taken for tests:   24.206 seconds
Complete requests:      1000
Failed requests:        0
Total transferred:      21931000 bytes
HTML transferred:       21733000 bytes
Requests per second:    41.31 [#/sec] (mean)
Time per request:       242.064 [ms] (mean)
Time per request:       24.206 [ms] (mean, across all concurrent requests)

优化2

将多表关联拆分为多个简单查询，查询效率提升非常大。

select id,user_id,identify from bookms_book_record where user_id=1 limit 100 offset 20000
select (case 
when r.lend_status=0 then '可借' 
when r.lend_status=5 then '已下架' 
when r.lend_status=1 and r.user_id=1 then '正在借阅' 
when r.lend_status=1 and r.user_id<>1 then '不可借' 
end) as lend_status,identify from bookms_book_record r where r.identify in(...)

select book_name,cover,author,identify from bookms_book where identify in(...)

select category_id,identify from bookms_book_category where identify in(...)

select category_name from bookms_category where id in(...)

通过apache bench压测，QPS约为178.
可见关联的表越多数据量越大，查询的效率越低。

演练相关材料

演练所用code及数据的伪造详见https://github.com/GrassInWind2019/bookms