关于数据库的问题

缺乏、安全感 2022-09-13 04:23 133阅读 0赞

1：数据库分区分库分表：  
1.1首先要了解数据库的架构逻辑。一般分为三层：  
客户端层：并非mysql独有，比如连接处理，授权验证等是在这一层  
中间层：属于核心服务，包括，查询，解析，优化，缓存，内置函数以及跨存储引擎的触发器，视图等。  
第三层是存储引擎，比如innnodb，负责数据的存储和获取，中间层何其通信靠的是mysql的API实现的。  
1.2mysql的查询过程：  
也就是一条sql进来后内部的操作流程：  
客户端通过通信协议进来后先查的是查询缓存，如果缓存中没有则去解析器进行解析，把语法解析成解析树，然后到预处理解析，再然后生成新的解析树，再去查询优化器进行优化，生成查询计划，去查询执行引擎，执行引擎根据API接口查询存储引擎（myisam/innnodb）。  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA55CG5oOz5piv6aG257qn5p625p6E5biI_size_12_color_FFFFFF_t_70_g_se_x_16]

1.3：数据库的查询优化，其实就是让数据在库的内部通过不同的执行方式达到你想要的执行流程来实现的。  
1.3.1比如说缓存，从图中可以看到，如果我们查询的时候，数据能够命中缓存，那么查询效率是非常高的。但是如果你建立了缓存，数据却没有命中缓存，那么数据会先在缓存里面走一圈然后再出来去解析，这样的过程反而是降低性能增加内耗了。因为缓存打开，第一步肯定会先走缓存，所以缓存的开关要特别注意。  
既然名不中就会下降，那么什么样的数据会名不中呢，去看缓存的存储结构，它是一张类似于hashmap的表结构，它的hash值是根据协议版本号，当前数据库等一些可能影响的信息算出来的，所以当你要缓存的数据如果包含用户变量，临时表，自定义函数的时候都不会被缓存，同时如果你的两条查询有任何字符上的不同的时候也不会命中查询，比如空格注释等。  
这是名不中降低效率带来的思考。  
另外缓存必然会有失效，它的失效时间又是什么？  
当跟缓存有关的表出现变化的时候，它的缓存就会失效。所以当你去写操作的时候，缓存会清空，清空也是需要耗时的，所以如果你缓存的数据多的话，去执行写操作也会增加内耗。  
所以上面可以看出缓存并不是开着才好，而是要谨慎合理的使用才行，比如设置一个几十兆的缓存大小，比如分表处理等。  
1.3.2数据查询通过缓存后进入解析器进行解析和预处理解析，通过语法关键字进行解析，检查语法正确性以及行和列是否存在等。这一步几乎没办法去优化。  
1.3.3预处理结束，生成执行计划，一般会生成很多种执行计划，然后从中选出一个基于成本最小的计划为执行计划。Sqlserver中可以用showplan\_on来看一段语句的成本。Mysql是根据last\_quary\_cost查询的。我们做的优化一般就是在这一步做的，因为他们的执行计划计算使用表的页面个数，索引的基数，分布情况，还有处理时的cpu,io的消耗以及内存占用等等来计算的，如果走到了索引的话，成本值会小，执行计划可以生成更优的计划，所以我们会加索引减少成本，但也不是盲目的加的。  
1.4执行计划结束后，进行查询执行引擎，通过tcp协议建立连接查询，然后返回给客户端，返回的时候是查出来一条立马返回的，这样可以减少本地存储，也可使得客户端更快看到。是把数据包发过去的。

1.5优化建议：  
1.5.1从内存以及cpu层面去想。  
也就是使用小而且简单的数据类型建表，占用内存小，使用起来快。  
另外注意给类型设定固定长度是不会优化的，比如int(11)，这是没用的，int占用了4个字节，32位存储空间，所以int(1)和int(32)是一样的效果。  
避免浮点数类型，因为cpu对浮点的精确计算消耗较大，可以成倍后存成整型。  
1.5.2：从索引的层面去想：  
索引的一些参数可以降低成本，但会增加内存，内存小的话也会降低成本，根据这种公式去算的话，也要注意索引可加，但不能盲目的加。  
为什么索引会降低成本，因为索引可以减少在存储引擎中查找的开销。  
从数据结构层去分析：

数据库发生了死锁：  
写一个事务操作，同时插入主表和入库表， 另外一个接口同时更新入库表和主表，注意这里的顺序是反的。  
模拟高并发的时候，有一个进程A插入主表，然后直接扫描全表锁表，紧接着去访问入库表。 进程B更新入库表锁住入库表，同时去访问主表，发现主表被进程A锁住的，进程A同样发现入库表被B锁住的，就互相等待。产生了死锁。  
错误解决，把第二个更新入库表再更新主表的位置换一下，让两个进程顺序的执行。  
更新前：  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA55CG5oOz5piv6aG257qn5p625p6E5biI_size_12_color_FFFFFF_t_70_g_se_x_16 1]

更新后：  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA55CG5oOz5piv6aG257qn5p625p6E5biI_size_12_color_FFFFFF_t_70_g_se_x_16 2]

第二点就是我们没有为要更新的where的条件加入索引，要是加入索引的话也会降低刚才产生死锁的原因：  
因为不加索引的更新是锁表，走索引是锁行的。  
假设有一表，分别是r1到r8这8行，然后进程A要更新r2,r8,这两行，由于没有加索引，直接扫描全表，看到r2把r2加上x锁1，进程B更新r1，也加上了锁1，这时进程A想更新r8，还从全表扫描，发现第一行被加了锁。进程B呢也是接着扫描到第二行，发现第二行也加着别人的锁。这就产生了死锁。

https://www.cnblogs.com/albertzhangyu/p/9696479.html

分库分表：  
我们的猎风者系统有三个小系统组成，然后分成三个库。  
我们的viocio输出项有一千多个字段，按照申请书，ocr信息，尽职调查信息，纳税识别人信息等分成几十张小表。

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA55CG5oOz5piv6aG257qn5p625p6E5biI_size_12_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/a5c73faa40394a1ebd757690583335ae.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA55CG5oOz5piv6aG257qn5p625p6E5biI_size_12_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/8f497349c5ab4faea901308b32722700.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA55CG5oOz5piv6aG257qn5p625p6E5biI_size_12_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/d5908c46465f4e63b92a0c5cd78a79e0.png