【Mysql性能调优】索引底层数据结构与算法

图灵学院公开课视频：性能调优系列 深入理解Mysql索引底层数据结构与算法【图灵课堂】-学习视频教程-腾讯课堂 (qq.com)https://ke.qq.com/course/2770537?taid=9867433962260073

一、什么是索引

场景：优化慢sql查询

定义：索引是帮助Mysql高效获取数据的排好序的数据结构

索引存储在哪里：

        a. 安装目录/data目录中，db库与data数据文件一一对应.

        b. 库文件夹下每张表都对应多个文件，文件根据创建表时所选定的存储引擎的不同而不同：

                MyISAM：

                        dbtable.frm存储表定义的结构,

                        dbtable.MYD存储表数据,

                        dbtable.MYI存储索引（默认是主键）

                InnoDB：

                        dbtable.frm 存储表结构

                        dbtable.ibd 存储索引+数据

索引的底层数据结构：

        常用索引的数据结构有：

                二叉树（红黑树），

                HASH，

                BTREE（mysql使用B+TREE来存储索引,B+ binarysearch trees）

    二叉树

 二叉树教学演示工具  : Data Structure Visualization (usfca.edu)https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 红黑树——二叉平衡树

 在数据足够多的情况下，红黑树的高度太高（例如：=100万，1000万时，n持续增加，高度无法控制）

B-Trees(多叉平衡树)

—— 子节点4的左边都是<4的节点，右边都是>4的节点，其他节点都同理

 

顺序访问指针的优点：提高范围查找的性能，非叶子节点快速定位 

非叶子节点不存储data的优点：关键索引值

Mysql存储引擎：MyISAM，InnoDB（存储引擎都是形容数据库表的）

不同的存储引擎的索引的B+Tree结构也有一些区别

 数据查找：

MyISAM——MyISAM索引文件和数据文件是分离的（MYI和MYD）。

Tips：MyISAM数据表结构中，使用非id/自定义的主键作辅助索引/二级索引，MyISAM数据库的主键索引和二级索引的结构是一样的

InnoDB——

Q：为什么InnoDB的索引和数据存储在一个文件中？

A：这个文件本身就是B+树的结构进行存储的，在使用索引进行查询时，索引节点中包含了完整的数据记录，不再需要去MYD文件中去寻找数据。此种方法叫做“聚集索引”

面1：为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

A：

1. 树高度越小，查询高度越低（性能更高），B+树(高度一般是1-3，特殊情况可能会到5)优于BTree的原因是：数据库一次查询(一次I/O)只能是页(page)的整数倍，即4k的整数倍。这里总共有3次I/O(查询coll为49这个数据时)

Mysql一次I/O的大小的默认设置： SHOW GLOBAL STATUS like 'Innodb_page_size'，结果是16384=16kb，即4页大小的数据。在Innodb(B+树)的非叶子节点上只存储索引值，叶子节点存储data值，即同样大小的磁盘空间可以存储更多的数据 

 总结：

2. 因为InnoDB本身就是B+树的结构(聚集索引结构)，因此必须要有主键（且在建库时mysql自动默认建唯一标识的col为主键，如果没有则增加id列为主键索引）。而通常有的设计中使用UUID作为主键索引，这种设计存在的问题：UUID更占空间（相对Int来说），因此树的高度会更高；在查询比大小的过程中，过程更复杂（UUID-> ASCII），且UUID未必是递增的，插入数据时，频繁调整索引树的结构。所以索引使用整形比使用UUID更高效

面2：除了主键索引之外，还有非主键索引|二级索引即辅助索引，InnoDB数据库的非主键索引结构的叶子节点存储的是主键值。原因是什么？ 

A： 二级主键的叶子节点存放的是主键的id（非聚集索引），原因是：保持数据一致性和节省存储空间

联合索引的底层存储结构长什么样？

索引的最左前缀原理/左列原理：

根据索引键的先后顺序键排序的数据结构，即先排name，name相同的情况下，对age索引键进行排序，在name和age一样的情况下，对position索引键进行排序

如下3条语句，哪一条会走索引？

a) EXPLAIN SELECT * FROM employees WHERE ·name· = ‘Bill’ and age=31;

b) EXPLAIN SELECT *  FROM employees WHERE age=30 and position=' dev';

c) EXPLAIN SELECT *  FROM employees WHERE position=' manager';

答案是a。原因是联合索引在使用时必须遵循左列原理，从name开始，不可以跳过name或者age直接根据最后一个索引键进行查询