84669 person learning
152542 person learning
20005 person learning
5487 person learning
7821 person learning
359900 person learning
3350 person learning
180660 person learning
48569 person learning
18603 person learning
40936 person learning
1549 person learning
1183 person learning
32909 person learning
问题一: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去具体看下到底哪些语句性能最佳
后两个要优于第一个