索引（一）索引的本质

索引（一）索引的本质

先看一个sql

SELECT
	count(*)
FROM
	op_log
WHERE
	method = "updateActivity"
AND operator_id ="shinan"
AND create_time > 1533819466000
AND create_time < 1543819466000
AND type=1;

查询效率很慢：


1、如何建立索引？

2、索引的顺序如何？

What is index

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。简单说：索引是一种数据结构。

Why use index

没有索引的情况下全盘扫描

磁盘IO和预读

索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作。与主存不同，磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。

1.将磁盘想象成一张黑胶唱片


2.盘片上有很多磁道，数据就存在磁道上。磁盘可以是单片，也可以多片组成盘组。每个盘片分2个面。盘片装在一根主轴上，绕主轴旋转。当磁道在磁头下通过时，磁头对磁盘进行数据读写。


3.各个盘面上半径相同的磁道组成了一个圆柱面，我们称为柱面。因此，柱面的个数也就是盘面上的磁道数。

磁盘读写原理

磁盘读写某一指定数据分3个步骤

1 磁头定位到磁道。


2 选好磁道之后，等待扇区旋转到磁头。


3 磁头定位完成，开始读或写操作


根据上面的3个步骤，可以知道读写某一指定数据的时间消耗同样有3部分时间组成

寻址时间：完成上述步骤(i)所需要的时间。这部分时间代价最高，最大可达到0.1s左右。

旋转时间：完成上述步骤(2)所需要的时间。由于盘片绕主轴旋转速度很快，一般为7200转/分(电脑硬盘的性能指标之一, 家用的普通硬盘的转速一般有5400rpm(笔记本)、7200rpm几种)。因此一般旋转一圈大约0.0083s（以7200rpm为例，转一圈的时间是1/240，大约为0.004s）。

传输时间：往磁盘传送或从磁盘传送的时间

执行一次IO的时间可以执行几十万条指令

局部性原理和磁盘预读

当一个数据被用到时，其附近的数据也通常会马上被使用。

程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

索引的数据结构

• 索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数
• B+树(一个高度可控的多路搜索树)

B+树

• 非叶子节点只存储键值信息
  
• 所有叶子节点之间都有一个链指针
  
• 数据记录都存放在叶子节点中

1. B+Tree的高度一般都在2~4层
2. 查找一个值，IO次数取决于B+数的高度
3. 磁盘块的大小就是页的大小，索引字段越小，内节点存储数据越多

SELECT
	count(*)
FROM
	op_log
WHERE
	method = "updateActivity"
AND operator_id ="shinan"
AND create_time > 1533819466000
AND create_time < 1543819466000
AND type=1;

建立method,operator_id,type,create_time联合索引

假设还有以下查询：

select * from op_log where method = "updateActivity" and type = 12;

如何建立索引？