准备数据
MySQL的limit子句的语法,我们是非常熟悉的,查询的起始位置不同,消耗的时间也是不一样的。
我们创建一个数据表t_test:
字段名 | 类型 | 长度 | 小数点 | 不是 null | 虚拟 | 键 |
---|---|---|---|---|---|---|
id | int | 0 | 0 | √ | √ | |
val | varchar | 255 | 0 | √ |
我们接下来往数据表里添加1000万条数据,怎么导入呢?先把数据写到文件里面,再导入到表里,先来看下生成数据的java代码吧
public class Demo2{
public static void main(String [] args){
try{
FileWriter w=new FileWriter("E:/1.txt");
BufferedWriter bw=new BufferedWriter(w);
for(int i=1; i<=10000000;i++){
String uuid=UUID.randomUUID().toString();
bw.write(i+","+uuid+"\n");
}
bw.close();
w.close();
System.out.println("执行完毕");
}catch(Exception e){
e.printStackTrace();
}
}
}
接下来通过navicat在数据表t_test上右键->导入数据,
可以看到,默认选择的就是txt类型的文件,直接点下一步选文件,直接下一步下一步的,大概需要10分到20分钟的导入时间。
为什么使用文件导入,不适用insert执行呢?因为insert是带着事务的,1000万条数据是很慢的,但是使用文档导入是没有带事务的,速度是很快的。
优化前
我们接下来,看看limit子句的消耗时间
select id,name from t_test limit 100,100;
select id,name from t_test limit 10000,100;
select id,name from t_test limit 100000,100;
select id,name from t_test limit 1000000,100;
等待的时间是查询越靠后返回的数据越慢,返回的时间可能不是几秒,而是几十秒几百秒了。
优化办法1:主键索引
select * from t_test where id>=5000000 limit 100;
select * from t_test where id>=5000000 and id<5000000+100;
主键索引耗时是0.01s,这个优化是几百倍的提升
优化办法2:用索引表加速
物理删除会造成主键的不连续,这样办法1利用主键去进行分页查询的方法行不通了。如果逻辑删除,等到数据从数据表中删除转到历史表,那主键还是不连续。那还有什么办法来加速分页查询吗?当然有,一个折中的办法——从主键的索引进行加速,从索引表里提取主键值,然后表做连接,从业务表里再取数据。怎么做呢?
首先我们要明白一个事情,我们给某个字段创建索引,那么数据库就会给某个字段创建一个索引表,这个索引表是二叉树的,记录的信息就是字段的值和主键的值,当我们使用索引字段作为查询条件,Mysql就会在二叉树里快速的定位到数据,然后取到主键的信息,再去关系表里,根据主键值来查找数据,所以就是这么来完成加速的
然后,我们看下如何对主键不连续的值,建立索引,看下面sql
select t.id,t.name from t_test t
join (select id from t_test limit 5000000 ,100) tmp on t.id=tmp.id
看join后的子查询,注意查询的只是id主键的值,我们知道索引里面是包含主键的值,这样查询就不用再走表了,直接查索引就行了,所以查询的时候会用索引表加速
这样速度比起没有优化时候要强点,但是也不是很强,如果优化前是3.9s多,那优化后是2.7s,比起通过where id>=5000000 limit 100 这种方式提升的还是差的很多 ,不过也是提升了1.2s,这已经是主键不连续的情况最好的sql语句了。
其它解决办法
结合业务上的一些设置,禁止查询早期数据。
比如支付宝上面查询不到几年前的一笔大额转账,数据是不可能丢失的,只不过支付宝不让你查询很早以前的数据。
再比如在百度上搜索信息,只能搜索到76页,更早的数据百度是不让你去看的,如果不信,可以在百度上搜索任意一个热门信息,最多只能看到76页。
总结
如何快速分页查询数据?
- 使用上面优化办法2的sql语句,对主键不连续的数据进行查询加速
- 在业务上,限制用户查询很靠后的数据