数据库（八）

前言

本篇博客内容为索引，索引是为了提高数据库的查询效率。

索引

什么是索引？

索引就相当于书的目录，是 mysql 中一种专门的数据结构，称为 key，索引的本质原理就是通过不断地缩小查询范围，来降低 io 次数从而提升查询性能。强调：一旦为表创建了索引，以后的查询都会先查索引，再根据索引定位的结果去找数据。

为什么要用索引？

对于一个应用来说，对数据库的读写比例基本上是10：1，即读多写少。而且对于写来说极少出现性能问题，大多数性能问题都是慢查询提到加速查，这时候就必须用到索引。

索引的影响

1. 在表中有大量数据的前提下，创建索引速度会很慢；
2. 在索引创建完毕后，对表的查询性能会大幅度提升，但是写性能会降低。

磁盘 io 与预读

磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms 以下；旋转延迟就是磁盘转速花费的时间，比如一个磁盘7200转，表示每分钟能转7200次，也就是一秒钟能转120次，旋转延迟就是1/120/2=4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间来说可以忽略不计。那么访问一次磁盘的时间，即一次磁盘 io 的时间约等于5+4.17=9ms 左右，看起来还挺短的，但是对于现在的计算机来说，一秒钟可以执行5亿条指令，执行一次 io 的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级别数据，每次9毫秒的时间，很显然会造成大量的等待时间。下图是计算机硬件延迟的对比图：

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

索引的数据结构

对于索引来说，它的目的就是降低查找数据时产生的磁盘 io 数量级，最好是常数数量级。索引使用的是 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+树查找过程

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

b+数性质

1. 索引字段要尽量的小：io 次数取决于 b+树的高度 h，假设当前数据表的数据为 n，每次磁盘块的数据项的数量是 m，则有 h=log(m+1)n，当数据量 n 一定的情况下，m 越大，h 越小；而 m=磁盘块的大小/数据项的大小，磁盘块的大小也是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如 int 占4字节，要比 bigint 8字节少一半。这也是为什么 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的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

聚集索引与辅助索引

在数据库中，b+树的高度一般都在2~4层，这也就是说查找某一个键值的行记录时最多只需要2到4次io，这倒不错。因为当前一般的机械硬盘每秒至少可以做100次io，2~4次的io意味着查询时间只需要0.02~0.04秒。

数据库中的b+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是b+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引与辅助索引不同的是：叶子节点存放的是否是一条完整的记录。

聚集索引

innodb存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵b+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同b+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
    
如果未定义主键，mysql取第一个唯一索引（unique）而且只含非空列（not null）作为主键，innodb使用它作为聚簇索引。
    
如果没有这样的列，innodb就自己产生一个这样的id值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

由于实际的数据页只能按照一棵b+树进行排序，因此每张表只能拥有一个聚集索引。在这种情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在b+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

聚集索引优点

1. 它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于 b+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录。
2. 范围查询，即如果要查找某一范围内的数据，通过叶子节点的上层节点就可以得到页的范围，之后直接读取数据页即可。

辅助索引

表中除了聚集索引外其他索引都是辅助索引，也称为非聚集索引，与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签。该书签用来告诉 innodb 存储引擎去哪里可以找到与索引相对应的行数据。

由于 innodb 存储引擎是索引组织表，因此 innodb 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。如下图：

辅助索引的存在并不影响数据再聚集索引中的组织，因此每张表中可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，innodb存储引擎会遍历辅助索引并通过叶子级别的指针获得指向主键索引的主键，然后通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行三次查找，最终找到一个完整的行数据所在的页，因此一共需要6次 io 访问才能得到最终的一个数据页。

mysql 索引管理

索引的功能

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

mysql 常用索引

• 普通索引：index 加速查找
• 唯一索引：
  • 主键索引 primary key：加速查找+约束（不能为空，不能重复）
  • 唯一索引 unique：加速查找+约束（不能重复）
• 联合索引：
  • primary key(id,name)：联合主键索引
  • unique(id,name)：联合唯一索引
  • index(id,name)：联合普通索引

创建/删除索引

1. 创建表时

mysql> create table 表名(
       字段名1 数据类型 [完整性约束条件...],
       字段名2 数据类型 [完整性约束条件...],
       [unique | fulltext | spatial] index | key
       [索引名] (字段名[(长度)] [asc | desc]));

1. 在已存在的表上创建索引

mysql> create [unique | fulltext | spatial ] index 索引名
       on 表名 (字段名[(长度)] [asc | desc]);

1. alter table在已存在的表上创建索引

mysql> alter table 表名 add [unique | fulltext | spatial ] index
       索引名(字段名[(长度)] [asc | desc]);

1. 删除索引

mysql> drop index 索引名 on 表名;

总结

1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了。

3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.myi

mysam索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照b+tree（btree即balance true）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.

正确使用索引

并不是说创建了索引就一定会加快查询速度，若想利用索引达到预期的提高查询速度的效果，我们在添加索引时，必须考虑以下问题：

1. 范围问题，或者说条件不明确，条件中出现这些符号：>、>=、<、<=、!=、between and、like

2. 尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录越少，唯一键的区分度是1，而一些状态、性别字段可能在数据量特别大的地方就是0，一般需要 join 的字段要求都是0.1以上，即平均1次扫描10条记录
3. =和 in 可以乱序，比如 a=1 and c=3建立(a,b,c)索引可以任意顺序，mysql 的查询优化器会优化成索引可以识别的形式
4. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)

5. and/or

1、and与or的逻辑
    条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
    条件1 or 条件2:只要有一个条件成立则最终结果就成立

2、and的工作原理
    条件：
        a = 10 and b = 'xxx' and c > 3 and d =4
    索引：
        制作联合索引(d,a,b,c)
    工作原理:
        对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序

3、or的工作原理
    条件：
        a = 10 or b = 'xxx' or c > 3 or d =4
    索引：
        制作联合索引(d,a,b,c)
        
    工作原理:
        对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d
在左边条件成立但是索引字段的区分度低的情况下(name 与 gender 均属于这种情况)，会依次往右找到一个区分度高的索引字段，加速查询

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. 其他情况

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（join）来代替子查询(sub-queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合