从程序员的角度深入理解MySQL

媒介

作为一名事变了4年的措施猿，本日我将站在措施员的角度以MySQL为例试探数据库的机密！

数据库根基道理

我对DB的领略

第一：数据库的构成：存储 + 实例

不必多说，数据虽然必要存储；存储了还不足，显然必要提供措施对存储的操纵举办封装，对外提供增编削查的API，即实例。

一个存储，可以对应多个实例，这将进步这个存储的负载手段以及高可用；多个存储可以漫衍在差异的机房、区域，将实现容灾。

第二：按Block or Page读取数据

用大腿想也知道，数据库不行能按行读取数据（Why? ? ^_^）。实质上，数据库，如Oracle/MySQL，都是基于牢靠巨细（好比16K）的物理块（Block or Page，我这里就不区分同一称为Block）来实现调治和打点的。要知道Block是数据库的观念，怎样对应到文件体系呢？显然必要指出“这个Block的地点在那边”，当查找到地点后，读取牢靠巨细的数据就相等于完成了Block的读取了。

数据库很智慧的，它不会仅仅只读取必要读取的Block，它还会替我们把四面的Block块都读取加载至内存。现实上，这是为了镌汰IO次数，进步掷中率。究竟上，一个Block块的四面Block也是热门数据，这种处理赏罚方法很有须要！

第三：磁盘IO是数据库的机能瓶颈

毫无疑问，数据在磁盘上，少不了磁盘IO。什么磁头旋转，定位磁道，寻址的进程，就不说了，我们是措施员，也管不了这些。可是这个进程确实长短常耗时的，和内存读取不是一个数目级，以是其后呈现了许多方法来镌汰IO，晋升数据库机能。

好比，增进内存，让数据库把数据更多的加载至内存。内存虽好，但也不能滥用，为什么这么说呢？假设数据库中有100G数据，假如都加载至内存，也就说数据库要打点100G磁盘数据+100G内存数据，你说累不累？（数据库要处理赏罚磁盘和内存的映射相关，数据的同步，还要对内存数据举办整理，假如涉及数据库事宜，又是一系列伟大操纵……）不外这里必要指出的是，为了加速内存查找速率，数据库一样平常对内存举办HASH存放。

如，操作索引，索引对比内存，是一本性价比很是高的对象，后文具体先容MySQL的索引道理。

好比，操作机能更好的磁盘…（和咱们就不要紧呢）

第四，提出一些题目思索下：

为什么我们说操作delete删除一个表的数据较trancate一个表要慢？

【一个按行查找删除，多费劲；一个基于Block的系统布局删除】

为什么我们说要小表驱动大表？

【小表驱动大表会快？什么鬼？MN和NM不是一样的么？有鬼的处所，就有索引！】

试探MySQL索引背后的道理

对付绝大数的应用体系，读写比例在10:1，乃至100:1，并且insert/update很难呈现机能题目，碰着最多的，最棘手的就是select了，select优化是重中之重，显然少不了索引！

提及MySQL的索引，我们会冒出许多这些对象：BTree索引/B+Tree索引/Hash索引/聚积索引/非聚积索引…这么多，晕头！

索引到底是什么，想办理什么题目？

老生常谈了，官网说MySQL索引是一种数据布局，索引的目标就是为了进步查询服从。

说白了，不行使索引的话，磁盘IO次数较量多！要想镌汰磁盘IO次数，怎么办？

我们想通过不绝缩小想要获取的数据的范畴来筛选出最终想要的功效，把每次查找数据的磁盘IO次数节制在一个很小的数目级，最好是常数数目级。

为了应对上述题目，B+Tree索引出来了！

Hello，B+Tree

在MySQL中，差异存储引擎对索引的实现方法是差异的，这里将重点说明MyISAM和Innodb。

MyISAM引擎的B+Tree索引布局

我们知道对付MyISAM引擎而言，数据文件和索引文件是疏散的。从图中也可以看出，通过索引查找到后，就获得了数据的物理地点，然后按照地点定位数据文件中的记录即可。这种方法也叫"非聚积索引"。

而对付Innodb引擎而言，数据文件自己是索引文件！普通点说，叶子节点上，MyISAM存储的是记录的物理地点，而Innodb上存储的是数据内容，这种方法即"聚积索引"。

其它一点必要留意的是，对付Innodb而言，主键索引中叶子节点存储的是数据内容，而平凡索引的叶子节点中存储的是主键值！也就是说，对付Innodb的平凡索引字段查找，先通过平凡索引的B+Tree查找到主键后，然后通过主键索引的B+Tree举办查找。从这里你可以看出，对付Innodb而言，主键的成立很是重要！

而对付MyISAM而言，主键索引和平凡索引仅仅的区别在于主键只必要查找到一笔记录即可遏制，而平凡索引应承一再，找到一笔记录后必要继承查找，在布局上没有区别，如上图所示。

深入B+Tree

提几个题目：

为什么B+Tree把真实的数据放到叶子节点，而不是内层节点？

为什么我们说索引字段要尽也许短，最好是单调递增的？

为什么复合索引存在最左匹配原则？

范畴查询（>,<,between,like）对最左匹配有什么影响？

关于B+Tree的一些数学理论，咱们就不玩了，至少一点可以必定的是：数据表的数据量N=F(树的高度h，每个Block存储的索引的个数m)。在N必然的环境下，索引字段越小，那么m会越大，这意味着h将越小！树越低，虽然查找的更快！

假如内层节点存放真实的数据，显然m会变小，树将变高。

在现实应用中，我们应该尽也许回收单调递增的字段作为主键，一方面不会使得索引的数据布局变大，减小了索引占用的空间；另一方面也不会频仍的破碎B+Tree，使得服从降落。

好比复合索引(name,age,sex)，B+Tree会优先较量name来确定下一步的搜刮偏向。假如溘然来了个(age,sex)，基础上就无从动手。这也是切合常理的，对付一本书，我们说“找到第几章第几节的XXX”，从没有传闻过“找到第几节的XXX”！这是复合索引的重要特征，即最左匹配特征。

（编辑：河北网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!