mysql optimizer组件_mysql optimizer_switch

参考:https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html

一 mysql 优化器常用选项

mysql> SELECT @@optimizer_switch\G

*************************** 1. row ***************************

@@optimizer_switch: index_merge=on,index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on,

engine_condition_pushdown=on,

index_condition_pushdown=on,

mrr=on,mrr_cost_based=on, block_nested_loop=on,batched_key_access=off,

materialization=on,semijoin=on,loosescan=on, firstmatch=on,

subquery_materialization_cost_based=on,

use_index_extensions=on

二 介绍各个优化项作用

2.1 use_index_extensions 二级索引扩展,包括主键信息

有如下表

CREATE TABLE t1 (

i1 INT NOT NULL DEFAULT 0,

i2 INT NOT NULL DEFAULT 0,

d DATE DEFAULT NULL,

PRIMARY KEY (i1, i2),

INDEX k_d (d)

) ENGINE = InnoDB;

插入语句INSERT INTO t1 VALUES (1, 1, '1998-01-01'), (1, 2, '1999-01-01'), (1, 3, '2000-01-01'), (1, 4, '2001-01-01'), (1, 5, '2002-01-01'), (2, 1, '1998-01-01'), (2, 2, '1999-01-01'), (2, 3, '2000-01-01'), (2, 4, '2001-01-01'), (2, 5, '2002-01-01'), (3, 1, '1998-01-01'), (3, 2, '1999-01-01'), (3, 3, '2000-01-01'), (3, 4, '2001-01-01'), (3, 5, '2002-01-01'), (4, 1, '1998-01-01'), (4, 2, '1999-01-01'), (4, 3, '2000-01-01'), (4, 4, '2001-01-01'), (4, 5, '2002-01-01'), (5, 1, '1998-01-01'), (5, 2, '1999-01-01'), (5, 3, '2000-01-01'), (5, 4, '2001-01-01'), (5, 5, '2002-01-01');

表定义了主键(i1,i2), 二级索引 k_d(d) 其实为(d,i1,i2)

对于如下语句分别在 use_index_extensions=off/on 检查执行计划

EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'

当use_index_extensions=off

mysql> set session optimizer_switch='use_index_extensions=off';

Query OK, 0 rows affected (0.01 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t1 force index(k_d) WHERE i1 = 3 AND d = '2000-01-01';

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+

| 1 | SIMPLE | t1 | NULL | ref | k_d | k_d | 4 | const | 5 | 20.00 | Using where; Using index |

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+

1 row in set, 1 warning (0.00 sec)

当use_index_extensions=on

mysql> set session optimizer_switch='use_index_extensions=on';

Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';

+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

| 1 | SIMPLE | t1 | NULL | ref | PRIMARY,k_d | k_d | 8 | const,const | 1 | 100.00 | Using index |

+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

发现如下变化,说明使用了二级索引的扩容功能

key_len 4->8 因为只使用到列 d 和 i1

ref const -> const,const 因为key查找两个部分

rows 5->1  innodb检查更少的行来产生结果

filtered 20->100

Extra Using where; Using index -> Using index 只要使用索引就行,不需要回表查询

下面通过刷新表,来检查优化器的异同

FLUSH TABLE t1;

FLUSH STATUS;

SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';

SHOW STATUS LIKE 'handler_read%';

当use_index_extensions=off

mysql> SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';

+----------+

| COUNT(*) |

+----------+

| 1 |

+----------+

1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'handler_read%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Handler_read_first | 0 |

| Handler_read_key | 1 |

| Handler_read_last | 0 |

| Handler_read_next | 5 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 0 |

+-----------------------+-------+

7 rows in set (0.00 sec)

当use_index_extensions=on

mysql> SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';

+----------+

| COUNT(*) |

+----------+

| 1 |

+----------+

1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'handler_read%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Handler_read_first | 0 |

| Handler_read_key | 1 |

| Handler_read_last | 0 |

| Handler_read_next | 1 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 0 |

+-----------------------+-------+

7 rows in set (0.01 sec)

Handler_read_next 5->1 说明扫描更少的行

Use of index extensions by the optimizer is subject to the usual limits on the number of key parts in an index (16) and the maximum key length (3072 bytes).

2.2 index_condition_pushdown (ICP where条件是否额外的索引列)

存在索引 index(zipcode, lastname, firstname)

SELECT * FROM people

WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

当 index_condition_pushdown=off,只会以 WHERE zipcode='95054' 条件,回表扫描所有符合的记录再次判断是否满足 lastname LIKE '%etrunia%' AND address LIKE '%Main Street%',扫描数据量大比较耗IO

当 index_condition_pushdown=on,会以 WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%' 整个条件加入索引筛选,在索引层面过滤了大量的数据

2.3 index_merge (只针对同一个表,利用多个索引)

index_merge (default on)

Controls all Index Merge optimizations.

index_merge_intersection (default on)

Controls the Index Merge Intersection Access optimization.

index_merge_sort_union (default on)

Controls the Index Merge Sort-Union Access optimization.

index_merge_union (default on)

Controls the Index Merge Union Access optimization.

被利用到如下情况

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name

WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2

WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')

AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2

WHERE t1.key1 = 1

AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);