写在前面

MySQL有多种存储引擎，因此选择合适的存储引擎可以解决一些特殊问题。

初始化数据库

MySQL安装完成后，将会在其data目录下自动创建几个必需的数据库，可以使用SHOW DATABASES;语句来查看当前所有存在的数据库，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| quotes             |
| sys                |
| test_db            |
+--------------------+
6 rows in set (0.22 sec)

可以看到，数据库列表中包含了6个数据库，这些都是MySQL必须的，它描述用户的访问权限，用户经常利用test_db数据库做测试相关工作。

数据库管理

创建数据库是在系统磁盘上划分一块区域用于数据的存储和管理，如果管理员在设置权限的时候为用户创建了数据库，则可以直接使用，否则需要自己创建数据库。MySQL创建数据库的基本SQL语法格式为：

1
CREATE DATABASE database_name;

例子1：创建测试数据库test_db，使用的命令为CREATE DATABASE test_db;。之后可以使用SHOW CREATE DATABASE database_name;声明来查看数据库的定义：

1
2
3
4
5
6
7
mysql> SHOW CREATE DATABASE test_db;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| test_db  | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.35 sec)

可以看到，如果数据库创建成功，将显示数据库的创建信息。

删除数据库

删除数据库是将已经存在的数据库从磁盘空间上清除，清除之后，数据库中的所有数据也将一同被删除。MySQL删除数据库的基本SQL语法格式为：

1
DROP DATABASE database_name;

database_name为要删除的数据库的名称，如果指定的数据库不存在，则删除出错。

例子2：删除测试数据库test_db，使用的命令为DROP DATABASE test_db;。该语句执行完成后，数据库test_db将被删除，再次使用SHOW CREATE DATABASE database_name;声明来查看数据库的定义，结果如下:

1
2
3
4
mysql> SHOW CREATE DATABASE test_db\G;
ERROR 1049 (42000): Unknown database 'test_db'
ERROR:
No query specified

可以看到执行结果抛出一条错误，其实就是告知用户数据库test_db已经不存在，被删除了。

  • 使用DROP DATABASE命令时需要注意，执行该命令时MySQL不会给出任何提醒确认信息，且执行该语句后，数据库中存储的所有数据表和数据也将被一同删除，且不能恢复。

    数据库存储引擎

    数据库存储引擎是数据库底层软件组件，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供了不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还能获得特定的功能，现在的数据库都支持不同的数据引擎，MySQL的核心就是存储引擎。

    存储引擎简介

    MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

MySQL5.7支持的存储引擎有：InnoDB，MyISAM，Memory，Merge，Archive，Federated，CSV，BLACKHOLE，PERFORMANCE_SCHEMA等。可以使用SHOW ENGINES语句来查看系统所支持的引擎类型，结果如下：

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
65
66
67
68
mysql> SHOW ENGINES\G;
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
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: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
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: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

ERROR:
No query specified

上面的Support列的值表示某种引擎是否能使用，Yes表示可以使用，No表示不能使用，DEFAULT表示该引擎为当前默认存储引擎。

存储引擎对比

InnoDB存储引擎

InnoDB是事务数据库首选引擎，支持事务安全表（ACID），支持行锁定和外键。自5.5.5版本后，InnoDB为默认存储引擎，特性如下：

（1）、InnoDB给MySQL提供了具有提交、回滚、和崩溃恢复能力的事务安全存储引擎（兼容ACID）。InnoDB锁定在行级且也在SELECT语句中提供一个类似Oracle的非锁定读。在SQL查询中可自由将InnoDB类型的表与其他MySQL的表的类型混合起来，亦可在同一个查询中混用。

（2）、InnoDB是为处理巨大数据量的最大性能设计，CPU效率远超基于磁盘的关系型数据库引擎。

（3）、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓存池。InnoDB将它的表和索引存在一个逻辑表空间中，表空间可以包含数个文件。

（4）InnoDB支持外键完整性约束。存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6B的ROWID，并以此作为主键。

（5）InnoDB被用在众多需要高性能的大型数据库站点上。InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

MyISAM存储引擎

MyISAM是基于ISAM的扩展存储引擎。它是在Web、数据存储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。5.5.5之前版本是默认引擎。特性如下：

（1）、大文件（达63位文件长度）在支持大文件的文件系统和操作系统上被支持。

（2）、每个MyISAM表最大索引数是64，但可通过重新编译来改变，每个索引最大的列数是16个。

（3）、最大的键长度是1000B，但可通过重新编译来改变。

（4）、BLOB和TEXT列可以被索引。

（5）、NULL值被允许在索引的列中，这个值占每个键的0-1个字节。

（6）、所有数字键值以高字节优先被存储以允许一个更高的索引压缩。

（7）、每表一个AUTO_INCREMENT列的内部处理。MyISAM为insert和update操作自动更新这一列。

（8）、可以把数据文件和索引文件放在不同的目录。

（9）、每个字符列可以有不同的字符集。

（10）、有varchar的表可以固定或动态记录长度。

使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表的名字开始，扩展名指出文件类型：frm文件存储表定义；数据文件的扩展名为.MYD（MYData）,索引文件的扩展名是.MYI（MYIndex）。

MEMORY存储引擎

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

（1）、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500B的最大键长度。

（2）、MEMORY存储引擎执行HASH和BTREE索引。

（3）、可以在一个MEMORY表中有非唯一键。

（4）、MEMORY表使用一个固定的记录长度格式。

（5）、MEMORY不支持BLOB或TEXT列。

（6）、MEMORY支持AUTO_INCREMENT列和可包含NULL值的列的索引。

（7）、MEMORY表在所有客户端之间共享（就像其他任何非TEMPORARY表）。

（8）、MEMORY表内容被存在内存中，内存是MEMORY表和服务器在查询处理时的空闲中创建的内部表共享。

（9）、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行delete from或truncate table，或者删除整个表（drop table）。

几种数据库引擎对照

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

使用哪一种引擎要根据需要灵活选择，一个数据库中的多个表可以使用不同的引擎以满足各种需求。使用合适的存储引擎将会提高整个数据库的性能。

Q&A

（1）如何查看默认的存储引擎？

前面介绍了开发者可以使用SHOW ENGINES语句来查看系统中所有的存储引擎，其中包含默认的存储引擎，其实还可以使用一种直接的方法来查看默认的存储引擎，可以使用SHOW VARIABLES LIKE 'default_storage_engine';，执行结果如下：

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set, 1 warning (0.13 sec)

执行结果直接显示了当前默认的存储引擎为InnoDB。请注意default_storage_engine表示永久表(permanent tables)的默认存储引擎，而default_tmp_storage_engine表示临时表的默认存储引擎。

（2）如何修改默认的存储引擎？

这里提供两种方式用于修改MySQL默认的存储引擎：修改my.cnf文件和修改系统变量的方式。

方法1：修改my.cnf文件。开发者只需要在my.cnf配置文件中新增参数default_storage_engine，然后重启数据库服务即可。如

1
2
[mysqld]
default_storage_engine=MyISAM

之后使用mysql network restart命令来重启数据库服务，接着使用show engines;就能查看到默认的存储引擎为MyISAM了。

方法2：修改系统变量。开发者还可以使用命令来修改系统变量，使用的命令为SET default_storage_engine=InnoDB;，之后使用show engines;就能查看到默认的存储引擎为InnoDB了。

  • 请注意这个系统变量default_storage_engine是BOTH(全局和临时)，且可以动态进行修改。但是要注意，即使你修改了系统变量default_storage_engine，重启过后就会失效，如果开发者想要永久修改，最好还是通过第一种方式来修改my.cnf配置文件。

（3）如何查看表使用的存储引擎？

这里提供3种方法来查看表所使用的存储引擎，具体操作如下：

方法1： 查询information_schema.TABLES表。请注意由于information_schema.TABLES表中包含21个字段，分别是：TABLE_CATALOG、TABLE_SCHEMA、 TABLE_NAME 、TABLE_NAME 、ENGINE、VERSION、 ROW_FORMAT、TABLE_ROWS、AVG_ROW_LENGTH、DATA_LENGTH、MAX_DATA_LENGTH、INDEX_LENGTH、DATA_FREE、AUTO_INCREMENT、CREATE_TIME、UPDATE_TIME、CHECK_TIME、TABLE_COLLATION、 CHECKSUM、CREATE_OPTIONS和TABLE_COMMENT。通常选择其中的4项：TABLE_SCHEMA、TABLE_NAME 、TABLE_NAME 和ENGINE即可：

1
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_NAME,ENGINE from information_schema.TABLES where TABLE_NAME = 'database_name';

【eg3】使用如下命令先给test_db数据库创建数据表test_table：

1
2
3
4
create table test_table(
id int not null primary key auto_increment,
name varchar(225)
)engine=innodb,default charset =utf8;

然后使用如下命令来查询test_table的存储引擎信息：

1
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_NAME,ENGINE from information_schema.TABLES where TABLE_NAME = 'test_table';

可以看到输出结果为：

1
2
3
4
5
6
7
mysql> SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_NAME,ENGINE from information_schema.TABLES where TABLE_NAME = 'test_table';
+--------------+------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_NAME | ENGINE |
+--------------+------------+------------+--------+
| test_db      | test_table | test_table | InnoDB |
+--------------+------------+------------+--------+
1 row in set (0.00 sec)

方法2： 使用SHOW CREATE TABLE table_name \G;命令。例如使用SHOW CREATE TABLE test_table \G;来查看该test_table数据表的创建信息：

1
2
3
4
5
6
7
8
9
mysql> SHOW CREATE TABLE test_table \G;
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(225) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.35 sec)

方法3： 使用SHOW TABLE STATUS WHERE NAME = ‘table_name’;命令。例如使用SHOW TABLE STATUS WHERE NAME = 'table_name';来查看该table_name数据表的相关信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> SHOW TABLE STATUS WHERE NAME = 'test_table'\G;
*************************** 1. row ***************************
           Name: test_table
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2020-06-16 10:56:39
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

当然也可以使用SHOW TABLE STATUS FROM 'database_name' WHERE NAME = 'table_name';，但是这种必须指定数据库信息，上面是已经使用了USE test_db;命令选中了待操作的数据库。

（4）如何修改相关表的存储引擎？

修改表的存储引擎非常简单，只需使用ALTER关键词，所构成的命令为ALTER TABLE 'table_name' ENGINE= InnoDB;，如修改test_table表的引擎为MyISAM，则使用的命令为：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> ALTER TABLE test_table ENGINE=MyISAM;
Query OK, 0 rows affected (0.71 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test_table\G;
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(225) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

（5）如何在创建表的时候指定存储引擎？

其实这个笔者已经在前面提到过，在创建test_table的时候，笔者就是在创建该表的时候就同时指定了存储引擎。其实也是非常简单，只需要指定ENGINE参数即可：

1
2
3
4
create table test_table(
id int not null primary key auto_increment,
name varchar(225)
)engine=InnoDB,default charset =utf8;

那么这样，关于数据库引擎就先学习到这，后期学习其他知识。