MySql进阶（一）

1、存储引擎

1.1 概念

? 存储引擎又叫做表类型，主要用来决定如何存储数据库的信息、如何检索信息。存储引擎是基于表的，本质上就是处理sql操作。注意：只有mysql才有存储引擎的概念，例如oracle数据库是没有存储引擎的概念。

1.2 特点

? 在使用MySql的时候用户可以选择不同的存储引擎来提高应用的效率，甚至可以定制自己的存储引擎。在 MySql 5.5 之前默认的存储引擎是 MyISAM ，而在这之后使用的是 InnoDB 。在Mysql的诸多存储引擎中，只有InnoDB 和NDB提供事务安全的，其他存储引擎都是非事务安全的。

1.3 存储引擎的特性

1.4 MyISAM

? 在MyISAM存储引擎中，它即不支持事务也不支持外键。它不支持事务。

? 不支持行级锁，只能对整张表加锁，读取会对需要读到的表添加共享锁，写入则会对表添加排他锁。在MyISAM指定了DELAY_KEY_WRITE选项，则每次修改后不会将索引的数据立即添加到数据库中，而是暂时先添加到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种操作虽然提高了写入的性能，但是容易在数据库或者主机崩溃时造成索引损坏，需要执行修复操作。

1.5 InnoDB

? 是MySql默认的事务型存储引擎，切记只有在需要它不支持的特性时，才会选择其他存储引擎。MySql中只有InnoDB引擎支持外键，并且它的主索引就是聚簇索引。在索引中存储了数据，而不用去磁盘中读取。内部使用了很多优化，从磁盘读取数据使用了可预测性读、能够加快读操作并且自动创建自适应的哈希索引、能够加速插入操作和插入缓冲区等。只有该引擎支持真正的在线热备份，缺点是获取一致性视图就需要停止对所有表的写入，在读写混合的场所，则不太适用。

拓展

READ UNCOMMITED 未提交读级别，事务中的修改，即使没有提交对于其他事务也是可见的。但是事务提取未提交的数据会变成脏读。一般不选择它作为隔离级别。
READ COMMITED 提交读级别，大多数数据库级别都是这个级别，但是MySql并不是这个级别。它表示一个事务开始前只能看到已提交事务的修改。这个级别也叫做不可重复。在同样的查询条件，可能会得到不同的结果。
REPEATABLE READ 可重复读级别，这个级别保证了同一个事务中多次读取同样记录的结果是一致的。但是该级别不能解决幻读的问题，即当一个事务读取记录时，另一个事务又插入新的记录。InnoDB 引擎通过多版并发控制解决了幻读问题。MySql默认事务隔离级别是可重复读，InnoDB通过使用MVVC获得高并发，并使用next-key-locking的策略来避免幻读。
SERIALIZABLE(可串行化) 是最高的隔离级别，强制事务串行，避免了之前说的幻读问题。会在每一行数据上加上行级锁，缺点是会导致大量的超时和锁征用的情况。很少会用到这个隔离级别，只有在确保数据的一致性和接受没有并发，才可以考虑用该级别。

2、索引

索引是数据库中用来提高性能的常用工具，他是帮助MySql中的一种数据结构。这种数据结构以某种方式引用数据，可以在这种数据结构上实现某种高级算法。
百度百科定义：索引是对数据库表中一个或多个列的值进行排序的结构。

2.1 种类

数据库的索引好比一本书前面的目录，能够加快数据库的查询速度。索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不是。聚簇索引主要是用来提高多行检索的速度，而非聚簇索引对于单行的检索很快。根据数据库的功能可以分为三种索引，主要为以下几种：

唯一索引：不允许数据库中任何两行具有相同索引值的索引
主键索引：数据库表中可以用一列或多列组合唯一标识表的每一行，该组合列称为主键。
聚集索引：一个表中只能包含一个聚集索引，聚集索引和表的内容是在一起的，通常只要查询到聚集索引，就能查询到数据库的内容。非聚集索引则需要多个地方查询。

MyISAM 和 InnoDB 存储引擎的表默认创建的都是BTREE 索引。

2 .2 原因

为什么需要使用索引？
答：传统的数据库中查询是顺序查找，这种查找的时间复杂度是O(n)。这种时间复杂度对于海量的数据来说，查询效率太慢了。已知查询方法有二分查找和二叉树查找，这些查找方法都比顺序查找效率高。但是这些查找方法都需要专门的数据结构，所以想要使用这些查找办法得需要设立一个特定的数据结构，这种数据结构以某种特定方式引用数据，就是我们说的索引。

2.3 BTree 索引

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

拓展：系统从磁盘读取数据到内存是以磁盘块为基本单位的，在同一个磁盘块种的数据会被一次性读取出来。InnoDB 引擎中有页的概念，页是磁盘管理的最小单位。InnoDB存储引擎默认每个页的大小为16kb。已知的是磁盘块存储空间并没有16kb这么大，一般MySql页中则会存储多个磁盘块的连续地址。所以现在可以用特定的树结构存储磁盘块的连续地址。

一颗m阶的B-tree树的特性：

每个节点最多有m个孩子。
除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
若根节点不是叶子节点，则至少有2个孩子。
所有叶子节点都在同一层，且不包含其它关键字信息。
每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
ki(i=1,…n)为关键字，且关键字升序排序。
Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)。

下图是一个三阶的B-Tree树：

B+Tree
则是B-Tree树的升级版本，它的特征是所有数据记录节点都是按照键值大小顺序放在同一层的叶子节点。即将真正的数据放在数据节点，非页子节点上只存储key值信息。特点是所有的叶子节点之间都有一个链指针。

拓展：聚簇索引和非聚簇索引都使用B+Tree结构，但是在聚簇索引中叶子节点存储的是数据，非聚簇索引中叶子节点存储的是数据记录的地址。MyISAM就是采用的非聚簇索引，InnoDB采用的是聚簇索引的方式。MyISAM的主索引和次索引结构相同，只是主索引要求key是唯一的，次索引是没有要求的。InnoDB表数据文件本身就是主索引，次索引存储相应的主键值。