Java 开发者必须知道的 MySQL 常识

MySQL 逻辑架构

Connection/thread handling 部分：这部分主要是用来处理 client 的连接及相应的授权登录等。Each client connection gets its own thread within the MySQL process. 如果只有一个单核 CPU 的情况下，会交替执行相应的连接线程，类似于操作系统调度进程。MySQL caches threads, so they don’t need to be created and destroyed for each new connection.

Parser 部分：这部分主要用于解析 SQL 语句，然后用 Optimizer 各种优化，最后去调用 storage engine API 去查询 disk 上的数据。

storage engine 部分：storage engine 类似于操作系统中的文件系统，不同的 engine 有各自的优势和缺点。大家熟透的 engine 有 InnoDB 和 MyISAM.

 

 

MySQL 中的锁机制

MySQL 进程为每个客户端连接都分配了一个线程，因此同样一份数据可能同时被多个线程修改，这就会导致出现并发问题！所以，我们需要引入 Concurrency Control 来解决出现的问题。MySQL 实现了2种锁类型：排他锁（exclusive locks or write locks）和共享锁（shared locks or read locks）。这种读写锁的思想不仅仅在 MySQL 中存在，在 Java 中也存在。

读写锁的大致机制如下：

Read locks（共享锁） on a resource are shared, or mutually nonblocking: many clients can read from a resource at the same time and not interfere with each other. Write locks（排他锁）, on the other hand, are exclusive—i.e., they block both read locks and other write locks.

MySQL 为了提高并发的性能，引入了2种锁粒度：行锁（Row locks）和表锁（Table locks）。顾名思义，行锁是锁定一行记录，而表锁是锁定整个表。当然，行锁的开销要比表锁大，但是并发性能更好。

关于乐观锁与悲观锁：它们只是利用 MySQL 中本身提供的锁机制来实现的，仅仅是一种思想而已。悲观锁通常会认为在当前事务下处理数据时，其它事务有可能对这个数据进行修改，因此在某个事务访问某个数据之前，会对其加上排他锁，以防止其它事务对数据进行操作，当然了这种做法也会阻止只读数据的线程，它降低了并行性，对于大部分时间都是读操作的系统，悲观锁并不合适。另一方面，乐观锁认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测。更多详情参考下面2个链接：

深入理解乐观锁与悲观锁

面试必备之乐观锁与悲观锁

乐观锁的一种实现方式——CAS

关于 CAS：Compare-and-swap 已经写的非常清楚了！它的基本行为如下面的 C 代码所示。CAS 过程为：它首先从某个内存位置A取出来一个值，称作旧值（oldval），然后基于这个旧值做一些计算得到新值（newval）。CAS 在将新值更新到内存位置A之前，它会先从内存位置A读一下当前值（old_reg_val），即下面方法中的第2行代码，然后与先前的旧值比较，如果相等，表明当前线程在计算期间，没有其它线程修改变量的值，所以此时将新值更新到内存位置A. 在上面的过程中，CAS 认为只要 old_reg_val 与 oldval 相等，就表明没有其它进程修改变量。这种方法有个问题，如果在你计算期间，有一个进程把变量的值修改了，做了一些工作，然后又把变量改回去了。在这种情况下，CAS 认为没有变化，但实际确实发生了一些变化，只不过 CAS 没有捕捉到。这种情况叫做 ABA problem

int compare_and_swap(int* reg, int oldval, int newval)
{
  ATOMIC(); // 原子操作开始
  int old_reg_val = *reg;
  if (old_reg_val == oldval)
     *reg = newval;
  END_ATOMIC(); // 原子操作结束
  return old_reg_val;
}

 

MySQL 事务

事务的定义：. A transaction is a group of SQL queries that are treated atomically, as a single unit of work.

下图是一个关于事务的例子：

一个数据库事务必须包含 ACID 4个属性：

• Atomicity（原子性）：每个事务 is treated as a single "unit", which either succeeds completely, or fails completely. 一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
• Consistency（一致性）：ensures that a transaction can only bring the database from one valid state to another.  任何写入数据库的数据必须遵循 all defined rules, including **constraints（约束）, cascades（级联回滚）, triggers（触发器）**
• Isolation（隔离性）：数据库允许多个**并发事务**同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读**未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化**（Serializable）。
• Durability（持久性）：一旦事务 commit 以后，即使系统失败了，数据变化也要被保存到持久化内存中，比如 disk

A database server with ACID transactions also generally requires more CPU power, memory, and disk space than one without them. 这增加了数据库的开销。同时，you can decide whether your application needs transactions. If you don’t really need them, you might be able to get higher performance with a nontransactional storage engine for some kinds of queries. You might be able to use LOCK TABLES to give the level of protection you need without transactions. It’s all up to you.

关于事务的隔离级别：用来控制一个事务对数据的修改在另一个事务中的可见性。它分为可序列化(Serializable)、可重复读(Repeatable reads)、提交读(Read committed)、未提交读(Read uncommitted)。关于它们的定义如下：

Read uncommitted 级别：transactions can view the results of uncommitted transactions. 它可能会导致 dirty read.

Read committed 级别：a transaction will see only those changes made by transactions that were already committed. 它可能会导致 nonrepeatable read. 即，一个事务内可能会读到不同的数据，因为在每次数据读完之后其他事务可以修改刚才读到的数据。

Repeatable reads 级别： It guarantees that any rows a transaction reads will “look the same” in subsequent reads within the same transaction. 它可能会导致 phantom reads. 即，a phantom read can happen when you select some range of rows, another transaction inserts a new row into the range, and then you select the same range again; you will then see the new “phantom” row.

Serializable 级别：它可以解决 phantom reads.

关于上面几种隔离级别的例子，请参考：深入分析事务的隔离级别

SQL 查询子句的执行顺序

总结自：https://www.periscopedata.com/blog/sql-query-order-of-operations

1. from 子句。Choose and join tables to get base data
2. where 子句。Filters the **base data**
3. group by 子句。Aggregates the **base data**
4. having 子句。Filters the **aggregated data**
5. select 子句。Returns the **final data**
6. order by 子句。Sorts the **final data**
7. limit 子句。Limits the **returned data** to a row count

10道mysql查询语句面试题中的题目都很不错，可以拿它练练手！其中第6题的 limit 语句请参考 MySQL LIMIT

Where 子句与 Having 子句的区别

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.

1.WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。  
2.GROUP BY 子句用来分组 WHERE 子句的输出。  
3.HAVING 子句用来从分组的结果中筛选行。

SQL Join 语句

本小节总结来自于视频：[SQL Joins Tutorial for Beginners - Inner Join, Left Join, Right Join, Full Outer Join](https://www.youtube.com/watch?v=2HVMiPPuPIM)

假设有2个表，T1 和 T2，2个表中是用 ShipperID 关联起来的。ShipperID 是 T1 中的外键，是 T2 中的主键。

**Inner Join**. 舍弃2张表中 ShipperID 为 NULL 的行

**Left Join**. 即使左表中某行的 ShipperID 为 NULL，也不舍弃

**Right Join**. 即使右表中某行的 ShipperID 为 NULL，也不舍弃

**Full Join（MySQL 不支持）**. 都不舍弃

**用 Inner Join 与用 where 子句关联外键与主键的效果是一样的**。然而，用 Inner Join 更好，它增加了可读性。详情参考：[INNER JOIN ON vs WHERE clause](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause)

数据库的五大约束

MySQL CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns. 约束被分为2类：column level and table level.

MySQL CONSTRAINT is declared at the time of **creating a table**. 约束主要完成对数据的检验和限制，从而保证数据库的**完整性**。

1. **非空约束(NOT NULL)**。
2. **唯一约束 （UNIQUE）**
3. **主键约束（Primay Key）**。主键约束列不允许重复，即任意两行的主键值都不相同；每行都具有一个主键值，也不允许出现空值
4. **外键约束 (Foreign Key)** ：A FOREIGN KEY is a key used to link two tables together. 比如订单表中会有一个用户ID（外键），而用户ID是用户表中的主键。**外键可以有重复的, 可以是空值**。包含外键的表叫 child table，外键引用的表叫做 parent table.
5. **默认约束 (Default) **：在执行insert命令时，如果命令没有显式给指定的列赋值，那么把默认约束值插入到该列中
6. **检查约束 (Check)**：验证数据，比如性别中只能为女或男，而不能为其它

外键约束的参照操作：

- **CASCADE**：从父表删除或更新且自动删除或更新子表中匹配的行

- **SET NULL**：从父表删除或更新行，并设置子表中的外键列为NULL，如果使用该选项，必须保证子表列没有指定NOT NULL

- **RESTRICT**：拒绝对父表的删除或更新操作

MySQL 的分布式解决方案

**方案一（Master-Slave Replication）**：Master 数据库只搞定 insert or modify. Slave 处理 read. Master 异步通知 slave.

问题：数据一致性，对于 write traffic 有瓶颈

解决方法参考下面2个链接吧！

https://dev.mysql.com/doc/refman/5.7/en/replication-features-transaction-inconsistencies.html
https://blog.csdn.net/z50l2o08e2u4aftor9a/article/details/80971847

**方案二（multi-master replication）**：全是 master，可读可写。

问题：[create conflicts](http://datacharmer.blogspot.bg/2013/03/multi-master-data-conflicts-part-1.html)

**方案三（[sharding](https://medium.com/@jeeyoungk/how-sharding-works-b4dec46b3f6)-分片）**：

问题一：类似于 hash，你必须要让它以 **uniform way** 分布，否则会有下面的 hot spot 问题。

A single shard that receives more requests than others is called a **hot spot** and must be avoided. Once split up, **re-sharding data** becomes incredibly expensive and can cause significant downtime

问题二：Sharding is no simple feat and is best avoided [until really needed](https://www.percona.com/blog/2009/08/06/why-you-dont-want-to-shard/)

MySQL 性能优化经验

加索引

详情参考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

索引分为单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。组合索引，即一个索引包含多个列。

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。加索引时，MySQL 用 where 子句查找某行数据时，就不用扫描全表了。可以大加快查询的速度。

MySQL 索引类似于书中的索引。MySQL 会用 B-tree 为指定的列创建索引。创建索引会加快查询的速度，因为可以在 B-tree 上用更快的查找算法。然而，加索引会影响插入、删除，及更新的速度，因为做这些操作时需要修改 B-tree 的数据结构。

当只要一行数据时使用 LIMIT 1

这样一来，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

避免 SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

尽可能的使用 NOT NULL

NULL 需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。

垂直分割

“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。（以前，在银行做过项目，见过一张表有100多个字段，很恐怖）

示例：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？ 这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性能。

另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级的下降。

参考资料

High Performance MySQL: Optimization, Backups, and Replication

https://coolshell.cn/articles/1846.html/comment-page-1

MySQL 在线练习