MySQL设计与优化

前言

• 怎么设计优雅的表结构？指导原则是什么？
• 索引为什么那么快？底层为什么要用B+树？
• 怎么设计好的索引? 怎么优化索引？
• 常用系统参数代表什么意思？怎么优化参数？
• mysql优化手段有哪些？

目录

基本概念

mysql概述

innodb引擎架构

mysql设计

mysql优化

总结

一. 基本概念

1. 关系模型

• 一对一
• 一对多
• 多对多

2. 关系型数据库

依赖关系模型创建的数据库，用一个二维表格及其关系组成的数据组织，最大的特点是事务的一致性

3. 非关系型数据库

基于非关系模型的数据库，非关系模型包括

• 列模型：Hbase
• 键值对模型：redis
• 文档型模型：mongodb（聚合型数据库）

4. 关系VS非关系

5. 冗余

同一信息的重复储存，叫做冗余

• 低级冗余：字段的重复
• 高级冗余：字段的派生：比如总额=单价*数量

形成原因

• 表重复
• 属性重复
• 元组重复

冗余的坏处

• 为了保证数据一致性，要维护冗余字段的成本高
• 可能导致数据不一致

6. 范式

作用：消除或减少冗余，增进数据一致性。设计出高效优雅的数据库

分类：

• 第一范式（1NF）：要求属性不可分，具有原子性。下图的属性被分开来，关系型数据库设计不出来这种表

• 第二范式（2NF）：要求记录具有唯一性
• 第三范式（3NF）：要求字段不能有冗余，任何字段不能由其他字段派生
• BC范式（BCNF）：主属性不依赖于主属性
• 第四范式（4NF）：要求把同一表内的多对多关系删除
• 第五范式（5NF）：从最终结构建立原始结构

最佳实践（中庸版）

• 一般，一个数据库设计符合3NF或BCNF就可以了
• 过于范式化甚至会对数据库的逻辑可读性和使用效率起到阻碍
• 适当增加冗余，达到以空间换时间的目的

最最佳实践（实践版）

• 除非你真的有足够证据证明按照规范范式设计数据库会有性能问题而且这个性能问题无法解决，或者有足够证据证明你写入的数据是永远不会被修改的，否则不要轻易用性能作为借口反范式设计。
• 数据库对于表连接的处理能力其实非常强大，关联几个十几个表，只要数据库结构设计合理，其实是非常轻松的事情。
• 在数据量没达到十万级别的时候，冗余根本没必要。如果数据量和并发数都上来后，会在前面加一个ETL层，其中有Join好的AB（可以是数据库，也可以是别的缓存层）。ETL层和数据库层之间用MQ打通数据同步机制

7. 事务

1. 概念：

指对系统进行的一个逻辑单元，包括一组操作。会把数据库从一种一致状态切换为另一种一致状态。普通文件系统是没有此特性的。

2. 事务需具备的特性（ACID）

• 原子性（Atomic）：要么完全执行，要么完全不执行，允许回滚
• 一致性（Consistency）：事务开始和结束的中间状态不能被其他事务看到
• 隔离性（Isolation）：并发事务直接的影响程度，比如一个事务会不会读到另一个未提交的事务修改的数据
• 持久性（Durability）：事务提交后就保证不会丢失

3. 事务并发可能出现的问题

• 脏读：事务A修改了数据，但是未提交，事务B读到了事务A未提交的更新结果，A提交失败，B就读到脏数据
• 不可重复读：事务B在事务A提交前读到的结果，和提交后读到的结果可能不同。比如，事务B在事务A提交前读到的结果，和提交后读到的结果可能不同。不可重复读出现的原因就是事务并发修改记录
• 幻读：在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。幻读是由于并发事务增加记录导致的

4. 事务的隔离级别（由低到高）

• RR（read uncommitted）：最低的隔离级别，什么都不需要做。所有的并发事务问题都会发生。
• RC(read committed)：只有在事务提交后，其更新结果才会被其他事务看见。可以解决脏读问题。
• RR(repeated read)：同一事务中，对同一份数据的读取结果总是相同的。可以解决脏读、不可重复读。mysql默认级别，在此基础上做了优化
• Serialization：串行化。隔离级别最高，牺牲并发性。可以解决并发事务的所有问题

5. 各种数据库对事务的支持情况

事务的定义极其严格，必须同时满足四个特性，但是数据库厂商处于各种目的，比如性能，并没有严格满足ACID的要求

• mysql的NDB cluster引擎，不满足D（持久性）
• Oracle数据库默认隔离级别为RC，不满足I（隔离性）
• mysql的InnoDB引擎，完全遵守ACID特性

二. MySQL概述

1. 特点

• 关系型数据库
• 插件式存储引擎

2. 架构

2.1 特点

单进程多线程模型

2.2 存储引擎

• InnoDB
  • 支持事务
  • 实现sql标准的4种隔离级别，默认为RR
  • 支持行锁
  • mysql5.5.8之后默认的存储引擎（windows除外）
• MyISAM：
  • 不支持事务
  • 支持全文索引
  • 只缓存索引文件，不缓存数据文件
  • mysql5.5.8之前默认的存储引擎
• NDB
  • 集群存储引擎
• Archive
  • 支持高比例压缩存储
• Memory（heap）
  • 数据全部放在内存中

  最新的mysql版本（8.0.12）支持的存储引擎

mysql->show engines; //查看所有支持的存储引擎和默认存储引擎

三. InnoDB引擎架构

1. 架构和逻辑存储结构

• 内存池的职责
  • 维护所有进程和线程需要访问的数据结构
  • 缓存磁盘数据
  • redo log缓冲
• 后台线程的职责
  • 负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据
  • 负责将已修改的数据文件刷新到磁盘文件
  • 保证数据库异常时能恢复到正常运行状态

2. 后台线程

• master thread：核心后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据一致性
• io thread：负责io请求的处理
• purge thread：回收事务提交后已经使用的undo页
• page clean thread: 负责脏页的刷新

3. 缓存池

概述：一块内存区域，按页存放，每页默认16k，通过checkpoint机制刷新会磁盘，包含多种页类型

缓存的页类型：

• 索引页
• 数据页
• undo页
• 锁信息
• ...

3. 缓存如何管理

• LRU list：改进的缓存数据列表
• free List: 空闲列表
• flush list：脏页列表

4. 事务

4.1 InnoDB引擎对事务的支持

• 事务的隔离性：默认隔离级别为RR，并使用Next-Key-Lock锁来避免幻读的产生
• 事务的原子性和持久性：redo log（重做日志）来保证
• 事务的一致性：undo log来保证（回滚操作）

   mysql->select @@tx_isolation\G; //查看隔离级别
   mysql->select @@global.tx_isolation|G; //查看全局的事务隔离级别

4.2 redo

• 在事务提交时，记录事务的行为到文件系统缓冲
• 之后调用fsync（刷新频率可调），将重做日志写入磁盘

4.3 undo

• 事务的回滚操作，将数据回滚到修改之前的样子
• 存放在数据库内部的特殊段中（segment），称为undo segment
• 恢复的是逻辑日志，不是物理日志：因为一页里面可能有很多并发的事务，不能回滚整个页，影响别的事务
• unduo还实现了MVCC：当用户读取一行时，若该记录被其他事务占用，当前事务科通过undo读取之前的行版本信息，实现非锁定读
• undo log的同事会产生redo log

4.4 redo VS binlog

4.5 事务的提交

1. 显示提交

• begin/start transaction：开启事务，start是存储过程专用的
• commit：提交
• rollback：回滚
• savepoint：创建事务保存点
• settransaction 手动设置隔离级别

2. 隐式提交

以下语句提交有隐式的commit操作

• 修改或删除相关：alter, create, drop, rename，set password，add user，grant...
• 管理相关：analyze, cache, check, load index, optimize, repair

4.6 innodb支持的锁

• 共享锁（S Lock）:允许多个事务读一行数据
• 排它锁（X Lock）:允许一个事务删除或更新一行数据
• 行锁和表锁

5. 逻辑存储结构

5.1 架构图

• 所有数据放在tablespace中
• tablespace由segment（段），extent（区），page（页，block）组成
• innodb引擎默认有一个共享表空间ibdata1
• segment包括数据段（Leaf node segment），索引段(Non-Lefa)，回滚段(Rollback)等
• 区由连续的页组成，每个区大小任何时候都为1MB，1MB=64页*每页16KB，每页大小可调整（需为2的倍数）
• 页是Innodb管理的最小单元
• 数据按行存放，每页最多允许16kb/2 -200 = 7992行记录
• 行的存储格式有：Compact（默认），Redundant（兼容旧的格式） 页结构

查看本机行存储格式

Compact格式

5.2 文件结构

• .ibd 表结构文件
• .frm 表数据文件

四. 数据库设计

1. 表关系设计

• 1对1：在任意一张表中添加外建指向另一张表的主键
• 1对多：“多”中添加一个外键，指向“1”的主键
• 多对多：添加一张关系表，两个外建分别指向两张表的主键

2. 表字段设计

2.1 表字段说明 参考

• 数值类型的选择
  • tinyint(num) : 长度只是一个最大显示宽度，宽度不足前面补0，跟数据存储范围无关
  • char（num）: num是字符的最大长度，不是字节。
  • 范围是怎么算出来的：1byte=8bit，1bit有0和1两种可能，所以1byte可表示无符号2^8=256个数字，但是如果有符号，需要1bit存储符号，所以只能存储正负各2^7=128个数字，0包含在正数范围内，所以正数最大是127

  类型 大小 范围（有符号） 范围（无符号） 用途 tinyint 1字节 (-128, 127) (0, 255) 小整数值 smallint 2字节 (-32768, 32767) (0, 65535) 大整数值 mediumint 3字节 (-2^(3x7), 2^(3x7) -1 ) (0, 2^(3*8) - 1) 大整数值 int/integer 4字节 .. .. 大整数值 bigint 8字节 .. .. 极大整数值 float 4字节 .. .. 单精度浮点数 double 8字节 .. .. 双精度浮点数 decimal decimal(M,D),若M>D，为M+2,否则D+2 小数

• 字符串的选择 类型 大小 用途 char 0-255字节 定长字符串 varchar 0-255字节 变长字符串 tinyblob 0-255字节 不超过255个字符的二进制字符串 tinytext 0-255字节 短文本字符串 blob 0-65535字节 二进制长文本数据 text 0-65535字节 长文本数据 mediumblob 16M 中等长二进制文本数据 mediumtext 16M 中等长文本数据 longblob 4G 极大的二进制文本数据 longtext 4G 极大文本数据
• 时间类型的选择 类型 大小 范围 格式 用途 date 3字节 1000-01-01 9999-12-31 YYYY-MM-DD 日期值 time 3字节 '-838:59:59' '838:59:59' HH:MM:SS 时间值 year 1字节 1901 2155 YYYY 年分值 timestamp 4字节 1970-01-01 2037年 YYYYMMDD HHMMSS 时间戳（空值或null会自动填充当前时间）,存储数会随时区变化而变化 datetime 8字节 1000-01-01 00:00:00 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 日期和时间,存储数不会随时区变化而变化
• 复合类型（技术上都是字符串类型） 类型 说明 enum 只允许从一个集合中取得一个值 set 允许从一个集合中取得任意多个值

2.2 表字段设计原则参考

• 主键一般使用自增长字段
• 字段选择合理范围内最小的，大大减少磁盘IO读写开销，内存和cpu占用率
• 选择相对简单的数据类型
• 不要使用NULL。因为MYSQL对NULL字段索引优化不佳，增加更多的计算难度，同时在保存与处理NULL类形时，也会做更多的工作，所以从效率上来说，不建议用过多的NULL。有些值他确实有可能没有值，怎么办呢？解决方法是数值弄用整数0，字符串用空来定义默认值即可
• 在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。但如果确切知道字符串长度,比如就在50~55之间,那就用 CHAR 因为 CHAR 类型由于本身定长的特性使其性能要高于 VARCHAR。如uuid，MD5
• 复合类型我们一般用tinyint,更快的时间更省的空间以及更容易扩展

3. 索引设计

3.1 什么是索引

• 存储引擎用于加快查找速度（排好序）的一种数据结构
• 索引会被存储到磁盘上

3.2 索引优点

• 能轻易将查询性能提升几个数量级
• 唯一索引保证数据唯一性
• 减少分组和排序时间

3.3 索引缺点

• 占用磁盘空间，大量索引可能导致比文件还大
• 损耗性能，增删改查都要维护索引

3.4 索引数据结构

B+树的特点

• 多叉树，高度较低
• 每个节点可存储多个key
• 非叶子节点存储key，叶子节点存储key和data
• 叶子节点两两相连

为什么是B+树？

普通平衡树的缺点

• 数据量不大时，普通平衡树（AVL树，红黑树）性能极好。但是数据量巨大时，内存不够用，无法将数据全部加载到内存中，只能放到磁盘
• 树的高度为LogN，导致磁盘IO次数过多影响效率
• 调整树的平衡是通过旋转实现，如果不把全部数据加载进内存是无法完成旋转的

B-树的缺点

• 非叶子节点也存储数据，每次磁盘io数据量是固定的，每一层索引范围小
• 数据分散在每个节点中，不支持范围查询

B+树的特有性质

• 非叶子节点只存储key，每一层能索引的数据更多。每次io能看到更多数据
• 树高度低（一般为3层左右），io次数少
• 叶子节点两两相连，符合磁盘预读特性，减少io次数
• 范围查询支持良好。真正数据只存储在叶子节点，范围查询只需遍历叶子节点
• 每个节点的大小设置为磁盘IO一次的大小（称为页，根据操作系统不同而定，如16k）

3.5 索引设计原则

• 索引并不是越多越好，过多索引不仅增加磁盘空间，而且更新插入数据都要动态维护索引，影响效率
• 经常作为where条件字段需要建立索引
• 数据量很少的表不要建索引，全表查询效率比遍历索引可能还快
• 将使用频率高，区分度大的列放在索引前面。范围查询或不等于查询的列放在最后
• 不同值较多的列上建立索引，在不同值较少的列上不要建立索引，比如性别字段只有男和女，就没必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度
• 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度
• 在频繁排序或分组（即group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引
• 没有必要为同一字段建立重叠索引
• 选择较短的数据类型，可以有效的减少索引的磁盘占用，提高索引的缓存效果
• join多个表时，为join的字段建立索引，mysql内部会优化sql语句。且join的字段类型必须是相同的，字符串的字符集也必须相同

五. 数据库优化

1. 索引优化

1.1 索引使用原则

• 查询语句中必须使用独立的列，不能含有表达式，否则不走索引
• 符合索引由左到右生效，遇到范围查询就不走索引 index(a, b, c) 查询语句 索引是否生效 where a=3 是，使用了a where a=3 and b=5 是，使用了a, b where c=4 and a=3 and b=5 是，使用了a,b,c.与查询顺序无关 where b=3 否 where a=3 and c=4 是，只使用了a where a>4 and b=5 and c=3 是，只使用了a
• 数据类型出现隐式转换不会走索引

1.2 索引分析

explain分析

select_type： 查询类型,性能由高到低

• simple 此查询不包含union或子查询（最常见）
• primary 最外层查询
• union union的第一个以外的查询
• subquery 子查询的第一个select
• derived 派生表的select（from字句的子查询）

table：查询涉及的表名（别名）

type：判断是全表扫描还是索引扫描（很重要的字段）

• const/system 根据主键或者唯一索引查询到，只读取一次，速度非常快
• eq_ref 等值引用。多表join，前表的每个结果，只能匹配到后表的一行结果，比较通常是=，查询效率较高
• ref 多表join，非唯一索引，或者使用了最左前缀规则索引的查询
• range 使用索引范围查询，此类型下ref字段为NULL
• index 全索引扫描，比all稍快
• all 全表扫描，查询性能最差，对数据库是灾难

possible_keys：查询可能使用的索引,设置过多的索引会影响性能

key：查询真正使用的索引，一般一个查询只使用一个索引

ken_len：表示where查询使用了索引的字节数(不包括order by/group by，也不是索引本身的长度)

该字段可以评估组合索引是被完全使用，还是只有最左部分被使用，越小表示索引占用磁盘空间越小，索引更高效

• 字符串
  • char(n): n字节长度
  • varchar(n): 如果是utf8编码，则是3n+2, 如果是utf8mb4编码，则是4n+2
• 数值类型
  • tinyint: 1字节
  • smallint：2字节
  • mediumint：3字节
  • int：4字节
  • bigint：8字节
• 时间类型
  • date：3字节
  • timestamp：4字节
  • datetime：8字节
• 字段属性
  • NULL：多加1字节
  • NOT NULL： 不用多加1字节

ref：使用哪个列或常熟与key一起从表中选择行

rows：扫描的行数（重要）

Extra：执行情况和描述

• Using index 表示在索引树种就可以查找到所需数据，不用扫描数据文件，说明性能不错
• Using where 使用了where条件限制哪些行匹配下一张表
• Using temporary 使用临时表存储结果，通常发生在对不同的列进行order by，效率不高，需优化
• Using Filesort 需要额外的排序操作，不能通过索引顺序排序。查询时cpu资源消耗大，需优化

2. 参数优化

2.1 参数说明

• innodb_buffer_size 缓冲区大小
• innodb_buffer_pool_instance 缓冲池实例个数
• innodb_old_blocks_pct 读取的页放入缓冲区LRU的位置，默认37%
• innodb_old_blocks_time 读取的页等待多久才放入LRU
• innodb_log_buffer_size undo日志缓冲区大小，默认8M
• innodb_page_size 每一页的大小
• max_connections 最大连接数
• key_buffer_size
• innodb_thread_concurrency 最大并发线程数
• thread_cache_size 缓存的最大线程数
• tmp_table_size 超过该值的用硬盘临时表，低于改值的直接放内存
• query_cache_limit 超过此大小的查询将不缓存
• query_cache_min_res_unit 缓存块的最小大小
• query_cache_size 查询缓存大小
• innodb_log_buffer_size 日志缓冲大小
• slow_query_log = ON 开启慢查询
• long_query_time = 3 超过3s的为慢查询
• innodb_flush_log_at_trx_commit重做日志从缓冲刷新到磁盘的策略：0表示不记录redo日志

3. 主从优化

3.1 概念

通过配置主库和从库，主库负责读取删改，从库负责只读，做到读写分离，并根据读写要求的不同配置不同的系统参数

3.2 数据库主从原理

• 主库打开binlog配置，对主库每次操作都会记录在binlog中
• 从库通过io线程从主库读取binlog，传输到从库
• 从库sql线程读取binlog，并应用到从库

3.3 主从配置（确保版本一致）

• 主从服务器分别添加binlog配置
• 重启服务
• 查看主库当前记录的日志位置
• 从库配置从主库读取到的位置，并开启同步

3.4 使用xtrabackup备份数据

该工具可在不停服的情况下，实现数据同步。

4. 分库分表

1. 垂直拆分

1.1 概念：列拆分，把列比较多的表拆分为多张表

1.2 原则：

• 把不常用的字段单独放在一张表
• 把text，blob等大字段拆分出来放在附表中
• 经常组合查询的列放在一张表中

2. 水平拆分

2.1 概念：行拆分，将一张表的数据拆分为多张表存放

2.2 原则：

• 通常根据id取模存放数据
• 部分业务逻辑可言通过地区，年份等字段归档拆分（界面上限定住不让跨年查询）

2.3 难点及问题：乐视网分库分表

• 扩展表时，旧的数据将全部失效，无法再扩展
• 跨节点join， order by group by的问题，分布式事务等
• 如何保证id的唯一性

2.4 常用的开源组件

• mycat
• sharding-sphere
• tsharing

总结

1. 本文内容串起来如下：

• 介绍了关关系型数据库和非关系型数据库
• 知道关系型数据库最重要的特性是事务的一致性，然后介绍了事务的相关特性
• 如何保证数据一致性：mysql底层做到RR级别事务隔离
• 用户设计数据库时如何提高一致性：遵守范式，以减少冗余
• 数据库查询效率怎么提升：设计好的表结构和索引
• 数据库查询慢的时候怎么优化：介绍了几种优化手段

2. 和前言中我们提到的几个问题，简短总结一下

怎么设计优雅的表结构？指导原则是什么？

大的前提是遵守范式以减少冗余，其次才综合业务量设置冗余。合理选择字段和建立索引

索引为什么那么快？底层为什么要用B+树？

索引是排好序的数据结构，B+树是为了适应磁盘数据的存储设计的结构，包括多叉结构降低高度较少io次数，非叶节点不存数据增加索引量，叶节点相连便于范围查询，节点大小设置为页大小充分利用io调用。

怎么设计好的索引? 怎么优化索引？

根据查询条件设置合适的组合索引，时常用explain分析并调整索引

常用系统参数代表什么意思？怎么优化参数？

略

mysql优化手段有哪些？

索引优化，参数优化，主从优化，分库分表等等

参考文献

• 《mysql技术内幕（innodb存储引擎）》
• 《高性能mysql》