MYSQL优化之分页

1. 生成测试数据

使用蠕虫复制

insert into tb_name (name,introduce) select name,introduce from tb_name;

注意,使用蠕虫复制的时候,选择的字段不能具有unique key(唯一索引)

多运行几次

Affected rows : 1572864, Time: 50.57sec

现在我有三百万数据了.

2. 使用传统的limit 200,2查询

mysql> select id,name,introduce from ds_goods limit 2000000,2;+---------+-----------+-------------------------------------------------+| id      | name      | introduce                                       |+---------+-----------+-------------------------------------------------+| 2262122 | adfsdf    | http://static.loseu.cn/goodsDetail/1741/45.html || 2262123 | adfsdfwer | http://static.loseu.cn/goodsDetail/1741/45.html |+---------+-----------+-------------------------------------------------+2 rows in set (6.75 sec)

可以看到用了6.75s

使用explain查看执行计划

mysql> explain select id,name,introduce from ds_goods limit 2000000,2\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: ds_goods         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 3011379        Extra: NULL1 row in set (0.00 sec)

使用了全表扫描.

3. 一个有趣的分表思路

以上用时较长的原因在于,其他的字段太长,导致占用了大量的物理空间,物理指针移动的速度有限导致的查询过慢.

建一个索引表： t (id) ，然后做分页，分页出结果即拿到了想要的id,再到 ds_goods 里面去找对应的数据.

仔细分析就会发现,这其实就是索引的一个变种而已.我没有专门去测试.

需要注意的,这种方法使用的in,它是可以用到主键索引的,所以应该会特别快.

4. 使用order by id limit 2000000,2

mysql> explain select id,name,introduce from ds_goods order by id limit 2000000,2\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: ds_goods         type: indexpossible_keys: NULL          key: PRIMARY      key_len: 4          ref: NULL         rows: 2000002        Extra: NULL1 row in set (0.00 sec)

用到了主键索引

5. 使用where id > 2000000 limit 2

mysql> explain select id,name,introduce from ds_goods where id > 2000000 limit 2\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: ds_goods         type: rangepossible_keys: PRIMARY          key: PRIMARY      key_len: 4          ref: NULL         rows: 1505689        Extra: Using where1 row in set (0.00 sec)

使用到了主键索引.但是type类型是range比index好一点.

6. 工作中的变种

实际上发现,只要用到了合适的索引,那么速度都不会很慢.所以优化sql,然后根据sql建立合适的索引才是精髓所在.

例子如下.

select id,name,type from ds_goods where type = 1 order by id limit 1000000,2;

在未建立合适索引的情况下…卡爆了.

然后我把limit后面的数据换成了一个比较小的数字,100 000 也用了15秒…

mysql> select id,name,type_id from ds_goods where type_id = 1 order by id limit 100000,2;+--------+--------------+---------+| id     | name         | type_id |+--------+--------------+---------+| 182772 | 小米手机     |       1 || 182773 | 大米手机     |       1 |+--------+--------------+---------+2 rows in set (1 min 15.70 sec)

执行计划显示

mysql> explain select id,name,type_id from ds_goods where type_id = 1 order by id limit 100000,2\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: ds_goods         type: refpossible_keys: typeid_price          key: typeid_price      key_len: 4          ref: const         rows: 1505689        Extra: Using where; Using filesort1 row in set (0.00 sec)

我实际用到了索引,但是这个名字叫typeid_price是我为type_id和price创建的复合索引. 实际上在Extra中也告诉我们了Using filesort,这是最差的情况. 其实和没用到索引是一个样子.

建立索引

mysql> alter table ds_goods add key type_ID(type_id,id) ;Query OK, 0 rows affected (15.09 sec)Records: 0  Duplicates: 0  Warnings: 0

数据量大的时候,建立时间还蛮久的(15s),但是也许是值得的.

mysql> select id,name,type_id from ds_goods where type_id = 1 order by id limit 1000000,2\G*************************** 1. row ***************************     id: 1696599   name: 小米手机type_id: 1*************************** 2. row ***************************     id: 1696600   name: 大米手机type_id: 12 rows in set (1 min 36.42 sec)mysql> explain select id,name,type_id from ds_goods where type_id = 1 order by id limit 1000000,2\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: ds_goods         type: refpossible_keys: typeid_price,type_ID          key: typeid_price      key_len: 4          ref: const         rows: 1505689        Extra: Using where; Using filesort1 row in set (0.00 sec)

还是慢的不行,查看执行计划,发现居然还是用到了typeid_price索引和Using filesort.

我试着删掉typeid_price索引试下.

mysql> alter table ds_goods drop key typeid_price;Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select id,name,type_id from ds_goods where type_id = 1 order by id limit 1000000,2\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: ds_goods         type: refpossible_keys: type_ID          key: type_ID      key_len: 4          ref: const         rows: 1505689        Extra: Using where1 row in set (0.00 sec)mysql> select id,name,type_id from ds_goods where type_id = 1 order by id limit 1200000,2\G*************************** 1. row ***************************     id: 2062122   name: 小米手机type_id: 1*************************** 2. row ***************************     id: 2062123   name: 大米手机type_id: 12 rows in set (7.13 sec)

用了七秒,是快了很多.但是明显还没达到我们的要求.

看来索引并非万能,至少在这里因为type类型并不是很多,所以为type建立索引的意义似乎不是很大(看来我之前的type_price的索引其实用处也不大,正好刚才删了).还是得想办法优化sql.

mysql> select id,name,type_id from ds_goods where type_id = 1 and id > 1200000 limit 2;+---------+--------------+---------+| id      | name         | type_id |+---------+--------------+---------+| 1200001 | adfsdwer     |       1 || 1200002 | adfadfjladsf |       1 |+---------+--------------+---------+2 rows in set (0.04 sec)

使用到了索引,速度也很快.

但是这种方法有种限制,必须由前台传递上一次查询的最大ID.但是直接跳转到n页的时候,就不好用了.但解决办法其实也很简单,不提供直接跳转到n页的功能就好了.

非要跳转到n页的功能的话,我个人认为可以用in,in是可以用到索引的.且当数字很小的时候,我们对数字是敏感的,1和11明显不同,但是123123213和123222222对于我们来讲都是一亿两千万而已.这好像不太符合程序员的严谨性. (/笑哭)