一：存储引擎概述
1：什么是存储引擎
数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。
2：MySQL 5.7 支持的存储引擎
MySQL 支持多种类型的数据库引擎，可分别根据各个引擎的功能和特性为不同的数据库处理任务提供各自不同的适应性和灵活性。在 MySQL 中，可以利用 SHOW ENGINES 语句来显示可用的数据库引擎和默认引擎。
MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。
MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE 等。可以使用SHOW ENGINES语句查看系统所支持的引擎类型
3：如何选择 MySQL 存储引擎
不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。
| 功能 | MylSAM | 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 |
提示：
- InnoDB 事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL 5.5.5 之后，InnoDB 作为默认存储引擎。
- MyISAM 是基于 ISAM 的存储引擎，并对其进行扩展，是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。
- MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他数据提供快速访问。
4：可以根据以下的原则来选择 MySQL 存储引擎
- 如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
- 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
- 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
- 如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。
提示：使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。
5：MySQL 默认存储引擎
InnoDB 是系统的默认引擎，支持可靠的事务处理。
使用下面的语句可以修改数据库临时的默认存储引擎
SET default_storage_engine=< 存储引擎名 >
二：操作存储引擎
1：查看mysql支持的存储引擎
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
2：MyISAM 存储引擎
MyISAM 存储引擎不支持事务，也不支持外键，特点是访问速度快，对事务完整性没有要求，以 SELECT、INSERT 为主的应用基本都可以使用这个引擎来创建表。
每个 MyISAM 表在磁盘上存储成 3 个文件，其中文件名和表名都相同，但是扩展名分别为：
（1）frm(存储表定义)
（2）MYD(MYData，存储数据)
（3）MYI(MYIndex，存储索引)
MyISAM 表还支持 3 种不同的存储格式：
（1）静态(固定长度)表
（2）动态表
（3）压缩表
3：InnoDB 存储引擎
InnoDB 是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为
用户操作非常大的数据存储提供了一个强大的解决方案。MySQL 从 5.5.5 版本开始，默认
的存储引擎为 InnoDB。InnoDB 存储引擎还引入了行级锁定和外键约束，在以下场景中使
用 InnoDB 存储引擎是最理想的选择：
（1）更新密集的表：InnoDB 存储引擎特别适合处理多重并发的更新请求。
（2）事务：InnoDB 存储引擎是支持事务的标准 MySQL 存储引擎。
（3）自动灾难恢复：与其它存储引擎不同，InnoDB 表能够自动从灾难中恢复。
（4）外键约束：MySQL 支持外键的存储引擎只有 InnoDB。
（5）支持自动增加列 AUTO_INCREMENT 属性。
Innodb的数据文件：
ibd：数据表的数据文件
frm：数据表的元数据
opt：存储的是mysql的一些配置信息，如编码、排序的信息等
4：关于 MyISAM 与 InnoDB 选择使用
（1）如果应用程序一定要使用事务，毫无疑问要选择 InnoDB 引擎。但要注意，InnoDB
的行级锁是有条件的。在 where 条件没有使用主键时，照样会锁全表。比如 DELETE FROM
mytable 这样的删除语句。
（2）如果应用程序对查询性能要求较高，就要使用 MyISAM 了。MyISAM 索引和数据
是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于 InnoDB。
压缩后的索引也能节约一些磁盘空间
5：修改默认的存储引擎
（1）创建表，并查看默认用的存储引擎
mysql> create database auth;
mysql> use auth
mysql> CREATE TABLE t1 (user_name CHAR(16), user_passwd CHAR(48));
mysql> show table status from auth where name='t1'\G
（2）通过 alter table 修改
mysql> alter table t1 engine=MyISAM;
mysql> show table status from auth where name='t1'\G
（3）通过配置文件修改
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
#添加下面语句
default-storage-engine=MyISAM
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -ppwd123
mysql> use auth
mysql> CREATE TABLE t2 (user_name CHAR(16), user_passwd CHAR(48));
mysql> show table status from auth where name='t2'\G
注意：
通过配置文件修改的方法，对以前的老表不会有影响，只会影响以后新创建的表，并且在创建表时没有指定存储引擎的情况。
（4）通过 create table 创建表时指定存储引擎
mysql> use auth
mysql> create table t3 (id int(10),name char(20)) engine=INNODB;
mysql> show table status from auth where name='t3'\G
（5）临时修改默认的存储引擎
SET default_storage_engine=< 存储引擎名 >
三：MyISam和InnoDB实例比较
这里我选择两个比较重点的存储引擎实验下速度之类的性能，对比一下看看。
1：创建两张表分别以MyIsam和InnoDB作为存储引擎。
create database test;
use test;
create table tm(id int(20) primary key auto_increment,name char(30)) engine=myisam;
create table ti(id int(20) primary key auto_increment,name char(30)) engine=innodb;
mysql> show create table tm\G
mysql> show create table ti\G
两张表内容是一致的但是存储引擎不一样，下面我们从插入数据开始进行测试比较。
2：插入一千万数据，来比较两个存储引擎的存储效率
这里当然不能手动的插入，创建一个存储过程插入一百万的数据。
（1）设置sql语句结束符
mysql> delimiter $
备注：
delimiter $语句是设置sql语句的结束符为“$”
（2）创建两个存储过程
mysql> create procedure insertm()
begin
set @i=1;
while @i<=10000000
do
insert into tm(name) values(concat("wy",@i));
set @i=@i+1;
end while;
end
$
mysql> create procedure inserti()
begin
set @i=1;
while @i<=10000000
do
insert into ti(name) values(concat("wy",@i));
set @i=@i+1;
end while;
end
$
mysql> delimiter ;备注：
delimiter ;语句是设置sql语句的结束符为“;”
（3）利用存储过程向两个表添加数据
插入（一千万条）MyIsam存储引擎的表中的时间如下：
mysql> call insertm;
Query OK, 0 rows affected (1 min 49.74 sec)
插入（一千万条）InnoDB存储引擎的表中的时间如下：
mysql> call inserti;
Query OK, 0 rows affected (13 min 32.96 sec)
比较结果：
MyIsam存储引擎在写入方面有优势
3：查询数据总数目
下面是InnoDB的SQL语句的分析：
MariaDB [test]> desc select count(*) from ti\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ti
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 1000160
Extra: Using index
1 row in set (0.00 sec)
下面是MyIsam（他的数据存储在其他的表中所以这里是没有影响行数的）的SQL语句的分析：
riaDB [test]> desc select count(*) from tm\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
1 row in set (0.00 sec)
MyIsam使用专门的MYD表存储数据，所以这里没有查询结果
MyISAM存储引擎会是数据表生成3个文件：
frm文件存储表的定义、MYD文件是数据文件、MYI文件是索引文件
4：查询某一范围的数据
（1）没有索引的列
MariaDB [test]> select * from tm where name>"wy100" and name <"wy10000000";
+---------+-----------+
| id | name |
+---------+-----------+
| 1000 | wy1000 |
| 10000 | wy10000 |
| 100000 | wy100000 |
| 1000000 | wy1000000 |
+---------+-----------+
4 rows in set (0.68 sec)
MariaDB [test]> select * from ti where name>"wy100" and name <"wy10000000";
+---------+-----------+
| id | name |
+---------+-----------+
| 1000 | wy1000 |
| 10000 | wy10000 |
| 100000 | wy100000 |
| 1000000 | wy1000000 |
+---------+-----------+
4 rows in set (2.71 sec)
MariaDB [test]> select * from tm where name="wy9999999";
+---------+-----------+
| id | name |
+---------+-----------+
| 9999999 | wy9999999 |
+---------+-----------+
1 row in set (0.42 sec)
MariaDB [test]> select * from ti where name="wy9999999";
+---------+-----------+
| id | name |
+---------+-----------+
| 9999999 | wy9999999 |
+---------+-----------+
1 row in set (2.38 sec)
无索引查询MyIsam有优势
（2）有索引的列
对于使用MyIsam存储引擎的表：
select * from tm where id>10 and id<999999;
执行时间：
9999988 rows in set (1.88 sec)
对于使用了InnoDB存储引擎的表：
select * from ti where id>10 and id<999999;
执行时间：
9999988 rows in set (0.65 sec)
有索引查询INNODB有优势
6656
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
