搭建 MySQL 5.7.19 主从复制，以及复制实现细节分析

概念

主从复制可以使MySQL数据库主服务器的主数据库，复制到一个或多个MySQL从服务器从数据库，默认情况下，复制异步; 根据配置，可以复制数据库中的所有数据库，选定的数据库或甚至选定的表。

MySQL中主从复制的优点

横向扩展解决方案

在多个从库之间扩展负载以提高性能。在这种环境中，所有写入和更新在主库上进行。但是，读取可能发生在一个或多个从库上。该模型可以提高写入的性能（由于主库专用于更新），同时在多个从库上读取，可以大大提高读取速度。

数据安全性

由于主库数据被复制到从库，从库可以暂停复制过程，可以在从库上运行备份服务，而不会破坏对应的主库数据。

分析

可以在主库上创建实时数据，而信息分析可以在从库上进行，而不会影响主服务器的性能。

长距离数据分发

可以使用复制创建远程站点使用的数据的本地副本，而无需永久访问主库。

1.准备工作

参考 MySQL官网 - 第16章主从复制

Mysql版本：MySQL 5.7.19
Master-Server : 192.168.252.123
Slave-Server : 192.168.252.124

关闭防火墙

1

$ systemctl stop firewalld.service

安装 MySQL

参考 - CentOs7.3 安装 MySQL 5.7.19 二进制版本

首先在两台机器上装上，保证正常启动，可以使用

2. Master-Server 配置

修改 my.cnf

配置 Master 以使用基于二进制日志文件位置的复制，必须启用二进制日志记录并建立唯一的服务器ID,否则则无法进行主从复制。

停止MySQL服务。

1

$ service mysql.server stop

开启binlog ，每台设置不同的 server-id

1
2
3
4

$ cat /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

启动MySQL服务

1

$ service mysql.server start

登录MySQL

1

$ /usr/local/mysql/bin/mysql -uroot -p

创建用户

每个从库使用MySQL用户名和密码连接到主库，因此主库上必须有用户帐户，从库可以连接。任何帐户都可以用于此操作，只要它已被授予 REPLICATION SLAVE权限。可以选择为每个从库创建不同的帐户，或者每个从库使用相同帐户连接到主库

虽然不必专门为复制创建帐户，但应注意，复制用到的用户名和密码会以纯文本格式存储在主信息存储库文件或表中 。因此，需要创建一个单独的帐户，该帐户只具有复制过程的权限，以尽可能减少对其他帐户的危害。

登录MySQL

1

$ /usr/local/mysql/bin/mysql -uroot -p

1
2

mysql> CREATE USER 'replication'@'192.168.252.124' IDENTIFIED BY 'mima';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.252.124';

3.Slave-Server 配置

修改 my.cnf

停止MySQL服务。

1

$ service mysql.server stop

1
2
3

$ cat /etc/my.cnf
[mysqld]
server-id=2

如果要设置多个从库，则每个从库的server-id与主库和其他从库设置不同的唯一值。

启动MySQL服务

1

$ service mysql.server start

登录MySQL

1

$ /usr/local/mysql/bin/mysql -uroot -p

配置主库通信

查看 Master-Server ， binlog File 文件名称和 Position值位置 并且记下来

1
2
3
4
5
6
7

mysql>  show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      629 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

要设置从库与主库进行通信，进行复制，使用必要的连接信息配置从库在从库上执行以下语句
将选项值替换为与系统相关的实际值

参数格式，请勿执行

1
2
3
4
5
6

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

1
2
3
4
5
6
7

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.252.123',
    -> MASTER_USER='replication',
    -> MASTER_PASSWORD='mima',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=629;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

MASTER_LOG_POS=0 写成0 也是可以的

放在一行执行方便

1

CHANGE MASTER TO MASTER_HOST='192.168.252.123', MASTER_USER='replication', MASTER_PASSWORD='mima', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=629;

启动从服务器复制线程

1
2

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

查看复制状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql>  show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.252.123
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 629
               Relay_Log_File: master2-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

检查主从复制通信状态

Slave_IO_State #从站的当前状态
Slave_IO_Running： Yes #读取主程序二进制日志的I/O线程是否正在运行
Slave_SQL_Running： Yes #执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样
Seconds_Behind_Master #是否为0，0就是已经同步了

必须都是 Yes

如果不是原因主要有以下 4 个方面：

1、网络不通
2、密码不对
3、MASTER_LOG_POS 不对 ps
4、mysql 的 auto.cnf server-uuid 一样（可能你是复制的mysql）

1
2
3
4

$ find / -name 'auto.cnf'
$ cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=6b831bf3-8ae7-11e7-a178-000c29cb5cbc # 按照这个16进制格式，修改server-uuid，重启mysql即可

检查复制状态

4.测试主从复制

启动MySQL服务

1

$ service mysql.server start

登录MySQL

1

$ /usr/local/mysql/bin/mysql -uroot -p

在 Master-Server 创建测试库

1
2
3

mysql> CREATE DATABASE `replication_wwww.ymq.io`;
mysql> use `replication_wwww.ymq.io`;
mysql> CREATE TABLE `sync_test` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

在 Slave-Server 查看是否同步过来

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| mysql                   |
| performance_schema      |
| replication_wwww.ymq.io |
| sys                     |
+-------------------------+

mysql> use replication_wwww.ymq.io
mysql> show tables;

+-----------------------------------+
| Tables_in_replication_wwww.ymq.io |
+-----------------------------------+
| sync_test                         |
+-----------------------------------+
1 row in set (0.00 sec)

一些命令

查看主服务器的运行状态

1
2
3
4
5
6

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1190 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

查看从服务器主机列表

1
2
3
4
5
6

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | 6b831bf2-8ae7-11e7-a178-000c29cb5cbc |
+-----------+------+------+-----------+--------------------------------------+

获取binlog文件列表

1
2
3
4
5
6

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1190 |
+------------------+-----------+

只查看第一个binlog文件的内容

1
2
3
4
5
6
7
8
9
10
11
12
13

mysql> mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                  |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.19-log, Binlog ver: 4                                                                                                                                                                 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                                                                                                                                                                                       |
| mysql-bin.000001 | 420 | Anonymous_Gtid |         1 |         485 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                  |
| mysql-bin.000001 | 485 | Query          |         1 |         629 | GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.252.124'                                                                                                                                     |
| mysql-bin.000001 | 629 | Anonymous_Gtid |         1 |         694 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                  |
| mysql-bin.000001 | 694 | Query          |         1 |         847 | CREATE DATABASE `replication_wwww.ymq.io`                                                                                                                                                             |
| mysql-bin.000001 | 847 | Anonymous_Gtid |         1 |         912 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                  |
| mysql-bin.000001 | 912 | Query          |         1 |        1190 | use `replication_wwww.ymq.io`; CREATE TABLE `sync_test` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

查看指定binlog文件的内容

1
2
3
4
5
6
7
8
9
10
11
12
13

mysql> mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                  |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.19-log, Binlog ver: 4                                                                                                                                                                 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                                                                                                                                                                                       |
| mysql-bin.000001 | 420 | Anonymous_Gtid |         1 |         485 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                  |
| mysql-bin.000001 | 485 | Query          |         1 |         629 | GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.252.124'                                                                                                                                     |
| mysql-bin.000001 | 629 | Anonymous_Gtid |         1 |         694 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                  |
| mysql-bin.000001 | 694 | Query          |         1 |         847 | CREATE DATABASE `replication_wwww.ymq.io`                                                                                                                                                             |
| mysql-bin.000001 | 847 | Anonymous_Gtid |         1 |         912 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                  |
| mysql-bin.000001 | 912 | Query          |         1 |        1190 | use `replication_wwww.ymq.io`; CREATE TABLE `sync_test` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

启动从库复制线程

1
2

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

停止从库复制线程

1
2

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)

5.复制实现细节分析

MySQL主从复制功能使用三个线程实现，一个在主服务器上，两个在从服务器上

1.Binlog转储线程。

当从服务器与主服务器连接时，主服务器会创建一个线程将二进制日志内容发送到从服务器。
该线程可以使用 语句 SHOW PROCESSLIST(下面有示例介绍) 在服务器 sql 控制台输出中标识为Binlog Dump线程。

二进制日志转储线程获取服务器上二进制日志上的锁，用于读取要发送到从服务器的每个事件。一旦事件被读取，即使在将事件发送到从服务器之前，锁会被释放。

2.从服务器I/O线程。

当在从服务器sql 控制台发出 START SLAVE语句时，从服务器将创建一个I/O线程，该线程连接到主服务器，并要求它发送记录在主服务器上的二进制更新日志。

从机I/O线程读取主服务器Binlog Dump线程发送的更新 （参考上面 Binlog转储线程 介绍），并将它们复制到自己的本地文件二进制日志中。

该线程的状态显示详情 Slave_IO_running 在输出端 使用 命令SHOW SLAVE STATUS

使用\G语句终结符,而不是分号,是为了，易读的垂直布局

这个命令在上面 查看从服务器状态 用到过

1

mysql> SHOW SLAVE STATUS\G

3.从服务器SQL线程。

从服务器创建一条SQL线程来读取由主服务器I/O线程写入的二级制日志，并执行其中包含的事件。

在前面的描述中，每个主/从连接有三个线程。主服务器为每个当前连接的从服务器创建一个二进制日志转储线程，每个从服务器都有自己的I/O和SQL线程。
从服务器使用两个线程将读取更新与主服务器更新事件，并将其执行为独立任务。因此，如果语句执行缓慢，则读取语句的任务不会减慢。

例如，如果从服务器开始几分钟没有运行，或者即使SQL线程远远落后，它的I/O线程也可以从主服务器建立连接时，快速获取所有二进制日志内容。

如果从服务器在SQL线程执行所有获取的语句之前停止，则I/O线程至少获取已经读取到的内容，以便将语句的安全副本存储在自己的二级制日志文件中，准备下次执行主从服务器建立连接，继续同步。

使用命令 SHOW PROCESSLIST\G 可以查看有关复制的信息

命令 SHOW FULL PROCESSLISTG

在 Master 主服务器 执行的数据示例

1
2
3
4
5
6
7
8
9
10

mysql>  SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
     Id: 22
   User: repl
   Host: node2:39114
     db: NULL
Command: Binlog Dump
   Time: 4435
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL

Id: 22是Binlog Dump服务连接的从站的复制线程
Host: node2:39114 是从服务，主机名 级及端口
State: 信息表示所有更新都已同步发送到从服务器，并且主服务器正在等待更多更新发生。
如果Binlog Dump在主服务器上看不到 线程，意味着主从复制没有配置成功; 也就是说，没有从服务器连接主服务器。

命令 SHOW PROCESSLISTG

在 Slave 从服务器 ，查看两个线程的更新状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 6
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 6810
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 7
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 3069
  State: Slave has read all relay log; waiting for more updates
   Info: NULL

Id: 6是与主服务器通信的I/O线程
Id: 7是正在处理存储在中继日志中的更新的SQL线程

在 运行 SHOW PROCESSLIST 命令时，两个线程都空闲，等待进一步更新

如果在主服务器上在设置的超时，时间内 Binlog Dump线程没有活动，则主服务器会和从服务器断开连接。超时取决于的 服务器系统变量 值 net_write_timeout(在中止写入之前等待块写入连接的秒数，默认10秒)和 net_retry_count;(如果通信端口上的读取或写入中断，请在重试次数，默认10次) 设置 服务器系统变量

该SHOW SLAVE STATUS语句提供了有关从服务器上复制处理的附加信息。请参见 第16.1.7.1节“检查复制状态”。

6.更多常见主从复制问题：

常见主从复制问题

留言與分享

查看是否支持 SSL

首先在 MySQL 上执行如下命令, 查询是否 MySQL 支持 SSL:

1
2
3
4
5
6
7

mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.02 sec)

当 have_ssl 为 YES 时, 表示此时 MySQL 服务已经支持 SSL 了. 如果是 DESABLE, 则需要在启动 MySQL 服务时, 使能 SSL 功能.

使用 OpenSSL 创建 SSL 证书和私钥

首先我们需要使用 openssl 来创建服务器端的证书和私钥. 我使用的 openssl 版本为:

1
2

>>> /usr/local/Cellar/openssl/1.0.2j/bin/openssl version
OpenSSL 1.0.2j  26 Sep 2016

新建一个 ~/temp/cert 目录, 用于存放生成的证书和私钥

1
2

mkdir ~/temp/cert
cd ~/temp/cert

创建 CA 私钥和 CA 证书

然后, 我们先来生成一个 CA 私钥:

1

openssl genrsa 2048 > ca-key.pem

当有了一个 CA 私钥, 我们接下来就可以使用这个私钥生成一个新的数字证书:

1

openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

执行这个命令时, 会需要填写一些问题, 随便填写就可以了. 例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

>>> openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

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) [AU]:CN
State or Province Name (full name) [Some-State]:Beijing
Locality Name (eg, city) []:Beijing
Organization Name (eg, company) [Internet Widgits Pty Ltd]:xys
Organizational Unit Name (eg, section) []:xys
Common Name (e.g. server FQDN or YOUR name) []:xys
Email Address []:yongshun1228@gmail.com

执行上述命令后, 我们就有了一个 CA 私钥和一个 CA 证书.

创建服务器端的 RSA 私钥和数字证书

接着, 我们需要创建服务器端的私钥和一个证书请求文件, 命令如下:

1

openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pem

上面这个命令会生成一个新的私钥(server-key.pem), 同时会使用这个新私钥来生成一个证书请求文件(server-req.pem).
上面这个命令同样需要回答几个问题, 随便填写即可. 不过需要注意的是, A challenge password 这一项需要为空.
即:

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

>>> openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pem

Generating a 2048 bit RSA private key
.................+++
..+++
writing new private key to 'server-key.pem'
-----
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) [AU]:CN
State or Province Name (full name) [Some-State]:Beijing
Locality Name (eg, city) []:Beijing
Organization Name (eg, company) [Internet Widgits Pty Ltd]:xys
Organizational Unit Name (eg, section) []:xys
Common Name (e.g. server FQDN or YOUR name) []:xys
Email Address []:yongshun1228@gmail.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

下一步, 我们需要将生成的私钥转换为 RSA 私钥文件格式:

1

openssl rsa -in server-key.pem -out server-key.pem

最后一步, 我们需要使用原先生成的 CA 证书来生成一个服务器端的数字证书:

1

openssl x509 -sha1 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

上面的命令会创建以服务器端的数字证书文件.

创建客户端的 RSA 私钥和数字证书

和服务器端所执行的命令类似, 我们也需要为客户端生成一个私钥和证书请求文件, 命令如下:

1

openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem

同样地, 我们需要将生成的私钥转换为 RSA 私钥文件格式:

1

openssl rsa -in client-key.pem -out client-key.pem

最后, 我们也需要为客户端创建一个数字证书:

1

openssl x509 -sha1 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

使用工具创建证书与私钥

前面我们介绍了如何使用 OpenSSL 来创建 SSL 连接的私钥和证书文件, 现在我们来看一个更简单的方法.
在 MySQL 5.7 中, 提供了一个名为 mysql_ssl_rsa_setup 的工具, 通过它, 我们可以很方便地创建 SSL 连接所需要的各种文件:

1
2
3

mkdir ~/temp/cert
cd ~/temp/cert
mysql_ssl_rsa_setup --datadir ./

上面的命令中, --datadir 表示生成的文件的目录.

当执行了上述命令后, 也会生成八个文件:

1
2
3
4
5
6
7
8

ca-key.pem
ca.pem
client-cert.pem
client-key.pem
private_key.pem
public_key.pem
server-cert.pem
server-key.pem

这些文件和我们使用 OpenSSL 所创建的那八个文件的作用是一样的, 这里就不再详述了.

SSL 配置

在前面的步骤中, 我们已经生成了8个文件, 分别是:

• ca-cert.pem: CA 证书, 用于生成服务器端/客户端的数字证书.

• ca-key.pem: CA 私钥, 用于生成服务器端/客户端的数字证书.

• server-key.pem: 服务器端的 RSA 私钥

• server-req.pem: 服务器端的证书请求文件, 用于生成服务器端的数字证书.

• server-cert.pem: 服务器端的数字证书.

• client-key.pem: 客户端的 RSA 私钥

• client-req.pem: 客户端的证书请求文件, 用于生成客户端的数字证书.

• client-cert.pem: 客户端的数字证书.

接下来我们就需要分别配置服务器端和客户端.

服务器端配置

服务器端需要用到三个文件, 分别是: CA 证书, 服务器端的 RSA 私钥, 服务器端的数字证书, 我们需要在 [mysqld] 配置域下添加如下内容:

1
2
3
4

[mysqld]
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

接着我们还可以更改 bind-address, 使 MySQL 服务可以接收来自所有 ip 地址的客户端, 即:

1

bind-address = *

当配置好后, 我们需要重启 MySQL 服务, 使能配置.

最后一步, 我们添加一个需要使用 SSL 才可以登录的帐号, 来验证一下我们所配置的 SSL 是否生效:

1
2

GRANT ALL PRIVILEGES ON *.* TO 'ssl_test'@'%' IDENTIFIED BY 'ssl_test' REQUIRE SSL;
FLUSH PRIVILEGES;

当配置好后, 使用 root 登录 MySQL, 执行 show variables like '%ssl%' 语句会有如下输出:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> show variables like '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.01 sec)

客户端配置

客户端配置相对简单一些. 首先我们需要拷贝 ca-cert.pem, client-cert.pem 和 client-key.pem 这三个文件到客户端主机中, 然后我们可以执行如下命令来使用 SSL 连接 MySQL 服务:

1

mysql --ssl-ca=/path/to/ca-cert.pem --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem -h host_name -u ssl_test -p

除了上述的使用命令行方式配置 SSL 外, 我们也可以使用配置文件的方式. 即在 ~/.my.cnf 文件中添加如下内容即可:

1
2
3
4

[client]
ssl-ca=/path/to/ca-cert.pem
ssl-cert=/path/to/client-cert.pem
ssl-key=/path/to/client-key.pem

当连接成功后, 我们执行如下指令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper

Connection id:        14
Current database:
Current user:        ssl_test@172.17.0.4
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.17 MySQL Community Server (GPL)
Protocol version:    10
Connection:        test_db via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:        3306
Uptime:            1 hour 2 min 9 sec

Threads: 1  Questions: 23  Slow queries: 0  Opens: 126  Flush tables: 3  Open tables: 0  Queries per second avg: 0.006
--------------

如果输出中有 SSL: Cipher in use is DHE-RSA-AES256-SHA 之类的信息, 则表示已经使用 SSL 来连接了.

在 Docker 中使能 MySQL SSL 连接

上面我们简单介绍了一下如果使能 MySQL SSL 连接, 那么现在我们使用 Docker 来具体的实战一把吧!

首先拉取最新的 MySQL 镜像:

1

docker pull mysql

然后需要准备一下挂载到 Docker 容器的目录结构:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

>>> cd ~/temp
>>> tree
.
├── cert
│   ├── ca-key.pem
│   ├── ca.pem
│   ├── client-cert.pem
│   ├── client-key.pem
│   ├── private_key.pem
│   ├── public_key.pem
│   ├── server-cert.pem
│   └── server-key.pem
├── config
│   └── my.cnf
└── db

3 directories, 9 files

在 temp 目录下有三个子目录:

• cert 目录用于存放我们先前生成的证书和私钥信息;

• config 目录用于存放 MySQL 服务的配置文件

• db 目录是用于存放 MySQL 的数据.

下一步我们需要使用如下命令启动 MySQL 容器:

1

docker run --rm --name test_db -p 10000:3306 -e MYSQL_ROOT_PASSWORD=root -v /Users/xiongyongshun/temp/db:/var/lib/mysql -v /Users/xiongyongshun/temp/config:/etc/mysql/conf.d -v /Users/xiongyongshun/temp/cert:/etc/mysql/cert mysql:latest

我们在上面的命令中, 我们分别挂载了 cert, config, db 这三个宿主机上的目录到 MySQL 容器中.

启动了 MySQL 服务后, 可以先使用 root 帐号登录 MySQL, 来检查 MySQL 服务此时是否已经开启了 SSL 功能:

1

docker run -it --link test_db:test_db --rm  mysql sh -c 'exec mysql -u root -p -h test_db'

登录成功后, 我们在 MySQL 中执行如下指令:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> show variables like '%ssl%';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| have_openssl  | YES                             |
| have_ssl      | YES                             |
| ssl_ca        | /etc/mysql/cert/ca-cert.pem     |
| ssl_capath    |                                 |
| ssl_cert      | /etc/mysql/cert/server-cert.pem |
| ssl_cipher    |                                 |
| ssl_crl       |                                 |
| ssl_crlpath   |                                 |
| ssl_key       | /etc/mysql/cert/server-key.pem  |
+---------------+---------------------------------+
9 rows in set (0.01 sec)

有上面的输出后, 表明此时 MySQL 服务已经使用 SSL 功能了.

接着下一步, 我们按照前面所提到的, 创建一个仅仅可以使用 SSL 登录的帐号, 来检验我们的配置是否有效:

1
2

GRANT ALL PRIVILEGES ON *.* TO 'ssl_test'@'%' IDENTIFIED BY 'ssl_test' REQUIRE SSL;
FLUSH PRIVILEGES;

上面的命令创建了一个帐号名为 ssl_test, 密码为 ssl_test, 并且不限制登录主机 ip 的帐号.

这些都配置成功后, 我们再启动一个 MySQL 客户端容器:

1

docker run -it --link test_db:test_db --rm -v /Users/xiongyongshun/temp/cert:/etc/mysql/cert mysql sh -c 'exec mysql --ssl-ca=/etc/mysql/cert/ca-cert.pem --ssl-cert=/etc/mysql/cert/client-cert.pem --ssl-key=/etc/mysql/cert/client-key.pem -h test_db -u ssl_test -p'

从上面的这个命令中我们可以看到, 启动 MySQL 客户端容器时, 我们挂载了宿主机的 cert 目录到容器内的 /etc/mysql/cert 目录, 这样在容器中就可以访问到 SSL 私钥和证书文件了. 接着我们在 MySQL 客户端命令行中, 使用 --ssl-ca, --ssl-cert, --ssl-key 这三个参数来指定 SSL 连接所需要的 CA 证书, RSA 私钥和客户端证书.

登录成功后, 我们执行 s 命令:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper

Connection id:        5
Current database:
Current user:        ssl_test@172.17.0.5
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.17 MySQL Community Server (GPL)
Protocol version:    10
Connection:        test_db via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:        3306
Uptime:            6 min 8 sec

Threads: 2  Questions: 10  Slow queries: 0  Opens: 113  Flush tables: 1  Open tables: 106  Queries per second avg: 0.027
--------------

输出中有 SSL: Cipher in use is DHE-RSA-AES256-SHA 信息则说明我们确实是使用了 SSL 连接的 MySQL 服务器.

留言與分享

CentOs7.3 安装 MySQL 5.7.19 二进制版本

参考官网 - 使用通用二进制文件在Unix / Linux上安装MySQL

MySQL社区版 下载地址

采用二进制方式免编译安装MySQL,适合各类MySQL产品系列,不需要复杂的编译设置和编译时间等待,直接解压下载的软件包,初始化即可完成MySQL的安装和启动.

1.准备工作

依赖环境

关闭防火墙

1

$ systemctl stop firewalld.service

MySQL依赖于libaio 库

1
2

$ yum search libaio
$ yum install libaio

下载，解压，重命名

通常解压在 /usr/local/mysql

把mysql-5.7.19-linux-glibc2.12-x86_64 文件夹，重命名成mysql,这样就凑成/usr/local/mysql目录了

1
2
3
4

$ cd /opt/
$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
$ tar -zxvf /opt/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
$ mv /usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/ /usr/local/mysql

解压目录内容

bin mysqld服务器，客户端和实用程序
data 日志文件，数据库
docs MySQL手册信息格式
man Unix手册页
include 包含（标题）文件
lib 库
share 其他支持文件，包括错误消息，示例配置文件，用于数据库安装的SQL

2.安装MySQL

1. 新建用户组和用户

1
2
3

$ cd /usr/local/mysql/ 
$ groupadd mysql
$ useradd mysql -g mysql

2. 创建目录并授权

1
2
3
4

$ mkdir data mysql-files
$ chmod 750 mysql-files
$ chown -R mysql .
$ chgrp -R mysql .

3. 初始化MySQL

1

$ bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and up

注意密码

4. mysql 临时密码

[注意]root@localhost生成临时密码：;b;s;)/rn6A3,也就是root@localhost:后的字符串

1
2
3
4
5
6

2017-08-26T03:23:35.368366Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-08-26T03:23:35.748679Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-08-26T03:23:35.793190Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-08-26T03:23:35.848286Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: f210c54b-8a0d-11e7-abbd-000c29129bb0.
2017-08-26T03:23:35.848889Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-08-26T03:23:35.849421Z 1 [Note] A temporary password is generated for root@localhost: ;b;s;)/rn6A3

5. 生成RSA私钥，可以跳过此步骤

mysql_ssl_rsa_setup需要openssl支持，用于启用数据量ssl连接，需要进一步配置。

参考-MySQL 使用 SSL 连接

1

$ bin/mysql_ssl_rsa_setup 

6. 授予读写权限

1
2

$ chown -R root .
$ chown -R mysql data mysql-files

7. 添加到MySQL 启动脚本到系统服务

1

$ cp support-files/mysql.server /etc/init.d/mysql.server

3.启动MySQL服务

启动脚本有两个分别是：

/usr/local/mysql/bin/mysqld_safe
/usr/local/mysql/support-files/mysql.server（即/etc/init.d/mysqld）

当启动mysqld时，mysqld_safe同时启动

mysqld_safe监控mysqld服务,记录错误日志，并在mysqld因故障停止时将其重启

启动方式一

1

$ bin/mysqld_safe --user=mysql &

启动方式二

1

$ service mysql.server start

或者

1

/usr/local/mysql/support-files/mysql.server start

如若出现报错

1
2

Starting MySQL.2017-08-26T07:31:24.312411Z mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.
 ERROR! The server quit without updating PID file (/var/lib/mysql/node1.pid).

给日志目录授予读写权限

1
2
3

$ mkdir /var/log/mariadb
$ touch /var/log/mariadb/mariadb.log
$ chown -R mysql:mysql /var/log/mariadb

4.登录MySQL

1
2

$ /usr/local/mysql/bin/mysql -uroot -p
Enter password: 

如果不知道密码
密码在，安装MySQL步骤 4 ，有提到，怎么找初始化临时密码

如若出现报错

1
2

Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

故障分析

查看mysql实例的状态

1
2

$ netstat -ntlp  | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      10794/mysqld  

查看my.cnf关于socket的配置

1
2

$ more /etc/my.cnf |grep sock
socket=/var/lib/mysql/mysql.sock

解决方法,修改/etc/my.cnf

1

$ vi /etc/my.cnf

修改 [mysqld]组下的 socket 路径，我是选择注释掉，加一行为tmp/mysql.soc

1
2
3
4

[mysqld]
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
socket=/tmp/mysql.sock

重启MySQL 服务

1
2

$ service mysql.server start
Shutting down MySQL.. SUCCESS!

再次登录

1

$ /usr/local/mysql/bin/mysql -uroot -p

如果不知道密码
密码在，安装MySQL步骤 4 ，有提到，怎么找初始化临时密码

设置MySQL密码

登陆成功后，设置MySQL密码

1

mysql> ALTER USER   'root'@'localhost' identified by 'mima';  

或者

1

mysql> set password=password("mima");

刷新权限

1
2

mysql> flush privileges;
mysql> exit;

查看mysql.user表中存在哪些帐户 以及它们的密码是否为空：

MySQL 5.7.6起，使用这个语句：

1
2
3
4
5
6
7
8

mysql> SELECT User, Host, HEX(authentication_string) FROM mysql.user;
+---------------+-----------+------------------------------------------------------------------------------------+
| User          | Host      | HEX(authentication_string)                                                         |
+---------------+-----------+------------------------------------------------------------------------------------+
| root          | localhost | 2A39383730334637413534333934344644333831383037373636394637344436303631364442324338 |
| mysql.session | localhost | 2A5448495349534E4F544156414C494450415353574F52445448415443414E42455553454448455245 |
| mysql.sys     | localhost | 2A5448495349534E4F544156414C494450415353574F52445448415443414E42455553454448455245 |
+---------------+-----------+------------------------------------------------------------------------------------+

开启远程登录

关闭防火墙

1

$ systemctl stop firewalld.service

以权限用户root登录

1

$ /usr/local/mysql/bin/mysql -uroot -p

1
2
3

mysql> use mysql;
mysql> update user set host = '%' where user ='root';
mysql> flush privileges;

第1行：选择mysql库
第2行：修改host值（以通配符%的内容增加主机/IP地址），当然也可以直接增加IP地址
第3行：刷新MySQL的系统权限相关表

或者

1
2

mysql> grant all privileges on *.*  to  'root'@'%'  identified by 'mima'  with grant option;
mysql> flush privileges;

推荐阅读

CentOs7.3 搭建 MySQL 5.7.19 主从复制，以及复制实现细节分析

留言與分享

什么是explain

使用explain可以模拟优化器执行SQL查询语句，从而知道MySQL怎么处理你的SQL语句的，分析你的查询语句和表结构的性能瓶颈。

explain能够干什么

• 读取表的顺序
• 哪些索引能够被使用
• 数据读取操作的操作类型
• 哪些索引能够被实际使用
• 表之间的引用
• 每张表有多少行被物理查询

创建一个学习用的数据库

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

CREATE DATABASE /*!32312 IF NOT EXISTS*/`mydb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mydb`;

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`id`,`name`) values 
(1,'语文'),(2,'高等数学'),(3,'视听说'),(4,'体育'),(5,'马克思概况'),(6,'民族理论'),(7,'毛中特'),(8,'计算机基础'),(9,'深度学习'),(10,'Java程序设计'),(11,'c语言程序设计'),(12,'操作系统'),(13,'计算机网络'),(14,'计算机组成原理'),(15,'数据结构'),(16,'数据分析'),(17,'大学物理'),(18,'数字逻辑'),(19,'嵌入式开发'),(20,'需求工程');

/*Table structure for table `stu_course` */

DROP TABLE IF EXISTS `stu_course`;

CREATE TABLE `stu_course` (
  `sid` int(10) NOT NULL,
  `cid` int(10) NOT NULL,
  PRIMARY KEY (`sid`,`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `stu_course` */

insert  into `stu_course`(`sid`,`cid`) values 
(1,2),(1,4),(1,14),(1,16),(1,19),(2,4),(2,8),(2,9),(2,14),(3,13),(3,14),(3,20),(4,5),(4,8),(4,9),(4,11),(4,16),(5,4),(5,8),(5,9),(5,11),(5,12),(5,16),(6,2),(6,14),(6,17),(7,1),(7,8),(7,15),(8,2),(8,3),(8,7),(8,17),(9,1),(9,7),(9,16),(9,20),(10,4),(10,12),(10,14),(10,20),(11,3),(11,9),(11,16),(12,3),(12,7),(12,9),(12,12),(13,1),(13,5),(13,13),(14,1),(14,3),(14,18),(15,1),
(15,9),(15,15),(16,2),(16,7);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `name_age` (`name`,`age`),
  KEY `id_name_age` (`id`,`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`age`) values 
(25,'乾隆',17),(14,'关羽',43),(13,'刘备',12),(28,'刘永',12),(21,'后裔',12),(30,'吕子乔',28),(18,'嬴政',76),(22,'孙悟空',21),(4,'安其拉',24),(6,'宋江',22),(26,'康熙',51),(29,'张伟',26),(20,'张郃',12),(12,'张飞',32),(27,'朱元璋',19),(11,'李世民',54),(9,'李逵',12),(8,'林冲',43),(5,'橘右京',43),(24,'沙和尚',25),(23,'猪八戒',22),(15,'王与',21),(19,'王建',23),(10,'王莽',43),(16,'秦叔宝',43),(17,'程咬金',65),(3,'荆轲',21),(2,'诸葛亮',71),(7,'钟馗',23),(1,'鲁班',21);

这个数据库实际上的业务是:学生表 - 选课表 - 课程表

如何使用explain

使用而explain很简单就是，在你书写的SQL语句加一个单词 - explain，然后将 explain + SQL执行后会出现一个表，这个表会告诉你MySQL优化器是怎样执行你的SQL的。

就比如执行下面一句语句：

1

EXPLAIN SELECT * FROM student

MySQL会给你反馈下面一个信息：

1
2
3

    id  select_type  table    partitions  type    possible_keys  key       key_len  ref       rows  filtered  Extra        
------  -----------  -------  ----------  ------  -------------  --------  -------  ------  ------  --------  -------------
     1  SIMPLE       student  (NULL)      index   (NULL)         name_age  68       (NULL)      30    100.00  Using index  

具体这些信息是干什么的，会对你有什么帮助，会在下面告诉你。

explain各个字段代表的意思

• id ：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
• select_type ：查询类型 或者是 其他操作类型
• table ：正在访问哪个表
• partitions ：匹配的分区
• type ：访问的类型
• possible_keys ：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
• key ：实际使用到的索引，如果为NULL，则没有使用索引
• key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
• ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
• rows ：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
• filtered ：查询的表行占表的百分比
• Extra ：包含不适合在其它列中显示但十分重要的额外信息

上面介绍了每个字段的意思，可以大体看一下，下面会逐一介绍每个字段表示的啥？该关注什么？

id与table字段

为什么要将id和table放在一起讲呢？因为通过这两个字段可以完全判断出你的每一条SQL语句的执行顺序和表的查询顺序。

先看id后看table，id和table在SQL执行判断过程中的关系就像是足球联赛的积分榜，首先一个联赛的球队排名应该先看积分，积分越高的球队排名越靠前，当两支或多只球队的积分一样高怎么办呢？那我们就看净胜球，净胜球越多的球队，排在前面。而在explain中你可以把id看作是球队积分，table当作是净胜球。

比如说我们explain一下这一条SQL：

1
2
3
4
5

EXPLAIN
SELECT 
    S.id,S.name,S.age,C.id,C.name
FROM course C JOIN stu_course SC ON C.id = SC.cid
JOIN student S ON S.id = SC.sid

结果是这样：

1
2
3
4
5

    id  select_type  table   partitions  type    possible_keys        key      key_len  ref      
------  -----------  ------  ----------  ------  -------------------  -------  -------  ----------- 
     1  SIMPLE       SC      (NULL)      index   PRIMARY              PRIMARY  8        (NULL)     
     1  SIMPLE       C       (NULL)      eq_ref  PRIMARY              PRIMARY  4        mydb.SC.cid 
     1  SIMPLE       S       (NULL)      eq_ref  PRIMARY,id_name_age  PRIMARY  4        mydb.SC.sid 

我们看到id全是1，那就说明光看id这个值是看不出来每个表的读取顺序的，那我们就来看table这一行，它的读取顺序是自上向下的，所以，这三个表的读取顺序应当是：SC - C - S。

再来看一条SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14

EXPLAIN
SELECT * 
FROM course AS C 
WHERE C.`id` = (
    SELECT SC.`cid` 
    FROM stu_course AS SC 
    WHERE SC.`sid` = 
    (
        SELECT 
            S.`id` 
        FROM student AS S
        WHERE  S.`name` = "安其拉"
    ) ORDER BY SC.`cid` LIMIT 1
)

这条语句是查询结果是：一个叫安其拉的学生选的课里面，课程id最小的一门课的信息，然后来看一下explain的结果吧！

1
2
3
4
5

    id  select_type  table   partitions  type    possible_keys  key      key_len  ref    
------  -----------  ------  ----------  ------  -------------  -------  -------  ------  
     1  PRIMARY      C       (NULL)      const   PRIMARY        PRIMARY  4        const   
     2  SUBQUERY     SC      (NULL)      ref     PRIMARY        PRIMARY  4        const  
     3  SUBQUERY     S       (NULL)      ref     name,name_age  name     63       const 

此时我们发现id是不相同的，所以我们很容易就看出表读取的顺序了是吧！C - SC - S

注意！！！！！！你仔细看一下最里面的子查询是查询的哪个表，是S这张表，然后外面一层呢？是SC这张表，最外面这一层呢？是C这张表，所以执行顺序应该是啥呢？是…是…难道是S - SC - C吗？是id越大的table读取越在前面吗？是的！这就像刚才说的足球联赛积分，分数越高的球队的排序越靠前。

当然还有下面这种情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14

EXPLAIN
SELECT * 
FROM course AS C 
WHERE C.`id` IN (
    SELECT SC.`cid` 
    FROM stu_course AS SC 
    WHERE SC.`sid` = 
    (
        SELECT 
            S.`id` 
        FROM student AS S
        WHERE  S.`name` = "安其拉"
    )
)

这个查询是：查询安其拉选课的课程信息

1
2
3
4
5

    id  select_type  table   partitions  type    possible_keys  key      key_len  ref           
------  -----------  ------  ----------  ------  -------------  -------  -------  ----------- 
     1  PRIMARY      SC      (NULL)      ref     PRIMARY        PRIMARY  4        const       
     1  PRIMARY      C       (NULL)      eq_ref  PRIMARY        PRIMARY  4        mydb.SC.cid 
     3  SUBQUERY     S       (NULL)      ref     name,name_age  name     63       const        

结果很明确：先看id应该是S表最先被读取，SC和C表id相同，然后table中SC更靠上，所以第二张读取的表应当是SC，最后读取C。

select_type字段

• SIMPLE 简单查询，不包括子查询和union查询

  1 2	EXPLAIN SELECT * FROM student JOIN stu_course ON student.`id` = sid

  1 2 3 4

  id select_type table partitions type possible_keys key ------ ----------- ---------- ---------- ------ ------------------- -------- 1 SIMPLE student (NULL) index PRIMARY,id_name_age name_age 1 SIMPLE stu_course (NULL) ref PRIMARY PRIMARY

• PRIMARY 当存在子查询时，最外面的查询被标记为主查询

• SUBQUERY 子查询

  1 2 3 4 5 6 7 8 9 10

  EXPLAIN SELECT SC.`cid` FROM stu_course AS SC WHERE SC.`sid` = ( SELECT S.`id` FROM student AS S WHERE S.`name` = "安其拉" )

  1 2 3 4

  id select_type table partitions type possible_keys key key_len ref ------ ----------- ------ ---------- ------ ------------- ------- ------- ------ 1 PRIMARY SC (NULL) ref PRIMARY PRIMARY 4 const 2 SUBQUERY S (NULL) ref name,name_age name 63 const

• UNION 当一个查询在UNION关键字之后就会出现UNION

• UNION RESULT 连接几个表查询后的结果

  1 2 3 4

  EXPLAIN SELECT * FROM student WHERE id = 1 UNION SELECT * FROM student WHERE id = 2

  1 2 3 4 5

  id select_type table partitions type possible_keys key ------ ------------ ---------- ---------- ------ ------------------- ------- 1 PRIMARY student (NULL) const PRIMARY,id_name_age PRIMARY 2 UNION student (NULL) const PRIMARY,id_name_age PRIMARY (NULL) UNION RESULT <union1,2> (NULL) ALL (NULL) (NULL)

  上面可以看到第三行table的值是<union1,2>

• DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL
  会递归执行这些子查询，把结果放在临时表中
  MySQL5.7+ 进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率

  如果你想了解更详细的派生合并请点击这里

  当你的MySQL是5.7及以上版本时你要将derived_merge关闭后才能看到DERIVED 状态

  1 2	set session optimizer_switch='derived_merge=off'; set global optimizer_switch='derived_merge=off';

  1 2 3 4 5 6 7

  EXPLAIN SELECT * FROM ( SELECT * FROM student AS S JOIN stu_course AS SC ON S.`id` = SC.`sid` ) AS SSC

  1 2 3 4 5

  id select_type table partitions type possible_keys key ------ ----------- ---------- ---------- ------ ------------------- -------- 1 PRIMARY <derived2> (NULL) ALL (NULL) (NULL) 2 DERIVED S (NULL) index PRIMARY,id_name_age name_age 2 DERIVED SC (NULL) ref PRIMARY PRIMARY

  上面我们观察，最外层的主查询的表是,而S和SC表的select_type都是DERIVED,这说明S和SC都被用来做衍生查询，而这两张表查询的结果组成了名为的衍生表，而衍生表的命名就是<select_type + id>。

partitions字段

该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

type字段

注意！！！注意！！！重点来了！

首先说一下这个字段，要记住以下10个状态，（从左往右，越靠左边的越优秀）

1

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

• NULL MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引

  有没有这样一种疑惑，不查询索引也不查询表那你的数据是从哪里来的啊？谁说SELECT语句必须查询某样东西了？

  1	EXPLAIN SELECT 5*7

  1 2 3

  id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ------ 1 SIMPLE (NULL) (NULL) (NULL) (NULL) (NULL)

  我就简简单单算个数不好吗？好啊😊。。。

  但是！！如果只是这样的话我们还explain个毛线啊！我很闲吗？

  存在这样一种情况，大家都知道索引是将数据在B+Tree中进行排序了，所以你的查询速率才这么高，那么B+树的最边上的叶子节点是不是要么是最大值要么是最小值啊？既然你都知道了，那MySQL比你更知道啊！当你要查询最大值或者最小值时，MySQL会直接到你的索引得分叶子节点上直接拿，所以不用访问表或者索引。

  1	EXPLAIN SELECT MAX(id) FROM student

  1 2 3

  id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ------ 1 SIMPLE (NULL) (NULL) (NULL) (NULL) (NULL)

  但是！你要记住，NULL的前提是你已经建立了索引。

• SYSTEM 表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略。

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

  简单来说，const是直接按主键或唯一键读取。

  1 2	EXPLAIN SELECT * FROM student AS S WHERE id = 10

  1 2 3

  id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ------- 1 SIMPLE S (NULL) const PRIMARY PRIMARY

• eq_ref 用于联表查询的情况，按联表的主键或唯一键联合查询。

  多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。

  1 2	EXPLAIN SELECT * FROM student AS S JOIN stu_course AS SC ON S.`id` = SC.`cid`

  1 2 3 4

  id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ------- 1 SIMPLE SC (NULL) index (NULL) PRIMARY 1 SIMPLE S (NULL) eq_ref PRIMARY PRIMARY

  以上面查询为例，我们观察id和table会知道，先是从SC表中取出一行数据，然后再S表查找匹配的数据，我们观察，SC中取出cid和S表中的id比较，毫无疑问因为id是S表中的主键（不重复），所以只能出现一个id与cid的值相同。所以！满足条件 S 表的 type为eq_ref。

• ref 可以用于单表扫描或者连接。如果是连接的话，驱动表的一条记录能够在被驱动表中通过非唯一（主键）属性所在索引中匹配多行数据，或者是在单表查询的时候通过非唯一（主键）属性所在索引中查到一行数据。

  1 2	EXPLAIN SELECT * FROM student AS S JOIN stu_course AS SC ON S.id = SC.`sid`

  不要在意SQL，以上SQL没有实际查询的意义只是用于表达用例

  1 2 3 4

  id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ------- 1 SIMPLE S (NULL) ALL PRIMARY (NULL) 1 SIMPLE SC (NULL) ref PRIMARY PRIMARY

  SC的主键索引是（cid,sid）所以sid列中肯定是重复的数据，虽然在后面的key中显示使用了主键索引。然后，就很明确了S.id一行能在SC表中通过索引查询到多行数据。

  下面是单表了，写一个例子，但是不细讲了

  1 2	EXPLAIN SELECT * FROM student AS S WHERE S.`name` = "张飞"

  1 2 3

  id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ---------- 1 SIMPLE S (NULL) ref index_name index_name

  注意name字段是有索引的哈！！！

• ref_or_null 类似ref，但是可以搜索值为NULL的行

  1 2	EXPLAIN SELECT * FROM student AS S WHERE S.`name` = "张飞" OR S.`name` IS NULL

  1 2 3

  id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ----------- ------------- ---------- 1 SIMPLE S (NULL) ref_or_null index_name index_name

• index_merge 表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。

  1 2	EXPLAIN SELECT * FROM student AS S WHERE S.`name` LIKE "张%" OR S.`age` = 30

  1 2 3

  id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ----------- -------------------- -------------------- 1 SIMPLE S (NULL) index_merge index_name,index_age index_name,index_age

• range 索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

  1 2	EXPLAIN SELECT S.`age` FROM student AS S WHERE S.`age` > 30

  1 2 3

  id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ -------------------- ---------- 1 SIMPLE S (NULL) range index_name,index_age index_name

• index index只遍历索引树，通常比All快。因为，索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。

  1 2	EXPLAIN SELECT S.`name` FROM student AS S

  1 2 3

  id select_type table partitions type possible_keys key ------ ----------- ------ ---------- ------ ------------- ---------- 1 SIMPLE S (NULL) index (NULL) index_name

• ALL 如果一个查询的type是All,并且表的数据量很大，那么请解决它！！！

possible_keys字段

这个表里面存在且可能会被使用的索引，可能会在这个字段下面出现，但是一般都以key为准。

key字段

实际使用的索引，如果为null,则没有使用索引，否则会显示你使用了哪些索引，查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。

ref字段

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

rows字段和Filter字段

rows是根据表的统计信息和索引的选用情况，优化器大概帮你估算出你执行这行函数所需要查询的行数。

Filter是查询的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。

Extra字段

这一字段包含不适合在其他列显示，但是也非常重要的额外信息。

• Using filesort 表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序，外部排序就不断的在磁盘和内存中交换数据，这样就摆脱不了很多次磁盘IO，以至于SQL执行的效率很低。反之呢？由于索引的底层是B+Tree实现的，他的叶子节点本来就是有序的，这样的查询能不爽吗？

  1 2	EXPLAIN SELECT * FROM course AS C ORDER BY C.`name`

  1 2 3

  type possible_keys key key_len ref rows filtered Extra ------ ------------- ------ ------- ------ ------ -------- ---------------- ALL (NULL) (NULL) (NULL) (NULL) 20 100.00 Using filesort

  没有给C.name建立索引，所以在根据C.name排序的时候，他就使用了外部排序

• Using tempporary 表示在对MySQL查询结果进行排序时，使用了临时表,这样的查询效率是比外部排序更低的，常见于order by和group by。

  1 2	EXPLAIN SELECT C.`name` FROM course AS C GROUP BY C.`name`

  1 2 3

  possible_keys key key_len ref rows filtered Extra ------------- ------ ------- ------ ------ -------- --------------------------------- (NULL) (NULL) (NULL) (NULL) 20 100.00 Using temporary; Using filesort

  上面这个查询就是同时触发了Using temporary和Using filesort，可谓是雪上加霜。

• Using index 表示使用了索引，很优秀👍。

• Using where 使用了where但是好像没啥用。

• Using join buffer 表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

• impossible where 筛选条件没能筛选出任何东西

• distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

留言與分享

在数据库管理和优化的世界里，MySQL作为一个流行的关系型数据库管理系统，其性能优化是任何数据密集型应用成功的关键。优化MySQL数据库不仅可以显著提高SQL查询的效率，还能确保数据的稳定性和可靠性。

在本文中，我将介绍12种提升SQL执行效率的有效方法，并通过实用的代码示例来具体展示如何实施这些优化策略。

1、使用索引优化查询

使用场景：当你的数据库表中有大量数据，而你需要频繁进行搜索查询时，索引是提高查询效率的关键。

代码示例：

1
2
3
4
5
6
7
8
9
10
11
12
13

-- 假设我们有一个员工表 employees
CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    department_id INT,
    PRIMARY KEY (id)
);

-- 为department_id字段创建索引
CREATE INDEX idx_department ON employees(department_id);

-- 使用索引进行查询
SELECT * FROM employees WHERE department_id = 5;

代码解释：

第一步是创建一个包含id, name, department_id字段的employees表。

然后为department_id字段创建一个索引idx_department。这个操作会让基于department_id的查询更快。

最后，我们执行一个查询，利用创建的索引，从而提高查询效率。

2、优化查询语句

使用场景：避免使用高成本的SQL操作，如SELECT *，尽量指定需要的列，减少数据传输和处理时间。

代码示例：

1
2
3
4
5

-- 不推荐的查询方式
SELECT * FROM employees;

-- 推荐的查询方式
SELECT id, name FROM employees;

代码解释：

第一个查询语句使用了SELECT *，它会获取所有列，这在数据量大时非常低效。

第二个查询仅请求需要的id和name列，减少了数据处理的负担。

3、使用查询缓存

使用场景：当相同的查询被频繁执行时，使用查询缓存可以避免重复的数据库扫描。

代码示例：

1
2
3
4
5
6

-- 启用查询缓存
SET global query_cache_size = 1000000;
SET global query_cache_type = 1;

-- 执行查询
SELECT name FROM employees WHERE department_id = 5;

代码解释：

通过设置query_cache_size和query_cache_type，我们启用了查询缓存。

当我们执行查询时，MySQL会检查缓存中是否已经有了该查询的结果，如果有，则直接返回结果，避免了重复的数据库扫描。

4、避免全表扫描

使用场景：当表中数据量巨大时，全表扫描会非常耗时。通过使用合适的查询条件来避免全表扫描，可以显著提高查询效率。

代码示例：

1
2
3
4
5
6

-- 假设我们需要查询员工表中特定部门的员工
-- 不推荐的查询方式，会导致全表扫描
SELECT * FROM employees WHERE name LIKE '%张%';

-- 推荐的查询方式
SELECT * FROM employees WHERE department_id = 3 AND name LIKE '%张%';

代码解释：

第一个查询使用了模糊匹配LIKE，但缺乏有效的过滤条件，可能导致全表扫描。

第二个查询在name字段的模糊匹配前，增加了对department_id的条件过滤，这样就可以先缩小查找范围，避免全表扫描。

5、使用JOIN代替子查询

使用场景：在需要关联多个表的复杂查询中，使用JOIN代替子查询可以提高查询效率。

代码示例：

1
2
3
4
5
6
7
8
9
10
11
12

-- 假设我们有一个部门表 departments
CREATE TABLE departments (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
);

-- 不推荐的子查询方式
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'IT');

-- 推荐的JOIN查询方式
SELECT employees.* FROM employees JOIN departments ON employees.department_id = departments.id WHERE departments.name = 'IT';

代码解释：

第一个查询使用了子查询，这在执行时可能效率较低，特别是当子查询或主查询的结果集较大时。

第二个查询使用了JOIN操作，这通常比子查询更有效，尤其是在处理大型数据集时。

6、合理分页

使用场景：在处理大量数据的列表展示时，合理的分页策略可以减少单次查询的负担，提高响应速度。

代码示例：

1
2
3
4
5
6

-- 假设我们需要分页显示员工信息
-- 不推荐的分页方式，尤其是当offset值很大时
SELECT * FROM employees LIMIT 10000, 20;

-- 推荐的分页方式，使用更高效的条件查询
SELECT * FROM employees WHERE id > 10000 LIMIT 20;

代码解释：

第一个查询使用了LIMIT和较大的偏移量offset，在大数据集上执行时会逐行扫描跳过大量记录，效率低下。

第二个查询通过在WHERE子句中添加条件来避免不必要的扫描，从而提高分页效率。

7、利用分区提高性能

使用场景：对于大型表，特别是那些行数以百万计的表，使用分区可以提高查询性能和数据管理效率。

代码示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 假设我们需要对一个大型的订单表 orders 进行分区
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10, 2),
    PRIMARY KEY (order_id)
) PARTITION BY RANGE ( YEAR(order_date) ) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
);

-- 查询特定年份的订单
SELECT * FROM orders WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31';

代码解释：

我们为orders表创建了基于order_date字段的年份范围分区。

查询特定年份的数据时，MySQL只会在相关分区中搜索，提高了查询效率。

8、利用批处理减少I/O操作

使用场景：在进行大量数据插入或更新时，批处理可以减少数据库的I/O操作次数，从而提高性能。

代码示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 批量插入数据
INSERT INTO employees (name, department_id)
VALUES 
    ('张三', 1),
    ('李四', 2),
    ('王五', 3),
    -- 更多记录
;

-- 批量更新数据
UPDATE employees
SET department_id = CASE name
    WHEN '张三' THEN 3
    WHEN '李四' THEN 2
    -- 更多条件
END
WHERE name IN ('张三', '李四', -- 更多名称);

代码解释：

在批量插入示例中，我们一次性插入多条记录，而不是对每条记录进行单独的插入操作。

在批量更新示例中，我们使用CASE语句一次性更新多条记录，这比单独更新每条记录更有效率。

9、使用临时表优化复杂查询

使用场景：对于复杂的多步骤查询，使用临时表可以存储中间结果，从而简化查询并提高性能。

代码示例：

1
2
3
4
5
6
7
8
9
10

-- 创建一个临时表来存储中间结果
CREATE TEMPORARY TABLE temp_employees
SELECT department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id;

-- 使用临时表进行查询
SELECT departments.name, temp_employees.emp_count
FROM departments
JOIN temp_employees ON departments.id = temp_employees.department_id;

代码解释：

首先，我们通过聚合查询创建了一个临时表temp_employees，用于存储每个部门的员工计数。

然后，我们将这个临时表与部门表departments进行连接查询，这样的查询通常比直接在原始表上执行复杂的聚合查询要高效。

10、优化数据类型

使用场景：在设计数据库表时，选择合适的数据类型对性能有显著影响。优化数据类型可以减少存储空间，提高查询效率。

代码示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 原始表结构
CREATE TABLE example (
    id INT AUTO_INCREMENT,
    description TEXT,
    created_at DATETIME,
    is_active BOOLEAN,
    PRIMARY KEY (id)
);

-- 优化后的表结构
CREATE TABLE optimized_example (
    id MEDIUMINT AUTO_INCREMENT,
    description VARCHAR(255),
    created_at DATE,
    is_active TINYINT(1),
    PRIMARY KEY (id)
);

代码解释：

在原始表中，使用了INT和TEXT这样的宽泛类型，这可能会占用更多的存储空间。

在优化后的表中，id字段改为MEDIUMINT，description改为长度有限的VARCHAR(255)，created_at只存储日期，而is_active使用**TINYINT(1)**来表示布尔值。这样的优化减少了每行数据的大小，提高了存储效率。

11、避免使用函数和操作符

使用场景：在WHERE子句中避免对列使用函数或操作符，可以让MySQL更有效地使用索引。

代码示例：

1
2
3
4
5

-- 不推荐的查询方式，使用了函数
SELECT * FROM employees WHERE YEAR(birth_date) = 1980;

-- 推荐的查询方式
SELECT * FROM employees WHERE birth_date BETWEEN '1980-01-01' AND '1980-12-31';

代码解释：

在第一个查询中，使用**YEAR()**函数会导致MySQL无法利用索引，因为它必须对每行数据应用函数。

第二个查询直接使用日期范围，这样MySQL可以有效利用birth_date字段的索引。

12、合理使用正规化和反正规化

使用场景：数据库设计中的正规化可以减少数据冗余，而反正规化可以提高查询效率。合理平衡这两者，可以获得最佳性能。

代码示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 正规化设计
CREATE TABLE departments (
    department_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (department_id)
);

CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    department_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 反正规化设计
CREATE TABLE employees_denormalized (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    department_name VARCHAR(100),
    PRIMARY KEY (id)
);

代码解释：

在正规化设计中，departments和employees表被分开，减少了数据冗余，但可能需要JOIN操作来获取完整信息。

在反正规化设计中，employees_denormalized表通过直接包含部门信息来简化查询，提高读取性能，但可能会增加数据冗余和更新成本。

总结

以上提到的优化方法只是众多MySQL优化技术中的一小部分。在实际应用中，应根据具体的数据模式和查询需求灵活选择最合适的优化策略。数据库优化是一个持续的过程，定期的性能评估和调优是保持数据库高效运行的关键。通过实践这些优化技巧，你可以显著提升数据库的性能和响应速度。

留言與分享

为什么需要数据库？

因为应用程序需要保存用户的数据，比如Word需要把用户文档保存起来，以便下次继续编辑或者拷贝到另一台电脑。

要保存用户的数据，一个最简单的方法是把用户数据写入文件。例如，要保存一个班级所有学生的信息，可以向文件中写入一个CSV文件：

1
2
3
4
5

id,name,gender,score
1,小明,M,90
2,小红,F,95
3,小军,M,88
4,小丽,F,88

如果要保存学校所有班级的信息，可以写入另一个CSV文件。

但是，随着应用程序的功能越来越复杂，数据量越来越大，如何管理这些数据就成了大问题：

• 读写文件并解析出数据需要大量重复代码；
• 从成千上万的数据中快速查询出指定数据需要复杂的逻辑。

如果每个应用程序都各自写自己的读写数据的代码，一方面效率低，容易出错，另一方面，每个应用程序访问数据的接口都不相同，数据难以复用。

所以，数据库作为一种专门管理数据的软件就出现了。应用程序不需要自己管理数据，而是通过数据库软件提供的接口来读写数据。至于数据本身如何存储到文件，那是数据库软件的事情，应用程序自己并不关心：

1
2
3
4
5
6
7
8
9
10
11

┌───────────┐
│application│
└───────────┘
     ▲ │
     │ │
 read│ │write
     │ │
     │ ▼
┌───────────┐
│ database  │
└───────────┘

这样一来，编写应用程序的时候，数据读写的功能就被大大地简化了。

数据模型

数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：

• 层次模型
• 网状模型
• 关系模型

层次模型就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

            ┌─────┐
            │     │
            └─────┘
               │
       ┌───────┴───────┐
       │               │
    ┌─────┐         ┌─────┐
    │     │         │     │
    └─────┘         └─────┘
       │               │
   ┌───┴───┐       ┌───┴───┐
   │       │       │       │
┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐
│     │ │     │ │     │ │     │
└─────┘ └─────┘ └─────┘ └─────┘

网状模型把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

     ┌─────┐      ┌─────┐
   ┌─│     │──────│     │──┐
   │ └─────┘      └─────┘  │
   │    │            │     │
   │    └──────┬─────┘     │
   │           │           │
┌─────┐     ┌─────┐     ┌─────┐
│     │─────│     │─────│     │
└─────┘     └─────┘     └─────┘
   │           │           │
   │     ┌─────┴─────┐     │
   │     │           │     │
   │  ┌─────┐     ┌─────┐  │
   └──│     │─────│     │──┘
      └─────┘     └─────┘

关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表：

1
2
3
4
5
6
7
8
9

┌─────┬─────┬─────┬─────┬─────┐
│     │     │     │     │     │
├─────┼─────┼─────┼─────┼─────┤
│     │     │     │     │     │
├─────┼─────┼─────┼─────┼─────┤
│     │     │     │     │     │
├─────┼─────┼─────┼─────┼─────┤
│     │     │     │     │     │
└─────┴─────┴─────┴─────┴─────┘

随着时间的推移和市场竞争，最终，基于关系模型的关系数据库获得了绝对市场份额。

为什么关系数据库获得了最广泛的应用？

因为相比层次模型和网状模型，关系模型理解和使用起来最简单。

关系数据库的关系模型是基于数学理论建立的。我们把域（Domain）定义为一组具有相同数据类型的值的集合，给定一组域D1,D2,…,Dn，它们的笛卡尔集定义为D1×D2×……×Dn={(d1,d2,…,dn)|di∈Di,i=1,2,…,n}， 而D1×D2×……×Dn的子集叫作在域D1,D2,…,Dn上的关系，表示为R(D1,D2,…,Dn)，这里的R表示#%&^@!&$#;!~％¥%……算了，根本讲不明白，大家也不用理解。

基于数学理论的关系模型虽然讲起来挺复杂，但是，基于日常生活的关系模型却十分容易理解。我们以学校班级为例，一个班级的学生就可以用一个表格存起来，并且定义如下：

其中，班级ID对应着另一个班级表：

通过给定一个班级名称，可以查到一条班级记录，根据班级ID，又可以查到多条学生记录，这样，二维表之间就通过ID映射建立了“一对多”关系。

数据类型

对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等：

上面的表中列举了最常用的数据类型。很多数据类型还有别名，例如，REAL又可以写成FLOAT(24)。还有一些不常用的数据类型，例如，TINYINT（范围在0~255）。各数据库厂商还会支持特定的数据类型，例如JSON。

选择数据类型的时候，要根据业务规则选择合适的类型。通常来说，BIGINT能满足整数存储的需求，VARCHAR(N)能满足字符串存储的需求，这两种类型是使用最广泛的。

主流关系数据库

目前，主流的关系数据库主要分为以下几类：

1. 商用数据库，例如：Oracle，SQL Server，DB2等；
2. 开源数据库，例如：MySQL，PostgreSQL等；
3. 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
4. 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

SQL

什么是SQL？SQL是结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。不同的数据库，都支持SQL，这样，我们通过学习SQL这一种语言，就可以操作各种不同的数据库。

虽然SQL已经被ANSI组织定义为标准，不幸地是，各个不同的数据库对标准的SQL支持不太一致。并且，大部分数据库都在标准的SQL上做了扩展。也就是说，如果只使用标准SQL，理论上所有数据库都可以支持，但如果使用某个特定数据库的扩展SQL，换一个数据库就不能执行了。例如，Oracle把自己扩展的SQL称为PL/SQL，Microsoft把自己扩展的SQL称为T-SQL。

现实情况是，如果我们只使用标准SQL的核心功能，那么所有数据库通常都可以执行。不常用的SQL功能，不同的数据库支持的程度都不一样。而各个数据库支持的各自扩展的功能，通常我们把它们称之为“方言”。

总的来说，SQL语言定义了这么几种操作数据库的能力：

DDL：Data Definition Language

DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

DML：Data Manipulation Language

DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

DQL：Data Query Language

DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

语法特点

SQL语言关键字不区分大小写！！！但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。

所以，本教程约定：SQL关键字总是大写，以示突出，表名和列名均使用小写。

MySQL是目前应用最广泛的开源关系数据库。MySQL最早是由瑞典的MySQL AB公司开发，该公司在2008年被SUN公司收购，紧接着，SUN公司在2009年被Oracle公司收购，所以MySQL最终就变成了Oracle旗下的产品。

和其他关系数据库有所不同的是，MySQL本身实际上只是一个SQL接口，它的内部还包含了多种数据引擎，常用的包括：

• InnoDB：由Innobase Oy公司开发的一款支持事务的数据库引擎，2006年被Oracle收购；
• MyISAM：MySQL早期集成的默认数据库引擎，不支持事务。

MySQL接口和数据库引擎的关系就好比某某浏览器和浏览器引擎（IE引擎或Webkit引擎）的关系。对用户而言，切换浏览器引擎不影响浏览器界面，切换MySQL引擎不影响自己写的应用程序使用MySQL的接口。

使用MySQL时，不同的表还可以使用不同的数据库引擎。如果你不知道应该采用哪种引擎，记住总是选择InnoDB就好了。

因为MySQL一开始就是开源的，所以基于MySQL的开源版本，又衍生出了各种版本：

MariaDB

由MySQL的创始人创建的一个开源分支版本，使用XtraDB引擎。

Aurora

由Amazon改进的一个MySQL版本，专门提供给在AWS托管MySQL用户，号称5倍的性能提升。

PolarDB

由Alibaba改进的一个MySQL版本，专门提供给在阿里云托管的MySQL用户，号称6倍的性能提升。

而MySQL官方版本又分了好几个版本：

• Community Edition：社区开源版本，免费；
• Standard Edition：标准版；
• Enterprise Edition：企业版；
• Cluster Carrier Grade Edition：集群版。

以上版本的功能依次递增，价格也依次递增。不过，功能增加的主要是监控、集群等管理功能，对于基本的SQL功能是完全一样的。

所以使用MySQL就带来了一个巨大的好处：可以在自己的电脑上安装免费的Community Edition版本，进行学习、开发、测试，部署的时候，可以选择付费的高级版本，或者云服务商提供的兼容版本，而不需要对应用程序本身做改动。

安装MySQL

要安装MySQL，可以从MySQL官方网站下载最新的MySQL Community Server版本：

https://dev.mysql.com/downloads/mysql/

选择对应的操作系统版本，下载安装即可。在安装过程中，MySQL会自动创建一个root用户，并提示输入root口令。

要在Linux上安装MySQL，可以使用发行版的包管理器。例如，Debian和Ubuntu用户可以简单地通过命令apt install mysql-server安装最新的MySQL版本。

MySQL安装后会自动在后台运行。为了验证MySQL安装是否正确，我们需要通过mysql这个命令行程序来连接MySQL服务器。

在命令提示符下输入mysql -u root -p，然后输入口令，如果一切正确，就会连接到MySQL服务器，同时提示符变为mysql>。

输入exit退出MySQL命令行。注意，MySQL服务器仍在后台运行。

使用Docker运行MySQL

另一种运行MySQL的方式不需要下载安装包，而是直接通过Docker安装最新的MySQL：

首先安装Docker Desktop，然后在命令行输入以下命令拉取MySQL最新版：

1

$ docker pull mysql

拉取完成后，输入以下命令直接启动MySQL服务器：

1

$ docker run -d --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -v /Users/chankein/mysql-data:/var/lib/mysql mysql

命令docker run表示启动一个容器，后面各参数含义如下：

• -d：表示在后台执行；
• --name mysql：表示容器的名字，不输入Docker会自动选择一个名字；
• -p 3306:3306：表示把容器的端口3306映射到本机，这样可以在本机通过3306端口连接MySQL；
• -e MYSQL_ROOT_PASSWORD=password：表示传入一个环境变量，作为root的口令，这里设置的口令是password，不输入此项则会自动生成一个口令，需要查看日志才能知道口令；
• -v /Users/chankein/mysql-data:/var/lib/mysql：表示将本地目录映射到容器目录/var/lib/mysql作为MySQL数据库存放的位置，需要将/Users/chankein/mysql-data改为你的电脑上的实际目录；
• mysql：最后一个参数是Docker镜像的名称。

可以在Docker Desktop的管理窗口中选择Containers，看到正在运行的MySQL：

点击MySQL查看日志：

点击Exec进入命令行，输入命令mysql -u root -p，输入口令，即可进入MySQL命令行界面：

使用Docker运行MySQL时，任何时候都可以删除MySQL容器并重新运行。如果删除了本地映射的目录，重新运行就相当于一个全新的MySQL，因此，建议仅作为学习和开发使用，不要存储重要的数据。

关系模型

我们已经知道，关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。

表的每一行称为记录（Record），记录是一个逻辑意义上的数据。

表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串''。

提示

通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。

和Excel表有所不同的是，关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。

例如，一个班级表：

每一行对应着一个班级，而一个班级对应着多个学生，所以班级表和学生表的关系就是“一对多”：

反过来，如果我们先在学生表中定位了一行记录，例如ID=1的小明，要确定他的班级，只需要根据他的“班级ID”对应的值201找到班级表中ID=201的记录，即二年级一班。所以，学生表和班级表是“多对一”的关系。

如果我们把班级表分拆得细一点，例如，单独创建一个教师表：

班级表只存储教师ID：

这样，一个班级总是对应一个教师，班级表和教师表就是“一对一”关系。

在关系数据库中，关系是通过主键和外键来维护的。我们在后面会分别深入讲解。

主键

在关系数据库中，一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。例如，students表的两行记录：

每一条记录都包含若干定义好的字段。同一个表的所有记录都有相同的字段定义。

对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

例如，假设我们把name字段作为主键，那么通过名字小明或小红就能唯一确定一条记录。但是，这么设定，就没法存储同名的同学了，因为插入相同主键的两条记录是不被允许的。

对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。

由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。如果我们以学生的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。

所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。

因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。

作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有：

1. 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
2. 全局唯一GUID类型：也称UUID，使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

对于大部分应用来说，通常自增类型的主键就能满足需求。我们在students表中定义的主键也是BIGINT NOT NULL AUTO_INCREMENT类型。

注意

如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。

联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

对于联合主键，允许一列有重复，只要不是所有主键列都重复即可：

如果我们把上述表的id_num和id_type这两列作为联合主键，那么上面的3条记录都是允许的，因为没有两列主键组合起来是相同的。

没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

小结

主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。

可以使用多个列作为联合主键，但联合主键并不常用。

当我们用主键唯一标识记录时，我们就可以在students表中确定任意一个学生的记录：

我们还可以在classes表中确定任意一个班级记录：

但是我们如何确定students表的一条记录，例如，id=1的小明，属于哪个班级呢？

由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个classes的记录可以对应多个students表的记录。

为了表达这种一对多的关系，我们需要在students表中加入一列class_id，让它的值与classes表的某条记录相对应：

这样，我们就可以根据class_id这个列直接定位出一个students表的记录应该对应到classes的哪条记录。

例如：

• 小明的class_id是1，因此，对应的classes表的记录是id=1的一班；
• 小红的class_id是1，因此，对应的classes表的记录是id=1的一班；
• 小白的class_id是2，因此，对应的classes表的记录是id=2的二班。

在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。

外键并不是通过列名实现的，而是通过定义外键约束实现的：

1
2
3
4

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。

通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录。

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。

要删除一个外键约束，也是通过ALTER TABLE实现的：

1
2

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

多对多

通过一个表的外键关联到另一个表，我们可以定义出一对多关系。有些时候，还需要定义“多对多”关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系：

teachers表：

classes表：

中间表teacher_class关联两个一对多关系：

通过中间表teacher_class可知teachers到classes的关系：

• id=1的张老师对应id=1,2的一班和二班；
• id=2的王老师对应id=1,2的一班和二班；
• id=3的李老师对应id=1的一班；
• id=4的赵老师对应id=2的二班。

同理可知classes到teachers的关系：

• id=1的一班对应id=1,2,3的张老师、王老师和李老师；
• id=2的二班对应id=1,2,4的张老师、王老师和赵老师；

因此，通过中间表，我们就定义了一个“多对多”关系。

一对一

一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。

例如，students表的每个学生可以有自己的联系方式，如果把联系方式存入另一个表contacts，我们就可以得到一个“一对一”关系：

有细心的童鞋会问，既然是一对一关系，那为啥不给students表增加一个mobile列，这样就能合二为一了？

如果业务允许，完全可以把两个表合为一个表。但是，有些时候，如果某个学生没有手机号，那么，contacts表就不存在对应的记录。实际上，一对一关系准确地说，是contacts表一对一对应students表。

还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度。

小结

关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

例如，对于students表：

如果要经常根据score列进行查询，就可以对score列创建索引：

1
2

ALTER TABLE students
ADD INDEX idx_score (score);

使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

1
2

ALTER TABLE students
ADD INDEX idx_name_score (name, score);

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。

可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。

但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设students表的name不能重复：

1
2

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

通过UNIQUE关键字我们就添加了一个唯一索引。

也可以只对某一列添加一个唯一约束而不创建唯一索引：

1
2

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

这种情况下，name列没有索引，但仍然具有唯一性保证。

无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别。这里的意思是说，当我们在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率，如果没有索引，查询也能正常执行，只是速度会变慢。因此，索引可以在使用数据库的过程中逐步优化。

小结

通过对数据库表创建索引，可以提高查询速度；

通过创建唯一索引，可以保证某一列的值具有唯一性；

数据库索引对于用户和应用程序来说都是透明的。

在关系数据库中，最常用的操作就是查询。

准备数据

为了便于讲解和练习，我们先准备好了一个students表和一个classes表，它们的结构和数据如下：

students表存储了学生信息：

classes表存储了班级信息：

请注意，和MySQL的持久化存储不同的是，由于我们使用的是AlaSQL内存数据库，两张表的数据在页面加载时导入，并且只存在于浏览器的内存中，因此，刷新页面后，数据会重置为上述初始值。

MySQL

如果你想用MySQL练习，可以下载这个SQL脚本，然后在命令行运行：

1

$ mysql -u root -p < init-test-data.sql

就可以自动创建test数据库，并且在test数据库下创建students表和classes表，以及必要的初始化数据。

和内存数据库不同的是，对MySQL数据库做的所有修改，都会保存下来。如果你希望恢复到初始状态，可以再次运行该脚本。

基本查询

要查询数据库表的数据，我们使用如下的SQL语句：

1

SELECT * FROM <表名>

假设表名是students，要查询students表的所有行，我们用如下SQL语句：

1
2

-- 查询students表的所有数据
SELECT * FROM students;

使用SELECT * FROM students时，SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询，本例中是students表。

该SQL将查询出students表的所有数据。注意：查询结果也是一个二维表，它包含列名和每一行的数据。

要查询classes表的所有行，我们用如下SQL语句：

1
2

-- 查询classes表的所有数据
SELECT * FROM classes;

运行上述SQL语句，观察查询结果。

SELECT语句其实并不要求一定要有FROM子句。我们来试试下面的SELECT语句：

1
2

-- 计算100+200
SELECT 100+200;

上述查询会直接计算出表达式的结果。虽然SELECT可以用作计算，但它并不是SQL的强项。但是，不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

小结

使用SELECT查询的基本语句SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据；

SELECT查询的结果是一个二维表。

使用SELECT * FROM <表名>可以查询到一张表的所有记录。但是，很多时候，我们并不希望获得所有记录，而是根据条件选择性地获取指定条件的记录，例如，查询分数在80分以上的学生记录。在一张表有数百万记录的情况下，获取所有记录不仅费时，还费内存和网络带宽。

SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。例如，要指定条件“分数在80分或以上的学生”，写成WHERE条件就是SELECT * FROM students WHERE score >= 80。

其中，WHERE关键字后面的score >= 80就是条件。score是列名，该列存储了学生的成绩，因此，score >= 80就筛选出了指定条件的记录：

1
2

-- 按条件查询students:
SELECT * FROM students WHERE score >= 80;

因此，条件查询的语法就是：

1

SELECT * FROM <表名> WHERE <条件表达式>

条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。例如，符合条件“分数在80分或以上”，并且还符合条件“男生”，把这两个条件写出来：

• 条件1：根据score列的数据判断：score >= 80；
• 条件2：根据gender列的数据判断：gender = 'M'，注意gender列存储的是字符串，需要用单引号括起来。

就可以写出WHERE条件：score >= 80 AND gender = 'M'：

1
2

-- 按AND条件查询students:
SELECT * FROM students WHERE score >= 80 AND gender = 'M';

第二种条件是<条件1> OR <条件2>，表示满足条件1或者满足条件2。例如，把上述AND查询的两个条件改为OR，查询结果就是“分数在80分或以上”或者“男生”，满足任意之一的条件即选出该记录：

1
2

-- 按OR条件查询students:
SELECT * FROM students WHERE score >= 80 OR gender = 'M';

很显然OR条件要比AND条件宽松，返回的符合条件的记录也更多。

第三种条件是NOT <条件>，表示“不符合该条件”的记录。例如，写一个“不是2班的学生”这个条件，可以先写出“是2班的学生”：class_id = 2，再加上NOT：NOT class_id = 2：

1
2

-- 按NOT条件查询students:
SELECT * FROM students WHERE NOT class_id = 2;

上述NOT条件NOT class_id = 2其实等价于class_id <> 2，因此，NOT查询不是很常用。

要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。例如，编写一个复杂的条件：分数在80以下或者90以上，并且是男生：

1
2

-- 按多个条件查询students:
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

常用的条件表达式

查询分数在60分(含)～90分(含)之间的学生可以使用的WHERE语句是：

小结

通过WHERE条件查询，可以筛选出符合指定条件的记录，而不是整个表的所有记录。

投影查询

使用SELECT * FROM <表名> WHERE <条件>可以选出表中的若干条记录。我们注意到返回的二维表结构和原表是相同的，即结果集的所有列与原表的所有列都一一对应。

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。

例如，从students表中返回id、score和name这三列：

1
2

-- 使用投影查询
SELECT id, score, name FROM students;

这样返回的结果集就只包含了我们指定的列，并且，结果集的列的顺序和原表可以不一样。

使用SELECT 列1, 列2, 列3 FROM ...时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...。

例如，以下SELECT语句将列名score重命名为points，而id和name列名保持不变：

1
2

-- 使用投影查询，并将列名重命名：
SELECT id, score points, name FROM students;

投影查询同样可以接WHERE条件，实现复杂的查询：

1
2

-- 使用投影查询+WHERE条件：
SELECT id, score points, name FROM students WHERE gender = 'M';

小结

使用SELECT *表示查询表的所有列，使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影；

SELECT语句可以对结果集的列进行重命名。

排序

排序

我们使用SELECT查询时，细心的读者可能注意到，查询结果集通常是按照id排序的，也就是根据主键排序。这也是大部分数据库的做法。如果我们要根据其他条件排序怎么办？可以加上ORDER BY子句。例如按照成绩从低到高进行排序：

1
2

-- 按score从低到高:
SELECT id, name, gender, score FROM students ORDER BY score;

如果要反过来，按照成绩从高到底排序，我们可以加上DESC表示“倒序”：

1
2

-- 按score从高到低:
SELECT id, name, gender, score FROM students ORDER BY score DESC;

如果score列有相同的数据，要进一步排序，可以继续添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：

1
2