MySQL执行的Io操作及索引基础

1. 索引介绍

1.1 认识索引

索引优化，可以说时数据库相关优化，尤其时quey优化中最常用的优化手段之一。对于大多数人来说只是知道索引可以加快query执行的更快，但不知道为什么会很快，以及它的实现原理、存储方式、以及不同索引之间的区别等就不怎么清楚。

书记两一般再1000W之内的可以使用到索引进行优化，超过1000W的数据量就要考虑分表分库操作。

再MySQL中，主要有4中类型的索引，分别为：B-Tree索引、Hash索引、Fulltext索引和R-tree索引。B-tree是整个数据库界中，比较常用的类型

索引功能包括：

• 保持数据的完整性
• 优化数据访问性能
• 改变表的链接（join）操作
• 对结构进行排序
• 优化聚合数据操作

1.2：数据完整性

数据完整性分为四类：实体完整性（Entity Integrity）、域完整性（Domain Interiry）、参照完整性（Referential Integrity）、用户自定义完整性（User-defiedIntegrity）。

1.2.1 实体完整性（其实就是主键）：

规定表中的一行，在表中是唯一的实体，一般是通过定义主键的形式来实现的，实体完整性要求每一个表中的主键字段都不能为空或则重复的值，实体完整性指表中行的完整性，要求表中的所有行都有唯一的标识符，成为 主关键字，主关键字是否可以修改，或整个列是否可以被删除，取决于主关键字域其他表之间的要求的完整性（一般每个表中都有规定一个唯一自增主键，主键必须是唯一有序的）

1.2.2 参照完整性（join，链表或多表设计）

当更新、删除、插入一个表中的数据时，通过参照引用相互关联的另外一个表中的数据，来检查对表的数据操作是否正确，简单的说就是表间主键外键的关系（其实就是join，链接其他表的时候，需要外键关联的主键）

1.2.3 域完整性（其实就是字段的数据类型）

指数据库表的列（即字段）必须符合某种特定的数据类型或约束，域完整性使针对某一具体关联数据库的约束条件，它保证表中某些列不能输入无效的指

Not Null 不能为空
unque 必须唯一的

1.2.4 索引俗语

类型	描述
索引技术	这个词是关于不同数据结构如何用不同的方法访问底层信息的理论 ，这些技术
索引实现	这个词是关于MySQL及各种存储引擎实现不同的数据结构技术的方法，例如，myisam引擎实现B-tree的方法和innodb实现的方法就有所不同
索引类型	索引索引类型包括：1. 主键2. 唯一键3. 非主码索引4. 全文索引以及空间类型每种类型支持在单一列、多列（也叫做混合列）或则列的一部分上定义的索引类型。最后这些索引类型中的一个或多个会成为所谓的覆盖索引

2 索引类型 – Btree介绍

B-tree索引是MySQL数据库中使用最为频繁的索引类型，特别是innodb中经常使用，在其他数据库中B-tree索引也同样是作为最主要的索引类型，这主要是因为B-tree索引的存储结构在数据库的数据检索中有着非常优异的表现。

如下就是tree（树）的基本结构对于这种结构来说到任何一个节点的最大路劲的长度都是完全相同的，但是对于数据库来说常用的主要是B-tree索引，这种索引的特点就是会把实际需要的数据都存放于tree的节点中，也就是说每一个节点中保存了索引的数值又加上了实际所对应的数据（还把每一页对应的数据也存放在节点中，其实就是地址）

而Innodb中的b-tree也就是这种结构，不过在这个基础上还是做了一点的额外的操作并称 b+tree，主要的操作点，在节点处不再存放数据，都放在了树的根处，而且同时还存放了它位置地址（而是把对应的数据存放在了子节点中）

在innodb存储引擎中，存在两种不同形式的索引，一种是Cluster形式的主键索引，另外的一种则是其他存储引擎存放形式基本相同的普通B-tree索引，这种索引在innodb存储引擎中被称为Secondary index（二级索引）

两种索引类型的关系，它们两则的区别主要是在于它们存储的数据不一样，主键索引在leafnodes存放的是page页地址，而二级索引存放的是对应的主键id，在结构上没有太多的区别

所以在innodb中如果通过主键来访问数据效率是非常高的，而如果是通过secondary index（二级索引）来访问数据的话，会根据二级btree获取到id在根据id查询数据（这个过程也可以称之为回表）

3. SQL-IO索引执行流程

1. 首先就是用户发送一条SQL通过客户端接受之后，交由解析器解析SQL创建对应的解析树之后（在解析器那一步，内部会进行创建解析树）
2. 然后优化获取对应的数据表的信息结构（获取索引对应表的结构信息）
3. 获取表中对应的数据表，首先就会去缓存中读取索引，如果没有读取到，就会通过IO读取磁盘中记录索引的信息并返回
4. 选择合适的索引：因为一个表会很多的索引，MySQL会对于每一个索引进行相应的算法推敲，然后再做相应的筛选留下最优最合适的索引，所以如果说索引的数量多的话会给查询优化器带来一定的负担（如果一条SQL语句有多个索引，内部会选择一个最后的作为索引搜索）
5. 因为在当前的索引为二级索引所以这个时候就会根据二级索引的btree获取到对应的id
6. 读取到的所对应的id之后再通过回表查询（二级索引因为只是存储了主键的id，并没有存储实际的数据，所以会拿到主键的id回表去查询数据）
7. 根据主键索引获取到对应的数据再磁盘中的位置（索引会一步步查询它对应在页中磁盘的位置并且返回数据）
8. 在获取数据之前会判断索引缓存的数据是否哦满足查询，然后再判断数据库缓存池以及缓冲区中是否有缓冲，如果有就返回，没有就会执行对应的执行计划，从磁盘中读取信息/

Hint：可以理解为SQL中的一个优化标识，再优化器中如果对于一条语句分析完了之后达到了优化的指标之后就会Hint记录，再执行SQL计划的时候就会判断这个值，没有就会读取所设计及读意向的统计信息，根据query记录写相应的计算分析

4. 理解btree - 二叉树算法

4.1 二分查找算法与平衡二分查找法

innodb存储引擎支持的哈希索引是自适应的，innodb存储引擎会根据表的使用情况自动挡为表生成哈希索引，不能认为干预是否再一张表中生成哈希索引（一般哈希索引比较少用）

b+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引，B+树结构构造类似于二叉树

二分查找法也成为折半查找法，用来查找一组有序的记录数组中的某一记录，其基本思想是：将记录按有序话排列，再查找过程中采用条约方式查找，即先以有序数列的中点位置为比较对象（就会先找到中间的数字），如果要找的元素直销与该重点元素，则将待查询列缩小为左半部分，否则为右半部分（则大于往右找，小于往左找）

比如：如下数据
5,10,19,22,33,44,48,55,60,68
-- 5, 10, 19, 22, 33, 44, 41, 55, 7, 51 --

现在打算查找60所在的位置，如果说我们通过顺序查找法的话，那么我们可能就需要查找9次也就是一个个去匹配的方式查找，那么二分法则是3次查找方式如下：

会先从 5，10，19，22，33，44，48，55，60，68 这个数组中查找中间值取整数页就是 (10 - 1) / 2 = 4.5  => 4
然后用60 与 33 对比发现比33大，这个时候需要去查找的区间就是 44，48，55，60，68 取中间值（5 - 1） / 2 = 2.5 =》 2
也就是与55进行对比，然后发现还是大于，那么继续往右找 60,68 取中间值(2-1) /2 =0.5 => 0
然后就匹配到了60这个数值，总共执行了3次

<?php
//二分查找法
function binSearch($arr, $search)
{
    $height = count($arr)-1;
    $low = 0;
    while ($low <= $height) {
        $mid = floor(($low + $height) / 2);//获取中间数
        if ($arr[$mid] == $search) {
            return $mid;//返回
        } elseif ($arr[$mid] < $search) {//当中间值小于所查值时，则$mid左边的值都小于$search，此时要将$mid赋值给$low
            $low = $mid + 1;
        } elseif ($arr[$mid] > $search) {//中间值大于所查值,则$mid右边的所有值都大于$search,此时要将$mid赋值给$height
            $height = $mid-1;
        }
    }
    return "查找失败";
}
//顺序查找
function seqSearch($arr, $k)
{
    foreach ($arr as $key => $val) {
        if ($val == $k) {
            return $key;
        }
    }
    return -1;
}
// 测试
$arr=array(5,10,19,22,33,44,48,55,60,68);

// echo binSearch($arr,44).'<br/>';
// echo seqSearch($arr,44).'<br/>';
?>