如果A=B，并且 B=1，那么可以得到A=1。下面这个例子中， 通过show warnings可以看到，WHERE条件被写成了t1.a = 1 and t2.b = 1。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
mysql> explain select * from tab t1, tab t2 where t1.a = t2.b and t2.b = 1; +----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 9913 | 10.00 | Using where | | 1 | SIMPLE | t1 | ALL | idx_abc | NULL | NULL | NULL | 9913 | 33.62 | Using where; Using join buffer (hash join) | +----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `rep`.`t1`.`id` AS `id`,`rep`.`t1`.`a` AS `a`, `rep`.`t1`.`b` AS `b`,`rep`.`t1`.`c` AS `c`,`rep`.`t1`.`padding` AS `padding`, `rep`.`t2`.`id` AS `id`,`rep`.`t2`.`a` AS `a`,`rep`.`t2`.`b` AS `b`, `rep`.`t2`.`c` AS `c`,`rep`.`t2`.`padding` AS `padding` from `rep`.`tab` `t1` join `rep`.`tab` `t2` where ((`rep`.`t2`.`b` = 1) and (`rep`.`t1`.`a` = 1))
移除重复或多余的条件
1 2 3 4 5 6
mysql> explain select * from tab where a = 5 and a between 1 and 10; +----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | tab | ref | idx_abc | idx_abc | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
移除永远为真的条件
条件1=1永远成立，优化器会直接移除类似这样的条件。
1 2 3 4 5 6
mysql> explain select * from tab where 1=1; +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | tab | ALL | NULL | NULL | NULL | NULL | 9913 | 100.00 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
对于永远无法满足的条件，直接返回无数据
下面这个例子中，t1.a < 0 and t1.a > 0逻辑上就不成立，因此查询可以直接返回无数据。
1 2 3 4 5 6 7
mysql> explain select * from tab t1, tab t2 where t1.a = t2.a and t1.a < 0 and t1.a > 0; +----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | +----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
外连接改写为内连接
下面这个SQL中，由于t2.a > 0这个条件，left join被转换成了普通的join。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
mysql> explain select * from tab t1 left join t2 on t1.a = t2.a where t2.a > 0; +----+-------------+-------+------+---------------+---------+---------+----------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+---------+---------+----------+------+----------+-------------+ | 1 | SIMPLE | t1 | ALL | idx_abc | NULL | NULL | NULL | 9913 | 67.25 | Using where | | 1 | SIMPLE | t2 | ref | idx_abc | idx_abc | 4 | rep.t1.a | 3304 | 100.00 | NULL | +----+-------------+-------+------+---------------+---------+---------+----------+------+----------+-------------+ mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `rep`.`t1`.`id` AS `id`,`rep`.`t1`.`a` AS `a`, `rep`.`t1`.`b` AS `b`,`rep`.`t1`.`c` AS `c`,`rep`.`t1`.`padding` AS `padding`, `rep`.`t2`.`id` AS `id`,`rep`.`t2`.`a` AS `a`,`rep`.`t2`.`b` AS `b`, `rep`.`t2`.`c` AS `c`,`rep`.`t2`.`padding` AS `padding` from `rep`.`tab` `t1` join `rep`.`tab` `t2` where ((`rep`.`t2`.`a` = `rep`.`t1`.`a`) and (`rep`.`t1`.`a` > 0))
mysql> create table t_cost ( id int not null auto_increment, a int not null, b int not null, c int not null, d int not null, padding varchar(7000) DEFAULT NULL, primary key (id), key idx_ac(a,c), key idx_bc(b,c) ) engine=InnoDB;
mysql> insert into t_cost (a,b,c,d, padding) select n%6, n%1000, n%100, n%100, rpad('', 2000, 'ABCDEFG XYZ') from numbers;
mysql> set optimizer_trace='enabled=on'; Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_cost where a=3 and b between 90 and 100 and c in (1,2,3,4,5,6,7,8,9,10) ; Empty set (0.01 sec)
mysql> select * from information_schema.optimizer_trace\G *************************** 1. row *************************** QUERY: select * from t_cost where a=3 and b between 90 and 100 and c in (1,2,3,4,5,6,7,8,9,10) TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "IN_uses_bisection": true }, { ......
"analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "idx_ac", "ranges": [ "a = 3 AND c = 1", "a = 3 AND c = 2", "a = 3 AND c = 3", "a = 3 AND c = 4", "a = 3 AND c = 5", "a = 3 AND c = 6", "a = 3 AND c = 7", "a = 3 AND c = 8", "a = 3 AND c = 9", "a = 3 AND c = 10" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "in_memory": 1, "rows": 172, "cost": 62.71, "chosen": true }, { "index": "idx_bc", "ranges": [ "90 <= b <= 100" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "in_memory": 1, "rows": 110, "cost": 38.76, "chosen": true } ]
{ "index": "idx_ac", "ranges": [ "a = 3 AND c = 1", "a = 3 AND c = 2", "a = 3 AND c = 3", "a = 3 AND c = 4", "a = 3 AND c = 5", "a = 3 AND c = 6", "a = 3 AND c = 7", "a = 3 AND c = 8", "a = 3 AND c = 9", "a = 3 AND c = 10" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "in_memory": 1, "rows": 172, "cost": 62.71, "chosen": true }
mysql> explain select * from t_cost where b in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) and c in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15); +----+-------------+--------+-------+---------------+--------+---------+------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+-------+---------------+--------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t_cost | range | idx_bc | idx_bc | 8 | NULL | 1800 | 100.00 | Using index condition | +----+-------------+--------+-------+---------------+--------+---------+------+------+----------+-----------------------+
参数range_optimizer_max_mem_size用来限制range优化能使用的内存。对于where a in (x,x,x) and b in (x,x,x) and c in (x,x,x)这样的条件，每一个组合都会消耗一定的内存，当组合的数量特别多时，如果内存消耗量超出了range_optimizer_max_mem_size的限制，优化器就会放弃这个range优化，改为使用全表扫描。
下面这个例子中，我们将range_optimizer_max_mem_size设置得小一些，就能看到这样的waning信息“Memory capacity of 16384 bytes for ‘range_optimizer_max_mem_size’ exceeded”。现实中，我也遇到过由于in的组合数太多导致的全表扫描。这种情况下，即使使用了FORCE INDEX提示，优化器还是会使用全表扫描。
mysql> set range_optimizer_max_mem_size=16384; Query OK, 0 rows affected (0.01 sec)
mysql> explain select * from tab where a in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and b in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and c in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) ; +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tab | ALL | idx_abc | NULL | NULL | NULL | 9913 | 12.50 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec)
mysql> explain select * from tab force index(idx_abc) where a in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and b in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and c in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) ; +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tab | ALL | idx_abc | NULL | NULL | NULL | 9913 | 12.50 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec)
1 2 3 4 5
mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 3170 Message: Memory capacity of 16384 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
