MySQ常量傳遞辨析

MySQ常量傳遞辨析

一 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通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：