慎用mysql max()函数,可通过走索引替换
来源:互联网 发布:python socket 多连接 编辑:程序博客网 时间:2024/05/22 12:21
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)
执行计划显示完全一样。
其中,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';+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_rows_read | 1022 |
+------------------+-------+
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)
执行后查看innodb_rows_read,发现innodb_rows_read增加了4,即number为98 对应的记录有4行
mysql> show global status like 'innodb_rows_read';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_rows_read | 1026 |
+------------------+-------+
1 row in set (0.00 sec)
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';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_rows_read | 1027 |
+------------------+-------+
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
- 慎用mysql max()函数,可通过走索引替换
- 慎用mysql max()函数,可通过走索引替换
- mysql 联合索引> 和 order by 不走 索引 慎用
- MySQL:UNIX_TIMESTAMP函数不走索引问题
- mysql添加索引的方法,及max()函数的优化
- mysql 执行计划走索引
- mysql不走索引总结
- MySQL in不走索引
- 如何让table表的null列由不走索引变为可走索引
- mysql in 不走索引的思考
- to_char函数引发的不走索引
- MySQL中的MAX函数总结
- mysql 有索引没走索引 更新锁全表
- mysql索引列为空,也可以走索引
- 慎用位图索引
- mysql可通过两种方式通过PHP与web相连,一种通过php的mysql相关函数,另一种通过php的ODBC相关函数
- mysql中MAX()函数MIN()函数
- mysql全文索引替换like '%11%'
- NodeJS+Redis实现分布式Session方案
- SpringMVC 学习系列 (11) 之 表单标签
- CF 509B Painting Pebbles
- sipXecs安装完成后,登录地址
- 组合框自动记录
- 慎用mysql max()函数,可通过走索引替换
- JNI中在被调用的C/C++函数中如何访问Java程序中的类,并编写应用实例
- php调用shell脚本(web端和客户端同时实现)
- thrift 安装 C++server 及PHP Client 测试Demo
- iOS Runtime详解(消息机制,类元对象,缓存机制,消息转发)
- Attribute在.net编程中的应用(一)
- MySQL中的MAX函数总结
- PendingIntent的内部机制
- 关于索引的那些事