MySQ常量传递辨析

一 MySQL对于表达式的化简技术,支持常量传递这一技术,如下例:CREATE TABLE `t1` (`id1` int(11) NOT NULL DEFAULT

一 MySQL对于表达式的化简技术,支持常量传递这一技术,如下例:
CREATE TABLE `t1` (
`id1` int(11) NOT NULL DEFAULT '0',
`a1` int(11) DEFAULT NULL,
`b1` int(11) DEFAULT NULL,
PRIMARY KEY (`id1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1；

CREATE TABLE `t5` (
`id5` int(11) DEFAULT NULL,
`a5` int(11) DEFAULT NULL,
`b5` int(11) DEFAULT NULL,
UNIQUE KEY `id5` (`id5`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

两表各插入一些数据.

对于条件”a1=a5 and a5=1”,被MySQL的优化器优化后,变为”(`xx`.`t5`.`a5` = 1) and (`xx`.`t1`.`a1` = 1)”, 这就是常量传递技术.

mysql> explain extended select * from t5, t1 where a1=a5 and a5=1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t5 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 9999 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
/* select#1 */ select `xx`.`t5`.`id5` AS `id5`,`xx`.`t5`.`a5` AS `a5`,
`xx`.`t5`.`b5` AS `b5`,`xx`.`t1`.`id1` AS `id1`,`xx`.`t1`.`a1`AS `a1`,`xx`.`t1`.`b1` AS `b1`
from `xx`.`t5` join `xx`.`t1`
where ((`xx`.`t5`.`a5` = 1) and (`xx`.`t1`.`a1` = 1))

 

二 当条件表达式中存在主键的时候,情况会有些不同,如:
mysql> explain extended select * from t5, t1 where id1=id5 and id5=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t5 | const | id5 | id5 | 5 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
/* select#1 */ select '1' AS `id5`,'1' AS `a5`,NULL AS `b5`,
'1' AS `id1`,'1' AS `a1`,NULL AS `b1`
from `xx`.`t5` join `xx`.`t1`
where 1

为什么会这样?
这是因为:
1 常量传递技术,使得MySQL的优化器认为”id1=id5 =1”
2 而id1和id5分别都是主键,所以t1和t5表,都被认为是”常量表”,所以执行计划中type的值是const. 也就是说,t1和t5表元组完全能够确定(即能够在优化阶段被直接读出而不用在执行阶段定位查找元组). 所以t1和t5表的连接操作就很简单了.
3 到了显示执行计划的阶段,这时就不难理解为什么成为了”where 1”. 因为元组已经找到,此时条件已经不在起作用. 结果为真的条件正好满足常量表的元组输出.

所以,如下的一个主键和一个非主键间发生常量传递的情况也就容易理解了.
mysql> explain extended select * from t5, t1 where id1=a5 and a5=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | t5 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
/* select#1 */ select `xx`.`t5`.`id5` AS `id5`,`xx`.`t5`.`a5` AS `a5`,
`xx`.`t5`.`b5` AS `b5`,'1' AS `id1`,'1' AS `a1`,NULL AS `b1`
from `xx`.`t5` join `xx`.`t1`
where (`xx`.`t5`.`a5` = 1)

--------------------------------------分割线 --------------------------------------

Ubuntu 14.04下安装MySQL

《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主从服务器

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二进制安装

--------------------------------------分割线 --------------------------------------

本文永久更新链接地址：

，