数据库的设计规范和其他调优策略

主键设计

自增ID的问题
1. 可靠性不高
  - 存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0 才修复
2. 安全性不高
  - 对外暴露的接口可以非常容易猜测对应的信息
3. 性能差
  - 自增ID的性能较差，需要在数据库服务器端生成
4. 交互多
  - 业务还需要额外执行一次类似 last_insert_id()函数才能知道刚才插入的子增值
5. 局部唯一性
  - 自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务期间都是唯一的。
UUID
- 特点：
  - 全局唯一，占用36字节，数据无序，插入性能差
- 认识UUID：
```
UUID = 时间 + UUID版本(16字节) - 时钟序列(4字节) - MAC地址(12字节)
```
  - UUID将时间低位放在最前面，而这部分数据是一直变化的，并且是无序
改造UUID
1. MySQL8.0解决了UUID存在的空间占用的问题，除去了UUID字符串中无意义的字符串，并且将字符串用二进制类型保存，这样存储空间降低16字节
2. 提供bin_to_uuid函数进行转换
```
SET @uuid = UUID();

SELECT @uuid.uuid_to_bin(@uuid),uuid_to_bin(@uuid,true);
```

数据库的设计规范

范式

在关系型数据库中，关于数据表设计的基本原则、规则就称为范式
常见有6种范式，按照范式级别，从低到高分别是：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)
键和相关属性的概念
- 范式的定义会使用到主键和候选键，数据库中的键由一个或者多个属性组成。
  - 超键：能唯一标识元组的属性集叫做超键
  - 候选键：如果超键不包括多余的属性，那么这个超键就是候选键
  - 主键：用户可以从候选键中选择一个作为主键
  - 外键：如果数据表R1中的某属性集不是R1的主键，而是另一个数据表R2的主键，那么这个属性集就是数据表R1的外键
  - 主属性：包含在任一候选键中的属性称为主属性
  - 非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性

第一范式：

第一范式主要是确保数据表中每个字段的值必须具有原子性，也就是说数据表中每个字段的值为不可再次拆分的最小数据单元

第二范式：

第二范式要求，在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的，而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。（要求中的主键，其实可以拓展替换为候选键）。
对于非主属性来说，并非完全依赖候选键，这样会产生怎样的问题呢？
1. 数据冗余
2. 插入异常
3. 删除异常
4. 更新异常

第三范式：

第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段。通俗的讲，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。

BCNF（巴斯范式）

在3NF的基础上进行了改进，提出了巴斯范式，也叫作巴斯-科德范式
若一个关系达到了第三范式，并且它只有一个候选键，或者它的每个候选键都是单属性，则该关系自然达到BC范式

第四范式

多值依赖的概念：
- 多值依赖：即属性之间的一对多关系，记为 k->->A
- 函数依赖事实上是单值依赖，所以不能表达属性值之间的一对多关系
- 平凡的多值依赖：全集 U = K+A，一个k可以对应于多个A，此时整个表就是一组一对多关系
- 非平凡的多值依赖：全集 U=K+A+B，整个表有多组一对多关系，且有一部分是相同的属性组合，多部份是互相独立的属性组合
第四范式即是在满足巴斯-科德范式的基础上，消除非平凡且非函数依赖的多值依赖（即把同一表内的多对多关系删除）

第五范式、域键范式

在满足第四范式（4NF）的基础上，消除不是由候选键所蕴含的连接依赖。

反范式化：

概述：
如果数据库中的数据量比较大，系统的UV和PV访问频次比较高，则完全按照MySQL的三大范式设计数据表，会产生大量的关联查询，在一定程度上会影响数据库的读性能。在此场景下，反范式化也是一种优化思路
1. 为满足某种商业目标，数据库性能比规范化数据库更重要
2. 在数据规范化的同时，要综合考虑数据库的性能
3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
4. 通过在给定的表中插入计算列，以方便查询

ER模型

ER模型是一个工具，ER模型也叫作实体关系模型，是用来描述现实生活中客观存在的事物、十五的属性，以及事物之间关系的一种数据模型。在开发基于数据库的信息系统的设计阶段，通常使用ER模型来描述信息需求和信息特性，帮助我们理清业务逻辑，从而设计出优秀的数据库
ER模型三要素
- E-R（entity-relationship，实体-联系）模型中有三个主要概念：实体集、属性、联系集
  - 实体：可以看作是数据对象，往往对应于现实生活中的真正存在的个体。在ER模型中，用矩形来表示。实体分为两类，分别是强实体和弱实体。强实体是指不依赖于其他实体的实体；弱实体是指对另一个实体有很强的依赖关系的实体
  - 属性：是指实体的特性。在ER模型中用椭圆形来表示
  - 关系：是指实体之间的联系。比如超市把商品卖给顾客，就是一种超市与顾客之间的联系。在ER模型用菱形来表示
- 注意：实体和属性不容易区分。这里提供一个原则：我们要从系统整体的角度出发去看，可以独立存在的是实体，不可再分的是属性。也就是说，属性不能包含其他属性
- 关系的类型
  - 一对一：指实体之间的关系是一一对应的，比如个人与身份证之间的关系就是一对一的关系。一个人只能有一个身份证信息，一个身份证信息也只属于一个人
  - 一对多：指一边的实体通过关系，可以对应多个另外一边的实体
  - 多对多：指关系两边的实体都可以通过关系对应多个对方的实体。
ER模型图转换成数据表
1. 一个实体通常转换成一个数据表
2. 一个多对多的关系，通常也转换成一个数据表
3. 一个1对1，或者1对多的关系，往往通过表的外键来表达，而不是设计一个新的数据表
4. 属性转换成表的字段

数据表的设计原则

原则：三少一多。核心是简单可复用。简单指的是用更少的表，更少的字段，更少的联合主键字段来完成数据表的设计。可复用则是通过主键、外键的使用来增强数据表之间的复用率。
1. 数据表的个数越少越好
2. 数据表中的字段个数越少越好
3. 数据表中联合主键的字段个数越少越好
4. 使用主键和外键越多越好

数据库其它调优策略

数据库调优的措施

调优的目标

尽可能节省系统资源，以便系统可以提供更大负荷的服务（吞吐量更大）
合理的结构设计和调整参数，以提高用户操作响应的速度（响应速度更快）
减少系统的瓶颈，提高MySQL数据库整体的性能

如何定位调优问题

用户的反馈
日志分析（主要）
- 通过查看数据库日志和操作系统日志等方式找出异常情况，通过它们来定位遇到的问题
服务器资源使用监控
- 通过监控服务器的CPU、内存、I/O等使用情况，可以实时了解服务器的性能使用，与历史情况进行对比
数据库内部状况监控
- 活动会话(Active Session)监控是一个重要的指标
其他
- 对事务、锁等待等进行监控，这些都可以帮助我们对数据库的运行状态有更全面的认识

调优的维度和步骤

选择合适的DBMS
优化表设计
优化逻辑查询
优化物理查询
使用Redis或Memcached作为缓存
库级优化
1. 读写分离
2. 数据分片

优化MySQL服务器

优化服务器硬件

配置较大的内存
配置告诉磁盘系统
合理分布磁盘I/O
配置多处理器

优化MySQL的参数

innodb_buffer_pool_size：这个参数是MySQL数据库最重要的参数之一，表示InnoDB类型的表和索引的最大缓存。这个值太大会影响操作系统的性能
key_buffer_size：表示索引缓冲区的大小。索引缓冲区是所有的线程共享。它的大小取决于内存的大小。如果值太大，就会导致操作系统频繁换页。对于内存在4GB左右的服务器该参数可设置为256M或384M
table_cache：表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多。默认2402，调到512-1024最佳
query_cache_size：表示查询缓冲区的大小。8.0需要和query_cache_type配合使用
query_cache_type：
- 0：所有的查询都不使用查询缓存区。但是query_cache_type=0并不会释放query_cache_size所配置的缓存区内存
- 1：所有的查询都将使用查询缓存区，除非在查询语句中指定SQL_NO_CACHE
- 2：只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓存区
sort_buffer_size：表示每个需要进行排序的线程分配的缓冲区的大小。默认数值是2097144字节（约2MB）。对于内存在4GB左右的服务器推荐设置为6-8M，如果有100个连接，那么实际分配的总共排序缓冲区的大小为100×6=600MB
join_buffer_size = 8M：表示联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享
read_buffer_size：表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节）。当线程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read_buffer_size = n 可以临时设置该参数的值。默认为64K，可以设置为4M
innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为0、1和2。该参数的默认值为1。
- 0：表示每秒1次的频率将数据写入日志文件并将日志文件写入磁盘。每个事务的commit并不会触发前面的任何操作。该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
- 1：表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘进行同步。该模式是最安全的，但也是最慢的一种方式。因为每次事务提交或事务外的指令都需要把日志写入（flush）硬盘。
- 2：表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘。该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
innodb_log_buffer_size：这是 InnoDB 存储引擎的事务日志所使用的缓冲区。为了提高性能，也是先将信息写入 Innodb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。
max_connections：表示允许连接到MySQL数据库的最大数量，默认值是 151 。如果状态变量connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。这个连接数不是越大越好，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。
back_log：用于控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ，之后的版本默认为 50 + （max_connections / 5），对于Linux系统推荐设置为小于512 的整数，但最大不超过900。如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log 的
thread_cache_size ：线程池缓存线程数量的大小，当客户端断开连接后将当前线程缓存起来，当在接到新的连接请求时快速响应无需创建新的线程。这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。那么为了提高性能可以增大该参数的值。默认为60，可以设置为120。可以通过如下几个MySQL状态值来适当调整线程池的大小：

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 2 |
+-------------------+-------+

当 Threads_cached 越来越少，但 Threads_connected 始终不降，且 Threads_created 持续升高，可适当增加 thread_cache_size 的大小。
wait_timeout ：指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
interactive_timeout ：表示服务器在关闭连接前等待行动的秒数。

优化数据结构

拆分表：冷热数据分离
增加中间表
增加冗余字段
优化数据类型

优化插入记录的速度

MyISAM引擎的表
1. 禁用索引
2. 禁用唯一性检查
3. 使用批量插入
4. 使用LOAD DATA INFILE批量导入
InnoDB引擎的表
1. 禁用唯一性检查
2. 禁用外键检查
3. 禁止自动提交

使用非空约束

分析表、检查表与优化表

分析表

MySQL中提供了ANALYZE TABLE语句分析表，ANALYZE TABLE语句的基本语法如下

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…

使用ANALYZE TABLE 分析表的过程中，数据库系统会自动对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表，但是不能作用于视图。

ANALYZE TABLE分析后的统计结果会反应到cardinality 的值，该值统计了表中某一键所在的列不重复的值的个数。该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使用。也就是索引列的cardinality的值与表中数据的总条数差距越大，即使查询的时候使用了该索引作为查询条件，存储引擎实际查询的时候使用的概率就越小。

检查表

MySQL中可以使用CHECK TABLE 语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。CHECK TABLE语句在执行过程中也会给表加上只读锁。

对于MyISAM类型的表，CHECK TABLE语句还会更新关键字统计数据。而且，CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。该语句的基本语法如下：

CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

其中，tbl_name是表名；option参数有5个取值，分别是QUICK、FAST、MEDIUM、EXTENDED和CHANGED。各个选项的意义分别是：
- QUICK ：不扫描行，不检查错误的连接。
- FAST ：只检查没有被正确关闭的表。
- CHANGED ：只检查上次检查后被更改的表和没有被正确关闭的表。
- MEDIUM ：扫描行，以验证被删除的连接是有效的。也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点。
- EXTENDED ：对每行的所有关键字进行一个全面的关键字查找。这可以确保表是100%一致的，但是花的时间较长。
option只对MyISAM类型的表有效，对InnoDB类型的表无效。比如：
该语句对于检查的表可能会产生多行信息。最后一行有一个状态的 Msg_type 值，Msg_text 通常为 OK。如果得到的不是 OK，通常要对其进行修复；是 OK 说明表已经是最新的了。表已经是最新的，意味着存储引擎对这张表不必进行检查。

优化表
- 方式1：OPTIMIZE TABLE
  
  MySQL中使用OPTIMIZE TABLE 语句来优化表。但是，OPTILMIZE TABLE语句只能优化表中的VARCHAR 、BLOB 或TEXT 类型的字段。一个表使用了这些字段的数据类型，若已经删除了表的一大部分数据，或者已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更新，则应使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。OPTIMIZE TABLE 语句对InnoDB和MyISAM类型的表都有效。该语句在执行过程中也会给表加上只读锁。
- OPTILMIZE TABLE语句的基本语法如下：
```
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
```
  - LOCAL | NO_WRITE_TO_BINLOG关键字的意义和分析表相同，都是指定不写入二进制日志。

大表优化

限定查询的范围
1. 禁止不带任何限制数据范围条件的查询语句
读/写分离
垂直拆分
水平拆分

其他调优策略

服务器语句超时处理

在MySQL 8.0中可以设置服务器语句超时的限制，单位可以达到毫秒级别。当中断的执行语句超过设置的毫秒数后，服务器将终止查询影响不大的事务或连接，然后将错误报给客户端。
设置服务器语句超时的限制，可以通过设置系统变量MAX_EXECUTION_TIME 来实现。默认情况下， MAX_EXECUTION_TIME的值为0，代表没有时间限制。例如：

SET GLOBAL MAX_EXECUTION_TIME=2000;
SET SESSION MAX_EXECUTION_TIME=2000; #指定该会话中SELECT语句的超时时间

创建全局通用表空间
使用手动创建共享表空间可以节约元数据方面的内存
MySQL 8.0新特性：隐藏索引对调优的帮助