Summer Blog

MySQL总结

SQL 优化

性能下降的原因

  1. 没有索引
  2. 索引创建不当
  3. 数据变化，如数据量增大、特征值变化
  4. join 过多
  5. 配置不当

执行计划

  1. id越大越先执行，相同 id 顺序执行
  2. select_type查询类型：
    1. simple：简单查询
    2. primary： 使用主键
    3. derive：衍生表
    4. subquery：子查询
    5. union\union result：合并
  3. table查询表，partition查询分区
  4. type连接类型（访问类型），最好到最差：
    1. system：表中只有一行记录
    2. const：只查询一次
    3. eq_ref：唯一性索引扫描
    4. ref：查找条件列使用了索引而且不为主键和 unique。语句最好能达到这种情况
    5. range：索引范围查询
    6. index：全量索引扫描
    7. all：全表
  5. possible_keys：可能用到的索引
  6. keys：实际用到的索引
  7. key_len：使用索引的长度
  8. ref：索引使用的具体值，常量或者是其他列的值
  9. rows：扫描行数
  10. filter：用到的行数与扫描行数的百分例，越大越好，最大 100
  11. extra：额外信息
    1. using filesort：使用文件排序
    2. using temporary：使用临时表
    3. using index：使用索引
    4. using where：使用查询条件
    5. using join buffer：使用链接缓存
    6. impossible value：条件永远不会达成

count(*)很慢，怎么办

为什么慢：InnoDB 支持事务，采用多版本并发控制(MVCC)，每次执行count(*)返回的行数是不确定的，所以执行的时候会逐行读取后，累积计数。

MySQL 优化：InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。会在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

方案：

  1. Redis 计数，由于调用 redis 时机不同，可能结果不准确
  2. 单独存储，用事务的方式解决不准确的问题

比较count(id)、count(*)、count(1)、count(字段)

所以结论是：按照效率排序的话，count(字段) < count(id) < count(1) =. count(*)

只返回一条记录的语句（简单）会执行很慢吗？

会，有两种情况：

  1. 长时间无相应
    1. 等待锁（表级、行级）
    2. 等待 flush
  2. 查询慢
    1. 扫描行多
    2. 事务长，其他链接执行了很多操作，导致回到事务开始状态做查询的时间变长

连接优化

最佳实践：小表驱动大表

group by优化

分页优化

分页会找到 offset+size 条数据后，抛弃前 offset 条数据，故执行分页越深查询与慢。优化方式：

  1. 使用索引，比如先查 id
  2. 定位索引开始，然后查询大于该值的数据的 size 条数据

原理

SQL 执行过程

索引

索引字段选择

  1. 适合：
    1. 经常查询的字段
    2. 外键关系字段
    3. 排序、分组字段
  2. 不适合
    1. 经常变更的字段
    2. 不作为查询条件的字段
    3. 重复度高的字段

注意：

  1. 对索引字段做函数、计算、类型转换操作，不会使用索引
  2. 查询条件使用！= <> is not null or，不会使用索引
  3. 查询条件使用%开头，不会使用索引
  4. 组合索引需遵守最左前缀原则
  5. 尽量使用覆盖索引（查询字段刚刚与索引匹配），不用回表

自增主键

不同引擎存储自增主键的地方不相同：

如果插入的数据没有对自增主键列赋值，就是用当前值；若赋值则使用给定的值，当给定值大于当前自增值时会修改为给定值下一个值。

自增值可以是不连续的，造成原因有三种：

  1. 唯一键冲突
  2. 事务回滚
  3. insert...select操作时会通过批量申请 id 多申请值，这些值会被浪费掉

排序

MySQL 执行带排序的查询时有两种情况：

锁

表锁

行锁

间隙锁

引用极客时间 MySQL 实战中的总结的可重复读情况下的加锁规则：

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。此时，行锁和间隙锁是分两步完成的。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

table t

案例 1-唯一索引等值查询的间隙锁：若等值查询中，没有命中的行，则锁单位是 next-key lock

-- session A
begin; update t set d = d + 1 where id = 7;

-- session B
insert into t values(8,8,8); -- 阻塞，锁(5,10]区间

-- session C
insert into t values(11,8,8); -- 正常执行
update t set d=d+1 where id = 10; -- 正常执行

案例 2-非唯一索引等值锁：只有访问到的对象才会加锁，需注意：锁存在与索引上，若使用覆盖索引，并不会锁主键。

-- session A
select id from t where c=5 lock in share mode;

-- session B
update t set d=d+1 where id =5; -- 不会被锁，正常执行，锁的是c索引(5,10)区间

-- session C
insert into t values (7,7,7); -- 阻塞

案例 3-主键索引范围锁：锁扫描到的行，若出现等值根据优化 1 会退化成行锁。

案例 4-非唯一索引范围锁：锁扫描到的行，因为非唯一索引不会进行等值退化成行锁

案例 5-limit 语句加锁：锁扫描到的行，若满足了 limit 会优化不用锁到 next key

日志

Innodb 日志有两种，binglog(MySQL 提供) 和 redolog(引擎实现)。

binglog：

作用：归档，主备同步，数据恢复

格式：

  1. statement 直接记录语句，有可能主备不一致
  2. row 记录变化的行和数据，缺点是很占空间，如果一个语句改变多条记录，row 模式也会记录多条
  3. mixed 一般情况下用 statement，遇到有可能主备不一致的情况用 row

维护

短链接风暴：短链接太多，系统压力大时的临时解决方案

  1. 处理掉占着链接但不工作的线程，优先断开事物外空闲链接
  2. 减少连接过程的消耗，如跳过验证（风险极高）

紧急处理慢查询性能的问题

慢查询通常有以下三种原因造成：

  1. 索引没有设计好：紧急加索引解决（ONLINE DDL）
  2. SQL 语句写的不好：用query_rewrite方法重写语句
  3. MySQL 选错索引：加force index

预防：测试阶段在数据库中打开慢查询日志，并设置long_query_time为 0，记录每条语句，分析每个语句的扫描行和使用的索引是否和设想一致，提早发现问题。

主备切换 – 一主一从

MySQL 通常使用主备模式实现高可用，主备同步之间会有一定的延迟，造成延迟的原因主要有：主备性能差异；备库执行其他耗时统计分析任务；大事务同步。由于延迟存在，所以切换主备时有两种策略：

  1. 可靠性优先：这种方式会有不可用的时间，其中第 3 步比较耗时
    1. 判断备库延迟，若小于阈值，继续下一步，否则重试
    2. 将主库设置为只读状态
    3. 判断备库的延迟，直至延迟为 0
    4. 将备库设置为可写状态
    5. 切换流量
  2. 可用性优先：将 4、5 步骤调整到最开始执行，之后通过 binglog 补数据

主备切换 – 一主多从

此时从库找新主库的位点是一个痛点，5.6 版本加入了每个事务都有一个唯一的 GTID，可以根据它完成找位点的工作，从库同步时维护自己的 GTID 集合，切换主库后可以通过这个集合方便的跳过其他从库已经执行了的事务

主备复制的策略：

执行 MySQL5.5 之前官方支持单线程复制，如果主库并发度比较高，吞吐量会大于单线程的复制（生成日志的速度大于同步日志的速度），导致备库落后主库很多。

策略原则：

  1. 对相同行的操作，需要按顺序执行
  2. 对于一个事务里的操作，需要按顺序执行

策略：

  1. MySQL5.6 按库并行复制
  2. MariaBD redo log 组提交（group commit）优化，将不修改相同行的提交放在同一个 group 中，这些语句就是可以并发执行的
  3. MySQL5.7 同时处于 redo log 的 prepare 状态和 commit 状态的语句可以并行执行
  4. MySQL 5.7.22 除了 5.7 多提供了一种基于 hash 的方式，这个 hash 值通过库名+表名+索引名+值在写 binlog 时计算出来的，如果两个语句的 hash 没有交集，就可以并行执行

读写分离架构

  1. 客户端路
    • 优点： 简单、性能好
    • 缺点： 需要知道后端细节
  2. MySQL 和客户端之间加入中间代理层 Proxy
    • 优点： 不需要知道后端细节、主从切换客户端无感
    • 缺点： Proxy 层引入新的复杂性，且 Proxy 也需要做高可用

读写分离的情况下解决延迟

读写分离情况下，在主库新修改/新增了数据就立即查询，这时如果查的是从库，可能会由于延迟的原因看不到刚刚修改/新增的数据。解决该问题有以下方式：

  1. 指定语句只读主库
  2. 写入后等待一段时间后再查询，
  3. 确定主备无延迟时执行查询，可以通过查询seconds_behind_master参数为 0，或者比较 GTID 是否一致确认
  4. 配合 semi-sync，如果启用了 semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志

数据库健康状况监视

  1. 差表判断：select * from t。无法检查磁盘空间等问题
  2. 更新判断：update t set update_time = now()。有一定的延迟
  3. 内部统计：performance_schema库中的表。有性能消耗

误删数据处理

怎样快速地复制一张表

  1. mysqldump

    mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"

  2. 导出 csv 文件

    select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

load data infile '/server_tmp/t.csv' into table db2.t;

Effective Java

Chapter 3. Methods Common to All Objects

10.重写equals

一般不需要重写，在这种情况下类的每个实例仅仅与自身相等。若一定要比较两个实例的重写equals需要遵循以下规定：

  1. 自反性：对于任何非空引用 x，x.equals(x) 必须返回 true
  2. 对称性：对于任何非空引用 x 和 y，如果且仅当 y.equals(x) 返回 true 时 x.equals(y) 必须返回 true
  3. 传递性：对于任何非空引用 x、y、z，如果 x.equals(y) 返回 true，y.equals(z) 返回 true，则 x.equals(z) 必须返回 true
  4. 一致性：对于任何非空引用 x 和 y，如果在 equals 比较中使用的信息没有修改，则 x.equals(y) 的多次调用必须始终返回 true 或始终返回 false
  5. 与null比较一定是false

11.重写equals后一定要重新hashCode

根据 Object 规范，hashCode需遵循以下时具体约定：

  1. 如果没有修改 equals 方法中用以比较的信息，在应用程序的一次执行过程中对一个对象重复调用 hashCode 方法时，它必须始终返回相同的值。在应用程序的多次执行过程中，每个执行过程在该对象上获取的结果值可以不相同。
  2. 如果两个对象根据 equals(Object) 方法比较是相等的，那么在两个对象上调用 hashCode 就必须产生的结果是相同的整数。
  3. 如果两个对象根据 equals(Object) 方法比较并不相等，则不要求在每个对象上调用 hashCode 都必须产生不同的结果。 但是，程序员应该意识到，为不相等的对象生成不同的结果可能会提高散列表（hash tables）的性能。

当无法重写 hashCode 时，所违反第二个关键条款是：相等的对象必须具有相等的哈希码（ hash codes）。

12.始终重写toString法

toString 通用约定

建议所有的子类重写这个方法

14.考虑实现Comparable接口’

通过实现 Comparable 接口，可以让你的类与所有依赖此接口的通用算法和集合实现进行互操作。 只需少量的努力就可以获得巨大的能量。 几乎 Java 平台类库中的所有值类以及所有枚举类型（详见第 34 条）都实现了 Comparable 接口。 如果你正在编写具有明显自然顺序（如字母顺序，数字顺序或时间顺序）的值类，则应该实现 Comparable 接口。

Chapter 4. Classes and Interfaces

15.使类和成员的可访问性最小化

将设计良好的组件与设计不佳的组件区分开来的最重要的因素是，隐藏内部数据和其他实现细节的程度。一个设计良好的组件隐藏了它的所有实现细节，干净地将它的 API 与它的实现分离开来。然后，组件只通过它们的 API 进行通信，并且对彼此的内部工作一无所知。信息隐藏有利于模块化，使系统的每个部分的耦合性降低，方便单独开发、测试、修改、理解、使用；也降低了构建大型系统的风险，因为即使系统不能运行，各个独立的组件也可能是可用的。

对于成员（字段、方法、嵌套类和嵌套接口），有四种可能的访问级别，在这里，按照可访问性从小到大列出：

在 Java 9 中，作为模块系统（module system）的一部分引入了两个额外的隐式访问级别。模块包含一组包，就像一个包包含一组类一样。模块可以通过模块声明中的导出（export）声明显式地导出某些包 (这是 module-info.java 的源文件中包含的约定)。模块中的未导出包的公共和受保护成员在模块之外是不可访问的；在模块中，可访问性不受导出（export）声明的影响。使用模块系统允许你在模块之间共享类，而不让它们对整个系统可见。在未导出的包中，公共和受保护的公共类的成员会产生两个隐式访问级别，这是普通公共和受保护级别的内部类似的情况。这种共享的需求是相对少见的，并且可以通过重新安排包中的类来消除。

16.在公共类中使用访问方法而不是公共属性

// Degenerate classes like this should not be public!
class Point {
    public double x;
    public double y;
}

公共类不应该暴露可变属性。公共类暴露不可变属性的危害虽然仍然存在问题，但其危害较小。然而，有时需要包级私有或私有内部类来暴露属性，无论此类是否是可变的。

17.最小化可变性

不可变类简单来说是其实例不能被修改的类。包含在每个实例中的所有信息在对象的生命周期中是固定的，因此不会观察到任何变化。Java 平台类库包含许多不可变的类，包括 String 类、基本类型包装类以及 BigInteger 类和 BigDecimal 类。有很多很好的理由：不可变类比可变类更易于设计，实现和使用。他们不容易出错，并且更安全。

要使一个类成为不可变类，请遵循以下五条规则：

  1. 不要提供修改对象状态的方法
  2. 确保这个类不能被继承
  3. 把所有字段设置为 final
  4. 把所有的字段设置为 private
  5. 确保对任何可变组件的互斥访问

好处：

缺点：

18.组合优于继承

只有在子类真的是父类的子类型的情况下，继承才是合适的。换句话说，只有在两个类之间存在「is-a」关系的情况下，B 类才能继承 A 类。如果你试图让 B 类继承 A 类时，问自己这个问题：每个 B 都是 A 吗？如果你不能明确的以“是的”来回答这个问题，那么 B 就不应该继承 A。如果答案是否定的，那么 B 通常包含一个 A 的私有实例，并且暴露一个不同的 API：A 不是 B 的重要部分，只是其实现细节。

总之，继承是强大的，但它是有问题的，因为它违反封装。只有在子类和父类之间存在真正的子类型关系时才适用。即使如此，如果子类与父类不在同一个包中，并且父类不是为继承而设计的，继承可能会导致脆弱性。为了避免这种脆弱性，使用组合和转发代替继承，特别是如果存在一个合适的接口来实现包装类。包装类不仅比子类更健壮，而且更强大。

19.要么设计继承并提供文档说明，要么禁用继承

在被设计用来继承的类中，必须保证：

  1. 类必须以精心挑选的 protected 方法的形式，提供适当的钩子（hook），以便进入其内部工作中
  2. 测试为继承而设计的类的唯一方法是编写子类。在发布它之前，你必须通过编写子类来测试你的类
  3. 构造方法绝不能直接或间接调用可重写的方法

20.接口优于抽象类

所有类都可以实现接口来改进自己的功能，但必须是抽象类的子类，才能得到抽象类提供的能力。

可以通过提供一个抽象的骨架实现类（abstract skeletal implementation class）来与接口一起使用，将接口和抽象类的优点结合起来。 接口定义了类型，可能提供了一些默认的方法，而骨架实现类在原始接口方法的顶层实现了剩余的非原始接口方法。 继承骨架实现需要大部分的工作来实现一个接口。 这就是模板方法设计模式[Gamma95]。

21.为后代设计接口

应该避免使用默认方法向现有接口添加方法，应该谨慎考虑。

22.接口仅用来定义类型

当类实现接口时,该接口作为一种类型(type)，可以用来引用类的实例。因此，一个类实现了一个接口，表明客户端可以如何处理类的实例。为其他目的定义接口是不合适的。

常量接口是对接口的糟糕使用。有几种替代方式：

  1. 将常量定义在其相关的接口中
  2. 用enum定义常量

23.类层次结构优于标签类

标签类是一些类有明显的分类，但只通过其中的字段标记具体的分类，这样的设计增加了类的复杂性，降低了拓展性。可以通过创建一个基类和多个子类来代替标签类。

24.支持使用静态成员类而不是非静态类

嵌套类有四种：

  1. 静态成员类：最好把它看作一个普通类，恰好在另一类中声明。
  2. 非静态成员类：每一个实例都隐含地与其中包含的类的宿主实例相关联。它们的关系实在创建成员类实例时建立的，并且之后不能修改。每个实例都会隐藏一个外部引用给它的宿主实例，这个引用需要占用空间和时间，且可能导致宿主类在满足垃圾回收的条件时人然驻留内存中。例，ArrayList.Itr
  3. 匿名类：在使用时才被声明和实例化，不是宿主类的成员。不能声明一个匿名类继承多个类，或实现多个接口。
  4. 局部类

25.将源文件限制为单个顶级类

虽然Java编译器允许在单个源文件中定义多个顶级类，但这样做没有任何好处，并且存在重大风险。风险源于在源文件中定义多个顶级类使得为类提供多个定义成为可能。使用哪个定义会受到源文件传递给编译器的顺序的影响。

2019年度总结

2019年度总结

完成的事

技术学习

项目