数据库——数据库性能优化

title: 数据库——数据库性能优化
date: 2024-07-06 12:26:21
tags: 数据库
categories: 数据库
cover: /image/T1.jpg
description: 数据库——MySQL的性能优化

数据库优化

系统的吞吐量瓶颈往往出现在数据库的访问速度上，随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢，因为其数据是存放在磁盘上的，读写速度无法和内存相比，所以使用数据库时，十分有必要了解数据库优化问题。其优化原则为：减少系统瓶颈，减少资源占用，增加系统的反应速度。

数据库结构优化

优化目标：

减少数据冗余：确保相同的数据不会在多个地方重复存储，减少数据更新和维护的复杂性。
避免数据维护异常：包括插入异常、更新异常和删除异常，确保数据库操作的正确性和一致性。
节约存储空间：通过合理设计表结构和数据类型，减少不必要的存储空间浪费。
提高查询效率：优化表结构和索引，加快数据检索速度，提升系统性能。

优化策略：

在数据库设计中，针对字段很多或查询复杂的情况，采用表分解、增加中间表（以及合理添加冗余字段是常见的优化策略。

表分解（Normalization）：
将大表分解成多个小表，每个表专注于一组相关的数据，通过外键关联。这有助于减少数据冗余，提高数据一致性，并使数据库结构更清晰。
增加中间表（Denormalization for Query Optimization）：
为优化查询性能，可以创建中间表来存储频繁联合查询的结果。这样做可以减少查询时的连接操作，提高查询速度，但会增加数据冗余和更新维护的复杂性。
合理添加冗余字段：
在不影响数据一致性的前提下，为了减少查询时的连接操作，可以在表中添加一些冗余字段。这些字段存储了原本需要通过联合查询才能获得的数据，从而提高了查询效率，但增加了数据更新的复杂性和存储成本。

分库分表

分库分表是数据库架构设计的一种策略，旨在通过将大量数据分散存储到多个数据库或表中，以提升系统的性能、可扩展性和可用性。这种方法主要用于处理海量数据和高并发访问的情况。

分库分表通过“垂直切分”或“水平切分”的方式，将数据库或表拆分成多个较小的部分。垂直切分通常按业务模块划分，将不同业务的数据放在不同的数据库或表中。水平切分则根据数据的某种特征（如用户ID、时间等）将数据分散到多个数据库或表中。

这种策略的主要目的是减轻单一数据库或表的压力，提高查询和写入速度，同时支持系统的水平扩展，即通过增加更多的数据库或表来应对数据量的增长。此外，分库分表还能提高系统的可用性，因为即使某个数据库或表出现问题，其他部分仍然可以正常工作。

垂直切分和水平切分

垂直切分：将表中的字段按照业务逻辑进行拆分，将相关的字段放在一起，形成一个新表。这种方式适用于按业务模块划分数据的场景。
水平切分：将表中的数据按照某个规则（如用户ID、时间范围等）分散到多个数据库中，每个数据库存储部分数据。这种方式适用于数据量大的场景，可以有效缓解单一数据库的性能瓶颈。

可能的问题

跨节点查询和事务一致性问题：分库分表后可能会面临跨节点查询和事务一致性的问题。这需要通过合理的设计和优化来解决，如使用分布式事务、数据一致性校验等机制。
数据迁移和扩容问题：在分库分表后，随着业务的发展可能需要进行数据迁移和扩容。这需要提前规划好数据迁移和扩容的策略和方案，以确保系统的平稳运行。

MySQL主从复制

主从复制是指将主数据库（Master）中的 DDL 和 DML 操作通过二进制日志传输到从数据库（Slave）上，然后将这些日志重新执行（重做），从而使得从数据库的数据与主数据库保持一致。MySQL 支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。

主从复制原理：

MySQL 的主从复制是一个异步的复制过程（一般情况下感觉是实时的），数据将从一个 MySQL 数据库（Master）复制到另外一个 MySQL 数据库（Slave），在 Master 与 Slave 之间实现整个主从复制的过程是由三个线程参与完成的，其中有两个线程（SQL 线程和 I/O 线程）在 Slave 端，另外一个线程（ I/O 线程）在 Master 端。

主从复制的作用有：

当主数据库出现问题时，可以切换到从数据库；
可以进行数据库层面的读写分离，实现负载均衡；
可以在从数据库上进行实时数据备份。

MySQL读写分离的实施方案

MySQL 读写分离的实现方式主要基于主从复制，通过路由的方式使应用对数据库的写请求只在 Master 上进行，读请求在 Slave 上进行。

具体地，有以下四种实现方案：

方案一：基于 MySQL proxy 代理

在应用和数据库之间增加代理层，代理层接收应用对数据库的请求，根据不同请求类型（即是读 read 还是写 write）转发到不同的实例，在实现读写分离的同时可以实现负载均衡。MySQL 的代理最常见的是 mysql-proxy、cobar、mycat、Atlas 等。
方案二：基于应用内路由

基于应用内路由的方式即为在应用程序中实现，针对不同的请求类型去不同的实例执行 SQL。具体实现可基于 spring 的 aop：用 aop 来拦截 spring 项目的 dao 层方法，根据方法名称就可以判断要执行的类型，进而动态切换主从数据源。
方案三：基于 MySQL-Connector-Java 的 JDBC 驱动方式

Java 程序通过在连接 MySQL 的 JDBC 中配置主库与从库等地址，JDBC 会自动将读请求发送给从库，将写请求发送给主库，此外， MySQL 的 JDBC 驱动还能够实现多个从库的负载均衡。
方案四：基于 sharding-jdbc 的方式

sharding-sphere 是强大的读写分离、分表分库中间件，sharding-jdbc 是 sharding-sphere 的核心模块。