
# mysql用户账户管理
- 授权相当于root权限的账户 liuning 任何地点都可以登录 密码123456 

````
第一种方式:

grant all privileges 
    -> on *.* to 'liuning'@'%'      // on 将数据库中的表授权给刘宁   % 表示任意地点可以登录
    -> identified by '123456'       // 指定密码验证
    -> with grant option            // 授权资源限制
    
第二种方式:

insert into user
->  values('localhost','liuning',password('123456'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

FLUSH PRIVILEGES;   // 重载授权表
````

-  授权账户访问某个数据库
```
第一种方式:

grant select,update,insert,delete,drop
->  on  demo.*                  # 授权liuning demo数据库中的所有表
->  to  'liuning'@'locahost'    # 只能本机登录
->  identified by '123456'

第二种方式:

insert into user (Host,User,Password)
->  values('douyacun.cn','liuning',password('123456'));
flush privileges;
```
```
grant select,update,insert,delete,drop,create
on `byidb`.`other*`
to "aiqiyi"@"%" identified by '123456';
```



- width 设置资源限制
```
GRANT ALL ON customer.* TO 'francis'@'localhost'
    ->     IDENTIFIED BY 'frank'
    ->     WITH MAX_QUERIES_PER_HOUR 20        // 账户每小时可以发出的查询数
    ->          MAX_UPDATES_PER_HOUR 10        // 账户每小时可以发出的更新数
    ->          MAX_CONNECTIONS_PER_HOUR 5     // 账户每小时可以连接服务器的次数
    ->          MAX_USER_CONNECTIONS 2;        // 表示账户一次可以同时连接的最大连接数
    
要想设置或更改已有账户的限制，在全局级别使用GRANT USAGE语句(在*.*)。下面的语句可以将francis的查询限制更改为100：
    
    mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
        ->     WITH MAX_QUERIES_PER_HOUR 100;
```
- 设置账户密码
```
mysqladmin -u root -h "%" password "123456"
或者
SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');
```
## mysql服务器授权表
- 权限表
    -  user表范围列决定是否允许或拒绝到来的连接。对于允许的连接，user表授予的权限指出用户的全局(超级用户)权限。这些权限适用于服务器上的all数据库。
    - db表范围列决定用户能从哪个主机存取哪个数据库。权限列决定允许哪个操作。授予的数据库级别的权限适用于数据库和它的表。
    - host表不受GRANT和REVOKE语句的影响。大多数MySQL安装根本不需要使用该表。
    - tables_priv和columns_priv表类似于db表，但是更精致：它们在表和列级应用而非在数据库级。授予表级别的权限适用于表和所有它的列。授予列级别的权限只适用于专用列。
    - procs_priv表适用于保存的程序。授予程序级别的权限只适用于单个程序。

----------------------------

- 当你修改授权表的内容时，确保你按你想要的方式更改权限设置是一个好主意。要检查给定账户的权限，使用SHOW GRANTS语句。例如，要检查Host和User值分别为pc84.example.com和bob的账户所授予的权限，应通过语句：
```
show grants for 'liuning'@'%';
```

## mysql提供的权限

权限|列|上下文 
:-----|:-----|:---- 
create|Create_priv|数据库、表或索引 创建新数据库和表，
drop|DROP|数据库、表  或删除(移掉)已有数据库和表
grant option|Grant_priv|数据库、表或保存的程序 允许你把你自己拥有的那些权限授给其他的用户。可以用于数据库、表和保存的程序。
REFERENCES|References_priv|数据库或表
alter|Alter_priv|表 更改表的结构和重新命名表。
delte|Delete_priv|表
index|INDEX_priv|表  允许创建或删除索引
insert|Insert_priv|表
select|Select_priv|表
update|Update_priv|表
create view|Create_view_priv|视图
show view|Show_view_priv|视图
alter routine|Alter_routine_priv|保存的程序 更改和删除保存的程序
create routine|Create_routine_priv|保存的程序 创建保存的程序（函数和程序）
execute|Execute_priv|保存的程序 执行保存的程序
file|FILE_priv|服务器主机上的文件访问  你用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句读和写服务器上的文件
create temporary tables|Create_tmp_table_priv|服务器管理
lock tables|Lock_tables_priv|服务器管理
create user|Create_user_priv|服务器管理
provess|PROCESS_priv|服务器管理 processlist
relaod|reload_priv|服务器管理  允许执行 flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload
replication client|Repl_client_priv|服务器管理
replication slave|Repl_slave_priv|服务器管理
show databases|	Show_db_priv|从服务器管理
shutdown|Shutdown_priv|服务器管理  shutdown
super|Super_priv|服务器管理   kill
usage|0| 创建用户不给于任何权限

# 数据库连接,访问控制
服务器基于你的身份以及你是否能通过供应正确的密码验证身份来接受或拒绝连接。如果不是，服务器完全拒绝你的访问，否则，服务器接受连接，然后进入阶段2并且等待请求。
- 你从那个主机连接
- 你的MySQL用户名
- 验证(Host, User和Password)范围列执行
- CURRENT_USER()函数 判断验证连接



# 实际授权 实例
```
mysql> grant select, insert, update, delete, create view, show view
->  on yar.*
->  to 'liuning'@'%'
->  identified by '123456';

mysql> show grants for 'liuning'@'%';

```

# 取消给用户的权限
```
mysql> revoke all
-> on yar.*
-> from 'liuning'@'%';
```



mysql 数据库授权sql


# 聚合函数
- avg(),        求取平均值
- count(),      计算表中的行数
- instr('mysql','sql')       计算子字符在字符串中第一次出现的位置
- sum()         计算总和
- min()         求取最小值
- max()         计算最大值
- group_concat()    函数将组中的字符串连接成为具有各种选项的单个字符串。
    ```
        GROUP_CONCAT(DISTINCT expression
            ORDER BY expression
            SEPARATOR sep);

    mysql> INSERT INTO t(v) VALUES('A'),('B'),('C'),('B');

    mysql> select group_concat(
                distinct v
                order by v desc
                separator ';')
            from t;

    mysql> d;c;b;a
    ```
    + 相当于:select v from t order by v desc;
    + 结果整合成字符串,PHP implode(',',$res);

# 字符串函数
- concat()  不多说
- length() char_length() 以字节或字符 字符串长度
- replace(str,old_string,new_string)     搜索并替换字符串中的子字符串
- substring(string,position)            截取字符串
- trim([{BOTH|LEADING|TRAILING} [removed_str]] FROM str) 和php作用相同
- find_in_set('a','a,b,c')     允许您在逗号分隔的字符串列表中查找指定字符串的位置。
- format(1111.22222,2);         相当于PHP number_format();

# 控制流
- CASE表达式 
```
CASE value
WHEN compare_value_1 THEN result_1
WHEN compare_value_2 THEN result_2
…
ELSE result 
END

CASE
    WHEN state IS NULL THEN country
    ELSE state
END

mysql> select
case phone
when 15258157582 then 1234567
else phone end
from ln_users;
```
- if语句
```
IF expression THEN
   statements;
END IF;
```
- ifnull语句
```
SELECT IFNULL(1,0); -- returns 1

SELECT IFNULL('',1); -- returns ''

select ifnull(email,concat(phone,'@abc.com') as email from ln_users;
```
- nullif语句
    + NULLIF(expression_1,expression_2);
    + 如果expression_1 = expression_2为true，则NULLIF函数返回NULL，否则返回expression_1 。
```
mysql> select nullif(phone,15258157582) as phone from ln_users;
```

# 日期函数
- current_date()    返回当前日期
- datediff()        计算两个日期之间的时间差
- day()             获取指定日期的天数   2017-09-05  5天
- [date_add()](http://www.yiibai.com/mysql/date_add.html)   将时间值添加到日起值
- [date_sub()](http://www.yiibai.com/mysql/date_sub.html)   减日期
- date_format()     格式化日期
```
select date_format(create_at,'%Y-%m-%d') from ln_users;
```
- from_unixtime(unixtime(),'%Y-%m-%d %H:%i:%s')   格式化时间戳
- uninx_timestamp(now());    获取当前时间戳
- now()             获取当前时时间
- month()           获取给定日期的整数月份
- str_to_date()     函数将字符串转换为日期时间值
```
SELECT STR_TO_DATE('21,5,2018','%d,%m,%Y');
```
- timediff()        返回两个TIME或DATETIME值之间的差值。
```
SELECT TIMEDIFF('12:00:00','10:00:00') diff;
```
- timestampdiff()   计算两个DATE或DATETIME值之间的差值。
- week()            返回一个日期的星期数值。
- weekday()         返回一个日期表示为工作日/星期几的索引。
- year()            放回年份


聚合函数、符串函数、控制流、日期函数

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使用


家里一台联想笔记本作为爬虫服务器，有一台公网阿里云服务器，目的爬虫跑完之后，公网服务器可以主从同步，家里的服务器和公网服务器通信的问题通过[内网穿透工具(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主从同步


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

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

**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存储过程语法

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

innodb事务详解

innodb关键特性包括：
- 插入缓冲 (insert buffer)
- 两次写(double write)
- 自适应hash索引(adaptive hash index)
- 异步I/O (async i/o)
- 刷新临界页 (flush neighbor page)

# 插入缓冲
## insert buffer

每张表都有一个主键索引(没有明确指明，会默认生成一个不可见的主键索引)，主键索引(聚集索引)的插入顺序是非常快的，但是不可能每张表只有一个聚集索引，更多情况下一张表会有很多辅助索引，对于主键索引的插入式顺序存放的，但是对于辅助索引的叶子节点的插入就不是顺序的了。

innodb 对于非聚集索引的插入和更新操作，不是每一次直接插入到索引页，而是先判断索引页是否在缓冲池中,若有直接插入，否则，先放入到insert buffer中。再以一定频率合并insert buffer和索引子节点。通常能将多个插入操作合并到一个操作中，因为在一个索引页中。使用insert buffer需要同时满足两个条件：
- 索引是辅助索引
- 索引不是唯一索引

`show engine innodb status\G;`可以查看insert buffer的使用情况

- seg_size 代表insert buffer大小为 n*16k
- free list len代表空闲列表的长度
- inserts代表插入记录数
- merged recs代表了合并后的插入记录数
- merges 代表合并的次数，实际读取页的次数

> 缓冲池: 是为了通过内存速度来弥补磁盘速度较慢对于性能的影响，包括insert buffer、data pag、index page、lock info、adaptive hash index

**IBUF_POOL_SIZE_PER_MAX_SIZE**可以调整insert_buffer_pool可以占用缓冲池的大小

## change buffer
change buffer可以对DML操作(insert,delete,update)进行缓冲操作,适用对象仍然是非唯一的辅助索引， 对一条记录update的步骤：
- 将记录标记为已删除
- 真正将记录删除

**innodb_change_buffer_max_size**控制change buffer最大使用内存的数量，25表示最多使用1/4缓冲池空间

# double write
插入缓冲给innodb存储引擎带来的是性能上的提升，double write提高了innodb存储引擎的数据页的可靠性

那么double write做了什么？\
数据库宕机时，可能对数据页只写入部分，这种情况称为部分写失效。可以通过redo log来恢复，在写入失效时，先通过页的副本还原该页，在进行重做，这就是double write

double write有一段2m的内存空间，在磁盘上还有2m*的共享表空间，数据先从内存空间写入到共享空间，立即调用fsync同步，此时是顺序写入速度快，在完成写入后，再将共享空间的数据写入到各个表空间文件中。此时写入是离散的。double write写入时会合并写入操作，消耗要小于两次io的消耗

```shell
mysql> show global status like 'innodb_dblwr%\G'
```

- innodb_dblwr_pages_writen: inndob一共写了多少页
- innodb_dblwr_writes: 实际写入次数

`skip_innodb_doublewrite`可以禁止使用doublewrite功能

# 自适应hash索引
hash索引的查找复杂度O(1),B+树的查找取决于B+树的高度，生产环境一般是3～4,需要3～4次

innodb会监控对表上各索引的查询，如果二级索引被频繁访问，成为热点数据，建立hash索引可以带来，速度的提升

hash索引有一个要求，查询模式必须是一样的，必须是等值查询

**`innodb_adaptive_hash_index`**: 是否开启自适应hash索引

# 异步I/O - AIO
AIO优势:
1. 多个I/O异步执行
2. 合并多个I/O为同步操作

mysql read、脏页刷新，磁盘写入都是通过AIO来实现的

# 刷新邻接页
原理是：innodb刷新脏页时，会检测该页所在区的所有页，如果有脏页AIO就会将多个I/O操作合并为一个操作。
**innodb_flush_neighbors** 可以控制是否启用，传统机械键盘建议启用，固态硬盘建议关闭

innodb说这些特性，相信比说支持事务要更有说服力

innodb关键特性



```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()` 效率高，且随机性也比较好；

排序模式

# 最终一致性

> 建议阅读
> - [分布式事务一致性，3种模式](https://zhuanlan.zhihu.com/p/36153160)
> - [TCC](https://juejin.im/post/5bf201f7f265da610f63528a)

原则还是尽量保持单机事物，只有单库执行才能保证数据库的ACID特性，遇到跨库事物， 通常通过“最终一致”事物保证事物执行的吞吐量，

- 定义
- 原理

# 定义
系统中所有的数据副本经过一段时间后最终能够达到一致的状态

# 原理
保证核心事物的正向执行，然后保存事物中间状态，其他事物异步执行，执行完成后达到最终的事物一致，避免跨库事物时间序列执行阻塞，提升事物吞吐量

# 实现模式
- 可靠事件模式
- 业务补偿模式
- TCC模式


# 可靠事件

关键点在于：**可靠事件投递** 和 **避免事件重复消费**

可靠事件投递：
  - 每个服务原子的业务操作和发布事件
  - 事件代理系统确保事件传递至少一次，避免重复消费，要求事件消费系统实现 **幂等性**，eg. 支付服务不能因为重复收到事件就多次支付

## 本地事件表

1. 在同一个本地事物中记录业务数据和事件
2. 实时发布一个事件 事件系统 立即通知关联的业务服务，事件发布成功立即删除记录的事件
3. 事件恢复服务定时从事件表中恢复未发布成功的事件，重新发布，重新发布成功才删除记录
  

2要求实时性，第三条保证一定发布，额外的事件数据库可能会给数据库带来额外的压力

## 外部事件表

将事件持久化到外部事件系统，事件系统需要提供实时事件服务以接受微服务发布事件，事件系统需要提供事件恢复服务来确认和恢复服务

1. 业务服务 在事物提交前，通过实时事件服务向事件系统请求 *发送事件*, 事件系统只记录事件不会真正发送事件
2. 业务服务 在事物提交后，向事件系统确认发送，事件得到确认后事件系统才真正发送事件
3. 业务服务 在事物回滚后，请求事件系统取消发送
4. 事件系统定时查找未确认发送的事件向业务服务查询状态，根据返回状态决定取消还是发布

需要一次确认操作 并且需要 业务服务提供额外的查询接口， 事件幂等性


### issue

1. 幂等性

    TODO:: 待完善

    幂等事件要考虑执行顺序，
    - 保证事件顺序的一个简单做法就是在添加时间戳，微服务记录最后处理的时间戳，如果事件戳早于记录的时间戳，丢弃
    - 更稳妥的做法是，每个事件有个全局递增的事件id，为每条事件记录处理结果，如果处理过返回上一次的处理结果，否则调度执行事件

2. 事件消费的开销

    需要考虑重复执行一条事件和查询存储结果的开销

    对于重复处理开销很小的事件，或者只有非常少的事件会被重复处理接收，可以重复处理一次事件，在将事件数据持久化时由数据库 **唯一索引** 抛出唯一性约束异常

    对于重复处理一条事件的开销比额外一次查询的开销要高很多， 可以使用过滤服务过滤处理的过的事件， 每个事件执行完成后记录执行结果

3. 过滤服务是在事件处理完成后记录结果，可能处理中间就再次收到重复事件

    可以记录事件的处理过程：
     - 接收
     - 处理中
     - 收到应答
     - 处理完成
    
    可以根据不同的状态，做不同的处理

4. 业务服务的 消息处理状态，发送请求成功但是没有收到处理结果

    选择暂时不一致性，采用对账和人工接入的方式保证一致性



# 业务补偿模式

- 业务异常：业务逻辑异常，eg. 商品库存不足、账户余额不足
- 技术异常：非业务逻辑异常，eg. 网络连接异常、请求超市

补偿模式使用一个额外的协调服务来保证各个需要保证一致性的微服务，协调服务按顺序调用各个微服务，如果某个微服务调用异常（包括业务异常和技术异常）就取消之前已经调用成功的微服务

为了降低开发的复杂度和效率，协调服务需要实现为一个通用补偿框架，提供服务编排和自动完成补偿的能力
    - 确定失败的步骤和状态，从而确定补偿的范围
    - 能提供补偿操作使用到的业务数据
    - 生成全局唯一的业务流水号
    - 关联表，获取各个步骤的业务数据和状态
    - 提供重试策略



## 重试策略

- 失败原因不是暂时性的，由于业务因素导致的，应立即终止重试
- 错误是一些罕见的异常，eg. 网络传输过程中数据丢失，应该立即重试
- 如果错误的是系统繁忙，eg. 500,或者超时 需要等一段时间再次重试
- 重试操作要指定次数，达到上线就不应该再次重试了
- 如果重试时仍然错误，可逐渐增加时间，直到达到上限后
- 如果某个时刻存在大量重试操作，补偿框架要控制请求的流量，防止对工作服务造成过大的压力


# TCC模式

建议阅读
- [TCC](https://juejin.im/post/5bf201f7f265da610f63528a)

3个阶段

## try
预处理阶段

## confirm
事物提交阶段

## cancel
事物回滚阶段


原则还是尽量保持单机事物，只有单库执行才能保证数据库的ACID特性，遇到跨库事物， 通常通过“最终一致”事物保证事物执行的吞吐量，

分布式事务最终一致性




```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的用法