MySQL知识（十六）——存储引擎

1 数据库存储引擎

1.1 MySQL存储引擎简介

　　MySQL可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据操作。
　　不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。

1.2 简单相关知识点

1.2.1 并发处理

　　并发控制：当多个连接对记录进行修改时保证数据的一致性和完整性。
　　假如有两个用户正在操作一个数据表，A用户读取id=22的记录，B用户删除id=22的记录，那么有可能A用户操作报错或者读取的记录不正确。
　　在解决并发读或写操作时，系统会使用锁系统来解决。锁分为两种：
　　①共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
　　②排它锁（写锁）：在任何时候只能只有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。
　　锁颗粒：
　　①表锁，是一种开销最小的锁策略。
　　②行锁，是一种开销最大的锁策略。
如果存在一张表，把整张表锁定起来，所有用户将无法操作，也就是说这张表只能有一个锁；行锁，是因为数据表中有多少条记录就要多少个锁，所以开销大。

1.2.2 事务处理

　　事务：事务用于保证数据库的完整性。
　　例如：A用户转账200元到B用户，则有两个步骤，第一，从当前账户减掉200元（账户余额大于等于200元）；第二，在对方账户增加200元。
　　两个步骤应该作为一个整体出现，无论两个过程的哪一个环节出现问题，都应该恢复到数据的原始状态，这就是一个事务。
　　事务的特性：ACID，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

1.2.3 外键和索引

　　外键：保证数据一致性的策略。
　　索引：对数据表中一列或多列的值进行排序的一种结构。

1.3 存储引擎的对比

（1）查看系统所支持的引擎类型：

 mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set

（2）存储引擎的选择

功能	MyISAM	Memory	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	NO	NO	YES	NO
锁颗粒	表锁	表锁	行锁	行锁
支持全文索引	YES	NO	NO	NO
支持数索引	YES	YES	YES	NO
支持哈希索引	NO	YES	NO	NO
支持数据缓存	NO	N/A	YES	NO
支持外键	NO	NO	YES	NO

　　如果要提供提交，回滚和崩溃恢复能力的事务安全（ACID兼容）能力，并要求是想并发控制，InnoDB是个很好的选择；
　　如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率；
　　如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果；
　　如果只有INSERT和SELECT操作，可以选择Archive引擎，Archive引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive存储引擎非常适合存储归档数据，如记录日志信息可以使用Archive引擎。

1.4 修改默认存储引擎

（１）查看默认存储引擎：

SHOW ENGINES;
显示的存储引擎中，有default字段即是。

SHOW VARIABLES LIKE 'storage_engine';
直接显示当前默认存储引擎。

（２）Windows设置默认引擎
　　方法一：
　　修改配置文件my.ini：首先打开my.ini，将[mysqld]字段下面的default-storage-engine参数后面的值改为相应存储引擎名，保存文件，重新启动MySQL即可。

　　方法二：
　　通过创建数据表命令实现：

CREATE TABLE table_name(
...
...
)ENGINE=engine_name;

例如：

mysql> CREATE TABLE tp1(
    -> id INT
    -> )ENGINE = MyISAM;
Query OK, 0 rows affected

mysql> SHOW CREATE TABLE tp1;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| tp1   | CREATE TABLE `tp1` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
1 row in set

　　方法三：
　　通过修改数据表命令实现：

ALTER TABLE table_name ENGINE[=] engine_name;

例如：

mysql> ALTER TABLE tp1 ENGINE = InnoDB;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tp1;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| tp1   | CREATE TABLE `tp1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
1 row in set

1.5 常用的两个存储引擎