每个面试题都是一个知识点,无论面不面试,都应该看看,掌握之后也是可以运用到开发中的,而且每个知识点都会衍生出很多问题,会的越多,掌握的知识点就越多,正所谓:不积跬步无以至千里,不积小流无以成江河…
今天的题目是:在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的索引情况
我们需要关注的列是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;
想要的效果已经出来,所以说上面的猜想是正确的
上面的情况是建了索引所以可以看到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最适合建索引,与测试结果一致