本笔记是观看慕课网视频高性能可扩展 MySQL 数据库架构设计与优化后记录。

第一章数据库设计规范
第二章数据库字段设计规范
第三章数据库 SQL 开发规范
第四章数据库操作行为规范
第五章设计数据库分区表
第六章 MySQL 存储引擎
第七章 MySQL 执行优化
第八章 MySQL 慢查询日志
第九章 MySQL 数据库备份与恢复
第十章 MySQL 构架拆分
其他：MySQL 表结构实践 sql
其他 2：MySQL 常用命令

第一章数据库设计规范

1. 数据库 - 命名规范

所有数据库对象名称必须使用小写字母并用下划线分割
1. eg：DbName != dbname
2. eg: Table != table != tabLe
所有数据库对象名称禁止使用 MySQL 保留关键字
1. 保留关键字：https://dev.mysql.com/doc/refman/8.0/en/keywords.html
数据库对象的命名要做到见名识义，并且最好不要超过 32 字符
1. 数据库名过长，会增加网络开销
临时库必须以 tmp 为前缀，并以日期为后缀
备份库、备份表必须以 bak 为前缀，并以日期为后缀
所有存储相同数据的列名和列类型必须一致
1. 即表A与表B的某个属性相同，则列名与类型必须一致
2. 如 A.cid与 B.cid，都必须是 cid int unsigned not null
3. 这种属性一般用于关联列上，当保持一致时，对数据库查询效率有一定帮助

2. 数据库 - 基本设计规范

所有表必须使用 Innodb 存储引擎
1. 在 MySQL 5.5 之前，MylSAM 是默认的存储引擎，MylSAM 是表级锁，在高并发场景下，锁表会有很严重的性能问题
2. InnoDB 是 5.6 以后的默认存储引擎
3. InnoDB 支持事务，行级锁，拥有更好的恢复性，高并发场景下性能更好
数据库和表的字符集统一使用 UTF8
1. 统一字符集可以避免由于字符集转换产生的乱码
2. MySQL 中 UTF8 字符集汉字占 3 个字节，ASCII 码占一个字符
所有表和字段都需要添加注释
1. 使用 comment 从句添加表和列的备注
2. 从一开始就进行数据字典的维护
尽量控制单表数据量的大小，建议控制在 500W 以内
1. 500 W 并不是 MySQL 数据库的限制
2. 可以使用历史数据归档，分库分表等手段限制单表的数据量大小
谨慎使用 MySQL 分区表
1. 分区表在物理上表现为多个文件，在逻辑上表现为一个表
2. 谨慎选择分区键，跨分区查询效率可能更低
3. 建议采用物理分表的方式管理大数据
尽量做到冷热数据分离，减小表的宽度
1. 减少磁盘IO，保证热数据的内存缓存命中率
2. 更有效的利用缓存，避免读入无用的冷数据
3. 经常一起使用的列放在一个表中
禁止在表中建立预留字段
1. 预留字段的命名很难做到见名识义
2. 预留字段无法确认存储的数据类型，所以无法选择合适的类型
3. 对预留字段类型的修改，会对表进行锁定
  1. 在 MySQL 中，对表类型修改的成本远远大于增加一个数据类型的成本
禁止在数据库中存储图片，文件等二进制数据
1. blob、text 等类型
禁止在线上做数据库压力测试
禁止从开发环境、测试环境直连生产环境数据库

3. 数据库 - 索引设计规范

限制每张表的索引数量，建议单张表索引不超过 5 个
1. 索引并不是越多越好，过多的索引可能会降低效率
2. 禁止给表中的每一列都建立单独的索引
每个 InnoDB 表必须有一个主键
1. Innodb 是按照主键索引的顺序来组织表的，因此每个表必须有一个主键
2. 如果表中未设置主键，MySQL 会自动选择第一个非空唯一性的属性作为主键；如果没有非空唯一约束的属性，MySQL 会自动生成一个 6 字节的主键，但是效率不高
3. 不使用更新频繁的列作为主键，不使用多列主键
4. 不使用 UUID、MD5、HASH、字符串列作为主键
5. 主键建议选择使用主键自增ID
常见索引建议
1. SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
2. 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
3. 多表 JOIN 的字段
如何选择索引列的顺序
1. 区分度最高的列放在联合索引的最左侧
2. 尽量把字段长度小的列放在联合索引的最左侧
3. 使用最频繁的列放在联合索引的最左侧
避免建议冗余索引和重复索引
1. eg: primary key(id) 、index(id)、unique index(id)
2. eg: index(a,b,c)，index(a,b)，index(a)
  1. 对于冗余索引，MySQL 执行优化器会对查询到的索引进行选择，降低了索引使用的效率
对于频繁的查询优先考虑使用覆盖索引
1. 避免 Innodb 表进行索引的二次查找
2. 可以把随机 IO 变为顺序 IO，提高查询效率
尽量避免使用外键
1. 不建议使用外键约束，但一定在表与表之间的关联键上建立索引
2. 外键可用于保证数据的参照完整性，但建议在业务端实现
3. 外键会影响父表和子表的写操作从而降低性能
  1. 存在外键约束的表在进行写操作时，会检查外键约束的完整性，降低写操作的性能

第二章数据库字段设计规范

字段类型的选择，会直接影响数据库的性能。

优先选择符合存储需要的最小的数据类型
1. 字段的长度越长，导致索引的长度也会越长
2. 将字符串转换为数字类型存储
  1. INET_ATON(‘255.255.255.255’) = 4294967295
  2. INET_NTOA(4294967295) = ‘255.255.255.255’
3. 对于非负整数的数据，优先使用无符号整型存储
  1. 无符号比有符号的数据多出一倍的存储空间
  2. eg：SIGNED INT -2147483648 ~ 2147483647 ; UNSIGNED INT = 4294967295
4. VARCHAR(N) 中的N代表的是字符数，而不是字节数
5. 使用 UTF8 存储汉字 Varchar(255) = 765个字节
6. 过大的长度会消耗更多的内存
避免使用 TEXT、BLOB 数据类型
1. 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中，且不要使用 SELECT * 这样的查询
2. TEXT 或 BLOB 类型只能使用前缀索引
避免使用 ENUM 数据类型
1. 修改 ENUM 值需要使用 ALTER 语句
2. ENUM类型的 ORDER BY 操作效率低，需要额外操作
  1. ENUM 在进行 ORDER BY 操作时，会将枚举转换为整型进行排序，排序效率低
3. 禁止使用数值作为 ENUM 数据类型
尽可能把所有列定义为 NOT NULL
1. 索引 NULL 列需要额外的空间来保存
  1. 为 NULL 的列索引，会多出一部分存储空间，用来标识该列是否为空
  2. 索引空间占用越少越好
2. 进行比较和计算时对 NULL 值做特别的处理
不建议使用，字符串存储日期型的数据
1. 缺点一：无法使用日期函数进行计算和比较
2. 缺点二：用字符串存储日期需要占用更多的空间
  1. 使用字符串存储需要占用 16 个字节
  2. 而使用日期类型只需要 8 个字节
使用 TIMESTAMP 或 DATETIME 类型存储时间
1. TIMESTAMP 1970-01-01 ~ 2038-01-19
2. TIMESTAMP 占用 4 字节，底层以 INT 存储，但是可读性比 INT 高
同财务相关的金额类数据，必须使用 decimal 类型
1. Decimal 类型为精准浮点数，在计算时不会丢失精度
2. 占用空间由定义的宽度决定
3. 可用于存储比 bigint 更大的整型数据

第三章数据库SQL开发规范

建议使用预编译语句进行数据库操作
1. 预编译语句可以重复使用执行计划
2. 可以有效避免动态 SQL 带来的 SQL 注入问题
3. 只传参数，比传递 SQL 语句更高效
4. 相同语句可以一次解析，多次使用，提高处理效率
避免数据类型的隐式转换
1. 隐式转换会导致索引失效
2. eg: select name,phone from customer where id = ‘111’
充分利用表上已经存在的索引
1. 避免使用双% 的查询条件。如 a like ‘%123%’
2. 一个 SQL 只能利用到复合索引中的一列进行范围查询
3. 使用 left join 或 not exists 来优化 not in 操作
  1. not in 会使索引失效
程序连接不同的数据库使用不同的账号，禁止夸库查询
1. 为数据库迁移和分库分表留出余地
2. 降低业务耦合度
3. 避免权限过大而产生的安全风险
禁止使用 SELECT * ，必须使用 SELECT <字段列表> 查询
1. 消耗更多的CPU和IO，以及网络带宽资源
2. 无法使用覆盖索引
3. 可减少表结构变更带来的影响
禁止使用不含字段列表的 INSERT 语句
1. eg：insert into tb_xx values(a,b,c) 错误
2. eg：insert into t(c1,c2,c3) values(a,b,c) 正确
3. 避免因为字段变更，导致的插入失败
避免使用子查询，可以把子查询优化为 join 操作
1. 子查询的结果集无法使用索引
2. 子查询会产生临时表操作，如果子查询数据量大则严重影响效率
3. 消耗过多的 CPU 及 IO 操作
4. 包含 GROUP BY、ORDER BY 等操作的子查询，无法转换为 JOIN 操作
避免使用 JOIN 关联太多的表
1. 每 JOIN 一个表会多占用一部分内存
2. 会产生临时表操作，影响查询操作
3. MySQl 最多允许关联 61 个表，建议不超过 5 个
减少同数据库的交互次数
1. 数据库更适合处理批量操作
2. 合并多个相同的操作到一起，可以提高处理效率
3. eg：alter table t1 add column c1 int, change column c2 c2 int
使用 in 代替 or
1. in 的值不要超过 500个
2. in 操作可以有效的利用索引
禁止使用 order by rand() 进行随机排序
1. 会把表中所有符合条件的数据装载到内存中进行排序
2. 会消耗大量的 CPU 和 IO 及内存资源
3. 推荐在程序中获取一个随机值，然后从数据库中获取数据的方式
where 从句中禁止对列进行函数转换和计算
1. 对列进行函数转换或计算会导致无法使用索引
2. eg：where date(createtime) = ‘20160901’
  1. 这样的话，无法使用到 createtime 列上的索引
  2. 可以修改为：where createtime >= ‘20160901’ and createtime < ‘20160902’
在明显不会有重复值时使用 UNION ALL 而不是 UNION
1. UNION 会把所有数据放到临时表中后再进行去重操作
2. UNION ALL 不会再对结果集进行去重操作
拆分复杂的大 SQL 为多个小 SQL
1. MySQL 一个 SQL 只能使用一个 CPU 进行计算
2. SQL 拆分后可以通过并行执行来提高处理效率
  1. 拆分 SQL 之后，多个小 SQL 能够更好的利用 CPU 资源
3. 需要拆分的 SQL 为书写复杂，且执行时间长的。避免因 SQL 执行过长导致处理效率缓慢。

第四章数据库操作行为规范

超过 100W 行的批量写操作，要分批多次进行操作
1. 大批量的操作可能会造成严重的主从延迟
2. binlog日志为 row 格式时会产生大量日志
3. 避免产生大事务操作
  1. 大的事务会导致数据库的阻塞时间过长
对于大表使用 pt-online-schema-change 修改表结构
1. 避免大表修改产生的主从延迟
2. 避免对表修改时，产生的表锁问题
3. pt-online-schema-change 修改表结构时的流程
  1. 建立一个和源表结构相同的新表
  2. 在新表上进行表结构修改
  3. 创建触发器，将源表的数据操作复制到新表中
  4. 表复制完成后，将新表重命名为源表名，并删除源表
  5. 以此来完成表结构的修改，而不产生对源表的加锁操作
禁止为程序使用的账号赋予 super 权限
1. 当数据库连接达到最大限制时，还允许 1 个super权限的用户连接
  1. 如果滥用 super 权限的话，会导致 DBA 无法登陆数据库检查问题
2. super 权限只能留给 DBA 处理问题的账号使用
对于程序连接数据库账号，遵循权限注销原则
1. 程序使用数据库账号只能在一个 DB 下使用，不准跨库
2. 程序使用的账号原则上不准有 drop 权限

向DB 中插入 100W 条数据

如果每次只插入一条数据，则会增加和数据库的交互次数
如果批量插入，虽然只有一次交互，但是会产生非常大的事务，可能造成内存溢出

分段批量插入

JDBC 连接 URL 需要加上 allowMultiQueries=true 参数
example by Mybatis Batch:

```java
// Java 代码
pst.addBatch();
if(i > 0 && i%1000 == 0){

pst.executeBatch();
pst.clearBatch();

}


4. ```xml
   <!-- MyBatis SQL -->
   <insert id="insertbatch" parameterType="java.util.List">
   	<selectKey keyProperty="fetchTime" order="BEFORE" resultType="java.lang.String">
       	insert into tb_user (username,password)
           values
           <foreach collection="list" item="item" index="index" separator=",">
           	(
               	#{item.username},#{item.password}
               )
           </foreach>
       </selectKey>
   </insert>

第五章设计数据库分区表

一、数据库设计三范式

第一范式：数据库表中的字段都是单一属性，不可再分。
第二范式：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖。
第三范式：数据表中不能存在非关键字段对任一候选关键字段的传递函数依赖。

翻译过来即：

第一范式：表必须有主键，且每个字段原子性不可再分。
第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
第三范式：所有非主键字段和主键字段之间不能产生传递依赖

二、MySQL 分区表

1. 检查 MySQL 是否支持分区功能

1
2
-- 查看MySQL 中的所有插件，如果存在 Name=partition；Status=ACTIVE，则表示支持分区功能
SHOW PLUGINS;
分区表的特点：在逻辑上为一个表，在物理上存储在多个文件中。

2. 分区表的使用方法

注：PARTITION BY 必须是主键HASH之后，而不能是其它属性。

在插入和查询时，与正常使用一致。

3. 分区的几种方式

1. HASH 分区

根据 MOD(分区键、分区数) 的值把数据行存储到表的不同分区中
数据可以平均的分布在各个分区中
HASH 分区的键值必须是一个 INT 类型的值，或是通过函数可以转为 INT 类型
1. 如果属性为整型，则可以 eg: PARTITION BY HASH(customer_id)
2. 如果属性为日期，则转换为整型后进行HASH：eg: PARTITION BY HASH(UNIX_TIMESTAMP(login_time))

eg: 对用户登录日志表进行分区，分区规则为 customer_id 的 hash 值，并分为4个区

CREATE TABLE customer_login_log(
 customer_id INT UNSIGNED NOT NULL COMMENT '登录用户ID',
 login_time TIMESTAMP NOT NULL COMMENT '用户登录时间',
 login_ip INT UNSIGNED NOT NULL COMMENT '登录IP',
 login_type TINYINT NOT NULL COMMENT '登录类型：0 未成功，1 成功'
) engine = innodb DEFAULT CHARSET=utf8 COMMENT '用户登录日志表'
PARTITION BY HASH(customer_id)
PARTITIONS 4;

创建 HASH 分区表时可以使用到的函数

ABS()	CEILING()	DAY()
DAYOFMONTH()	DAYOFWEEK()	DAYOFYEAR()
DATEDIFF()	EXTRACT()	FLOOR()
HOUR()	MICROSECOND()	MINUTE()
MOD()	MONTH()	QUARTER()
SECOND()	TIME_TO_SEC()	TO_DAYS()
TO_SECONDS()	UNIX_TIMESTAMP()	WEEKDAY()
YEAR()		YEARWEEK()

2. RANGE 分区

按照范围分区，特点：

根据分区键值的范围把数据行存储到表的不同分区中
多个分区的范围要连续，但是不能重叠
默认情况下使用 VALUES LESS THAN 属性，即每个分区不包含指定的那个值

eg: 对用户id 进行分区，p0分区为 0~~9999，依次类推，p3分区为 3000~~max

CREATE TABLE customer_login_log(
 customer_id INT UNSIGNED NOT NULL COMMENT '登录用户ID',
 login_time TIMESTAMP NOT NULL COMMENT '用户登录时间',
 login_ip INT UNSIGNED NOT NULL COMMENT '登录IP',
 login_type TINYINT NOT NULL COMMENT '登录类型：0 未成功，1 成功'
) engine = innodb DEFAULT CHARSET=utf8 COMMENT '用户登录日志表'
PARTITION BY RANGE (customer_id) (
	PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p1 VALUES LESS THAN (20000),
    PARTITION p2 VALUES LESS THAN (30000),
    PARTITION p3 VALUES LESS THAN MAXVALUE
)

RANGE 分区的适用场景：

分区键为日期或是时间类型
1. 时间归档
所有查询中都包括分区键
定期按分区范围清理数据

3. List 分区

List 分区的特点：

按分区键取值的列表进行分区
同范围分区一样，各分区的列表值不能重复
每一行数据必须能找到对应的分区列表，否则数据插入失败

如何建立 LIST 分区：

CREATE TABLE customer_login_log(
 customer_id INT UNSIGNED NOT NULL COMMENT '登录用户ID',
 login_time TIMESTAMP NOT NULL COMMENT '用户登录时间',
 login_ip INT UNSIGNED NOT NULL COMMENT '登录IP',
 login_type TINYINT NOT NULL COMMENT '登录类型：0 未成功，1 成功'
) engine = innodb DEFAULT CHARSET=utf8 COMMENT '用户登录日志表'
PARTITION BY LIST (login_type) (
  PARTITION p0 VALUES IN (1,3,5,7),
  PARTITION p1 VALUES IN (2,4,6,8)
)

4. 分区表的常见操作

查询分区信息表
1. ```mysql
SELECT
table_name,
partition_name,
partition_description,
table_rows
FROM
information_schema.PARTITIONS
WHERE table_name = ‘customer_login_log’;
1
2
3
4
5

2. 新增分区

1. ```mysql
ALTER TABLE customer_login_log ADD PARTITION (PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01 00:00:00')))

删除分区及其中的数据

```mysql
ALTER TABLE customer_login_log DROP PARTITION p0;


4. 分区数据归档迁移：

   1. 条件：

      1. MySQL >= 5.7
      2. 结构相同
      3. 归档到的数据表一定要是非分区表
      4. 非临时表；不能有外键约束
      5. 归档引擎要是：archive
         1. 无法从该表中删除数据，仅能添加数据
         2. 占用的空间更小

   2. 创建数据归档表，用于存储用户登录日志的归档数据

      1. ```mysql
         CREATE TABLE arch_customer_login_log(
          customer_id INT UNSIGNED NOT NULL COMMENT '登录用户ID',
          login_time TIMESTAMP NOT NULL COMMENT '用户登录时间',
          login_ip INT UNSIGNED NOT NULL COMMENT '登录IP',
          login_type TINYINT NOT NULL COMMENT '登录类型：0 未成功，1 成功'
         ) ENGINE = ARCHIVE COMMENT '用户登录日志表归档'

将 p1 分区(2018年用户的登录日志) 迁移到归档表中

```mysql
ALTER TABLE customer_login_log
EXCHANGE PARTITION p1
WITH TABLE arch_customer_login_log;


4. 此时再查看 customer_login_log，已不存在 p1 分区中的数据了，全部转移到了 arch_customer_login_log 中。

5. 迁移完成，删除 customer_login_log 中的 p1 分区

   1. ```mysql
      ALTER TABLE customer_login_log DROP PARTITION p1;

第六章 MySQL 中的存储引擎

一、查询 MySQL 的存储引擎

SHOW ENGINES;

第七章 MySQL 执行计划优化

通过 EXPLAIN 对 SQL 语句的执行计划进行分析，以此来判断一条 SQL 的执行效率，并作出对应的优化调整。

EXPLAIN 能够分析 select update insert replace delete 这些SQL。

一、执行计划分析

SQL 如何使用索引
1. 并不是某一列上存在索引，sql 在执行时就必定会使用该索引。
联接查询的执行顺序
查询扫描的数据行数

二、执行计划中的内容

以下面的查询SQL 为例，解释执行计划中输出内容的含义

EXPLAIN
SELECT customer_id,title,content 
FROM `product_comment`
WHERE audit_status = 1
AND product_id = 199726
LIMIT 0,5

id: 1
- ID 列中的数据为一组数字，表示执行 SELECT 语句的顺序
- ID 值相同时，执行顺序由上至下
- ID 值越大优先级越高，执行越靠前
select_type: SIMPLE
- SIMPLE：不包含子查询或是 UNION 操作的查询
- PRIMARY：查询中如果包含任何子查询，那么最外层的查询则被标记为 PRIMARY
- SUBQUERY：SELECT 列表中的子查询
- DEPENDENT SUBQUERY：依赖外部结果的子查询
- UNION：Union操作的第二个或是之后的查询的值为 union
- DEPENDENT UNION：当 UNION 作为子查询时，第二或是第二个后的查询的 select_type 值
- UNION RESULT：UNION 产生的结果集
- DERIVED：出现在 FROM 子句中的子查询
table: product_comment
- 输出数据行所在的表的名称
- <unionM,N>由ID 为 M，N 查询union 产生的结果集
- / 由 ID 为N 的查询结果的结果集
partition: NULL
- 对于分区表，显示查询的分区ID
- 对于非分区表，显示为 NULL
type: ALL
- 以下类型的性能从高到低依次排列
- system：这是 const 联接类型的一个特例，当查询的表只有一行时使用
- const：表中有且只有一个匹配的行时使用，如对主键或是唯一索引的查询，这是效率最高的联接方式
- eq_ref：唯一索引或是主键索引查找，对于每个索引键，表中只有一条记录与之匹配
- ref：非唯一索引查找，返回匹配某个单独值的所有行
- ref_or_null：类似于 ref 类型的查询，但是附加了对 NULL 值列的查询
- index_merge：该联接类型表示使用了索引合并优化方法
- range：索引范围扫描，常见于 between、>、< 这样的查询条件
- index：FULL index Scan 全索引扫描，同ALL 的区别是，遍历的是索引树
- ALL：FULL Table Scan 全表扫描，这是效率最差的联接方式
possible_keys: NULL
- 指出 MySQL 能使用哪些索引来优化查询，基于查询所使用到的列和过滤条件来判断的
- 查询列所涉及到的列上的索引都会被列出，但不一定会被使用
key: NULL
- 查询优化器优化查询实际所使用的索引
- 如果没有可用的索引，则显示为 NULL
- 如果查询使用了覆盖索引，则该索引仅出现在 Key 列中
key_len: NULL
- 表示索引字段的最大可能长度
- ken_len的长度由字段定义计算而来，并非数据的实际长度
ref: NULL
- 表示那些列或常量被用于查找索引列上的值
rows: 9400
- 表示 MySQL 通过索引统计信息，估算的所需读取的行数
- Rows 值的大小是个统计抽样结果，并不十分准确
filtered: 1.00
- 表示返回结果的行数占需读取行数的百分比
- Filtered 列的值越大越好，越大表示过滤的比例越高，最大100%
- Filtered 列的值依赖统计信息，并不十分准确
Extra: Using where
- Distinct：优化 distinct 操作，在找到第一匹配的元素后即停止找同样值的动作
- Not exists：使用 not exists 来优化操作
- Using filesort：使用额外操作进行排序，通常会出现在 order by 或 group by 查询中
- Using index：使用了覆盖索引进行查询
- Using teporary：MySQL 需要使用临时表来处理查询，常见于排序、子查询和分组查询
- Using where：需要在 MySQL 服务器层使用 WHERE 条件来过滤数据
- select tables optimized away：直接通过索引来获取数据，不用访问表

通过上面的查询语句，以及索引的设计规范，应该在 where 语句查询的部分设置索引，这里使用联合索引。

-- 查询表中，audit_status和product_id 的区分度，应该将区分度高的列放在联合索引的左侧
SELECT COUNT(DISTINCT audit_status)/COUNT(*) AS audit_rate,
COUNT(DISTINCT product_id)/COUNT(*) AS product_rate
FROM product_comment;

假设 product_rate = 0.8 ，audit_rate = 0.2，则联合索引应该设计为：

1 2	CREATE INDEX idx_productID_auditStatus ON product_comment(product_id,audit_status);

执行计划的限制

无法展示存储过程、触发器、UDF 对查询的影响
无法使用 EXPLAIN 对存储过程进行分析
早期版本的MySQL 只支持对 SELECT 语句进行分析

第八章 MySQL 慢查询日志

在业务中，无法保证对程序中的每一条SQL 都进行优化，那么就需要对某一些指定条件的 sql 进行优化。

一、捕获有问题的SQL

启用 mysql 慢查询日志

set global show_query_log_file = /sql_log/slow_log.log
1. 慢查询日志的log文件位置
set global log_queires_not_using_indexes = on;
1. 是否对未使用索引的sql进行抓取
set global long_query_time = 0.001;
1. 抓取执行超过多少时间的sql，与2不冲突
set global low_query_log = on;
1. 开启慢查询日志

如何分析慢查询日志

如果存在很多未使用索引的sql，或者存在很多慢执行的sql，那么会在短时间内产生大量的日志记录。

此时就需要 mysql 提供的 mysqldumpslow 来进行分析：

1	mysqldumpslow slow-mysql.log

mysqldupslow 会把相同的sql 汇总在一起，方便分析慢查询日志中的执行计划，有目的的进行优化。

第九章数据库备份与恢复

一、数据库备份

1、数据库备份的划分

按照备份的结果分为：逻辑备份和物理备份
1. 逻辑备份的结果为SQL 语句，适用于所有存储引擎
2. 物理备份是对数据库目录的拷贝，对于内存表只备份结构
按照备份的数据分为：全量备份和增量备份
1. 全量备份是对整个数据库的一个完整备份
2. 增量备份是在上次全量或增量备份的基础上，对于更改数据进行的备份

2、使用 mysqldump 进行备份

常用语法：

备份单数据库的表：mysqldump [OPTIONS] database [tables]

备份多个数据库：mysqldup [OPTIONS] –databases [OPTIONS] DB1[DB2…]

备份全部的数据库：mysqldump [OPTIONS] –all-databases [OPTIONS]

常用参数：

-u, –user=name

-p, –password[=name]

–single-transaction：在备份前开启一个事务，不能有 DDL 操作正在执行，常用于 InnoDB 存储引擎

-l, –lock-tables：备份时，只能对数据库进行读操作，对当前备份的表进行锁定，因此并不能保证备份时所有表的全局一致性，只能保证单表的一致性，常用于 MySAM 存储引擎

-x，–lock-all-tables：对所有的表同时锁定，可以解决-l 参数的问题

–master-data=[1/2]：默认值为1，只记录 change-master 语句；当指定了 –master-data 参数，但是没有指定 –single-transaction 时，默认使用 –lock-all-tables

-R，–routines：备份数据库中的存储过程

–triggers：备份数据库中的触发器

-E，–events：备份数据库中的调度事件

–hex-blob：对数据库中的二进制等类型转换为十六进制来保存，可以解决某些文本不可见的问题

–tab=path

-w，–where=’过滤条件’：条件导出，但是只能用在单表导出

备份数据库的账号需要具有以下权限：SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, PROCESS

3、备份实例

1. 创建一个用于备份数据库的用户

进入 MySQL 实例中，并执行语句，创建用户：

1	create user 'backup'@'localhost' identified by '123456';

2. 为用户授予备份权限

1	grant SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, PROCESS on . to 'backup'@'localhost';

3. 使用事务备份数据库

情况一：备份 mc_order 数据库中的全部表数据：

1	mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events mc_order > mc_order.sql

情况二：备份 mc-_order 数据库下的 order_master 表：

1	mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events mc_order order_master > order_master.sql

情况三：备份全部的数据库，将所有的数据库都保存在 mc.sql 下：

1	mysqldump -ubackup -p --master-data=1 --single-transaction --routines --triggers --events --all-databases > mc.sql

情况四：备份 mc_order 数据库下的每个表，至指定的目录文件下，即 mc_order 下的每张表都是区分开的：

注意，使用 –tab 命令需要用户具有 file 写文件的权限：

1	grant file to 'backup'@'localhost';

1	mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/data/db_backup/mc_order" mc_order;

情况五：备份 mc_oder 下的 order_master 表中 id为1000-1050 的数据：

1	mysqldump -ubackup -p --master-data=2 --single-transaction --routines --where "order_id>1000 and order_id<1050" mc_order order_master > order_master_1000.sql

四、备份脚本

#!/bin/bash
##########Basic paramters ######
DAY=`date +%Y%m%d`
Environment=$(/sbin/ifconifg | grep "inet addr" | head -1 |grep -v "127.0.0.1" | awk '{print $2;}' | awk -F':' '{print $2;}')
USER="backup"
PASSWD="123456"
HostPort="3306"
MYSQLBASE="/home/sql"
DATADIR="/data/db_backup/${DAY}"
MYSQL=`/usr/bin/whick mysql`
mkdir -p ${DATADIR}

Dump(){
 ${MYSQLDUMP} --master-data=2 --single-transaction --routines --triggers --events -u${USER} -p${PASSWD} -P${HostPort} ${database} > ${DATADIR}/${Environment}-${database}.sql
 cd ${DATADIR}
 gzip ${Environment}-${database}.sql
}

for db in `echo "SELECT schema_name FROM information_schema.schemata where schema_name not in ('information_schema','sys','performance_schema')" | ${MYSQL} -u${USER} -p${PASSWD} --skip-column-names`
do
  database=${db}
  Dump
done

二、数据库恢复

1. 使用 mysqldump 恢复语句

数据的恢复是单线程进行的，因此数据量越大，执行效率可能越慢

2. 如何进行指定时间点的恢复

先决条件
1. 具有指定时间点前的一个全被
2. 具有自上次全备后到指定时间点的所有二进制日志

第一章 数据库设计规范