设为首页 加入收藏

TOP

MySQL中min和max查询优化(一)
2019-01-02 00:08:45 】 浏览:38
Tags:MySQL min max 查询 优化

MySQL max() 函数的需扫描where条件过滤后的所有行:


在测试环境中重现:


测试版本:Server version:        5.1.58-log MySQL Community Server (GPL)


testtable表中的索引


mysql> show index from testtable;
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| testtable |          0 | PRIMARY    |            1 | id          | A        |          2 |    NULL | NULL  |      | BTREE      |        |
| testtable |          1 | key_number |            1 | number      | A        |          2 |    NULL | NULL  | YES  | BTREE      |        |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


对比的sql为:


 select sql_no_cache  max(id) from testtable where number=98;
 select sql_no_cache id from testtable where number=98 order by id desc limit 1;


查看执行计划:


mysql> explain select sql_no_cache  max(id) from testtable where number=98;
+----+-------------+-----------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | testtable | ref  | key_number    | key_number | 5      | const |    4 | Using where; Using index |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)



mysql> explain select sql_no_cache id from testtable where number=98 order by id desc limit 1;
+----+-------------+-----------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | testtable | ref  | key_number    | key_number | 5      | const |    4 | Using where; Using index |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
执行计划显示完全一样。


其中,n
编程开发网

首页 上一页 1 2 下一页 尾页 1/2/2
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇MySQL查看binlog日志内容 下一篇MySQL实例crash的案例分析

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

array(4) { ["type"]=> int(8) ["message"]=> string(24) "Undefined variable: jobs" ["file"]=> string(32) "/mnt/wp/cppentry/do/bencandy.php" ["line"]=> int(214) }