数据库索引设置及命中

一、数据库索引的类型

1. 主键索引

2. 唯一索引

3. 普通索引

4. 全文索引

5. 空间索引

二、索引命中

1. 主要讨论下联合索引命中的问题

数据准备：假设我们有如下图所示的数据库表study_goods_size，并且我们设置goods_id和size_id为联合索引，即：idx_goods_size。

CREATE TABLE `study_goods_size` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `goods_id` varchar(50) NOT NULL DEFAULT '' COMMENT '商品Id',
  `goods_name` varchar(50) NOT NULL DEFAULT '' COMMENT '商品名称',
  `size_id` varchar(100) NOT NULL DEFAULT '' COMMENT '尺码Id',
  `size_name` varchar(100) NOT NULL DEFAULT '' COMMENT '尺码名称',
  PRIMARY KEY (`id`),
  KEY `idx_goods_size` (`goods_id`,`size_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1;

联合索引命中总结

场景	SQL	是否命中索引	其他
场景1	explain select * from study_goods_size where goods_id = 123 and size_id = 234;	NO
场景2	explain select * from study_goods_size where goods_id = '123' and size_id = 234;	YES
场景3	explain select * from study_goods_size  where goods_id = 123 and size_id = '234';	NO
场景4	explain select * from study_goods_size  where goods_id = '123' and size_id = '234';	YES
场景5	explain select * from study_goods_size  where size_id = '234' and goods_id = '123' ;	YES
场景6	explain select * from study_goods_size  where goods_id = '123';	YES
场景7	explain select * from study_goods_size  where goods_id = 123;	NO
场景8	explain select size_id from study_goods_size  where goods_id = 123;	YES
场景9	explain select size_id from study_goods_size  where goods_id = '123';	YES
场景10	explain select size_id from study_goods_size;	YES

 

• 情况一：explain select * from study_goods_size where goods_id = 123 and size_id = 234;   

结果：不能命中索引，实测情况如下图所示：

• 情况二：explain select * from study_goods_size  where goods_id = '123' and size_id = 234;

• 情况三：explain select * from study_goods_size  where goods_id = 123 and size_id = '234';

• 情况四：explain select * from study_goods_size  where goods_id = '123' and size_id = '234';

• 情况五：explain select * from study_goods_size  where size_id = '234' and goods_id = '123' ;

• 情况六：explain select * from study_goods_size  where goods_id = '123';

• 情况七：explain select * from study_goods_size  where goods_id = 123;

• 情况八：explain select size_id from study_goods_size  where goods_id = 123;

• 情况九：explain select size_id from study_goods_size  where goods_id = '123';

• 情况十：explain select size_id from study_goods_size;

【参考资料】

1. 索引类型参考：https://www.cnblogs.com/zhenghengbin/p/11908268.html
2. 如何检测MySql是否命中索引：https://blog.youkuaiyun.com/ba_qi/article/details/88799093