【超简单】MySQL存储引擎的选择与配置

存储引擎简介

MySQL中的数据用各种不同的技术存储在文件(或内存)中。每一种技术都使用不同的存储机构,索引技巧,锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,能够获得额外的速度或者功能,从而改善应用的整体功能。这些不同的技术以及配套的相关功能在MySQL中被称为存储引擎。

下面就为大家介绍两种MySQL中比较常见的两种存储引擎:MyISAM和InnoDB

MyISAM存储引擎

MyISAM存储引擎是MySQL关系数据库系统5.5版本之前默认的存储引擎,它的前身是ISAM,ISAM执行读取操作的速度很快,而且不占用大量的内存和存储资源。但是也有他的不足之处:①、不支持事务处理;②、不能容错,即如果硬盘崩溃了,那么数据文件就无法修复了,除非经常备份所有的实时数据,通过其复制特性,MySQL能够支持这样的备份应用程序了。

MyISAM存储引擎的特点

  1. 不支持事务,需要事务支持的系统不能使用MyISAM作为存储引擎
  2. 表级锁定形式,数据在更新时锁定整个表
  3. 数据库在读写过程中相互阻塞,即读取时不能写入,写入时不能读取
  4. 可以通过key_buffer_size来设置缓存索引,提高访问的性能,减少磁盘IO(读写)的压力
  5. MyISAM存储引擎不支持外键约束,只支持全文索引
  6. 每个MyISAM在磁盘上存储成三个文件:

.frm 文件存储表定义

.MYD(MYData) 数据文件

.MYI(MYIndex) 索引文件

基于MyISAM这样的特性,所以MyISAM主要适用于:一些非高并发的读写不需要同时进行的场所;如:电子商城。。。

InnoDB存储引擎

InnoDB是为了处理巨大数据量时的最大性能而设计的。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。

InnoDB存储引擎的特点

  1. 支持事务,支持四个事务隔离级别
  2. 行级锁定,但是全表扫描仍然会是表级锁定
  3. 读写阻塞与事务隔离级别相关
  4. 具有非常高效的缓存特性,能缓存索引,也能缓存数据
  5. 表与主键以簇的方式存储,即存在外键约束
  6. 支持分区、表空间,类似Oracle数据库

基于InnoDB的特性,所以InnoDB主要适用于:论坛、微博、银行等高并发的场所

企业选择存储引擎的依据

  1. 存储引擎支持的字段和数据类型
  2. 锁定类型
  3. 索引的支持
  4. 事务处理的支持

数据库及表的存储引擎的查看

1. 查看数据库目前使用的存储引擎

mysql> use school;    //进入school数据库
Database changed
mysql> show engines;     //查看school默认使用的存储引擎,这里因为我使用的是MySQL5.7版本的,所以默认是InnoDB

   引擎类型          是否能使用                                                                  是否支持事务           表示为当前默认的引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

2. 查看表正在使用的存储引擎

①、直接使用show table status;图示如下

mysql> mysql> show table status from school where name='info';
+------+--------+---------+------------+------+
| Name | Engine | Version | Row_format | Rows |
+------+--------+---------+------------+------+
| info | InnoDB |      10 | Dynamic    |    1 |                 
+------+--------+---------+------------+------+
1 row in set (0.00 sec)
//省略部分内容

这里可以看到info使用的存储引擎是InnoDB

②、进入表所在的数据库,然后使用show create 命令;图示如下

mysql> use school;

Database changed
mysql> show create table info;
+-------+--------------------------------------+
| Table | Create Table                         |
+-------+--------------------------------------+
| info  | CREATE TABLE "info" (
  "name" char(10) DEFAULT NULL,
  "score" decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------+
1 row in set (0.00 sec)

这里也可以直接看到info表的存储引擎是InnoDB;

数据库存储引擎的修改

这里也有四种方法进行修改

①、使用alter table命令修改,(这里只针对现有的表或者数据库)图示如下

mysql> use school;     //进入school数据库
Database changed
mysql> show create table info;        //查看当前使用的存储引擎
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| info  | CREATE TABLE "info" (
  "name" char(10) DEFAULT NULL,
  "score" decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |      //这里可以看到默认使用的InnoDB类型的
+-------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table info engine=MyISAM;      //使用alter table 命令进行修改
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table info;      //再次查看info表使用的存储引擎类型
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| info  | CREATE TABLE "info" (
  "name" char(10) DEFAULT NULL,
  "score" decimal(5,2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |     //这里可以看到存储引擎就被修改为MyISAM类型了
+-------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

②、修改MySQL的配置文件/etc/my.cnf,指定default-storage-engine选项设置默认的存储引擎 (这里针对的是后续新创建的表)

[root@localhost ~]# vim /etc/my.cnf
...省略
[mysqld]
...省略
pid-file = /usr/local/mysql/mysqld.pid
default-storage-engine=MyISAM       //添加指定默认存储引擎类型MyISAM
socket = /usr/local/mysql/mysql.sock
...省略
修改了配置文件后需要重启MySQL服务

> [root@localhost ~]# systemctl restart mysqld.service

重新启动MySQL服务后,我们再次进入数据库,然后创建一个新的表,在查看存储引擎

mysql> create database test;       //创建一个新的数据库test
Query OK, 1 row affected (0.01 sec)

mysql> use test;       //进入test数据库
Database changed
mysql> create table test (id int);        //创建一个test表
Query OK, 0 rows affected (0.01 sec) 

mysql> show create table test;      //查看test表的存储引擎
+-------+----------------------------+
| Table | Create Table               |
+-------+----------------------------+
| test  | CREATE TABLE "test" (
  "id" int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |      //这里可以看到存储引擎就是MyISAM  
+-------+----------------------------+
1 row in set (0.03 sec)

③、使用create table 创建表时用engine指定默认的存储引擎 (创建新表时自己指定)

mysql> use school;

Database changed
mysql> create table zyc (id int) engine=InnoDB;    //创建一个新表zyc并且指定存储引擎为InnoDB
Query OK, 0 rows affected (0.02 sec)

mysql> show create table zyc;        //查看zyc的存储引擎
+-------+------------------------------+
| Table | Create Table                 |
+-------+------------------------------+
| zyc   | CREATE TABLE "zyc" (
  "id" int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |   //这里可以看到存储引擎为InnoDB
+-------+------------------------------+
1 row in set (0.00 sec)

④、使用mysql_convert_table_format命令批量转换存储引擎,命令格式如下:

mysql_convert_table_format --user=root --password=密码 --socket=/temp/mysql.sock --engine=引擎 库名 表名

由于博主使用的是MySQL5.7版本,而这条命令只有MySQL5.5才有,所以这里就不详细介绍了,如果有看官感兴趣,可以自己安装一个MySQL5.5尝试下。

MySQL的存储引擎到这里就介绍完了,后续请期待:MySQL增量备份、MySQL主从同步、MySQL读写分离