首先,表信息如下:
CREATE TABLE `tb_user_info` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` varchar(32) NOT NULL,
`name` varchar(64) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_id_index` (`user_id`),
UNIQUE KEY `union_name_address` (`name`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=252 DEFAULT CHARSET=utf8mb4;
测试信息如下:
-- type = const;
explain select * from tb_user_info where id = 24;
-- 如果user_id不在索引位,type = ALL
-- 如果user_id为唯一索引,type = const key = uk_user_id_index
explain select * from tb_user_info where user_id = '123470';
-- where条件是联合索引的左边,则会使用到索引 type = ref key = union_name_address
explain select * from tb_user_info where `name` = '123470';
-- where条件是联合索引的右边,则用不到索引,还是全表查询 type = ALL key = NULL
explain select * from tb_user_info where address = '123470';