认识MySQL

1985 年，瑞典的几位志同道合小伙子（以 David Axmark 为首）成立了一家公司，这就是 MySQL AB 的前身。这个公司最初并不是为了开发数据库产品，而是实现他们想法的过程中，需要一个数据库。他们希望能够使用开源的产品。但是当时并没有一个合适的选择，没办法，那就自己开发吧。

MySQL 架构示意简图

MySQL 架构图

Understanding MySQL Architecture

• Connectors（接入方）：支持协议很多

• Management Serveices & Utilities（系统管理和控制工具）：例如：备份恢复，MySQL 复制集群等
• Connection Pool（连接池）：管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求
• SQL Interface（SQL 接口）：接受用户的 SQL 命令，并且返回用户需要查询的结果。比如 select from 就是调用 SQL Interface

• Parser（解析器）：SQL 命令传递到解析器的时候会被解析器验证和解析。解析器是由 Lex 和 YACC 实现的。

• Optimizer（查询优化器）：SQL 语句在查询之前会使用查询优化器对查询进行优化

• Cache & Buffer（高速缓存区）：查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

• Pluggable Storage Engines（插件式存储引擎）：存储引擎是 MySQL 中具体的与文件打交道的子系统。也是 MySQL 最具有特色的一个地方。 Mysql的存储引擎是插件式的。

• File System（文件系统）：数据、日志（redo，undo）、索引、错误日志、查询记录、慢查询等

MySQL 模块间的关系图

运行时机理图

存储引擎

• 插拔式的插件方式
• 存储引擎是指定在表之上的，即一个库的每一个表都可以指定专用的存储引擎
• 不管表采用什么样的存储引擎，都会在数据区产生对应的一个 frm 文件（表结构定义描述文件）

CSV 存储引擎

• CSV 官方文档

• 数据存储为 CSV 文件
• 特定
  • 不支持索引，不支持分区，列定义必须为 NOT NULL，不能设置自增列。不适用大表或大数据的在线处理
  • CSV 数据的存储用 , 隔开，可直接编辑 CSV 文件进行数据的编辑。数据安全性低。编辑之后，使用 flush table table_name; 使其生效
• 应用场景
  • 数据的快速导入导出
  • 表格直接转换成 CSV

ARCHIVE 存储引擎

• ARCHIVE 官方文档
• 压缩协议进行数据的存储，数据存储为 ARZ 文件格式
• 特点
  • 只支持 insert 和 select 两种操作
  • 只允许自增 ID 列建立索引
  • 行级锁
  • 不支持事务
  • 数据占用磁盘少
• 应用场景
  • 日志系统
  • 大量的设备数据采集

MEMORY 存储引擎

• MEMORY 官方文档
• 数据d欧式存储在内存中，IO 效率要比其他存储引擎高很多。服务重启数据丢失，内存数据默认只有 16M
• 特点
  • 支持 hash 索引，B-Tree 索引，默认 hash（查找复杂度 O(1)）
  • 字段长度都是固定长度 varchar(32)=char(32)
  • 不支持大数据存储类型字段，如 blog text
  • 表级锁
• 应用场景
  • 等值查找热度较高数据
  • 查询结果内存中的计算，大多数都是采用这种存储引擎作为临时表存储需计算的数据

MyISAM 存储引擎

• MyISAM 官方文档
• MySQL 5.5 版本之前的默认存储引擎，较多的系统表也还是使用这个存储引擎，系统临时表也会用到 MyISAM 存储引擎
• 特点
  • select count(*) from table 无需进行数据的扫描（MyISAM 存储引擎保存表的总行数）
  • 数据 MYD 和索引 MYI 分开存储
  • 表级锁
  • 不支持事务

InnoDB 存储引擎

• InnoDB 官方文档
• MySQL 5.5 及以后版本的默认存储引擎
• 特点
  • 事务 ACID
  • 行级锁
  • 聚集索引（主键索引）方式进行数据存储
  • 支持外键关系保证数据完整性

存储引擎对比

查询执行路径

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入一下阶段。
3. 服务端进行 SQL 解析、预处理，再由优化器生成对应的执行计划。
4. MySQL 根据优化器生成的执行计划，调用存储执行引擎的 API 来执行查询。
5. 将结果返回给客户端。

MySQL 客户端/服务端通信

• MySQL 客户端有服务端的通信方式是 半双工
• 全双工：双向通信，发送同时也可以接收
• 半双工：双向通信，同时只能接收或者是发送，无法同时操作
  • 在任何一个时刻，要么是有服务器向客户端发送数据，要么是客户端向服务端发送数据，这两个动作不能同时发生。所以我们无法也无需将一个消息切成小块进行传输
  • 特点和限制
    • 客户端一旦开始发送消息，另一端要接收完整个消息才能响应
    • 客户端一旦开始接收数据没法停下来发送指令
• 单工：只能单一方向传送

对于一个 MySQL 连接，或者说一个线程，时刻都有一个状态来标识这个连接正在做什么

-- 查看命令
show full processlist;
show processlist;

查询结果

查询状态 Command

• General Thread States
• Sleep 线程正在等待客户端发送数据
• Query 连接线程正在执行查询
• Locked 线程正在等待表锁的释放
• Sorting result 线程正在对结果进行排序
• Sending data 向请求端返回数据

可通过 kill {id} 的方式杀掉连接

查询缓存

• 工作原理
  • 缓存 select 操作的结果集和 SQL 语句
  • 新的 select 语句，先去查询缓存，判断是否存在可用的记录集
• 判断标准
  • 与缓存的 SQL 语句是否完全一样，区分大小写（简单认为存储了一个 key-value 结构，key 为 SQL，value 为 SQL 查询结果集）
• 主要参数
  • query_cache_type
    • 0-不启用查询缓存，默认值
    • 1-启用查询缓存，只要符合查询缓存的要求，客户端的查询语句和记录集都可以缓存起来，供其他客户端使用，加上 SQL_NO_CACHE 将不缓存
    • 2-启用查询缓存，只要查询语句中添加了参数 SQL_CACHE，且符合查询缓存的要求，客户端的查询语句和记录集则可以缓存起来，供其他客户端使用
  • query_cache_size：允许设置 query_cache_size 的值，最小为 40K，默认 1M，推荐设置为：64M/128M
  • query_cache_limit：限制查询缓存区最大能缓存的查询记录集，默认设置为 1M
  • show status like 'Qcache'：可查看缓存情况
• 不会缓存的情况
  • 当查询语句中有一些不确定的数据时，则不会被缓存。如包含函数 NOW()、CURRENT_DATE() 等类似的函数、或者用户自定义的函数、存储函数、用户变量等都不会被缓存
  • 当查询的结果大于 query_cache_limit 设置的值时，结果不会被缓存
  • 对于 InnoDB 引擎来说，当一个语句在事务中修改了某个表，那么在这个事务提交之前，所有与这个表相关的查询都无法被缓存。因此长时间执行事务，会大大降低缓存命中率。
  • 查询的表是系统表
  • 查询语句不涉及到表
• 为什么 MySQL 默认关闭了查询缓存？
  • 在查询之前必须先检查是否命中缓存，浪费计算资源
  • 如果这个查询可以被缓存，那么执行完成后，MySQL 发现查询缓存中没有这个查询，则会将结果存入查询缓存，这会带来额外的系统消耗
  • 针对表进行写入或更新数据时，将对应表的所有缓存设置失效
  • 如果查询缓存很大或者碎片很多时，这个操作可能带来很大的系统消耗
• 适用业务场景
  • 以读为主的业务，数据生成之后就不常改变的业务
  • 比如：门户类、新闻类、报表类、论坛类等

查询优化处理

• 查询优化处理的三个阶段

  • 解析 SQL：通过 lex 词法分析、yacc 语法分析将 SQL 语句解析成解析树
  • 预处理阶段：根据 MySQL 的语法的规则进一步检查解析树的合法性，如：检查数据的表和列是否存在，解析名字和别名的设置。还会进行权限的验证
  • 查询优化器：优化器的主要作用是找到最优的执行计划

• 查询优化器如何找到最优的执行计划

  • 使用等价变化规则

    -- 基于联合索引，调整条件位置等
    5=5 and a>5 改为 a>5
    a<b and a=5 改为 b>5 and a=5
    

  • 优化 count min max 等函数

    • min 函数只需找索引最左边
    • max 函数值需找索引最右边
    • MyISAM 引擎 count(*)

  • 覆盖索引扫描

  • 子查询优化

  • 提前终止查询：用 limit 关键字或者使用不存在的条件

  • in 的优化：先进性排序，再采用二分查找的方式

  • MySQL 的查询优化器是基于成本计算的原则，它会尝试各种执行计划。数据抽样的方式进行试验（随机的读取一个 4K 数据库块进行分析）

查询执行引擎

调用插件式的存储引擎的原子API的功能进行执行计划的执行

返回客户端

• 有需要做缓存的，执行缓存操作
• 增量的返回结果：开始生成第一条结果时，MySQL 就开始往请求方逐步返回数据。好处：MySQL 服务器无须保存过多的数据，浪费内存。用户体验好，马上就拿到了数据

执行计划

• 执行计划（Query Execution Plan）：使用 explain 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句。分析查询语句或是表结构的性能瓶颈
• 语法：explain + SQL 语句

• 执行计划的作用

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

MySQL 解析器顺序

1
2
3
4
5
6
7
8
9
10
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT 
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

执行计划查询结果字段

• id：标识执行的顺序

  • id 相同，执行顺序由上至下
  • id 不同，如果是子查询，id 的序号会递增。id 值越大优先级越高，越先被执行
  • id 相同又不同，即两种情况都存在，id 如果相同，可以认为是一组，从上往下顺序执行。在所有组中，id 值越大，优先级越高，越先执行

• select_type：主要用于区分普通查询、联合查询、子查询等

  • SIMPLE：简单的 select 查询，查询中不包含子查询或者 union
  • PRIMARY：查询中包含子部分，最外层查询则被标记为 primary
  • SUBQUERY/MATERIALIZED：SUBQUERY 表示在 select 或 where 列表中包含了子查询。MATERIALIZED 表示where 后面in条件的子查询
  • UNION：若第二个 select 出现在 union 之后，则被标记为 union
  • UNION RESULT：从 union 表获取结果的 select

• table：查询涉及到的表，直接显示表名或者表的别名

  • <unionM,N> 由ID为 M,N 查询 union 产生的结果
  • <subqueryN> 由 ID 为 N 查询生产的结果

• type：访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是： system > const > eq_ref > ref > range > index > ALL

  • system：表只有一行记录（等于系统表），const 类型的特例，基本不会出现，可以忽略不计
  • const：表示通过索引一次就找到了，const 用于 primary key 或者 unique 索引
  • eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质是也是一种索引访问
  • range：只检索给定范围的行，使用一个索引来选择行，where 语句用到索引
  • index：Full Index Scan，索引全表扫描，把索引从头到尾扫一遍。覆盖索引被引用，即查询扫描索引树
  • ALL：Full Table Scan，遍历全表以找到匹配的行

• possible_key：查询过程中有可能用到的索引

• key

  • 实际使用的索引，如果为 NULL，则没有使用索引

  • 查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠

• key_len

  • key_len：表示索引使用的字节数

  • 根据这个值，可以判断索引的使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到

  • char 和 varchar 跟字符编码有密切的联系

  • Latin1 占用 1 个字节，GBK 占用 2 个字节，UTF-8 占用 3 个字节。（不同字符编码占用的存储空间不同）

  • ken_len 总结

    • 变长字段需要额外的 2 个字节（varchar 值保存时只保存需要的字符数，另加 1 个字节来记录长度，如果列声明的长度超过 255，则使用 2 个字节，所以 varchar 索引长度计算时候要加 2），固定长度字段不需要额外的字节
    1. 而 NULL 都需要 1 个字节的额外空间，索引索引字段最好不要为 NULL，因为 NULL 让统计更加复杂并且需要额外的存储空间
    2. 复合索引有最左前缀的特性，如果复合索引能全部用上，则是复合索引字段的索引长度之和，这也可以用来判断复合索引是否部分使用，还是全部使用

  • 整数、浮点数、时间类型的索引长度

    1. NOT NULL = 字段本身的长度
    2. NULL = 字段本身的字段长度 + 1（因为需要有是否为空的标记，这个标记需要占用 1 个字节）
    3. datetime 类型在 5.6 中字段长度是 5 个字节，在 5.5 中字段长度是 8 个字节

• ref：显示索引的哪一列被使用了，哪些列或常量被用于查找索引列上的值。const 表示是一个常量。

• rows：根据表统计信息及索引选用情况，大致估算出找到查询记录所需要读取的行数

• filtered：它指返回结果的行占需要读到的行(rows列的值)的百分比 表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好

额外信息

• Using filesort：MySQL 对数据使用一个外部的文件内存进行了排序，而不是按照表内的索引进行排序读取
• Using temporary：使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于 order by 和 group by
• Using index：表示相应的 select 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高
• Using where：使用了 where 过滤条件
• select tables optimized away：基于索引优化 min、max 操作，或者 MyISAM 存储引擎优化 count(*) 操作，不必等到执行阶段在进行计算，查询执行计划生成的极端即可完成优化

索引

索引是什么

• 正确的创建合适的索引是提升数据库查询性能的基础
• 定义：索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构
• pros：提高检索效率，降低排序成本，排序、分组主要消耗内存和 CPU 资源
• cons：更新索引的 IO 量，调整索引所致的计算量、存储空间
• 是否创建索引
  • 较频繁的行为查询条件的字段应该创建索引
  • 唯一性太差的字段不适合单独创建索引
  • 更新非常频发的字段不适合创建索引
  • 不会出现在 where 子句中的字段不该创建索引
• 原则
  • 最左匹配原则
  • 选择性最好（离散性最好）原则
  • 最小空间原则

索引分类

MySQL 如何使用索引

大多数 MySQL 索引（PRIMARY KEY， UNIQUE，INDEX 和 FULLTEXT）存储在 B 树。例外：空间数据类型的索引使用 R 树；MEMORY 表还支持哈希索引 ; InnoDB 对 FULLTEXT 索引使用倒排列表。

• 唯一索引：索引列的值必须唯一，但允许有空值
• 单列索引/普通索引：节点中关键字 [name]，即一个索引只包含单个列，一个表可以有多个单列索引。单列索引是特殊的联合索引
• 联合索引/复合索引：节点中关键字 [name,age]，即一个索引包含多个列
  • 联合索引列选择原则
    • 经常用的列优先「最左匹配原则」
    • 选择性（离散度）高的列优先「离散度高原则」
    • 宽度小的列优先「最少空间原则」
• 聚簇索引（聚集索引）：数据库表行中数据的物理顺序与键值的逻辑（索引） 顺序相同。并不是一个单独的索引类型，而是一种数据存储方式。
• 非聚簇索引：不是聚簇索引，就是非聚簇索引
• 辅助索引：InnoDB 中叶子节点存储的是主键索引的关键字，而不是数据相关信息。防止数据插入或删除后辅助索引的维护所带来的性能问题
• 覆盖索引
  • 如果查询列可通过索引节点中的关键字直接返回，则该索引为覆盖索引
  • 覆盖索引可减少数据库 IO，将随机 IO 变为顺序 IO，可提高查询性能

实例

1
2
3
4
5
6
-- 经排查发现最常用的 SQL 语句
select * from users where name = ?;
select * from users where name = ? and age = ?;
-- 解决方案：
create index idx_name on users(name);
create index idx_name_age on users(name, age);

解决：重复创建索引，只需要联合索引 idx_name_age 即可

为什么使用索引

• 索引能极大的减少存储引擎需要扫描的数据量
• 索引可以把随机 IO 变为顺序 IO
• 索引可以帮助我们在进行分组、排序等操作时，避免使用临时表

为什么是 B+Tree

平衡二叉查找树（Balanced Binary Bearch Tree）

• X < 10 -> P1；X = 10 -> 命中；X > 10 -> P2
• 
• 太深了：数据处的深（高）度决定它的 IO 操作次数，IO 操作耗时大
• 太小了：每一个磁盘块（节点/页）保存的数据量太小了
  • 没有很好的利用操作磁盘 IO 的数据交换特性，也没有利用好磁盘 IO 的预读能力（空间局部性原理），从而带来频繁的 IO 操作
  • 操作系统一次 IO 操作是一页，即 4k 数据（ssd 4k 对齐）

多路平衡查找树（B-Tree）

• 绝对平衡树，X < 17 -> P1；X = 17 -> 命中；17< X < 35 -> P2；X = 35 -> 命中；X > 35 -> P3
• 

加强版多路平衡查找数据（B+Tree）

• 左闭合 B+Tree：1 <= X < 28 -> P1；28 <= X < 66 -> P2；X >= 66 -> P3
• 

B+Tree 和 B-Tree 的区别

• B+Tree 节点关键字搜索采用闭合区间
• B+Tree 非叶子节点不保存数据相关信息，只保存关键字和子节点的引用
• B+Tree 关键字对应的数据保存在叶子节点中
• B+Tree 叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系
• B-Tree 和 B+Tree 详解

为什么选用 B+Tree

• B+Tree 是 B-Tree的变种（PLUS 版），多路绝对平衡查找树，具有 B-Tree 的优势
• B+Tree 扫库、表能力更强
• B+Tree 的磁盘读写能力更强
• B+Tree 的排序能力更强
• B+Tree 查询效率更加稳定（ 由于数据 B+Tree 的数据都保存在叶子节点，相对于 B-Tree 更稳定）

MySQL B+Tree 索引体现形式

MyISAM

• .frm：表结构文件
• .MYD：数据文件
• .MYI：索引文件
• 
• 

InnoDB

• .frm：表结构文件
• .ibd：数据+索引文件

• 聚集索引：数据库表行中数据的物理顺序与键值的逻辑（索引） 顺序相同

• 
• 辅助索引：叶子节点存储的是主键索引的关键字，而不是数据相关信息。防止数据插入或删除后辅助索引的维护所带来的性能问题
• 

InnoDB VS MyISAM

• InnoDB：一个文件 IBD
• MyISAM：两个文件，MYI、MYD

索引选择原则

• 离散度高原则：列的离散性：count(distinct col):count(col)，比值越大离散性越好。结论：离散性越高，选择性就越好。反例：性别
• 最左匹配原则：对索引中关键字进行计算（对比），一定是从左往右进行，且不可跳过
  • 

索引策略

• 索引列的数据长度能少则少
• 索引一定不是越多越好，越全越好，一定是建合适的
• 匹配列前缀可能会用到索引：like 9999%，like %9999%、like %9999 用不到索引
• where 条件中 not in 和 <> 操作无法使用索引
• 匹配范围值：order by、group by 也可用到索引
• 多用指定列查询，只返回自己想要的数据列，少用 select *
• 联合索引中如果不是按照索引最左列开始查找，无法使用索引
• 联合索引中精确匹配最左列，并范围匹配另外一列可以用到索引
• 联合索引中如果查询中有某个列范围查询，则其右边的所有列都无法使用索引

1. 尽量全值匹配

2. 最佳左前缀法则：如果是多列索引，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
3. 不在索引列上做任何操作：不在索引列上做任何操作（计算、函数、自动或手动类型转换），会导致索引失效而转向全表扫描
4. 范围条件放最后：存储引擎不能使用索引中范围条件右边的列（范围查询的列会使用）
5. 覆盖索引尽量用：尽量使用覆盖索引（只访问索引的查询，索引和查询列一致），减少 select *
6. 不等于要慎用：MySQL 在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描
7. NULL/NOT 有影响
8. like 查询要当心：like 以通配符开头 %abc… 时，MySQL 索引会失效
9. 字符类型加引号
10. or 改 union 效率高

速记

• 全值匹配我最爱，最左前缀要遵守
• 带头大哥不能死，中间兄弟不能断
• 索引列上少计算，范围之后全失效
• like 百分写最右，覆盖索引不写 *
• 不写空值还有 or，索引影响要注意
• var 引号不可丢，SQL 优化有诀窍

索引基础语法

-- 查看索引
show index from table_name \G;
-- 创建索引
create [unique] index index_name on table_name(column_name);
alert table table_name add [unique] index [index_name] on column_name;
-- 删除索引
drop index [index_name] on table_name;

如何定位慢 SQL

• 业务驱动
• 测试驱动
• 慢查询日志

配置文件

1
2
3
4
5
[mysqld]
slow_query_log=1
slow_query_log_file=/path/to/file
long_query_time=0.2
log_output=FILE

SQL 语句配置

show variables like 'slow_query_log'
set global slow_query_log = on
set global slow_query_log_file = '/var/log/mysql/slow.log'
set global log_queries_not_using_indexes = on
-- 单位：秒
set global long_query_time = 0.1

• Time ：日志记录的时间
• User@Host：执行的用户及主机
• Query_time：查询耗费时间
• Lock_time 锁表时间
• Rows_sent 发送给请求方的记录、条数
• Rows_examined 语句扫描的记录条数
• SET timestamp 语句执行的时间点
• select...：执行的具体语句

慢查询日志分析工具

• mysqldumpslow
• mysqlsla
• pt-query-digest

mysqldumpslow -t 10 -s at /var/log/mysql/slow.log

事务

数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）

典型事务场景(转账)：

update user_account set balance = balance - 1000 where userID = 3;
update user_account set balance = balance +1000 where userID = 1;

-- MySQL 中如何开启事务
begin / start transaction -- 手工
commit / rollback -- 事务提交或回滚
set session autocommit = on/off; -- 设定事务是否自动开启

JDBC 编程： connection.setAutoCommit(boolean);

Spring 事务 AOP 编程： expression=execution(com.test.dao.*.*(..))

事务四大特性

• 原子性（Atomicity）：最小的工作单元，整个工作单元要么一起提交成功，要么全部失败回滚
• 一致性（Consistency）：事务中操作的数据及状态改变是一致的，即写入资料的结果必须完全符合预设的规则，不会因为出现系统意外等原因导致状态的不一致
• 隔离性（Isolation）：一个事务所做的操作在提交之前，对其他事务的可见性设定（一般设定为不可见）
• 持久性（Durability）：事务所做的修改就会永久保存，不会因为系统意外导致数据的丢失

事务并发带来了哪些问题

事务四种隔离级别

Transaction Isolation Levels

SQL92 ANSI/ISO 标准

InnoDB 中的事务隔离级别和锁的关系

• Read Uncommitted（未提交读） – 未解决并发问题
  • 事务未提交对其他事务也是可见的，脏读（dirty read）
• Read Committed（提交读） – 解决脏读问题
  • 一个事务开始之后，只能看到自己提交的事务所做的修改，不可重复读（nonrepeatable read）
• Repeatable Read (可重复读) – 解决不可重复读问题，MySQL 默认隔离级别
  • 在同一个事务中多次读取同样的数据结果是一样的，这种隔离级别未定义解决幻读的问题
• Serializable（串行化） – 解决所有问题
  • 最高的隔离级别，通过强制事务的串行执行
• 脏读：T1 修改数据 A 未提交，T2 访问 A，读到了脏数据
  • 指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据， 那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。
• 不可重复读：T1 多次读取数据 A，T2 在此过程中修改数据 A
  • 指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
• 幻读：T1 修改表 A 中所有数据，T2 向表 A 中插入一条新数据，T1 发现表 A 中有未修改数据
  • 指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

InnoDB 引擎对隔离级别的支持

隔离级别实现：锁、MVCC

锁

• InnoDB Locking
• 共享锁（行锁）：Shared Locks
• 排它锁（行锁）：Exclusive Locks
• 意向锁共享锁（表锁）：Intention Shared Locks
• 意向锁排它锁（表锁）：Intention Exclusive Locks
• 自增锁：AUTO-INC Locks
• 行锁的算法
  • 记录锁 Record Locks
  • 间隙锁 Gap Locks
  • 临键锁 Next-key Locks
• 页锁：介于表锁和行锁之间，实现 BerkeleyDB

-- 表级锁的竞争状态变量
show status like 'table%';
-- 行级锁竞争状态变量
show status like 'innodb_row_lock%';

理解表锁、行锁

• 锁是用于管理不同事务对共享资源的并发访问

• 表锁与行锁的区别：
  • 锁定粒度：表锁 > 行锁
  • 加锁效率：表锁 > 行锁
  • 冲突概率：表锁 > 行锁
  • 并发性能：表锁 < 行锁
• InnoDB存储引擎支持行锁和表锁（另类的行锁）

InnoDB 锁实现

• InnoDB 的行锁是通过给索引上的索引项加锁来实现的。

• 只有通过索引条件进行数据检索，InnoDB 才使用行级锁，否则 InnoDB 将使用表锁（锁住索引的所有记录）

• 表锁：lock tables xx read/write;

行锁

• pros：粒度小
• cons：获取、释放做的工作更多，容易发生死锁
• 实现：InnoDB
  • 共享锁
  • 排它锁
  • 间隙锁：通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空余空间上标记锁定信息来实现的
• 锁优化
  • 尽可能让所有的数据检索都通过索引来完成
  • 合理设计索引
  • 减少基于范围的数据是检索过滤条件
  • 尽量控制事务的大小
  • 业务允许的情况下，尽量使用较低级别的事务隔离

表锁

• pros：实现逻辑简单，获取、释放快，避免死锁
• cons：粒度太大，并发不够高
• 实现：MyISAM

共享锁（Shared Locks）VS 排它锁（Exclusive Locks）

• 共享锁
  • 又称为读锁，简称 S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁， 都能访问到数据，但是只能读不能修改;
  • 加锁释锁方式：select * from users WHERE id=1 LOCK IN SHARE MODE; commit/rollback
• 排他锁
  • 又称为写锁，简称 X锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他 锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有该获取了排他锁的事务是可以对 数据行进行读取和修改，（其他事务要读取数据可来自于快照）
  • 加锁释锁方式
    • delete / update / insert 默认加上 X锁
    • SELECT * FROM table_name WHERE ... FOR UPDATE
    • commit/rollback

意向锁共享锁（IS）&意向锁排它锁（IX）

• 意向共享锁（IS）表示事务准备给数据行加入共享锁，即一个数据行加共享锁前必须先取得该表的 IS锁， 意向共享锁之间是可以相互兼容的

• 意向排它锁（IX）表示事务准备给数据行加入排他锁，即一个数据行加排他锁前必须先取得该表的 IX锁， 意向排它锁之间是可以相互兼容的

• 意向锁（IS、IX）是 InnoDB 数据操作之前自动加的，不需要用户干预

• 意义：当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能启用表锁

自增锁 AUTO-INC Locks

针对自增列自增长的一个特殊的表级别锁

show variables like 'innodb_autoinc_lock_mode';

默认取值 1，代表连续，事务未提交，ID 永久丢失

记录锁（Record）&间隙锁（Gap）&临键锁（Next-key）

• Next-key locks： 锁住记录+区间（左开右闭）
  • 当sql执行按照索引进行数据的检索时,查询条件为范围查找（between and、<、>等）并有数 据命中则此时SQL语句加上的锁为 Next-key locks，锁住索引的记录+区间（左开右闭）
  • 
• Gap locks： 锁住数据不存在的区间（左开右开）
  • 当sql执行按照索引进行数据的检索时，查询条件的数据不存在，这时 SQL 语句加上的锁即为 Gap locks，锁住索引不存在的区间（左开右开）
  • Gap 只在 RR 事务隔离级别存在
  • 
• Record locks： 锁住具体的索引项
  • 当 SQL 执行按照唯一性（Primary key、Unique key）索引进行数据的检索时，查询条件等值匹 配且查询的数据是存在，这时SQL语句加上的锁即为记录锁Record locks，锁住具体的索引项
  • 

利用锁解决脏读

利用锁解决不可重复读

利用锁解决幻读

死锁

Deadlocks in InnoDB

• 多个并发事务（2个或者以上）
• 每个事务都持有锁（或者是已经在等待锁）
• 每个事务都需要在继续持有锁
• 事务之间产生加锁的循环等待，形成死锁

BEGIN;
update users set lastUpdate = NOW() where id =1;
update t2 set `name`='test' where id =1;

-- 其他会话
BEGIN;
update t2 set `name`='test' where id =1;
update users set lastUpdate = NOW() where id =1;

死锁的避免

• 类似的业务逻辑以固定的顺序访问表和行数据
• 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
• 在同一个事务中，尽可能做到一次锁定所需要的资源，减少死锁概率。
• 降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择
• 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁（或者说是表锁）

MVCC

MVCC：Multiversion concurrency control (多版本并发控制)

并发访问(读或写)数据库时，对正在事务内处理的数据做 多版本的管理。以达到用来避免写操作的堵塞，从而引发读操作的并发问题。

MVCC 逻辑流程-插入

删除

修改

查询

案例

-- 数据准备
insert into teacher(name,age) value ('seven',18);
insert into teacher(name,age) value ('qing',20);
-- 事务1
begin; --1
select * from users; --2
commit;
-- 事务2
begin; --3
update teacher set age =28 where id =1; --4
commit;

案例一（1，2，3，4，2）

案例二（3，4，1，2）

问题

-- 查看mysql的设置的事务隔离级别
select global.@@tx_isolation;
select @@tx_isolation;
-- 示例1
-- 事务1
set session autocommit = off;
update users set lastUpdate = now() where id = 1;
-- 在未做commit/rollback操作之前 在其他的事务我们能不能进行对应数据的查询（特别是加上了 X锁的数据）
-- 事务2
select * from users where id > 1;
select * from users where id = 1;

-- 示例2
-- 事务1
begin
select * from users where id =1 ;
-- 事务2
begin
update users set lastUpdate = now() where id =1;
-- 事务1
select * from users where id =1;
-- 这两个案例从结果上来看是一致的，原理 MVVC

Undo

• Undo Log 是什么
  • undo 意为取消，以撤销操作为目的，返回指定某个状态的操作
  • undo log指事务开始之前，在操作任何数据之前,首先将需操作的数据备份到一个地方 (Undo Log)
• UndoLog 是为了实现事务的原子性而出现的产物
• Undo Log实现事务原子性：事务处理过程中如果出现了错误或者用户执行了 ROLLBACK 语句，Mysql可以利用Undo Log中的备份 将数据恢复到事务开始之前的状态 UndoLog 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制
• Undo log 实现多版本并发控制：事务未提交之前，Undo保存了未提交之前的版本数据，Undo 中的数据可作为数据旧版本快照供 其他并发事务进行快照读

当前读、快照度

• 快照读：SQL读取的数据是快照版本，也就是历史版本，普通的SELECT就是快照读 innodb快照读，数据的读取将由 cache(原本数据) + undo(事务修改过的数据) 两部分组成

• 当前读：SQL读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改 UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE 都是当前读

Redo

• Redo Log 是什么
  • Redo，顾名思义就是重做。以恢复操作为目的，重现操作；
  • Redo log指事务中操作的任何数据,将最新的数据备份到一个地方 (Redo Log)
• Redo log的持久：不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo 中。具体 的落盘策略可以进行配置

• RedoLog 是为了实现事务的持久性而出现的产物

• Redo Log 实现事务持久性：防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据 redo log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

Redo log 补充知识点

• 指定Redo log 记录在{datadir}/ib_logfile1&ib_logfile2 可通过innodb_log_group_home_dir 配置指定 目录存储

• 一旦事务成功提交且数据持久化落盘之后，此时Redo log中的对应事务数据记录就失去了意义，所 以Redo log的写入是日志文件循环写入的
  • 指定Redo log日志文件组中的数量 innodb_log_files_in_group 默认为2
  • 指定Redo log每一个日志文件最大存储量innodb_log_file_size 默认48M
  • 指定Redo log在cache/buffer中的buffer池大小innodb_log_buffer_size 默认16M
• Redo buffer 持久化Redo log的策略， Innodb_flush_log_at_trx_commit：
  • 取值 0 每秒提交 Redo buffer –> Redo log OS cache –>flush cache to disk[可能丢失一秒内 的事务数据]
  • 取值 1 默认值，每次事务提交执行Redo buffer –> Redo log OS cache –>flush cache to disk [最安全，性能最差的方式]
  • 取值 2 每次事务提交执行Redo buffer –> Redo log OS cache 再每一秒执行 ->flush cache to disk操作

配置优化

MySQL 服务器参数类型

• 基于参数的作用域：

• 全局参数 set global autocommit = ON/OFF;

• 会话参数(会话参数不单独设置则会采用全局参数) set session autocommit = ON/OFF;

• 注意：

  • 全局参数的设定对于已经存在的会话无法生效
  • 会话参数的设定随着会话的销毁而失效
  • 全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效

配置文件位置

mysql --help 寻找配置文件的位置和加载顺序

1
2
# Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
mysql --help | grep -A 1 'Default options are read from the following files in the given order'

全局配置文件配置

• 最大连接数配置 max_connections

• 系统句柄数配置
  • /etc/security/limits.conf
  • ``ulimit -a`
• MySQL 句柄数配置 /usr/lib/systemd/system/mysqld.service

常见全局配置文件配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0

max_connections=2000
lower_case_table_names = 0 #表名区分大小写 server-id = 1
tmp_table_size=16M
transaction_isolation = REPEATABLE-READ
ready_only=1
...

MySQL 内存参数配置

• 每一个 connection 内存参数配置
  • sort_buffer_size：connection 排序缓冲区大小
    • 建议 256K(默认值) -> 2M之内
    • 当查询语句中有需要文件排序功能时，马上为 connection 分配配置的内存大小
  • join_buffer_size：connection 关联查询缓冲区大小
    • 建议256K(默认值) -> 1M之内
    • 当查询语句中有关联查询时，马上分配配置大小的内存用这个关联查询，所以有可能在一个查询语句中会分配很多个关联查询缓冲区
• 上述配置4000连接占用内存：4000*(0.256M+0.256M) = 2G
• innodb_buffer_pool_size：innodb buffer/cache 的大小（默认128M）
• innodb_buffer_pool
  • 数据缓存
  • 索引缓存
  • 缓冲数据
  • 内部结构
• 大的缓冲池可以减小多次磁盘 I/O 访问相同的表数据以提高性能
• 参考计算公式：innodb_buffer_pool_size = （总物理内存 - 系统运行所用 - connection 所用）* 90%

MySQL 其他参数配置

• wait_timeout：服务器关闭非交互连接之前等待活动的秒数
• innodb_open_files：限制 InnoDB 能打开的表的个数
• innodb_write_io_threads innodb_read_io_threads：InnoDB 使用后台线程处理 InnoDB 缓冲区数据页上的读写 I/O(输入输出)请求
• innodb_lock_wait_timeout：InnoDB事务在被回滚之前可以等待一个锁定的超时秒数

MySQL 配置文件参数详解

数据库表设计

数据库设计

• 第一范式（ 1NF）： 字段具有原子性,不可再分。 所有关系型数据库系统都满足第一范式）数据库表中的字 段都是单一属性的， 不可再分；

• 第二范式（ 2NF）： 要求实体的属性完全依赖于主键。 所谓完全依赖是指不能存在仅依赖主键一部分的属性， 如果存在， 那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体， 新实体与原 实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。 简而言之， 第二范式就是属性完全依赖主键。

• 第三范式（ 3NF）： 满足第三范式（ 3NF） 必须先满足第二范式（ 2NF）。 简而言之， 第三范式（ 3NF） 要求一个数据库表中不包含已在其它表中已包含的非主键信息。

• 简单一点：

  • 每一列只有一个单一的值，不可再拆分
  • 每一行都有主键能进行区分
  • 每一个表都不包含其他表已经包含的非主键信息。

数据表设计

• 充分的满足第一范式设计将为表建立太量的列
  • 数据从磁盘到缓冲区，缓冲区脏页到磁盘进行持久的过程中，列的数量过多 会导致性能下降。过多的列影响转换和持久的性能
• 过分的满足第三范式化造成了太多的表关联
  • 表的关联操作将带来额外的内存和性能开销
• 使用 InnoDB 引擎的外键关系进行数据的完整性保证
  • 外键表中数据的修改会导致Innodb引擎对外键约束进行检查，就带来了额外的开销

影响性能的因素

1. 人为因素-需求
2. 程序员因素-面向对象
3. Cache
4. 对可扩展过度追求
5. 表范式
6. 应用场景
   • OLTP
   • OLAP

OLTP

联机事务处理（OLTP, Online transaction processing）是指透过信息系统、电脑网络及数据库，以在线交易的方式处理一般即时性的作业数据，和更早期传统数据库系统大量批量的作业方式并不相同。OLTP通常被运用于自动化的数据处理工作，如订单输入、金融业务…等反复性的日常性交易活动。 和其相对的是属于决策分析层次的联机分析处理（OLAP）。

• 特点：数据量大，每次访问数据比较少，数据离散，活跃数据占比不大
• 优化
  • 活跃数据不大，扩大内存容量将活跃数据 cache 住
  • IO 频繁，IOPS（IO per second），Not 吞吐量
  • 并发大，CPU 要强劲
  • 与客户端交互频繁，网络设备扛流量的能力不能差

OLAP

联机分析处理（英语：Online analytical processing），是计算机技术中快速解决多维分析问题（MDA）的一种方法。OLAP是更广泛的商业智能范畴的一部分，它还包括关系数据库、报告编写和数据挖掘。OLAP的典型应用包括销售业务报告、市场营销、管理报告、业务流程管理（BPM）、预算和预测、财务报表以及类似领域，新的应用正在出现，如农业。术语“OLAP”是对传统数据库术语“联机事务处理”（OLTP）稍微修改而成的。

• 特点：数据量大，并发不高，单次检索数据量多，数据访问集中，没有明显的活跃数据
• 优化
  • 磁盘单位容量要大
  • IO 吞吐量，Not IOPS
  • 并发少，CPU 要求没有那么高
  • 计算量大时间长，并行要求高，集群网络通讯要求高

MySQL 优化

• 常用优化方式
  • 服务器硬件
  • MySQL 服务器优化
  • 反范式设计优化
  • 物理设计优化（字段类型、长度设计、存储引擎选择）
  • 索引优化
• 优化原则
  • 永远用小结果集驱动大结果集 join
  • 只取出自己需要的 columns
    • 数据量
    • 排序占用空间 max_length_for_sort_data
  • 仅仅使用最有效的过滤条件 ken length
  • 尽可能避免复杂的 join 和子查询，锁资源

Profiling（了解）

set profiling=1;
select nick_name ,count(*) from user group by nick_name;
show profiles;
show profile cpu,block io for query 75;

join

优化

• 永远用最小结果集驱动大的结果集
• 保证被驱动表上的 join 条件字段已经被索引
• Join Buffer：join_buffer_size

-- 单位：字节（B）
show variables like 'join_%';

order by

• 实现
  • 有序
  • 无序
    • 排序字段和指针 sort buffer 排序，然后用指针去取数据
    • 排序字段和所有数据全部取出，排序字段和指针 sort buffer 排序（其它数据放到内存中），指针到内存里去取数据然后返回。特点：节省 IO，耗内存，空间换时间
• 优化
  • 索引顺序一致的话不需要再排序
  • 加大 max_length_for_sort_data，从而使用第二种排序方法（排序只针对需要排序的字段）
  • 内存不充足时，去掉不必要的字段
  • 增大 sort_buffer_size，减少在排序过程中对需要排序的数据进行分段

group by

group by 前提是排序

DISTINCT 基于 group by

LIMIT

1
2
3
SELECT * FROM user limit 10000,10;
-- 取 10010 条数据
SELECT * FROM user where id> 10000 limit 10;

数据库如何优化

• 业务，数据库并发（锁）
• 索引：优化原则，join、order by、group by

• Previous
  性能优化-Tomcat
• Next
  Git