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对于我们来讲都是一亿两千万而已.这好像不太符合程序员的严谨性. (/笑哭)