explain 工具分析mysql索引

首先，表信息如下：

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';