MySQL性能管理及架构设计（学习笔记）

第1章 实例和故事

1.3 什么影响数据库的性能

1.3.1 超高的QPS和TPS

QPS ：Queries Per Second意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。

TPS：是 TransactionsPerSecond的缩写，也就是事务数/秒。它是软件测试结果的测量单位。一个事务是指一个客户机向服务器发送请求然后服务器做出反应的过程。

数据库的性能问题80%都是由于sql引起的。

1.3.2 大量的并发和超高的CPU使用率

大量的并发：
数据库连接被占满。（max_connections默认100）

超高的CPU使用率：
因CPU资源耗尽而出现宕机

1.3.3 磁盘IO

风险：磁盘IO性能突然下降（使用更快的磁盘设备）
其他大量消耗磁盘性能的计划任务（调整计划任务，做好磁盘维护）

1.3.4 网卡流量

• 风险

   网卡IO被占满（1000Mb/8=100MB）
  

• 如何避免无法连接数据库的情况：

    减少从服务器的数量
    进行分级缓存
    避免“select *” 进行查询
    分离业务网络和服务器网络
  

1.4 大表带来的问题

1.4.1 什么样的表可以称之为大表

记录行数巨大，单表超过千万行
表数据文件巨大，表数据文件超过10G

1.4.2 大表对查询的影响

慢查询：很难在一定的时间内过滤出所需要的数据

1.4.3 大表对DDL操作的影响

建立索引需要很长的时间

• 风险：
  MySQL版本<5.5建立索引会锁表
  MySQL版本 >=5.5，虽然不会锁表，但是会引起主从延迟

修改表结构需要长时间锁表

• 风险
  会造成长时间的主从延迟
  影响正常的数据库操作

1.4.4 如何处理数据库中的大表

分库分表把一张大表分为多个小表
难点：
分表主键的选择
分表后跨分区数据的查询和统计

大表的历史数据归档：减少对前后端业务的影响。
难点：
归档时间点的选择
如何进行归档操作

1.4.5 大事务带来的问题

• 什么是事务？

1. 事务是数据库系统区别于其他一切文件系统的重要特征之一
2. 事务是一组具有原子性的SQL语句，或是一个独立的工作单元

事务的特性：
1.原子性
一个事务必须被视为一个不可分割的最小工作单元，
整个事务找那个的所有操作要么全部提交成功，要么全部失败，
对于一个事务来说，不可能只执行其中的一部分操作

比如：银行转账事务

2.一致性
一致性是指事务将数据库从一种一致性的状态转换到另一种一致性状态
，在事务开始之前和事务结束后数据库中数据的完整性没有破坏。

比如：银行转账前后，总金额一样

3.隔离性
要求一个事务对数据库中数据的修改，在未提交完成前对于其他事务是不可见的。
SQL标准中定义的四种隔离级别：
未提交读（READ UNCOMMITED）：未提交的事务，对于其他事务是可见的，会出现脏的，不可重复读，幻读。不建议使用
已提交读（READ COMMITED）：（oralce,sql server大不多数据库默认隔离级别）一个事务在未提交前对其他事务是不可见的。

可重复读（REPEATABLE READ）–默认，保证在同一个事务中，多次读取同样记录的结果是一致的。
可串行化（SERIALIZABLE ）：最高的隔离级别，对每一行读取的数据都加锁。

查看当前会话隔离级别

select @@tx_isolation;;

查看系统当前隔离级别

select @@global.tx_isolation;

设置当前会话隔离级别

set session transaction isolatin level repeatable read;

设置系统当前隔离级别

set global transaction isolation level repeatable read;

命令行，开始事务时

set autocommit=off 或者 start transaction

4.持久性
一旦事务提交，则起所做的修改就会永远保存到数据库中。
此时即使系统崩溃，已经提交的修改数据也不会丢失

• 什么是大事务？
  定义：运行时间比较长，操作的数据比较多的事务
  风险：
  锁定太多的数据，造成大量的阻塞和锁超时
  回滚时所需时间比较长
  执行时间长，容易造成主从延迟

• 解决：
  避免一次处理太多的数据
  一处不必要的在事务中的select操作

第2章什么影响了MySQL性能

2.1 影响数据库的几个方面

• 服务器硬件：CPU,磁盘IO，内存
• 服务器系统：数据库服务器的操作系统
• MySQL数据库存储引擎
• 数据库参数配置
• 数据库表结构的设计和SQL语句编写和优化 --（慢查询）

2.2 CPU资源 和 内存大小

2.2.1 如何选择CPU?

选择更多还是更快的CPU（数量&频率）？

从下面几个方面思考：
- 我们的应用是否是CPU密集型？
是的话，选择更好的CPU
不支持多CPU对同一SQL并发处理

- 我们的系统的并发量如何？
衡量数据库处理能力的指标
QPS:同时处理SQL的数量
Web应用选择数量

- 选择32位CPU还是64位的CPU？
选择64位的CPU

2.2.2 如何选择内存

内存大小直接影响数据库的性能。

内存虽然是越多越好，但是对性能的影响确实有限的，不能指望通过增加内存，无限的增加系统的性能。

数据库能使用的内存是有限的，当缓存的数据和磁盘存储的数据一样时（所有数据缓存到内存），再增加内存大小，对数据库性能的影响就没多大意义了。

缓存对读取和写入都有益处。

在缓存池中，将多次操作，合并成一次操作。

- 内存的选择

• 主板支持的最大内存频率
  内存的主频和CPU的主频是类似的，建议选择服务器支持的主频最高的内存，频率越高，内存读取的速度越快。

• 根据数据大小选择内存

• 注意事项
  组成购买升级
  每个通道的内存：相同品牌、颗粒
  频率、电压、校验技术和型号
  单条容量尽可能大

2.3 磁盘的配置和选择

（略）

2.4 使用RAID增加传统机器硬盘的性能

2.4.1 常用的RAID组别–RAID5

RAID 5 又称为分布式奇偶校验磁盘阵列
通过分布式奇偶校验块把数据分散到多个磁盘上，这样如果任何一个盘数据失效，都可以从奇偶校验块中重建。但是如果有两块磁盘失效，则整个卷的数据都无法恢复。

2.4.2 常用的RAID组别–RAID10

RAID 10又称分片的镜像
它是对磁盘先做RAID 1 之后对两组RAID 1的磁盘在做RAID 0，所以对读写都有良好的性能，相对于RAID 5 重建起来更简单，速度也更快。

2.4.3 RAID级别选择

2.5 使用固态存储SSD或PCLe卡

2.5.1 固态磁盘与机械磁盘优缺点

相比机械磁盘固态磁盘有更好的随机读写性能
相比机械磁盘固态磁盘能更好的支持并发
相比机械磁盘固态磁盘更容易损坏

2.5.2 固态硬盘特点

使用SATA接口
可以替换传统磁盘而不需要任何改变
SATA接口的SSD同样支持RAID技术

2.5.3 固态存储的使用场景

使用于存在大量随机I/O的场景
使用与解决单线程负载的I/O瓶颈

2.6 使用网络存储SAN和NAS

2.6.1 SAN和NAS是两种外部文件存储设备加载到服务器上的方法

2.6.2 网络存储适用的场景

采用高性能和高宽带的网络接口设备和交换机
对多个网卡进行绑定，增强可用性和带宽
尽可能的进行网络隔离

• ## 2.7 总结：服务器硬件对性能的影响

• CPU
  64位的CPU一定要工作在CPU位的系统小
  对于并发比较高的场景CPU的数量比频率重要
  对CPU密集型场景和复杂SQL则频率越高越好

• 内存
  选择主板所能使用的最高频率的内存
  内存的大小对性能很重要，所以尽可能的大

• I\O子系统
  PCIe->SSD->RAID 10->磁盘->SAN

2.7 总结：服务器硬件对性能的影响

（略）

2.8 操作系统对性能的影响

（略）

2.9 CentOS系统参数优化

（略）

2.10 文件系统对性能的影响

（略）

2.11 MySQL体系结构

2.12 MySQL常见的引擎——MyISAM

2.12.1 MyISAM 引擎物理存储结构**

.frm:存储MyISAM表的结构信息
.myd: 存储MyISAM表的数据信息
.myi: 存储MyISAM表的索引信息

2.12.2 MyISAM特性

• 并发性与锁级别

• 表损坏修复

  check table tablename;
  

  repair table tablename;
  

• MyISAM表支持的索引类型

• MyISAM表支持数据压缩(对于压缩的表，只能进行读操作，不能进行写操作)
  命令：

  myisampack -b -f myIsam.MYI
  

限制：
使用场景：
非事务型应用
只读类应用
空间类应用

2.13 MySQL常见的引擎——InnoDB

2.13.1 InnoDB引擎物理存储结构**

.frd:存储表结构
.idb:存储表数据

MySQL5.5之后的默认存储引擎。
使用表空间进行数据存储。
innodb_file_per_table参数决定存储在什么样的表空间中。
ON:独立表空间：tablename.idb
OFF:系统共享表空间：ibdataX

2.13.2 系统表空间和独立表空间要如何选择

比较：
系统表空间无法简单的收缩文件大小
独立表空间可以通过optimize table 命令收缩系统文件

系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据

建议：
对Innodb使用独立表空间。

把原来存在于系统表空间中的表转移到独立表空间的方法
步骤：
1、使用mysqldump导出所有数据库表数据。
2、停止mysql服务，修改参数，并删除innodb相关文件
3、重启mysql服务，重建innodb系统表空间
4、重新导入数据

2.14 Innodb存储引擎的特性

Innodb数据字典信息
Undo回滚段

2.14.1 Innodb特性

1. Innodb是一种事务性存储引擎
2. 完全支持事务的ACID特性
3. redo log 和undo log
   redo log :存储已提交的事务，顺序写入
   undo log：存储未提交的事务，随机读入
4. Innodb支持行级锁
5. 行级锁可以最大程度的支持并发
6. 行级锁是有存储引擎层实现的

2.14.2 什么是锁

1. 锁的主要作用是管理共享资源的并发访问。
2. 所用于实现事务的隔离性

2.14.3 锁的类型

1. 共享锁（也称读锁）
2. 独占锁（也称写锁）

锁定资源越少，并发性越高。

2.14.4 锁的粒度

1. 表级锁
   操作表级锁语句：
   lock table tablename write;
   unlock tables;

2. 行级锁
   最大程度的支持表并发的次数，但是锁的开销比表级锁要大，行级锁只存存储引擎中进行实现，在MySQL服务器层没有进行实现）

2.15 Innodb存储引擎的特性2

2.15.1 阻塞和死锁

1. 什么是阻塞？
   阻塞是因为不同锁之间兼容性的关系，在有些时刻，一个事务中的锁需要等待另一个事务中的锁释放占用的资源。

阻塞是为了确保事务的可以并发且正常运行。

但当一个系统中出现了大量的阻塞，以为这系统中一个被频繁更新的表出现了慢查询或被其他的管理操作比如alter table 加上了排它锁。

而过多的阻塞和长时间的阻塞会造成数据库大量的连接堆积，占用系统资源，造成系统性能整体下降。

2. 什么是死锁？
   死锁指两个或两个以上的事务在执行中相互占用了对方等待的资源，而产生的异常。

死锁会被数据库系统自动发现，选择资源占用少的事务进行回滚操作，让其他事务正常运行。

少量死锁不会对系统造成影响，但如果频繁出现大量的死锁，可通过增加资源和索引来解决。

2.15.2 Innodb其他特性

提供独特的性能监控工具

1. Innodb状态检查
   show engine innodb status;

2. 使用场景：
   Innodb适应于大多是OLTP应用

2.16 MySQL常见存储引擎——CSV

2.16.1 CSV引擎文件系统存储特点

数据以文本方式存储在文件中
.csv :文件存储表内容
.csm :文件存储表的元数据入表状态和数据量
.frm : 文件存储表结构信息

2.16.2 CSV引擎特点

以CSV格式进行数据存储
所有列必须都是不能为NULL的
不支持索引（不适合大表，不适合在线处理）
可以对数据文件直接编辑（保存文本文件内容）

2.16.3 CSV引擎适用场景

适合做为数据交换的中间表

2.17 MySQL常见存储引擎——Archive

2.17.1 Archive引擎文件系统存储特点

以zlib对表数据进行压缩，磁盘I/O更少
数据存储在ARZ为后缀的文件中

2.17.2 Archive存储引擎特点

只支持insert和select操作
是允许在自增列上加索引

2.17.3 Archive引擎适用场景

日志和数据采集类应用

2.18 MySQL常见存储引擎——Memory

2.18.1 Memory引擎文件系统存储特点

也称HEAP存储引擎，所以数据保存在内存中

• 功能特点
  支持HASH索引和BTree索引
  所有字段都为固定长度varchar(10)=char(10)
  不支持BLOG和TEXT大字段
  Memory存储引擎使用表级锁
  最大大小由max_heap_table_size参数决定

• 使用场景
  用于查找或者是映射表，例如邮编和地区的对应表
  用于保存数据分析中产生的中间表
  用于缓存周期性聚合数据的结果表

2.19 MySQL常见存储引擎——Federated

2.19.1 Federated引擎文件系统存储特点

• 特点
  提供了访问远程MySQL服务器上表的方法
  本地不存储数据，数据全部放到远程服务器上
  本地需要保存表结构和远程服务器的连接信息

• 如何使用
  默认禁止，启用需要在启动是增加federated参数

• 使用场景
  偶尔的统计分析和手工查询

2.20如何选择正确的存储引擎

• 参考条件
  事务（innodb可进行事务回滚）
  备份(只有innodb有免费热备的方案)
  崩溃恢复
  存储引擎的特有特性（建议不要混合使用存储引擎）

2.21 MySQL服务器参数

2.21.1 MySQL获取配置信息路径

1. 命令行参数

mysqld_safe --datadir = /data/sql_data

2. 配置文件

mysqld --help --verbose|grep -A 1“Default options”
/etc/my.cnf/ etc/mysql/my.cnf /home/mysql/my.cnf

2.21.2 MySQL配置参数的作用域

1. 全局参数
   set global 参数名=参数值；
   set @@global.参数名:=参数值；

2. 会话参数
   set [session] 参数名=参数值；
   set @@session.参数名:=参数值;

2.22 内存配置相关参数

2.22.1 内存配置相关参数

• 确实能够使用的内存上限
• 确定MySQL的每个连接使用的内存
  sort_buffer_size join_buffer_size read_buffer_size read_rnd_buffer_size
• 确定需要为操作系统保留多少内存
• 如何为缓存池分配内存
  innodb_buffer_pool_size
  =总内存-（每个线程所需要的内存*连接数）-系统保留内存（大约是系统内存的75%）
  key_buffer_size

2.23 I/O配置相关参数

2.23.1 Innodb I/O相关配置

• Innodb_log_fle_size:控制单个日志打大小

• Innodb_log_files_in_group:控制个数

• 事务日志总大小=Innodb_log_fle_size*Innodb_log_files_in_group

• Innodb_log_buffer_size：控制日志缓冲区大小

• Innodb_flush_log_at_tex_commit
  参数选择
  0：每秒进行一次log写入cache,并flush log 到磁盘
  1【默认值】：在每次事务提交执行log写入cache，并flush log 到磁盘（最安全的设置，不会丢失已提交的事务，安全性最高，但是性能最差）
  2：每次事务提交，执行log数据写入到cache，美妙执行一次flush log到磁盘

• Innodb_flush_method=O_DIRECT

• Innodb_file_per_table=1(控制innodb如何使用表空间，建议启用 )

• Innodb_doublewrite=1（控制是否使用双写缓存，建议启用，增加数据的安全性）

2.23.2 MyISAM I/O相关配置

delay_key_write
OFF:每次写操作后刷新到缓冲中的脏块到磁盘
ON:只对在键表时指定delay_key_write选项的表使用延迟刷新
ALL:对所有MYISAM表都使用延迟键写入

2.24 安全相关配置参数

• expire_logs_days 指定自动清理binlog的天数
  建议：覆盖两次全备所间隔的天数，至少保存7天左右。

• max_allowed_packet：控制MySQL可以接收的包的大小

• Skip_name_resolve：禁用DNS查找

• sysdata_is_now ：确保sysdate()返回确定性的日期

• read_only：禁止非super权限的用户写权限

• skip_slave_start：禁用Slave自动恢复

• sql_mode ：设置MySQL所使用的SQL模式
  sql_mode常用值如下:

ONLY_FULL_GROUP_BY:
对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中

ERROR_FOR_DIVISION_BY_ZERO:
在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时MySQL返回NULL

NO_AUTO_VALUE_ON_ZERO:
该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。

STRICT_TRANS_TABLES:
在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制

NO_ZERO_IN_DATE:
在严格模式下,不允许日期和月份为零

NO_ZERO_DATE:
设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO:
在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL

 ANSI_QUOTES:
启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符

PIPES_AS_CONCAT:
将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似

NO_AUTO_CREATE_USER:
禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION:
如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常

2.25 其他常用配置参数

• sync_binlog ：控制MySQL如何向磁盘刷新binlog
  默认为0，表示mysql不主动刷新，而是由操作系统自己决定什么时候将cache的数据刷新到磁盘。
  如果设置为1 ，每次事务提交，就主动刷新到磁盘。建议设置为1 。
• tmp_tabe_size和max_heap_table_size：一起使用，用于控制内存临时表大小
• max_connections：控制允许的最大连接数，默认值100，通常设置为2000

2.26 数据库设计对性能的影响

• 过分的反范式化为表建立太多的列
• 过分的范式化造成太多表关联
  Mysql 限制表关联最多只能关联61个表，建议关联表控制在10个表以内
• 在OLTP环境中使用不恰当的分区表
• 使用外键保证数据的完整性

2.27 总结

性能优化顺序

• 数据库结构设计和SQL语句
• 数据库存储引擎的选择和参数配置
• 系统选择及优化
• 硬件升级

第3章MySQL基准测试

3.1 什么是基准测试

基准测试是针对系统设置的一种压力测试。是一种测量和评估软件性能指标的活动，用于建立某个时刻的性能基准，以便当系统发生软硬件变化时重新进行基准测试以评估变化对性能的影响。

**基准测试：**可能不关心业务逻辑，所使用的查询和业务的真实性可以和业务环境没关系。

**压力测试:**需要针对不同主题，所使用的数据和查询也是真实用到的。

3.2 如何进行基准测试

3.2.1 基准测试的目的

• 建立MySQL服务器的性能基准线
  确定当前MySQL服务器运行情况
• 模拟比当前系统更高的负载，已找出系统的扩展瓶颈
  增加数据库开发，观察QPS,TPS变化，确定并发量与性能最优的关系
• 测试不同的硬件，软件和操作系统配置
• 证明新的硬件设备是否配置正确

3.2.2 如何进行基准测试

• 两种常见方法：

1. 对整个系统进行测试
   优点：
   能够测试整个系统的性能，包括web服务器缓存、数据库等
   能反映出系统中各个组件接口间的性能问题，提现真实性能状况
   缺点：
   测试设计复杂，消耗时间长

2. 单独对MySQL进行基准测试
   优点：
   测试设计简单，所需耗费时间端
   缺点：
   无法全面了解整个系统的性能基线

3.2.3 MySQL基准测试的常见指标

• 单位时间内处理的事务数（TPS）
• 单位时间内所处理的查询数（QPS）
• 响应时间
• 并发量：同事处理的查询请求的数量

3.3 基准测试演示实例

3.3.1 基准测试的步骤**

1. 计划和设计基准测试

• 对整个系统还是某一组件

• 使用什么样的数据

• 准备基准测试及数据收集脚本

• CPU使用率、IO、网络流量、状态和计数器信息等。
  系统信息收集脚本：Get_TEST_info.sh
  

• 准备基准测试

• 保存及分析基准测试结果
  分析脚本：analyze.sh
  

3.3.2基准测试中容易忽略的问题

• 使用生产环境数据时只使用了部分数据
  推荐：使用数据库备份来测试
• 在多用户场景中，只做单用户的测试
  推荐：使用多线程并发测试
• 在单服务器上测试分布式应用
  推荐:使用相同架构进行测试
• 反复执行统一查询
  容易缓存命中，无法反映真实查询性能

3.4 MySQL基准测试工具mysqlslap

mysqlslap系统自带的工具

• 常用参数说明

--auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。
--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)。
--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持。
--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。
--create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database。
--commint=N 多少条DML后提交一次。
--compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。
--concurrency=N, -c N 表示并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者--delimiter参数指定的值做为分隔符。例如：--concurrency=100,200,500。
--engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb。
--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次。
--only-print 只打印测试语句而不实际执行。
--detach=N 执行N条语句后断开重连。
--debug-info, -T 打印内存和CPU的相关信息

3.5 MySQL基准测试工具sysbench

3.5.1 sysbench安装说明

常见参数：

第4章 MySQL数据库结构优化

4.1 数据库结构优化介绍

良好的数据库逻辑设计和物理设计是数据库获得高性能的基础
查询语句尽量简单

4.1.1 数据库优化的目的

• 减少数据冗余（尽量减少冗余，而不是完全没有）
• 尽量避免数据维护中出现更新，插入和删除
  插入异常：如果表中的某个实体随着另一个实体而存在
  更新异常：如果更改表中的某个实体的单独属性时，需要对多行进行更新
  删除异常：如果删除表中的某一实体，导致其他实体的消失
• 节约数据库的存储空间
• 提高查询效率

4.1.2 数据库结构设计的步骤

• 需求分析：全面了解产品设计的存储需求
  存储需求
  数据处理需求
  数据的安全性和完整性

• 逻辑设计：设计数据的逻辑存储结构
  数据实体之间的逻辑关系，解决数据冗余和数据维护异常

• 物理设计：根据所使用的数据库特点进行表结构设计
  关系型数据库：Oracle,SQLServer,MySQL,postgressSQL
  非关系型数据库：Mongo,Redis,Hadoop
  存储引擎：Innodb

• 维护优化：根据实际情况对索引、存储结构等进行优化

4.2 数据库结构设计

4.2.1 数据库设计范式

• 数据库设计的第一范式
  数据库表中的所有字段都具有单一属性
  单一属性的列都由基本的数据类型所构成的
  设计出来的表都是简单的二维表

• 数据库设计的第二范式
  在满足第一范式基础上
  第二范式要求满足：
  要求一个表中只具有一个业务主键，也就是说符合第二范式的表中不能 存在非主键列对只对部分主键的依赖关系

• 数据库设计的第三范式
  每一个非主属性既不依赖也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主属性对主键的传递依赖

4.3 需求分析和逻辑设计

(略)

4.4 需求分析和逻辑设计-反范式化设计

4.4.1 什么叫反范式化设计

反范式化是针对范式化而言的，在前面介绍了数据库设计的范式，所谓的反范式化就是为了性能和读取效率的考虑而适当的对数据库设计范式的要求进行违反，而允许存在少量的数据冗余，按句话来说反范式化就是使用空间来换取时间。

4.5 范式化设计和反范式化设计

4.5.1 范式化设计

• 优点：
  可以尽量的减少数据冗余
  范式化的更新操作比反范式化更快
  范式化的表通常比反范式化更小
  - 缺点：
  对于查询需要对多个表进行关联
  更难进行索引优化

4.5.2 反范式化设计

- 优点：
可以减少表的关联
可以更好的进行索引优化
- 缺点：
存在数据冗余及数据维护异常
对数据的修改需要更多的成本

索引没有决定的范式和反范式化的设计，二者适当结合才能设计出高性能的数据库结构

4.6 物理设计介绍

4.6.1 物理设计涉及的内容

定义数据库、表及字段的命名规范
选择合适的存储引擎
为表中的字段选择合适的数据类型
建立数据库结构

4.6.2 定义数据库、表和字段的命名规范

数据库、表和字段的命名要遵循可读性原则
数据库、表和字段的命名要遵循表意性原则
数据库、表和字段的命名要遵循长名原则

4.6.3 选择合适的存储引擎

4.7 物理设计-数据类型的选择

4.7.1 选择合适的数据类型**

当一个列可以选择多种数据类型时，应该选择优先考虑数字类型，其次

如何选择整型

4.7.2 如何选择正确的实数类型

4.7.3 如何选择正确的varchar和char类型

• varchar字符类型
  1. varchar类型存储特点
  varchar用于存储变长字符串，只占用必要的存储空间。列的最大长度小于255则只占用一个额外字节永不记录字符串长度。列的最大长度大于255则要占用两个额外字节用于存储字符串长度。
  2. varchar长度的选择问题
  使用最小的符合需求的长度
  varchar(5)和varcahr(200）存储“mysql”字符串性能不同
  3. varchar的使用场景
  字符串的最大长度比平均长度大很多
  字符串列很少被更新
  使用了多字节字符集存储字符串

• char字符类型
  1. char类型的存储特点
  char类型是定长的
  字符串存储在char类型的列中会删除末尾的空格
  char类型的最大宽度为255
  2.char的使用场景
  char类型适合存储长度近似的值。比如MD5值
  char类型适合存储短字符串
  char类型适合存储经常更新的字符串列

4.8 物理设计-如何存储日期类型

4.8.1 日期数据**

1. DATETIME类型
以YYYY-MM-DD HH:MM:SS[.fraction]格式存储日期时间
datetime = YYYY-MM-DD HH:MM:SS
datetime(6)=YYYY-MM-DD HH:MM:SS.fraction
DATETIME类型与时区无关，占用8个字节的存储空间
2.TIMESTAMP类型
存储了有格林威治时间1970年1月1日到当前时间的描述，以YYYY-MM-DD HH:MM:SS[.fraction]的格式显示，占用4个字节。
时间范围1970-01-01到2038-01-19
TIMESTAMP类型显示依赖于所指定的时区
在行的数据修改时可以自动修改TIMESTAMP列的值。

注意：
在同一个表中，如果有两个TIMESTAMP列，那么只有第一个TIMESTAMP列可以被自动更新。
也可以指定被自动更新的列。

4.8.2 DATE类型

DATE类型的优点：

1. 占用的字节数比使用字符串，datetime,int存储要少，使用date类型只需3个字节。
2. 使用DATE类型还可以利用日期时间函数进行日期之间的计算。
3. DATE类型用于保存1000-01-01到9999-12-31之间的日期

4.8.3 TIME类型

TIME类型用于存储时间数据，格式为HH:MM:SS

存储日期时间数据的注意事项

1. 不要使用字符串类型来存储日期时间数据
   日期时间类型通常比字符串占用的存储空间小
   日期时间类型在进行查找过滤是可以利用日期来进行对比
   日期时间类型还有这丰富的处理函数，可以方便的对日期时间类型进行日期计算
   2.使用int存储日期时间不如使用timestamp类型

4.9 总结

第5章 MySQL高可用架构设计

5.1 mysql复制功能介绍

5.1.1 复制解决了什么问题**

1. 实现在不同服务器上的数据分布
利用二进制日志增量进行
不需要太多的带宽
但是使用基于行的复制在进行大批量的更改时，会对带宽带来一定的压力，特别是跨IDC环境下进行复制，应该分批进行
2. 实现数据读取的负载均衡
需要其他组件配合完成
利用DNS轮询的方式把程序的读连接到不同的备份数据库
使用LVS、haproxy这样的代理方式
3. 增加了数据的安全性
利用备库的备份来减少主库负载
复制并不能代替备份
方便进行数据库高可用架构的部署
避免MySQL单点失败
4. 实现数据库高可用和故障切换
5. 实现数据库在线升级

5.2 mysql二进制日志

5.2.1 MySQL日志分类

1. 服务层日志
二进制日志、慢日志、通用日志

2. 存储引擎层日志
重做日志、回滚日志

5.2.2 二进制日志

记录了所有对MySQL数据库的修改事件，包括增删改查事件和对表结构的修改事件。通过binlog命令行工具进行二进制日志的查看.

1. 查看二进制日志格式的命令

show variables like 'binlog_format';

2.修改二进制日志格式的命令

set session binlog_format=[STATEMENT|ROW|MIXED];

5.2.3 配置二进制日志记录的格式：（3种）

1. 基于段的格式：binlog_format=STATEMENT。
   查看不需要指定特殊参数，可查看mysql执行的操作。示例：mysqlbinlog mysql-bin.001
   优点：
   日志记录量相对较小，节约磁盘及网络IO
   只对一条记录修改或插入，row格式所产生的的日志量小于段格式产生的日志量
   缺点：
   必须记录上下文信息。保证语句在从服务器上执行结果和在主服务器上相 同。
   可能造成MySQL复制的主备服务器数据不一致，从而中断数据服务链路。 特定函数如UUID()和USER()，这样非确定函数还是无法复制。

2. 基于行的格式（默认格式）：binlog_format=RAW 。
   需要加参数查看。示例：mysqlbinlog -vv mysql-bin.001
   ROW格式可以避免MySQL复制中出现的主从不一致问题。范围修 改时，比如同一条SQL修改10000条数据，基于段的日志格式 只会记录这个SQL语句，而基于行的日志格式会有10000条记 录每一行的数据修改。
   优点：
   使MySQL主从复制更加安全
   对每一行数据的修改比基于段的复制高效
   误操作时，根据row格式的二进制日志分析和恢复数据
   缺点：
   记录日志量大
   MySQL5.6之后，可通过参数binlog_row_image适当控制日志记录的方式。 binlog_row_image=[FULL|MINIMAL|NOBLOB] 默认FULL。
   FULL:表示数据修改时，会记录一行数据所有列的内容，无论这些列是否被修改过，都会记录所有列修改的前后值。
   MINIMAL：表示数据修改时，只会记录被修改的列前后的记录。
   NOBLOB：和FULL相像，在full基础上，只要没有对blob字段修改就不会记录blob字段修改的值。

3. 混合日志格式：binlog_format=MIXED。
   特点：
   根据SQL语句由系统决定在基于段和基于行的日志格式中进行选择。
   数量的带下有所执行的SQL语句决定

5.2.4 如何选择二进制日志的格式

建议：
binlog_format =MIXED
binlog_format=ROW

5.3 mysql二进制日志格式对复制的影响

5.3.1 复制的分类

1. 基于SQL语句的复制（SBR）
二进制日志格式使用的是statement格式
2. 基于行的复制（RBR）【推荐使用】
二进制日志格式使用的是基于行的日志格式
3. 混合模糊
根据实际内容在以上两者间切换

5.3.2 影响

1. 基于SQL语句的复制（SBR）
优点：
生成的日志量少，节约网络传输I/O
并不强制要求主从数据库的表定义完全相同
相比于基于行的复制方式更为灵活
缺点：
对于非确定性时间，无法保证主从复制数据的一致性
对于存储过程，触发器，自定义函数惊醒的修改也可能造成数据不一致
相比于基于行的复制方式在从库上执行时需要更多的行锁
2. 基于行的复制（RBR）
优点：
可以应用于任何SQL的复制包括非确定函数，存储过程等
可以减少数据库锁的使用
缺点：
要求主从数据库的表结构相同，否则可能会中断复制
无法在从库上单独执行触发器

5.3.3 混合模糊**

5.4 mysql复制工作方式

5.4.1 复制的过程**

• 主库将变更写入二进制日志
• 从库读取主库的二进制日志变更并写入到relay_log中
  根据读取的位置不同：可分为基于日志点的复制和基于GTID 的复制
• 在从库重放relay_log中的日志
  基于SQL段的日志是在从库上重新执行记录的SQL
  基于行的日志则是在从库上直接应用对数据库行的修改
  

5.5 基于日志点的复制

5.5.1 基于日志点的复制配置步骤**

• 在主库服务器上建立复制账号

CREATE USER ‘repl’@'ip 段' identified by ‘’password;
GRANT REPLICATION SLAVE ON *.* TO  ‘repl’ @'ip 段';

• 配置主数据库服务器

bin_log = mysql-bin          -- 启动二进制日志
server_id = 100              -- 唯一值
relay_log = mysql-relay-bin  -- 
Log_salve_update = on [可选]   -- 如果这个从服务器作为其他服务器的主，需要配置这个参数
read_only = on[可选]        	   -- 安全配置参数，阻止没有权限的用户对开启这个权限的数据库进行写操作

• 配置从数据库服务器

bin_log = mysql-bin       
server_id = 101            
relay_log = mysql-relay-bin    
Log_salve_update = on [可选]   
read_only = on[可选]  

• 初始化从服务器数据

mysqldump  --master-date = -single-transaction 
xtrabackup  -- slave-info

• 配置复制链路

CHANGE ASTER TO MASTER_HOST =”MASTER_HOST_IP”,
MASTER_USER =’repl’,
MASTER_PASSWORD=’pasword’,
MASTER_LOG_FILE =’mysql_log_file_name’, -- 查看备份文件头部
MASTER_LOG_POS=4;

• 启动复制链路

start slave 

• 查看启动的线程
  从库：

show slave status；

show full processlist;

主库：dump进程
从库：io进程和SQL进程

5.5.2 基于日志点的复制配置步骤的优缺点

优点：
是MySQL最早支持的复制技术，bug相对较少
对SQL查询没有任何限制
故障处理比较容易
缺点：
故障转移是重新获取新主的日志点信息比较困难

5.6 基于GTID的复制【建议】

5.6.1 什么是GTID

GTID即全局事务ID，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID
GTID=SOURCE_ID:TRANSACTION_ID

5.6.2 基于GTID的复制配置步骤

• 在主库服务器上建立复制账号

//创建复制用户
CREATE USER ‘repl’@'ip 段' identified by ‘’password;

//授权 
GRANT REPLICATION SLAVE ON *.* TO  ‘repl’ @'ip 段';

• 配置主数据库服务器

bin_log = /usr/localmysql/log/mysql-bin
server_id=100
gtid_mode = on        -- 启动GTID 复制方式
enforce-gtid-consiste =on    -- 强制GTID一致性
log_slave-updates = on 

• 配置从数据库服务器

server_id=101
relay_log = /usr/localmysql/log/relay_log
gtid_mode = on        -- 启动GTID 复制方式
enforce-gtid-consiste =on    -- 强制GTID一致性
log_slave-updates = on 
read_only= on [建议]
master_info_repository =TABLE[建议]
master_log_info_repository =TABLE[建议]

• 初始化从服务器数据

// (记录备份时最后的事务的GTID值)
mysqldump  --master-date = -single-transaction 
xtrabackup  -- slave-info

• 启动基于GTID的复制

CHANGE ASTER TO MASTER_HOST =”MASTER_HOST_IP”,
MASTER_USER =’repl’,
MASTER_PASSWORD=’pasword’,
MASTER_AUTO_POSITION =1;

5.6.3 基于GTID的复制配置步骤的优缺点**

优点：
可以很方便的进行故障转移
从库不会丢失主库上的任何修改
缺点：
故障处理比较复杂
对执行的SQL有一定的限制

5.6.4 选择复制模式要考虑的问题

所使用的MySQL版本
复制架构及主从切换的方式
所使用的的高可用管理组件
对应用支持的程度

5.7 MySQL复制拓扑

MySQL5.7之前，一个从库只能有一个主库
MySQL5.7之后，支持一主多从架构

5.7.1 一主多从的复制拓扑

拓扑图：

优点：
配置简单
可以用多个从库分担读负载

用途：
为不同业务使用不同的从库
将一台从库放到远程IDC,用作灾备
分担主库的读负载

5.7.2 主-主复制拓扑

主主模式下的主-主复制
拓扑图：

主主模式下的主-主复制注意事项：
问题：
产生数据冲突而造成复制链路中断
耗费大量的时间
造成数据丢失

处理办法：
两个主中所操作的表最好能够分开
使用下面的两个参数控制自增ID的生成

auto_increment_increment = 2
auto_increment_offset = 2

5.7.3 主备模式下的主-主复制

注意事项：

• 只有一台主服务器对外提供服务
• 一台服务器处于只读状态并且只作为热备使用
• 在对外提供服务的主库出现故障或是计划性的维护时才会进行切换
• 使原来的备库成为主库，而原来的主库会成为新的备库并处理只读或是下线状态，待维护完成后重新上线

配置注意事项：

• 确保两台服务器上的初始数据相同
• 确保两台服务器上已经启动binlog并且有不用的server_id
• 在两台服务器上启用log_slave_updates参数
• 在初始的备库上启用read_only

拥有备库的主-主复制拓扑

级联复制
在分发主库上配置：slave_log_updates参数

5.8 MySQL复制性能优化

5.8.1 影响主从延迟的因素

• 主库写入二进制日志的时间
  解决办法：控制主库的事务大小，分隔大事务
• 二进制日志传输时间
  解决办法：使用mixed日志格式或设置set binlog_row_image=minimal;
• 默认情况下从只有一个SQL线程，主上并发的修改在从上变成串行
  解决办法：使用多线程复制 （5.6中开始使用）
  在MySQL5.7中可以按照逻辑始终的方式来分配SQL线程

5.8.2 如何配置多线程复制**

// 关闭从库复制功能
stop slave;

//开启逻辑时钟
set global slave_parallel_type=”logical_clock”; 

//设置线程数
set global slave_parallel_workers=4; 

//启动从库复制功能
start slave;

5.9 MySQL复制常见问题处理

5.9.1 由于数据损坏或丢失所引起的主从复制错误**

• 主库或从库意外宕机引起的错误
  解决办法：使用跳过二进制日志事件
  注入空事务的方式先恢复中断的复制链路
  再使用其它方法来对比主从服务器上的数据

• 主库上的二进制日志损坏
  解决办法：通过change master 命令重新制定

• 备库上的中继日志损坏

5.9.2 在从库上进行数据修改造成的主从复制错误

不唯一的server_id 或server_uuid
max_allow_packet设置引起的主从复制错误
主从复制不能分担主数据库的写负载
主从复制无法自动进行故障转移及主从切换
主从复制不提供读写分离功能

5.10 什么是高可用架构

5.10.1 高可用定义

指通过尽量缩短因日常维护操作和突发的系统崩溃所导致的停机时间，以提高系统和应用的可用性。

造成可用的因素：严重的主从延迟，主从复制中断，锁引起的大量阻塞，软硬件故障造成的服务器故障。

5.10.2 高可用的计算

用正常可用的时间和全年时间百分比表示高可用的程度。
如果全年可用是99.999%的时间，则全年不可用时间为：
（3652460）（1-0.99999）=5.256分钟
如果全年可用是99.99%的时间，则全年不可用时间为：
（3652460）（1-0.99999）=52.56分钟
如果全年可用是99.9%的时间，则全年不可用时间为：
（3652460）*（1-0.99999）=525.6分钟

可用性越高，实现成本越大。

5.10.3 如何实现高可用

• 避免导致系统不可用的因素，减少系统不可用的时间
  比如：服务器磁盘空间耗尽，性能糟糕的SQL，表结构和索引没有优化，主从数据 不一致，人为的操作失误等。
  解决： 建立完善的监控及报警系统。避免错报和漏报
  对备份数据进行恢复测试
  正确配置数据库环境
  对不需要的数据进行归档和清理
• 增加系统冗余，保证发生系统不可用时可以尽快恢复
  避免存在单点故障
  主从切换及故障转移

5.10.4 如何解决单点故障

单点故障
指一个系统中提供相同功能的组件只有一个，如果这个组件失效了，就会影响整个系统功能的正常使用。组成应用系统的各个组件都有可能成为单点。

如何避免MySQL单点故障

• 利用SUN共享存储或DRDB磁盘复制解决MySQL单点故障
  共享存储：
  
  磁盘镜像：
  

• 利用多写集群或NDB集群来解决MySQL单点故障（很少使用）
  

• 利用MySQL主从复制来解决MySQL单点故障

5.10.5 如何解决主服务器的单点问题

• 主服务器切换后，如何通知应用新的主服务器的IP地址
• 如何检查MySQL主服务器是否可用
• 如何处理服务器和新主服务器之间的复制关系

5.11 MMM架构介绍

MMM: Multi-Master Repalication Manager
MySQL多主复制管理集群

5.11.1 MMM作用

监控和管理MySQL的主主复制拓扑，并在当前主服务器失效时，进行主和主备服务器之间的主从切换和故障转移等工作。

5.11.2 MMM提供的功能

• MMM监控MySQL主从复制健康情况
• 在主库出现宕机是进行故障转移并自动配置其他从对新主的复制
• 提供了主，写虚拟IP，在主从服务器出现问题时可以自动迁移虚拟IP

5.11.3 MMM架构

MMM部署所需资源

5.12 MMM架构实例演示（上）

5.12.1 MMM部署步骤

• 配置主主复制及主从集群
• 安装主从节点所需要的支持包-（yum安装）
• 安装及配置MMM工具集
• 运行MMM监控服务
• 测试

5.12.2 MMM演示拓扑图

具体演示步骤暂无。

5.13 MMM架构实例演示（下）

5.13.1安装MMM步骤

（略）

5.13.2 安装MMM代理组件步骤

需要在每一台服务器上安装
在监控服务器上安装监控服务
创建监控，代理用户和授权
配置mmm配置文件
启动3个节点上的代理
模式测试故障转移

5.14 MMM架构的优缺点

5.14.1 MMM架构优点

• 使用Perl脚本语言开发及完全开源
• 提供了读写VIP（虚拟IP），使服务器角色的变更对前端应用透明MMM提供了从服务器的延迟监控
• MMM提供了主数据库故障转以后从服务器对新主的重新同步功能
• 很容易对发生故障的主数据库重新上线
• MMM提供了从服务器的延迟监控

5.14.2 MMM架构缺点

• 发布时间比较早不支持MySQL新的复制功能
  只支持基于日志点的同步方式，不支持GTID复制的同步方式
  基于gtid的复制可以保证日志不会重复在slave服务器上执行
  对于MySQL5.6后所提供的多线程复制技术也不支持
• 没有读负载均衡的功能
• 在进行主从切换时，容易造成数据丢失（事务丢失，重复提交事务）
• MMM监控服务存在单点故障

5.15 MHA架构介绍

MHA由perl脚本开发的。

MHA更多关注主从复制中的主DB,其重要功能是MySQL主从复制架构下，完成故障切换，并在众多的从服务器中选举出主服务器，并将其他的从服务器和新选出的新主服务器的进行同步切换，在MySQL故障切换过程中，MHA可以做到高效的完成主从切换，基本可以保证在30秒内完成所有的切换操作，并且切换过程中最大程度保证切换的一致性，避免丢失事务，达到真正意义的高可用。

5.15.1MHA提供的功能

• 监控主数据库服务器是否可用
• 当主DB不可用时，从多个从服务器中选举出心得主数据库服务器
• 提供了主从切换和故障转移功能
  MHA可以与半同步复制结合

5.15.2 MHA如何进行主从切换

• 尝试从出现故障的主数据库保存二进制日志
• 从多个备选从服务器中选举出新的备选主服务器
  可认为设置不参与选举的服务器
• 从备选主服务器和其他从服务器之间同步差异二进制数据
• 应用从原主DB服务器上保存的二进制日志
• 提升备选主DB服务器为新的主DB服务器
• 迁移集群中的其他从DB作为新的主DB的从服务器

5.15.3 MHA演示架构**

MMM:不支持gtid的复制
MHA:支持gtid的复制

5.15.4 MHA配置步骤**

• 配置集群内所有主机的SSH免认证登录

• 安装MHA-node软件包和MHA-manager软件包
  

• 建立主从复制集群

• 配置MHA管理节点

• 使用masterha-check-ssh和masterha-check-repl对配置进行检验

5.16 MHA架构实例演示(1)

配置基于DTID的主从复制架构

具体演示步骤暂无。

5.17 MHA架构实例演示(2)

具体演示步骤暂无。

5.18 MHA架构优缺点

MHA优点：
同样是有Perl语言开发的开源工具
可以支持基于GTID的复制模式
MHA在进行故障转移是更不易产生数据丢失
同一个监控节点可以监控多个集群

MHA缺点：
需要编写脚本或利用第三方工具来实现Vip的配置
MHA启动后只会对主数据库进行监控
需要基于SSH免认证配置，存在一定的安全隐患
没有提供从服务器的读负载均衡功能

5.19 读写分离和负载均衡介绍

5.19.1 实现读写分离的方法

程序实现读写分离
中间件实现读写分离

1. 程序实现读写分离
优点：
由开发人员控制什么样查询在从库中执行，因此比较灵活
由程序直接连接数据库，所以性能损耗比较少

缺点：
增加开发人员的工作量，是程序代发更加复杂
人为控制，容易出现错误
中间件实现读写分离

2.中间件实现读写分离
mysql-proxy：MySQL实验室项目，没有发布正式版本，性能和稳定新不够稳定，在高并发的情况下容易死掉。

maxScale：MariaDB提供的中间件

优点：
由中间件根据查询语法分析，自动完成读写分离。
对程序透明，对于已有程序不用做任务调整

缺点：
由于增加了中间层，所以对查询效率有损耗。–差不多降低50%-70%的损耗
对于延迟敏感业务无法自动在主库执行

5.19.2 如何实现负载均衡

1. 软件
LVS
Haproxy
MaxScale

2. 硬件
F5

5.20 MaxScale实例演示

MaxScale插件

MaxScale使用介绍

安装

最终的高可用架构

第6章 数据库索引优化

6.1 Btree索引和Hash索引

MySQL支持的索引类型
（不同的存储引擎支持的索引类型不同）

B-tree索引的特点

• B-tree索引以B+树的结构存储数据
• B-tree索引能够加快数据的查询速度
• B-tree索引更适合进行范围查找

在什么情况下可以用到B树索引

• 全值匹配的查询
• 匹配最左前缀的查询
• 匹配列前缀查询
• 匹配范围值的查询
• 精确匹配最左前列并范围匹配另外一列
• 只访问索引的查询

B-tree索引的使用限制

• 如果不是按照索引最左列开始查找，则无法使用索引
• 使用索引是不能跳过索引中的列
• Not in 和<> 操作无法使用索引
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引

Hash索引的特点

• Hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到Hash索引。
• 对于Hash索引中的所有列，存储引擎都会为每一列计算一个Hash码。Hash索引中存储的就是Hash码。

Hash索引的限制

• Hash索引必须进行二次查找
• Hash索引无法用于排序
• Hash索引不支部分索引查找也不支持范围查找
• Hash索引中Hash码的计算可能存在Hash冲突

为什么使用索引

• 索引大大减少了索引存储引擎需要扫描的数据量
• 索引可以帮助我们进行排序以避免使用临时表
• 索引把随机I/O变为顺序 I/O

索引是不是越多越好

• 索引会增加写操作的成本
• 太多的索引会增加查询优化器的选择时间

6.2 安装演示数据库

安装演示数据库步骤

6.3 索引优化策略（上）

索引优化策略

• 索引列上不能使用表达式或函数

select  ... from product
where to_days(out_date) - to_days(current_date) <= 30 

改写：

select  ... from product
where out_date <=date_add(current_date,interval 30 day)

• 前缀索引和索引列的选择性

CREATE INDEX index_name on table(col_name(n));

索引的选择性是不重复的索引值和标的记录数的比值。
（在选择性不好的列建立索引，对性能提升是没有用的。　一个索引的选择性越接近于1，这个索引的效率就越高。）

• 联合索引
  如何选择列的顺序
  经常会被使用到的列优先-放在联合索引的最左边
  选择性高的列优先-
  宽度小的列优先
  覆盖索引-- 包含所以需要查询中的字段的全部值的索引
  优点
  可以优化缓存，减少磁盘IO操作
  可减少随机IO，随机IO操作变为顺序IO操作
  可以避免对Innodb主键索引的二次查询
  可以免MyISAM表进行系统调用
  无法使用覆盖索引的情况
  存储引擎不支持覆盖索引
  查询中使用了太多的列
  使用了双%号的like查询

6.4 索引优化策略（中）

使用索引来优化查询
使用索引扫描来优化排序

• 两种方式：
  通过排序操作
  按照索引顺序扫描数据
• 条件：
  索引的列顺序和order by子句的顺序完全一致
  索引中所有列的方向（升序，降序）和order by子句完全一致
  order by 中的字段全部在关联表中的第一张表中

6.5 索引优化策略（下）

利用索引优化锁
索引可以减少锁定的行数
索引可以加快处理速度，同时也加快了锁的释放

索引的维护和优化
删除重复和冗余的索引


重复的索引完全没有必要，但是冗余的索引可以适当的添加

通过工具发现重复和冗余的索引

MySQL5.7及以上版本提供直接查询冗余索引、重复索引和未使用过索引的视图，直接查询即可。

• 查询冗余索引、重复索引

select * from sys.schema_redundant_indexes;

• 查找未被使用过的索引

select * from sys.schema_unused_indexes;

• 更新索引统计信息及减少索引碎片
  （如果统计信息不准确，优化器可能会进行错误的判断）

• 更新索引统计信息：

//（不同存储引擎，分析评估效率不同，innodb效率较高，但是统计信息相对myisam的准确性不高）
analyze table table_name ;

减少索引碎片：

// 使用不当，此操作会锁表。
optimize table table_name ; 

第7章 SQL查询优化

7.1 获取有性能问题SQL的三种方法

提高MySQL性能，不仅要设计最优的数据库表结构,建立最好的索引，扩展数据库的查询。查询优化，索引优化，库表结构优化还需要齐头并进。

获取存在性能问题的SQL

• 通过用户反馈获取存在性能问题的SQL（被动）
• 通过慢查询日志获取存在性能问题的SQL
• 实时获取存在性能问题的SQL

7.2 慢查询日志介绍

存储日志需要耗费大量的存储空间

slow_query_log           启动停止记录慢查询日志 
slow_query_log_file      指定慢日志的存储路径及文件-默认情况保存在MySQL的数据目录，建议日志存储和数据存储的位置放在把不同的路径
Long_query_time 	     指定记录慢查询日志SQL执行时间的阈值，可精确到微妙，该参数的单位是秒，默认10秒。通常改为0.001秒也就是1毫秒可能比较合适
Log_queries_not_using_indexes 是否记录未使用索引的SQL

常用的慢查询日志分析工具（sqldumpslow）
汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出。

用法：

mysqldumslow -s r -t 10 slow-mysql.log

参数说明：

7.3 慢查询日志实例

常用的慢查询日志分析工具（pt-query-digest）
用法：（在服务器执行）

pt-query-digest \
--explain h=127.0.0.1 ,u=root,p=p@ssw0rd\
slow-mysql.log

7.4 实时获取性能问题SQL

select * from information_schema.processlist where time>60;

7.5 SQL的解析预处理及生成执行计划

查询速度为什么会慢

• 客户端发送SQL请求给服务器
• 服务器检查是否可以在查询缓存中命中该SQL
• 服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划
• 根据执行计划，调用存储引擎API来查询数据
• 将结果返回给客户端
  (这就是MySQL服务器处理查询请求的整个过程)

查询缓存对SQL性能的影响

• 优先检查这个查询是否命中查询缓存中的数据。
  通过一个对大小写敏感的哈希查找实现的。Hash查找只能进行全值匹配

• 检查用户权限
  …

// 与查询缓存相关的参数
query_cache_type           设置查询缓存是否可用
[ON|OFF|DEMAND]
DEMAND 表示只有在查询语句中使用SQL_CACHE和SQl_NO_CACHE来控制是否需要缓存
query_cache_size            设置查询缓存的内存大小，必须是1024的整数倍
query_cache_limit           设置查询缓存可用存储的最大值
query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据，默认关闭
query_cache_min_res_unit    设置查询缓存分配的内存块最小单位

• MySQL依照执行计划和存储引擎进行交互
  这个阶段包含很多子过程：
  解析SQL，预处理，优化SQL执行计划
  语法解析阶段是通过关键字对MySQL语句进行解析，并生成一颗对应的“解析树”
  MySQL解析器将使用MySQL语法规则验证和解析查询
  包括检查语法是否使用了正确的关键字
  关键字的顺序是否正确等
  预处理阶段是根据MySQL规则进一步检查解析树是否合法
  检查查询中所涉及的表和数据列是否存在及名字或别名是否存在歧义等等

语法检查全部通过后，查询优化器就可以生成查询计划了

影响MySQL生成错误的执行计划的原因：

• 统计信息不准确
• 执行计划的成本估算并不等于实际的执行计划的成本
• MySQL优化器所认为的最优可能与你所认为的最优不一样
• MySQL从不考虑其他并发的查询，这可能影响当前查询的速度
• MySQL有时候也会基于一些固定的规则来生成执行计划
• MySQL不会考虑不受其控制的成本

MySQL优化器可优化的SQL类型

• 重新定义表的关联顺序
• 将外联转化为内连接
• 使用等价变换规则
• 优化count(),min()和max()
• 将一个表达式转化为常数表达式
• 子查询优化
• 提前终止查询
• 对in()条件进行优化

7.6 如何确定查询处理各个阶段所消耗的时间

使用profile

set profiling =1;-- 启动profile,这是一个session级的配制

执行查询SQL

Show profiles;

//-- 查询每个阶段所消耗的时间
Show profile for query N;

使用performance_schema
启用功能：

UPDATE SETUP_INSTRUMENTS  set enabled=”YES”,TIMED=”YES” WHERE NAME LIKE “stage”;

UPDATE SETUP_CONSUMERS set enabled=”YES” WHERE NAME LIKE “events”;

7.7 特定SQL的查询优化

大表的更新与删除

DELIMITER $$
USE 'immoc'$$
drop procedure if exists 'p_delete_rows'$$
create procedure 'p_delete_rows'()
begin 
		declare v_rows int ;
		set v_rows =1；
		while v_rows >0
		do 
			delete from tablename where id >=1 and id <=1000;;
			selelct row_count() int v_rows;
			select sleep();
			end while;
	end $$
DELIMITER ;

如何修改大表的表结构

pt-online-schema-change \
-- alter ="modify c varchar(150) not null default '' "\
-- user=root --password =p@ssw0rd = d=tmp,t=test \
-- charset =utf8 --execute

如何优化not in 和 <> 查询

使用汇总表优化查询

第8章数据库的分库分表

8.1 数据库分库分表的几种方式

把一个实例中的多个数据库拆分到不同的实例

把一个库中的表分离到不同的数据库中

8.2 数据库分片前的准备

对一个库中的相关表进行水平拆分到不同实例的数据库中

对数据库分片前的准备
如何选择分区键

1.分区键要能尽量避免跨分片查询的产生
2.分区键要能尽量使用各个分片中的数据平均
3.如何存储无需分片的表
每个分片中存储一份相同的数据
使用额外的节点统一存储
4. 如何在节点上部署分片
每个分片使用单一数据库，并且数据库名也相同
将朵儿分片表存储在一个数据库中，并在表名上加入分片号后缀
在一个节点中部署多个数据库，每个数据库包含一个分片
5. 如何分配分片中的数据
按分区键的hash值取模来分配分片数据
按分区键的范围来分配分片数据。常用语分区间为日期类型和int类型的字段
利用分区键和分片的映射表来分配分片数据
6. 如何生成全局唯一ID
使用auto_increment_increment 和auto_increment_offset参数
使用全局节点来生成ID
使用redis等缓存服务器中的全局ID

8.3 数据库分片演示(上)

（略）

8.4 数据库分片演示(下)

（略）

第9章 数据库监控

9.1 数据库监控介绍

MySQL数据库要监控的内容

• 对数据库服务可用性进行监控
  通过网络连接到数据库并且确定数据库是可以对外提供服务的

• 对数据库性能进行监控
  QPS和TPS 并发线程数量
  监控InnoDB阻塞和死锁

• 对主从复制进行监控
  主从复制链路状态的监控
  主从复制延迟的监控
  定期的确认主从复制的数据是否一致

• 对服务器资源进行监控
  监控磁盘空间、CPU、内存、Swap及网络IO的使用情况

9.2 数据库可用性监控

对需监控的资源进行监控的方法
mysqladmin -umonitor_user -p -h ping
telnet ip db_port
使用程序通过网络建立数据库连接（最优方式）

如何确定数据库是否可读写
检查数据库的read_only参数是否为off
建立监控表对表中数据进行更新
执行检查的查询select@@version

如何检查数据库的连接数

show variables like ‘max_connections’；
show global status like “Threads_connected”;
threads_connected/max_connections > 0.8 

9.3 数据库性能监控

数据库可用性监控
记录性能监控过程中所采集到的数据库的状态

如何计算QPS和 TPS

如何监控数据库的并发请求数量
数据库系统的性能会随着并发处理请求数量的增加而下降

show global status  like “Threads_running”;

并发处理的数量通常会远小于同一时间连接到数据库的线程的数量

如何监控Innodb的阻塞

select b.trx_mysql_thread_id as '被阻塞线程'
,b.trx_query as '被阻塞SQL'
,c.trx_mysql_thread_id as '阻塞线程'
,c.trx_query as '阻塞SQL'
,(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started)) as '阻塞时间' 
from information_schema.innodb_lock_waits a 
join information_schema.innodb_trx b on a.requesting_trx_id=b.trx_id 
join information_schema.innodb_trx c on a.blocking_trx_id=c.trx_id 
where (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started))>10;

9.4 MySQL主从复制监控

如何监控主从复制链路的状态

show slave status

如何监控主从复制延迟

// 查看延迟时间 （简单，但不过准确）
show slave status;

主从的二进制日志文件名和偏移量

如何验证主从复制的数据是否一致
使用工具pt-table-checksum,在主库运行即可。