数据库优化

发表于 分类于
1. 读写分离 2. 分库分表 3. 数据库查询优化

1 关系型数据库、非关系型数据库

非关系型数据库也叫NOSQL，采用键值对的形式进行存储。

它的读写性能很高，易于扩展，可分为内存性数据库以及文档型数据库，比如 Redis，Mongodb，HBase等等。

适合使用非关系型数据库的场景：

  • 日志系统

  • 地理位置存储

  • 数据量巨大

  • 高可用

2 数据库中的主键、超键、候选键、外键是什么？

  • 超键：在关系中能唯一标识元组的属性集称为关系模式的超键

  • 候选键：不含有多余属性的超键称为候选键。也就是在候选键中，若再删除属性，就不是键了！

  • 主键：用户选作元组标识的一个候选键程序主键

  • 外键：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。

学号 姓名 性别 年龄 系别 专业
20020612 李辉 男 20 计算机 软件开发
20060613 张明 男 18 计算机 软件开发
20060614 王小玉 女 19 物理 力学
20060615 李淑华 女 17 生物 动物学
20060616 赵静 男 21 化学 食品化学
20060617 赵静 女 20 生物 植物学

  1. 超键：于是我们从例子中可以发现 学号是标识学生实体的唯一标识。那么该元组的超键就为学号。除此之外我们还可以把它跟其他属性组合起来，比如：(学号，性别)，(学号，年龄)

  2. 候选键：根据例子可知，学号是一个可以唯一标识元组的唯一标识，因此学号是一个候选键，实际上，候选键是超键的子集，比如 （学号，年龄）是超键，但是它不是候选键。因为它还有了额外的属性。

  3. 主键：简单的说，例子中的元组的候选键为学号，但是我们选定他作为该元组的唯一标识，那么学号就为主键。

  4. 外键是相对于主键的，比如在学生记录里，主键为学号，在成绩单表中也有学号字段，因此学号为成绩单表的外键，为学生表的主键。

主键为候选键的子集，候选键为超键的子集，而外键的确定是相对于主键的

3 数据库约束

在SQL数据库中，约束是用于确保数据完整性和一致性的规则。它们定义了对表中数据的限制和规定，以确保数据的有效性和准确性。

  1. 主键约束（Primary Key Constraint）： 主键约束用于唯一标识表中的每一行数据，并确保该列中的值不重复且不为空。主键约束通常与表中的主键列相关联，以提供快速的数据检索和关联。

  2. 唯一约束（Unique Constraint）： 唯一约束确保表中某列的值是唯一的，但允许空值。与主键约束不同，唯一约束允许表中存在多个唯一值，但每个值只能出现一次。

  3. 外键约束（Foreign Key Constraint）： 外键约束用于定义表之间的关系，确保引用表中的外键值必须存在于主表中的主键列中。外键约束用于维护表之间的一致性和关联性，以实现数据的引用完整性。

  4. 默认约束（Default Constraint）： 默认约束用于在插入新行时为列提供默认值。如果插入操作未为列提供值，则默认约束将自动分配默认值，确保数据的完整性和一致性。

  5. 检查约束（Check Constraint）： 检查约束用于定义表中列的取值范围或条件，确保插入或更新的数据满足指定的条件。检查约束可以用于限制数据的类型、范围或格式，以确保数据的有效性和准确性。

4 数据库三大范式

第一范式：无重复的列；字段不可分（原子性）第二范式：非主属性完全依赖与主关键字，而非关键字的一部分；有主键，非主键字段依赖主键（唯一性）第三范式：一个数据库表中不包含已在其它表中已包含的非主关键字信息；非主键字段不能相互依赖（每列都与主键有直接关系，不存在传递依赖）

部门表：部门id，部门名称员工表：员工id，部门id，（部门名称）

5 数据库优化

  1. 索引优化，为搜索字段创建索引

  2. 避免使用 Select *，列出需要查询的字段

  3. 读写分离和分库分表

  4. 数据冷热分离

  5. SQL 优化

  6. 深度分页优化

  7. 适当冗余数据

  8. 使用更高的硬件配置

5.1 SQL优化

  • 批量操作

  • 小表驱动大表（小标筛选，再到大表查询）

  • 查询SQL尽量不要使用select *，而是具体字段

  • 避免再where子句中使用or来连接条件，使用union all

6 数据库优化（高并发）

  • 在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层，将高频访问的数据存入缓存中，减少数据库的读取负担。

  • 增加数据库索引，进而提高查询速度。（不过索引太多会导致速度变慢，并且数据库的写入会导致索引的更新，也会导致速度变慢）

  • 主从读写分离，让主服务器负责写，从服务器负责读。

  • 将数据库进行拆分，使得数据库的表尽可能小，提高查询的速度。

  • 使用分布式架构，分散计算压力。

7 读写分离

读写分离主要应对数据库读并发

  • 部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。

  • 保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是我们常说的主从复制。

  • 系统将写请求交给主数据库处理，读请求交给从数据库处理。

8 数据库水平分区、垂直分区

数据库的分区有两种方式：水平分区和垂直分区。

  • 水平分区：根据某个字段的值，将数据分布到不同的表或表中不同的行。

  • 垂直分区：根据某个字段的列值，将数据分布到不同的表中。通过合理地选择分区键，可以有效地管理大量数据]

9 分库分表

分库分表是一种数据库水平拆分的策略，旨在应对数据库规模不断增长导致的性能瓶颈和存储容量限制。这种策略将一个大型数据库拆分成多个较小的数据库实例（分库）和表（分表），从而提高并行处理能力、降低单个数据库实例的负载，提升整体系统的性能和可伸缩性。

分库分表主要解决数据库存储，避免MySQL一张表的数据量过大

[!note] 为什么要分库分表

分库与分表的目的在于，减小数据库的单库单表负担，提高查询性能，缩短查询时间。

通过分表，可以减少数据库的单表负担，将压力分散到不同的表上，同时因为不同的表上的数据量少了，起到提高查询性能，缩短查询时间的作用，此外，可以很大的缓解表锁的问题。 分表策略可以归纳为垂直拆分和水平拆分： 水平分表：取模分表就属于随机分表，而时间维度分表则属于连续分表。 如何设计好垂直拆分，我的建议：将不常用的字段单独拆分到另外一张扩展表. 将大文本的字段单独拆分到另外一张扩展表, 将不经常修改的字段放在同一张表中，将经常改变的字段放在另一张表中。 对于海量用户场景，可以考虑取模分表，数据相对比较均匀，不容易出现热点和并发访问的瓶颈。

库内分表，仅仅是解决了单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此并不能减轻 MySQL 服务器的压力，仍然存在同一个物理机上的资源竞争和瓶颈，包括 CPU、内存、磁盘 IO、网络带宽等。

分库与分表带来的分布式困境与应对之策 数据迁移与扩容问题----一般做法是通过程序先读出数据，然后按照指定的分表策略再将数据写入到各个分表中。 分页与排序问题----需要在不同的分表中将数据进行排序并返回，并将不同分表返回的结果集进行汇总和再次排序，最后再返回给用户

[!question] 分库分表后如何查询订阅数据库binlog日志，通过canal写到ElasticSearch，建立要搜索字段与数据库的映射

9.1 分库

分库 就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。

垂直分库 就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。

水平分库 是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。

9.2 分表

分表 就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

垂直分表 是对数据表列的拆分，把一张列比较多的表拆分为多张表。
水平分表 是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。

水平拆分只能解决单表数据量大的问题，为了提升性能，我们通常会选择将拆分后的多张表放在不同的数据库中。也就是说，水平分表通常和水平分库同时出现。

9.3 分片算法

  • 哈希分片：求指定分片键的哈希，然后根据哈希值确定数据应被放置在哪个表中。哈希分片比较适合随机读写的场景，不太适合经常需要范围查询的场景。哈希分片可以使每个表的数据分布相对均匀，但对动态伸缩（例如新增一个表或者库）不友好。

  • 范围分片：按照特定的范围区间（比如时间区间、ID 区间）来分配数据，比如 将 id 为 1~299999 的记录分到第一个表， 300000~599999 的分到第二个表。范围分片适合需要经常进行范围查找且数据分布均匀的场景，不太适合随机读写的场景（数据未被分散，容易出现热点数据的问题）。

  • 映射表分片：使用一个单独的表（称为映射表）来存储分片键和分片位置的对应关系。映射表分片策略可以支持任何类型的分片算法，如哈希分片、范围分片等。映射表分片策略是可以灵活地调整分片规则，不需要修改应用程序代码或重新分布数据。不过，这种方式需要维护额外的表，还增加了查询的开销和复杂度。

  • 一致性哈希分片：将哈希空间组织成一个环形结构，将分片键和节点（数据库或表）都映射到这个环上，然后根据顺时针的规则确定数据或请求应该分配到哪个节点上，解决了传统哈希对动态伸缩不友好的问题。

9.4 要点

  1. 水平拆分： 分库分表是水平拆分的一种形式，与垂直拆分（按照业务模块划分表）不同。水平拆分指的是将一个表中的数据按照某个规则分散存储在多个数据库实例或表中。

  2. 分库： 将原来的单个数据库拆分成多个独立的数据库。每个数据库实例都可以独立运行在不同的物理服务器上，从而提高并发处理能力。分库通常采用数据划分的规则，比如按照用户ID的取模或哈希值进行分库。

  3. 分表： 在每个数据库中，原来的大表被拆分成多个较小的表。分表的常见策略包括按照时间范围、按照业务模块或按照数据范围等。分表可以减小每个表的数据量，提高查询性能。

  4. 数据一致性： 在分库分表的架构中，需要考虑数据一致性的问题。因为数据分散在不同的数据库和表中，应用程序需要确保对跨库、跨表的事务有良好的处理机制。

  5. 全局唯一标识： 在分库分表的情况下，通常需要一个全局唯一的标识符，以便在多个数据库和表中唯一标识一条记录。这可以是通过分布式ID生成器、UUID等方式实现的。

  6. 路由策略： 应用程序需要实现合适的路由策略，确保查询时能够正确定位到需要的数据库和表。路由策略可以基于数据划分的规则，例如用户ID的哈希值。

  7. 动态扩展： 分库分表的优势之一是能够相对容易地进行水平扩展。当数据量增加时，可以通过增加新的数据库实例或表来进行扩展，而不必改变整体系统架构。

10 数据冷热分离

数据冷热分离是指根据数据的访问频率和业务重要性，将数据分为冷数据和热数据，冷数据一般存储在存储在低成本、低性能的介质中，热数据高性能存储介质中。

  • 优点：热数据的查询性能得到优化（用户的绝大部分操作体验会更好）、节约成本（可以冷热数据的不同存储需求，选择对应的数据库类型和硬件配置，比如将热数据放在 SSD 上，将冷数据放在 HDD 上）

  • 缺点：系统复杂性和风险增加（需要分离冷热数据，数据错误的风险增加）、统计效率低（统计的时候可能需要用到冷库的数据）。

11 数据库查询优化

  • 索引优化：前缀索引优化；覆盖索引优化；主键索引最好是自增的；防止索引失效；

  • SQL查询优化：避免使用SELECT *，而是选择需要的列。避免在WHERE子句中使用函数或运算，这可能会导致索引失效。使用LIMIT分页查询时，注意性能问题，可以考虑使用索引覆盖扫描或其他分页技术。

  • 启用查询缓存：启用MySQL的查询缓存功能，缓存频繁查询且结果不经常变动的查询结果。注意在数据变动频繁的场景下，查询缓存可能会成为性能瓶颈，需要谨慎使用。

  • 分区和分表：对大表进行分区，将数据水平分割到不同的物理区域，提高查询性能。使用分表技术，如垂直分表或水平分表，将一个大表拆分成多个小表。

[!question] 假如你所在的公司选择MySQL数据库作数据存储，一天五万条以上的增量，预计运维三年，你有哪些优化手段？

  • 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
  • 选择合适的表字段数据类型和存储引擎，适当的添加索引。
  • MySQL库主从读写分离。
  • 找规律分表，减少单表中的数据量提高查询速度。
  • 添加缓存机制，比如Memcached，Apc等。
  • 不经常改动的页面，生成静态页面。
  • 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE。

12 MySQL慢查询排查

  1. 开启慢查询日志：

  • 确保MySQL的慢查询日志已经开启。通过查看slow_query_log和slow_query_log_file变量来确认。

  • 如果没有开启，可以在MySQL配置文件（如my.cnf或my.ini）中设置这些变量，然后重启MySQL服务。

  1. 分析慢查询日志：

  • 使用mysqldumpslow或其他慢查询日志分析工具来查看和分析慢查询日志中的条目。

  • 关注查询的执行时间、锁等待时间以及被调用的次数等信息。

  1. 使用EXPLAIN命令：

  • 对于日志中记录的慢查询，使用EXPLAIN命令来查看查询的执行计划。

  • 分析查询是否使用了合适的索引，以及是否存在全表扫描等低效操作。

  1. 优化查询：

  • 根据EXPLAIN的输出结果，优化查询语句，比如添加或修改索引。

  • 避免在查询中使用*，而是指定需要的列。

  • 减少JOIN操作的数量或复杂性，特别是在大数据集上。

  • 考虑将计算密集型的操作移到应用层进行。

作者：BAT后端A师兄
链接：https://www.nowcoder.com/discuss/597116383192690688?sourceSSR=users
来源：牛客网