linuxsir首页 LinuxSir.Org | Linux、BSD、Solaris、Unix | 开源传万世,因有我参与欢迎您!
网站首页 | 设为首页 | 加入收藏
您所在的位置:主页 > Linux数据库 >

MySQL中min和max查询优化

时间:2019-01-04  来源:未知  作者:admin666

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

在测试环境中重现:

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

testtable表中的索引

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

对比的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;
+----+-储藏2------------+-储藏2----------+-储藏2-----+-储藏2--------------+-储藏2-----------+-储藏2--------+-储藏2------+-储藏2-----+-储藏2-------------------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref  | rows | Extra                    |
+----+-储藏2------------+-储藏2----------+-储藏2-----+-储藏2--------------+-储藏2-----------+-储藏2--------+-储藏2------+-储藏2-----+-储藏2-------------------------+
|  1 | SIMPLE      | testtable | ref  | key_number    | key_number | 5      | const |    4 | Using where; Using index |
+----+-储藏2------------+-储藏2----------+-储藏2-----+-储藏2--------------+-储藏2-----------+-储藏2--------+-储藏2------+-储藏2-----+-储藏2-------------------------+
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;
+----+-储藏2------------+-储藏2----------+-储藏2-----+-储藏2--------------+-储藏2-----------+-储藏2--------+-储藏2------+-储藏2-----+-储藏2-------------------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref  | rows | Extra                    |
+----+-储藏2------------+-储藏2----------+-储藏2-----+-储藏2--------------+-储藏2-----------+-储藏2--------+-储藏2------+-储藏2-----+-储藏2-------------------------+
|  1 | SIMPLE      | testtable | ref  | key_number    | key_number | 5      | const |    4 | Using where; Using index |
+----+-储藏2------------+-储藏2----------+-储藏2-----+-储藏2--------------+-储藏2-----------+-储藏2--------+-储藏2------+-储藏2-----+-储藏2-------------------------+
1 row in set (0.00 sec)
执行计划显示完全一样。

其中,number为98 对应的记录有4行:

mysql> select count(*) from testtable where number=98;

+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

 

执行前查看innodb_rows_read

#innodb_rows_read  从InnoDB表读取的行数

mysql> show global status like 'innodb_rows_read';
+------------------+-储藏2------+
| Variable_name    | Value |
+------------------+-储藏2------+
| Innodb_rows_read | 1022  |
+------------------+-储藏2------+
1 row in set (0.00 sec)

执行sql1
mysql> select sql_no_cache  max(id) from testtable where number=98;
+---------+
| max(id) |
+---------+
|      13 |
+---------+

1 row in set (0.00 sec)


执行后查看浙江风采双色球走势图2innodb_rows_read,发现innodb_rows_read增加了4,即number为98 对应的记录有4行
mysql> show global status like 'innodb_rows_read';
+------------------+-储藏2------+
| Variable_name    | Value |
+------------------+-储藏2------+
| Innodb_rows_read | 1026  |
+------------------+-储藏2------+
1 row in set (0.00 sec)

 

执行sql2
mysql> select sql_no_cache id from testtable where number=98 order by id desc limit 1;
+----+
| id |
+----+
| 13 |
+----+
1 row in set (0.00 sec)

执行后查看innodb_rows_read,发现innodb_rows_read增加了1
mysql> show global status like 'innodb_rows_read';
+------------------+-储藏2------+
| Variable_name    | Value |
+------------------+-储藏2------+
| Innodb_rows_read | 1027  |
+------------------+-储藏2------+
1 row in set (0.00 sec)

测试得出:

 select sql_no_cache  max(id) from testtable where number=98;

需要读取 number=98 的所有行,才能得到最大的id

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

由于id是主键,number是第二索引,只需扫描1行即可得到最大的id

请慎用max()函数,特别是频繁执行的sql,若需用到可转化为测试中的  order by id desc limit 1

因为往往min()或者max()函数往往会造成全表扫描

Linux公社的RSS地址:https://www.linuxidc.com/rssFeed.aspx

友情链接