MySql操作手册

MySql

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySql操作手册

安装装MySql(docker)

https://www.cnblogs.com/jinit/p/13379065.html

创建用户

CREATE USER 'david'@'198.51.100.0/255.255.255.0'; #创建用户

#满足此条件的IP地址范围为 198.51.100.0到 198.51.100.255。
#网络掩码通常以设置为1的位开始，然后是设置为0的位。
#198.0.0.0/255.0.0.0：198类A网络上的任何主机
#198.51.100.0/255.255.0.0：198.51 B类网络上的任何主机
#198.51.100.0/255.255.255.0：198.51.100 C类网络上的任何主机
#198.51.100.1：仅具有此​​特定IP地址的主机
CREATE USER 'finley'@'localhost'
  IDENTIFIED BY 'password'; #创建用户finley，密码为password
GRANT ALL #对用户开发所有权限，（除外GRANT OPTION）
  ON *.* #权限针对所有库所有表，库.表
  TO 'finley'@'localhost' #赋权用户@地址
  WITH GRANT OPTION;

REVOKE ALL #取消所有权限
  ON *.* #针对所有库所有表
  FROM 'finley'@'%.example.com'; #对finley用户取消权限

更改密码

#要在创建新帐户时分配密码，请使用 CREATE USER并包含一个 IDENTIFIED BY子句：
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

#要为现有帐户分配或更改密码，请将该 ALTER USER语句与以下IDENTIFIED BY子句一起使用 ：
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

#如果您不是以匿名用户身份连接的，则可以更改自己的密码，而无需直接命名自己的帐户：
ALTER USER USER() IDENTIFIED BY 'password';

#要从命令行更改帐户密码，请使用 mysqladmin命令：
mysqladmin -u user_name -h host_name password "password"

#要手动使帐户密码失效，请使用以下 ALTER USER语句：
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

#要建立密码有效期大约为六个月的全局策略，请在服务器my.cnf文件中使用以下几行来启动服务器：
[mysqld]
default_password_lifetime=180

#要建立全局策略以使密码永不过期，请将其设置 default_password_lifetime 为0：
[mysqld]
default_password_lifetime=0

#default_password_lifetime 也可以在运行时设置和持久化：
SET PERSIST default_password_lifetime = 180;
SET PERSIST default_password_lifetime = 0;

配置字符集

#默认MySQL字符集和排序规则（utf8mb4， utf8mb4_0900_ai_ci）

#每个“字符”列（即，类型列 CHAR， VARCHAR中， TEXT类型，或其任何同义词）具有一列的字符集和列排序规则。列定义语法，CREATE TABLE并 ALTER TABLE具有用于指定列字符集和排序规则的可选子句：

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]
这些子句也可用于 ENUM和 SET列：

col_name {ENUM | SET} (val_list)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]
例子：

CREATE TABLE t1
(
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_german1_ci
);

ALTER TABLE t1 MODIFY
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_swedish_ci;
#每个表都有一个表字符集和一个表排序规则。该 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;

#每个数据库都有一个数据库字符集和一个数据库排序规则。该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]
#SCHEMA可以使用 关键字代替 DATABASE。
#使用CHARACTER SETand COLLATE子句可以在同一MySQL服务器上创建具有不同字符集和排序规则的数据库。

#在服务器启动时指定字符设置。  要在服务器启动时选择字符集和排序规则，请使用 --character-set-server和 --collation-server选项。
例如，要在选项文件中指定选项，请包括以下几行：

[mysqld]
character-set-server=latin1
collation-server=latin1_swedish_ci

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

#或者，在不更改默认数据库的情况下显示值:
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

数据备份（sql格式，其他格式参考https://dev.mysql.com/doc/refman/8.0/en/mysqldump-delimited-text.html）

#将数据备份到特定文件
 SELECT * INTO OUTFILE 'file_name' FROM table_name;

#使用mysqldump命令备份 （https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html;https://dev.mysql.com/doc/refman/8.0/en/mysqldump-sql-format.html）
mysqldump -u root -p nacos_config1 > nacos_config.sql  #mysqldump --databases test > dump.sql 单个库可省略--databases

#默认情况下，mysqldump将信息作为SQL语句写入标准输出。您可以将输出保存在文件中：
shell> mysqldump [arguments] > file_name

#要转储所有数据库，请使用以下选项调用mysqldump--all-databases：
shell> mysqldump --all-databases > dump.sql

#要仅转储特定数据库，请在命令行上命名它们并使用以下--databases 选项：
shell> mysqldump --databases db1 db2 db3 > dump.sql

#导入数据（sql格式）
#要重新加载由mysqldump编写的 包含SQL语句的转储文件，请将其用作mysql客户端的输入 。
#如果转储文件是由mysqldump使用 --all-databases或 --databases选项创建的 ，则它包含CREATE DATABASE和 USE语句，无需指定默认数据库以将数据加载到其中：
shell> mysql < dump.sql

#或者，从mysql内部，使用 source命令：
mysql> source dump.sql

#如果该文件是不包含CREATE DATABASE和 USE语句的单数据库转储 ，请首先创建数据库（如有必要）：
shell> mysqladmin create db1
#然后在加载转储文件时指定数据库名称：
shell> mysql db1 < dump.sql

#或者，从mysql内部，创建数据库，将其选择为默认数据库，然后加载转储文件：
mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

SQL优化（摘自mysql官方文档，属于优化器执行的优化，了解优化器优化方法编写的sql更容易被优化器所优化）

优化SQL语句
典型sql优化

• MySQL可以对col_name = constant_value使用的col_name IS NULL执行相同的优化。 例如，MySQL可以使用索引和范围使用IS NULL搜索NULL。

#key_col索引列
SELECT * FROM tbl_name WHERE key_col IS NULL;

SELECT * FROM tbl_name WHERE key_col <=> NULL;

SELECT * FROM tbl_name
  WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

MySQL可以对col_name = constant_value使用的col_name IS NULL执行相同的优化。 例如，MySQL可以使用索引和范围使用IS NULL搜索NULL。
如果WHERE子句包含声明为NOT NULL的列的col_name IS NULL条件，则该表达式将被优化。 在该列无论如何都可能产生NULL的情况下（例如，如果它来自LEFT JOIN右侧的表），则不会进行此优化。

MySQL还可以优化组合col_name = expr或col_name IS NULL，这种形式在已解析子查询中很常见。 使用此优化时，EXPLAIN显示ref_or_null。

此优化可以为任何关键部分处理一个IS NULL。

ref_or_null的工作方式是先对参考键进行读取，然后单独搜索具有NULL键值的行。
假设在表t2的a和b列上有索引，则对查询进行一些优化的示例：

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;

SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1, t2
  WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
  OR (t1.a=t2.a AND t2.a IS NULL AND ...);

该优化只能处理一个IS NULL级别。在以下查询中，MySQL仅在表达式上使用键查找（t1.a = t2.a AND t2.a IS NULL），而不能在b上使用键部分：

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL)
  OR (t1.b=t2.b AND t2.b IS NULL);

• 使用索引满足ORDER BY
  以前（MySQL 5.7及更低版本）， GROUP BY在某些条件下隐式排序。在MySQL 8.0中，不再发生这种情况，因此ORDER BY NULL不再需要在末尾进行指定以禁止隐式排序（如前所述）。但是，查询结果可能与以前的MySQL版本不同。要产生给定的排序顺序，请提供一个ORDER BY子句。

在某些情况下，MySQL可以使用索引来满足ORDER BY子句，并避免执行文件排序操作时涉及的额外排序。
即使ORDER BY与索引不完全匹配，也可以使用索引，只要索引的所有未使用部分和所有额外的ORDER BY列在WHERE子句中都是常量即可。

#alter table employees add index index_name (emp_no,first_name) ;
 explain select emp_no,first_name,e.* from employees e where first_name ='Kyoichi' order by emp_no,first_name;
#explain select emp_no,first_name,e.* from employees e where first_name ='Kyoichi' order by emp_no;
#explain 使用索引
id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'index', NULL, 'PRIMARY', '4', NULL, '298980', '10.00', 'Using where'

如果索引不包含查询访问的所有列，则仅当索引访问比其他访问方法便宜时才使用索引。
假设在（key_part1，key_part2）上有一个索引，以下查询可以使用该索引来解析ORDER BY部分。 优化器是否实际上这样做取决于如果还必须读取索引中没有的列，则读取索引是否比表扫描更有效。但是，查询使用SELECT *，它可能会选择比key_part1和key_part2更多的列。在这种情况下，扫描整个索引并查找表行以查找索引中未包含的列可能比扫描表并排序结果要昂贵。如果是这样，优化器可能不会使用该索引。如果SELECT *仅选择索引列，则将使用索引并避免排序。

#alter table employees add index index_name (emp_no,first_name) ;
explain select emp_no,first_name from employees e order by emp_no,first_name;
# explain 使用索引
id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'index', NULL, 'index_name', '62', NULL, '298980', '100.00', 'Using index'

#alter table employees add index index_name (emp_no,first_name) ;
explain select e.* from employees e order by emp_no,first_name;
#explain select emp_no,first_name,last_name from employees e order by emp_no,first_name;(可能使用索引，但未使用)

#explain 未使用索引
id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'ALL', NULL, NULL, NULL, NULL, '298980', '100.00', 'Using filesort'

排序中使用索引的情况
（1）、在此查询中，（emp_no,first_name）上的索引使优化器避免排序：

#alter table employees add index index_name (emp_no,first_name) ;
explain select emp_no,first_name from employees e order by emp_no,first_name;

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'index', NULL, 'index_name', '62', NULL, '298980', '100.00', 'Using index'

（2）、如果employees是InnoDB 表，则表主键隐式属于索引的一部分，并且该索引可用于解析 ORDER BY此查询：

#alter table employees add index index_name (first_name,last_name) ;emp_no为主键
explain select emp_no,first_name,last_name from employees e order by first_name,last_name;

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'index', NULL, 'index_name', '124', NULL, '298980', '100.00', 'Using index'

（3）、在此查询中，key_part1是常量，因此通过索引访问的所有行都按key_part2顺序排列，并且如果WHERE子句的选择性足以使索引范围扫描比表扫描便宜，则（key_part1，key_part2）上的索引可以避免排序：

#alter table employees add index index_name (first_name,last_name) ;
 explain select first_name,last_name from employees e where first_name ='Kyoichi' order by last_name;

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'ref', 'index_name', 'index_name', '58', 'const', '251', '100.00', 'Using index'

 explain select * from employees e where first_name ='Kyoichi' order by last_name;
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'ref', 'index_name', 'index_name', '58', 'const', '251', '100.00', NULL

（4）、ORDER BY中的两列可以按相同方向（两个ASC或两个DESC）或相反方向（一个ASC，一个DESC）排序。 使用索引的条件是索引必须具有相同的同质性，但不必具有相同的实际方向。如果查询混合了ASC和DESC，则优化器可以在列上使用索引，前提是该索引还使用了相应的混合升序和降序列：
如，如果key_part1降序而key_part2升序，则优化器可以在（key_part1，key_part2）上使用索引。 如果key_part1升序而key_part2降序，它也可以在这些列上使用索引（向后扫描）。

#alter table employees add index index_name (first_name asc,last_name desc) ;
explain select first_name,last_name from employees e order by first_name asc,last_name desc;

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'index', NULL, 'index_name', '124', NULL, '298980', '100.00', 'Using index'


explain select first_name,last_name from employees e order by first_name desc,last_name asc;

#向后扫描
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'index', NULL, 'index_name', '124', NULL, '298980', '100.00', 'Backward index scan; Using index'

#索引排序类型不相同 alter table employees add index index_name (first_name asc,last_name desc) ;
explain select first_name,last_name from employees e order by first_name desc,last_name desc;

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'index', NULL, 'index_name', '124', NULL, '298980', '100.00', 'Using index; Using filesort'

#对单个key_part1排序 alter table employees add index index_name (first_name asc,last_name desc) ;
explain select first_name,last_name from employees e where first_name ='Kyoichi'  order by last_name asc;
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'ref', 'index_name', 'index_name', '58', 'const', '251', '100.00', 'Backward index scan; Using index'

explain select first_name,last_name from employees e where first_name ='Kyoichi'  order by last_name desc;
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'ref', 'index_name', 'index_name', '58', 'const', '251', '100.00', 'Using index'

（5）、在使用别名时使用索引排序

#使用索引
explain select first_name a,last_name b  from employees e order by first_name asc,last_name desc;
#explain select first_name a,last_name b  from employees e order by a asc,b desc;

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'index', NULL, 'index_name', '124', NULL, '298980', '100.00', 'Using index'

#索引中的列名是查询结果中的别名，索引不生效
explain select birth_date as first_name,hire_date last_name from employees e order by first_name asc,last_name desc;

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'e', NULL, 'ALL', NULL, NULL, NULL, NULL, '298980', '100.00', 'Using filesort'

在某些情况下，MySQL不能使用索引来解析ORDER BY，尽管它仍可以使用索引来查找与该WHERE子句匹配的行 。例子：

（1）、该查询用于ORDER BY不同的索引：

SELECT * FROM t1 ORDER BY key1, key2;

（2）、该查询ORDER BY在索引的非连续部分上使用：

SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;

（3）、用于获取行的索引与在ORDER BY中使用的索引不同：

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

（4）、该查询使用ORDER BY的表达式包含除索引列名称以外的术语：

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

（5）、该查询联接了许多表，并且中的列 ORDER BY并非全部来自用于检索行的第一个非恒定表。（这是EXPLAIN输出中没有const联接类型的第一个表 。）
（6）、该查询具有不同的ORDER BY和GROUP BY表达式。
（7）、仅在ORDER BY子句中命名的列的前缀上存在索引。 在这种情况下，索引不能用于完全解析排序顺序。 例如，如果仅索引CHAR（20）列的前10个字节，则索引无法区分第10个字节之后的值，因此需要进行文件排序。
（8）、索引不按顺序存储行。 例如，对于MEMORY表中的HASH索引，这是正确的。
（9）、该查询联接了许多表，并且ORDER BY中的列并非全部来自用于检索行的第一个非恒定表。 （这是EXPLAIN输出中的第一个没有const join类型的表。）

• 索引用于分组

松散索引扫描
处理的最有效方法GROUP BY是使用索引直接检索分组列。通过这种访问方法，MySQL使用键排序的某些索引类型的属性（例如BTREE）。使用此属性，可以在索引中使用查找组，而不必考虑索引中满足所有WHERE条件的所有键(键即索引组成中的列) 。此访问方法仅考虑索引中的一部分键，因此称为“松散索引扫描”。如果没有WHERE 子句，则“松散索引扫描”将读取与组数一样多的键，这可能比所有键的数量少得多。如果WHERE子句包含范围谓词，“松散索引扫描”查找满足范围条件的每个组的第一个键，并再次读取最小的可能值按键数。在以下情况下可以这样做：

（1）、查询是在单个表上。

（2）、GROUP BY仅命名构成索引最左前缀的列，而没有命名其他列。 （如果查询具有DISTINCT子句，而不是GROUP BY，则所有不同的属性都引用构成索引最左前缀的列。）例如，如果表t1在（c1，c2，c3）上具有索引， 如果查询具有GROUP BY c1，c2，则松散索引扫描适用。 如果查询具有GROUP BY c2，c3（列不是最左边的前缀）或GROUP BY c1，c2，c4（c4不在索引中），则此方法不适用。

（3）、选择列表中使用的唯一聚合函数（如果有）是MIN（）和MAX（），它们全部都引用同一列。 该列必须在索引中，并且必须紧随GROUP BY中的列。

（4）、除MIN（）或MAX（）函数的参数外，查询中所引用的索引中除GROUP BY以外的任何其他部分都必须是常量（即，必须与常量相等地引用）。

（5）、对于索引中的列，必须索引完整的列值，而不仅仅是索引。 例如，对于c1 VARCHAR（20）和INDEX（c1（10）），索引仅使用c1值的前缀，而不能用于宽松索引扫描。

如果“松散索引扫描”适用于查询，则 EXPLAIN输出将显示 Using index for group-by在该 Extra列中。

假设idx(c1,c2,c3)table上 有一个索引 t1(c1,c2,c3,c4)。松散索引扫描访问方法可用于以下查询：

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

以下查询不能使用松散索引扫描
（1）、除了MIN（）或MAX（）以外的聚合函数

SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

（2）、GROUP BY子句中 的列不构成索引的最左前缀

SELECT c1, c2 FROM t1 GROUP BY c2, c3;

（3）、该查询引用的是键的一部分，该键位于GROUP BY部分之后，并且该部分与常量不相等

SELECT c1, c3 FROM t1 GROUP BY c1, c2;

#如果查询包含，则可以使用松散索引扫描。 
SELECT c1, c3 FROM t1 WHERE c3 = const GROUP BY c1, c2;

除了已经支持的MIN()和 MAX()引用之外，松散索引扫描访问方法还可以应用于选择列表中的其他形式的聚合函数引用
（1）、支持AVG（DISTINCT），SUM（DISTINCT）和COUNT（DISTINCT）。 AVG（DISTINCT）和SUM（DISTINCT）采用一个参数。 COUNT（DISTINCT）可以有多个列参数。
（2）、查询中不得有GROUP BY或DISTINCT子句。
（3）、先前描述的松散索引扫描限制仍然适用。
假设idx(c1,c2,c3)table上 有一个索引 t1(c1,c2,c3,c4)。松散索引扫描访问方法可用于以下查询：

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

紧密索引扫描
紧密索引扫描可以是全索引扫描，也可以是范围索引扫描，具体取决于查询条件。

当不满足宽松索引扫描的条件时，仍然有可能避免为GROUP BY查询创建临时表。如果WHERE子句中有范围条件，则此方法仅读取满足这些条件的键。否则，它将执行索引扫描。因为此方法读取WHERE子句定义的每个范围内的所有键，或者在不存在范围条件的情况下扫描整个索引，所以称为紧索引扫描。对于紧密索引扫描，仅在找到所有满足范围条件的键之后才执行分组操作。

为了使该方法起作用，对于查询中所有引用在GROUP BY键的各个部分之前或之间的键的部分，所有列均具有恒定的相等条件就足够了。来自相等条件的常量将填充搜索键中的所有“间隙”，以便可以形成索引的完整前缀。这些索引前缀然后可以用于索引查找。如果GROUP BY结果需要排序，并且有可能形成作为索引前缀的搜索关键字，则MySQL还避免了额外的排序操作，因为在有序索引中使用前缀进行搜索已经按顺序检索了所有关键字。

假设在表t1（c1，c2，c3，c4）上有一个索引idx（c1，c2，c3）。以下查询不适用于前面所述的“松散索引扫描”访问方法，但仍适用于“紧索引扫描”访问方法。
（1）、There is a gap in the GROUP BY, but it is covered by the condition c2 = 'a':

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

（2）、GROUP BY并非以键的第一部分开头，但是存在为该部分提供常量的条件

SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

• DISTINCT优化
  在大多数情况下，DISTINCT子句可以视为GROUP BY的特殊情况。 例如，以下两个查询是等效的：

SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;

SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;

由于存在这种等效关系，因此适用于GROUP BY查询的优化也可以应用于具有DISTINCT子句的查询。

• 函数调用优化
  MySQL函数在内部被标记为确定性或不确定性。如果给定参数固定值的函数可以为不同的调用返回不同的结果，则它是不确定的。不确定函数的示例： RAND()， UUID()。
  如果某个函数被标记为不确定的，则将WHERE针对每一行（从一个表中选择时）或行的组合（从多表联接中选择时）评估子句中对该函数的引用。
  MySQL还根据参数的类型（参数是表列还是常量值）确定何时评估函数。每当表列更改值时，都必须评估将表列作为参数的确定性函数。
  非确定性函数可能会影响查询性能。例如，某些优化可能不可用，或者可能需要更多锁定。以下讨论使用 RAND()但也适用于其他不确定性函数。不确定性函数的影响
  （1）、由于不确定函数不会产生恒定值，因此优化器无法使用其他可能适用的策略，例如索引查找。结果可能是表扫描。

（2）、InnoDB 可能升级为范围键锁，而不是为一个匹配的行获取单行锁。

（3）、无法确定执行的更新对于复制是不安全的。
优化方式
（1）、将包含不确定性函数的表达式移到单独的语句，将值保存在变量中。在原始语句中，将表达式替换为对变量的引用，优化器可以将该变量视为常量值：

SET @keyval = FLOOR(1 + RAND() * 49);
UPDATE t SET col_a = some_expr WHERE id = @keyval;

（2）、将随机值分配给派生表中的变量。此技术使变量在WHERE子句中的比较中使用之前被分配一个值 ：

UPDATE /*+ NO_MERGE(dt) */ t, (SELECT FLOOR(1 + RAND() * 49) AS r) AS dt
SET col_a = some_expr WHERE id = dt.r;

（3）、如前所述，WHERE子句中的不确定性表达式 可能会阻止优化并导致表扫描。但是，WHERE如果其他表达式是确定性的，则可以部分优化该子句。例如：

SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();

• 避免全表扫描
  对于小型表，表扫描通常是适当的，并且对性能的影响可以忽略不计。对于大型表，请尝试以下技术，以避免优化器错误地选择表扫描：
  （1）、对扫描的表使用FORCE INDEX告诉MySQL与使用给定索引相比，表扫描非常昂贵：
  （2）、使用ANALYZE TABLE tbl_name更新扫描表的密钥分布。 。参见“ ANALYZE TABLE语句”

• 使用exists优化 IN 条件
  半联接是准备时转换，它启用多种执行策略，例如表提取，重复删除，首次匹配，松散扫描和实现。 如本节所述，优化器使用半联接策略来改善子查询的执行。

对于两个表之间的内部联接，该联接从一个表返回一行的次数是另一表中存在匹配项的次数。 但是对于某些问题，唯一重要的信息是是否存在匹配项，而不是匹配数。 假设有一个名为class和名册的表，分别列出了课程表和班级名册（每个班级的学生）中的班级。 要列出实际招收学生的课程，您可以使用以下联接：

SELECT class.class_num, class.class_name
    FROM class
    INNER JOIN roster
    WHERE class.class_num = roster.class_num;

但是，结果为每个注册学生列出一次每个班级。 对于所提出的问题，这是不必要的信息重复。

假设class_num是类表中的主键，则可以通过使用SELECT DISTINCT来抑制重复，但是先生成所有匹配的行仅用于稍后消除重复是无效的。

可以通过使用子查询获得相同的无重复结果：

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM roster);

在这里，优化器可以识别出IN子句要求子查询仅从名册表返回每个类编号的一个实例。 在这种情况下，查询可以使用半联接。 也就是说，该操作仅返回类中每一行的一个实例，该实例与名册中的行匹配。

包含EXISTS子查询谓词的以下语句与包含IN子查询谓词的先前语句等效：

SELECT class_num, class_name
    FROM class
    WHERE EXISTS
        (SELECT * FROM roster WHERE class.class_num = roster.class_num);

转换规则

#单条件
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
#多条件
(oe_1, ..., oe_N) IN
  (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

转化为

#单条件
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)
#多条件
EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND oe_1 = ie_1
                          AND ...
                          AND oe_N = ie_N)

假设outer_expr已知为非NULL值，但子查询不会产生使得outer_expr = inner_expr的行。 然后，outer_expr IN（SELECT ...）评估如下：

（1）、如果SELECT产生inner_expr为NULL的任何行，则为NULL

（2）、FALSE，如果SELECT只产生非NULL值或什么都不产生

在这种情况下，使用outer_expr = inner_expr查找行的方法不再有效。 有必要查找这样的行，但是如果找不到，则还要查找inner_expr为NULL的行。 粗略地讲，子查询可以转换为如下形式：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND
        (outer_expr=inner_expr OR inner_expr IS NULL))

在不确定outer_expr 是否为空时

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

#(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

#转化为

#每个trigcond（X）是一个特殊函数，其结果为以下值：
#当“链接的”外部表达式oe_i不为NULL时为X
#当“链接的”外部表达式oe_i为NULL时为TRUE
EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(outer_expr=inner_expr))

#EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(oe_1=ie_1)
                          AND ...
                          AND trigcond(oe_N=ie_N)
       )

#(outer_expr IS NOT NULL) AND (outer_expr [NOT] IN (SELECT inner_expr FROM ...))

• 索引
  索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
  MySQL索引通常是B+树索引（还有Hash索引）。
  索引用于快速查找具有特定列值的行。没有索引，MySQL必须从第一行开始，然后通读整个表以找到相关的行。
  大多数MySQL索引（PRIMARY KEY，UNIQUE，INDEX和FULLTEXT）存储在B树中。 例外：空间数据类型的索引使用R树； MEMORY表还支持哈希索引。 InnoDB对FULLTEXT索引使用反向列表。
  （一个表的索引不是越多越好，创建索引能提高查询效率，但是维护索引树对增删改i/o的操作会耗时增多，且查询时增大优化器选择合适索引或合并索引的成本。）
  优势：提高查询效率，降低排序分组的成本。
  劣势：增加磁盘空间消耗（索引以文件形式存储）、增大表更新的成本（不及更新数据还需要更新索引）
  innodb聚集索引：叶子节点存储行记录数据;
  innodb普通索引：叶子节点存储主键值;
  回表查询：通过普通索引定位主键值，再通过聚集索引查询行记录，它的性能较扫一遍索引树更低。
  索引覆盖：查询的字段为索引中的键,数据列直接从索引中查询。

#创建索引的三种方式：
create table employees(
id int not null primary key AUTO_INCREMENT,
first_name varchar(100),
last_name varchar(100),
index index_name(first_name,last_name));

alter table employees add index index_name (first_name,last_name) ;

create index index_name on employees(first_name,last_name) ;
#创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
#创建表时定义索引
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition: {
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | check_constraint_definition
}

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
      [ENGINE_ATTRIBUTE [=] 'string']
      [SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
      [STORAGE {DISK | MEMORY}]
      [reference_definition]
      [check_constraint_definition]
  | data_type
      [COLLATE collation_name]
      [GENERATED ALWAYS] AS (expr)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [reference_definition]
      [check_constraint_definition]
}

data_type:
    (see Chapter 11, Data Types)

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  |ENGINE_ATTRIBUTE [=] 'string'
  |SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

check_constraint_definition:
    [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

reference_definition:
    REFERENCES tbl_name (key_part,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
    table_option [[,] table_option] ...

table_option: {
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | ENGINE_ATTRIBUTE [=] 'string'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
  | UNION [=] (tbl_name[,tbl_name]...)
}

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

query_expression:
    SELECT ...   (Some valid select or union statement)

#修改表添加索引
ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

alter_option: {
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX | KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
        [index_name] [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
  | ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
  | DROP {CHECK | CONSTRAINT} symbol
  | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
  | ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
  | ALTER [COLUMN] col_name
        {SET DEFAULT {literal | (expr)} | DROP DEFAULT}
  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | {DISABLE | ENABLE} KEYS
  | {DISCARD | IMPORT} TABLESPACE
  | DROP [COLUMN] col_name
  | DROP {INDEX | KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | FORCE
  | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
  | RENAME {INDEX | KEY} old_index_name TO new_index_name
  | RENAME [TO | AS] new_tbl_name
  | {WITHOUT | WITH} VALIDATION
}

partition_options:
    partition_option [partition_option] ...

partition_option: {
    ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING
}

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
}

table_options:
    table_option [[,] table_option] ...

table_option: {
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | ENGINE_ATTRIBUTE [=] 'string'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
  | UNION [=] (tbl_name[,tbl_name]...)
}

partition_options:
    (see CREATE TABLE options)
#查看索引
SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

SHOW INDEX返回表索引信息,输出信息含义https://dev.mysql.com/doc/refman/8.0/en/show-index.html

#删除索引
drop index index_name on table_name;

MySQL使用索引进行以下操作：

（1）、快速查找与WHERE子句匹配的行。

（2）、从考虑中消除行。如果可以在多个索引之间进行选择，MySQL通常会使用找到最少行数的索引（最具选择性的索引）。

（3）、如果表具有多列索引，那么优化器可以使用索引的任何最左前缀来查找行。例如，如果在（col1，col2，col3）上有一个三列索引，则在（col1），（col1，col2）和（col1，col2，col3）上具有索引搜索功能。

（4）、执行联接时从其他表中检索行。如果声明相同的类型和大小，MySQL可以更有效地在列上使用索引。在这种情况下，如果将VARCHAR和CHAR声明为相同大小，则认为它们相同。例如，VARCHAR（10）和CHAR（10）的大小相同，但VARCHAR（10）和CHAR（15）的大小不同。

对于非二进制字符串列之间的比较，两个列应使用相同的字符集。例如，将utf8列与latin1列进行比较会排除使用索引。

如果不能不通过转换直接比较值，则比较不同的列（例如，将字符串列与时间或数字列进行比较）可能会阻止使用索引。对于数字列中的给定值（例如1），它可能会与字符串列中的任意数量的值进行比较，例如“ 1”，“ 1”，“ 00001”或“ 01.e1”。这排除了对字符串列使用任何索引的可能性。

（5）、查找特定索引列key_col的MIN（）或MAX（）值。这由预处理器优化，该预处理器检查是否在索引中在key_col之前出现的所有关键部分上使用WHERE key_part_N = constant。在这种情况下，MySQL对每个MIN（）或MAX（）表达式执行一次键查找，并将其替换为常量。如果所有表达式都用常量替换，查询将立即返回。例如：

SELECT MIN(key_part2),MAX(key_part2)
  FROM tbl_name WHERE key_part1=10;

（6）、如果排序或分组是在可用索引的最左前缀（例如，ORDER BY key_part1，key_part2）上完成的，则对表进行排序或分组。 如果在所有关键部分后面都跟随有DESC，则将以相反的顺序读取密钥。 （或者，如果索引是降序索引，则按向前顺序读取键。）请参见“按优化排序”、“按优化分组”和“ 降序索引”。
（7）、在某些情况下，可以优化查询以检索值而无需查询数据行。 （为查询提供所有必要结果的索引称为覆盖索引。）如果查询仅从表中使用某些索引中包含的列，则可以从索引树中检索所选值以提高速度：

SELECT key_part3 FROM tbl_name
  WHERE key_part1=1

（8）、对于报表查询处理大多数或所有行的小型表或大型表，索引的重要性不那么重要。 当查询需要访问大多数行时，顺序读取要比处理索引快。 顺序读取可最大程度地减少磁盘查找，即使查询不需要所有行。 有关详细信息，请参见“避免全表扫描”。

• 主键优化
  MySQL主键它具有关联的索引，可提高查询性能，查询性能可从NOT NULL优化中受益，因为它不能包含任何NULL值。使用InnoDB存储引擎，可以对表数据进行物理组织，以根据一个或多个主键列进行超快速查找和排序。
• 列索引
  索引的最常见类型涉及单个列，该列将来自该列的值的副本存储在数据结构中，从而允许快速查找具有相应列值的行。B树数据结构可以让索引快速查找特定值，一组值，或值的范围，对应于运营商，如=， >，≤， BETWEEN，IN，等等，一在WHERE子句。

每个存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎每个表至少支持16个索引，并且总索引长度至少为256个字节。大多数存储引擎都有更高的限制。
（Ⅰ）、普通索引
普通索引没有任何约束。
（Ⅱ）、唯一索引
唯一索引跟普通索引一样，只是必须唯一，可与复合索引组合使用
（Ⅲ）、前缀索引
使用字符串列的索引规范中的col_name（N）语法，可以创建仅使用该列的前N个字符的索引。 以这种方式仅索引列值的前缀可以使索引文件小得多。 对BLOB或TEXT列建立索引时，必须为索引指定前缀长度。 例如：

#如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

（Ⅳ）、全文索引
FULLTEXT索引用于全文搜索。仅InnoDB和MyISAM存储引擎支持FULLTEXT索引，并且仅支持CHAR，VARCHAR和TEXT列。索引始终在整个列上进行，并且不支持列前缀索引。优化适用于针对单个InnoDB表的某些FULLTEXT查询。具有以下特征的查询特别有效：
（1）、仅返回文档ID或文档ID和搜索等级的FULLTEXT查询。
（2）、FULLTEXT查询按分数的降序对匹配的行进行排序，并应用LIMIT子句获取前N个匹配的行。为了应用此优化，必须没有WHERE子句，并且只有一个降序的ORDER BY子句。
（3）、FULLTEXT查询仅检索与搜索词匹配的行的COUNT（*）值，而没有其他WHERE子句。将WHERE子句编码为WHERE MATCH（text）AGAINST（'other_text'），而没有任何> 0的比较运算符。

对于包含全文表达式的查询，MySQL在查询执行的优化阶段评估这些表达式。 优化器不仅查看全文表达式并进行估计，而且还在制定执行计划的过程中对它们进行评估。
此行为的含义是，对于全文查询，EXPLAIN通常比在优化阶段未进行任何表达式求值的非全文查询慢。
全文查询的EXPLAIN可能会由于优化过程中发生匹配而在Extra列中显示已优化的Select表。 在这种情况下，以后执行期间无需进行表访问。

如：

CREATE TABLE articles (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR (200),
    content TEXT,
    FULLTEXT (title, content) 
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COMMENT='文章';

#使用条件格式MATCH (columnName) AGAINST ('string')
explain select * from articles where MATCH (title,content) AGAINST ('MySQL');

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'articles', NULL, 'fulltext', 'title', 'title', '0', 'const', '1', '100.00', 'Using where; Ft_hints: sorted'

（Ⅴ）、空间指数
您可以在空间数据类型上创建索引。 MyISAM并InnoDB 支持有关空间类型的R树索引。其他存储引擎使用B树来为空间类型建立索引（除外 ARCHIVE，不支持空间类型建立索引）。
（Ⅵ）、复合索引
MySQL可以创建复合索引（即，多列上的索引）。一个索引最多可以包含16列。对于某些数据类型，可以为列的索引编制索引。
MySQL可以将多列索引用于测试索引中所有列的查询，或者仅测试第一列，前两列，前三列等等的查询。如果在索引定义中以正确的顺序指定列，则单个复合索引可以加快对同一表的几种查询。（按照索引顺序）。多列索引可以被认为是排序数组，其行包含通过串联索引列的值而创建的值。
如果表具有多列索引，那么优化器可以使用索引的任何最左前缀来查找行。举例来说，如果你有一个三列的索引(col1, col2, col3)，你有索引的搜索功能 (col1)，(col1, col2)以及 (col1, col2, col3)。

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

• 适合建立索引场景
  （1）、主键自动创建索引
  （2）、经常用作查询条件的字段
  （3）、查询中与其他表关联的字段、外键关系
  （4）、通常用于排序、分组的字段（排序分组优化可能使用索引）

• 不适合创建索引场景
  （1）、小表
  （2）、经常执行插入删除操作的表或字段
  （3）、字段值重复、多个值分布平均的字段，如性别

• 使用EXPLAIN优化查询
  当EXPLAIN与可解释的语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表的信息。有关 EXPLAIN用于获取执行计划信息的信息，对于SELECT语句， EXPLAIN产生可以使用来显示的其他执行计划信息 SHOW WARNINGS；在的帮助下EXPLAIN，您可以看到应该在表中添加索引的位置，以便通过使用索引查找行来使语句更快地执行。
  （1）、explain 显示信息含义：

  • id SELECT标识符。 这是查询中SELECT的顺序号。 如果该行引用其他行的并集结果，则该值可以为NULL。 在这种情况下，表列显示类似<unionM，N>的值，以指示该行引用ID值为M和N的行的并集。id大的优先执行，id相同从上往下执行。
  • SELECT的类型，可以是下表中显示的任何类型。

• table 输出行所引用的表的名称
  （1）、<unionM，N>：该行引用ID值为M和N的行的并集。
  （2）、：该行引用ID值为N的行的派生表结果。派生表可能来自于例如FROM子句中的子查询。
  （3）、：该行引用ID为N的行的实例化子查询的结果。请参见第“通过实例化优化子查询”。
• partitions 查询将从中匹配记录的分区。该值适用NULL于未分区的表。
• type 联接类型

• possible_keys Possible_keys列指示MySQL可以从中选择的索引，以查找此表中的行。查询字段涉及某个索引，则该索引被列出（不一定被使用），如果此列是NULL（或在JSON格式的输出中未定义），则没有相关的索引或使用了覆盖索引（select first_name,last_name from employees e#first_name,last_name上有一个索引）。
• key key列指示MySQL实际决定使用的key（索引）。如果key为NULL，则MySQL未找到可用于更有效地执行查询的索引。要强制MySQL使用或忽略mays_keys列中列出的索引，请在查询中使用FORCE INDEX，USE INDEX或IGNORE INDEX。
• key_len key_len列指示MySQL决定使用的key的长度(字节数)。 key_len的值使您能够确定多部分key MySQL实际使用了多少部分。 如果key列为NULL，则key_len列也为NULL。
• ref ref列显示将哪些列或常量与键列中命名的索引进行比较以从表中选择行。如果该值为func，则使用的值是某些函数的结果。如果该值为func，则使用的值是某些函数的结果。要查看哪个函数，请使用 SHOW WARNINGS以下 EXPLAIN命令查看扩展 EXPLAIN输出。（值为库.表.字段）
  
• rows 该rows列表示MySQL认为执行查询必须检查的行数。对于InnoDB表，此数字是估计值，可能并不总是准确的。
• filtered filtered列指示按表条件筛选的表行的估计百分比。最大值为100，这意味着没有对行进行筛选。值从100减小表示过滤量增加。rows显示检查的估计行数，rows×filtered显示与下表联接的行数。例如，如果rows为1000，filtered为50.00（50%），则要与下表联接的行数为1000×50%=500。
• Extra 这列包含MySQL解决查询的额外信息。

show warnings 显示信息含义;

优化方案

• 查询、排序、分组尽可能使用索引
• left join 在右表建立索引，righth join 在左表建立索引（小表驱动大表）
• 避免使用 is null、is not null、!=、 <>条件，索引会失效
• 避免对索引字段做任何操作（如+1、-1、left()等），索引会失效
• 避免使用or条件，or后面索引会失效
• 尽量使用覆盖索引查询（只从索引树上查询比查询表快）
• 子查询表小于外查询使用in，反之使用exists

索引失效

• 没遵循最左前缀规则（如index(c1,c2,c3),则只有 where、group by、order by中存在(c1),(c1,c2),(c1,c2,c3)索引有效,其中where子句索引键顺序无关;order by、group by与键顺序有关）
• 对索引键使用范围条件（如>、<、between...and），之后的索引键失效（复合索引中在该键之后的键索引失效，如index index(c1,c2,c3),select * from t where c1='c1' and c2>'c2' and c3='c3',c3索引失效）
• like 条件中 ‘%’在前面，如‘%abc’、‘%abc%’，‘abc%’后索引有效，虽然是范围条件range
• 字符串没加‘’
• order by索引失效
• group by索引失效

慢查询日志

慢查询日志由SQL语句组成，这些语句执行时间超过long_query_time秒，并且至少需要检查min_examined_row_limit行。 慢查询日志可用于查找执行时间较长的查询，因此可以作为优化的候选对象。 但是，检查较长的慢查询日志可能是一项耗时的任务。 为了使此操作更容易，您可以使用mysqldumpslow命令来处理慢速查询日志文件并总结其内容。 请参见“ mysqldumpslow-汇总慢查询日志文件”。

#sql 命令查看设置或者在my.ini（windows）和my.cnf（linux）中配置
show global variables like '%slow_quer%';#查看慢查询配置
show global variables like '%long_query%';#查看慢查询阙值
set global slow_query_log =1;#设置开启慢查询日志
set global long_query_time = 3;#设置阙值


#my.ini配置文件
[mysqld]
# General and Slow logging.
log-output=FILE
#关闭全局日志表，记录所有sql,测试环境重现问题使用。select * from mysql.general_log;
general-log=0

general_log_file="D:/Development/MySQL/logs/general_log_file.log"

slow-query-log=1

slow_query_log_file="D:/Development/MySQL/logs/slow_query_log_file.log"

long_query_time=3

使用性能模式进行查询分析

下面的示例演示如何使用Performance Schema语句事件和stage事件来检索与SHOW PROFILES和SHOW PROFILE语句提供的概要分析信息相当的数据。
setup_actors表可用于限制主机，用户或帐户对历史事件的收集，以减少运行时开销和历史表中收集的数据量。 该示例的第一步显示了如何将历史事件的收集限制为特定用户。
性能架构以皮秒（万亿分之一秒）为单位显示事件计时器信息，以将计时数据标准化为标准单位。 在以下示例中，将TIMER_WAIT值除以1000000000000，以秒为单位显示数据。 值也被截断为小数点后6位，以与SHOW PROFILES和SHOW PROFILE语句相同的格式显示数据。

#查看配置， 默认情况下，setup_actors配置为允许对所有前台线程进行监视和历史事件收集；
SELECT * FROM performance_schema.setup_actors;
#将历史事件的收集限制为运行查询的用户。
#更新setup_actors表中的默认行以禁用所有前台线程的历史事件收集和监视，并插入一个新行，该行为运行查询的用户启用监视和历史事件收集
UPDATE performance_schema.setup_actors
       SET ENABLED = 'NO', HISTORY = 'NO'
       WHERE HOST = '%' AND USER = '%';	
#配置当前主机查询监控
INSERT INTO performance_schema.setup_actors
       (HOST,USER,ROLE,ENABLED,HISTORY)
       VALUES('localhost','root','%','YES','YES');
#通过更新setup_instruments表，确保已启用语句和阶段检测。 默认情况下，某些仪器可能已启用。
 UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%statement/%';
UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%stage/%';
#确保启用events_statements_ *和events_stages_ *。 默认情况下，某些使用者可能已启用。
UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_statements_%';
UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_stages_%';

#在监控账户下执行要分析的语句
SELECT * FROM employees.employees WHERE emp_no = 10001;
# emp_no, birth_date, first_name, last_name, gender, hire_date
# '10001', '1953-09-02', 'Georgi', 'Facello', 'M', '1986-06-26'

#通过查询events_statements_history_long表来标识语句的EVENT_ID。
SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
       FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%10001%';
# EVENT_ID, Duration, SQL_TEXT
# '102', '0.174395', 'SELECT * FROM employees.employees WHERE emp_no = 10001'

       
#查询events_stages_history_long表以检索语句的阶段事件。 阶段使用事件嵌套链接到语句。 每个阶段事件记录都有一个NESTING_EVENT_ID列，其中包含父语句的EVENT_ID。
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
       FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=102;
# Stage, Duration
# 'stage/sql/starting', '0.000097'
# stage/sql/starting,	0.000097
# stage/sql/Executing hook on transaction begin.,	0
# stage/sql/starting,	0.000008
# stage/sql/checking permissions,	0.000005
# stage/sql/Opening tables,	0.011296
# stage/sql/init,	0.000004
# stage/sql/System lock,	0.000006
# stage/sql/optimizing,	0.000008
# stage/sql/statistics,	0.000016
# stage/sql/preparing,	0.00002
# stage/sql/executing,	0.162846
# stage/sql/end,	0.000001
# stage/sql/query end,	0.000001
# stage/sql/waiting for handler commit,	0.000007
# stage/sql/freeing items,	0.000061
# stage/sql/cleaning up,	0



#使用show profile
#查询配置
show variables like '%profiling%';
#开启配置
SET profiling = 1;
#执行sql
SELECT * FROM employees.employees WHERE emp_no = 10001;
#查询query_id
show profiles;
#查询query_id为1的监控信息
SHOW PROFILE FOR QUERY 1;
#type可以指定 可选值以显示特定的其他信息类型,如SHOW PROFILE ALL FOR QUERY 1;
#ALL 显示所有信息
#BLOCK IO 显示块输入和输出操作的计数
#CONTEXT SWITCHES 显示自愿和非自愿上下文切换的计数
#CPU 显示用户和系统的CPU使用时间
#IPC 显示已发送和已接收邮件的计数
#MEMORY 目前尚未实施
#PAGE FAULTS 显示主要和次要页面错误的计数
#SOURCE 显示源代码中的函数名称以及该函数所在文件的名称和行号
#SWAPS 显示掉期计数

行锁和表锁

（一）、行级锁定
MySQL对InnoDB表使用行级锁定，以支持多个会话同时进行写访问，从而使其适用于多用户，高并发和OLTP应用程序。
为了避免在单个InnoDB表上执行多个并发写操作时出现死锁，请在事务开始时通过为预期要修改的每一行行发出SELECT ... FOR UPDATE语句来获取必要的锁，即使数据更改语句也是如此稍后再交易。如果事务修改或锁定了多个表，请在每个事务中以相同顺序发出适用的语句。死锁会影响性能，而不是代表严重的错误，因为InnoDB默认情况下会自动检测死锁条件，并回滚受影响的事务之一。
在高并发系统上，当多个线程等待相同的锁时，死锁检测会导致速度变慢。有时，当发生死锁时，禁用死锁检测并依靠innodb_lock_wait_timeout设置进行事务回滚可能会更有效。可以使用innodb_deadlock_detect配置选项禁用死锁检测。
行级锁定的优点：

• 当不同的会话访问不同的行时，锁冲突减少。
• 回滚更改较少。
• 可以长时间锁定单个行。

（二）、表级锁定
MySQL对MyISAM，MEMORY和MERGE表使用表级锁定，一次只允许一个会话更新这些表。此锁定级别使这些存储引擎更适合于只读，只读或单用户应用程序。

这些存储引擎通过始终在查询开始时一次请求所有需要的锁并始终以相同顺序锁定表来避免死锁。权衡是该策略减少了并发性。其他要修改表的会话必须等待，直到当前数据更改语句完成为止。
表级锁定的优点：

• 所需的内存相对较少（行锁定需要锁定每行或每组行的内存）
• 在表的大部分上使用时非常快，因为仅涉及一个锁。
• 如果您经常对大部分数据执行GROUP BY操作，或者必须经常扫描整个表，则速度很快。

MySQL授予表写锁定，如下所示：

• 如果表上没有锁，请在其上放置写锁。
• 否则，将锁定请求放入写锁定队列中。
  MySQL授予表读取锁，如下所示：
• 如果表上没有写锁，请在其上放置一个读锁。
• 否则，将锁定请求放入读取锁定队列中。

表更新的优先级高于表检索。因此，释放锁时，该锁可用于写锁队列中的请求然后才是读锁队列中的请求。这样可以确保即使表有大量的SELECT活动，对表的更新也不会“饿死”。但是，如果表有许多更新，则SELECT语句将等到没有更多更新。

（三）、选择锁定类型
通常，在以下情况下，表锁优于行级锁：

• 该表的大多数语句均为读取。
• 该表的语句是读和写的混合，其中写是对单行的更新或删除，可通过一次按键读取来获取：

UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;

• SELECT与并发INSERT语句结合使用，很少有UPDATE或DELETE语句。
• 整个表上有许多扫描或GROUP BY操作，没有任何编写程序。
  （四）、表级锁缺点
• 表锁定允许多个会话同时从表中读取数据，但如果会话要写入表，则必须首先获得独占访问权，这意味着它可能必须先等待其他会话完成对表的操作。在更新期间，要访问此特定表的所有其他会话都必须等到更新完成。
• 当会话等待时，表锁定会导致问题，因为磁盘已满，并且需要有可用空间才能继续会话。在这种情况下，所有要访问问题表的会话也将处于等待状态，直到有更多的磁盘空间可用。
• 运行时间较长的SELECT语句会阻止其他会话同时更新表，从而使其他会话显得缓慢或无响应。当一个会话正在等待以独占方式访问该表以进行更新时，发出SELECT语句的其他会话在其后面排队，从而降低了即使是只读会话的并发性。
  （五）、行级锁定缺点
• 会产生死锁
• 行锁开销大（时间或空间）

innodb锁机制

（一）、共享锁和排他锁 （S和X）
InnoDB表使用行级锁定，因此多个会话和应用程序可以同时从同一表读取和写入同一表，而不会彼此等待或产生不一致的结果。对于此存储引擎，请避免使用该LOCK TABLES语句，因为它不提供任何额外的保护（相对于行锁定，即效果跟行锁定一样），而是减少了并发性。其中有两种类型的锁定：共享（S）锁定和排他（X）锁定。

• 共享（S）锁允许持有该锁的事务读取一行。
• 排他（X）锁允许持有该锁的事务更新或删除行。

如果事务T1在行r上持有共享（S）锁，那么来自某些不同事务T2的对行r的锁请求将按以下方式处理：

• T2对S锁的请求可以立即获得批准。 结果，T1和T2都在r上保持了S锁。
• T2不能立即授予X锁请求。
  如果事务T1在行r上持有排他（X）锁，则不能立即批准某个不同事务T2对r上任一类型的锁的请求。 相反，事务T2必须等待事务T1释放对行r的锁定。

（二）、意向锁
InnoDB支持多种粒度锁定，允许行锁和表锁并存。例如，诸如LOCK TABLES ... WRITE之类的语句对指定表采用排他锁（X锁）。为了使在多个粒度级别上的锁定变得切实可行，InnoDB使用了意图锁定。意向锁是表级锁，指示事务稍后对表中的行需要哪种类型的锁（共享锁或排他锁）。有两种类型的意图锁：

• 意向共享锁（IS）表示事务打算对表中的各个行设置共享锁。
• 意向排他锁（IX）表示事务打算对表中的各个行设置排他锁。

例如，SELECT ... FOR SHARE设置IS锁，而SELECT ... FOR UPDATE设置IX锁。

意向锁定协议如下：

• 在事务可以获取表中某行的共享锁之前，它必须首先获取该表中的IS锁或更强的锁。
• 在事务可以获取表中某行的排它锁之前，它必须首先获取该表中的IX锁。
  表级锁类型的兼容性汇总在以下矩阵中。
  
  （三）、记录锁
  记录锁定是对索引记录的锁定。 例如，从t WHERE c1 = 10 FOR UPDATE中选择c1； 防止任何其他事务插入，更新或删除t.c1值为10的行。
  （四）、间隙锁
  间隙锁定是对索引记录之间的间隙的锁定，或者是对第一个或最后一个索引记录之前的间隙的锁定。例如，从10和20 FOR UPDATE之间的t中选择c1。防止其他事务将值15插入到t.c1列中，无论该列中是否已有这样的值，因为该范围中所有现有值之间的间隙是锁定的。

间隙可能跨越单个索引值，多个索引值，甚至为空。

间隙锁是性能和并发性之间权衡的一部分，并且在某些事务隔离级别而非其他级别中使用。

对于使用唯一索引来锁定唯一行来锁定行的语句，不需要间隙锁定。 （这不包括搜索条件仅包含多列唯一索引的某些列的情况；在这种情况下，会发生间隙锁定。）例如，如果id列具有唯一索引，则以下语句仅使用ID值为100的行的索引记录锁定，其他会话是否在前面的间隙中插入行都没有关系：

SELECT * FROM child WHERE id = 100;
如果id未建立索引或索引不唯一，则该语句会锁定前面的间隙。

在这里还值得注意的是，可以通过不同的事务将冲突的锁保持在间隙上。例如，事务A可以在间隙上保留一个共享的间隙锁（间隙S锁），而事务B可以在同一间隙上保留排他的间隙锁（间隙X锁）。允许冲突的间隙锁的原因是，如果从索引中清除记录，则必须合并由不同事务保留在记录上的间隙锁。

InnoDB中的间隙锁是“完全禁止的”，这意味着它们的唯一目的是防止其他事务插入间隙。间隙锁可以共存。一个事务进行的间隙锁定不会阻止另一事务对相同的间隙进行间隙锁定。共享和专用间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能。

间隙锁定可以显式禁用。如果将事务隔离级别更改为READ COMMITTED，则会发生这种情况。在这种情况下，将禁用间隙锁定进行搜索和索引扫描，并且仅将其用于外键约束检查和重复键检查。

使用READ COMMITTED隔离级别还有其他影响。 MySQL评估WHERE条件后，将释放不匹配行的记录锁。对于UPDATE语句，InnoDB进行“半一致”读取，以便将最新的提交版本返回给MySQL，以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配。
（五）、下一键锁
下一键锁是索引记录上的记录锁定和索引记录之前的间隙上的间隙锁定的组合。

InnoDB执行行级锁定的方式是，当它搜索或扫描表索引时，会在遇到的索引记录上设置共享或互斥锁。 因此，行级锁实际上是索引记录锁。 索引记录上的下一键锁定也会影响该索引记录之前的“间隙”。 即，下一键锁是索引记录锁加上索引记录之前的间隙上的间隙锁定。 如果一个会话在索引中的记录R上具有共享或排他锁，则另一会话无法按照索引顺序在R之前的间隙中插入新的索引记录。

假设索引包含值10、11、13和20。该索引的可能的下一键锁定涵盖以下间隔，其中，圆括号表示排除区间端点，方括号表示包括端点：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

对于最后一个时间间隔，next-key锁定将间隙锁定在索引中的最大值之上，并且“超级”伪记录的值高于索引中的任何实际值。最高不是真正的索引记录，因此，实际上，此下一键锁仅锁定跟随最大索引值的间隙。

#session 1
create table innodb_lock_tbl(
id int not null primary key,
text varchar(100)
);
insert into innodb_lock_tbl values(1,'t1');
insert into innodb_lock_tbl values(2,'t2');
insert into innodb_lock_tbl values(3,'t3');
insert into innodb_lock_tbl values(5,'t5');
insert into innodb_lock_tbl values(6,'t6');
insert into innodb_lock_tbl values(7,'t7');
insert into innodb_lock_tbl values(9,'t9');
insert into innodb_lock_tbl values(10,'t10');

#取消自动提交
set autocommit =0;
#添加间隙锁和下一键锁，id = 4 和id = 8的记录有间隙锁，(1,10)记录上有记录锁，下一键锁=记录锁+间隙锁，next-key锁定将间隙锁定在索引中的最大值之上，如图
select * from innodb_lock_tbl where id >1 and id <10 for share;

#session 2
#取消自动提交
set autocommit =0;
#被间隙锁阻塞
insert into innodb_lock_tbl values (4,'t4');

#session 3
#取消自动提交
set autocommit =0;
#被记录锁阻塞
update innodb_lock_tbl set text ='text5' where id =5;

#session 4
#查询session 1加锁情况，s2和s3未执行
SELECT * FROM performance_schema.data_locks where object_name = 'innodb_lock_tbl'; 

默认情况下，InnoDB以REPEATABLE READ事务隔离级别运行。在这种情况下，InnoDB使用next-key锁定进行搜索和索引扫描，这可以防止幻像行（请参见第15.7.4节“幻像行”）。
（六）、插入意图锁
插入意图锁是在行插入之前通过INSERT操作设置的间隙锁的一种。此锁以这种方式发出信号，表明要插入的意图是：如果多个事务未插入间隙中的相同位置，则不必等待彼此插入的多个事务。假设有索引记录，其值分别为4和7。单独的事务分别尝试插入值5和6，在获得插入行的排他锁之前，每个事务都使用插入意图锁来锁定4和7之间的间隙，但不要互相阻塞，因为行是无冲突的。

下面的示例演示了在获得对插入记录的排他锁之前，使用插入意图锁的事务。该示例涉及两个客户端A和B。

客户端A创建一个包含两个索引记录（90和102）的表，然后启动一个事务，该事务将排他锁放置在ID大于100的索引记录上。排他锁在记录102之前包括一个间隙锁：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

客户B开始交易以将记录插入空白。事务在等待获得排他锁的同时获取插入意图锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

（七）、自动上锁
AUTO-INC锁是一种特殊的表级锁，由插入到具有AUTO_INCREMENT列的表中的事务获取。 在最简单的情况下，如果一个事务正在向表中插入值，那么任何其他事务都必须等待自己在该表中进行插入，以便第一个事务插入的行接收连续的主键值。

innodb_autoinc_lock_mode配置选项控制用于自动增量锁定的算法。 它使您可以选择如何在可预测的自动增量值序列与插入操作的最大并发性之间进行权衡。

.如果没有适合索引，并且MySQL必须扫描整个表以处理该语句，则表的每一行都将被锁定，从而阻塞其他用户对表的所有插入。创建良好的索引很重要，这样您的查询就不必不必要地扫描很多行。

查看锁

#您可以通过检查Table_locks_immediate和 Table_locks_waited状态变量来分析系统上的表锁争用 ，这两个变量分别指示可以立即授予表锁请求的次数和必须等待的次数：
SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

#通过表查看
#data_locks：该data_locks表显示了持有和请求的数据锁；
SELECT * FROM performance_schema.data_locks;
#data_lock_waits：显示了data_locks表中的哪些数据锁请求被data_locks表中的哪些保留数据锁阻止。 data_locks中的持有锁只有在阻止某些锁请求时才会出现在data_lock_waits中。
SELECT * FROM performance_schema.data_lock_waits;
#metadata_locks：持有和请求的元数据锁
#table_handles：持有并请求表锁
#threads：线程表记录某个事务的线程信息
select * from  performance_schema.threads where thread_id = 50 or thread_id =52;

总结：
共享锁（表获得意向共享锁IS）
非索引，所有行获得共享锁：

• 其它session(事务)能获得共享锁，所有行被锁
• 同session或其它session不能进行写操作，也不能获得排他锁
  唯一索引（记录锁），一行被锁：
• 其它session(事务)能获得共享锁，单行被锁
• 同session能进行写操作，获得排他锁
• 同session能立即获得排他锁

排他锁(表获得意向排他锁IX)
非索引，所有行获得共享锁：

• 同session或其它session不能进行写操作，也不能获得排他锁、或共享锁
  唯一索引（记录锁），一行被锁：
• 其他session不能再次获得该行排他锁
• 同session排他锁能立即覆盖共享锁（优先级比共享锁高）
• 排他锁不能立即覆盖其他session 共享锁

innodb 如何最小化和处理死锁

启用死锁检测（默认）后，InnoDB会自动检测事务死锁并回滚一个或多个事务以打破死锁。 InnoDB尝试选择一些小的事务以进行回滚，其中事务的大小由插入，更新或删除的行数决定。
如果innodb_table_locks = 1（默认值）且autocommit = 0，则InnoDB知道表锁，并且它上面的MySQL层知道行级锁。否则，在涉及由MySQL LOCK TABLES语句设置的表锁或由InnoDB以外的存储引擎设置的锁的情况下，InnoDB无法检测到死锁。通过设置innodb_lock_wait_timeout系统变量的值来解决这些情况。
如果InnoDB的监视器输出的LATEST DETECTED DEADLOCK部分包括一条消息 TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION，这表明等待列表上的事务数已经达到了200的限制。超过200个事务的等待列表被视为死锁，尝试检查等待列表的事务将回滚。如果锁定线程必须查看wait-for列表中事务拥有的1000000个以上的锁，也可能发生相同的错误。

死锁是事务数据库中的经典问题，但是除非死锁如此频繁以至于您根本无法运行某些事务，否则它们并不危险。通常，您必须编写应用程序，以便在由于死锁而使事务回滚时，它们始终准备重新发出事务。

InnoDB使用自动行级锁定。即使在仅插入或删除单行的事务中，也可能会遇到死锁。这是因为这些操作并不是真正的“原子”操作。它们会自动对插入或删除的行的（可能是多个）索引记录设置锁定。

您可以使用以下技术来处理死锁并减少发生死锁的可能性：

• 在任何时候，发出SHOW ENGINE INNODB STATUS命令来确定最新死锁的原因。这可以帮助您调整应用程序以避免死锁。
• 如果频繁的死锁警告引起问题，请通过启用innodb_print_all_deadlocks配置选项收集更广泛的调试信息。关于每个死锁的信息，不仅仅是最新的死锁，都记录在MySQL错误日志中。完成调试后禁用此选项。
• 如果事务因死锁而失败，请始终做好重新发出事务的准备。死锁并不危险。再试一次。
• 使事务保持较小和较短的持续时间，以减少冲突的可能性。
• 在进行一组相关更改后立即提交事务，以减少冲突的可能性。对于一个未提交的事务，不要让一个未提交的事务长时间处于打开状态。
• 如果使用锁定读取（SELECT ... FOR UPDATE或SELECT ... FOR SHARE），请尝试使用较低的隔离级别，例如READ COMMITTED。
• 当修改一个事务中的多个表或同一个表中的不同行集时，每次都要以一致的顺序执行这些操作。然后事务形成定义良好的队列，而不会死锁。例如，将数据库操作组织到应用程序中的函数中，或调用存储的例程，而不是在不同的位置编写多个相似的INSERT、UPDATE和DELETE语句序列。
• 将精心选择的索引添加到表中。然后，您的查询需要扫描更少的索引记录，从而设置更少的锁。使用EXPLAIN SELECT确定MySQL服务器认为哪些索引最适合您的查询。
• 少用锁。如果允许SELECT从旧快照返回数据，请不要向其添加 FOR UPDATE或FOR SHARE子句。在这里使用READ COMMITTED隔离级别是很好的，因为同一事务中的每个一致读取都会从其自己的新快照中读取。
• 如果没有其他帮助，请使用表级锁序列化事务。将锁表与事务性表（如InnoDB TABLES）一起使用的正确方法是：先用SET autocommit=0（而不是START transaction）开始事务，然后再使用LOCK TABLES，并且在显式提交事务之前不要调用UNLOCK TABLES。例如，如果您需要写入表t1并从表t2读取，可以执行以下操作：

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

表级锁可防止对表的并发更新，从而避免死锁，但代价是对繁忙系统的响应速度较慢。

• 序列化事务的另一种方法是创建一个只包含一行的辅助“信号量”表。让每个事务在访问其他表之前更新该行。这样，所有的事务都以串行方式发生。注意，InnoDB即时死锁检测算法在这种情况下也可以工作，因为序列化锁是行级锁。对于MySQL表级锁，必须使用timeout方法来解决死锁。

表锁

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK TABLES；

#查看锁
show OPEN TABLES like 'employees';
show processlist;

READ [LOCAL] 锁:

• 持有锁的会话可以读取表（但不能写入表）。

• 多个会话可以同时获取该表的READ锁。

• 其他会话可以在不显式获取READ锁的情况下读取表。

• LOCAL修饰符使其他会话可以在保持锁的同时执行无冲突的INSERT语句（并发插入）。但是，如果您要在持有锁的同时使用服务器外部的进程来操作数据库，则无法使用READ LOCAL。对于InnoDB表，READ LOCAL与READ相同。
  [LOW_PRIORITY] WRITE 锁:

• 持有锁的会话可以读写表。

• 只有持有锁的会话才能访问该表。在释放锁之前，没有其他会话可以访问它。

• 保持WRITE锁定时，其他会话对表的锁定请求将阻塞。

• LOW_PRIORITY修饰符无效。在以前的MySQL版本中，它影响了锁定行为，但现在不再如此。现在已弃用它，并且使用它会产生警告。请改用不带LOW_PRIORITY的WRITE。
  WRITE锁通常具有比READ锁更高的优先级，以确保尽快处理更新。这意味着，如果一个会话获得了READ锁，然后另一个会话请求了WRITE锁，则随后的READ锁请求将等待，直到请求WRITE锁的会话获得了该锁并释放了该锁。

共同：

• 在保留由此获得的锁的同时，会话只能访问锁定的表。
• 不能在使用相同名称的单个查询中多次引用锁定的表。

mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

• 使用别名锁定表，则必须在该语句中使用该别名引用该表；通过别名引用表，则必须使用相同的别名锁定表。

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

释放表锁

• UNLOCK TABLES语句释放
• 如果会话发出LOCK TABLES语句以在已经持有锁的同时获取锁，则在授予新锁之前隐式释放其现有锁。
• 如果会话开始事务（例如，使用START TRANSACTION），则会执行隐式UNLOCK TABLES，这将导致释放现有的锁。 （有关表锁定和事务之间的交互的其他信息，请参阅表锁定和事务之间的交互。）
• 如果在锁定的表上使用ALTER TABLE，则它可能会被解锁。 例如，如果尝试第二次ALTER TABLE操作，则结果可能是错误表'tbl_name'没有被LOCK TABLES锁定。 要处理此问题，请在第二次更改之前再次锁定表。
  如果客户端会话的连接终止，无论是正常连接还是异常连接，服务器都会隐式释放该会话持有的所有表锁（事务性和非事务性）。如果客户端重新连接，则锁定不再有效。另外，如果客户端有活动的事务，则服务器在断开连接时会回滚事务，如果发生重新连接，则新会话将从启用自动提交开始。因此，客户端可能希望禁用自动重新连接。启用自动重新连接后，如果发生重新连接，则不会通知客户端，但是任何表锁或当前事务都将丢失。在禁用自动重新连接的情况下，如果连接断开，则下一条发出的语句将发生错误。

锁和事务

• LOCK TABLES 不是事务安全的，在尝试锁定表之前隐式提交任何活动事务。
• UNLOCK TABLES隐式提交任何活动事务，但前提是已用LOCK TABLES获取表锁。例如，在以下语句集中，UNLOCK TABLES将释放全局读锁，但不会提交事务，因为没有表锁生效：

FLUSH TABLES WITH READ LOCK;
START TRANSACTION;
SELECT ... ;
UNLOCK TABLES;

• 带有读锁的FLUSH TABLES获取全局读锁，而不是表锁，因此在表锁定和隐式提交方面，它不受与锁定表和解锁表相同的行为的约束。例如，START TRANSACTION不会释放全局读锁。
• 开始一个事务（例如，使用START transaction）隐式地提交任何当前事务并释放现有的表锁。
• 其他隐式导致提交事务的语句不会释放现有的表锁。其它隐式提交事务语句。
• ROLLBACK 不释放表锁。

对事务性表（如InnoDB TABLES）使用锁表和解锁表的正确方法是，以SET autocommit=0（not START transaction）开始一个事务，并在显式提交事务之前不调用UNLOCK TABLES。例如，如果您需要写表 t1和从表中读取数据 t2，则可以执行以下操作：

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

调用LOCK TABLES时，InnoDB内部有自己的表锁，MySQL有自己的表锁。InnoDB会在下一次提交时释放内部表锁，但是MySQL要释放表锁，必须调用UNLOCK TABLES。您不应该将autocommit设置为1，因为InnoDB在调用lock TABLES之后立即释放其内部表锁，很容易发生死锁。如果autocommit=1，InnoDB根本不获取内部表锁，以帮助旧应用程序避免不必要的死锁。

表锁定限制和条件

• LOCK TABLES和 UNLOCK TABLES不能在存储的程序中使用。
• performance_schema数据库中除了setup_xxx表之外不能用LOCK TABLES锁定。
• 当锁表语句生效时，禁止使用以下语句:CREATE TABLE, CREATE TABLE ... LIKE, CREATE VIEW, DROP VIEW和关于存储函数、过程和事件的DDL语句。
• 如果要用lock tables语句显式地对这些表中的任何一个表进行写锁，则该表必须是唯一被锁定的表；不能用同一语句锁定其他表。
• 通常，您不需要锁定表，因为所有单个 UPDATE语句都是原子的。没有其他会话可以干扰任何其他当前正在执行的SQL语句。
• 如果要在一组MyISAM表上运行许多操作，则锁定要使用的表要快得多。 锁定MyISAM表可以加快对其上的插入，更新或删除操作，因为MySQL在调用UNLOCK TABLES之前不会刷新锁定表的键高速缓存。 通常，在每个SQL语句之后刷新键缓存。
• 如果要将表用于非事务性存储引擎，则要确保没有其他会话修改SELECT和UPDATE之间的表，则必须使用LOCK TABLES。 此处显示的示例要求LOCK TABLES安全执行：

LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer
  SET total_value=sum_from_previous_statement
  WHERE customer_id=some_id;
UNLOCK TABLES;

如果没有LOCK TABLES，则另一个会话可能会在执行SELECT和UPDATE语句之间在转换表中插入新行。
锁定表的不利之处在于，没有会话可以更新READ锁定表（包括持有锁的表），没有会话可以访问WRITE锁定表，除了持有锁的表之外。

InnoDB中由不同的SQL语句设置的锁

锁定读取，UPDATE或DELETE通常会对在处理SQL语句时扫描的每个索引记录设置记录锁定。 语句中是否存在排除行的条件并不重要。 InnoDB不记得确切的WHERE条件，而只知道扫描了哪个索引范围。 锁通常是下一键锁，它还会阻止在记录之前插入“间隙”。 但是，可以明确禁用间隙锁定，这将导致不使用下一键锁定。如果在搜索中使用二级索引，并且要设置的索引记录锁是互斥的，那么InnoDB还将检索相应的聚集索引记录并对其设置锁。
如果没有适合您的语句的索引，并且MySQL必须扫描整个表以处理该语句，则表的每一行都将被锁定，从而阻塞其他用户对表的所有插入。创建良好的索引很重要，这样您的查询就不必不必要地扫描很多行。

InnoDB 设置特定类型的锁:

• SELECT。。。FROM是一致的读取，读取数据库的快照，并且除非事务隔离级别设置为SERIALIZABLE，否则不设置锁。对于SERIALIZABLE级别，搜索将在遇到的索引记录上设置共享的下一个键锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁。
• SELECT ... FOR UPDATE和SELECT ... FOR SHARE使用唯一索引获取扫描行的锁，并释放不符合包含在结果集中的行的锁（例如，如果它们不满足 WHERE子句中给出的条件）。 但是，在某些情况下，行可能不会立即被解锁，因为结果行与其原始源之间的关系在查询执行期间会丢失。 例如，在UNION中，在评估它们是否符合结果集之前，可以将来自表的扫描（和锁定）行插入到临时表中。 在这种情况下，临时表中的行与原始表中的行之间的关系将丢失，并且直到查询执行结束后，后行才被解锁。
• 对于锁定读取（使用FOR UPDATE或FOR SHARE进行SELECT），UPDATE和DELETE语句，采用的锁定取决于该语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。

• 对于具有唯一搜索条件的唯一索引，InnoDB仅锁定找到的索引记录，而不锁定其前的间隙。

#test 1
alter table departments drop primary key;
#session 1
start transaction;
select * from departments e where e.dept_no = 'd001' for share;#dept_no不是索引，所有行获得共享锁，表获得意向共享锁
#select * from departments e where e.dept_no = 'd001' lock in share mode;
#select * from departments e where e.dept_no = 'd003' for update;#其它session不能获得该行排他锁、共享锁

select * from departments e where e.dept_no = 'd001' ;#可执行查询
select * from departments;

update departments e set e.dept_name ='Marketing01' where e.dept_no = 'd001';#共享锁不可执行写操作，排他锁可执行
update departments e set e.dept_name ='Finance02' where e.dept_no = 'd002';#共享锁不可执行写操作，排他锁可执行

select * from departments e where e.dept_no = 'd001' for update;#共享锁时，对该行不能获取排他锁，被阻塞，表获得意向排他锁（IX）
select * from departments e where e.dept_no = 'd002' for update;#共享锁时，对该行不能获取排他锁，被阻塞，表获得意向排他锁（IX）

commit;#不提交事务，提交后解锁
SELECT * FROM performance_schema.data_locks where object_name = 'departments';#data_locks表显示了持有和请求的数据锁；可看到全表被锁。

#session 2
select * from departments e where e.dept_no = 'd001' for share;#可获得共享锁，同时表获得意向共享锁（IS）所有行被锁定，排他锁不能获取
#select * from departments e where e.dept_no = 'd001' lock in share mode;

select * from departments e where e.dept_no = 'd002' for share;#可获得共享锁，同时表获得意向共享锁（IS）所有行被锁定

select * from departments e where e.dept_no = 'd001';#可执行查询
select * from departments;
select * from departments e where e.dept_no = 'd002';#可执行查询


update departments e set e.dept_name ='Georgi01' where e.dept_no = 'd001';#对该行不可执行写操作，被阻塞
update departments e set e.dept_name ='Georgi01' where e.dept_no = 'd002';#对该行不可执行写操作，被阻塞

select * from departments e where e.dept_no = 'd001' for update;#对该行不能获取排他锁，被阻塞
select * from departments e where e.dept_no = 'd002' for update;#对该行不能获取排他锁，被阻塞

commit;#不提交事务，提交后解锁
SELECT * FROM performance_schema.data_locks where object_name = 'departments'; #data_locks表显示了持有和请求的数据锁；可看到全表被锁。

#commit;不提交事务，提交后释放锁

#test 2

#创建行共享锁 where后条件具有唯一性
#session 1
alter table departments add primary key departments(dept_no);#添加主键索引或union索引
start transaction;
# 1
select * from departments e where e.dept_no = 'd001' for share;#dept_no是唯一索引且是主键，'d001'行获得共享锁，表获得意向共享锁，同session能再次获得排他锁
#select * from departments e where e.dept_no = 'd001' lock in share mode;
# 2
select * from departments e where e.dept_no = 'd002' for share;#其他session能获该行共享锁

# 3
select * from departments e where e.dept_no = 'd003' for update;#其它session不能获得该行排他锁、共享锁

select * from departments e where e.dept_no = 'd001' ;#可执行查询
select * from departments;

# 4
update departments e set e.dept_name ='Marketing01' where e.dept_no = 'd001';#同session对共享锁可执行写操作，无for update，获得排他锁
update departments e set e.dept_name ='Human Resources03' where e.dept_no = 'd003';#同session对排他锁可执行写操作

select * from departments e where e.dept_no = 'd001' for update;#同session有共享锁的行能获取排他锁，表获得意向排他锁（IX）
select * from departments e where e.dept_no = 'd003' for share;#同session排他锁，能执行，该行没有获得共享锁
commit;#不提交事务，提交后解锁
SELECT * FROM performance_schema.data_locks where object_name = 'departments';#data_locks表显示了持有和请求的数据锁；可看到全表被锁。

#session 2
start transaction;
# 5
select * from departments e where e.dept_no = 'd002' for share;#可获得共享锁，同时表获得意向共享锁（IS）所有行被锁定，该行有排他锁（X）则不能获得，如图1.2
#select * from departments e where e.dept_no = 'd001' lock in share mode;
select * from departments e where e.dept_no = 'd003' for share;#不能获得行共享锁，被排他锁阻塞
select * from departments e where e.dept_no = 'd001';#可执行查询
select * from departments;
select * from departments e where e.dept_no = 'd002';#可执行查询

update departments e set e.dept_name ='Finance02' where e.dept_no = 'd002';#对该行不可执行写操作，没有获取排他锁，被阻塞
update departments e set e.dept_name ='Human Resources03' where e.dept_no = 'd003';#排他锁不可执行写操作

# 6
select * from departments e where e.dept_no = 'd002' for update;#对该行能获取排他锁，但不能立即执行，被阻塞

commit;#不提交事务，提交后解锁
SELECT * FROM performance_schema.data_locks where object_name = 'departments'; #data_locks表显示了持有和请求的数据锁；可看到全表被锁。

#查看锁信息
#该INNODB_TRX表提供有关InnoDB内部当前正在执行的每个事务的信息，包括事务状态（例如，它是正在运行还是正在等待锁），事务何时开始以及事务正在执行的特定SQL语句。
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
SELECT * FROM performance_schema.data_locks; 
SELECT * FROM performance_schema.data_lock_waits;#此性能模式表指示哪些事务正在等待给定的锁，或者给定事务正在等待的锁。
select * from  performance_schema.threads where thread_id = 50 or thread_id =52; #事务执行的线程

• 对于其他搜索条件和非唯一索引，InnoDB使用间隔锁定或下一键锁定来锁定扫描的索引范围，以阻止其他会话插入该范围所覆盖的间隔。 有关间隙锁定和下一键锁定的信息。

• 对于搜索遇到的索引记录，SELECT ... FOR UPDATE阻止其他会话执行SELECT ... FOR SHARE或读取在某些事务隔离级别。一致的读取将忽略设置在读取视图上的任何锁。
• UPDATE ... WHERE ...在搜索遇到的每条记录上设置独占的下一键锁定。 但是，对于使用唯一索引来搜索唯一行的行来锁定的语句，仅需要索引记录锁定。
• 当UPDATE修改聚集索引记录时，对受影响的辅助索引记录进行隐式锁定。 在插入新的辅助索引记录之前执行重复检查扫描时，以及在插入新的辅助索引记录时，UPDATE操作还会在受影响的辅助索引记录上获得共享锁。
• DELETE FROM ... WHERE ...在搜索遇到的每条记录上设置独占的下一键锁定。 但是，对于使用唯一索引来搜索唯一行的行来锁定的语句，仅需要索引记录锁定。
• INSERT在插入的行上设置排他锁。 该锁是索引记录锁，不是下一个键锁（即没有间隙锁），并且不会阻止其他会话插入到插入行之前的间隙中。
  在插入行之前，设置了一种称为插入意图间隙锁的间隙锁。 此锁以这种方式发出信号，表明要插入的意图是：如果多个事务未插入间隙中的相同位置，则不必等待彼此插入的多个事务。 假设有索引记录，其值分别为4和7。尝试在插入行上获得排他锁之前，分别尝试插入5和6的值的每个事务都使用插入意图锁来锁定4和7之间的间隙，但是没有 彼此阻塞，因为行没有冲突。
  如果发生重复键错误，则会在重复索引记录上设置一个共享锁。 如果另一个会话已经具有互斥锁，则如果有多个会话试图插入同一行，则使用共享锁可能会导致死锁。 如果另一个会话删除该行，则会发生这种情况。 假设InnoDB表t1具有以下结构：

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

现在假设三个会话按顺序执行以下操作：

#session 1
START TRANSACTION;
INSERT INTO t1 VALUES(1); #获得意向排他锁IX
#session 2
START TRANSACTION;
INSERT INTO t1 VALUES(1); #等待获得一个共享锁S,被授予排他锁X和意向排他锁IX,如图s2

#session 3
START TRANSACTION;
INSERT INTO t1 VALUES(1); #等待获得一个共享锁S,被授予意向排他锁IX;s2被授予排他锁X和意向排他锁IX,如图s3

#session 1
ROLLBACK; #session 1 释放排他锁，session 2 和session 3被授予意向排他锁IX，竞争排他锁X造成死锁
#s2s3 Error Code: 1205. Lock wait timeout exceeded; try restarting transaction

#以下操作也会发生类似的情况
#session 1
START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

#session 2
START TRANSACTION;
INSERT INTO t1 VALUES(1);

#session 3
START TRANSACTION;
INSERT INTO t1 VALUES(1);

#session 1
COMMIT;

session 1的第一个操作获取该行的排他锁。session 2和会话3的操作都会导致一个重复键错误，它们都请求为该行提供一个共享锁。当session 1回滚时，它将释放该行上的排他锁，并授予session 2和session 3的排队共享锁请求。此时，session 2和session 3死锁：由于另一个session持有共享锁，因此这两个session都无法获取该行的排他锁。
图s2

图s3

• INSERT ... ON DUPLICATE KEY UPDATE与简单的区别在于，INSERT在发生重复键错误时，将排他锁而不是共享锁放在要更新的行上。对重复的主键值采用排它索引记录锁定。 唯一的下一键锁定为重复的唯一键值。
• 如果唯一键上没有冲突，则REPLACE就像INSERT一样完成。 否则，排他的下一键锁将放置在要替换的行上。
• INSERT INTO T SELECT ... FROM S WHERE ...在插入T的每一行上设置排他索引记录锁（不带间隙锁）。如果事务隔离级别为READ COMMITTED，则InnoDB会以一致的方式对S进行搜索 读取（无锁）。否则，InnoDB在S的行上设置共享的下一键锁。在后一种情况下，InnoDB必须设置锁：在使用基于语句的二进制日志的前滚恢复期间，每个SQL语句都必须以与最初完全相同的方式执行。
• InnoDB在初始化表上先前指定的AUTO_INCREMENT列时，在与AUTO_INCREMENT列关联的索引的末尾设置排他锁。在innodb_autoinc_lock_mode = 0的情况下，InnoDB使用特殊的AUTO-INC表锁定模式，该模式将在访问自动递增计数器的同时获取并保持到当前SQL语句的末尾（而不是整个事务的末尾）。 按住AUTO-INC表锁时，其他客户端无法插入该表。对于innodb_autoinc_lock_mode = 1的“批量插入”，也会发生相同的行为。 表级AUTO-INC锁不与innodb_autoinc_lock_mode = 2一起使用。InnoDB无需设置任何锁定即可获取先前初始化的AUTO_INCREMENT列的值。
• 如果在表上定义了外键约束FOREIGN KEY，则任何需要检查约束条件的插入、更新或删除操作都会在检查约束时查看的记录上设置共享记录级锁。InnoDB还会在约束失败的情况下设置这些锁。
• LOCK TABLES设置表锁，但是设置这些锁的是InnoDB层之上的MySQL高层。如果InnoDB_table_locks=1（默认值）和autocommit=0，InnoDB就知道表锁，InnoDB上面的MySQL层知道行级锁。否则，InnoDB的自动死锁检测无法检测到涉及此类表锁的死锁。 同样，由于在这种情况下，较高的MySQL层不知道行级锁，因此可以在另一个会话当前具有行级锁的表上获取表锁。但是，这不会危及事务完整性。
• 如果innodb_table_locks = 1（默认值），则LOCK TABLES在每个表上获取两个锁。 除了MySQL层上的表锁外，它还获得一个InnoDB表锁。 4.1.2之前的MySQL版本未获得InnoDB表锁； 可以通过设置innodb_table_locks = 0来选择旧的行为。 如果没有获取InnoDB表锁，即使表的某些记录被其他事务锁定，LOCK TABLES也会完成。
  在MySQL 8.0中，innodb_table_locks = 0对于使用LOCK TABLES ... WRITE显式锁定的表无效。 对于通过LOCK TABLES ... WRITE隐式（例如，通过触发器）或LOCK TABLES ... READ锁定的表进行读取或写入操作，它确实具有作用。

#如果autocommit = off且innodb使用LOCK TABLES；innodb不会从LOCK TABLES ... WRITE 返回直到其他线程释放了所有的锁；
#autocommit = off且innodb_table_locks= on,使用LOCK TABLES会导致InnoDB在内部锁定表。
#对于用LOCK TABLES ... WRITE明确锁定的表，innodb_table_locks = 0无效。
#对于通过LOCK TABLES ... WRITE隐式（例如，通过触发器）或LOCK TABLES ... READ锁定的表进行读取或写入操作，它确实具有作用。（这句不知道啥意思）
show variables like 'innodb_table_locks';
#自动提交事务
show variables like 'autocommit';
#阻塞时长，超时后检测死锁并回滚事务
show variables like 'innodb_lock_wait_timeout';

#test
#s1
set autocommit = 0;
set innodb_table_locks = 0 ;  
lock table employees write;
select sleep(60);
commit;
unlock table;

#s
SELECT * FROM performance_schema.data_locks where object_name = 'employees'; #如图1，innodb_table_locks= off;innodb没有产生内部锁
show OPEN TABLES like 'employees';#图2
#s2 一直等待
set innodb_lock_wait_timeout = 10 ;  
set autocommit = 0;
lock table employees write;
#update employees set first_name ='Georgi' where emp_no ='10001';
commit;

#s1
set autocommit = 0;
set innodb_table_locks = 1 ;  
lock table employees write;
select sleep(60);
commit;
unlock table;
#s
SELECT * FROM performance_schema.data_locks where object_name = 'employees'; #如图3，innodb_table_locks= off;innodb产生表级排他锁
show OPEN TABLES like 'employees';#图2

图1

图2

图3

• 提交或中止事务时，将释放事务持有的所有InnoDB锁。 因此，在autocommit = 1模式下在InnoDB表上调用LOCK TABLES没有多大意义，因为获取的InnoDB表锁将立即释放。
• 您不能在事务中间锁定其他表，因为LOCK TABLES执行隐式的COMMIT和UNLOCK TABLES。

innodb事务提交

默认情况下，与MySQL服务器的连接从启用的自动提交模式开始，该模式会在您执行它时自动提交每个SQL语句。
要使用多语句事务，请使用SQL语句SET autocommit = 0关闭自动提交，并在适当时以COMMIT或ROLLBACK结束每个事务。 要保留自动提交功能，请以START TRANSACTION开始每个事务，并以COMMIT或ROLLBACK结束它。

innodb 隔离级别

1、脏读（dirty read）：一个事务可以读取另一个尚未提交事务的修改数据。
2、非重复读（nonrepeatable read）：在同一个事务中，同一个查询在T1时间读取某一行，在T2时间重新读取这一行时候，这一行的数据已经发生修改，可能被更新了（update），也可能被删除了（delete）。
3、幻像读（phantom read）：在同一事务中，同一查询多次进行时候，由于其他插入操作（insert）的事务提交，导致每次返回不同的结果集。

• REPEATABLE READ （可重复读）这是InnoDB得默认隔离级别。 同一事务中的一致读取将读取第一次读取建立的 快照。这意味着，如果您SELECT 在“同一事务”中发出多个普通（非锁定）语句，则这些 SELECT语句彼此之间也是一致的。
• READ COMMITTED（读已提交）即使在同一事务中，每个一致的读取都将设置并读取其自己的新快照。
• READ UNCOMMITTED （读未提交）SELECT语句以非锁定方式执行，但可能使用行的早期版本。因此，使用这个隔离级别，这样的读取是不一致的。这也叫脏读。
• SERIALIZABLE （序列化）此级别类似于REPEATABLE READ，但如果禁用了自动提交，则InnoDB会将所有普通的SELECT语句隐式转换为SELECT ... FOR SHARE。 如果启用了自动提交，则SELECT是它自己的事务。 因此，它被认为是只读的，并且如果以一致的（非锁定）读取方式执行并且不需要阻塞其他事务就可以序列化。 （如果其他事务已修改选定的行，则强制普通SELECT阻止，请禁用自动提交。）

事务特性

ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性： 原子性是指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性：一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。
• 隔离性：隔离性是指并发的事务是相互隔离的。即一个事务内部的操作及正在操作的数据必须封锁起来，不被企图进行修改的事务看到 。
• 持久性：持久性是指在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。 即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中。

SQL查询语句执行顺序

FROM (table_name) [ > ON(condition) > JOIN (table_name)] > WHERE > GROUP BY[ > HAVING] > SELECT(DISTINCT) > ORDER BY[ > LIMIT (number)]

JOIN 类型

• ON和WHERE
  ON子句用于指定如何联接表的条件，而WHERE子句限制要在结果集中包括哪些行。
  如果在中的ON或USING部分中 没有与右表匹配的行，则将 LEFT JOIN所有列设置为的行 NULL用于右表。您可以使用此事实在一个表中查找在另一个表中没有对应项的行：

#本示例查找其中不存在left_tbl 任何id值的 所有行right_tbl（即，left_tbl其中没有对应行的 所有行 right_tbl）。
SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

#内连接inner join =》（C）
#内连接 交集，相当于‘,’，优先级比‘,’高，如果在存在联接条件时将逗号联接与其他联接类型混合使用，则可能会出现形式错误
select * from A inner join B on A.id = B.id

#左外连接left (outer) join =》(A+C)
select * from A left join B on A.id = B.id

#左外连接left (outer) join =》(A)
select * from A left join B on A.id = B.id where B.id is null

#右外连接right (outer) join =》(B+C)
select * from A right join B on A.id = B.id

#右外连接right (outer) join =》(B)
select * from A right join B on A.id = B.id where A.id is null


#全外连接full (outer) join =>(A+B+C)
#MySQL不支持全外连接，实现方式 union
select * from A full join B on A.id = B.id

#union
select * from A left join B on A.id = B.id   #A+C
union
select * from A right join B on A.id = B.id where A.id is null #B

#全外连接full (outer) join =>(A+B)
select * from A full join B on A.id = B.id where A.id is null or B.id is null

#union
select * from A left join B on A.id = B.id where B.id is null #A
union
select * from A right join B on A.id = B.id where A.id is null #B

MySql配置文件

my.cnf
服务器选项，系统变量和状态变量参考

MySQL主从复制，读写分离(使用docker 容器模拟)

#自定义网络，是容器能通信
docker network create --driver bridge --subnet 172.19.0.0/16 mysql_network

#创建挂载目录(目录内配置文件需自己复制进去)
mkdir -p /usr/etc/mysql8.0/mysql-master/conf /usr/etc/mysql8.0/mysql-master/logs /usr/etc/mysql8.0/mysql-master/data /usr/etc/mysql8.0/mysql-master/mysql-files

docker run --network mysql_network --ip 172.20.0.11 -p 13306:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 -v /usr/etc/mysql8.0/mysql-master/conf:/etc/mysql -v /usr/etc/mysql8.0/mysql-master/logs:/var/log/mysql -v /usr/etc/mysql8.0/mysql-master/data:/var/lib/mysql -v /usr/etc/mysql8.0/mysql-master/mysql-files:/var/lib/mysql-files  mysql

mkdir -p /usr/etc/mysql8.0/mysql-slave/conf /usr/etc/mysql8.0/mysql-slave/logs /usr/etc/mysql8.0/mysql-slave/data /usr/etc/mysql8.0/mysql-slave/mysql-files

docker run --network mysql_network --ip 172.20.0.12 -p 23306:3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD=123456 -v /usr/etc/mysql8.0/mysql-slave/conf:/etc/mysql -v /usr/etc/mysql8.0/mysql-slave/logs:/var/log/mysql -v /usr/etc/mysql8.0/mysql-slave/data:/var/lib/mysql -v /usr/etc/mysql8.0/mysql-slave/mysql-files:/var/lib/mysql-files  mysql

#开放端口，还有配置阿里控制台访问规则
firewall-cmd --zone=public --add-port=13306/tcp --permanent
firewall-cmd --zone=public --add-port=23306/tcp --permanent

以下涉及set 环境变量均建议卸载my.cnf文件中，否则重启容器后变量会重置

主库mysql控制台

#查看二进制文件是否开启，以及文件名
show variables like 'log_bin%';
#设置server_id，主机为1，从机为2
set global server_id =1;
#查看server_id
show variables like 'server_id';
#下述两个参数为1，InnoDB与事务一起使用的复制设置中获得最大的持久性和一致性
show variables like 'innodb_flush_log_at_trx_commit';
show variables like  'sync_binlog';
#是否开启网络连接
show variables like  'skip_networking';
#创建用于复制的用户,默认使用caching_sha2_password加密
CREATE USER 'salve_repl'@'%' IDENTIFIED BY '123456';
#赋予salve_repl所有库.所有表复制权限
GRANT REPLICATION SLAVE ON *.* TO 'salve_repl'@'%';

#查看用户
select * from mysql.user;
#查看SSL连接配置
#ssl_ca：证书颁发机构（CA）证书文件的路径名。（ssl_capath类似，但指定CA证书文件目录的路径名。）
#ssl_cert：服务器公钥证书文件的路径名。该证书可以发送到客户端，并根据其拥有的CA证书进行身份验证。
#ssl_key：服务器私钥文件的路径名。
show variables like 'ssl_%';

#获取复制二进制文件坐标
#执行FLUSH TABLES WITH READ LOCK语句刷新所有表并阻止写入语句
FLUSH TABLES WITH READ LOCK;
#在源上的另一个会话中，使用该 SHOW MASTER STATUS语句确定当前二进制日志文件的名称和位置
SHOW MASTER STATUS;
#从库配置复制源并启动复制，该复制启动之前的数据必须要锁表之后生成dump文件，使用dump文件导入从库，从库宕机期间主库操作不会复制到从库。
#从库开启复制服务后解锁
unlock tables;

#测试
create database repl_database;
use repl_database;
create table test_tbl(
id int not null,
name varchar(200));
insert into test_tbl values(1,'n1');
insert into test_tbl values(2,'n2');
insert into test_tbl values(3,'n3');
insert into test_tbl values(4,'n4');
insert into test_tbl values(5,'n5');

从服务器MySQL控制台

#查看二进制文件是否开启，以及文件名，默认情况下，所有服务器上都启用二进制日志记录。副本不需要启用二进制日志记录就可以进行复制。但是，副本上的二进制日志记录意味着副本的二进制日志可用于数据备份和崩溃恢复。启用了二进制日志记录的副本也可以用作更复杂的复制拓扑的一部分。例如，您可能要使用此链式设置来设置复制服务器：A -> B -> C
show variables like 'log_bin%';
#设置server_id，主机为1，从机为2
set global server_id =2;
#查看server_id
show variables like 'server_id';
#下述两个参数为1，InnoDB与事务一起使用的复制设置中获得最大的持久性和一致性
show variables like 'innodb_flush_log_at_trx_commit';
show variables like  'sync_binlog';
#是否开启网络连接，默认禁用，允许tcp连接
show variables like  'skip_networking';
#副本更新，默认启用，副本会将从源接收并由副本的SQL线程执行的更新写入从库自己的二进制日志。
#如果需要禁用副本上的二进制日志记录或副本更新日志记录，可以通过指定从库的skip_log_bin 和log_slave_updates=OFF选项来实现。
show variables like 'log_slave_updates';
#没有该参数
#show variables like 'skip_log_bin';
#binlog_do_db 复制的数据库，默认空，全部；要配置则使用多个set 语句配置多个数据库
show variables like 'binlog_do_db';
#binlog_ignore_db 忽略数据库，默认空，不忽略
show variables like 'binlog_ignore_db';

#查看SSL配置，salve_repl用户密码使用caching_sha2_password加密
#要在从库副本上启用加密连接，请使用CHANGE MASTER TO语句。
#您可以在副本的my.cnf文件的[client]部分中为加密连接所需的副本的证书和SSL私钥文件命名，
#或者可以使用CHANGE MASTER TO语句显式指定该信息。https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html,https://dev.mysql.com/doc/refman/8.0/en/using-encrypted-connections.html
#要使用选项文件命名副本的证书和密钥文件，请将以下行添加到副本的my.cnf文件的[client]部分，并根据需要更改文件名：
#[client]
#ssl-ca=cacert.pem
#ssl-cert=client-cert.pem
#ssl-key=client-key.pem
#可看到该文件和配置由于是同一个镜像，所以跟主库（源）相同
show variables like '%ssl_%';
#设置源配置，使用CHANGE MASTER TO来指定源配置，并添加MASTER_SSL选项以使用加密进行连接
#ip 为自定义的ip
CHANGE MASTER TO  MASTER_HOST='172.20.0.11', 
MASTER_USER='salve_repl', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='binlog.000004', 
MASTER_LOG_POS=692,
MASTER_SSL=1;

#开启复制
start slave;
#查看状态，Slave_io_running、Slave_SQL_running两个字段必须为YES
SHOW SLAVE STATUS;
#查看复制信息
select * from mysql.slave_master_info;

#测试
show databases;
use repl_database;
select * from test_tbl;
#停止复制
stop slave;

主mysql服务器

从mysql服务器