MySQL学习总结之路（第六章：表类型【存储引擎】的选择）
目录
MySQL学习总结之路（第六章：表类型【存储引擎】的选择）
1、查看当前数据库支出的存储引擎：
方法1：
mysql> show engines \G; *************************** 1. row *************************** Engine: InnoDB Support: YES Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: MyISAM Support: DEFAULT Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 9. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL 9 rows in set (0.00 sec) ERROR: No query specified
方法2：（Value 显示为“DISABLED”的记录表示支持该存储引擎，但是数据库启动的时候被禁用。）
mysql> show variables like 'have%'; +------------------------+----------+ | Variable_name | Value | +------------------------+----------+ | have_compress | YES | | have_crypt | NO | | have_dynamic_loading | YES | | have_geometry | YES | | have_openssl | DISABLED | | have_profiling | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | DISABLED | | have_statement_timeout | YES | | have_symlink | YES | +------------------------+----------+ 11 rows in set, 1 warning (0.00 sec)
2、ENGINE={存储引起类型} 创建表的时候，设置存储引擎：
mysql> create table a( -> i bigint(20) not null auto_increment, -> primary key (i) -> ) engine=myisam default charset=gbk; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 3 Current database: test Query OK, 0 rows affected (1.33 sec)
3、alter able tablename engine={存储引起类型} 修改表为其他存储引擎
mysql> alter table a engine=innodb; Query OK, 0 rows affected (1.70 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table a \G; *************************** 1. row *************************** Table: a Create Table: CREATE TABLE `a` ( `i` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk 1 row in set (0.14 sec)
常用存储引擎的对比
| 特点 | MyISAM | InnoDB | MEMORY | MERGE | NDB |
| 存储限制 | 有 | 64TB | 有 | 没有 | 有 |
| 事务安全 | 支持 | ||||
| 锁机制 | 表锁 | 行锁 | 表锁 | 表锁 | 表锁 |
| B 树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | 支持 | |||
| 全文索引 | 支持 | ||||
| 集群索引 | 支持 | ||||
| 数据缓存 | 支持 | 支持 | 支持 | ||
| 索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 数据可压缩 | 支持 | ||||
| 空间使用 | 低 | 高 | N/A | 低 | 低 |
| 内存使用 | 低 | 高 | 中等 | 低 | 高 |
| 批量插入的速度 | 高 | 低 | 高 | 高 | 高 |
| 支持外键 | 支持 |
常用存储引擎学习（MyISAM、InnoDB、MEMORY 和 MERGE）
MyISAM：
默认的MySQL存储引擎，不支持事务和外键
优点：访问速度快
每个MyISAM在磁盘上存储成3个文件，其文件名和表名都相同。扩展名分别是：
.frm (存储表定义)
.MYD (MYData，存储数据)
.MYI (MYIndex，存储索引)
(数据文件和索引文件可以放置在不同的目录，平均分布 IO，获得更快的速度。)
InnoDB:
处理效率较差，占用较多的空间用来保留数据和索引
优点：具有提交、回滚、奔溃恢复能力的事务安全、唯一支持外键的存储引擎
自动增长列：InnoDB 表的自动增长列可以手工插入，但是插入的值如果是空或者 0，则实际插入的将是自动增长后的值
mysql> create table autoincre_demo( -> i smallint not null auto_increment, -> name varchar(10),primary key(i) -> )engine=innodb; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 5 Current database: test Query OK, 0 rows affected (1.19 sec) mysql> insert into autoincre_demo values(1,"121"),(0,"dddf"),(null,"fdf"); Query OK, 3 rows affected (0.59 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from autoincre_demo; +---+------+ | i | name | +---+------+ | 1 | 121 | | 2 | dddf | | 3 | fdf | +---+------+ 3 rows in set (0.00 sec)
alter table tabename auto_increment=n 设置自动增长列的初始值（此值默认从1开始）
可以使用 LAST_INSERT_ID()查询当前线程最后插入记录使用的值。如果一次插入了多条记录，那么返回的是第一条记录使用的自动增长值。下面的例子演示了使用 LAST_INSERT_ID()的情况：
mysql> insert into autoincre_demo(name) values('3'); Query OK, 1 row affected (0.36 sec) mysql> select LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 15 | +------------------+ 1 row in set (0.00 sec) mysql> insert into autoincre_demo(name) values('3'),('6'),('323'),('21'); Query OK, 4 rows affected (0.09 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 16 | +------------------+ 1 row in set (0.00 sec)
外键约束：
在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。
下面是样例数据库中的两个表，country 表是父表，country_id 为主键索引，city 表是子表，country_id 字段对 country 表的 country_id 有外键。
mysql> create table country( -> country_id smallint unsigned not null auto_increment, -> country varchar(50) not null, -> last_update timestamp not null default current_timestamp on update current_timestamp, -> primary key(country_id) -> )engine=innodb default charset=utf8; Query OK, 0 rows affected (0.86 sec)
mysql> 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; Query OK, 0 rows affected (3.22 sec)
在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包 RESTRICT、CASCADE、SET NULL 和 NO ACTION
RESTRICT 和 NO ACTION 相同，是指限制在子表有关联记录的情况下父表不能更新
CASCADE 表示父表在更新或者删除时，更新或者删除子表对应记录；
SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被 SET NULL。
mysql> select * from country; +------------+---------+---------------------+ | country_id | country | last_update | +------------+---------+---------------------+ | 1 | AAA | 2021-06-16 15:09:22 | +------------+---------+---------------------+ 1 row in set (0.00 sec) mysql> select * from city; +---------+------+------------+---------------------+ | city_id | city | country_id | last_update | +---------+------+------------+---------------------+ | 10 | bb | 1 | 2021-06-16 15:11:45 | +---------+------+------------+---------------------+ 1 row in set (0.00 sec) mysql> delete from country where country_id = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE) mysql> update country set country_id = 10000 where country_id = 1; Query OK, 1 row affected (0.62 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from country; +------------+---------+---------------------+ | country_id | country | last_update | +------------+---------+---------------------+ | 10000 | AAA | 2021-06-16 15:13:35 | +------------+---------+---------------------+ 1 row in set (0.00 sec) mysql> select * from city -> ; +---------+------+------------+---------------------+ | city_id | city | country_id | last_update | +---------+------+------------+---------------------+ | 10 | bb | 10000 | 2021-06-16 15:11:45 | +---------+------+------------+---------------------+ 1 row in set (0.00 sec)
在导入多个表的数据时，如果需要忽略表之前的导入顺序，可以暂时关闭外键的检查；同样，在执行 LOAD DATA 和 ALTER TABLE 操作的时候，可以通过暂时关闭外键约束来加快处理的速度，关闭的命令是“SET FOREIGN_KEY_CHECKS = 0;”，执行完成之后，通过执行“SETFOREIGN_KEY_CHECKS = 1;”语句改回原状态。
查看表外键信息：show create table 或者 show table status 命令
mysql> show table status like 'city' \G; *************************** 1. row *************************** Name: city Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 1 Avg_row_length: 16384 Data_length: 16384 Max_data_length: 0 Index_length: 16384 Data_free: 0 Auto_increment: 11 Create_time: 2021-06-16 15:02:17 Update_time: 2021-06-16 15:13:35 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.43 sec) ERROR: No query specified
存储方式：
（1）、使用共享表空间存储：表的表结构保存在.frm文件中，数据+索引存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件
（2）、使用多表空间存储：表的表结构也保存在.frm文件中，数据+索引单独存在.ibd中；如果是分区表，则每个分区对应单独的.ibd文件，文件名是：“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的 IO 均匀分布在多个磁盘上
MEMORY：
使用存在内存中的内容来创建表
每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。
优点：访问速度快（数据存储在内存中），并且默认使用HASH索引，服务关闭则数据丢失
mysql> CREATE TABLE tab_memory ENGINE=MEMORY -> SELECT city_id,city,country_id -> FROM city GROUP BY city_id; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 12 Current database: test Query OK, 1 row affected (0.62 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from tab_memory; +---------+------+------------+ | city_id | city | country_id | +---------+------+------------+ | 10 | bb | 10000 | +---------+------+------------+ 1 row in set (0.00 sec) mysql> show table status like 'tab_memory' \G *************************** 1. row *************************** Name: tab_memory Engine: MEMORY Version: 10 Row_format: Fixed Rows: 1 Avg_row_length: 155 Data_length: 520320 Max_data_length: 65011650 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-06-16 15:28:58 Update_time: NULL Check_time: NULL Collation: utf8_unicode_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
给表创建索引的时候可以指定索引类型是HASH或是BTREE
mysql> create index mem_hash using hash on tab_memory(city_id); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 13 Current database: test Query OK, 1 row affected (0.63 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show index from tab_memory \G; *************************** 1. row *************************** Table: tab_memory Non_unique: 1 Key_name: mem_hash Seq_in_index: 1 Column_name: city_id Collation: NULL Cardinality: 1 Sub_part: NULL Packed: NULL Null: Index_type: HASH Comment: Index_comment: 1 row in set (0.32 sec) ERROR: No query specified mysql> drop index mem_hash on tab_memory; Query OK, 1 row affected (0.31 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> create index mem_hash using btree on tab_memory(city_id); Query OK, 1 row affected (0.16 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show index from tab_memory \G; *************************** 1. row *************************** Table: tab_memory Non_unique: 1 Key_name: mem_hash Seq_in_index: 1 Column_name: city_id Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec) ERROR: No query specified
MERGE：
此存储殷勤是一组MyISAM表的组合
对 MERGE 类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的 MyISAM 表进行的。
对于 MERGE 类型表的插入操作，是通过INSERT_METHOD 子句定义插入的表，可以有 3 个不同的值，使用 FIRST 或 LAST 值使得插入操作被相应地作用在第一或最后一个表上，不定义这个子句或者定义为 NO，表示不能对这个 MERGE 表执行插入操作。
可以对 MERGE 表进行 DROP 操作，这个操作只是删除 MERGE 的定义，对内部的表没有任何的影响。
存储文件：一个.frm 文件存储表定义，另一个.MRG 文件包含组合表的信息，包括 MERGE 表由哪些表组成、插入新的数据时的依据
mysql> create table payment_2020( -> country_id smallint, -> payment_date datetime, -> amount DECIMAL(15,2), -> KEY idx_fk_country_id (country_id) -> )engine=myisam; Query OK, 0 rows affected (0.25 sec) mysql> create table payment_2021( -> country_id smallint, -> payment_date datetime, -> amount DECIMAL(15,2), -> KEY idx_fk_country_id (country_id) -> )engine=myisam; Query OK, 0 rows affected (0.54 sec) mysql> CREATE TABLE payment_all( -> country_id smallint, -> payment_date datetime, -> amount DECIMAL(15,2), -> INDEX(country_id) -> )engine=merge union=(payment_2020,payment_2021) INSERT_METHOD=LAST; Query OK, 0 rows affected (0.47 sec)
分别向2020和2021中插入数据，并查询
mysql> insert into payment_2020 values(1,'2020-06-01',100000),(2,'2020-06-15',150000); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into payment_2021 values(1,'2021-04-20',35000),(2,'2021-06-15',220000); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from payment_2020; +------------+---------------------+-----------+ | country_id | payment_date | amount | +------------+---------------------+-----------+ | 1 | 2020-06-01 00:00:00 | 100000.00 | | 2 | 2020-06-15 00:00:00 | 150000.00 | +------------+---------------------+-----------+ 2 rows in set (0.00 sec) mysql> select * from payment_2021; +------------+---------------------+-----------+ | country_id | payment_date | amount | +------------+---------------------+-----------+ | 1 | 2021-04-20 00:00:00 | 35000.00 | | 2 | 2021-06-15 00:00:00 | 220000.00 | +------------+---------------------+-----------+ 2 rows in set (0.00 sec) mysql> select * from payment_all; +------------+---------------------+-----------+ | country_id | payment_date | amount | +------------+---------------------+-----------+ | 1 | 2020-06-01 00:00:00 | 100000.00 | | 2 | 2020-06-15 00:00:00 | 150000.00 | | 1 | 2021-04-20 00:00:00 | 35000.00 | | 2 | 2021-06-15 00:00:00 | 220000.00 | +------------+---------------------+-----------+ 4 rows in set (0.00 sec)
可以发现，payment_all 表中的数据是 payment_2020 和 payment_2021 表的记录合并后的结果集
下面向 MERGE 表插入一条记录，由于 MERGE 表的定义是 INSERT_METHOD=LAST，就会向最后一个表中插入记录，所以虽然这里插入的记录是 2006 年的，但仍然会写到 payment_2021表中。
mysql> insert into payment_all values(3,'2020-03-30',12333131); Query OK, 1 row affected (0.31 sec) mysql> select * from payment_all; +------------+---------------------+-------------+ | country_id | payment_date | amount | +------------+---------------------+-------------+ | 1 | 2020-06-01 00:00:00 | 100000.00 | | 2 | 2020-06-15 00:00:00 | 150000.00 | | 1 | 2021-04-20 00:00:00 | 35000.00 | | 2 | 2021-06-15 00:00:00 | 220000.00 | | 3 | 2020-03-30 00:00:00 | 12333131.00 | +------------+---------------------+-------------+ 5 rows in set (0.00 sec) mysql> select * from payment_2021; +------------+---------------------+-------------+ | country_id | payment_date | amount | +------------+---------------------+-------------+ | 1 | 2021-04-20 00:00:00 | 35000.00 | | 2 | 2021-06-15 00:00:00 | 220000.00 | | 3 | 2020-03-30 00:00:00 | 12333131.00 | +------------+---------------------+-------------+ 3 rows in set (0.00 sec) mysql> select * from payment_2020; +------------+---------------------+-----------+ | country_id | payment_date | amount | +------------+---------------------+-----------+ | 1 | 2020-06-01 00:00:00 | 100000.00 | | 2 | 2020-06-15 00:00:00 | 150000.00 | +------------+---------------------+-----------+ 2 rows in set (0.00 sec)
