关于MySQL组合索引你必须要知道的知识点

背景

在面试时，我经常会问面试者对MySQL掌握的熟练程度。大多数给我的回答是，用MySQL只限于增删改查。

MySQL作为关系型数据库就是用来增删改查的。普通的数据开发、数据分析师只要掌握增删改查就够了。但不要以为增删改查很容易，如果你认为是，那只能说明你浅懂。

从MySQL表中查数据是最广泛的应用。但当继续问面试者关于索引优化、组合索引的问题时，能回答到位的就很少了。如果不了解MySQL的索引，那你就不会MySQL的查，只能说是会写SQL查询语句。

本文就带你了解MySQL中常被忽略，但很重要的一种索引——组合索引

知识点1：什么是组合索引

组合索引，是在按一定顺序的多个字段上创建的索引。多个字段的排序不同，索引的使用不同。即 index_name （a, b）和 index_name2 (b, a)是不同的索引。

组合唯一索引也遵循组合索引的使用规则

知识点2：组合索引的使用规则-最佳左匹配规则

举例说明，假设有如下一个MySQL表：

create table tb_person_info (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(255) NOT NULL COMMENT '姓名',
`age` int DEFAULT '0' COMMENT '年龄',
`birthday` date DEFAULT NULL COMMENT '出生日期',
`sex` varchar(10) DEFAULT NULL COMMENT '性别',
`id_card` char(18) DEFAULT NULL COMMENT '身份证号',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_card` (`name`, `age`, `id_card`),
KEY `idx_id_card` (`id_card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人员信息';

在该表上有创建三个索引，分别是：

• 主键索引id
• 组合索引idx_name_age_card
• 普通索引idx_id_card

下面展示一些SQL语句，并标明是否用到索引，以及用到了哪个索引

# 用到索引idx_name_age_carcd
select * from tb_person_info where name = '张伟';
# 没有用到索引
select * from tb_person_info where age = 25;
# 用到索引idx_id_card
select * from tb_person_info where id_card like '4110812022%';
# 用到索引idx_name_age_card
select * from tb_person_info where name = '张伟' and age = 25;
# 用到索引idx_name_age_card
select * from tb_person_info where name = '张伟' and id_card like '411081%';
# 用到索引idx_id_card
select * from tb_person_info where age = 25 and id_card like '411081%';
# 用到索引idx_name_age_card
select age, id_card from tb_person_info where age = 25 and id_card like '411081%';

总结以上SQL语句可以得到一下结论：

• 查询字段有组合索引之外的字段时，查询条件必须包含组合索引中的第一个字段，才会用到该索引

• 查询字段只限于组合索引内的字段时，查询条件只要有组合索引中的字段，就会用到该索引

知识点3：使用explain关键字分析SQL语句索引使用情况

在SQL语句前面加上explain关键字再执行，可以分析SQL语句的索引使用情况、可以用到的索引、要扫描的行数等。

在执行加上explain关键字的SQL语句时，并不会进行查表操作，只是对SQL语句进行分析

例如执行如下SQL语句：

explain select age, id_card from test.tb_person_info where age = 25 and id_card like '411081%';

得到结果：

最后

掌握了以上知识点，在建MySQL表时，可以根据所建表的实际查询场景对表的索引进行优化；也可以在进行查询操作时合理利用索引，从而更有效的返回结果。

另外，在建组合索引时经常会犯一个错误：重复创建索引。具体的是对组合索引的第一个字段再单独创建索引。由上述知识点2我们可以知道，组合索引的第一个字段一定是有索引的，而其他字段是没有索引的。重复创建索引不但没有必要，还会占用磁盘空间，切记不要犯这种错误！

附：

微信搜索公众号：追光的鲲 ，获取最新文章