mysql5.7官网直译优化和索引--使用索引扩展
来源:互联网 发布:数据统计软件 编辑:程序博客网 时间:2024/06/05 06:03
8.3.9 Use of index Extensions 使用索引扩展。
innoDB自动扩展每一个二级索引通过添加主键列到索引中。考虑这样的表定义:
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;
该表定义的主键在列(i1,i2).表中也定义了一个二级索引k_d在列d上,但是innoDB内部扩展这个索引并且对待它以(d,i1,i2)的形式。
优化器会考虑主键列在被扩展的二级索引中当决定是否使用索引或者怎么样使用索引时。这能使得定制更有效的查询计划和更高的性能。
优化器能够使用二级索引关于ref,range和index_merge索引查询方法,对于松散索引查询,连接和排序,和MIN()/MAX()等的优化。
下面的例子展示了优化器是否使用了扩展的二级索引是如何影响执行计划的。假设t1中有这些行:
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');
那么考虑这样的查询:
EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'
优化器不能使用主键索引,因为主键包含列(i1,i2)并且查询没有引用i2.相反的,优化器能够使用二级索引k_d在列d上。而且执行计划依赖于是否被扩展的索引被使用。
当优化器没有考虑索引扩展,它对打索引k_d就只有(d)。EXPLAIN关于查询产生这样的结果:
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: PRIMARY,k_d
key: k_d
key_len: 4
ref: const
rows: 5
Extra: Using where; Using index
当优化器考虑索引扩展的话,它对待k_d是这样的(d,i1,i2).在这种情况下,它能使用左前缀索引(d,i1)从而形成一个更好的执行计划:
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: PRIMARY,k_d
key: k_d
key_len: 8
ref: const,const
rows: 1
Extra: Using index
在这两种情况下,key列说明优化器将会使用二级索引k_d但是EXPLAIN的输出说明通过使用被扩展的索引提高了在这些方面有表示:
1)key_len由4bytes变为8bytes,说明key查找的是d和i1,不仅仅是d
2) ref值的改变从const到const,const因为key查找的是两个键部分,而不是一个。
3)行数量从5减少到1,说明innoDB会扫描更少的行来得到结果
4)Extra值改变从Using index;Using index到Using index。这意味着行读取能够只通过索引,而不需要咨询数据表中的数据。
对于优化器使用扩展索引的不同可以看SHOW STATUS:
FLUSH TABLE t1;
FLUSH STATUS;
SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%'
先前的语句包括FLUSH TABLE和FLUSH STATUS来刷新表缓存和清除状态数。
没有索引扩展,SHOW STATUS是这样的:
+-----------------------+-------+
| 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 |
+-----------------------+-------+
通过索引扩展,SHOW STATUS产生这样的结果。Handler_read_next值从5变为1,说明更有效的使用了索引:
+-----------------------+-------+
| 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 |
+-----------------------+-------+
optimizer_switch系统变量的use_index_extensions标记值用来控制优化器是否考虑主键列到二级索引中。默认情况下,use_index_extensions是可以用的。为了检测是否索引可以可用来提高查询性能,使用这样的语句:
SET optimizer_switch = 'use_index_extensions=off';
优化器使用索引扩展但是对叙索引数量一个索引不能超过16个部分,并且最大长度不能超过3072bytes.到此关于索引的扩展就结束了,接下来我们要说明的8.3.10 Optimizer Use of Generated Column Indexes 优化使用生成的列的索引。
innoDB自动扩展每一个二级索引通过添加主键列到索引中。考虑这样的表定义:
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;
该表定义的主键在列(i1,i2).表中也定义了一个二级索引k_d在列d上,但是innoDB内部扩展这个索引并且对待它以(d,i1,i2)的形式。
优化器会考虑主键列在被扩展的二级索引中当决定是否使用索引或者怎么样使用索引时。这能使得定制更有效的查询计划和更高的性能。
优化器能够使用二级索引关于ref,range和index_merge索引查询方法,对于松散索引查询,连接和排序,和MIN()/MAX()等的优化。
下面的例子展示了优化器是否使用了扩展的二级索引是如何影响执行计划的。假设t1中有这些行:
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');
那么考虑这样的查询:
EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'
优化器不能使用主键索引,因为主键包含列(i1,i2)并且查询没有引用i2.相反的,优化器能够使用二级索引k_d在列d上。而且执行计划依赖于是否被扩展的索引被使用。
当优化器没有考虑索引扩展,它对打索引k_d就只有(d)。EXPLAIN关于查询产生这样的结果:
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: PRIMARY,k_d
key: k_d
key_len: 4
ref: const
rows: 5
Extra: Using where; Using index
当优化器考虑索引扩展的话,它对待k_d是这样的(d,i1,i2).在这种情况下,它能使用左前缀索引(d,i1)从而形成一个更好的执行计划:
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: PRIMARY,k_d
key: k_d
key_len: 8
ref: const,const
rows: 1
Extra: Using index
在这两种情况下,key列说明优化器将会使用二级索引k_d但是EXPLAIN的输出说明通过使用被扩展的索引提高了在这些方面有表示:
1)key_len由4bytes变为8bytes,说明key查找的是d和i1,不仅仅是d
2) ref值的改变从const到const,const因为key查找的是两个键部分,而不是一个。
3)行数量从5减少到1,说明innoDB会扫描更少的行来得到结果
4)Extra值改变从Using index;Using index到Using index。这意味着行读取能够只通过索引,而不需要咨询数据表中的数据。
对于优化器使用扩展索引的不同可以看SHOW STATUS:
FLUSH TABLE t1;
FLUSH STATUS;
SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%'
先前的语句包括FLUSH TABLE和FLUSH STATUS来刷新表缓存和清除状态数。
没有索引扩展,SHOW STATUS是这样的:
+-----------------------+-------+
| 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 |
+-----------------------+-------+
通过索引扩展,SHOW STATUS产生这样的结果。Handler_read_next值从5变为1,说明更有效的使用了索引:
+-----------------------+-------+
| 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 |
+-----------------------+-------+
optimizer_switch系统变量的use_index_extensions标记值用来控制优化器是否考虑主键列到二级索引中。默认情况下,use_index_extensions是可以用的。为了检测是否索引可以可用来提高查询性能,使用这样的语句:
SET optimizer_switch = 'use_index_extensions=off';
优化器使用索引扩展但是对叙索引数量一个索引不能超过16个部分,并且最大长度不能超过3072bytes.到此关于索引的扩展就结束了,接下来我们要说明的8.3.10 Optimizer Use of Generated Column Indexes 优化使用生成的列的索引。
阅读全文
0 0
- mysql5.7官网直译优化和索引--使用索引扩展
- mysql5.7官网直译优化和索引--主键优化
- mysql5.7官网直译优化和索引--mysql如何使用索引
- mysql5.7官网直译优化和索引--使用生成列的索引
- mysql5.7官网直译优化和索引--多列索引
- mysql5.7官网直译优化和索引--索引统计的收集
- mysql5.7官网直译优化和索引--B树索引和哈希索引的对比
- mysql5.7官网直译SQL语句优化--索引合并的优化
- mysql5.7官网直译SQL语句优化--索引条件压入优化
- mysql5.7官网直译SQL语句优化--排序优化
- mysql5.7官网直译SQL语句优化--分组优化
- mysql5.7官网直译数据结构优化--数据大小优化
- mysql5.7官网直译SQL语句优化--使用存在策略来优化子查询
- mysql5.7官网直译SQL语句优化--派生表和试图引用的优化
- mysql5.7官网直译锁操作优化--表锁的使用建议
- mysql5.7官网直译SQL语句优化--子查询,派生表和试图引用
- mysql5.7官网直译SQL语句优化--块式嵌套循环和批量key访问连接
- mysql5.7官网直译SQL语句优化--select语句优化
- 2017年12月8日 Web 开发基础 笔记备份
- python 时间模块: datetime
- 两种方法教你在Excel2007中自动换行
- SolrJ 复杂查询 高亮显示
- 正则表达式相关
- mysql5.7官网直译优化和索引--使用索引扩展
- MySQL数据库迁移(直接复制文件)
- LDAP-Apache Directory Studio使用(创建DC.OU及用户)
- 如何有效地报告Bug
- Linux系统上安装Java详细步骤
- 判断数字里是否含有某个数字的事情
- Java security之记不住的CodeBase
- timestamp与datetime使用
- 线程进阶