索引匹配方式

关于 explain 关键字各个列描述可以阅读：
MySQL 内置的监控工具介绍及使用篇

首先创建好表结构，并设置好对应的索引

CREATE TABLE `member` (
  `id` bigint(10) not null primary key auto_increment,
  `nick_name` varchar(32) not null default '' comment '昵称',
  `real_name` varchar(32) not null default '' comment '真实姓名',
  `phone` varchar(11) not null default '' comment '手机号',
  `age` int not null default 0 comment '年龄',
  `level_id` bigint(10) not null default 1 comment '等级id',
  `level_name` varchar(32) not null default 1 comment '等级名称',
   `register_time` datetime not null default current_timestamp comment '注册时间'
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
alter table `member` add index idx_name_phone_level(nick_name,phone, level_id);

新增一部分测试数据

insert into `member`
(nick_name,real_name,phone,age,level_id,level_name) values
('January','一月','1111111101',18,1,'青铜'),
('February','二月','1111111102',18,2,'白银'),
('March','三月','1111111103',18,3,'黄金'),
('April','四月','1111111104',18,3,'黄金'),
('May','五月','1111111105',18,3,'黄金'),
('June','六月','1111111106',18,3,'黄金'),
('July','七月','1111111107',18,4,'铂金'),
('August','八月','1111111108',18,5,'钻石'),
('September','九月','1111111109',18,5,'钻石'),
('October','十月','1111111110',18,5,'钻石'),
('November','十一月','1111111111',18,6,'翡翠'),
('December','十二月','1111111112',18,7,'大师');

全值匹配

全值匹配：索引内的值都是等值查询，例如：

mysql> explain select * from `member` where phone='1111111101' and level_id=1 and nick_name='January';
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | member | NULL       | ref  | idx_phone_level_name | idx_name_phone_level | 184     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-------+

最左前缀匹配

最左前缀匹配：匹配组合索引列的部分列，例如：

mysql> explain select * from `member` where phone='1111111101'  and nick_name='January';
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys        | key                  | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | member | NULL       | ref  | idx_name_phone_level | idx_name_phone_level | 176     | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+

如上，只会匹配到 nick_name、phone 字段

列前缀匹配

列前缀匹配：匹配某一列值的开头部分，例如：

mysql> explain select * from `member` where nick_name like 'Ja%';
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | member | NULL       | range | idx_name_phone_level | idx_name_phone_level | 130     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+

虽然是模糊匹配方法，但也用到了索引，但以下这种方式是使用不到索引的

mysql> explain select * from `member` where nick_name like '%nuary';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | member | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

范围匹配

范围匹配：查询某一个范围的数据，例如：

mysql> explain select * from `member` where nick_name > 'May';
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | member | NULL       | range | idx_name_phone_level | idx_name_phone_level | 130     | NULL |    3 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+

精确匹配某一列、范围匹配另一列

精确匹配某一列、范围匹配另一列：第一列的值全值匹配，另外的列部分匹配，例如：

mysql> explain select * from `member` where nick_name = 'May' and phone > '1111111101';
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | member | NULL       | range | idx_name_phone_level | idx_name_phone_level | 176     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+

访问索引

访问索引：查询时只需要访问索引，从二级索引树可以拿到所有需要的数据，无需通过主键再次去回表查询，本质上就是覆盖索引，例如：

mysql> explain select id,phone,level_id,nick_name from `member` where phone='1111111101' and level_id=1 and nick_name='January';
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | member | NULL       | ref  | idx_name_phone_level | idx_name_phone_level | 184     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-------------+

Extra 列显示 Using index，则表达直接可以在索引取回

哈希索引

在 InnoDB、MyISAM 存储引擎中使用 B+ 树存储索引，MySQL Memory 存储引擎中显示支持哈希索引，它基于 Hash 表实现，只有精确匹配所有列的查询才有效，哈希索引自身只需存储对应的 hash 值，所以索引结构十分紧凑，才让哈希索引查找速度非常快

哈希索引使用的限制如下：

只包含了哈希值、行指针，而不存储字段值，索引不能使用索引中的值来避免读取行，每次查询必须要先匹配到哈希值，然后再读取行指针，再根据行指针去读取我们实际的数据
数据不是按照索引值顺序存储的，所以无法排序
不支持部分列查询，哈希索引是使用索引的全部内容来计算哈希值
访问哈希索引数据非常快，除非有很多的哈希冲突，当出现哈希冲突时，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找出所有符合条件的行
哈希冲突比较多时，维护的代价也会很高

当需要大量的 URL，并且通过 URL 进行搜索查找，若使用 B+ 树，存储的内容就会变的很大

select id,url from url_base where url = '';

此时，可以利用 CRC32 对 url 作哈希算法，使用以下的查询方式：

select id,url from url_base where url_crc = CRC32('');

此查询性能较高的原因 > 使用体积很小的索引来完成查找

组合索引

通常在实际生产开发情况下，会选择多列值作为索引，也就是组合索引，又称之为复合索引；在使用组合索引时，要注意最左匹配原则，当创建组合索引之后，进行列值匹配时，从左到右匹配

创建一张表，将表中 a,b,c 三列作为组合索引，注意不同查询语句的索引匹配情况，如下：

语句	索引是否匹配
where a=3	是，使用了 a
where a=3 and b=5	是，使用了 a,b
where a=3 and b=5 and c=4	是，使用了 a,b,c
where b=5、where c=4	否
where a=3 and c=4	是，使用了 a
where a=3 and b > 10 and c=7	是，使用了 a,b
where a=3 and b like ‘%xx%’ and c=7	是，使用了 a

聚簇、非聚簇索引

所谓的聚簇索引并不是单独的索引类型，而是一种数据存储方式，聚簇索引指的是数据、索引列紧凑的存储在一起；非聚簇索引指的是数据、索引分开存储

聚簇索引优点：

可以把相关数据保存在一起
数据访问更快，数据、索引保存在同一颗树中
使用覆盖索引扫描的查询可以直接使用叶子节点的主键值

聚簇索引缺点：

聚簇数据最大限度提高了 IO 密集型应用的性能，若数据全部放在内存，那么聚簇索引就没有优势
插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
更新聚簇索引列代价很高，会强制将每个被更新的行移动到新的位置（在数据表已经堆积了很多数据再加索引导致的，最好是先暂停表数据的使用，在停用的状态下添加索引）
基于聚簇索引的表在插入新行或者主键被更新导致需要移动行的时候，可能面临页分裂问题
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏或者由于页分裂导致数据存储不连续的时候

覆盖索引

一个索引包含所有需要查询的字段值，称之为覆盖索引

并非所有索引的类型都可以称之为覆盖索引，覆盖索引必须要存储索引列的值

不同的存储引擎实现覆盖索引的方式不同，不是所有的存储引擎都支持覆盖索引，Memory 不支持覆盖索引

覆盖索引优点：

索引条目通常远小于数据行大小，若只需要读取索引，那么 MySQL 就会极大减少数据的访问量
索引是按照列值顺序存储的，所以对于 IO 密集型的范围查询会比从一行一行读取数据的 IO 少得多
MyISAM 存储引擎在内存中只缓存索引，数据依赖于操作系统来缓存，因此访问数据需要进行一次系统调用，可能会导致严重的性能问题
由于 InnoDB 是聚簇索引，所以覆盖索引对 InnoDB 特别有用

当发起一个被索引覆盖的查询时，在 explain > extra 列可以看到 using index 信息，此时就使用了覆盖索引

mysql> explain select store_id,film_id from inventory;
+----+-------------+-----------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key                  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | inventory | NULL       | index | NULL          | idx_store_id_film_id | 3       | NULL | 4581 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+

在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询；不过，可以进一步的进行优化，可以使用 InnoDB 二级索引来覆盖查询

例如：actor 表使用 InnoDB 存储引擎，并在 last_name 字段有索引，虽然该索引的列不包含主键 actor_id，但仍然能够对 actor_id 作覆盖查询，二级索引叶子节点也以主键作为唯一 Key 存储了起来

mysql> explain select actor_id,last_name from actor where last_name='HOPPER';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ref  | idx_actor_last_name | idx_actor_last_name | 182     | const |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+

优化细节（important）

数据库勿做计算

数据库勿计算：当使用索引列进行查询时，尽量不要使用表达式，把计算逻辑放到代码业务层而不是在数据库层操作

mysql> explain select * from actor where actor_id=4;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from actor where actor_id+1=4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  200 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

如上，当 where 条件中包含了表达式以后，不会使用对应的索引列

尽量主键查询

尽量使用主键查询，而不是使用其他索引，因此主键查询不会触发回表查询 > explain 分析出来会是 const，效率极高

前缀索引

前缀索引：当创建的索引字符串比较长时，可以考虑使用索引前缀 > 创建索引，来提高数据检索的效率

1、有时需要索引很长的字符串，这会让索引变得很大且很慢，通常情况下，可以使用某个列开始的部分字符串，这样可以大大节约索引空间，从而提高索引效率，但这会大大降低索引的选择性；索引选择性越高则查询效率越高，因为选择性更高的索引可以让 MySQL 在查找时过滤掉更多的行

2、一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对于 Blob、Text、Varchar 类型列，必须使用前缀索引，因为 MySQL 不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以此来保证较高的选择性

举例如下，先创建好对应的表结构、数据：

# 创建数据表
create table citydemo(city varchar(50) not null);
insert into citydemo(city) select city from city;
# 重复执行 5 次，以下的 SQL 语句
insert into citydemo(city) select city from citydemo;
# 随机更新城市表中的名称
update citydemo set city=(select city from city order by rand() limit 1);

数据准备好以后，进行 SQL 测试，如下：

查询最常见的城市列表，发现每个值出现了 40 次以上

mysql> select count(*) cnt,city from citydemo group by city order by cnt desc limit 10;
+-----+-------------+
| cnt | city        |
+-----+-------------+
|  73 | London      |
|  49 | Chatsworth  |
|  48 | Ikerre      |
|  47 | Tychy       |
|  46 | Santa Rosa  |
|  45 | Alessandria |
|  45 | Akron       |
|  44 | Tartu       |
|  44 | Kuwana      |
|  44 | Tsuyama     |
+-----+-------------+

MySQL 数据结构优化与索引细节解析：打造高效数据库的优化秘笈（二）

索引匹配方式

哈希索引

组合索引

聚簇、非聚簇索引

覆盖索引

优化细节（important）

数据库勿做计算

尽量主键查询

前缀索引

热门文章

最新文章

相关课程

相关电子书

推荐镜像

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

MySQL 数据结构优化与索引细节解析：打造高效数据库的优化秘笈（二）

索引匹配方式

哈希索引

组合索引

聚簇、非聚簇索引

覆盖索引

优化细节（important）

数据库勿做计算

尽量主键查询

前缀索引

热门文章

最新文章

相关课程

相关电子书

推荐镜像