-----mysql数据结构 MYSQL调优----
?如何优化数据库性能或MYSQL调优?,工作中你是怎么做的(自述)?
(前提---SQL的查询优化器--分析并完成优化SQL,选择是走索引还是全表查询,全表快就不走索引)
0、数据库版本选择:
数据库最好选择5.6.0以后的,增加了索引下推的功能,减少了回表查找
1.创建表时
1.1、选择正确的存储引擎,myisam是表级锁,不适合带事物的高并发写,只适合大量查询,写少的。一般事物写入多,用innodb
1.2、主键id最好是一个int类型的,推荐使用unsigned,自动增加auto_increment---插入时不用移位,有序插入成本最优(因为主键树上带有所有完整的数据,所以比较大,如果不是有序插入,B+树移位或分裂代价大)
1.3、建立合适的索引,业务查询需要多的字段建索引,有必要时建联合索引
1.4、尽量使用Not Null
1.5、能使用tinyint的不要用string,提升检索效率
1.6、使用enum而不是varchar。实际上,enum保存的是tinyint类型,但其显示为字符串
1.7 设计表结构时,满足数据库三范式:https://blog.csdn.net/CharlesYooSky/article/details/89204229
----------1.7.1、NF:保证每列的原子性,实际需求下字段不可再细分;----比如,收货地址的省市县存三个字段的需求,对这三个字段不能再往下细分了,最小原子了;
----------1.7.2、2NF:保证一张表只描述一件事情,有主键,非主键字段依赖主键;----比如,支付渠道就10家,渠道信息全存在交易表里就重复存很多次,应改成存渠道信息表的ID
----------1.7.3、3NF:保证每列都和主键直接相关;----多表不能有重复字段,修改易造成不一致问题
巧记:innodb id 索引 not null tinyint
1.8邻接表与闭包表的选择,多层关系选择闭包表设计模式
2.查询表时
索引失效相关: 巧记:左 *%!or null 不使用 引号 LIMIT *
其他相关: 巧记:拆分小查询,分解关联查询
1)慢查询-哪些可以避免
-----1.1)开启缓存,最好是mybatis一级缓存,不使用mybatis二级缓存和mysql缓存。--重复查询相同的数据(其实可以取一次后放在缓存中)等
----1.2)只需要一条数据时使用LIMIT 1;----,MySQL在查询到一条数据之后,会立即停止搜索,不会再往下收索其他满足条件的(如果有排序,则加limit无用,还是先查所有再limit),这会带来性能上的提升
-----1.2)避免select *,不要向数据库请求不需要的数据:原因如下两点
----------1.2.1)数据量大,有些无用的字段,增加读取磁盘IO的工作,查到数据后还会占用多余内存(缓存)
----------1.2.2)可能效率会提升巨大,这里特别是,要重点提一下覆盖索引----减少IO操作,辅助索引中就可以得到查询的记录,不需要查询聚簇索引中的记录。---比如我只想查询一个字段普通索引1 ,即select 普通索引1 ,那就不会走回表查询聚簇索引,少一次查询IO操作
2)MySQL是否在扫描额外的记录
可以从响应时间、扫描的行数、返回的行数评估一下
3)重构查询的方式
一个复杂查询还是多个简单查询--可以拆多个小语句。还有时间大范围的也可以拆成多个小分段时间会快会多。
大查询切分成小查询,每个查询功能一样--可以拆多个小语句。还有时间大范围的也可以拆成多个小分段时间会快会多。
分解关联查询--每一个表进行一次单表查询
4)如果确定这张表基本不会修改,比如流水表,可以开启缓存功能,提升效率(写多的场景会令缓存频繁修改,性能低)
参考:https://blog.csdn.net/hellozhxy/article/details/88824563?ops_request_misc=&request_id=&biz_id=102&utm_term=mysql%E5%85%B3%E9%97%AD%E7%BC%93%E5%AD%98%E7%9A%84%E5%8E%9F%E5%9B%A0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduweb~default-0-88824563
再开启缓存的情况下我们对sql语句做一些改动
Select sql_no_cache count(*) from users; 不缓存
Select sql_cache count(*) from users; 缓存(也可以不加,默认缓存已经开启了)
5)联合索引的正确使用
不使用范围查询---下一项不再匹配;
不对索引字段使用计算公式---改成对值使用公式;
新增联合索引(a,b)时,如果有a索引存在,可直接改它最好,能兼容且重复利用----分裂、合并等修改为索引(a, b)
3.数据量实在太大了的解决办法,引入中间件来进行
减少压力(分库);
分表
也可选用ES来存诸一些检索需求大的数据
MySQL整个查询执行过程:
1、客户端同数据库服务层建立TCP-sqlsession连接。(sql连接器)
2、客户端向MySQL服务器发送一条查询请求,先用sql语句等值查询缓存中中有没有相应的数据。
4、先看查询缓存中是否有结果,如果有结果可以直接返回给客户端。
3、连接线程接收到SQL语句之后,将语句交给SQL语句解析模块(SQL解析器)进行语法分析和语义分析。
5、如果查询缓存中没有结果,就需要真的查询数据库引擎层了,于是发给SQL优化器,进行查询的优化,生成相应的执行计划。
6、MySQL根据执行计划,调用存储引擎的API来执行查询(SQL执行器-执行引擎)
7、使用存储引擎查询时,先打开表,如果需要的话获取相应的锁。 (到存储引擎查询)先查询缓存页中有没有相应的数据,如果有则可以直接返回,如果没有就要从磁盘上去读取。
8、当在磁盘中找到相应的数据之后,则会加载到缓存中来,从而使得后面的查询更加高效,由于内存有限,多采用变通的LRU表来管理缓存页,保证缓存的都是经常访问的数据。
9、最后,获取数据后返回给客户端,关闭TCP-sqlsession连接,释放连接线程。
-- 表各字段分析-建议
select * from trx_trans_info PROCEDURE ANALYSE(1);
参考:https://www.cnblogs.com/chen-chen-chen/p/12176897.html
一般应急调优的思路:针对突然的业务办理卡顿,无法进行正常的业务处理,需要立马解决的场景。
1)show processlist;//查看所有连接者情况Sleep/query等
2)explain select id ,name from stu where name='clsn'; //sql执行计划查看分析explain # ALL id name age sex;
select id,name from stu where id=2-1 函数 结果集>30;show index from table;
3)通过执行计划判断,索引问题(有没有、合不合理)或者语句本身问题;
4)show status like '%lock%'; # 查询锁状态lock
kill SESSION_ID; # 杀掉有问题的session。
常规调优思路:针对业务周期性的卡顿,例如在每天10-11点业务特别慢,但是还能够使用,过了这段时间就好了。
1)查看slowlog,分析slowlog,分析出查询慢的语句;
2)按照一定优先级,一个一个排查所有慢语句;
3)分析top SQL,进行explain调试,查看语句执行时间;
4)调整索引或语句本身。
参考:https://www.jianshu.com/p/2bca7e9c2ad0
-------------------mysql数据结构 B+树--索引-------------------------------------
参考:https://c.lanmit.com/shujuku/MySQL/11343.html
----先提问-----
?innodb为什么要用非聚簇索引,为啥不统一用聚簇索引不是更快吗/为什么innodb为什么要有主键?
1)节省磁盘空间,所有普通索引都存一份,占用磁盘过大
2) 避免数据一致性问题----把数据只存在一个主键索引文件上,其他索引都通过关键主键索引来获取其对应数据(回表查询),避免数据一致性问题(如果每个索引都存一份完整数据,就可以数据不一致);
?索引的优缺点?
索引的优点:快速定位,查询快;降低数据库的排序成本
索引的缺点:占用磁盘空间,更新慢,增加了磁盘IO。
?mysql索引的实现原理是什么?
使用B+数,innodb和myisam这两个引擎都使用B+数据,B+数比其他算法效率要高很多,比如hash...,avg....,b树...,各自的缺点如下题。
?为什么用B+树-?
(数据只存叶子节点上且增加链表,不再中序遍历,高效范围查询;其他节点只存边界索key,快速定位到叶子节点,另外也具有B树的优点,因多叉树变矮了)原在B树基础上进行了改进,数据只存储在叶子节点上,且叶子节点之间增加了链表,这样获取节点时,
不用中序遍历,这样便于快速定位数据,是减少磁盘IO的最佳方式,这就说明,很重要的一点是可以做范围查询。(为什么是IO,因为索引是存在硬盘里的,如果存内存里,数据太大容易内存溢出)
?为什么不用hash?(适合等值查找,比如redis)
想进一步比较可参考:https://www.cnblogs.com/JMLiu/p/8856785.html
无论读还是写,哈希都比树更快,那为什么索引结构要选用树型结构呢?
(hash散列无序,只有等号查询('=','IN','<=>')效率高,无法高效范围查找,会全表扫)因为对于分组、排序、比较,哈希型索引的时间复杂度会退化到O(n),而这类查询实际业务中会经常出现。(hash计算hash索引值来放数据的,是散列无序的,可以看作只有等号查询效率高,无法高效范围查找,而B+树是一次查到子节点中最小值后再通过链表后移N位,就能全查到范围内所有的N个值)
?为什么不用平衡二叉树?
平衡二叉树 查询效率还可以,缺点:
(1)(支持范围查询,但删改会回旋保持平衡)维护平衡的成本代价很高,每次删除一个节点或者增加一个节点的话,需要一次或者多次的左旋,右旋等去维护“平衡”状态,
(2)(如果在最下面,效率更低)然后是查询的效率不稳定,还是会有看运气的成分在里面,如果在最下面,那就慢
(3)(AVL树的每个节点只有两个分支,高度如果很高,查询IO次数会越多)然后是如果节点很多的话,那么这个AVL树的高度还是会很高的,那么查询效率还是会很低(B+树是每个节点可以多个叉)
(4)一个父节点只能有两个子节点,不如B树节点多,树高IO查询次数多
?为什么不用B-树?
(高度会矮一点,但每节点存数据--要中序遍历)B-树每层节点数目非常多,层数很少,相比二叉树减少了磁盘IO次数,但每个节点都存储数据,查询需要进行中序遍历,范转查询不快,并不是快速定位数据的最佳方式。
B+树相比于B树,有什么优势呢:
1.单一节点存储更多的元素,使得查询的IO次数更少。
2.所有查询都要查找到叶子节点,查询性能稳定。
3.所有叶子节点形成有序链表,便于范围查询。
4、因为 B 树非叶子节点保存数据,导致在非叶子节点中能保存的指针数量变少,指针少的情况下要保存大量数据,只能增加树的高度,导致 IO读写操作变多(高度为N就IO读写N次),查询性能变低。
为什么生产环境中B+树的高度总是3-4层?
一、理解页是什么
1)页(也称表页)是innoDB中管理储存空间的基本单位,最下层的一页(即叶子节点)可以包含多条数据,查找时都是先从根节点页二分找到对应的非叶子节点页,再二分找到叶子节点页,再二分找到这页的具体行。
2) InnoDB 存储引擎也有自己的最小储存单元——页(Page),一个页的大小是 16K。
3)每一页中的每条数据之间,是单向链表,也就是每条记录只记录下一条记录位置。
但是页是双向链表,每页会记录上一页下一页
二、B+树的高度总是3-4层的原因
示例:
mysql规定,一页默认是16K,每个叶子节点所在的页,能存的数据行数=16/每行数据大小。每层有1170个分支。
按一行数据1kb为例,n层: 16*叶子节点= 16*(n-1)*1170*
3层B+树,以bigint 为主键,能存2千多万条数据,查3次IO。实际上,平时不可能1kb,所以大多数是3层就够了。
即16*(n-1)*1170=16*(3-1)*1170=21902400=2千多万
?mysql innodb和myisam这两个引擎的区别?
都是通过B+树来实现的,(都继承了AVL->B树的优点:支持范围查询->和树的高度较低,IO效率高),同时只有叶子节点存数据且有链表,查询效率比较高。
但是
1、数据文件本身所在地不同、辅助索引性质不同-------innodb分聚簇索引(主索引)和非聚簇索引(二级主索引/辅助索引).myisam没有聚簇索引。
innodb里的聚簇索引在叶子结点存的是主键索引和数据data行。而innodb非聚簇索引在叶子结点存的是普通索引和对应数据data行关联的主键索引值,还需要通过这个主键索引值去主键B+树里取一次数据data行.
;而myisam所有的value值不是数据,是数据地址(数据所在的别处磁盘地址),需要通过个数据地址去找到对应的数据。
myisam也有辅助索引,与主键索引没有任何区别,只不过主键索引的key是唯一的,而辅助索引的key可以重复
它们的数据存储文件里分别包含:
1)myisam
u_user.frm //该文件存储的是,当前表的信息,比如字段类型等等
u_user.MYD //该文件存储的是,数据
u_user.MYI //该文件存储的是,索引(Myisam把数据和索引分开,插入修改是很快的,但查询很慢)
2)innodb
u_user.frm //该文件存储的是,当前表的信息,比如字段类型等等
u_user.idb //该文件存储的是,带有索引的数据(索引和数据是在一起的)
2、大容量的数据集时趋向于选择Innodb,Innodb支持事务处理和故障的恢复。Innodb可以利用数据日志来进行数据的恢复。主键的查询在Innodb也是比较快的,更新也涉及到查询条件,也用它(查询快或使用聚集索引的原因:比如查询多个值id=1,2,3对应的数据,Innodb会一次从磁盘上提取出整棵B-TREE装入内存,还带有数据。MYIASM没有使用聚集索引,读取每个数据1,2,3都会访问磁盘,访问三次的IO读效率很慢)
3、读大于写操作时(不需要事务)首选MYIASM,大批量的插入语句(不需要事务)首选MYIASM
3.1、大数据量(读多写少)查询适合用MYIASM(因为没行级锁,不用等待,写读都超快;另外写少不用考虑锁安全,就不必用innodb);
3.2、而写多,且要求数据安全,就使用innodb
?MySQL是如何定位慢查询,举例说明怎么处理?
(实际开发中sql语句怎么优化的:先捕获低效SQL→慢查询优化方案→慢查询优化原则)。
1)开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。(set global slow_query_log='ON';set global long_query_time=1;开设并设置1秒以上为慢查询,然后重启)
2)监控慢查询日志,找到慢查询语句,针对慢查询作出优化方案
示例:
比如,我们在做一个交易订单推送核算中心的功能,里面涉及一个把每天的数据查出来,交易表日积月累慢慢的交易量就大了,前期不卡,到后期量大了就卡得不行。生产一般都是开启慢查询日志且监控着的,DBA发现该慢查询语句,会告警我们。此时可以对语句作出优化,比如把时间切分成很多小段,24小时分成10分钟查一次,查多次来解决这个问题。
?mysql二级索引叶子节点里的插值时是怎么排序的?(这个面试没答好,被问过)
由于遍历的二级索引有不唯一可重复的情况,二级索引值不相等时,按二级索引值的顺序遍历。二级索引值相等时,按主键的索引值顺序遍历。(如果是Myisum主键的索引值就换成物理地址)
基本可以确定二级索引不仅按索引列排序,还按主键值排序了
?哪些情况会导致索引失效?注意那些事项??
1.(非最左前缀原则)对于多列索引即联合索引,不是使用的联合索引的第一部分,则第一部分不会使用索引(是第一部分全表扫,第二部分如果有还会走索引)
2.like查询以%开头(但主健这样查是会走索引的)
3.NOT IN和 !=会导致索引失效(NOT IN可以NOT EXISTS代替,id != 3则可使用id>3 or id < 3)
4.(有or,只有所有条件字段都加索引才有用)如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) ,要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
5.(null值不走索引)索引无法存储null值,所以查null值不走索引
6.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
7.(字符串无引号)如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
巧记:左 %!or null 不使用 引号
附:
并不是 is null ,is not null走和不走索引是和数据量或者和其他元素有关系(这里我只是测试到和数据量有关系) sql优化器在执行的时候会计算成本,其实和基数,选择性,直方图有关,其实就是看你所搜索的部分占全表的比例是走索引还是全表成本低
<> 和!= 是同一个意思.
?最左前缀是什么,原理呢?
最左前缀是针对组合索引而言的,以index1 (a,b,c)为例建立这样的索引相当于建立了索引a、ab、abc三个索引。
实际上是一个。具体实现先不讨论。入库时就按三个索引的逻辑入库,查的时候,有前面的索引自然就能走索引查询。
原理:索引是(叶子节点链表)有序的,index1索引在索引文件中的排列是有序的,首先根据a来排序,把a开头的放前面,然后才是根据b来排序,最后是根据c来排序,如果没有a,就会遍历所有
?MySQL缓存机制?
参考:https://blog.csdn.net/weixin_34275734/article/details/89624537
1.MySQL查询缓存是什么?
2. MySQL缓存规则是什么?
3. 如何配置和缓存MySQL缓存
4. MySQL缓存的优缺点
5. 生产要不要开启MySQL缓存
按道理,MySQL Server默认打开,是鼓励用户使用缓存,但是大拿们却建议关闭此功能,并且国内各个云厂商提供的MySQL云服务中默认都是关闭这个功能
MySQL的一个缓存区域,用来缓存Query的整个结果集,且共享给所有客户端。
对查询sql进行Hash计算后,把得到的hash值与Query查询的结果集对应存放在Query Cache中.
MySQL Server会对接收到的每一个SELECT 语句通过特定的Hash算法计算该Query的Hash值,然后通过该hashi值到Query Cache中去匹配。
- 如果没有匹配,将这个hash值存放在一个hash链表中,并将Query的结果集存放到cache中,存放hashi值链表的每个hash节点存放了相应Quey结果集在cache中的地址,以及该query所涉及到一些table相关信息;
- 如果通过hash值匹配到了一样的Query,则直接将cache中相应的Query结果集返回给客户端。
![](https://i-blog.csdnimg.cn/blog_migrate/742b890f05c891235a347896f2fd8386.png)
![](https://i-blog.csdnimg.cn/blog_migrate/d5879f928a0ffa3d6d2b9449ee50aaef.png)
?索引下推是什么?好处是什么?(减少回表次数---所以走主健不回表就不会触发索引下推的好处)
一、概念:索引下推(index condition pushdown )简称ICP,在Mysql5.6后增加的功能;(我用的5。6。25)
不使用ICP--------使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎查出所有索引的数据,直接返回给MySQL服务器,让sql服务器做最终全data结果,做条件帅选数据
使用ICP-------当前二级索引存储引擎树内部查出符合条件的数据,再做回表,再返回给MySQL服务器-(关键好处是减少回表次数):
二、索引下推ICP,带来的好处:
1、IO读写减少---数据量先在存储引擎帅选时减少了,要读出来的量也少了;
2、二级索引的回表查询量减少----数据量先在存储引擎帅选时减少了,要回表读的量也少了;
?覆盖索引是什么?好处是什么?
一、覆盖索引是什么---比如我只想查询一个字段普通索引1 ,即select 普通索引1 ,查询的字段时刚才覆盖到索引,那就不会走回表查询聚簇索引,少一次查询IO操作
一、覆盖索引好处
----减少IO操作,辅助索引中就可以得到查询的记录,不需要查询聚簇索引中的记录。
----避免回表查询
?一个Join查询?
-----------------------------------------表设计(和树无关)-----------------------
下面讲的,只是把数据想像成树一样的结构关系存在于表中,其实不像索引树那样是真实结构。
邻接表
(仅支持二层父子关系表)当前表再添加一个parent_id字段存相邻父评论,只能查到最近两层的
SELECT c1.*, c2.* FROM comments c1 LEFT OUTER JOIN comments c2 ON c2.parent_id = c1.comment_id and comment_id='张三1';
闭包表:
(树形结构存诸设计--多层父子关系)用第二张表,记录了树中所有节点间的关系,而不仅仅只有那些直接的父子节点。也就是插入第N层子数据进评论表,就得往关系表插入N-1条关系记录。
下面示例
create table `node` (
`id` int(10) not null comment '评论id',
`name` varchar(50) default null comment '评论内容',
primary key (`id`)
) engine=innodb default charset=utf8 comment '评论表';
create table `tree_path_relation` (
`anc` int(10) not null comment '祖先节点',
`des` int(10) not null comment '子孙节点',
`pc` int(1) default null comment '父子节点距离 N至0',
primary key (`anc`,`des`)
) engine=innodb default charset=utf8 comment '父子关系表';
---------------------------------mysql事物日志log---------------------------------------
一、redolog是重做日志,提供前滚操作
记录的是数据页的物理修改,恢复到最后一次提交的数据页原样
二 、undolog是回滚日志,提供回滚操作。
根据每行记录进行记录,用来回滚
行记录到某个版本
三、对比binlog、redolog、undolog:
bin log主要用来做数据归档,但是它并不具备崩溃恢复的能力。
(redo log重做日志有恢复能力-且是事物已提交,所以才需重做;undo log只是不操作-回滚,事物没提交)
innodb事务日志包括redo log和undo log
-----------------------------------------主从复制---------------------------------------
核心点:主从复制原理;从库延迟原因(写并发高);提高效率-半同步复制(写并发高效果不明显);并行复制(单库并发高效果不明显);
?mysql备份有哪些方式?
逻辑备份:使用mysql自带的mysqldump工具进行备份。备份成sql文件形式;
物理备份:直接拷贝mysql的数据目录。(只适用于myisam类型的表。这种类型的表是与机器独立的)
双机热备份:mysql数据库提供了一种主从备份的机制(也就是双机热备)
优点:适合数据量大的时候。大的互联网公司对于mysql数据备份,都是采用热机备份。搭建多台数据库服务器,进行主从复制。
?主从复制和读写分离一样吗?主从复制的原理是什么?
主从复制只用到了binlog和二进制文件(mysql自带功能,不会需要中间件);
读写分离会用到中间件来实现的(服务器中间件mycat;客户端中间件sharding jdbc)。
(因为binlog日志是记录SQL执行命令的,主从复制并没有依赖任何三方技术,它只是用mysql自带的binlog在salve端重复执行一遍就达到了主从复制)
主从复制的原理或流程是(下面有详细介绍):从节点发起一个IO线程去建立长连接请求得到binlog,主库开启logDump线程把本地binlog给长连接,从节点的I/O Thread 写到从节点的中继日志文件 relay log,从节点再开一个SQL线程,把拿到的中继日志文件增量式地解析成 SQL 语句在从新执行一遍(
主库的配置是:log-bin=mysql-bin
)
?主从复制的作用,为什么需要主从复制,实现什么目的?
集群负载均衡、高可用、故障转移、数据备份、读写分离才
?主从复制中为什么会产生延迟?
主从库几毫秒延迟是不用考虑的,如果网络原因出现几秒的延迟就得考虑数据库优化了。
延迟原因:
当主库的TPS并发较高时,产生的DDL数量超过slave一个sql线程所能承受的范围,
那么延时就产生了,当然还有就是可能与slave的大型query语句产生了锁等待
?若主库突然宕机,怎么解决主库数据丢失问题?
开启 MySQL 支持的半同步复制:
主库写入binlog日志后,就会强制此时立即将数据同步到从库,
从库将日志写入自己本地的relay log中继日志文件后,会返回一个ack响应给主库,
主库接收到至少一个从库的ack之后才会认为写操作完成
?主从延迟较为严重,有没有什么解决方案(先插后查的情况)?
1)分库,将一个主库拆分为多个主库,每个主库的写并发就减少了几倍(IO线程复制量拆分变少了),此时主从延迟可以忽略不计(binlog的复制在主从之间也会进行,增加了复制流程难度)。
2)打开 MySQL 支持的并行复制,对多个主库并行主从复制。如果说某个库的写入并发就是特别高,单库写并发达到了 2000/s,并行复制还是没意义(这里只是,从库开启多个SQL线程并行读中继日志文件relay log中多个主库的日志,然后并行重写到当前的从库。主库若写的压力量太大,需要IO复制的量也大了,延迟变长)。
3)重写代码1,写代码的同学,要慎重,插入数据成功时不要去立马查询,直接更新。
4)重写代码2,如果确实是存在必须先插入,要求立马就查询到,然后立马就要反过来执行一些操作,对这个查询设置直连主库。不推荐这种方法,---你要是这么搞,读写分离的意义就丧失了。
5)(从主,二次读取)从库没读到之后再去主库读一下。但是问题是---如有人恶意攻击,就一直访问没有的数据,那主库就可能爆了。
6)(分业务)关键业务读写都由主库承担,非关键业务读写分离。类似付钱的这种业务,读写都到主库;用户个人信息(签名、头像)这种比较不重要的就读写分离,查询都去从库查,毕竟延迟一下影响也不大
7)MQ限流
-----读写分离------
?读写分离的过程及原理?
基于主从复制架构,简单来说,就搞一个主库,挂多个从库,然后我们就单单只是写主库,读从库,然后主库会自动把数据给同步到从库上去.
但是它只能分担访问的压力,分担不了存储的压力,也就是你的数据库表的数据逐渐增多,但是面对一张表海量的数据,查询还是很慢的,所以如果业务发展的快数据暴增,到一定时间还是得分库分表.
?读写分离原理是什么?怎么用的?怎么实现的?
原理是:依赖mysql的主从复制机制配合。比如搞一台服务器作为主库,多台作为从库,我们在写的时候用主库,在读取的时候用从库。
实现(用ShardingSphere):
1、配置
使用Sharding-JDBC的ShardingSphere中间件(POM导入JAR),application.properties里配置ShardingSphere内嵌进来的参数(# 配置真实数据源、#主数据库、# 从数据库、# 配置读写分离、# 配置从库选择策略,提供轮询与随机,这里选择用轮询、)具体如下:
datasource.names=master,slave 设置所有数据库名称,从库可以多个
masterslave.master-data-source-name=master 设置主库名称
masterslave.slave-data-source-names=slave设置从库名称(指向谁,就开启了读写分离)
load-balance-algorithm-type=round_robin 设置读哪个从库的策略,这表示从库循环策略,还有一种是随机
2、无需改代码,ShardingSphere拦截SQL解析时会自动把DML语句指向配置的主库(上一步配置文件里开启了),(只需要在项目中集成主和从的数据源,Sharding-Jdbc自动根据DML和DQL 语句类型连接主或者从数据源。)
(Mycat方式:客户端接入Mycat读写分离时,可以使用AOP代理特定的规则(get/update开头的方法),来指向主从库)
除了这个之外,还有其他两个方法:配置多数据源和mycat,有代码嵌入缺点,不是最优选
-----mysql 分库分表--源来----
?分表会带来哪些不好的影响?
拆分规则也可能导致某个业务需要同时查询所有的表然后进行聚合。增加了---联合查询
如果需要排序和函数计算则更加复杂。增加了---排序和函数计算复杂度
所以不到万不得已可以先不必拆分。
?垂直拆分和水平拆分有什么区别,各自的拆分规则是怎么样的?
如果表的字段比较多,那么会用到------垂直拆分;
如果数据量比较多(行级),那么会用到------水平拆分;( MySQL 中当数据量达到千万级,一般2000W必须得分了,不是很卡的话,2000W一次分合适;一直不卡的话,你随便存,10亿都可不分)
它们的一般拆分规则如下:
1、垂直拆分(看情况分库还是分表):
按业务来拆分(和二楼相似);
把不常用的字段拆分,单独放在一张表;
把text,blob等大字段拆分出来放在附表中;
组合查询关系拆分,相关列放在一张表中;
按修改查询的冷热分离拆分(商品的特有属性修改少查询多,叫做冷数据,适合用 MyISAM 存储引擎;而商品的库存等修改多查询少,叫做活跃数据或者热数据,适合用 InnoDB)
2、水平拆分(看情况分库还是分表):
一致性Hash分片(取模均分)的方式,分片数为N张表,对N取模--比较均匀(但要注意,主键取模不能新增服务器)
通过地区,年份等字段来进行(区间)归档拆分;
范围分片(常见的按时间段来分)
枚举分片(比如QQ按地区收索,地区是常量)
?垂直拆分的优缺点及实现?
- 垂直拆分的优点
可以使得行数据变小,一个数据块就能存放更多的数据,在查询时就会减少I/O次数(每次查询时读取的Block就少)
可以达到最大化利用Cache的目的,具体在垂直拆分的时候可以将不常变的字段放一起,将经常改变的放一起
数据维护简单
- 垂直拆分的缺点
主键和部分字段多张表出现,需要管理冗余列
会引起表连接JOIN操作(增加CPU开销)可以通过在业务服务器上进行join来减少数据库压力
依然存在单表数据量过大的问题(需要水平拆分)
事务处理复杂
- 分片实现原则:
多字段的大表才分。
开发过程中的举例:
交易系统里,一个表里既有用户数据,也有订单数据,那用户数据放到用户表并放到用户库、把订单数据放到订单表并放到订单库。
(多个微服务系统来操作不同的库)
这种拆分比较简单,就不说了。
?水平拆分的优缺点及实现?
- 水平拆分的优点
不存在单库大数据和高并发的性能瓶颈
应用端改造较少
提高了系统的稳定性和负载能力
- 水平拆分的缺点
分布式事务一致性难以解决(分布式事务难度很大)
跨节点Join性能差,逻辑复杂
数据多次扩展难度跟维护量极大
- 分片实现原则:
- 能不分就不分,分片数量尽量少,均匀,因为查询SQL跨分片越多性能越差
- 分片规则的选择,需要考虑数据的增长模式,数据的访问模式,分片关联性问题,以及分片扩容问题,最近的分片策略为:范围分片,枚举分片,一致性Hash分片(取模均分)
一个事务中SQL尽量不要跨越多个分片
查询条件尽量优化,尽量避免select * 的方式
尽量为频繁使用的查询语句建立索引
通过数据冗余和表分区赖降低跨库join的可能
-----mysql 分库分表--中间件选择----
?MyCat与 Shardingjdbc ,并进行了比较?
MyCAT是服务端中间件(mysql的代理),Shardingjdbc是客户端集成的中间件。
使用mycat时很少或是不需要改代码,使用sharding-jdbc需要修改代码。
下面从几个方面来比较一下,哪个好,选用哪个?
一、基本特点比较
1.sharding-jdbc(sharding-sphere)
优点:
1)可适用于任何基于java的ORM框架,如:JPA、Hibernate、Mybatis、Spring JDBC Template,或直接使用JDBC
2)可基于任何第三方的数据库连接池,如:DBCP、C3P0、Durid等
3)分片策略灵活,可支持等号、between、in等多维度分片,也可支持多分片键。
4)SQL解析功能完善,支持聚合、分组、排序、limit、or等查询,并支持Binding Table以及笛卡尔积表查询。对于sql都是99%支持的
5)性能高,单库查询QPS为原生JDBC的99.8%,双库查询QPS比单库增加94%。
6)就是一个jar包,加强版的JDBC驱动,这种方式,无需额外部署,无其他依赖,DBA也无需改变原有的运维方
缺点:
1)理论上可支持任意实现JDBC规范的数据库。目前仅支持mysql
2)维护会比较麻烦,需要逐个项目的修改配置。不能进行跨库连接,代码需要进行改造。
3)在扩展数据库服务器时需要考虑一致性哈希问题,或者采用分片键局部取模方式,也难免要进行部分的数据迁移。
4)类似sum 这种函数不支持,以avg1 +avg2+avg3/3计算平均值并不正确。(解决方法:需要改写为(sum1+sum2+sum3)/(count1+count2+ count3)。这就需要将包含avg的SQL改写为sum和count)
5)分页问题,limit 10, 10,归并之后再根据排序条件取出前10条数据是不正确的结果。正确的做法是将分条件改写为limit 0, 20(解决方法:条件里加上大于上一次最大的那个ID)
6)联合查询跨库了有的查不到:必须分开查询,先查分片表,再查原生关联表(这表是没有分片的)
2.mycat
优点:
1)支持Mysql集群,可以作为Proxy使用
2)支持JDBC连接ORACLE、DB2、SQL Server,将其模拟为MySQL Server使用
3.自动故障切换,高可用性
4)支持读写分离,支持Mysql双主多从,以及一主多从的模式 ,支持全局表,数据自动分片到多个节点,用于高效表关联查询
5.支持独有的基于E-R 关系的分片策略,实现了高效的表关联查询
6)多平台支持,部署和实施简单
缺点:
1)mycat不支持二维路由,仅支持单库多表或多库单表 由于自定义连接池,这样就会存在mycat自身维护一个连接池,MySQL也有一个连接池,任何一个连接池上限都会成为性能的瓶。
2)mycat不适用场景
1非分片字段查询,无法快速定位至单片上,可能所有片都执行了,会极大消耗Mycat和MySQL数据库资源。
2.分页排序 但Mycat向应用返回的结果集取决于哪个DB节点最先返回结果给Mycat。如果Mycat最先收到DB1节点的结果集,那么Mycat返回给应用端的结果集为 [0,1],如果Mycat最先收到DB2节点的结果集,那么返回给应用端的结果集为 [5,6]。也就是说,相同情况下,同一个SQL,在Mycat上执行时会有不同的返回结果。
3.任意表JOIN 无法跨库join
4.分布式事务 Mycat并没有根据二阶段提交协议实现 XA事务,而是只保证 prepare 阶段数据一致性的 弱XA事务
二、工作原理比较
类似点:SQL 解析 -> SQL 改写 -> SQL 路由 -> SQL 执行 -> 结果归并。
拦截用户的SQL语句,对SQL语句做--改写,然后将此SQL转发真实数据库 --执行,并将返回的结果做适当的--结果归并处理,最终再返回给用户。
不同点:
Mycat是基于 Proxy代理,复写了MySQL协议,伪装成一个 MySQL 数据库
Sharding-JDBC 是基于 JDBC 接口的扩展(增强版数据库驱动),是以 jar 包的形式提供轻量级服务的。
选sharding-jdbc而不选mycat的原因?
巧记:驱动jar与代理;组件集成与单独部署;理论性能是最高的;sharding-jdbc文档写的好
1)sharding-jdbc目前是基于jdbc驱动,无需额外的proxy,以 jar 包的形式提供轻量级服务的客户端中件间;Mycat 是基于 Proxy,它复写了 MySQL 协议,将 Mycat Server 伪装成一个 MySQL 数据库,是一个服务端中件间。
2)sharding-jdbc作为一个组件集成在应用内(但使得开发成本相对较高),更轻量级,使用起来成本并没有那么高。而mycat则作为一个独立的应用需要单独部署,要关注proxy本身的高可用;
3)从架构上看sharding-jdbc更符合分布式架构的设计,直连数据库,没有中间应用,理论性能是最高的
4)sharding-jdbc文档写的好,做调查的时候实在受不了mycat的官网文档
-----mysql 分库分表--Sharding-JDBC的原理配置及实现流程----
?Sharding-JDBC 还有哪些需要注意的?
Shariding-JDBC 本意就是只做分片 + 读写分离,(分库分表、读写分离,还有分布式主键),连接池应该交由连接池去处理,比如druid
分库分表使用 like 查询是有限制的。目前 Shariding-JDBC 不支持 like 语句中包含分片键,但不包含分片键的 like 语句可以正确执行。
Sharding-JDBC 强制查询走主库,大致使用方式如下:HintManager.getInstance().setMasterRouteOnly();--待查
目前支持聚合、分组、排序等查询。暂时不支持 distinct,对 or 的支持也不是特别完善,但是 distinct 和 group by 可以互换,or 也可以用 in 代替。因此绝大部分 SQL 经过修改是可以使用的。
?工作当中,Sharding-JDBC分库分表的接入流程?怎么处理的?
垂直拆分一般是按业务拆,拆到不同的库,上层业务支撑使用微服务,比较好理解。一般分库分表就是指水平拆分,水平式的切片
一、策略的选择,一般有:
范围分片,比如时间来分---账务流水记录,交易系统交易记录--增加机器节点方便--后面单独说扩容
枚举分片,比如按省市区县这种地区来分---用户信息表主要按地区来收索时就可以用,比如添加好友按地区收索
取模分片(一致性hash),比较均匀。比如按ID来分。---不方便拓展机器节点,根据分片数N来做hash取模的,N变了,规则变了,影响历史数据查询
二、怎么配置的具体接入实现
一般要重点考虑三个配置的设置:
1.(分片字段)shardingColumns=order_id,user_id设置分片的字段(复合分片算法类才多个字段)
2.(分片所有表)actual-data-nodes=ds${0..1}.order_${0..1} 设置分片的所有带库信息的表,这里表示用到2个库两个表,这支持inline表达式(也可以这样写,以逗号分隔: ds0.order_0,ds0.order_1,ds1.order_0,ds1.order_1)
3.(分片策略)分片策略的配置选择,各个配置不一样:https://blog.csdn.net/z394642048/article/details/83307196
有的需要重写策略类,有的直接配置sharding支持的通用策略比如取模,默认分库策略
1)重写策略类:
单分片键的标准分片策略---该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
多分片键的复合分片策略---该类需实现ComplexKeysShardingAlgorithm接口并提供无参数的构造器
Hint分片策略---该类需实现HintShardingAlgorithm接口并提供无参数的构造器
2)直接配置通用策略
行表达式分片策略----algorithm-expression= #分片算法行表达式,需符合groovy语法----只能进行简单的取模哈希运算等
分表策略,同分库策略--未知,待补充
配置的具体接入举例:
1)单分片键的标准分片策略
sharding.jdbc.config.sharding.default-database-strategy.standard.sharding-column=id
sharding.jdbc.config.sharding.tables.xmjbq_user.actual-data-nodes=ds$->{0..1}.xmjbq_user$->{0..1}
sharding.jdbc.config.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.hyf.shardingsphere.utils.XmjbqUserDataBasesPreciseShardingAlgorithm,(PreciseShardingAlgorithm子类,----)
2)举例:https://my.oschina.net/xiaominmin/blog/1825947,解释的比较好,
也有另外一种简结一点的说法,分库分表最主要有几个配置:
1. 有多少个数据源
2. 每张表的逻辑表名和所有物理表名
3. 用什么列进行分库以及分库算法
4. 用什么列进行分表以及分表算法
?分库分表写库原理是怎样的,举例说明--1取模?
假设策略是:交易订单表order,分成两个库两个表,按user_id分库,按order_id分表,都采用取模分片算法:https://my.oschina.net/xiaominmin/blog/1825947
各种分库分表都大概是这样流程:(分片规则配置)、SQL解析、SQL改写、SQL路由、SQL执行以及结果归并等模块。
举个实例,交易订单表到瓶颈了,分库分表之后,写库原理怎么实现的呢
比如,insert into order (user_id,order_id) values("0","1");(猜想是直接,查第一个库的第二张表)
0、配置如上面详解,可以选择通用策略配置,也可以自定义重写策略。
1、 SQL解析:
1)SQL被Sharding-JDBC拦截解析,Sharding-JDBC通过sql解析发现sql有一张自已虚拟出来对外的逻辑表名称:order,发现两个写字段: order.userId 和 order.order_id(分库分表是把原先的单库单表通过虚拟的逻辑库逻辑表代替,映射到真正的多库多表去)
结合分库策略DatabaseShardingStrategy的分库分表规则TableRule,通过虚拟逻辑表order,找到order对应的两个物理表集: [order_0,order_1]和两个物理数据库集[dabase_0 , dabase_1]
2)查库:
查逻辑表order的分库策略DatabaseShardingStrategy,找到库的分片列: user_id
本语句的写字段中带有user_id=0,传入参数值(0),比如对2取模,得到分库分片值ShardingValue=0,
调用自定义分库策略类(传输[dabase_0 , dabase_1],ShardingValue)结合,由于取模值0,知道了是表集合[dabase_0]。
3)查表:
查逻辑表order的分表策略TableShardingStrategy,找到表的分片列: order_id
然后从条件 order_id=1拿到值1,传入参数值(1),比如对2取模,得到分表分片值ShardingValue=1,
调用自定义分表策略类(传输[order_0,order_1],ShardingValue),由于取模值1,知道了是表集合[order_1]
4)所以最终分析到要去这个表查:[dabase_0]下的最终物理表集合[order_1]
根据数据源和物理表,得到 DataNode的集合(DataNode由 物理库,物理表 二个字段组成)
根据 DataNode生成表单元TableUnits集合(TableUnit由 逻辑表,物理库,物理表 三个字段组成, 此示例为: order 、dabase_0 、order_1)
2、SQL改写:
->创建DataNodes集合,这里只有一个元素,里面包含属性[dabase_0] 和 [order_1](每一个DataNode表示 xx库.xx表)
->创建TableUnits集合,这里只有一个元素,里面包含属性[dabase_0] 和 [order_1]外,增加了逻辑表名称order(每一个TableUnit表示 逻辑表和xx库.xx表,比DataNode多一个逻辑表属性)
->(创建重写引擎SQLRewriteEngine),根据表单元TableUnits生成对应最终的sql语句执行单元(替换成最终表名)
3、SQL路由:->创建ExecutionUnits执行单元集合,把SQL语句执行单元放里面,这里只有一个元素,里面包含:每一个执行单元表示了在哪个库,执行什么sql语句,这就是SQL路由,路由映射好了每一个SQL的执行方向,
4、SQL执行:线程池并发执行PreparedStatementUnit (ExecutionUnits转换为PreparedStatement,最后又转为PreparedStatementUnit,然后执行)
5、归并集合:把查询结果合并到一起,返回,插入没有这一步
此示例刚好走分片字段,所以只查一个表单元,如果是最坏情况,全没走分片,则可能查所有表单元,会把4个表都查一遍。。
?那分库分表查询原理又是怎样的?(和写库类似)
各种分库分表都大概是这样流程:(分片规则配置)、SQL解析、SQL改写、SQL路由、SQL执行以及结果归并等模块。
举个实例,交易订单表到瓶颈了,分库分表之后,查询原理怎么实现的呢
比如,SELECT * FROM order where user_id=0 and order_id=1。(猜想是直接查第一个库的第二张表)
1、 SQL解析:
1)SQL被Sharding JDBC拦截解析,Sharding-JDBC通过sql解析发现sql有一张自已虚拟出来对外的逻辑表名称:order,发现两个条件: order.userId = 0 和 order.order_id = 1 (分库分表是把原先的单库单表通过虚拟的逻辑库逻辑表代替,映射到真正的多库多表去)
结合分库策略DatabaseShardingStrategy的分库分表规则TableRule,通过虚拟逻辑表order,找到order对应的两个物理表集: [order_0,order_1]和两个物理数据库集[dabase_0 , dabase_1]
2)查库:
查逻辑表order的分库策略DatabaseShardingStrategy,找到库的分片列: user_id
本语句的条件中带有user_id=0,传入参数值(0),比如对2取模,得到分库分片值ShardingValue=0,
调用自定义分库策略类(传输[dabase_0 , dabase_1],ShardingValue)结合,由于取模值0,知道了是表集合[dabase_0]。
3)查表:
查逻辑表order的分表策略TableShardingStrategy,找到表的分片列: order_id
然后从条件 order_id=1拿到值1,传入参数值(1),比如对2取模,得到分表分片值ShardingValue=1,
调用自定义分表策略类(传输[order_0,order_1],ShardingValue),由于取模值1,知道了是表集合[order_1]
4)所以最终分析到要去这个表查:[dabase_0]下的最终物理表集合[order_1]
根据数据源和物理表,得到 DataNode的集合(DataNode由 物理库,物理表 二个字段组成)
根据 DataNode生成表单元TableUnits集合(TableUnit由 逻辑表,物理库,物理表 三个字段组成, 此示例为: order 、dabase_0 、order_1)
2、SQL改写:
->创建DataNodes集合,这里只有一个元素,里面包含属性[dabase_0] 和 [order_1](每一个DataNode表示 xx库.xx表)
->创建TableUnits集合,这里只有一个元素,里面包含属性[dabase_0] 和 [order_1]外,增加了逻辑表名称order(每一个TableUnit表示 逻辑表和xx库.xx表,比DataNode多一个逻辑表属性)
->(创建重写引擎SQLRewriteEngine),根据TableUnits生成对应最终的sql语句执行单元(替换成最终表名)
3、SQL路由:->创建ExecutionUnits执行单元集合,把SQL语句执行单元放里面,这里只有一个元素,里面包含:每一个执行单元表示了在哪个库,执行什么sql语句,这就是路由映射好了每一个SQL的执行方向
4、SQL执行:线程池并发执行PreparedStatementUnit (ExecutionUnits转换为PreparedStatement,最后又转为PreparedStatementUnit,然后执行)
5、归并集合:把查询结果合并到一起,返回
此示例刚好走分片字段,所以只查一个表单元,如果是最坏情况,全没走分片,则可能查所有表单元,会把4个表都查一遍。。
-----mysql 分库分表--Sharding-JDBC接入---工作中的实际经验举例--实战--
?分库分表前可以通过哪些手段来避免分库分表?
除非预估的业务量大到万不得已,切莫过度设计、过早优化(可以先创建分区表来优化:对业务透明,分区只不过把存放数据的文件分成了许多小块,根据一定的规则把数据文件(MYD)和索引文件(MYI)进行了分割,分区后的表呢,还是一张表。但是分区还是不太建义,因为mysql里的分区表对索引的支持不是太好)
规划期内的数据量和性能问题,尝试能否用下列方式解决:
当前数据量:如果没有达到几百万,通常无需分库分表;
数据量问题:增加磁盘、增加分库(垂直拆分,不同的业务功能表,整表拆分至不同的数据库);
性能问题:升级CPU/内存、读写分离、优化数据库系统配置、优化数据表/索引、优化 SQL、分区、数据表的垂直切分;
如果仍未能奏效,才考虑最复杂的方案:数据表的水平切分。(我们公司单表数据量大,查询慢,因为大表上的B+树太大,扫描太慢,甚至可能需要4层B+树,过多查询会导致io随机读写比例高)
?分库分表写库原理是怎样的,举例说明--2时间范围连续分片?
示例:由于账务流水表accountFlows,历史数据基本不会再改动主要是查询历史数据,写入最新的数据。所以采用范围分片,按时间范围水平分片分库分表(以后查询都要求带时间)。
6个月为一张表( 2017.1开始,流水表20来个字段,用户数20W,活跃用户2W,每人3笔,单日交易流水量6W,1月180W,最多半年1000W,至2019-01有近2年=4000W数据了,库物理文件大小200G(应小于100G,拆完后50G),查询已经很卡了,停机单次迁移时间3至4小时,不停机因为要反复检查除了上线时半夜不加班,所以要好几天,但不影响服务不加班很好)
配置设置有哪些表,数据节点,格式为数据库.表名,比如我按时间分完之后,有"db_2017.af_201701,db_2017.af_201707,db_2018.af_201901,db_2018.af_201807"
配置设置分库分片列=createTime,分表分片列=createTime.
分别重写Sharding JDBC的分库策略和分表策略类(都继承PreciseShardingAlgorithm精准分片算法),自定义分库和分表的算法如下:
分库策略(database-strategy)算法是先分析分片字段createTime对应的值(preciseShardingValue)=2018-01-01 00:00:00,转换成日期格式后把期年月日分别解析出来(String.format("%tY", date);),然后拿把原库名+年份,做为分库库名的后缀。
分表策略(table-strategy)算法是先分析分片字段createTime对应的值(preciseShardingValue)=2018-01-01 00:00:00,转换成日期格式后把期年月日分别解析出来(String.format("%tY", date);),然后拿把对应的月份,判断是否小于6,如果小于6,则把原表名+年份+"01",做为分表表名的后缀,反之"07"。
这样的策略的优点和缺点如下,
优点:后续扩容方便,数据不用迁移,直接在配置里增加新的表名,在数据库里新建表和库就好了。(读压力)
缺点:热点问题,访问量分布不均匀,写操作主要集中在最新时间段的库表了(插入全部走这库表,但是我们的数据量不是特别的大,主要是查询慢,写压力还行,效率还是在能接受范围内)
?分库分表过程中遇到的问题?
示例:
limit问题(解决方法:重新写代码,把上一次最近的值记下来带进条件里)
join跨节点联合查询的问题 :联合查询基本不可能,因为关联的表有可能不在同一数据库中。(解决方法:我们有个很少用的核算对账业务使用这个功能接口,没有改全,有个地方联合了流水表和账户表,没有改过来,应该分两次查询.)
我总结了一下分库分表后的坑
1.分完之后尽量按分片键查询,为了避免扫所有分片,如果按非分片键查询,在OLTP环境中得走搜索引擎。数据库和搜索引擎同步数据靠binlog
2.按不同维度查询,比如买家维度和卖家维度查订单。除了走搜索引擎之外,还可以在不同的系统中各写一条订单数据。
3.ID得通过ID生成器。
4.有热点数据问题,比如一个超级买家,买了好多种商品,然而还有不怎么热的买家,没什么订单。解决方法两种,热点数据拿出来放到单独的系统。或者按数据块分片,比如十种商品算一个块,但这种方法具体细节我忘了,只是听人分享过。
5.跨库事务问题(范围分片刚好6月底能遇到),NPC一般不用,补偿是一种方法,TCC是一种方法,TCC的变种,比如SAGA比如XTS。另外,柔性事物BASE努力送达是一种方法,XA要低一级
6.数据扩展问题,可以看看阿里的愚公。我个人觉得还半夜停机维护比较靠谱。
7.分页的坑,前期可以用中间件Limit,中期得走搜索引擎,后期OLAP
8.可用性问题,依赖数据库高可用方案。据说会出现 sharding 算法 会因为网络抖动 造成部分分区错误 导致片出问题
9.配置中心问题。尽量使用配置中心,不要用zookeeper
10.非代理模式,就是JDBC路由模式 每个client都会对 db开启pool ,数据库可能会死在数据库连接上,一种方法是定制
Mysql,设置高低水位,让超过数据库处理能力的数据库连接排队。第二种方法是在JDBC路由模式之上做Mysql的Proxy
?如果想要升级成热点均匀,怎么设计?
可以使用取模分片策略。按自增ID取模,热点均分到各个库表。(我表ID原来是用的自增的雪花算法出来的ID)
这样带来的问题,就是后期机器扩容,迁移数据比较麻烦。
?回顾一下刚才的时间分片策略,你们分库分表后,如何部署上线的?
停机部署法、双写部署法.。我们选择的双写法,双写进MQ队列,如下(只是数据迁完了,短暂切换新代码停机)。
?分库分表的数据如何迁移?
1.停机迁移方案(缺点:累,半夜加班,影响服务)
这是最low的迁移方案了,如果短期停机造不成多大的影响,可以用。
首先停掉机器,挂一个公告,半夜停机升级,将系统全都停掉,不要再有新的数据进来,
然后使用之前写好的迁移程序,连接旧的数据库,将旧数据库里面的数据读出来,然后通过数据分发中间件写到分库分好的数据里面去。(使用canal中间件,迁移了1个晚上,数据量2000W)
然后修改系统是数据库连接、分库分表配置,然后重新上线。
2.双写不停机迁移方案(也有两种,一种是mq队列,但是这个需要mq代码代码入侵,做完还得删一编代码,还有一种采用订阅binlog日志)
1)旧程序双写新数据上线:记录上线那时的max(主键),主键小等于该max(主键)的值,写操作成功之后双写一份到MQ,
2)迁移程序迁移开始:上线一个迁移程序,只迁移主键小于该max(主键)的数据至新库表。(流水表迁移量2000W,100G,3个小时,能不能把双写在晚一点流量少时上线,然后上线那刻把从库切下来一个直接文件导出或canal后再走分片策略?,要保证只多不少,MQ插入判断有就不插,更新发现更新时间更新就不跳过此条更新,不是跳过此用户,但可能一个用户下一条更新有效呢)
3)订阅消费队列至0:将迁移程序下线,写一段订阅程序订阅消息队列中的数据。
4)检测一致性:
(1)先验数量是否一致,因为验数量比较快。至于验具体的字段,有两种方法:
(2.1)有一种方法是,只验关键性的几个字段是否一致。 (2.2)还有一种是,一次取50条拼在一起。用md5进行加密,得到一串数值。新库一样如法炮制,比较两串数值是否一致。如果一致,继续比较下50条数据。如果发现不一致,用二分法确定不一致的数据在0-25条,还是26条-50条。以此类推,找出不一致的数据,进行记录即可(跳过MQ修改过的那些ID,那个数据用户可能还会再改的)。
5)上线分表后的新程序:在夜晚没有人的时候,观察到队里不再写入数据,就开始上线切换分表后的新程序切库。下线双写旧程序(这里免停机得这样:先预发布上线新代码,队列消费完了直接切主生产环境)
?分库分表的数据迁移过程中旧数据A迁到新库,所有迁移还没执行完,A被改了怎么办?
如上,队列可以解决。
?分库分表后,测试怎么测?
部署sharding-proxy服务,需要将对应算法的class文件放到sharding-proxy的conf目录下
Navicat连接上sharding-proxy即可:IP地址就是你部署sharding-proxy的地址,端口就是sharding-proxy服务的端口,默认3307。
?分库分表后,效率提升多少(用压测报告说明)?
哪怕不是走单片库表,也是异步走所有的库表,然后归并,多出来的时间只是归并这部分。
1)模拟线上数据量,插入370万条订单,横向对比改造前、sharding-jdbc方案、mysql分区表三者的效率
改造前压测结果:30个线程,压力测试,聚合报告吞吐量达到1100,
sharding-jdbc方案:30个线程,压力测试,聚合报告吞吐量达到1242.077,
mysql分区表:30个线程,压力测试,聚合报告吞吐量达到1100,
4.1 对比结果
Sharding-jdbc方案:
吞吐量(Throughput)提升8.84%
90%用户的响应时间(90% Line)提升15.24%
响应时间中位数(Median)提升:9.09%
平均响应时间
?分库分表的接入后,如何平滑(不停机)扩容?
范围分片,改代码里的分片数量,待续
枚举分片,改代码里的分片数量,待续
取模分片(一致性hash),改代码里的分片数量。然后去迁移数据&双写数据到新的扩容后的数据库上。反正麻烦。
?第一次分库分表成功之后,以后为什么还是不能无限扩容?分库分表之后存在的数据库多连接数量超限(16384个)问题怎么解决?
问题发生场景:如果是 30 个 RPC 应用,每个 RPC 的数据库连接池大小是 8(8个水平分库),每个 MySQL 需要维护 240 个连接。 2048 个微服务应用,就达到最大连接数是 16384,也就无法继续扩容了。
问题解决方案:
1)只有单元化才能解决这问题,而单元化则带来更多的复杂性。
假设我们根据 Range 分成了 10 个库,现在有 10 个应用,我们让每个应用只连一个库,当应用增多变成 20 个,数据库的连接不够用了,我们就将 10 个库分成 20 个库。
这样,无论你应用扩容到多少个,都可以解决数据库连接数过多的问题。
注意:做这件事的前提是,当用户从 DNS 那里进来的时候,就知道自己要去那个应用,由配置中心广播 分库分表 规则,Sharding-JDBC和dns共用。
2)数据库代理(解决不了问题):代理的连接数也是不能超过 16384 的,正常情况代理是可以的,因为A应用连完数据库之后会释放,B应用进来交给B,并不会每个应用都连接所有的8个库,正常是总量8个库就完全够了。但是如果并发,就会增加连接,并发很多,甚至可以超过 16384,变成 163840,那么 Proxy 也解决不了问题。
?Sharding-JDBC如何解决分布式Mysql唯一性主键ID?
1)UUID(不推荐,随机无序,做主健还有一个坏处:入库性能差,可能多次入库分列,有过多随机写操作(连续的ID会产生部分的顺序写),因为存储时,因无序需要把ID对应的B+树查出来加载到内存,依次查找应该插到哪个位置---由此也能看出Myisam把数据和索引分开,插入修改是很快的,但查询很慢)
2)基于步长设置数据库自增ID (不推荐,增加服务器会改变规则,旧数据查询慢或者得重新迁移,如果预先把步长设长一些,预留未来增加服务器,这样可能会好点)
1)redis(一般,数字ID天然排序且性能优于数据库,但引入新的组件,增加系统复杂度,redis宕机问题)
2)ZooKeeper(一般,数据节点引入了版本,任何更新操作都会引起版本号的变化,达到唯一;每个父节点都会为他的第一级子节点维护一份顺序,达到有序)
3)取当前毫秒数(不推荐,并发量超过1000,会生成重复的ID)
4)雪花算法,(推荐,原理:1个正负符号位,41个用来存放时间戳,10位是机器id,12位作为随机序列---总数位64位。把id按位打散,然后再分成上面这几块,用位来表示状态,这其实就是一种思想。(调用静态方法CommonSelfIdGenerator.setWorkerId(“xxxx”)设置进程ID,)
SnowFlake的优点是,整体上按照时间自增排序,并且整个分布式系统内不会产生ID碰撞(由数据中心ID和机器ID作区分),并且效率较高,经测试,SnowFlake每秒能够产生26万ID左右。
SnowFlake的接入:博客里找了一个例子,
设置好开始时间截,设当前时间的话,41位能用69年 ---选取时间越靠前,则生成id越长,转为字符串最长为19位,最短为9位
如果想生成最长是 16 位,可以把机器位10位舍掉;如果想未来几年服务器拓展很多,可以把机器位10位调大,随机序列位12位调小
10位机器id一般可以设成5位数据中心标识和5位机器标识(Twitter实现)。
改进:如果系统时钟回退,抛出异常,即比较上一次的时间和本次的时间戳,如发现时间回退,则抛出异常
if (timestamp < lastTimestamp) {
throw new RuntimeException(String.format("Clock moved backwards. Failed to generate id for %d milliseconds", lastTimestamp - timestamp));
}
)
?通过雪花算法,怎么实现URL长变短的过程?
使用发号策略。配合redis
1、生成简码算法设计要点如下:
(1)利用分布式发号器(比如雪花算法实现的),递增放号,再将此值转换为62进制(a-zA-Z0-9),会更短一些,比如值为10000,对应转成62进制为sBc。
(2)将短链接服务器域名与放号器的62进制值进行字符串连接,即为短链接的URL,比如:t.cn/sBc。
(3)重定向过程:生成短链接之后,需要存储短链接到长链接的映射关系,即sBc -> URL,映射关系可使用K-V存储,比如Redis或Memcache。
2、访问跳转流程细节如下:
(1)用户访问短链接:http://t.cn/sBc;
(2)短链接服务器t.cn收到请求,根据URL路径sBc获取到原始的长链接(KV缓存数据库redis中去查找):https://blog.csdn.net/xlgen157387/article/details/79863301;
(3)服务器返回302状态码,将响应头中的Location设置为:https://blog.csdn.net/xlgen157387/article/details/79863301;(301是永久重定向,302是临时重定向。301无法统计到短地址被点击的次数)
(4)浏览器重新向https://blog.csdn.net/xlgen157387/article/details/79863301发送请求;
(5)返回响应;
?如何设置分库分表最合适?
关于修改数据的分库分表方案:
如果只是分表或分库,根据分片字段修改即可。如果是分表分库的,需要根据分表的分片字段修改,就比较麻烦了。
---------------------------------mysql事物传播性-------------------------
@Transactional(propagation=Propagation.REQUIRED) (默认会用它)
如果有事务, 那么加入事务, 没有的话新建一个
@Transactional(propagation=Propagation.NOT_SUPPORTED)
容器不为这个方法开启事务
@Transactional(propagation=Propagation.REQUIRES_NEW)
不管是否存在事务,都创建一个新的事务,原来的挂起,新的执行完毕,继续执行老的事务
@Transactional(propagation=Propagation.MANDATORY)
必须在一个已有的事务中执行,否则抛出异常
@Transactional(propagation=Propagation.NEVER)
必须在一个没有的事务中执行,否则抛出异常(与Propagation.MANDATORY相反)
@Transactional(propagation=Propagation.SUPPORTS)
如果其他bean调用这个方法,在其他bean中声明事务,那就用事务.如果其他bean没有声明事务,那就不用事务.
-----mysql事物隔离级别 --分布式事务---一致性-高并发---(中间件级别也算在内,如Sharding-JDBC)
对sql事物隔离级别的理解?
先看三大定义:
一、事务的基本要素(ACID)--ACID又叫酸碱平衡
1、原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节。事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位。
2、一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 。比如A向B转账,不可能A扣了钱,B却没收到。
3、隔离性(Isolation):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱,在A对卡1取钱的过程结束前,B不能向这张卡(甚至另一张卡2,锁表就是)转账。
4、持久性(Durability):事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。
二、事务的并发问题
1、脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到B回滚前的数据是脏数据(错误的数据)
2、不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。(相反,重复读就是,除非自已更新外,要重复读出来一样。如果不一样就是被其他事物修改了,就是不可重复读,锁读能解决)
3、幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。(事物A第二次时,发现多一条事物B插入的数据,就是幻读)
三、隔离级别4大类:
read- unconmmitted:读未提交,在其他事务中对数据进行修改但是未提交更改,此时在此事务进行查询时会查询到其他事务未提交的数据,这是不安全的因为其他事务可以对数据进行回滚。
read-conmmitted:读已提交,其他事务对数据进行修改但未提交的数据在此事务中是不会被读取到的,只有其他事物执行完更改提交事务后才可以读取到。
repeatable:可重复读,在一个事务对某些数据进行连续读取时,其他事务对连续读取的数据进行更改后,事务并不会读取到其他事务更改的数据。
Serializable:直接锁住涉及到的表,其他事务无法访问,并发效率低,容易死锁。
(单独设置隔离级别只对单个连接有效,即一个Connection对象。)
我的理解,先区分未提交读和已提交读,这两个好理解,就是你事物提交了我才能读到你。
再区分可重复读和串行读,分别是行锁读和表锁读,分别解决了不可重复读和幻读。
未提交读--假如事务A读取但未提交(回滚)数据,事务B能读到A的未提交的值,这里---没有锁读---事物回滚前就被读到(脏读)----有“脏读取”、“不可重复读”、“不可重复读”
已提交读--默认,假如事务A读取但未提交(回滚)数据,事务B读不到A的未提交的值,这里--没有锁读---避免了A事物回滚前就被B读到(脏读)----避免了“脏读取”
可重复读--事务A在读到一条数据之后,此时事务B对该数据进行了修改并提交,那么事务A再读该数据,读到的还是原来的内容。----避免了“不可重复读”(这个级别别的事物是可以更改的,除非锁读for update)
串行化读--事务串行执行,假如事务A读取但未提交(回滚)数据,事务B不能插入等任何操作,即A事物----表级锁读了---避免了A先读了但事物完成前被事物B插入且A重读到其新增插入的数据了----避免了“幻读”
怎么实现可重复读的?
答:使用MVCC(多版本并发控制)。InnoDB为每行记录添加了一个版本号(系统版本号),每当修改数据时,版本号加一。
事务开始时拿到当前最新版本号A,事务内都会读取版本号<=这个版本号A的数据,这时就算另一个事务插入一个数据,并立马提交,变更了实际的版本号,新插入这条数据的版本号会比读取事务的版本A号高,因此读取事务读的数据还是不会变
?分库之后,Sharding-JDBC如何解决sql分布式事务不一致的问题?
关于分布式事务,一共有三种:TransactionType.LOCAL、TransactionType.XA、TransactionType.BASE。
XA 多阶段提交的方式,虽然能保障数据的完整性一致性,会极大降低应用性能,所以Sharding-JDBC没采用。而采用了下面两种方式,一种是弱XA,另一种是柔性事务即 BASE(BASE理论是对CAP中一致性和可用性权衡的结果)。
1)LOCAL是本地事务,不用管(默认是LOCAL,如果业务逻辑抛出错误,会对所有的库进行回退操作的(库异常回滚否?),只是断电断网会导致数据不一致)
2)XA/两阶段提交方案,分库之后的数据库各自负责自己事务的提交和回滚,一个提交成功,另一个提交时断电断网导致失败,则会发生数据不一致的问题(失败必然不一致性,但性能较好,应用最广,却不适合解决微服务事务,因为锁定公共资源时间长)
3)柔性事务BASE,也是各个数据库各自负责自己事务的提交和回滚,但实现最大努力送达型,即如果失败,尝试N次,之后入库人工干预,则会发生数据一定时间的不一致,但最终是一致的问题(BASE是基本可用最终一致性,但性能还行)(柔性事务满足BASE)(虽然刚性事务满足全部ACID,用不上,一般采用bas足矣)
默认LOCAL是没有事物的,如果要用他的柔性事物BASE,需要引入他的transaction的jar包才能使用哦。<artifactId>sharding-transaction-spring</artifactId>(我们下一个问题来讲
)
所以,工作中,保证分库分表后的分布式事务的正确用法如下:
选择--柔性事务BASE(XA也行,性能高点,断电不安全,看情况选择,比如金额修改和用户签名信息修改),且在注解事物的时候要注意两点,
1、@ShardingTransactionType需要同Spring的@Transactional配套使用,事务才会生效
2、需要在catch的代码块加上手动调用事物回滚,事务异常回滚才会成功:TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();(有点像原始的rollback)
附:
柔性事务分为 1. 两阶段型 2. 补偿型 3. 异步确保型 4. 最大努力通知型几种 (---柔性事务满足BASE)
-----mysql工作中遇到的一些问题-----
一、遇到,把生产环境包含时间Timestamp类型的sql创建语句,拿到本地来执行不通过。
原因:SHOW VARIABLES LIKE '%explicit_defaults_for_timestamp%';
解决:
set @@session.explicit_defaults_for_timestamp=on;(当前会话内生效)
然后当前会话内再执行你的语句,就忽略时间默认值校验了
二、You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.
原因:因为安装mysql的时候时区设置的不正确 mysql默认的是美国的时区,而我们中国大陆要比他们迟8小时,采用+8:00格式
show variables like '%time_zone%';
解决:
先改时间:
在jdbc连接的url后面加上serverTimezone=GMT即可解决问题,如果需要使用gmt+8时区,需要写成GMT%2B8,否则会被解析为空。再一个解决办法就是使用低版本的MySQL jdbc驱动,5.1.28不会存在时区的问题。
加上这个 ?serverTimezone=GMT%2B8
spring.datasource.url=jdbc:mysql://localhost:3306/exam?serverTimezone=GMT%2B8