MySQL

索引失效

NULL

• 索引是有序的，NULL 是不确定值，无法确定其在索引中位置，使用 IS NULL / IS NOT NULL 可能会使索引失效
  • 如果字段不允许为空，则 IS NULL / IS NOT NULL 会使索引失效
  • 如果字段允许为空，则 IS NULL 走 ref 类型的索引，而 IS NOT NULL 走 range 类型的索引
• 如果需要把 NULL 存入索引，方法有二，其一，把 NULL 值转为一个特定的值，在 WHERE 中检索时，用该特定值查找；其二，建立一个复合索引。例如　ALTER TABLE FOO CREATE INDEX IDX_BAR(COL_1, 1)，通过在复合索引中指定一个非空常量值，而使构成索引的列的组合中，不可能出现全空值。

前导模糊查询

• LIKE 查询以 % 开头会使索引失效

违反最左匹配原则

• 使用联合索引时，缺失左边的索引列会导致索引失效

索引列上作操作

• 在索引列上作任何操作，如计算、函数、类型转换等，会导致索引失效从而全表扫描

范围查询

• 索引中有一个索引列用的是大于、小于等范围值，会导致该列之后的索引列失效

OR

• OR 可能会使索引失效，可在某些场景下使用 UNION 代替

NOT IN

• NOT IN 可能会使索引失效，5.7 中是全表扫描，5.8 中使用了 range 类型索引

!= / <>

• != / <> 可能会使索引失效，5.7 中是全表扫描，5.8 中使用了 range 类型索引

类型/编码不一致

• 隐式的类型转换会导致索引失效，进行全表扫描

锁

MySQL 锁可以按使用方式分为：乐观锁与悲观锁，按粒度分可以分为全局锁、表级锁、行级锁、页级锁。

全局锁

• 全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 FLUSH TABLES WITH READ LOCK。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句
• 全局锁的典型使用场景是做全库逻辑备份，也就是把整库每个表都 SELECT 出来存成文本。但是让整个库都只读，可能出现以下问题：
  • 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
  • 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

表级锁

• MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）。
  • 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
• MySQL 表级锁有两种模式：表共享锁（Table Read Lock）和表独占写锁（Table Write Lock）。
  • 对 MyISAM 的读操作，不会阻塞其他用户对同一表请求，但会阻塞对同一表的写请求；
  • 对 MyISAM 的写操作，则会阻塞其他用户对同一表的读和写操作；
  • MyISAM 表的读操作和写操作之间，以及写操作之间是串行的。
• 当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

行级锁

• 行级锁是 MySQL 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为 共享锁 和 排他锁。
  • 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
  • 基于索引，如果 sql 没有走索引，将会退化为表锁

页级锁

• 页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB 支持页级锁。
  • 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

显式加锁

-- 共享锁
SELECT ... LOCK IN SHARE MODE
-- 排他锁
SELECT ... FOR UPDATE

悲观锁

• 悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。
  • 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

乐观锁

• 乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。
  • 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

MyISAM vs. InnoDB

• 事务：MyISAM 不支持；InnoDB 支持
• 锁：MyISAM 支持表锁；InnoDB 支持行锁、表锁
• 主键：MyISAM 可以没有主键；InnoDB 必须有，没有指定会默认生成一个隐藏列作为主键
• 索引：MyISAM 为非聚集索引，使用 B+ 树作为索引结构，索引和数据文件是分离的，主键索引和辅助索引是独立的；InnoDB 为聚集索引，使用 B+ 树作为索引结构，数据文件和索引绑在一起，必须要有主键，主键索引一次查询，辅助索引两次查询，先查询主键，再查询数据
• 外键：MyISAM 不支持；InnoDB 支持
• AUTO_INCREMENT：MyISAM 自增列如果处于组合索引，自增列可以不是第一列，可以根据组合索引左边的列进行排序后递增；InnoDB 必须包含只有自增列的索引，如果自增列处于组合索引，也必须是组合索引的最左列
• 表的行数：MyISAM 保存了表的行数；InnoDB 没有保存表的行数
• 全文索引：MyISAM 支持，InnoDB 5.7+(含)支持

MySQL 日志

日志是 MySQL 数据库的重要组成部分。日志文件中记录着 MySQL 数据库运行期间发生的变化，比如客户端连接状况、SQL 语句的执行情况和错误信息等。当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。MySQL 日志主要包含：错误日志、查询日志、慢查询日志、二进制日志、重做日志、回滚日志等，其中重做日志和回滚日志也称事务日志(InnoDB)。

错误日志(errorlog)

• 错误日志功能是默认开启的，并且错误日志无法被禁止。错误日志记录着 MySQL 启动和停止，以及服务器在运行过程中发生的错误的相关信息。
  • 未显式指定开启时，错误日志将被写入标准错误输出 stderr。
  • 通过配置文件中 log-error 选项显式开启，并可通过该选项指定日志存储文件。
  • 如未指定日志存储文件，错误日志将被写入数据目录下 host_name.err 文件中，host_name 为 MySQL 服务器主机名。

查询日志(general log)

• 查询日志功能是默认关闭的。查询日志会记录用户的所有操作。
  • 查询日志在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响数据库性能的，建议仅在为了调试数据库的目的下开启查询日志。
  • 通过配置文件中 general_log = ON 选项开启，通过配置文件中 log_output = TABLE|FILE 指定日志存储方式。
  • 如果 log_output = TABLE，日志存储在 MySQL.gengera_log 表中。
  • 如果 log_output = FILE，通过配置文件中 general_log_file 指定日志存储文件。

慢查询日志(slow query log)

• 慢查询日志功能是默认关闭的。慢查询日志用来记录执行时间超过指定时间的查询语句。
  • 通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。
  • 一般建议开启，它对服务器性能的影响微乎其微，但是可以记录 MySQL 服务器上执行了很长时间的查询语句，可以帮助定位性能问题。
  • 通过配置文件中 slow_query_log 选项开启慢查询日志。
  • 通过配置文件中 slow_query_log_file 选项指定慢查询日志存储文件。
  • 通过配置文件中 long_query_time 选项指定判定慢查询的时间阈值，时间以秒为单位，可以精确到微秒。

二进制日志

• 二进制日志也叫作变更日志，主要用于记录修改数据或有可能引起数据改变的 SQL 语句，并且记录了语句发生时间、执行时长、操作的数据等。
  • 通过配置文件中 log_bin = ON|file_name 选项开启或同时指定二进制日志存储目录/文件。
  • 内容是逻辑格式日志，可用于在主从复制中从库利用主库上的 binlog 进行重播实现主从同步，也可用于数据库基于时间点的还原。
  • 事务提交时产生记录，生成时间超过 expire_logs_days 配置的天数之后，会被自动删除。

重做日志(redo log)

• 确保事务的持久性。redo 日志记录事务执行后的状态，用来恢复未写入 data file 的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 MySQL 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性。
  • 内容是物理格式的日志，记录的是物理数据页面的修改的信息，其 redo log 是顺序写入 redo log file 的物理文件中去的。
  • 事务开始后就会产生 redo log，redo log 的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 redo log 文件中。
  • 当对应事务的脏页写入到磁盘之后，redo log 的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。
  • 通过配置文件中 innodb_log_group_home_dir 选项指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。
  • 通过配置文件中 innodb_log_files_in_group 选项指定日志文件组中文件的数量，默认 2。
  • 通过配置文件中 innodb_log_file_size 指定日志文件的大小。
  • 通过配置文件中 innodb_mirrored_log_groups 指定日志镜像文件组的数量，默认 1。
  • 重做日志有一个缓存区 innodb_log_buffer，innodb_log_buffer 的默认大小为8M，Innodb 存储引擎先将重做日志写入 innodb_log_buffer 中。
    • Master Thread 每秒一次执行刷新 innodb_log_buffer 到日志文件。
    • 每个事务提交时会将重做日志刷新到重做日志文件。
    • 当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件。

回滚日志(undo log)

• 事务原子性和隔离性实现的基础，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。
  • 内容是逻辑格式日志，在执行 undo 时仅将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的（不同于 redo log）。
  • 事务开始之前，将当前数据版本生成 undo log，undo 也会产生 redo 来保证 undo log 的可靠性。
  • 当事务提交之后，undo log 并不能立马被删除，而是放入待清理的链表，由 purge 线程判断是否有其他事务在使用 undo 段中表的上一个事务之前的版本信息，决定是否可以清理 undo log 的日志空间。
  • 通过配置文件中 innodb_data_file_path 指定共享表空间路径。（5.6-）
  • 通过配置文件中 innodb_undo_directory 指定独立 undo 表空间存储目录，通过配置文件中 innodb_undo_logs 指定回滚段大小，通过配置文件中 innodb_undo_tablespaces 指定 undo log 文件数量。（5.7+）
• undo log 主要分为两种
  • insert undo log，代表事务在 INSERT 新记录时产生的 undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃
  • update undo log（主要），事务在进行 UPDATE 或 DELETE 时产生的 undo log，不仅在事务回滚时需要，在快照读时也需要，所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除

redo log vs. binlog

• 作用不同：redo log 是用于 crash recovery 的，保证 MySQL 宕机也不会影响持久性；binlog 是用于 point-in-time recovery 的，保证服务器可以基于时间点恢复数据，此外 binlog 还用于主从复制。
• 层次不同：redo log 是 InnoDB 存储引擎实现的，而 binlog 是 MySQL 的服务器层实现的，同时支持 InnoDB 和其他存储引擎。
• 内容不同：redo log 是物理日志，内容基于磁盘的 Page；binlog 的内容是二进制的，根据 binlog_format 参数的不同，可能基于 SQL 语句、基于数据本身或者二者的混合。
• 写入时机不同：binlog 在事务提交时写入；redo log 的写入时机相对多元：
  • 当事务提交时会调用 fsync 对 redo log 进行刷盘，这是默认情况下的策略，修改 innodb_flush_log_at_trx_COMMIT 参数可以改变该策略，但事务的持久性将无法保证。
  • Master Thread 每秒刷盘一次 redo log，这样的好处是不一定要等到 COMMIT 时刷盘，COMMIT 速度大大加快。

ACID

事务（Transaction）是访问和更新数据库的程序执行单元，事务中可能包含一个或多个 SQL 语句，这些语句要么都执行，要么都不执行。MySQL 支持事务的存储引擎有 InnoDB、NDB Cluster 等，其中 InnoDB 的使用最为广泛，其他存储引擎不支持事务，如 MyIsam、Memory 等。

MySQL 服务器逻辑架构从上往下可以分为三层：
第一层：处理客户端连接、授权认证等。
第二层：服务器层，负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等。
第三层：存储引擎，负责 MySQL 中数据的存储和提取。MySQL 中服务器层不管理事务，事务是由存储引擎实现的。

• 按照严格的标准，只有同时满足 ACID 特性才是事务。但是在各大数据库厂商的实现中，真正满足 ACID 的事务少之又少。例如 MySQL 的 NDB Cluster 事务不满足持久性和隔离性，InnoDB 默认事务隔离级别是可重复读，不满足隔离性，Oracle 默认的事务隔离级别为 READ COMMITTED，不满足隔离性。因此与其说 ACID 是事务必须满足的条件，不如说它们是衡量事务的四个维度。

Atomicity

• 原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个 sql 语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。
  • 通过 undo log 实现原子性。
  • 对于每个 INSERT，回滚时会执行 DELETE
  • 对于每个 DELETE，回滚时会执行 INSERT
  • 对于每个 UPDATE，回滚时会执行一个相反的 UPDATE，把数据改回去

Consistency

• 一致性是指事务把数据库从一个有效的状态转移成另一个有效状态，事务执行结束后，数据库的完整性没有被破坏，事务执行的前后都是合法的数据状态。一致性是事物追求的最终目标，原子性，隔离性，持久性都是为了保证数据库的一致性。此外，除了数据库底层的保障，一致性的实现也需要应用层的保障。
  • 保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证
  • 数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等
  • 应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致

Isolation

• 隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。严格的隔离性，对应了事务隔离级别中的 Serializable （可串行化），但实际应用中出于性能方面的考虑很少会使用可串行化。
  • （一个事务）写操作对（另一个事务）写操作的影响：锁机制保证隔离性
  • （一个事务）写操作对（另一个事务）读操作的影响：MVCC 保证隔离性

Durability

• 持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
  • 通过 redo log 实现持久性。

    InnoDB 作为 MySQL 的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘 IO，效率会很低。
为此，InnoDB 提供了缓存(Buffer Pool)，Buffer Pool 中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲。
当从数据库读取数据时，会首先从 Buffer Pool 中读取，如果 Buffer Pool 中没有，则从磁盘读取后放入 Buffer Pool。
当向数据库写入数据时，会首先写入 Buffer Pool，Buffer Pool 中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。
Buffer Pool 的使用大大提高了读写数据的效率，但是也带了新的问题。
如果 MySQL 宕机，而此时 Buffer Pool 中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。
    于是，redo log 被引入来解决这个问题。
当数据修改时，除了修改 Buffer Pool 中的数据，还会在 redo log 记录这次操作。
当事务提交时，会调用 fsync 接口对 redo log 进行刷盘。
如果 MySQL 宕机，重启时可以读取 redo log 中的数据，对数据库进行恢复。
redo log 采用的是 WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到 Buffer Pool，
保证了数据不会因 MySQL 宕机而丢失，从而满足了持久性要求。
    既然 redo log 也需要在事务提交时将日志写入磁盘，为什么它比直接将 Buffer Pool 中修改的数据写入磁盘(即刷脏)要快呢？
    主要有以下两方面的原因：
    （1）刷脏是随机 IO，因为每次修改的数据位置随机，但写 redo log 是追加操作，属于顺序 IO。
    （2）刷脏是以数据页（Page）为单位的，MySQL 默认页大小是 16KB，一个 Page 上一个小修改都要整页写入；
而 redo log 中只包含真正需要写入的部分，无效 IO 大大减少。

MVCC

全称 Multi-Version Concurrency Control，即多版本并发控制，主要是为了提高数据库的并发性能。MVCC 为事务分配单向增长的时间戳，维持一个数据的多个版本，使读写操作没有冲突的一个抽象概念。可解决脏读、幻读、不可重复读等事务隔离问题。

当前读

• 读取的数据库记录都是当前最新的版本，会对当前读取的数据进行加锁，防止其他事务修改数据，是悲观锁的一种操作。
  • SELECT LOCK IN SHARE MODE (共享锁)
  • SELECT FOR UPDATE (排他锁)
  • INSERT / UPDATE / DELETE (排他锁)
  • 串行化事务隔离级别

快照读

• 基于多版本并发控制（MVCC），读到的数据不一定是当前最新的数据，有可能是之前历史版本的数据。
  • 不加锁的 SELECT 操作（注：事务级别不是串行化）

实现原理

• 主要通过版本链，undo log ，Read Viev 来实现

版本链

• InnoDB 的每行数据有一个隐藏的 db_roll_pointer 字段，是一个回滚指针，用于配合 undo log，指向上一个旧版本。每次对数据库记录进行改动，都会记录一条 undo log，每条 undo log 也都有一个 roll_pointer 属性（INSERT 操作对应的 undo log 没有该属性，因为该记录并没有更早的版本），可以将这些 undo log 都连起来，串成一个链表，称之为版本链。

Read Viev（读视图）

• 主要是用来做可见性判断的, 即当某个事务执行快照读的时候，对该记录创建一个 Read Viev 读视图，用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的 undo log 里面的某个版本的数据。
• Read Viev 属性
  • trx_ids: 当前系统活跃（未提交）事务版本号集合
  • low_limit_id: 创建当前 Read Viev 时当前系统最大事务版本号 + 1
  • up_limit_id: 创建当前 Read Viev 时系统正处于活跃事务最小版本号
  • creator_trx_id: 创建当前 Read Viev 的事务版本号
• Read Viev 可见性判断条件
  • db_trx_id < up_limit_id || db_trx_id == creator_trx_id（显示）
    • 如果事务 ID 小于 Read Viev 中的最小活跃事务 ID，则可以肯定该数据是在当前事务启之前就已经存在了的，可以显示
    • 如果事务 ID 等于 creator_trx_id，那么说明数据就是当前事务生成的，可以显示
  • db_trx_id >= low_limit_id（不显示）
    • 如果事务 ID 大于 Read Viev 中的当前系统的最大事务 ID，则数据是在当前 Read Viev 创建之后产生的，不显示
    • 如果小于则进入下一个判断
  • db_trx_id 是否在活跃事务 trx_ids 中
    • 不存在，则说明 Read Viev 产生的时候事务已经 COMMIT 了，可以显示
    • 已存在，则代表 Read Viev 产生的时候事务还在活跃（没有 COMMIT），不显示
• Read View 用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现
• RR、RC 隔离级别下 Read View 生成时机
  • RC 隔离级别下，每个快照读都会生成并获取最新的 Read View
  • RR 隔离级别下，同一个事务中的第一个快照读会创建 Read View, 之后的快照读获取的都是同一个 Read View
• RC、RR 隔离级别下快照读区别
  • RR 级别下事务对某条记录的第一次快照读会创建一个快照及 Read View， 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，使用的是同一个 Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个 Read View，所以对之后的修改不可见，即 RR 级别下，快照读生成 Read View 时，Read View 会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的，而早于 Read View 创建的事务所做的修改均是可见
  • RC 级别下事务每次快照读都会新生成一个快照和 Read View, 所以 RC 级别事务中可以看到其他事务提交的更新

MySQL 对幻读的处理

• 快照读：通过 MVCC 来进行控制的，不用加锁
• 当前读：通过 next-key 锁（行锁 + gap 锁）来解决

聚集索引 vs. 非聚集索引

聚集索引

• 索引中键值的逻辑顺序决定了表中相应行的物理顺序，只要是索引是连续的，那么数据在存储介质上的存储位置也是连续的
• 查询效率快，只要找到第一个索引值记录，其余连续性的记录在物理上也一样连续存放
• 修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序
• 因为在物理内存中的顺序只能有一种，所以聚集索引在一个表中只能有一个

非聚集索引

• 索引的逻辑顺序与磁盘上的物理存储顺序不同，非聚集索引的键值在逻辑上也是连续的，但是表中的数据在存储介质上的物理顺序是不一致的，即记录的逻辑顺序和实际存储的物理顺序没有任何联系
• 叶子层并不和实际数据页相重叠，叶子层包含一个指向表中的记录在数据页中的指针
• 层次多，不会造成数据重排
• 非聚集索引一个表可以存在多个

B+Tree

https://www.cnblogs.com/cangqinglang/p/15042752.html

• 与 B-Tree 相比，B+Tree 有以下不同点：
  • 每个节点的指针上限为 2d 而不是 2d + 1
  • 内节点不存储 data，只存储 key，叶子节点不存储指针
  • 每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的 B+Tree，做这个优化的目的是为了提高区间访问的性能
• 平衡二叉树每个节点只存储一个键值和数据的，如果要存储海量的数据，那么二叉树的节点将会非常多，高度也会极其高，查找数据时会进行很多次磁盘 IO，查找数据的效率将会极低

MySQL 主从同步

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

主从同步的好处

• 读写分离，让主库负责写，从库负责读，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运行
• 高可用，数据实时备份（热备），主库宕机后能够及时替换主库，保证业务可用性
• 架构的扩展，增加多个数据存储节点，将负载分布在多个从节点上，可降低磁盘 I/O 访问的频率，提高单个机器的 I/O 性能

主从形式

• 一主一从
• 一主多从
• 多主一从 （5.7+）
  • 可将多个主节点数据备份到一台存储性能比较好的从节点上
• 双主复制
  • 互做主从复制，两个节点互为 master/slave，任一节点所做的变更都会复制应用到另外一个节点
• 级联复制
  • 部分从节点不直接从主节点同步数据，而是从与主节点直连的从节点同步数据（主节点有太多的从节点，会损耗一部分性能用于 replication，可以让 3 ~ 5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响）

主从同步方法

• 有多种主从同步的方法
  • Statement Based Replication（SBR）基于 SQL 语句的复制 （mysql5.6 默认使用）
    • 日志文件更小
    • 记录了所有的语句，可以用来日后审计
    • 部分函数语句不能被正确地复制
    • INSERT ... SELECT 语句会执行大量的行级锁表
    • UPDATE 语句会执行大量的行级锁表来扫描整个表
  • Row Based Replication（RBR）基于行的复制
    • 所有的数据变化都是被复制，这是最安全的复制方式
    • 更少的行级锁表
    • 日志会很大
    • 不能通过查看日志来审计执行过的 sql 语句（可通过 mysqlbinlog --base64-output=decode-rows --verbose 查看数据变动）
  • Mixed Based Replication（MBR） - SQL + 行混合复制（既使用 SBR 也使用 RBR，默认使用 SBR）
  • global transaction identifiers（GTIDs）基于事务的复制（GTIDs 完全基于事务，可大大简化复制过程，只要在主节点上提交了事务，从节点就一定会执行该事务）

配置主从同步

• 主节点操作
  1. 开启二进制日志，并配置一个独立的 ID（server-id）

  [mysqld]
  server-id = 1
  log-bin = mysql-bin                                                # 日志文件
  binlog_format = MIXED                                              # 日志记录的格式
  max_binlog_size = 512M  　　　                                      # 单个日志文件最大
  expire_logs_day = 3                                                # 日志有效期（天）
  binlog_do_db = db1,db2                                             # 日志记录的数据库
  binlog_ignore_db = mysql,performance_schema,information_schema     # 日志记录忽略的数据库
  2. 创建一个用来专门同步数据的账号

  GRANT REPLICATION SLAVE ON *.* to 'slave'@'192.168.1.253' IDENTIFIED WITH caching_sha2_password BY '123456';
  3. 刷新并锁表（只允许查数据不允许写数据）

  FLUSH TABLES WITH READ LOCK;
  4. 备份要同步的数据库的数据，拷贝到从节点（仅在主节点已有数据时需要）

  -- 主节点导出数据
  mysqldump -uroot -p db1 > db1.sql;
  mysqldump -uroot -p db2 > db2.sql;
  -- 从节点导入数据
  mysql -uroot -p db1 < db1.sql;
  mysql -uroot -p db2 < db2.sql;
  5. 查询并记录 Position 和 File 的值（从节点配置同步用）

  SHOW MASTER STATUS;
  6. 在完成从节点操作后（从节点同步状态正常），解锁表

  UNLOCK TABLES;
• 从节点操作
  1. 开启二进制日志，并配置一个独立的 ID（server-id）

  [mysqld]
  server-id = 2
  log-bin = mysql-bin                                                # 日志文件，建议开启，可能变主节点
  binlog_format = MIXED                                              # 日志记录的格式
  max_binlog_size = 512M  　　　                                      # 单个日志文件最大
  expire_logs_day = 3                                                # 日志有效期（天）
  replicate_do_db = db1,db2                                          # 复制的数据库
  replicate-ignore-db = mysql,performance_schema,information_schema  # 忽略的数据库
  relay_log_recovery = 1                                             # 建议开启，有利于数据一致性
  log_slave_updates = 1                                              # 建议开启，可能变主节点
  2. 重启从节点，执行如下命令(先停止同步，然后设置同步信息，master_log_file 和 master_log_pos 从主节点查询)

  STOP SLAVE;
  CHANGE MASTER TO
      master_host = '192.168.1.252',
      master_user = 'slave',
      master_password = '123456',
      master_port = 3306,
      master_log_file = 'mysql-bin.000023', 
      master_log_pos = 2720
      -- 定义通道名称，可用于多主同步
      FOR CHANNEL 'channel_name';                                        
  SLAVE START;
  3. 检查从节点同步状态

  SHOW SLAVE STATUS;
  主要看这几项

  lave_IO_Running: Yes
  Slave_SQL_Running: Yes
  Master_Log_File: mysql-bin.000023
  Relay_Master_Log_File: mysql-bin.000023
  Read_Master_Log_Pos: 2720
  Exec_master_log_pos: 2720

主从复制的流程

1. 主节点的更新事件（UPDATE、INSERT、DELETE）被写到 binlog
2. 从节点通过 I/O 线程 创建到主节点的连接，连接后向主节点发送身份信息、所需的 binlog 文件名及偏移量
3. 主节点开启 dump 线程（有多个从节点时会分别开启 dump 线程）与从库建立连接，连接后校验从库的身份信息，然后根据从节点发来的 binlog 文件名及偏移量，从本地读取指定 binlog 文件，发送给从库
4. 从节点拿到 binlog 数据后，将其写入本地的中继日志（relay log）
5. 从库的 SQL 线程 读取中继日志，逐个解析出日志里的命令，然后执行这些命令

多主一从同步

• 基本操作与一主一从同步一致，从节点配置同步略有不同。

STOP SLAVE;
CHANGE MASTER TO
    master_host = '192.168.1.252',
    master_user = 'slave',
    master_password = '123456',
    master_port = 3306,
    master_log_file = 'mysql-bin.000023', 
    master_log_pos = 2720
    -- 定义通道名称，可用于多主同步
    FOR CHANNEL 'channel_1';
CHANGE MASTER TO
    master_host = '192.168.1.253',
    master_user = 'slave',
    master_password = '123456',
    master_port = 3306,
    master_log_file = 'mysql-bin.000023', 
    master_log_pos = 2720
    -- 定义通道名称，可用于多主同步
    FOR CHANNEL 'channel_2';                                         
SLAVE START;

双主同步

• 基本操作与一主一从同步一致，只不过转换角色再操作一遍。

索引下推

索引下推（index condition pushdown ）简称 ICP，在 Mysql5.6 的版本上推出，用于优化查询，它能减少回表查询次数。 在不使用 ICP 的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给 MySQL 服务器，服务器然后判断数据是否符合条件。在使用 ICP 的情况下，如果存在某些被索引的列的判断条件时，MySQL 服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少 MySQL 服务器从存储引擎接收数据的次数。 索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

使用条件

• 只能用于 range、 ref、 eq_ref、ref_or_null 访问方法
• 只能用于 InnoDB 和 MyISAM 存储引擎及其分区表
• 对 InnoDB 存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）