MySQL：架构缓存，事务，锁机制，SQL优化，分库分表

燕双嘤

已于 2024-08-13 15:38:34 修改

阅读量1k

点赞数 23

分类专栏： MySQL/数据库原理文章标签： mysql 数据库 db

于 2024-08-06 15:00:23 首次发布

本文链接：https://blog.csdn.net/qq_42192693/article/details/140955745

版权

MySQL/数据库原理专栏收录该内容

23 篇文章 57 订阅

订阅专栏

1，MySQL架构

1.1，体系结构

【网络连接器】客户端连接器（Client Connectors）：提供与MySQL服务器建立的支持。目前几乎支持所有主流的服务端编程技术。

【服务层】服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析查询优化器和缓存六部分。

连接器（连接池）：负责存储和管理客户端与数据路的连接，一个线程负责管理一个连接。
系统管理和控制工具：备份恢复、安全管理、集群管理等。
SQL接口：用于接收客户端发送的各种SQL命名，并且返回用户查询结果。
解析器：分析器负责对 SQL 语句进行解析，验证 SQL 语句的语法和语义正确性，并生成对应的语法树。
查询优化器：优化器负责对语法树进行优化，选择最优的查询执行计划。优化器会考虑查询的复杂度、索引使用情况、数据分布情况等因素，以便提高查询性能。
缓存：缓存机制是由一些列小缓存组成的。比如表缓存、记录缓存、权限缓存、引擎缓存等。查询缓存可以缓存查询结果，以便下次查询时能够快速返回结果。但是，在高并发的场景下，由于查询缓存锁的争用，缓存命中率不高，因此在 MySQL 8.0 中已经被废弃。

【存储引擎】存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异。常见的存储引擎MyISAM、InnoDB。存储引擎是针对表，而不是库。一个库中可以存在存储引擎不同的表。

【系统文件层】该层负责将数据库的数据和日志存储再文件系统上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid文件，socket文件等。

日志文件：错误日志，二进制日志（记录对MySQL数据库执行执行的更改操作，并且记录了语句的发生时间，执行时长；但是它不记录select、show等不修改数据的SQL。主要用于数据库恢复和主从复制），慢查询日志（记录所有执行时间超时的查询SQL，默认10s），事务日志。
配置文件：用于存放MySQL所有的配置信息文件，比如my.cnf、myini等。
pid文件：pid文件时mysqld应用程序在unix/linux环境下的一个进程文件，存放着自己的进程id。
socket文件：socket 文件也是在 Unix/Linux 环境下才有的，用户在 Unix/Linux 环境下客户端连接可以不通过 TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL。
数据文件：db.opt、.frm文件、.MYD文件等。

【问题】一条 SQL 语句在数据库框架中的执行流程？

【答案】

建立连接：通过客户端/服务器通信协议与MySQL建立连接。MySQL客户端与服务端的通信方式是“半双工”。对于每一个MySQL的连接，时刻都有一个线程状态来标识这个连接正在做什么。
查询缓存：如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的SQL语句则会由解析器进行语法语义解析，并生成解析树。
show variables like '%query_cache%';  // 查看缓存是否启用，空间大小，限制等。
show status like 'Qcache%';  // 查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等。
解析器：将客户端发送的SQL进行语法解析，生成解析树。预处理器根据一些MySQL规则进一步检查”解析树“是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的”解析树“。
查询优化器：根据”解析树“生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。
等价变换策略：5=5 and a>5 改成 a>5 ；a<b and a=5 改为 b>5 and a =5；基于联合索引，调整条件位置等。
优化count、min、max等函数：InnoDB引擎min函数只需要找索引最左边，InnoDB引擎max函数只需要找索引最右边；MyISAM引擎count(*)，不需要计算，直接返回。
提前终止查询：使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据。
in的优化：MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变为in (1,2,3) 。
查询执行引擎：执行SQL语句，此时查询执行引擎会根据SQL语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启查询缓存，这时会将SQL语句和结果完整的保存到查询缓存（Cache&Buffer）中，以后若有相同的SQL语句执行则直接返回结果。

1.2，数据库三范式

数据库的三范式（Normalization）是指将关系型数据库中的表设计成一定的规范形式，以减少数据冗余和数据异常，提高数据的完整性和一致性。

第一范式（1NF）：所有的属性都是原子性的，不可再分。即每个属性都只包含一个数据项，如一列中不能同时包含多个值。
第二范式（2NF）：在满足1NF的基础上，对于存在多个候选键的关系，消除非主属性对候选键的部分函数依赖。换句话说，每个非主属性完全依赖于候选键而不是部分依赖。
第三范式（3NF）：在满足2NF的基础上，消除非主属性对其它非主属性的传递依赖。即一个非主属性不能依赖于另一个非主属性。

1.3，缓存

【查询缓存】MySQL 曾经支持查询缓存，但在MySQL 8.0版本中已经被移除。查询缓存的原理是，MySQL会将查询结果缓存起来，当相同的查询再次被执行时，可以直接从缓存中获取结果，而不必再次执行查询。然而，查询缓存存在多种性能问题，因此在现代版本中被弃用。

【InnoDB缓存】

InnoDB 是 MySQL 中最常用的存储引擎，它提供了自己的缓存机制。InnoDB 缓存主要包括缓冲池（Buffer Pool）和日志缓存（Log Buffer）。
缓冲池用于缓存数据页，以减少磁盘 I/O 操作。在查询时，如果数据页在缓冲池中，则可以直接从内存中获取数据，而不必从磁盘读取。
日志缓存用于缓存写入操作的日志记录，以提高事务的持久性。这些日志记录会定期刷写到磁盘。

【Query Cache】MySQL 8.0之前版本支持Query Cache，但在8.0版本中已被移除。Query Cache是一个全局缓存，用于存储查询的结果集。如果相同的查询再次执行，MySQL可以检查Query Cache，如果有匹配的查询，则可以直接返回缓存中的结果。

【键/值缓存】为了提高特定查询或操作的性能，可以使用外部缓存系统，如Memcached或Redis。这些缓存系统允许将特定的查询结果或数据存储在内存中，以供后续请求直接访问，而不必查询数据库。

【操作系统缓存】操作系统本身也提供了文件系统缓存，可以缓存数据库文件的部分内容，从而减少磁盘 I/O。这是由操作系统自动管理的。

2，事务

2.1，基本概念

事务：数据库中一个单独的执行单元（Unit），它通常由高级数据库操纵语言（SQL）或编程语言（Java）书写的用户程序的执行所引起。当在数据库更改数据成功时，在事务中更改的数据便会提交，不再改变；否则事务就取消或者回滚。

事务是数据库应用程序的基本逻辑单元
事务处理技术主要包括数据库恢复技术和并发控制技术，事务是恢复和并发控制的基本单位
数据库恢复机制和并发控制机制是数据库管理系统的重要组成部分

事务和程序是两个概念：

在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。
一个程序通常包含多个事务。

事务属性可以理解成事务的一些基本配置，描述了事务策略如何应用到方法。事务属性包含了5个方面：

是否只读：这是事务的第三个特性，是否为只读事务。如果事务只对后端的数据库进行该操作，数据库可以利用事务的只读特性来进行一些特定的优化。通过将事务设置为只读，你就可以给数据库一个机会，让它应用它认为合适的优化措施。

事务超时：为了使应用程序很好地运行，事务不能运行太长的时间。因为事务可能涉及对后端数据库的锁定，所以长时间的事务会不必要的占用数据库资源。事务超时就是事务的一个定时器，在特定时间内事务如果没有执行完毕，那么就会自动回滚，而不是一直等待其结束。

回滚规则：事务五边形的最后一个方面是一组规则，这些规则定义了哪些异常会导致事务回滚而哪些不会。默认情况下，事务只有遇到运行期异常时才会回滚，而在遇到检查型异常时不会回滚（这一行为与EJB的回滚行为是一致的）。但是你可以声明事务在遇到特定的检查型异常时像遇到运行期异常那样回滚。同样，你还可以声明事务遇到特定的异常不回滚，即使这些异常是运行期异常。

事务的开始与结束可以由用户显示控制。如果用户没有显示地定义事务则由DBMS按缺省规定自动划分事务（隐式事务）
BEGIN TRANSACTION
SQL 语句1
COMMIT/ROLL
COMMIT ROLLBACK
事务正常结束 事务异常终止
提交事务所有操作（读+更新） 事务运行的过程中发生了故障
事务中所有对数据库的更新写回到磁盘上的物理数据库中 系统将事务中对数据库的所有已完成的操作全部撤销

2.2，ACID特性

特性	解释
原子性	事务是数据库的基本逻辑工作单位
	事务执行时的不可分割性，即事务所包含的活动要么都做，要么都不做
	若事务因故障而中止，则要设法消除该事务所产生的影响，使数据库恢复到该事务执行前的状态。
一致性	事务对数据库的作用应使数据库从一个一致状态到另一个一致状态
一致性	定义一个事务，该事务包括两个操作，A=A-1，B=B+1 这两个操作要么全做，要么全不做；全做或者全不做，数据库都处于一致性状态。
隔离性	多事务并发执行，应象各事务独立执行一样，不能相互干扰。
隔离性	一个事务内部的操作及使用的数据对其他并发事务是隔离的。
持久性	一旦事务提交，不论执行何种操作或发生何种故障，都不应对该事务的执行结果有任何影响。

保证事务ACID特性是事务处理的任务，破坏事务ACID特性的因素和责任。

事务在运行过程中被强行终止；DBMS必须保证多个事务的交叉运行，不影响这些事务的原子性。
多个事务并行运行时，不同事务的操作交叉执行；DBMS必须保证被迫终止的事务对数据库和其他事务没有影响。

2.3，传播行为

Spring：事务管理_燕双嘤的博客-CSDN博客

2.4，隔离级别

隔离级别：定义了一个事务可能受其他并发事务影响的程度。

Spring：事务管理_燕双嘤的博客-CSDN博客

隔离级别\问题 脏读 不可重复读 幻读
读未提交数据 × × ×
读已提交 √ × ×
可重复读（默认） √ √ ×
串行化 √ √ √

并发事务引起的问题：在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务。并发虽然是必须的，但可能会导致以下的问题。

脏读：脏读发生在一个事务读取了另一个事务改写但尚未提交的数据时。如果改写在稍后被回滚了，那么第一个事务获取的数据就是无效的。（读取未提交数据）

时刻 事务一 事务二
t1 开始事务
t2 开始事务
t3 查询余额为2000元
t4 取款1000元，余额更改为1000元
t5 查询余额为1000元（产生脏读）
t6
取款操作发生未知错误，事务回滚，余额变更为2000元

t7 转入2000元，余额被更改为3000元（脏读的1000+2000）
t8 提交事务
备注 按照正确逻辑，此时账户余额应该为4000元

不可重复读：不可重复读发生在一个事务执行相同的查询两次或两次以上，但是每次都得到不同的数据时。这通常是因为另一个并发事务在两次查询期间进行了更新。（前后多次读取，数据内容不一致）

时刻 事务一 事务二
t1 开始事务
t2 第一次查询，小明的年龄为20岁
t3 开始事务
t4 其他操作
t5 更改小明的年龄为30岁
t6 提交事务
t7 第二次查询小明的年龄为30岁
备注 按照正确逻辑，事务A前后两次读取到的数据应该一致

幻读：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录。

时刻 事务一 事务二
t1 开始事务
t2 第一次查询，数据总量为100条
t3 开始事务
t4 其他操作
t5 新增100条数据
t6 提交事务
t7 第二次查询，数据总量为200条
备注 按照正确逻辑，事务A前后两次读取到的数据总量应该一致

【问题】不可重复读和幻读到底有什么区别呢？

（1）不可重复读是读取了其他事务更改的数据，针对insert与update操作

解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。

（2）幻读是读取了其他事务新增的数据，针对insert操作

解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

【问题】Mysql的可重复读是怎么实现的？

事务中的快照读：在事务中进行查询操作时，使用的是快照读取方式，即事务开启时读取到的数据的版本号（transaction id）会被记录下来，并在整个事务过程中保持不变。这样，在同一个事务中多次读取同一条数据时，都能读到相同的数据快照，从而实现了可重复读。

MVCC：MVCC（多版本并发控制）是 InnoDB 存储引擎所采用的一种技术，可以在多个事务并发执行的情况下，保证每个事务读取到的数据都是一致的。MVCC 主要是利用了每个数据行都有一个版本号的特点，在事务开始时，就根据自身的事务 ID 来确定需要读取到哪个版本的数据，以此来避免由于并发修改导致的数据不一致问题。

MVCC的优点是可以提高数据库的并发性和可靠性，避免了锁竞争和死锁等问题。但它也会带来一些额外的开销，如版本控制和回收垃圾版本等。因此，在实际应用中需要根据具体情况进行权衡和选择。

3，锁

3.1，死锁

一个经典的MySQL死锁场景是两个事务同时访问同一个表中的不同行，每个事务都试图在另一个事务持有的行上加锁，导致了死锁。
==========事务A==========
BEGIN;
UPDATE account SET balance = balance + 100 WHERE id = 1;
UPDATE account SET balance = balance - 100 WHERE id = 2;
COMMIT;
==========事务B==========
BEGIN;
UPDATE account SET balance = balance + 100 WHERE id = 2;
UPDATE account SET balance = balance - 100 WHERE id = 1;
COMMIT;
如果事务A和事务B同时执行，它们会发生死锁。比如，事务A先获取了id=1的行锁，然后尝试获取id=2的行锁。同时，事务B已经获取了id=2的行锁，然后尝试获取id=1的行锁。由于两个事务互相持有对方需要的行锁，所以它们都无法继续执行下去，从而造成了死锁。

3.2，数据库锁的类型

锁（Lock）机制用于管理对共享资源的并发访问，用于多用户环境下，可以保证数据库的完整性和一致性。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

InnoDB存储引擎实现了如下两种标准的行级锁，InnoDB行级锁是通过给索引上的索引项加锁来实现的。只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

共享锁（S Lock），允许事务读一行数据。
排他锁（X Lock），允许事务删除或更新一行数据。

如果一个事务 $T_1$ 已经获得了行 $r$ 的共享锁，那么另外的事务 $T_2$ 可以立即获得行 $r$ 的共享锁，因为读取并没有改变行 $r$ 的数据，称这种情况为锁兼容。

但若有其他的事务 $T_3$ 想获得行 $r$ 的排他锁，则其必须等待事务 $T1,T2$ 释放行 $r$ 上的共享锁，这种情况称为锁不兼容。

下图显示了共享锁和排他锁的兼容性，可以发现 $X$ 锁与任何的锁都不兼容，而 $S$ 锁仅和 $S$ 锁兼容。需要特别注意的是， $S$ 和 $X$ 锁都是行锁，兼容是指对同一记录（row）锁的兼容性情况。

X S
X 不兼容 不兼容
S 不兼容 兼容

表级锁：表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

3.3，乐观锁&悲观锁（数据库并发策略）

悲观锁（Pessimistic Lock）：顾名思义，就是很悲观，每次去读取数据的时候都认为你会修改，所以每次在读取数据的时候都会上锁，这样别人想读取这个数据就会 block 直到它读取到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

java里面的同步机制synchronized关键字就是一个悲观锁，当一个变量或者是方法使用了synchronized修饰时，其他的线程想要拿到这个变量或者是方法的时候将就需要等到别的线程释放。
数据库里面也用到了这种悲观锁的机制。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。这样其他的线程就不能同步操作，必须要等到他释放才可以。
-- 锁定整个表
LOCK TABLE your_table_name WRITE;  -- 写锁
LOCK TABLE your_table_name READ;   -- 读锁
-- 解锁表
UNLOCK TABLES;

-- 行级锁示例
-- 在需要锁定特定行的地方使用FOR UPDATE或FOR SHARE
-- 写锁（排他锁）
SELECT * FROM your_table_name WHERE your_condition FOR UPDATE;
-- 读锁（共享锁）
SELECT * FROM your_table_name WHERE your_condition FOR SHARE;

-- 读锁（共享锁）
SELECT * FROM your_table_name WHERE your_condition LOCK IN SHARE MODE;
-- 写锁（排他锁）
SELECT * FROM your_table_name WHERE your_condition FOR UPDATE;
乐观锁（Optimistic Lock）：顾名思义，就是很乐观，每次去读取数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量。

CAS 实现：Java 中java.util.concurrent.atomic包下面的原子变量使用了乐观锁的一种 CAS 实现方式。
版本号控制：一般是在数据表中加上一个数据版本号 version 字段，表示数据被修改的次数。当数据被修改时，version 值会 +1。当线程 A 要更新数据时，在读取数据的同时也会读取 version 值，在提交更新时，若刚才读取到的 version 值与当前数据库中的 version 值相等时才更新，否则重试更新操作，直到更新成功。

乐观锁和悲观锁都是用于解决并发访问时数据一致性的问题。它们各自有自己的优缺点，具体如下：

【乐观锁-优点】

读取不需要加锁，因此对并发访问性能的影响较小；
可以减少锁争用，提高并发访问效率；
相对简单易实现。

【乐观锁-缺点】

如果并发写入冲突比较频繁，乐观锁需要不断重试，可能导致性能下降；
如果数据冲突频繁且并发度高，容易导致大量的重试操作，增加系统负担；
需要保证版本号的正确性，增加了程序的复杂度。

【悲观锁-优点】

可以有效避免并发写入冲突，保证数据的一致性；
不需要进行多次重试，因此对于并发度高的场景，悲观锁的性能更稳定。

【悲观锁-缺点】

需要频繁加锁，可能导致锁争用严重，降低并发效率；
锁定时间长，可能会导致系统响应时间变慢；
实现复杂，需要考虑锁的粒度和加锁顺序等问题。

【乐观锁和悲观锁的实现】

（1）如果不采用锁，那么操作方法如下：
//1.查询出商品信息
select status from t_goods where id=1;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
上面这种场景在高并发访问的情况下很可能会出现问题。只有当goods status为1时才能对该商品下单，上面第一步操作中，查询出来的商品status为1。但是当我们执行第三步Update操作的时候，有可能出现其他人先一步对商品下单把goods status修改为2了，但是我们并不知道数据已经被修改了，这样就可能造成同一个商品被下单2次，使得数据不一致。所以说这种方式是不安全的。

（2）使用悲观锁来实现：商品信息从查询出来到修改，中间有一个处理订单的过程，使用悲观锁的原理就是，当我们在查询出goods信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为goods被锁定了，就不会出现有第三者来对其进行修改了。

注：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。
set autocommit=0;
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;
与普通查询不一样的是，我们使用了select…for update的方式，这样就通过数据库实现了悲观锁。此时在t_goods表中，id为1的那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

（3）使用乐观锁来实现：

使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

4，分库分表

4.1，SQL优化

SQL 慢查询的原因：

是否走了索引
是否存在大量的数据操作（如全表扫描）
是否存在锁等并发问题
是否存在子查询，循环嵌套
是否使用了较为复杂的函数或表达式

可通过explain命令查看执行计划来确定SQL执行情况，根据结果分析是否需要进一步优化。

SQL 是一种用于管理关系型数据库的语言，优化 SQL 查询可以提高数据库的性能并减少查询所需的时间和资源。以下是 SQL 常用的优化技术及其原因：

使用索引优化查询：索引是一种数据结构，用于加速查询操作，可以大大提高查询效率。当表中有大量数据时，使用索引可以显著降低查询的成本。

避免使用“SELECT *”语句：使用“SELECT *”语句会导致查询返回所有列的数据，包括不需要的数据，这会浪费时间和资源。并使用尽可能简单的WHERE子句筛选数据集合，从而减少不必要的数据操作。

使用连接查询代替子查询：连接查询可以更好地利用索引，提高查询效率。而子查询会执行多次，对性能有一定影响。

尽可能使用 EXISTS 替代 IN：IN 子句的性能较低，而 EXISTS 可以更好地利用索引和缓存。

避免使用<>、not in等操作符。
避免在SELECT语句中使用子查询、嵌套查询、聚合函数等复杂操作。

使用 UNION ALL 代替 UNION：UNION ALL 不会对查询结果进行排序和去重，因此可以减少查询所需的时间和资源。

使用 LIMIT 限制结果集：LIMIT 可以限制返回的行数，从而减少查询所需的时间和资源。

使用分区表：分区表可以根据数据的特征将表分成多个分区，每个分区都可以使用不同的索引和存储引擎，可以显著提高查询效率。

定期清理过期数据：过期的数据会占用存储空间和查询资源，应该定期清理不需要的数据。

4.2，MySQL优化

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

4.3，锁优化

读写分离可以将读操作和写操作分别分配到不同的数据库服务器上，避免在同一台数据库服务器上读写操作相互干扰，提高数据库的并发处理能力。
分段加锁可以将数据库的数据划分为多个段，在对某个段进行更新或查询时，只需要对该段加锁，减少锁的竞争，提高并发处理能力。
减少锁持有的时间可以通过缩小事务的范围，减少加锁时间，从而降低锁冲突的概率，提高数据库的并发处理能力。
多个线程尽量以相同的顺序去获取资源可以降低锁的竞争，避免死锁的产生，提高数据库的并发处理能力。

4.4，分表，分库，分区

数据库分区和分表是两种不同的技术，用于优化数据库性能和管理大型数据集。它们的主要区别在于它们所应用的级别和目的：

分表：是将一个大表按照某种规则（如按照某个字段的值范围、哈希值等）分成多个小表，存储在不同的数据库服务器上，从而分散了单个表的数据量和访问负载。分表通常是根据某些列中的数据值进行的，例如将一张大型的用户表按照地理位置分为多个表，每个表只包含一个特定地理位置的用户数据。分表的主要目的是提高写入性能，增加数据库的横向扩展能力，但也增加了数据一致性、分布式事务等问题的处理难度。
分库：将一个大数据库按照某种规则（如按照业务、地理位置等）分成多个小数据库，每个小数据库包含多个表，存储在不同的数据库服务器上。分库可以降低单个数据库的数据量和访问负载，提高数据库的横向扩展能力，但也增加了数据一致性、跨库查询等问题的处理难度。
分区：是将一个大型数据库分割成更小的、更易于管理和操作的块。分区可以按照不同的标准进行，如时间、地理位置或其他业务相关的标准。分区可以提高查询性能、减少索引占用空间等，但也增加了管理、备份、恢复等问题的处理难度。
分区主要用于提高查询性能，而分库，分表主要用于提高写入性能。

MySQL的水平切分、垂直切分和读写分离旨在提高数据库的性能和可扩展性：

水平切分：将数据库的数据水平分割成多个部分，每个部分存储在不同的服务器上。这样做可以提高数据库的性能和可扩展性，因为每个服务器只需要处理其中一部分数据，可以避免单一服务器的性能瓶颈。但是，在进行水平切分之前，需要仔细考虑如何划分数据，以避免数据不一致和查询性能下降等问题。

垂直切分：将数据库的不同列分割成不同的表或服务器，以便在不同的表或服务器上存储和管理。垂直切分可以使查询操作更加高效，因为只需要查询所需的列，而不需要扫描整个表。但是，垂直切分也会增加数据模型的复杂性，并且可能会导致一些关联查询的性能下降。

读写分离：将数据库的读操作和写操作分离到不同的服务器上处理。读操作通常比写操作更频繁，因此可以将读操作分配到多个服务器上，以提高查询性能和减轻单个服务器的负载。但是，由于写操作需要更新所有服务器的数据，因此需要采取额外的措施（MySQL主从复制）来确保数据的一致性。

MySQL主从复制是一种常见的数据库复制技术，可以将一个MySQL数据库的数据从主服务器复制到一个或多个从服务器，以提高数据库的可用性、可靠性和性能。在主从复制中，主服务器将其更新的数据写入二进制日志（Binary Log），从服务器通过读取主服务器的二进制日志来复制主服务器的数据。从服务器将二进制日志应用到本地数据库中，以保持与主服务器的数据同步。

主线程：运行在主服务器上，负责将主服务器上的更新操作写入二进制日志（Binary Log）。

IO 线程：运行在从服务器上，负责读取主服务器上的二进制日志，并将其写入本地的中继日志（Relay Log）。

SQL 线程：运行在从服务器上，负责从中继日志中读取数据，并执行其中的 SQL 语句，使得从服务器上的数据与主服务器上的数据保持同步。

【问题】主从同步的延迟原因及解决办法？

【答案】（1）大量写入操作：如果主服务器上有大量的写入操作，从服务器需要不断地将这些操作进行重放，可能会导致延迟。（2）主从服务器时钟不同步：如果主从服务器的时钟不同步，可能会导致主从同步延迟。可以通过配置时间同步服务或手动调整时间来解决。

【分库中间件】

ShardingSphere： ShardingSphere是一个开源的分布式数据库中间件，支持分库分表和读写分离。它提供了多种分片算法，可以根据业务需求配置不同的分片策略。ShardingSphere支持多种数据库，包括MySQL、Oracle、SQL Server等。它还提供了多语言客户端，方便与不同编程语言的应用集成。

Vitess： Vitess是一个开源的数据库中间件，最初由YouTube开发，并后来开源。它专注于MySQL数据库的分片和扩展，提供水平扩展的能力。Vitess可以用于将大型MySQL数据库划分成多个分片，并提供了查询路由、负载均衡、事务管理等功能。

Cobar： Cobar是一个由淘宝开发的开源分库分表中间件，用于MySQL数据库。它支持自动的分片和数据路由，还提供了读写分离和负载均衡的功能。Cobar虽然不再主要维护，但仍然可以在某些项目中使用。

MyCAT： MyCAT（MySQL Clusters and All together）是一个开源的MySQL中间件，用于实现分库分表和数据路由。它提供了一种类似于MySQL的SQL接口，可以将查询请求路由到正确的分片上。MyCAT也支持读写分离和负载均衡。

AtlasDB： AtlasDB是一个由Netflix开发的分布式数据库中间件，用于处理大规模的分库分表需求。它支持多种数据库后端，包括MySQL。AtlasDB提供了事务管理、分片策略、数据一致性等功能。

燕双嘤

关注

23
点赞
踩
27

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL：架构缓存，事务，锁机制，SQL优化，分库分表

2w字说明白MySQL：MySQL架构，SQL执行过程，三范式，缓存，ACID，传播行为，隔离级别，死锁，乐观锁锁&悲观锁，SQL优化，分库分表分区。
复制链接

扫一扫

专栏目录

COMMIT	ROLLBACK
事务正常结束	事务异常终止
提交事务所有操作（读+更新）	事务运行的过程中发生了故障
事务中所有对数据库的更新写回到磁盘上的物理数据库中	系统将事务中对数据库的所有已完成的操作全部撤销

隔离级别\问题	脏读	不可重复读	幻读
读未提交数据	×	×	×
读已提交	√	×	×
可重复读（默认）	√	√	×
串行化	√	√	√

时刻	事务一	事务二
t1		开始事务
t2	开始事务
t3		查询余额为2000元
t4		取款1000元，余额更改为1000元
t5	查询余额为1000元（产生脏读）
t6		取款操作发生未知错误，事务回滚，余额变更为2000元
t7	转入2000元，余额被更改为3000元（脏读的1000+2000）
t8	提交事务
备注	按照正确逻辑，此时账户余额应该为4000元