mysql索引下推原理_mysql的索引下推理解和实践

对于mysql建表稍有点经验的开发人员都会为后续的where查询条件提早考虑建立索引。mysql

这里说的是在使用索引查询时有关索引下推的有关知识点。sql

综合前人的经验结果：索引下推是数据库检索数据过程当中为减小回表次数而作的优化。docker

判断是否须要回表的是由mysql存储引擎控制，默认从mysql5.6版本开始支持。数据库

下面用docker分别建立基于mysql5.5和mysql5.6的容器，表结构保持一致(docker建立mysql容器不作演示)。优化

首先看mysql5.5：spa

mysql> select version();

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

| version() |

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

| 5.5.62 |

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

1 row in set (0.00 sec)

mysql> show create table testhh\G;

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

Table: testhh

Create Table: CREATE TABLE `testhh` (

`id` int(10) unsigned NOT NULL,

`age` int(10) unsigned DEFAULT '0',

`name` char(10) NOT NULL DEFAULT '',

`height` int(10) NOT NULL DEFAULT '0',

`name2` char(10) NOT NULL DEFAULT '',

`height2` int(10) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `age_index` (`age`) USING HASH,

KEY `un` (`name`,`height`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> explain select * from testhh where name like 'a%' and height = 100\G;

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

id: 1

select_type: SIMPLE

table: testhh

type: range

possible_keys: un

key: un

key_len: 14

ref: NULL

rows: 1

Extra: Using where

1 row in set (0.00 sec)

ERROR:

No query specified

上面可见explain的extra字段结果时Using where，表示优化器须要经过索引回表查询数据。code

再看mysql5.6:blog

mysql> selectversion();+-----------+

| version() |

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

| 5.6.50 |

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

1 row in set (0.00sec)

mysql>show create table ua\G;*************************** 1. row ***************************Table: ua

Create Table: CREATE TABLE `ua` (

`id`int(10) NOT NULL AUTO_INCREMENT,

`name`char(10) NOT NULL DEFAULT '',

`height`int(10) NOT NULL DEFAULT '0',

`name2`char(10) NOT NULL DEFAULT '',

`height2`int(10) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `nh` (`name`,`height`)

) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00sec)

ERROR:

No query specified

mysql> explain select * from ua where name like 'a%' and height=10\G;*************************** 1. row ***************************id:1select_type: SIMPLE

table: ua

type: range

possible_keys: nh

key: nh

key_len:14

ref: NULL

rows:1Extra: Using index condition1 row in set (0.00sec)

ERROR:

No query specified

explain的extra字段是Using index condition，表示会先经过条件过滤索引，再经过过滤后的索引查询符合索引条件的数据。索引