Java面试八股文-数据库（四）数据库调优经验

大吉智悠生
104 阅读14分钟

一、索引创建和优化

索引的目的

  1. 快速访问数据表中的特定信息，提高检索速度
  2. 创建唯一性索引，保证数据库表中每一行数据的唯一性。
  3. 加速表和表之间的连接
  4. 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

引对数据库系统的负面影响

  1. 创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；
  2. 索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
  3. 当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

数据表建立索引的原则

  1. 在最频繁使用的、用以缩小查询范围的字段上建立索引。
  2. 在频繁使用的、需要排序的字段上建立索引

什么情况下不宜建立索引

  1. 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。
  2. 对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

索引优化

  1. 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
  2. 尽量使用短索引，如果可以，应该制定一个前缀长度
  3. 对于经常在where子句使用的列，最好设置索引，这样会加快查找速度
  4. 对于有多个列where或者order by子句的，应该建立复合索引
  5. 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
  6. 尽量不要在列上进行运算（函数操作和表达式操作）
  7. 尽量不要使用not in和<>操作

二、SQL慢查询

1. 如何捕获低效sql

  1. slow_query_log

这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。

  1. ong_query_time

当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。

  1. slow_query_log_file

记录日志的文件名。

  1. log_queries_not_using_indexes

这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。

2. 慢查询优化的基本步骤

  1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
  2. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
  3. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
  4. order by limit 形式的sql语句让排序的表优先查
  5. 了解业务方使用场景
  6. 加索引时参照建索引的几大原则

3.优化原则

  1. 查询时，能不要就不用，尽量写全字段名
  2. 大部分情况连接效率远大于子查询
  3. 多使用explain和profile分析查询语句
  4. 查看慢查询日志，找出执行时间长的sql语句优化
  5. 多表连接时，尽量小表驱动大表，即小表 join 大表
  6. 在千万级分页时使用limit
  7. 对于经常使用的查询，可以开启缓存

4. 数据库表优化**

  1. 表的字段尽可能用NOT NULL

  2. 字段长度固定的表查询会更快

  3. 把数据库的大表按时间或一些标志分成小表。将表拆分

    1. 水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。

    2. 垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系

三、读写分离，主从复制

主从复制

数据库主从复制（Database Master-Slave Replication）是一种用于提高数据库性能、可用性和冗余的技术。它允许将一个数据库服务器（主服务器）的数据复制到一个或多个其他数据库服务器（从服务器）上。这有助于分担读取负载、提供灾难恢复和数据备份，以及提高数据库的可用性。以下是数据库主从复制的基本原理：

1. 主服务器（Master）

  • 主服务器是数据库系统的主要实例，它处理所有的写操作（INSERT、UPDATE、DELETE）以及一些读操作。
  • 主服务器上的数据被称为“主数据库”。
  • 主服务器负责将其数据的更改记录到二进制日志文件（Binary Log）中。

2. 从服务器（Slave）

  • 从服务器是主服务器的副本，它接收主服务器上的数据更改并将其应用到自己的数据库中。
  • 从服务器上的数据被称为“从数据库”。
  • 从服务器不允许写入操作，它只处理读取操作。

3. 主从同步过程

  • 主服务器将所有的写入操作记录到二进制日志中。
  • 从服务器连接到主服务器并请求复制二进制日志。
  • 主服务器将二进制日志的内容传输给从服务器。
  • 从服务器将接收到的二进制日志内容逐一应用到自己的数据库，从而保持与主数据库的同步。

4. 延迟

  • 从服务器上的数据复制通常会有一定的延迟，因为数据的传输和应用需要时间。
  • 这意味着从服务器上的数据不会实时反映主服务器上的数据更改，但通常是可控制的。

5. 冲突处理

  • 如果在主服务器和从服务器上同时对相同数据进行修改，可能会出现冲突。
  • 通常，主从复制技术不负责解决冲突，而是要求开发者在应用层面处理这些问题。

主从复制的应用场景包括：

  • 提供读取负载均衡：从服务器可以分担主服务器的读取请求，从而减轻主服务器的负担。
  • 高可用性和灾难恢复：如果主服务器出现故障，从服务器可以升级为新的主服务器，确保数据库服务的连续性。
  • 数据备份：从服务器可以用于定期备份数据，而不会影响主服务器的性能。

读写分离

数据库读写分离是一种数据库架构模式，旨在提高数据库性能、可伸缩性和可用性。它的基本原理是将数据库的读取操作（SELECT查询）和写入操作（INSERT、UPDATE、DELETE）分开，并将它们分配给不同的数据库实例。以下是数据库读写分离的原理：

1. 主数据库（Master）

  • 主数据库是负责处理所有写入操作（INSERT、UPDATE、DELETE）的数据库实例。
  • 主数据库上的数据是最新的，因为它是唯一执行写入操作的地方。
  • 主数据库通常配置为较强大、高性能的服务器，以应对写入负载。

2. 从数据库（Slave）

  • 从数据库是主数据库的副本，负责处理读取操作（SELECT查询）。
  • 从数据库上的数据是主数据库的复制，通常是主数据库的数据的一个近似快照。
  • 从数据库可以配置为多个实例，用于分担读取负载，提高性能。

3. 数据复制和同步

  • 主数据库记录所有写入操作，将这些操作的结果记录到二进制日志（Binary Log）中。
  • 从数据库连接到主数据库，请求复制主数据库上的二进制日志。
  • 主数据库将二进制日志的内容传输给从数据库。
  • 从数据库将接收到的二进制日志的内容逐一应用到自己的数据库，以保持与主数据库的同步。
  • 从数据库的数据复制通常会有一定的延迟，但这不会影响主数据库上的写入操作。

4. 读写分离的负载均衡

  • 通过将读取操作分配给从数据库，主数据库的负载减轻，因为写入操作通常比读取操作更消耗资源。
  • 负载均衡可以通过在应用程序中实现，或者使用负载均衡代理来自动路由读取操作到不同的从数据库。

5. 写入操作的处理

  • 所有写入操作必须发送到主数据库，以确保数据的一致性。
  • 从数据库不允许进行写入操作，只负责读取操作。

6. 高可用性和灾难恢复

  • 如果主数据库发生故障，可以将一个从数据库提升为新的主数据库，以确保数据库服务的连续性。
  • 这提供了高可用性和灾难恢复的解决方案，因为数据在多个地方进行了备份。

数据库读写分离的优点包括提高性能、分担负载、提供冗余和灾难恢复，但需要注意冲突处理问题，因为写入操作可能在不同的数据库实例上引起冲突。此外，配置和管理读写分离需要详细了解数据库系统的特性和工具。

主从读写相关问题

问题1：master的写操作，slaves被动的进行一样的操作，保持数据一致性，那么slave是否可以主动的进行写操作？

假设slave可以主动的进行写操作，slave又无法通知master，这样就导致了master和slave数据不一致了。因此slave不应该进行写操作，至少是slave上涉及到复制的数据库不可以写。实际上，这里已经揭示了读写分离的概念。

问题2：主从复制中，可以有N个slave,可是这些slave又不能进行写操作，要他们干嘛？

实现数据备份:

类似于高可用的功能，一旦master挂了，可以让slave顶上去，同时slave提升为master。

异地容灾:比如master在北京，地震挂了，那么在上海的slave还可以继续。

主要用于实现scale out,分担负载,可以将读的任务分散到slaves上。

【很可能的情况是，一个系统的读操作远远多于写操作，因此写操作发向master，读操作发向slaves进行操作】

问题3：主从复制中有master,slave1,slave2,...等等这么多MySQL数据库，那比如一个JAVA WEB应用到底应该连接哪个数据库?

我们在应用程序中可以这样，insert/delete/update这些更新数据库的操作，用connection(for master)进行操作，

select用connection(for slaves)进行操作。那我们的应用程序还要完成怎么从slaves选择一个来执行select，例如使用简单的轮循算法。

这样的话，相当于应用程序完成了SQL语句的路由，而且与MySQL的主从复制架构非常关联，一旦master挂了，某些slave挂了，那么应用程序就要修改了。能不能让应用程序与MySQL的主从复制架构没有什么太多关系呢？

找一个组件，application program只需要与它打交道，用它来完成MySQL的代理，实现SQL语句的路由。

MySQL proxy并不负责，怎么从众多的slaves挑一个？可以交给另一个组件(比如haproxy)来完成。

这就是所谓的MySQL READ WRITE SPLITE，MySQL的读写分离。

问题4：如果MySQL proxy , direct , master他们中的某些挂了怎么办？

总统一般都会弄个副总统，以防不测。同样的，可以给这些关键的节点来个备份。

问题5：当master的二进制日志每产生一个事件，都需要发往slave，如果我们有N个slave,那是发N次，还是只发一次？如果只发一次，发给了slave-1，那slave-2,slave-3,...它们怎么办？

显 然，应该发N次。实际上，在MySQL master内部，维护N个线程，每一个线程负责将二进制日志文件发往对应的slave。master既要负责写操作，还的维护N个线程，负担会很重。可以这样，slave-1是master的从，slave-1又是slave-2,slave-3,...的主，同时slave-1不再负责select。 slave-1将master的复制线程的负担，转移到自己的身上。这就是所谓的多级复制的概念。

问题6：当一个select发往MySQL proxy，可能这次由slave-2响应，下次由slave-3响应，这样的话，就无法利用查询缓存了。

应该找一个共享式的缓存，比如memcache来解决。将slave-2,slave-3,这些查询的结果都缓存至mamcache中。

问题7：随着应用的日益增长，读操作很多，我们可以扩展slave，但是如果master满足不了写操作了，怎么办呢？

scale on ?更好的服务器？ 没有最好的，只有更好的，太贵了。。。

scale out ? 主从复制架构已经满足不了。

可以分库【垂直拆分】，分表【水平拆分】。

四、 高并发环境解决方案

优化查询

  • 使用合适的索引：确保数据库表上的查询字段有合适的索引，以加速检索操作。
  • 避免全表扫描：尽量避免执行全表扫描，使用合适的条件来限制检索的数据范围。
  • 使用合适的SQL语句：避免使用过于复杂或低效的SQL语句，优化查询以减少数据库负载。

数据库缓存

  • 使用缓存：将频繁读取但不经常更新的数据缓存在内存中，例如使用缓存系统（如Redis）来存储常用数据，减轻数据库压力
  • MySQL查询缓存：适用于相对静态数据，但在高并发情况下，它可能不太适用，因为缓存失效成本较高。

分库分表

  • 水平分割数据：将大表分成小表，减小单表的数据量，以降低锁竞争和提高查询性能。
  • 垂直分割数据：将表按照字段的访问频率分为多个表，以减少每个表的复杂性。

连接池

  • 使用连接池：使用连接池来管理数据库连接，减少连接的创建和销毁开销，提高性能。

负载均衡

  • 使用负载均衡器：通过负载均衡器将请求均匀分发给多个数据库服务器，以分担数据库服务器的负载。

数据库复制和主从复制

  • 使用主从复制：配置数据库主从复制，将读操作分发到从服务器，以减轻主服务器的负担。
  • 使用多个从服务器：在高并发环境中，可以使用多个从服务器以进一步分担负载。

事务优化

  • 使用合适的事务隔离级别：根据需求选择合适的事务隔离级别，以平衡数据一致性和性能。
  • 批量操作：将多个操作合并为一个事务，以减少事务的开销。

监控和调优

  • 实时监控：使用数据库性能监控工具，实时监控数据库的性能，识别瓶颈和问题。
  • 定期调优：基于监控数据，定期进行数据库的性能调优，优化查询计划、索引和硬件配置。

硬件升级

  • 如果可能的话，升级数据库服务器的硬件，增加内存、CPU 和存储容量，以提高性能。

在高并发环境下，综合考虑这些解决方案，以及根据具体需求采取适当的措施，可以帮助提高 MySQL 数据库的性能和可用性，确保数据的一致性和完整性。

avatar
文章
阅读
粉丝