MySQLのオプティマイザは実行結果に影響を与えないように、クエリーを変換することがあります。余分な操作を取り除き、クエリーをより速く実行できるようクエリーを書き換えることがこの「変換」のゴールです。例えば、次のクエリーについて考えてみましょう。
SELECT * FROM Country
WHERE population > 5000000 AND continent='Asia' AND 1=1;
「1は常に1」ですので、この条件とAND(ORではありません)は完全に冗長です。
クエリーを実行中に各行に対して「1が1のままである」ことを確認することには何にも意味がありませんし、条件を削除しても同じ結果がえられます。OPTIMIZER_TRACEを利用すると、MySQLがこの「変換」およびその他の数々の変換を実施していることを確認できます。
..
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`Country`.`Population` > 5000000) and (1 = 1))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`Country`.`Population` > 5000000) and (1 = 1))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`Country`.`Population` > 5000000) and (1 = 1))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`Country`.`Population` > 5000000)"
}
]
}
},
..
すべての変換が終わったあとの書き換えられたクエリーは、EXPLAINが実行されたあとのSHOW WARNINGSでも確認できます。上記のステートメントは次のように書き換えられます。
EXPLAIN FORMAT=JSON SELECT * FROM Country WHERE population > 5000000 AND 1=1;
SHOW WARNINGS;
/* select#1 */ select `world`.`Country`.`Code` AS `Code`,`world`.`Country`.`Name` AS `Name`,`world`.`Country`.`Continent` AS `Continent`,`world`.`Country`.`Region` AS `Region`,`world`.`Country`.`SurfaceArea` AS `SurfaceArea`,`world`.`Country`.`IndepYear` AS `IndepYear`,`world`.`Country`.`Population` AS `Population`,`world`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,`world`.`Country`.`GNP` AS `GNP`,`world`.`Country`.`GNPOld` AS `GNPOld`,`world`.`Country`.`LocalName` AS `LocalName`,`world`.`Country`.`GovernmentForm` AS `GovernmentForm`,`world`.`Country`.`HeadOfState` AS `HeadOfState`,`world`.`Country`.`Capital` AS `Capital`,`world`.`Country`.`Code2` AS `Code2` from `world`.`Country` where (`world`.`Country`.`Population` > 5000000)
下記に論理変換されたクエリーの例をいくつか示します。いくつかの例でPRIMARYおよびUNIQUEインデックスがクエリーの実行フェーズの前に永続的に変換されていることに注意してください。クエリーのこの部分は既に最も効率的な形式となっているため、オプティマイザは実行計画を考慮する前に永続的な変換を行っています。
ただし、考慮する実行計画の数を減らすための「他の最適化」は引き続き適用されます。
SELECT * FROM Country WHERE code='CAN'
/* select#1 */ select 'CAN' AS `Code`,'Canada' AS `Name`,'North America'
AS `Continent`, 'North America' AS `Region`,'9970610.00' AS
`SurfaceArea`,'1867' AS `IndepYear`, '31147000' AS `Population`,'79.4' AS
`LifeExpectancy`,'598862.00' AS `GNP`,'625626.00' AS `GNPOld`, 'Canada' AS
`LocalName`,'Constitutional Monarchy, Federation' AS `GovernmentForm`,
'Elisabeth II' AS `HeadOfState`,'1822' AS `Capital`,'CA' AS `Code2` from
`world`.`Country` where 1
SELECT * FROM Country WHERE code='XYZ'
/* select#1 */ select NULL AS `Code`,NULL AS `Name`,NULL AS
`Continent`,NULL AS `Region`, NULL AS `SurfaceArea`,NULL AS
`IndepYear`,NULL AS `Population`,NULL AS `LifeExpectancy`,NULL AS `GNP`,
NULL AS `GNPOld`,NULL AS `LocalName`,NULL AS `GovernmentForm`,NULL AS
`HeadOfState`,NULL AS `Capital`, NULL AS `Code2` from `world`.`Country`
where multiple equal('XYZ', NULL)
SELECT * FROM Country WHERE code='CAN' AND 1=0
/* select#1 */ select `world`.`Country`.`Code` AS `Code`,
`world`.`Country`.`Name` AS `Name`, `world`.`Country`.`Continent`
AS `Continent`,`world`.`Country`.`Region` AS `Region`,
`world`.`Country`.`SurfaceArea` AS
`SurfaceArea`,`world`.`Country`.`IndepYear` AS `IndepYear`,
`world`.`Country`.`Population` AS
`Population`,`world`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,
`world`.`Country`.`GNP` AS `GNP`,`world`.`Country`.`GNPOld` AS `GNPOld`,
`world`.`Country`.`LocalName` AS
`LocalName`,`world`.`Country`.`GovernmentForm` AS `GovernmentForm`,
`world`.`Country`.`HeadOfState` AS
`HeadOfState`,`world`.`Country`.`Capital` AS `Capital`,
`world`.`Country`.`Code2` AS `Code2` from `world`.`Country` where 0
SELECT City.* FROM City, (SELECT * FROM Country WHERE continent='Asia') as
Country WHERE Country.code=City.CountryCode AND Country.population >
5000000;
/* select#1 */ select `world`.`City`.`ID` AS `ID`,`world`.`City`.`Name` AS
`Name`, `world`.`City`.`CountryCode` AS
`CountryCode`,`world`.`City`.`District` AS `District`,
`world`.`City`.`Population` AS `Population` from `world`.`City` join
`world`.`Country` where ((`world`.`Country`.`Continent` = 'Asia') and
(`world`.`City`.`CountryCode` = ` world`.`Country`.`Code`) and
(`world`.`Country`.`Population` > 5000000))
CREATE VIEW Countries_in_asia AS SELECT * FROM Country WHERE
continent='Asia'; SELECT * FROM Countries_in_asia WHERE population >
5000000;
/* select#1 */ select `world`.`Country`.`Code` AS
`Code`,`wo`rld`.`Country`.`Name` AS `Name`, `world`.`Country`.`Continent`
AS `Continent`,`world`.`Country`.`Region` AS `Region`,
`world`.`Country`.`SurfaceArea` AS `SurfaceArea`,`world`.`Country`.`IndepYear`
AS `IndepYear`, `world`.`Country`.`Population` AS
`Population`,`world`.`Country`.`LifeExpectancy` AS `LifeExpectancy`, `world`.`Country`.`GNP` AS `GNP`,`world`.`Country`.`GNPOld` AS `GNPOld`,
`world`.`Country`.`LocalName` AS
`LocalName`,`world`.`Country`.`GovernmentForm` AS `GovernmentForm`,
`world`.`Country`.`HeadOfState` AS `HeadOfState`,`world`.`Country`.`Capital` AS `Capital`, `world`.`Country`.`Code2` AS `Code2` from `world`.`Country` where ((`world`.`Country`.`Continent` = 'Asia') and (`world`.`Country`.`Population` > 5000000))
この挙動はoptimizer_switchのderived_merge(デフォルトではon)の設定に依存します ↩