MySQL知识点

InnoDB与MyISAM的区别

MysSQL 5.5及以上的版本默认使用InnoDB引擎。

InnoDB特点：

1. 灾难恢复性好
2. 支持四种级别的事务，默认为可重复读级别
3. 使用行级锁
4. 数据的物理组织形式是簇表
5. 支持外键
6. 支持热备份
7. 支持索引和数据的缓冲

MyISAM特点：

1. 表级锁
2. 灾难恢复性差
3. 不支持事务
4. 数据紧凑存储
5. 仅支持索引缓存

事务ACID特性

1. 原子性(atomicity) 事务中的所有操作，要么都做，要么都不做
2. 一致性(consistency) 事务执行结果必须是使数据库从一个一致性状态变到另一个一致性状态
3. 隔离性(isolation) 并发执行的各个事务相互不干扰
4. 持续性(durability) 事务一旦提交，对数据库的修改就是永久的

事务隔离级别

1. read uncommited--读未提交，存在脏读
2. read commited 读提交，不允许脏读，允许不可重复读
3. repeatable read 可重复读，同一个事务内，同一个查询多次执行结果相同，但不能拒绝幻读
4. serializable 序列化，解决了幻读，将锁施加在所有访问的数据上

MySQL InnoDB引擎默认使用的是repeatable read，利用了MVVC(多版本并发控制)技术

MVVC简要介绍

innodb的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的。 这两个列,一个保存了行的创建时间,一个保存行的过期时间(或删除时间)，当然存储的不是实际的时间值,而是系统版本号。每开始一个事务,系统版本号自增。事务开始时刻的系统版本号,将作为这个事务的版本号，用来和查询的到的每行记录版本号进行比较。

MVVC下的insert操作

InnoDB插入新增行，并以系统当前的版本号作为行的创建时间

MVVC下的select操作

InnoDB只选择行的创建时间小于当前事务的版本号，即事务开始前就存在的行

MVVC下的delete操作

InnoDB将删除的行的过期时间设置为当前系统的版本号

MVVC下的update操作

InnoDB复制了一行,这个新行的版本号使用了系统版本号,它也把系统版本号作为了删除行的版本。

这里的删除只是标记删除，在commit之后真正删除。

封锁协议

封锁是并发控制一个非常重要的技术，基本的封锁类型有两种：排他锁（简称X锁）和共享锁（简称S锁）。

排他锁又称为写锁。若事务T对数据对象A加上X锁，则只允许事务T读取和修改A，其他事务在事务T释放A上的锁之前不能对数据A进行操作。

共享锁又称为读锁。若事务T对数据对象A加上S锁，则事务T可以读取但不能修改A，其他事务可以对A上S锁但是不能上X锁，直到T释放了锁。

一级封锁协议

事务T在修改数据R之前必须先对其加上X锁，直到事务结束时释放。 可以防止丢失修改，但不能保证可重复读和不读脏数据。

二级封锁协议

在一级封锁协议的基础上，事务T在读取数据R之前必须先对其加上S锁，读取完后即可释放S锁。 可以防止丢失修改和读脏数据，但是不能保证可重复读。

三级封锁协议

在一级封锁协议的基础上，事务T在读取数据R之前必须先对其加上S锁，事务结束时才可以释放S锁。 可以防止丢失修改、读脏数据以及可重复读。

死锁四个条件

1. 不可剥夺资源
2. 持有和等待
3. 互斥
4. 循环等待

Mysql InnerDB处理死锁的机制：发现死锁，回退开销最小的事务

查询优化常用策略

1. 减少对数据的访问
2. 重写SQL
3. 重新设计库表
4. 添加索引

连接优化

1. ON, USING子句中的列确认有索引，如果连接顺序为B、A，则只需要在A上创建索引。举例select B.b,A.a from B join A on B.col1=A.col2,因为会遍历B表，所以只需要在A的col2上有索引

2. LEFT JOIN的成本比INNER JOIN成本高

3. 反范式设计，减少表连接个数

4. 考虑在应用层实现连接

GROUP BY、DISTINCT、ORDER BY语句优化

1. 尽量对较少的行进行排序

2. 如果连接了多张表，ORDER BY的列应该属于连接顺序的第一张表

3. 利用索引排序

4. GROUP BY，ORDER BY的列尽量在一个表中

5. 保证索引列与ORDER BY的列相同，且各列按相同方向排序

索引优化

1. 筛选记录的条件应该能组成复合索引最左边的部分，即按照最左前缀的原则进行筛选。

CREATE INDEX idx_a,b,c on table1(a,b,c);
# where子句
where a=? and c=? # 只用到了a的索引信息
where a=? and b>? and c>? # 只用到了a和b的索引，因为MySQL只支持最近一个范围查询

1. 索引的列不要有太多重复项

2. 使用更短的索引，当索引的列是一个很长的字符串时，可以考虑前缀索引，仅索引前面一部分字符，但是也得保证重复项少

3. 不要创建太多索引，索引也会占用大量空间

子查询优化

1. 子查询尽量改写成JOIN的写法

IN列表优化

对于IN列表，MySQL会排序IN列表，然后用二分查找去定位数据。IN的列表不要过长，可以用多个等于查询的方式代替IN查询

UNION优化

UNION语句默认移除重复记录，需要用到排序操作，如果结果集合很大，成本会很高，尽量使用UNION ALL

范式

第一范式： 表的每一个字段都不可分

第二范式： 满足第一范式，且每一个非主属性完全函数依赖于任何一个候选码

第三范式： 每一个非主属性既不传递依赖于码，也不部分依赖于码，也就是非主属性互相之间应该是无关的

参考书籍

《MySQL DBA修炼之道》

书上还有很多的优化方式，但是我不太看得懂，还得慢慢学习。