最新要闻

广告

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

 警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

  1. 首页
  2. 移动通信
  3. 内容

MySQL存储引擎_新视野

来源：博客园

一：存储引擎概念

存储引擎其实就是对于数据库文件的一种存取机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。MySQL中的数据用各种不同的方式存储在文件（或内存）中，每一种存储的方式都使用不同的存储机制，索引技巧等，最终提供广泛的不同功能和能力。在MySQL中将这些不同的技术及配套的相关功能称为存储引擎。

1：MySQL逻辑架构

这里主要注意引擎层，因为本文主要围绕索引展开，具体的MySQL逻辑架构参考：MySQL逻辑架构及执行过程第一层：连接层，所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的：连接处理，身份验证，安全性等。第二层：服务层，这是MySQL的核心部分。通常叫做SQL Layer。在MySQL数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权       限判断，sql解析，行计划优化，query cache的处理以及所有内置的函数(如日期，时间，数学运算，加密)等等。各个存储引擎提供的       功能都集中在这一层，如存储过程，触发器，视图等。第三层：引擎层。通常叫做StorageEngine Layer，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。它们负责存储和获取所有       存储在MySQL中的数据。就像Linux众多的文件系统一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交       互的。这个接口隐藏了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事物，或者取       出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器的请求。第四层：存储层。将数据存储在文件系统上，并完成与存储引擎的交户。

二：MySQL查询与修改引擎

1：查询支持的存储引擎

可以通过SQL命令查看支持的存储引擎：SHOW engines;+--------------------+---------+------------------+--------------+------+------------+    | Engine             | Support | Comment          | Transactions | XA   | Savepoints |    +--------------------+---------+------------------+--------------+------+------------+    | ndbcluster         | NO      | Clustered, fau...| NULL         | NULL | NULL       |    | FEDERATED          | NO      | Federated MySQ...| NULL         | NULL | NULL       |    | MEMORY             | YES     | Hash based, st...| NO           | NO   | NO         |    | InnoDB             | DEFAULT | Supports trans...| YES          | YES  | YES        |    | PERFORMANCE_SCHEMA | YES     | Performance Sc...| NO           | NO   | NO         |    | MyISAM             | YES     | MyISAM storage...| NO           | NO   | NO         |    | ndbinfo            | NO      | MySQL Cluster ...| NULL         | NULL | NULL       |    | MRG_MYISAM         | YES     | Collection of ...| NO           | NO   | NO         |    | BLACKHOLE          | YES     | /dev/null stor...| NO           | NO   | NO         |    | CSV                | YES     | CSV storage en...| NO           | NO   | NO         |    | ARCHIVE            | YES     | Archive storag...| NO           | NO   | NO         |    +--------------------+---------+------------------+--------------+------+------------+字段说明Engine：存储引擎名称Support：是否支持该引擎以及该引擎是否为默认存储引擎，YES表示支持，NO表示不支持，DEFAULT表示默认引擎Comment：存储引擎的简单介绍Transactions：表示该引擎是否支持事务XA：说明该存储引擎是否支持分布事务Savepoints：说明该存储引擎是否支持部分事务回滚

2：修改系统默认的存储引擎

查询会话级别的默认存储引擎：SHOW SESSION VARIABLES LIKE "default_storage_engine";    SELECT @@SESSION.default_storage_engine;      +----------------------------------+      | @@SESSION.default_storage_engine |      +----------------------------------+      | InnoDB                           |      +----------------------------------+Ⅰ：临时修改全局（系统级别）的存储引擎：  SET @@GLOBAL.default_storage_engine=MyISAM;  SELECT @@GLOBAL.default_storage_engine;    +---------------------------------+    | @@GLOBAL.default_storage_engine |    +---------------------------------+    | MyISAM                          |    +---------------------------------+Ⅱ：临时修改会话级别的存储引擎：    SET @@SESSION.default_storage_engine=MyISAM;    SELECT @@SESSION.default_storage_engine;      +---------------------------------+      | @@SESSION.default_storage_engine|      +---------------------------------+      | MyISAM                          |      +---------------------------------+Ⅲ：修改my.cnf配置文件（全局修改，需要重启MySQL服务）：  [mysqld]  default-storage-engine=引擎名字使用vim修改后务必重启MySQL：systemctl.mysqld

3：设置表的存储引擎

存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

创建表基本语法：CREATE TABLE [IF NOT EXISTS] [数据库名.]表名(           字段1, 数据类型 [约束条件] [默认值] [字段备注信息],           字段1, 数据类型 [约束条件] [默认值] [字段备注信息],           字段1, 数据类型 [约束条件] [默认值] [字段备注信息],           ...           [表约束条件]      )[表字符集][表校对集] [表存储引擎];创建表示例：    CREATE DATABASE dbtest1;    CREATE TABLE IF NOT EXISTS dbtest1.student(          sid INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT "主键ID",          sname VARCHAR(6) NOT NULL COMMENT "姓名",          ssex ENUM("男","女") DEFAULT "男" COMMENT "性别",          sage INT(4) COMMENT "年龄"      )CHARACTER SET UTF8 COLLATE utf8_general_ci ENGINE INNODB;  查询创建表的存储引擎：SHOW CREATE TABLE dbtest1.student\G    *************************** 1. row ***************************           Table: student    Create Table: CREATE TABLE `student` (      `sid` int NOT NULL AUTO_INCREMENT COMMENT "主键ID",      `sname` varchar(6) NOT NULL COMMENT "姓名",      `ssex` enum("男","女") DEFAULT "男" COMMENT "性别",      `sage` int DEFAULT NULL COMMENT "年龄",      PRIMARY KEY (`sid`)    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3注：UTF8默认就是utf8mb3    注：创建数据库时是不存在存储引擎设置的　　补充：　　　　也可以使用查询表状态SQL语句：SHOW TABLE STATUS LIKE "表名";　　　　严谨点就是查询某一数据库下的某一表：SHOW TABLE STATUS FROM 数据库名 WHERE name = "表名";修改表存储引擎：　　ALTER TABLE dbtest1.student ENGINE MyISAM;

三：部分存储引擎介绍

1：InnoDB引擎（重要）

InnoDB是一个事务型存储引擎，提供了对数据库ACID事务的支持，并实现了SQL标准的四种隔离级别，具有行级锁定（这一点说明锁的粒度小，在写数据时，不需要锁住整个表，因此适用于高并发情形）及外键支持（所有数据库引擎中仅有它支持外键）；该引擎的设计目标便是处理大容量数据的数据库系统，MySQL在运行时InnoDB会在内存中建立缓冲池，用于缓存数据及索引。


【资料图】

具体说明：①：MySQL从3.23.34开始就包含InnoDB存储引擎。MySQL5.5及之后版本，默认采用InnoDB引擎。    ②：InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。        可以确保事务的完整提交(Commit)和回滚(Rollback)。    ③：除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。    ④：除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。    ⑤：InnoDB的数据文件结构不同：参考MySQL数据目录⑥：InnoDB是为处理巨大数据量的最大性能设计    ⑦：对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。    ⑧：MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响缺点：①：该引擎不支持FULLTEXT类型的索引；    ②：没有保存表的行数，在执行SELECT COUNT(*) FROM表名时，需要遍历扫描全表，而MyISAM支持保存表行数；适用场景：①：经常需要更新的表，适合处理多重并发的更新请求；    ②：支持事务和外键约束；    ③：可以从灾难中恢复（通过bin-log日志等）    ④：支持自动增加列属性auto_increment

2：MyISAM引擎

MyISAM引擎是MySQL主流引擎之一，但它相比起InnoDB，没有提供对数据库事务的支持，不支持细粒度的锁（行锁）及外键，当表INSERT与UPDATE时需要锁定整个表，因此效率会低一些，在高并发时可能会遇到瓶颈；但MyISAM引擎独立与操作系统，可以在Windows及Linux上使用，这说明可以轻松的将其从Windows移植到Linux上，每当我们建立一个MyISAM引擎表时，就会在本地磁盘创建三个文件，文件名就是表名（如创建tb_demo表）：tb_demo.frm存储表定义、tb_demo.MYD存储数据、tb_demo.MYI存储索引。

具体说明：①：MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外键，        但有个严重问题就是崩溃后无法安全恢复。    ②：MySQL5.5版本之前默认的存储引擎就是MyISAM。    ③：主要优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主则使用MyISAM。    ④：针对数据统计有额外的常数存储。故而 COUNT(*) 的查询效率很高。    ⑤：MyISAM的数据文件结构不同：参考MySQL数据目录⑥：该存储引擎下主要以读为主的业务场景缺点：①：若数据库服务发生宕机后导致表损坏则不能安全恢复数据。适用场景：①：MyISAM极度强调快速读取    ②：MyIsam表中自动存储了表的行数，需要时直接获取即可    ③：适用于不需要事物支持、外键功能、及需要对整个表加锁的情形

3：Memory（Heap）引擎

Memory采用的逻辑介质是内存，每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，它的数据是放在内存中的，并且默认使用HASH索引。但是当mysqld守护进程崩溃或关闭的时候，数据会丢失。HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多。

具体说明：①：Memory同时支持哈希（HASH）索引和B+树索引。    ②：Memory表至少比MyISAM表要快一个数量级。    ③：Memory表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。        其中max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大。    ③：数据文件与索引文件分开存储。    查询表的大小：    SHOW SESSION VARIABLES LIKE "max_heap_table_size";    +---------------------+----------+    | Variable_name       | Value    |    +---------------------+----------+    | max_heap_table_size | 16777216 |    +---------------------+----------+    创建测试：        CREATE TABLE t1(id INT PRIMARY KEY, c INT) engine=Memory MAX_ROWS=3;缺点：①：要求存储的数据是数据长度不变的格式，Blob和Text类型数据不可用（长度不固定）    ②：数据易丢失，生命周期短；用完表格后表格便被删除适用场景：①：那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果    ②：目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。    ③：数据是临时的，而且必须立即能取出用到，于是可存放在内存中    ④：存储在Memory表中的数据如果突然间丢失的话也没有太大的关系

四：MyISAM和InnoDB对比

MySQL5.5之前的默认存储引擎是MyISAM(MyISAM是由早期的ISAM改良的)，5.5之后改为了InnoDB。

对比项MyISAMInnoDB
外键不支持支持
事务不支持支持
行表锁

表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作

行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作

缓存只缓存索引，不缓存真实数据

不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大

小对性能有决定性的影响

自带系统表使用YN
关注点性能：节省资源、消耗少、简单业务事务：并发写、事务、更大资源
默认安装YY
默认使用NY

.

关键词：