数据库进阶之——数据库的存储引擎

最新推荐文章于 2024-10-11 17:04:33 发布

一元十二會_

最新推荐文章于 2024-10-11 17:04:33 发布

阅读量116

点赞数 1

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_45059041/article/details/118713666

版权

本文介绍了MySQL数据库的两种主要存储引擎InnoDB和MyISAM的特点及应用场景。InnoDB支持事务处理，行级锁定，适合需要事务安全的场景；而MyISAM则适合读取密集型应用，其数据存储为三个文件。通过实例展示了如何更改表的存储引擎，并分析了默认引擎设置对新建表的影响。

摘要由CSDN通过智能技术生成

数据库的存储引擎

数据库常用的两种存储引擎

MyiSAM

myisam支持三种不同储存格式

MYISAM适合的场景

InnoDB

特点

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show table status from blbl where name='qq'\G;
*************************** 1. row ***************************
           Name: qq
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-07-06 22:34:39
    Update_time: 2021-07-11 22:58:24
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> alter table qq engine=MyISAM;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table qq;
| qq    | CREATE TABLE "qq" (
  "id" int(11) NOT NULL,
  "name" char(10) NOT NULL,
  "score" decimal(5,2) DEFAULT NULL,
  "address_new" varchar(50) DEFAULT NULL,
  "hobby" varchar(20) DEFAULT NULL,
  PRIMARY KEY ("id"),
  UNIQUE KEY "address_new" ("address_new")
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

[root@mysql ~]# vim /etc/my.cnf
default-storage-engine=INNODB	//添加一行
[root@mysql ~]# systemctl restart mysqld.service

mysql> create table qwe(id int,name char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> show create table qwe;	//修改后的引擎只对新加的表生效，之前的表还是之前的引擎
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| qwe   | CREATE TABLE "qwe" (
  "id" int(11) DEFAULT NULL,
  "name" char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table tyu(id int,name char(10)) engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table tyu;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| tyu   | CREATE TABLE "tyu" (
  "id" int(11) DEFAULT NULL,
  "name" char(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

小结

MySQL数据库常见的引擎有 innodb 和myisam两种。采用不同的数据存储文件管理数据。myisam引擎：创建一张表对应三个文件：表名.frm 表名.MYD 表名.MYI。位于 “/var/lib/mysql/数据库名” 目录内，依次存放表的结构，表的数据和表的索引;

innodb引擎：建一张表对应两个文件：表名.frm 表名.ibd。位于 “/var/lib/mysql/数据库名” 目录内，依次表示表的结构，表的数据信息和索引信息。但较为特殊的是：所有的innodb引擎创建的表的数据统一存放在 /var/lib/mysql/ibdata1文件中。如果数据量很大，MySQL会自动的创建ibdata2，ibdata3，…，便于管理。MySQL数据库，缺省选用innodb引擎，来支持事务。