mysql底层书_mysql底层实现

MySQL的常用引擎

1，InnoDB

innodb 的存储文件有两个，后缀名为 .frm 和 .idb ,其中 .frm 是定义文件，.idb是数据文件

innodb 中存在表锁和行锁，不过行锁在命中索引的情况下才会起作用

innodb 支持事务，且支持四种隔离级别(读未提交，读已提交，可重复读，串行化)，默认为可重复读，而在oracle数据库中，只支持串行化级别和读已提交这两个级别，其中默认的为读已提交级别

2，Myisam

myisam 的存储文件有三个，后缀名分别是 .frm ，.myd ，.myi，其中 .frm 是定义文件，.myd 是数据文件 ， .myi 是索引文件

myisam 只支持表锁，且不支持事务。Myisam 由于有单独的索引文件，在读取数据方面的性能很高 。

存储结构

InnoDB 和 Myisam 都是用 B+Tree 来存储数据的。

mysql 的数据，索引存储结构

1，数据存储的原理(硬盘)

信息存储在硬盘里，硬盘是由很多盘片组成，通过盘片表面的磁性物质来存储数据。盘片表面是凹凸不平的，凸起来的代表1，凹下去的代表0，所以硬盘可以通过二进制的方式来存储信息。

硬盘是由盘片，磁头，盘片主轴，控制机电，磁头，盘片主轴，控制电机，磁头控制，数据转换器，接口，缓存。

2，数据读写的原理

硬盘在逻辑上被分为磁道，柱面以及扇区。

磁头靠近主轴接触的表面，即线速度最小的地方，是一个特殊的区域，它不存放任何数据，称为启停区或者着陆区，启停区外就是数据区。

在最外圈，离主轴最远的地方是 “0” 磁道，硬盘数据的存放就是从最外圈开始的。

在硬盘中还有一个叫 “0” 磁道检测器的构件，它是用来完成硬盘的初始定位。

3，访盘请求完成过程

1)确定磁盘地址(柱面号，磁头号，扇区号)，内存地址(源 / 目)：

当需要从磁盘读取数据的时候，系统会将数据的逻辑地址传递个磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。

2)为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点：

A. 首先必须找到柱面，即磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间。

B. 然后目标扇区旋转到磁头下，即磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

3)即一次访盘请求(读 / 写)完成过程由三个动作组成：

A. 寻道(时间)：磁头移动定位到指定磁道。

B. 旋转延迟(时间)：等待指定扇区从磁头下旋转经过。

C. 数据传输(时间)：数据在磁盘与内存之间的实际传输。

4，磁盘的读写原理

系统将文件存储到磁盘上时，按柱面、磁头、扇区的方式进行，即最先是第 1 磁道的第一磁头下的所有扇区，然后是同一柱面的下一个磁头……

一个柱面存储满后就推进到下一个柱面，直到把文件内容全部写入磁盘。

系统也以相同的顺序读出数据，读出数据时通过告诉磁盘控制器要读出扇区所在柱面号、磁头号和扇区号(物理地址的三个组成部分)进行。

5 ，减少 I/O 的预读原理

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费的时间，磁盘的存取速度往往是主存的几百分之一。

因此，为了提高效率，要尽量减少磁盘的 I/O。

磁盘往往不是严格地按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。

这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据一般来说也会被马上使用。

程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高(不需要寻道时间，只需要很少的旋转时间)，因此对于具有局部性的程序来说，预读可以提高 I/O 效率。

预读的长度一般为页(Page)的整数倍。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储分割为连续的大小相等的块。

每个存储块称为一页(在许多操作系统中，页的大小通常为 4k)，主存和磁盘以页为单位交换数据，当程序要读取的数据不在主存中时，会触发一个缺页异常。

此时系统会向磁盘发出读盘信息，磁盘会找到数据的起始位置并向后连续读取一页或几页的数据载入内存中，然后异常返回，程序继续运行。

MySQL 的索引

索引是一种用来实现 MySQL 高效获取数据的数据结构。

我们通常所说的在某个字段上建索引，意思就是让 MySQL 对该字段以索引这种数据结构来存储，然后查找的时候就有对应的查找算法。

建索引的根本目的是为了查找的优化，特别是当数据很庞大的时候，一般的查找算法有顺序查找、折半查找、快速查找等。

但是每种查找算法都只能应用于特定的数据结构之上，例如顺序查找依赖于顺序结构，折半查找通过二叉查找树或红黑树实现二分搜索。因此在数据之外，数据库系统还维护着满足特定查找算法的数据结构。

这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

MySQL 的 B+Tree

目前大多数数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B+ 树索引是 B+ 树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引。B+ 树中的 B 代表平衡，而不是二叉。

因为 B+ 树是从最早的平衡二叉树演化而来的。B+ 树是由二叉查找树、平衡二叉树(AVLTree)和平衡多路查找树(B-Tree)逐步优化而来。

二叉查找树：左子树的键值小于根的键值，右子树的键值大于根的键值。

AVL 树：平衡二叉树(AVL 树)在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为 1。

平衡多路查找树(B-Tree)：为磁盘等外存储设备设计的一种平衡查找树。

MySQL 的相关优化

MySQL 性能优化：组成、表的设计

开启查询缓存。避免某些 SQL 函数直接在 SQL 语句中使用，从而导致 Mysql 缓存失效。

避免画蛇添足。目的是什么就取什么，例如某个逻辑是只需要判断是否存在女性，若是查到了一条即可，勿要全部都查一遍，此时要善用 limit。

建合适的索引。所以要建在合适的地方，合适的对象上。经常操作 / 比较 / 判断的字段应该建索引。

字段大小合宜。字段的取值是有限而且是固定的，这种情况下可以用 enum，IP 字段可以用 unsigned int 来存储。

表的设计。垂直分割表，使得固定表与变长表分割，从而降低表的复杂度和字段的数目。

SQL 语句优化：避免全表扫描

建索引：一般在 where 及 order by 中涉及到的列上建索引，尽量不要对可以重复的字段建索引。

尽量避免在 where 中使用 !(<>)或 or，也不要进行 null 值判断。

尽量避免在 where 中对字段进行函数操作、表达式操作。

尽量避免使用 like- %，在此种情况下可以进行全文检索。