一、数据库存储引擎概念¶

存储引擎就是数据库服务中的文件系统，用户可以根据应用的需要选择如何存储和索引数据，是否使用事务等；

二、数据库存储引擎种类¶

在各种版本的数据库服务中，是有多种存储引擎可以应用的，以MySQL数据库服务为例，可以使用命令查看可以应用存储引擎：

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

引擎类型信息输出：

Day007-数据库服务存储引擎知识-图1

在实际场景中，99.9%都是使用innodb存储引擎，并且在最新版8.0数据库中，所有mysql数据库中的表对应的引擎也都改为了innodb；

如果在面试环节中，面试官询问你：

列举出mysql中支持的存储引擎种类：InnoDB、MyISAM、CSV、MEMORY；
列举出mysql分支产品的存储引擎种类：在percone、mariadb数据库中，可能还会应用TokuDB MyRocks Rocksdb存储引擎

从特点上可以支持innodb引擎的特性(支持事务)，并且数据压缩比比较高(15倍)，数据插入性能比较强(5~6倍)；

以上存储引擎就比较适合于zabbix监控类的平台，归档数据、历史数据存储业务等，数据量级比较大的情况；

监控服务部署tokuDB存储引擎参考链接：https://www.cnblogs.com/oldboy-heqing/articles/16891210.html

三、数据库存储引擎特性¶

在数据库服务领域，大部分场景下都会使用innodb存储引擎，是因为innodb存储引擎具有一定优秀特性：

序号	特性	解释说明
01	数据访问特性	支持多版本并发控制特性(MVCC)，支持行级锁控制并发
02	数据索引特性	支持聚簇索引/辅助索引特性，可以组织存储数据和优化查询(IOT)
03	数据事务特性	支持事务概念特性，可以实现数据的安全保证
04	数据缓冲特性	支持多缓冲区功能，自适应hash索引(AHI)
05	数据迁移特性	支持复制数据中的高级功能特性，支持数据备份恢复的热备
06	服务自愈特性	支持自动故障恢复(CR-Crash Recovery)
07	数据存储特性	支持数据双写机制(Double write) 数据存储有关的安全机制

如果在面试环节中，面试官询问你：

InnoDB核心特性有哪些，以及与MyISAM存储引擎之间的区别：

InnoBD支持：事务、mvcc、聚簇索引、外键、缓冲区、AHI、DW；MyISAM均不支持

InnoDB支持：行级锁，MyISAM只支持表级锁；

InnoDB支持：数据热备，可以保证业务正常运行，对业务影响低，MyISAM只支持温备份，需要锁表备份；

InnoDB支持：支持CR自动故障恢复，宕机自动恢复，数据安全和一致性可以得到保证；MyISAM不支持，宕机可能丢失当前数据；

企业实际场景案例分析说明：

案例说明01：某期学员负责运维+MySQL相关工作；

平台环境：zabbix 3.2 + centos 7.3 + mariadb 5.5（InnoDB引擎），利用监控平台，监控了2000多个节点服务；

问题现象：每隔一段时间zabbix服务就会运行操作很卡，每隔3-4个月，都要重新部署zabbix，存储空间经常爆满（ibdata1 400~500G）；ibdate1 ibdata1-01

异常分析：

zabbix版本过低，建议将zabbix程序进行升级更新；
zabbix使用的数据版本过低，建议将数据库版本进行升级，因为新版本数据库的原生态环境就比旧版本好些；
在数据库5.5版本中，在没有做数据存储调配时，数据库所有数据都会保存到ibdata1文件中；
在ibdata1文件中的数据空间，不会因为数据库中的数据删除，产生数据回缩的效果，即空间不释放；

优化建议：

数据库版本升级到percona 5.7+（mariadb 10.x+），zabbix软件升级更高版本；
数据库服务存储引擎改为tokudb；
监控数据最好按月份进行数据切割（二次开发zabbix程序，数据保留机制功能重写，并且数据库分表存储）
将数据库服务的binlog功能关闭（双1）；
数据库服务相关内存优化参数调整；

优化思路：

zabbix程序原生态支持TokuDB，经过压力测试，5.7要比5.5数据库版本性能高出 2~3倍；
使用TokuDB作为数据库存储引擎，insert数据比innodb要快的多，数据压缩比也要比Innodb高；
监控数据按月份进行切割（分区），为了能够truncate每个分区表，以便立即释放存储空间；
将数据库服务binlog关闭，是为了减少无关日志的记录，避免磁盘IO的消耗，以及节省磁盘空间的使用；
参数优化调整，主要是对安全性参数或内存相关参数调整，提高数据库服务运行性能；

企业案例资料参考：

https://mariadb.com/kb/en/installing-tokudb

https://docs.percona.com/percona-server/5.7/tokudb/tokudb_intro.html

https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_installation.html

案例说明02：企业客户实际数据库需求

平台环境：centos 5.8+mysql 5.0版本，MyISAM存储引擎+网站架构LNMP，数据量50G左右

问题现象：业务并发压力大的时候，整体网站访问非常卡，还会出现数据库服务宕机情况，造成部分数据丢失

问题分析：

MyISAM存储引擎具有表级锁，在高并发访问时，会频繁出现锁等待情况；
MyISAM存储引擎不支持事务机制，在断电或宕机时，会有可能丢失数据信息；

优化建议：

数据库服务版本进行升级，从5.0升级到更高的版本；ok
数据库服务升级后，迁移所有表数据到新环境（表空间迁移），调整存储引擎为InnoDB；
数据库服务开启双1安全参数；
数据库服务进行重构主从架构

四、数据库存储引擎应用¶

4.1 引擎基本操作说明¶

数据库存储引擎信息查看：

# 查看数据库可用存储引擎
mysql> show engines;

# 查看数据库默认存储引擎
mysql> select @@default_storage_engine;
+----------------------------------+
| @@default_storage_engine |
+----------------------------------+
| InnoDB                                    |
+----------------------------------+
1 row in set (0.00 sec)

数据库存储引擎配置修改：

# 永久修改存储引擎配置(重启数据库服务生效)
## 在服务端添加default_storage_engine=InnoDB这行配置信息
[root@master ~]# vim /data/3306/data/my.cnf
[mysql]
socket=/tmp/mysql.sock
default-character-set=utf8mb4
sql_safe_updates=1

[mysqld]
default_storage_engine=InnoDB
init-file=/opt/init.sql
port=3306
server_id=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
innodb_fast_shutdown=0
character-set-server=utf8mb4

数据表存储引擎信息查看：（具体表的存储引擎）

# 查看建表语句获取存储引擎信息，观察到ENGINE对应的值为InnoDB
mysql> show create table db18.test01;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                   |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------+
| test01 | CREATE TABLE `test01` (
  `id` int NOT NULL,
  `name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 查看information_schema数据库获取存储引擎信息,获取所有非系统库的系统表的引擎信息
mysql > select table_schema,table_name,engine from information_schema.tables where table_schema not in('sys','mysql','information_schema','performance_schema');

数据表存储引擎配置修改：（具体表的存储引擎）

# 创建表时设置存储引擎
mysql > create table xxx (id int) engine=innodb charset=utf8mb4;

# 修改表示设置存储引擎
mysql > alter table db18.xxx engine=myisam;

# 假设设置存储引擎为myisam,进行结果验证。观察到ENGINE=MyISAM
mysql> show create table xxx;
+-------+-----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------+
| xxx   | CREATE TABLE `xxx` (
  `id` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 还原环境
mysql > alter table db18.xxx engine=innodb;

MySQL存储引擎基础：概念、种类、特性与引擎选择

一、数据库存储引擎概念¶

二、数据库存储引擎种类¶

三、数据库存储引擎特性¶

四、数据库存储引擎应用¶

4.1 引擎基本操作说明¶

评论区