VC' Blog

疫情风险等级爬虫小记

Posted on 2020-04-12 Edited on 2020-04-16 In Python

近日看到国务院印发的《全国不同风险地区企事业单位复工复产疫情防控措施指南》中提到了低风险，中风险和高风险地区，搜索发现只能通过“国务院客户端微信小程序”以区/县为单位进行查询，想了解一下全国各地的形式需要挨个查询，没有一个可以纵览全局的地图，心血来潮想要自己画一个。

数据源

网上常见的查询入口都是微信小程序，但是微信小程序相对封闭，咱也不属于数据获取相对不方便，想找个普通的网站服务。经过一番搜索发现国家政务服务平台也提供了一个区/县疫情风险等级查询入口，开干。

可扩展的 MySQL

Posted on 2020-04-05 Edited on 2020-04-11 In Database

在 MySQL 的规模越来越大时，如何保证快速、高效且经济。

什么是可扩展性

容易混淆的用词：可扩展性，高可用性，性能。

可扩展性就是能过够通过增加资源提升容量（工作效率）的能力。表明了当需要增加资源以执行更过工作时系统能够划算地提供等同提升（equal bang for the buck）的能力。另一种说法是，可扩展性是当增加资源以处理负载和增加容量时系统能够获得的投入产出比。

大部分系统都没办法做到线性扩展，越高的扩展系数导致越大的线性偏差，最终达到临界点。—— 详见通用可扩展定律（Universal Scalability Law，USL）

Mysql 复制

Posted on 2020-03-13 Edited on 2020-04-09 In Database

概述

复制功能不仅有利于构建高性能的应用，同时也是高可用、可扩展性、容灾恢复、备份以及数据仓库等工作的基础。

MySQL 支持两种复制方式：

基于行的复制
基于语句的复制（也称为逻辑复制）

两种方式都是通过在主库上记录二进制日志，在备库中重放日志实现异步的数据复制，因此存在延迟。

MySQL 的复制大部分后向兼容，新版本的服务器可以作为老版本服务器的备库，但反之不行，因为它可能无法解析新版中的新特性或语法。对于小版本号升级通常是兼容的。

查询性能优化

Posted on 2020-02-13 Edited on 2020-10-23 In Database

表结构优化、索引优化、查询优化缺一不可，需要齐头并进。

嵌套循环算法

MySQL 的联表算法是基于嵌套循环算法（nested-loop algorithm）而衍生出来的一系列算法，根据不同条件而选用不同的算法

在使用索引关联的情况下，有 Index Nested-Loop join 和 Batched Key Access join 两种算法;
在未使用索引关联的情况下，有 Simple Nested-Loop join 和 Block Nested-Loop join 两种算法;

// Simple Nested-Loop
for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}


// Block Nested-Loop
for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}

if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}

Simple Nested-Loop 最为简单粗暴，毫无性能可言，事实上不会使用这种算法。

Block Nested-Loop 缓存块嵌套循环连接，简称 BNL，是对 SNL 的一种优化。一次性缓存多条驱动表的数据到 Join Buffer，然后拿 Join Buffer 里的数据批量与内层循环读取的数据进行匹配。将内部循环中读取的每一行与缓冲区中的所有记录进行比较，这样就可以减少内层循环的读表次数。原来内层循环的读表次数是外层循环的行数 n，如今读表次数为 n/buffer_size。

Index Nested-Loop 索引嵌套循环，简称 INL，是基于被驱动表的索引进行连接的算法；驱动表的记录逐条与被驱动表的索引进行匹配，避免和被驱动表的每条记录进行比较。

Batched Key Access 是对INL的进一步优化，详见文档。

查询速度为什么会变慢

查询需要在不同的地方花费时间，包括网络，CPU 计算，生成统计信息和执行计划，锁等待等操作，尤其是底层存储引擎检索数据，这些检索需要内存操作、CPU 操作和内存不足时的 IO 操作。

慢查询基础：优化数据访问

分析步骤

确认应用程序是否检索大量不必要的的数据，太多行或太多列。
确认 MySQL 服务器层是否在分析大量不必要的数据行。

是否向数据库请求了多余的数据

查询不需要的记录
使用 SELECT 查询所有结果，获取前面的 N 行后关闭，实际上 MySQL 已经查询出全部结果。需要多少查多少，用 LIMIT 进行限制。
取出全部列
不要使用 SELECT * FROM a INNER JOIN b INNER JOIN C;这样的语句。很多时候取出全部列是不必要的。并且，取出全部列会让优化器无法完成索引覆盖这类优化，还会为服务器带来额外的 IO、内存和 CPU 的消耗。
有时候出于开发效率，应用程序可以通过缓存进行复用等角度也可以查询这些多余的数据，需要进行权衡。
重复查询相同的数据
利用缓存，减少重复查询。

创建高性能的索引

Posted on 2020-02-12 Edited on 2020-08-26 In Database

索引基础

索引可以包含一个或多个列的值，MySQL 只能高效地使用索引的最左前缀，如果索引包含多个列，那么列的顺序也十分重要。

B 树索引

B 树中所有的值按顺序存储，每个叶节点到根的距离相同。

索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。

B 树适用于全键值、键值范围或键前缀查找：

全值匹配：和索引中的所有列进行匹配
匹配最左前缀：只使用索引的第一列
匹配列前缀：只匹配某一列的开头部分
匹配范围值：匹配值处于某个区间
精确匹配某一列并范围匹配另一列
只访问索引的查询

索引树中的节点是有序的，因此还可以用于查询中的ORDER BY操作。如果ORDER BY子句满足前面列出的集中查询类型，则索引也可以满足对应的排序需求。

B 树索引的限制：

如果不是按照索引的最左列开始查找，则无法使用索引。如为last_name, first_name, birthday创建索引，索引无法用于查询名字为 Bill 的人，也无法用于查找某个特定的生日。类似的，无法查找姓氏以某个字结尾的人。
不能跳过索引中的列。前述的索引无法用于查找特定姓氏并且在某个特定生日的人，如果不指定 first_name，只能使用索引的第一列。
如果查询中有某个列的范围查询，则其右边所有的列无法使用索引优化查询。

Schema 与数据类型优化

Posted on 2020-02-07 Edited on 2020-08-26 In Database

选择优化的数据类型

选择数据类型的原则

更小的通常更好。应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型占用更少的磁盘、内存和 CPU 缓存，处理时需要的 CPU 周期也更少。但也要确保没有低估需要存储的值的范围。(个人觉得字符串的长度限制有时候真的很难把握)
简单就好。简单数据类型的操作通常需要更少的 CPU 周期，整型的代价比字符串低，内建时间类型比字符串低，用整型存储 IP 比字符串低。
尽量避免使用NULL。查询可为 NULL 的列对于 MySQL 来说更难优化，这些列使得索引、索引统计和值比较都更复杂。

MySQL 架构和历史

Posted on 2020-02-07 Edited on 2020-09-05 In Database

逻辑架构

三层架构：

连接与线程处理

每个连接拥有一个线程，线程的创建和销毁消耗资源，可以通过维护线程池的方式，提高性能。
注意区分线程池和连接池，连接池一般在客户端设置，而线程池是在 DB 服务器上配置。连接池可以减少连接的创建和释放，提高请求的平均响应时间，控制一个应用的 DB 连接数，但无法控制整个应用集群的连接数规模。线程池和连接池需要结合使用。

MySQL 服务层：SQL 解析、分析、优化、缓存

MySQL 的优化器不关心使用的是什么存储引擎，但存储引擎对于优化查询有影响。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息。
Oracle 中有库基于规则优化 (RBO) 和基于代价优化 (CBO) 两种方式，但oracle 10g之后已经弃用 RBO。相比 MySQL 的优化器，Oracle 的优化更加丰富和完善，可以对照着学习。