MySQL面试宝典

==============================================
# 参数
==============================================
autocommit
事务操作中是否自动提交，如果为1则自动提交，为0则需要commit。

back_log
mysql服务在很短的时间内有很多的连接请求时该参数就会发挥作用。 如果短时间内有很大连接数可考虑增加该值。不过限制于系统层面的连接数。 5.6.6之前默认50, 5.6.6之后基于50+（max_connections/5)公式，上限900

binlog_format
binlog的记录格式。

connect_timeout
这个超时使用在MySQL服务器和客户端交换授权包时。默认10秒。

key_buffer_size
只用于MyISAM存储引擎表，缓存MyISAM存储。

sort_buffer_size
主要用于SQL语句在内存中的临时排序

join_buffer_size
表连接时使用。

log_bin
打开或关闭binlog的参数。需要指定server-id。

innodb_buffer_pool_size
InnoDB缓存表和索引数据的内存区域。

innodb_fast_shutdown
innodb关闭的几种模式。动态参数，默认值为1，可选值为0，1,2
为0时，全部脏叶刷盘并且change buffer合并。关闭缓慢。
为1时，跳过以上操作。关闭迅速。
为2时，innodb只会刷新他的log，当mysql崩溃的时候，提交的事务不会丢失。但是恢复的时间会很长。
一般来说，使用innodb_fast_shutdown在紧急情况下或者troubleshooting中。比如数据有在内存中有雪崩的危险。

sync_binlog
sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
当sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

innodb_flush_log_at_trx_commit
0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
2：每次事务提交时mysql都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

innodb_log_file_size
设置每个log file的大小，默认48M。过小日志轮转（rotate）受影响，影响checkpoint，过大恢复缓慢。设置多大是一门技术，优化是一个调优的过程。

innodb_thread_concurrency
innodb可限制并发线程数。默认值为0，表示不受限制。如果参数设置大于0，则表示检查机制开启，允许进入的线程数就是参数的值。如果设置过大会因为增加消耗系统层面的连接和资源导致性能退化。 一般情况下，值得设定应小于CPU的核数。 根据系统的负载，硬件环境调整参数。

innodb_flush_method
有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT
默认是fdatasync，只要MYSQL线程进行了刷新动作，那么他的这些文件的数据一定会同步到磁盘
为O_DSYNC时，不绕过pape cache, 每次write的数据不仅会在page cache里写入，还会写入存储设备，并且，O_SYNC保证每次write返回后，数据都已经写入page cache和存储设备。
为O_DIRECT时，该文件的读写操作将会绕过page cache，直接与存储设备打交道
建议设置为O_DIREC 避免双缓冲(double buffering)和降低swap的压力

max_connections
允许最大并发的连接数。如果因为max_connections而导致的连接错误，会导致状态参数Connection_errors_max_connections的增加。

max_user_connection
每个数据库用户的最大连接

max_error_count
show warnings语句最多能显示的error，warnings。

wait_timeout
MySQL客户端的数据库连接闲置最大时间值。

thread_concurrency
应设为CPU核数的2倍

innodb_additional_mem_pool_size
设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

innodb_log_buffer_size
InnoDB存储引擎的事务日志所使用的缓冲区。

==============================================
# MySQL 规范
==============================================
必须使用InnoDB存储引擎
必须使用UTF8字符集
数据表、数据字段必须加入中文注释
禁止使用存储过程、视图、触发器、Event（解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是让给应用服务器去做吧。）
禁止存储大文件或者大照片（解读：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URI多好）
只允许使用内网域名，而不是ip连接数据库
表必须有主键，例如自增主键
必须把字段定义为NOT NULL并且提供默认值（null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化、null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多、）
禁止使用TEXT、BLOB类型（会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能）
禁止在更新十分频繁、区分度不高的属性上建立索引
禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性
禁止在WHERE条件的属性上使用函数或者表达式（SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描，正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')）

==============================================
# MySQL新特性
==============================================
MySQL 5.5
1.InnoDB作为默认的数据库存储引擎。
2.用两个新的参数取代了 innodb_file_io_threads,那就是innodb_read_io_threads 和 innodb_write_io_threads,你就可以根据你的 CPU 核数来更改。
3.在 MySQL5.1.X 版本,由于代码写死,最多只会刷新 100 个脏页到磁盘,合并 20 个插入缓冲,即使磁盘有能力处理更多的请求,也只会处理这么多,这样在更新量较大(比如大批量 INSERT)的时候,脏页刷新可能会跟不上,导致性能下降。而在 MySQL5.5.X 版本里,innodb_io_capacity 参数可以动态调整刷新脏页的数量,在一定程度上解决了这一问题。
4.INNODB 同时支持多个 BufferPool 实例，通过 innodb_buffer_pool_instances 参数来增加InnoDB_Buffer_Pool 实例的个数。
5.增加了半同步复制，relay-log的自我修复，增加了 relay_log_recovery 参数,这个参数的作用是:当 slave 从库宕机后,假如 Relay-Log 损坏了,导致一部分中继日志没有处理,则自动放弃所有未执行的 relay-log,并且重新从 MASTER 上获取日志,这样保证 relay-log 的完整。默认情况下该功能是关闭的,将 relay_log_recovery 的值设置为 1 时,可在 slave 从库上开启该功能,建议开启。

MySQL 5.6
1.全文索引的支持。
2. undo log可独立出系统表空间，原来存在ibd系统表空间里。（innodb_undo_directory、innodb_undo_tablespaces）
3.支持多线程复制。slave_parallel_workers
4.支持延迟复制。CHANGE master TO MASTER_DELAY=600;
5.新增GTID复制。
6.ICP优化（Index Condition Pushdown）：index_condition_pushdown=on。ICP的优化用于range, ref, eq_ref, and ref_or_null访问方法
Index Condition Pushdown (ICP)是MySQL用索引去表里取数据的一种优化。禁用ICP(MySQL5.6之前)，引擎层会利用索引在基表中寻找数据行，然后返回给MySQL Server层，再去为这些数据行进行WHERE后的条件的过滤（回表）。启用ICP(MySQL5.6之后)，如果部分WHERE条件能使用索引中的字段，MySQL会把这部分下推到引擎层。存储引擎通过使用索引把满足的行从表中读取出。ICP减少了引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。总之是 ICP的优化在引擎层就能够过滤掉大量的数据，减少io次数，提高查询语句性能。
7.支持read-only事务。

MySQL 5.7
1.动态修改 Buffer Pool。
2.MySQL默认的部署策略发生了变化，变得更加安全。mysql_install_db 弃用了，改用 mysqld。
3.支持JSON数据类型
4.已经删除了test数据库，默认安装完后是没有test数据库。
5.可以为用户设置密码过期策略。
6.可以”锁”住用户，用以暂时禁用某个用户。
7.generated column，这一列由其他列计算而得。
8.sys schema。
9.在线开启GTID。
10.MySQL 5.7版本已经支持”真正”的并行复制功能。


MySQL 8.0
1.MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍。
2.提供 NoSQL 存储功能。
3.新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行当中。即窗口函数不需要 GROUP BY。
4.隐藏索引。
5.降序索引。
6.utf8mb4 作为 MySQL 的默认字符集。
7.取消默认MyISAM系统表。
8.取消Query Cache。

==============================================
# InnoDB
==============================================
InnoDB体系结构
内存：
innodb_buffer_pool由包含数据、索引、insert buffer ,adaptive hash index,lock 信息及数据字典。
redo log buffer用来缓存重做日志。
additional memory pool:用来缓存LRU链表、等待、锁等数据结构。

线程
master thread负责刷新缓存数据到磁盘并协调调度其它后台进程。
IO thread 分为 insert buffer、log、read、write进程。分别用来处理insert buffer、重做日志、读写请求的IO回调。
purge thread用来回收undo 页
page cleaner thread用来刷新脏页。

关键特性之 insert buffer：
专门为维护非唯一辅助索引的更新设计的。因为innodb的记录是按主键的顺序存放的，所以主键的插入是顺序的，而聚集索引对应的辅助索引的更新则是离散的，为了避免大量离散读写，先检查要更新的索引页是否已经缓存在了内存中，如果没有，先将辅助索引的更新都放入缓冲（inset buffer区），等待合适机会（master thread的定时操作，索引块需要被读取时，insert buffer bitmap检测到对应的索引页不够用时）进行insert buffer和索引页的合并。因为辅助索引缓存到insert buffer中时并不会读取磁盘上的索引页，以至于无法校验索引的唯一性，所以不适用唯一辅助索引。innodb中所有的非唯一辅助索引的insert buffer均由同一棵二叉树维护。二叉树的非叶子节点由space（表空间id）+marker（兼容老版本的insert buffer）+offset（在表空间中的位置）构成，叶子节点由space+marker+offset+metadata(进入顺序+类型+标志)+辅助索引构成，进行merge合并时，按顺序进行回放。mysql5.1之后，insert buffer支持change_buffer，还可以缓冲非唯一辅助索引的update\delete操作。insert buffer的二叉树结构是存放在共享表空间中的，所以通过独立表空间恢复表时，执行check table操作会失败，因为辅助索引的数据可能还在insert buffer中，需要通过repair table 重建表上全部的辅助索引。为了保证每次 merge insert buffer成功，表空间中每隔256个连续区就有一个insert buffer bitmap页用来记录索引页的可用空间。insert buffer bitmap页总是处于这个连续区间的第二页，每个索引页在insert buffer bitmap中占4 bit。可以通过show engine innodb stauts\G;查看insert buffer and adaptive hash index 查看insert buffer的合并数量、空闲页数量、本身的大小、合并次数及索引操作次数。通过索引操作次数与合并次数的的比例可以判断出insert buffer所带来的性能提升。

关键特性之 double write：
因为脏页刷新到磁盘的写入单元小于单个页的大小，如果在写入过程中数据库突然宕机，可能会使数据页的写入不完成，造成数据页的损坏。而redo log中记录的是对页的物理操作，如果数据页损坏了，通过redo log也无法进行恢复。所以为了保证数据页的写入安全，引入了double write。double write的实现分两个部分，一个是缓冲池中2M的内存块大小，一个是共享表空间中连续的128个页，大小是2M。脏页从flush list刷新时，并不是直接刷新到磁盘而是先调用函数（memcpy），将脏页拷贝到double write buffer中，然后再分两次，每次1M将double write buffer 刷新到磁盘double write 区，之后再调用fsync操作，同步到磁盘。如果应用在业务高峰期，innodb_dblwr_pages_written:innodb_dblwr_writes远小于64:1，则说明，系统写入压力不大。虽然，double write buffer刷新到磁盘的时候是顺序写，但还是是有性能损耗的。如果系统本身支持页的安全性保障（部分写失效防范机制），如ZFS，那么就可以禁用掉该特性（skip_innodb_doublewrite）。

关键特性之 adaptive hash index：
innodb会对表上的索引页的查询进行监控，如果发现建立hash索引能够带来性能提升，就自动创建hash索引。hash索引的创建是有条件的,首先是必定能够带来性能提升。其次数据库以特定模式的连续访问超过了100次，通过该模式被访问的页的访问次数超过了1/16的记录行数。自适应hash根据B+树中的索引构造而来，只需为这个表的热点页构造hash索引而不是为整张表都构建。同样可以通过show engine innodb status\G中的 insert buffer and adaptive hash index(hash searches/s non-hash searches)查看hash index的使用情况。

MyISAM 和 InnoDB 的区别
1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3. InnoDB支持行级锁，而MyISAM只支持表级锁；
4. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
5. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
6. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；
7. InnoDB 把数据和索引存放在一个数据文件里面，而MyISAM 表被存放在三个文件 frm：存放表格定义。 MYD：数据文件。MYI：索引文件。

为什么InnoDB表最好要有自增列做主键？
1.InnoDB引擎表是基于B+树的索引组织表
2.使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；

关于query cache
Query Cache（查询缓存，以下简称QC）存储SELECT语句及其产生的数据结果，特别适用于：频繁提交同一个语句，并且该表数据变化不是很频繁的场景，例如一些静态页面，或者页面中的某块不经常发生变化的信息。QC有可能会从InnoDB Buffer Pool或者MyISAM key buffer里读取结果。
由于QC需要缓存最新数据结果，因此表数据发生任何变化（INSERT、UPDATE、DELETE或其他可能产生数据变化的操作），都会导致QC被刷新。
QC严格要求2次SQL请求要完全一样，包括SQL语句，连接的数据库、协议版本、字符集等因素都会影响。
关闭QC: query_cache_size = 0， query_cache_type = 0
==============================================
# 复制
==============================================
复制原理：
1. 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
2. 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；
3. 从：sql执行线程——执行relay log中的语句；

MySQL复制格式小结
基于语句级的复制
binlog=statement

优点：
（1）binlog文件较小。
（2）日志是包含用户执行的原始SQL，方便统计和审计。
（3）出现最早可binlog、兼容较好。
（4）binlog方便阅读，方便故障修复。

缺点：
存在安全隐患，可能导致主从不一致。
对一些系统函数不能准确复制或是不能复制：load file()、uuid()、user()、found rows()、sysdate()。

基于行级的复制
binlog=row
优点：
（1）相比statement更加安全的复制格式。
（2）在某些情况下复制速度更快（SQL复杂、表有主键）。
（3）系统的特殊函数也可以复制。
（4）更少的锁。

缺点：
（1）binary log比较大（MySQL 5.6 支持binlog row image）。
（2）单语句更新/删除的表执行过多，会形成大量binlog。
（3）无法从binlog看见用户执行的SQL（MySQL5.6增加一个新的Event binlog_rw_query_log_event记录用户的query）。

基于混合级复制
1.混个使用ROW和statement格式，对于DDL记录会stement，对于TABLE里的行操作记录为ROW格式。
2.如果使用innodb表，事务级别使用了read commeitted or read uncommitted 日志级别只能使用row格式。
3.单在使用row格式中DDL语句还是会记录成statement格式。
4.mixed格式，那么在以下几种情况会自动将binlog的模式由SBR模式改成RBR。
5.当DML语句更新一个NDB表时。
6.当函数中包含UUID()时。
7.2个及以上包含AUTO_INCREMENT字段的表被更新时。
8.行任何INSERT DELAYED语句时。
9.用UDF时。
10.视图中必须要求使用RBR时，例如创建视图是使用了UUID()函数。

异步复制
异步复制，主库将事务 Binlog 事件写入到 Binlog 文件中，此时主库只会通知一下 Dump 线程发送这些新的 Binlog，然后主库就会继续处理提交操作，而此时不会保证这些 Binlog 传到任何一个从库节点上。

全同步复制
全同步复制，当主库提交事务之后，所有的从库节点必须收到、APPLY并且提交这些事务，然后主库线程才能继续做后续操作。但缺点是，主库完成一个事务的时间会被拉长，性能降低。

半同步复制
半同步复制，是介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全完成并且提交的反馈，如此，节省了很多时间

主从复制延时判断
当遇到从库延迟时，要怎么做？
首先输入 show slave status \G;看一下。

在生产环境中，主从复制常常会有复制延迟的现象，主要是master是并发的写，而slave是单线程的应用relay log，所以会出现复制延时，在MySQL 5.6版本中有了基于库的多线程复制。还有MariaDB的并行复制。但是我们使用MySQL 5.5的版本也比较多。如何判断复制是否延时呢？工具现在可以使用的有pt-heartbeat，但是如果我们自己明白怎么样判断复制是否延时的话，自己写简单的shell脚本或者python脚本也可以完成。

复制是否延时的判断标准如下：
Master_Log_File: restoredb-bin.000013
Read_Master_Log_Pos: 2421074
Relay_Log_File: restoredb2-relay-bin.000010
Relay_Log_Pos: 2419771
Relay_Master_Log_File: restoredb-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2421074
Relay_Log_Space: 2420428
不要通过Seconds_Behind_Master去判断，该值表示slave上SQL线程和IO线程之间的延迟
判断方法如下：
1、首先看 Relay_Master_Log_File 和 Master_Log_File 是否有差异
2、如果Relay_Master_Log_File 和 Master_Log_File 有差异的话，那说明延迟很大
3、如果Relay_Master_Log_File 和 Master_Log_File 没有差异，再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，那么更加严谨的做法是同时在主库执行show master status和在从库上面执行show slave status 的输出进行比较。MHA就是这样保证数据一致性的。MMM都没有做到。这也算MHA比MMM更加优秀的地方。


GTID的限制
1.不支持非事务引擎（从库报错，stop slave;start slave;忽略）。
2.不支持create table ... select 语句复制（主库直接报错）。
3.不允许一个SQL同时更新一个事务引擎和非事务引擎的表。
4.在一个复制组中，必须要求统一开启GTID或者是关闭GTID。
5.开启GTID需要重启。
6.开启GTID后，就不再使用原来的传统的复制方式。
7.对于create temporary table和drop temporary语句不支持。
8.不支持sql_slave_skip_counter。

==============================================
# MHA
==============================================
在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性
该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。
要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器。
为了尽可能的减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL 5.5的半同步复制。关于半同步复制原理各位自己进行查阅。（不是必须）。

MHA原理
1.从宕机崩溃的master保存二进制日志事件（binlog events）;
2.识别含有最新更新的slave；
3.应用差异的中继日志（relay log）到其他的slave；
4.应用从master保存的二进制日志事件（binlog events）；
5.提升一个slave为新的master；
6.使其他的slave连接新的master进行复制；

==============================================
# PXC
==============================================
特性如下：
1.同步复制，事务要么在所有节点提交或不提交。
2.多主复制，可以在任意节点进行写操作。
3.在从服务器上并行应用事件，真正意义上的并行复制。
4.节点自动配置。
5.数据一致性，不再是异步复制。

优点如下：
1.当执行一个查询时，在本地节点上执行。因为所有数据都在本地，无需远程访问。
2.无需集中管理。可以在任何时间点失去任何节点，但是集群将照常工作，不受影响。
3.良好的读负载扩展，任意节点都可以查询。

缺点如下：
1.加入新节点，开销大。需要复制完整的数据。
2.不能有效的解决写缩放问题，所有的写操作都将发生在所有节点上。
3.有多少个节点就有多少重复的数据。

局限性

1、存储引擎：
基于PXC的复制仅适用于InnoDB存储引擎。
对其他存储引擎的表，包括mysql.*表之类的系统表，任何写入都不会被复制。
那创建用户那岂不是无法同步了？关于这个问题。对于基于DDL方式的语句还是被支持的。
DDL语句使用基于语句级别的方式来实现(即不使用row模式)。
对mysql.*表的所有已DDL方式的更改都将以语句级别式进行复制。
如：CREATE USER… DDL被复制(语句级)
INSERT INTO mysql.user… myisam存储引擎，不会被复制，因为非DDL语句
当然也可以配置wsrep_replicate_myisam参数实现(不建议使用)

2、不支持的查询：
LOCK TABLES在多主模式中不支持UNLOCK TABLES以及LOCK TABLES
锁定功能，如GET_LOCK()，RELEASE_LOCK()等也不被支持

3、查询日志不能定向到表：
如果启用查询日志记录，则必须将日志转发到文件
使用general_log和general_log_file选择查询日志记录和日志文件名称
log_output = file # Author : Leshami # Blog : https://blog.csdn.net/leshami

4、最大事务大小：
允许的最大事务大小由wsrep_max_ws_rows和wsrep_max_ws_size变量定义
LOAD DATA INFILE方式处理每10000行提交一次。对于大的事务将被分解众多小型事务

5、集群乐观并发控制：
PXC集群使用乐观并发控制，事务发出COMMIT可能仍会在该阶段中止
可以有两个事务写入相同的行并在单独的Percona XtraDB集群节点中提交，并且只有其中一个可以成功提交。
失败的将被中止。对于集群级中止，Percona XtraDB集群返回死锁错误代码：
(Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)).

6、由于可能的提交回滚，XA事务不受支持：

7、硬件配置短板限制：
整个群集的写吞吐量受最弱节点的限制。如果一个节点变慢，整个集群变慢。
如果您对稳定的高性能有要求，那么它应该由相应的硬件支持。

8、建议的最小群集大小是3个节点。第三个节点可以是仲裁者。

9、InnoDB虚假更改功能不受支持。

10、enforce_storage_engine=InnoDB与wsrep_replicate_myisam=OFF（默认）不兼容 。

11、binlog_rows_query_log_events变量不受支持。

12、高负载时避免ALTER TABLE … IMPORT / EXPORT

13、如果DDL语句有问题将破坏集群。建议使用pt-online-schema-change操作DDL。
 

==============================================
# MGR（MySQL Group Replication）
==============================================
基于传统异步复制和半同步复制的缺陷——数据的一致性问题无法保证，MySQL官方在5.7.17版本正式推出组复制（MySQL Group Replication，简称MGR）。
由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N / 2 + 1）决议并通过，才能得以提交。
由3个节点组成一个复制组，Consensus层为一致性协议层，在事务提交过程中，发生组间通讯，由2个节点决议(certify)通过这个事务，事务才能够最终得以提交并响应。
引入组复制，主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。组复制依靠分布式一致性协议(Paxos协议的变体)，实现了分布式下数据的最终一致性

MySQL官方推出的高可用解决方案，基于原生复制技术，并以插件的方式提供。
复制的管理操作变得更为自动化
通过Paxos协议提供数据库集群节点数据强一致保证
集群间所有节点可写入
解决网络分区导致的脑裂问题，提升复制数据的可靠性。
MGR是Share Nothing，Oracle RAC是Share Everything。
高容错性，只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理，并且内置了自动化脑裂防护机制

MGR优势：
1.不会出现脑裂现象；
2.冗余能力好，能够保证binlog event至少被复制到超过一半的成员上，主要同时宕机的成员不超过半数便不会导致数据丢失；
3.宕机的服务器重启后，不再需要特殊的处理就可以重新加入组；
4.多节点写入支持：多写模式下支持集群中的所有节点都可以写入

MGR的限制：
存储引擎必须为innodb
每个表必须提供主键
只支持ipv4，网络需求较高
不支持Savepoints
必须打开GTID特性，二进制日志格式必须设置为ROW
二进制日志不支持binlog event checksum

MGR搭建步骤：
1.安装搭建MySQL数据。
2.在原有参数文件的基础上，确认加入以下参数：
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
binlog-checksum = NONE
transaction_write_set_extraction =XXHASH64 ###开启主键信息采集功能
loose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" ###设置组名
loose-group_replication_start_on_boot =off
loose-group_replication_local_address ="192.168.56.101:33061" ###设置成员的本地地址
loose-group_replication_group_seeds ="192.168.56.101:33061,192.168.56.102:33061,192.168.56.103:33061" ###设置种子成员的地址
loose-group_replication_bootstrap_group =off
loose-group_replication_single_primary_mode=FALSE ###搭建多主模式
loose-group_replication_enforce_update_everywhere_checks= TRUE ###避免未检测到的外键冲突
3.创建授权用户。
4.安装插件，启动复制组
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='abc_Test123' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
set global group_replication_single_primary_mode=off;
START GROUP_REPLICATION;
5.db2、db3上启动group_replication：
Db2上mysql命令行上执行启动：
set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
select * from performance_schema.replication_group_members;
Db3上启动group_replication：
set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
6.查看现有组成员
mysql> SELECT * FROM performance_schema.`replication_group_members`;

日常维护步骤：
1、如果从库某一节点关闭
stop group_replication;
2、如果所有的库都关闭后，第一个库作为主库首先执行
set global group_replication_bootstrap_group=ON;
start group_replication;
剩下的库直接执行即可！
start group_replication;
3、如果主库故障，会自动从两个从库选出一个主库，主库启动后再次执行如下命令后会变成从库
start group_replication;


==============================================
# 执行计划
==============================================

select_type
示查询中每个select子句的类型（简单OR复杂）
a. SIMPLE：查询中不包含子查询或者UNION
b. 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
c. 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
d. 在FROM列表中包含的子查询被标记为：DERIVED（衍生）用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的
e. 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
f. 从UNION表获取结果的SELECT被标记为：UNION RESULT

SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。
DEPENDENT意味着select依赖于外层查询中发现的数据。
UNCACHEABLE意味着select中的某些 特性阻止结果被缓存于一个item_cache中。


type
表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下:
ALL 执行full table scan，这事最差的一种方式
index 执行full index scan，并且可以通过索引完成结果扫描并且直接从索引中取的想要的结果数据，也就是可以避免回表，比ALL略好，因为索引文件通常比全部数据要来的小
range 利用索引进行范围查询，比index略好
index_subquery 子查询中可以用到索引
unique_subquery 子查询中可以用到唯一索引，效率比 index_subquery 更高些
index_merge 可以利用index merge特性用到多个索引，提高查询效率
ref_or_null 表连接类型是ref，但进行扫描的索引列中可能包含NULL值
fulltext 全文检索
ref 基于索引的等值查询，或者表间等值连接
eq_ref 表连接时基于主键或非NULL的唯一索引完成扫描，比ref略好
const 基于主键或唯一索引唯一值查询，最多返回一条结果，比eq_ref略好
system 查询对象表只有一行数据，这是最好的情况

possible_keys
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

key
显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

ref
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows
表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra
包含不适合在其他列中显示但十分重要的额外信息
Using filesort 将用外部排序而不是按照索引顺序排列结果，数据较少时从内存排序，否则需要在磁盘完成排序，代价非常高，需要添加合适的索引
Using temporary 需要创建一个临时表来存储结果，这通常发生在对没有索引的列进行GROUP BY时，或者ORDER BY里的列不都在索引里，需要添加合适的索引
Using index 表示MySQL使用覆盖索引避免全表扫描，不需要再到表中进行二次查找数据，这是比较好的结果之一。注意不要和type中的index类型混淆
Using where 通常是进行了全表/全索引扫描后再用WHERE子句完成结果过滤，需要添加合适的索引
Impossible WHERE 对Where子句判断的结果总是false而不能选择任何数据，例如where 1=0，无需过多关注
Select tables optimized away 使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询，例如MIN()\MAX()，这种也是比较好的结果之一

==============================================
# 事务、锁
==============================================
脏读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务更新后达到了满足其查询条件的旧数据（此时它还未被提交），这种现象就称为“脏读”。
不可重复读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务更新后达到了满足其查询条件的旧数据（此时它已被提交），这种现象就称为“不可重复读”。
幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据（此时它已被提交），这种现象就被称为“幻读”。

事务隔离级别
Read Uncommitted（读未提交）隔离级别最低
允许脏读，允许事务查看其它事务所进行的未提交更改，可能读取到其他会话中未提交事务修改的数据。

Read Commited（读已提交）
允许幻读，允许事务查看其它事务所进行的已提交更改，只能读取到已经提交的数据。

Repeatable Read（可重复读）
消除了脏读、不可重复读、幻读，保证事务一致性。
在同一个事务内的查询都是事务开始时刻一致的，确保每个事务的读取结果都是一样的，默认隔离级别。

Serializable（串行）隔离级别最高
串行化读，每次读都需要获得表级共享锁，读写间相互都会阻塞。


通常的锁范围：
1.全局锁（global lock)。
2.表锁（table lock）。
3.行锁（row lock）。

InnoDB行锁范围（粒度）：
1.record lock. 单个记录上的锁
2.gap lock. 间隙锁，锁定一个范围，但不包含记录本身
3.next-key lock = record lock + gap lock. 锁定一个范围，并且锁定记录本身

Record Lock 总是会去锁住索引记录，如果innodb存储引擎表在建立的时候没有设置任何一个索引，而且查询的时候没有使用到索引，那么这时就会导致表锁。
Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，innodb对于行的查询都是采用这种锁定算法。例如一个索引有10,11,13,20这4个值，那么该索引可能被Next-Key Locking的范围为：
(- &，10]
(10,11]
(13,20]
(20,+ &)

采用Next-Key Lock的锁定技术称为Next-Key Locking。这种设计的目的是为了解决幻读（Phantom Problem）。利用这种锁定技术，锁定的不是单个值，而是一个范围。

注：当查询的索引含有唯一属性时，innodb存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即锁住索引记录本身，而不再是范围。

注意：
对于唯一索引，其加上的是Record Lock，仅锁住记录本身。但也有特别情况，那就是唯一索引由多个列组成，而查询仅是查找多个唯一索引列中的其中一个，那么加锁的情况依然是Next-key lock。

对于辅助索引，其加上的是Next-Key Lock，锁定的是范围，包含记录本身。
另外如果使用相等的条件给一个不存在的记录加锁，innodb也会使用Next-key lock

特别注意：
innodb存储引擎是通过给索引上的索引项加锁来实现，这意味着：只有通过索引条件检索数据，innodb才会使用行锁，否则，innodb将使用表锁。当然这种说法是在表没有主键或者没有任何索引的情况下。如果一个表有主键，没有其他的索引，检索条件又不是主键，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

RC级别下，只有record lock，没有next key lock。
RR级别下，除非primary key或unique key是record lock，其余都是next key lock。

==============================================
# pt工具
==============================================
pt-heartbeat 监控mysql复制延迟
pt-online-schema-change 在线修改表结构
pt-table-checksum 校验主从复制一致性
pt-table-sync 同步表数据

pt-online-schema-change工具的使用限制：
1）如果修改表有外键，除非使用 --alter-foreign-keys-method 指定特定的值，否则工具不予执行
2）被修改表必须要有主键，否则报错：Cannot chunk the original table `houyi`.`ga`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5353.
3）被修改表上不能有针对after delete|insert|update三个触发器，否则修改表结构操作失败

online ddl的原理如下：
1、创建一个和原来表结构一样的临时表并ddl
2、锁住原表，所有数据都无法写入（insert,update,delete）
3、将原表数据写入到临时表中（通过insert ...select方式）
4、写入完后，重命名临时表和原表名称
5、删除原表，释放锁

pt-online-schema-change在线ddl原理如下：
1、创建一个和原来表结构一样的临时表并ddl
2、将原表数据写入到临时表中（通过insert ...select方式）,并且在原表上创建触发器，如果原表有数据写入，通过触发器方式将新增的数据写入临时表中(前提该表之前没有触发器)
3、写入完后，重命名临时表和原表名称
4、删除原表

==============================================
# 备份恢复
==============================================
mysqldump实现原理
1.修改session级别的sql_mode为空，避免可能有些些sql_mode值会对备份产生影响
2.强制刷新表缓存到磁盘，并关闭表。如果另一个会话已经加上表锁，那么该语句会被阻塞
3.调用 flush tables with read lock 对整个实例加全局读锁
4.开启快照读，在session级别将隔离级别设置成RR，因为开启一个一致性快照事务，需要在RR隔离级别，获取此时的快照(仅对innodb表起作用)
5.备份非innodb表数据(*.frm,*.myi,*.myd等)
6.非innodb表备份完毕后，释放FTWRL锁
7.逐一备份innodb表数据
8.表备份结束，回滚到保存点sp，以释放select *语句产生的MDL锁，如果不回滚，后续整个备份过程中无法对该表执行DDL操作
9.备份完成后，释放该保存点

Xtrabackup实现原理
1.innobackupex 在启动后，会先 fork 一个进程，启动 xtrabackup进程，然后就等待 xtrabackup 备份完 ibd 数据文件；
2.xtrabackup 在备份 InnoDB 相关数据时，是有2种线程的，1种是 redo 拷贝线程，负责拷贝 redo 文件，1种是 ibd 拷贝线程，负责拷贝 ibd 文件；redo 拷贝线程只有一个，在 ibd 拷贝线程之前启动，在 ibd 线程结束后结束。xtrabackup 进程开始执行后，先启动 redo 拷贝线程，从最新的 checkpoint 点开始顺序拷贝 redo 日志；然后再启动 ibd 数据拷贝线程，在 xtrabackup 拷贝 ibd 过程中，innobackupex 进程一直处于等待状态（等待文件被创建）。
3.xtrabackup 拷贝完成idb后，通知 innobackupex（通过创建文件），同时自己进入等待（redo 线程仍然继续拷贝）;
4.innobackupex 收到 xtrabackup 通知后，执行FLUSH TABLES WITH READ LOCK (FTWRL)，取得一致性位点，然后开始备份非 InnoDB 文件（包括 frm、MYD、MYI、CSV、opt、par等）。拷贝非 InnoDB 文件过程中，因为数据库处于全局只读状态，如果在业务的主库备份的话，要特别小心，非 InnoDB 表（主要是MyISAM）比较多的话整库只读时间就会比较长，这个影响一定要评估到。
5.当 innobackupex 拷贝完所有非 InnoDB 表文件后，通知 xtrabackup（通过删文件） ，同时自己进入等待（等待另一个文件被创建）；
6.xtrabackup 收到 innobackupex 备份完非 InnoDB 通知后，就停止 redo 拷贝线程，然后通知 innobackupexredo log 拷贝完成（通过创建文件）；
7.innobackupex 收到 redo 备份完成通知后，就开始解锁，执行 UNLOCK TABLES；
8.最后 innobackupex 和 xtrabackup 进程各自完成收尾工作，如资源的释放、写备份元数据信息等，innobackupex 等待 xtrabackup 子进程结束后退出。

==============================================
# 常见问题
==============================================
某人曰，在数据检索的条件中使用!=操作符时，存储引擎会放弃使用索引。
理由：因为检索的范围不能确定,所以使用索引效率不高，会被引擎自动改为全表扫描。你认可他的说法吗？

答：通常情况下，这个说法是正确的。当然，也有特殊情况，话不能说绝对了。
有一个测试表共80万条数据，其中type列只有1、2两个值，分别占比97%和3%。
这种情况下，查询条件 WHERE type != 1，是有可能也可以走索引的。
下面是两个SQL的执行计划：
mysql> desc select * from t1 where type = 1\G
**************** 1. row ****************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: type
key: type
key_len: 4
ref: const
rows: 399731
filtered: 100.00
Extra: NULL

mysql> desc select * from t1 where type != 1\G
**************** 1. row ****************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: type
key: type
key_len: 4
ref: const
rows: 10182
filtered: 100.00
Extra: NULL

type数据分布
mysql> select type, count(*) as cnt from t1 group by type order by cnt;
+------+--------+
| type | cnt |
+------+--------+
| 2 | 38304 |
| 1 | 761690 |
+------+--------+

MySQL在高并发时有什么过载保护的方法？
1、在前端Nginx层/中间件中限流，控制对MySQL的请求数。
2、控制MySQL的参数max_connections/max_user_connections，防止过高并发。
3、利用pt-kill等工具，及时杀掉引发性能问题的SQL。
4、根据服务器的压力情况，适当调整MySQL的innodb_thread_concurrency参数。
5、快速构建从库，缓解主库读压力。
6、适当调整max_execution_time=N。当SQL语句超过N秒，不予执行。
7、尽快优化慢SQL。

在大表执行ddl的过程中，若临时中断，会发生什么状况，需要特别处理吗 ？
前提说明：MySQL5.7.23、innodb表、“双1”
1、添加/删除列，采用copy的方式
1.1、ctrl+c。在当前session中，手动执行ctrl+c。无影响，并且会自动删除产生的临时文件。
1.2、kill -9。在执行ddl的时候，服务器发生意外宕机或者手动执行kill -9。待MySQL启动后，则会自动执行InnoDB Recovered流程。并且不会删除产生的临时文件，需要手工处理。
2、添加/删除索引，采用INPLACE方式
2.1、ctrl+c。同1.1
2.2、kill -9。不会删除临时文件，也不会执行InnoDB Recovered流程并且报错 Operating system error number 2 in a file operation ....OS error: 71
在开始执行alter table的过程中，在没有结束的时候，并不会写入到binglog文件中。

如何优化一条慢SQL
1.表设计、数据类型是否合理。
2.SQL写法是否正确。
3.表的统计信息是否正确。
4.看执行计划，判断上面有没有合适的索引。

MySQL数据库cpu飙升到500%的话该怎么处理？
当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，
看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。也有可能是每个 sql 消耗资源并不多，但是突然之间，
有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

转载于:https://www.cnblogs.com/liang545621/p/9622178.html