Mysql分页查询优化

目录

1.插入大量数据（批处理）

(1). 插入相同的数据：

(2).插入不同的数据：

2. 分页查询大数据量效率低

3.分页查询优化：

---

---

1.插入大量数据（批处理）

(1). 插入相同的数据：

创建存储过程，向表中插入大量数据

drop procedure if EXISTS `my_procedure`; 
delimiter //
create procedure my_procedure()
BEGIN
  DECLARE n int DEFAULT 1;
  #关闭自动提交，否则insert一次，则提交一次事务，严重降低性能，我们关系自动提交，使用批处理，整个过程只提交一次事务。
  SET autocommit = 0;
  WHILE n < 10001 DO
    insert INTO weather (city,shidu,pm25,pm10,quality,high_temp,low_temp) VALUE ('Qingdao','20%',50,60,'good',29,21); 
    set n = n + 1;
  END WHILE;
  COMMIT; #提交事务，一次性提交
END
delimiter ;  #恢复以分号做为一行的结束

执行的时候报错（单独执行第一句，就报出如下错误），怀疑是因为当前用户没有足够的权限。

授予用户权限：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY '123456' WITH GRANT OPTION;  

但是加了权限之后仍然不行， 于是参考了这篇文章:MYSQL中创建存储过程实现向表中循环插入数据_sunstubble的博客-CSDN博客_mysql 存储过程 插入。

发现是因为存储过程开始的时候，"delimiter //"，意思是mysql语句的结尾换成以  // 结束,mysql默认是;结束。这样的话，存储过程结束的时候，要加'//', 修改成下面的代码：

DELIMITER // /*(意思是mysql语句的结尾换成以 //  结束,mysql默认是;结束)*/

create procedure my_procedure()
BEGIN
  DECLARE n int DEFAULT 1;
  #关闭自动提交，否则insert一次，则提交一次事务，严重降低性能，我们关系自动提交，使用批处理，整个过程只提交一次事务。
  SET autocommit = 0;
  WHILE n < 10001 DO
    insert INTO weather (city,shidu,pm25,pm10,quality,high_temp,low_temp) VALUE ('Qingdao','20%',50,60,'good',29,21); 
    set n = n + 1;
  END WHILE;
  COMMIT; #提交事务，一次性提交
END 
//      /*存储过程结束, 要以//做为结束标记*/

DELIMITER ; #恢复以分号做为一行的结束

创建存储过程成功之后，执行存储过程， 数据插入成功。

(2).插入不同的数据：

上述创建的存储过程，插入的数据都是相同的，如果想插入不同的数据呢？

需要创建函数，函数返回随机值，做为insert的value值。

创建两个function ： rand_string(n INT)产生随机字符串， rand_num()创建随机数字。

 然后创建存储过程，插入数据，其中要用到function的返回值，做为value值。

 然后执行存储过程, 插入10条部门数据（根据自己需要进行设置，比如50000），部门编号从100开始。

DELIMITER ;

CALL insert_dept(100,10); 

 插入的数据如下：

2. 分页查询大数据量效率低

mysql大数据量使用limit分页，随着页码的增大，查询效率越低下，因为分页查询是全表查询，然后从查询出的记录中再获取分页数据。

SELECT * FROM weather LIMIT start, pageSize;
(1).起始页较小时，查询是没有性能问题的，分别从10， 100， 1000， 10000开始分页的执行时间（每页取10条）如下：
select * from weather limit 10, 10   0.016秒
select * from weather limit 100, 10   0.016秒
select * from weather limit 1000, 10   0.047秒
select * from weather limit 10000, 10   0.094秒
可以看出，随着起始记录的增加，时间也随着增大， 这说明分页语句limit跟起始页码是有很大关系的，

(2).上面的查询效率还可以接受，但是我们把起始记录改为40w看下。

select * from product limit 400000, 10   3.229秒
(3).如果起始记录再大呢？显然这种时间是无法忍受的。
select * from product limit 900000, 10   37.44秒
从中我们总结出两件事情：

1. limit语句的查询时间与起始记录的位置成正比
2. mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。

3.分页查询优化：

好视频：【IT老齐074】从76237到753毫秒,海量数据大页码MySQL查询该如何优化？_哔哩哔哩_bilibili

既然分页查询有这样的问题，那么能够对其进行优化呢？当然可以，利用表的覆盖索引来加速分页查询。
覆盖索引所要查询(select)的字段和where条件都只用建立了索引的字段，只在索引中查询就能完成查询工作，不用回表查询，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。
在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何：
select id from weather limit 900000, 10 0.2秒
只查询建立了索引的列id（覆盖索引），这相对于查询了所有列的37.44秒，提升了大概100多倍的速度。
那么如果我们也要查询所有列，有三种方法（其实都是利用了子查询）：

(1).一种是id>=的形式（必须是id连续的情况???）。
SELECT * FROM weather WHERE id >=(select id from weather limit 900000, 1) limit 10
查询时间为0.2秒，质的飞跃。子查询用了主键索引，父查询也用了主键索引。
(2).另一种就是利用join（连接查询）（必须是id连续的情况???）。
SELECT * FROM weather a JOIN (select id from weather limit 900000, 10) b ON a.id = b.id

子查询用了主键索引，父查询也用了主键索引。

*通配符在实际的查询中最好不使用，应该指明相应的属性，效率更高。

(3).复合索引。

上面的例子如果加上where语句，会怎么样呢？

select id from weather where vtype=1 order by id limit 900000,10; 看看结果，时间是10秒！

因为vtype字段不在索引中，所以查询效率又降低了。

建立一个search(vtype,id) 这样的索引，再执行查询语句（这里不光是能够通过索引查询，另外还覆盖索引）：

select id from weather where vtype=1 order by id limit 90000,10; 非常快！0.04秒完成！

总结：如果对于有where 条件，又想走索引的，必须设计一个索引，将where 语句中用到的字段放第一位，limit用到的主键放第2位。

其实三者用的都是一个原理，所以效果也差不多