mysql 分页查询分析_mysql 大表分页查询测试分析优化

http://blog.csdn.net/xluren/article/details/32746183

http://www.cnblogs.com/kupig/archive/2011/10/19/2217228.html

http://blog.csdn.net/longyulu/article/details/8850672

首先创建表结构

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(255) default NULL,

`age` int(11) default NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

出入800万数据；

测试开始：

1.正常查询

SELECT * FROM test.`user` LIMIT 7921323,10

耗时：3.513s

2.利用id排序

SELECT * FROM test.`user` ORDER BY id LIMIT 7721323, 10;

耗时3.408s

3.利用IN

SELECT * FROM test.`user` WHERE id IN (7921323,7921300,7921386)

耗时：0.097s，这个主要是利用了索引所以速度快

来看一下没有索引的情况

SELECT * FROM test.`user` WHERE `name` IN ('jack7999008','jack7699008','jack7599008')

耗时：3.683s

针对IN这种情况需要找出ID，对于几百万的表，想要查询出id也需要耗时，对于条件分页查询，我们可以针对条件对应的字段建立索引，找出id，然后再IN SET；

4.网上说的利用Id

SELECT * FROM test.`user` WHERE id >= (SELECT id FROM test.`user` LIMIT 7721323, 1) LIMIT 10;

耗时：3.409s

5.BETWEEN

SELECT * FROM test.`user` WHERE id BETWEEN 7921323 AND 7921333 and `name` LIKE '%jack%' ;

耗时0.001s,这种情况也是利用主键索引

SELECT * FROM test.`user` WHERE `name` LIKE '%jack%' AND id BETWEEN 7921323 AND 7921333

耗时0.160s，从此可以看出条件放前，放后还是有区别的；

6.不利用索引单纯查询某一条记录

SELECT * FROM test.`user` WHERE `name` LIKE 'jack7721323'

耗时3.777s

为name字段添加索引

ALTER TABLE user ADD INDEX(name);

再执行上面查询：

耗时0.258s

show index from user; 查看user表索引

show table status from test where name='user';查看user表状态

总结：

针对ID连续的我们可以利用BETWEEN；对于ID不连续的我们可以用IN，但这是需要查询出ID集合，我们可以对所要查询的条件添加INDEX，需要注意的是：MySQL只对一下操作符才使用索引，,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)，而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引，建立索引的字段就越多,那数据量大的时候,索引文件就会越大,这是最显著的问题，同时

，一个索引会在 update 或 insert 时增加一次 I/O，对于操作系统底层来说是非常损耗性能的，索引过多一定情况下会导致索引文件过大(指数增长)，系统在寻址时查询时间增长。

分享到：

2016-07-25 11:30

浏览 1204

分类:数据库

评论