mysql-索引

 1.索引的定义：

        索引是帮助MySql高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

2.为什么需要索引

        试想我们如果随机的存放数据，那么查找的时候就需要挨个遍历数据，最终拿拿到数据，如果这时候还需要将数据排序，取其中某一段，天呐，这可真是个灾难。这时候聪明的人类就在想我们可以可以以某种特定的方式来组织我们的数据呢？现实生活中我们通过拼音将无序的汉字变得有序，通过目录直接跳转到具体的页码，在数据库中，我们通过hash算法将无序的数据变成有序，各种数据结构（B+树，hash表等）来实现类似目录的功能。这里的"目录"即使我们所要探索的索引啦。

        索引可以提高数据库的查询效率，索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySql的性能优化问题。

3.索引的优缺点

        优点：

                可以显著提高查询效率

        缺点：

                索引需要额外占用存储空间

                索引会降低数据的更新速度，因为数据变更时，需要同时更新索引。

 4.索引的分类    

         从应用层次来分：

•                 主键索引：设定为主键后，数据库自动建立的索引。

                         创建示例： ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

•                 单值索引：除主键索引外，只包含一个列的索引，也称为普通索引，一个表可以有多个单列索引。

                        创建示例 ：ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

•                 唯一索引：设定为唯一列后，数据库自动建立的索引。

                        创建示例：ALTER TABLE `table_name` ADD UNIQUE (`column`)

•                 复合索引：即包含多个列的索引

                       创建示例：

        ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

                                注意点：关于复合索引有个注意点，最左匹配原则。

•                全文索引：在定义的列上支持全文查找的索引。可定义在类型为CHAR,VARCHAR,TEXT字段上

                        创建示例：ALTER TABLE `table_name` ADD FULLTEXT ( `column1`)

        其中主键索引又被称为主索引，除主索引外的索引被称为辅助索引

        从存储结构上来划分：

                B+Tree，Hash索引

        从数据的物理顺序与键值的逻辑（索引）顺序关系：

               聚集索引，非聚集索引

           关于聚簇索引与非聚簇索引的详细知识，可以参照这篇博客

        聚簇索引和非聚簇索引的关系https://www.cnblogs.com/wsming/p/13864292.html

        这里需要说明，以mysql为例，索引是在mysql的存储引擎层中实现的，而不是应用层。不同的存储引擎支持不同的索引类型（全文索引5.6版本之前只有MyISAM支持，5.6之后均支持）。 

        小知识：虽然传统的关系型数据库也支持全文索引，但是效率比ES之类搜索引擎低了很多，因此很多时候引入ES是更好的选择。       

 6.对比常用的数据结构

        我们都知道Mysql主要的数据结构式B+树和Hash表。那么我们为什么要采取这两种数据结构呢？下面我就带大家一起来探讨下常见的数据结构以及各自的优缺点。

        Hash表

                   说到hash表我们先说一说hash算法，hash算法又称之为散列算法，取余就可以算作一种最原始的散列算法，我们可以将0-99这一百个数字分别对10取余，根据余数分别放入标号为0到9的这是个通中，我们取值的时候通过逆向运算只需要先对这个数字取余我们就能直接定位到桶，然后再遍历桶中的数据就可以找到对应的数据，理论上它的时间复杂度为O(1)，而不使用任何数据结构存放查询复杂度为O(n).

        hash表就是一张一列存放键的hash值和真实值地址的对应关系的表格。如下图

        

 hash表的有点：时间复杂度为O1,取值快

缺点：存在hash碰撞，不同的hash值可能相同，不支持范围查找，不支持排序。

        二叉树

          说明：本节部分动图取至博客4 张 GIF 图帮助你理解二叉查找树 - 走看看

                二叉树的特点：

1. 一个节点只能有两个子节点，也就是一个节点度不能超过2
2. 左子节点 小于 本节点，右子节点大于等于 本节点

     下面我用两张动图来演示二叉树的插入和查询

        二叉树插入过程

        

二叉树读取过程

从动图来看，二叉树确实有效的提高了数据检索效率，但是试想极端情况，若依次存入的数据是递增的主键，那么我们二叉树会退化为线性链表，检索速度降低。如下图所示

        

        

                优点：一般情况下二叉树有效的提高了数据检索效率，可以实现范围查找、数据排序

                缺点：极端情况，二叉树会退化成线性列表，查询时间复杂度为O(n)

        平衡二叉树（红黑树）

        平衡二叉树是二叉树的改良，为了避免二叉树的极端情况，我们在插入时候有左右子树高度差大于1的时候，会旋转，从而使左右子树高度差始终小于等于1.

AVL 树的特点：

1. 平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。
2. 很好的查找性能，不存在极端的低效查找的情况。
3. 可以实现范围查找、数据排序。

        平衡二叉树插入过程

        平衡二叉树的查找过程与二叉树一致。

        平衡二叉树的优点：二叉树有效的提高了数据检索效率，支持排序

        平衡二叉树的缺点：为了保证平衡，在插入数据的时候必须要旋转，通过插入性能的损失来弥补查询性能的提升。时间复杂度为O(logn)，但是由于没有限制数的层高，会导致IO次数过多，大家都知道IO是计算机的性能瓶颈。

   B-树

        磁盘 IO 特点：从磁盘读取1B 数据和 1KB 数据所消耗的时间是基本一样的(空间局部性与时间局部性决定)，根据这个思路，可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就尽可能多的加载数据到内存，影响数据查询时间的是树的高度，高度越高，比较的次数越多，尽量把树的高度降低，这就是B树的设计原理了

        为了解决树过高导致的频繁IO问题，人们想到了使用多叉树来解决（一个节点分多个叉，每个节点再多存几个数）。B树应运而生。

B-Tree特点:

1. 叶节点具有相同的深度。
2. 节点中的元素从左向右递增排序
3. 所有的元素不重复

B-数的插入流程：此处最大度数为3（树的度数是指数允许拥有的最大子树个数（其实就是分叉的个数））

        B-树的优点：提高了数据检索效率，支持排序

        缺点：非叶子节点中存有数据，可以近一步优化。

B+树

        B+树是在B树上的对于现有硬件条件的进一步优化权衡形成的一种数据结构。

        有了B树知识铺垫，一个树节点我们应该尽可能的包含更多的子节点，但又不能超过一个磁盘页（16kb）的大小。发现B树的节点中还包含了一些关键字信息data（对于聚簇索引来说，data存的是数据行，对于非聚簇索引来说，data存的是主键的值），这个data也占据着一定的数据量，如果把data去掉，这样就又能多加很多子节点了。这也就是B+树的核心思想。

        例如有如下一张采用InnoDB作为存储引擎的表-person

 

        下面用一张图来简单描述它在mysql中是如何存储的

       1.首先InnoDB存放数据时候是根据主键顺序一列一列在内存中依次存储的（聚簇索引）。

                此处留一个小小的问题？ 如果字段长度设置为20个字节，实际存储的数据不足20个字节，那么多出来的空间会被浪费掉吗？答案：具体看字段类型，如果是Char类型，那么多出的空间会填入空格。如果是varchar，那么后面的数据会紧挨着存储。

        2.我们来看第一页第一条数据，绿色1表示主键Id，黄色中的1,23对应具体列的值，蓝色P为指向下一条数据Id的指针，此时数据结构是一条单向链表。

        3.mysql管理数据是基于页的管理，每一页大小16KB，每一页至少要存储两个节点（因此使用InnoDB的表格每一列字段长度之和与指向下一节点的指针长度之和不能超过8KB）。

        4.然后为了提高查询数据，Mysql将保存具体数据的页作为叶子节点，在其上构建了树枝节点（即页目录），树枝节点存储空间大小也为16KB，页目录只存储每一页第一列数据的Id和指向第一列位置的指针。

        5.重复4的步骤，创建页目录的页目录，直到收为一个根节点。一般来说普通数据量的表最多加上叶子节点三层就足够。使用时，根节点是缓存在应用程序内存中的，可以减少一次IO；

        为什么说一般的表三层就足够了?

                我们来计算一下极端情况，当一列数据非常大的时候，也就是一个叶子节点只存两列数据的情况 

                叶子节点存放列个数 ： 2

                二级页目录节点存放页个数： 16 * 1024 / (4+8) = 1365.33333 （id占4个字节，指针占8个）

                根节点存放的页目录的个数 ： 1365.33333

                总记录条数 ： 1365.3333* 1365.3333 *2 = 3,728,270.221312

                由上可知最极端的情况下，innoDb引擎下三级目录可以存放三百七十多万条数据，一般情况下三级直接就能存千万到亿级的数据了 。千万以上最多四级，再往上就需要分库分表或者采取其他措施了。

       查找过程举例：

                select * from person where id = 2；

        1.首先mysql拿到id = 2 去页目录中查找，发现2大于大于第一页第一条数据的id 1，小于第二页第一条数据的id 4.因此得出目标数据存放在第一页。

        2.然后拿到第一页第一条数据的指针，跳转至第一页，然后定位到第一页中id = 2 的数据位置。

       上图只阐述了核心原理，实际mysql还做了很多优化，例如页内数据的查找添加了算法（增加查询速度），以及对兄弟叶子节点的引用（排序分页时方便定位前后节点）。如下图所示

 B+树  与 B树的区别：

        1.非叶子节点不能存储实际数据

        2.所有叶子节点之间都有一个指针

        3.数据都记录在叶子节点中

 聚簇索引与非聚簇索引

        关于聚簇索引和非聚簇索引，这篇文章写的非常好。强烈推荐：

聚簇索引和非聚簇索引的关系https://www.cnblogs.com/wsming/p/13864292.html

         聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（上述的主键索引就是聚簇索引）

        思考:为什么一张表只能有一个聚簇索引？

        非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置（将主键索引中叶子节点中的数据换成指向数据的应用那么就成了非聚簇索引）