程序员视角深度解读MySQL数据库秘密

以MySQL为例，程序员视角探索数据库秘密！

数据库基本原理

我对数据库的理解

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

不言而喻，数据肯定是需要存储的；存储是不够的，显然必须提供存储程序。操作被封装，添加、删除、修改、查询API暴露给外界，即实例。

一个存储可以容纳多个实例，提高该存储的承载能力和高可用性；多个存储可以分布到不同的机房和区域，实现容灾。

第二：按照Block或页码读取数据

用大腿就知道，数据库中一行一行的读取数据是不可能的（为什么？？^_^）。基本上像Oracle/MySQL这样的数据库都是基于固定大小（比如16K）的物理块（Block或者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，并且由于性能问题导致插入/更新困难。选择最常遇到、最困难的部分。优化的选择是重中之重，索引显然是不可或缺的！

说起MySQL索引，我们会看到很多这样的东西：BTree索引/B+Tree索引/哈希索引/聚集索引/非聚集索引......多得让人目不暇接！

索引到底是什么？你想解决什么问题？

这是陈词滥调。官网介绍说MySQL索引是一种数据结构，索引的目的是为了提高查询性能。

坦白说，如果不使用索引的话，磁盘IO次数会更高！如果想减少磁盘IO次数该怎么办？

我们希望通过不断缩小我们想要检索的数据范围来过滤出最终想要的结果，并将每次数据查找的磁盘IO数量控制到一个较小的数量级，最好是恒定的数量级。

B+Tree索引的发布就是为了解决以上问题！

Hello B+Tree

在MySQL中，不同的存储驱动器以不同的方式实现索引。这里我们重点分析MyISAM和Innodb。

B+MyISAM引擎索引树结构

我们知道，对于MyISAM引擎来说，数据和索引文件是分开的。从图中还可以看出，查找索引后，就得到了数据的物理地址，然后可以根据该地址来定位数据文件中的记录。这种方法也称为“非聚集索引”。

对于Innodb引擎来说，数据文件本身就是一个索引文件！通俗地说，MyISAM在叶子节点上存储一条记录的物理地址，而Innodb则存储数据内容。这种方法称为“聚集索引”。

还有一点需要注意的是，对于Innodb来说，主键索引中的叶子节点存储的是数据内容，而普通索引中的叶子节点存储的是主键值！即Innodb对普通索引字段的查找，首先通过普通索引的B+Tree查找主键，然后再查找主键索引的B+Tree。从这里可以看出，建立主键对于Innodb来说是非常重要的！

对于MyISAM来说，主键索引和普通索引唯一的区别是主键只需要找到一条记录就停止，而普通索引允许复制。一旦找到记录，您需要继续搜索。如图所示结构没有什么区别。

深入研究B+Tree

问一些问题：

为什么B+Tree将真实数据放在叶子节点而不是内部节点？

为什么说索引字段越短越好，最好是单调递增？

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

范围查询（>、26且性别='男'，实际只使用复合索引的姓名列。

要用索引就得“干净”

什么是“干净”？只是不要让索引参与计算！例如，如果对索引使用函数，索引可能会失败。为什么

其实不用想。 B+Tree存储数据。要进行比较，您必须对所有数据应用函数。显然成本太高了。

我想创建一个指数并看看差异

虽然指数质优价廉，但不要被愚弄。 count(distinct col) / count(*) 可以计算distinct col。显然主键是1。如果相差太小，你可能会考虑是否还需要创建索引？

哈希索引

这并不是要深入分析哈希索引，而是为了说明哈希的思想真的无处不在！
MySQL存储引擎中有一个哈希函数。根据密钥，使用哈希函数计算地址。因此，一般情况下散列索引查找会非常快，O(1)的速度。但也存在哈希冲突，像HashMap一样，以单链表的形式解决。

想知道哈希索引是否支持范围查询？

显然不支持，只能给出一个KEY进行搜索。像HashMap一样，它会很快找到包含“zhangfengzhe”的key吗？

SQL优化神器：详解

SQL优化场景很多，网上技术也很多，记不住了！

要彻底解决这个问题，我想我们只能正确理解数据的结构以及索引背后的原理。当我们遇到SQL写法或者SQL查询慢的时候，我们就有了分析的依据，然后使用解释工具来验证。 ，这应该不是什么大问题。

解释查询结果可以告诉你使用了哪些索引、如何检查表等。我将在这里展示演示文稿。

数据表学生：

注意复合索引（年龄、地址）

匹配最左边的前缀

复合索引失败

OK，准备工作到这里就完成了，查询很简单，优化不容易，写下来欣赏一下！