Mysql如何优化延迟索引和分页

拧巴人

什么是延迟索引？使用索引查询出来数据，之后把查询结果和同一张表中数据进行连接查询，进而提高查询速度!

分页是一个很常见功能，select ** from tableName limit ($page - 1 ) * $n ,$n

通过一个存储过程插入10000条数据进行测试：

1. create table smth1 (
   
2.     id int auto_increment ,
   
3.     ver int(11) default null,
   
4.     content varchar(1000) not null,
   
5.     intro varchar(1000) not null,
   
6.     primary key(id),
   
7.     key idver(id,ver)
   
8.      
   
9. )engine = innodb default charset = utf8;
   
10. 
    
11. 
    
12. 
    
13. 
    
14. 
    
15. 
    
16. create procedure smthTest1()
    
17. begin
    
18.     declare num int default 100001;
    
19.     while num < 1000000 do
    
20.         set num := num +1;
    
21.         insert into smth1 values (num ,num,'我是*****','我是谁');
    
22.     end while ;
    
23. 
    
24. end;

复制代码

查询：

1. mysql> show profiles;
   
2. +----------+------------+----------------------------------------------+
   
3. | Query_ID | Duration   | Query                                        |
   
4. +----------+------------+----------------------------------------------+
   
5. |        1 |   0.002006 | select id ,content from smth1 limit 1000,10  |
   
6. |        2 |   0.030106 | select id ,content from smth1 limit 5000,10  |
   
7. |        3 |   0.042428 | select id ,content from smth1 limit 9000,10  |
   
8. |        4 | 0.01297225 | select id ,content from smth1 limit 10000,10 |
   
9. |        5 | 0.13077625 | select id ,content from smth1 limit 20000,10 |
   

复制代码

可见随着查询$page 变大，时间会越来越大！


怎样避免这种情况？

一般我们数据库里面数据都不会直接删除，数据时很宝贵的，不舍得删除，另一方便能提高查询数据

先利用索引查询出来数据，再进行联合查询不就行了

1. select C.id,C.content from smth1 C inner join
   
2. (
   
3.     select id from smth1 where id > 1000 limit 10
   
4. ) as t on C.id = t.id ;
   
5. 
   
6. select C.id,C.content from smth1 C inner join
   
7. (
   
8.     select id from smth1 where id > 5000 limit 10
   
9. ) as t on C.id = t.id ;
   
10. 
    
11. select C.id,C.content from smth1 C inner join
    
12. (
    
13.     select id from smth1 where id > 9000 limit 10
    
14. ) as t on C.id = t.id ;
    
15. 
    
16. select C.id,C.content from smth1 C inner join
    
17. (
    
18.     select id from smth1 where id > 10000 limit 10
    
19. ) as t on C.id = t.id ;
    
20. 
    
21. select C.id,C.content from smth1 C inner join
    
22. (
    
23.     select id from smth1 where id > 20000 limit 10
    
24. ) as t on C.id = t.id ;
    
25. 
    
26. 进行执行计划分析，没有一个大于1s的
    
27. 
    
28. 11 | 0.04538625 | select C.id,C.content from smth1 C inner join
    
29. (
    
30.     select id from smth1 where id > 5000 limit 10
    
31. ) as t on C.id = t.id  |
    
32. |       12 |   0.023278 | select C.id,C.content from smth1 C inner join
    
33. (
    
34.     select id from smth1 where id > 9000 limit 10
    
35. ) as t on C.id = t.id  |
    
36. |       13 | 0.02320425 | select C.id,C.content from smth1 C inner join
    
37. (
    
38.     select id from smth1 where id > 10000 limit 10
    
39. ) as t on C.id = t.id |
    
40. |       14 |   0.001938 | select C.id,C.content from smth1 C inner join
    
41. (
    
42.     select id from smth1 where id > 20000 limit 10
    
43. ) as t on C.id = t.id |
    

复制代码

此外，还会想到用in来查询而不是子查询，为什么不用in，使用in会先查询出来一条id，之后再去和下面进行匹配，会进行smth1进行全表扫描！