【超簡單】MySQL存儲引擎的選擇與配置

【超簡單】MySQL存儲引擎的選擇與配置

存儲引擎簡介

MySQL中的數(shù)據(jù)用各種不同的技術存儲在文件（或內存）中。每一種技術都使用不同的存儲機構，索引技巧，鎖定水平并且最終提供廣泛的不同的功能和能力。通過選擇不同的技術，能夠獲得額外的速度或者功能，從而改善應用的整體功能。這些不同的技術以及配套的相關功能在MySQL中被稱為存儲引擎。

下面就為大家介紹兩種MySQL中比較常見的兩種存儲引擎：MyISAM和InnoDB

MyISAM存儲引擎

MyISAM存儲引擎是MySQL關系數(shù)據(jù)庫系統(tǒng)5.5版本之前默認的存儲引擎，它的前身是ISAM，ISAM執(zhí)行讀取操作的速度很快，而且不占用大量的內存和存儲資源。但是也有他的不足之處：①、不支持事務處理；②、不能容錯，即如果硬盤崩潰了，那么數(shù)據(jù)文件就無法修復了，除非經(jīng)常備份所有的實時數(shù)據(jù)，通過其復制特性，MySQL能夠支持這樣的備份應用程序了。

MyISAM存儲引擎的特點

1. 不支持事務，需要事務支持的系統(tǒng)不能使用MyISAM作為存儲引擎
2. 表級鎖定形式，數(shù)據(jù)在更新時鎖定整個表
3. 數(shù)據(jù)庫在讀寫過程中相互阻塞，即讀取時不能寫入，寫入時不能讀取
4. 可以通過key_buffer_size來設置緩存索引，提高訪問的性能，減少磁盤IO（讀寫）的壓力
5. MyISAM存儲引擎不支持外鍵約束，只支持全文索引
6. 每個MyISAM在磁盤上存儲成三個文件：

.frm 文件存儲表定義

.MYD(MYData) 數(shù)據(jù)文件

.MYI(MYIndex) 索引文件

基于MyISAM這樣的特性，所以MyISAM主要適用于：一些非高并發(fā)的讀寫不需要同時進行的場所;如：電子商城。。。

InnoDB存儲引擎

InnoDB是為了處理巨大數(shù)據(jù)量時的最大性能而設計的。它的CPU效率可能是任何其他基于磁盤的關系數(shù)據(jù)庫引擎所不能匹敵的。

InnoDB存儲引擎的特點

1. 支持事務，支持四個事務隔離級別
2. 行級鎖定，但是全表掃描仍然會是表級鎖定
3. 讀寫阻塞與事務隔離級別相關
4. 具有非常高效的緩存特性，能緩存索引，也能緩存數(shù)據(jù)
5. 表與主鍵以簇的方式存儲，即存在外鍵約束
6. 支持分區(qū)、表空間，類似Oracle數(shù)據(jù)庫

基于InnoDB的特性，所以InnoDB主要適用于：論壇、微博、銀行等高并發(fā)的場所

企業(yè)選擇存儲引擎的依據(jù)

1. 存儲引擎支持的字段和數(shù)據(jù)類型
2. 鎖定類型
3. 索引的支持
4. 事務處理的支持

數(shù)據(jù)庫及表的存儲引擎的查看

1. 查看數(shù)據(jù)庫目前使用的存儲引擎

mysql> use school;    //進入school數(shù)據(jù)庫
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

②、進入表所在的數(shù)據(jù)庫，然后使用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；

數(shù)據(jù)庫存儲引擎的修改

這里也有四種方法進行修改

①、使用alter table命令修改，（這里只針對現(xiàn)有的表或者數(shù)據(jù)庫）圖示如下

mysql> use school;     //進入school數(shù)據(jù)庫
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選項設置默認的存儲引擎 （這里針對的是后續(xù)新創(chuàng)建的表）

[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服務后，我們再次進入數(shù)據(jù)庫，然后創(chuàng)建一個新的表，在查看存儲引擎

mysql> create database test;       //創(chuàng)建一個新的數(shù)據(jù)庫test
Query OK, 1 row affected (0.01 sec)

mysql> use test;       //進入test數(shù)據(jù)庫
Database changed
mysql> create table test (id int);        //創(chuàng)建一個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 創(chuàng)建表時用engine指定默認的存儲引擎 （創(chuàng)建新表時自己指定）

mysql> use school;

Database changed
mysql> create table zyc (id int) engine=InnoDB;    //創(chuàng)建一個新表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的存儲引擎到這里就介紹完了，后續(xù)請期待：MySQL增量備份、MySQL主從同步、MySQL讀寫分離