mysql: 数据库引擎和底层算法

查看数据库使用的是什么引擎

SHOW VARIABLES LIKE 'storage_engine';

Mysql默认存储引擎

InnoDB是Mysql的默认存储引擎(Mysql5.5.5之前是MyISAM，
可以参看：https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_default-storage-engine)

Mysql数据库引擎简单一览图

Mysql常见的三中存储引擎为InnoDB、MyISAM、MEMORY。其区别提现在事物安全、存储限制、空间使用、插入数据的速度和对外键的支持上，具体如下：

特性	InnoDB	MyISAM	MEMORY
事务	支持	不支持	不支持
外键	支持	不支持	不支持
存储	64TB	有	有
空间	高	低	低
内存	高	低	高
插入速度	低	高	高

1）事务安全：
InnoDB支持事物安全，MyISAM和MEMORY两个不支持。

2）对外键的支持：
InnoDB对外键支持情况较好，MyISAM和MEMORY两个不支持外键。

3）存储限制：
InnoDB有64TB的存储限制，MyISAM和MEMORY要是具体情况而定。

4）空间使用：
InnoDB对空间使用程度较高，MyISAM和MEMORY对空间使用程度较低。

5）内存使用：
InnoDB和MEMORY对内存使用程度较高，MyISAM对内存使用程度较低。

6）插入数据的速度：
InnoDB插入数据的速度较低，MyISAM和MEMORY插入数据的速度较高。

实际操作修改mysql默认引擎

Mysql默认是关闭InnoDB存储引擎的使用的，将InnoDB设置为默认的引擎如下。

1. 查看Mysql存储引擎情况： mysql>show engines，结果是： InnoDB | YES，说明此Mysql数据库服务器支持InnoDB引擎。
2. 设置InnoDB为默认引擎：在配置文件my.ini中的 [mysqld] 下面加入default-storage-engine=INNODB
3. 重启Mysql服务器
4. 登录Mysql数据库，mysql>show engines 如果出现 InnoDB |DEFAULT，则表示设置InnoDB为默认引擎成功

Mysql三种引擎的特点

1. InnoDB存储引擎
   InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），其它存储引擎都是非事务安全表，支持行锁定和外键，MySQL5.5以后默认使用InnoDB存储引擎。

InnoDB特点： 支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），
要求实现并发控制（比如售票），那选择InnoDB有很大的优势。
如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

2. MyISAM存储引擎
   MyISAM基于ISAM存储引擎，并对其进行扩展。
   它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。
   MyISAM拥有较高的插入、查询速度，但不支持事务，不支持外键。

MyISAM特点： 插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。
如果应用的完整性、并发性要求比较低，也可以使用。

3. MEMORY存储引擎
   MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

MEMORY特点： 所有的数据都在内存中，数据的处理速度快，但是安全性不高。
如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。
它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

对于InnoDB和MyISAM两个数据库引擎的选择

1）当需要使用数据库的事务时，InnoDB引擎就是首选。
由于锁的粒度小，写操作是不会锁定全表的。所以在并发度较高的场景下使用会提升效率的。

2）大容量的数据集时趋向于选择Innodb。因为它支持事务处理和故障的恢复。
Innodb可以利用数据日志来进行数据的恢复。主键的查询在Innodb也是比较快的。

3）如果表的读操作远远多于写操作时，并且不需要事务的支持的。
可以将MyIASM作为数据库引擎的首先。

4）大批量的插入语句时（这里是INSERT语句）在MyIASM引擎中执行的比较的快，
但是UPDATE语句在Innodb下执行的会比较的快，尤其是在并发量大的时候。

为什么用B+树而不是B树

比起B树，B+树 ①IO次数更少 ②查询性能很稳定 ③范围查询更简便

数据库引擎和索引的数据结构？都是B+树

对于MyIASM引擎来说，B+树的数据结构中存储的内容实际上是实际数据的地址值。
也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

而Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。
这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），
如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，
如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
并且和MyISAM不同，InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，
所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。
所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。
建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，
而不会频繁的分裂调整，会有效的提升插入数据的效率

其他

mysql其余不太常见的存储引擎如下：

1. BDB： 源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性
2. Merge ：将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用
3. Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差
4. Federated： 将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用
5. Cluster/NDB ：高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用
6. CSV： 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件。
   这是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。
7. BlackHole ：黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继