第三个模块 MySQL 5.7之联合（复合）索引实践
索引:是当你的业务完成后，跟据查询条件来建立的。当你的数据量大（一般是10万条数据）了之后，我们会再把普通索引删除，使用自建索引表。因为数据量大的时候你要批量修改（索引表也会修改）会变的非常的慢！
加索引的时候，先建议使用单列索引一个一个加！然后再改进使用联合索引！
本文是针对mysql 5.7联合（复合）索引进行验证测试!
版本：mysql 5.7
测试结果日期：2017-01-02
表结构：
mysql> show create table m_user\G;
*************************** 1. row ***************************
Table: m_user
Create Table: CREATE TABLE `m_user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` char(32) NOT NULL,
`age` tinyint(4) NOT NULL,
`school` char(128) NOT NULL,
`status` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `name` (`name`,`age`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
联合索引字段：
KEY name (name,age,status)
| 序号 | 字段名 | 类型 |
|---|---|---|
| 1 | name | char |
| 2 | age | tinyint |
| 3 | status | tinyint |
结果：
-
[1 3 命中]
select * from m_user where name='feng1' and status=1 -
[1 3 order by 3 命中]
select * from m_user where name='feng1' and status=1 order by status desc -
[2 3 不命中]
select * from m_user where age=10 and status=1 -
[1 in 命中]
select * from m_user where name in ('feng1') and age<10
select * from m_user where name in ('feng1') and age<10 order by school -
[1 between 不命中]
select * from m_user where name between 'feng1' and 'feng3'
select * from m_user where name between 'feng1' and 'feng3' order by school desc -
[1 <> 不命中]
select * from m_user where name<>'feng1'
select * from m_user where name<>'feng1' and age<10
select * from m_user where name<>'feng1' and age<10 order by school desc -
[1 < 或 <= 命中]
select * from m_user where name < 'feng1'
select * from m_user where name <= 'feng1'
select * from m_user where name <= 'feng1' and age<10
select * from m_user where name <= 'feng1' and age<10 order by school desc -
[1 > 或 >= 不命中]
select * from m_user where name>'feng1'
select * from m_user where name>='feng1' -
[无where条件 直接order by 不命中]
select * from m_user order by name desc
测试过程
1.[1 3 命中]
mysql> desc select * from m_user where name='feng1' and status=1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m_user
partitions: NULL
type: ref
possible_keys: name
key: name
key_len: 96
ref: const
rows: 1
filtered: 10.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
2.[1 3 order by 3 命中]
mysql> desc select * from m_user where name='feng1' and status=1 order by status desc \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m_user
partitions: NULL
type: ref
possible_keys: name
key: name
key_len: 96
ref: const
rows: 1
filtered: 10.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
3.[2 3 不命中]
mysql> desc select * from m_user where age=10 and status=1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1001
filtered: 1.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
4.[1 in 命中]
mysql> desc select * from m_user where name in ('feng1') and age<10 order by school
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m_user
partitions: NULL
type: range
possible_keys: name
key: name
key_len: 97
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)
5.[1 between 不命中]
mysql> desc select * from m_user where name between 'feng1' and 'feng3' order by school desc \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m_user
partitions: NULL
type: ALL
possible_keys: name
key: NULL
key_len: NULL
ref: NULL
rows: 1001
filtered: 22.38
Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)
6.[1 <> 不命中]
mysql> desc select * from m_user where name<>'feng1' and age<10 order by school desc \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m_user
partitions: NULL
type: ALL
possible_keys: name
key: NULL
key_len: NULL
ref: NULL
rows: 1001
filtered: 33.30
Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)
--------------------------------------------------------------
mysql> desc select * from m_user where name<>'feng1'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m_user
partitions: NULL
type: ALL
possible_keys: name
key: NULL
key_len: NULL
ref: NULL
rows: 1001
filtered: 99.90
Extra: Using where
1 row in set, 1 warning (0.00 sec)
7.[1 < 或 <= 命中]
mysql> desc select * from m_user where name < 'feng1' and age<10 order by school desc \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m_user
partitions: NULL
type: range
possible_keys: name
key: name
key_len: 96
ref: NULL
rows: 1
filtered: 33.33
Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)
8.[1 > 或 >= 不命中]
mysql> desc select * from m_user where name >= 'feng1' and age<10 order by school desc \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m_user
partitions: NULL
type: ALL
possible_keys: name
key: NULL
key_len: NULL
ref: NULL
rows: 1001
filtered: 33.30
Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)
9.[无where条件 直接order by 不命中]
mysql> explain select * from m_user order by name desc\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m_user
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.13 sec)
