数据库系统之索引

存储管理器实现了几种数据结构，作为系统物理实现的一部分:
●数据文件(data files), 存储数据库自身。
●数据字典(data dictionary)，存储关于数据库结构的元数据，尤其是数据库模式。
●索引(index)，提供对数据项的快速访问。和书中的索引一样，数据库索引提供了指向包含特定值的数据的指针。例如可以运用索引找到具有特定的ID的instruetor记录，或者具有特定的name的所有instructor记录。
物理存储介质

一、磁盘

磁盘的每一个盘片(patter)是扁平的圆盘。它的两个表面都覆盖着磁性物质，信息就记录在表面上。盘片由硬金属或玻璃制成。磁盘使用时，驱动马达使磁盘以很高的恒定速度旋转(每秒60、90或120转，也可达到每秒250转)。有一个读写头位于盘片表面的上方。盘片的表面从逻辑上划分为磁道(track),磁道又划分为扇区(sector)。扇区是从磁盘读出和写入信息的最小单位。现在的磁盘扇区大小一般为512字节，每一个盘片有约50 000~ 100 000条磁道，每条磁盘有1-5个盘片。内侧的磁道(离转轴近的地方)长度较短。现代磁盘中，外侧的磁道比内侧的磁道拥有更多的扇区。一般而言，内侧每磁道大约包含500-1000个扇区，而外侧每磁道大约包含1000 - 2000个扇区。对于不同模式的磁盘，上面的数字会有变化， 高容量模式通常在每条磁道上含有更多的扇区，而且在每个盘片上有更多的磁道。
通过反转磁性物质磁化的方向，读写头将信息磁化存储到扇区中。磁盘的每个盘片的每一面都有一个读写头，通过在盘片上移动来访问不同的磁道。一张磁盘通常包括多个盘片，所有磁道的读写头安装在一个称为磁盘臂的单独装置上，并且一起移动。安装在转轴上的所有磁盘盘片和安装在磁盘臂上的所有读写头统称为磁头一磁盘装置(head-disk assembly)。因为所有盘片上的读写头一起移动，所以当某一个盘片的读写头在第i条磁道上时，所有其他盘片的读写头也都在各自盘片的第i条磁道上。因此，所有盘片的第i条磁道合在一起称为第i个柱面。

二、索引类型

索引是一个单独的、存储在磁盘上的数据库结构，包含对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引。索引在存储引擎中实现，每种存储引擎支持的索引都不完全相同。MySQL中索引类型有两种，BTREE和HASH。
索引的优点主要有:
1.通过创建唯一索引可以保证数据库表中每行数据的唯一性。
2.加快数据的查询速度。
3.加速表和表之间的连接。
增加索引不利的方面：
1.创建和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
2.索引占磁盘空间，除了数据表占数据空间之外,每个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
3.对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

索引种类：

1.普通索引和唯一索引
普通索引是MySQL中的基本索引类型，允许索引为重复值和空值。
唯一索引要求索引列的值必须唯一，允许为NULL。 如果是组合索引，则列值的组合必须唯一。主键索引是特殊的唯一索引，不允许有空值。
2.组合索引
由多个列值组成的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀匹配原则。
3.全文索引
在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。

三、聚簇索引和非聚簇索引

主要区别是索引和数据是否分开存储。在InnoDB中，聚簇索引是根据主键创建的一棵B+树，将数据和索引放到一起存储，叶子节点存放了数据行。辅助索引将数据和索引分开存储，其叶子节点仅存放索引键值，以及该索引键值指向的主键。也就是说如果通过辅助索引来查找数据，那么当找到辅助索引的叶子节点后还要根据主键值查找聚簇索引来得到数据。因为辅助索引不包含行记录的所有数据，这就意味着可以存放更多的键值，因此其高度一般都要小于聚簇索引。

在MylSAM存储引擎中，默认的索引也是B+树索引，但主索引和辅助索引都是非聚簇索引,也就是说索引结构的叶子节点存储的都是一个指向数据行的地址。 并且使用辅助索引检索无需访问主键的索引。


B+树(B+tree)索引结构是在数据插入和删除的情况下仍能保持其执行效率的几种使用最广泛的索引结构之一。B+树索引采用平衡树结构，其中树根到树叶的每条路径长度相同。树中每个非叶结点有[n/2] ~ n个子女，其中n对特定的树是固定的。B+树结构会增加文件插入和删除处理的性能开销，同时会增加空间开销。但是这种开销是可接受的，因为这样能够减小文件重组的代价。结点有可能是半空的(如果它们具有最少子结点数的话)，会造成空间的浪费。

非聚簇索引一定会进行回表查询吗?

非聚簇索引的叶子节点存储的是主键，也就是说要先通过非聚簇索引找到主键,再通过聚簇索引找到主键所对应的数据，后面这个再通过聚簇索引找到主键对应的数据的过程就是回表查询，那么非聚簇索引一定会进行回表查询吗?
不一定，这里涉及到一个索引覆盖的问题，如果查询的数据在辅助索引上能获取到便不需要回表查询。例如有一张表存储着个人信息包括id、name. age等字段。假设聚簇索引是以ID为键值构建的索引，非聚簇索引是以name为键值构建的索引，select id,name from user where name = ' zhangsan'；这个查询便不需要进行回表查询。因为通过非聚簇索引已经能全部检索出数据，这就是索引覆盖的情况。如果查询语句是这样，select id,name,age from user where name =
' zhangsan'；则需要进行回表查询，因为通过非聚簇索引不能检索出age的值。那应该如何解决呢?只需要将索引覆盖即可，建立age和name的联合索引再使用select id,name,age from user where name = ' zhangsan '；进行查询即可。
所以通过索引覆盖能解决非聚簇索引回表查询的问题。

联合索引：

联合索是指对表上的多个列进行索引，创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。从本质上来说，联合索引还是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2。只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，所以使用联合索引时遵循最左前缀匹配原则。

四、B+树索引

B树和B+树最主要的区别有两点:
●B树中的内部节点和叶子节点均存放键和值，而B+树的内部节点只有键没有值，叶子节点存放所有的键和值。
●B+树的叶子节点是通过相连在一起的，方便顺序检索。

B-Tree,平衡多路查找树，如果每个节点最多有N个孩子，这样的树就叫N阶B-Tree, 每个节点中主要包含关键字和指向孩子的指针。B-Tree作为一种数据结构，有如下特征:
1.根节点至少包含两个孩子
2.树中每个节点至多含有N个孩子(N>=2)
3.除根节点和叶节点外，其它每个节点至少有ceil(N/2)个子节点。
4.所有叶子节点都位于同一层，即叶子节点的高度相同。

假设查询关键字为10的数据，从根节点出发，10<17,于是通过P1进入其孩子节点，10>8且10<12, 于是通过P2进入其孩子节点，最后寻找到10。如果不使用索引，而使用逐行扫描的方式进行查找，则从0开始至少扫描10次才能查找到10号数据，有了索引之后查找次数从10变为3，大大提高了查找效率。
如果这里是二又查找树，会出现极端情况，使得查找时间复杂度为0(n)；而如果是B-Tree,可以让节点通过合并、分裂、上移、下移等操作，使得树高度较二叉查找树小，查找效率显然更高。

B+Tree是B-Tree的一个变体，其定义基本与B树相同，除了:
1.非叶子节点的子树指针与关键字个数相同。
2.非叶子节点仅用来做索引，数据到保存在叶子节点中。
3.所有叶子节点均有一个指针，指向下个叶子节点。

下图为一颗B+树。和常规B+树有些不同，这是一棵MySQL意义上的B+树，MySQL的一种索引结构，其中的每个节点可以理解为是一个页， 而叶子节点也就是数据页，除了叶子节点以外的节点就是目录页。在图中也可以看出，非叶子节点只存放了索引，只有叶子节点中存放了真实的数据，符合B+树的特点。

图11.9是文件(n=4)的一棵完整的B+树，忽略了空指针。图11-10是文件(n=6)的另一棵完整的B+树。可以观察到这棵树的高度小于前面n=4的树。
这些B+树的例子都是平衡的，即从根到叶结点的每条路径长度都相同。对于B+树来说这是一个必需的性质。B+树的“B"就表示“平衡" (balanced)的意思。正是B+树的平衡属性保证了索引有良好的查找、插入和修改性能。

数据库为什么使用B+树而不是B树?
●B树适用于随机检索，B+树的叶子节点连接在一起，适用于范围检索和顺序检索。
●B+树的空间利用率更高，因为B树每个节点要存储键和值，而B+树的内部节点只存储键，这样B+树的一个节点就可以存储更多的索引，从而使树的高度变低，减少了I/0次数，使得数据检索速度更快。
●B+树的性能更加稳定，因为在B+树中，每次查询都是从根节点到叶子节点，而在B树中，要查询的值可能不在叶子节点，在内部节点就已经找到。
B树的内部节点存储值，可以把一些频繁访问的值放在距离根节点比较近的地方，可以提高查询效率。B+树的性能更加适合作为数据库的索引。

五、散列索引

1.静态散列索引

顺序文件组织的一个缺点是必须访问索引结构来定位数据，或者使用二分法搜索，导致过多的I/O操作。哈希索引是基于哈希表实现的。对于每一行数据，存储引擎会对索引列通过哈希算法进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的。将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是o(1)，一般多用于精确查找。
假定两个搜索码有相同的散列值，则桶h(Ki)包含搜索码值是Ki以及Kj的记录。因此必须检查桶中每条记录的搜索码值，以确定该记录是否是要查找的记录。
最坏的可能是散列函数把所有的搜索码值映射到同一桶中，这种函数让所有的记录存放到同一个桶里，查找一个需要的记录时将检查所有记录。理想的散列函数把存储的码均匀、随机地分布到所有桶中，使每个桶含有相同数目的记录。

2.动态散列索引

静态散列技术要求固定桶地址集合，这导致大多数数据库都会随时间而变大。几种动态散列技术允许散列函数动态改变，以适应数据库增大或缩小的需要。比如可扩充散列。
当数据库增大或缩小时，可扩充散列可以通过桶的分裂或合并来适应数据库大小的变化。此外由于重组每次仅作用于一个桶，因此所带来的性能开销较低。使用可扩充散列时，选择一个具有均匀性和随机性的散列函数。此散列函数产生的值范围相对较大，是b位二进制整数。一个典型的b值是32。

两种索引比较

两者数据结构上的差异导致它们的使用场景也不同，哈希索引一般用于精确的等值查找，B+索引则多用于除了精确的等值查找外的其他查找。大多数情况下使用B+树索引。
●哈希索引不支持排序和范围查找，因为哈希表是无序的。
●哈希索引不支持模糊查询及多列索引的最左前缀匹配。
●因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点

六、索引使用

MySQL怎么判断要不要加索引?

1.当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索需能确保定义的列的数据完整性，以提高查询速度。
2.在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

只要创建了索引，就一定会走索引吗?

不一定。在使用组合索引的时候如果没有遵从最左前缀的原则进行搜索，则索引是不起作用的。假设在id、name、 age字段上建立了一个名为Multildx的组合索引。 索行中按id、name、 age的顺序存放，索引可以搜索id、( id,name )、( id, name, age )字段组合。如果列不构成索引最左面的前缀，那么MySQL不能使用局部索引。如( age )或者( name,age )组合不能使用该索引查询。

如何判断数据库的索引有没有生效?

使用EXPLAIN语句查看索引是否正在使用。
假设创建了book表并在其year_publication字段上建立了普通索引。执行如下语句:
EXPLAIN SELECT * FROM book WHERE year_publication=1990;
EXPL AIN语句输出SQL执行信息，其中:
●possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。
●key行是MySQL实际选用的索引。
如果possible_keys行和key行都包含year_publication字段 ,则说明在查询时使用了该索引。

索引的使用场景

●对于中大型表建立索引非常有效，对于非常小的表，一般全表扫描速度更快。
●对于超大型的表，建立和维护索引的代价也会变高，这时可以考虑分区技术。
●如果表的增删改非常多而查询需求非常少的话，就没有必要建立索引了，因为维护索引也是需要代价的。
●一般不会出现在where条件中的字段没有必要建立索引。
●多个字段经常被查询的话可以考虑联合索引。
●字段多且字段值没有重复的时候考虑唯一索引。
●字段多且有重复的时候考虑普通索引。

索引的设计原则?

●最适合索引的列是在where后面出现的列或者连接句子中指定的列，而不是出现在SELECT关键字后面的选择列表中的列。
●索引列的基数越大，索引的效果越好。换句话说就是索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差，因为列的基数最多也就是2种。
●尽量使用短索引。对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少,并且索引高速缓存中的块可以容纳更多的键值，会使得查询速度更快。
●尽量利用最左前缀。
●不要过度索引，每个索引都需要额外的物理空间，维护也需要花费时间，索引不是越多越好。

索引优化

关键就是符合索引的设计原则和应用场景，将不符合要求的索引优化成符合索引设计原则和应用场景的索引。除了索引的设计原则和应用场景那几点外，还可以从以下两方面考虑。
●在进行查询时，索引列不能是表达式的一部分,也不能是函数的参数，因为这样无法使用索引。例如select * from table_ name where a+1=2
●将区分度最高的索引放在前面
●尽量少使用select*

创建、删除索引

创建索引:

●使用CREATE INDEX语句
CREATE INDEX index_name ON table_name (column_list);
●在CREATE TABLE时创建
CREATE TABLE user(
id INT PRIMARY KEY,
information text ，
FULLTEXT KEY (information)
);
●使用ALTER TABLE创建索引
ALTER TABLE table_name ADD INDEX index_name (column_list);

删除索引:

●删除主键索引
alter tab1e 表名 drop primary key
●删除其他索引
alter tab1e 表名 drop key 索引名

最左匹配原则

从最左边为起点开始连续匹配，遇到范围查询(<、 > between. like) 会停止匹配。例如建立索引(a,b,c)。

///
select * from table_name where a=1 and b=2 and c=3
select * from table_name where b=2 and a=1 and c=3
上面两次查询过程中所有值都用到了索引, where后面字段调换不会影响查询结果，因为MySQL中
的优化器会自动优化查询顺序。
///
select * from table_name where a = 1 
select * from table_name where a= 1 and b = 2
select * from table_name where a = 1 and b=2 and C=3
三个查询语句都用到了索引，因为三个语句都是从最左开始匹配的。
///
select * from table_name where b=1
select * from table_name where b=1 and c=2
两个查询语句都没有用到索引，因为不是从最左边开始匹配的
///
select * from table_name where a=1 and c=2
只有a列用到了索引，c列没有用到索引，因为中间跳过了b列,不是从最左开始连续匹配的。
///
select * from table_name where a=1 and b<3 and c<1
只有a列和b列使用到了索引而c列没有，因为根据最左匹配查询原则，遇到范围查询会停止。
///
select * from table_name where a like 'ab%'
select * from table_name where a 1ike '%ab'
select * from table_name where a 1ike '%ab%'
对于列为字符串的情况，只有前缀匹配可以使用索引，中缀匹配和后缀匹配只能进行全表扫描。

索引在什么情况下会失效?

不符合最左匹配原则的情况会导致索引失效。以下这几种情况也会导致索引失效。.
●条件中有or，例如select * from table_name where a=1 or b=3
●在索引上进行计算会导致索引失效，例如select * from table_name where a+1=2
●在索引的类型上进行数据类型的隐形转换会导致索引失效，例如字符串一定要加引号，假设
select * from table_name where a = '1'会使用到索引，如果写成select * from table_name where a = 1则会导致索引失效。
●在索引中使用函数会导致索引失效，例如select * from table_name where abs(a) = 1
●在使用like查询时以%开头会导致索引失效
●索引上使用!、=、<>进行判断时会导致索引失效，例如select * from table_name where a != 1
●索引字段上使用is null / is not null判断时会导致索引失效，例如select * from table_name where a is null
 

七、在MySQL中的应用

哈希索引只有Memory, NDB两种引擎支持，Memory引擎默认支持哈希索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储。Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引。

InnoDB或MyISAM默认支持的是b+树索引，可以通过伪哈希索引来实现哈希索引，叫自适应哈希索引。 但是这里的哈希索引的用途不是用来根据搜索码找到对应的记录  而是系统自动对那些B+树索引中使用较频繁的索引页建立哈希索引，索引更像是"索引的索引"，它是Innodb/myisam引擎根据表的使用情况自动为表添加的的，不能人为干预是否在表中生成哈希索引。

在MySQL运行的过程中，如果InnoDB发现，有很多SQL存在这类很长的寻路，并且有很多SQL会命中相同的页面，InnoDB会在自己的内存缓冲区里开辟一块区域， 建立自适应哈希索引以加速查询。InnoDB的自适应哈希索引，更像“索引的索引”，目的是为了加速索引。key是索引键值(或者键值前缀)，value是索引记录页面位置。系统自己判断“应该可以加速查询”而建立，不需要用户手动建立，故称“自适应”。

经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。

由于innodb不支持hash索引，但是在某些情况下hash索引的效率很高，于是出现了adaptive hash index功能。默认开启。可以通过set global innodb_adaptive_hash_index=off/on开关该功能。特点
1、无序，没有树高
2、降低对二级索引树的频繁访问资源。索引树高<=4，访问索引：访问树、根节点、叶子节点
3、自适应
缺陷
1、hash自适应索引会占用innodb buffer pool;
2、自适应hash索引只适合搜索等值的查询，如select * from table where index_ col=xxx',而对于其他查找类型，如范围查找，是不能使用的;
3、极端情况下，自适应hash索引才有比较大的意义，可以降低逻辑读。

八、总结

如果大多数查询形如
select   A1, A2 .. An
from     r
where   Ai =c;
对于这种形式的查询，散列的方案更可取。顺序索引的查找所需时间与关系r中Ai值的个数的对数成正比。但在散列结构中，平均查找时间是一个与数据库大小无关的常数。对于这种形式的查询，非散列的索引结构的唯一优点是最坏情况下的查找时间和关系r中Ai值的个数的对数成正比；而用散列时，最坏情况下所需的查找时间和关系r中Ai值的个数成正比。但是用散列时最坏查找发生的可能性极小，因而在这种情况下散列更可取。
顺序索引技术在指出了一个值范围的查询中比散列更可取。这样的查询有如下形式: 
select        A1, A2 ... Ai
from          r
where       Ai≤c2; and Ai>c1
用顺序索引处理该查询，首先查找值c1。一旦找到值c1的桶，就可以顺着索引中的指针链顺序读取下一个桶，如此继续下去直到到达C2。如果不用顺序索引而用散列结构，因为好的散列函数总是将值随机地分散到各桶中。因而没有一个简单的概念能够表示“按顺序排序的下一个桶"。既然值由散列函数随机地散布，在一定范围内的值就很可能散布在很多桶中，为了找到所需搜索码不得不读取所有的桶。
通常使用顺序索引，除非预先知道将来不会频繁使用范围查询，在这种情况下使用散列。散列组织对于在查询执行过程中创建的临时文件来说特别有用，如果需要基于码值查找并且不执行范围查询。

一个关系数据库系统需要维护关于关系的数据，如关系的模式等。一般来说，这样的“关于数据的数据"称为元数据(metadata)。关于关系的关系模式和其他元数据存储在称为数据字典或系统目录的结构中。
实际上所有这些元数据信息组成了一个微型数据库。一些数据库系统使用专用的数据结构和代码来存储这些信息。通常人们更倾向于在数据库中存储关于数据库本身的数据。通过使用数据库来存储系统数据，简化了系统的总体结构。