SQL优化--索引的创建

1、mysql的架构

在不同层面上采取优化策略是不一样的。

mysql整体上有四层

我们重点

本次主要的内容：主要是基于存储引擎的索引优化。主要通过分析索引的存储结构，理解索引的原理。

2、mysql InnoDB引擎索引储存结构

B+树

关于数据结构的模型，可以使用这个工具，查看数据结构的变化

Data Structure Visualization

B+的特点

1、B+ 是一个多路平衡查找树（和二叉树相比是多路的）

2、B+树非叶子节点只用于索引，不存储数据（B树中各个节点存储的都独立存储数据。）

3、B+树使用叶子节点存储数据

4、叶子节点之间通过双向链表来彼此链接，叶子节点内的数据按照顺序使用单链表连。

InnoDb中B+使用的基本信息

索引实际上是存储在文件上的，确切的说是存储在页结构中的。InnoDB将数据划分为若干页，Mysql页的默认大小是16kb.可以使用下面的命令进行查看。

mysql中磁盘与内存交互的基本单位是页，这表示我们在磁盘与内存之间进行数据交互，最少是一页，并且每次交互都是整数页。即使我们数据存储只存储了一行，数据库I/O的操作单位也是一页。这样设计其实也是为了提高效率，毕竟I/O的时间消耗很大，不可能读、写一次数据就进行一次磁盘的I/O操作。

在InnoDB存储引擎中，我们假设主键索引使用BigInt，占8个字节，再加上6字节的页指针。因此，一页16KB的大小，可以存储的索引节点数量为：

(16KB - 页头20字节) / 14字节 =1170个索引节点,在使用主键索引的情况下,一页可以存储的主键索引节点数量1170索引节点。

加上每行数据占用的1K。那么3层数据可以存储的数据1170*1170*16=21,902,400。非常可观。

由此我们认为数据超过2千万，是要考虑分库分表的问题。

主键索引的存储

主键索引在叶子节点中直接包含了该行数据的全部列，这种索引格式被称为聚集索引

查询过程：

存储引擎会先根据查询条件在主键索引树上定位到对应的叶子节点，叶子节点中直接包含了该行数据的全部列，然后将查找到的数据返回给客户端，不需要再回到数据表中查找一次。使用聚集索引可以减少一次IO。

非主键索引的存储

叶子节点存储了索引列和数据行的主键。

查询的过程：

1. 首先在二级索引中找到叶子节点对应的数据主键值；
   
2. 根据这个主键值去聚集索引中找到真正对应的数据行。。
   

所以这里需要两次 B+ Tree 查找。

查询流程如下：

联合索引的存储结构

数据存储：一个索引出错了多个字段的列，排序优先按照最左侧面的字段。如上，优先按照name排序，在name排序的基础上，在进行age排序。

如果查询的只查询索引列，这个时候就不需要回表查询了，换句话说要查询的列已经被索引列覆盖。这也就是覆盖索引

思考：

1、理解一下最左匹配原则

2、数据库主键的设计原则

3、数据库NULL值的处理

讨论

1、联合索引应该如何建立

2、select * 查询有哪些问题

3、还有哪些常见的需要的SQL问题

本次主要分享索引的存储，后面分享优化器的使用和explain工具的使用