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 常用存储引擎的对比

特点	myisam	innodb	memory	merge	ndb
存储限制	有	64tb	有	没有	有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁	表锁
b 树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	n/a	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外键		支持

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 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)

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