每个面试题都是一个知识点,无论面不面试,都应该看看,掌握之后也是可以运用到开发中的,而且每个知识点都会衍生出很多问题,会的越多,掌握的知识点就越多,正所谓:不积跬步无以至千里,不积小流无以成江河…

今天的题目是:在mysql中,一条sql,有多个查询条件,你会选择哪个字段作为索引,依据是什么?

sql示例是这样的:

select * from table where field_a = xx and field_b = xx and field_c = xx

思考一下,如果是你,你会选哪个字段作为索引,为啥那,如果你一点思路没有,就且听我慢慢道来…

这个回答的时候,蒙肯定是不行的,你选择field_a字段肯定要把理由说出来,如果说不出来,直接说不会也要好过瞎蒙,起码留给面试官一个不会就是不会,不瞎蒙的印象 这都不知道,还说你会mysql

解题

首先说索引,表中创建索引的目的是为提供更高效的查询,既然是提高效率,肯定是有高效的索引和低效的索引的区分,这道题其实也是说选择哪个字段作为索引会提供更高效的查询。

为了循序渐进的剖析这个问题,我创建一个测试表 test,表中插入十万条测试数据,我们模拟一下这个问题

表结构如下:

CREATE TABLE `test` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `field_a` int(11) DEFAULT NULL,
  `field_b` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COMMENT='测试表';

使用存储过程,添加测试数据:

delimiter ;;
create procedure test()
begin
    declare i int;
    set i=1;
    while(i<=100000)do
        insert into test values(i, i, i%100);
        set i=i+1;
    end while;
end;;
delimiter ;
-- 执行
call test();

添加field_a字段的数据是从1到100000,field_b字段的数据是从0到99,查一下数据,十万条数据已经添加成功了

那题目中的sql就变成了这样

select * from test where field_a = xx and field_b = xx

这时候在没创建索引的情况下我们先用执行一下explain

对于explain不熟悉的可以看下这篇文章 https://chenmingyu.top/mysql-optimize/

从type字段我们知道sql走的全表扫描,但是预计的扫描行数 rows字段只有94001条,明显是跟表中的总数据量对不上啊,没事儿,别急,放心,且先放着

我们再执行下面的sql看下表test的索引情况

show index from test

我们需要关注的列是cardinality(基数),这列表示索引中唯一值的数量的估计值,这个值越高,说明字段的区分度越高,代表着这列越适合作为索引出现,如果这个值非常小,就要考虑这个字段是否适合作为索引出现了,所以我们可以使用Cardinality列来判断索引是否具有高选择性

当然还有一种方式可以计算索引是否具有高选择性,就是Cardinality的值/table_rows的值 如果尽可能接近1,那么这个索引具有高选择性,如果非常小,那么就要考虑是否可以删除该索引了

可以使用 analyze table test 后Cardinality列与rows列显示的数值一致

那我们知道了上面的知识后,再看一下这道题,是不是我们只要比较每个字段的Cardinality值的大小就可以选取那个字段作为索引了

现在给field_a和field_b添加索引,现在表结构如下:

CREATE TABLE `test` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `field_a` int(11) DEFAULT NULL,
  `field_b` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_field_a` (`field_a`),
  KEY `idx_field_b` (`field_b`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COMMENT='测试表';

我们再看一下Cardinality列

明显idx_field_a索引要比idx_field_b索引的Cardinality值大,大胆猜测一下是不是idx_field_a索引要比idx_field_b索引的效率高?

用force index强制使用索引,我们测下:

使用explain看下

explain select * from test force index(idx_field_a) where field_a = 1 and field_b = 1;

explain select * from test force index(idx_field_b) where field_a = 1 and field_b = 1;

虽然type都是ref,但是rows的值还是差距很大的,心里不禁暗暗有些激动,猜想与事实逐渐重合…

执行sql,看下耗时

-- sql执行0.001s
select * from test force index(idx_field_a) where field_a = 1 and field_b = 1;
-- sql执行0.004s
select * from test force index(idx_field_b) where field_a = 1 and field_b = 1;

想要的效果已经出来,所以说上面的猜想是正确的img

上面的情况是建了索引所以可以看到Cardinality列的值,在没建索引的时候怎么办?

再看下Cardinality的概念:表示唯一值的数量的预估值,所以我们可以用distinct 去计算字段的唯一值数量

所以最终字段是否适合创建索引可以使用下面的sql进行判断,值等于1或趋近为1的字段最适合创建索引

-- 值为1
select count(distinct field_a)/count(*) from test
-- 值为0.001
select count(distinct field_b)/count(*) from test

显然字段field_a最适合建索引,与测试结果一致