# 关联分页查询优化
这里以图书馆图书借阅收藏信息获取的两个例子来演示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
<iframe src="https://ghbtns.com/github-btn.html?user=GrassInWind2019&repo=bookms&type=watch&count=true&size=large" allowtransparency="true" frameborder="0" scrolling="0" width="156px" height="30px"></iframe>
<iframe src="https://ghbtns.com/github-btn.html?user=GrassInWind2019&repo=bookms&type=fork&count=true&size=large" allowtransparency="true" frameborder="0" scrolling="0" width="156px" height="30px"></iframe>
有疑问加站长微信联系（非本文作者））
