MySQL表类型 存储引擎 的选择

目录

1、查看当前数据库支出的存储引擎

方法1： 方法2：



2、ENGINE={存储引起类型}  创建表的时候，设置存储引擎 3、alter able tablename engine={存储引起类型} 修改表为其他存储引擎


3.1 常用存储引擎的对比 3.2 常用存储引擎学习（MyISAM、InnoDB、MEMORY 和 MERGE）

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)

3.1 常用存储引擎的对比

3.2 常用存储引擎学习（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 TIMESТAMP NOT NULL DEFAULT CURRENT_TIMESТAMP ON UPDATE CURRENT_TIMESТAMP,

    -> 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     | 2022-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 | 2022-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     | 2022-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 | 2022-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: 2022-06-16 15:02:17

    Update_time: 2022-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: 2022-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_2022(

    -> 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_2022(

    -> 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_2022,payment_2022) INSERT_METHOD=LAST;

Query OK, 0 rows affected (0.47 sec)

分别向2022和2022中插入数据，并查询

mysql> insert into payment_2022 values(1,’2022-06-01′,100000),(2,’2022-06-15′,150000);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into payment_2022 values(1,’2022-04-20′,35000),(2,’2022-06-15′,220000);

Query OK, 2 rows affected (0.03 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from payment_2022;

+————+———————+———–+

| country_id | payment_date        | amount    |

+————+———————+———–+

|          1 | 2022-06-01 00:00:00 | 100000.00 |

|          2 | 2022-06-15 00:00:00 | 150000.00 |

+————+———————+———–+

2 rows in set (0.00 sec)

mysql> select * from payment_2022;

+————+———————+———–+

| country_id | payment_date        | amount    |

+————+———————+———–+

|          1 | 2022-04-20 00:00:00 |  35000.00 |

|          2 | 2022-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 | 2022-06-01 00:00:00 | 100000.00 |

|          2 | 2022-06-15 00:00:00 | 150000.00 |

|          1 | 2022-04-20 00:00:00 |  35000.00 |

|          2 | 2022-06-15 00:00:00 | 220000.00 |

+————+———————+———–+

4 rows in set (0.00 sec)

可以发现，payment_all 表中的数据是 payment_2022 和 payment_2022 表的记录合并后的结果集

下面向 MERGE 表插入一条记录，由于 MERGE 表的定义是 INSERT_METHOD=LAST，就会向最后一个表中插入记录，所以虽然这里插入的记录是 2006 年的，但仍然会写到 payment_2022表中。

mysql> insert into payment_all values(3,’2022-03-30′,12333131);

Query OK, 1 row affected (0.31 sec)

mysql> select * from payment_all;

+————+———————+————-+

| country_id | payment_date        | amount      |

+————+———————+————-+

|          1 | 2022-06-01 00:00:00 |   100000.00 |

|          2 | 2022-06-15 00:00:00 |   150000.00 |

|          1 | 2022-04-20 00:00:00 |    35000.00 |

|          2 | 2022-06-15 00:00:00 |   220000.00 |

|          3 | 2022-03-30 00:00:00 | 12333131.00 |

+————+———————+————-+

5 rows in set (0.00 sec)

mysql> select * from payment_2022;

+————+———————+————-+

| country_id | payment_date        | amount      |

+————+———————+————-+

|          1 | 2022-04-20 00:00:00 |    35000.00 |

|          2 | 2022-06-15 00:00:00 |   220000.00 |

|          3 | 2022-03-30 00:00:00 | 12333131.00 |

+————+———————+————-+

3 rows in set (0.00 sec)

mysql> select * from payment_2022;

+————+———————+———–+

| country_id | payment_date        | amount    |

+————+———————+———–+

|          1 | 2022-06-01 00:00:00 | 100000.00 |

|          2 | 2022-06-15 00:00:00 | 150000.00 |

+————+———————+———–+

2 rows in set (0.00 sec)

到此这篇关于MySQL表类型 存储引擎 的选择的文章就介绍到这了,更多相关MySQL表类型 存储引擎内容请搜索共生网络以前的文章或继续浏览下面的相关文章希望大家以后多多支持共生网络！