《MySQL DBA 修炼之道》第四章 开发进阶

范式和反范式

1. 范式

常用的范式有第一、二、三范式。
（1） 第一范式
要求：数据库【表】的每一列【属性】都是不可分割的数据项。每行每列的数据只能存放单个值，属性列不能重复。其实第一范式是对关系模式的基本要求。

范式是数据库规范化的一个手段，是数据库设计的一系列原理和技术。目的是为了减少数据库中数据冗余，增进数据一致性。
通常来说，数据库满足某一层级的范式，那么它就满足前面所有层级的范式。比如，满足了第二范式，就满足了第一范式。满足了第三范式就满足了第一、二范式。如果数据库表满足了第三范式，该表通常可认为是“范式化”的。

【疑问】那么符合第一范式要求的设计是什么样子呢？

【疑问】不满足第一范式要求的设计，一定不会出现吗？

telephone列就属于常用的反范式设计，使用分隔符存储多个值。一般来说，如果应用程序只需要存储和使用（没有业务逻辑的需要），不需要对该项检索或者修改，那么就可以存储为以上形式。

（2）第二范式
要求：数据表里面的数据都要和该数据表主键（这里的主键不是我们常说的id，主键会决定能否通过主键得出后面的值）有完全对应关系；如果有哪些数据只和主键的一部分有关，就得独立出来变成另外一张表。
举例：

user_id	name	skill	work location	skill level
1	Trishia	Java	北京	3

假设主键是复合主键(user_id_name_skill)，我们来思考work location是由谁决定的？应该是user_id和name，所以work location应该被移出单独建立一张表。

id	name	skill	skill level
1	Trishia	Java	3

area_id	user_id	work location	area_code
1	1	北京	10

（3）第三范式
要求：非主键属性之间没有相关性，不能互相决定值。

范式的好处是可以使编程相对简单，数据量小了，更适合放入内存，只需要更新少量的数据，所以更新数据快。
缺点：查询变的很复杂，需要更多的JOIN操作，可以复合索引的列因为范式的需要被分不到了不同的表中，导致索引策略不佳。

个人观点：纯范式的设计，也不一定有多好。

2. 反范式

什么是反范式？范式是减少数据冗余，反范式就试图增加数据冗余，但是也不是乱增加的。目的是为了优化数据库读取数据的性能。
因为：范式化的设计如果JOIN的表很多，会导致很多的I/O，那么查询就会变慢。反范式话的好处也随之体现，减少I/O次数，用空间换取时间。

好的数据库表设计，需要考虑各个表的更新量和查询量，以及该表设计对其他查询的影响，避免引发性能问题。
冗余数据势必会带来数据一致性的问题，我们势必要考虑数据一致性的优先级，对于重要的、敏感的数据，一定要保证一致性。

1）反范式化设计在统计分析、数据仓储等领域使用的比较多，通过冗余数据，增加统计表、中间表，目的是为了让数据更快的加载和分析。

举例子：
用户表 user

user_id	username
778899	Trishia

账户表 account

id	user_id	bank_number	bank_name	bank_address
1	778899	3367799902937392	中山银行	中山路778号

如果要显示所有账户信息，还需要去user表查询username，我们可以在account中增加username冗余字段，避免多余的I/O查询，影响性能。

2）反范式对索引筛选和排序也很友好，上一个例子中，如果需要对信息进行排序，我们还要查询user表显示username，如果使用反范式设计，则可以直接在account表上对username创建索引，可以利用索引排序快速返回结果。

3）冗余数据还可以放入缓存中，如memcached，这样就可以从缓存中获取，而不需要与建立数据库的连接。

4）一些统计操作，比如COUNT,SUM,MAX,MIN等操作，如果计算耗时较多，可以考虑增加冗余统计数据。如增加额外的字段或表。

对于发帖统计，需要统计最近24小时的发帖数量，我们可以每小时插入一条统计数据到统计表，虽然不够准确，但这种数据用户不会介意。

一般项目中都会混合使用范式与反范式，开发们不用过多纠结，最终目的都是便于写入或者查询，查询多的业务优先考虑便于快速查询的方法，比如添加冗余字段，减少连接与I/O次数，达到快速响应。对于经常被改动的数据，要贴近范式设计，目的是为了，修改最少的数据，占用最少的空间，降低数据不一致的可能性。

慢查询日志

1. 开启慢查询日志

-- 查询慢查询是否启用
show variables like '%query_log%';

我现在默认是关闭的。

-- 开启慢查询日志 0关闭，1开启
set global slow_query_log=1;

2. 查看慢查询日志

mysqldumpslow 是官方自带的

终端运行：
查看访问时间最长的10条sql
msyqldumpslow -t 10 /path/.../slowquery.log

查看访问次数最多的10条sql
msyqldumpslow -s -c -t 10 /path/.../slowquery.log

查看返回结果集最多的10条sql
msyqldumpslow -s -r -t 10 /path/.../slowquery.log

还有一些第三方工具mysqlsla、pt-query-digest等，感兴趣自己查阅一下。

事务和锁

事务

事务是数据库管理系统执行过程中的一个逻辑单元。

1. 事务的ACID特性

并非任意的对数据库的操作都是数据库事务。数据库事务有以下四个特性，常称为ACID特性。

• Atomic 原子性
  事务作为一个整体被执行，事务中对数据库的操作，要么全部执行成功，要么全部不执行。
• Consistency 一致性
  事务应该确保数据库中的数据从一个一致状态转变为另一个一致状态。
• Isolation 隔离性
  多个事务并发执行操作，事务之间互相不影响。
• Durability 持久性
  提交的事务对数据库做的修改，被永久保存在数据库中。

2. 事务的隔离级别

事务隔离级别越高，越能保证数据的ACID特性。MySQL事务包含四个隔离级别，从高到低如下：

• read uncommitted （dirty read)
  读取到未提交的事务，开始事务做了修改，但是还没有提交，其他事务已经看到该事务所做的修改了。

• read committed
  读取到已经提交的事务，乍一看好像没什么问题。比如你在操作一个账户的时候，（1）第一次查询余额剩5万，准备取出500当作这周的生活费
  （2）这时候有银行业务员动了你的账户，转走了5万元钱。
  （3）你准备转账了，咔～提示你余额不足，蒙圈了吧。没钱了。
  是不是搞的你一头雾水。明明卡里还有5万块，怎么转账的时候就没钱了呢？看来read committed也不太合适。

• repeatable read
  可重复读。同一个事务，同一个查询请求，多次执行，获得的结果集是相同的。
  MySQL InnoDB引擎默认使用的就是可重复读。当事务A发出一个SELECT语句时，InnoDB会给该事务一个时间节点，如果事务B是在该时间节点之后进行的删除数据操作，事务A是看不到被删除的数据的。这也被称为MVCC（multi-versioned concurrency control）多版本并发控制。

• serializable
  序列化，最高级别的锁，它将锁施加在所有访问的数据上。把查询语句涉及的数据也加上共享锁，阻止其他事务修改真实数据。

锁

InnoDB行锁

一般来说，我们没有必要针对InnoDB引擎的表使用Table Locks。InnoDB支持的行级锁就能解决绝大部分场景。
数据库的锁技术是基于索引实现的，InnoDB也不例外，如果SQL语句没有利用到索引（如 Primary key），那么InnoDB会进行全表扫描，锁定所有行（但也不是表锁）。
锁过多的行增加了锁的竞争，降低了病发率，所以建立索引是很重要的。InnoDB需要索引过滤那些不需要访问的行。

扩展：在Insert的时候，InnoDB会使用next-key锁技术，锁住相邻的键，防止其他事务相邻数据操作，从而避免产生幻读。只有repeatable read或者更高级别才有next-key锁。

等待行锁超时

当我们在慢查询日志中会看到一些很耗时的操作，但是单独执行却很快，此时可能是因为等待InnoDB行锁而超时。