Mysql常见QA

2018-02-23 04:07:30 Mysql 833 0

Mysql如何分表分库?

数据库的复制能解决访问问题,并不能解决大规模的并发写入问题,要解决这个问题就要考虑 Mysql 数据切分了

数据切分,顾名思义,就是数据分散,将一台主机上的数据分摊到多台,减轻单台主机的负载压力

实际应用中,一般互联网企业的路线都是先分库再分表,两者结合使用,取长补短,这样发挥了mysql扩展的最大优势,
但是缺点是架构很大,很复杂,应用程序的编写也比较复杂

分表

又叫水平分区 --- 单表记录条数达到百万到千万级别时就要使用分表了

能解决各个模块的之间的频繁交互问题

纵向分表

[分割字段]根据数据的活跃度进行分离(因为不同活跃的数据,处理方式是不同的)

案例

对于一个博客系统,文章标题,作者,分类,创建时间等,
是变化频率慢,查询次数多,而且最好有很好的实时性的数据,我们把它叫做冷数据。
而博客的浏览量,回复数等,类似的统计信息,或者别的变化频率比较高的数据,我们把它叫做活跃数据。
所以,在进行数据库结构设计的时候,就应该考虑分表,首先是纵向分表的处理。

随记

冷数据使用 MyIsam 可以有更好的查询数据
活跃数据用 Innodb 可以有更好的更新速度
其次,对冷数据进行更多的从库配置,因为更多的操作时查询,这样来加快查询速度。对热数据,可以相对有更多的主库的横向分表处理
其实,对于一些特殊的活跃数据,也可以考虑使用 memcached redis 之类的 nosql 缓存,等累计到一定量再去更新数据库

横向分表

根据数据量的规模来划分,保证单表的容量不会太大,从而来保证单表的查询等处理能力

如,用户信息表,user_1user_2
表结构是完全一样,根据某些特定的规则来划分的表,如根据用户ID来取模划分

案例

当博客的量达到很大时候,就应该采取横向分割来降低每个单表的压力,来提升性能
例如博客的冷数据表,假如分为 100 个表,当同时有 100万 个用户在浏览时,
如果是单表的话,会进行 100万 次请求,而现在分表后,就可能是每个表进行1万个数据的请求
(因为,不可能绝对的平均,只是假设),这样压力就降低了很多很多

分库

又叫垂直分区 --- 按照业务模块分多个库,每个库中的表不一样

实现简单,库与库之间界限分明,便于维护,缺点是不利于频繁跨库操作,单表数据量大的问题解决不了

MySQL多表关联查询效率高点还是多次单表查询效率高,为什么?

比如有a,b两张表,两张表都差不多有十几w行记录,需要查询的字段在两张表中,那么是关联查询效率高点还是分别查a,b两张表,再在service层合并数据效率高点?想问问大家在实际开发中都是怎么考量的?

A,B两个表数据规模十几万,数据规模都不大,单机MySQL够用了,在单机的基础上要关联两表的数据,
先说一个极端情况,A,B两个表都没有索引,并且关联是笛卡尔积,那关联结果会爆炸式增长,可能到亿级别,
这个时候网络IO成了瓶颈,这个时候两次十万行结果集的拉去可能远小于1次亿级别的结果集的拉取,那么将关联合并拉到service层做更快。
但实际业务中一般不会有这么蠢的行为,一般关联会有连接条件,并且连接条件上会有索引,
一般是有一个结果集比较小,拿到这个结果集去另一张表去关联出其它信息,
如果放到service层去做,最快的方式是,先查A表,得到一个小的结果集,一次rpc,再根据结果集,
拼凑出B表的查询条件,去B表查到一个结果集,再一次rpc,再把结果集拉回service层,再一次rpc,然后service层做合并,3次rpc,
如果用数据库的join,关联结果拉回来,一次rpc,帮你省了两次rpc,
当然数据库上做关联更快,对应到数据库就是一次blk nested loop join,这是业务常用情况
但是确实大多数业务都会考虑把这种合并操作放到service层,我觉得有几方面考虑:
第一:单机数据库计算资源很贵,数据库同时要服务写和读,都需要消耗CPU,为了能让数据库的吞吐变得更高,
而业务又不在乎那几百微妙到毫秒级的延时差距,业务会把更多计算放到service层做,
毕竟计算资源很好水平扩展,数据库很难啊,所以大多数业务会把纯计算操作放到service层做,
而将数据库当成一种带事务能力的kv系统来使用,这是一种重业务,轻DB的架构思路
第二:很多复杂的业务可能会由于发展的历史原因,一般不会只用一种数据库,一般会在多个数据库上加一层中间件,
多个数据库之间还能做毛的join,自然业务会抽象出一个service层,降低对数据库的耦合
第三:对于一些大型公司由于数据规模庞大,不得不对数据库进行分库分表,这个问题我在《阿里为什么要禁用三表以上的join》上也回答过,
对于分库分表的应用,使用join也受到了很多限制,除非业务能够很好的根据sharding key明确要join的两个表在同一个物理库中
而中间件一般对跨库join都支持不好
举一个很常见的业务例子,在分库分表中,要同步更新两个表,这两个表位于不同的物理库中,为了保证数据一致性,
一种做法是通过分布式事务中间件将两个更新操作放到一个事务中,
但这样的操作一般要加全局锁,性能很捉急,而有些业务能够容忍短暂的数据不一致,怎么做?
让它们分别更新呗,但是会存在数据写失败的问题,那就起个定时任务,扫描下A表有没有失败的行,
然后看看B表是不是也没写成功,然后对这两条关联记录做订正,
这个时候同样没法用join去实现,只能将数据拉到service层应用自己来合并了...

Sql查询优化

优化分组自动后排序

group by 之后会自动排序 我们应该使用默认排序

Select * 
From User 
Group By User.name 
Order By null

优化嵌套查询

where in 语句内部sql会使用索引,而外部不会

Select * 
From a 
Where id  in (
    Select a_id 
    From b 
) 

join 查询更好

Select * 
From a 
Left Join b 
On a.id = b.a_id 
Where a.id is not null
注:若无特殊说明,文章均为云天河原创,请尊重作者劳动成果,转载前请一定要注明出处