mysql-----（3）常见知识点

MYSQL

【一】Mysql常用的引擎

• 1、 InnoDB
  InnoDB 的存储文件有两个，后缀名分别是 .frm 和 .idb，其中 .frm 是表的定义文件，而 idb 是数据文件。
  InnoDB 中存在表锁和行锁，不过行锁是在命中索引的情况下才会起作用。
  InnoDB 支持事务，且支持四种隔离级别（读未提交、读已提交、可重复读、串行化），默认的为可重复读；
  InnoDB 主键使用的是聚簇索引

• 2、Myisam
  Myisam 的存储文件有三个，后缀名分别是 .frm、.MYD、MYI，其中 .frm 是表的定义文件，.MYD 是数据文件，.MYI 是索引文件
  Myisam 只支持表锁，且不支持事务。Myisam 由于有单独的索引文件，在读取数据方面的性能很高

• 存储结构
  InnoDB 和 Myisam 都是用 B+Tree 来存储数据的

【二】MySQL 的索引

目前大多数数据库系统及文件系统都采用变种 B+Tree 作为索引结构B+ 树中的 B 代表平衡，而不是二叉，B+ 树是由二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree）逐步优化而来
系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一磁盘块中的数据会被一次性读取出来，而不是按需读取

【三】MySQL 的相关优化

1. 开启查询缓存。避免某些 SQL 函数直接在 SQL 语句中使用，从而导致 Mysql 缓存失效。
2. 优化sql语句，多表在查询时，可以使用内连接，外连接查询。避免一个笛卡尔积的查询。
3. 若是查到了一条即可，勿要全部都查一遍，此时要善用 limit
4. 可以创建适当的存储过程，因为存根过程是在数据库中编译好了的，查询的速度更快。
5. 考虑表的分割，如果一些字段不常用，可以考虑把表进行拆分，降低表的复杂度。
6. 建合适的索引。所以要建在合适的地方，合适的对象上。经常操作 / 比较 / 判断的字段应该建索引。
7. 允许的情况下，可进行读写分离，还可以将数据库做一个主从，一个主要用于读数据，另外一个用于写更新数据。
8. 搭建redis或者memcache做为缓存层，提高数据库读取速度。

【四】B树与B+树的底层:

B树也称B-树,它是一颗平衡多路查找树。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B-tree算法减少定位记录时所经历的中间过程，从而加快存取速度。

【五】mysql的索引分为聚簇索引和非聚簇索引

mysql中，不同的存储引擎对索引的实现方式不同，大致说下MyISAM（非聚簇索引）和InnoDB（聚簇索引）两种存储引擎。
聚集索引表示表中存储的数据按照索引的顺序存储，检索效率比非聚集索引高，但对数据更新影响较大。
非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，非聚集索引检索效率比聚集索引低，但对数据更新影响较小。

【六】事务的实现原理：

• 隔离性：通过锁来实现，讲解数据库的隔离性，就必须提到其脏读、不可重复读、幻读的概念，具体会在下面讲解到！！
• 原子性和持久性：通过redo log 来实现
• 一致性：通过undo来实现

Undo原理：（备份旧数据）
在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

Redo原理：（保存最新数据）
和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

【七】MySQL的并发控制与加锁分析

隔离级别

• 脏读（dirty read）：两个事务，一个事务读取到了另一个事务未提交的数据。
• 不可重复读：一个事务中两次读取的数据的内容不一致
• 幻读（phantom read）：两个事务，事务A与事务B，事务A在自己执行的过程中，执行了两次相同查询，第一次查询事务B未提交，第二次查询事务B已提交，从而造成两次查询结果不一样，这个其实被称为不可重复读；如果事务B是一个会影响查询结果的insert操作，则好像新多出来的行像幻觉一样，因此被称为幻读。其他事务的提交会影响在同一个事务中的重复查询结果。（一个事务中两次读取的数据的数量不一致 。因为另一个事务是insert操作）

【八】死锁（需要继续补充）

死锁是指两个或者多个事务在同一资源上相互作用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。

【九】慢查询