←MySQL 查询缓存
Mysql用户管理→

InnoDB 存储引擎

MySQL 的默认存储引擎 InnoDB 提供了高可靠性和高性能，以及以下主要优势：

  • 事务安全（符合 ACID）
  • MVCC（多版本并发控制）——InnoDB 行级锁定和 Oracle 风格的一致非锁定读取。
  • 排列表数据以优化基于主键的查询
  • 支持外键参照完整性约束
  • 大数据量的最佳性能
  • 混合查询不同存储引擎的表
  • 崩溃后快速自动恢复
  • 用于在内存中缓存数据和索引的缓冲池

任何其他与磁盘相关的关系数据库引擎都难以与 InnoDB 的效率相媲美。
以下是使用 InnoDB 的一些另外优势：

  • 事务安全：通过事务提交、回滚和崩溃恢复功能实现 ACID 合规性，以保护用户数据。
  • 外键支持：包括级联删除和更新
  • 恢复和备份：支持一致和在线逻辑备份
  • 混合查询：在同一语句中，我们可以将 InnoDB 表与来自其他 MySQL 存储引擎的表混合。例如，我们可以使用连接操作在单个查询中组合来自 InnoDB 和 MEMORY 表的数据。
  • 全文索引：可以在文本列中高效搜索单词或者短语

表空间目录结构

除了表空间文件之外，InnoDB 存储引擎还管理一组 InnoDB 特定的日志文件，其中包含有关正在进行的事务的信息。
当客户端执行事务时，它所做的更改保存在 InnoDB 日志 (ib_logfile) 中。
较新的日志内容缓存在内存中。
通常，缓存的日志信息在事务提交时写入并刷新到磁盘上的日志文件，尽管这也可能发生得更早。

在 MySQL 数据库中使用 NoSQL

要从 memcached 获得最大好处，请将其用作快速键/值访问方法，并带有 InnoDB 后备存储。
memcached 协议不支持简单聚合、加入数据或者 SQL 协议支持的其他功能。
使用 InnoDB 表作为 memcached 键/值对的容器，我们可以执行强大的 SQL 语句，对使用 memcached 协议创建和修改的数据进行分组、聚合和连接。

innodb_memcache.containers 表允许我们配置一个或者多个 InnoDB 表以用作 memcached 后备存储。
为键、值、标志和其他设置指定容器表的名称、架构和列。
每个容器都有一个 memcached 名称，我们可以使用它来标识用于 memcached 操作的容器。
操作以名为“default”的容器为目标，直到请求更改容器。

/usr/share/mysql/innodb_memcached_config.sql 文件创建并填充 innodb_memcache.containers 表，是配置容器的一个很好的例子。

更多： zhilu jiaocheng

数据表空间

InnoDB 创建的每个新表都会在数据库目录中设置一个 .ibd 文件，以伴随表的 .frm 文件。
.ibd 文件充当表自己的表空间文件，InnoDB 存储表内容和索引。
我们仍然需要共享表空间，因为它包含 InnoDB 数据字典和回滚段。
我们可以使用 innodb_file_per_table 选项控制此设置。
我们需要使用默认设置才能使用其他一些功能，例如表压缩和快速截断。

我们可以通过使用 skip_innodb_file_per_table 选项或者将 innodb_file_per_table 选项设置为 OFF 将数据存储在共享数据库中，而不是将数据存储在每个表的表空间中。
禁用该选项不会影响已创建的任何 InnoDB 表的可访问性。
这些表仍然可以访问。
我们可以在同一数据库中的不同表之间混合使用表空间类型。
更改设置只会更改创建的新表的默认值，或者更改表以将引擎设置为 InnoDB（甚至已经使用 InnoDB 的表）。

显示存储引擎设置

通过显示 storage_engine 变量的当前值来确认设置：

mysql> SELECT @@default_storage_engine; 
+--------------------------+ 
| @@default_storage_engine | 
+--------------------------+ 
| InnoDB                   | 
+--------------------------+

使用 SHOW 确认每个表的存储引擎：

SHOW CREATE TABLE City\G 
SHOW TABLE STATUS LIKE 'CountryLanguage'\G

例如：

mysql> SHOW CREATE TABLE City\G 
*** 1. row *** 
Table: City 
Create Table: CREATE TABLE `City` ( 
... 
) ENGINE=InnoDB AUTO_INCREMENT=4081 DEFAULT CHARSET=latin1
mysql> SHOW TABLE STATUS LIKE 'CountryLanguage'\G 
*** 1. row *** 
Name: CountryLanguage 
Engine: InnoDB

使用 INFORMATION_SCHEMA 确认每个表的存储引擎：

SELECT TABLE_NAME, ENGINE FROM 
INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'City' 
AND TABLE_SCHEMA = 'world_innodb'\G

共享表空间配置

我们可以通过添加数据文件来增加表空间大小。
在 my.cnf 文件中使用 innodb_data_file_path 选项。
该值必须是规范列表：

[mysqld] 
innodb_data_file_path=datafile_spec1[;datafile_spec2]...

如果命名了多个数据文件，请用分号 (;) 字符分隔它们，如下例所示。

配置示例：使用名为 ibdata1 的 50 MB（固定）数据文件和名为 ibdata2 的自动扩展的 50 MB 数据文件创建一个表空间：

[mysqld] 
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

文件默认放置在数据目录中。
如果需要，明确指定文件位置。

增加 InnoDB 系统表空间大小的一种更简单的方法是从一开始就将其配置为自动扩展。
为表空间定义中的最后一个数据文件指定 autoextend 属性。
然后 InnoDB 会在空间不足时以 64 MB 的增量自动增加该文件的大小。
我们可以通过设置 innodb_autoextend_increment 系统变量的值来更改增量大小，该值以 MB 为单位。
这种方法的另一个好处是我们不需要重新启动服务器。

如果最后一个数据文件是用关键字 autoextend 定义的，那么重新配置表空间的过程必须考虑到最后一个数据文件已增长到的大小。
获取数据文件的大小，向下取整到最接近的1024×1024字节（=1MB）倍数，并在innodb_data_file_path中明确指定取整后的大小。
还存在）。
请记住，只有 innodb_data_file_path 中的最后一个数据文件可以指定为自动扩展。

设置存储引擎

如果创建表时没有使用 ENGINE 选项显式指定存储引擎，则 MySQL 服务器使用默认引擎创建表，该引擎由 storage_enginesystem 变量的值给出。

将服务器存储引擎设置为启动配置文件的一部分：

[mysqld] 
default-storage-engine=[Storage Engine]

我们可以使用 SET 命令在会话级别覆盖服务器默认值。
例如：

SET @@storage_engine=[Storage Engine];

使用 CREATE TABLE 语句指定：

CREATE TABLE t (i INT) ENGINE = [Storage Engine];

InnoDB 系统表空间

InnoDB 使用两个主要的基于磁盘的资源运行：

  • 表空间：将表内容（数据行）和索引存储在单个逻辑存储区域中
  • 日志文件：记录回滚和恢复的事务活动

InnoDB 在表空间中存储数据、索引、元数据、日志和缓冲区。
默认情况下，数据和索引存储在每个表的表空间中。
InnoDB 使用共享表空间来包含元数据、撤消日志、更改缓冲区和双写缓冲区。

共享表空间可以占用多个文件。
我们可以将共享表空间中的最终文件配置为自动扩展，在这种情况下，如果表空间已满，InnoDB 会自动扩展它。

默认情况下，共享表空间还包含一个回滚段。
当事务修改行时，撤消日志信息存储在回滚段中。
此信息用于回滚失败的事务。
通过将 innodb_undo_logs 选项设置为非零值并配置 innodb_undo_tablespaces 的值，将回滚段移出共享表空间文件。

存储引擎和 MySQL

创建表时，MySQL 使用 InnoDB 存储引擎在硬盘上为该表创建存储。
我们可以为每个表选择一个备用存储引擎。
通常，我们根据哪个存储引擎提供最适合应用程序需求的功能来做出此选择。
每个存储引擎都有一组特定的操作特性。
这些特征包括用于管理查询争用的锁类型，以及存储引擎是否支持事务。
这些引擎属性对查询处理性能、并发性和死锁预防有影响。

尽管还有许多其他存储引擎可用，但 InnoDB 最适合大多数用例。

InnoDB 作为默认存储引擎

新表的默认存储引擎是 InnoDB。
使用 ENGINE=[Storage Engine] 等子句创建或者更改表时，请选择备用存储引擎。
InnoDB 内置于 MySQL 服务器并解决了许多需要高效存储引擎的行业趋势：

  • 不断上升的硬盘和内存容量
  • 不断增长的性能价格比要求
  • 提高需要可靠性和崩溃恢复的性能
  • 越来越大、繁忙、健壮、分布式和重要的 MySQL 数据库

日志文件和缓冲区：图表

当客户端执行事务时，它所做的更改保存在 InnoDB 日志中。
较新的日志内容缓存在内存（日志缓冲区）中。
缓存的日志信息在事务提交时写入并刷新到磁盘上的日志文件，尽管这也可能发生得更早。
如果在修改表时发生崩溃，日志文件将用于自动恢复。
当 MySQL 服务器重新启动时，它会重新应用日志中记录的更改以确保表反映所有已提交的事务。

InnoDB 维护自己的缓冲池，用于缓存主内存中经常使用的数据和索引。
InnoDB 将其表和索引存储在表空间中。
InnoDB 表可能非常大，即使在文件大小限制为 2 GB 的操作系统上也是如此。
我们可以启用多个缓冲池以最大程度地减少争用。
此缓存适用于如此多类型的信息，并大大加快了处理速度，因此我们应该将最多 80% 的数据库服务器物理内存分配给 InnoDB 缓冲池。

如果未指定 InnoDB 配置选项，MySQL 会在 MySQL 数据目录中创建一个名为 ibdata1 的自动扩展 10 MB 数据文件和两个名为 ib_logfile0 和 ib_logfile1 的 5 MB 日志文件。
为了获得良好的性能，我们应该指定显式 InnoDB 参数。
我们可以使用 innodb_log_files_in_group 选项选择不同数量的 InnoDB 日志文件。
默认（和推荐）值为 2，生成两个文件 ib_logfile0 和 ib_logfile1.

要更改 ib_logfile* 文件的位置，请使用 innodb_log_group_home_dir 选项指定目录路径。
如果我们希望通过将日志文件存储在与包含数据文件的物理设备不同的物理设备上来最大限度地降低硬件故障的风险，这将非常有用。

为性能配置缓冲池

当 InnoDB 缓冲池很大时，可以从内存而不是硬盘中快速检索许多数据请求。
MySQL 使用多个缓冲池作为大型缓冲池的性能增强，通常在数 GB 范围内。
缓冲池中的每个页面通过使用散列函数随机分配给其中一个缓冲池。
每个缓冲池管理自己的空闲列表、刷新列表、LRU 和所有其他连接到缓冲池的数据结构，并由自己的缓冲池互斥锁保护。

默认情况下，MySQL 配置了 8 个缓冲池实例（32 位 Windows 系统除外）。
要更改此设置，请将 innodb_buffer_pool_instances 配置选项设置为 1（最小值）到 64（最大值）之间的值。
此选项仅在我们将 innodb_buffer_pool_size 设置为 1 GB 或者更大的大小时生效。
我们指定的总大小在所有缓冲池之间分配。
我们应该指定 innodb_buffer_pool_instances 和 innodb_buffer_pool_size 的组合，以便每个缓冲池实例至少为 1 GB。

要在服务器重新启动时预加载缓冲池，请启用以下选项：
innodb_buffer_pool_dump_at_shutdown 和
innodb_buffer_pool_load_at_startup 。

NoSQL 和 memcached API

InnoDB 支持带有集成 memcached 守护进程的插件，它在相同的进程空间中运行，以低开销进行高效的数据通信。
通过使用更简单的 memcached API（基于文本和二进制协议）而不是使用 SQL 层进行某些操作，应用程序可以绕过提交 SQL 语句时所需的解析和优化阶段，并避免检查强类型数据的开销.这种类型的应用程序称为 NoSQL（Not Only SQL）。

除了经典的 memcached 环境（在 memcached 重新启动时会丢失键/值存储）之外，InnoDB 集成意味着我们可以将 memcached 存储配置为持久保存到 InnoDB。
由于存储由 InnoDB 支持，我们可以从 InnoDB 的其他功能中受益，例如缓冲数据访问和崩溃恢复。
这意味着我们可以获得使用内存存储的 memcached 存储的速度和简单性，并由强大且持久的 InnoDB 表提供支持。

我们仍然可以通过 SQL 访问底层表进行报表、分析、即席查询、批量加载、集合操作（例如并集和交集）以及其他非常适合表现力和灵活性的操作（例如汇总和聚合）的 SQL。
由于 memcached 占用的 CPU 相对较少，并且其内存占用易于控制，因此它可以在同一系统上与 MySQL 实例一起舒适地运行。

MySQL 存储引擎

配置内存缓存

innodb_memcached_config.sql 脚本设置 memcached 配置数据库及其表。
默认的 InnoDB 后备存储是 test 数据库中的 demo_test 表。
我们可以通过修改 innodb_memcache 数据库中的容器表来更改此设置。

配置内存缓存表：

SOURCE [MYSQL_HOME]/scripts/innodb_memcached_config.sql

abive 命令创建 innodb_memcache 数据库，其中包含以下表格：

  • cache_policies ：保存每个 memcached 操作的后备存储策略；本地 内存 缓存、InnoDB 或者两者。
  • Containers ：保存用作 memcached 后备存储的每个 InnoDB 表的配置。
  • config_options ：保存 separator 和 table_map_delimiter 配置选项的值。

安装 memcached 插件：

INSTALL PLUGIN daemon_memcached SONAME "libmemcached.so";

该插件启用了许多可配置的变量。
要查看 memcached 配置变量，请发出以下命令：

SHOW VARIABLES LIKE 'daemon_memcached%';

例如，以下选项配置了 memcached 插件的行为：

  • daemon_memcached_enable_binlog ：为 memcached 操作启用二进制日志记录，从而启用 memcached API 操作的复制
  • daemon_memcached_r_batch_size ：出于性能考虑，控制读取批量大小。
  • daemon_memcached_w_batch_size ：控制写入批量大小。默认值为 1，因此每个 memcached 写操作都会立即提交。

可用的存储引擎

MySQL 提供以下存储引擎：

  • InnoDB
  • MyISAM
  • MEMORY
  • ARCHIVE
  • FEDERATED
  • EXAMPLE
  • BLACKHOLE
  • MERGE
  • NDBCLUSTER
  • CSV

第三方存储引擎也可用。

MySQL 提供并维护了多种存储引擎。
MySQL 服务器还兼容许多第三方存储引擎。
MySQL 存储引擎是数据库服务器内部的低级引擎，负责存储和检索数据，可以通过内部 MySQL API 访问，或者在某些情况下，可以由应用程序直接访问。
请注意，在任何给定时间，一个应用程序可以使用多个存储引擎。
幻灯片列出了当前支持的存储引擎。
本课概述了一些常用的 MySQL 存储引擎。
请注意，InnoDB 和 NDBCLUSTER 是仅有的两个事务性 MySQL 存储引擎。

第三方引擎具有不同的来源和特性，并且不受 MySQL 支持。
有关这些引擎的更多信息、文档、安装教程、错误报告或者任何帮助或者帮助，请直接联系引擎的开发人员。

将现有表转换为 InnoDB

当前使用其他存储引擎的表可以使用 ALTER TABLE 更改为 InnoDB 表。
例如：

ALTER TABLE t ENGINE = InnoDB;

使用 ALTER TABLE 更改引擎是一项代价高昂的操作，因为它会在内部将所有数据从一个引擎复制到另一个引擎。
不要将mysql数据库中的MySQL系统表（如用户或者主机）转换为InnoDB。
这是不受支持的操作。
系统表使用 MyISAM 引擎。

当我们使用另一个存储引擎创建作为表的克隆的 InnoDB 表时，我们还可以在插入数据后创建索引。
按照下面概述的步骤从另一个存储引擎克隆表：

  1. 创建一个具有相同定义的空 InnoDB 表。

  2. 创建适当的索引。

  3. 插入行：

INSERT INTO [innodb_table] SELECT * FROM [other_table];
  1. 将大表插入小块以获得更好的控制。

为了更好地控制插入过程，我们可以分块插入大表：

INSERT INTO newtable SELECT * FROM oldtable 
WHERE yourkey > something AND yourkey <= somethingelse;

在大表转换过程中，增加InnoDB缓冲池的大小以减少磁盘I/O。
我们还可以增加 InnoDB 日志文件的大小。
当我们第一次在启用 InnoDB 的情况下启动服务器时，最好从命令提示符运行 MySQL 服务器 mysqld，而不是从 mysqld_safe 或者作为 Windows 服务。

键/值数据

memcached 协议支持 add、delete、set、get、incr 和 ,decr 等操作。

每个操作都使用缓存中的键/值对。

  • 键和值作为字符串传递。
  • 键不能包含空格、回车、换行、制表符等空白字符。
  • 键类似于表中的主键，值类似于同表中的第二列。

Cache 是一个平面命名空间，可确保每个键都是唯一的。
例如，如果我们要表示来自不同表的键/值对，请在键前添加表名。
值可以是简单的字符串（或者存储为字符串的数字）或者序列化的复杂数据。
使用 memcached 的主要好处是它在读取或者写入键/值数据时的效率。
从概念上讲，这类似于两列表，其中键列是主键（因此是唯一的）。
键和值都存储为字符串，并且由于 memcached 协议的性质，键值不能包含空格或者换行符。

memcached 插件使用的直接、低级数据库访问路径对于键/值查找比等效的 SQL 查询更有效。
值存储为字符串，可以表示数字或者字符串等简单值，也可以表示经过序列化（即放入可以重构原始指针和嵌套的文本形式）的复杂值。
我们必须在 API 接口的 memcached 之外执行此序列化（例如，通过在代码中实现 Java Serializable 接口）或者通过将复杂数据格式化为 JSON 或者 XML。

同样，在访问 memcached 表的 SQL 语句中，必须将简单数值从字符串转换为数字（例如，通过使用 CAST() 函数）。

日期：2020-09-17 00:11:06 来源：oir作者：oir
←MySQL 查询缓存
Mysql用户管理→

目录

Copyright © 2017-2019 OnITRoad.com All rights reserved.  桂ICP备20001160号-1  关于 | 隐私和政策