Mysql面试题十三-优快云博客

本文链接：https://blog.youkuaiyun.com/qw_111118/article/details/144303586

大表怎么优化？分库分表了是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？

当mysql单表记录过大时，数据库的CRUD性能明显下降，常见的优化措施如下：

限制数据的范围：禁止不带任何限制条件的查询语句。
读/写分离：数据拆分方案，主库负责写，从库负责读。
缓存：使用mysql缓存，可以对重量级、更新少的数据使用应用级别的缓存。

还有就是通过分库分表的方式进行优化。主要有垂直分库、垂直分表和水平分库、水平分表。

垂直分库

**根据数据库里面数据表的相关性进行拆分。**将关联度低的不同表存储到不同的数据库。

垂直分表

垂直分表是基于数据库中的列进行，某个表字段比较多，可以新建一扩展表，将不经常用或者长度较大的字段拆分到扩展表中。

适用于：

1.如果一个表中某些列常用，另外一些列不常用
2.可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数

优点：

便于开发与维护
避免跨页问题。一条记录太长可能导致一个数据页不能全部覆盖，造成跨页问题。
减少磁盘IO，从而提升数据库性能。（字段越少读取的记录越多，从而提高命中率减少磁盘IO）。

垂直切分的优点：

解决业务系统层面的耦合，业务清晰
高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈。
能对不同业务的数据进行分级管理、维护、监控、拓展等。

垂直切分的缺点：

部分表无法join，只能通过接口聚合方式解决，提升了开发复杂度。
分布式事务处理复杂
依然存在单表数据量过大的问题。

水平拆分使用场景：

当一个应用难以进行细粒度的垂直拆分，或拆分之后数据量行数巨大，存在单库读写，存储性能瓶颈。

水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表只包含一部分的数据，从而使单个表的数据量便小，达到分布式的效果。

3.水平库内分表

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表水平拆分可以支撑非常大的数据量

水品拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库

水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨界点Join性能较差，逻辑复杂。

水平分库分表

将同一个表按不同的条件分散到多个数据库，每个表只包含一部分的数据，从而使单个表的数据量便小，达到分布式的效果。

水平切分的优点：

不存在单库数据量过大、高性能的瓶颈，提升系统的稳定性和负载能力。
应用端不需要拆分业务模块。

水平切分的缺点

给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作。
在许多数据库应用中，这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数。
跨分片的事务一致性难以保证
跨库的join关联查询性能较差
数据多次扩展和维护量极大。

水平切分的常用方式：

根据数值范围进行切分：比如说id或者日期

优点：

单表大小可控
天然支持水平扩展，只需添加节点即可无须对数据进行迁移
使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，避免跨分片查询。

缺点：
热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询。

根据数值取模

一般采用hash取模mod切分。

优点：

数据分片相对均匀，不容易出现热点和并发访问瓶颈。

缺点：

后期分片集群扩容时，需要迁移旧的数据。（采用一致性hash可以降低这种影响）
容易面临跨分片查询的复杂问题。如果频繁用到的查询条件中不带cusno时，将会导致无法定位数据库，从而需要同时向4个库发起查询，再在内存中合并数据，取最小集返回给应用，分库反而成为拖累。

分库分表面临的问题

事务一致性问题
- 分布式事务
  
  当更新内容同步分布在不同库中，不可避免带来跨库事务问题和跨分片事务就是分布式事务，可以使用XA协议的两阶段提交处理。
  
  分布式事务能最大限度保证数据库操作的原子性。但在提交事务时需要协调多个节点，推后事务的提交时间点延长事务的执行时间。导致事务访问共享资源发生冲突和死锁概率增加，节点越多这种影响越强。影响数据库性能。
- 最终一致性
  
  对于性能要求很高，但一致性要求不高的系统，不要求系统的实时一致性，只要在允许的时间内达到最终一致性即可，可采用**事务补偿方式。**事务补充是一种事后检查补救的措施。常见的方法有：对数据进行对账检查，基于日志进行对比。
跨节点关联查询join问题

切分之后，数据可能分布在不同的节点上，此时join带来的问题就比较麻烦，考虑到性能，尽量避免使用join查询。
- 全局表
  
  系统中所有模块都可能依赖的一些表，为了避免跨库join查询，可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改，所以也不担心一致性的问题。
- 字段冗余
  
  反范式设计，利用空间换时间。添加冗余字段减少联合查询。但需要保证冗余字段的一致性。
- 数据组装
  
  在系统层面，分两次查询，第一次查询的结果集中找出关联数据id，然后根据id发起第二次请求得到关联数据，最后将获得到的数据进行字段拼装。
- ER分片
  
  关系型数据库中，如果可以先确定表之间的关联关系，并将哪些存在关联关系的表记录存放在同一个分片上，就能较好避免跨分片join问题。

跨节点的count,order by,group by以及聚合函数问题

这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

数据迁移，容量规划，扩容等问题

淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。