以MySQL为例，带大家从原理上了解所谓的数据库军规。同时，数据库在高并发阶段往往成为瓶颈。如果一开始数据库表和索引设计不好，后期数据库的水平扩展就会遇到分库分表的困难。对于互联网公司来说，一般都使用MySQL数据库。一、数据库的整体结构我们先来看看MySQL数据的整体结构如下：这是一张非常经典的MySQL系统架构图，通过它可以看到MySQL各个部分的功能。当客户端连接数据库时，首先面对的是连接池，连接池是用来管理用户的连接的，会做一定的鉴权和鉴权。连接上数据库后，客户端会发送SQL语句，SQL接口模块就在这里接受用户的SQL语句。SQL语句往往需要遵守严格的语法规则，因此需要语法解析器来解析语句。解析语法的原理和编译原理中学的一样，都是从一条语句到语法树。可以对用户所属的查询进行优化，从而选择最快的查询路径，这就是优化器的作用。为了加快查询速度，会有一个查询缓存模块。如果查询缓存中有最新的查询结果，查询语句可以直接从查询缓存中取数据。以上所有组件都是数据库服务层，其次是数据库引擎层。目前主流的数据库引擎是InnoDB。对于数据库的任何修改，数据库服务层都会记录在二进制日志中，这是主从复制的基础。对于数据库引擎层，一个比较有名的图是这样的：在存储引擎层，还有缓存和日志，最后的数据落盘。存储引擎层的缓存也是用来提升性能的，但是和数据库服务层的缓存不同。数据库服务层的缓存是查询缓存，而数据库引擎层的缓存是读写缓存。数据库服务层的缓存是基于查询逻辑的，而数据库引擎引擎的缓存是基于数据页的，可以说是物理的。即使数据只是在数据库引擎层写入了缓存，对于数据库服务层来说，即使已经持久化了，当然这个时候会造成缓存页和硬盘上的页数据不一致。通过在数据库引擎级别进行日志记录来维护不一致。所以数据库引擎层和数据库服务层的日志也是不一样的。服务层的日志记录了修改逻辑，引擎层的日志记录了缓存页和数据页的物理差异。2.数据库的工作流程当收到查询时，MySQL架构中的各个组件是这样工作的：客户端与数据库服务层建立TCP连接，连接管理模块会建立连接并请求连接线程.如果连接池中有空闲的连接线程，就会分配给这个连接。如果没有，在没有超过最大连接数的情况下，会创建一个新的连接线程负责这个client。在实际操作之前，还需要调用用户模块进行授权检查，验证用户是否有权限。通过后提供服务，连接线程开始接收和处理来自客户端的SQL语句。连接线程收到SQL语句后，将语句传递给SQL语句解析模块进行语法分析和语义分析。如果是查询语句，可以先查看查询缓存中是否有结果，如果有，可以直接返回给客户端。如果查询缓存中没有结果，则需要真正查询数据库引擎层，因此将其发送给SQL优化器进行查询优化。如果是表变化，则交给insert、update、delete、create、alter处理模块处理。接下来就是请求数据库引擎层，打开表，必要时获取相应的锁。接下来是数据库引擎层，比如InnoDB。在数据库引擎层，首先需要检查缓存页中是否有对应的数据。如果有，可以直接退货。如果不是，则必须从磁盘读取。当在磁盘中找到对应的数据时，会加载到缓存中，让后续的查询更加高效。由于内存有限，经常使用灵活的LRU表来管理缓存页，以保证缓存中是频繁访问的数据。.拿到数据后返回客户端，关闭连接，释放连接线程，流程结束。3、数据库索引原理在整个过程中，最容易被称为瓶颈点的是数据的读写，往往是顺序或随机读写磁盘，读写磁盘的速度是通常比较慢。如何加快这个过程？相信大家都猜到了，就是建立索引。为什么索引可以加速这个过程呢？美食城相信大家都去过，里面的餐厅也很多。如果你不赶时间，不饿，对搜索性能没有要求，可以在商场里慢慢逛，一家一家逛，直到知道你想吃什么餐厅。但是当你饿了，或者你约了一家餐馆，你必须直奔那家餐馆。这时候，你会经常看楼层的索引图，快速找到你目标餐厅的位置。找到之后，直奔主题。会大大节省时间，这就是索引的作用。所以索引就是通过值快速的找到它的位置，从而可以快速的访问到它。索引的另一个作用是可以不用实际看数据就可以做出一些判断。比如商场里有某家餐厅，你看索引就可以了。川菜馆只需要看索引，不需要从一家川菜馆跑到另一家川菜馆。那么在MySQL中，索引是如何工作的呢？MySQL的索引结构往往是B+树。M阶B+树具有以下性质：节点分为索引节点和数据节点。索引节点相当于B树的内部节点，所有的索引节点组成一棵B树，具有B树的所有特性。在索引节点中，存放Key和指针，不存放具体的元素。数据节点相当于B树的外部节点。B-tree的外部结点是空的，在B+树中用来存放真正的数据元素，包含元素的Key等信息，但没有指针。整个索引节点组成的B-tree只是用来查找具有某个Key的数据元素位于哪个外部节点，如果在索引节点中找到了key，则事情还没有结束。需要继续寻找数据节点，然后读出数据节点中的元素，或者二分查找，或者顺序扫描，找到真正的数据元素。M的顺序只是用来控制索引节点部分的度数，至于每个数据节点包含多少个元素，与M无关。另外还有一个链表将所有数据节点串在一起，可以顺序访问。这个定义比较抽象，我们来看一个具体的例子。从图中我们可以看出，这是一个3阶B+树，一个外部数据节点最多包含5个item。如果插入的数据在数据节点中，如果不引起分裂和合并，则由索引节点组成的B树不会发生变化。如果在71到75的外部节点插入一个item76，会引起分裂，71、72、73成为数据节点，74、75、76成为数据节点，对于索引节点，相当于插入Key74的过程。如果在41到43的外部节点中删除43，会引起合并，41、42、61、62、63合并为一个节点。对于索引节点来说，相当于删除Key60的过程。在查找的时候，因为B+树层的高度很小，所以可以比较快的定位到。比如我们要查找62的值，如果在根节点找到大于40的值，就访问右边，如果小于70，就访问左边，如果它大于60，我们将访问右侧。对于第二个节点，找到并成功定位到62。在MySQL的InnoDB中，B+树索引有两种，一种叫聚簇索引，一种叫二级索引。聚集索引的叶子节点是数据节点，通常主键作为聚集索引，二级索引的叶子节点存储KEY字段加上主键值。因此，通过二级索引访问数据需要访问索引两次。还有一种索引形式叫做复合索引，或者复合索引，可以在多个列上建立索引。该索引的排序规则是先比较第一列，当第一列相等时再比较第二列，以此类推。四、数据库索引的优缺点数据库索引最明显的优点就是减少I/O。下面分析几种情况。对于=条件的字段，可以直接搜索B+树，通过少量的硬盘读取（相当于B+树层的高度），就可以到达叶子节点，然后直接定位到的位置数据。对于范围字段，由于B+树是排序的，所以通过树可以快速定位到范围。同样，对于orderby、groupby、distinct/max、min，由于B+树是排序的，所以可以很快得到结果。还有一种常见的场景叫做索引覆盖数据。比如A和B两个字段作为条件字段，经常出现A=aANDB=b。当同时选择C和D时，往往会建立一个联合索引（A和B），这是一个二级索引，所以在查找的时候，二级索引的B+树可以快速找到对应的叶子节点和记录，但是有些记录中包含聚簇索引的ID，所以需要在聚簇索引的B+树中搜索一次，找到真实表中的记录，然后在记录中读出C和D。如果在建立联合索引时是(A,B,C,D)，则二级索引的B+树中的所有数据都可用，可以直接返回，减少了一次查找树的过程。当然，做指数是要付出代价的，天下没有免费的午餐。索引的好处主要是读取效率的提升，而索引的代价是写入效率的降低。插入和修改数据可能意味着索引更改。插入的时候往往会在主键上建立聚簇索引，所以主键总是使用自增长，所以插入的数据总是在最前面，而且是顺序的，效率比较高。主键不要使用UUID，顺序是随机的，会导致随机写入，效率低下。不要使用与业务相关的主键，因为与业务相关就意味着要更新，会面临一次删除，重新插入，效率较低。通过上面对B+树原理的介绍，我们可以看出，B+树分裂的代价还是比较高的，分裂往往发生在插入过程中。至于数据修改，基本相当于删除再插入，成本比较高。对于一些字符串列的二级索引，经常会造成乱写乱读，I/O压力比较大。5.解读数据库军规背后的原理了解了这两个指标的原理之后，我们就可以解释为什么很多所谓的数据库军规都是这个样子了。让我们一一解释。什么时候应该使用复合索引而不是单个索引？假设有一个条件语句A=aANDB=b，如果A和B是两个独立的索引，AND条件下只有一个索引起作用，B必须一一判断，如果组合索引(A,b)使用，只要遍历一棵树，效率大大提高。但是对于A=aORB=b，由于or的关系，组合索引是无效的，所以可以使用单独的索引。这时候两个索引就可以同时工作了。为什么索引要区分，区分的应该放在复合索引的前面？如果不加区分，比如使用性别，就相当于把整个大表分成了两部分，还需要遍历一半的表才能找到数据，让索引变得毫无意义。如果有复合索引，还需要单列索引吗？如果复合索引是(A,B)，那么对于条件A=a，可以使用这个复合索引，因为复合索引是先根据***列排序的，所以不需要单独创建indexforA,但不用于B=b，因为只有第一列相同时才比较第二列，所以第二列相同，可以分布在不同的节点上，没办法快速找到它。索引越多越好吗？当然不是，只在需要的地方加索引。索引不仅会降低插入和修改的效率，而且在查询的时候，还有一个查询优化器。索引太多会使优化器感到困惑，可能无法找到正确的索引。查询路径选择慢索引。为什么要使用自增主键？因为字符串主键和随机主键会造成数据随机插入，效率比较差，主键的更新频率要低一些，避免B+树和频繁的合并分裂。为什么尽量不要使用NULL？NULL在B+树中比较难处理，往往需要特殊的逻辑来处理，降低了效率。为什么不在频繁更新的字段上建立索引？更新一个字段意味着相应的索引也必须更新。更新通常意味着先删除再插入。索引本来就是一种在写入阶段预先形成一定的数据结构，从而使读取阶段效率更高的一种方式。但是，如果一个字段写多于读，不建议使用索引。为什么不在查询条件中使用函数？例如对于ID+1=10的条件，索引是在预先写入的时候生成的。ID+1的操作处于查询阶段。例如，索引无能。没办法先把所有的指标都计算出来，然后再进行比较，成本太高，所以应该用ID=10-1。为什么不使用NOT之类的否定查询条件呢？你可以想象一下，对于一个B+树，根节点是40，如果你的条件等于20，就往左边查，如果你的条件等于50，就往右边查，但是你的条件不是等于66，索引怎么办？你不知道，直到你经历它。为什么模糊查询不以通配符开头？对于B+树，如果根是字符def，如果通配符在后面，比如abc%，就应该往左边找，比如efg%，如果通配符在前面，就应该往右边找%abc，你不知道去哪里一方面，让我们再次扫描所有内容。为什么要把OR改成IN，或者用Union呢？OR查询条件的优化往往很难找到最佳路径，尤其是当OR条件很多的时候，尤其是同一个字段，最好使用IN，数据库会对IN中的条件进行排序，并通过二进制统一处理搜索方法。对于不同的字段，使用Union可以让每个子查询使用一个索引。为什么数据类型要越小越好，整型经常不用字符类型，长字符类型可以考虑使用前缀索引？因为数据库是分页存储的，所以每一页的大小都是一样的。如果数据类型越大，页数就会越多，每页的数据就会越少，树的高度就会越高，所以搜索数据读取的I/O数会相对大，插入时节点容易分裂，效率下降。使用整数而不是字符主要是出于这种考虑。整数对于索引更有效，例如IP地址。如果有长字符类型需要使用索引进行查询，为了不让索引过大，可以考虑索引字段的前缀，而不是索引整个字段。6.查询优化方法论要找到需要优化的SQL语句，首先要收集有问题的SQL语句。MySQL数据库提供了慢SQL日志功能。通过参数slow_query_log，获取执行时间超过一定阈值的SQL语句列表。可以使用long_queries_not_using_indexes参数启用不使用索引的SQL语句。min_examined_row_limit，扫描记录数大于该值的SQL语句将记录在慢SQL日志中。找到问题语句后，下一步就是通过explainSQL获取SQL执行计划。无论是否通过索引扫描记录，都可以通过创建索引来优化执行效率。扫描记录是否过多。加锁时间是否过长，是否有加锁冲突。返回的记录数是否很大。然后你可以自定义优化。对于过滤条件涉及的字段，索引未覆盖的字段，在区分度高的字段上创建索引。如果涉及多个字段，尽量创建联合索引。扫描记录数很大，返回记录数少，辨别度差。重新评估SQL语句涉及的字段，选择多个区分度高的字段创建索引。扫描记录数很大，返回记录数也很大，过滤条件不强。添加SQL过滤条件schema_redundant_indexes，查看有哪些冗余索引可用。如果多个索引涉及相同顺序的字段，可以组成一个联合索引schema_unused_indexes，看看哪些索引没有被使用过。7、读写分离原则数据库往往写的少，读的多，所以性能优化的第一步就是读写分离。主从复制是基于主节点上服务层的日志实现的，从节点上有一个IO线程读取日志并写入本地。从本地日志读取后，另一个线程在从节点上重新执行。下图是主从异步复制的流程图。master实例写入engine后，返回success，然后将事件发送给slave实例，在slave实例上执行。这种同步方式比较快，但是当master挂了，如果还没有被复制，可能会出现数据丢失的问题。数据库同步复制也不同。从节点入盘后返回给客户端。当然，这会降低性能。网易数据库团队通过分组提交、并行复制等技术提升性能。通过主从复制，可以在数据库DAO层设置读写分离策略，也可以通过数据库中间件来实现。其实数据库日志还有很多其他用途，比如使用Canal（阿里巴巴开源项目：基于MySQL数据库Binlog的增量订阅&消费）订阅数据库的Binlog，可以用来更新缓存等。
