java - sql语句执行性能分析
PHP中文网
PHP中文网 2017-04-18 09:38:10
0
5
410

问题一:
1.有什么可视化的,能够特别直观的分析SQL语句性能的工具?

问题二:

select *  limit offset,amount;
select *  where id between offset and offset+amount;
select *  where id > offset limit amount;

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

PHP中文网
PHP中文网

认证0级讲师

全部回复(5)
巴扎黑
  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)
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责声明 Sitemap
PHP中文网:公益在线PHP培训,帮助PHP学习者快速成长!