MySQL简要架构总结

1.MySQL简要架构

MySQL整体分为三层：客户端、MySQL服务、文件系统。

• 客户端：是各种编程语言的connector。

• MySQL服务：内部包含各种组件，实现各种功能。例如：连接器、缓冲池、分析器、优化器、执行器、存储引擎等。

• 文件系统：数据存储与日志。

 

2.客户端

2.1 数据库连接策略

1.主从数据库链接

• 主从链接选择方式：开启事务则自动选择主库链接，其他情况自动选择从库链接。

• 写数据自动选择主库，读数据默认选中从库，也可以手动选择主库。多个从库使用随机策略选择。

 

2.2 数据库写策略

1.预防SQL注入

• mysql_real_escape_string()函数转义 SQL 语句中使用的字符串中的特殊字符。

• 使用PDO预处理prepare方式。

2.预防死锁

• 以相同的顺序访问表和行。避免逐步加锁导致锁等待，从而造成死锁。

• 大事务拆小。并且事务中禁止调用外部接口，因为外部接口响应时间过长时，会导致事务超时，也提升了死锁的几率。

• 注意where条件的强制类型转换和函数计算，会导致扫描全表，从而锁住全表的每一条数据。

• 尽量使用主键作为条件更新数据，普通索引会有gap锁（间隙锁），锁住当前数据的相邻数据，死锁几率增大。

3.并发问题

• 预防离线更新丢失

  • 事务中禁止调用外部接口。因为事务中调用外部接口之后，出现数据回滚，会导致外部接口接收到数据，而本系统没有数据。

• 预防不一致读导致的更新丢失

  • 更新数据时对比版本号（通常是数据的update_time），因为如果不对比版本号，那么可能会出现数据在读取之后发生了变化，而更新的时候用了变化之前的数据。从而导致数据更新丢失。

• 预防本地数据更新丢失

  • 事务中SQL异常必须处理，如果不处理，那么会重新连接数据库，从而导致最后提交（commit）的数据只有异常之后的数据，前半部分数据丢失。

  • 禁止使用嵌套事务，MySQL已经开启事务后，再开启事务（start transaction），会隐式的提交（commit）上一个事务。如果下一个事务回滚，会导致只提交了前半部分数据，后半部分数据丢失。

4.写数据效率问题

• 单条写优化为批量写（可以提升10倍的速度，从100条/s提升到1000条/s），受硬盘转速和连接资源消耗影响。

  • 批量添加数据示例：INSERT INTO {table} ({$field}) VALUES (),();

  • 批量更新数据示例：UPDATE {table} SET {field} = CASE {primaryKey} WHEN {key} THEN {val} END WHERE {where}。

 

2.3 数据库读策略

1.where条件必须命中索引，预防不能命中索引的常见场景：

• 字段允许为null，负向查询时不能命中索引（此字段负向查询还会把null排除，完整的悲剧）。

• where条件的强制类型转换，不能命中索引，会扫描全表。

• 前导模糊查询不能使用索引。例如：like '%xx';

• 在where条件里进行函数计算不能命中索引。例如：YEAR(date)

• 组合索引的where条件，需满足最左前缀的原则。

2.查询结果处理

• 如果明确只有一条数据，那么使用limit 1。因为limit 1能告诉数据库主动停止游标移动。

• 计算放到业务层，可以节省数据的CPU，例如：CURDATE()。

• 一次性不宜返回太多数据。返回太多数据会增加MySQL查询和业务层内存压力。

 

3.MySQL服务

3.1 连接器

1.连接器负责跟客户端建立连接、获取权限、维持和管理连接。

• 数据库链接验证：权限信息会放入内存，数据库连接由user表里的Host/user/password三个列进行验证。

• SQL请求验证：验证有没有权限对数据库进行操作如：（create , drop , select , delete等等。）

2.长连接和短连接的选择

• 长连接能节省连接时通讯造成的资源浪费，但是链接过多后，会导致mysql占用内存较大，从而可能被系统强行杀掉（OOM）。

• 短链接没有占用内存较大的风险，但是会频繁建立链接，从而消耗资源。

• 因此长连接和短连接需根据具体情况选择

  • 普通的页面访问建议使用短连接

  • 脚本处理大量数据可适度选择用长连接（注意用完之后mysql_reset_connection释放链接资源。）

 

3.2 分析器

1.分析器分析SQL语法

• 词法分析：分析SQL语句中的字符串分别代表什么意思。

• 语法分析：分析SQL语句是否满足MySQL语法。

 

3.3 优化器

1.优化器生成执行计划，选择索引

• 选择索引：优化器是在表里面有多个索引的时候，决定使用哪个索引。

• 在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

 

3.4 执行器

1.执行语句

• 开始执行的时候，要先判断用户对这个表T有没有查询权限，如果没有，就会返回权限的错误。

• 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

 

3.5 存储引擎

1.了解存储引擎

• 存储引擎是数据库存储数据到文件或者内存中的一种技术，不同的存储引擎具有不同的存储机制、索引技巧、锁水平。

• MySQL常用的存储引擎有InnoDB、MyISAM、Memory，其中InnoDB使用范围最广。

  • InnoDB：支持ACID事务，支持行级锁定，支持外键，是聚集索引，数据文件和索引是绑在一起的。

  • MyISAM: 不支持ACID事务，不支持外键，是非聚集索引，数据文件和索引是分离的，索引保存的是数据文件的指针。

  • Memory：文件数据保存在内存中。

• 根据不同的数据存储场景，选择不同的存储引擎。例如：需要事务的写数据场景，使用InnoDB。不需要事务，读频繁的读数据场景可以使用MyISAM。

• 数据表中设置ENGINE=InnoDB，即可使用InnoDB存储引擎。

2.InnoDB的性能优化技巧

• 合理设置写缓冲池大小，以此解决磁盘读写性能瓶颈问题。通过innodb_buffer_pool_size可以配置缓冲池大小，innodb_change_buffer_max_size配置写缓冲池的占比。

• 大量数据操作修改为批量操作，以此解决硬盘转速瓶颈，并减少客户端和服务端的通信开销。例如 : INSERT INTO yourtable VALUES (1, 2), (5, 5);

• 配置定期刷盘:

  • innodb_flush_log_at_trx_commit=0，每隔1s将日志缓冲区的数据刷到系统缓冲区和磁盘。性能最好，但数据库崩溃有1s的数据丢失可能。

  • innodb_flush_log_at_trx_commit=1，每次事务提交都刷系统缓冲区和落盘，Innodb默认配置。性能较低，但能保证事务ACID特性。

  • innodb_flush_log_at_trx_commit=2，每次事务提交都刷系统缓冲区，但是每隔1s批量刷盘。批量优化的最佳选择，只要系统不崩溃，数据就不会丢失。（系统崩溃的概率，远小于数据库服务崩溃的概率）

• 配置redo log缓冲，优化其性能。可通过innodb_log_buffer_size配置。

 

3.6 缓冲池

1.了解缓冲池

• 缓冲池是一种把数据和索引缓冲到内存中的一种机制。数据放到内存后，可以避免每次操作进行磁盘IO，从而提升MySQL的性能。

• MySQL缓冲池是按照局部性原理，进行预读，一次读取一页数据（一般是4k）。使用LRU算法管理，LRU算法是用链表进行管理的，遵循最晚淘汰原则。

• MySQL缓冲池相关的配置有：

  • innodb_buffer_pool_size可以配置缓冲池大小。

  • innodb_old_blocks_pct配置老生代占整个LRU链长度。

  • innodb_old_blocks_time配置老生代停留时间窗口。

  • innodb_change_buffer_max_size 配置写缓冲占整个缓冲池的比例，默认25%。

  • innodb_change_buffering 配置哪些写操作启用写缓冲池，可以配置all/none/insert/delete。

2.缓冲池的相关疑问？

• 写缓冲写入动作

  • 如果是缓存池中的页，那么会有一次内存操作和一次磁盘顺序写操作。如果修改不在缓冲池的页，那么会有一次磁盘随机读操作、一次内存操作、一次磁盘顺序写操作。

• 写缓冲如何保证数据一致性？

  • 数据库异常崩溃，能够从redo log中恢复数据。

  • 写缓冲不只是一个内存结构，它也会被定期刷盘到表空间。

  • 数据读取时，会将数据合并到缓冲池。

 

3.7 redo log日志

1.了解redo log日志

• redo log被称为重做日志，其用于保证事务的ACID特性，同时也能提升数据库的写性能。

• 数据库遭遇故障重启时，可以使用redo log进行数据恢复，从而保证事务的ACID特性。

• redo log有3层架构：日志缓冲区log buffer、系统缓冲区OS cahce、磁盘的日志文件log file。可以通过innodb_flush_log_at_trx_commit配置来调整顺序写和批量写：

  • innodb_flush_log_at_trx_commit=0，每隔1s将日志缓冲区的数据刷到系统缓冲区和磁盘。性能最好，但数据库崩溃有1s的数据丢失可能。

  • innodb_flush_log_at_trx_commit=1，每次事务提交都刷系统缓冲区和落盘，Innodb默认配置。性能较低，但能保证事务ACID特性。

  • innodb_flush_log_at_trx_commit=2，每次事务提交都刷系统缓冲区，但是每隔1s批量刷盘。批量优化的最佳选择，只要系统不崩溃，数据就不会丢失。（系统崩溃的概率，远小于数据库服务崩溃的概率）

 

3.8 binlog 日志

1.了解binlog日志

• binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。包括索引文件和内容文件。

• binlog可以用于：恢复数据、复制数据（主从复制）、审计数据。

  • 恢复数据：应用于增量备份的数据恢复过程。当遇到数据误删或者服务异常造成数据丢失时，可以binlog重放到异常之前以此来恢复数据。

  • 复制数据：主库有一个log dump线程，将binlog传给从库，从库有两个线程，一个I/O线程，一个SQL线程，I/O线程读取主库传过来的binlog内容并写入到relay log,SQL线程从relay log里面读取内容，写入从库的数据库。

  • 审计数据：

 

4.数据库设计

4.1 数据库设计的基概

1.单库架构：一个库进行数据存储。

2.分组架构：

• 一主多从，主从同步，读写分离数据库架构。

• 一主多从结构，可以线性提升数据库读性能。通过读写分离消除读写锁冲突，从而提升数据库写性能。

• 主从之间通过binlog进度数据同步。多个数据库实例数据结构完全相同，实质是将数据进行复制。

• 这种架构，可以支持生产环境千万级别的数据。

3.分片架构：

• 水平切分数据，多个数据库实例构成一个数据库集群。(画外音：数据表还有垂直切分，把大表拆成多个小表)

• 分片可以很容易将数据迁移到不同的数据库实例，拥有更好的扩展性。

• 水平切分可以依据主键有两种策略：

  • 范围法：以业务主键为依据，划分为多个范围的数据。

  • 哈希法：通过对业务主键进行哈希取摸，查询不同的数据。

• 分片架构可以线性提升数据库的写性能，降低单库数据容量。

4.分组+分片结构：

• 一主多从结构，加上水平切分的对个数据库实例，构成一个数据库集群。

• 可以线性提升数据库写性能、读性能，同时保证读高可用。（提供灾备数据库）

• 这种架构，可以支持生产环境亿级别的数据。

 

4.2 数据库安全方案

1.全量备份+增量备份

• 定期将数据库文件进行全量备份，例如：每月将库文件通过tar -czvf进行备份。

• 定期将binlog进行增量备份。

• 数据恢复过程

  • 首先，将最近一次全量备份找到，拷贝回来解压、应用。

  • 然后，将每一天的增量备份找到，拷贝回来依次重放，直至删库之前的binlog。

2.1小时延时从库（灾备数据库）

• 每隔1小时同步一次主库，同步完成后断开。

• 数据恢复过程

  • 首先，从没有删除数据的从库中恢复数据。

  • 然后，将1小时延时的binlog找到重放。

3.双份1小时延时从库

• 为预防删库时刚好是1小时延时从库同步的一小段时间，可以使两个从库岔开半小时。

• 数据恢复过程

  • 首先，从没有删除数据的从库中恢复数据。

  • 然后，将1小时延时的binlog找到重放。

 

5.MySQL常用知识

5.1 MySQL死锁问题

1.什么是死锁？

• 死锁是指两个或两个以上的线程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。

2.MySQL是怎么出现死锁的？

• 首先了解MySQL的加锁情况

  • InnoDB使用的是聚集索引，普通索引的叶子节点存储主键值，主键索引的叶节点存储行数据。对主键进行操作会锁住整行数据，对普通索引操作会锁住索引和行数据，如果没有索引会锁住整个表。

  • 锁和隔离级别也有关系，可重复读（RR）级别下，给索引上的记录添加上X锁，此外，还在非唯一索引与相邻两个索引的区间加上锁。

• 对两条数据，在两个事务执行不同顺序的写入操作。例如：事务1（A->B），事务2（B->A），会出现事务1等待B的资源，事务2等待A的资源。

• 不同索引锁冲突，使用范围条件做写入条件，则可能会出现在聚簇索引上加锁顺序不一致，从而造成死锁。

• gap锁（间隙锁）冲突，可重复读（RR）级别下，会给相邻索引的区间加上锁。

3.如何解决死锁问题？

• 避免死锁的方法：

  • 以相同的顺序访问表和行。

  • 大事务拆小。

  • 降低隔离级别。

  • 为表添加索引，避免不走索引而导致数据每行都加锁的情况。（这里有个常见问题，where条件类型转换，会导致扫描全表，从而锁住全部记录）

• 分析、定位死锁：

  • SHOW ENGINE INNODB STATUS; 分析最近的锁日志

  • Explain执行计划，可分析对应SQL语句的扫描情况。

  • innodb_lock_wait_timeout 设置事务等待的超时时间。

4.理解死锁的好处。

• 减少事务等待，避免死锁而造成服务资源浪费，提升服务性能。

• 降低数据回滚频率，提升系统可用性。

 

5.2 Explain分析SQL的执行计划

1.type扫描方式

• system: 从系统表查询数据，这些数据已经加载到了内存，不需要磁盘IO。

• const: 命中索引并且被连接的部分是一个常量。

• eq_ref: join查询，命中主键或者非空唯一索引，等值链接。

• ref: 命中非普通索引。

• range: 索引范围扫描。

• index: 索引范围内的全表扫描。

• all: 全表扫描。

2.Extra

• Using where SQL使用了where条件过滤数据。

• Using index SQL所需数据都在一棵索引树上，无需访问实际的行记录。

• Using index condition SQL命中了索引，但不是所有列数据都在索引树上，需要访问实际的行记录。

• Using filesort SQL所需的结果集，需要对所有记录进行文件排序。

• Using temporary SQL需要建立临时表来暂存中间结果集。

• Using join buffer SQL需要进行嵌套循环计算。

 

5.3 聚集索引和普通索引

1.聚集索引

• 聚集索引，叶子节点存的是整行数据，直接通过这个聚集索引的键值找到某行。数据行和相邻的键值紧凑地存储在一起，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。

2.普通索引

• 非聚集索引，叶子节点存的是字段的值，通过这个非聚集索引的键值找到对应的聚集索引字段的值，再通过聚集索引键值找到表的某行。

3.回表查询

• 先通过普通索引定位到主键值，然后通过聚集索引定位到行记录。此称为回表查询。

4.索引覆盖

• 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表。就是Using index 索引覆盖。

5.联合索引

• 多个字段上建立索引，遵循最左侧原则。

 

5.4 热备和冷备

1.热备

• 热备就是逻辑备份。是通过访问MySQL服务，来获得数据库结构和数据被内容进行备份的。

• 热备的备份力度更细，备份粒度可以全库、单库、全表、单表。可移植性也很好，可跨平台使用。不过速度较慢。

• 热备通过将数据库信息转化为逻辑格式（SQL）进行备份。

2.冷备

• 冷备就是物理备份。是通过数据文件和日志文件拷贝来进行备份的。

• 冷备的优点是速度比较快，文件大小也比较小。缺点是可移植性比较差，为保证数据一致性，往往要求数据库处理离线状态。

 

5.5 索引的底层实现

1.索引是一种加速查询的数据结构，常见的索引有两类：哈希索引、树索引。

2.为什么选择树索引而不是哈希索引？

• 哈希索引单条数据查询的复杂度是O(1)，但是分组、排序、比较的复杂度有可能退化为O(n)。

• 树索引的时间复杂度都是O(lg(n))。

3.为什么要使用B+树

• 二叉树，当数据量大时树的高度会比较高，一次查询可能会多次磁盘IO。从而导致查询会比较慢。

• B+树即M叉树，在B树上做了改进。数据只存储在同一层的叶子节点上，并增加了链表，获取所有节点，不再需要中序遍历。B+树每个节点可以设置为一页（4k），可以利用计算机系统的局部性原理，三层树结构可以存储4G的索引数据。

• B+树的优点：

  • 很适合磁盘存储，能够充分利用局部性原理，磁盘预读（计算机系统磁盘读取是按页读取的，一页4kb）。

  • 很低的树高度，能够存储大量数据。

  • 索引本身占用内存很小。

  • 能够很好支持单点查询、范围查询、有序性查询。