Mysql进阶

引言：

1. 数据存储的原理（硬盘）

信息存储在硬盘里，硬盘是由很多的盘片组成，通过盘片表面的磁性物质来存储数据。把盘片放在显微镜下放大，可以看到盘片表面是凹凸不平的，凸起的地方被磁化，代表数字 1，凹的地方没有被磁化，代表数字 0，因此硬盘可以通过二进制的形式来存储表示文字、图片等的信息。硬盘有很多种，但是都是由盘片、磁头、盘片主轴、控制电机、磁头控制器、数据转换器、接口、缓存等几个部分组成。所有的盘片都固定在一个旋转轴上，这个轴即盘片主轴。所有的盘片之间是绝对平行的，在每个盘片的盘面上都有一个磁头，磁头与盘片之间的距离比头发丝的直径还小。所有的磁头连在一个磁头控制器上，由磁头控制器负责各个磁头的运动，磁头可沿盘片的半径方向移动，实际上是斜切运动，每个磁头同一时刻必须是同轴的，即从正上方往下看，所有磁头任何时候都是重叠的。由于技术的发展，目前已经有多磁头独立技术了，在此不考虑此种情况。盘片以每分钟数千转到上万转的速度在高速运转，这样磁头就能对盘片上的指定位置进行数据的读写操作。由于硬盘是高精密设备，尘埃是其大敌，所以必须完全密封。

2. 数据读写的原理

硬盘在逻辑上被划分为磁道、柱面以及扇区。
磁头靠近主轴接触的表面，即线速度最小的地方，是一个特殊的区域，它不存放任何数据，称为启停区或者着陆区，启停区外就是数据区。
在最外圈，离主轴最远的地方是 “0” 磁道，硬盘数据的存放就是从最外圈开始的。
在硬盘中还有一个叫 “0” 磁道检测器的构件，它是用来完成硬盘的初始定位。

盘面

硬盘的盘片一般用铝合金材料做基片，硬盘的每一个盘片都有上下两个盘面，一般每个盘面都会得到利用，都可以存储数据，成为有效盘面，也有极个别的硬盘盘面数为单数。
每一个这样的有效盘面都有一个盘面号，按顺序从上至下从 0 开始编号。
在硬盘系统中，盘面号又叫磁头号，因为每一个有效盘面都有一个对应的读写磁头，硬盘的盘片组在 2-14 片不等，通常有 2-3 个盘片。

磁道

磁盘在格式化时被划分成许多同心圆，这些同心圆轨迹叫做磁道。
磁道从外向内从 0 开始顺序编号，硬盘的每一个盘面有 300-1024 个磁道，新式大容量硬盘每面的磁道数更多，信息以脉冲串的形式记录在这些轨迹中，这些同心圆不是连续记录数据，而是被划分成一段段的圆弧。
这些圆弧的角速度一样，由于径向长度不一样，所以线速度也不一样，外圈的线速度较内圈的线速度大，即同样的转速度下，外圈在同样时间段里，划过的圆弧长度要比内圈划过的圆弧长度大。
每段圆弧叫做一个扇区，扇区从 1 开始编号，每个扇区中的数据作为一个单元同时读出或写入。
磁道是看不见的，只是盘面上以特殊形式磁化了的一些磁化区，在磁盘格式化时就已规划完毕。

柱面

所有盘面上的同一磁道构成一个圆柱，通常称作柱面。
每个圆柱上的磁头由上而下从 0 开始编号，数据的读 / 写按柱面进行，即磁头读 / 写数据时首先在同一柱面内从 0 磁头开始进行操作，依次向下在同一柱面的不同盘面即磁头上进行操作。
只有在同一柱面所有的磁头全部读 / 写完毕后磁头才转移到下一柱面（同心圆再往里的柱面），因为选取磁头只需要通过电子切换即可，而选取柱面则必须机械切换，电子切换相当快，比在机械上的磁头向邻近磁道移动快得多。
所以，数据的读 / 写按柱面进行，而不按盘面进行，也就是说，一个磁道写满数据后，就在同一柱面的下一个盘面来写，一个柱面写满后，才移到下一个扇区开始写数据，读数据也按照这种方式进行，这样就提高了硬盘的读 / 写效率。

扇区

操作系统以扇区形式将信息存储在硬盘上，每个扇区包括 512 个字节的数据和一些其他信息，一个扇区有两个主要部分：存储数据地点的标识符和存储数据的数据段。
标识符就是扇区头标，包括组成扇区三维地址的三个数字：盘面号，柱面号，扇区号（块号）。
数据段可分为数据和保护数据的纠错码（ECC）。在初始准备期间，计算机用 512 个虚拟信息字节（实际数据的存放地）和与这些虚拟信息字节相应的 ECC 数字填入这个部分。

磁盘块/簇
  1. 虚拟出来的，块是操作系统中最小的逻辑存储单位，操作系统与磁盘打交道的最小单位是磁盘块。通俗的来讲，在Windows下如NTFS等文件系统中叫做簇；在Linux下如Ext4等文件系统中叫做块（block）。每个簇或者块可以包括2、4、8、16、32、64…2的n次方个扇区
  2. 读取方便：由于扇区的数量比较小，数目众多在寻址时比较困难，所以操作系统就将相邻的扇区组合在一起，形成一个块，再对块进行整体的操作。
  3. 分离对底层的依赖：操作系统忽略对底层物理存储结构的设计。通过虚拟出来磁盘块的概念，在系统中认为块是最小的单位。
Page

操作系统经常与内存打交道的最小单位是页，类似于“块”的概念，都需要一种虚拟的基本单位。

3. 访盘请求完成过程

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

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

2）为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点：
A. 首先必须找到柱面，即磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间。
B. 然后目标扇区旋转到磁头下，即磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

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

A. 寻道（时间）：磁头移动定位到指定磁道所需要的时间，寻道时间越短，I/O操作越快，目前磁盘的平均寻道时间一般在3－15ms，一般都在10ms左右。

B. 旋转延迟（时间）：盘片旋转将请求数据所在扇区移至读写磁头下方所需要的时间，旋转延迟取决于磁盘转速。普通硬盘一般都是7200rpm，慢的5400rpm。

C. 数据传输（时间）：数据在磁盘与内存之间的实际传输所需要的时间。

4. 磁盘的读写原理

系统将文件存储到磁盘上时，按柱面、磁头、扇区的方式进行，即最先是第 1 磁道的第一磁头下的所有扇区，然后是同一柱面的下一个磁头……
一个柱面存储满后就推进到下一个柱面，直到把文件内容全部写入磁盘。
系统也以相同的顺序读出数据，读出数据时通过告诉磁盘控制器要读出扇区所在柱面号、磁头号和扇区号（物理地址的三个组成部分）进行。

注：操作系统读取同理，只是颗粒的更大的块操作

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

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费的时间，磁盘的存取速度往往是主存的几百分之一。因此，为了提高效率，要尽量减少磁盘的 I/O。磁盘往往不是严格地按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据一般来说也会被马上使用。程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高（不需要寻道时间，只需要很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。预读的长度一般为页（Page）的整数倍。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储分割为连续的大小相等的块。每个存储块称为一页（在许多操作系统中，页的大小通常为 4k），主存和磁盘以页为单位交换数据，当程序要读取的数据不在主存中时，会触发一个缺页异常。此时系统会向磁盘发出读盘信息，磁盘会找到数据的起始位置并向后连续读取一页或几页的数据载入内存中，然后异常返回，程序继续运行。

思考

（1）什么是数据库存储引擎

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

（2）为什么要合理选择数据库存储引擎：

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

（3）MySQL存储引擎有哪些

Mysql存储引擎主要有： 1. MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole, 5. CSV, 6. Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam

分析开始

1.MySQL存储引擎及索引的实现原理

注：一般用的都是 MyIsam , InnoDB，Memory 所以也只介绍这三种，别的也不会。。。。。。

1.1 InnoDB （mysql5.5后的默认引擎）

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。InnoDB还引入了行级锁定和外键约束。

InnoDB 的存储文件有两个，后缀名分别是 .frm 和 .idb，其中 .frm 是表的定义文件，而 idb 是数据文件。
InnoDB 中存在表锁和行锁，不过行锁是在命中索引的情况下才会起作用。
InnoDB 支持事务，且支持四种隔离级别（读未提交、读已提交、可重复读、串行化），默认的为可重复读；而在 Oracle 数据库中，只支持串行化级别和读已提交这两种级别，其中默认的为读已提交级别。

在以下场合下，使用InnoDB是最理想的选择：

1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
  2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
  3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
  4.外键约束。MySQL支持外键的存储引擎只有InnoDB。
  5.支持自动增加列AUTO_INCREMENT属性。
  
  一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

InnoDB的存储方式

注：
1. 底层实现数据结构：B+Tree

2. 由于数据与索引在一起，所以 InnoDB是聚簇索引。

mysql数据存储方式 mysql数据存储原理_存储引擎


通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。

因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

InnoDB的锁机制详述

innodb支持行级锁

行级锁可以最大程度的支持并发

行级锁是由存储引擎层实现的

InnoDB的事务处理

Innodb是一种事务型存储引擎
完全支持事务的ACID特性
即原子性、一致性、隔离性、持久性
使用Redo Log和Undo Log来完成一致性
show variables like ‘innodb_log_buffer_size’; 查看redo 缓冲区大小
show variables like ‘innodb_log_file_in_group’; 查看log file数量

思考

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

表数据文件本身就是按B+Tree组织的一个索引结构文件，所以必须有主键索引，如果没有设置，Mysql会在表里面挑选一个能够唯一标识这一列数据的字段作为主键索引，如果找不到，会生成一个默认的列（隐藏列 – 主键唯一索引）。

整型相对与String 占据节约存储空间，而且B+树是按照从左到右一次递增存储的，设置整型方便比较（如果是String要先转成ASCII码，再比较，效率太低）。

如果自增方式，在插入的时候直接在后面加就可以了，如果是随机的，在插入的时候需要插入到中间，因为B+树会自动平衡，所以会导致树的裂化。效率会降低。

2.为什么非主键索引结构叶子节点存储的是主键值
一致性和节省空间，正是因为非主键索引结构叶子节点存储的是主键值，所以主键值不应该过大，否则会浪费非主键索引结构叶子节点的存储空间。

1.2 MyISAM （mysql5.5前的默认引擎）

MyISAM不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。

每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：

frm(存储表定义)
 MYD(MYData，存储数据)
 MYI(MYIndex，存储索引)

数据文件和索引文件可以放置在不同的目录，平均分配IO，获取更快的速度。要指定数据文件和索引文件的路径，需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定，文件路径需要使用绝对路径。

每个MyISAM表都有一个标志，服务器或myisamchk程序在检查MyISAM数据表时会对这个标志进行设置。MyISAM表还有一个标志用来表明该数据表在上次使用后是不是被正常的关闭了。如果服务器以为当机或崩溃，这个标志可以用来判断数据表是否需要检查和修复。如果想让这种检查自动进行，可以在启动服务器时使用–myisam-recover现象。这会让服务器在每次打开一个MyISAM数据表是自动检查数据表的标志并进行必要的修复处理。MyISAM类型的表可能会损坏，可以使用CHECK TABLE语句来检查MyISAM表的健康，并用REPAIR TABLE语句修复一个损坏到MyISAM表。

MyISAM的表还支持3种不同的存储格式：

静态(固定长度)表
动态表
压缩表

静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格，但是在访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。同时需要注意：在某些情况下可能需要返回字段后的空格，而使用这种格式时后面到空格会被自动处理掉。
　
动态表包含变长字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁到更新删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。

压缩表由myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

MyISAM的存储方式

注：
1. 底层实现数据结构：B+Tree

2. 由于 Myisam 中的索引和数据分别存放在不同的文件，所以在索引树中的叶子节点中存的数据是该索引对应的数据记录的地址，由于数据与索引不在一起，所以 Myisam 是非聚簇索引。

原理图

mysql数据存储方式 mysql数据存储原理_柱面_02


通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。

因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

MyISAM的锁机制详述

。。。。。

1.3MEMORY

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

2.数据库的引擎选择

在实际工作中，选择一个合适的存储引擎是一个比较复杂的问题。每种存储引擎都有自己的优缺点，不能笼统地说谁比谁好。

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

注意：同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。