MySQL存储引擎

存储引擎概叙

和大多数的数据库不同,MySQL中有一个存储引擎的概念,针对不同的存储需求可以选择最优的存储引擎。

存储引擎就是存储数据、建立索引、更新查询数据等等技术的实现方式。存储引擎是基于表的,而不是基于库。所以存储引擎也可以被称为表的类型。

Oracle、SQLserver等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎可以根据需要使用相应的引擎或者编写存储引擎。

MySQL5.0支持的存储引擎包括:innoDB(默认,支持事务,行锁,支持外键)、MyISAM(5.5之前默认,表锁)、BDB、MEMORY、MERGE、EXAMPLE、NDB CLuster、ARCHIVE、CSV等,其中innoDB和BDB提供事务安全表,其他存储引擎式非事务安全表。

可以通过 show engines,来查询当前数据库支持的存储引擎。

show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)                   | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| ARCHIVE            | YES     | gzip-compresses tables for a low storage footprint                               | NO           | NO   | NO         |
| FEDERATED          | YES     | Allows to access tables on other MariaDB servers, supports transactions and more | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

 查看数据库默认的存储引擎

show variables like '%storage_engine%';

innoDB innoDB存储引擎式MySQL默认存储引擎,其提供了具有提交、回滚、崩溃恢复能力的事务安全。对MyISAM的存储引擎,innoDB写的处理效率较差一点,并且会占用更多的磁盘空间以保留数据和索引。

innoDB存储引擎不同于其他存储引擎的特点:

  • 事务控制
    create database emp1;
    create  table emp1.tb_innodb(
     id int not null auto_increment,
     name varchar(20) not null,
     primary key(id)
     )engine=innodb default charset=utf8;
    

    插入数据

     insert into tb_innodb(name) values("test"),("test2");
    

    新开启一个终端查询表

    MariaDB [emp1]> select * from tb_innodb;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | test  |
    |  2 | test1 |
    +----+-------+
    

    手动开启事务

    MariaDB [emp1]> start transaction;
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [emp1]> insert into tb_innodb(name) values("test3");
    Query OK, 1 row affected (0.000 sec)
    
    
    #在新开启的终端上查询,可以看到查询不到数据,原因式是事务没有提交
    MariaDB [emp1]> select * from tb_innodb;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | test  |
    |  2 | test1 |
    +----+-------+
    2 rows in set (0.001 sec)
    
    #提交事务
    MariaDB [emp1]> commit;
    Query OK, 0 rows affected (0.001 sec)
    
    #再次查询
    MariaDB [emp1]> select * from tb_innodb;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | test  |
    |  2 | test1 |
    |  3 | test3 |
    +----+-------+
    3 rows in set (0.001 sec
    

    外键约束 MySQL支持外键的存储引擎只有innoDB,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候,也会自动创建对应的索引

    下面两长表中,country_innodb是父表,country_id为主键索引

    city_innodb表是子表,country_id字段为外键,对应country_innodb表的主键country_id

    create table country_innodb(
    country_id int not null auto_increment,
    country_name varchar(20) not null,
    primary key(country_id)
    )charset=utf8;
    
    create table city_innodb(
    city_id int not null auto_increment,
    city_name varchar(20) not null,
    country_id int not null,
    primary key(city_id),
    key inx_fk_country_id(country_id),
    constraint idx_fk_country_id foreign key(country_id) references country_innodb(country_id) on delete restrict on update cascade
    )charset=utf8;
    
    #插入数据
    MariaDB [emp1]> insert into country_innodb  values(null,'Chain'),(null,'America'),(null,'Japan');
    insert into city_innodb values(null,'shang',1),(null,'NewYork',2),(null,'beijing',1);
    
    #测试约束
    MariaDB [emp1]> delete from country_innodb where country_id = 2;
    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`emp1`.`city_innodb`, CONSTRAINT `idx_fk_country_id` FOREIGN KEY (`country_id`) REFERENCES `country_innodb` (`country_id`) ON UPDATE CASCADE)
    
    #更新主表
    MariaDB [emp1]> update country_innodb set country_id = 10 where country_id = 2;
    Query OK, 1 row affected (0.001 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    #跟新主表,子表也更新了
    MariaDB [emp1]> select * from  city_innodb;
    +---------+-----------+------------+
    | city_id | city_name | country_id |
    +---------+-----------+------------+
    |       1 | shang     |          1 |
    |       2 | NewYork   |         10 |
    |       3 | beijing   |          1 |
    +---------+-----------+------------+
    

    MyISAM存储引擎

    不支持事务,也不支持外键,优点是访问快,对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎创建表。

    不支持事务

    create table tb_myisam(
     id int not null auto_increment,
     name varchar(20) not null,
     primary key(id)
     )engine=myisam default charset=utf8;
     
    #开启事务插入数据
    MariaDB [emp1]> start transaction ;
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [emp1]> insert into tb_myisam values(null,'小白');
    Query OK, 1 row affected (0.001 sec)
    
    #新开启一个终端查看,可以看到事务并没有控制
    MariaDB [emp1]>  select * from tb_myisam;
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | 小明   |
    |  2 | 小白   |
    +----+--------+
    2 rows in set (0.000 sec)
    

    myisam文件存储的方式 每个myisam在磁盘上存储成3个文件,其文件名和表名相同,扩展名分别是:

    • .frm(存储表定义)
    • .MYD(MYData,存储数据)
    • .MYI(MYIndex,存储索引)
    [root@localhost emp1]# ll /var/lib/mysql/emp1/
    -rw-rw----. 1 mysql mysql   1004 May 26 17:57 tb_myisam.frm
    -rw-rw----. 1 mysql mysql     40 May 26 17:59 tb_myisam.MYD
    -rw-rw----. 1 mysql mysql   2048 May 26 17:59 tb_myisam.MYI
    
     
原文地址:https://www.cnblogs.com/diqiyao/p/14828840.html