
# 页面缓存

查看页面缓存：
```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`
- 充值记录要完善，充值记录，账户充值后快照记录，多写一份数据备份记录，意味着可以更快的定位问题
- 监控，对充值逻辑的反向验证，这个根据具体业务实现逻辑来。走社会主义特色道路。
- 数据快照，公司重要数据一定要做每日快照，像这次如果没有代理商账户的每日快照数据备份，即便定位到了问题，也没有证据充分证明这件事，追账打官司复杂多就高了。

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

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

int导致的充值事故报告


家里一台联想笔记本作为爬虫服务器，有一台公网阿里云服务器，目的爬虫跑完之后，公网服务器可以主从同步，家里的服务器和公网服务器通信的问题通过[内网穿透工具(frp)](https://github.com/fatedier/frp)解决, 优秀简单，具体可以看[frp内网穿透_mysql服务_ssh服务_http服务](/linux/frp内网穿透_mysql服务_ssh服务_http服务.md)

MYSQL主从同步是在MySQL主从复制(Master-Slave Replication)基础上实现的，通过设置在Master MySQL上的binlog(使其处于打开状态)，Slave MySQL上通过一个I/O线程从Master MySQL上读取binlog，然后传输到Slave MySQL的中继日志中，然后Slave MySQL的SQL线程从中继日志中读取中继日志，然后应用到Slave MySQL的数据库中。这样实现了主从数据同步功能。

# 配置

## 主库

1. 配置
```conf
# 日志文件名
log-bin = mysql-bin

# 主数据库端ID号
server-id = 1

# 不同步哪些数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema

# 只同步哪些数据库，除此之外，其他不同步
binlog-do-db = game

# 日志保留时间
expire_logs_days = 10

# 控制binlog的写入频率。每执行多少次事务写入一次
# 这个参数性能消耗很大，但可减小MySQL崩溃造成的损失
sync_binlog = 5

# 日志格式，建议默认mixed
# statement 保存SQL语句
# row 保存影响记录数据
# mixed 前面两种的结合
binlog_format = mixed
```

2. 重启数据库，创建同步账户
```sql
-- 创建slave帐号slave，密码123456, 这里host 是 127.0.0.1，frp连接的内网，不需要开放，大家可以写成%
grant replication slave on *.* to 'slave'@'127.0.0.1' identified by '123456';


CREATE DATABASE videos DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 更新数据库权限
flush privileges;
```
> 可以去这里看[frp内网穿透_mysql服务_ssh服务_http服务](/linux/frp内网穿透_mysql服务_ssh服务_http服务.md)

1. 查询master的状态
```
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      196 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set
```

## 从库

1. 配置
```conf
log-bin=mysql-bin
server-id=20
```

2. 执行同步命令
```sql
-- 执行同步命令，设置主数据库ip，同步帐号密码，同步位置
change master to master_host='127.0.0.1',master_port=3307, master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=151459295;

-- 开启同步功能
start slave;
```

3. 检查从库状态, 在执行几条sql看看sql同步情况
```
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 185295
               Relay_Log_File: 00h-relay-bin.000002
                Relay_Log_Pos: 756
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes    -----------> 这里是连接成功
            Slave_SQL_Running: Yes    -----------> 这里sql执行成功
            ......
1 row in set (0.00 sec)
```

4. 常用
```sql
-- 停止主从同步
stop slave;

-- 连接断开时，重新连接超时时间
change master to master_connect_retry=50;

-- 开启主从同步
start slave;
```


# 关于`binlog`和`relaylog`

## 格式化

主库binlog
```
 mysqlbinlog --base64-output=decode-rows -v -v /var/lib/mysql/mysql-bin.000001 > binlog
```

从库relaylog
```
mysqlbinlog --base64-output=decode-rows -v -v mysql-relay-bin.000001 > relaylog
```

##  binlog和relaylog的rotate机制
binlog
- Rotate：每一条binary log写入完成后，都会判断当前文件是否超过 max_binlog_size(默认1g)，如果超过则自动生成一个binlog file
- delete: expire-logs-days 只在 实例启动时 和 flush logs 时以及文件文件是否超过 max_binlog_size时判断是否有过期的binlog文件，如果文件访问时间早于设定值，则purge file

relaylog
- Rotate：每从Master fetch一个events后，判断当前文件是否超过 max_relay_log_size（默认1g) 如果超过则自动生成一个新的relay-log-file
- Delete：purge-relay-log 在SQL Thread每执行完一个events时判断，如果该relay-log 已经不再需要则自动删除


## 配置解析

binlog 配置
```
mysql> show variables like "%bin%";
+--------------------------------------------+--------------------------------+
| Variable_name                              | Value                          |
+--------------------------------------------+--------------------------------+
| bind_address                               | *                              |
| binlog_cache_size                          | 32768                          |
| binlog_checksum                            | CRC32                          |
| binlog_direct_non_transactional_updates    | OFF                            |
| binlog_error_action                        | ABORT_SERVER                   |
| binlog_format                              | ROW                            |
| binlog_group_commit_sync_delay             | 0                              |
| binlog_group_commit_sync_no_delay_count    | 0                              |
| binlog_gtid_simple_recovery                | ON                             |
| binlog_max_flush_queue_time                | 0                              |
| binlog_order_commits                       | ON                             |
| binlog_row_image                           | FULL                           |
| binlog_rows_query_log_events               | OFF                            |
| binlog_stmt_cache_size                     | 32768                          |
| binlog_transaction_dependency_history_size | 25000                          |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER                   |
| innodb_api_enable_binlog                   | OFF                            |
| innodb_locks_unsafe_for_binlog             | OFF                            |
| log_bin                                    | ON                             |
| log_bin_basename                           | /var/lib/mysql/mysql-bin       |
| log_bin_index                              | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators            | OFF                            |
| log_bin_use_v1_row_events                  | OFF                            |
| log_statements_unsafe_for_binlog           | ON                             |
| max_binlog_cache_size                      | 18446744073709547520           |
| max_binlog_size                            | 1073741824                     |
| max_binlog_stmt_cache_size                 | 18446744073709547520           |
| sql_log_bin                                | ON                             |
| sync_binlog                                | 1                              |
+--------------------------------------------+--------------------------------+
29 rows in set (0.01 sec)
```

- log_bin
设置此参数表示启用binlog功能，并指定路径名称
- log_bin_index
设置此参数是指定二进制索引文件的路径与名称
- binlog_do_db
此参数表示只记录指定数据库的二进制日志
- binlog_ignore_db
此参数表示不记录指定的数据库的二进制日志
- max_binlog_cache_size
此参数表示binlog使用的内存最大的尺寸
- binlog_cache_size
此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。
- binlog_cache_use：使用二进制日志缓存的事务数量
- binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
- max_binlog_size
Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束
- sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。
从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多

relaylog 配置
```
mysql> show variables like '%relay%';
+---------------------------+------------------------------------+
| Variable_name             | Value                              |
+---------------------------+------------------------------------+
| max_relay_log_size        | 0                                  |
| relay_log                 |                                    |
| relay_log_basename        | /var/lib/mysql/00h-relay-bin       |
| relay_log_index           | /var/lib/mysql/00h-relay-bin.index |
| relay_log_info_file       | relay-log.info                     |
| relay_log_info_repository | FILE                               |
| relay_log_purge           | ON                                 |
| relay_log_recovery        | OFF                                |
| relay_log_space_limit     | 0                                  |
| sync_relay_log            | 10000                              |
| sync_relay_log_info       | 10000                              |
+---------------------------+------------------------------------+
11 rows in set (0.01 sec)
```

- max_relay_log_size：标记relay log 允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小；
- relay_log：定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录，文件名为host_name-relay-bin.nnnnnn（By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory）；
- relay_log_index：同relay_log，定义relay_log的位置和名称；
- relay_log_info_file：设置relay-log.info的位置和名称（relay-log.info记录MASTER的binary_log的恢复位置和relay_log的位置）
- relay_log_purge：是否自动清空不再需要中继日志时。默认值为1(启用)。
- relay_log_recovery：当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。
- relay_log_space_limit：防止中继日志写满磁盘，这里设置中继日志最大限额。但此设置存在主库崩溃，从库中继日志不全的情况，不到万不得已，不推荐使用；
- sync_relay_log：这个参数和sync_binlog是一样的，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay log中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值。
- sync_relay_log_info：这个参数和sync_relay_log参数一样，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay-log.info里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入relay-log.info里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值。


# mysql 主从同步原理
**主从复制原理**

1.  主服务器：binlog线程记录下所有改变了数据库数据的语句，放进master的binlog中
2.  从服务器：在start slave 以后，I/O线程请求主服务器拉去binlog内容，主服务器binlog dump负责响应，存到中继日志(relay log)中. 
3.  从服务器：sql执行线程，执行relay log中的语句

**主从一致性延时，数据恢复**

-   异步复制：mysql复制是异步的，主机将event写入二进制日志，但不知道从库是否或者何时检索并处理了他们，如果主服务器崩溃，则它提交的事务可能不会传输到任何服务器，主服务器到从服务器的故障转移可能会导致未同步事务丢失
-   半同步复制：主库在应答客户端提交的事务前需要保证至少一个从库接受并写到relay log中，半同步服务器通过`rpl_semi_sync_master_wait_point` 控制master在哪个环节接受slave ack, master接收到ack返回状态给客户端
    -   `WAIT_AFTER_COMMIT`: master write binlog -> slave sync binlog -> **master commit** -> **salve ack** -> master return result。
    -   `WAIT_AFTER_SYNC` （默认）:master write binlog -> slave sync binlog -> **salve ack** -> **master commit** -> master return result

# myaql主从同步延时问题

如果主从同步延时比较严重，解决方案：
- 分库，将一个主库拆分为多个主库，每个主库的写压力就小了几倍，此时延迟问题就会小了
- 并行复制，多个库并行复制如果某个库的写入特别高，并行复制还是没
- 重构代码

**主从一致性检测**



-   [MySQL 8 中复制延时的测量与监控分析]([https://dbalife.info/2018/06/12/MySQL-8-%E4%B8%AD%E5%A4%8D%E5%88%B6%E5%BB%B6%E6%97%B6%E7%9A%84%E6%B5%8B%E9%87%8F%E4%B8%8E%E7%9B%91%E6%8E%A7/](https://dbalife.info/2018/06/12/MySQL-8-中复制延时的测量与监控/))

# mysql故障转移

mysql主从同步配置一台内网服务器和一台外网服务器

mysql主从同步




```shell
mysql> explain select * from media where id = 100\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: media
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
```

列的含义：

- id: 每个select都会自动分配一个唯一的标识符
- select_type: select 查询的类型
- table：查询的是哪张表
- partitions：匹配哪个区
- type：join类型
- possible_keys: 此次查询中可能选用的索引
- key：此次查询中确认用到的索引
- ref：哪个字段或常数与key一起被使用
- rows：此查询一共扫描了多少行，估计值
- filtered：此查询条件所过滤数据的百分比
- Extra：额外的信息

# select_type

-   SIMPLE: 此查询不包含子查询或者union查询
-   PRIMARY：此查询是最外层查询
-   UNION: 此查询是UNION的第二或随后的查询
-   DEPENDEND UNION, UNION中的第二个或后面的查询语句，取决于外面的查询
-   UNION RESULT: UNION的结果
-   SUBQUERY: 子查询的中第一个SELECT
-   DEPENDEND SUBQUERY: 子查询中的第一个SELECT，取决于外面的的查询
-   MATERIALIZED：物理化，将查询结果作为临时表以便后续需要时直接使用结果，详情见[Optimizing Subqueries with Materialization](https://dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html)

```sql
explain select m.target_id, (
    select d.content_after from m_log d
    where d.target_id = m.target_id
    and d.target_type = 5
    and d.create_time <= "2019-11-13 07:59:30"
    and d.create_time >= "2019-10-01 00:00:00"
    order by d.id desc limit 0,1
) as 变更后金额
from `m_log` m
where m.target_type = 5
  and m.target_id in (select child from `relation` where parent = 1698747)
and m.create_time <= "2019-11-13 07:59:30"
and m.create_time >= "2019-10-01 00:00:00"
group by m.target_id;
```

| id   | select_type        |    table    | type | possible_keys             | key           | key_len | ref               | rows | Extra                           |
| ---- | ------------------ | :---------: | ---- | ------------------------- | ------------- | ------- | ----------------- | ---- | ------------------------------- |
| 1    | PRIMARY            | <subquery3> | ALL  | NULL                      | NULL          | NULL    | NULL              | NULL | Using temporary; Using filesort |
| 1    | PRIMARY            |      m      | ref  | create_time,idx_target_id | idx_target_id | 4       | <subquery3>.child | 2    | Using where                     |
| 3    | **MATERIALIZED**   |  relation   | ref  | child,idx_par_st          | idx_par_st    | 4       | const             | 14   | Using index                     |
| 2    | DEPENDENT SUBQUERY |      d      | ref  | create_time,idx_target_id | idx_target_id | 4       | func              | 2    | Using where; Using filesort     |

# type

-   system：表只有一行，这是一个`const` type 的特殊情况。
-   consts:  最多只有一行匹配。当你使用主键或者唯一索引的时候，就是`const`类型

```SQL
explain SELECT * from foo where id = 1;
```

| id   | select_type | table | type | possible_keys | key     | key_len | ref  | rows  |               Extra               |
| ---- | ----------- | ----- | ---- | ------------- | ------- | ------- | ---- | ----- | :-------------------------------: |
| 1    | SIMPLE      | foo   |      | const         | PRIMARY | PRIMARY | 4    | const | Directly search via Primary Index |

-   eq_ref: 通常出现在join中，表示前一列的结果都只能匹配到后一行的结果，并且查询的匹配的操作通常是 `=`,查询效率较高

```sql
explain select * from u, `u_ocpc` where u.id = u_ocpc.u_id and u.id in (2657099,2657010,2656981)
```

| id   | select_type | table  | type   | possible_keys | key     | key_len | ref                   | rows |    Extra    |
| ---- | ----------- | ------ | ------ | ------------- | ------- | ------- | --------------------- | ---- | :---------: |
| 1    | SIMPLE      | u_ocpc | range  | PRIMARY       | PRIMARY | 4       | NULL                  | 3    | Using where |
| 1    | SIMPLE      | u      | eq_ref | PRIMARY       | PRIMARY | 4       | adv.unit_ocpc.unit_id | 1    |    NULL     |

-   ref: 多表join，触发联合索引最左原则，或者这个索引不是唯一索引/主键索引

```sql
explain select * from `config` C left JOIN `dark` D on D.medias = C.value where C.group_id = 1;
```

| id   | select_type | table | type    | possible_keys       |         key         | key_len | ref   | rows | Extra                                              |
| ---- | ----------- | ----- | ------- | ------------------- | :-----------------: | ------- | ----- | ---- | -------------------------------------------------- |
| 1    | SIMPLE      | C     | **ref** | config_group_id_key | config_group_id_key | 4       | const | 12   | NULL                                               |
| 1    | SIMPLE      | D     | ALL     | NULL                |        NULL         | NULL    | NULL  | 4    | Using where; Using join buffer (Block Nested Loop) |

-   unique_subquery：使用了in的子查询，而且子查询是主键或者唯一索引

- range: 索引范围查询， 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 

```sql
explain select * from media where id between 2 and 10;
```

| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
| ---- | :---------: | ----- | ----- | ------------- | ------- | ------- | ---- | ---- | ----------- |
| 1    |   SIMPLE    | idea  | range | PRIMARY       | PRIMARY | 4       | NULL | 2    | Using where |

- ALL 全表扫描,性能最差的查询之一，因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 

```sql
explain select * from creative WHERE target_url = 'http://cdn.yuming.com//allsites/1647060/3ea6c76347dd4c7167b9c1c83f069003/index_1738893.html?r=9223'
```

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    |    Extra    |
| ---- | ----------- | ----- | ---- | ------------- | ---- | ------- | ---- | ------- | :---------: |
| 1    | SIMPLE      | idea  | ALL  | NULL          | NULL | NULL    | NULL | 3222483 | Using where |

# possible_key

表示在mysql查询时，能够是用到的索引，即使这些索引在possible_key中出现，但是并不是此索引会真正被MYSQL使用到，具体用到使用了哪些索引，由KEY决定



# key

 MySQL 在当前查询时所真正使用到的索引.



# key_len

索引的字节数，这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.



# rows

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数

sql最重要的评价字段



# Extra

- using filesort: mysql需要额外的排序操作，不能通过索引达到排序的效果，一半有using filesort都建议优化掉
- using index: 覆盖索引扫描，说明所需数据在索引中即可取到，性能不错
- using temporary：使用了临时表，一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

详细了解explain参数含义，后续还有trace的用法

explain详解


[TOC]

# gRPC权限认证

系统设计要满足最低安全标准，尽管平时我们设计的系统不是专门为了高安全性应用而设计的，但必须要用最低标准的加密和身份验证。例如：web服务要加密密码和身份证信息。同样分布式系统也要能加密二进制数据和节点通信数据，进行身份和权限认证。虽然这些事有开销的，但是这些开销相比安全风险来说是值得的。

gRPC本身支持几种授权验证机制：

- SSL/TLS: 
  - SSL/TLS 两端授权认证
  - 加密通信
- Token Authentication

## 创建证书

通过证书加密数据，使用openssl生成

server.key: 私钥

server.crt: 公钥

```shell
$ openssl genrsa -out server.key 2048
$ openssl req -new -x509 -days 3650 \
    -subj "/CN=douyacun.com" \
    -key server.key -out server.crt
```

`-subj` 内的值等价于：

```shell
$ openssl req -new -x509 -sha256 -key server.key -out server.crt -days 3650
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) []:CH
State or Province Name (full name) []:shandong
Locality Name (eg, city) []:laiwu
Organization Name (eg, company) []:douyacun
Organizational Unit Name (eg, section) []:douyacun
Common Name (eg, fully qualified host name) []:douyacun.com
Email Address []:douyacun@gmail.com
```

## 服务端加密

[【源码阅读】](https://github.com/douyacun/foo_go/tree/master/cmd/rpc/tls-auth)

使用gRPC加密通信最简单方式就是服务端加密，服务端需要初始化密钥对(server.key/server.crt)，客户端只需要公钥

```go
// 初始化tcp通道
lis, err := net.Listen("tcp", Port)
if err != nil {
  log.Fatal(err)
}
// 初始化TLS证书
creds, err := credentials.NewServerTLSFromFile("../cert/server.crt", "../cert/server.key")
if err != nil {
  log.Fatal(err)
}
// 创建 gRPC 服务，并配置证书
s := grpc.NewServer(grpc.Creds(creds))
// 注册函数
greeter.RegisterGreeterServer(s, new(server))
// 启动服务
if err := s.Serve(lis); err != nil {
  log.Fatalf("failed to serve: %v", err)
}
```

客户端为了能创建连接，需要获取server.crt(公钥)。

```go
// 初始化TLS证书
creds, err := credentials.NewClientTLSFromFile("../cert/server.crt", "server.douyacun.com")
if err != nil {
  log.Fatal(err)
}
// 拨号通信
conn, err := grpc.Dial("localhost:4000", grpc.WithTransportCredentials(creds))
if err != nil {
  log.Fatal(err)
}
defer conn.Close()
// 初始化客户端接口
client := greeter.NewGreeterClient(conn)
reply, err := client.SayHello(context.Background(), &greeter.HelloRequest{Name: "world"})
if err != nil {
  log.Fatal(err)
}

fmt.Println(reply.GetMessage())
```

## 双向证书验证

[【源码阅读】](https://github.com/douyacun/foo_go/tree/master/cmd/rpc/mutual-tls-auth)

上面的方式需要提前将证书给到客户端，每次变更都需要维护证书，为了避免这种麻烦。可以通过一个安全可靠的根证书分别对服务器和客户端的证书进行签名，这样客户端或服务端在收到对方证书后可以通过根证书验证证书的有效性

生成客户端证书

```shell
$ openssl genrsa -out client.key 2048
$ openssl req -new -x509 -days 3650 \
    -subj "/CN=douyacun.com" \
    -key client.key -out client.crt
```

生成根证书ca

```shell
$ openssl genrsa -out ca.key 2048
$ openssl req -new -x509 -days 3650 \
    -subj "/CN=douyacun.com" \
    -key ca.key -out ca.crt
```

对服务端证书签名

```shell
$ openssl req -new \
    -subj "/CN=douyacun.com" \
    -key server.key \
    -out server.csr
$ openssl x509 -req -sha256 \
    -CA ca.crt -CAkey ca.key -CAcreateserial -days 3650 \
    -in server.csr \
    -out server.crt
```

对客户端证书签名

```shell
$ openssl req -new \
    -subj "/CN=douyacun.com" \
    -key client.key \
    -out client.csr
$ openssl x509 -req -sha256 \
    -CA ca.crt -CAkey ca.key -CAcreateserial -days 3650 \
    -in client.csr \
    -out client.crt
```

服务端

```go
// 从磁盘加载证书
cert, err := tls.LoadX509KeyPair("../cert/server.crt", "../cert/server.key")
if err != nil {
  log.Fatalf("tls load key pair failed, %v",err)
}
certPool := x509.NewCertPool()
ca, err := ioutil.ReadFile("../cert/ca.crt")
if err != nil {
  log.Fatalf("ca.crt read filed %v", err)
}
if ok := certPool.AppendCertsFromPEM(ca); !ok {
  log.Fatal("failed to append client certs")
}
// 初始化tcp通道
lis, err := net.Listen("tcp", Port)
if err != nil {
  log.Fatal(err)
}
// 初始化TLS证书
creds := credentials.NewTLS(&tls.Config{
  ClientAuth:   tls.RequireAndVerifyClientCert,
  Certificates: []tls.Certificate{cert},
  ClientCAs:    certPool,
})
// 创建 gRPC 服务，并配置证书
s := grpc.NewServer(grpc.Creds(creds))
// 注册函数
greeter.RegisterGreeterServer(s, new(server))
// 启动服务
if err := s.Serve(lis); err != nil {
  log.Fatalf("failed to serve: %v", err)
}
```

客户端

```go
// 从磁盘加载证书
certificate, err := tls.LoadX509KeyPair("../cert/client.crt", "../cert/client.key")
if err != nil {
  log.Fatalf("tls load key pair failed, %v", err)
}
certPool := x509.NewCertPool()
ca, err := ioutil.ReadFile("../cert/ca.crt")
if err != nil {
  log.Fatalf("ca read failed, %v", err)
}
if ok := certPool.AppendCertsFromPEM(ca); !ok {
  log.Fatal("cert append pool failed")
}
// 初始化TLS证书
creds := credentials.NewTLS(&tls.Config{
  ServerName:   "douyacun.com", // 对服务端证书签名的配置(/CN = ?)
  Certificates: []tls.Certificate{certificate},
  RootCAs:      certPool,
})
// 拨号通信
conn, err := grpc.Dial("localhost:4000", grpc.WithTransportCredentials(creds))
if err != nil {
  log.Fatal(err)
}
defer conn.Close()
client := greeter.NewGreeterClient(conn)

reply, err := client.SayHello(context.Background(), &greeter.HelloRequest{Name: "world"})
if err != nil {
  log.Fatal(err)
}

fmt.Println(reply.GetMessage())
```

# token认证

[【源码阅读】](https://github.com/douyacun/foo_go/tree/master/cmd/rpc/token)

token认证主要是需要实现credentials.PerRPCCredentials接口

```go
// PerRPCCredentials defines the common interface for the credentials which need to
// attach security information to every RPC (e.g., oauth2).
type PerRPCCredentials interface {
	// GetRequestMetadata gets the current request metadata, refreshing
	// tokens if required. This should be called by the transport layer on
	// each request, and the data should be populated in headers or other
	// context. If a status code is returned, it will be used as the status
	// for the RPC. uri is the URI of the entry point for the request.
	// When supported by the underlying implementation, ctx can be used for
	// timeout and cancellation.
	// TODO(zhaoq): Define the set of the qualified keys instead of leaving
	// it as an arbitrary string.
	GetRequestMetadata(ctx context.Context, uri ...string) (map[string]string, error)
	// RequireTransportSecurity indicates whether the credentials requires
	// transport security.
	RequireTransportSecurity() bool
}
```

实现PerRPCCredentials interface

```go
type Auth struct {
	Account     string
	Password string
}
func (a *Auth) GetRequestMetadata(ctx context.Context, uri ...string) (map[string]string, error) {
	return map[string]string{"account": a.Account, "password": a.Password}, nil
}
func (a *Auth) RequireTransportSecurity() bool  {
	return false
}
```

服务端使用中间件验证,这里集成[go-grpc-middleware](https://github.com/grpc-ecosystem/go-grpc-middleware)

```go
func main() {
	s := grpc.NewServer(grpc.UnaryInterceptor(grpc_middleware.ChainUnaryServer(
		grpc_auth.UnaryServerInterceptor(check),
		grpc_recovery.UnaryServerInterceptor(),
	)))
	greeter.RegisterGreeterServer(s, &server{})
	lis, err := net.Listen("tcp", Port)
	if err != nil {
		log.Fatal(err)
	}
	if err := s.Serve(lis); err != nil {
		log.Fatalf("failed to server: %v", err)
	}
}
func check(ctx context.Context) (context.Context, error){
  // FromIncomingContext 可以获取到 GetRequestMetadata 返回到数据
	md, ok := metadata.FromIncomingContext(ctx)
	if !ok {
		return nil, fmt.Errorf("missing credentials")
	}
	var (
		account, password string
	)
	if val, ok := md["account"]; ok {
		account = val[0]
	}
	if val, ok := md["password"]; !ok {
		password = val[0]
	}
	fmt.Println(account, password)
	return nil, nil
}
```

# gRPC扩展

## 默认值

需要引入扩展选项`import "google/protobuf/descriptor.proto";` 实现默认值

```protobuf
// 这里声明协议版本，默认是proto2, 3和2并不向下兼容
syntax = "proto3";

import "google/protobuf/descriptor.proto";

// 这里指定 go 包名
package greeter;

// 定义默认类型
extend google.protobuf.FieldOptions {
    string default_name = 5000;
}

// 定义要序列化的信息的结构
message HelloRequest {
    // string 字段类型， 也可以自定义字段类型
    string name = 1 [(default_name) = "world"]; // 注意这里1是唯一的， 1-15 占用1个字节， 16 - 2047 占用2个字节
}

// 同一个proto文件中可以定义多个message type
message HelloReply {
    string message = 1;
}

// 定义接口
service Greeter {
    rpc SayHello (HelloRequest) returns (HelloReply);
}
```

##grpc-gateway

安装， 这里最好将GO111MODULE设为auto, `GO111MODULE=auto`,否则下面的安装会安装到`$GOPATH/pkg/mod`下面，不是安装到`$GOPATH/src`下

```go
go get -u github.com/grpc-ecosystem/grpc-gateway/protoc-gen-grpc-gateway
go get -u github.com/grpc-ecosystem/grpc-gateway/protoc-gen-swagger
go get -u github.com/golang/protobuf/protoc-gen-go
```

创建protobuf文件，生成gRPC使用的pb文件，grpc-gateway使用的gw文件

```shell
$ protoc -I/usr/local/include -I. \
  -I$GOPATH/src \
  -I$GOPATH/src/github.com/grpc-ecosystem/grpc-gateway/third_party/googleapis \
  --grpc-gateway_out=. \
  --go_out=plugins=grpc:. \
  hello.proto
```

```protobuf
// 这里声明协议版本，默认是proto2, 3和2并不向下兼容
syntax = "proto3";

// 我的goland这里会提示 file not found
import "google/api/annotations.proto";

// 这里指定 go 包名
package greeter;

// 定义要序列化的信息的结构
message HelloRequest {
    // string 字段类型， 也可以自定义字段类型
    string name = 1; // 注意这里1是唯一的， 1-15 占用1个字节， 16 - 2047 占用2个字节
}

// 同一个proto文件中可以定义多个message type
message HelloReply {
    string message = 1;
}

// 定义接口
service Greeter {
    //    rpc SayHello (HelloRequest) returns (HelloReply);
    rpc Echo (HelloRequest) returns (HelloReply) {
        option (google.api.http) = {
            get : "/echo/{name}"
        };
    }
}
```

创建httpserver文件

```go
ctx := context.Background()
ctx, cancel := context.WithCancel(ctx)
defer cancel()

// Register gRPC server endpoint
// Note: Make sure the gRPC server is running properly and accessible
mux := runtime.NewServeMux()
err := greeter.RegisterGreeterHandlerFromEndpoint(ctx, mux,  "localhost:4000", []grpc.DialOption{grpc.WithInsecure()})
if err != nil {
  log.Fatal(err)
}

// Start HTTP server (and proxy calls to gRPC server endpoint)
err = http.ListenAndServe(":8081", mux)
if err != nil {
  log.Fatal(err)
}
```

创建rpcserver文件，这里还是和上面的rpc server启动方式一样

```go
s := grpc.NewServer()
greeter.RegisterGreeterServer(s, new(server))
lis, err := net.Listen("tcp", Port)
if err != nil {
  log.Fatal(err)
}
if err := s.Serve(lis); err != nil {
  log.Fatalf("failed to serve: %v", err)
}
```

先启动rpcserver，然后启动httpserver，通过curl请求

```shell
$ curl -X GET "http://localhost:8081/echo/douyacun"
{"message":"hello "}%
```

# 推荐阅读

- [https://bbengfort.github.io/programmer/2017/03/03/secure-grpc.html](https://bbengfort.github.io/programmer/2017/03/03/secure-grpc.html)

扫码关注获得更多关于深入理解redis、go、mysql源码的文章

gRPC是基于http2.0，这里实现证书认证、rpc和RESTful HTTP API共存

gRPC进阶


# 系统

- uname -a               - 查看内核/操作系统/CPU信息
- head -n 1 /etc/issue   - 查看操作系统版本
- cat /proc/cpuinfo      - 查看CPU信息
- hostname               - 查看计算机名
- lspci -tv              - 列出所有PCI设备
- lsusb -tv              - 列出所有USB设备
- lsmod                  - 列出加载的内核模块
- env                    - 查看环境变量

# 资源

- free -m                - 查看内存使用量和交换区使用量
- df -h                  - 查看各分区使用情况
- du -sh <目录名>        - 查看指定目录的大小
- grep MemTotal /proc/meminfo   - 查看内存总量
- grep MemFree /proc/meminfo    - 查看空闲内存量
- uptime                 - 查看系统运行时间、用户数、负载
- cat /proc/loadavg      - 查看系统负载

# 磁盘和分区

- mount | column -t      - 查看挂接的分区状态
- fdisk -l               - 查看所有分区
- swapon -s              - 查看所有交换分区
- hdparm -i /dev/hda     - 查看磁盘参数(仅适用于IDE设备)
- dmesg | grep IDE       - 查看启动时IDE设备检测状况

# 网络

- ifconfig               - 查看所有网络接口的属性
- iptables -L            - 查看防火墙设置
- route -n               - 查看路由表
- netstat -lntp          - 查看所有监听端口
- netstat -antp          - 查看所有已经建立的连接
- netstat -s             - 查看网络统计信息

# 进程

- ps -ef                 - 查看所有进程
- top                    - 实时显示进程状态

# 用户

- w                      - 查看活动用户
- id <用户名>            - 查看指定用户信息
- last                   - 查看用户登录日志
- cut -d: -f1 /etc/passwd   - 查看系统所有用户
- cut -d: -f1 /etc/group    - 查看系统所有组
- crontab -l             - 查看当前用户的计划任务

# 服务

- chkconfig --list       - 列出所有系统服务
- chkconfig --list | grep on    - 列出所有启动的系统服务

# 程序

- rpm -qa                - 查看所有安装的软件包

linux常用命令_磁盘_进程_网络_内存_负载

# 电脑重装成centos系统

## 下载iso镜像

[镜像下载](http://centos.01link.hk/)

```
[DIR] Parent Directory                                             -   
[TXT] 0_README.txt                            10-May-2018 04:16  2.4K  
[   ] CentOS-7-x86_64-DVD-1804.iso            04-May-2018 05:07  4.2G  
[   ] CentOS-7-x86_64-DVD-1804.torrent        11-May-2018 23:43   84K  
[   ] CentOS-7-x86_64-Everything-1804.iso     07-May-2018 20:55  8.8G  
[   ] CentOS-7-x86_64-Everything-1804.torrent 11-May-2018 23:43  176K  
[   ] CentOS-7-x86_64-LiveGNOME-1804.iso      03-May-2018 02:21  1.3G  
[   ] CentOS-7-x86_64-LiveGNOME-1804.torrent  11-May-2018 23:43   52K  
[   ] CentOS-7-x86_64-LiveKDE-1804.iso        03-May-2018 02:28  1.8G  
[   ] CentOS-7-x86_64-LiveKDE-1804.torrent    11-May-2018 23:43   71K  
[   ] CentOS-7-x86_64-Minimal-1804.iso        04-May-2018 05:07  906M  
[   ] CentOS-7-x86_64-Minimal-1804.torrent    11-May-2018 23:43   36K  
[   ] CentOS-7-x86_64-NetInstall-1804.iso     04-May-2018 04:34  495M  
[   ] CentOS-7-x86_64-NetInstall-1804.torrent 11-May-2018 23:43   20K     >>>>>>>>>
[TXT] sha1sum.txt                             10-May-2018 04:02  454   
[TXT] sha1sum.txt.asc                         11-May-2018 23:12  1.3K  
[TXT] sha256sum.txt                           10-May-2018 03:59  598   
[TXT] sha256sum.txt.asc                       11-May-2018 23:12  1.4K  
```
> 选择的是 上面标记的 网络安装版本 >>>>>>>>> ,一些基础软件没有, 需要联网下载

## 制作centos系统盘 (mac os)

----

1. 把ISO转成DMG，命令中的-o后面的linux是目标文件名，按自己需求改

    ```
    # hdiutil convert -format UDRW -o 输出 输入文件.iso
    # hdiutil convert /Users/os/Downloads/ylmf.iso -format UDRW -o ylmf.img    // windows
    ```


2. .把U盘插上，查看盘符

    ```
    # diskutil list
    ```

3. 卸载U盘，把命令里的disk2改成你自己的盘符，还要注意卸载不是弹出！

    ```
    # diskutil unmountDisk /dev/disk2
    ```

4. 开始把光盘写入到u盘

    ```
    # sudo dd if=linux.dmg of=/dev/disk2 bs=1m

    之后终端里就不动了，看着没反应了，其实正在后台运行呢，等着就行，写入完成以后终端有提示，然后再弹出U盘就可以正常使用了。

    408+0 records in
    408+0 records out
    427819008 bytes transferred in 3.608223 secs (118567786 bytes/sec)
    ```

## 装机 安装选择

- f12 进入bios
```
Boot Mode  : Legacy Support
Boot Priority : Legacy First
```
- 重启 f12 选 use 启动

- 设置启动盘, 配置目录google或baidu搜吧!

- 上面的关键点来了, 因为选择的netinstall, 需要设置安装源, 下面的源可能会失效 (https://opsx.alibaba.com/mirror) 自己选吧

- https://mirrors.aliyun.com/centos/7.5.1804/os/x86_64/

- 选择最小化安装就行


# 扩展
- linux 挂载FAT32系统
    ```
    # mount -t vfat /dev/sdb1 /package
    ```