Mysql索引

前言

今天一大堆事等着做,这期需求就5天时间,一个全新的功能要上,今天不算时间,要过评审,要设计表结构,此时的我正在等待拉新分支,然后疯狂输出。

刚刚看有道云笔记,发现我之前写过关于Mysql索引的文章,我都快忘记了,现在重温一下,整理到博客中。

开始

在项目中,我常听到加索引啊,走索引啊,你这样写,是全表扫,没走索引,你这索引失效了等等一大堆的,围绕的一个点都是索引,关于SQL优化,在日常中就是让这个sql去走索引查询,所以今天就来看看索引到底什么。

是什么

简单来说,索引就是让我们查询更快,能更快的找到想要的值,避免去全表扫描,从而优化查询性能。就这么简单。

但是如果说添加的索引多了,则会降低修改这个表的速度,因为当表里面新增或修改了数据,就需要去维护索引,从而导致耗时,还有索引文件也是会占用空间,如果数据量很大,会导致索引文件大小达到上限。

怎么建

上面说了,索引并不是越多越好,而是要在合适的字段上面去建立合适的索引,所以就有一些原则:

1、对经常更新的表,尽量少建索引,只对经常查询的字段去建索引。

2、对数据量小的表最好不要使用索引,因为数据比较少,可能查询全部数据的耗时和查找索引的时间还要短,所以就没有多大意义

3、在区别度不大的字段上不要建立索引,比如性别,类型。

上面说了不适合建索引的,下面在来看看哪些情况需要建索引 往往不需要自己主动建索引,一般在DBA发现性能问题出报告后,才会根据SQL去建立合适的索引

1、主键,自动建立唯一索引

2、频繁作为查询条件的字段

3、频繁和其他表关联的字段,比如外键

4、排序字段,比如create_time等,建立索引会对排序的数据有很大的提高

5、查询统计字段或分组字段

到底是什么

Mysq的InnoDB存储引擎支持B Tree索引,全文索引,Hash索引,但是InnoDB存储引擎支持的是Hash索引自适应的,InnoDB存储引擎会根据表的使用情况自动为表生成Hash索引,不能人为干预是否在一张表中生成Hash索引

这里说一下Hash索引,Hash索引底层是一张Hash表,Hash表是一种key-value存储数据的结构,所以多行数据没有任何顺序关系,所以,对于区间查询,是无法直接通过索引查询的,必须全表扫,Hash索引适合等值查询,但是无法进行范围查询,Hash索引没有办法利用索引完成排序,如果有大量重复键值的情况,Hash索引的效率会很低,因为存在Hash碰撞

索引采用平衡树的数据结构(非二叉树)也可以叫做B Tree 或 B+ Tree

聚集索引 (主键索引)

一个表只能有一个聚集索引,但是聚集索引可能会有多个列

主键索引的叶子节点存的是整行数据

1
select * from tableName where id=8

这个查询会先根据索引定位到id为8的这个叶节点,然后通过叶节点取到id为8的这一行数据

聚集索引会让查询速度上升,但是会使写入的速度下降,因为增删改都会改变平衡树各节点中的索引内容,破坏树的结构,因此每次数据改变的时候,数据库都会去重新梳理树的结构,以保证结构正确,会有不小的性能开销

非聚集索引 (其他索引)

每次给一个字段新建一个索引,字段中的数据就会被复制一份出来,用于生成索引,因此会增加表的体积,占用磁盘空间。

非聚集索引和聚集索引的区别是,通过聚集索引可以直接查询到数据,而通过 的主键值,然后通过主键值去通过聚集索引查到需要的数据

普通索引和唯一索引

普通索引和唯一索引查询的过程区别在于,普通索引当查到一条数据之后,会向右继续查询,直到碰到一个不满足的条件,而唯一索引只要查询到匹配的数据之后,不在遍历

在更新过程的区别在于唯一索引没有change buffer这个操作

change buffer 更新操作会先存到change buffer上,然后持久化到磁盘,因为唯一索引在做更新时,会先将数据页读到内存来判断是否唯一,已经读到内存中,直接更新更快,就没必要使用change buffer。

将数据从磁盘读取到内存设计的随机IO的访问,是数据库里面成本最高的操作之一,change buffer减少了随机磁盘访问,所以对更新性能的提升更明显,因为change buffer在merge的时候才是数据更新的时候,而change buffer是将记录的变更动作缓存下来,所以一个数据页在做merge的时候,change buffer记录的动作越多(更新次数越多),收益越大

索引优化

MySQL为什么会选错索引

在执行SQL的过程中,会经过优化器,数据库会考虑很多条件(回表次数,临时表等),所以会选择错的索引

这里会考虑一个重要因素就是 show index name 查看索引的技术,索引基数越大,区分度越高,优化器选择该索引的几率越大

完整索引和前缀索引

前缀索引适当使用可以节省空间,不怎么增加查询成本

在一个字符串上建立完整索引,查询过程和非聚集索引查询过程类似

前缀索引则查询到非聚集索引保存的字段值时会匹配是否和查询条件相等,多了这一步,类似like查询

前缀索引应该取字符串前几位区分度较大,如区分度不大,则可以采用倒叙存储和hash字段

覆盖索引

非聚集索引树上包含了要查询的字段,相当于已经索引已经覆盖了要查询的字段,不需要根据查询到的字段在去主键索引上回表,减少回表次数,这个称谓覆盖索引

最左前缀原则

mysql建立多利索引(联合索引)时,有最左前缀原则,即最左优先

索引下推

在联合索引内部已经判断了查询中的部分条件,减少回表次数

MySQL索引优化规则

1.前导模糊查询不能使用索引。

1
select * from doc where title like '%XX'

而非前导模糊查询则可以使用索引,如下面的 SQL 语句。

1
select * from doc where title like 'XX%'

2.union、in、or 都能够命中索引,建议使用 in。

3.负向条件查询不能使用索引,可以优化为 in 查询。

负向条件有:!=、<>、not in、not exists、not like 等。

1
select * from doc where status != 1 and status != 2

可以优化为 in 查询:

1
select * from doc where status in (0,3,4)

4.联合索引最左前缀原则(又叫最左侧查询)

等号和非等号,应该把等号放在最左侧,并且,把区分列最高的字段放在最左侧查询

5.把计算放到业务层而不是数据库层。

在字段上进行计算不能命中索引。

例如下面的 SQL 语句。

1
select * from doc where YEAR(create_time) <= '2016'

即使 date 上建立了索引,也会全表扫描,可优化为值计算,如下:

1
select * from doc where create_time <= '2016-01-01'

6.强制类型转换会全表扫描

phone 字段是 varchar 类型,则下面的 SQL 不能命中索引。

1
select * from user where phone=13800001234

可以优化为:

1
select * from user where phone='13800001234'

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

更新会改变B+树的结构,更新频繁的话会降低数据库的性能

8.建立索引的列,不允许为 null。

9.超过三个表最好不要 join。

10.如果明确知道只有一条结果返回,limit 1 能够提高效率。

-------------本文结束感谢您的阅读-------------
0%