= 简介 =
MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
*MySQL 是开源的，目前隶属于 Oracle 旗下产品。
*MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
*MySQL 使用标准的 SQL 数据语言形式。
*MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
*MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
*MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。
= 环境 =
本文部署环境为linux，使用docker进行容器化部署。
= 配置文件推荐（性能调优） =
<syntaxhighlight lang="js">
[mysqld]
########basic settings########
disabled_storage_engines="MEMORY"
server-id =12345678
port = 3301
socket = /tmp/mysql_3301.sock
user = mysql
character_set_server=utf8mb4
lower_case_table_names=1
max_allowed_packet = 128M
#explicit_defaults_for_timestamp = 1
#event_scheduler = 1
basedir=/usr/local/mysql-5.7.20
datadir=/data/mysql_data/3301
tmpdir=/data/mysql_log/3301/tmp_dir
max_execution_time=100000
#sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
########connection settings########
max_connections=8500
max_user_connections=8000
max_connect_errors = 6553600
connect_timeout = 60
net_read_timeout = 60
# 非交互式连接 最大空闲时长（jdbc）单位：秒
wait_timeout = 120
# 交互式连接 最大空闲时长（mysql客户端）单位：秒
interactive_timeout=120
skip_name_resolve = 1
########table cache settings########
table_open_cache = 20000
table_definition_cache=10000
table_open_cache_instances = 16
########session memory settings########
read_buffer_size = 8M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 64M
tmp_table_size = 256M
thread_cache_size = 256
########log settings########
log-error=/data/mysql_log/3301/error_log/error_3301.log
# 慢查询开启
slow_query_log = 1
# 慢查询sql存放文件路径
slow_query_log_file=/data/mysql_log/3301/slow_log/slow_log_3301.log
expire_logs_days = 10
# 记录查询超过1秒的语句
long_query_time = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1
log_slow_slave_statements = 1
binlog_rows_query_log_events = 1
log_bin_trust_function_creators = 1
########innodb settings########
# 表示缓冲池字节大小，推荐值为物理内存的50%~80%。
innodb_buffer_pool_size = 32G
# 可以修改 InnoDB 为独立表空间模式，每个数据库的每个表都会生成一个数据空间。当一个 MySQL 服务器作为 Mycat 分片表存储服务器使用的情况下，单独表空间的访问性能要大大好友共享表空间，因此强烈建议使用独立表空间。
innodb_file_per_table=1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_home_dir = /data/mysql_data/3301
innodb_data_file_path = ibdata1:500M;ibdata2:500M:autoextend
innodb_log_group_home_dir = /data/mysql_log/3301/redo_log
#innodb_undo_directory = /data/mysql_log/3301/undo_log
#innodb_rollback_segments = 128
#innodb_undo_tablespaces = 3
#innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 2G
# 除了缓存表数据和索引外,可以为操作所需的其他内部项分配缓存来提升InnoDB 的性能。这些内存就可以通过此参数来分配。（默认值不到百MB，调到1G提升3000tps）
#innodb_additional_mem_pool_size = 200M
innodb_flush_neighbors = 0
# 此参数确定数据日志文件的大小，以 M 为单位，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间。（默认值50MB，调到2G后，tps由2000提升到20000，提高了10倍）
innodb_log_file_size = 4G
# InnoDB 存储引擎的事务日志所使用的缓冲区
innodb_log_buffer_size = 16M
# 用来控制redo log刷新到磁盘的策略。
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
# 后台进程最大IO性能指标。默认200，如果SSD，调整为5000~20000
#cl for 1k product for 1w
innodb_io_capacity = 1000
#cl for 2k product for 2w
innodb_io_capacity_max = 2000
innodb_open_files=60000
# 并发线程数，设为 CPU 核数的两倍
#innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 64M
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size=1G
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_numa_interleave=1
innodb_page_cleaners = 8
########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
slave_load_tmpdir=/data/mysql_log/3301/tmp_dir
#log_bin_basename=/data/mysql_log/3301/bin_log/mysql-bin-3301
log_bin=/data/mysql_log/3301/bin_log/mysql-bin-3301
log_bin_index =/data/mysql_log/3301/bin_log/mysql-bin-3301.index
relay_log=/data/mysql_log/3301/relay_log/mysql-relay-3301
relay_log_index=/data/mysql_log/3301/relay_log/mysql-relay-3301.index
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates=1
binlog_format = row
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors
transaction_write_set_extraction=XXHASH64
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
slave_preserve_commit_order=1
slave_transaction_retries=128
</syntaxhighlight>
= 常用配置查看命令 =
*查看mysql数据库默认编码:show variables like "character%";
*查看慢查询日志是否开启：show variables like 'slow_query_log';
*查看binlog是否开启：show variables like "%log_bin%";
*查看数据库最大连接数SQL：show variables like '%max_connections%';
*查看数据库各表数据条数及大小SQL：SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,3), 'MB') as total_size FROM information_schema.TABLES WHERE TABLE_SCHEMA='database_name' order by length desc;
*查看数据库存储空间命令：df -h
= Mysql设计原则 =
== 核心原则 ==
*不在数据库做运算;
*控制列数量(字段少而精,字段数建议在20以内);
== sql类原则 ==
*当只要一行数据时使用 LIMIT 1(提高查询性能，预防非预期错误)
*sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库);
*减少与数据库交互次数，尽量采用批量SQL语句
*禁用count(*);
*使用union all替代union(union有去重开销);
*少用连接join;
*使用group by;
*使用同类型比较;
= Mysql设计规范 =
== 数据库命名规范 ==
*采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成,禁止使用mysql保留字;
*命名简洁明确(长度不能超过30个字符);
例如：vr_mmo; 除非是备份数据库可以加0-9的自然数：vr_mmo_20171210;
== 数据库表名命名规范 ==
*采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成,禁止使用mysql保留字;
*命名简洁明确,多个单词用下划线'_'分隔;
*静态表的表名增加s_的前缀，动态表的表名增加d_的前缀。
例如：用户表d_user, 物品类型表s_itemtype
*表前缀'player_'可以有效的把相同关系的表显示在一起;
== 数据库表字段名命名规范 ==
*采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成,禁止使用mysql保留字;
*命名简洁明确,多个单词用下划线'_'分隔;
例如：player表字段 id, name, login_time;
*每个表中必须有自增主键;
*使用配置表的ID做为字段，字段名要体现出对应的表。比如物品表item有个字段是物品类型对应itemtype的id，那物品表的相应字段应该为itemtypeId。
*表与表之间的相关联字段名称要求尽可能的相同;
== 数据库表字段类型规范 ==
*用尽量少的存储空间来存数一个字段的数据;
例如：能使用tinyint就不要使用smallint,能使用smallint就不要使用int,能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256)，只有配置表（总体尺寸小）才不需要遵守;
*字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);
*建议使用UNSIGNED存储非负数值;
*固定长度的类型最好使用char,例如：邮编;
*所有字段均定义为NOT NULL,给每个字段一个默认值(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);
*在扩充表字段的时候，通常建议在原表的尾部添加字段，不要在中间插入。这样在更新时，可以提前更新表结构，后更新代码。减少因版本更新不同步，导致的字段错位和混乱，甚至导致数据丢失等严重后果;
== 数据库表索引规范 ==
*命名简洁明确,例如：item表owner字段的索引应为owner_index唯一索引;
*为每个表创建一个主键索引,推荐使用UNSIGNED自增列作为主键;
*哪些情况需要建立索引：出现在where的字段，较频繁地作为查询条件的字段
*不适合建立索引：字段更新太频繁，唯一性太差的字段(比如性别)
*建立复合索引请慎重，不用外键(由程序保证约束)，建立索引可以加快查询速度，但是索引的创建和维护需要消耗时间和物理空间。
== 简单熟悉数据库范式 ==
*第一范式(1NF)：字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式);
例如：姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段;
*第二范式(2NF)：一个表必须有主键,即每行数据都能被唯一的区分;
备注：必须先满足第一范式;
*第三范式(3NF)：一个表中不能包含其他相关表中非关键字段的信息,即数据表不能有冗余字段;
备注：必须先满足第二范式; 备注：平衡范式与冗余，效率优先;
= 主从复制 =
== 简介 ==
主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库，主数据库一般是准实时的业务数据库。您看，像在mysql数据库中，支持单项、异步赋值。在赋值过程中，一个服务器充当主服务器，而另外一台服务器充当从服务器。此时主服务器会将更新信息写入到一个特定的二进制文件中。并会维护文件的一个索引用来跟踪日志循环。这个日志可以记录并发送到从服务器的更新中去。当一台从服务器连接到主服务器时，从服务器会通知主服务器从服务器的日志文件中读取最后一次成功更新的位置。然后从服务器会接收从哪个时刻起发生的任何更新，然后锁住并等到主服务器通知新的更新。
== 主从复制的好处 ==
*做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
*架构的扩展。业务量越来越大,I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的评率，提高单个机器的I/O性能。
*读写分离，使数据库能支持更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。
== 步骤 ==
=== 部署主服 ===
（1）开启bin-log
（2）在mysqld.cnf中配置server-id = 1
（3）重启Mysql
（4）提供允许同步的账号 GRANT all ON *.* TO 'DBbackup'@'%' IDENTIFIED BY 'DBbackup.nd' ;
（5）show master status; 查看主服状态，如下图显示：
[[File:Mysql1.png|Mysql1.png]]
=== 部署备服 ===
（1）执行刷库脚本，保证数据库是干净的，且表结构与主服要同步的数据库是一致的。
（2）在mysqld.cnf中配置server-id = 2
（3）重启mysql
（4）执行开启同步命令：
<syntaxhighlight lang="js">
change master to master_host='10.0.3.4', master_user='DBbackup', master_password='DBbackup.nd', master_log_file='mysql-bin.000001', master_log_pos=0;
start slave;
</syntaxhighlight>
（5）查看从库状态
<syntaxhighlight lang="js">
show slave status\G //查看从库状态，其中Slave_IO_Running:Yes、Slave_SQL_Running:Yes 即为配置成功
</syntaxhighlight>
（6）检查数据库数据是否从主库同步过来了
= explain =
== 简介 ==
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法，在select语句前加上explain就可以了：
如：
explain select surname,first_name form a,b where a.id=b.id
=== explain列名解释 ===
'''table：'''显示这一行的数据是关于哪张表的
'''type：'''这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 一般来说，得保证查询至少达到range级别，最好能达到ref。
'''possible_keys：'''显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
'''key：''' 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
'''key_len：'''使用的索引的长度。在不损失精确性的情况下，长度越短越好
'''ref：'''显示索引的哪一列被使用了，如果可能的话，是一个常数
'''rows：'''MYSQL认为必须检查的用来返回请求数据的行数
'''Extra：'''关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
==== extra列返回的描述的意义 ====
'''Distinct：'''一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
'''Not exists：''' MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
'''Range checked for each Record（index map:#）：'''没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
'''Using filesort：''' 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
'''Using index：''' 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
'''Using temporary：''' 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
'''Where used：''' 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）
'''system：''' 表只有一行：system表。这是const连接类型的特殊情况
'''const：'''表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
'''eq_ref：'''在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
'''ref：'''这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
'''range：'''这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
'''index： '''这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
'''ALL：'''这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免
= 索引与索引优化 =
== 简介 ==
索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据结构。 我们需要知道索引其实是一种数据结构，其功能是帮助我们快速匹配查找到需要的数据行，是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。 索引一经创建不能修改，如果要修改索引，只能删除重建。
== 索引类型 ==
=== 主键索引（PRIMARY KEY） ===
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。注意：一个表只能有一个主键。
=== 唯一索引（UNIQUE） ===
唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建唯一索引：
<syntaxhighlight lang="js">
ALTER TABLE table_name ADD UNIQUE (column);
</syntaxhighlight>
创建唯一组合索引：
<syntaxhighlight lang="js">
ALTER TABLE table_name ADD UNIQUE (column1,column2);
</syntaxhighlight>
=== 普通索引（INDEX） ===
这是最基本的索引，它没有任何限制。
创建普通索引：
<syntaxhighlight lang="js">
ALTER TABLE table_name ADD INDEX index_name (column);
</syntaxhighlight>
=== 组合索引（INDEX） ===
即一个索引包含多个列，多用于避免回表查询。
创建组合索引：
<syntaxhighlight lang="js">
ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);
</syntaxhighlight>
=== 全文索引（FULLTEXT） ===
也称全文检索，是目前搜索引擎使用的一种关键技术。
创建全文索引：
<syntaxhighlight lang="js">
ALTER TABLE table_name ADD FULLTEXT (column);
</syntaxhighlight>
== 索引设计的原则 ==
*适合索引的列是出现在where子句中的列，或者连接子句中指定的列；
*基数较小的类，索引效果较差，没有必要在此列建立索引；
*使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间；
*不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
== 组合索引的最左优先原则 ==
在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到，示例： 对列col1、列col2和列col3建一个联合索引：
KEY test_col1_col2_col3 on test(col1,col2,col3);
联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。
上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。
== 为什么要使用组合索引 ==
*减少开销。建一个组合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用组合索引会大大的减少开销.
*覆盖索引。对组合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
*效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是组合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升。
= 锁与事务 =
== 锁机制 ==
*共享锁（读锁）：其他事务可以读，但不能写。
*排他锁（写锁） ：其他事务不能读取，也不能写。
== 粒度锁 ==
MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：
*MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
*BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁
*InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。
但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。
== 不同粒度锁的比较 ==
*表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
1.这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。 2.表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
*行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
1.最大程度的支持并发，同时也带来了最大的锁开销。 2.在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。 3.行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
*页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
== InnoDB行级锁和表级锁 ==
InnoDB 实现了以下两种类型的行锁：
*共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
*排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：
*意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
*意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
锁模式的兼容情况（如图）：
（如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放。）
== InnoDB加锁方法 ==
*意向锁是 InnoDB 自动加的， 不需用户干预。
*对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；
*对于普通 SELECT 语句，InnoDB 不会加任何锁；
事务可以通过以下语句显式给记录集加共享锁或排他锁： 1.共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。 2.排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁
=== 隐式锁定 ===
InnoDB在事务执行过程中，使用两阶段锁协议：
随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；
锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。
=== 显式锁定 ===
<syntaxhighlight lang="js">
select ... lock in share mode //共享锁
select ... for update //排他锁
</syntaxhighlight>
*select for update：
在执行这个 select 查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果。
select *** for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。
*select lock in share mode ：
in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。select *** lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。
=== 性能影响 ===
select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。 select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。
=== for update 和 lock in share mode 的区别 ===
前一个上的是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 for update ； 后一个是共享锁，多个事务可以同时的对相同数据执行 lock in share mode。
== InnoDB 行锁实现方式 ==
*InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
*不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
*只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，
别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。（更多阅读：MySQL索引总结）
*由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点。
== InnoDB的间隙锁 ==
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
=== InnoDB使用间隙锁的目的 ===
1.防止幻读，以满足相关隔离级别的要求； 2.满足恢复和复制的需要：
MySQL通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制（复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复）有以下特点： 1.MySQL的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。
2.MySQL的Binlog 是按照事务提交的先后顺序记录的， 恢复也是按这个顺序进行的。
由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。
== 死锁 ==
=== 死锁产生 ===
*死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
*当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
*锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
=== 检测死锁 ===
数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
=== 死锁恢复 ===
死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
=== 外部锁的死锁检测 ===
发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
=== 死锁影响性能 ===
死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。
== InnoDB避免死锁 ==
*为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
*在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
*如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
*通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
*改变事务隔离级别
== InnoDB死锁原因排查 ==
如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。
== 一些优化锁性能的建议 ==
*尽量使用较低的隔离级别；
*精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
*选择合理的事务大小，小事务发生锁冲突的几率也更小
*给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
*不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
*尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
*不要申请超过实际需要的锁级别
*除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
*对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能
== 乐观锁 ==
假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。 乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
== 悲观锁 ==
假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。 悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
== 事务 ==
=== 幻读（前后多次读取，数据总量不一致） ===
事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。
=== 脏读（读取未提交数据） ===
A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。就好像原本的数据比较干净、纯粹，此时由于B事务更改了它，这个数据变得不再纯粹。这个时候A事务立即读取了这个脏数据，但事务B良心发现，又用回滚把数据恢复成原来干净、纯粹的样子，而事务A却什么都不知道，最终结果就是事务A读取了此次的脏数据，称为脏读。
这种情况常发生于转账与取款操作中
=== 不可重复读（前后多次读取，数据内容不一致） ===
事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。
=== 事务隔离级别（如何解决幻读、脏读、不可重复读） ===
幻读、脏读、不可重复读都是数据库事务隔离级别的问题，SQL标准定义了事务隔离级别分为四种（级别递减）：
*Serializable （串行化）：最严格的级别，事务串行执行，资源消耗最大；简言之，它是在每个读的数据行上加上共享锁，但可能导致大量的超市现象和锁竞争。
*REPEATABLE READ（可重复读） ：保证了一个事务不会修改已经由另一个事务读取但未提交（回滚）的数据。避免了“脏读取”和“不可重复读取”的情况，但不能避免“幻读”，但是带来了更多的性能损失。
*READ COMMITTED （提交读）：大多数主流数据库的默认事务等级，保证了一个事务不会读到另一个并行事务已修改但未提交的数据，避免了“脏读取”，但不能避免“幻读”和“不可重复读取”。该级别适用于大多数系统。
*Read Uncommitted（未提交读） ：事务中的修改，即使没有提交，其他事务也可以看得到，会导致“脏读”、“幻读”和“不可重复读取”。
根据实际情况，采用不同的隔离级别，隔离级别的设置：
<syntaxhighlight lang="js">
# 修改配置文件
cd /etc/mysql/mysql.conf.d/
sudo vim mysqld.cnf
# 添加以下代码
transaction-isolation=READ-COMMITTED
</syntaxhighlight>
= 慢查询 =
慢查询分析：
<pre>pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' k8s-1-slow.log
</pre>
其中 pt-query-digest 在 172.24.140.3 有部署该软件
[[Category:服务端中间件]]
