MySQL入门学习笔记之存储引擎

534523

1、什么是存储引擎？

存储引擎就是指表的类型，数据库的存储引擎决定了表在计算机中的存储方式。而存储引擎的概念是MySQL的特点，是一种插入式的存储引擎概念。MySQL数据库中的表可以使用不同的方式存储。

2、如何查看MySQL 中支持的存储引擎？

       在命令行中输入： SHOW ENGINES;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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 (0.00 sec)

        在命令后面通过参数 ‘\G’可以调整数据的输出格式，使得显示更加直观。

        mysql> SHOWENGINES \G

mysql>SHOW ENGINES \G

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64

*************************** 1. row ***************************       Engine: FEDERATED      Support: NO      Comment: Federated MySQL storage engine Transactions: NULL           XA: NULL   Savepoints: NULL *************************** 2. row ***************************       Engine: MRG_MYISAM      Support: YES      Comment: Collection of identical MyISAM tables Transactions: NO           XA: NO   Savepoints: NO *************************** 3. row ***************************       Engine: MyISAM      Support: YES      Comment: MyISAM storage engine Transactions: NO           XA: NO   Savepoints: NO *************************** 4. row ***************************       Engine: BLACKHOLE      Support: YES      Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO           XA: NO   Savepoints: NO *************************** 5. row ***************************       Engine: CSV      Support: YES      Comment: CSV storage engine Transactions: NO           XA: NO   Savepoints: NO *************************** 6. row ***************************       Engine: MEMORY      Support: YES      Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO           XA: NO   Savepoints: NO *************************** 7. row ***************************       Engine: ARCHIVE      Support: YES      Comment: Archive storage engine Transactions: NO           XA: NO   Savepoints: NO *************************** 8. row ***************************       Engine: InnoDB      Support: DEFAULT      Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES           XA: YES   Savepoints: YES *************************** 9. row ***************************       Engine: PERFORMANCE_SCHEMA      Support: YES      Comment: Performance Schema Transactions: NO           XA: NO   Savepoints: NO 9 rows in set (0.00 sec)

查询结果参数说明：

         Engine参数：表示存储引擎的名称

         Support参数：表示是否支持该类型的存储引擎，YES表示支持，NO表示不支持。

         Comment参数：对该引擎的一些描述。

        Transactions 参数：表示是否支持事务处理，YES表示支持，NO不支持。

XA参数：表示是否遵循分布式交易处理的XA规范，YES 支持，NO不支持。

Savepoints参数：表示是否支持保存点，可以使事务回滚到保存点，yes表示支持。

其它方法查询：

      mysql> SHOW  VARIABLES LIKE 'have%'

查询结果如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql>SHOW VARIABLES LIKE 'have%'; +----------------------+----------+ | Variable_name        | Value    | +----------------------+----------+ | have_compress        | YES      | | have_crypt           | NO       | | have_dynamic_loading | YES      | | have_geometry        | YES      | | have_openssl         | DISABLED | | have_profiling       | YES      | | have_query_cache     | YES      | | have_rtree_keys      | YES      | | have_ssl             | DISABLED | | have_symlink         | YES      | +----------------------+----------+ 10 rows in set (0.00 sec)

参数说明：Variable_name表示存储引擎的名称， Value表示MySQL的支持情况

YES 表示支持,NO表示不支持，DISABLED 表示支持但没有开启。

查看默认支持的存储引擎：

Mysql> SHOW VARIABLE LIKE 'storage_engine';

1 2 3 4 5 6 7

mysql>SHOW VARIABLES LIKE 'storage_engine'; +----------------+--------+ | Variable_name  | Value  | +----------------+--------+ | storage_engine | InnoDB | +----------------+--------+ 1 row in set (0.00 sec)

从输出结果中看出，本版本中用的默认存储引擎为InnoDB

注意：本实验中用到的mysql版本为5.6，在使用5.7的版本输入该命令时，发现

不能输出，出现警告信息，原因未知。

可以 通过修改MySQL的配置文件，来修改MySQL中默认的存储引擎，在my.ini文件

中将"default-storage-engine=INNODB"改为“default-storage-engine=MyISAM”,修改完成后

需要重启一下服务，修改才能生效。

3、InnoDB存储引擎

InnoDB 是MySQL数据库中的默认的存储引擎，InnoDB给MySQL表提供了事务、回滚、崩溃修

复能力 和多版本并发控制的事务安全 。

特点：

        1> 支持自增长列AUTO_INCREMENT ,自增长列不能为空，而且值必须唯一，在MySQL中规定自增长列必须为主键。

      2> 支持外键FOREIGN KEY 外键所在的表为子表，外键所依赖的表为父表。父表中被子表

外键关联 的字段必须为主键。当删除、更新父表中的某条信息时，子表也必须有相应的改变。

      3> InnoDB存储引擎创建的表的表结构存储在.frm文件中。数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中。

优点：提供良好的事务管理、崩溃修复能力和并发控制。

缺点： 读写效率稍差，占用的数据空间相对较大。

4、 MyISAM存储引擎

特点：MyISAM存储引擎的表存储为3个文件。文件名与表名相同。扩展名包括frm、MYD、

MYI 。frm为扩展名的文件存储表的结构，MYD为扩展名的文件存储数据，MYI为扩展名的文件存储索引。

优点：占用空间小。处理速度快。

缺点：不支持事务的完整性和并发性。

5、MEMORY 存储引擎

    MEMORY存储引擎是存储在内存中的内容来创建表，所有的数据都是放在内存中的。每个MEMORY存储引擎的表对应一个磁盘文件。文件名与表名相同，类型为frm 该文件只存储表的结构。数据文件是存储在内存当中的，有利于数据的快速处理，提高整个表的处理效率。

6、InnoDB 、MyISAM、MEMORY三种存储引擎的对比

表1 3种存储引擎的对比

特性	InnoDB	MyISAM	MEMORY
事务安全	支持	无	无
存储限制	64TB	有	有
空间使用	高	低	低
内存使用	高	低	高
插入数据的速度	低	高	高
对外键的支持	支持	无	无