Gluh's thoughts on the MySQL Optimizer
来源:互联网 发布:淘宝怎么上下架宝贝 编辑:程序博客网 时间:2024/05/16 12:04
среда, 19 декабря 2012 г.
InnoDB, extended secondary keys.
It's a well-know fact that InnoDB secondary keys contain both user defined columns and the primary key columns. For example, if a table has PRIMARY KEY(pk) and secondary key k1(f1), then index k1 is internally stored as k1(f1,pk).
Prior to version 5.6.9, the MySQL optimizer could only partially use these extended primary key columns: they could be used for sorting and to provide index only access. Starting from MySQL 5.6.9, the optimizer makes full use of the extended columns. This means that 'ref' access, range access, MIN/MAX optimizations, index_merge, loose index scan etc all works as if you had created the index with all primary key columns in all secondary keys. The new feature is turned on and off by optimizer switch 'use_index_extensions' and is on by default.
Consider we have following table:
CREATE TABLE t1
(
f1 INT NOT NULL DEFAULT '0',
f2 INT NOT NULL DEFAULT '0',
f3 DATE DEFAULT NULL,
PRIMARY KEY (f1, f2),
KEY k_f3 (f3)
) 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');
Lets take a look at the difference in results with "use_index_extensions=off|on":
Number of handler_read_next is decreased with use_index_extensions=on.It happens because optimizer uses 'f3, f1' pair for range access.This behaviour change is indirectly can be visible is explain:
As you can see key_len field is changed (4 with "use_index_extensions=off" and 8 with "use_index_extensions=on") which means that 'f3, f1' pair is used with enabled feature.
Another example shows the improvement when using JOIN(the same table with 12800 records):
As you can see execution of the query is roughly 2.5 better with enabled "use_index_extensions" switch.
Prior to version 5.6.9, the MySQL optimizer could only partially use these extended primary key columns: they could be used for sorting and to provide index only access. Starting from MySQL 5.6.9, the optimizer makes full use of the extended columns. This means that 'ref' access, range access, MIN/MAX optimizations, index_merge, loose index scan etc all works as if you had created the index with all primary key columns in all secondary keys. The new feature is turned on and off by optimizer switch 'use_index_extensions' and is on by default.
Consider we have following table:
CREATE TABLE t1
(
f1 INT NOT NULL DEFAULT '0',
f2 INT NOT NULL DEFAULT '0',
f3 DATE DEFAULT NULL,
PRIMARY KEY (f1, f2),
KEY k_f3 (f3)
) 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');
Lets take a look at the difference in results with "use_index_extensions=off|on":
mysql> set optimizer_switch= "use_index_extensions=off";Query OK, 0 rows affected (0.00 sec)mysql> FLUSH STATUS;Query OK, 0 rows affected (0.00 sec)mysql> SELECT COUNT(*) FROM t1 WHERE f3 = '2000-01-01' AND f1 BETWEEN 1 AND 3;+----------+| COUNT(*) |+----------+| 3 |+----------+1 row in set (0.01 sec)mysql> SHOW STATUS LIKE 'handler_read_next';+-------------------+-------+| Variable_name | Value |+-------------------+-------+| Handler_read_next | 5 |+-------------------+-------+1 row in set (0.00 sec)mysql> set optimizer_switch= "use_index_extensions=on";Query OK, 0 rows affected (0.00 sec)mysql> FLUSH STATUS;Query OK, 0 rows affected (0.00 sec)mysql> SELECT COUNT(*) FROM t1 WHERE f3 = '2000-01-01' AND f1 BETWEEN 1 AND 3;+----------+| COUNT(*) |+----------+| 3 |+----------+1 row in set (0.00 sec)mysql> SHOW STATUS LIKE 'handler_read_next';+-------------------+-------+| Variable_name | Value |+-------------------+-------+| Handler_read_next | 3 |+-------------------+-------+1 row in set (0.00 sec)
Number of handler_read_next is decreased with use_index_extensions=on.It happens because optimizer uses 'f3, f1' pair for range access.This behaviour change is indirectly can be visible is explain:
mysql> set optimizer_switch= "use_index_extensions=off";Query OK, 0 rows affected (0.00 sec)mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE f3 = '2000-01-01' AND f1 BETWEEN 1 AND 3;+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+| 1 | SIMPLE | t1 | ref | PRIMARY,k_f3 | k_f3 | 4 | const | 5 | Using where; Using index |+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+1 row in set (0.00 sec)mysql> set optimizer_switch= "use_index_extensions=on";Query OK, 0 rows affected (0.00 sec)mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE f3 = '2000-01-01' AND f1 BETWEEN 1 AND 3;+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+| 1 | SIMPLE | t1 | range | PRIMARY,k_f3 | k_f3 | 8 | NULL | 3 | Using where; Using index |+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
As you can see key_len field is changed (4 with "use_index_extensions=off" and 8 with "use_index_extensions=on") which means that 'f3, f1' pair is used with enabled feature.
Another example shows the improvement when using JOIN(the same table with 12800 records):
mysql> SELECT COUNT(*) FROM t1;+----------+| COUNT(*) |+----------+| 12800 |+----------+1 row in set (0.07 sec)mysql> set optimizer_switch= "use_index_extensions=off";Query OK, 0 rows affected (0.00 sec)mysql> SELECT count(*) FROM t1 AS t1 JOIN t1 AS t2 ON t2.f1 = t1.f1WHERE t1.f3 = '2000-01-01' AND t2.f3 = '2002-01-01';+----------+| count(*) |+----------+| 10240 |+----------+1 row in set (0.47 sec)set optimizer_switch= "use_index_extensions=on";Query OK, 0 rows affected (0.00 sec)mysql> SELECT count(*) FROM t1 AS t1 JOIN t1 AS t2 ON t2.f1 = t1.f1WHERE t1.f3 = '2000-01-01' AND t2.f3 = '2002-01-01';+----------+| count(*) |+----------+| 10240 |+----------+1 row in set (0.18 sec)
As you can see execution of the query is roughly 2.5 better with enabled "use_index_extensions" switch.
- Gluh's thoughts on the MySQL Optimizer
- Thoughts On <To The Moon>
- Mooly's Corner: Thoughts on ARs
- Some Thoughts on the Common Toad
- Some of my thoughts on life's crossroads.
- Stonebraker教授的文章:Thoughts on the VLDB conference
- Thoughts on Java
- Thoughts on directshow
- Thoughts on Redis
- Some thoughts on love
- Thoughts on Flash
- 最短路径搜索。(Amit's Thoughts on Path-Finding and A-Star)
- Second Thoughts on James Burnham
- Thoughts on an OpenDocument Toolkit
- Random thoughts on code review
- Thoughts on love and Happiness
- Thoughts on Flash - Steve Jobs
- 教主的"Thoughts on Flash"
- Android学习15——RSS阅读器
- 代理模式(PROXY)
- Unity中网格合并示例研究
- Case Study: Activiti & JSF
- Tomcat调节
- Gluh's thoughts on the MySQL Optimizer
- java随机函数用法Random
- source insight打开工程挂掉问题和彻底删除source insigh的解决办法
- php检测上传excel文件类型
- Android4.0.3修改启动动画和开机声音
- The given object has a null identifier解决之法
- hash数据库概述
- 在myeclipse上部署没有java源文件的程序
- Android和ios哪个前景更好