发表于 | 更新于 | 分类于

背景

项目信息 说明
项目名称 售后服务微信公众号
机房位置 北京亦庄 / 卓朗机房
本地数据库 10.199.96.188
数据量 140 GB
版本 MySQL 5.5
阿里云 ECS 说明
主机 47.95.110.25
自建 MySQL MySQL 5.5
用户名 root
密码 HkdV*Q4mVavjYRSg
阿里云 RDS 说明
实例 rm-2zeh0aso72hr897it.mysql.rds.aliyuncs.com:3306
版本 MySQL 5.6
用户名 haierfw
密码 **

迁移方案

本地 IDC 数据库 -> ECS 自建数据库 -> 阿里云 RDS

操作

步骤 耗时
全备 30 min
传输 3 h 30 min
增备 30 min
恢复 30 min
DTS 迁移 2 h 30 min

实施

  • 全量备份
1
innobackupex --defaults-file=/etc/my.cnf --user=sre --password='xx' --no-timestamp /data/backup
  • 传输
1
scp -r /data/backup/* root@47.95.110.25:/data/backup
  • 全量恢复
1
2
3
4
5
mysqladmin -uroot -p'xx' shutdown
innobackupex --defaults-file=/etc/my.cnf --apply-log /data/backup
mv mysql mysql_bak
mkdir mysql
innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup
  • 增量备份

在进行增量备份时，首先要进行一次全量备份，第一次增量备份是基于全备的，之后的增量备份是基于上一次的增量备份，以此类推。

1
innobackupex --defaults-file=/etc/my.cnf --user=sre --password='xx' --incremental /data/incremental --incremental-basedir=/data/backup --parallel=2
  • 增量恢复
1
2
3
4
5
innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only /data/backup
innobackupex --defaults-file=/etc/my.cnf --apply-log /data/backup --incremental-dir=/data/incremental/2019-12-16_15-33-48
innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup
chown -R mysql:mysql mysql
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql >/dev/null &
  • 配置复制
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 获取Master binlog位置
$ cat xtrabackup_binlog_info
mysql-binlog.000297	900834433

# 配置复制
CHANGE MASTER TO 
MASTER_HOST='x.x.x.x',
MASTER_USER='repl',
MASTER_PASSWORD='Changeme_123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000297', 
MASTER_LOG_POS=900834433,
MASTER_CONNECT_RETRY=10;

# 启动同步
mysql> start slave;
  • 传输
1
scp -r /data/backup/* root@47.95.110.25:/data/backup

发表于 | 更新于 | 分类于

语法

1
2
3
Usage: mysqldump [OPTIONS] database [tables] > [backupfile.sql]
       mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] > [backupfile.sql]
       mysqldump [OPTIONS] --all-databases [OPTIONS] > [backupfile.sql]

备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# 备份所有库
mysqldump -u<user> -p<password> --all-databases > backup.sql

# 备份指定库
mysqldump -u<user> -p<password> --databases school hospital > backup.sql

# 备份指定表
mysqldump -u<user> -p<password> school table_name > backup.sql

# 导出表结构，无数据
mysqldump -u<user> -p<password> --all--database --no-data > backup.sql

# 备份存储过程
mysqldump --user=sre --password='xx' --single-transaction --master-data=2 -n -d -t -R user > dump.sql

# 按条件备份
mysqldump -usre -p'xx' -t user uc_event_logs_bak --where="id > 46005503" > uc_event_logs_bak2.sql

# 后台执行任务
nohup mysql -usre -p'xx' user < uc_event_logs_bak2.sql 2> myout.file &

# 备份InnoDB
mysqldump --socket=mysql.sock --user=user --password=pass --single-transaction --flush-logs --master-data=2 --all-databases --extended-insert --routines --events > dump.sql

# 备份Myisam
mysqldump --socket=mysql.sock --user=user --password=pass --lock-all-tables --flush-logs --master-data=2 --all-databases --extended-insert --routines --events > dump.sql

## --socket: sock文件
## --user: 用户名
## --password: 密码
## --single-transaction: 备份期间不加锁
## --flush-logs: 开始导出之前刷新日志
## --master-data: 该选项将 binlog 的位置和文件名追加到输出文件中
## --all-databases: 备份所有库
## --extended-insert: 使用具有多个VALUES列的INSERT语法，这样使导出文件更小，并加速导入的速度
## --routines: 导出存储过程以及自定义函数
## --events: 导出事件
## --triggers: 导出触发器，该选项默认启用
## 默认导出视图

Table：mysqldump Options

Option Name Description
–add-drop-database Add DROP DATABASE statement before each CREATE DATABASE statement
–add-drop-table Add DROP TABLE statement before each CREATE TABLE statement
–add-drop-trigger Add DROP TRIGGER statement before each CREATE TRIGGER statement
–add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
–all-databases Dump all tables in all databases
–allow-keywords Allow creation of column names that are keywords
–apply-slave-statements Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output
–bind-address Use specified network interface to connect to MySQL Server
–comments Add comments to dump file
–compact Produce more compact output
–compatible Produce output that is more compatible with other database systems or with older MySQL servers
–complete-insert Use complete INSERT statements that include column names
–compress Compress all information sent between client and server
–create-options Include all MySQL-specific table options in CREATE TABLE statements
–databases Interpret all name arguments as database names
–debug Write debugging log
–debug-check Print debugging information when program exits
–debug-info Print debugging information, memory, and CPU statistics when program exits
–default-auth Authentication plugin to use
–default-character-set Specify default character set
–defaults-extra-file Read named option file in addition to usual option files
–defaults-file Read only named option file
–delete-master-logs On a master replication server, delete the binary logs after performing the dump operation
–disable-keys For each table, surround INSERT statements with statements to disable and enable keys
–dump-date Include dump date as “Dump completed on” comment if –comments is given
–dump-slave Include CHANGE MASTER statement that lists binary log coordinates of slave’s master
–events Dump events from dumped databases
–extended-insert Use multiple-row INSERT syntax
–flush-logs Flush MySQL server log files before starting dump
–flush-privileges Emit a FLUSH PRIVILEGES statement after dumping mysql database
–force Continue even if an SQL error occurs during a table dump
–help Display help message and exit
–host Host on which MySQL server is located
–ignore-error Ignore specified errors
–ignore-table Do not dump given table
–include-master-host-port Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with –dump-slave
–insert-ignore Write INSERT IGNORE rather than INSERT statements
–lock-all-tables Lock all tables across all databases
–lock-tables Lock all tables before dumping them
–log-error Append warnings and errors to named file
–login-path Read login path options from .mylogin.cnf
–master-data Write the binary log file name and position to the output
–max-allowed-packet Maximum packet length to send to or receive from server
–net-buffer-length Buffer size for TCP/IP and socket communication
–no-autocommit Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
–no-create-db Do not write CREATE DATABASE statements
–no-create-info Do not write CREATE TABLE statements that re-create each dumped table
–no-data Do not dump table contents
–no-defaults Read no option files
–opt Shorthand for –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset.
–order-by-primary Dump each table’s rows sorted by its primary key, or by its first unique index
–password Password to use when connecting to server
–pipe Connect to server using named pipe (Windows only)
–port TCP/IP port number for connection
–print-defaults Print default options
–quick Retrieve rows for a table from the server a row at a time
–replace Write REPLACE statements rather than INSERT statements
–result-file Direct output to a given file
–routines Dump stored routines (procedures and functions) from dumped databases
–set-charset Add SET NAMES default_character_set to output
–set-gtid-purged Whether to add SET @@GLOBAL.GTID_PURGED to output
–single-transaction Issue a BEGIN SQL statement before dumping data from server
–skip-add-drop-table Do not add a DROP TABLE statement before each CREATE TABLE statement
–skip-add-locks Do not add locks
–skip-comments Do not add comments to dump file
–skip-compact Do not produce more compact output
–skip-disable-keys Do not disable keys
–skip-extended-insert Turn off extended-insert
–skip-opt Turn off options set by –opt
–skip-quick Do not retrieve rows for a table from the server a row at a time
–skip-set-charset Do not write SET NAMES statement
–skip-triggers Do not dump triggers
–socket Unix socket file or Windows named pipe to use
–tables Override –databases or -B option
–triggers Dump triggers for each dumped table
–user MySQL user name to use when connecting to server
–verbose Verbose mode
–version Display version information and exit
–where Dump only rows selected by given WHERE condition
–xml Produce XML output

恢复

  • 登录情况下：source 备份文件
  • 不登录情况下：mysql -u -p 库名 < xx.sql

案例

1
2
3
4
5
6
7
8
9
10
11
# 全局只读锁
flush tables with read lock;

# 备份
mysqldump -uroot -pHaier@123 --all-databases --triggers --routines --events --single-transaction --master-data=2 > all_databases.sql

# 传输
scp master.sql root@x.x.x.x:/tmp

# 导入
mysql -uroot -p'xx' < all_databases.sql

发表于 | 分类于

判断

Seconds_Behind_Master 值不为 0，单位是 s。

风险

  • 从库复制夯住，会导致备份失败（flush tables with read lock 900s 超时）
  • 以从库为基准进行的备份，数据不是最新的有延迟
  • 异常情况下，主从 HA 无法切换，HA 需要检查数据的一致性，延迟时主备不一致

原因

最根本原因是主库是多线程读写，从库读取主库的 binlog 的线程只有一个，常见的场景如下：

  • 无主键、无索引或索引区分度不高
1
2
3
show slave status;
show open tables where In_use=1;
desc table;
  • 主库上有大事务，导致从库延时
  • 主库写入频繁，从库压力跟不上导致延时
  • 大量 myisam 表，在备份时导致 slave 延迟

解决方法

  • 为夯住的表添加主键或者索引
1
2
3
4
5
6
7
8
9
10
# 找到表区分度比较高的几个字段
select count(*) from xx;
select count(*) from (select distinct xx from xxx) t;

# 添加主键
stop slave;
set sql_log_bin=0;
alter table xx add key xx(xx);
alter table 'xxxx' add id int(10) unsigned primary key AUTO_INCREMENT;
start slave
  • 调整数据库中 IO 相关参数
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                0 |
+----------------------------------+

mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
|             0 |
+---------------+
  • 修改表存储引擎为 InnoDB

案例 1

现象：从库两个线程 Slave_IO_Running 和 Slave_SQL_Running 均是 Yes，主从复制链路正常，但是 Seconds_Behind_Master 不断增大，且主从数据延迟有不断扩大的趋势。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.133.0.51
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000232
          Read_Master_Log_Pos: 3108253
               Relay_Log_File: relay-log.000004
                Relay_Log_Pos: 169851605
        Relay_Master_Log_File: mysql-binlog.000229
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              ....
        Seconds_Behind_Master: 42302
Master_SSL_Verify_Server_Cert: No
              ....
  • 检查当前数据库线程状态，未发现有明显异常
1
2
3
4
5
6
7
8
9
10
mysql> show processlist;
+-------+-------------+-------------------+--------------------+---------+--------+----------------------------------+--------------------------------+
| Id    | User        | Host              | db                 | Command | Time   | State                            | Info                           |
+-------+-------------+-------------------+--------------------+---------+--------+----------------------------------+--------------------------------+
|     1 | system user |                   | NULL               | Connect | 172777 | Waiting for master to send event | NULL                           |
|     2 | system user |                   | NULL               | Connect | 678456 | Reading event from the relay log | NULL                           |
|  4345 | cvbak       | localhost         | NULL               | Query   | 130546 | Waiting for table flush          | FLUSH /*!40101 LOCAL */ TABLES |
| 17662 | root        | localhost         | NULL               | Query   |      0 | starting                         | show processlist               |
| 17671 | hdm         | 10.133.0.52:37170 | information_schema | Sleep   |      1 |                                  | NULL                           |
+-------+-------------+-------------------+--------------------+---------+--------+----------------------------------+--------------------------------+
  • 检查当前正在使用的表：show open tables where In_use=1; 发现有一张表一直处于 In_use 状态。
1
2
3
4
5
6
7
mysql> show open tables where In_use=1;
+----------+---------------------------+--------+-------------+
| Database | Table                     | In_use | Name_locked |
+----------+---------------------------+--------+-------------+
| tdmetl   | odsepg_zrfc_zco_zzfymx_sw |      1 |           0 |
+----------+---------------------------+--------+-------------+
1 row in set (0.00 sec)
  • 根据 Relay_Log_Pos 解析 relay-log.000004，查看当时正在执行的操作是 Delete_rows，操作的表是 tdmetl.odsepg_zrfc_zco_zzfymx_sw。
1
mysqlbinlog -vv --base64-output=decode-rows relay-log.000004 --start-position=169851605 | more
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# at 169851605
#190718  4:02:58 server id 3306100  end_log_pos 169851451 CRC32 0x6683bf99 	Anonymous_GTID	last_committed=1831	sequence_number=1832	rbr_
only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 169851670
#190718  4:02:58 server id 3306100  end_log_pos 169851525 CRC32 0x9d910e5d 	Query	thread_id=1254417	exec_time=0	error_code=0
SET TIMESTAMP=1563393778/*!*/;
SET @@session.pseudo_thread_id=1254417/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549128/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;

# at 169851744
#190718  4:02:58 server id 3306100  end_log_pos 169851626 CRC32 0x6b8bbd2d 	Table_map: `tdmetl`.`odsepg_zrfc_zco_zzfymx_sw` mapped to number 170
018

# at 169851845
#190718  4:02:58 server id 3306100  end_log_pos 169859832 CRC32 0x338e105a 	Delete_rows: table id 170018
  • 检查表结构，发现该表数据量很大且无主键。由此基本确定其问题根本原因：对无主键的表进行删除或者更新，导致从库夯住。该表数据量：77961221条。

image-20190822092437992

解决方法

  • 表添加自增主键。
1
ALTER TABLE 'xxxx' ADD id int UNSIGNED primary key AUTO_INCREMENT;
  • 大表删除使用 truncate 命令。

案例 2

原因分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
mysql> mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.133.7.5
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000279
          Read_Master_Log_Pos: 663610631
               Relay_Log_File: mysqld-relay-bin.000007
                Relay_Log_Pos: 139799382
        Relay_Master_Log_File: mysql-bin.000273
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                            ....
        Seconds_Behind_Master: 475123
        
mysql> show open tables where In_use=1; 
+----------+-------------------+--------+-------------+
| Database | Table             | In_use | Name_locked |
+----------+-------------------+--------+-------------+
| hce      | dim_r_cei_general |      1 |           0 |
+----------+-------------------+--------+-------------+

mysql> desc dim_r_cei_general;
+---------------------+------------------+------+-----+-------------------+----------------+
| Field               | Type             | Null | Key | Default           | Extra          |
+---------------------+------------------+------+-----+-------------------+----------------+
| MATERIAL_CODE       | varchar(30)      | YES  |     | NULL              |                |
| MATERIAL_DESCRITION | varchar(383)     | YES  |     | NULL              |                |
| PA_DATE_ACTUAL      | varchar(30)      | YES  |     | NULL              |                |
| HR_INDEX            | varchar(30)      | YES  |     | NULL              |                |
| SALE_CHAR           | varchar(100)     | YES  |     | NULL              |                |
| CREATE_DATE         | datetime         | YES  |     | CURRENT_TIMESTAMP |                |
| id                  | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
+---------------------+------------------+------+-----+-------------------+----------------+

解决方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0     |
+--------------------------------+-------+

mysql> select count(*) from dim_r_cei_general;
+----------+
| count(*) |
+----------+
|  1933121 |
+----------+

mysql> select count(*) from (select distinct MATERIAL_CODE from dim_r_cei_general) t;
+----------+
| count(*) |
+----------+
|  1933111 |
+----------+

mysql> stop slave;
mysql> set sql_log_bin=0;
mysql> alter table dim_r_cei_general add key index_MATERIAL_CODE(MATERIAL_CODE);
mysql> start slave;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.133.7.5
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000279
          Read_Master_Log_Pos: 663999593
               Relay_Log_File: mysqld-relay-bin.000010
                Relay_Log_Pos: 336125628
        Relay_Master_Log_File: mysql-bin.000274
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                            ....
        Seconds_Behind_Master: 462357

发表于 | 分类于

数据类型

数值类型

类型 大小 范围（有符号） 范围（无符号）
TINYINT 1 字节 -128 ～ 127 0 ～ 255
SMALLINT 2 字节 -32768 ～ 32767 0 ～ 65535
MEDIUMINT 3 字节 -8388608 ～ 8388607 0 ～ 16777215
INT 4 字节 -2147483648 ～ 2147483647 0 ～ 4294967295
BIGINT 8 字节 -2^63 ～ 2^63-1 0 ～ 18446744073709551615
FLOAT 4 字节 – –
DOUBLE 8 字节 – –
DECIMAL[(M,D)] M+2/D+2 M：总位数，D：小数点位数 依赖 M 和 D 的值

字符串类型

类型 存储范围 用途
CHAR(M) M 字节，其中 M 为 0 ～ 255 定长字符串
VARCHAR(M) L + 1 字节，0 ～ 255 变长字符串
TINYTEXT – –
TEXT L + 2 字节，0 ～ 65535 文章等文本数据
MIUDMTEXT – –
LONGTEXT – –
BLOB 0 ～ 65535 图片等二进制形式数据
MIUDMBLOB – –
LONGBLOB – –
ENUM(‘val’,’val’) 1 或 2 字节，取决于枚举的个数 –
SET(‘val’,’val’,’val’) 1, 2, 3, 4, 8 字节，取决于成员的数目 –

时间类型

类型 大小 范围 格式 用途
DATE 3 字节 1000-01-01～9999-12-31 YYYY-MM-DD 日期值
TIME 3 字节 -838:59:59/838:59:59 HH:MM:SS 时间值
YEAR 1 字节 1901/2155 YYYY 年份值
DATETIME 8 字节 1000-01-01 00:00:00/9999 YYYY-MM-DD HH:MM:SS 日期和时间值
TIMESTAMP 4 字节 1970-01-01 00:00:00/2038 YYYYMMDD HHMMSS 日期和时间值

选择

原则

  • 选择合理范围内最小的，这样可以大大减少磁盘空间及磁盘 I/0 读写开销，减少内存占用，减少 CPU 占用率。
  • 选择相对简单的数据类型，数字类型相对字符串类型要简单的多。
  • 尽量避免 NULL，这是因为 MySQL 对 NULL 字段索引优化不佳，解决方法是数值弄用整数 0，字符串用空来定义默认值即可。

示例

  • 手机号：char(11)
  • 姓名：char(20)
  • 价格：decimal(7, 3)
  • 产品序列号：smallint(5) unsigned
  • 文章内容: text
  • MD5: char(32)
  • IP: char(15)
  • Email：char(32)
  • 性别：enum(‘男’,’女’)
  • 布尔值：tinyint(1)

发表于 | 更新于

学习教程

https://github.com/judasn/IntelliJ-IDEA-Tutorial

快捷键

1、打开项目属性配置对话框：⌘ Cmd + ;

2、打开整个 IDEA 属性配置对话框：⌘ Cmd + ,

3、搜索文件名：↑ Shift 快速连续按两下

4、显示便捷提示和快速修复：⌘ Cmd + ⤶ Enter

5、快速生成代码：^  Control + ⤶ Enter

6、选中方法，查看参数信息：⌘ Cmd + P

7、弹出当前打开的文件列表：⌘ Cmd + E

8、保存文件：⌘ Cmd + S

9、查找当前文件：⌘ Cmd + F

10、快速定位到某一行某一列：⌘ Cmd + G

11、替换：⌘ Cmd + R

12、在路径里寻找：^  Control + ↑ Shift + F

13、在路径里替换：^  Control + ↑ Shift + R

14、环绕选中代码块，生成注释：⌘ Cmd + ⌥ Option + T

15、注释代码或者取消注释：⌘ Cmd + /

16、格式化代码：⌘ Cmd + ⌥ Option + L

17、优化导入，删除掉没用被使用的导入：⌘ Cmd + ⌥ Option + O

18、缩进选中的行：Tab

19、取消缩进选中的行：↑ Shift + Tab

20、将当前行剪切到剪贴板：⌘ Cmd + X

21、将当前行复制到剪贴板：⌘ Cmd + C

22、从粘贴板复制：⌘ Cmd + V

23、重复输入当前行：⌘ Cmd + D

24、开始新的一行：↑ Shift + ⤶ Enter

25、选中的单词或者代码，切换大小写：⌘ Cmd + ↑ Shift + U

26、返回或者前进最近编辑的地方：⌘ Cmd + ⌥ Option + ← / →

27、打开变量声明处：⌘ Cmd + B 或者 ⌘ Cmd + 鼠标左键

28、打开类型声明处：^ Control + ↑ Shift + B

29、插入注释模板：⌘ Cmd + J

官方快捷键

发表于 | 更新于 | 分类于

SQL 查询执行过程

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分：

  • Server 层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能。
    • 连接器：跟客户端建立连接、获取权限、维持和管理连接，建议使用长连接。
    • 查询缓存：(key,value) 方式存储，命中返回、未命中执行下一步，更新时缓存失效，不建议打开。
    • 解析器：进行词法分析、语义分析生成解析树，预处理器会检查解析树的合法性。
    • 优化器：根据代价模型，选择索引和执行顺序，生成执行计划。
    • 执行器：验证权限，调存储引擎接口返回结果，并更新查询缓存。
  • 存储引擎层：与数据库文件打交道，负责数据的存储和读取。

SQL 更新执行过程

与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）。

背景：MySQL 有个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了 Write-Ahead Logging 思想（它的关键点就是先写日志，再写磁盘）来提升更新效率。

redo log

1
2
-rw-r----- 1 mysql mysql 1.0G Sep 17 07:20 ib_logfile0
-rw-r----- 1 mysql mysql 1.0G Sep 17 07:15 ib_logfile1

说明：记录数据页做了什么改动，而不是这个页更新之后的状态。

作用：有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）。

原理：InnoDB 的 redo log 是固定大小的，每个文件的大小是 1GB，从头开始写，写到末尾就又回到开头循环写。write pos 是当前记录的位置，checkpoint 是当前要擦除的位置，如果 write pos 追上 checkpoint，表示满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

参数：innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

binlog

1
2
3
4
-rw-r----- 1 mysql mysql 1.1G Sep 11 07:17 mysql-bin.000098
-rw-r----- 1 mysql mysql 1.1G Sep 15 07:17 mysql-bin.000099
-rw-r----- 1 mysql mysql 515M Sep 17 07:20 mysql-bin.000100
-rw-r----- 1 mysql mysql   87 Sep 15 07:17 mysql-bin.index

上面介绍的 redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

说明：binlog 有两种模式，statement 格式的话是记 sql 语句， row 格式会记录行的内容，记两条更新前和更新后都有。

作用：binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式，用于备份恢复。

参数：sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数也建议设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

区别：

  • redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  • redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  • redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

update 执行流程

  • 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
  • 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
  • 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
  • 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交 commit 状态，更新完成。

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是 “两阶段提交”。简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个数据状态保持逻辑上的一致。

发表于 | 更新于 | 分类于

简述

在 MySQL 中，当数据量增长的特别大的时候就需要用到索引来优化 SQL 语句，使用 Explain 来查看 SQL 语句的执行计划，是优化 SQL 最有效地的一种手段。

使用方法

语法：explain + SQL

示例：explain select * from customer

1
2
3
4
5
6
7
mysql> explain select * from customer;
+----+-------------+----------+------+---------------+------+---------+------+--------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | 
+----+-------------+----------+------+---------------+------+---------+------+--------+
|  1 | SIMPLE      | customer | ALL  | NULL          | NULL | NULL    | NULL | 936161 |       
+----+-------------+----------+------+---------------+------+---------+------+--------+
1 row in set

参数介绍

id

含义：SELECT 标识符，SQL 执行顺序的标识。

三种情况：

  • id 相同，执行顺序由上至下。

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column = '';
    +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
    | id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
    +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
    | 1  | SIMPLE      | t1    | ALL    | PRIMARY       | NULL    | NULL    | NULL          | 5    | Using where |
    | 1  | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | test_db.t1.id | 1    |             |
    | 1  | SIMPLE      | t3    | eq_ref | PRIMARY       | PRIMARY | 4       | test_db.t1.id | 1    | Using index |
    +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

  • id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.other_column = ''));
    +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
    | id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
    +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
    | 1  | PRIMARY     | t2    | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
    | 2  | SUBQUERY    | t1    | const | PRIMARY       | PRIMARY | 4       |       | 1    | Using index |
    | 3  | SUBQUERY    | t3    | ALL   | NULL          | NULL    | NULL    | NULL  | 5    | Using where |
    +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

  • id 有相同也有不同，id 值越大，优先级越高，越先执行；id 相同，则认为是一组，从上往下顺序执行。

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.other_column = ''));
    +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
    | id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
    +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
    | 1  | PRIMARY     | t2    | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
    | 2  | SUBQUERY    | t1    | const | PRIMARY       | PRIMARY | 4       |       | 1    | Using index |
    | 3  | SUBQUERY    | t3    | ALL   | NULL          | NULL    | NULL    | NULL  | 5    | Using where |
    +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

select_type

含义：查询类型，主要用于区别普通查询、联合查询、子查询等复杂查询。

六种类型：

  • SIMPLE：简单的 select 查询，查询中不包含子查询或者 union 联合查询。
  • PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为 PRIMARY。
  • SUBQUERY：在 select或 where 列表中包含了子查询。
  • DERIVED：在 from 列表中包含的子查询被标记为 DERIVED（衍生），MySQL 会递归执行这些子查询，把结果放在临时表里。
  • UNION：若第二个 select 出现在 union 之后，则被标记为 union；若 union 包含在 from 字句的查询中，外层 select 将被标记为 DERIVED。
  • UNION RESULT：两种 union 语句的合并。

table

含义：查询涉及的表或者衍生表。

type

含义：显示连接使用的类型。常见的有7种，从最好到最差排序：system > const > eq_ref > ref > range > index > all（全表扫描），一般来说 至少达到 range 级别，最好达到 ref。

七种类型：

  • system：表只有一行记录，这是 const 类型的特例，只能用于 myisam 和 memory 表，可以忽略不计。

  • const：表示通过索引一次就找到了，const 用于 primary key 或者 unique 索引，因为只匹配一行数据，所以很快，如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。

1
2
3
4
5
6
7
mysql> explain select * from (select * from t1 where id =1) d1;
+----+-------------+------------+--------+---------------+---------+---------+------+----
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+----
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | t1         | const  | PRIMARY       | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+---------------+---------+---------+------+----
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。
1
2
3
4
5
6
7
mysql> explain select * from t1,t2 where t1.id = t2.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------
|  1 | SIMPLE      | t1    | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    5 |       |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | test_db.t1.id |    1 |       |
+----+-------------+-------+--------+---------------+---------+---------+---------------
  • ref：非唯一索引或者主键扫描，或者使用了最左前缀规则索引的查询，它返回匹配某个单独值的所有行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
1
2
3
4
5
6
7
mysql> create index idx_name on t1(name);
mysql> explain select * from t1 where name ='Tim';
+----+-------------+-------+------+---------------+------+---------+-------+------+------
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+------
|  1 | SIMPLE      | t1    | ref  | idx_name          | idx_name  | 92      | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+------
  • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的 where语句中出现了 between、<>、in 等查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
1
2
3
4
5
6
mysql> explain select * from t1 where id between 2 and 5;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---
|  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---
  • index：表示全索引扫描 (full index scan)，index 与 all 区别为 index 类型只扫描所有的索引不扫描数据，通常比 all 快，因为索引文件通常比数据文件小。
1
2
3
4
5
6
mysql> explain select id from t1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---
|  1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 4       | NULL |    6 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---
  • all：表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说，查询不应该出现 all 类型的查询, 因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。
1
2
3
4
5
6
mysql> explain select * from t2 where other_column = '';
+----+-------------+-------+------+---------------+------+---------+------+------+-------
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------

possible_keys

含义：显示可能应用在这张表中的索引。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

含义：实际使用的索引。如果为 NULL，则没有使用索引，key 参数可以作为使用了索引的判断标准。

key_len

含义：使用的索引的长度。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度。在不损失精确性的情况下，长度越短越好。

ref

含义：显示索引的哪一列被使用了，如果可能的话是一个常数，哪些列或常量被用于查找索引列上的值。

rows

含义：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，当然越小越好。

Tips：explain 结果中，join 的查询代价可以用依次连乘 rows 估算。

Extra

含义：附加信息，提供了与操作有关联的信息。

三种类型：

  • Using filesort：比较危险。说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称之为文件排序，需要进行额外的步骤来发现如何对返回的行排序。
1
2
3
4
5
6
mysql> explain select other_column from t1 where other_column = 'A' order by other_column3;
+----+-------------+-------+------+---------------+------+---------+-------+------+---------
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+-------+------+---------
|  1 | SIMPLE      | t1    | ref  | idx           | idx  | 92      | const |    1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+-------+------+---------

优化案例：

1
2
3
4
5
6
7
mysql> create index u_idx2 on t1(other_column,other_column2,other_column3);
mysql> explain select other_column from t1 where other_column = 'A' order by other_column2,other_column3;
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------
|  1 | SIMPLE      | t1    | ref  | idx,u_idx2    | u_idx2 | 92      | const |    1 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------
  • Using temporary：比较危险，使用了临时表保存中间结果。这通常发生在对不同的列集进行 ORDER BY 上和 GROUP BY 上，拖慢了查询速度。
1
2
3
4
5
6
mysql> explain select other_column from t1 where other_column in ('A','B','C') group by other_column3;
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra                                                     |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------
|  1 | SIMPLE      | t1    | range | idx,u_idx2    | u_idx2 | 92      | NULL |    3 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------

优化案例：

1
2
3
4
5
6
mysql> explain select other_column from t1 where other_column in ('A','B','C') group by other_column,other_column2;
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------
|  1 | SIMPLE      | t1    | range | idx,u_idx2    | u_idx2 | 92      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------

  • Using index：表示相应的 select 操作中使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表明索引用来读取数据而非执行查找动作。

    覆盖索引： 就是 select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。注意的是，如果要使用覆盖索引，一定要注意 select 列表中只读取出需要的列，而不是 select *，因为如果将所有字段一起做索引会导致索引文件过大，降低查询性能。

1
2
3
4
5
6
mysql> explain select other_column from t1 where other_column in ('A','B','C') group by other_column,other_column2;
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------
|  1 | SIMPLE      | t1    | range | idx,u_idx2    | u_idx2 | 92      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------
1
2
3
4
5
6
mysql> explain select other_column,other_column2,other_column3 from t1;
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------
|  1 | SIMPLE      | t1    | index | NULL          | u_idx2 | 276     | NULL |    6 | Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------

Tips: 在 show table status 结果中看到的 Rows 用于表示表的当前行数。对于 MyISAM 表这是一个精确值，但对InnoDB 这是个估算值。

发表于 | 更新于 | 分类于

示意图

处理流程

处理过程解析

输入 URL

URI：Uniform Resource Identifier，统一资源标识符，用字符串标识某一互联网资源。

URL：Uniform Resource Locator，统一资源定位符，URL 是 URI 的子集，也就是我们通常使用的网页地址。

1
2
3
4
5
# HTTP URL格式
http://<host>:<port>/<path>?<searchpart>

# 文件URL格式
file://<host>/<path>

DNS 域名解析

功能：简单的说就是把域名翻译成 IP 地址，基本过程如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
➜  hexo dig +cmd +trace www.baidu.com   

; <<>> DiG 9.10.6 <<>> +cmd +trace www.baidu.com
;; global options: +cmd
.			103	IN	NS	j.root-servers.net.
.			103	IN	NS	g.root-servers.net.
.			103	IN	NS	a.root-servers.net.
.			103	IN	NS	i.root-servers.net.
.			103	IN	NS	e.root-servers.net.
.			103	IN	NS	f.root-servers.net.
.			103	IN	NS	d.root-servers.net.
.			103	IN	NS	h.root-servers.net.
.			103	IN	NS	l.root-servers.net.
.			103	IN	NS	m.root-servers.net.
.			103	IN	NS	c.root-servers.net.
.			103	IN	NS	k.root-servers.net.
.			103	IN	NS	b.root-servers.net.
;; Received 239 bytes from 10.138.92.76#53(10.138.92.76) in 94 ms

com.			172800	IN	NS	l.gtld-servers.net.
com.			172800	IN	NS	b.gtld-servers.net.
com.			172800	IN	NS	c.gtld-servers.net.
com.			172800	IN	NS	d.gtld-servers.net.
com.			172800	IN	NS	e.gtld-servers.net.
com.			172800	IN	NS	f.gtld-servers.net.
com.			172800	IN	NS	g.gtld-servers.net.
com.			172800	IN	NS	a.gtld-servers.net.
com.			172800	IN	NS	h.gtld-servers.net.
com.			172800	IN	NS	i.gtld-servers.net.
com.			172800	IN	NS	j.gtld-servers.net.
com.			172800	IN	NS	k.gtld-servers.net.
com.			172800	IN	NS	m.gtld-servers.net.
com.			86400	IN	DS	30909 8 2 E2D3C916F6DEEAC73294E8268FB5885044A833FC5459588F4A9184CF C41A5766
com.			86400	IN	RRSIG	DS 8 1 86400 20191219050000 20191206040000 22545 . OBLBMAKPWdA9vtl+G+51COaZLcCCWqZZHqV/EgOTAVGNDx4JrinTOynB eY2PHFjv4VbzRhjZxic7LH3gVJhO0T7nu+VygYvL2jshHIP+1uc15fzl 42PgBvJEVVV2FZoMDoS72wJ10jK/dN2PfhPfXTrK42XqGDa253opx2W2 +aTcu4YsXCbEjn7z2wg5LguHcsFX56zhYONsnH2UWtCAQCQhBH64M/NL CweHmJNTEjvhBQOATGB66vuhkgn5v9EpBwRSH/MG6klVj6KOfcpHpbAj GKjazHo3A7l4e5EkVYXNhDPF2bI3/thcplNFkGDvuAAfrftfnvddZWgB beBOGg==
;; Received 1173 bytes from 192.203.230.10#53(e.root-servers.net) in 96 ms

baidu.com.		172800	IN	NS	ns2.baidu.com.
baidu.com.		172800	IN	NS	ns3.baidu.com.
baidu.com.		172800	IN	NS	ns4.baidu.com.
baidu.com.		172800	IN	NS	ns1.baidu.com.
baidu.com.		172800	IN	NS	ns7.baidu.com.
CK0POJMG874LJREF7EFN8430QVIT8BSM.com. 86400 IN NSEC3 1 1 0 - CK0Q1GIN43N1ARRC9OSM6QPQR81H5M9A  NS SOA RRSIG DNSKEY NSEC3PARAM
CK0POJMG874LJREF7EFN8430QVIT8BSM.com. 86400 IN RRSIG NSEC3 8 2 86400 20191213054807 20191206043807 12163 com. NjRosY9LtJZsqeG+g+/JAi8jqB/0KYSqvkI/a4KEUM8atk25ERfJ+69Y WLKQDc048p4OC4TFn/R+Z73M3Xo1uAp0QSiBOzQXasVb0RvXKJOIfDyy mIMQh9IJGXXBMNgrbCG1AaN4DyU4uTxi6nIsoOc58h1FYov7seLt9ezy waw1r5UbrRg6J7xML7Ge/yui3VffXOYoeitCRJZkeTvcGg==
HPVV2B5N85O7HJJRB7690IB5UVF9O9UA.com. 86400 IN NSEC3 1 1 0 - HPVVN3Q5E5GOQP2QFE2LEM4SVB9C0SJ6  NS DS RRSIG
HPVV2B5N85O7HJJRB7690IB5UVF9O9UA.com. 86400 IN RRSIG NSEC3 8 2 86400 20191211055253 20191204044253 12163 com. LW49gsAlOuvZZeH44KGPPfP+9wOlaCwJ+y0PyStRG+5XLjM1Ah6tDlY5 J65QbqG6IyucqnZIyPbXc+pZnnMFxrTG8tyQ2JenP7bCtVBmkjmMQa9g 6Kocjnb5RiGYIqpX+IfX5y43KmLiQr+Ikh24HuVfpow30C+8qxDpt9UB CMIoFLtBjjZPFcX6XRxrIgNeN2NXCFSDwDw73HEq74nCAw==
;; Received 761 bytes from 192.33.14.30#53(b.gtld-servers.net) in 507 ms

www.baidu.com.		1200	IN	CNAME	www.a.shifen.com.
a.shifen.com.		1200	IN	NS	ns1.a.shifen.com.
a.shifen.com.		1200	IN	NS	ns2.a.shifen.com.
a.shifen.com.		1200	IN	NS	ns5.a.shifen.com.
a.shifen.com.		1200	IN	NS	ns3.a.shifen.com.
a.shifen.com.		1200	IN	NS	ns4.a.shifen.com.
;; Received 239 bytes from 14.215.178.80#53(ns4.baidu.com) in 97 ms
  • DNS 根域名服务器（13台）：．
  • DNS 主域名服务器（13台）：com.
  • 管理方 DNS 服务器：baidu.com.

举个例子，你想知道某个一起上法律课的女孩的电话，并且你偷偷拍了她的照片，回到寝室告诉一个很仗义的哥们儿，这个哥们儿二话没说，拍着胸脯告诉你，甭急，我替你查（此处完成了一次递归查询，即，问询者的角色更替）。然后他拿着照片问了学院大四学长，学长告诉他，这姑娘是xx系的；然后这哥们儿马不停蹄又问了xx系的办公室主任助理同学，助理同学说是xx系yy班的，然后很仗义的哥们儿去xx系yy班的班长那里取到了该女孩儿电话（此处完成若干次迭代查询，即，问询者角色不变，但反复更替问询对象）。最后，他把号码交到了你手里，完成整个查询过程。

建立 TCP 连接

拿到域名对应的 IP 地址后，浏览器会以一个随机端口（1024 < 端口 < 65535）向服务器的 WEB 程序发起 TCP 的连接请求。

三次握手：

  • Client 首先发送一个连接试探
  • Server 监听到连接请求报文后，如同意建立连接，则向 Client 发送确认
  • Client 收到确认后还需再次发送确认，同时携带要发送给 Server 的数据

抓包

发送 HTTP 请求

HTTP 协议

服务器处理 HTTP 请求

服务器在接收到请求后，解析用户请求，知道要调度哪些资源文件，再通过相应的资源文件处理用户的请求和参数，并调用数据库，将结果通过 web 服务器返回给浏览器。

Nginx

关闭 TCP 连接

数据传输完成后，为了避免服务器与客户端双方的资源占用和损耗，会经过四次挥手，关闭 TCP 连接。

浏览器渲染页面

发表于 | 更新于 | 分类于

背景

编程规范中，经常会看到一条，不建议使用 select * 语句，然而实际开发中又屡禁不止，有两个问题：

  • 为什么 select * 影响 SQL 性能？
  • 有多大的影响？

为什么 select * 是低效语句？

主要体现在三个方面：

  • select * 会让优化器无法使用覆盖索引优化。
  • 网络开销，这种开销非常明显。
  • 额外的 IO / CPU 开销，因为多取了不必要的列。

有多大的影响？

  • 测试表信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> desc salaries;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int(11) | NO   | PRI | NULL    |       |
| salary    | int(11) | NO   |     | NULL    |       |
| from_date | date    | NO   | PRI | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+

mysql> select count(*) from salaries;
+----------+
| count(*) |
+----------+
|  2844047 |
+----------+
  • 执行速度对比：查询返回字段越多，耗时越多。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select * from salaries;
2844047 rows in set (1.01 sec)

mysql> select emp_no from salaries;
2844047 rows in set (0.56 sec)

mysql> select salary,to_date from salaries;
2844047 rows in set (0.67 sec)

mysql> select emp_no,salary,to_date from salaries;
2844047 rows in set (0.86 sec)

mysql> select emp_no,salary,from_date,to_date from salaries;
2844047 rows in set (1.00 sec)
  • 覆盖索引：是可以不用读 data，直接使用 index 里面的值就返回结果。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

mysql> explain select last_name from employees;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 298980 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+

mysql> explain select emp_no from employees;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | PRIMARY | 4       | NULL | 298980 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

发表于 | 更新于 | 分类于

背景

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select table_name,table_rows,CONCAT(
TRUNCATE(SUM(data_length)/1024/1024/1024,2),'GB') AS data_size, CONCAT(
TRUNCATE(SUM(index_length)/1024/1024/1024,2),'GB') AS index_size from information_schema.tables where TABLE_SCHEMA = 'dzspzy' and table_name='t_hr_13plan_res_history';

+-------------------------+------------+-----------+------------+
| table_name              | table_rows | data_size | index_size |
+-------------------------+------------+-----------+------------+
| t_hr_13plan_res_history |  296397209 | 110.43GB  | 3.72GB     |
+-------------------------+------------+-----------+------------+

+-------------------------+------------+-----------+------------+
| table_name              | table_rows | data_size | index_size |
+-------------------------+------------+-----------+------------+
| t_hr_13plan_res_history |  269082432 | 102.44GB  | 3.45GB     |
+-------------------------+------------+-----------+------------+

+-------------------------+------------+-----------+------------+
| table_name              | table_rows | data_size | index_size |
+-------------------------+------------+-----------+------------+
| t_hr_13plan_res_history |  213501502 | 78.35GB   | 2.64GB     |
+-------------------------+------------+-----------+------------+

清理

  • 批量删除
1
pt-archiver --source u=sre,p='xx',S=/tmp/mysql-dzsp.sock,P=3306,D=dzspzy,t=t_hr_13plan_res_history --no-check-charset --progress 10000 --where "ID < 251061689" --limit 10000 --txn-size 1000 --bulk-delete --purge --statistics
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
show table status like 't_hr_13plan_res_history'\G
*************************** 1. row ***************************
           Name: t_hr_13plan_res_history
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 205639249
 Avg_row_length: 410
    Data_length: 84329660416
Max_data_length: 0
   Index_length: 2843754496
      Data_free: 35414605824
 Auto_increment: 590343627
    Create_time: 2019-03-27 13:18:10
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment:
  • 整理碎片
1
2
3
4
5
6
7
8
# 方法一
alter table t_hr_13plan_res_history engine = innodb

# 方法二
optimize table t_hr_13plan_res_history

# 方法三
pt-online-schema-change --alter="ENGINE=innodb" D=dzspzy,t=t_hr_13plan_res_history --execute