MySQL 是一种关系型数据库管理系统，使用标准数据库查询语言 SQL。

1 SQL

1.1 数据类型

  1. 整型：TINYINT - 8，SMALLINT - 16，MEDIUMINT - 24，INT - 32，BIGINT - 64。
  2. 浮点型：FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。
  3. 字符串：CHAR - 定长，VARCHAR - 变长。
  4. 时间日期：DATETIME 和 TIMESTAMP。

1.2 数据库语句

  • 数据库定义语言 DDL：CREATE、DROP、ALTER、TRUNCATE（效率上 TRUNCATE 比 DELETE 快，但由于删除后不会记录日志，所以不可恢复数据）；
  • 数据库控制语言 DCL：GRANT、DENY、REVOKE；
  • 数据库操纵语言 DML
    • 基本操作：选择：SELECT（投影）；数据源：FROM（笛卡尔积）；条件：WHERE（选择）；排序：ORDER BY（正序：ASC、倒序：DESC）；
    • 集合操作：差集：UNION、UNION ALL；交集：INTERSECT、INTERSECT ALL；差集：EXCEPT、EXCEPT ALL；
    • 聚集操作：分组：GROUP BY；条件：HAVING、WHERE；平均值：AVG；最值：MIN、MAX；总和：SUM；计数：COUNT；
      • 先根据 WHERE 筛选，再进行行分组，最后根据 HAVING 筛选
    • 嵌套子查询：包含：IN、NOT IN；比较：SOME、ALL；全称：ALL；特称：SOME；存在：EXIST；重复：UNIQUE、NOT UNIQUE；
    • 死循环插入：INSERT INTO TABLE SELECT * FROM TABLE；
    • 连接操作：内连接：INNER JOIN；左连接：LEFT JOIN；右连接：RIGHT JOIN；全连接：FULL OUTER JOIN；外连接：OUTER JOIN。

  • 语法顺序：SELECT -> DISTINCT -> FROM -> JOIN ON -> WHERE -> GROUP BY -> HAVING -> UNION -> ORDER BY
  • 执行顺序：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> UNION -> ORDER BY

1.3 存储过程与函数

避免开发人员编写大量重复的 SQL 语句，都是在 MySQL 服务器运行，减少客户端与服务器之间的数据传输。

  • 存储过程
    • 实现更复杂的功能，包括修改表、全局数据库状态等一系列数据库操作。
    • 作为独立部分执行，SQL 语句中不能使用。
    • 与数据库实现绑定，降低程序可移植性。
    • 在存储过程中使用游标可以对一个结果集进行移动遍历。
  • 函数
    • 实现定制化功能，可作为查询语句的一部分调用，在 SQL 语句中使用。

2 索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

2.1 按约束分类

  • 单列索引
    • 普通索引：这是最基本的索引，它没有任何限制。
      • 如查询条件中包含索引，会在查找到匹配的记录后继续查找下一个记录，直到碰到第一个不满足条件的记录。
      • KEY(id)
    • 唯一索引：索引列的值必须唯一，但允许有空值。
      • 如查询条件中包含索引，会在找到第一个满足条件的记录后停止检索立即返回；
      • 如果查询条件满足与不满足的记录分布恰好在不同的页，则其唯一的特性效果会很明显；
      • UNIQUE KEY(id)
    • 主键索引：是一种特殊的唯一索引，不允许有空值。
      • 自增 ID
      • 业务字段
      • PRIMARY KEY(id)
  • 组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
    • KEY MultiIdx(id, name, age)

2.2 按结构分类

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

  1. B+ Tree 索引
    • B+ Tree 是大多数 MySQL 存储引擎的默认索引类型。适用于全键值、键值范围和键最左前缀查找，否则索引会失效。
    • 除了用于查找，还可以用于排序和分组。
    • InnoDB 的 B+ Tree 索引分为主索引和辅助索引。
      • 聚集索引/聚簇索引/主索引
        • 叶子节点 data 域记录着完整的数据记录。
        • 一个表只能有一个聚簇索引。
      • 非聚集索引/辅助索引/二级索引
        • 叶子节点的 data 域记录着主键的值。
        • 一个表可以有多个非聚簇索引。
        • 使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。
    • 比之红黑树的优势：
      1. 更少的查找次数；
      2. 利用磁盘预读特性（叶子节点形成有序链表）；
      3. 不使用 B 树
        1. B+ 树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历；
        2. B+ 树在内部节点上不含数据信息，因此在内存页中能够存放更多的 key。
  2. 哈希索引：InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引。
  3. 全文索引：只有在 MyISAM 引擎上才能使用，只能在 CHAR，VARCHAR，TEXT 类型字段上使用全文索引。
  4. 空间数据索引：MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。

2.3 索引失效的场景

  1. Like 关键字 % 在左
  2. 用索引列进行计算
  3. 索引列使用函数
  4. 索引列使用 !=

3 基本原理

3.1 工作流程

查询操作：建立连接 -> 查询缓存 -> SQL 解析 -> SQL 优化 -> 执行查询

更新操作

  1. 【Service 层】请求获取数据行（省略解析、优化部分）
  2. 【存储引擎】从内存或磁盘返回数据行（change buffer 更新）
  3. 【Service 层】请求执行数据行修改
  4. 【存储引擎】更新到内存，写入 redo log（prepare 阶段）
  5. 【Service 层】记录操作写入 binlog，提交事务
  6. 【存储引擎】确认事务提交，修改 redo log（commit 阶段）

刷写操作：对于数据更新操作，在更新内存写完 redo log 后，就返回给客户端，本次更新成功。而在空闲时，把内存里的数据写入磁盘的过程称为 flush。触发条件：redo log 写满、系统内存不足、系统空闲时、正常关闭时。

排序操作

  • 如果内存足够，则多利用内存，尽量减少磁盘访问。
  • 如果可确保从索引上取出的行即有序（如联合索引），则不需要排序，在索引上取满足条件的主键、在主键索引上取完整行添加到结果集即可。
  • 结合覆盖索引优化：结果集的字段设置为联合索引，可避免回表。

3.2 系统架构

3.2.1 Service层
  • 连接器：负责与客户端建立连接、获取权限、维护与管理连接。
  • 分析器：对客户端请求的 SQL 依次执行词法分析、语法分析，将 SQL 转换成执行计划（合法的语法树），MySQL 根据执行计划与 InnoDB 交互。
  • 优化器：执行前需要对指令进行优化，包括决定使用的索引、决定多表关联时的连接顺序等，生成最优的的执行计划（语法树遍历的最优路径）。
    • 优化类型：重写关联表顺序、外连接改内连接、表达式等价替换、优化聚集操作、预估转换常量表达式、覆盖索引扫描、子查询优化、提前终止优化、等值传播。
    • 执⾏计划：MySQL ⽣成⼀颗指令树，通过存储引擎执⾏该树上的指令。
      • 关联查询：多表 JOIN 时，MySQL 从左边开始嵌套循环查询，因此成本越小的表放在前面越能得到最优的查询计划。
      • 排序
        • 若数量小于排序缓冲区，使用快速排序；否则使用归并排序 + 快速排序。
        • 涉及 JOIN 时，如果排序字段都来自第一个表，则嵌套之前就完成排序。
    • 可能出现索引误判的情况，可以手动修改或强制指定索引。
  • 执行器：与存储引擎交互，查询数据行、提交修改操作等，最终返回结果。
  • 日志模块：binlog —— 备份恢复
3.2.2 存储引擎
  • InnoDB 是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。
    • 实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻读。
    • 主索引是聚簇索引。
    • redo log 即重做日志。MySQL 的更新操作利用 WAL(Write-Ahead Logging) 机制，先写日志，再写磁盘。有了 redo log 才能确保数据库发生异常重启时之前提交的记录都不会丢失（crash-safe）。
      • 一条记录需要更新时会先写入 redo log，并更新内存，此时更新操作完成；
      • InnoDB 引擎会在系统较空闲时把这个操作写入磁盘。
    • 修改缓存 change buffer：当执行器请求更新一个数据页时，如果数据页在内存中，则直接更新；如不在内存中，则会将这些更新操作缓存在 change buffer。
    • InnoDB 里的数据用 B+ 树的结构组织。假设要删掉某条记录，InnoDB 引擎只会把这个记录标记为删除。对于 delete 命令，只是把记录的位置或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。
  • MyISAM 设计简单，数据以紧密格式存储。
比较InnoDBMyISAM
事务支持不支持
并发表级锁
行级锁		表级锁
外键支持不支持
备份在线热备份 
崩溃恢复快慢，且易损坏
其它MySQL 默认压缩表
空间索引技术
3.2.3 日志
  • binlog 用于记录数据库执行的写入性操作（不包括查询）信息，以二进制的形式保存在磁盘中。binlog 是 MySQL 的逻辑日志（简单理解为记录 SQL），并且由 Server 层进行记录，使用任何存储引擎的 MySQL 数据库都会记录 binlog 日志。
    • 使用场景：主从复制和数据恢复。
  • redo log 包括两部分：一个是内存中的日志缓冲（redo log buffer），另一个是磁盘上的日志文件（redo log file）。
    • MySQL 每执行一条DML语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。
  • undo log 主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条 DELETE 的 undo log，这样在发生错误时，就能回滚到事务之前的数据状态。
    • 原子性底层就是通过 undo log 实现的
    • MVCC（多版本并发控制）实现的关键
对比redo logbinlog
功能重做日志归档日志
文件大小大小固定可配置
归属模块InnoDB 存储引擎Service 层，所有引擎
写入方式循环写，写到结尾则回到开头追加写，达到大小切换文件
性质物理日志，记录数据页执行的具体修改内容逻辑日志，记录语句的原始逻辑
适用场景崩溃恢复主从复制和数据恢复

4 性能优化

4.1 查询优化

  • Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句（是否适用索引、适用哪个索引、扫描行数、排序等）。主要字段：
    • select_type: 查询类型，有简单查询、联合查询、子查询等
    • table、type：表、表的连接类型
    • key: 使用的索引
    • rows: 扫描的行数
  • 减少请求数据量
    • 必要的列：不要使用 SELECT * 语句
    • 必要的行：使用 LIMIT 语句来限制返回的数据
    • 缓存数据
  • 减少服务器端扫描行数：索引
  • 切分大查询：一个大查询如果一次性执行，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
  • 分解关联查询：将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，让应用程序承担更多职责。
  • 分组查询：优先根据索引分组；优先对 JOIN 的后面的表分组。

4.2 索引优化

  • 使用 EXPLAIN 会预估出需要扫描的行数，并可根据当前查询方式分析出访问类型（type）。
  • 查询条件
    • 独立的列：在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，也要避免隐式类型转换和隐式字符编码转换。
    • 多列索引顺序：选择性（不重复的索引值和记录总数的比值）越高，查询效率也越高。应该让选择性最强的索引列放在前面。
    • 范围查询：尽可能将范围查询的字段放在最后；不要滥用 IN 语句。
  • 涉及维护（建议）
    • 多列索引：在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。
    • 覆盖索引：索引中包含所有需要查询的字段的值，查询时只需要过滤辅助索引，不需要回表（不断在辅助索引和聚簇索引之间判断、取值）向引擎请求主键索引。
    • 前缀索引：对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引
    • 避免冗余：如果已经有一个联合索引，就不必再有其子集的索引。
    • 锁优化：在辅助索引上使用共享锁，在主键索引上使用排他锁。

4.3 库表优化

  • 分表
    • 水平切分又称为 Sharding，它是将同一个表中的记录根据 ID （哈希、范围、映射表等策略）拆分到多个结构相同的表中。当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。
    • 垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。
  • 分库
    • 读写分离：主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。基于主从复制实现。
    • 切分中间件。

数据库复制：为了减轻主库的压力，应该在系统应用层面做读写分离，写操作走主库，读操作走从库。复制的结果是集群（Cluster）中的所有数据库服务器得到的数据理论上都是一样的，都是同一份数据，只是有多个副本。

主从复制。主要涉及三个线程。

  • binlog 线程：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
  • I/O 线程：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
  • SQL 线程：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

5 JDBC

架构

  • 双层架构：Java Applet 或应用直接访问数据源。
  • 三层架构：侧架构特殊之处在于，引入中间层服务。可以增加企业数据的访问控制，以及多种类型的更新；另外，也可简化应用的部署，并在多数情况下有性能优势。

编程步骤

  1. 加载驱动程序；
    • Class.forName(driverClass)
  2. 获得数据库连接；
  3. 创建 Statement / PreparedStatement 对象；
    • PreparedStatement：表示预编译的 SQL 语句的对象，然后可以使用此对象多次高效地执行该语句。批处理比 Statement 效率高。
    • Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
  4. 执行 SQL 语句；
    • executeQuery：用于产生单个结果集的语句，例如 SELECT 语句。返回值是 ResultSet。
    • executeUpdate：用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQL DDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。返回值是一个整数，指示受影响的行数（即更新计数）。
    • execute：用于执行返回多个结果集、多个更新计数或二者组合的语句。
  5. 如果有数据，从 ResultSet 中获取数据。

数据库连接池：负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。

  • C3P0 是一个开源的 JDBC 连接池，它实现了数据源和 JNDI 绑定，支持 JDBC3 规范和 JDBC2 的标准扩展。目前使用它的开源项目有 Hibernate、Spring 等。
  • DBCP 是一个依赖 Jakartacommons-pool 对象池机制的数据库连接池。DBCP 可以直接的在应用程序中使用。
⤧  Next post 【OO】敏捷软件开发 ⤧  Previous post 【DB】数据库 01 数据库原理