关于 MySQL 索引的一些认识
最近由于公司的业务量增加需要给先有数据库增加索引,查询网上资料库,学习了一番,特来分享!
-
MYSQL索引类型:
- 普通索引(normal)
这是最基本的索引,它没有任何限制CREATE INDEX indexName ON mytable(username(length));
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。 - 唯一索引(unique)
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。CREATE UNIQUE INDEX indexName ON mytable(username(length))
-
全文索引(full textl)
全文搜索的索引,用于搜索很长一篇文章的时候,效果最好。用在比较短的文本,如果就一两行字的,普通索引可以。CREATE FULLTEXT INDEX indexName ON mytable(username)
总结,索引的类别由建立索引的字段内容特性来决定,通常normal最常见。
- 普通索引(normal)
-
建立索引:
前言:给字段建立索引之前一定想一下该字段的区分度,根据区分度的大小,也能大概知道在该字段上的新建索引是否有效,以及效果如何。区分度越大,索引效果越明显。区分度SQL语句:
select count(distinct(username))/count(*) from mytable;
根据该SQL可知:区分度值位于0.000~1.000之间,该值越大即该字段的区分度越大!- 建立单列索引:
可以查看该字段的区分度,根据区分度的大小,也能大概知道在该字段上的新建索引是否有效,以及效果如何。区分度越大,索引效果越明显。 -
建立多列索引:
多列索引中其实还有一个字段的先后顺序问题,一般是将区分度较高的放在前面,这样联合索引才更有效。
eg:
select * from t_base_user where name='' and status=1;
应该增加idx_name_status(name,status)
;而不是idx_status_name(status,name)
;最左前缀匹配原则:和where后面的顺序无关
eg:当我们建立的索引为
index(type,created_at,status)
。这个根据最左前缀原则其实是建立了3个索引分别为index(type)
,index(type,created_at)
,index(type,created_at,status)
。 以下三个SQL都会走index(type,created_at,status)
这个索引,
SQL1:select * from t_base_user where type=10 and created_at='2017-11-03' and status=1
,
SQL2:select * from t_base_user where type=10 and status=1 and created_at='2017-11-03'
,
SQL3:select * from t_base_user where created_at='2017-11-03' and status=1 and type=10
,因为mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。所以,当然是我们能尽量的利用到索引时的查询顺序效率最高咯,所以mysql查询优化器会最终以这种顺序进行查询执行。
- 建立单列索引:
Last::mysql会选择一个最严格(获得结果集记录数最少)的索引,意思也就是只会走一个索引
第一次写了个分享,耗时比较长不太会排版,请各位包含,如有不对请指正:bowtie:
本作品采用《CC 协议》,转载必须注明作者和本文链接
推荐文章: