1. 索引

索引用于:

  • 快速找出匹配一个WHERE子句的行;

  • 当执行联结时,从其他表检索行;

  • 对特定的索引列找出MAX()或MIN()值;

  • 如果排序或分组在一个可用键的最左面前缀上进行(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。

  • 如果所有键值部分跟随DESC,键以倒序被读取。

  • 在一些情况中,一个查询能被优化来检索值,不用咨询数据文件。

如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀,为了更快,值可以从索引树被检索出来。

1.1. 索引原理

索引要求做到减少IO,加速查询

  • 在表中有大量数据的前提下,创建索引速度会很慢

    在没有数据的情况下先建索引或者说目录快,还是已经存在好多的数据了,然后再去建索引,哪个快,肯定 是没有数据的时候快,因为如果已经有了很多数据了,你再去根据这些数据建索引,是不是要将数据全部遍历一遍,然后根据数据建立索引。

  • 在索引创建完毕后,对表的查询性能会幅度提升,但是写性能会降低

    索引建立好之后再添加数据快,还是没有索引的时候添加数据快,索引是用来干什么的,是用来加速查询的, 那对你写入数据会有什么影响,肯定是慢一些了,因为你但凡加入一些新的数据,都需要把索引或者说书的目录重新做一个,所以索引虽然会加快查询,但是会降低写入的效率

1.2. 磁盘IO与预读

磁盘读取数据靠的是机械运动,每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分。

  • 寻道时间指的是磁臂移动到指定磁道所需要的时间,主流磁盘一般在5ms以下;

  • 旋转延迟就是我们经常听说的磁盘转速,比如一个磁盘7200转/min,表示每分钟能转7200次,也就是说1秒钟能转120次,旋转延迟就是1/120/2 = 4.17ms,也就是半圈的时间(这里有两个时间:平均寻道时间,受限于目前的物理水平,大概是5ms的时间,找到磁道了,还需要找到你数据存在的那个点,寻点时间,这寻点时间的一个平均值就是半圈的时间,这个半圈时间叫做平均延迟时间,那么平均延迟时间加上平均寻道时间就是你找到一个数据所消耗的平均时间,大概9ms,其实机械硬盘慢主要是慢在这两个时间上了,当找到数据然后把数据拷贝到内存的时间是非常短暂的,和光速差不多了);

  • 传输时间指的是从磁盘读出或将数据写入磁盘的时间,一般在零点几毫秒,相对于前两个时间可以忽略不计

那么访问一次磁盘的时间,即一次磁盘IO的时间约等于5+4.17 = 9ms左右。

计算机硬件延迟的对比图:

img

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

1.3. 索引的数据结构

红黑树等数据结构也可以用来实现索引,但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构.

一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数,最好是常数数量级。

1.3.1. 详解b+树

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并不真实存在于数据表中。

1.3.2. b+树的查找过程

如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

1.3.3. b+树性质

1**.索引字段要尽量的小**:通过上面的分析,我们知道IO次数取决于b+数的高度h或者说层级,这个高度或者层级就是你每次查询数据的IO次数,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N一定的情况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。

2.索引的最左匹配特性:简单来说就是你的数据来了以后,从数据块的左边开始匹配,在匹配右边的.

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

1.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) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

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

1.5. MySQL索引管理

1.5.1. 功能

#1. 索引的功能就是加速查找
#2. mysql中的primary key,unique,联合唯一也都是索引,这些索引除了加速查找以外,还有约束的功能

1.5.2. MySQL常用的索引

普通索引INDEX:加速查找

唯一索引:
    -主键索引PRIMARY KEY:加速查找+约束(不为空、不能重复)
    -唯一索引UNIQUE:加速查找+约束(不能重复)

联合索引:
    -PRIMARY KEY(id,name):联合主键索引
    -UNIQUE(id,name):联合唯一索引
    -INDEX(id,name):联合普通索引

1.5.3. 参考如下:

MySQL索引原理及慢查询优化

MySQL索引