环境
- 操作系统
Win10 64bit - Mysql 版本
5.7.14数据库字符集utf8mb4排序规则utf8mb4_general_ci
测试数据准备
建表脚本
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`sex` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`birthdate` date NULL DEFAULT NULL,
`status` tinyint(255) NULL DEFAULT NULL,
`money` decimal(10, 2) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
写程序插入数据 529717 条，插入时内容如下：
- id 自增主键
- name 随机生成的长度为
4的字符串 - sex 插入值轮流设置为
m或者f - birthdate 插入
2003-11-05至2019-06-15之间的任意一天 - status 每100条记录写入1个值
1和99个值2 - money
1~1000000之间的任意数字，两位小数
前10条数据概览：
mysql> select * from person limit 10;
+----+------+------+------------+--------+-----------+
| id | name | sex | birthdate | status | money |
+----+------+------+------------+--------+-----------+
| 1 | gnFV | m | 2018-05-26 | 1 | 724065.50 |
| 2 | hvDT | f | 2004-02-18 | 2 | 72224.01 |
| 3 | Ajyu | m | 2003-12-30 | 2 | 383774.51 |
| 4 | Usxj | f | 2019-05-01 | 2 | 946915.34 |
| 5 | Euhs | m | 2017-12-03 | 2 | 564437.18 |
| 6 | GOWk | f | 2016-05-29 | 2 | 57537.67 |
| 7 | CoZd | m | 2007-09-15 | 2 | 421090.69 |
| 8 | eQOx | f | 2011-08-14 | 2 | 578494.77 |
| 9 | ZwCk | m | 2013-10-26 | 2 | 604131.59 |
| 10 | rxyG | f | 2010-05-04 | 2 | 790983.80 |
+----+------+------+------------+--------+-----------+
10 rows in set (0.00 sec)
测试过程
主键索引的使用
依据ID查询单条记录，速度非常快，耗时几乎为0
mysql> select * from person where id = 1000;
+------+------+------+------------+--------+-----------+
| id | name | sex | birthdate | status | money |
+------+------+------+------------+--------+-----------+
| 1000 | iYcE | f | 2006-12-10 | 2 | 253368.78 |
+------+------+------+------------+--------+-----------+
1 row in set (0.00 sec)
执行计划如下，可以看到使用了主键索引。
mysql> explain select * from person where id = 1000;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | person | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
普通索引对查询效率的提升
在没有索引的情况下，先使用 name 给定条件进行查询
mysql> select * from person where name = 'iYcE';
+------+------+------+------------+--------+-----------+
| id | name | sex | birthdate | status | money |
+------+------+------+------------+--------+-----------+
| 1000 | iYcE | f | 2006-12-10 | 2 | 253368.78 |
+------+------+------+------------+--------+-----------+
1 row in set (0.30 sec)
给 name 列加上索引
mysql> ALTER TABLE `test`.`person` ADD INDEX `index_name`(`name`) USING BTREE;
Query OK, 0 rows affected (1.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
执行同样的查询
mysql> select * from person where name = 'iYcE';
+------+------+------+------------+--------+-----------+
| id | name | sex | birthdate | status | money |
+------+------+------+------------+--------+-----------+
| 1000 | iYcE | f | 2006-12-10 | 2 | 253368.78 |
+------+------+------+------------+--------+-----------+
1 row in set (0.00 sec)
查询耗时 0.30 -> 0.00
mysql> explain select * from person where name = 'iYcE';
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | person | NULL | ref | index_name | index_name | 1023 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
看一下执行计划，确实使用到了索引。
关于执行计划的详细解释可以看一下这篇文章 www.jianshu.com/p/514aa0b13…
查询语句是否能够命中索引
先看 like 的情况
mysql> explain select * from person where name like '%YcE%';
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 528768 | 11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
mysql> explain select * from person where name like '%YcE';
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 528768 | 11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
mysql> explain select * from person where name like 'YcE%';
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
|
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | person | NULL | range | index_name | index_name | 1023 | NULL | 29 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
mysql> explain select * from person where name not like 'YcE%';
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 528768 | 88.89 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
可以看到，前模糊查询无法使用索引，而后模糊查询可以使用索引。而 not like 查询未使用索引。
mysql> explain select * from person where name in ('iYcE','wWYp');
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
|
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | person | NULL | range | index_name | index_name | 1023 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
mysql> explain select * from person where name not in ('iYcE','wWYp');
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | person | NULL | ALL | index_name | NULL | NULL | NULL | 528768 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
使用 in 查询命中了索引，而 not in 无法命中索引。
现在我们给 money 列加上单列索引，然后继续。
mysql> explain select * from person where money > 100000;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
|
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | person | NULL | range | index_money | index_money | 6 | NULL | 264384 | 100.00 | Using index condition; Using MRR |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
mysql> explain select * from person where money <= 400000;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
|
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | person | NULL | range | index_money | index_money | 6 | NULL | 264384 | 100.00 | Using index condition; Using MRR |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
mysql> explain select * from person where money > 100000 and money <= 400000;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
|
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | person | NULL | range | index_money | index_money | 6 | NULL | 264384 | 100.00 | Using index condition; Using MRR |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
mysql> explain select * from person where money between 100000 and 400000;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
|
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | person | NULL | range | index_money | index_money | 6 | NULL | 264384 | 100.00 | Using index condition; Using MRR |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
mysql> explain select * from person where money not between 100000 and 400000;
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | person | NULL | ALL | index_money | NULL | NULL | NULL | 528768 | 70.08 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
mysql> explain select * from person where money < 100000 or money > 400000;
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | person | NULL | ALL | index_money | NULL | NULL | NULL | 528768 | 70.08 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
mysql> explain select * from person where money > 100000 or money < 400000;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
|
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | person | NULL | range | index_money | index_money | 6 | NULL | 264384 | 100.00 | Using index condition; Using MRR |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
可以看到，<= >= > < between 等运算符，以及 and 连接条件都成功命中了索引，而 not between 同样无法命中索引。最后两个 or 比较有意思，前一个显示没有命中索引，后一个显示命中了索引，其实后一个实际效果相当于没有 where 条件吧，这里不太清楚为什么
mysql> explain select * from person where money + 10000 > 510000;
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 528768 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
请注意，对于字段进行运算是无法命中索引的
索引一定能够提高查询效率吗
看下面的示例，此时 sex 列未加索引
select id from person where sex = 'm'
> OK
> 时间: 0.264s
select id,name from person where sex = 'm'
> OK
> 时间: 0.328s
给 sex 列加上索引之后呢
select id from person where sex = 'm'
> OK
> 时间: 0.138s
select id,name from person where sex = 'm'
> OK
> 时间: 3.519s
纳尼？？？可以看到，sex 列加上索引之后，对于结果只包含主键 id 列的查询来说，效率有提升，但是提升并不明显，而对于查询结果除了包含主键 id 还包含 name 的查询来说，效率大幅降低了！
- 对于可能出现的数值比较少的列，如
sex列，索引带来的查询效率提升很小。就是说当表里的数据非常多的时候，每一种值对应的数据量都很多，那么这个列对数据筛选的帮助就不大。因此，对于性别、状态等值多为枚举（男/女）或（0/1）的字段来说，可以不必创建索引。 - 对于查询结果数量比较多的情况下，如此例，查询会返回表中
1/2的数据量。那么这时，使用索引的情况下会存在一个回表查询的操作（即从sex的索引结构中获取到指针，还需要回到person表中将name信息查询出来），这样的效率可能还不如直接到person表中全表扫描查询，因此耗时更长了（此部分解释为笔者个人理解，可能并不准确）。
-- status列未加索引
select id from person where status = 1
> OK
> 时间: 0.362s
select id from person where status = 2
> OK
> 时间: 0.426s
select id,`name` from person where status = 1
> OK
> 时间: 0.336s
select id,`name` from person where status = 2
> OK
> 时间: 0.524s
-- status列加索引
select id from person where status = 1
> OK
> 时间: 0.016s
select id from person where status = 2
> OK
> 时间: 0.328s
select id,`name` from person where status = 1
> OK
> 时间: 0.071s
select id,`name` from person where status = 2
> OK
> 时间: 7.438s
