
# 二进制传输
HTTP/2 传输数据量的大幅减少, 主要有两个原因: 以二进制方式传输和 Header 压缩。我们先来介绍二进制传输，HTTP/2 采用二进制格式传输数据，而非 HTTP/1.x 里纯文本形式的报文 ，二进制协议解析起来更高效。HTTP/2 将请求和响应数据分割为更小的帧，并且它们采用二进制编码。

它把 TCP 协议的部分特性挪到了应用层，把原来的 "Header+Body" 的消息 " 打散 " 为数个小片的二进制 " 帧 "(Frame), 用 "HEADERS" 帧存放头数据、"DATA" 帧存放实体数据。HTTP/2 数据分帧后，“Header+Body" 的报文结构就完全消失了，协议看到的只是一个个 " 碎片”。

HTTP/2 中，同域名下所有通信都在单个连接上完成，该连接可以承载任意数量的双向数据流。每个数据流都以消息的形式发送，而消息又由一个或多个帧组成。**多个帧之间可以乱序发送，根据帧首部的流标识可以重新组装**。

# Header 压缩

HTTP/2 并没有使用传统的压缩算法，而是开发了专门的 "HPACK”算法，在客户端和服务器两端建立“字典”，用索引号表示重复的字符串，还采用哈夫曼编码来压缩整数和字符串，可以达到 50%~90% 的高压缩率。

具体来说：

- 在客户端和服务器端使用“首部表”来跟踪和存储之前发送的键 - 值对，对于相同的数据，不再通过每次请求和响应发送。
- 首部表在 HTTP/2 的连接存续期内始终存在，由客户端和服务器共同渐进地更新 。
- 每个新的首部键 - 值对要么被追加到当前表的末尾，要么替换表中之前的值。

# 多路复用

在 HTTP/2 中引入了多路复用的技术。多路复用很好地解决了浏览器限制同一个域名下请求数量的问题，同时也更容易实现全速传输，毕竟新开一个 TCP 连接都需要慢慢提升传输速度。

在 HTTP/2 中，有了二进制分帧之后，HTTP /2 不再依赖 TCP 链接去实现多流并行了，在 HTTP/2 中：

- 同域名下所有通信都在单个连接上完成。
- 单个连接可以承载任意数量的双向数据流。
- 数据流以消息的形式发送，而消息又由一个或多个帧组成，多个帧之间可以乱序发送，因为根据帧首部的流标识可以重新组装。

这一特性使性能有了极大提升：

- 同个域名只需要占用一个 TCP 连接，使用一个连接并行发送多个请求和响应, 这样整个页面资源的下载过程只需要一次慢启动，同时也避免了多个 TCP 连接竞争带宽所带来的问题。
- 并行交错地发送多个请求 / 响应，请求 / 响应之间互不影响。
- 在 HTTP/2 中，每个请求都可以带一个 31bit 的优先值，0 表示最高优先级， 数值越大优先级越低。有了这个优先值，客户端和服务器就可以在处理不同的流时采取不同的策略，以最优的方式发送流、消息和帧。

# Server Push

TTP2 还在一定程度上改变了传统的“请求 - 应答”工作模式，服务器不再完全被动地响应请求，也可以新建“流”主动向客户端发送消息。比如，在浏览器刚请求 HTML 的时候就提前把可能会用到的 JS、CSS 文件发给客户端，减少等待的延迟，这被称为 " 服务器推送 "（ Server Push，也叫 Cache push）。

![](/images/blog/1/https/assert/http2-server-push.png)

另外需要补充的是，服务端可以主动推送，客户端也有权利选择是否接收。如果服务端推送的资源已经被浏览器缓存过，浏览器可以通过发送 RST_STREAM 帧来拒收。主动推送也遵守同源策略。换句话说，服务器不能随便将第三方资源推送给客户端，而必须是经过双方确认才行。



# 配置nginx使用http2

- [免费https证书](/article/04f1a525d54ec5174b7b91e98717704e)

```nginx
server {
    listen 443 ssl http2;
    server_name www.example.com;

    ssl_certificate /etc/nginx/ssl/*.example.com/fullchain.cer;
    ssl_certificate_key /etc/nginx/ssl/*.example.com/*.example.com.key;

    # disable SSLv2
    ssl_protocols TLSv1 TLSv1.1 TLSv1.2;

    # ciphers' order matters
    ssl_ciphers "ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:ECDHE-RSA-AES128-SHA:ECDHE-RSA-AES256-SHA:AES128-SHA:AES256-SHA:DES-CBC3-SHA:!aNULL";

    ...
}
```

```shell
# curl -I https://www.douyacun.com
HTTP/2 200
server: openresty/1.15.8.2
date: Sat, 22 Feb 2020 16:03:00 GMT
content-type: text/html; charset=utf-8
content-length: 57890
vary: Accept-Encoding
x-powered-by: Next.js
etag: "e222-J6hQavZqthjlqWZcgZo9xa0vNyE"
vary: Accept-Encoding
```

HTTP/2 相比于 HTTP/1.1，可以说是大幅度提高了网页的性能，只需要升级到该协议就可以减少很多之前需要做的性能优化工作。当然，兼容问题以及如何优雅降级应该是国内还不普遍使用的原因之一。虽然 HTTP/2 提高了网页的性能，但并不代表它已经完美，

http/2新特性


# 文档

- logstash最佳实践(只能入门看看)：https://doc.yonyoucloud.com/doc/logstash-best-practice-cn/index.html

- 官方文档(看过logstash最佳实践以后，官方文档的大概结构就知道了)：https://www.elastic.co/guide/en/logstash/current/index.html

- filter grok 工具(logstash提供正则匹配工具)：http://grokdebug.herokuapp.com/
- logstash下载地址: https://www.elastic.co/cn/downloads/logstash
- 测试数据集: https://grouplens.org/datasets/movielens/

# 定义配置文件

logstash.conf

```conf
input {
        file {
                path => "/Users/liuning/Documents/utils/datasets/movies.csv"
                start_position => "beginning"
                sincedb_path => "/dev/null"
        }
}

filter {
        csv {
                separator => ","
                skip_header => true
                columns => ["id","content","genres"]
        }
        mutate {
                split => ["genres", "|"]
                remove_field => ["path", "message", "@timestamp", "host"]
        }
        mutate {
                split => ["content", "("]
                add_field => { "title" => "%{[content][0]}"}
                add_field => { "year" => "%{[content][1]}"}
        }
        mutate {
                convert => {
                        "year" => "integer"
                }
                strip => ["title"]
                remove_field => ["path", "host","@timestamp","message","content"]
        }
}

output {
        elasticsearch {
                index => "movies"
                document_id => "%{id}"
        }
        stdout { codec => rubydebug }
}
```

- file 读取文件，还支持网络等。
  - path：这里会有坑，如果启动后一直不读取数据，估计是路径出了问题，这里不支持中文，`-`, 老老实实放到一个简单的路径里，不知道下划线支不支持，自己试试
  - start_position：从文件开始的位置读，首次提取数据时，可以这么设置。
  - sincedb_path: logstash读取文件后，会记录文件已经读取的位置，重启也会从上次的位置开始，这里让他写入``/dev/null`,就可以每次从头开始方便调试。
- 从grouplens下载datasets是csv格式的，logstash默认值csv解析
  - autodetect_column_names: 默认是不自动解析表头
  - separator: csv分隔列是用`,`默认是`,`写不写都行
  - quota_char：csv 如果某一列有逗号的话，需要带上`"`表示引用格式，这里自带的csv插件是支持的。如果有解析错乱的话，不用考虑这个。
  - skip_header：跳过表头

- mutate: 修改数据，添加字段，移除字段等等

  - split：分割字段为数据
  - remove_field：移除一些没用的字段
  - strip：去掉空格, php trim() ,python strip()，golang strings.Trim()

- output：输出到哪

  - elasticsearch
    - index:指定索引
    - document_id：指定文档id，否则会默认生成

  - stdout：标准输出到控制台

# 运行

```shell
sudo ./bin/logstash -f logstash.conf
```

这里用sudo，是因为要写入到/dev/null, 需要root权限。

具体 elasticsearch、kibana是如果运行的，看 [elasticsearch docker的安装步骤](安装运行.md)

![](/images/blog/1/elasticsearch/assert/movies.png)

# logstash 同步mysql到elasticsearch

有需求需要把mysql中的数据同步到elasticsearch中, 按照每条记录的更新时间导入数据

[官方文档](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html)

## 安装

看一下logstash已经有的plugins，我当前用的是7.6.0默认已经有了input jdbc了

```
bin/logstash-plugin list
```

老版本需要安装插件：

```
bin/logstash-plugin install logstash-input-jdbc
```

下在java mysql连接驱动，jdbc_driver_library: [从这里下载最新的版本](https://mvnrepository.com/artifact/mysql/mysql-connector-java)

# 配置文件

```conf
input {
	jdbc {
		jdbc_validate_connection => true
		jdbc_driver_library => "/opt/driver/mysql-connector-java-5.1.36.jar"
		jdbc_driver_class => "com.mysql.jdbc.Driver"
		jdbc_connection_string => "jdbc:mysql://localhost:3306/videos_t"
		jdbc_user => "root"
		jdbc_password => "123456"
		# 定时器
		schedule => "*/1 * * * * "
		# 记录 sql_last_value 值的类型，支持: number / timestamp
		tracking_column_type => "timestamp"
		# 记录上一次查询是最后的值
		use_column_value => true
		tracking_column => "updated_at"
		# 时区 local 或 utc
		plugin_timezone="local"
		# sql_last_value logstash提供的预定义宏，上一次最后查询列的值，具体那一列由 tracking_column 指定
		statement => "select * from foo where updated_at > :sql_last_value"
		# 分页，防止首次导入的时候数据过多
		jdbc_paging_enabled => "true"
		jdbc_page_size => "10000"
	}
}

filter {
}

output {
	stdout { codec => json_lines }
}
```

这里的需要是按照更新时间导入数据，需要用到3个配置：

- `use_column_value`:  使用上一次查询的值，作为 sql_last_value
- `tracking_column`: 使用哪一列
- `tracking_column_type`: 该列值的类型，`numeric`, `timestamp`

> 如果是只有插入同步到elasticsearch的话，可以按id同步 `tracking_column => "id"`    `tracking_column_type => "numeric"`

- `statement` : sql语句，支持参数绑定的形式
- `prepared_statement_bind_values`: 数组，sql中绑定的参数

- `schedule`: 定时器，具体看cron的用法
- `plugin_timezone`: 按更新时间同步数据的话，一定要注意时区，默认是:utc,支持local和utc。否则每次查询条件都是差8个小时
- `jdbc_paging_enabled`: 分页，第一次同步几百万的话，一次查询数据量可能太多
- `jdbc_page_size`: 每页条数
- `logstash_jdbc_last_run`: 上一次同步的值, 默认在 `～/.logstash_jdbc_last_run`, 这个最后单独配置一下，一张表同步的话还可以，如果是多张的话就会用成同一个文件中的值

# 错误

这个错误用logstash(7.5.0)报的错，github issue作者提到新版已经修复这个问题所以上面用的版本是7.6.0

```error
com.mysql.jdbc.Driver not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library?
```



认识logstash


## 一、存储过程的创建和调用

-   存储过程就是具有名字的一段代码，用来完成一个特定的功能。
-   创建的存储过程保存在数据库的数据字典中。

**MYSQL 存储过程中的关键语法**

声明语句结束符，可以自定义:

```
DELIMITER $$
或
DELIMITER //
```

声明存储过程:

```
CREATE PROCEDURE demo_in_parameter(IN p_in int)       
```

存储过程开始和结束符号:

```
BEGIN .... END    
```

变量赋值:

```
SET @p_in=1  
```

变量定义:

```
DECLARE l_int int unsigned default 4000000; 
```

创建mysql存储过程、存储函数:

```
create procedure 存储过程名(参数)
```

存储过程体:

```
create function 存储函数名(参数)
```

### 实例

**存储过程体**

-   存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等
-   过程体格式：以begin开始，以end结束(可嵌套)

```
BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END
```

**注意：**每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块(又叫做复合语句compound statement)，则不需要分号。

为语句块贴标签:

```
[begin_label:] BEGIN
　　[statement_list]
END [end_label]
```

标签有两个作用：

-   1、增强代码的可读性
-   2、在某些语句(例如:leave和iterate语句)，需要用到标签

## 二、存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

```
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
```

-   IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
-   OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
-   INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

### 1、in 输入参数

```sql
mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;
 
mysql> set @p_in=1;
 
mysql> call in_param(@p_in);
```

### 2、out输出参数

```sql
mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;
 
mysql> set @p_out=1;
 
mysql> call out_param(@p_out);
```

### 3、inout输入参数

```sql
mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;
 
mysql> set @p_inout=1;
 
mysql> call inout_param(@p_inout);
```

**注意：**

1、如果过程没有参数，也必须在过程名后面写上小括号例：

```
CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……
```

2、确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理

**建议：**

-   输入值使用in参数。
-   返回值使用out参数。
-   inout参数就尽量的少用。

------

## 三、变量

### 1. 变量定义

局部变量声明一定要放在存储过程体的开始：

```
DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];
```

其中，datatype 为 MySQL 的数据类型，如: int, float, date,varchar(length)

例如:

DECLARE l_int int unsigned default 4000000;   DECLARE l_numeric number(8,2) DEFAULT 9.95;   DECLARE l_date date DEFAULT '1999-12-31';   DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';   DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

### 2. 变量赋值

```
SET 变量名 = 表达式值 [,variable_name = expression ...]
```

### 3. 用户变量

**在MySQL客户端使用用户变量:**

```sql
mysql > SELECT 'Hello World' into @x;  
mysql > SELECT @x;  
+-------------+  
|   @x        |  
+-------------+  
| Hello World |  
+-------------+  
mysql > SET @y='Goodbye Cruel World';  
mysql > SELECT @y;  
+---------------------+  
|     @y              |  
+---------------------+  
| Goodbye Cruel World |  
+---------------------+  
 
mysql > SET @z=1+2+3;  
mysql > SELECT @z;  
```

**在存储过程中使用用户变量**

```sql
mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');  
mysql > SET @greeting='Hello';  
mysql > CALL GreetWorld( );  
```

**在存储过程间传递全局范围的用户变量**

```sql
mysql> CREATE PROCEDURE p1()   SET @last_procedure='p1';  
mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);  
mysql> CALL p1( );  
mysql> CALL p2( );
```



**注意:**

-   1、用户变量名一般以@开头
-   2、滥用用户变量会导致程序难以理解及管理

------

## 四、注释

MySQL 存储过程可使用两种风格的注释

两个横杆**--**：该风格一般用于单行注释。

**c 风格**： 一般用于多行注释。

例如：

```sql
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc1 --name存储过程名  
     -> (IN parameter1 INTEGER)   
     -> BEGIN   
     -> DECLARE variable1 CHAR(10);   
     -> IF parameter1 = 17 THEN   
     -> SET variable1 = 'birds';   
     -> ELSE 
     -> SET variable1 = 'beasts';   
    -> END IF;   
    -> INSERT INTO table1 VALUES (variable1);  
    -> END   
    -> //  
mysql > DELIMITER ;
```



### MySQL存储过程的调用

用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。

### MySQL存储过程的查询

我们像知道一个数据库下面有那些表，我们一般采用 **showtables;** 进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是另一钟方式。

我们可以用以下语句进行查询：

```
selectname from mysql.proc where db='数据库名';

或者

selectroutine_name from information_schema.routines where routine_schema='数据库名';

或者

showprocedure status where db='数据库名';
```

**如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe 表名进行查看呢？**

**答案是：**我们可以查看存储过程的详细，但是需要用另一种方法：

```
SHOWCREATE PROCEDURE 数据库.存储过程名;
```

就可以查看当前存储过程的详细。

### MySQL存储过程的修改

```
ALTER PROCEDURE
```

更改用 CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

### MySQL存储过程的删除

删除一个存储过程比较简单，和删除表一样：

```
DROPPROCEDURE
```

从 MySQL 的表格中删除一个或多个存储过程。

### MySQL存储过程的控制语句

**(1). 变量作用域**

内部的变量在其作用域范围内享有更高的优先权，当执行到 end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。

```sql
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3()  
     -> begin 
     -> declare x1 varchar(5) default 'outer';  
     -> begin 
     -> declare x1 varchar(5) default 'inner';  
      -> select x1;  
      -> end;  
       -> select x1;  
     -> end;  
     -> //  
mysql > DELIMITER ;
```

**(2). 条件语句**

1.  if-then-else 语句

```sql
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;
```

2.  case语句

```sql
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3 (in parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> case var  
     -> when 0 then   
     -> insert into t values(17);  
     -> when 1 then   
     -> insert into t values(18);  
     -> else
     -> insert into t values(19);  
     -> end case;  
     -> end;  
     -> //  
mysql > DELIMITER ; 
case
    when var=0 then
        insert into t values(30);
    when var>0 then
    when var<0 then
    else
end case
```

**(3). 循环语句**

```sql
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ;
```

```
while 条件 do
    --循环体
endwhile
```



想实践一下mysql的关键特性，没有数据，不想写脚本，怎么用mysql造数据呢，

mysql存储过程语法



```sql
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

select city,name,age from t where city='杭州' order by name limit 1000;
```



# 全字段排序

排序可能在内存中完成，也可能使用使用外部排序

`sort_buffer_size`

-   如果排序的数据量小于sort buffer size，排序在内存中完成，

-   如果排序数据量大，内存放不下时，就要使用外部排序，一般是归并排序， 将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。

执行过程

1.  初始化sort_buffer，确定放入name、city、age这三个字段
2.  从索引city找到第一个满足city='杭州’条件的主键id
3.  到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中
4.  从索引city取下一个记录的主键id
5.  重复步骤3、4直到city的值不满足查询条件为止
6.  对sort_buffer中的数据按照字段name做快速排序
7.  按照排序结果取前1000行返回给客户端

# rowid排序

如果字段太多，排序的单行长度太大，mysql回采用row id来排序。只把需要排序的字段放入sort buffer，然后根据id的值回到原表取其他的字段

`SET max_length_for_sort_data = 16;` 可以控制排序字段的长度

执行过程

1.  初始化sort_buffer，确定放入两个字段，即name和id
2.  从索引city找到第一个满足city='杭州’条件的主键id
3.  到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中
4.  从索引city取下一个记录的主键id
5.  重复步骤3、4直到不满足city='杭州’条件为止
6.  对sort_buffer中的数据按照字段name进行排序
7.  遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户

# 排序优化

**联合索引**: city取出来的数据是天然排好序的，就不需要在排序了, 创建一个city,name的联合索引

```sql
alter table t add index city_user(city, name);
```

执行过程：

1.  从索引(city,name)找到第一个满足city='杭州’条件的主键id；
2.  到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；
3.  从索引(city,name)取下一个记录主键id；
4.  重复步骤2、3，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束

**索引覆盖**: age，就不需要回主键索引查age了

```sql
alter table t add index city_user(city, name, age);
```

执行过程：

1.  从索引(city,name,age)找到第一个满足city='杭州’条件的记录，取出其中的city、name和age这三个字段的值，作为结果集的一部分直接返回；
2.  从索引(city,name,age)取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
3.  重复执行步骤2，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束。

# 取随机值

总行数：

```sql
select count(*) as c from t;
```

随机数：

```php
$offset = mt_rand(1, c)
```

跳过offset取1条

```sql
select * from t limit :offset, 1;
```

这样要比`order by rand()` 效率高，且随机性也比较好；

排序模式


# 安装

**mac**

mac直接使用brew安装就好，brew请求的github，慢的话可以ss开启全局模式
```shell
$ brew install webp
```

**centos7**
因为是png、gif、jpeg转webp，所以这里需要libpng,libjpeg,giflib

libpng,libjpeg
```shell
sudo yum install libjpeg-turbo-devel libjpeg-turbo libpng-devel -y
```

webp还需要依赖LibTIFF, [http://download.osgeo.org/libtiff/](http://download.osgeo.org/libtiff/)这里可以下载最新的安装包
```shell
$ cd /opt
$ wget http://download.osgeo.org/libtiff/tiff-4.1.0.tar.gz
$ tar -zxvf tiff-4.1.0.tar.gz && cd tiff-4.1.0
$ ./configure
$ make && make install 
```

giflib安装, 这里可以获取最新的giflib [https://sourceforge.net/projects/giflib/files/latest/download](https://sourceforge.net/projects/giflib/files/latest/download)
```shell
$ cd /opt
$ wget https://nchc.dl.sourceforge.net/project/giflib/giflib-5.2.1.tar.gz
$ tar -zxvf giflib-5.2.1.tar.gz
$ cd giflib-5.2.1
$ make && make install
```

libwebp安装, 从github获取即可 [http://www.linuxfromscratch.org/blfs/view/svn/general/libwebp.html](http://www.linuxfromscratch.org/blfs/view/svn/general/libwebp.html)
```shell
$ cd /opt
$ wget https://github.com/webmproject/libwebp/archive/v1.0.3.tar.gz
$ tar -zxvf v1.0.3.tar.gz
$ cd v1.0.3
$ ./configure --prefix=/usr           \
            --enable-libwebpmux     \
            --enable-libwebpdemux   \
            --enable-libwebpdecoder \
            --enable-libwebpextras  \
            --enable-swap-16bit-csp \
            --disable-static
$ make && make install
```

安装完libwebp之后，执行dwebp或者cwebp命令报 dwebp: error while loading shared libraries: libwebpdemux.so.2: cannot open shared object file: No such file or directory 错误的解决办法：
```shell
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib:/usr/lib
echo "export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib:/usr/lib" >> ~/.bashrc
```

添加环境变量： 

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
echo $LD_LIBRARY_PATH
echo "export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib" >> ~/.bashrc



# libwebp提供的功能

**cwebp**

compresses an image using the WebP format.

**dwebp**

decompresses WebP files into PNG, PAM, PPM or PGM images.

**gif2webp**

converts a GIF image to a WebP image.

**vwebp**

decompress a WebP file and display it in a window.

**webpmux**

creates animated WebP files from non-animated WebP images, extracts frames from animated WebP images, and manages XMP/EXIF metadata and ICC profile.

**libwebp.so**

contains the API functions for WebP encoding and decoding.


这里主要是用到cwebp的功能
```shell
$ cwebp
Usage:

   cwebp [options] -q quality input.png -o output.webp

where quality is between 0 (poor) to 100 (very good).
Typical value is around 80.
```

我这里测试了一下quality 10 80 100的效果，当前使用webp的主要目的是为了减小图片的大小节省流量。



## quality 10
**stack.png**
![](/images/blog/1/linux/assert/stack.10.webp)

**stack.10.webp**

```shell
$ cwebp -q 10 stack.png -o stack.10.webp
$ Desktop ll stack.*
-rw-r--r--  1 douyacun  staff   8.3K 12  1 23:09 stack.10.webp
-rw-r--r--@ 1 douyacun  staff   127K 12  1 23:08 stack.png
```
可以看到减少了118.7k, 然后在看下效果:
![](/images/blog/1/linux/assert/stack.png)


可以看到10的这个效果也是非常吃惊，像一些清晰度要求不高的场景使用webp绝对超值

**stack.80.webp**
```shell
$ cwebp -q 80 stack.png -o stack.80.webp
Saving file 'stack.80.webp'

$ ll stack.*
-rw-r--r--@ 1 douyacun  staff   8.3K 12  1 23:09 stack.10.webp
-rw-r--r--@ 1 douyacun  staff    19K 12  1 23:15 stack.80.webp
-rw-r--r--@ 1 douyacun  staff   127K 12  1 23:08 stack.png
```

![](/images/blog/1/linux/assert/stack.80.webp)

quality 10和80的区别也只是一些颜色上的区别，没有出现锯齿的效果，而且80也是只有19k

**stack.100.webp**

```shell
$ cwebp -q 100 stack.png -o stack.100.webp
Saving file 'stack.100.webp'
-rw-r--r--@ 1 douyacun  staff   8.3K 12  1 23:09 stack.10.webp
-rw-r--r--@ 1 douyacun  staff    36K 12  1 23:20 stack.100.webp
-rw-r--r--@ 1 douyacun  staff    19K 12  1 23:15 stack.80.webp
-rw-r--r--@ 1 douyacun  staff   127K 12  1 23:08 stack.png
```

100压缩有36K
![](/images/blog/1/linux/assert/stack.100.webp)

图片转webp


# 页面缓存

查看页面缓存：
```shell
[www@00h ~]$ cat /proc/meminfo
MemTotal:        1882236 kB
MemFree:           75952 kB
MemAvailable:      58212 kB
Buffers:           14576 kB
Cached:           136732 kB
SwapCached:            0 kB
Active:          1630800 kB
Inactive:          79352 kB
Active(anon):    1584460 kB
Inactive(anon):    34188 kB
Active(file):      46340 kB
Inactive(file):    45164 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:             0 kB
SwapFree:              0 kB
Dirty:                84 kB  => 写入缓存页，Dirty的值会增加
Writeback:             0 kB  => 缓存页开始写入硬盘，Writeback值会增加直到结束
...
```

## [脏页](#dirty_page)

硬盘的读写速度远源跟不上内存的速度，内核会把读写比较频繁的数据事先放到内存中，以提高读写速度减少I/O操作，这叫高速缓存，
linux是以页作为高速缓存的单位，当进程修改了高速缓冲中的数据时，该页就被称为脏页

## 延迟写

当将数据写入文件时，内核通常先将数据复制页面高速缓存，如果该缓冲区没有写满，并不将其写入到输出队列，而是等待其写满或者当内核需要重用这块缓冲区以便存放其他磁盘块数据时，
在将该缓冲区排入到输出队列，待其到达队首时，才进程实际的I/O操作，这种输出方式被称为延迟写

## 内存映射(mmap)

mmap用于把文件映射到内存空间中，简单说mmap就是把一个文件的内容在内存里面做一个映像。映射成功后，用户对这段内存区域的修改可以直接反映到内核空间，同样，内核空间对这段区域的修改也直接反映用户空间。那么对于内核空间<---->用户空间两者之间需要大量数据传输等操作的话效率是非常高的。

# 页面缓存后，如何访问数据？

1. 内核开始读时，先检查高速缓存中是否有该数据
2. 如果有，命中缓存，没有直接从磁盘读取
3. 内核调度块I/O，从磁盘中读取全部数据，将全部数据放入到页高速缓冲中


# 写缓冲

内核从磁盘读取数据时，如果从页高速缓冲中没有读取到的话，会直接从磁盘中读取数据并将数据存入页高速缓存中，保证下次使用，写缓存策略：
- 直接I/O，不经过页高速缓存，会增大磁盘I/O开销
- 写透缓存，写操作更新内存缓存，同时更新磁盘文件，可以保持缓存一致性
- 回写，程序执行写操作直接将数据写入到缓存中，然后将被写入的页面标记为脏页，加入脏页链表中，[回写进程](/linux/页缓存.md#pdflush)会将脏页刷新到磁盘中,最终保持数据一致性

# [缓存回收(pdflush)](#pdflush)
缓存回收，清除页的数据，为更重要的数据提供坑位，收缩缓存大小减小内存压力
linux回收策略：

- LRU（最近最少使用）
- 双链，分活跃链表/非活跃链表

pdflush写入到硬盘主要看两个参数：
1. 数据在页缓冲中是否超过30秒，如果是标记为脏页
2. 脏页缓存是否达到工作内存的100%

以下也会影响pdflush线程：
- 用户进程调用[sync/fsync/fdatasync](/article/5346fecbca9c98ddeb6290c06a5a9a36)
- `/proc/sys/vm/dirty_ratio`: 默认30，总内存的百分比，系统所能拥有的最大脏页缓存总量
- `/proc/sys/vm/dirty_background_ratio`: 默认 10， 如果需要把缓存持续的而不是一下子大量的写入磁盘，降低这个值

pdflsuh 受 `/proc/sys/vm/` 下的参数控制：
```
[root@00h www]# ll /proc/sys/vm/|gawk '{print $9}'
admin_reserve_kbytes
block_dump
compact_memory 
dirty_background_bytes
dirty_background_ratio (10) 判断脏页缓存是否到了最大值 内存的%
dirty_ratio（30） 总内存的最大百分比，超过这个值开启pdflush写入硬盘，如果cache大于pdflush，会遇到I/O瓶颈，所有的I/O都要等待cache被pdflush进磁盘, 业务繁忙建议5-10%
dirty_bytes
dirty_expire_centisecs (3000) 缓存页里的过期时间，默认30秒
dirty_writeback_centisecs (500) 单位（1/100秒）多长时间唤醒pdflush将缓存页写入硬盘，默认：2个线程/5秒
drop_caches
extfrag_threshold
hugepages_treat_as_movable
hugetlb_shm_group
laptop_mode
legacy_va_layout
lowmem_reserve_ratio
max_map_count
memory_failure_early_kill
memory_failure_recovery
min_free_kbytes 保留内存的值
min_slab_ratio
min_unmapped_ratio
mmap_min_addr
mmap_rnd_bits
mmap_rnd_compat_bits
nr_hugepages
nr_hugepages_mempolicy
nr_overcommit_hugepages
nr_pdflush_threads 查看多少个pdflush进程
numa_zonelist_order
oom_dump_tasks
oom_kill_allocating_task
overcommit_kbytes
overcommit_memory
overcommit_ratio
page-cluster
panic_on_oom
percpu_pagelist_fraction
stat_interval
swappiness (0) linux倾向于从物理内存映射到硬盘缓存，保持硬盘缓存尽可能大。未用的页缓存会被放进swap区, 少用swapping会增加程序的响应速度；多用swapping将会提高系统的可用性。
    - 0 将会避免使用swapping
    - 100 尽量使用swapping
user_reserve_kbytes
vfs_cache_pressure
zone_reclaim_mode
```


- dirty_ratio 单位百分比，表示当写缓冲内存占用内存多少的时候，开始向磁盘写数据，增大会使更多的系统内存用于磁盘写缓冲，可以极大的提高系统的写性能, 但是当你需要持续、恒定的写入场合时，应该降低其值
- dirty_background_ratio 控制pdflush进程，在何时刷新磁盘，单位百分比，pdflush用于将内存中的内容和文件系统进行同步，每当内存中的垃圾页（dirty page）超过10%的时候,pdflush就会将这些页面备份回硬盘.增大之会使用更多系统内存用于磁盘写缓冲，也可以极大提高系统的写性能。但是，当你需要持续、恒定的写入场合时，应该降低其数值， 配合dirty_ratio
- min_free_kbytes: 文件系统数据缓冲需要频繁的内存分配。加大保留内存的值能提升系统速度和稳定。小于8G的内存，保留内存为64M，大于8G的设置为256M
```
echo 65536 > /proc/sys/vm/min_free_kbytes
```
- dirty_writeback_centisecs: 控制pdflush线程的运行间隔，单位时1/100秒，默认5秒（500），如果是持续的写入操作，降低这个值比较好，可以把剑锋的写操作削平成多次写操作，该参数的设置应该小于dirty_expire_centisecs, 也不能太小，太小I/0太频繁, 1:6 (dirty_expire_centisecs  : dirty_writeback_centisecs )的比例比较好。
- dirty_expire_centisecs: 声明Linux内核写缓冲区里面的数据多“旧”了之后，pdflush进程就开始考虑写到磁盘中去。单位是 1/100秒。缺省是 30000，也就是 30 秒的数据就算旧了，将会刷新磁盘。对于特别重载的写操作来说，这个值适当缩小也是好的，但也不能缩小太多，因为缩小太多也会导致IO提高太快。
- min_free_kbytes: VM最低保留多少空闲内存（Kbytes）



理解一下Linux的是如何处理脏页的

linux页高速缓存_脏页_延迟写_I/O内核参数优化_pdflush

start transaction以后事务是否真正启动了? MVCC是什么? 可重复读是如何实现的? 为啥RR级别要比RU级别效率高？

innodb事务详解


innodb是索引存储表，表数据按主键顺序存放

[toc]

# 基础概念

聚簇索引、辅助索引、联合索引

## 聚簇索引

聚集索引按照每张表的主键构造一棵B+树，叶子节点存放的是整张表的行记录数据，叶子节点被称为数据页。

每张表只能按照一颗B+树排序，每张表只能有一个聚集索引

聚集索引对于主键的排序查找和范围查找速度非常快

没有主键或唯一索引，innodb默认会生成一个行号(不可见)，作为聚集索引的主键

## 辅助索引

辅助索引叶子节点不会包含行记录的所有数据。叶子节点除了包含键值以外还会包含主键，来查找叶子节点以外的其它数据。

辅助索引通过主键id来关联记录

## 联合索引

对表上的多个列进行索引

联合索引的技巧

1. 覆盖索引：从索引即可查询到所需字段，不需要查询聚集索引中的记录，减少io操作。

2. 最左前缀：对a,b创建索引

   - 可以用到索引
     - where a = 1;
     - where a = 1 and b = 2;
     - where a = 1 order by b;

   - 用不到索引
     - where b = 2;

3. 索引下推：a like 'hello%’ and b >10，检索出来的数据会先过滤掉b <= 10 的数据，然后在进行回表查询，减少回表查

## MRR(multi-range read)

目的： 减少磁盘的随机访问，将随机访问变为顺序的数据访问。

MRR执行过程：

- 优化器将二级索引查询到的记录放到一块缓存中
- 如果二级索引扫描到文件的末尾或者缓冲区已满，使用快排对缓冲区中的内容按照住建进行排序
- 用户线程调用MRR接口取cluster index, 然后根据cluster index取行数据
- 当根据缓冲区中的cluster index取完数据，继续调用2、3过程，直至扫面结束 

MRR好处：

- 使数据访问变得较为顺序，在查询辅助索引时，首先根据得到的结果，按照主键排序，
- 减少缓冲池中页被替换的次数
- 批量处理对键值的查询

explain 可以看到extra列 using MRR

- [MySQL · 特性分析 · 优化器 MRR & BKA](http://mysql.taobao.org/monthly/2016/01/04/)

## ICP

mysql 5.6开始支持，将where的过滤部分放到存储层

```sql
select * from salaries
where (from_date between "2019-01-10" and "2019-02-10") and (salaries between 3800 and 4000)
```

不启用ICP，则数据需要先取出from_date between 2019-01-10 and 2019-02-10的数据，然后再过滤 3800-4000的数据

启用ICP，则会在索引取出时就会进行where过滤，前提是where条件被索引覆盖

explain 可以看到extra列 using index condition

- [MySQL · 特性分析 · Index Condition Pushdown (ICP)](http://mysql.taobao.org/monthly/2015/12/08/)

# 疑难杂症

## 普通索引和唯一索引应该怎么选择？

例如身份证号，身份证号具有唯一性，由于身份证号比较大，不建议把身份证号作为主键，要么给身份证号加一个唯一索引，要么加一个普通索引。如果业务代码已经保证不会写入重复的身份证号，那么是选择普通索引还是唯一索引比较好？

查询过程：

- 对于普通索引，查找到第一个满足条件的索引后，需要继续查找下一个记录，知道碰到第一个不满足条件的记录
- 对于唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录后，会停止继续检索

这点查询性能影响很小，可以忽略，所以唯一索引和普通索引查询性能相差不多(别钻牛角尖，如果是字段重复度高，这种情况不符合我们讨论的场景)。

更新过程：

​	对于插入一条(4,500)

- 这个记录要更新的目标页在内存中
  - 唯一索引，找到3和5之间的位置，判断没有冲突值，插入这个值，语句结束；
  - 普通索引，找到3和5之间的位置，插入这个值，语句结束
- 这个记录要更新的目标页没有在内存中
  - 唯一索引，需要将目标页读入到内存中，判断没有冲突，插入这个值，语句结束
  - 普通索引，将更新记录在change buffer中，语句执行结束

数据从磁盘读入到内存涉及到随机I/O的访问，是数据库成本最高的操作之一。change buffer减少了随机磁盘访问。对于提升更新性能很明显

## change buffer

**什么条件下可以使用change buffer？**

对于唯一索引，所有的更新操作都要先判断这个操作是否违反唯一约束，要判断值是否就必须先把数据页读入到内存中才能判断，如果已经读入到内存中，那直接更新内存会更快，没必要使用change buffer了

唯一索引更新就不能使用change buffer了，实际上只有普通索引可以使用

**change buffer的使用场景？**

- change buffer对于更新过程有加速作用
- merge是数据真正更新的时刻，在一个数据库做merge之前，change buffer记录的越多，收益越大。写多读少的场景change buffer使用效果最好。
- 如果一个业务的更新模式写入之后马上做查询，将更新记录在了change buffer中，但之后由于马上要访问这个数据页，马上会触发merge，反而增加change buffer的维护成本。

**change buffer和redo log**

change buffer主要是在更新时，不用将数据读入到内存中，节省的是随机读磁盘的I/O消耗。

redo log主要节省的是随机写磁盘的I/O消耗（顺序写）

## 如何重建索引和主键索引？

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，造成空间浪费

```sql
alter table T engine=InnoDB;
```

## 字符串如何创建索引?

**前缀索引**

定义好长度，可以做到既节省空间，又不用额外增加太多的查询成本。

例如邮箱，对于每个记录都是只取前6个字节

```sql
alter table user add index index2(email(6));
```

- 占用的空间更小
- 增加额外的扫描次数
- 用不上覆盖索引对查询性能的优化，需要回主键索引查一次

```sql
select id, email from SUser where email='douyacun@gmail.com';
```

当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？

```sql
select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from user;
```

**倒序存储**

例如：身份证号前面的几位是地址码重复度高，这个索引的区分度就非常低了。按照前缀索引来创建的索引需要创建12位以上，才能满足区分度要求。

存储身份证号的时候把它倒过来存，每次查询的时候，可以这么写：

```sql
select field_list from t where id_card = reverse(:id_card);
```

**hash字段**

使用crc32函数得到校验码使用整型存储，只在这个列上加索引，索引空间就会小很多。但是CRC32函数很容易碰撞，需要加上原条件判断

```sql
select field_list from t where id_card_crc = crc32(:id_card) and id_card = :id_card;
```

普通索引和唯一索引应该怎么选择？字符串如何加索引才能更高效并且节省空间？

mysql索引


# 安装

下载地址: [https://www.percona.com/downloads/percona-toolkit/LATEST/](https://www.percona.com/downloads/percona-toolkit/LATEST/)

官方文档: [https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html](https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html)

下载依赖包：

```
yum -y install perl-Digest-MD5 perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL
```

# 执行

增加一列：

```
/bin/pt-online-schema-change --print --execute --alter "ADD COLUMN test varchar(255) NOT NULL DEFAULT '' COMMENT '测试pt' AFTER status" D=videos_t,t=media_copy1,u=root,p=123456
```

看下执行过程:

```shell
[root@douyacun percona-toolkit-3.1.0]# ./bin/pt-online-schema-change --print --execute --alter "ADD COLUMN test varchar(255) NOT NULL DEFAULT '' COMMENT '测试pt' AFTER status" D=videos_t,t=media_copy1,u=root,p=123456
No slaves found.  See --recursion-method if host home has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `videos_t`.`media_copy1`...
Creating new table...
CREATE TABLE `videos_t`.`_media_copy1_new` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `subtype` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '分类分类',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '标题',
  `subject` bigint(19) unsigned DEFAULT NULL COMMENT '豆瓣subject，唯一索引',
  `source` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '1: 豆瓣 2: 百度百科 …',
  `torrent_num` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '种子数量',
  `tags` varchar(255) NOT NULL COMMENT '标签',
  `original_title` varchar(500) NOT NULL DEFAULT '' COMMENT '原标题',
  `directors` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '导演',
  `casts` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ' 演员阵容',
  `genres` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '类型',
  `released` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '上映时间',
  `released_timestamp` timestamp NULL DEFAULT NULL,
  `alias` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '别名',
  `summary` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '摘要',
  `rate` float(3,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '豆瓣得分',
  `duration` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '片长',
  `region` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '地区',
  `language` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '语言',
  `official_website` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '官网',
  `poster` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '海报',
  `cover` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '视频封面',
  `new_cover` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '解析下载以后的封面',
  `current_season` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新集数',
  `episodes_update` int(11) unsigned NOT NULL DEFAULT '0',
  `episodes_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '总集数',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `status` tinyint(3) NOT NULL DEFAULT '0' COMMENT '状态',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `med` (`subject`,`source`) USING BTREE,
  KEY `idx_title` (`title`) USING BTREE,
  KEY `idx_torrent_num` (`torrent_num`),
  KEY `idx_rate` (`rate`) USING BTREE,
  KEY `idx_created_at` (`created_at`) USING BTREE,
  KEY `idx_tags` (`tags`),
  KEY `idx_subtype_genres` (`subtype`,`genres`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=194664 DEFAULT CHARSET=utf8 COMMENT='视频库'
Created new table videos_t._media_copy1_new OK.
Altering new table...
ALTER TABLE `videos_t`.`_media_copy1_new` ADD COLUMN test varchar(255) NOT NULL DEFAULT '' COMMENT '测试pt' AFTER status
Altered `videos_t`.`_media_copy1_new` OK.
2020-01-15T21:59:11 Creating triggers...
2020-01-15T21:59:11 Created triggers OK.
2020-01-15T21:59:11 Copying approximately 167063 rows...
INSERT LOW_PRIORITY IGNORE INTO `videos_t`.`_media_copy1_new` (`id`, `subtype`, `title`, `subject`, `source`, `torrent_num`, `tags`, `original_title`, `directors`, `casts`, `genres`, `released`, `released_timestamp`, `alias`, `summary`, `rate`, `duration`, `region`, `language`, `official_website`, `poster`, `cover`, `new_cover`, `current_season`, `episodes_update`, `episodes_count`, `created_at`, `updated_at`, `status`) SELECT `id`, `subtype`, `title`, `subject`, `source`, `torrent_num`, `tags`, `original_title`, `directors`, `casts`, `genres`, `released`, `released_timestamp`, `alias`, `summary`, `rate`, `duration`, `region`, `language`, `official_website`, `poster`, `cover`, `new_cover`, `current_season`, `episodes_update`, `episodes_count`, `created_at`, `updated_at`, `status` FROM `videos_t`.`media_copy1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 24862 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `videos_t`.`media_copy1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Copying `videos_t`.`media_copy1`:  32% 01:03 remain
Copying `videos_t`.`media_copy1`:  61% 00:36 remain
Copying `videos_t`.`media_copy1`:  86% 00:13 remain
2020-01-15T22:00:55 Copied rows OK.
2020-01-15T22:00:55 Analyzing new table...
2020-01-15T22:00:55 Swapping tables...
RENAME TABLE `videos_t`.`media_copy1` TO `videos_t`.`_media_copy1_old`, `videos_t`.`_media_copy1_new` TO `videos_t`.`media_copy1`
2020-01-15T22:00:56 Swapped original and new tables OK.
2020-01-15T22:00:56 Dropping old table...
DROP TABLE IF EXISTS `videos_t`.`_media_copy1_old`
2020-01-15T22:00:57 Dropped old table `videos_t`.`_media_copy1_old` OK.
2020-01-15T22:00:57 Dropping triggers...
DROP TRIGGER IF EXISTS `videos_t`.`pt_osc_videos_t_media_copy1_del`
DROP TRIGGER IF EXISTS `videos_t`.`pt_osc_videos_t_media_copy1_upd`
DROP TRIGGER IF EXISTS `videos_t`.`pt_osc_videos_t_media_copy1_ins`
2020-01-15T22:00:57 Dropped triggers OK.
Successfully altered `videos_t`.`media_copy1`.
```

**详细执行流程如下：**

1. 相关环境参数检查
2. 检查该表格是否存在
3. show create table media_copy1
4. create table _media_copy1_new
5. alter table _media_copy1_new 
6. 创建删除触发器: `pt_osc_videos_t_media_copy1_del` , (如果数据修改的时候，还没有拷贝过来，修改后再拷贝则是覆盖，正确；如果是已经拷贝过来，再修改，也是正确，这里同时会检查是否具有主键或者唯一索引，如果都没有，这一步会报错，提示The new table `videos_t`.`_media_copy1_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.)
7. 创建更新触发器：`pt_osc_videos_t_media_copy1_upd`
8. 创建插入触发器：`pt_osc_videos_t_media_copy1_ins`
9. 按块拷贝数据到新表，拷贝过程对数据行持有S锁
10. analyze 新表
11. rename 表名，`RENAME TABLE videos_t.media_copy1 TO videos_t._media_copy1_old, videos_t._media_copy1_new TO videos_t.media_copy1`
12. 删除旧表
13. 删除新表上的删除、更新、插入 触发器

**DSN 选项**

- A 字符集设置
- D 需要Alter的表格是在哪个数据库，指定数据库
- F mysql read default file，如果数据源的相关选项存储在文件中，则通过 F 来指定
- h host，数据库主机名或IP
- p password，数据库用户的密码
- P port，数据库实例端口号
- S socket，实例socket文件
- t 表格名
- u 用户名

**从库延迟情况**

- --max-lag：默认1s，检查从库延迟情况，如果超过，则停止copy data，休息--check-interval秒后，再重新开始copy数据
- --check-interval：从库延迟超过指定的--max-lag，中断copy data休息的时间
- --recursion-method：MASTER寻找SLAVE的方式
  - processlist: 使用show processlist查找从库
  - hosts: 不是使用默认端口号3306，那么使用hosts方式来查找从库会更可靠
  - dsn: 手动在需要DDL的数据库内，创建 dsns 表格, pt会从表各种读取从库信息
    - `CREATE TABLE dsns (id int(11) NOT NULL AUTO_INCREMENT,parent_id int(11) DEFAULT NULL,dsn varchar(255) NOT NULL, PRIMARY KEY (id))`
    - 存储从库信息
    - insert into dsns(dsn) values(h=slave_host,u=repl_user,p=repl_password,P=port );
    - 该参数使用的时候，按以下格式（假设 dsns表格建立在数据库 dbosc）
  - none：不查找从库
- --check-slave-lag

**执行选项**

- --execute : 确定执行 alter 操作，如果不指定的话，只会做安全检查操作，类似于`nginx -t`
- --alter : 不包含前面的 `alter table t`

**打印选项**

- --print： 详细打印alter过程，不指定的时候，简略打印
- --statistics： 增加影响行数打印，可以查看copy进度

**服务器负载**

- --max-load： 
  - 默认为Threads_running=25
  - 每个chunk拷贝完后，会检查SHOW GLOBAL STATUS的内容，检查指标是否超过了指定的阈值,超过了就暂停

- --critical-load:
  - 默认为Threads_running=50
  - 类似`--max-load`,如果超过指定值，则工具直接退出，而不是暂停。

# 安全

- 大部分情况下，pt会拒绝给没有主键和唯一键的表做变更
- 如果发现影响主从超时厉害，max-lag参数要配置一下
- 如果发现服务器负载非常高，也会暂停或停止相关操作。`-—max-load`, `--critical-load`要配置一下
- 默认会设置 innodb_lock_wait_timeout=1 和 lock_wait_timeout=60来减少竞争
- 如果有外键约束，那么禁止改表，指定 —alter-foreign-keys-method.

# 使用限制

1. 禁止创建唯一索引，会丢失数据，更加不允许添加 --alter-check=no，--check-unique-key-change=no  
2. 如果原表没有主键，或者也没有唯一索引，这些表是不允许用pt做DDL的  
3. 禁止对外键的表进行pt ddl
4. 禁止对表进行重命名
5. 禁止对列进行重命名，如果一定要做，也必须先print出来检测清楚列名是否正确  
6. 新增字段，NOT NULL必须要指定默认值
7. 不允许删除主键


热表、大表怎么变更表结构？虽然知道pt，但是真正用过吗，有啥限制知道吗？

pt-online-scheme-change使用


今天是2020年1月16号，记录这周发生的一件充值事故，1月12号周一下班后被通知某家代理商出现消耗金额超过充值金额800多万，当时第一反应是计费又出问题了，要帮忙定位问题，在家连zoom，翻看整个代理商从开户到现在每个月的充值数据，然后查询代理商每个月月月初的账户数据快照（实际每天晚上23:59都会快照一份存在hive中），一直到了晚上12点半，一直没有定位到问题出现的原因，然后领导就下线睡觉了，这个问题也出现一段时间不急于这一天了。

1月17号，大家都在吐槽年底了，为啥比平时还忙。针对昨天的bug，领导去支持了，我这边手上有两个大的功能点需要迭代上线。晚上回家后，被通知问题出现平台充值这边，不是计费的问题，头大，通过充值数据和消耗数据对比发现11月之前的正常的，12月开始出现消费大于充值金额；问题出现在11月份，还是完全没有思路，因为还是没有证据确定是充值问题还是计费问题，后续还是在翻看一直到12点半。

1月18号，下午定了一个会议室，专门来查这件事情，从代理商充值开始到12月1号00:00充值金额 - 12月1号00:00代理商下所有广告主的消耗金额 - 12月1号00:00代理商剩余金额 - 12月1号00:00代理商下广告主剩余金额。发现差值有：8486863.50元，这个时候可以错略确定的是充值问题，计费不会出现这么大的问题，而且19年1月份计费没有出现过重大bug。翻看代理商的充值记录有一条:8525163.53, 数值相近。不会是巧合，而且充值一般都是整数，问题出现了。

记得当时代理商是要预充值3000万，而数据库充值记录的金额字段是用的int(11), 单位：分，最多能存2147483647，所以当时后来又给代理商补了8525163.53，按道理这会应该it

也没有问题，问题出现账户表上，充值记录金额是有符号的，而账户余额是unsigned无符号的, 最大值到4294967295，是能存下3000万的。虽然充值记录显示2147483647，但实际账户确实是充值了3000万。

**充值是有事务的，为啥没有回滚？如何避免这种情况的发生**

MySQL默认情况下，当一个数据超出定义列的数据类型的范围时，数据会以数据类型所充许的最大值存储。不会出现报错，事务没有回滚。

在这种情况下，我们希望在插入或更新字段时，如果插入或更新的值超出定义的字段数据类型的范围时，提出报错，禁示修改数据，通过修改全局变量sq_mode   `STRICT_ALL_TABLES` 实现

```
[SQL] insert into t6(`num`) value(2147483648);
[Err] 1264 - Out of range value for column 'num' at row 1
```

**充值没有监控吗？为啥会出现这么重大bug**

功能版本迭代，开始建表是int，后续有种改不动的感觉。大家都开始回避这个棘手问题。直至出现这样的问题，所以监控是非常必要的，代理商自助充值的功能也是在出现问题增加的监控，这是第二次出现充值问题。

**总结**， 经历两次充值事故后了以下几点：

- 充值类型：`big int`, `decimal`，其他类型就不要考虑了，留坑而已
- 充值sql_mod的session连接，一定要设置成为 `STRICT_ALL_TABLES`
- 充值记录要完善，充值记录，账户充值后快照记录，多写一份数据备份记录，意味着可以更快的定位问题
- 监控，对充值逻辑的反向验证，这个根据具体业务实现逻辑来。走社会主义特色道路。
- 数据快照，公司重要数据一定要做每日快照，像这次如果没有代理商账户的每日快照数据备份，即便定位到了问题，也没有证据充分证明这件事，追账打官司复杂多就高了。

最后这两点不管是用在哪里都合适，就是因为用在哪里都合适所以很难重视起来，只有吃过亏的才知道。

最近公司审计发现某家代理商的消耗数据要比充值金额多出来了几百万？