通过mysqlbinlog –skip-gtids恢复后再备份可能造成的坑

版本

1
2
3
4
5
6
7
8

[root@uz22199 backup]# innobackupex --version
innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7)
[root@uz22199 backup]# mysql -e"select @@version"
+------------+
| @@version  |
+------------+
| 5.7.18-log |
+------------+

源库

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

表结构与数据
root@mysqldb 21:51:  [fandb]> show create table users\G
*************************** 1. row ***************************
      Table: users
Create Table: CREATE TABLE `users` (
  `email` varchar(10) DEFAULT NULL,
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@mysqldb 18:43:  [fandb]> select* from users;
+-------+
| email |
+-------+
| 1     |
| 10    |
| 20    |
| 30    |
| 5     |
+-------+
插入一条数据
insert into users values(50);  --GTID=1297
再删掉
delete from users where email=50; ----GTID=1298

当前Executed_Gtid_Set
root@mysqldb 18:35:  [fandb]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000005 |      495 |              |                  | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1298 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

源库再次应用一下已经执行过得binlog, 再次应用insert into users values(50); 这一条

这里先不考虑有没有可能这样子去恢复数据,只做实验

在datadir外创建表

要在MySQL datadir外的特定位置创建新的InnoDB file-per-table tablespace,请在create table时指定DATA DIRECTORY = absolute_path_to_directory子句

提前规划好位置,因为无法使用alter语句修改一个表的DATA DIRECTORY属性.

MySQL会在目标目录中创建一个对应于数据库名称的子目录,并在改目录中创建表的.ibd文件用于存储表数据.在MySQL datadir目录下的数据库目录中,MySQL创建一个包含表的路径名称的table_name.isl文件. .isl文件由MySQL处理,像一个符号链接(不过InnoDB表不支持实际的符号链接)

示例:

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

版本
[root@test43100 ~]# cat /etc/redhat-release 
CentOS release 6.4 (Final)
[root@test43100 ~]# mysql -e "\s"
--------------
mysql  Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using  EditLine wrapper


root@mysqldb 11:00:  [(none)]> use fandb
Database changed
root@mysqldb 11:00:  [fandb]> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

root@mysqldb 11:00:  [fandb]> create table t_out(id int auto_increment primary key) data directory='/data/outdir';
Query OK, 0 rows affected (0.37 sec)

查看目标目录
[mysql@test43100 data]$ tree outdir/
outdir/
└── fandb
    └── t_out.ibd

查看datadir
[root@test43100 fandb]# ls -lt
total 412
-rw-r----- 1 mysql mysql    28 Sep  6 11:03 t_out.isl
-rw-r----- 1 mysql mysql  8556 Sep  6 11:03 t_out.frm
包含.isl和.frm文件

您还可以将CREATE TABLE … TABLESPACE与DATA DIRECTORY子句结合使用，以便在MySQL数据目录之外创建一个file-per-table tablespace。 为此，您必须指定innodb_file_per_table作为表空间名称。

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

root@mysqldb 11:03:  [fandb]> create table t_out2(id int auto_increment primary key) TABLESPACE = innodb_file_per_table data directory='/data/outdir';
Query OK, 0 rows affected (0.42 sec)

查看目标目录
[mysql@test43100 data]$ tree outdir/
outdir/
└── fandb
    ├── t_out2.ibd
    └── t_out.ibd

查看datadir
[root@test43100 fandb]# ls -lt
total 428
-rw-r----- 1 mysql mysql    29 Sep  6 11:08 t_out2.isl
-rw-r----- 1 mysql mysql  8556 Sep  6 11:08 t_out2.frm

使用说明

• MySQL最初保持.ibd文件打开，阻止您卸载设备，但如果服务器正忙，最终可能会关闭该表。 当MySQL运行时，请注意不要意外卸载外部设备，或者在设备断开连接时启动MySQL。 当相关的.ibd文件丢失时尝试访问表会导致严重的错误，需要重新启动服务器。
  如果.ibd文件仍然不在预期路径，服务器重新启动可能会失败。 在这种情况下，请手动删除数据库目录中的table_name.isl文件，并在重新启动后执行DROP TABLE以删除.frm文件，并从数据字典中删除有关该表的信息。

• Before tables on an NFS-mounted volume, review potential issues outlined in Using NFS with MySQL.

• If you use an LVM snapshot, file copy, or other file-based mechanism to back up the .ibd file, always use the FLUSH TABLES … FOR EXPORT statement first to make sure all changes that were buffered in memory are flushed to disk before the backup occurs.

• DATA DIRECTORY子句是使用符号链接的一个支持的替代方法.InnoDB直接使用符号链接是不支持的

stream选择使用tar方式压缩到时slave_info信息不完整

版本

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

[root@test1 backup]# innobackupex --version
innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7)
[root@test1 backup]# cat /etc/redhat-release 
CentOS release 6.4 (Final)
[root@test1 backup]# gzip --version
gzip 1.3.12
Copyright (C) 2007 Free Software Foundation, Inc.
Copyright (C) 1993 Jean-loup Gailly.
This is free software.  You may redistribute copies of it under the terms of
the GNU General Public License <http://www.gnu.org/licenses/gpl.html>.
There is NO WARRANTY, to the extent permitted by law.

Written by Jean-loup Gailly.
[root@test1 backup]# mysql -e"\s"
--------------
mysql  Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Connection id:          11
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.18-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /data/mysqldata/3306/mysql.sock
Uptime:                 15 min 52 sec

Threads: 3  Questions: 61  Slow queries: 0  Opens: 116  Flush tables: 5  Open tables: 0  Queries per second avg: 0.064
--------------

1

innobackupex --user=backup --password='backup' --slave-info --stream=tar /tmp | gzip -> /data/mysqldata/backup/xtra_full.tar.gz

1
2
3

[root@test1 slave_info]# more xtrabackup_slave_info 
SET GLOBAL gtid_purged='5c351518-78ec-11e7-8e7a-005056a610c3:1-1164';
CHANGE MASTER TO MASTER_AUTO_POSITION=1;

主库import tablespace只会在binlog中记录alter table xxx import tablespace语句,而不会记录表中的数据的插入语句

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

[mysql@master2 ~]$ mysqlbinlog -vv --base64-output=decode-rows /data/mysqldata/3306/binlog/mysql-bin.000013 --start-position=694
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 694
#170713  7:46:11 server id 23306  end_log_pos 759 CRC32 0xfa604449    GTID    last_committed=2    sequence_number=3
SET @@SESSION.GTID_NEXT= '5691c701-382a-11e5-bbc4-000c293d13e1:19'/*!*/;
# at 759
#170713  7:46:11 server id 23306  end_log_pos 869 CRC32 0x35860c26    Query    thread_id=8    exec_time=0    error_code=0
use `fandb`/*!*/;
SET TIMESTAMP=1499903171/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
alter table dept import tablespace
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

1
2
3

2017-07-10T21:48:13.649264Z 25 [Warning] Slave: InnoDB: ALTER TABLE `fandb`.`dept4` IMPORT TABLESPACE failed with error 44 : 'Tablespace not found' Error_code: 1816
2017-07-10T21:48:13.649293Z 25 [Warning] Slave: Tablespace is missing for table `fandb`.`dept4`. Error_code: 1812
2017-07-10T21:48:13.649321Z 25 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000001' position 1015

Transportable Tablespace Internals

以下信息描述了InnoDB的传输表空间复制过程的内部原理和error log中输出的信息

当在目标端执行ALTER TABLE ... DISCARD TABLESPACE命令时:

• The table is locked in X mode.
• 表空间会与表分离.The tablespace is detached from the table.

当在源端执行FLUSH TABLES ... FOR EXPORT命令时:

• The table being flushed for export is locked in shared mode.
• 清除协调程序线程已停止The purge coordinator thread is stopped.
• 脏块会被写入磁盘
• 表的元数据信息会被写入二进制文件.cfg中

此操作的预期错误日志消息:

1
2
3
4

2013-09-24T13:10:19.903526Z 2 [Note] InnoDB: Sync to disk of '"test"."t"' started.
2013-09-24T13:10:19.903586Z 2 [Note] InnoDB: Stopping purge
2013-09-24T13:10:19.903725Z 2 [Note] InnoDB: Writing table metadata to './test/t.cfg'
2013-09-24T13:10:19.904014Z 2 [Note] InnoDB: Table '"test"."t"' flushed to disk

当在源端执行UNLOCK TABLES命令时:

• 二进制文件.cfg会被删除
• The shared lock on the table or tables being imported is released ,并且清除协调程序线程会重启purge coordinator thread is restarted.

此操作的预期错误日志消息:

1
2

2013-09-24T13:10:21.181104Z 2 [Note] InnoDB: Deleting the meta-data file './test/t.cfg'
2013-09-24T13:10:21.181180Z 2 [Note] InnoDB: Resuming purge

当在目标端执行ALTER TABLE ... IMPORT TABLESPACE时,导入算法会执行如下操作:

• 将检查每个表空间页是否损坏.
• 每个页面上的空间ID和日志序列号（LSN）都会更新
• 标志被验证,LSN被更新为头页.
• Btree页面更新.
• 页面状态设置为dirty,以便它将被写入磁盘.

此操作的预期错误日志消息:

1
2
3
4
5
6

2013-07-18 15:15:01 34960 [Note] InnoDB: Importing tablespace for table 'test/t' that was exported from host 'ubuntu'
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase I - Update all pages
2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk
2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk - done!
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase III - Flush changes to disk
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase IV - Flush complete

注意
您还可能会收到一个警告，表明丢弃了表空间（如果您丢弃了目标表的表空间）和一条消息，指出由于缺少.ibd文件而无法计算统计信息：

1
2
3

2013-07-18 15:14:38 34960 [Warning] InnoDB: Table "test"."t" tablespace is set as discarded.
2013-07-18 15:14:38 7f34d9a37700 InnoDB: cannot calculate statistics for table "test"."t" because the .ibd file is missing. For help, please refer to
http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html

Transportable Tablespace示例

http://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html

例1:将InnoDB表从一个服务器复制到另一个服务器

此过程演示如何将InnoDB表从正在运行的MySQL服务器实例复制到另一个正在运行的实例.经过一些小调整相同过程可用于在同一实例上执行完整表恢复.
1.source端

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

(mysql@localhost) [test]> show table status like 'dept'\G
*************************** 1. row ***************************
           Name: dept
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-08-26 14:25:50
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

(mysql@localhost) [test]> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.01 sec)

2.target端

1
2
3
4
5
6

CREATE TABLE `dept` (
  `deptno` int(11) NOT NULL,
  `dname` varchar(14) DEFAULT NULL,
  `loc` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.在目标端discard tablespace.Before a tablespace can be imported, InnoDB must discard the tablespace that is attached to the receiving table

1

mysql> alter table dept discard tablespace;

迁移File-Per-Table Tablespaces

Transportable Tablespace示例
Transportable Tablespace Internals
本节介绍如何将file-per-table tablespace从一个server迁移到另一个server,也称为可Transportable Tablespaces功能。 在MySQL 5.7.4之前,只支持非分区的InnoDB表。 从MySQL 5.7.4开始,还支持分区的InnoDB表和各个InnoDB表分区和子分区。

There are many reasons why you might copy an InnoDB file-per-table tablespace to a different database server:

• 生成报告而不影响生产库
• 为备库初始化数据
• 用于数据恢复
• 相对于mysqldump,传输表空间有个更快的速度
• 将每个文件的表空间移动到具有更适合系统要求的存储介质的服务器.例如,您可能希望在SSD设备上拥有繁忙的表,或在大容量HDD设备上使用大型表.

限制和使用注意事项

• 仅当innodb_file_per_table设置为ON（这是MySQL 5.6.6的默认设置）时，传输表空间功能才可以使用。 驻留在共享系统表空间中的表不能使用此功能。
• 在传输过程中,只有只读操作可以执行
• page size要相同.When importing a tablespace, the page size must match the page size of the importing instance.
• Prior to MySQL 5.7.4, DISCARD TABLESPACE is not supported for partitioned tables meaning that transportable tablespaces is also unsupported. If you run ALTER TABLE … DISCARD TABLESPACE on a partitioned table, the following error is returned: ERROR 1031 (HY000): Table storage engine for ‘part’ doesn’t have this option. As of MySQL 5.7.4, ALTER TABLE … DISCARD TABLESPACE is supported for partitioned InnoDB tables, and ALTER TABLE … DISCARD PARTITION … TABLESPACE is supported for InnoDB table partitions.
• 当foreign_key_checks设置为1时，对于父 - 子（主键 - 外键）关系的表空间不支持DISCARD TABLESPACE。在放弃父子表的表空间之前，请设置foreign_key_checks = 0。 分区的InnoDB表不支持外键。
• ALTER TABLE … IMPORT TABLESPACE不会对导入的数据实施外键约束。 如果表之间存在外键约束，则应在相同（逻辑）时间点导出所有表。 分区的InnoDB表不支持外键。

• ALTER TABLE … IMPORT TABLESPACE和ALTER TABLE … IMPORT PARTITION … TABLESPACE不需要.cfg元数据文件来导入表空间。 但是，在不使用.cfg文件导入时，不会执行元数据检查，并且将发出类似于以下内容的警告：

  1 2 3

  Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.\ test\t.cfg', will attempt to import without schema verification 1 row in set (0.00 sec)

  没有.cfg文件导入的能力可能更方便，当不需要模式不匹配。 此外，无法使用.cfg文件导入的能力在无法从.ibd文件收集元数据的崩溃恢复方案中很有用。

  The ability to import without a .cfg file may be more convenient when no schema mismatches are expected. Additionally, the ability to import without a .cfg file could be useful in crash recovery scenarios in which metadata cannot be collected from an .ibd file.

• 由于.cfg元数据文件限制，在为分区表导入表空间文件时，不会报告分区类型或分区定义差异的模式不匹配。但会报告列差异。

• 在子分区表上运行ALTER TABLE … DISCARD PARTITION … TABLESPACE和ALTER TABLE … IMPORT PARTITION … TABLESPACE时，允许使用分区表和子分区表名。指定分区名称时，该分区的子分区将包括在操作中。

• 在MySQL 5.6或更高版本中，如果两个服务器都具有GA（通用可用性）状态并且它们的版本在同一系列中，则从另一个服务器导入表空间文件是有效的。否则，该文件必须在导入它的服务器上创建。

• 在复制方案中，在主节点和从节点上必须将innodb_file_per_table设置为ON。

• 在Windows上，InnoDB以小写形式内部存储数据库，表空间和表名。要避免区分大小写操作系统（如Linux和UNIX）上的导入问题，请使用小写名称创建所有数据库，表空间和表。一种方便的方法是在创建数据库，表空间或表之前，在my.cnf或my.ini文件的[mysqld]节中添加以下行：

调整InnoDB系统表空间大小

本文介绍如何增大或缩小InnoDB system tablespace

增大InnoDB system tablespace

最简单的增大InnoDB system tablespace大小的方法是在一开始配置的时候就指定为自动扩展. 为innodb_data_file_path参数中的最后一个数据文件指定autoextend选项. InnoDB在空间不足时以64MB为单位自动增加该文件的大小. 可以通过设置innodb_autoextend_increment系统变量的值（以兆字节为单位）来更改增量大小.

您可以通过添加另一个数据文件来扩展系统表空间：

1.关闭MySQL
2.如果上一个数据文件是使用关键字autoextend定义的,则根据实际增长的大小将其定义更改为使用固定大小. 检查数据文件的大小,将其舍入到1024×1024字节（= 1MB）的最接近的倍数,并在innodb_data_file_path中显式指定舍入后的大小.
3.将新的数据文件添加到innodb_data_file_path的末尾,可以指定该文件为自动扩展. 注意,只能将innodb_data_file_path中的最后一个数据文件指定为自动扩展.
4.启动MySQL

实际例子:

初始只有一个ibdata1,现在我们想增加一个数据文件

1
2

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

假设ibdata1此时已经增长到988M,那么修改配置为

1
2

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

启动MySQL后,ibdata2会被初始化

1
2
3

2017-08-11T10:27:06.014446+08:00 0 [Note] InnoDB: Need to create a new innodb_system data file 'ibdata2'.
2017-08-11T10:27:06.014567+08:00 0 [Note] InnoDB: Setting file './ibdata2' size to 50 MB. Physically writing the file full; Please wait ...
2017-08-11T10:27:06.182464+08:00 0 [Note] InnoDB: File './ibdata2' size is now 50 MB.

缩小InnoDB system tablespace

您不能从系统表空间中删除数据文件. 要减少系统表空间大小,请使用以下过程：

1.使用mysqldump来转储所有的InnoDB表,包括位于MySQL数据库中的InnoDB表.

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

mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
+---------------------------+
| TABLE_NAME                |
+---------------------------+
| engine_cost               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
+---------------------------+

2.关闭MySQL
3.删除所有现有的表空间文件（ .ibd）,包括ibdata和ib_log文件. 不要忘记删除位于MySQL数据库中的表的 .ibd文件.
4.删除InnoDB表的任何.frm文件.
5.配置新的表空间.
6.重启MySQL
7.导入dump文件

Note
如果您的数据库仅使用InnoDB引擎,可能会更容易地转储所有数据库,停止服务器,删除所有数据库和InnoDB日志文件,重新启动服务器以及导入转储文件.

MySQL数据库设计规范

MySQL数据库与Oracle、sqlserver等数据库相比，有其内核上的优势与劣势。我们在使用MySQL数据库的时候需要遵循一定规范，扬长避短。本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规范，从而为公司业务系统稳定、健康地运行提供保障。

数据库设计

以下所有规范会按照【高危】、【强制】、【建议】三个级别进行标注，遵守优先级从高到低。
对于不满足【高危】和【强制】两个级别的设计，DBA会强制打回要求修改。

库名

1.【强制】库的名称必须控制在32个字符以内，相关模块的表名与表名之间尽量提现join的关系，如user表和user_login表。
2.【建议】库的名称格式：业务系统名称子系统名，同一模块使用的表名尽量使用统一前缀。
**3.【强制】一般分库名称命名格式是“库通配名编号”，编号从“0”开始递增，比如“wenda001” **
以时间进行分库的名称格式是“库通配名时间”
4.【强制】创建数据库时必须显式指定字符集，并且字符集只能是utf8或者utf8mb4

创建数据库SQL举例：

1

Create database db1 default character set utf8;

表结构

1.【强制】表和列的名称必须控制在32个字符以内，表名只能使用字母、数字和下划线，一律小写。
2.【建议】表名要求模块名强相关，如师资系统采用”sz”作为前缀，渠道系统采用”qd”作为前缀等。
3.【强制】创建表时必须显式指定字符集为utf8或utf8mb4。
4.【强制】创建表时必须显式指定表存储引擎类型，如无特殊需求，一律为InnoDB。

当需使用除InnoDB以外的存储引擎时,必须通过DBA审核才能在生产环境中使用

因为Innodb表支持事务、行锁、宕机恢复、MVCC等关系型数据库重要特性，为业界使用最多的MySQL存储引擎。而这是其他大多数存储引擎不具备的，因此首推InnoDB

这个案例是JAVA开发说一个存储过程跑的很慢，之后我跑这个过程，然后通过脚本抓出了慢的SQL

表大小

1
2
3

tb_user_channel  --1W
tb_channel_info  --1W
base_data_login_info 19W

就是这条SQL，跑完要7分钟。base_data_login_info本来是@db_link，但是我在本地建了一个同样的表发现还是7分钟左右，所以排除了可能是由于db_link造成问题的可能性

1
2
3
4
5
6

select 
count(distinct a.user_name),count(distinct a.invest_id)
         from base_data_login_info a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform

看一下这个sql返回多少行，结果秒杀，瞬间就出结果了

1
2
3
4
5
6

select 
 count(*)
         from base_data_login_info@agent a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform

1
2
3
4
5
6

select 
count(distinct a.user_name),count( a.invest_id)
         from base_data_login_info a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform

1
2
3
4
5
6

select 
count( a.user_name),count(distinct a.invest_id)
         from base_data_login_info a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform