mysql学习笔记（二）——数据库引擎

什么是数据库引擎？

数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使⽤数据引擎进⾏创建、查询、更新和删除数据操作。
不同的存储引擎提供不同的存储机制、索引技巧、锁定⽔平等功能，使⽤不同的存储引擎，还可以获得特定的功能。
现在许多不同的数据库管理系统都⽀持多种不同的数据引擎。
MySQL的核⼼就是存储引擎。

数据库引擎简述

MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理⾮事务安全表的引擎。在MySQL中，不需要在整个服务器中使⽤⼀种引擎，针对具体要求可以
对每⼀个表使⽤不同的存储引擎。MySQL5.5⽀持的存储引擎有：InnoDB、
MyISAM、Memory等。查看引擎的命令⽤
SHOW ENGINES\G

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM 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: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
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: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
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)

mysql> 

查看默认存储引擎：

mysql> SHOW variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

数据库引擎特性

• 存储引擎说⽩了就是数据存储的格式，不同的存储引擎功能不同，占⽤的空间⼤⼩不同，读取性能也不同
• 数据库存储引擎是数据库底层软件组件，不同的存储引擎提供不同的存储机制
• 在 MySQL 中，不需要在整个服务器中使⽤同⼀种存储引擎，可以对每⼀个表使⽤不同的存储引擎
• MySQL ⽀持多种存储引擎，如 InnoDB 、MyISAM 、Memory 、Merge 、Archive 、CSV 、Federated 等等

InnoDB 存储引擎与MyISAM 存储引擎对比

MyISAM 存储引擎特点：

1. MySQL 5.5 之前使⽤ MyISAM 引擎，MySQL 5.5 之后使⽤InnoDB 引擎
2. MyISAM 引擎读取速度较快，占⽤资源相对较少，不⽀持事务，不⽀持外键约束，但⽀持全⽂索引
3. 读写互相阻塞，也就是说读数据的时候你就不能写数据，写数据的时候你就不能读数据
4. MyISAM 引擎只能缓存索引，⽽不能缓存数据

MyISAM 适⽤场景：

1. 不需要事务⽀持的业务，例如转账就不⾏
2. 适⽤于读数据⽐较多的业务，不适⽤于读写频繁的业务
3. 并发相对较低、数据修改相对较少的业务
4. 硬件资源⽐较差的机器可以考虑使⽤ MyISAM 引擎

InnoDB 存储引擎特点

1. 事务型数据库的⾸选引擎，⽀持事务安全表，⽀持⾏锁定和外键
2. 具有提交、回滚和崩溃恢复能⼒的事务安全存储引擎，能处理巨⼤数据量，性能及效率⾼，完全⽀持外键完整性约束
3. 具有⾮常⾼效的缓存特性，能缓存索引也能缓存数据，对硬件要求⽐较⾼
4. 使⽤ InnoDB 时，将在 MySQL 数据⽬录下创建⼀个名为 ibdata1 的 10MB ⼤⼩的⾃动扩展数据⽂件，以及两个名为 ib_logfile0 和 ib_logfile1 的 5MB ⼤⼩的⽇志⽂件

InnoDB 适⽤场景：

1. 需要事务⽀持的业务、⾼并发的业务
2. 数据更新较为频繁的场景，⽐如 BBS、SNS、微博等
3. 数据⼀致性要求较⾼的业务，⽐如充值转账、银⾏卡转账

Memory 存储引擎特点：

• Memory 存储引擎将表中的数据存储到内存中，为查询和引⽤其他表数据提供快速访问
• Memory 存储引擎执⾏ HASH 和 BTREE 索引，不⽀持 BLOB 和 TEXT 列，⽀持 AUTO_INCREMENT 列和对可包含 NULL 值得列的索引
• 当不再需要 Memory 表的内容时，要释放被 Memory 表使⽤的内存，应该执⾏DELETE FROM 或 TRUNCATE TABLE ，或者删除整个表

存储引擎的选择

• 如果要提供提交、回滚和崩溃恢复能⼒的事务安全能⼒，并要求实现并发控制 InnoDB 是个很好的选择
• 如果数据表主要⽤来插⼊和查询记录，则 MyISAM 引擎能提供较⾼的处理效率
• 如果只是临时存放数据，数据量不⼤，并且不需要较⾼的安全性，可以选择将数据保存在内存中的 Memory 引擎，MySQL 使⽤该引擎作为临时表，存放查询的中间结果
• 如果只有 INSERT 和 SELECT 操作，可以选择 Archive 引擎，⽀持⾼并发的插⼊操作，如记录⽇志信息可以使⽤ Archive 引擎

这三种引擎区别如下图：

补充：
在mysql存储方面，每个数据库最多可创建20亿个表，⼀个表允许定义1024列，每⾏的最⼤⻓度为8092字节（不包括⽂本和图像类型的⻓度），所以，理论上⽆限⼤ 就看你硬⽚够不够⼤ ⼤多数情况先是你的硬⽚不够。