深入浅出mysql_7.存储引擎

最新推荐文章于 2024-06-03 11:51:39 发布

wangxiaoangg

最新推荐文章于 2024-06-03 11:51:39 发布

阅读量285

点赞数

分类专栏：随笔数据库文章标签： mysql存储引擎 InnoDB MyISAM

本文链接：https://blog.csdn.net/qq_16399991/article/details/78894934

版权

数据库同时被 2 个专栏收录

45 篇文章 1 订阅

订阅专栏

随笔

28 篇文章 0 订阅

订阅专栏

7.表类型（存储引擎）的选择

7.1 MySQL 存储引擎概述

默认的存储引擎

创建新表不指定表的存储引擎，则新表是默认存储引擎的

修改默认存储引擎：可修改参数文件中设置 default_table_type

查看当前的默认存储引擎，可以使用以下命令：

show variables like 'table_type';

查看当前数据库支持的引擎：

方法一：

SHOW ENGINES \G

第二种方法：

SHOW VARIABLES LIKE 'have%';

创建表时设置引擎：

CREATE TABLE ai (
i bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (i)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;

修改已有表引擎：

使用 ALTER TABLE 语句，将一个已经存在的表修改成其他的存储引擎

alter table ai engine = innodb;

7.2 各种存储引擎的特性

7.2.1 MyISAM

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表。
每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，但扩展名分别是：

frm（存储表定义）；
.MYD（MYData，存储数据）；
MYI （MYIndex，存储索引）。

数据文件和索引文件可以放置在不同的目录，平均分布 IO，获得更快的速度。

要指定索引文件和数据文件的路径，需要在创建表的时候通过 DATA DIRECTORY 和 INDEXDIRECTORY 语句指定，也就是说不同 MyISAM 表的索引文件和数据文件可以放置到不同的路径下。文件路径需要是绝对路径，并且具有访问权限。

MyISAM 类型的表可能会损坏，原因可能是多种多样的，损坏后的表可能不能访问，会提示需要修复或者访问后返回错误的结果。MyISAM 类型的表提供修复的工具，可以用 CHECK TABLE 语句来检查 MyISAM 表的健康，并用 REPAIR TABLE 语句修复一个损坏的 MyISAM 表。表损坏可能导致数据库异常重新启动，需要尽快修复并尽可能地确认损坏的原因。具体的操作步骤可以参见第 35 章应急处理。

MyISAM 的表又支持 3 种不同的存储格式，分别是：

静态（固定长度）表；
动态表；
压缩表。

静态表是默认存储格式，静态表中的字段都是非变长度字段，这样每个记录都是固定长度。
优点: 存储迅速，容易缓存，出现故障容易恢复。
缺点：占用空间比动态表多。
静态表存储时会按照列的宽度补足空格，应用访问是并不会得到这些空格，这些空格在返回应用前已经去掉。当存储时尾部真的需要空格时也会被去掉。

动态表中包含变长字段，记录不是固定长度的。
占用空间相对较少，但是频繁的更新个删除记录会产生碎片，需要定期执行OPTIMIZE TABLE 或者 myisamchk -r命令来改善性能。并且在出现故障时恢复相对较困难。

压缩表由myisampack 工具创建，占据非常小的空间，因为每条记录是单独压缩的，所以有非常小的访问开支。

7.2.2 InnoDB

InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 MyISAM的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

特点：

自动增长列： InnoDB的自动增长列可以手工插入，但是插入的值如果是0或者空时将自动增长。可以通过
```
ALTER TABLE tablename AUTO_INCREMENT = n 
```
来设置自动增长的初始值。但是该值是保留在内存中的。如果重新启动数据库，该值将丢失。可以使用
```
select LAST_INSERT_ID();
```
LAST_INSERT_ID(),来查询当前线程最后插入使用的值。如果插入多条，则返回第一条记录使用的值。对于InnoDB的自增列必须是索引，如果是组合索引，必须是组合索引的第一列。但是对于MyISAM表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照前几列进行排序后自增的。
外键约束：只有InnoDB支持外键约束。创建外键时，父表必须有对应的索引，子表在创建外建时也会创建对应的索引。在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包 RESTRICT、CASCADE、SET NULL 和 NO ACTION。其中 RESTRICT 和 NO ACTION 相同，是指限制在子表有关联记录的情况下父表不能更新；CASCADE 表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被 SET NULL。选择后两种方式的时候要谨慎，可能会因为错误的操作导致数据的丢失。
存储方式： InnoDB 存储表和索引有以下两种方式，
1.使用共享表空间存储，这种方式创建的表的表结构保存在.frm 文件中，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。
2.使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。如果是个分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的 IO 均匀分布在多个磁盘上。

修改：
ALTER TABLE tablename ADD CONSTRAINT foreign_keyname FORENTIN KEY(col_name) REFERENCES 关联表名(关联字段名);
创建：
CREATE TABEL table_name (
.....,
CONSTRAINT foreign_keyname FORENTIN KEY(col_name) REFERENCES 关联表名(关联字段名)
) ENGINE = InnoDB;

创建外键时，可以指定在删除更新父表时对子表的操作。
RESTRICT
NO ACTION
CASCADE
SET NULL

例子:
CREATE TABLE city (
city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
city VARCHAR(50) NOT NULL,
country_id SMALLINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (city_id),
KEY idx_fk_country_id (country_id),
CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON
DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

在导入多表数据时，可以暂时关闭外键检查 SET FOREIGN_KEY_CHECK = 0 :导入完成再开启 SET FOREIGN_KEY_CHECK = 1；

7.2.3 MEMORY

memory 存储引擎使用存在于内存中的内容来创建表，每个memory实际只对应一个磁盘文件，格式是.frm。 memory 表访问速度非常快，因为他数据存储在内存中的，并默认使用HASH索引。但是服务一旦关闭表中的数据就会丢失。

创建索引的时候可以指定使用HASH还是BTREE：

CREATE INDEX mem_hash USING HASH ON tablename(col_name)

持久数据:
在启动mysql时使用 --init-file选项，把INSERT INTO ....SELECT 或者LOAD DATA INFILE 这样的语句放入文件中，这样就可以将持久的数据载入表。

数据表的大小：
每个memory 表可以放置的数据大小，收到max_heap_table_size限制，系统默认是16Mb，可以根据需要加大。此外定义表的时候可以设置MAX_ROWS子句指定表的最大长度。

适用环境:
memory主要适用那些内容变化不频繁，或者作为那么统计操作的中间结果表。
要注意数据并没有写入硬盘，重启时数据会丢失。

7.2.4 MERGE [mɜ:rdʒ]

MERGE 存储引擎是一组MyISAM表的组合，这些MyISAM表的结构必须完全相同，MERGE表本身并没有数据，对MERGE标的可以进行查询、更新、删除操作，这些操作实际是对MyISAM表的操作。
可以对 MERGE 表进行 DROP 操作，这个操作只是删除 MERGE 的定义，对内部的表没有任何的影响

定义MERGE表语法：

CRATE TABLE tablename (
....
)ENGINE = MERGE UNION(tablename1,tablename2, ,,) INSERT_METHOD=LAST

范例
（1）创建 3 个测试表 payment_2006、payment_2007 和 payment_all，其中 payment_all是前两个表的 MERGE 表：

create table payment_2006(
 country_id smallint,
payment_date datetime,
 amount DECIMAL(15,2),
 KEY idx_fk_country_id (country_id)
 )engine=myisam;

create table payment_2007(
country_id smallint,
 payment_date datetime,
 amount DECIMAL(15,2),
 KEY idx_fk_country_id (country_id)
 )engine=myisam;

CREATE TABLE payment_all(
 country_id smallint,
 payment_date datetime,
 amount DECIMAL(15,2),
    INDEX(country_id)
 )engine=merge union=(payment_2006,payment_2007) INSERT_METHOD=LAST;

插入操作：
对MERGE表进行插入操作，是通过INSERT_METHOD 定义插入的表，有三个值：LAST 插入到最后一张表，FIRST 插入到第一张表，NO 不允许插入

7.3 如何选择合适的存储引擎

MyISAM：如果应用是读和插入操作为主，只有很少的更新和删除操作。对事务的完整性和并发要求不高，MyISAM 非常合适
InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。
MEMORY：将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。
MERGE：用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善 MERGE 表的访问效率。这对于诸如数据仓储等 VLDB环境十分适合。