一、存储引擎
1.1 MyISAM
一张表生成三个文件
- xxx.frm：存储表结构
- xxx.MYD：存储表数据
- xxx.MYI：存储表索引
索引文件和数据文件是分离的（非聚集）
select * from t where t.col1 = 30;
先去t.MYI文件查找30对应的索引所在磁盘文件地址，去t.MYD文件找出对应磁盘文件地址的数据
1.2 Innodb
一张表生成两个文件
- xxx.frm：存储表结构
- xxx.ibd：存储索引和数据
索引和数据（索引行对应的所有列数据）不是分离的（聚集）
二、索引
索引是帮助MySQL高效获取数据的排好序的数据结构
2.1 索引数据结构
- 数组
等值查询需要一个一个去匹配，效率不高。
不适合范围查找、排序、统计、模糊查询等常见操作。
数组的通病：插入和删除性能差。
- 二叉查找树
大数据情况下，树的层级很深，性能差。
最坏的情况可能退化成链表，查询效率更差。
不适合范围查找、排序、统计、模糊查询的常见操作。
不适合频繁的插入和删除。
- 二叉平衡树
大数据情况下，树的层级很深，性能差。
不适合范围查找、排序、统计、模糊查询的常见操作。
不适合频繁的插入和删除。
- 红黑树
与二叉平衡树一样。
- Hash表
对索引的key进行一次hash计算就可以定位出数据存储的位置，多用于MEMORY存储引擎。
等值查询效率高（=，IN），不能排序，不能进行范围查询，hash冲突问题。
- B-Tree
节点具有相同的深度，页节点的指针为空
所有索引元素不重复
节点中的数据所有从左到右递增排列
数据有序，范围查询
- B+Tree（B-Tree变种）
非页子节点不存储data，只存储索引（冗余），可以放更多的索引
页子节点包含所有的索引字段
页子节点用指针（双向指针）连接，提高区间访问的性能
MySQL加载的时候，一二层叶子数据（冗余索引）加载在内存中，底层叶子数据存储在磁盘
数据页是Innodb内部存储数据的基本单位，默认16K：show variables like 'innodb_page_size'。
在计算机中，磁盘存储数据最小单位是扇区，一个扇区的大小是512B。
文件系统中，最小单位是块，一个块大小就是4K。
Innodb一颗B+树可以存储多少行数据？
假设B+树的高度为2的话，即有一个根节点和若干叶子节点。
这颗B+树存储的总记录数=根节点指针数*单个叶子节点记录行数
根节点可以存储多少指针？
我们假设主键ID为bigint类型，长度为8字节，而Innodb源码中指针大小设置为6字节，指针大小：8+6=14字节。根节点存储指针数：16K/14B=1170。
叶子节点中每个数据页可以存储多少行记录？
如果一行记录的数据大小为1K，那么单个叶子节点可以存储的记录数：16K/1K=16。
因此，一颗高度为2的B+数，能存储记录数：1170*16=18720。
同理一颗高度为3的B+树，存储记录数：1170*1170*16=21902400。
总结：B+树高度为1-3层，已经满足千万级别的数据存储。
2.2 联合索引
-- 根据最左匹配原则不会走索引
select * from t_employees where age=30 and position='dev';-- 先根据索引获取索引对应记录的主键，然后根据主键回表获取主键对应的数据记录
select * from t_employees where name='Bill' and age=30 and position='dev';2.3 索引下推
索引下推（Index Condition Pushdown，简称ICP）是MySQL 5.6引入的一种索引优化技术，它可以在索引遍历过程中，对索引字段先判断，过滤掉不符合条件的记录后再回表，可以有效的减少回表的次数。
当一条SQL使用了索引下推之后，在explain执行计划中，Extra列中出现Using index condition的信息。文章来源：https://www.toymoban.com/news/detail-685701.html
explain select * from t_employees where name='Bill' and age=30 and position='dev';
文章来源地址https://www.toymoban.com/news/detail-685701.html
到了这里，关于Mysql优化原理分析的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
