MySQL

MySQL 服务器配置文件一般名为 my.cnf ，它的所在目录可以通过下面命令得到：

$ /usr/sbin/mysqld --help --verbose |more

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

查看系统变量有很多方法。

方法一、通过 show variables; 可以查看服务器所有系统变量。如：

mysql> show variables;
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| auto_increment_increment        | 1                                 |
| auto_increment_offset           | 1                                 |
| automatic_sp_privileges         | ON                                |
| back_log                        | 50                                |
| basedir                         | /home/mysql/                      |
| binlog_cache_size               | 32768                             |
| bulk_insert_buffer_size         | 8388608                           |
| character_set_client            | utf8                              |
| character_set_connection        | utf8                              |
| character_set_database          | latin1                            |
| character_set_filesystem        | binary                            |
| character_set_results           | utf8                              |
| character_set_server            | latin1                            |
| character_set_system            | utf8                              |
| character_sets_dir              | /home/mysql/share/mysql/charsets/ |
| collation_connection            | utf8_general_ci                   |
| collation_database              | latin1_swedish_ci                 |
| collation_server                | latin1_swedish_ci                 |
...
| innodb_additional_mem_pool_size | 1048576                           |
| innodb_autoextend_increment     | 8                                 |
| innodb_buffer_pool_size         | 8388608                           |
| innodb_checksums                | ON                                |
| innodb_commit_concurrency       | 0                                 |
| innodb_concurrency_tickets      | 500                               |
| innodb_data_file_path           | ibdata1:10M:autoextend            |
| innodb_data_home_dir            |                                   |
...
| version                         | 5.5.56-log                        |
| version_comment                 | Source distribution               |
| version_compile_machine         | i686                              |
| version_compile_os              | suse-linux                        |
| wait_timeout                    | 28800                             |
+---------------------------------+-----------------------------------+

方法二、通过 show variables like; 可以配置指定模式的系统变量。如：

mysql> show variables like '%commit%';              -- 包含commit的系统变量
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| autocommit                              | ON    |
| binlog_group_commit_sync_delay          | 0     |
| binlog_group_commit_sync_no_delay_count | 0     |
| binlog_order_commits                    | ON    |
| innodb_api_bk_commit_interval           | 5     |
| innodb_commit_concurrency               | 0     |
| innodb_flush_log_at_trx_commit          | 1     |
| slave_preserve_commit_order             | OFF   |
+-----------------------------------------+-------+
8 rows in set (0.01 sec)

mysql> show variables like '%commit';              -- 以commit结尾的系统变量
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | ON    |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

方法三、通过 select @@varname; 可以查看某个系统变量的当前值。如：

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

修改“全局系统变量”，有下面两种形式：

SET GLOBAL max_connections = 1000;
SET @@GLOBAL.max_connections = 1000;    // 同上

修改“session 系统变量”，有下面很多种形式：

SET SESSION sql_mode = 'TRADITIONAL';
SET LOCAL sql_mode = 'TRADITIONAL';      // 同上
SET @@SESSION.sql_mode = 'TRADITIONAL';  // 同上
SET @@LOCAL.sql_mode = 'TRADITIONAL';    // 同上
SET @@sql_mode = 'TRADITIONAL';          // 同上
SET sql_mode = 'TRADITIONAL';            // 同上

下面操作可以把数据库 db1 复制为 db2：

shell> mysqldump db1 > dump.sql
shell> mysqladmin create db2
shell> mysql db2 < dump.sql

参考：https://dev.mysql.com/doc/refman/8.0/en/mysqldump-copying-database.html

mysql> CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t SET c = 'a';
Query OK, 1 row affected (0.03 sec)

mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;
+--------+---------+-------------+
| HEX(c) | c = 'a' | c = 'a\0\0' |
+--------+---------+-------------+
| 610000 |       0 |           1 |
+--------+---------+-------------+
1 row in set (0.09 sec)

mysql> INSERT INTO t SET c = 0x62;
Query OK, 1 row affected (0.11 sec)

mysql> select hex(c) from t;
+--------+
| hex(c) |
+--------+
| 610000 |
| 620000 |
+--------+
2 rows in set (0.00 sec)

MySQL 中 DATETIME 和 TIMESTAMP 两者都是时间类型字段。它们的区别如下：
不同点一、TIMESTAMP 会跟随设置的时区变化而变化，而 DATETIME 保存的是什么拿出来就是什么，不受时区影响。
不同点二、DATETIME 类型的存储长度是 8 字节，而 TIMESTAMP 类型的存储长度 4 字节，这导致 TIMESTAMP 支持的范围的比 DATETIME 的要小。The range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'.

注 1、MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)
注 2：在 MySQL 5.6.5 版本之前，Automatic Initialization and Updating（更新某行数据时自动更新时间字段）只适用于 TIMESTAMP，而且一张表中，最多允许一个 TIMESTAMP 字段采用该特性。从 MySQL 5.6.5 开始，Automatic Initialization and Updating 同时适用于 TIMESTAMP 和 DATETIME，且不限制数量。

-- 字段类型为 TIMESTAMP
CREATE TABLE `t1` (
`id` int unsigned AUTO_INCREMENT,
`created_at` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`updated_at` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`deleted_at` TIMESTAMP NULL,
PRIMARY KEY (`id`)
)

-- 同上。只不过是字段类型为 DATETIME
CREATE TABLE `t2` (
`id` int unsigned AUTO_INCREMENT,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME NULL,
PRIMARY KEY (`id`)
)

使用 show variables like '%log_bin%'; 可以查看 Binary Log 是否开启：

MySQL [(none)]> show variables like '%log_bin%';
+---------------------------------+----------------------------+
| Variable_name                   | Value                      |
+---------------------------------+----------------------------+
| log_bin                         | ON                         |
| log_bin_basename                | /rdsdbdata/db/binlog       |
| log_bin_index                   | /rdsdbdata/db/binlog.index |
| log_bin_trust_function_creators | OFF                        |
| log_bin_use_v1_row_events       | OFF                        |
| sql_log_bin                     | ON                         |
+---------------------------------+----------------------------+
6 rows in set (0.01 sec)

上面输出中，log_bin 变量对应的 Value 那列显示“ON/OFF”分别表示“开启/关闭”了 Binary Log。

使用 show binary logs; （或者 show master logs; ）可以查看系统中的 Binary Log 文件列表：

MySQL [(none)]> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
.......
| binlog.019884 |  20315325 |
| binlog.019885 |  20337827 |
| binlog.019886 |     83627 |
| binlog.019887 |     63267 |
| binlog.019888 |  20376091 |
| binlog.019889 |  20334810 |
| binlog.019890 |  20381044 |
| binlog.019891 |  20348220 |
| binlog.019892 |     52217 |
| binlog.019893 |     22295 |
| binlog.019894 |  40643843 |
| binlog.019895 | 142201414 |
| binlog.019896 |  20324146 |
| binlog.019897 |  40644610 |
+---------------+-----------+
8645 rows in set (0.02 sec)

通过 SHOW BINLOG EVENTS 可以查看 Binary Log 中的内容，如：

mysql> SHOW BINLOG EVENTS in 'TP-bin.000105'\G;
 *************************** 1. row ***************************
   Log_name: TP-bin.000105
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 125
       Info: Server ver: 8.0.22, Binlog ver: 4
 *************************** 2. row ***************************
   Log_name: TP-bin.000105
        Pos: 125
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 156
       Info:
 *************************** 3. row ***************************
   Log_name: TP-bin.000105
        Pos: 156
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 233
   Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
 *************************** 4. row ***************************
   Log_name: TP-bin.000105
        Pos: 233
 Event_type: Query
  Server_id: 1
End_log_pos: 366
   Info: use `test`; DROP TABLE `employee` /* generated by server */ /* xid=8 */
 *************************** 5. row ***************************
   Log_name: TP-bin.000105
        Pos: 366
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 445
   Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
 *************************** 6. row ***************************
   Log_name: TP-bin.000105
        Pos: 445
 Event_type: Query
  Server_id: 1
End_log_pos: 730
       Info: use `test`; CREATE TABLE EMPLOYEE(
         ID INT NOT NULL,
         FIRST_NAME CHAR(20) NOT NULL,
         LAST_NAME CHAR(20),
         AGE INT,
         SEX CHAR(1),
         INCOME FLOAT,
         CONTACT INT
) /* xid=9 */
 *************************** 7. row ***************************
   Log_name: TP-bin.000105
        Pos: 730
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 809
   Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'

使用 mysqlbinlog 可以下载 Binary Log，如：

$ mysqlbinlog \
    --read-from-remote-server \
    --host=primarydb.xxx.com \
    --port=3306  \
    --user repl \
    --password \
    --raw \
    --verbose \
    --result-file=/tmp/ \
    mysql-bin-changelog.418987

上面命令是从 primarydb.xxx.com 下载 Binary Log mysql-bin-changelog.418987，保存到本地目录 /tmp 中。

使用 mysqlbinlog 可以查看 Binary Log 文件中的内容：

$ mysqlbinlog /tmp/mysql-bin-changelog.418987

在 MySQL 的主从模式中，Binary Log 会发送给“从库”来实现同步。如果不定期清理 Binary Log，会占用过多的磁盘空间。

我们可以设置日志保留天数（即变量 expire_logs_days ）来达到自动删除 Binary Log 的效果。

查看当前的日志保存天数：

MySQL [(none)]> show variables like '%expire_logs_days%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.01 sec)

expire_logs_days 的默认值是 0，也就是日志不过期，可通过设置全局的参数，使它临时生效：

MySQL [(none)]>  set global expire_logs_days=7;

设置了只保留 7 天 BINLOG, 下次重启 mysql 这个参数默认会失败。要永远有效，需要在 my.cnf 中设置：

expire_logs_days = 7

我们也使用下面命令手动删除 Binary Log：

PURGE {MASTER | BINARY} LOGS TO 'log_name'
PURGE {MASTER | BINARY} LOGS BEFORE 'date'

比如：

PURGE BINARY LOGS TO 'mysql-bin.010';                       -- 删除指定log之前的Binary Log
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';             -- 删除指定日期之前的Binary Log
PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY); -- 删除7天前的Binary Log

异步复制是 MySQL 主从复制模型下的默认工作方式。

异步复制的机制如下：主库执行完写操作后，且写入 binlog 日志后，就返回客户端。这个过程中，并不会验证从库有没有收到，所以这样可能会造成数据的不一致。换句话说，复制过程中数据是否一致，主要取决于 binlog 日志的安全性与完整性。

如果主库突然宕机，那么 binlog 可能还没有安全地传输给任何一个从库。这时，进行故障恢复（把从库升级为主库，以提供服务）后，可能会丢失之前交易对数据的改动。

前面说过，采用异步复制时，主库不会去验证 binlog 有没有成功复制到从库，这有潜在的数据丢失风险。

半同步复制：一主多从模式下，有一个从库返回成功，即成功，不必等待多个从库全部返回。

MySQL5.5 中增加了半同步复制（Semi-Synchronous Replication），它是由 Google 贡献的补丁。 半同步复制的机制如下：主库在应答客户端提交的事务前需要保证“至少一个从库”接收并写到 relay log 中。 从而，能更好地保证数据的安全性和一致性。

而半同步复制，当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到 Binlog 并成功写入 Relay-log 中才返回客户端，所以这样就保证了一个事务至少有两份日志，一份保存在主库的 Binlog，另一份保存在其中一个从库的 Relay-log 中，从而保证了数据的安全性和一致性。

需要说明的是： 在半同步复制中，如果主库的一个事务提交成功了，但在推送到从库的过程当中，从库宕机了或网络故障，导致从库并没有接收到这个事务的 binlog，此时主库会等待一段时间（这个时间由 rpl_semi_sync_master_timeout 的毫秒数决定，默认为 10000，即 10s ），如果这个时间过后还无法推送到从库，那 MySQL 会自动从半同步复制切换为异步复制，当从库恢复正常连接到主库后，主库又会自动切换回半同步复制。

要启用半同步复制，需要主库从库都安装对应插件：

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> SELECT * FROM mysql.plugin;
+----------------------+--------------------+
| name                 | dl                 |
+----------------------+--------------------+
| rpl_semi_sync_master | semisync_master.so |
| rpl_semi_sync_slave  | semisync_slave.so  |
+----------------------+--------------------+
2 rows in set (0.00 sec)

参考：https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html

MySQL 5.6.5 开始新增了一种基于 GTID (Global Transaction Identifieds) 的复制方式。GTID 可以保证每个在主库上提交的事务在集群中有一个唯一的 ID。

基于 GTID 的复制不像传统的复制方式（异步复制、半同步复制）那样复制时需要找到 binlog（MASTER_LOG_FILE）及 POS 点（MASTER_LOG_POS），GTID 复制只需要知道主库的 IP、端口、账号、密码即可。启用 GTID 复制后，MySQL 会通过内部机制自动找点同步。

下面是 GTID 的两个关键配置：

gtid_mode=ON
enforce-gtid-consistency=true

启用 GTID 复制后，有些 SQL 语句是禁用的，如 CREATE TABLE xxx AS SELECT 语句（其实会被拆分为两部分，CREATE 语句和 INSERT 语句）：

mysql> CREATE TABLE test2 AS SELECT * FROM test1;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

参考：
https://dev.mysql.com/doc/refman/8.0/en/replication-gtids.html
https://severalnines.com/resources/tutorials/mysql-replication-high-availability-tutorial

下面介绍一下主从复制 MySQL 集群的部署。

在开始之前，请做好下面 3 个准备工作：
准备 1、开启 Binary Log 功能。从 MySQL 8.0 开始，Binary Log 功能默认是开启的，不用额外操作。通过 SHOW VARIABLES LIKE '%log_bin%;' 可以检查目前 Binary Log 功能是否开启：

mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+----------------------------+
| Variable_name                   | Value                      |
+---------------------------------+----------------------------+
| log_bin                         | ON                         |
| log_bin_basename                | /rdsdbdata/db/binlog       |
| log_bin_index                   | /rdsdbdata/db/binlog.index |
| log_bin_trust_function_creators | OFF                        |
| log_bin_use_v1_row_events       | OFF                        |
| sql_log_bin                     | ON                         |
+---------------------------------+----------------------------+
6 rows in set (0.01 sec)

如果 Binary Log 功能没有开启，则通过指定 --log-bin 选项启动 mysqld 可以开启 Binary Log 功能。

准备 2、确保主库和从库的 server-id 是不相同的。通过 SHOW VARIABLES LIKE 'server_id'; 可以查看当前的 server_id：

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

尽管通过 SET GLOBAL server_id = 123; 可以修改 server_id，但当 MySQL 重启后，这个修改就失效了。建议在配置文件 my.conf 中修改 server_id：

[mysqld]
server-id=123

准备 3、主库上创建用于同步的用户（假设用户名为 repl），并为这个新用户进行授权：

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'mypassword';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

这个用户后面会用到。

在做完上面 3 个准备工作后。主库要进行下面操作：

mysql> FLUSH TABLES WITH READ LOCK;             -- 第一步，为了数据一致，先把主库加锁，让主库处于只读状态
mysql> SHOW MASTER STATUS\G                     -- 第二步，查看目前 binlog 的状态
 *************************** 1. row ***************************
             File: binlog.000003
         Position: 73
     Binlog_Do_DB: test
 Binlog_Ignore_DB: manual, mysql
Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5
1 row in set (0.00 sec)
$ mysqldump -u root -p --all-databases --source-data > dbdump.sql    # 第三步，导出主库所有数据到 dbdump.sql 中，后面从库会从这个文件加载数据

把主库中导出的文件 dbdump.sql 复制到从库所在机器，在从库中执行 mysql -u root -p < dbdump.sql 加载 dbdump.sql 中的内容，这样从库的内容就行主库一样了。

在从库中执行 CHANGE REPLICATION SOURCE TO 让从库与主库建立连接，同时配置从哪个 binlog 的哪个位置开始复制：

mysql> CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primarydb.xxx.com',
  SOURCE_PORT=3306,
  SOURCE_USER='repl',
  SOURCE_PASSWORD='mypassword',
  SOURCE_LOG_FILE='binlog.000003',
  SOURCE_LOG_POS=73;                  -- 在从库中配置主库的地址，配置从哪个 binlog 的哪个位置开始复制
mysql> START REPLICA;                 -- 开始主从同步

上面 SOURCE_USER/SOURCE_PASSWORD 就是前面在主库中创建的用于同步的用户名和密码；SOURCE_LOG_FILE/SOURCE_LOG_POS 可以从主库中执行 SHOW MASTER STATUS\G 时的输出中找到。

在从库中执行 SHOW REPLICA STATUS\G 可以查看目前的同步状态：

mysql> SHOW REPLICA STATUS\G
 *************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 127.0.0.1
                  Source_User: repl
                  Source_Port: 3307
                Connect_Retry: 60
              Source_Log_File: binlog.000002
......
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
......

如果 Slave_IO_Running 与 Slave_SQL_Running 都为 Yes，说明配置成功。

参考：https://dev.mysql.com/doc/refman/8.0/en/replication-howto.html

前面介绍了 EXPLAIN 输出中 type 列如果为 ALL 表示“全表扫描”，这列还有其他一些常见可能值，它们的含义如下：

• system ：查询 MySQL 系统表，数据很少，往往不需要进行磁盘 I/O；
  
• const ：常量连接，主键；
  
• eq_ref ：主键索引 (primary key) 或者非空唯一索引 (unique not null) 等值扫描；
  
• ref ：非主键非唯一索引等值扫描；
  
• range ：范围扫描；
  
• index ：会扫描索引上的全部数据；
  
• ALL ：全表扫描 (full table scan)。
  

上面各类扫描方式，由快到慢分别为：
system > const > eq_ref > ref > range > index > ALL

参考：
https://www.tuicool.com/articles/jyMviu3
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
https://severalnines.com/blog/become-mysql-dba-blog-series-using-explain-improve-sql-queries

create table user (
    id int primary key ,
    name varchar(50),
    index(name)
) engine=innodb;

insert into user values(1, 'john');
insert into user values(2, 'jack');
insert into user values(3, 'alice');
insert into user values(4, 'bob');

mysql> explain select * from user where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where id < 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where name like 'j%';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | user  | NULL       | range | name          | name | 203     | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

create table user (
    id int primary key ,
    name varchar(50),
    index(name)
) engine=innodb;

insert into user values(1, 'john');
insert into user values(2, 'jack');
insert into user values(3, 'alice');
insert into user values(4, 'bob');

create table user_ex (
    id int primary key ,
    age int
) engine=innodb;

insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);

mysql> explain select * from user, user_ex where user.id=user_ex.id;
+----+-------------+---------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra       |
+----+-------------+---------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | user    | NULL       | index  | PRIMARY       | name    | 203     | NULL         |    4 |   100.00 | Using index |
|  1 | SIMPLE      | user_ex | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.user.id |    1 |   100.00 | NULL        |
+----+-------------+---------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

create table user (
    id int,
    name varchar(50),
    index(name, id)
) engine=innodb;

insert into user values(1, 'john');
insert into user values(2, 'jack');
insert into user values(3, 'alice');
insert into user values(4, 'bob');

create table user_ex (
    id int,
    age int,
    index(id)
) engine=innodb;

insert into user_ex values(1,18);
insert into user_ex values(2,20);
insert into user_ex values(3,30);
insert into user_ex values(4,40);

mysql> explain select * from user where name='alice';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | name          | name | 203     | const |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

create table user (
    id int primary key ,
    name varchar(50),
    index(name)
) engine=innodb;

insert into user values(1, 'john');
insert into user values(2, 'jack');
insert into user values(3, 'alice');
insert into user values(4, 'bob');

mysql> explain select count(*) from user;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | name | 208     | NULL |    4 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

对数据进行分布查询时，我们往往会用到类似于 select * from table where c1 = xx limit 10 offset 10000 这样的语句。

一个非常常见又令人头疼的问题就是，在 offset 非常大的时候，例如 limit 20 offset 10000 这样的査询，MySQL 需要査询 10020 条记录然后只返回最后 20 条，前面 10000 条记录都将被抛弈，这样的代价非常高。 如果所有的页面被访问的频率都相同，那么这样的査询平均需要访问半个表的数据。

下面是同一个查询使用不同的 offset 所消耗的时间：

select * from tb1 where score = 2 order by id limit 10 offset 10000;   -- 0.02 sec
select * from tb1 where score = 2 order by id limit 10 offset 100000;  -- 0.23 sec
select * from tb1 where score = 2 order by id limit 10 offset 200000;  -- 0.71 sec
select * from tb1 where score = 2 order by id limit 10 offset 300000;  -- 1.29 sec
select * from tb1 where score = 2 order by id limit 10 offset 400000;  -- 4.66 sec
select * from tb1 where score = 2 order by id limit 10 offset 500000;  -- 7.47 sec
select * from tb1 where score = 2 order by id limit 10 offset 600000;  -- 10.02 sec

这个例子中，表 tb1 的定义如下：

CREATE TABLE tb1 (
  id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  score int,
  name varchar(20),
  email varchar(40),
  hash char(32),
  key(score)
);

从前面测试中可以发现， offset 值越大，执行的时间越长。 这个例子中，表的列比较少，如果表的列比较多（比如有几十个列），则会执行更长的时间。

为什么 offset 值越大，执行的时间越长呢？前面提到过，limit 20 offset 10000 这样的査询，MySQL 需要査询 10020 条记录然后只返回最后 20 条，前面 10000 条记录都将被抛弈，这样的代价非常高。

为什么会这样呢？这是和 MySQL 的分层结构有关系。 MySQL 存储引擎层的 Api 比较简单，只是简单地响应高层的请求，比如我们想得到某个索引下的下一条记录，调用 Api index_init 即可。但是像 limit offset 这样的“高级”功能，存储引擎层没有提供直接的支持，不是在存储引擎层实现的，而是通过对存储引擎层返回的结果进行加工来实现的（如向存储引擎层要 10020 条记录，最后丢掉前面的 10000 条记录）。

select * from tb1 where score = 2 order by id limit 10 offset 100000;

select * from tb1 where score = 2 order by id limit 10 offset 100010;

select * from tb1 where score = 2 and where id > 234120 order by id limit 10;

select id from tb1 where score = 2 order by id limit 10 offset 10000;  -- 只查询 id 列（它是主键），这是“索引覆盖扫描”
select * from tb1 where score = 2 order by id limit 10 offset 10000;   -- 查询所有列，这不是“索引覆盖扫描”。

mysql> explain select id from tb1 where score = 2 order by id limit 10 offset 600000;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+--------+----------+--------------------------+
|  1 | SIMPLE      | tb1   | NULL       | ref  | score         | score | 5       | const | 923670 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tb1 where score = 2 order by id limit 10 offset 600000;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows   | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+--------+----------+-----------------------+
|  1 | SIMPLE      | tb1   | NULL       | ref  | score         | score | 5       | const | 923670 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

select * from tb1 where score = 2 order by id limit 10 offset 10000;

select * from tb1
 inner join (select id from tb1 where score = 2 order by id limit 10 offset 10000) as xx
 on xx.id = tb1.id;    -- 注 on xx.id = tb1.id; 也可以用语法 using(id); 表示

select * from tb1 inner join (select id from tb1 where score = 2 order by id limit 10 offset 10000) as xx on xx.id = tb1.id;  -- 0.01 sec
select * from tb1 inner join (select id from tb1 where score = 2 order by id limit 10 offset 100000) as xx on xx.id = tb1.id; -- 0.02 sec
select * from tb1 inner join (select id from tb1 where score = 2 order by id limit 10 offset 200000) as xx on xx.id = tb1.id; -- 0.04 sec
select * from tb1 inner join (select id from tb1 where score = 2 order by id limit 10 offset 300000) as xx on xx.id = tb1.id; -- 0.07 sec
select * from tb1 inner join (select id from tb1 where score = 2 order by id limit 10 offset 400000) as xx on xx.id = tb1.id; -- 0.10 sec
select * from tb1 inner join (select id from tb1 where score = 2 order by id limit 10 offset 500000) as xx on xx.id = tb1.id; -- 0.11 sec
select * from tb1 inner join (select id from tb1 where score = 2 order by id limit 10 offset 600000) as xx on xx.id = tb1.id; -- 0.14 sec

假设有下面语句：

SELECT * FROM tbl_name WHERE str_col=1;

其中 str_col 是字符串类型，上面有索引。但上面查询语句拿数字和字符串比较，并不会使用索引（会全表扫描），这是因为不同的字符串，如 '1', ' 1', '1a' 等都可以转换为“数字 1”。

假设有下面测试表，表中有 5 条数据：

create table t1 (
  `id` int unsigned not null auto_increment,
  `c1` varchar(64) default null,
  PRIMARY KEY (`id`),
  KEY `idx_c1` (`c1`)
) engine=innodb default charset=utf8mb4;

insert into t1(c1) values ('1'),(' 1'),('1a'),('2'),('3');

下面测试一下 SELECT * FROM t1 WHERE c1 = 1; 和 SELECT * FROM t1 WHERE c1 = '1'; 的区别：

mysql> SELECT * FROM t1 WHERE c1 = 1;             -- 全表扫描，当数据多时严重影响性能
+----+------+
| id | c1   |
+----+------+
|  2 |  1   |
|  1 | 1    |
|  3 | 1a   |
+----+------+
3 rows in set, 1 warning (0.00 sec)

mysql> SELECT * FROM t1 WHERE c1 = '1';           -- 会利用索引 idx_c1
+----+------+
| id | c1   |
+----+------+
|  1 | 1    |
+----+------+
1 row in set (0.00 sec)

使用 explain 可以看一下这两个语句执行时的不同，如图 1 所示。

参考：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

下面是使用 mysqldump 备份 mysql 的脚本实例：

#!/bin/bash
# mysql backup script

user=your_db_username
password=your_db_password
host=your_db_host
dbname=your_dbname

bakDir=/opt/backup/mysqlbak
logFile=/opt/backup/mysqlbak.log

datetime=`date +%Y%m%d%H%M%S`

keepDay=21

echo "begin backup $datetime" >> $logFile

cd $bakDir
bakFile=$dbname.$datetime.sql.gz

# 下面是备份数据库的关键语句，备份时使用gzip压缩了sql文件，使用gunzip可以解压
mysqldump -h $host -u$user -p$password $dbname | gzip > $bakFile

echo "database [$dbname] backup finished" >> $logFile
echo "$bakDir/$bakFile" >> $logFile
echo "remove backup files before ${keepDay} days:" >> $logFile
find $bakDir -ctime +$keepDay >> $logFile
find $bakDir -ctime +$keepDay -exec rm -rf {} \;

把上面脚本配置到文件“/etc/crontab”中可实现定时备份，比如每天凌晨 4 点执行上面脚本：

0 4 * * * /opt/backup/mysqlbak.sh
# 不同系统配置/etc/crontab可能不一样，有的需要在执行命令前加上用户名，如：
# 0 4 * * * root /opt/backup/mysqlbak.sh

重启 crond 使配置生效：

$ sudo systemctl restart crond.service

下面命令将授权用户 user1 对数据库 db1 进行任何操作：

mysql> GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'%';

查看数据库（假设名为 db1）中每个表所占用的大小：

mysql> SELECT
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.tables
WHERE table_schema = 'db1';
+--------+------------+
| Table  | Size in MB |
+--------+------------+
| order  |      32.11 |
| user   |       0.06 |
+--------+------------+
2 rows in set (0.00 sec)

查看数据库（假设名为 db1）中某个表（假设名为 user）所占用的大小：

mysql> SELECT
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.tables
WHERE table_schema = 'db1' AND table_name = "user";
+-------+------------+
| Table | Size in MB |
+-------+------------+
| user  |       0.06 |
+-------+------------+
1 row in set (0.01 sec)

执行 SHOW CHARACTER SET; 可以查看 MySQL 支持的字符集，如：

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.01 sec)