Database MySQL

MySQL Notes

Posted by keys961 on April 17, 2018

Chapter 1: MySQL 架构

1.1 逻辑架构

最上层是客户端，中间是核心服务功能（查询解析、分析、优化、缓存、内置函数，存储过程、触发器、视图再这里实现），最下层是存储引擎。

  • 客户端连接会在服务器进程中拥有一个线程，查询只在这个单独的线程中运行。而服务器会缓存线程，不用显式创建/销毁
  • MySQL会解析查询并创建解析树，并对其优化（重写查询、更改读取顺序、选合适索引等）。可用hint优化器影响决策，也可以通过EXPLAIN查看服务优化的决策
  • SELECT语句查询前会检查缓存来加速查找

1.2 并发控制

  • 锁种类：S锁(Read), X锁(Write)。只有S与S是不互斥的，其它组合都互斥。

  • 锁粒度：表锁（粒度大，开销小，并发度低，如ALTER TABLE会上表锁），行锁（粒度小，开销大，并发度高，在某些特殊的存储引擎中实现）

1.3 事务

ACID：原子性，一致性（状态一致），隔离性（并发但感觉不到其它事务的存在），持久性。

用BEGIN/START TRANSACTION开启事务, COMMIT提交事务，ROLLBACK 回滚事务。

  • 隔离级别：MySQL定义了4种

    • READ UNCOMMIT：事务的修改，即使没提交，修改也对其它事务可见（即脏读）
    • READ COMMIT： 事务只能看到已提交事务的修改（解决脏读）。但是一个事务到提交前可能会出现2个相同的查询，不同的结果（即不可重复读）
    • REPEATABLE READ: 保证同个事务中相同查询结果是一致的（解决不可重复读）。但当别的事务在本事务范围内插入新记录，本事务会感知到这个记录的存在，即幻读。InnoDB通过MVCC解决了幻读问题（默认不开启）。（Case: 2个事务A和B，都没提交，A修改了行a，B去读a还是原来的值，因为a加了X锁，B无法加S锁所以去读了快照值；若B去修改a会阻塞，因为加不了X锁。若A提交了，B去改，条件可能会失效，条件是不会适配快照的数据）
    • SERIALIZABLE：强制事务串行执行，读和写都会对数据（每一行）加锁，解决幻读问题。
  • 死锁：InnoDB会检测死锁的循环依赖，抛出错误；以及实现死锁超时机制。死锁错误产生后，InnoDB将持有最好行级X锁的事务回滚。
  • 事务日志：采用追加方式，磁盘I/O是顺序的。日志采用Write-Ahead，写入并持久化日志后，数据可以慢慢写回磁盘，即使崩溃了也可以恢复（参考《数据库系统概念》第16章）
  • MySQL中的事务
    • 自动提交：AUTOCOMMIT默认开启，认为每个语句都是一个事务。而对于不支持事务的引擎（如MyISAM）修改它没任何影响，因为没有事务的概念。
    • 事务控制是由下层的存储引擎实现的，当使用非事务存储引擎，那么它不会回滚/恢复。
    • 2PL：InnoDB使用2PL，只在COMMIT或者ROLLBACK 后才释放掉所有的锁，且同时释放。
    • 显式锁定：SELECT ... LOCK IN SHARE MODE/FOR UPDATE显式加行级IS/IX锁（意向锁）。IS与IX之间是能相容的，但是IS不能与X容，IX不能与S和X相容。LOCK/UNLOCK TABLES 对表上锁，在服务层实现，和引擎无关，不能用于替代事务。

1.4 MVCC

MVCC是行级锁的一个变种，但很多情况下避免加锁操作，典型有乐观实现和悲观实现。

InnoDB实现：通过保存数据在某个时间点的快照。每行记录维护2个列——行创建时间T1，行过期/删除时间T2。此外每个事务都有一个自己的时间T（没开始一个新事务，T会递增）。

只支持RR和RC。

RR下：

  • SELECT: 满足下面2个条件即作为结果

    • 只找T1 <= T的数据行
    • T2要么没定义，要么T2 > T

  • INSERT: 插入新的一行，该行的T1 = T

  • DELETE: 删除一行，该行的T2 = T

  • UPDATE: 会插入一行新记录，其T1 = T；同时把原来行的T2 = T

大多数读不用加锁，读性能好。但是要额外的存储空间，做更多的行检查工作，以及额外的维护。

1.5 MySQL存储引擎

可用SHOW TABLE STATUS显示表的相关信息

  • InnoDB（默认）

    用来处理大量短期事务，且很少回滚。

    • 数据存储在表空间（一系列数据文件组成），每个表数据和索引放在单独文件中
    • 使用MVCC支持高并发，实现4个隔离标准，默认为RR
    • 使用间隙锁防止幻读（锁定查询的行且对索引间隙进行锁定，防止幻行插入）
    • 表基于聚簇索引建立（查询性能高），其辅助索引必须包含主键列，所以主键应尽可能小，否则索引会非常大
    • 存储格式平台独立
    • 内部优化：可预测读、自适应hash索引、插入缓冲区等等
    • 是事务型存储引擎，支持热备份（系统处于正常运转状态下的备份，不需要停止写入）

  • MyISAM

    引擎设计简单，数据以紧密格式存储，某些场景下性能很好。

    不支持事务特性，不支持行锁，是以前的默认存储引擎。

    • 表存储在2个文件：数据文件(.myd)，索引文件(.myi)
    • 加锁与并发：只支持表锁，读上S锁，写上X锁。不过查询同时可以插入新记录，即并发插入
    • 可手动修复，但不支持事务级别的恢复
    • 支持全文索引（InnoDB不支持），且可延迟更新索引键（DELAY_KEY_WRITE选项，极大提高写入性能，但是崩溃后索引会损坏，需要修复）
    • 可对表压缩，适合导入数据后不再修改的场景，其索引是只读的

  • Memory

    数据保存在内存中，性能更好，但是重启后数据会丢失（结构不会丢失）

    • 只支持表锁
    • 支持Hash索引
    • 适用于查找/映射表，缓存周期性聚合数据，保存中间数据

1.6 选用合适的引擎

从这些因素考虑：事务，备份 ，崩溃恢复，特有特性

1.7 转换存储引擎

  • ALTER TABLE table_name ENGINE = engine_name;

  • mysqldump 导出，并修改CREATE TABLE 引擎选项，然后导入

  • CREATE&SELECT,综合使用前2种方法

Chapter 2：MySQL Benchmark

  • 策略：集成式、单组件式

  • 测试指标
    • 吞吐量：单位时间处理的事务数量
    • 响应时间/延迟：任务所需的整体时间（如平均、最小、最大和所占百分比）
    • 并发性：关注的是正在工作中的并发操作/同时工作的线程数或者连接数。测量并发性增加时，吞吐量是否变小，响应时间是否变长。
    • 可扩展性：给系统增加几倍工作量或者几倍资源，是否能获得相应几倍的结果，且其他性能在可接受范围内。
  • 测试工具
    • 集成式：ab, http_load, JMeter
    • 单组件：mysqlslap, MySQL Benchmark Suite, sysbench, Database Test Suite
  • 其它内容
    • OLTP：联机事务处理（On-Line Transaction Processing） 主要是执行基本日常的事务处理，比如数据库记录的增删查改。比如在银行的一笔交易记录，就是一个典型的事务。
      • 特点：
        1. 实时性要求高。我记得之前上大学的时候，银行异地汇款，要隔天才能到账，而现在是分分钟到账的节奏，说明现在银行的实时处理能力大大增强。
        2. 数据量不是很大，生产库上的数据量一般不会太大，而且会及时做相应的数据处理与转移。
        3. 交易一般是确定的，比如银行存取款的金额肯定是确定的，所以OLTP是对确定性的数据进行存取
        4. 高并发，并且要求满足ACID原则。比如两人同时操作一个银行卡账户，比如大型的购物网站秒杀活动时上万的QPS请求。
    • OLAP：联机分析处理（On-Line Analytical Processing） 是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。典型的应用就是复杂的动态的报表系统。（数据立方体）
      • 特点：
        1. 实时性要求不是很高，比如最常见的应用就是天级更新数据，然后出对应的数据报表。
        2. 数据量大，因为OLAP支持的是动态查询，所以用户也许要通过将很多数据的统计后才能得到想要知道的信息，例如时间序列分析等等，所以处理的数据量很大;
        3. OLAP系统的重点是通过数据提供决策支持，所以查询一般都是动态，自定义的。所以在OLAP中，维度的概念特别重要。一般会将用户所有关心的维度数据，存入对应数据平台。

Chapter 3 服务器性能剖析

3.1 服务器负载

  • 捕获查询日志
    • 可设置long_query_time 捕获超过该时长的查询（即慢查询日志，而通用日志很少用）
    • TCP抓包，然后根据MySQL通信协议进行解析（如tcpdump->pt-query-digest --type=tcpdump 解析）
  • 分析查询日志
    • 生成查询日志报告（如使用pt-query-digest，--explain 输出执行计划，--limit,`–outliers显示更多详细信息）

3.2 单条查询

  • SHOW PROFILE [FOR QUERY id]：默认禁用，可通过SET profiling = 1;打开。可以查看对应查询不同阶段的时间
  • SHOW STATUS：查看MySQL服务器的一些计数器（如Querie, Innodb_row_lock_time等），但无法提供基于时间的统计
  • EXPLAIN [SQL Statement]: 查看该SQL语句的执行计划。具体的参数可参考：https://www.jianshu.com/p/ea3fc71fdc45
  • 慢查询日志：在Percona Server中使用SHOW PROFILE
  • Performance Schema

3.3 捕获诊断数据

  • 使用诊断触发器（如pt-stalk）

  • 使用收集诊断数据工具（包含系统状态，CPU利用率，磁盘使用率和可用空间，ps输出采样，内存利用率），也可以从MySQL中获得信息，如SHOW STATUS,SHOW PROCESSLIST, SHOW INNODB STATUS

3.4 其它剖析工具

  • USER_STATISTIC 表
  • strace, oprofile

Chapter 4 Schema与数据类型

4.1 数据类型优化原则

选择更小的，选择简单的，避免NULL

  • 整数
    • 包含TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT，分别使用8,16,24,32,64位存储空间
    • 可配置UNSIGNED属性
    • 可指定整数类型的宽度，如INT(11)，但是对存储和计算而言无意义
    • 整数计算一般使用BITINT，即使是32-bit环境
  • 实数
    • 非精确（浮点）类型：FLOAT,DOUBLE ；精确类型: DECIMAL
    • 浮点类型使用DOUBLE计算
    • 精确类型，CPU不支持DECIMAL直接运算，会更慢
    • 支持指定精度，会影响列空间消耗，如DECIMAL(18,9)意为18位定点精度，9位小数位数
  • 字符串

    • VARCHAR与CHAR(类似的有二进制的BINARY和VARBINARY)

      • VARCHAR: 变长字符串，比定长更省空间。前1~2字节用于记录长度。行尾空格会保留。InnoDB下过长字符串会被保存为BLOB

        虽然省空间，但是当UPDATE时需要做额外动作，如MyISAM拆成不同片段，InnoDB分裂页。所以当字符串最大长度比平均长度长很多，且列很少更新时适用。

        给VARCHAR分配过大长度是不明智的

      • CHAR: 定长字符串。行尾空格不保留。

        适合存短字符串，或者长度方差不大（如适合存储MD5值）。更新多的情况CHAR比VARCHAR 好，因为碎片更少。

    • BLOB与TEXT

      这两种类型都是为了存储大数据而设计的字符串数据类型，分别用二进制（无排序规则或字符集）和字符（有排序规则和字符集）方式存储。

      • TYNYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB为一个家族，BLOB == SMALLBLOB
      • TYNYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT为一个家族，TEXT == SMALLTEXT

      MySQL把它们当做一个独立对象处理。当它过大时，InnoDB会用专门的外部存储区域来存储它们，每个值需要在行内存储1~4个指针引用它们。

      排序和索引时也不是对全部长度进行操作，而只是取前面的一小部分。

      所以避免使用它们，若要使用，则尽量使用SUBSTRING(column, length)转成字符串，就可以使用内存临时表，否则使用磁盘临时表会很耗性能（可在EXPLAIN执行计划的Extra列看到Using temporary,说明使用了隐式临时表）。

    • ENUM

      枚举把不重复的字符串存储为一个预定义的集合，而在列中就是一个整数，并保存一个整数到字符串的映射。因此，它的存储很紧凑，表的大小会更小，性能较好。

      枚举的排序是默认按照对应的数字排序的。可利用FIELD()函数显式定义排序规则，但是导致无法用索引消除排序。

      作JOIN运算时，都为ENUM会很快，但ENUM和VARCHAR/CHAR一起会慢很多。

  • 日期与时间

    • DATETIME: 范围从1001~9999年，精度为秒，格式为YYYYMMDDHHMMSS，与时区无关，长度8字节。默认可排序的字符串格式为YYYY-MM-DD HH:MM:SS,即ANSI标准定义的日期和时间表示方法

    • TIMESTAMP:时间戳，同UNIX时间戳，有时区影响，长度4字节，范围比DATETIME小的多

    除了特殊行为外，通常用TIMESTAMP, 因为空间效率更高。

  • 位数据

    • BIT

      存储位，BIT(n)表示存储n个位，最长64。在MyISAM中所有的BIT都打包存储，而InnoDB对每个列使用最小整数类型存放，所以不能节省空间。

      BIT被当做字符类型而非数字类型，检索时是当成位字符串的。（而在数字上下文中会转成数字，需要注意）

    • SET

      内部是一系列打包的位的集合表示，有效利用存储空间。也有FIND_IN_SET()和FIELD() 等函数方便查询，但是定义代价高，且修改需要ALTER TABLE, 不能在SET上通过索引查找。

      可用INT等整数类型替代，然后进行位操作。

  • 标识符(Identifier)

    • 整数类型是最好的选择，速度快且可用AUTO_INCREMENT
    • ENUM和SER 不适合，它们适合存储固定的状态信息
    • 字符串不适合，很耗空间且比数字慢
      • 若存储UUID，应去除-符号，或者用UNHEX()函数转成16字节的数字存在BINARY(16)中。检索时用HEX()格式化为16进制格式
      • IPv4地址应该用无符号整数存储，而非字符串。MySQL提供INET_ATON()和INET_NTOA() 函数进行转换

4.2 Schema设计

MySQL中需要避免：

  • 太多的列
  • 太多的关联（MySQL限制为最多61个，单个查询最好在12个表以内关联）
  • 全能（枚举量过多）和变相（不必要的）的枚举
  • Not Invent Here的NULL。虽然避免用空值有很多好处，但是不能走极端，有时候使用NULL会让代码处理更加简单

4.3 范式与反范式

  • 一个表R可有多个超码，最小的超码是候选码，候选码有多个。主键是候选码的其中一个。

  • 范式
    • 1NF：一个表R的所有列都是原子的（不可分的）
    • 2NF：
    • BCNF：一个表R的所有函数依赖满足下列条件之一
      • α -> β是平凡的
      • α是R的一个超码
    • 3NF：一个表R的所有函数依赖蛮子下列条件之一
      • α -> β是平凡的
      • α是R的一个超码
      • β - α的每个属性A都包含于R的其中一个候选码（候选码可有多个，可包含于不同的候选码中）
  • 范式的优缺点
    • 优点
      • 冗余小，表更小
      • 执行更新（写操作）快
      • 由于表小，可以更好地放入内存中，所以执行操作更快
      • 需要更少的DISTINCT或者GROUP BY,查询代码更加简单
    • 缺点
      • 会有更多的关联，而这个运算代价很昂贵，且可能会让一些索引策略无效化
  • 反范式的优缺点
    • 优点
      • 用冗余避免了过多的关联，而在最坏情况下（全表扫描），相比，使用关联会带来随机I/O，性能更差（相比顺序I/O）
      • 更有效使用索引策略
    • 缺点
      • 数据冗余问题
      • 没有了范式化的优点

4.4 缓存表与汇总表

用缓存表和汇总表保存衍生的冗余数据，在容许少量不精确数据的情况下，可以提升性能（减少耗费大的实时计算）

  • 缓存表与汇总表

    • 缓存表：存储那些可以比较简单从其他表获取的数据的表（但获取较慢的，是逻辑上冗余的数据）
    • 汇总表：存储聚合数据的表（数据不是逻辑上冗余的）
    • 影子表：指一张真实表背后创建的表，在表重建的时候有用。如果要重建table1，那么可以先创建table1_phantom,然后往影子表中填充好数据，填好了和真实表作切换，这样在重建的时候，原表依然可用

  • 物化视图

    • 视图：视图概念上包含查询结果，但不预先计算和存储，在使用这个视图的时候才会去计算。可以看成对用户可见的“虚关系”，可被用于查询。在查询语句使用视图时，视图会被它存储的查询表达式代替。

      1
2

      CREATE VIEW view_name(column_name, ...) AS
[SELECT clause]

    • 物化视图：视图原本不存储计算的结果，物化视图允许数据库存储视图关系的内容（即计算结果）。若用于定义视图的实际的表发生改变，该视图（的内容）也会被更新。

    MySQL不原生支持物化视图。但是开源工具Flexviews实现了物化视图。

  • 计数器表

    计数器表可以用于计数，小而快，并可以帮助避免查询缓存失效。

    一些设计技巧：

    • 避免将表设成1行（因为记录上有全局互斥锁，并发度不高），可设成多行，然后随机选择行数更新，统计的时候使用聚合函数查询
    • 可写一个周期执行任务，周期性地合并结果到某一行中，这样可以避免表变得太大

设计高性能数据库时，一种技巧是：更快地读，更慢地写。在不显著减少写性能的前提下，尽量提高读性能。

4.5 加速ALTER TABLE

MySQL执行大部分ALTER TABLE操作是：创建新空表 -> 把旧表数据插入新表 -> 删除旧表，常导致服务中断。

常用的一些技巧：

  • 在不提供服务的机器上ALTER TABLE, 然后和提供服务的主库进行切换
  • 影子拷贝

一些操作不会引起表重建，如

只修改.frm文件：

  • 修改列默认值（ALTER TABLE table ALTER COLUMN column ...）
  • 移除一个列的AUTO_INCREMENT属性
  • 修改ENUM和SET常量

步骤常为：

  • 创建一张相同空表，进行所需的更改
  • 执行FLUSH TABLES WITH READ LOCK来关闭所有的表，并禁止任何表读取
  • 交换.frm文件
  • 执行UNLOCK TABLES释放第2步的锁

Chapter 5 索引

5.1 索引类型

CATALOG
    FEATURED TAGS
    Algorithm Java Network Distributed System Stream Processing Operating System Paper Linux Redis etcd Kubernetes C++