更换服务器后,mysql查询变慢很多,采用的是直接拷贝MYI,MYD,frm文件的形式备份的数据库,新服务器上mysql版本和旧服务器上的版本完全一样,同样sql(SELECT id,keyWords,shortUrl FROM `keywords`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `keywords`)-(SELECT MIN(id) FROM `keywords`)) + (SELECT MIN(id) FROM `keywords`)))
ORDER BY id LIMIT 300;)老服务器上不到1s,新服务器上用了几分钟
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `keywords`)-(SELECT MIN(id) FROM `keywords`)) + (SELECT MIN(id) FROM `keywords`)))
ORDER BY id LIMIT 300;)老服务器上不到1s,新服务器上用了几分钟
explain
另外 my.cnf的配置一样吗
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+------------------------------+
| 1 | PRIMARY | keywords | index | NULL | PRIMARY | 4 | NULL | 3209133 | Using where |
| 5 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 4 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 3 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+------------------------------+
4 rows in set, 1 warning (0.00 sec)
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+------------------------------+
| 1 | PRIMARY | keywords | index | NULL | PRIMARY | 4 | NULL | 3209133 | Using where |
| 5 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 4 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 3 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+------------------------------+
4 rows in set, 1 warning (0.00 sec)