MySQL底层原理与性能调优相关解析

MySQL底层原理与性能调优相关解析

一、索引的本质

1. 索引是帮助MySQL高效获取数据的排好序的数据结构
2. 索引数据结构
   二叉树
   红黑树
   Hash表
   B-Tree

二、B树剖析

1、B-Tree

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列
  

2、B+Tree

• 非叶节点不存储data，只存储索引（冗余），可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能，如下图中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

查找元素过程如下，比如要查找30：

• 先把上图中第一行的根节点索引加载到内存，也就是我们的RAM（即内存）中，然后在内存中查找，因为是排好序的，可以折半查找，效率非常高。如上图中，30在15和56之间，所以把15和56之间的白框也就是下一级节点的磁盘文件地址定位
• 把15和56的磁盘文件地址定位到的索引 再次加载到内存中，继续折半查找，找到20和49之间的白框（也就是下一节点的磁盘文件地址）
• 找到20和49的磁盘文件地址，加载到内存，再次查找定位到30

扩展：

 a.图中第一行的结构：索引、磁盘文件地址、索引、磁盘文件地址、...、...
 b.15是bigint类型，占用8个字节，15后面的磁盘文件地址，MySQL给分配的6个字节，加起来总共14个字节；
 c.通过在MySQL执行SHOW GLOBAL STATUS like 'Innodb_page_size';可以查到16384字节，大概能存放16384/(8+6) = 1170个索引。
 d.假如B+Tree的高度为3，则可以存放1170*1170*16 = 21902400，可以存放两千多万数据

三、MySQL数据库引擎

常用的存储引擎包括MyISAM和InnoDB

1、MyISAM

• MyISAM的索引文件和数据文件是分离的（非聚集）

MySQL的数据库存放路径：
是存储在安装根目录下的data文件夹下，表明和文件名一一对应

MyISAM的存储结构有三个：表名.frm、表名.MYD、表名.MYI

• 表名.frm：存放的是表结构
• 表名.MYD：存放的是数据文件
• 表名.MYI：存放的是索引数据，一级索引、二级索引等

2、InnoDB

• 表数据文件本身就是按B+Tree组织的一个索引文件
• 聚集索引：说白了就是叶节点包含了完整的数据记录
• 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
• 为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）
  
  图中第三层指针的作用

（1）为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

• 如果不建立主键的话，会默认建立一个主键
• 建立主键的话，可以自动建立主键索引来维护数据
• 不建议使用uuid作为主键，而使用自增主键

InnoDB的存储结构有三个：表名.frm、表名.ibd

• 表名.frm：存放的是表结构
• 表名.ibd：存放的是数据文件

四、Hash剖析

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+树索引更高效
• 仅能满足 “=” 、“in”，不支持范围查询
• hash冲突问题

上图中col3列 Hash存储结构如下图：

五、索引相关

索引最左前缀原理

例如：
建立索引：

KEY 'idx_name_age_position ('name','age','posikon ' ) USING BTREE

判断一下查询语句是否走上述索引：

-- 走索引
EXPLAIN SELECT * FROM employee WHERE name = 'Bill' and age = 31;

-- 不走索引
EXPLAIN SELECT * FROM employee WHERE age = 30 AND pdsition = 'dev' ;

-- 不走索引
EXPLAIN SELECT * FROM employee WHERE position = 'manager';

原理如下图：