Mysql文档记录

一、语言结构
字符串是一个字节或字符序列，包含在单引号（’）或双引号（"）字符中
符串文字可以具有可选的字符集介绍人和COLLATE子句，以将其指定为使用特定字符集和排序规则的字符串
[_charset_name]‘string’ [COLLATE collation_name]
SELECT _latin1’string’;
SELECT _binary’string’;
SELECT _utf8’string’ COLLATE utf8_danish_ci;

有几种方法可以在字符串中包含引号字符：
一个’带引号的字符串中 '可以写成 ‘’
一个"带引号的字符串中 "可以写成 “”
通过转义字符（\）来引用引号字符
一个’带引号的字符串内 “不需要特殊对待而且不必增加一倍或逃脱。以同样的方式，” 引用的字符串里面’不需要特殊处理。

合法的比特值文字：b’01’ B’01’ 0b01

对于排序方式ORDER BY， NULL值在升序排序的其他值之前排序，在降序排序的其他值之后排序

除非非限定引用不明确，否则无需在语句中为对象引用指定限定符。假设列c1只在表中出现 t1，c2只在 t2和c两 t1和t2。任何非限定引用c在引用两个表的语句中都是不明确的，必须限定为 t1.c或t2.c表示您的表
此语句使用限定名称创建表 db1.t1：CREATE TABLE db1.t1 (i INT);

用户定义的变量
SET @var_name = expr [, @var_name = expr]

注释语法
支持三种注释样式：从一个#角色到最后一行;从-- 序列到行尾;从/*序列到以下 /序列
MySQL Server支持一些C风格注释的变种。通过使用以下格式的注释，这些使您能够编写包含MySQL扩展的代码，但仍然是可移植的：
/! MySQL-specific code /
MySQL Server会像在任何其他SQL语句中那样解析和执行注释中的代码，但其他SQL服务器将忽略这些扩展。
SELECT /! STRAIGHT_JOIN / col1 FROM table1,table2 WHERE
如果在! 字符后添加版本号，则仅当MySQL版本大于或等于指定的版本号时，才会执行注释中的语法。
CREATE TABLE t1(a INT, KEY (a)) /!50110 KEY_BLOCK_SIZE=1024 */;

四、字符集
字符串表达式有一个repertoire属性，它可以有两个值：
ASCII：表达式可以包含以Unicode范围中的字符U+0000来 U+007F
UNICODE：表达式可以包含以Unicode字符范围U+0000来 U+10FFFF。这包括基本多语言平面（BMP）范围（U+0000to U+FFFF）中的字符和BMP范围（U+10000to U+10FFFF）之外的补充字符
任何描述数据库的东西 - 而不是数据库的内容 - 都是元数据
MySQL将元数据存储在Unicode字符集中，即UTF-8
字符集特征：两个不同的字符集不能具有相同的排序规则；每个字符集都有一个默认排序规则。例如，对于默认排序规则 latin1和utf8是 latin1_swedish_ci和 utf8_general_ci分别

MySQL排序规则名称遵循以下约定：排序规则名称以与其关联的字符集的名称开头，通常后跟一个或多个后缀，指示其他排序规则特征。例如，utf8_general_ci；排序后缀表示排序规则是大小写还是重音敏感或二进制
_ai 口音不敏感
_as 口音敏感
_ci 不区分大小写
_cs 区分大小写
_bin 二进制
对于未指定重音敏感度的非二进制校对名称，它由大小写敏感性决定。如果归类名不包含_ai或者 _as，_ci在其名称所暗示_ai，并_cs在其名称所暗示_as。例如， latin1_general_ci明确区分大小写且隐式重音不敏感，并且 latin1_general_cs明确区分大小写且隐式重音敏感。

数据库字符集和校对：
每个数据库都有一个数据库字符集和一个数据库排序规则。该CREATE DATABASE 和ALTER DATABASE语句有用于指定数据库字符集和校对可选条款：
CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]

如果未指定，则使用字符集 及其默认排序规则，要查看给定数据库的缺省字符集和排序规则，请使用以下语句：USE db_name;
SELECT @@character_set_database, @@collation_database;

每个表都有一个表字符集和一个表格排序规则。该 CREATE TABLE和 ALTER TABLE语句对指定表字符集和校对可选条款
CREATE TABLE tbl_name (column_list)
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]]

ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]

CREATE TABLE t1 ( … )
CHARACTER SET latin1 COLLATE latin1_danish_ci;

每个“ 字符 ”列（即，类型列 CHAR， VARCHAR中， TEXT类型，或其任何同义词）具有列字符集和列排序规则。用于列定义的语法CREATE TABLE和 ALTER TABLE具有用于指定列的字符集和归类可选子句：col_name {CHAR | VARCHAR | TEXT} (col_length)
[CHARACTER SET charset_name]
[COLLATE collation_name]

SET NAMES 'charset_name’一个 语句相当于这三个语句：
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

utfmb4字符集有以下特点：支持BMP和补充字符;每个多字节字符最多需要四个字节
utf8每个多字节字符最多需要三个字节

五、功能和操作
以下规则描述了比较操作的转换方式：如果是一个或两个参数NULL，则比较的结果是NULL；如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较；如果两个参数都是整数，则将它们作为整数进行比较；如果不与数字进行比较，十六进制值将被视为二进制字符串；如果其中一个参数是a TIMESTAMP或 DATETIME列而另一个参数是常量，则在执行比较之前将常量转换为时间戳

BINARY 将字符串转换为二进制字符串
CAST() 将值转换为特定类型
CONVERT() 将值转换为特定类型

六、SQL语句语法
SHOW CHARACTER SET和SHOW COLLATION 语句查看可用的字符集和排序规则

七、分区
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;

分区适用于表的所有数据和索引; 您不能只分区数据而不分区索引，反之亦然，也不能只分区表的一部分。
DATA DIRECTORY并且INDEX DIRECTORY不支持MyISAMWindows上的表的各个分区或子分区。
表的DATA DIRECTORY各个分区和子分区 仅支持该选项 InnoDB。
表的分区表达式中使用的所有列必须是表可能具有的每个唯一键的一部分，包括任何主键
分区的一些优点：通过分区，可以在一个表中存储比在单个磁盘或文件系统分区上保存的数据更多的数据；通过删除仅包含该数据的分区（或多个分区），通常可以轻松地从分区表中删除失去其实用性的数据。相反，在某些情况下，通过添加一个或多个新分区来特别存储该数据，可以极大地促进添加新数据的过程；由于满足给定WHERE子句的数据只能存储在一个或多个分区上，因此可以大大优化某些查询，这会自动从搜索中排除任何剩余的分区。由于在创建分区表后可以更改分区，因此可以重新组织数据以增强在首次设置分区方案时可能不常使用的频繁查询。这种排除不匹配分区（以及它们包含的任何行）的能力通常称为 分区修剪。

1、范围分区
按范围分区的表的分区方式是每个分区包含分区表达式值位于给定范围内的行。范围应该是连续的但不重叠，并使用VALUES LESS THAN运算符定义
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT ‘1970-01-01’,
separated DATE NOT NULL DEFAULT ‘9999-12-31’,
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

2、列表分区
PARTITION BY LIST(expr)exprVALUES IN (value_list)value_list
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT ‘1970-01-01’,
separated DATE NOT NULL DEFAULT ‘9999-12-31’,
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);

3、列分区
RANGE COLUMNS分区与RANGE以下方式的分区显着不同：
RANGE COLUMNS 不接受表达式，只接受列的名称。
RANGE COLUMNS 接受一列或多列。

CREATE TABLE table_name
PARTITIONED BY RANGE COLUMNS(column_list) (
PARTITION partition_name VALUES LESS THAN (value_list)[,
PARTITION partition_name VALUES LESS THAN (value_list)][,
…]
)

column_list:
column_name[, column_name][, …]

value_list:
value[, value][, …]

CREATE TABLE customers_1 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN(‘Oskarshamn’, ‘Högsby’, ‘Mönsterås’),
PARTITION pRegion_2 VALUES IN(‘Vimmerby’, ‘Hultsfred’, ‘Västervik’),
PARTITION pRegion_3 VALUES IN(‘Nässjö’, ‘Eksjö’, ‘Vetlanda’),
PARTITION pRegion_4 VALUES IN(‘Uppvidinge’, ‘Alvesta’, ‘Växjo’)
);

KEy分区
CREATE TABLE k1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;

NULL不同类型的分区之间的处理会有所不同

从通过任意分区的表中删除分区 RANGE或LIST可以使用来实现 ALTER TABLE的发言DROP PARTITION选项
ALTER TABLE tr DROP PARTITION p2;
如果希望在保留表定义及其分区方案的同时删除所有分区中的所有数据，请使用该TRUNCATE TABLE语句

4、分区的维护
重建分区。 重建分区; 这与删除存储在分区中的所有记录，然后重新插入它们具有相同的效果
ALTER TABLE t1 REBUILD PARTITION p0, p1;

优化分区。 如果已经从一个分区删除大量行的，或者如果你已与可变长度行许多变化，以一个分区表（即，具有VARCHAR， BLOB或 TEXT列），可以使用 ALTER TABLE … OPTIMIZE PARTITION以回收任何未使用的空间和整理分区数据文件。
ALTER TABLE t1 OPTIMIZE PARTITION p0, p1

分析分区。 这将读取并存储分区的密钥分发。
ALTER TABLE t1 ANALYZE PARTITION p3;

修复分区。 这会修复损坏的分区。
ALTER TABLE t1 REPAIR PARTITION p0,p1

检查分区。 您可以使用CHECK TABLE与非分区表一样的方式检查分区是否存在错误。
ALTER TABLE trb3 CHECK PARTITION p1

5、获取分区信息
使用该SHOW CREATE TABLE 语句查看创建分区表时使用的分区子句
使用该SHOW TABLE STATUS 语句确定表是否已分区
查询 INFORMATION_SCHEMA.PARTITIONS 表格
使用该语句 EXPLAIN SELECT查看给定的分区 SELECT

显式分区选择的SQL语句：select、delete、replace、insert、update、load data、load xml
MERGE存储引擎。 用户定义的分区和MERGE 存储引擎不兼容。MERGE无法对使用存储引擎的表 进行分区。分区表无法合并。
FEDERATED存储引擎。 FEDERATED不支持 对表进行分区; 无法创建分区 FEDERATED表。
CSV存储引擎。 CSV不支持 使用存储引擎的分区表; 无法创建分区CSV表。
InnoDB存储引擎。 InnoDB外键和MySQL分区不兼容。分区 InnoDB表不能具有外键引用，也不能具有外键引用的列。InnoDB具有或由外键引用的表不能被分区。
InnoDB不支持将多个磁盘用于子分区

对DML语句的影响:
SELECT语句（包括那些包含联合或联接的语句）仅锁定实际需要读取的分区
update只对没有更新分区列的表删除锁
替换和插入要插入或替换行的分区。但是，如果为任何分区列生成了自动增量值，那么所有分区都将被锁定。
INSERT … ON DUPLICATE KEY UPDATE没有分区更新时候会修正
select … insert只锁定源表中需要读取的那些分区，尽管目标表中的所有分区都已锁定

八、优化
数据库级别进行优化:
1.表格结构合理吗？特别是，列是否具有正确的数据类型，并且每个表是否具有适合工作类型的列？例如，执行频繁更新的应用程序通常具有许多具有少量列的表，而分析大量数据的应用程序通常具有很少列的表
2.是否有适当的 索引来提高查询效率
3.您是否为每个表使用适当的存储引擎
4.每个表是否使用适当的行格式？此选择还取决于用于表的存储引擎。特别是，压缩表使用较少的磁盘空间，因此需要较少的磁盘I / O来读取和写入数据。压缩适用于具有InnoDB表的所有类型的工作负载 以及只读 MyISAM表。

1)、优化SQL语句
优化select语句
首先读取所有常量表，然后再读取查询中的任何其他表。常量表是以下任何一种：空表或只有一行的表；与主键或唯一索引上的WHERE子句一起使用的表，其中所有索引部分都与常量表达式进行比较，并定义为非空

以下所有表都用作常量表：
SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

通过尝试所有可能性，可以找到加入表格的最佳连接组合。如果ORDER BY和GROUP BY子句中的所有列 都来自同一个表，则在加入时首先首选该表；
如果存在ORDER BY子句和不同的GROUP BY子句，或者如果 ORDER BY或者GROUP BY 包含连接队列中第一个表以外的表中的列，则会创建临时表。
查询每个表索引，并使用最佳索引

要使优化器使用范围扫描，查询必须满足以下条件：
只使用IN()谓词，而不是NOT IN()；
在IN()谓词的左侧 ，行构造函数仅包含列引用
在IN()谓词的右侧 ，行构造函数仅包含运行时常量；
在IN()谓词的右侧 ，有多个行构造函数

要控制范围优化程序可用的内存，请使用 range_optimizer_max_mem_size 系统变量：值为0表示“ 无限制；值大于0时，优化程序会在考虑范围访问方法时跟踪消耗的内存

嵌套循环连接算法：
一个简单的嵌套循环连接（NLJ）算法一次一个循环地从第一个表中读取行，将每一行传递给一个嵌套循环，该循环处理连接中的下一个表。这个过程重复多次，因为还有待连接的表。

块嵌套循环连接算法：
块嵌套循环（BNL）连接算法使用外部循环中读取的行的缓冲来减少必须读取内部循环中的表的次数。例如，如果将10行读入缓冲区并将缓冲区传递给下一个内部循环，则可以将内循环中读取的每一行与缓冲区中的所有10行进行比较。这将内表必须读取的次数减少一个数量级。

Mysql的连接缓冲区有以下特点：

1）、连接缓存可以被使用，当join的类型为：All、index、range。在外连接中使用缓冲区，也被描述在：Block Nested-Loop and Batched Key Access Joins

2）、绝不会为第一个非常数表分配一个缓冲区，尽管它是All或者index类型

3）、仅会把连接中必要的列存入它的连接缓存，而不是整行数据

4）、连接缓存的大小的系统变量定义了每一个被用于查询的连接缓存的大小

5）、每一个可以被缓存的连接都会被分配一个缓冲区，所以，一个查询可以会需要使用几个连接缓存

6）、一个缓存区在它执行连接之前建立，而在查询结束后释放

条件过滤:
SELECT *
FROM employee JOIN department ON employee.dept_no = department.dept_no
WHERE employee.first_name = ‘John’
AND employee.hire_date BETWEEN ‘2018-01-01’ AND ‘2018-06-01’;
假设数据集具有以下特征：
该employee表有1024行。
该department表有12行。
两个表都有一个索引dept_no。
该employee表有一个索引 first_name。
8行符合以下条件 employee.first_name：employee.first_name = ‘John’
150行符合以下条件 employee.hire_date：employee.hire_date BETWEEN ‘2018-01-01’ AND ‘2018-06-01’
1行满足两个条件：employee.first_name = ‘John’
AND employee.hire_date BETWEEN ‘2018-01-01’ AND ‘2018-06-01’

没有条件过滤， EXPLAIN产生如下输出：
id | table | type | possible_keys | key | ref | rows | filtered |
±—±-----------±-------±-----------------±--------±--------±-----±---------+
| 1 | employee | ref | name,h_date,dept | name | const | 8 | 100.00 |
| 1 | department | eq_ref | PRIMARY | PRIMARY | dept_no | 1 | 100.00

通过条件过滤，优化器还考虑WHERE 访问方法未考虑的子句中的条件
id | table | type | possible_keys | key | ref | rows | filtered |
±—±-----------±-------±-----------------±--------±--------±-----±---------+
| 1 | employee | ref | name,h_date,dept | name | const | 8 | 16.31 |
| 1 | department | eq_ref | PRIMARY | PRIMARY | dept_no | 1 | 100.00

要控制优化程序是否考虑其他过滤条件，请使用系统变量的 condition_fanout_filter标志 optimizer_switch
如果优化器过高估计条件过滤的影响，则性能可能比不使用条件过滤时更差。

GROUP BY优化:
最有效的处理方法GROUP BY是使用索引直接检索分组列

避免全表扫描:
当MySQL使用全表扫描来解析查询时 ，输出EXPLAIN显示 ALL在 type列中 。这通常在以下条件下发生：
该表非常小，执行表扫描比使用键查找更麻烦
索引列 的ON or WHERE子句中没有可用的限制
您正在将索引列与常量值进行比较，并且MySQL已经计算（基于索引树）常量覆盖了表的一部分，并且表扫描会更快

对于大型表，请尝试以下技术以避免优化程序错误地选择表扫描：
使用更新的扫描表的键分布。ANALYZE TABLE tbl_name
使用FORCE INDEX的扫描表告诉MySQL该表扫描是非常昂贵相比，使用给定的指标：SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;
启动mysqld的与 --max-seeks-for-key=1000 选项或使用SET max_seeks_for_key=1000告诉优化器：假定任何按键扫描导致1000个以上的键搜索

分页查询的优化：
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

网上的例子，主要是利用索引来加速查询速度，感觉应该是可行的，具体调试还是要考虑到实际情况。

子查询：
在这种情况下，查询可以使用半连接 ; 也就是说，仅返回一个中的每一行的实例的操作 class由按行匹配 roster。
子查询必须满足这些条件才能作为半连接处理：它必须是SELECT 没有UNION结构的单一。

它不能包含GROUP BYor HAVING子句。
它不能隐式分组（它必须不包含聚合函数）。
它一定不能ORDER BY用 LIMIT。
该语句不得STRAIGHT_JOIN在外部查询中使用 连接类型。
该STRAIGHT_JOIN修改必须不存在。
外表和内表的数量必须小于连接中允许的最大表数。

以下限制适用于子查询实现的使用：
内表达式和外表达式的类型必须匹配；
内在表达不能是一个 BLOB

优化INFORMATION_SCHEMA查询:
表 指定列以避免数据目录扫描 指定列以避免数据库目录扫描
COLUMNS TABLE_SCHEMA TABLE_NAME
KEY_COLUMN_USAGE TABLE_SCHEMA TABLE_NAME
PARTITIONS TABLE_SCHEMA TABLE_NAME
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA TABLE_NAME
STATISTICS TABLE_SCHEMA TABLE_NAME
TABLES TABLE_SCHEMA TABLE_NAME
TABLE_CONSTRAINTS TABLE_SCHEMA TABLE_NAME
TRIGGERS EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE
VIEWS TABLE_SCHEMA TABLE_NAME

限制为特定常量数据库名称的查询的好处是只需要对指定的数据库目录进行检查。例：SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘test’;

优化数据变更声明
插入行所需的时间由以下因素决定，其中数字表示大致比例：连接：（3）
向服务器发送查询：（2）
解析查询：（2）
插入行：（1×行的大小）
插入索引：（1×索引数）
结束：（1）

可以使用以下方法来加速插入：
如果要同时从同一客户端插入多行，请使用INSERT 包含多个VALUES列表的语句一次插入多行
从文本文件加载表时，请使用 LOAD DATA

九、索引优化
索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后读取整个表以查找相关行。表越大，成本越高。如果表中有相关​​列的索引，MySQL可以快速确定要在数据文件中间寻找的位置，而无需查看所有数据。这比按顺序读取每一行要快得多。
大多数MySQL索引（PRIMARY KEY， UNIQUE，INDEX和 FULLTEXT）存储在 B树。
MySQL使用索引进行这些操作：WHERE快速 查找与子句匹配的行；消除行的考虑。如果在多个索引之间有选择，MySQL通常使用找到最小行数（最具 选择性的索引）的索引；如果表具有多列索引，则优化程序可以使用索引的任何最左前缀来查找行

十、优化数据库结构
表列：尽可能使用最有效（最小）的数据类型；
NOT NULL如果可能， 声明列。它通过更好地使用索引并消除测试每个值是否的开销来使SQL操作更快NULL

行格式：InnoDBDYNAMIC默认情况下，使用行格式创建表

索引：表的主索引应尽可能短；仅创建提高查询性能所需的索引。索引适用于检索，但会降低插入和更新操作的速度。如果您主要通过搜索列的组合来访问表，请在它们上创建单个复合索引，而不是为每列创建单独的索引；如果长字符串列很可能在第一个字符数上有唯一的前缀，那么最好只使用MySQL支持在列的最左边部分创建索引来索引此前缀

优化InnoDB表
一旦您的数据达到稳定的大小，或者增长的表增加了数十或几百兆字节，请考虑使用该OPTIMIZE TABLE语句重新组织表并压缩任何浪费的空间。

十一、查询执行计划
EXPLAIN返回SELECT语句中使用的每个表的一行信息 。它按照MySQL在处理语句时读取它们的顺序列出输出中的表。MySQL使用嵌套循环连接方法解析所有连接。这意味着MySQL从第一个表中读取一行，然后在第二个表，第三个表中找到匹配的行，依此类推。处理完所有表后，MySQL将通过表列表输出所选列和回溯，直到找到有更多匹配行的表。从该表中读取下一行，并继续下一个表

id select_id 该SELECT标识符
select_type 没有 该SELECT类型
table table_name 输出行的表
partitions partitions 匹配的分区
type access_type 连接类型
possible_keys possible_keys 可供选择的索引
key key 实际选择的指数
key_len key_length 所选键的长度
ref ref 列与索引进行比较
rows rows 估计要检查的行
filtered filtered 按表条件过滤的行的百分比
Extra 没有 附加信息

select_type：类型SELECT，可以是下表中显示的任何类型

select_type 值 JSON名称 含义
SIMPLE 没有 简单SELECT（不使用 UNION或子查询）
PRIMARY 没有 最 SELECT
UNION 没有 第二次或以后的SELECT陈述 UNION
DEPENDENT UNION dependent（true） a中的第二个或更晚的SELECT语句 UNION，取决于外部查询
UNION RESULT union_result 的结果UNION。
SUBQUERY 没有 首先SELECT是子查询
DEPENDENT SUBQUERY dependent（true） 首先SELECT在子查询中，依赖于外部查询
DERIVED 没有 派生表
MATERIALIZED materialized_from_subquery 物化子查询
UNCACHEABLE SUBQUERY cacheable（false） 无法缓存结果的子查询，必须为外部查询的每一行重新计算
UNCACHEABLE UNION cacheable（false） UNION 属于不可缓存的子查询的第二个或后一个选择（请参阅参考资料 UNCACHEABLE SUBQUERY）

rows:该rows列指示MySQL认为必须检查以执行查询的行数。
filtered：该filtered列指示将按表条件过滤的表行的估计百分比。最大值为100，这意味着不会对行进行过滤

type类型：
system该表只有一行;
const该表最多只有一个匹配行，在查询开头读取；
eq_ref对于前面表格中的每个行组合，从该表中读取一行，除了 system和 const类型之外，这是最好的连接类型
ref对于上一个表中的每个行组合，将从此表中读取所有具有匹配索引值的行
fulltext使用FULLTEXT 索引执行连接。
ref_or_null这种连接类型是这样的 ref，但除此之外，MySQL还会对包含NULL值的行进行额外搜索
index_merge此连接类型表示使用了索引合并优化
unique_subqueryunique_subquery 只是一个索引查找功能，它可以完全替换子查询以提高效率
index_subquery此连接类型类似于 unique_subquery。它替换IN子查询
range仅检索给定范围内的行，使用索引选择行可使用 =， <>， >， >=， <， <=， IS NULL， <=>， BETWEEN， LIKE，或 IN()
index该index联接类型是一样的 ALL，只是索引树被扫描。这有两种方式：如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra专栏说 Using index。仅索引扫描通常比ALL索引的大小通常小于表数据更快 。使用索引中的读取执行全表扫描，以按索引顺序查找数据行。 Uses index没有出现在 Extra列中
ALL对前面表格中的每个行组合进行全表扫描

Extra列 EXPLAIN输出包含MySQL解决查询的额外信息

估计查询性能：在大多数情况下，您可以通过计算磁盘搜索来估计查询性能。对于小型表，通常可以在一个磁盘查找中找到一行（因为索引可能已缓存）。对于更大的表，你可以估计，使用B树索引，你需要这么多寻找一行： 。 log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1
在MySQL中，索引块通常为1,024字节，数据指针通常为4字节。对于密钥值长度为3个字节（大小MEDIUMINT）的500,000行表 ，公式表示 log(500,000)/log(1024/3*2/(3+4)) + 1= 4seek。

十二、查询优化提示
控制优化器的方法是使用优化器提示，可以在各个语句中指定，例如：
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
5.7.7以后的版本支持
优化程序提示适用于不同的范围级别：全局：提示会影响整个语句
查询块：提示会影响语句中的特定查询块
表级：提示会影响查询块中的特定表
索引级别：提示会影响表中的特定索引

提示名称 描述 适用范围
BKA,NO_BKA 影响批量密钥访问联接处理 查询块，表
BNL，NO_BNL 影响块嵌套循环连接处理 查询块，表
MAX_EXECUTION_TIME 限制语句执行时间 全球
MRR，NO_MRR 影响多范围读取优化 表，索引
NO_ICP 影响指数条件下推优化 表，索引
NO_RANGE_OPTIMIZATION 影响范围优化 表，索引
QB_NAME 为查询块分配名称 查询块
SEMIJOIN， NO_SEMIJOIN 影响半连接策略 查询块
SUBQUERY 影响物化， IN到EXISTS 子查询的策略 查询块

MySQL支持SQL语句中的注释,必须在/*+ … */注释中指定优化程序提示

十三、锁定
行级锁定：MySQL 对表使用行级锁定InnoDB以支持多个会话的同时写访问，使其适用于多用户，高度并发和OLTP应用程序
为了避免在单个表上执行多个并发写操作时出现死锁， InnoDB通过SELECT … FOR UPDATE为每个预期要修改的行组发出一个语句，在事务开始时获取必要的锁，即使数据更改语句在事务中稍后出现
行级锁定的优点：
当不同的会话访问不同的行时，更少的锁冲突。
回滚的变化较少。
可以长时间锁定单行。

表级锁定：MySQL使用表级锁的MyISAM， MEMORY和MERGE 表，只允许一个会话更新一次这些表。此锁定级别使这些存储引擎更适合于只读，大多数读取或单用户应用程序。
表级锁定的优点：
需要相对较少的内存（行锁定需要每行或锁定行组的内存）
在大部分表上使用时速度很快，因为只涉及一个锁。
如果您经常GROUP BY 对大部分数据进行操作，或者必须经常扫描整个表，请快速。

您可以通过检查Table_locks_immediate和 Table_locks_waited状态变量来分析系统上的表锁争用 ，这些变量分别指示可以立即授予对表锁的请求的次数和必须等待的次数：SHOW STATUS LIKE ‘Table%’;

十四、备份和恢复
此方法适用于任何类型的数据文件，但仅保存表数据，而不保存表结构。 SELECT * INTO OUTFILE ‘file_name’ FROM tbl_name

十五、复制
本节介绍基于二进制日志文件位置方法的MySQL服务器之间的复制，其中作为主服务器运行的MySQL实例（数据库源更改）将更新和更改作为“ 事件 ”写入二进制日志。二进制日志中的信息根据记录的数据库更改以不同的日志记录格式存储。从站配置为从主站读取二进制日志，并在从站的本地数据库上执行二进制日志中的事件。每个从站都会收到二进制日志的全部内容的副本。从属设备负责决定应该执行二进制日志中的哪些语句。除非另行指定，否则主从二进制日志中的所有事件都在从站上执行。
每个从站都会记录二进制日志坐标：文件名和文件中它从主站读取和处理的位置。这意味着可以将多个从站连接到主站并执行同一二进制日志的不同部分。由于从站控制此过程，因此可以在服务器上连接和断开各个从站，而不会影响主站的操作。此外，由于每个从站都记录了二进制日志中的当前位置，因此可以断开从站的连接，重新连接然后恢复处理。
必须使用唯一ID配置主站和每个从站（使用该server-id选项）。此外，必须为每个从站配置有关主主机名，日志文件名和该文件中位置的信息。

设置复制主配置：服务器配置为使用基于二进制日志文件位置的复制，必须启用二进制日志记录并建立唯一的服务器ID。如果尚未执行此操作，则需要重新启动服务器。要配置二进制日志和服务器ID选项，请关闭MySQL服务器并编辑my.cnf或 my.ini文件。在[mysqld]配置文件的 部分中，添加log-bin和 server-id选项。
[mysqld]
log-bin=mysql-bin
server-id=1
进行更改后，重新启动服务器。
为了在使用InnoDB事务的复制设置中实现最大的持久性和一致性 ，您应该在主 文件中使用 innodb_flush_log_at_trx_commit=1和 sync_binlog=1

创建用于复制的用户：每个从站使用MySQL用户名和密码连接到主站，因此主站上必须有用户帐户；要创建新帐户，请使用CREATE USER。要授予此帐户复制所需的权限，请使用该GRANT 语句。请 在主服务器上发出以下语句：CREATE USER ‘repl’@’%.example.com’ IDENTIFIED BY ‘password’;
GRANT REPLICATION SLAVE ON . TO ‘repl’@’%.example.com’;

要获取主二进制日志坐标，请按照下列步骤操作：1、通过使用命令行客户端连接到主服务器来启动主服务器上的会话，并通过执行以下FLUSH TABLES WITH READ LOCK语句来刷新所有表和阻止写语句：
2、在master上的不同会话中，使用该 SHOW MASTER STATUS语句确定当前二进制日志文件的名称和位置

设置复制从站配置
每个复制从站必须具有唯一的服务器ID，如果尚未设置从属服务器ID，或者当前值与您为主服务器选择的值冲突，请关闭从属服务器并编辑[mysqld]配置文件的 部分以指定唯一的服务器ID。例如：
[mysqld]
server-id=2
进行更改后，重新启动服务器。
如果要设置多个从站，则每个从站必须具有server-id与主站和任何其他从站的唯一值不同的唯一值。
要将从站设置为与主站通信以进行复制，请使用必要的连接信息配置从站。为此，请在从站上执行以下语句，将选项值替换为与系统相关的实际值：CHANGE MASTER TO MASTER_HOST=‘master_host_name’,MASTER_USER=‘replication_user_name’,MASTER_PASSWORD=‘replication_password’,MASTER_LOG_FILE=‘recorded_log_file_name’,MASTER_LOG_POS=recorded_log_position;

要在主服务器和新服务器之间设置复制：启动MySQL从站并连接到它；执行CHANGE MASTER TO 语句以设置主复制服务器配置

基于事务的复制
全局事务标识符（GTID）是创建的唯一标识符，并与在源服务器（主服务器）上提交的每个事务相关联。此标识符不仅对其发起的服务器是唯一的，而且在给定复制拓扑中的所有服务器上都是唯一的。
GTID表示为一对坐标，用冒号字符（:）分隔，如下所示：GTID = source_id:transaction_id
每个事务由一个唯一的gtid标识，当slave都成功执行之后master才写入硬盘完成该事务，如果master突然宕机，那么就自动回滚。数据的一致性得到保证。
操作方法和普通的基于日志的主从复制差不了很多，主要就是打开两个开关
enforce_gtid_consistency = ON
gtid_mode = ON
Master的配置：my.cnf文件中加上：
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
enforce_gtid_consistency = ON
gtid_mode = ON

binlog的格式也有三种：STATEMENT，ROW，MIXED。
STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

从数据库：
server-id = 2
enforce_gtid_consistency = ON
gtid_mode = ON
修改数据库的UUID；查看当前mysql UUID的命令
show variables like ‘%server_uuid%’;
解决这个问题的方法是修改/var/lib/mysql/auto.cnf这个文件中的UUID，只要和其他master，slave不同即可，然后重启数据库
启动之后，通过change master语句设置master的IP地址，无需设置binlog的位置和行数，只需保证目前的库和master库内容一致即可
change master to master_host=‘Master的IP地址’, master_user=‘dba’,
master_password=‘123456’
start slave;

十六、Innodb引擎
mysql中普遍使用B+Tree做索引，但在实现上又根据聚簇索引和非聚簇索引而不同。
所谓聚簇索引，就是指主索引文件和数据文件为同一份文件，聚簇索引主要用在Innodb存储引擎中。
非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中。
“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引，因为在一个表中数据的存放方式只有一种。
聚簇索引的优点：1、聚簇索引将索引和数据行保存在同一个B+Tree中，查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高。
2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的。

聚簇索引的缺点：1、聚簇索引的更新代价比较高，如果更新了行的聚簇索引列，就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
2、插入速度严重依赖于插入顺序，按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入，最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
3、聚簇索引在插入新行和更新主键时，可能导致“页分裂”问题。
4、聚簇索引可能导致全表扫描速度变慢，因为可能需要加载物理上相隔较远的页到内存中（需要耗时的磁盘寻道操作）。

非聚簇索引：非聚簇索引，又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针，而是行的主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行，这需要两次B-Tree查找。

InnoDB是一种平衡高可靠性和高性能的通用存储引擎，是默认的MySQL存储引擎。
主要优势：它的DML操作遵循 ACID模型， 具有 提交， 回滚和 崩溃恢复 功能的事务来保护用户数据；行级锁定和Oracle风格的一致性读取可提高多用户并发性和性能；InnoDB表格将您的数据排列在磁盘上，以根据主键优化查询 ；要保持数据 完整性，请 InnoDB支持 FOREIGN KEY约束。
通过SHOW ENGINES;语句查询引擎
修改表的引擎ALTER TABLE table_name ENGINE=InnoDB;

InnoDB内存中结构：Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer
缓冲池是主存储器中的一个区域，用于在访问时缓存表和索引数据，为了提高大容量读取操作的效率，缓冲池被分成可以容纳多行的页面。为了提高缓存管理的效率，缓冲池被实现为链接的页面列表; 使用LRU算法的变体，很少使用的数据在缓存中老化 。
使用最近最少使用（LRU）算法的变体将缓冲池作为列表进行管理。当需要空间将新页面添加到缓冲池时，最近最少使用的页面被逐出，并且新页面被添加到列表的中间。此中点插入策略将列表视为两个子列表：1、在头部，最近访问过的新（“ 年轻 ”）页面 的子列表；2、在尾部，是最近访问的旧页面的子列表
InnoDB可以使用的标准监视器输出 SHOW ENGINE INNODB STATUS提供有关缓冲池操作的指标。缓冲池指标位于标准监视器输出BUFFER POOL AND MEMORY部分。

Change Buffer是 buffer pool 中的一部分内存；它既在内存中有拷贝，也可以持久化到磁盘；其大小通过参数 innodb_change_buffer_max_size 控制,表示最多占用 buffer pool的百分比；默认情况下， innodb_change_buffer_max_size设置为25.最大设置为50。
当需要更新一个数据页时，如果数据页在内存中，则直接更新；否则，在不影响数据一致性的前提下，InnoDB 将这些操作缓存在 change buffer 中，这样就不必从磁盘中读取数据，当下次查询需要访问这个数据页时，再将数据页读入内存，然后执行 change buffer 中与这个页有关的操作，最后将查询结果返回。
merge：将 change buffer 的操作应用到数据页的过程称为 merge。除了访问数据页会触发 merge 外；系统后台有线程会定期 merge；数据库正常关闭的过程中也会触发 merge 操作。
更新操作记录到 change buffer ，可以减少读磁盘，提高执行效率；而且读入数据会占用 buffer pool ，还可以提高内存使用率。
使用条件：1、对于唯一索引，所有更新操作都需要做唯一性约束的判断，必须将数据页读入内存，直接在内存中更新，不使用 change buffer ；2、对于普通索引，当数据页在内存中时，直接进行更新操作即可；当数据页不在内存中时，直接将更新操作写入 change buffer 即可。

Adaptive Hash Index：InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive） 的。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式 来为某些页建立哈希索引。
根据InnoDB的官方文档显示，启用自适应哈希索引后，读取和写入速度可以提高2倍；对于辅助索引的连接操作，性能可以提高5倍。我们可以通过参数innodb_adaptive_hash_index来禁用或启动此特性，默认为开启。

Log Buffer：日志缓冲区是保存要写入磁盘上日志文件的数据的内存区域。日志缓冲区大小由innodb_log_buffer_size变量定义 。默认大小为16MB。日志缓冲区的内容会定期刷新到磁盘。大型日志缓冲区使大型事务能够运行，而无需在事务提交之前将重做日志数据写入磁盘。因此，如果您有更新，插入或删除许多行的事务，则增加日志缓冲区的大小可以节省磁盘I / O。该 innodb_flush_log_at_trx_commit 变量控制如何写入日志缓冲区的内容并刷新到磁盘。该 innodb_flush_log_at_timeout 变量控制日志刷新频率。

InnoDB磁盘结构：Tables、Indexes、Tablespaces、InnoDB Data Dictionary、Doublewrite Buffer、Redo Log、Undo Logs

要检查默认存储引擎，请发出以下语句：SELECT @@default_storage_engine;
创建InnoDB表时，MySQL会在MySQL数据目录下的数据库目录中创建一个.frm文件。对于在每个表文件表空间中创建的表，MySQL 默认情况下还会在数据库目录中创建 .ibd表空间文件。MySQL 在数据库目录中的.frm文件中存储表的数据字典信息 。
InnoDB表 的默认行格式由innodb_default_row_format 配置选项定义 ，其默认值为 DYNAMIC。 Dynamic 和 Compressed 行格式允许您利用 InnoDB表压缩和长列值的高效页外存储等功能。要使用这些行格式， innodb_file_per_table必须启用（默认情况下从MySQL 5.6.6开始），并且 innodb_file_format必须设置为Barracuda。
要查看InnoDB表的属性，请发出以下SHOW TABLE STATUS 语句SHOW TABLE STATUS FROM test LIKE ‘t%’ \G;
InnoDB也可以使用InnoDBInformation Schema系统表查询表属性：SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME=‘test/t1’ \G

移动或复制InnoDB表：RENAME TABLE db1.tbl_name TO db2.tbl_name;
InnoDB表的限制:一个表最多可以包含1017列;一个表最多可包含64个 二级索引;多列索引最多允许16列;虽然InnoDB内部支持的行大小超过65,535字节，但MySQL本身对所有列的总大小强加了行大小限制为65,535;BLOB并且 TEXT列只向行大小限制贡献9到12个字节，因为它们的内容与行的其余部分分开存储;在某些较旧的操作系统上，文件必须小于2GB;InnoDB日志文件 的总大小最大可达512GB;最小表空间大小略大于10MB。最大表空间大小取决于 InnoDB页面大小;
InnoDB页面大小 最大表空间大小
4KB 16TB
8KB 32TB
16KB 64TB
32KB 128TB
64KB 256TB
最大表空间大小也是表的最大大小
默认页面大小InnoDB为16KB。您可以通过innodb_page_size在创建MySQL实例时配置选项来增加或减少页面大小

索引
Clustered and Secondary Indexes、The Physical Structure of an InnoDB Index、Sorted Index Builds、InnoDB FULLTEXT Indexes

Clustered and Secondary Indexes：每个InnoDB表都有一个称为聚簇索引的特殊索引， 其中存储了行的数据。通常，聚簇索引与主键同义 。
在PRIMARY KEY表上定义a 时，InnoDB将其用作聚簇索引；
如果没有PRIMARY KEY，MySQL将找到UNIQUE所有键列所在的第一个索引，NOT NULL并将 InnoDB其用作聚簇索引；
如果表没有PRIMARY KEY或没有合适的 UNIQUE索引，则在InnoDB 内部生成一个隐藏的聚簇索引GEN_CLUST_INDEX，该索引在包含行ID值的合成列上命名 。

聚簇索引如何加快查询速度：通过聚簇索引访问行很快，因为索引搜索直接指向包含所有行数据的页面。如果表很大，与使用与索引记录不同的页面存储行数据的存储组织相比，聚簇索引体系结构通常会保存磁盘I / O操作。

The Physical Structure of an InnoDB Index：除空间索引外，InnoDB 索引是B树数据结构。空间索引使用 R树，R树是用于索引多维数据的专用数据结构。索引记录存储在其B树或R树数据结构的叶页中。索引页的默认大小为16KB。
将新记录插入到InnoDB 聚簇索引中时， InnoDB尝试将页面的1/16保留为可用，以便将来插入和更新索引记录。如果索引记录按顺序（升序或降序）插入，则生成的索引页大约为15/16。如果以随机顺序插入记录，则页面从1/2到15/16满。

Sorted Index Builds：InnoDB在创建或重建索引时，执行批量加载而不是一次插入一个索引记录。这种索引创建方法也称为排序索引构建。空间索引不支持排序索引构建。

InnoDB FULLTEXT Indexes：FULLTEXT索引是在基于文本的列（CHAR， VARCHAR或TEXT列）上创建的， 以帮助加快对这些列中包含的数据的查询和DML操作
创建InnoDB FULLTEXT索引时，会创建一组索引表

在MySql数据库中，有四种索引：聚簇索引（主键索引）、普通索引、唯一索引以及我们这里将要介绍的全文索引（FUNLLTEXT INDEX）。

INNODB_SYS_INDEXES:提供了InnoDB索引的状态信息。

INNODB_SYS_TABLES：提供了InnoDB表的状态信息。

INNODB_FT_CONFIG：显示一个InnoDB表的FULLTEXT索引及其相关处理的元数据。

INNODB_FT_INDEX_TABLE：转化后的索引信息用于处理基于InnoDB表FULLTEXT索引的文本搜索。一般用于调试诊断目的。使用该表前需先配置innodb_ft_aux_table配置选项,将其指定为想要查看的含FULLTEXT索引的InnoDB表，选项值的格式为database_name/table_name。配置了该选项后INNODB_FT_INDEX_TABLE,INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG, INNODB_FT_DELETED和INNODB_FT_BEING_DELETED表将被填充与innodb_ft_aux_table配置选项指定的表关联的搜索索引相关信息。

INNODB_FT_INDEX_CACHE：向含FULLTEXT索引的InnoDB表插入数据后新插入数据转后的索引信息。表结构与INNODB_FT_INDEX_TABLE一致。为含FULLTEXT索引的InnoDB表执行DML操作期间重组索引开销很大，因此将新插入的被索引的词单独存储于该表中，当且仅当为InnoDB表执行OPTIMIZE TABLE语句后才将新的转换后的索引信息与原有的主索引信息合并。使用该表前需先配置innodb_ft_aux_table配置选项。

INNODB_FT_DEFAULT_STOPWORD：在InnoDB表上创建FULLTEXT索引所使用的默认停止字表。

INNODB_FT_DELETED：记录了从InnoDB表FULLTEXT索引中删除的行。为了避免为InnoDB的FULLTEXT索引执行DML操作期间重组索引的高开销，新删除的词的信息单独存储于此表。当且仅当为此InnoDB表执行了OPTIMIZE TABLE操作后才会从主搜索索引中移除已删除的词信息。使用该表前需先配置innodb_ft_aux_table选项。

INNODB_FT_BEING_DELETED：为含FULLTEXT索引的InnoDB表执行OPTIMIZE TABLE操作时会根据INNODB_FT_DELETED表中记录的文档ID从InnoDB表的FULLTEXT索引中删除相应的索引信息。而INNOFB_FT_BEING_DELETED表用于记录正在被删除的信息，用于监控和调试目的。

全文搜索的语法：MATCH(col1,col2,…) AGAINST (expr[search_modifier])。其中MATCH中的内容为已建立FULLTEXT索引并要从中查找数据的列，AGAINST中的expr为要查找的文本内容，search_modifier为可选搜索类型。

InnoDB FULLTEXT由于其缓存和批处理行为，索引具有特殊的事务处理特性。具体而言，FULLTEXT在事务提交时处理索引上的更新和插入，这意味着 FULLTEXT搜索只能查看已提交的数据。

表空间：系统表空间、File-Per-Table Tablespaces、General Tablespaces、Undo Tablespaces、The Temporary Tablespace、Creating a Tablespace Outside of the Data Directory、Copying Tablespaces to Another Instance、InnoDB Data-at-Rest Encryption

InnoDB系统表空间包含的 InnoDB数据字典（元数据 InnoDB-相关对象）和对于双写缓冲器，所述缓冲器改变的存储区，并撤消日志。系统表空间还包含在系统表空间中创建的表和索引数据。系统表空间可以包含一个或多个数据文件；系统表空间数据文件的大小和数量由innodb_data_file_path 启动选项控制。
无法从系统表空间中删除数据文件
每个InnoDB表都存储在自己的表空间数据文件（.ibd文件）中。
ALTER TABLE驻留在共享表空间中的表上的表复制操作可以增加表空间使用的空间量。此类操作可能需要与表中的数据和索引一样多的额外空间。表复制ALTER TABLE操作所需的额外空间 不会像文件每表表空间一样释放回操作系统。
innodb_file_per_table 默认情况下启用 该选项
还可以innodb_file_per_table 在服务器运行时动态设置 ：SET GLOBAL innodb_file_per_table=1;

一般表空间：通用表空间是可以存储多个表的数据的共享表空间；
CREATE TABLESPACE ts1 ADD DATAFILE ‘ts1.ibd’ Engine=InnoDB;
或者
CREATE TABLESPACE ts1 ADD DATAFILE ‘/my/tablespace/directory/ts1.ibd’ Engine=InnoDB;

临时表空间在正常关闭或中止初始化时被删除，并在每次启动服务器时重新创建。临时表空间在创建时会收到动态生成的空间ID。如果无法创建临时表空间，则拒绝启动。如果服务器意外停止，则不会删除临时表空间。在这种情况下，数据库管理员可以手动删除临时表空间或重新启动服务器，从而自动删除并重新创建临时表空间。
临时表空间数据文件是自动扩展的，并且根据需要增加大小以容纳磁盘上的临时表。
要确定临时表空间数据文件是否自动扩展，请检查以下 innodb_temp_data_file_path 设置：SELECT @@innodb_temp_data_file_path;
要防止临时数据文件变得太大，可以配置 innodb_temp_data_file_path 选项以指定最大文件大小。例如：innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

InnoDB静态数据加密：必须安装和配置密钥环插件。使用该early-plugin-load选项在启动时执行密钥环插件安装；要验证密钥环插件是否处于活动状态，请使用该 SHOW PLUGINS语句或查询该 INFORMATION_SCHEMA.PLUGINS 表，SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE ‘keyring%’;
加密生产数据时，请确保采取措施防止丢失主加密密钥。如果主加密密钥丢失，则存储在加密表空间文件中的数据将无法恢复。

启用每表文件表空间加密：要为新的每表文件表空间启用加密，请ENCRYPTION在CREATE TABLE语句中指定该选项 。以下示例假定 innodb_file_per_table已启用。CREATE TABLE t1 (c1 INT) ENCRYPTION=‘Y’;
要为现有的每表文件表空间启用加密，请ENCRYPTION在ALTER TABLE语句中指定该选项 。ALTER TABLE t1 ENCRYPTION=‘Y’;
要禁用每个表文件表空间的加密，请 ENCRYPTION='N’使用 ALTER TABLE。
ALTER TABLE t1 ENCRYPTION=‘N’;

InnoDB数据字典包括包含用于跟踪对象，如表，索引，和表中的列的元数据的内部系统表。元数据实际位于InnoDB系统表空间中

doublewrite缓冲区是位于系统表空间中的存储区域，在将页面写入数据文件中的正确位置之前，InnoDB写入从InnoDB缓冲池中刷新的页面。只有在将页面刷新并写入双写缓冲区后，才能将页面写入正确的位置。如果在页面写入过程中存在操作系统，存储子系统或 mysqld进程崩溃，则InnoDB可以在崩溃恢复期间从doublewrite缓冲区中找到该页面的良好副本。
尽管数据总是写入两次，但双写缓冲区不需要两倍的I / O开销或两倍的I / O操作。数据作为一个大的顺序块写入doublewrite缓冲区本身，只需一次fsync() 调用操作系统。
在大多数情况下，默认情况下启用doublewrite缓冲区。要禁用双写缓冲区，请设置 innodb_doublewrite为0。

InnoDB实现标准的行级锁定，其中有两种类型的锁， shared（S）锁和exclusive（X）锁。甲共享（S）锁允许持有锁读取行的事务;一个独占（X）锁允许持有锁，更新或删除行的事务
如果事务在行上T1持有一个shared（S）锁r，那么来自某个不同事务T2 的对行锁的请求r将按如下方式处理：由A请求T2用于 S锁可以立即被授予；通过请求T2一个 X锁不能立即授予。

InnoDB提供由SQL描述的所有四个事务隔离级别：1992标准： READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ，和 SERIALIZABLE。InnoDBis 的默认隔离级别REPEATABLE READ。
INSERT在插入的行上设置独占锁。
发出 SHOW ENGINE INNODB STATUS命令以确定最近死锁的原因

Innodb配置
要确保mysqld仅从特定文件读取选项，请–defaults-file在启动服务器时将该 选项用作命令行上的第一个选项：mysqld --defaults-file=path_to_configuration_file
innodb_data_file_path 配置选项定义的名称，大小，和属性 InnoDB系统表空间的数据文件。如果没有为其指定值 innodb_data_file_path，则默认行为是创建一个名称略大于12MB的自动扩展数据文件ibdata1。要指定多个数据文件，请用分号（;）字符分隔它们：innodb_data_file_path=ibdata1:12M:autoextend

索引机制(B+Tree)
索引是搜索引擎去实现的，在建立表的时候都会指定，搜索引擎是一种插拔式的，根据自己的选择去决定使用哪一个。
索引是为了加速对表中数据行的检索而创建的一种分散存储的（不连续的）数据结构，硬盘级的。
　索引意义：索引能极大的减少存储引擎需要扫描的数据量，索引可以把随机IO变成顺序IO。索引可以帮助我们在进行分组、排序等操作时，避免使用临时表。正确的创建合适的索引是提升数据库查询性能的基础。
　
平衡多路查找树：B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：
B+节点关键字搜索采用闭合区间
B+非叶节点不保存数据相关信息，只保存关键字和子节点的引用
B+关键字对应的数据保存在叶子节点中
B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

聚簇索引，索引的顺序就是数据存放的顺序，将数据存入索引叶子页面上。所以，很容易理解，一张数据表只能有一个聚簇索引。
非聚簇索引：索引的顺序与数据物理排列顺序无关。非聚簇索引 来说，每次通过索引检索到所需行号后，还需要通过叶子上的磁盘地址去磁盘内取数据（回行）消耗时间。

InnoDB的辅助索引（非聚簇索引）data域存储相应记录主键的值而不是地址。
InnoDB默认对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，InnoDB 会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。