MySQL 索引应用及命中索引分析。

在MySQL中, 索引有两种分类方式:逻辑分类和物理分类。

按照逻辑分类,索引可分为:

主键索引:一张表只能有一个主键索引,不允许重复、不允许为 NULL;

唯一索引:数据列不允许重复,允许为 NULL 值,一张表可有多个唯一索引,但是一个唯一索引只能包含一列,比如身份证号码、卡号等都可以作为唯一索引;

普通索引:一张表可以创建多个普通索引,一个普通索引可以包含多个字段,允许数据重复,允许 NULL 值插入;

全文索引:让搜索关键词更高效的一种索引。

按照物理分类,索引可分为:

聚集索引:一般是表中的主键索引,如果表中没有显示指定主键,则会选择表中的第一个不允许为 NULL 的唯一索引,如果还是没有的话,就采用 Innodb 存储引擎为每行数据内置的 6 字节 ROWID 作为聚集索引。每张表只有一个聚集索引,因为聚集索引的键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引在精确查找和范围查找方面有良好的性能表现(相比于普通索引和全表扫描),聚集索引就显得弥足珍贵,聚集索引选择还是要慎重的(一般不会让没有语义的自增 id 充当聚集索引);

非聚集索引:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同(非主键的那一列),一个表中可以拥有多个非聚集索引。

在目前用的最多的mysql的InnoDB存储引擎中,是使用B+Tree索引方法来进行索引建立的。

一、索引的优缺点

优点:

1、索引能够提高数据检索的效率,降低数据库的IO成本。

2、通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性,创建唯一索引

3、在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间

4、加速两个表之间的连接,一般是在外键上创建索引

缺点:

1、创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加

2、索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大

3、当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度

二、索引何时应该使用

需创建索引的情况:

1.主键,自动建立唯一索引
2.频繁作为查询的条件的字段
3.查询中与其他表关联的字段存在外键关系
4.查询中排序的字段,排序字段若通过索引去访问将大大提高排序的速度
5.查询中统计或者分组字段

避免创建索引的情况:

1.数据唯一性差的字段不要使用索引
比如性别,只有两种可能数据。意味着索引的二叉树级别少,多是平级。这样的二叉树查找无异于全表扫描。

2.频繁更新的字段不要使用索引
比如登录次数,频繁变化导致索引也频繁变化,增大数据库工作量,降低效率。

3.字段不在where语句出现时不要添加索引
只有在where语句出现,mysql才会去使用索引

4.数据量少的表不要使用索引
使用了改善也不大

三、哪些sql能命中索引

1.前导模糊查询不能使用索引,如name like ‘%nowjava’

2、Union、in、or可以命中索引,建议使用in。

3、负条件查询不能使用索引,可以优化为in查询,其中负条件有!=、<>、not in、not exists、not like等

4、联合索引最左前缀原则,又叫最左侧查询,如果在(a,b,c)三个字段上建立联合索引,那么它能够加快a|(a,b)|(a,b,c)三组的查询速度。

5、建立联合查询时,区分度最高的字段在最左边

6、如果建立了(a,b)联合索引,就不必再单独建立a索引。同理,如果建立了(a,b,c)索引就不必再建立a,(a,b)索引

7、存在非等号和等号混合判断条件时,在建索引时,要把等号条件的列前置

8、范围列可以用到索引,但是范围列后面的列无法用到索引。

索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。范围条件有:<、<=、>、>=、between等。

9、把计算放到业务层而不是数据库层。在字段上计算不能命中索引,

10、强制类型转换会全表扫描,

如果phone字段是varcher类型,则下面的SQL不能命中索引。Select * from user where phone=1380000000

11、更新十分频繁、数据区分度不高的字段上不宜建立索引。

更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能。

“性别”这种区分度不太大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似。

一般区分度在80%以上就可以建立索引。区分度可以使用count(distinct(列名))/count(*)来计算。

12、利用覆盖索引来进行查询操作,避免回表。

被查询的列,数据能从索引中取得,而不是通过定位符row-locator再到row上获取,即“被查询列要被所建的索引覆盖”,这能够加速度查询。

13、建立索引的列不能为null,使用not null约束及默认值

14、利用延迟关联或者子查询优化超多分页场景,

展开阅读全文

本文系作者在时代Java发表,未经许可,不得转载。

如有侵权,请联系nowjava@qq.com删除。

编辑于

关注时代Java

关注时代Java