MySQL存储引擎 MYISAM与 INNODB

一、MySQL 存储引擎概述

MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎。

二、MySQL常用的存储引擎

2.1 MyISAM

• MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的
• 访问速度快，对事务完整性没有要求，适合查询、插入为主的应用场景
• MyISAM 在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为：
  • .frm 表结构定义
  • .MYD 数据文件
  • .MYI 索引文件
    
• 表级锁定形式，数据在更新时锁定整个表
  • 数据库在读写过程中相互阻塞：写入的时候不让读取，读取的时候不让写入
  • 数据单独写入或读取，速度过程较快且占用资源相对少
• MyISAM 表支持三种不同的存储格式：
  • 1、静态表：默认的存储格式。静态表中的字段都是固定长度的，优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间相对大一些。
  • 2、动态表：表中字段长度不固定。优点是存储占用空间较少；缺点是频繁的更新、删除记录会产生碎片，需要定期执行 optimize table 语句，或 myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。
  • 3、压缩表：由 myisamchk 工具创建，占据非常小的空间，因为每条记录单独压缩，所以只有非常小的访问开支。

MyISAM适用的生产场景

• 公司业务不需要事务的支持
• 当方面读取或写入数据比较多的业务
• 使用读写并发访问相对较低的业务
• 数据修改相对较少的业务
• 对数据业务一致性要求不是非常高的业务
• 服务器硬件资源相对比较差

2.2InnoDB

特点：

• 支持事务，支持四个事务隔离级别
• MySQL从5.5.5版本开始默认的存储引擎为InnoDB
• 读写阻塞与事务隔离级别相关
• 能非常高的缓存索引和数据
• 支持分区、表空间
• 支持外键约束，5.5版本前不支持全文索引，5.5版本后支持全文索引
• 行级锁定，全表扫描时是表级锁定

InnoDB中不保存表的行数，如使用 count() 函数统计表的行数时，InnoDB需要扫描一遍整张表来计算有多少行，二MyISAM只需要简单的读出保存好的行数即可。注意点：当count() 语句包含 where 条件表达式时 MyISAM也需要扫描整张表，因为不是单纯的统计整张表有多少行，而是针对特定字段值来判断。
对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立组合索引。
清空整个表示，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。

三、查看存储引擎

3.1 查看系统支持的存储引擎

• show engines;

mysql> mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| 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.00 sec)

mysql> 

3.1 查看表使用的存储引擎

方法一：show table status from 数据库名 where name=‘表名’\G；

mysql> show table status from testsql where name='test_transcation'\G;
*************************** 1. row ***************************
           Name: test_transcation
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-07-11 18:15:59
    Update_time: 2021-07-11 18:21:04
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 

方法二：show create table 表名;

mysql> use testsql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table test_transcation;
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                      |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_transcation | CREATE TABLE "test_transcation" (
  "id" int(4) NOT NULL,
  "name" varchar(20) NOT NULL,
  "money" double DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

四、修改存储引擎

方法一：通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务

注意点：此方法只对修改了配置文件并重启mysql服务之后新创建的表有效，之前创建的表不会发生影响。
方法二：使用 alter 命令针对表进行修改
例如：alter table 表名 engine=引擎名;

mysql> alter table test_transcation engine=MYISAM;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 

方法三：在创建表的时候指定引擎名
例如：create table 表名(字段1 数据类型…字段n 数据类型) engine=MYISAM;

mysql> create table userinfo(id int(4) not null primary key,name varchar(20) not null) engine=MYISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table userinfo;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                               |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------+
| userinfo | CREATE TABLE "userinfo" (
  "id" int(4) NOT NULL,
  "name" varchar(20) NOT NULL,
  PRIMARY KEY ("id")
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>