mysql 5.7联合索引_第三个模块 MySQL 5.7之多列[单]索引实践

索引:是当你的业务完成后，跟据查询条件来建立的。当你的数据量大(一般是10万条数据)了之后，我们会再把普通索引删除，使用自建索引表。因为数据量大的时候你要批量修改(索引表也会修改)会变的非常的慢！

加索引的时候，先建议使用单列索引一个一个加！然后再改进使用联合索引！

本文是针对mysql 5.7多列[单]索引进行验证测试!

版本：mysql 5.7

测试结果日期：2017-01-05

表结构：

mysql> show create table m_user2\G;

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

Table: m_user2

Create Table: CREATE TABLE `m_user2` (

`id` int(10) unsigned NOT NULL DEFAULT '0',

`name` char(32) NOT NULL,

`age` tinyint(4) NOT NULL,

`school` char(128) NOT NULL,

`status` tinyint(4) NOT NULL DEFAULT '1',

KEY `name` (`name`),

KEY `age` (`age`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

多列[单]索引字段：

KEY name (name),

KEY age (age)

序号

字段名

类型

1

name

char

2

age

tinyint

结果：

1.[1 and 2 命中最严格的一个]

select * from m_user2 where name='feng1' and age=10

2.[1 or 2 两个都命中]

select * from m_user2 where name='feng1' or age=10

3.[1 or 2 order by 1 命中]

select * from m_user2 where name='feng1' or age=10 order by name desc

4.[between 命中]

select * from m_user2 where name between 'feng1' and 'feng123' order by name desc

select * from m_user2 where age between 10 and 20 order by name desc

5.[in 命中]

select * from m_user2 where name in ('feng1','feng123') order by name desc

6.[无where条件 直接order by 不命中]

select * from m_user2 order by name desc

测试过程

1.[1 and 2 命中最严格的一个]

mysql> explain select * from m_user2 where name='feng1' and age=10\G;

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

id: 1

select_type: SIMPLE

table: m_user2

partitions: NULL

type: ref

possible_keys: name,age

key: name

key_len: 96

ref: const

rows: 1

filtered: 5.00

Extra: Using where

1 row in set, 1 warning (0.00 sec)

2.[1 or 2 两个都命中]

mysql> explain select * from m_user2 where name='feng1' or age=10\G;

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

id: 1

select_type: SIMPLE

table: m_user2

partitions: NULL

type: index_merge

possible_keys: name,age

key: name,age

key_len: 96,1

ref: NULL

rows: 17

filtered: 100.00

Extra: Using union(name,age); Using where

1 row in set, 1 warning (0.01 sec)

3.[1 or 2 order by 1 命中]

mysql> explain select * from m_user2 where name='feng1' or age=10 order by name desc\G;

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

id: 1

select_type: SIMPLE

table: m_user2

partitions: NULL

type: index_merge

possible_keys: name,age

key: name,age

key_len: 96,1

ref: NULL

rows: 17

filtered: 100.00

Extra: Using union(name,age); Using where; Using filesort

1 row in set, 1 warning (0.00 sec)

4.[between 命中]

mysql> explain select * from m_user2 where name between 'feng1' and 'feng123' order by name desc\G;

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

id: 1

select_type: SIMPLE

table: m_user2

partitions: NULL

type: range

possible_keys: name

key: name

key_len: 96

ref: NULL

rows: 29

filtered: 100.00

Extra: Using index condition

1 row in set, 1 warning (0.00 sec)

---------------------------------------------------------------------

mysql> explain select * from m_user2 where age between 10 and 20 order by name desc\G;

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

id: 1

select_type: SIMPLE

table: m_user2

partitions: NULL

type: range

possible_keys: age

key: age

key_len: 1

ref: NULL

rows: 134

filtered: 100.00

Extra: Using index condition; Using filesort

1 row in set, 1 warning (0.01 sec)

5.[in 命中]

mysql> explain select * from m_user2 where name in ('feng1','feng123') order by name desc\G;

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

id: 1

select_type: SIMPLE

table: m_user2

partitions: NULL

type: range

possible_keys: name

key: name

key_len: 96

ref: NULL

rows: 2

filtered: 100.00

Extra: Using index condition

1 row in set, 1 warning (0.01 sec)

6.[无where条件 直接order by 不命中]

mysql> explain select * from m_user2 order by name desc\G;

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

id: 1

select_type: SIMPLE

table: m_user2

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1001

filtered: 100.00

Extra: Using filesort

1 row in set, 1 warning (0.00 sec)