前言作为一名工作了4年的程序员，今天我就站在程序员的角度，以MySQL为例，一探数据库的奥秘！数据库的基本原理我对DB的理解首先是：数据库的组成：存储+实例不用多说，数据当然要存储；storage是不够的，显然需要提供一个程序来封装storage的操作，对外提供增删改查的API，实例。一个存储可以对应多个实例，提高存储的负载能力和高可用性；多个存储可以分布在不同的机房和区域，实现容灾。第二种：按Block或Page读取数据。用大腿就知道，数据库是不可能按行读取数据的（Why？？^_^）。本质上，Oracle/MySQL等数据库都是基于固定大小（比如16K）的物理块（Block或Page，这里不做区分，统称为Block）来实现调度和管理。要知道Block是数据库的一个概念，它和文件系统是怎么对应的呢？显然，有必要指出“这个块的地址在哪里”。当找到地址后，读取固定大小的数据就相当于完成了块的读取。数据库非常智能。它不仅会读取需要读取的块，还会为我们读取附近的块并将其加载到内存中。其实这是为了减少IO次数，提高命中率。事实上，一个区块的附近区块也是热点数据，所以这种处理方式很有必要！第三：磁盘IO是数据库的性能瓶颈。毫无疑问，数据在磁盘上，磁盘IO是少不了的。磁头旋转、轨道定位、寻址的过程就不说了。我们是程序员，我们无法控制这些。但是这个过程确实非常耗时，而且和内存读取不是一个数量级的，所以出现了很多减少IO，提高数据库性能的方法。例如，增加内存允许数据库将更多数据加载到内存中。记忆力虽好，但不能滥用。你为什么这么说？假设数据库中有100G数据，如果全部加载到内存中，意味着数据库要管理100G磁盘数据+100G内存数据，累不累？（数据库要处理磁盘和内存的映射关系，数据的同步，内存数据的清理，如果涉及到数据库事务，就是一系列复杂的操作。。。）但是需要什么这里需要指出的是，为了加快内存查找速度，数据库一般会对内存进行HASH存储。比如使用索引，与内存相比，索引是一个非常划算的东西。下面的文章将详细介绍MySQL的索引原理。比如使用性能更好的磁盘……（与我们无关）四、提出一些问题思考：为什么说用delete删除表的数据比transcate表慢？[逐行查找和删除很费力；ablock-basedarchitecturedelete]为什么说小表驱动大表？【小表会不会带动大表快？我勒个去？MN和NM不是一样的吗？哪里有鬼，哪里就有索引！】探究MySQL索引背后的原理对于大多数应用系统来说，读写比都是10:1，甚至100:1，insert/update很难出现性能问题。遇到最难的就是select。选择优化是重中之重，显然索引是不可或缺的！说到MySQL索引，我们会有一大堆这样的东西：BTree索引/B+Tree索引/Hash索引/聚集索引/非聚集索引……太多了，晕！索引到底是什么，要解决什么问题？老生常谈了，官网说MySQL索引是一种数据结构，索引的目的是为了提高查询效率。说白了，如果不使用索引，磁盘IO次数比较多！想减少磁盘IO次数怎么办？我们想通过不断缩小想要获取的数据范围来筛选出最终想要的结果，并且将每次数据搜索的磁盘IO次数控制在一个较小的数量级，最好是恒定的数量级。为了解决以上问题，B+Tree索引就出来了！你好，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，每个块存储的索引个数m）。在N一定的情况下，索引字段越小，m越大，也就是h越小！树越低，搜索速度当然越快！如果内部节点存储真实数据，显然m会更小，树会更高。在实际应用中，我们应该尽量使用单调递增的字段作为主键。一方面不会增加索引的数据结构，减少索引占用的空间；另一方面，它不会频繁分裂B+Tree。使效率下降。比如复合索引（姓名、年龄、性别），B+Tree会先比较姓名来决定下一步的查找方向。如果（年龄、性别）来得突然，基本无从下手。这也符合常识。对于一本书，我们说“找到哪一章哪一节的XXX”，从来没有听说过“找到哪一节的XXX”！这是复合索引的一个重要特征，即最左匹配特征。假设有一个复合索引（name,age,sex），我们在select的时候，不按照这个顺序，而是sex='man'andname='zfz'andage=27，会用索引吗？数据库很聪明，SQL优化的时候会自动帮我们调整！但是如果缺少复合索引的第一列，数据库就无能为力了。对于最左边的匹配，MySQL会一直向右匹配，直到遇到范围查询停止匹配。什么意思？比如复合索引(name,age,sex)，对于name='zhangfengzhe'andage>26andsex='man'，实际只使用了复合索引的name列。要想用索引，就得“干净”什么是“干净”？只是不要让指数参与计算！例如，将函数应用于索引可能会导致索引失败。为什么？其实不用想。B+Tree存储数据。如果要比较，就需要把函数应用到所有的数据上，显然代价太大了。如果要建立索引，请看判别索引。虽然物美价廉，但也不要乱来。count(distinctcol)/count(*)可以计算出col的区分度，显然对于主键来说，就是1。如果区分度太低，可以考虑是否需要建索引？这里的Hash索引并不是深入分析Hash索引，而是说明Hash的思想真的是无处不在！在MySQL的Memory存储引擎中，有一个哈希函数。给定一个键，通过哈希函数计算出地址。因此，一般情况下，哈希索引查找会非常快，速度为O(1)。但是也有hash冲突，和HashMap一样，都是以单链表的形式来解决的。想一想，哈希索引支持范围查询吗？显然不支持，只能给个KEY去找。就像HashMap一样，找到包含“zhangfengzhe”的key会不会很快？SQL优化神器：explainSQL优化的场景很多，网上也有很多技巧，根本想不起来！要彻底解决这个问题，我觉得只能正确理解索引背后的数据结构和原理。遇到写SQL或者SQL慢查询的时候，我们有基础去分析，然后用explain工具去验证，应该问题不大。解释查询的结果可以告诉您正在使用哪些索引、表是如何扫描的，等等。这里我将演示一个Demo。数据表同学：注意复合索引（年龄，地址）符合最左前缀匹配复合索引失败OK，到这里，准备工作结束，查询容易，优化不易，且写且珍惜！
