Mysql概念

索引

• 存储结构区分

  • B-Tree
  • B+Tree
  • Hash索引

• 应用层区分

  • 普通索引
  • 唯一索引
  • 主键索引
  • 复合索引
  • 空间索引
  • FULLTEXT全文索引

存储引擎

• InnoDB
  • 默认存储引擎
  • 支持自增长列（auto_increment）
  • 支持外键（foreign key）
  • 索引使用的是B+Tree
  • 支持MVCC的行级锁
  • 提供了事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全
  • 优点
    • 支持事务，崩溃修复能力，并发控制
  • 缺点
    • 读写效率差，占用数据空间相对较大
  • 场景：既有读，写也频繁，不知道如何选择适合时。
• MyISAM
  • 不支持事务
  • 不支持行锁
  • 支持并发插入表锁
  • 主要用于高负载的select
  • 优点
    • 占用空间小，处理速度快
  • 缺点
    • 不支持事务的完整性和并发
  • 场景：表中绝大多数都只是读查询（一般R/W > 100:1且update相对较少），可以考虑 MyISAM。
• MEMORY
  • 数据存储在内存中
  • 默认使用哈希（HASH）索引，其速度比B+Tree要快，如果希望使用B树型，可在创建表的时候使用
  • 优点
    • 数据快速访问和处理
  • 缺点
    • 一旦发生异常，重启或关闭机器，数据都会丢失。
  • 场景：适合用于查询的临时表。
• Archive 归档
  • 只支持insert和select
  • 插入快，查询需要执行全表扫描
  • 优点
    • 高压缩，快速插入。
  • 缺点
    • 不适合频繁查询。
  • 场景：适合存储大量独立的、作为历史记录的数据。
• 其它引擎
  • CSV存储引擎
  • BLACKHOLE存储引擎（黑洞引擎）
  • PERFORMANCE_SCHEMA存储引擎
  • Federated存储引擎
  • MERGE存储引擎

事务

事务特性

• A 原子性
  • 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• C 一致性
  • 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
• I 隔离性
  • 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
• D 持久性
  • 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句

• BEGIN 或 START TRANSACTION 显式地开启一个事务；
• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier 把事务回滚到标记点；
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

mysql事务处理方法

• 用 BEGIN, ROLLBACK, COMMIT来实现
  • BEGIN 开始一个事务
  • ROLLBACK 事务回滚
  • COMMIT 事务确认
• 用SET 来改变 MySQL 的自动提交模式:
  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

脏读，幻读，不可重复读

• 脏读
  • 事务A将某一值修改但是还未提交，然后事务B读取该值，然后使用了这个数据
• 幻读
  • 事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读。
• 不可重复读
  • 在一个事务内根据同一个条件对数据进行多次查询，但是结果却不一致，原因是其他事务对该数据进行了修改。

隔离级别

• 未提交读（READ UNCOMMITTED）
  • READ UNCOMMITTED 提供了事务之间最小限度的隔离。除了容易产生虚幻的读操作和不能重复的读操作外，处于这个隔离级的事务可以读到其他事务还没有提交的数据，如果这个事务使用其他事务不提交的变化作为计算的基础，然后那些未提交的变化被它们的父事务撤销，这就导致了大量的数据变化。
• 提交读（READ COMMITTED）
  • READ COMMITTED 隔离级别的安全性比 REPEATABLE READ 隔离级别的安全性要差。处于 READ COMMITTED 级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的多个 SELECT 语句可能返回不同的结果。
• 可重复读（REPEATABLE READ）
  • 在可重复读在这一隔离级别上，事务不会被看成是一个序列。不过，当前正在执行事务的变化仍然不能被外部看到，也就是说，如果用户在另外一个事务中执行同条 SELECT 语句数次，结果总是相同的。（因为正在执行的事务所产生的数据变化不能被外部看到）。
• 序列化（SERIALIZABLE）
  • 如果隔离级别为序列化，则用户之间通过一个接一个顺序地执行当前的事务，这种隔离级别提供了事务之间最大限度的隔离。

锁

• 锁粒度
  • 表锁
    • 使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。
    • 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 行锁
    • 使用行级锁定的主要是InnoDB存储引擎。
    • 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • 页锁
    • 使用页级锁定的主要是BerkeleyDB存储引擎。
    • 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
• 加锁机制
  • 乐观锁
  • 悲观锁
• 兼容性
  • 共享(s)锁
    • select * from table_name where … lock in share mode (加共享锁)
  • 排它(x)锁
    • 对于InnoDB 在RR(MySQL默认隔离级别) 而言，对于 update、delete 和 insert 语句， 会自动给涉及数据集加排它锁（X）。
    • 对于普通 select 语句，innodb 不会加任何锁。如果想在select操作的时候加上 S锁 或者 X锁，需要我们手动加锁。
    • select * from table_name where … for update (加排它锁)
  • 读锁会阻塞写(X)，但是不会堵塞读(S)。而写锁则会把读(S)和写(X)都堵塞
• 加锁模式
  • 记录锁
    • 对表中的记录加锁，叫做记录锁，简称行锁
      • SELECT * FROM test WHERE id = 1 FOR UPDATE;
      • 它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。
      • id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。
      • 同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁
      • 在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：
      • UPDATE SET age = 50 WHERE id = 1;
    • 记录锁是锁住记录，锁住索引记录，而不是真正的数据记录.
    • 记录锁也是排它(X)锁,所以会阻塞其他事务对其插入、更新、删除。
  • 间隙(gap Locks)锁
    • 间隙锁是Innodb在RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。
    • 间隙锁是innodb中行锁的一种。
    • 使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
      • SELECT * FROM emp WHERE empid > 100 FOR UPDATE
      • 当我们用条件检索数据，并请求共享或排他锁时，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
      • 这个时候如果你插入empid等于102的数据的，如果那边事物还没有提交，那你就会处于等待状态，无法插入数据。
  • 临键(next-key)锁
    • 可以理解为一种特殊的间隙锁。
    • Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。
    • 通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。
    • InnoDB 中行级锁是基于索引实现的。
    • 临键锁只与非唯一索引列 有关，在唯一索引列包括主键列上不存在临键锁。

    ---

    记录锁、间隙锁、临键锁 总结
    1. InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。
    2. 记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
    3. 间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。
    4. 临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。

    ---

  • 意向锁
    • 意向锁又分为 意向共享锁（IS）和 意向排他锁（IX）
    • 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
    • 意向共享锁（IS）和 意向排他锁（IX）都是表锁。
    • 意向锁是一种不与行级锁冲突的表级锁
    • 意向锁是 InnoDB 自动加的， 不需用户干预。
    • 意向锁是在 InnoDB 下存在的内部锁，对于MyISAM 而言 没有意向锁之说。
    • 意向锁 存在的目的就是 为了让 InnoDB 中的行锁和表锁更高效的共存。
  • 插入意向锁
    • 插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。
    • 插入意向锁是一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。
    • 插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待
    • 插入意向锁是行锁
    • InnoDB在RR的事务隔离级别下，使用插入意向锁来控制和解决并发插入。

优化 Explain 性能分析

1. 语法

EXPLAIN  (SQL语句)
1

2. 执行计划包含的信息
   

• id
  • id是用来顺序标识整个查询中SELECT语句的编号，在嵌套查询中id越大的语句越先执行。该值可能为NULL，那么这一行用来说明的是其他行的联合结果。
• select_type
  • simple
    • 简单子查询，查询中不包含子查询和union
  • primary
    • 包含union或者子查询，最外层的部分标记为primary
  • subquery
    • 在select或者where列表中包含了子查询
  • derived
    • 派生表，该临时表是从子查询中派生出来的，位于form中的子查询
  • union
    • 位于union中第二个及其以后的子查询被标记为union，第一个就被标记为primary，如果是union位于from中则标记为derived
  • union result
    • 用来从匿名临时表里检索结果的select被标记为union result
  • dependent union
    • 首先需要满足union的条件，及union中第二个以及后面的select语句，同时该语句依赖外部的查询
  • dependent subquery
    • 和dependent union相对union一样
• table
  • 对应行正在访问哪一个表，表名或者别名。
• partitions
  • 匹配的分区，如果查询是基于分区表的话,会显示查询将访问的分区
• type
  • type显示的是访问类型，是较为重要的一个指标
  • 结果值从好到坏依次是：
    • system
      • 表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
    • const
      • 使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
    • eq_ref
      • 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
    • ref
      • 非唯一性索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独值得行，然而，它可能会找到多个符合条件的行，所以它属于查找和扫描的混合体
    • fulltext
      • 全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
    • ref_or_null
      • 与ref方法类似，只是增加了null值的比较。实际用的不多。
    • index_merge
      • 表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
    • unique_subquery
      • 用于where中的in形式子查询，子查询返回不重复值唯一值
    • index_subquery
      • 用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
    • range
      • 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般是在where语句中出现between、<、>、in等的查询中，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点不用做扫描全部索引
    • index
      • index和all的区别为index类型只遍历索引树，这通常比all要快，因为索引文件通常比数据文件小，也就是说all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读取的
    • all
      • 最坏的情况，将遍历全表以找到匹配的行
• possible_keys
  • 显示查询使用了哪些索引，表示该索引可以进行高效的查找，但是列出来的索引对于后续优化过程可能是没有用的。
• key
  • key列显示了MySQL优化器实际上所决定使用的键（索引），如果没有选择索引，键是NULL。要想MySQL使用或者忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
• key_len
  • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示地值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
• ref
  • 显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或者常量被用于查找索引列上的值。
• rows
  • rows列显示MySQL认为它执行查询时必须检查的行数，注意这是一个预估值。
• filtered
  • MySQL 5.7缺省就会输出filtered，它指返回结果的行占需要读到的行(rows列的值)的百分比
• Extra
  • Extra是Explain输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

mysql主从延时解决方案

• 产生原因
  • 随机重放
    • Mysql 主库中写 binlog 的操作是顺序写的，磁盘的顺序读写速度是很快的。同样的，从库中的 I/O 线程操作日志的速度效率也是很高的。但是SQL 线程重放的过程是随机写盘的，某一时刻 relay log 里的数据来不及重放进从库，就会产生主从延迟的情况。
  • 锁等待
    • 对于 SQL 单线程来说，当遇到阻塞时就会一直等待，直到执行成功才会继续进行。如果某一时刻从库因为查询产生了锁等待的情况，此时只有当前的操作执行完成后才会进行下面的操作，同理也就产生了主从延迟的情况。
  • 大事务
    • 当主库执行大事务时，例如大表DDL，一个事务操作很多行时，也会导致从库出现主备延时。
  • 硬件问题
• 解决方案
  • 并行复制
    • MySQL 5.6 版本后，提供了一种并行复制的方式，通过将 SQL 线程转换为多个 work 线程来进行重放
      
  • 降低主库并发
    • 合理使用队列和redis等，对高并发请求做限流等处理
  • 读主库
    • 对于一些实时性极高的数据，可以直接读主库，但这样就失去了读写分离的意义
  • 尽量保持主备库的硬件配置一致，甚至从库的服务器配置略高于主库。
  • .避免大事务
    • 大表ddl操作，建议将大事务拆分为多个小事务进行执行
  • 半同步复制
    • 主库只需要等待至少一个从库接收到并写到 Relay Log 文件即可，主库不需要等待所有从库给主库返回 ACK。主库收到这个 ACK 以后，才能给客户端返回 “事务完成” 的确认
      • 全同步复制(介绍)
        • 指当主库执行完一个事务，并且所有的从库都执行了该事务，主库才提交事务并返回结果给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
    • 半同步复制可能遇到的问题

      • 主库已经将事务 commit 到了存储引擎层，应用已经可以看到数据发生了变化，只是在等待返回而已。如果此时主库宕机，可能从库还没写入 Relay Log，就会发生主从库数据不一致 (如下图)
        

      • MySQL 5.7 引入了增强半同步复制。“Waiting Slave dump” 被调整到了 “Storage Commit” 之前，即主库写数据到 binlog 后，就开始等待从库的应答 ACK，直到至少一个从库写入 Relay Log 后，并将数据落盘，然后返回给主库 ACK，通知主库可以执行 commit 操作，然后主库再将事务提交到事务引擎层，应用此时才可以看到数据发生了变化。
        

主从复制，读写分离

springboot整合mycat实现读写分离