关于 MySQL 索引的一些认识

最近由于公司的业务量增加需要给先有数据库增加索引,查询网上资料库,学习了一番,特来分享!

  • MYSQL索引类型:

    1. 普通索引(normal)
      这是最基本的索引,它没有任何限制
      CREATE INDEX indexName ON mytable(username(length));
      如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。
    2. 唯一索引(unique)
      它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
      CREATE UNIQUE INDEX indexName ON mytable(username(length))
    3. 全文索引(full textl)
      全文搜索的索引,用于搜索很长一篇文章的时候,效果最好。用在比较短的文本,如果就一两行字的,普通索引可以。
      CREATE FULLTEXT INDEX indexName ON mytable(username)

      总结,索引的类别由建立索引的字段内容特性来决定,通常normal最常见。

  • 建立索引:

    前言:给字段建立索引之前一定想一下该字段的区分度,根据区分度的大小,也能大概知道在该字段上的新建索引是否有效,以及效果如何。区分度越大,索引效果越明显。区分度SQL语句:select count(distinct(username))/count(*) from mytable;根据该SQL可知:区分度值位于0.000~1.000之间,该值越大即该字段的区分度越大!

    1. 建立单列索引:
      可以查看该字段的区分度,根据区分度的大小,也能大概知道在该字段上的新建索引是否有效,以及效果如何。区分度越大,索引效果越明显。
    2. 建立多列索引:

      多列索引中其实还有一个字段的先后顺序问题,一般是将区分度较高的放在前面,这样联合索引才更有效。

      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: