数据库索引

本文参考了上下几十篇博客得以总结

什么是索引？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息

索引的作用？

1. 在设计数据库时，通过创建一个惟一的索引，能够在索引和信息之间形成一对一的映射式的对应关系，增加数据的惟一性特点。
2. 能提高数据的搜索及检索速度，符合数据库建立的初衷。
3. 能够加快表与表之间的连接速度，这对于提高数据的参考完整性方面具有重要作用。
4. 在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。
5. 建立索引之后，在信息查询过程中可以使用优化隐藏器，这对于提高整个信息检索系统的性能具有重要意义。

原文博主

举个例子来说，假设我们有一个数据库student，这个表分别有三个字段：name，age，class。假设表中有2000条记录。
1、假如没有使用索引，当我们查询名为“xiaxia”的学生的时候，即调用：
select name,age,class from student where name = "xiaxia";
此时数据库不得不在student表中对这2000条记录一条一条的进行判断name字段是否为“xiaxia”。这也就是所谓的全表扫描。
2、而当我们在student表上的name字段上创建索引时，当我们查询名为“xiaxia”的学生时：
会通过索引查找去查询名为“xiaxia”的学生，因为该索引已经按照字母顺序排列，因此要查找名为“xiaxia”的记录时会快很多，因为名字首字母为“x”的雇员都是排列在一起的。通过该索引，能获取到表中对应的记录。

索引的分类

聚簇是指：如果一组表有一些共同的列，则将这样一组表存储在相同的数据库块中;聚簇还表示把相关的数据存储在同一个块上。

按照存储角度分

聚簇索引(簇类索引) 是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。
非聚簇索引 叶级页指向表中的记录，记录的物理顺序与逻辑顺序没有必然的联系。非聚簇索引则更像书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。更像一种映射的关系
使用时机

按照数据库功能分

• 唯一索引
  即索引值是唯一的，不准有相同的重复，比如在表中创建唯一索引的话就那属性就不能出现重复的啦，就像不能出现两个相同的id。可以使用关键字UNIQUE。
• 主键索引
  数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。
  在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
  eg: table中 创建一个 a int, primary key (a);等
  或者 a int priamary key
• 聚集索引
  在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。
  如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度
  比如查找apple时它就在字典对应的第一页这种。

索引的存储

原文博主

1.局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。
为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的**局部性**原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。
预读的长度一般为 页 （page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

2.磁盘的构造

磁盘是一个扁平的圆盘。盘面上有许多称为磁道的圆圈，数据就记录在这些磁道上。磁盘可以是单片的，也可以是由若干盘片组成的盘组，每一盘片上有两个面。如上图11.3中所示的6片盘组为例，除去最顶端和最底端的外侧面不存储数据之外，一共有10个面可以用来保存信息

当磁盘驱动器执行读/写功能时。盘片装在一个主轴上，并绕主轴高速旋转，当磁道在读/写头(又叫磁头) 下通过时，就可以进行数据的读 / 写了。
一般磁盘分为固定头盘(磁头固定)和活动头盘。固定头盘的每一个磁道上都有独立的磁头，它是固定不动的，专门负责这一磁道上数据的读/写。

活动头盘 (如上图)的磁头是可移动的。每一个盘面上只有一个磁头(磁头是双向的，因此正反盘面都能读写)。它可以从该面的一个磁道移动到另一个磁道。所有磁头都装在同一个动臂上，因此不同盘面上的所有磁头都是同时移动的(行动整齐划一)。当盘片绕主轴旋转的时候，磁头与旋转的盘片形成一个圆柱体。各个盘面上半径相同的磁道组成了一个圆柱面，我们称为柱面 。因此，柱面的个数也就是盘面上的磁道数。

3.磁盘的读写原理及效率

读/写磁盘的三个步骤：

(1) 首先移动臂根据柱面号使磁头移动到所需要的柱面上，这一过程被称为定位或查找 。 (2)
如上图中所示的6盘组示意图中，所有磁头都定位到了10个盘面的10条磁道上(磁头都是双向的)。这时根据盘面号来确定指定盘面上的磁道。 (3)
盘面确定以后，盘片开始旋转，将指定块号的磁道段移动至磁头下。

耗费时间：
查找时间：即完成步骤（1）的时间，这部分耗时最多
等待时间：即完成步骤（3）的时间
传输时间：数据通过系统总线送到内存的时间

4.数据库中使用什么数据结构作为索引

原文博主
（a）链表
链表的查询速度是O（N），每次查询都得从链表头开始查询，例如上面查询“xiaxia”，如果xiaxia在1000的位置，那么需要遍历1000次才能查找到。
（b）数组
有人可能会说，查询速度肯定是数据最快呀，毕竟O（1），的确单纯就select的话，采用数组的形式是最合适的，但是采用数组会遇到如下几个问题：1、采用数组的话，其他操作如Delete、Update、Insert就不合适了；2、另外一个原因：索引是存在于磁盘中，当索引非常大的时候，达到几个G的时候，无法一次加载到内存中。
c）平衡二叉树
二叉查找树查询的时间复杂度是O（logN），查找速度最快和比较次数最少，既然性能已经如此优秀，但为什么实现索引是使用B-Tree而不是二叉查找树，关键因素是磁盘IO的次数。
d）B树和B+树
数据库索引采用的数据结构

5.索引存储为什么采取这样的数据结构？

大规模的数据不可能全部存储在内存中，故要存储到磁盘上，这样查找读取等操作时就涉及到磁盘IO，那么索引就要尽量减少磁盘IO次数，才能保证查找速度。如果采用普通的二叉查找树结构，会由于树的高度过深进行多次磁盘IO，导致查询效率低下，那么就要尽量减少树的高度，这就引出了B-Tree和B±Tree,即B树和B+树。

总结：为什么使用B+树？

1. 文件很大，不可能全部存储在内存中，故要存储到磁盘上 。

2. 索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数（为什么使用B-/+Tree，还跟磁盘存取原理有关，具体看下边分析）。

3. 局部性原理与磁盘预读，预读的长度一般为页（page）的整倍数，（在许多操作系统中，页得大小通常为4k）。

4. 数据库系统巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样 每个节点只需要一次I/O
   就可以完全载入，(由于节点中有两个数组，所以地址连续)。而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性

6.采用平衡二叉树和B树，数据查询的对比

采用平衡二叉树和B树，数据查询的对比

7.MySQL的B-Tree索引和Hash索引的区别

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引。

可能很多人又有疑问了，既然Hash索引的效率要比B-Tree高很多，为什么大家不都用Hash索引而还要使用B-Tree索引呢?任何事物都是有两面性的，Hash索引也一样，虽然Hash索引效率高，但是Hash索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。

(1)Hash索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。 　　由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。

(2)Hash索引无法被用来避免数据的排序操作。 　　由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算;

(3)Hash索引不能利用部分索引键查询。 　　对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。

(4)Hash索引在任何时候都不能避免表扫描。 　　前面已经知道，Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

(5)Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。 　　对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

8.InnoDB和MyISAM存储引擎

8.1存储引擎的概念：

1.存储引擎其实就是如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。

2.MySQL中的数据用各种不同的技术存储在文件（或内存）中，这些技术中的每一种技术都使用不同的存储机制，索引技巧，锁定水平并且最终提供广泛的不同功能和能力。在MySQL中将这些不同的技术及配套的相关功能称为存储引擎。

查看数据库存储引擎

1.show engines//查看MySQL支持的存储引擎 
2.show variables like '% storage_engine'//查看默认支持的存储引擎 
3.show create table tablename //查看某个表使用的存储引擎 
4.show table status from database where name="tablename"// 查看某个数据库中某个数据表存储引擎

常用存储引擎的特点
MySQL中常用的几种存储引擎：MyISAM、InnoDB、bdb、MEMORY，对比如下：

博主上的MyISAM&InnoDB不同

菜鸟上两者的不同

9.索引底层实现原理

索引底层实现

10.数据库索引在什么情况下失效

原文博主
1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

2.对于多列索引，不是使用的第一部分，则不会使用索引

      alter table student add index my_index(name, age)   // name左边的列， age 右边的列                                                              

       select * from student where name = 'aaa'     // 会用到索引

       select * from student where age = 18          //  不会使用索引

3.like查询是以%开头

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
查看索引的使用情况：
show status like ‘Handler_read%’;

注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询低效

11.索引的优化

csdn索引优化（左前缀法则什么的）
csdn原文博主的索引优化
简书索引优化

12.sql语句的优化

①能用到索引尽量用到索引.对索引的优化实际上就是sql语句的调优

②任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

③尽量使用where,而不要使用having

④尽量使用多表查询,不要使用子查询

⑤where后的and.or左右执行顺序是从右至左

运算符为and时–尽量把为假的放在右边

运算符为or时–尽量把为真的放在右边

13.补充：

1.explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法，在select语句前加上explain就可以了，如：

explain select * from statuses_status where id=11;

2.索引的创建查询
索引的创建查询
有一组合索引（A,B,C），会出现哪几种查询方式？
A
AB
ABC
待补充区域