1 深入理解MySQL底层数据结构与算法

目录

        1 索引简介

        2 索引数据结构

        2.1 二叉树

        2.2 红黑树

        2.3 B-树

        2.4 B+树

        2.5 Hash

        3 实战分析

        4 存储引擎

        4.1 MyISAM

        4.2 InnoDB

        5 联合索引

---

1 索引简介

        索引：帮助MySQL高效获取结果的排好序的数据结构

        索引数据结构：二叉树；红黑树；Hash表；B树

        MySQL表数据分布情况：在磁盘上随机分布，在内存中定位节点的时间 << 从磁盘Load到内存的时间（磁盘IO）

        索引存在的目的是为了减少磁盘IO的次数

2 索引数据结构

        2.1 二叉树

                1 产生：想到查找，就会想到时间复杂度较低的二叉搜索树

                2 缺点：数据是自增的情况下，会退化成链表

        2.2 红黑树

                1 产生：解决二叉树退化成链表的问题，插入数据时，会对树进行平衡

                2 缺点：数据量一大，树的高度会很大（500w，1000w+的数据会有很多次的磁盘IO）

                

        2.3 B-树

                1 产生：减少树的高度（磁盘IO）

                2 特点：树的每层尽可能平铺更多节点；每个节点均有数据和索引

                3 优点：频繁访问的数据如果都接近根节点，磁盘IO次数就会很少（B+树的数据均在叶子节点上）；仅支持随机检索，而不支持顺序检索

                4 缺点：没有解决遍历效率低下的问题（遍历到叶子节点后，下次搜索需要回到根节点）

        2.4 B+树

                1 产生：同时支持随机检索和顺序检索

                2 特点：非叶子节点不存Data，只存索引；叶子节点包含所有索引字段；叶子节点用双向指针相连

                3 优点：查询效率比B-树稳定；B+树叶子节点通过指针相连且按顺序排序，支持范围查找；增删节点效率高（叶子节点操作即可）

                4 缺点：某些场景，如：等值查询没Hash快

        2.5 Hash

                1 特点：对索引Key进行一次Hash运算就可以定位数据存储的位置；能满足 = in 不支持范围

                2 优点：某些时候Hash要比B+树更高效

                        查找 name = "liuxin"

                        hash("liuxin")，理想情况下一次hash就能定位，这种情况比B+树高效

                3 缺点：不支持范围查询；会存在Hash冲突

        综合考虑，MySQL采用B+树作为索引

3 实战分析

        MySQL在设计时，一个节点存16KB数据（MySQL综合考虑的结果）

        B-树每个节点占用1KB，每层共16个节点；16的？次方≈2000w；很显然由于B-树的特点，导致B-树的高度会很高

        这就是B+树为什么单个元素只存放索引而不存放数据，尽可能让单个节点存放更多的索引元素，降低树的高度

        1 是否可以将整张表的所有索引全放到B+树的同一层级上？

                不可以，内存可能会撑爆；内存数据大时进行数据比对也不会很快

        2 MySQL对B+树叶子节点的大小设置为16KB，能放多少数据？

                假如是bigInt = 8B + 存放下个元素的地址6B => 16KB/（8+6）B≈1143个数据；高度为3的B+树最终能存放：1143*1143*16（数据+索引占1KB） ≈ 2000多w

        3 千万级别的表，走索引为什么快？

                MySQL索引采用B+树，2000多w的数据，树的高度为3，磁盘IO为3次；若不创建索引，多次磁盘IO，可能需要耗时几十秒

        数据达到亿级别就应考虑分库分表；理论上B+树的高度可以为>3：1143*1143*1143*16≈百亿

4 存储引擎

           存储引擎针对于数据库表

        4.1 MyISAM

        myisam存储引擎：非聚簇（索引和数据分离）

        执行一条查询语句：MYI 索引文件查找，定位到某个具体元素后：根据数据（MYD文件数据的地址）去MYD文件去查找具体数据

        4.2 InnoDB

        1 表数据文件本身就是按B+树组织的数据文件

        2 聚集索引-叶子节点本身包含了表的所有数据（索引和数据在一起）

        3 为什么建议innodb表必设置一个主键，且推荐自增整型

                索引和数据在一个文件，在创建数据时就会根据一个字段去创建B+树（若没有主键索引，MySQL会自动选择不重复的列构建索引，如果不满足，MySQL会创建一个隐藏列RowId作为索引）；选用整型比较速度快且占用内存比字符串小

        4 为什么非主键索引，叶子节点存放主键值？

                一致性和节省空间；由于innodb数据和索引在一个文件中，且根据主键构建了一个索引树，非主键索引叶子节点数据存放主键值

        5 除主键索引外的其它索引均是非聚集索引，需要进行回表操作

5 联合索引

        例子：KEY 'idx_name_age_position'('name','age','position') USING BTREE

        索引最左前缀原理：构建索引时按字段顺序构建，name>age>position；查询条件不能跳过 name 直接查询 age和position