MySQL索引与Index Condition Pushdown

转载 2013年12月06日 11:29:22

大约在两年前,我写了一篇关于MySQL索引的文章。最近有同学在文章的评论中对文章的内容提出质疑,质疑主要集中在联合索引的使用方式上。在那篇文章中,我说明联合索引是将各个索引字段做字符串连接后作为key,使用时将整体做前缀匹配。

而这名同学在这个页面找到了如下一句话:index condition pushdown is usually useful with multi-column indexes: the first component(s) is what index access is done for, the subsequent have columns that we read and check conditions on。从而认为联合索引的使用方式与文中不符。

实际上,这个页面所讲述的是在MariaDB 5.3.3(MySQL是在5.6)开始引入的一种叫做Index Condition Pushdown(以下简称ICP)的查询优化方式。由于本身不是一个层面的东西,前文中说的是Index Access,而这里是Query Optimization,所以并不构成对前文正确性的影响。在写前文时,MySQL还没有ICP,所以文中没有涉及相关内容,但考虑到新版本的MariaDB或MySQL中ICP的启用确实影响了一些查询行为的外在表现。所以决定写这篇文章详细讲述一下ICP的原理以及对索引使用方式的优化。

实验

先从一个简单的实验开始直观认识ICP的作用。

安装数据库

首先需要安装一个支持ICP的MariaDB或MySQL数据库。我使用的是MariaDB 5.5.34,如果是使用MySQL则需要5.6版本以上。

Mac环境下可以通过brew安装:

  1. brew install mairadb

其它环境下的安装请参考MariaDB官网关于下载安装的文档

导入示例数据

与前文一样,我们使用Employees Sample Database,作为示例数据库。完整示例数据库的下载地址为:https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2

将下载的压缩包解压后,会看到一系列的文件,其中employees.sql就是导入数据的命令文件。执行

  1. mysql -h[host] -u[user] -p < employees.sql

就可以完成建库、建表和load数据等一系列操作。此时数据库中会多一个叫做employees的数据库。库中的表如下:

  1. MariaDB [employees]> SHOW TABLES;
  2. +---------------------+
  3. | Tables_in_employees |
  4. +---------------------+
  5. | departments |
  6. | dept_emp |
  7. | dept_manager |
  8. | employees |
  9. | salaries |
  10. | titles |
  11. +---------------------+
  12. 6 rows in set (0.00 sec)

我们将使用employees表做实验。

建立联合索引

employees表包含雇员的基本信息,表结构如下:

  1. MariaDB [employees]> DESC employees.employees;
  2. +------------+---------------+------+-----+---------+-------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +------------+---------------+------+-----+---------+-------+
  5. | emp_no | int(11) | NO | PRI | NULL | |
  6. | birth_date | date | NO | | NULL | |
  7. | first_name | varchar(14) | NO | | NULL | |
  8. | last_name | varchar(16) | NO | | NULL | |
  9. | gender | enum('M','F') | NO | | NULL | |
  10. | hire_date | date | NO | | NULL | |
  11. +------------+---------------+------+-----+---------+-------+
  12. 6 rows in set (0.01 sec)

这个表默认只有一个主索引,因为ICP只能作用于二级索引,所以我们建立一个二级索引:

  1. ALTER TABLE employees.employees ADD INDEX first_name_last_name (first_name, last_name);

这样就建立了一个first_name和last_name的联合索引。

查询

为了明确看到查询性能,我们启用profiling并关闭query cache:

  1. SET profiling = 1;
  2. SET query_cache_type = 0;
  3. SET GLOBAL query_cache_size = 0;

然后我们看下面这个查询:

  1. MariaDB [employees]> SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';
  2. +--------+------------+------------+-----------+--------+------------+
  3. | emp_no | birth_date | first_name | last_name | gender | hire_date |
  4. +--------+------------+------------+-----------+--------+------------+
  5. | 254642 | 1959-01-17 | Mary | Botman | M | 1989-11-24 |
  6. | 471495 | 1960-09-24 | Mary | Dymetman | M | 1988-06-09 |
  7. | 211941 | 1962-08-11 | Mary | Hofman | M | 1993-12-30 |
  8. | 217707 | 1962-09-05 | Mary | Lichtman | F | 1987-11-20 |
  9. | 486361 | 1957-10-15 | Mary | Oberman | M | 1988-09-06 |
  10. | 457469 | 1959-07-15 | Mary | Weedman | M | 1996-11-21 |
  11. +--------+------------+------------+-----------+--------+------------+

根据MySQL索引的前缀匹配原则,两者对索引的使用是一致的,即只有first_name采用索引,last_name由于使用了模糊前缀,没法使用索引进行匹配。我将查询联系执行三次,结果如下:

  1. +----------+------------+---------------------------------------------------------------------------+
  2. | Query_ID | Duration | Query |
  3. +----------+------------+---------------------------------------------------------------------------+
  4. | 38 | 0.00084400 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
  5. | 39 | 0.00071800 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
  6. | 40 | 0.00089600 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
  7. +----------+------------+---------------------------------------------------------------------------+

然后我们关闭ICP:

  1. SET optimizer_switch='index_condition_pushdown=off';

在运行三次相同的查询,结果如下:

  1. +----------+------------+---------------------------------------------------------------------------+
  2. | Query_ID | Duration | Query |
  3. +----------+------------+---------------------------------------------------------------------------+
  4. | 42 | 0.00264400 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
  5. | 43 | 0.01418900 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
  6. | 44 | 0.00234200 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
  7. +----------+------------+---------------------------------------------------------------------------+

有意思的事情发生了,关闭ICP后,同样的查询,耗时是之前的三倍以上。下面我们用explain看看两者有什么区别:

  1. MariaDB [employees]> EXPLAIN SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';
  2. +------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-----------------------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-----------------------+
  5. | 1 | SIMPLE | employees | ref | first_name_last_name | first_name_last_name | 44 | const | 224 | Using index condition |
  6. +------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-----------------------+
  7. 1 row in set (0.00 sec)
  1. MariaDB [employees]> EXPLAIN SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';
  2. +------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-------------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-------------+
  5. | 1 | SIMPLE | employees | ref | first_name_last_name | first_name_last_name | 44 | const | 224 | Using where |
  6. +------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-------------+
  7. 1 row in set (0.00 sec)

前者是开启ICP,后者是关闭ICP。可以看到区别在于Extra,开启ICP时,用的是Using index condition;关闭ICP时,是Using where。

其中Using index condition就是ICP提高查询性能的关键。下一节说明ICP提高查询性能的原理。

原理

ICP的原理简单说来就是将可以利用索引筛选的where条件在存储引擎一侧进行筛选,而不是将所有index access的结果取出放在server端进行where筛选。

以上面的查询为例,在没有ICP时,首先通过索引前缀从存储引擎中读出224条first_name为Mary的记录,然后在server段用where筛选last_name的like条件;而启用ICP后,由于last_name的like筛选可以通过索引字段进行,那么存储引擎内部通过索引与where条件的对比来筛选掉不符合where条件的记录,这个过程不需要读出整条记录,同时只返回给server筛选后的6条记录,因此提高了查询性能。

下面通过图两种查询的原理详细解释。

关闭ICP

在不支持ICP的系统下,索引仅仅作为data access使用。

开启ICP

在ICP优化开启时,在存储引擎端首先用索引过滤可以过滤的where条件,然后再用索引做data access,被index condition过滤掉的数据不必读取,也不会返回server端。

注意事项

有几个关于ICP的事情要注意:

  • ICP只能用于二级索引,不能用于主索引。
  • 也不是全部where条件都可以用ICP筛选,如果某where条件的字段不在索引中,当然还是要读取整条记录做筛选,在这种情况下,仍然要到server端做where筛选。
  • ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

参考

[1] https://mariadb.com/kb/en/index-condition-pushdown/

[2] http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html


原文链接:http://blog.codinglabs.org/articles/index-condition-pushdown.html

MySQL中Index与Key的区别

看似有差不多的作用,加了Key的表与建立了Index的表,都可以进行快速的数据查询。 他们之间的区别在于处于不同的层面上。 Key即键值,是关系模型理论中的一部份,比如有主键(Primary Key...
  • ljabclj
  • ljabclj
  • 2015年03月24日 09:07
  • 3792

Mysql索引详解及优化(key和index区别)

MySQL索引的概念     索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据...
  • oChangWen
  • oChangWen
  • 2017年01月03日 17:21
  • 8582

innodb的多版本控制

inndo也是通过跟oracle类似的回滚段来实现多版本,内部实现中,innodb会添加3个字段到数据库中的每个记录上,一个6byte的DB_TRX_ID表示最后插入或更新的行的事务标识符,删除在内部...
  • aoerqileng
  • aoerqileng
  • 2016年05月07日 13:36
  • 2142

MySQL5.6之Index Condition Pushdown(ICP,索引条件下推)

ICP(index condition pushdown)是mysql利用索引(二级索引)元组和筛字段在索引中的where条件从表中提取数据记录的一种优化操作。ICP的思想是:存储引擎在访问索引的...
  • wang1518037965
  • wang1518037965
  • 2015年06月18日 17:55
  • 319

MySQL5.6新特性之Index Condition Pushdown

一 概念介绍     Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。 a 当关闭ICP时,ind...
  • xtjsxtj
  • xtjsxtj
  • 2015年06月01日 11:32
  • 747

MYSQL INDEX CONDITION PUSHDOWN

转载自大牛何登成blog(http://hedengcheng.com/?p=577) 1        问题描述 一条SQL,在数据库中是如何执行的呢?相信很多人都会...
  • DBABruce
  • DBABruce
  • 2016年02月18日 17:41
  • 248

Index Condition Pushdown Optimization

MySQL 5.6 Reference Manual  /  ...  /  Index Condition Pushdown Optimization 8.2.1.6 ...
  • shaochenshuo
  • shaochenshuo
  • 2016年06月24日 17:28
  • 243

浅析index condition pushdown

另一篇文章讲叙了 MRR和BKA  什么是indexcondition pushdown(ICP)? 在数据库中pushdown表示某些操作“下推”,也就是某些操作提前执行,在生成执行计划时某些操...
  • zbszhangbosen
  • zbszhangbosen
  • 2012年04月15日 18:46
  • 4447

MySQL 优化之 index merge(索引合并)

深入理解 index merge 是使用索引进行优化的重要基础之一。理解了 index merge 技术,我们才知道应该如何在表上建立索引。 1. 为什么会有index merge 我们的...
  • caomiao2006
  • caomiao2006
  • 2016年08月07日 22:24
  • 723

在mysql中运用全文索引(FULLTEXT index)

在MYSQL中使用全文索引(FULLTEXTindex) MYSQL的一个很有用的特性是使用全文索引(FULLTEXTindex)查找文本的能力.目前只有使用MyISAM类型表的时候有效(MyISAM...
  • u014313075
  • u014313075
  • 2016年08月23日 12:14
  • 545
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL索引与Index Condition Pushdown
举报原因:
原因补充:

(最多只允许输入30个字)