MySQL（三）——锁、数据库优化和SQL语句执行过程

目录

【五】 锁

【六】数据库优化

6.1 分库分表

6.2 主从复制和读写分离

【七】一条SQL语句在MySQL中是如何执行的

---

【五】 锁

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁（table-level locking）。
• InnoDB支持行级锁（row-level locking）和表级锁，默认为行级锁。

表级锁和行级锁对比：

• 表级锁：MySQL中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和InnoDB引擎都支持表级锁。
• 行级锁：MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB存储引擎的锁的算法有三种：

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

【六】数据库优化

1. 数据库设计——根据数据库三大设计范式进行表结构的设计
2. 数据库索引优化
3. 存储过程（模块化编程，可以提高速度）
4. 分库分表（水平分割，垂直分割）
5. 主从复制、读写分离
6. SQL调优

6.1 分库分表

数据库的数据量达到一定程度之后，为避免带来系统性能上的瓶颈。需要进行数据的处理，采用的手段是分区、分库、分表。

水平分割：按记录进行分割，不同的记录可以分开保存，每个子表的列数相同。

垂直分割：按列进行分割，即把一条记录分开多个地方保存，每个子表的行数相同。

分区：MySQL的分区是一种简单的水平分区，将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件（分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了）。

分区的好处：

• 可以让单表存储更多的数据；
• 分区表的数据更容易维护，可以通过清除整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作；
• 部分查询能够从查询条件确定只落在少数分区上，速度会很快；
• 分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备；
• 可以备份和恢复单个分区。

分区的限制和缺点：

• 一个表最多只能有1024个分区；
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来；
• 分区表无法使用外键约束；
• NULL值会使分区过滤无效；
• 所有分区必须使用相同的存储引擎。

分表：将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些表可以分布在同一块磁盘上，也可以在不同的机器上。包括垂直分表和水平分表。

垂直分表的拆分原则：

• 把不常用的字段单独放在一张表；
• 把text，blob（binary large object，二进制大对象）等大字段拆分出来放在附表中；
• 经常组合查询的列放在一张表中。

水平分表的拆分原则：

通常使用hash、取模等方式来进行表的拆分（如，对id取模来进行分表）。

分库：包括垂直分库和水平分库。垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用；水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

6.2 主从复制和读写分离

目的：为了提高数据库的并发性能。主从复制是读写分离的基础。

主从复制（Master-Slave）原理/流程：

MySQL之间数据复制的基础是二进制日志文件。MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志（Relay log）中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

1） 当Master节点进行insert、update、delete操作时，会按顺序写入到binlog（二进制日志文件，binary log file）中；

2） salve从库连接master主库，Master有多少个slave就会创建多少个binlog dump线程；

3） 当Master节点的binlog发生变化时，binlog dump线程会通知所有的salve节点，并将相应的binlog内容推送给slave节点；

4） I/O线程接收到binlog内容后，将内容写入到本地的relay-log；

5） SQL线程读取I/O线程写入的relay-log，并且根据relay-log的内容对从数据库做对应的操作。

读写分离：master负责写入数据，slave负责读取数据。

【七】一条SQL语句在MySQL中是如何执行的

1. 客户端通过TCP连接发送连接请求到MySQL连接器，连接器会对该请求进行权限验证及连接资源分配；

2. 查缓存（当判断缓存是否命中时，MySQL不会解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中）；

3. 语法分析（SQL语法是否写错了）。 如何把语句给到预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义；

4. 优化，是否使用索引，生成执行计划；

5. 交给执行器，将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。