java sql语句执行时间差,【Java】sql语句执行性能分析

问题一：

1.有什么可视化的，能够特别直观的分析SQL语句性能的工具？

问题二：

select * limit offset,amount;

select * where id between offset and offset+amount;

select * where id > offset limit amount;

以上三个SQL语句，哪个性能更佳呢？项目中我们一般用的好像是limit a b 这样的吧，性能又如何呢？

回答

explain

根据上面提到的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)

explain +1

还有，你这3条SQL表达的都不是一个意思，所以无可比性。

select * limit offset,amount;

select * where id between offset and offset+amount;

select * where id > offset limit amount; 这条性能最佳

为何？因为id比较走了索引，如果id没有索引，还可以加索引，请记住，select判断性能佳不佳 首先去看下自己的where条件有没有可能走索引，如果自己的几种方案都走了索引，那么再用explain去具体看下到底哪些语句性能最佳

后两个要优于第一个