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: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3011379
        Extra: NULL
1 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: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2000002
        Extra: NULL
1 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: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1505689
        Extra: Using where
1 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: ref
possible_keys: typeid_price
          key: typeid_price
      key_len: 4
          ref: const
         rows: 1505689
        Extra: Using where; Using filesort
1 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: 1
2 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: ref
possible_keys: typeid_price,type_ID
          key: typeid_price
      key_len: 4
          ref: const
         rows: 1505689
        Extra: Using where; Using filesort
1 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: ref
possible_keys: type_ID
          key: type_ID
      key_len: 4
          ref: const
         rows: 1505689
        Extra: Using where
1 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: 1
2 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对于我们来讲都是一亿两千万而已.这好像不太符合程序员的严谨性. (/笑哭)