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