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. 查看数据库目前使用的存储引擎

1. mysql> use school; //进入school数据库

2. Database changed

3. mysql> show engines; //查看school默认使用的存储引擎，这里因为我使用的是MySQL5.7版本的，所以默认是InnoDB

4.  

5. 引擎类型 是否能使用 是否支持事务 表示为当前默认的引擎

6. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

7. | Engine | Support | Comment | Transactions | XA | Savepoints |

8. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9. | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

10. | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

11. | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

12. | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

13. | MyISAM | YES | MyISAM storage engine | NO | NO | NO |

14. | CSV | YES | CSV storage engine | NO | NO | NO |

15. | ARCHIVE | YES | Archive storage engine | NO | NO | NO |

16. | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

17. | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

18. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

19. 9 rows in set (0.01 sec)

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

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

1. mysql> mysql> show table status from school where name='info';

2. +------+--------+---------+------------+------+

3. | Name | Engine | Version | Row_format | Rows |

4. +------+--------+---------+------------+------+

5. | info | InnoDB | 10 | Dynamic | 1 |

6. +------+--------+---------+------------+------+

7. 1 row in set (0.00 sec)

8. //省略部分内容

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

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

1. mysql> use school;

2.  

3. Database changed

4. mysql> show create table info;

5. +-------+--------------------------------------+

6. | Table | Create Table |

7. +-------+--------------------------------------+

8. | info | CREATE TABLE "info" (

9. "name" char(10) DEFAULT NULL,

10. "score" decimal(5,2) DEFAULT NULL

11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

12. +-------+--------------------------------------+

13. 1 row in set (0.00 sec)

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

数据库存储引擎的修改

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

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

1. mysql> use school; //进入school数据库

2. Database changed

3. mysql> show create table info; //查看当前使用的存储引擎

4. +-------+--------------------------------------------------------------------------------------------------------------------------------+

5. | Table | Create Table |

6. +-------+--------------------------------------------------------------------------------------------------------------------------------+

7. | info | CREATE TABLE "info" (

8. "name" char(10) DEFAULT NULL,

9. "score" decimal(5,2) DEFAULT NULL

10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | //这里可以看到默认使用的InnoDB类型的

11. +-------+--------------------------------------------------------------------------------------------------------------------------------+

12. 1 row in set (0.00 sec)

13.  

14. mysql> alter table info engine=MyISAM; //使用alter table 命令进行修改

15. Query OK, 0 rows affected (0.04 sec)

16. Records: 0 Duplicates: 0 Warnings: 0

17.  

18. mysql> show create table info; //再次查看info表使用的存储引擎类型

19. +-------+--------------------------------------------------------------------------------------------------------------------------------+

20. | Table | Create Table |

21. +-------+--------------------------------------------------------------------------------------------------------------------------------+

22. | info | CREATE TABLE "info" (

23. "name" char(10) DEFAULT NULL,

24. "score" decimal(5,2) DEFAULT NULL

25. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | //这里可以看到存储引擎就被修改为MyISAM类型了

26. +-------+--------------------------------------------------------------------------------------------------------------------------------+

27. 1 row in set (0.00 sec)

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

1. [root@localhost ~]# vim /etc/my.cnf

2. ...省略

3. [mysqld]

4. ...省略

5. pid-file = /usr/local/mysql/mysqld.pid

6. default-storage-engine=MyISAM //添加指定默认存储引擎类型MyISAM

7. socket = /usr/local/mysql/mysql.sock

8. ...省略

9. 修改了配置文件后需要重启MySQL服务

10.  

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

12.  

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

14.  

15. mysql> create database test; //创建一个新的数据库test

16. Query OK, 1 row affected (0.01 sec)

17.  

18. mysql> use test; //进入test数据库

19. Database changed

20. mysql> create table test (id int); //创建一个test表

21. Query OK, 0 rows affected (0.01 sec)

22.  

23. mysql> show create table test; //查看test表的存储引擎

24. +-------+----------------------------+

25. | Table | Create Table |

26. +-------+----------------------------+

27. | test | CREATE TABLE "test" (

28. "id" int(11) DEFAULT NULL

29. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | //这里可以看到存储引擎就是MyISAM

30. +-------+----------------------------+

31. 1 row in set (0.03 sec)

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

1. mysql> use school;

2.  

3. Database changed

4. mysql> create table zyc (id int) engine=InnoDB; //创建一个新表zyc并且指定存储引擎为InnoDB

5. Query OK, 0 rows affected (0.02 sec)

6.  

7. mysql> show create table zyc; //查看zyc的存储引擎

8. +-------+------------------------------+

9. | Table | Create Table |

10. +-------+------------------------------+

11. | zyc | CREATE TABLE "zyc" (

12. "id" int(11) DEFAULT NULL

13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | //这里可以看到存储引擎为InnoDB

14. +-------+------------------------------+

15. 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尝试下。

-------------------------------------------------------------------

转载自：https://blog.csdn.net/hypmxy/article/details/82758900