select 出现排序过慢(非插入顺序)

在查询测试时发现,查询很慢,60万条查一,用了10秒,

使用了explain分析一下sql解释过程,发现没使用主键,而是使用了filesort,把缓存放到文件中进行了排序;

觉得很奇怪,在其它机器中,使用了id来排序,很快.才1秒,或是少于这个.

后来才发现,原来此机内存过小(设置过小也会导致),导致数据量大时,内存无法进行排序放到硬盘中来了.才这么慢.

暂时解决方式是小表/多表了.

 

mysql> select count(*) from email;
+----------+
| count(*) |
+----------+
|   148973 |
+----------+
1 row in set (0.00 sec)

mysql> explain select * from email where id > 400000  order by id asc limit 400000, 100;
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | email | ALL  | PRIMARY       | NULL | NULL    | NULL | 148973 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain select * from email where id > 100000  order by id asc limit 100000, 100;
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | email | range | PRIMARY       | PRIMARY | 4       | NULL | 148971 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> select * from email where id > 100000  order by id asc limit 100000, 100;
+---------+-----------------------------------+---------+------+
| id      | email                             | unexist | time |