MySQL

wzl
124 阅读11分钟

数据库三范式

第一范式

  1. 数据库所有字段只有单一属性
  2. 单一属性都是由基本数据类型构成
  3. 数据库的表都是二维的（行与列）

第二范式

  1. 要求符合第一范式
  2. 表必须有一个主键
  3. 其他字段可由主键确定
  4. 二范式的目的是通过拆表解决数据冗余的问题

第三范式

  1. 要求符合第二范式
  2. 字段要求直接依赖于主键，不允许间接依赖
  3. 第三范式目的在于拆分实体

数字类型

整数类型

列类型存储空间取值范围
SIGNEDUNSIGNED
tinyint1字节-128~1270~255
smallint2字节-32768~327670~65535
mediumint3字节-8388608~83886070~1677215
int4字节-2147483648~21474836470~4294967295
bigint8字节-9223372036854775808~92233720368547758070~18446744073709551615

实数类型

列类型存储空间是否精确
float4字节否
double8字节否
decimal每4个字节存9个数字，小数点占一个字节是

字符类型

VARCHAR 类型

  1. 使用最小的符合需求的长度
  2. VARCHAR(255) 会有额外空间占用，保存的是数据的实际长度，具体原因：行格式
  3. VARCHAR(5) 与 VARCHAR(255) 虽然保存字节的额外长度相同，但是占用内存不同，所以要尽量满足 1
  4. 在 MySQL 5.6 之前，VARCHAR 变更长度时会出现锁表，在 5.6 以上，因为 Online DDL 的出现，进行了优化，但是在长度由 255 或者 255 以下变为 255 以上，还是会出现锁表

VARCHAR 适合的使用场景

  1. VARCHA 适合存储长度波动大的数据（博客文章等）
  2. 字符串很少被更新的场景

CHAR 类型

  1. 定长类型
  2. CHAR 最大长度 255
  3. CHAR 类型的数据会自动删除末尾的空格
  4. CHAR 检索效率比 VARCHAR 高

CHAR 适用的使用场景

  1. CHAR 适合存储长度波动不大的数据，如 MD5 摘要
  2. CHAR 适合存储段字符串
  3. CHAR 类型适合存储经常更新的字符串

日期类型

DATETIME 类型

  • DATETIME 占用 8 字节
  • 与时区无关
  • 可保存到毫秒
  • 可保存范围大

TIMESTAMP 类型

  • 占用 4 个字节
  • 时间范围 1970-01-01 到 2038-01-19
  • 采用整形存储
  • 依赖于时区，随着 MySQL 的时区设置而变化
  • 可以自动更新 timestamp 的值

存储引擎

  1. InndDB
  2. MyISAM
  3. XtraDB
  4. CSV
  5. Memory
  6. Archive
  7. Federated
  8. .....

InnoDB

  • MySQL 5.5.8 开始默认存储引擎
  • 支持事务处理，支持 MVCC
  • 采用“表空间”保存文件
    • 使用系统表空间 ibdataN（ibdata1、ibdata2 .....）
      • 不利于管理
      • 会产生 IO 瓶颈
      • 存储空间很难回收
    • MySQL 5.6 以后使用独立表空间：tablename.ibd
      • 使用 optimize table 命令回收存储空间
      • 设置 innodb_file_per_table 决定表空间模式
  • 5.7 以后支持全文索引，支持了空间类型

MyISAM

  • 不支持事务
  • 支持全文索引
  • 支持数据压缩
  • 紧密存储，顺序读性能很好
  • 表级锁，并发性差

应用场景

  • 非事务应用，例如：保存日志
  • 只读类应用，报表数据，字典数据
  • 空间类应用，开发 GIS 系统（5.7 版本之前只要 MyISAM 引擎支持）
  • 系统临时表：子查询、分组统计超过一定内存就会使用 MyISAM

Memory

  • 不支持事务
  • 内存读写，临时存储
  • 超高的读写效率
  • 表级锁，并发性差

应用场景

  • 读多写少的静态数据
  • 需要额外创建一张表用来持久化
  • 充当缓存使用，保存高频访问静态数据
  • 系统临时表，不超过一定内存时，使用 Memory 引擎表做临时表

关键参数

  • max_heap_table_size 设置用户创建的 Memory 表允许增长的最大大小，用于计算内存表的 MAX_ROWS 值，和 tmp_table_size 一起使用以限制内存表的大小
  • tmp_table_size 设置内存临时表最大值，超过这个值，系统临时表就会使用 MyISAM 引擎

CSV

  • 纯文本保存
  • 不支持事务
  • 不支持索引

应用场景

  • 数据交互/数据迁移
  • 不依赖 MySQL 环境

锁

按职责分类

  • 共享锁 - 读锁
  • 独占锁（排它锁）- 写锁

按粒度分类

  • 行锁：在根据索引进行操作数据才会使用行锁
  • 表锁

索引

  • 索引就是为表建立的目录，把随机 IO 变成顺序 IO，大幅提高了数据的检索效率
  • 索引的存储形式由存储引擎决定的

索引分类

  • 从存储结构上划分
    • BTree 索引（B+Tree 或 B-Tree 索引）：适用于范围查找、顺序查找
    • Hash 索引：适用于精准匹配，只有 Memory 引擎支持显示创建，会自动生成 HashCode，存入缓存
    • Full-index 索引
    • R-Tree 索引：通常用在 GIS 系统（地理信息系统）
  • 从应用层次来分
    • 普通索引
    • 唯一索引
    • 复合索引
  • 根据数据的物理顺序与键值的索引顺序关系
    • 聚簇索引：将数据与索引放到了一块，例如：InnoDB 的主键索引
    • 非聚簇索引：数据与索引分开存储

什么情况不会用到索引

  • 索引选择性太差
  • where 子句不满足最左匹配原则
  • 对索引列的显示和隐式函数

Explain

MySQL 优化

可使用的分析方式

  • pt-duplicate-key-checker 是 percona-toolkit 工具包中的实用组件
    • 它可以帮助你检测表中重复的索引或者主键
    • 使用教程
  • 查看所有的索引执行使用情况
SELECT
  object_type,
  object_schema,
  object_name,
  index_name,
  count_read,
  count_fetch,
  count_insert,
  count_update,
  count_delete FROM performance_schema.table_io_waits_summary_by_index_usage 
ORDER BY
	sum_timer_wait DESC;

  优化器在优化 SQL 、选择索引时，会根据索引的统计信息和扫描行数去做一些判断，使用以下语句可以看索引的统计信息

show index from 表名

  但是这个统计信息并不准确，是通过采样统计得出的结果，InnoDb 会默认选择 N 个数据页，统计这些页面的不同值，然后乘以索引的页面数，就得到了索引的基数值，当变更的数据行超过指定的比例时，就会重新做一次索引统计，也可以使用下面的命令手动重新统计索引信息，但是会加 MDL 读锁，虽然 MySQL 5.6 引入了 Online DDL ，但是还是尽量要注意执行时机

analyze table 表名

  新增和删除可能会造成空洞，而 MySQL 也有能达到压缩表的目的的操作。
  可以使用下面的命令来重建表。在 MySQL 5.5 之前，MySQL 会自动完成创建一个空间紧凑的临时表、转换数据、交换表明、删除旧表的操作。

alter table A engine=InnoDB

optimize table 表名 = （alter table A engine=InnoDB）+ (analyze table 表名)

Explain

select type

  • simple：简单查询，不包含子查询和 union
  • primary：复杂查询中最外层的 select
  • derived：包含在 from 子句中的子查询，MySQL 会将结果存放在一个临时表中 ，也称为派生表
  • union：在 union 中的第二个随后的 select
  • union result：从 union 临时表检索结果的 select，union all 关键字不会出现
  • subquery： 包含在 select 中的子查询

partitions

表示查询作用在哪个分区表上

type

  • system：系统表，少量数据，往往不需要进行磁盘 IO
  • const：一般出现于主键和唯一索引筛选
  • eq_ref：一般出现于关联查询，primary key 或 unique key 索引的所有部分被连接使用
  • ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀
  • fulltext
  • ref_or_null
  • index_merge
  • unique_subquery
  • index_subquery
  • range
  • index
  • all

分区表

  分区表就是把大表按条件单独存储到不同的“物理小表”中，构建出完整“逻辑表”，分区只是一张表中的数据和索引的存储位置发生改变，分表则是将一张表分成多张表，是真实的有多套表的配套文件

分区表的优点

  • 更少的数据检索范围
  • 分区表的数据更容易维护
  • 分区表数据文件可以分布到不同的硬盘上，并发 IO
  • 减少锁的范围，避免大表锁表
  • 可独立备份，恢复分区数据

分区表的使用限制

  • 如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分，无法对非分区列创建唯一索引
  • 分区表必须是数字类型
  • 分区表不支持建立外键
  • 某些存储引擎不支持分区( MERGE、CSV、FEDERATED )
  • 如果查询不包含分区列，或者对分区列进行计算，那么会扫描所有分区
  • 一张表最多只能有 1024 个分区，达到上限分区肯定是支持不了的，就需要考虑垂直、水平拆分表

行格式

Compact 行格式

变长字段长度列表

  如果表中所有的列都不是可变长的数据类型的话，这一部分就没有。
  在 Compact 行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放。但要注意编码格式，不同的编码，一个字符占用的字节不同。
  假如一个表里 c1、c2、c3 字段都是 varchar(10)，而且是 ascil 字符集，所以每个字符只需要 1 字节来进行编码，那看下某一条记录各边长字段内容的长度：

列名存储内容内容长度（十进制）内容长度（十六进制）
c1aaaa40x04
c2bbb30x03
c3c10x01

  又因为这些长度值需要按照列的逆序排序，所以变长字段长度列表的字符串用十六进制表示的效果就是（各个字节之间实际没有空格）：01 03 04
  由于这一行记录中的字符串都比较短，用一个字节就可以表示，那么可变长列表需要的字节是 1+ 1 + 1 = 3，具体用1个还是2个字节来表示真实数据占用的字节数， InnoDB 有它的一套规则，我们首先声明一下 W 、 M 和 L 的意思：

  1. 假设某个字符集中表示一个字符最多需要使用的字节数为 W ，也就是使用 SHOW CHARSET 语句的结果中的 Maxlen 列，比方说 utf8 字符集中的 W 就是 3 ， gbk 字符集中的 W 就是 2 ， ascii 字符集中的 W 就是 1 。
  2. 对于变长类型 VARCHAR(M) 来说，这种类型表示能存储最多 M 个字符（注意是字符不是字节），所以这个类型能表示的字符串最多占用的字节数就是 M×W 。
  3. 假设它实际存储的字符串占用的字节数是 L 。

所以确定使用1个字节还是 2 个字节表示真正字符串占用的字节数的规则就是这样：

  • 如果 M×W <= 255 ，那么使用 1 个字节来表示真正字符串占用的字节数。也就是说 InnoDB 在读记录的变长字段长度列表时先查看表结构，如果某个变长字段允许存储的最大字节数不大于 255 时，可以认为只使用 1 个字节来表示真正字符串占用的字节数。
  • 如果 M×W > 255 ，则分为两种情况：
    • 如果 L <= 127 ，则用 1 个字节来表示真正字符串占用的字节数。
    • 如果 L > 127 ，则用 2 个字节来表示真正字符串占用的字节数。

  对于一些占用字节数非常多的字段，比方说某个字段长度大于了 16KB，那么如果该记录在单个页面中无法存储时，InnoDB 会把一部分数据存放到所谓的溢出页中（我们后边会唠叨），在变长字段长度列表处只存储留在本页面中的长度，所以使用两个字节也可以存放下来。

  • 总结一下就是说：如果该可变字段允许存储的最大字节数（ M×W ）超过 255 字节并且真实存储的字节数（ L ）超过127字节，则使用 2 个字节，否则使用 1 个字节。

NULL 值列表

avatar
文章
阅读
粉丝