在取mysql主键最大最小值时，mysql会使用最优方式 SELECT tables optimized away
官方解释
SELECT tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段时，MySQL Query Optimizer 会通过索引直接一次定位到所需的数据行完成整个查询。当然，前提是在 Query 中不能有 GROUP BY 操作。如使用MIN()或MAX()的时候
[cc lang='sql']root@wap 06:57:32>explain select max(id) from tmp_xf;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
[/cc]
我们知道，innodb引擎的表是索引组织表，按照主键顺序存储。每个索引的都包含主键用来做指针定位到具体的记录。
[cc lang='sql']dbaeyes 09:48:10>show create table tmp_xf\G
*************************** 1. row ***************************
Table: tmp_xf
Create Table: CREATE TABLE `tmp_xf` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
`account` varchar(50) NOT NULL ,
`status` int(11) NOT NULL ,
`activity_id` int(11) NOT NULL,
`gmt_create` datetime NOT NULL,
`gmt_modified` datetime NOT NULL ,
PRIMARY KEY (`id`),
KEY `status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=990001 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
[/cc]
表记录99w
select max(id) from tmp_xf where status = 0; 但是这个sql并不能使用
optimized away
[cc lang='sql']dbaeyes 09:48:19>explain select max(id) from tmp_xf where status = 0;
+----+-------------+--------+------+---------------+--------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+--------+---------+-------+--------+-------------+
| 1 | SIMPLE | tmp_xf | ref | status | status | 4 | const | 494509 |Using index |
+----+-------------+--------+------+---------------+--------+---------+-------+--------+-------------+[/cc]
sql直接使用了索引，而且扫描了整个索引才获取到需要的数据，这个效率是非常低的。从执行计划可以看到扫描了49w+
dbaeyes 09:48:30>select max(id) from tmp_xf where status = 0;
+---------+
| max(id) |
+---------+
| 990000 |
+---------+
1 row in set (1.89 sec)
下面尝试另外一种方式，我们创建 包含了 status,id的索引，既把id显示的放在索引中，这样id在索引中肯定是排序好的。
[cc lang='sql'] root@wap 07:21:36>explain select max(id) from tmp_xf where status = 0;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)
root@wap 07:21:54>select max(id) from tmp_xf where status = 0;
+---------+
| max(id) |
+---------+
| 990000 |
+---------+
1 row in set (0.00 sec)
[/cc]
--eof--
This work is licensed under a CC A-S 4.0 International License.