MySQL表类型中如何查看数据库支出的存储引擎

技术MySQL表类型中如何查看数据库支出的存储引擎这期内容当中小编将会给大家带来有关MySQL表类型中如何查看数据库支出的存储引擎,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。1、查看

这期内容当中小编将会给大家带来有关关系型数据库表类型中如何查看数据库支出的存储引擎,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。

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

方法1:

MySQL show engines \ G;

* * * * * * * * * * * * * * * * * * * * * 1 .行* * * * * * * * * * * * * * * * * * * * * * * *

Engine:InnoDB

支持:是

注释:支持事务、行级锁定和外键

交易:是

XA:YES是是

保存点:是

* * * * * * * * * * * * * * * * * * * * * * * 2 .行* * * * * * * * * * * * * * * * * * * * * * * *

Engine:MRG _ MYISAM

支持:是

注释:富达almysamtables系列

交易:否

XA:NO

保存点:否

* * * * * * * * * * * * * * * * * * * * * 3 .行* * * * * * * * * * * * * * * * * * * * * * * *

发动机:记忆

支持:是

注释:基于,存储在内存中,用于临时表

交易:否

XA:NO

保存点:否

* * * * * * * * * * * * * * * * * * * * * 4 .行* * * * * * * * * * * * * * * * * * * * * * * *

工程:黑洞

支持:是

注释:/dev/nullstorageengine(出现任何出现任何东西)

交易:否

XA:NO

保存点:否

* * * * * * * * * * * * * * * * * * * * * * * 5 .行* * * * * * * * * * * * * * * * * * * * * * * *

工程:米

Support:DEFAULT默认默认

评论:百万存储引擎

交易:否

X

A: 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表类型中如何查看数据库支出的存储引擎了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注行业资讯频道。

内容来源网络,如有侵权,联系删除,本文地址:https://www.230890.com/zhan/78642.html

(0)

相关推荐

  • python内置函数中的均值函数在哪(python中计算数量的函数)

    技术Python矩与矩生成函数是什么本篇内容主要讲解“Python矩与矩生成函数是什么”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“Python矩与矩生成函数是什么”吧!斜度

    攻略 2021年12月21日
  • 抽油烟机品牌大全,抽油烟机什么牌子好用又实惠

    技术抽油烟机品牌大全,抽油烟机什么牌子好用又实惠⭐⭐⭐老梁谈家居抽油烟机品牌大全,每天都会更新关于装修的知识,关注、点赞、评论、私信,各种家居干货在等你⭐⭐⭐吸油烟机是每家每户必备的厨房电器,好的吸油烟机可以把炒菜过程当

    生活 2021年10月28日
  • 草木灰是什么,草木灰都有什么化学成分

    技术草木灰是什么,草木灰都有什么化学成分草木灰都有什么化学成分?要回答这个问题,得弄懂如下几个问题草木灰是什么。1、什么叫草木灰?2、草木灰的化学成分;3、草木灰的性质是什么?4、如何使用草木灰?
    一、什么叫草木灰?草木

    生活 2021年10月23日
  • 笔记本电脑怎样截屏,笔记本电脑怎么选择性截图

    技术笔记本电脑怎样截屏,笔记本电脑怎么选择性截图电脑自带截图 我们的电脑其实本身就有截图功能,只是很多人不知道而已,今天小编就将电脑自带截图的使用方法分享给大家,希望对大家有所帮助笔记本电脑怎样截屏! 电脑的自带截图的使

    生活 2021年10月25日
  • java如何简单快速处理 json 中的数据

    技术java如何简单快速处理 json 中的数据java如何简单快速处理 json 中的数据,相信很多没有经验的人对此束手无策,为此本文总结了问题出现的原因和解决方法,通过这篇文章希望你能解决这个问题。JSONstr.j

    攻略 2021年12月2日
  • 剪辑音频,有什么好用的音频编辑软件

    技术剪辑音频,有什么好用的音频编辑软件有一款很著名也存在很久的音频编辑软件,名叫“CoolEdit Pro”,到百度一搜就能搜到并下载,原软件是英文的,但是有汉化版剪辑音频。我自己一直用它来处理音频的问题,感觉基本上我需

    生活 2021年10月20日