联合索引最左匹配原则

建表语句：

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(30) DEFAULT NULL COMMENT '名称',
  `sex` varchar(1) DEFAULT NULL COMMENT '性别',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `card_no` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_sex_card_no` (`name`,`sex`,`card_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

设置联合索引：

顺序为(`name`,`sex`,`card_no`)分别用a,b,c代表。

-- name, sex, card_no
EXPLAIN select * from user where name = '1'; -- a ref
EXPLAIN select * from user where name = '1' and sex = '1'; -- ab ref
EXPLAIN select * from user where name = '1' and sex = '1' and card_no = '1'; -- abc ref
EXPLAIN select * from user where card_no = '1' and sex = '1' and name = '1'; -- cba ref
EXPLAIN select * from user where name = '1' and card_no = '1'; -- ac ref
EXPLAIN select * from user where card_no = '1' and name = '1'; -- ca ref
EXPLAIN select * from user where sex = '1' and card_no = '1'; -- bc ALL
EXPLAIN select * from user where sex = '1'; -- b ALL
EXPLAIN select * from user where card_no = '1'; -- c ALL
-- 最左匹配原则
-- a必须得有
-- 覆盖索引
EXPLAIN select name,sex,card_no from user where name = '1' and sex = '1' and card_no = '1';