MySQL

数据库三范式和反模式

范式

第一范式：原子性约束，字段不可再分。

第二范式：唯一性约束，必须有主键，其他列必须依赖主键。

第三范式：冗余性约束，非主键列必须直接依赖于主键。例如 订单表（订单编码，顾客编码，顾客名称）中，名称依赖于顾客编码，从而间接依赖于主键，就不符合第三范式。

反模式

严格的范式强调数据之间的精确关联关系，但是会增加表的数量，从而增加多表关联查询，降低性能。

因此在实际项目中出于性能的考虑，可能不会严格遵守范式，即使用反模式，冗余一些关联度很高的数据在同一张表，减少关联查询，后续通过在短时间内同步数据。

典型应用场景就是在很大数据量统计的情况下，增加一张缓存表，用来在业务表中插入数据的时候在这张表记录数据量，统计的时候直接查询这种缓存表，从而提升性能。

InnoDB和MYISAM引擎如何选择（区别）

区别

	ISAM	InnoDB
事务	不支持	支持
锁	表级锁	行级锁
主键	允许没有索引和主键	无主键情况自动创建主键（隐藏）
外键	不支持	支持
统计行数	有保存行数的属性，直接查询	实时统计，速度慢，需要where结合索引

选择标准：

是否需要事务

索引和缓存，外键

读多写少还是读少写多

热备，崩溃恢复，存储限制

InnoDB引擎独有特性（4大特性）

• 插入缓冲(insert buffer)：缓存辅助索引数据，批量插入
• 二次写(double write)
• 自适应哈希索引(ahi)
• 预读(read ahead)

MySQL创建索引的原则

最适合建立索引的列是WHERE子句中的列，或是连接子句中的列，而不是SELECT子句中的列

应该在索引基数大的列建立索引。索引基数越小，索引效果越差，极端情况是性别列，只有M和F，结果可能都是一半，和没有索引差不多。其背后与索引数据结构B树有关。

适当建立复合索引，因为复合索引可以提高索引基数

避免过度建立索引，因为会占用更多空间

UUID不适合建立主键索引

对字符串建立索引，最好指定长度，可以减少空间占用

MYSQL使用索引注意事项

避免使用 != ，IS NULL 及 OR 条件，会避开索引

避免WHERE中字段使用表达式操作

避免WHERE中字段使用函数操作

不要在WHERE的 = 左边使用表达式，函数运算

字符串列是索引时，查询一定要加双引号

LIKE的%不能放在前面

MYSQL索引原理

B-树 （平衡多路查找树）

二叉查找树：左子树 < 父节点 < 右子树

二叉查找树在插入一系列有序数据时会退化成序列，数的高度变大，查找次数变多，可考虑用平衡二叉树替代。

平衡二叉树：是一颗满足二叉查找树条件，且任何节点的两棵子树高度相差<=1

平衡二叉树具有很好的查找性能，但是一旦插入删除数据，很可能破坏平衡性，此时可通过左旋或又旋重新达到平衡。

多路平衡二叉树（B-Tree）：为磁盘等外存储设备设计的一种平衡查找树。

背景：InnoDB引擎读取磁盘数据时按块读取，InnoDB每次读取一页16K，但磁盘块最小单位是4K，因此会预读多3k，从而减少IO次数。

B-Tree主要特征：

1. 高度很小（3到4）
2. 所有叶子节点均在同一层
3. 所有节点关键字是按递增次序排列，并遵循左小右大原则
4. B-TREE的节点中包含三部分，键值，指针，数据。每个节点存储一个磁盘块

InnoDB读取磁盘策略结合B-TREE的数据结构，可以让读取IO次数减少，但是查询结果速度尽量高。

B+TREE：由B-TREE优化而来，INNODB选择它作为索引数据结构。

B+TREE相对于B-TREE的不同点：

1. 非叶子节点只存储键值信息
2. 叶子节点之间有一个链指针
3. 数据记录都存放在叶子节点中

从上一节中的 B-Tree 结构图中可以看到，每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度。

InnoDB有哪些索引类别

主键索引（聚集索引，INNODB专有）：将主键作为键值构造B+树，叶子节点不仅包含主键，还包含剩余的键。聚集索引是根据键值顺序存放的。非叶子节点存储的是<键值，下一层地址>

非主键索引（辅助索引）：叶子节点存储的<键值，主键值(InnoDB方式)或记录地址(ISAM方式)>，因此这需要两次查找。

聚簇索引的注意点有哪些？

1.要用自增ID做主键：插入速度依赖插入顺序，按照主键的插入是最快的方式，否则容易出现页分裂，影响性能。故我们要定义一个自增ID为主键。

以上这点相关其他问题：为什么主键需要主键为自增ID，为什么需要主键带有时间关联性。

答：如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。 总的来说就是可以提高查询和插入的性能。聚簇索引中，N行形成一个页（一页通常大小为16K)。如果碰到不规则数据插入时，为了保持B+树的平衡，会造成频繁的页分裂和页旋转，插入速度比较慢。所以聚簇索引的主键值应尽量是连续增长的值，而不是随机值(不要用随机字符串或UUID)。
故对于InnoDB的主键，尽量用整型，而且是递增的整型。这样在存储/查询上都是非常高效的。

2. 将InnoDB主键定义为不可更新，因为更新主键代价高（树平衡？）

什么是索引的最左匹配特性？

当 B+Tree 的数据项是复合的数据结构，比如索引 (name, age, sex) 的时候，B+Tree 是按照从左到右的顺序来建立搜索树的。

比如当 (张三, F) 这样的数据来检索时，B+Tree 可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了

事务的四个特性

原子性：同一个事物中的操作要么都成功要么都失败，失败后能回退到原来状态。

一致性：资料写入必须符合数据库约束，保证数据库完整性不遭到破坏。

隔离性：防止并发事物由于交叉而产生与预期结果不一致。隔离级别：Read uncommitted，read committed，repeatable read，Serializable

持久化：事物结束后，对数据的修改就是永久的，即使系统故障也不应该丢失。

MYSQL四种隔离级别

Read uncommitted，read committed，repeatable read，Serializable

事务的并发问题

脏读：A读取了B更新的数据，然后B回滚，A读的就是脏数据。

不可重读： A多次读取同一数据，读取过程中B修改了数据，导致A读的多次结果不一致。解决方法：锁行

幻读：A在读取某表期间，B插入了数据，导致A多次读取记录条数不一样。解决方法：锁表

MYSQL事务隔离级别会产生的并发问题

READ UNCOMMITED (未提交读）：事务中修改的数据，即使未提交，其他事务也是可见的，会产生脏读。

READ COMMITED（提交读）：提交前的修改，其他事务不可见。可以避免脏读，但是会有不可重读的问题。

READ REPEATABLE（可重读）：可以保证多次读同一条数据结果一致，但是不能保证读取期间有新的数据插入，导致幻读。

SERIALIZABLE（可串行化）：强制并发事务串行执行。

 

MYSQL默认事务隔离级别是 READ REPEATABLE

四种隔离区别见表

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	否（x）
串行化（serializable）	否	否	否

• 上图的 <X> 处，MySQL 因为其间隙锁的特性，导致其在可重复读（repeatable-read）的隔离级别下，不存在幻读问题。也就是说，上图 <X> 处，需要改成“否”！！！！

MYSQL锁机制

分类

共享锁——读锁

排他锁——写锁

粒度

表锁

行锁

悲观锁和乐观锁

悲观锁：依靠的数据库本身的锁，就是上面的共享锁和排他锁

乐观锁：不适用数据库本身的锁，而是为数据表加一个version字段，用CAS算法实现锁定。

死锁

两个或者多个线程/进程互相等待对方持有的锁，进入死循环状态。

如何避免死锁：

1. 超时机制
2. 按顺序获取对象
3. 避免事务中与用户交互
4. 保持事务简短，尽量将相关操作在一个事务中完成
5. 使用低隔离级别

InnoDB行锁机制

InnoDB基于索引进行锁定，

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁，可以如下显示加锁

排他锁

SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE 

FOR UPDATE

共享锁

SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE

 

MYSQL查询执行顺序

略

MYSQL SQL优化

略

什么是MVCC

MVCC（Multi-Version Concurrency Control）实际上就是一种乐观锁，只不过稍微复杂点，

需要版本号和删除标记这两个字段互相配合，保证事务的一致性。

“但由于Mysql的写操作会加排他锁（前文有讲），如果锁定了还算不算是MVCC？

了解乐观锁的小伙伴们，都知道其主要依靠版本控制，即消除锁定，二者相互矛盾，so从某种意义上来说，Mysql的MVCC并非真正的MVCC，他只是借用MVCC的名号实现了读的非阻塞而已。

”

 

 

 

 

转载于:https://my.oschina.net/u/3300976/blog/3032206