sql语句执行性能分析

Question

问题一：1.有什么可视化的，能够特别直观的分析SQL语句性能的工具？问题二：select *  limit offset,amount;select *  where id between offset and offset+amount;select *  where id > offset limit amount;以上三个SQL语句，哪个性能更佳呢？项目中我们一般用的好像是limit a b 这样的吧，性能又如何呢？

Answer 1

1. explain

2. 根据上面提到的explain去比较，就可以得出结果了

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)