84669 人學習
152542 人學習
20005 人學習
5487 人學習
7821 人學習
359900 人學習
3350 人學習
180660 人學習
48569 人學習
18603 人學習
40936 人學習
1549 人學習
1183 人學習
32909 人學習
问题一:1.有什么可视化的,能够特别直观的分析SQL语句性能的工具?
问题二:
select * limit offset,amount; select * where id between offset and offset+amount; select * where id > offset limit amount;
以上三个SQL语句,哪个性能更佳呢?项目中我们一般用的好像是limit a b 这样的吧,性能又如何呢?
认证0级讲师
http://www.cnblogs.com/RunFor...
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表达的都不是一个意思,所以无可比性。
参考:https://mp.weixin.qq.com/s?__...
select * limit offset,amount;select * where id between offset and offset+amount;select * where id > offset limit amount; 这条性能最佳为何?因为id比较走了索引,如果id没有索引,还可以加索引,请记住,select判断性能佳不佳 首先去看下自己的where条件有没有可能走索引,如果自己的几种方案都走了索引,那么再用explain去具体看下到底哪些语句性能最佳
后两个要优于第一个
http://www.cnblogs.com/RunFor...
explain
根据上面提到的explain去比较,就可以得出结果了
explain +1
还有,你这3条SQL表达的都不是一个意思,所以无可比性。
参考:https://mp.weixin.qq.com/s?__...
select * limit offset,amount;
select * where id between offset and offset+amount;
select * where id > offset limit amount; 这条性能最佳
为何?因为id比较走了索引,如果id没有索引,还可以加索引,请记住,select判断性能佳不佳 首先去看下自己的where条件有没有可能走索引,如果自己的几种方案都走了索引,那么再用explain去具体看下到底哪些语句性能最佳
后两个要优于第一个