探讨索引设计和优化原则之前,先给大家熟悉一下索引类型:
PRIMARY KEY
:它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。UNIQUE
:唯一索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
创建命令:ALTER TABLE table_name ADD UNIQUE (column);
INDEX
:最基本的索引,它没有任何限制。
创建命令:ALTER TABLE table_name ADD INDEX index_name (column);
INDEX
:组合索引,即一个索引包含多个列。多用于避免回表查询。
创建命令:ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);
FULLTEXT
:全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。
创建命令:ALTER TABLE table_name ADD FULLTEXT (column);
读到就是赚到,溪源这里再赠送一条删除索引命令:索引一经创建不能修改,如果要修改索引,只能删除重建。
删除索引命令:DROP INDEX index_name ON table_name;
为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引;溪源给大家整理了以下基本原则:
where
关键词后面的字段适合设置为索引列,或者连接子句
中指定的列也是可以设置为索引列;唯一性的列
,设置索引效果是最佳的;而对于具有多个重复值
的列,其索引效果是最差的。
因此设置索引时,大家需要考虑该列中值的分布情况;(大家注意:此处说的字段值的基数越小【即接近于除自身之外无其他重复值】,越适合做索引列,但这里不是指唯一性索引,不要陷入深深的误解哦~)
扩展点:区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1;
简单举例说明:
如果将存放年龄字段列设置为索引列,由于各个年龄的值不同,值的区分度较高
,可以考虑作为索引列;
而对于性别列而言:即男,女,未知;数据量基本上没有很大差别,便不适合作为索引列;备注
列字段VARCHAR(200),如果该列设置为索引列,查询效率不很高,因为索引字段长度过大,索引节点树高增加,I/O次数也会增加。因此,对于长子字符考虑使用前缀索引。将备注
字段值得前10个字符设置为索引,就会节省索引空间,提高效率。对于长时间不再使用或者很少使用的索引要进行删除操作。
上面说完索引的设计原则,那么我们下面探讨一下索引的优化原则吧!
最左匹配原则并不是指查询条件的顺序,而是指查询条件中是否包含索引最左列字段;
CREATE TABLE `member` (
`member_id` INT NOT NULL AUTO_INCREMENT COMMENT '会员ID',
`member_name` VARCHAR(45) NOT NULL COMMENT '会员名字',
`age` INT(3) NULL,
`sex` VARCHAR(3) NULL,
`address` VARCHAR(45) NULL COMMENT '地址',
`status` VARCHAR(3) NULL COMMENT '状态:0(失效),1(有效)',
`remark` VARCHAR(45) NULL COMMENT '备注',
PRIMARY KEY (`member_id`),
UNIQUE INDEX `member_name_UNIQUE` (`member_name` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = '会员表';
上面我们成功建立会员表,使用show index from member;
命令查看当前表的索引列;
性别
字段设置为索引列,验证是否能够命中索引;
-- 性别列设置普通索引
ALTER TABLE member ADD INDEX sex_index (sex);
验证是否设置成功:
溪源此时表中仅有6条数据时,设置性别作为索引列,查询会命中索引;
可以从结果中看到已经命中索引列
sex_index
删除,重新建立age_index
索引-- 删除性别索引列
DROP INDEX sex_index ON member;
-- 年龄列设置普通索引
ALTER TABLE member ADD INDEX age_index (age);
show index from member;
以下这条SQL会命中索引吗???
答案肯定是:不会 解密时刻:
细心的伙伴可能会发现,溪源故意将status
字段设置成VARCHAR类型,到了显露目的的时候啦,这里会将age索引列一起谈论是目的的,哈哈~
首先使用命令将status字段设置成普通索引ALTER TABLE member ADD INDEX status_index (status);
那么反过来,如果我们将status字段,查询时设置成非VARCAHR类型,会命中索引吗???
对比结果很明显啦,为了加深大家的理解和好奇心,溪源这里暂时不抛出答案,有兴趣的小伙伴百度一下哦,欢迎评论去留言哦~
相信到这里大家已经捞到了不少东西,趁着大家高涨的热情,继续分享联合索引~
为了避免索引数量过多,下面溪源将上面建立的age_index\status_index
全部删除后,我们建立三个字段的联合索引;
ALTER TABLE member ADD INDEX age_sex_status_index (age,sex,status);
explain select * from member where age=21 and sex='男' and status = 1; explain select * from member where age=21 and status = 1 and sex='男'; explain select * from member where status = 1 and age=21 and sex='男'; explain select * from member where age=21 and status = 1;
以上SQL语句均会命中索引,因为底层MySQL提供语句优化器,优先使用索引。
下面再验证一条语句:
通过以上SQL语句验证溪源上面所整理的原则,保证查询条件中存在最左索引即可,实践是检验真理的唯一标准,只有动手实践后,才能够存在话语权,空谈理论不行啊。
最后,溪源总结一下本篇文章没有涉及到的SQL优化原则,后面溪源有时间会持续更新此文章;
参考资料: MySQL索引优化原则