MySQL学习之——索引(普通索引、唯一索引、全文索引、索引匹配原则、索引命中等)

版权声明:本文为郭了个治浩(http://ninty.cc)原创文章,未经允许不得转载。 https://blog.csdn.net/mysteryhaohao/article/details/51719871

在上一篇博客中,我们主要探讨了关于MySQL锁的一些问题。这一次,我们主要来聊聊,MySQL中的索引。

MySQL是目前绝大多数互联网公司使用的关系型数据库,它性能出色、资源丰富、成本低廉,是快速搭建互联网应用的首选关系型数据库。但是,俗话说,“好马配好鞍”,仅仅会使用MySQL是不够的,对MySQL在不同场景下使用性能的最小化使用代价,是一个重要的课题。一般,在互联网公司的大部分业务中,读写的比例大约是10:1,也就是说,查询的场景往往比更新或写入的场景多得多,那么问题来了,如何优化查询呢?


慢查询

<span style="font-size:14px;">SELECT 
COUNT(*) AS count 
FROM trade_base AS a
WHERE 
a.trade_status = 7 
AND a.create_time 
BETWEEN '2015-09-01' AND '2016-01-14' 
AND a.booking_source = '2'</span>

在公司的慢查询日志中,开发人员找到了这样一条SQL语句。对于一般的开发人员而言,优化这条SQL的方式是,在SQL中查询条件里的字段上,添加索引。但是如何添加索引?索引的顺序如何?索引是如何匹配命中的?一般的开发人员可能只知道大概,并没有很深入的了解。

首先解释一下,慢查询,指的是SQL查询的时间超过了预设的“慢查询定义时间”。在MySQL中,使用

<span style="font-size:14px;">SHOW variables LIKE "long%"</span>

来查询慢查询的时间定义。


MySQL索引原理

索引的目的与原理

在日常生活中,经常有这样的场景:有一个没见到过的英文单词,我们查字典找到这个单词的意思;我们要出去旅行,查询到具体地点的航班号;诸如此类。在这样的场景中,我们都是通过不断的缩小范围来筛选出最终预期的结果,同时把随机的事件变成顺序事件:查询字典,比如查单词mysql,我们是按照一个字母一个字母的顺序来查询的;查询航班号,我们也是通过地点机场航空公司一个一个来筛选缩小范围的。我们总是通过同一种查找方式来锁定数据。

数据库也是一样,但显然要比现实生活中的场景要复杂得多,因为不仅会有等值查询(=),还有范围查询(>,<,BETWEEN,IN)、模糊查询(LIKE)、交集查询(AND)、并集查询(OR)等等。数据库应该选择什么样的方式来应对所有的问题呢?我们使用查字典的例子,能不能把数据分段呢?比如,如果有1000条数据,1到100分成第一段,101到200分成第二段...这样,如果要查询第328条数据,只要找第三段就可以了,这样就省去了90%的无效数据。但是,如果数据量达到100亿,要分成多少段呢?学过数据结构的童鞋知道,在数据结构中,有一种数据结构是树(Tree),树里面有一种树叫二叉搜索树(Binary Search Tree),平均复杂度是O(logN),具有不错的查询性能。但是在这里,我们忽略了一个关键的问题,复杂度模型是基于每次相同的操作成本来考虑的,数据库的实现比较复杂,数据保存在磁盘上,而为了提高性能,每次又可以把部分数据读入内存来计算,因为我们知道——磁盘访问的成本大概是内存访问成本的十万倍左右,所以简单的搜索树,难以满足复杂的应用场景。

磁盘I/O与预读

刚刚提到了磁盘访问(别问题刚刚是谁...),那么这里先简单介绍一下磁盘的I/O与预读。磁盘读取数据,考的是机械运动,每次读取数据花费的时间可以分成:寻道时间、旋转延迟、传输时间三个部分。寻道时间指的是磁臂移动到指定磁盘所需要的时间,主流的磁盘一般在5ms以下;旋转延迟指的是我们经常说的磁盘转速,比如一个磁盘7200转,表示的就是每分钟磁盘能转7200次,转换成秒也就是120次每秒,旋转延迟就是1/120/2=4.17ms;传输时间指的是从磁盘读取出数据或将数据写入磁盘的时间,一般都在零点几毫秒,相对于前两个,可以忽略不计。那么访问一次磁盘的时间,即一次磁盘I/O的时间约等于5+4.17=9.17ms,9ms左右,听起来还是不错的哈,但要知道一台500-MIPS的机器每秒可以执行5亿条指令,因为指令依靠的是电的性质,换句话说,执行一次I/O的时间可以执行40万条指令,数据库动辄百万级甚至千万级的数据,每次9ms的时间,显然是一个灾难。


上图是计算机硬件延迟时间的对比图。

考虑到磁盘I/O是非常高昂代价的操作,计算机系统做了一些优化,当一次I/O时,不光会把当前磁盘地址的数据读取到内存中,而且会把相邻的数据也读取到内存缓冲区中,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快访问到。每一次I/O读取的数据我们称之为一页(Page)。具体一页的数据有多大,这个跟操作系统有关,一般为4K或8K,也就是我们读取一页数据的时候,实际上才发生了一次I/O,这个理论对于索引的数据结构设计很有帮助。

索引的数据结构

上面讲了索引的基本原理,数据库的复杂性,以及操作系统的一些内容,目的就是让大家了解到,任何一种数据结构都不是凭空产生的,一定有它的背景和使用场景。那么,我们需要这些数据结构能够做什么呢?其实很简单,就是:每次查找数据的时候,把磁盘I/O次数限制在一个很小的数量级,最好是一个常量数量级。那么我们就想到,如果一个高度可控的多路搜索树,是否能够满足需求呢?在这样的背景下,B+树应运而生。


详解B+树

如上图,是一棵B+树。B+树的定义,童鞋可以自行百度,我们只说一些重点。图中浅蓝色的块,我们称之为一个磁盘,可以看到,每个磁盘块包含几个数据项(深蓝色)和指针(黄色)。如:磁盘块1包含数据17和数据35,包含指针P1,P2,P3,P1指向数据小于17的磁盘块,P2指向数据在17到35之间的数据所在磁盘块,P3指向数据大于35的数据所在的磁盘块。真实数据存在于叶子节点,即3,5,9,10,13,15,28,29,36,60,75,79,90,99 。 非叶子节点不存储真实数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。


B+树的查找过程

还是使用上面的B+树。假设,我们要查找数据项29,那么我们首先会把磁盘块1由磁盘加载到内存中,此时进行了一次I/O,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存计算时间由于非常短(对比于I/O)可以忽略不计,通过磁盘块1的P2指针的磁盘地址指向磁盘块3,由磁盘加载到内存,此时进行了第二次I/O,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,此时进行了第三次I/O,同时内存中计算二分查找找到29,查询结束。这一过程,一共进行了3次I/O。在真实使用场景中,三层的B+树可以表示上百万的数据,如果上百万的数据查询只需要三次I/O,性能提高将会是巨大的。B+树就是一种索引数据结构,如果没有这样的索引,每个数据项发生一次I/O,那么成本将会大大提升。


B+树的性质

在上面的查找例子中,我们可以分析出一些B+树的性质:

1,I/O的次数取决于B+树的高度H,假设当前数据表的数据为N,每个磁盘块的数据项的数量是M,则有:H=log(M+1)N,当数据量N一定的情况下,M越大,H越小;而M=磁盘块大小/数据项大小,磁盘块大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度也就越低。这也就是为什么每个数据项,即索引字段要尽量的小,比如int占4个字节,要比bigint的8个字节小一半。这也是为什么B+树要求把真实数据放在叶子节点内而不是内层节点内,一旦放到内层节点内,磁盘块的数据项会大幅度的下降,导致树层级的增高。当数据项为1时,B+树会退化成线性表。

2,B+树的数据项是复合性数据结构,比如(name,age,gender)的时候,B+树是按照从左到右的顺序来建立搜索树的,比如当(小张,22,女)这样的数据来检索的时候,B+树会优先比较name来确定下一步的搜索方向,如果name相同再依次比较age和gender,最后得到检索的数据。但是,当(22,女)这样没有name的数据来的时候,B+树就不知道下一步该查哪个节点,因为建立搜索树的时候,name就是第一个比较因子,必须根据name来搜索才知道下一步去哪里查询。比如,当(小张,男)这样的数据来检索时,B+树就可以根据name来指定搜索方向,但下一字段age缺失,所以只能把名字是“小张”的所有数据都找到,然后再匹配性别是“男”的数据了。这个是非常重要的一条性质,即索引的最左匹配特性。


索引的类型

在MySQL中,索引分为两大类:聚簇索引和非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引则不同;聚簇索引能够提高多行检索的速度,而非聚簇索引则对单行的检索速度很快。

在这两大类的索引类型下,还可以将索引分成四个小类:

1,普通索引:最基本的索引,没有任何限制,是我们大多数情况下使用到的索引。

2,唯一索引:与普通索引类型,不同的是唯一索引的列值必须唯一,但允许为空值。

3,全文索引:全文索引(FULLTEXT)仅可以适用于MyISAM引擎的数据表;作用于CHAR、VARCHAR、TEXT数据类型的列。

4,组合索引:将几个列作为一条索引进行检索,使用最左匹配原则。


建立索引的原则

我们回头来看一开始提到的慢查询,当我们了解完索引原理之后,对慢查询的优化应该有一些想法,这里我们先总结一下建立索引的一些原则:

1,最左前缀匹配原则。这是非常重要、非常重要、非常重要(重要的事情说三遍)的原则,MySQL会一直向右匹配直到遇到范围查询(>,<,BETWEEN,LIKE)就停止匹配,比如: a = 1 AND b = 2 AND c > 3 AND d = 4,如果建立 (a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引,则都可以用到,a,b,d的顺序可以任意调整。

2,等于(=)和in 可以乱序。比如,a = 1 AND b = 2 AND c = 3 建立(a,b,c)索引可以任意顺序,MySQL的查询优化器会帮你优化成索引可以识别的模式。

3,尽量选择区分度高的列作为索引,区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率,比率越大我们扫描的记录数就越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度是0。可能有人会问,这个比率有什么经验么?使用场景不同,这个值也很难确定,一般需要JOIN的字段我们要求在0.1以上,即平均1条扫描10条记录。

4,索引列不能参与计算,尽量保持列“干净”。比如,FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引,原因很简单,B+树中存储的都是数据表中的字段值,但是进行检索时,需要把所有元素都应用函数才能比较,显然这样的代价太大。所以语句要写成 : create_time = UNIX_TIMESTAMP('2016-06-06')。

5,尽可能的扩展索引,不要新建立索引。比如表中已经有了a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

6,单个多列组合索引和多个单列索引的检索查询效果不同,因为在执行SQL时,MySQL只能使用一个索引,会从多个单列索引中选择一个限制最为严格的索引。

根据上面这些原则,我们来修改开篇的慢查询:

SELECT 
count(*) AS count 
FROM trade_bASe AS a
WHERE 
a.trade_status = 7 
AND a.create_time BETWEEN '2015-09-01' AND '2016-01-14' 
AND a.booking_source = '2'
根据这条SQL,应该建立的索引是:trade_status, booking_source,create_time的联合索引;其中,trade_status、booking_source的顺序可以颠倒,而且 create_time 的区间查询放到后面。这就是利用了索引的最左匹配原则。

慢查询的优化步骤

1,查看运行效果,是否真的很慢,主要设置SQL_NO_CACHE。

2,WHERE条件单表查询,锁定最小返回记录表。这句话的意思是,把查询语句的WHERE都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高

3,EXPLAIN查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)

4,ORDER BY LIMIT 形式的SQL语句,让排序的表优先查

5,多去了解业务的使用场景

6,加索引时,要参照建立索引的几大原则

7,观察结果,不符合预期,则重新从1开始分析。


索引的优化方法

1,何时使用聚簇索引或非聚簇索引:

使用动作描述 使用聚簇索引 使用非聚簇索引
列经常被分组排序
返回某范围内的数据
×
一个或极少不同的值 ×
×
小数目不同的值
×
大数目不同的值 ×

频繁更新的列 ×

外键列

主键列

频繁修改索引列 ×

2,索引不会包含有NULL值的列:只要列中包含有NULL值,都将不会被包含在索引中,组合索引中只要有一列有NULL值,那么这一列对于此条组合索引就是无效的。所以我们在数据库设计时,不要让索引字段的默认值为NULL。

3,使用短索引:假设,如果有一个数据类型为CHAR(255)的列,在前10个或20个字符内,绝大部分数据的值是唯一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省I/O操作。

4,索引列排序:MySQL查询只使用一个索引,因此如果WHERE子句中已经使用了索引的话,那么ORDER BY中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下,不要使用排序操作;尽量不要包含多个列的排序,如果需要,最好给这些列也创建组合索引。

5,LIKE语句操作:一般情况下,不建议使用LIKE操作;如果非使用不可,如何使用也是一个研究的课题。LIKE "%aaaaa%"不会使用索引,但是LIKE "aaa%"却可以使用索引。

6,不要在索引列上进行运算:在建立索引的原则中,提到了索引列不能进行运算,这里就不再赘述了。


最后,总结一下,其实任何数据库层面的优化,都抵不上应用系统的优化,同样是MySQL,Facebook/Google等等都可以支撑,所以且行且珍惜吧!

展开阅读全文

没有更多推荐了,返回首页