MySQL的存储引擎

于 2019-02-14 20:02:41 发布
阅读量198 收藏
点赞数
分类专栏： MySQL学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Micky_Yang/article/details/87294735
版权

存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据是以表的形式存储的，所有存储引擎也可以称为表类型（即存储和操作此表的类型）。在Oracle和SQL Server等数据库中只有一种存储引擎，所以数据存储管理机制都是一样的；而MySQL数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据需要编写自己的存储引擎。

1、MyISAM存储引擎：
MyISAM存储引擎是MySQL5.5版本前的默认存储引擎，MyISAM存储引擎是基于ISAM存储引擎发展起来的，它解决了ISAM的很多不足。MyISAM增加了很多有用的扩展功能。
MyISAM存储引擎的文件类型：
MyISAM存储引擎的表存储成3个文件。文件的名字与表名相同，扩展名包括frm、MYD和MYI。

[root@www bbb]# ll
total 20
-rw-rw---- 1 mysql mysql   65 Feb 14 12:30 db.opt
-rw-rw---- 1 mysql mysql 8614 Feb 14 12:32 student.frm
-rw-rw---- 1 mysql mysql    0 Feb 14 12:32 student.MYD
-rw-rw---- 1 mysql mysql 1024 Feb 14 12:32 student.MYI
[root@www bbb]#

（1）frm：存储表结构；
（2）MYD：存储数据，是MYData的缩写；
（3）MYI：存储索引，是MYIndex的缩写；

MyISAM引擎的特点：
1） 不支持事务（事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功，要么全失败）
2） 表级锁定（数据更新时锁整个表）：其锁定机制是表级锁定，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性。
3） 读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。
4） 只会缓存索引：MyISAM可以通过key_buffer_size缓存索引，以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。
5） 读取速度快，占用资源较少。
6） 不支持外键约束，但支持全文索引。
7） MyISAM引擎是MySQL5.5前缺省的存储引起。
8）修复：手工或自动修复、但可能丢失数据
9）支持压缩表

MyISAM引擎调优：
1）设置合适的索引（缓存机制）
2）调整读写优先级，根据实际需求确保重要操作更优先执行。
3）启用延迟插入改善大批量写入性能（降低写入频率，尽可能多条数据一次性写入）。
4）尽量顺序操作让insert数据都写入到尾部，减少阻塞。
5）分解大的时间长的操作，降低操作的阻塞时间。
6）降低并发数（减少对MySQL访问），某些高并发场景通过应用进行排队队列机制。
7）对于静态（更改不频繁）的数据库数据，充分利用Query Cache或memcached缓存服务可以极大提高访问效率。
8）MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问。
9）可以把主从同步的主库使用InnoDB，从库用MyISAM引擎（不推荐）。

2、InnoDB存储引擎
InnoDB存储引擎是MySQL5.5之后的默认存储引擎，InnoDB给MySQL第表提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全；InnoDB是MySQL上第一个提供外键约束的表引擎，而且InnoDB对事务处理的能力，也是MySQL其他存储引擎所无法与之比拟的。
InnoDB存储引擎的文件类型：

[root@www bbb]# ll -h /var/lib/mysql/ccc/
total 16K
-rw-rw---- 1 mysql mysql   65 Feb 14 12:43 db.opt
-rw-rw---- 1 mysql mysql 8.5K Feb 14 12:43 teachers.frm
[root@www bbb]#

InnoDB存储引擎的特点：
1） 支持事务：支持4个事务隔离级别，支持多版本读。
2） 行级锁定（更新时一般是锁定当前行）：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。
3） 读写阻塞与事务隔离级别相关。
4） 具有非常高效的缓存特性：能缓存索引，也能缓存数据。
5） 整个表和主键以Cluster方式存储，组成一颗平衡树。
6） 所有Secondary Index都会保存主键信息。
7） 支持分区，表空间，类似oracle数据库。
8） 支持外键约束，5.5以前不支持全文索引，以后支持了；使用聚集索引。
9） 和MyISAM引擎比，InnoDB对硬件资源要求比较高。
10）支持数据热备份

InnoDB引擎调优：
1）主键尽可能小，避免给Secondary index带来过大的空间负担
2）避免全表扫描，因为会使用表锁。
3）尽可能缓存所有的索引和数据，提高响应速度，减少磁盘IO消耗。
4）在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交。有开关可以控制提交方式；
5）合理设置innodb_flush_log_at_trx_commit的参数值，不要过度追求安全性。如果innodb_flush_log_at_trx_commit的值为0，log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作。
6）避免主键更新，因为这会带来大量的数据移动。

3、其他存储引擎：
CSV：将普通的CSV（字段通过逗号分隔）作为MySQL表使用；
MRG_MYISAM：将多个MyISAM表合并成为一个虚拟表；
BLACKHOLE：类似于/dev/null，不真正的存储任何数据；
MEMORY：所有数据都保存在内存中，内存表；支持hash索引；表级锁；临时表
PERFORMANCE_SCHEMA：伪存储引擎；
ARCHIVE：只支持select和insert操作；支持行级锁和专用缓存区；不支持事务；
FEDERATED：用于访问其他远程MySQL服务器的一个代理，它通过创建一个到远程ySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取；

4、查询MySQL中支持的所有存储引擎

MariaDB [(none)]> show engines\G;
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
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: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: FEDERATED
     Support: YES
     Comment: FederatedX pluggable storage engine
Transactions: YES
          XA: NO
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 10. row ***************************
      Engine: Aria
     Support: YES
     Comment: Crash-safe tables with MyISAM heritage
Transactions: NO
          XA: NO
  Savepoints: NO
10 rows in set (0.00 sec)

ERROR: No query specified

5、定义表的存储引擎

MariaDB [aaa]> create table student(id int(10),name char(20),age int(10))engine=InnoDB;   //定义存储引擎为InnoDB；
Query OK, 0 rows affected (0.18 sec)
MariaDB [aaa]> show table status like "student"\G;
*************************** 1. row ***************************
           Name: student
         Engine: InnoDB               //查看为InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: NULL
    Create_time: 2019-02-14 12:11:25
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [aaa]> 
MariaDB [aaa]> create table teachers(id int(10),name char(20),age int(10))engine=MyISAM;    //定义存储引擎为MyISAM；
Query OK, 0 rows affected (0.07 sec)

MariaDB [aaa]> show table status like "teachers"\G;
*************************** 1. row ***************************
           Name: teachers
         Engine: MyISAM          //查看为MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 8162774324609023
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-02-14 12:13:07
    Update_time: 2019-02-14 12:13:07
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [aaa]>

6、修改表的存储引擎：

MariaDB [aaa]> ALTER TABLE student ENGINE = MyISAM;        //修改student表的存储引擎为MyISAM；
Query OK, 0 rows affected (0.05 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [aaa]> show table status like "student"\G;
*************************** 1. row ***************************
           Name: student
         Engine: MyISAM            //现在查看student表的存储引擎已经改为了MyISAM；
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 8162774324609023
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-02-14 12:16:22
    Update_time: 2019-02-14 12:16:22
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [aaa]>
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值