MySQL知识点总结

索引 ：帮助MySQL高效获取数据记录的数据结构。

B-Tree和B+Tree的区别

1.B+Tree 每个节点的指针数和key数相当

2.B+Tree内节点不存储data，叶子节点不存储指针

3.叶子结点增加一个只想相邻结点的指针

为什么使用B+Tree作为索引？

每次创建节点直接申请一个页面大小空间，保证了一个节点只需要一次I/O

b+tree 相比较与平衡二叉树树的深度h较小 （b+tree中每个节点含有多个key值降低了树的深度）

查找时需进行树的深度-1次I/O操作即可。（根节点一直保存在主存中）

MyISAM：叶节点的data域存放的数据记录地址

lnnoDB： 叶节点的data域存放数据文件，表数据文件就是按b+tree组织的索引文件（聚簇索引）

索引的失效条件？

最左前缀原理

在有多列索引时，要在查询中使用索引必须依次从左到右的使用索引，范围查询后面的列无法使用索引。

索引列应单独放在等号一侧（否则失效）

引擎对比（lnnoDB，MyISAM）

lnnoDB

lnnoDB采用MVCC来支持高并发，并实现了四个标准的事务隔离级别。默认级别为REPETABLE READ（可重复读）

并且通过间隙锁策略防止幻读，间隙锁不仅仅锁定查询涉及的行，还会的索引中的间隙进行锁定。防止换行插入。

lnnoDB表基于聚簇索引建立的，表数据就是根据b+tree组织的索引文件。

MVCC多版本并发控制：在每行数据记录后增加两个列，分别为创建版本号和删除版本号。每开始一个新的事务版本号会自动递增，开始时刻的版本号记录为事务的版本号。

进行SELECT操作只能查询到a小于等于并且最接近事务版本号

且b删除版本号要么没定义要么大于事务版本号的记录。

优点：减少的加锁的开销。

缺点：需要额外的两列空间，并且需要进行检查和维护开销。

MyISAM

MyISAM提供大量特性，全文索引，压缩，空间函数（GIS）等，但其不支持事务和行级锁。

 

MySQL数据库事务的ACID

原子性：事务是一个不可分割的最小工作单位，要么事务中的所有操作成功提交事务，要么事务中的所有操作失败回滚。不准许只完成事务中的一部分操作。

一致性：数据库在事务开始之前和事务结束之后总是从一个一致性状态到另一个一致性状态转移。

隔离性：在多个事务并发进行时，事务内部操作的其他事务的可见性（隔离性）有4中标准的隔离等级

持久性：事务提交之后既被持久化的保存到数据库中。即使数据库崩溃也不会丢失数据。通常通过数据库备份保障。

四个标准的隔离等级

READ UNCOMMITTED（未提交读）

可以读取到其它未提交事务的数据。在进行未提交的写事务时准许其他事物进行读事务（造成脏读），禁止进行写事务（防止了更新丢失），在进行读事务的时候准许其它写事务（造成不可重复读，幻读）与读事务。

很少应用

READ COMMITTED   （提交读）

可以读取到其它已提交事务的修改。改进在进行未提交的写事务时禁止其它事务进行读事务（解决脏读）与写事务

但还有不可重复读和幻读的可能性 。

大部分数据库的默认隔离级别。

REPETABLE  READ  （可重复读）

在进行读事务的时候禁止其它写事务，准许读事务。解决了不可重复读的问题。但仍有幻读的可能，因为其它事务仍可以进行插入操作（lnnoDB采用行级锁只锁住了读取的行，用间隙锁策略监控索引的间隙防止幻读问题）。

MySQL的默认隔离等级。

SERIALIZABLE      （可串行化）

通过强制事务串行执行，同一时间只准许一个事务逐条的进行（读取的每一行数据进行加锁）

并发性能低，很少使用。

数据库的三大范式 

第一范式：强调列的原子性，每一列的属性都必须是不可分割，不可重复的

第二范式：在第一范式基础上1.每个表必须有主键，2.表中每一列必须完全依赖主键（多主键时不能依赖一部分）（减少数据库的冗余）。

第三范式：在第二范式基础上。表中的每一列必须直接依赖于主键，不可以传递依赖

数据库的优化

数据库分区

建立索引（结构层次）

缓存机制（减少I/O）

优化查询语句：慢查询日志 explain 解析  是否查询了不需要的列limit返回的行数

sql语句  

内连接，外连接

A  INNER(可不写) JOIN B  ON （condion）按联结条件去两表相匹配的数据记录。

A  LEFT   JOIN B  ON （condion） 取左表全部记录和右表匹配的记录无对应填NULL。