問題一：1.有什么可視化的，能夠特別直觀的分析SQL語句性能的工具？問題二：select * limit offset,amount;select * where id between offset and offset+amount;select * where id > offset limit amount;以上三個SQL語句，哪個性能更佳呢？項目中我們一般用的好像是limit a b 這樣的吧，性能又如何呢？
4 回答
慕哥6287543
TA貢獻1831條經(jīng)驗 獲得超10個贊
explain
根據(jù)上面提到的explain去比較，就可以得出結(jié)果了
mysql> explain select * from users limit 1000,20;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 169847 | |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)
mysql> explain select * from users where uid>1000 limit 20;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | users | range | PRIMARY | PRIMARY | 4 | NULL | 84923 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from users where uid between 1000 and 1020;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | users | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
添加回答
舉報
0/150
提交
取消