文档章节

MySQL专题—— 从认识索引到理解索引【索引优化】

架构师springboot
 架构师springboot
发布于 11/18 16:56
字数 2424
阅读 9
收藏 3

认识索引

认识索引是什么东西非常关键,一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系,为了方便查找书中的内容,通过对内容建立索引形成目录。因此,首先你要明白的一点就是,索引它也是一个文件,它是要占据物理空间的。

比如对于MyISAM存储引擎来说:

.frm后缀的文件存储的是表结构。

.myd后缀的文件存储的是表数据。

.myi后缀的文件存储的就是索引文件。

  如下图所示:

对于InnoDB存储引擎来说:

.frm

.ibd后缀的文件存放索引文件和数据(需要开启innodb_file_per_table参数)

  如下图所示:

因此,当你对一张表建立索引时,索引文件的大小也会改变,当你数据表中的数据因为增删改变化时,索引文件也会变化的,只不过MySQL会自动维护索引,这个过程不需要你介入,这也是为什么不恰当的索引会影响MySQL性能的原因。

总结:

1. 索引是按照特定的数据结构把数据表中的数据放在索引文件中,以便于快速查找;

2. 索引存在于磁盘中,会占据物理空间。

索引的类型

B-Tree 索引

  以 B-Tree 为结构的索引是最常见的索引类型,比如 InnoDB 和 MyISAM 都是以 B-Tree 为索引结构的索引,事实上是以 B+ Tree 为索引结构,B-Tree 和 B+Tree 区别在于,B+ Tree 在叶子节点上增加了顺序访问指针,方便叶子节点的范围遍历。这里主要介绍一下 InnoDB 和 MyISAM。

InnoDB

  InnoDB 支持聚簇索引,聚簇索引和非聚簇索引严格来说不是一种索引,而是一种数据存储方式,这个名字跟它本身的存储方式有关系,“聚簇“表示数据行和相邻的键值存储在一起,简单的说,就是叶子节点中存储的实际是真实的数据。InnoDB 通过主键聚集数据,所以一个表只能有一个聚簇索引,且必须有主键,如果没有定义主键,且不存在非空索引可以代替,InnoDB 会隐式定义一个主键作为聚簇索引。

  聚簇索引的二级索引存储的不是指向行的物理位置的指针,而是行的主键值,所以如果通过二级索引查找行,需要找到二级索引的叶子结点获得对应的主键值,然后再去查找对应的行。对于 InnoDB,自适应哈希索引可以减少这样的重复工作。

  InnoDB 使用的是行锁,所以支持事务,而 MyISAM 使用的是表锁,不支持事务。

适用范围

  B-Tree 索引适用于区间查询,因为 B-Tree 存储后的叶子节点本身就是有序的,并且 B+ Tree 结构还增加了叶子节点的连续顺序指针,对于区间查询来说就更加方便了。

哈希索引

  哈希索引是基于哈希表实现的,只有精确匹配索引所有列的查询才有效。方法是,对所有的索引列计算一个 hash code,hash code 作为索引,在哈希表中保存指向每个数据行的指针。

优点

  索引本身只存储 hash code,所以结构很紧凑,并且查找速度很快

  限制

  索引中的 hash code 是顺序存储的,但是 hash code 对应的数据并不是顺序的,所以无法用于排序

  不支持部分索引列匹配查找,因为哈希索引是使用索引列的全部内容来计算 hash code

  只支持等值比较,不支持范围查询

  如果哈希冲突严重时,必须遍历链表中所有行指针

  哈希冲突严重的话,索引维护操作的代价也很高

  InnoDB 的自适应哈希索引

  首先,请注意,自适应哈希索引对于用户来说是无感知的,这是一个完全自动、内部的行为,用户无法控制或者配置,但是可以关闭。

  当 InnoDB 注意到某个索引值被使用的非常频繁时,它会在内存中基于 B-Tree 索引之上再创建一个哈希索引,这样 B-Tree 也可以具有哈希索引的一些优点,比如快速的哈希查找。

当然如果存储引擎不支持哈希索引,用户也可以自定义哈希索引,这样性能会比较高,缺陷是需要自己维护哈希值,如果采用这种方法,不要使用 SHA1() 和 MD5() 作为哈希函数,因为这两个是强加密函数,设计目标是最大限度消除冲突,生成的 hash code 是一个非常长的字符串,浪费大量的空间,哈希索引中对于索引的冲突要求没有那么高。

索引的优点

  使用索引可以减少服务器需要扫描的数据量

  使用索引可以帮助服务器避免排序和临时表

  使用索引可以将随机 I/O 变为顺序 I/O

  但是不是所有情况下,索引都是最好的解决方案,对于非常小的表来说,大部分情况下简单的全表扫描更高效,对于中到大型表,索引就比较有效,对于特大型的表来说,分区会更加有效。

常见优化方法

联合索引最左前缀原则

  复合索引遵守「最左前缀」原则,查询条件中,使用了复合索引前面的字段,索引才会被使用,如果不是按照索引的最左列开始查找,则无法使用索引。

  比如在(a,b,c)三个字段上建立联合索引,那么它能够加快a|(a,b)|(a,b,c)三组查询的速度,而不能加快b|(b,a)这种查询顺序。

  另外,建联合索引的时候,区分度最高的字段在最左边。

不要在列上使用函数和进行运算

  不要在列上使用函数,这将导致索引失效而进行全表扫描。

  例如下面的 SQL 语句:

select * from artile where YEAR(create_time) <= '2018'; 

  即使 date 上建立了索引,也会全表扫描,可以把计算放到业务层,这样做不仅可以节省数据库的 CPU,还可以起到查询缓存优化效果。

负向条件查询不能使用索引

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

select * from artile where status != 1 and status != 2; 

  可以使用in进行优化:

select * from artile where status in (0,3) 

使用覆盖索引

  所谓覆盖索引,是指被查询的列,数据能从索引中取得,而不用通过行定位符再到数据表上获取,能够极大的提高性能。

  可以定义一个让索引包含的额外的列,即使这个列对于索引而言是无用的。

避免强制类型转换

  当查询条件左右两侧类型不匹配的时候会发生强制转换,强制转换可能导致索引失效而进行全表扫描。

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

select * from user where phone=12345678901; 

  可以优化为:

select * from user where phone='12345678901'; 

范围列可以用到索引

  范围条件有:<、<=、>、>=、between等。

  范围列可以用到索引,但是范围列后面的列无法用到索引,索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。

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

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

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

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

索引列不允许为null

  单列索引不存null值,复合索引不存全为null的值,如果列允许为 null,可能会得到不符合预期的结果集。

避免使用or来连接条件

  应该尽量避免在 where 子句中使用 or 来连接条件,因为这会导致索引失效而进行全表扫描,虽然新版的MySQL能够命中索引,但查询优化耗费的 CPU比in多。

模糊查询

  前导模糊查询不能使用索引,非前导查询可以。

加Java架构师群获取Java工程化、高性能及分布式、高性能、深入浅出。高架构。性能调优、Spring,MyBatis,Netty源码分析和大数据等多个知识点高级进阶干货的直播免费学习权限 都是大牛带飞 让你少走很多的弯路的 群..号是:855801563 对了 小白勿进 最好是有开发经验的哦~

© 著作权归作者所有

共有 人打赏支持
架构师springboot
粉丝 11
博文 91
码字总数 186418
作品 0
东城
私信 提问
MySQL专题—— 从认识索引到理解索引【索引优化】

认识索引 认识索引是什么东西非常关键,一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系,为了方便查找书中的内容,通过对内容建立索引形成目录。因此,首先你要明白的一点就是,...

架构师springboot
11/18
0
0
「mysql优化专题」程序员面试都用得上的索引优化手册(5)【面试重点】

本专题讲到索引查询优化,恭喜你,已经达到mysql优化的中级水平。这篇我们要讲的是mysql优化中重点中的重点——索引优化。面试官百分百必问 目录 多关于索引,分为以下几点来讲解: 一、索引...

JAVA高级架构v
09/26
0
0
「mysql优化专题」90%程序员面试都用得上的索引优化手册(5)【面试重点】

本专题讲到索引查询优化,恭喜你,已经达到mysql优化的中级水平。这篇我们要讲的是mysql优化中重点中的重点——索引优化。面试官百分百必问 目录 多关于索引,分为以下几点来讲解: 一、索引...

java进阶架构师
2017/12/03
0
0
MySQL专题——从认识索引到理解索引「索引优化」

认识索引 认识索引是什么东西非常关键,一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系,为了方便查找书中的内容,通过对内容建立索引形成目录。因此,首先你要明白的一点就是,...

远方的梦Java
08/08
0
0
「mysql优化专题」本专题总结终章(13)

一个月过去了,【mysql优化专题】围绕着mysql优化进行了十三篇的优化文章,下面进行一次完整的总结!我尝试用最简短最通俗易懂的话阐述明白每篇文章,让本专题画上完美的句号!坚持到文末,留...

java进阶架构师
01/04
0
0

没有更多内容

加载失败,请刷新页面

加载更多

java框架学习日志-7(静态代理和JDK代理)

静态代理 我们平时去餐厅吃饭,不是直接告诉厨师做什么菜的,而是先告诉服务员点什么菜,然后由服务员传到给厨师,相当于服务员是厨师的代理,我们通过代理让厨师炒菜,这就是代理模式。代理...

白话
今天
21
0
Flink Window

1.Flink窗口 Window Assigner分配器。 窗口可以是时间驱动的(Time Window,例如:每30秒钟),也可以是数据驱动的(Count Window,例如:每一百个元素)。 一种经典的窗口分类可以分成: 翻...

满小茂
今天
17
0
my.ini

1

architect刘源源
今天
14
0
docker dns

There is a opensource application that solves this issue, it's called DNS Proxy Server It's a DNS server that solves containers hostnames, if could not found a hostname that mat......

kut
今天
15
0
寻找数学的广度——《这才是数学》读书笔记2700字

寻找数学的广度——《这才是数学》读书笔记2700字: 文|程哲。数学学习方式之广:国内外数学教育方面的专家,进行了很多种不同的数学学习方式尝试,如数学绘本、数学游戏、数学实验、数学步道...

原创小博客
今天
27
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部