文档章节

MySQL 分页优化三步走

IT--小哥
 IT--小哥
发布于 2017/07/19 17:45
字数 1805
阅读 13
收藏 0

前言:线上环境大概都遇到过MySQL分页查询的问题,在OFFSET比较小的时候,查询几乎没有瓶颈,但是在大数据量的情况下,性能就很差了,本文主要讨论如何优化分页查询和分页查询的一个问题

 

1、贡献一个建表语句和造数据的shell(适合小数据量插入)

root@localhost:mysql.sock  15:49:26 [tom]>create table test1( id int(10) NOT NULL AUTO_INCREMENT, name varchar(32), age int(10), creattetime datetime not null default CURRENT_TIMESTAMP, primary key(id) )ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.17 sec)

[root@redis01 ~]# cat insert.sh 
#!/bin/bash
i=1;
MAX_INSERT_ROW_COUNT=$1;
while [ $i -le $MAX_INSERT_ROW_COUNT ]
do
  mysql -uroot -p123456 tom -e "insert into test(name,age,createtime) values ('hello$i',$i % 99,NOW());"
  d=$(date +%M-%d\ %H\:%m\:%S)
  echo "INSERT HELLO $i @@ $d" 
  i=$(($i+1))
  sleep 0.05
done
 
exit 0

2、分页语句

select xx from table_name wheere xxx order by 字段A limit offset;

MySQL分页语句数据量小的时候用起来很方便,但是数据量大的时候就不适合直接使用了,此时,就需要进行一些优化:

 

方法一:使用合适的索引,可以走主键或者覆盖索引。程序做翻页的话只需要记录当前页最大ID和最小ID即可,利用偏移量进行翻页,比如from tablename id >20000 limit 0,20。例如

root@localhost:mysql.sock  16:24:16 [tom]>select * from test order by createtime limit 20000,2;
+-------+------------+------+---------------------+
| id    | name       | age  | createtime          |
+-------+------------+------+---------------------+
| 20001 | hello20001 |    3 | 2017-07-19 15:42:04 |
| 20002 | hello20002 |    4 | 2017-07-19 15:42:04 |
+-------+------------+------+---------------------+
2 rows in set (0.23 sec)

root@localhost:mysql.sock  16:24:52 [tom]>explain select * from test order by createtime limit 20000,2;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 47933 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

root@localhost:mysql.sock  16:24:59 [tom]>select * from test where id >20000 order by createtime limit 0,2;
+-------+------------+------+---------------------+
| id    | name       | age  | createtime          |
+-------+------------+------+---------------------+
| 20001 | hello20001 |    3 | 2017-07-19 15:42:04 |
| 20002 | hello20002 |    4 | 2017-07-19 15:42:04 |
+-------+------------+------+---------------------+
2 rows in set (0.08 sec)

root@localhost:mysql.sock  16:25:38 [tom]>explain select * from test where id >20000 order by createtime limit 0,2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | test  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 24243 |   100.00 | Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

方法二:使用join,虽然走了索引,但是扫描的数据量还是很多

root@localhost:mysql.sock  16:32:27 [tom]>select * from test order by createtime limit 20000,10;
+-------+------------+------+---------------------+
| id    | name       | age  | createtime          |
+-------+------------+------+---------------------+
| 20001 | hello20001 |    3 | 2017-07-19 15:42:04 |
| 20002 | hello20002 |    4 | 2017-07-19 15:42:04 |
| 20003 | hello20003 |    5 | 2017-07-19 15:42:04 |
| 20004 | hello20004 |    6 | 2017-07-19 15:42:05 |
| 20005 | hello20005 |    7 | 2017-07-19 15:42:05 |
| 20006 | hello20006 |    8 | 2017-07-19 15:42:05 |
| 20007 | hello20007 |    9 | 2017-07-19 15:42:05 |
| 20008 | hello20008 |   10 | 2017-07-19 15:42:05 |
| 20009 | hello20009 |   11 | 2017-07-19 15:42:05 |
| 20010 | hello20010 |   12 | 2017-07-19 15:42:05 |
+-------+------------+------+---------------------+
10 rows in set (0.23 sec)

root@localhost:mysql.sock  16:32:46 [tom]>select * from test a join (select id from test limit 20000,10) b on a.id=b.id;
+-------+------------+------+---------------------+-------+
| id    | name       | age  | createtime          | id    |
+-------+------------+------+---------------------+-------+
| 20001 | hello20001 |    3 | 2017-07-19 15:42:04 | 20001 |
| 20002 | hello20002 |    4 | 2017-07-19 15:42:04 | 20002 |
| 20003 | hello20003 |    5 | 2017-07-19 15:42:04 | 20003 |
| 20004 | hello20004 |    6 | 2017-07-19 15:42:05 | 20004 |
| 20005 | hello20005 |    7 | 2017-07-19 15:42:05 | 20005 |
| 20006 | hello20006 |    8 | 2017-07-19 15:42:05 | 20006 |
| 20007 | hello20007 |    9 | 2017-07-19 15:42:05 | 20007 |
| 20008 | hello20008 |   10 | 2017-07-19 15:42:05 | 20008 |
| 20009 | hello20009 |   11 | 2017-07-19 15:42:05 | 20009 |
| 20010 | hello20010 |   12 | 2017-07-19 15:42:05 | 20010 |
+-------+------------+------+---------------------+-------+
10 rows in set (0.02 sec)

root@localhost:mysql.sock  16:33:03 [tom]>show profiles;
+----------+------------+-------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                         |
+----------+------------+-------------------------------------------------------------------------------+
|        1 | 0.22927725 | select * from test order by createtime limit 20000,10                         |
|        2 | 0.01904900 | select * from test a join (select id from test limit 20000,10) b on a.id=b.id |
+----------+------------+-------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

root@localhost:mysql.sock  16:33:10 [tom]>show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000184 |
| checking permissions | 0.000020 |
| Opening tables       | 0.000040 |
| init                 | 0.000112 |
| System lock          | 0.000047 |
| optimizing           | 0.000012 |
| statistics           | 0.000048 |
| preparing            | 0.000028 |
| Sorting result       | 0.000014 |
| executing            | 0.000020 |
| Sending data         | 0.000021 |
| Creating sort index  | 0.228360 |
| end                  | 0.000041 |
| query end            | 0.000029 |
| closing tables       | 0.000027 |
| freeing items        | 0.000056 |
| cleaning up          | 0.000219 |
+----------------------+----------+
17 rows in set, 1 warning (0.00 sec)

root@localhost:mysql.sock  16:33:20 [tom]>show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000451 |
| checking permissions | 0.000023 |
| checking permissions | 0.000014 |
| Opening tables       | 0.000046 |
| init                 | 0.000123 |
| System lock          | 0.000028 |
| optimizing           | 0.000013 |
| optimizing           | 0.000009 |
| statistics           | 0.000034 |
| preparing            | 0.000041 |
| statistics           | 0.000074 |
| preparing            | 0.000024 |
| executing            | 0.000022 |
| Sending data         | 0.000024 |
| executing            | 0.000008 |
| Sending data         | 0.017794 |
| end                  | 0.000031 |
| query end            | 0.000024 |
| closing tables       | 0.000008 |
| removing tmp table   | 0.000015 |
| closing tables       | 0.000021 |
| freeing items        | 0.000056 |
| cleaning up          | 0.000172 |
+----------------------+----------+
23 rows in set, 1 warning (0.00 sec)

root@localhost:mysql.sock  16:33:23 [tom]>explain select * from test a join (select id from test limit 20000,10) b on a.id=b.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 20010 |   100.00 | NULL        |
|  1 | PRIMARY     | a          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | b.id |     1 |   100.00 | NULL        |
|  2 | DERIVED     | test       | NULL       | index  | NULL          | PRIMARY | 4       | NULL | 54429 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

root@localhost:mysql.sock  16:34:13 [tom]>explain select * from test order by createtime limit 20000,10;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 54541 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

方案三:用between减少扫描数据量(感觉这样优化效果最好了)

root@localhost:mysql.sock  17:09:18 [tom]>show profiles;
+----------+------------+-------------------------------------------------------------+
| Query_ID | Duration   | Query                                                       |
+----------+------------+-------------------------------------------------------------+
|        6 | 0.07874750 | select * from test where id limit 50000,100                 |
|        7 | 0.00184650 | select * from test where id between 50000 and 50100         |
+----------+------------+-------------------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)

root@localhost:mysql.sock  17:09:20 [tom]>show profile for query 6;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000312 |
| checking permissions | 0.000026 |
| Opening tables       | 0.000049 |
| init                 | 0.000057 |
| System lock          | 0.000031 |
| optimizing           | 0.000018 |
| statistics           | 0.000043 |
| preparing            | 0.000038 |
| executing            | 0.000011 |
| Sending data         | 0.077964 |
| end                  | 0.000035 |
| query end            | 0.000028 |
| closing tables       | 0.000025 |
| freeing items        | 0.000058 |
| cleaning up          | 0.000053 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)

root@localhost:mysql.sock  17:09:31 [tom]>show profile for query 7;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000174 |
| checking permissions | 0.000029 |
| Opening tables       | 0.000050 |
| init                 | 0.000079 |
| System lock          | 0.000042 |
| optimizing           | 0.000035 |
| statistics           | 0.000492 |
| preparing            | 0.000054 |
| executing            | 0.000011 |
| Sending data         | 0.000593 |
| end                  | 0.000024 |
| query end            | 0.000030 |
| closing tables       | 0.000023 |
| freeing items        | 0.000166 |
| cleaning up          | 0.000046 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

分页查询的一个问题

root@localhost:mysql.sock  17:24:43 [tom]>select @@version;
+------------+
| @@version  |
+------------+
| 5.7.14-log |
+------------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  17:24:59 [tom]>show variables like "sql_mode";
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  17:27:10 [tom]>show create table glon;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| glon  | CREATE TABLE `glon` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `create_time` datetime NOT NULL,
  `age` tinyint(3) unsigned DEFAULT '18',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)

root@localhost:mysql.sock  17:27:36 [tom]>select * from glon;
+----+--------------+---------------------+------+
| id | name         | create_time         | age  |
+----+--------------+---------------------+------+
|  1 | Eason Chan   | 2017-05-02 08:10:10 |   19 |
|  2 | Glon Ho      | 2017-05-03 12:10:10 |   18 |
|  3 | 赵敏         | 2017-05-03 14:10:10 |   17 |
|  4 | Jacky Cheung | 2017-05-02 14:00:00 |   22 |
|  5 | 周芷若       | 2017-05-02 14:00:00 |   16 |
|  6 | Andy Lau     | 2017-05-02 14:00:00 |   50 |
|  7 | 至尊宝       | 2017-05-02 14:00:00 |   20 |
|  8 | 刘三姐       | 2017-05-02 14:00:00 |   19 |
+----+--------------+---------------------+------+
8 rows in set (0.00 sec)

root@localhost:mysql.sock  17:27:59 [tom]>select * from glon ORDER BY create_time limit 0, 4;
+----+--------------+---------------------+------+
| id | name         | create_time         | age  |
+----+--------------+---------------------+------+
|  1 | Eason Chan   | 2017-05-02 08:10:10 |   19 |
|  8 | 刘三姐       | 2017-05-02 14:00:00 |   19 |
|  6 | Andy Lau     | 2017-05-02 14:00:00 |   50 |
|  4 | Jacky Cheung | 2017-05-02 14:00:00 |   22 |
+----+--------------+---------------------+------+
4 rows in set (0.00 sec)

root@localhost:mysql.sock  17:28:26 [tom]>select * from glon ORDER BY create_time limit 4, 4;
+----+-----------+---------------------+------+
| id | name      | create_time         | age  |
+----+-----------+---------------------+------+
|  7 | 至尊宝    | 2017-05-02 14:00:00 |   20 |
|  8 | 刘三姐    | 2017-05-02 14:00:00 |   19 |
|  2 | Glon Ho   | 2017-05-03 12:10:10 |   18 |
|  3 | 赵敏      | 2017-05-03 14:10:10 |   17 |
+----+-----------+---------------------+------+
4 rows in set (0.01 sec)

细心的同学可能已经发现,上面的数据有一条重复的,而且少了一条数据。
这是因为如果排序字段有相同值得情况下,可能导致每次排序产生的结果不一样。也有可能会产生重复数据,最好的办法就是提高排序字段的唯一性,或者引入其他字段增加排序字段的唯一性。具体可以参考:http://mysql.taobao.org/monthly/2015/06/04/

 

为了方便大家交流,本人开通了微信公众号,和QQ群291519319。喜欢技术的一起来交流吧

© 著作权归作者所有

IT--小哥
粉丝 47
博文 147
码字总数 150342
作品 0
东城
数据库管理员
私信 提问
mysql5.6 分页查询优化

mysql5.6 分页查询优化 场景: 表结构:主键(非自增)contentCode(varchar),过滤条件列为updateTime(timeStamp),已经为timestamp建立索引。 搜索sql为: 问题:数据在分页到60w后,分...

爱吃窝窝头
04/27
22
0
数据库分页查询和跨页选中行问题处理

今天看到一篇博客,突然想到以前遇到的一个类似的问题,关于分页查询和跨页保持选中行的问题,下面给出答案,一起探讨下吧。 一、什么是数据库分页查询? 即在服务端分页,跳到第n页才查询、...

海岸线的曙光
2018/07/03
657
0
mac系统 设置mysql开机自动启动

mac系统 设置mysql开机自动启动只需要三步:(先admin 登录,查看brew --prefix mysql,该目录是/usr/local/homebrew/opt/mysql,查看其下文件是否有有mysql.plist文件,这里我查看一下,有h...

薇薇乐
2018/06/26
0
0
MySQL limit 优化,百万至千万级快速分页:复合索引

MySQL 性能到底能有多高?用了php半年多,真正如此深入的去思考这个问题还是从前天开始。有过痛苦有过绝望,到现在充满信心!MySql 这个数据库绝对是适合dba级的高手去玩的,一般做一点1万篇...

大数据之路
2012/12/19
1K
4
mysql 优化实例(百万级数据)

MySql 这个数据库绝对是适合dba级的高手去玩的,一般做一点1万篇新闻的小型系统怎么写都可以,用xx框架可以实现快速开发。可是数据量到了10万,百万至千万,他的性能还能那么高吗?一点小小的...

tantexian
2016/04/22
776
0

没有更多内容

加载失败,请刷新页面

加载更多

SpringBoot中 集成 redisTemplate 对 Redis 的操作(二)

SpringBoot中 集成 redisTemplate 对 Redis 的操作(二) List 类型的操作 1、 向列表左侧添加数据 Long leftPush = redisTemplate.opsForList().leftPush("name", name); 2、 向列表右......

TcWong
今天
4
0
排序––快速排序(二)

根据排序––快速排序(一)的描述,现准备写一个快速排序的主体框架: 1、首先需要设置一个枢轴元素即setPivot(int i); 2、然后需要与枢轴元素进行比较即int comparePivot(int j); 3、最后...

FAT_mt
昨天
4
0
mysql概览

学习知识,首先要有一个总体的认识。以下为mysql概览 1-架构图 2-Detail csdn |简书 | 头条 | SegmentFault 思否 | 掘金 | 开源中国 |

程序员深夜写bug
昨天
10
0
golang微服务框架go-micro 入门笔记2.2 micro工具之微应用利器micro web

micro web micro 功能非常强大,本文将详细阐述micro web 命令行的功能 阅读本文前你可能需要进行如下知识储备 golang分布式微服务框架go-micro 入门笔记1:搭建go-micro环境, golang微服务框架...

非正式解决方案
昨天
8
0
前端——使用base64编码在页面嵌入图片

因为页面中插入一个图片都要写明图片的路径——相对路径或者绝对路径。而除了具体的网站图片的图片地址,如果是在自己电脑文件夹里的图片,当我们的HTML文件在别人电脑上打开的时候图片则由于...

被毒打的程序猿
昨天
9
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部