MySQL理论基础

基本概念

存储引擎

  • 存储引擎的种类包括:InnoDB、MyISAM、CSV、MEMORY、Merge、Archive、Fedrated
  • 存储引擎是针对于表的而不是针对于库的(一个库中的不同表可以使用不同的存储引擎),但是不建议使用混合存储引擎。

存储引擎对比

存储引擎类型 介绍 特性 适用场景
MyISAM 1. MySQL5.58之前版本默认存储引擎。
2. 大部分系统表、临时表(在排序、分组等操作中,当数量超过一定的大小之后,由查询优化器建立的临时表)所采用的存储引擎。
3. 由数据文件(MYD)和索引文件(MYI)组成
1. 表级锁
- 支持对意外关闭的表进行检查和修复
2. 支持全文索引、对text、blob类型建立前500字的前缀索引
3. 对表进行数据压缩
4. <MySQL5.0时默认表大小为4G
1. 非事务型应用。在线分析类型应用数据仓库、报表、非财务类引用。
2. 只读类应用。可以对报表进行压缩。3. 空间类应用。如GPS,可以应用空间函数对数据进行运算。
InnoDB 1. MySQL5.58之后版本默认存储引擎
2. 使用表空间进行数据存储
1. 事务型存储引擎
2. 完全支持事务的ACID特性
3. Redo Log和Undo Log
4. 支持行级锁(可以最大程度的支持并发)
5. 支持状态检查(show engine innodb status)
大多数OLTP应用(MySQL5.7以后支持全文索引、空间函数)
CSV 1. 数据以文本方式存储在文件中
2. .CSV文件存储表内容
3. .CSM文件存储表的元数据如表状态和数据量
4. .frm文件存储表结构信息
1. 所有列必须都是不能为NULL的
2. 不支持索引
3. 可以对数据文件直接编辑
1. 数据交换的中间表
2. 不适合大表,不适合在线处理
Archive 1. 以zlib对表数据进行压缩,磁盘I/O更少
2. 数据存储在ARZ为后缀的文件中
1. 只支持insert和select操作
2. 只允许在自增ID列上加索引
日志和数据采集类应用
MEMORY 1. 也称为HEAP存储引擎,数据保存在内存中
重启后表结构在,但是数据会丢失
3. 最大大小由max_heap_table_size参数决定
1. 支持HASH索引(默认)和BTree索引
2. 所有字段都为固定长度
3. 不支持BLOB和TEXT等大字段
4. 表级锁
1. 用于查找或者是映射表,例如邮编和地区的对应表
2. 用于保存数据分析中产生的中间表
3. 用于缓存周期性聚合数据的结果表
Fedrated 1. 提供了访问远程MySQL服务器上表的方法
2. 本地不存储数据,数据全部放到远程服务器上
3. 本地需要保存表结构和远程服务器的连接信息
4. 默认禁止,启用需要在启动时增加federated=1参数
偶尔的统计分析及手工查询

说明
1. 锁的主要作用是管理共享资源的并发访问
2. 锁用于实现事务的隔离性
共享锁 也称读锁
独占锁 也称写锁
表级锁 对表进行加锁
行级锁 对行进行加锁
悲观锁 每次拿数据的时候都认为别人会修改数据,所以每次在拿数据的时候都会先上锁,这样别人想拿这个数据就会阻塞直到它拿到锁。
传统的关系数据库里边就用到了很多这种锁机制,比如:行锁、表锁、读锁、写锁等,都是在操作之前先上锁,让别人无法操作该数据。
乐观锁 每次拿数据的时候都认为别人不会修改该数据,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这条数据,一般用版本号机制进行判断。
乐观锁适用于读多写少的应用类型,这样可以提高吞吐量。
基于数据库版本号的乐观锁解决方案 在基于数据库表的版本解决方案中,一般是通过为数据库表添加一个“version”字段来实现读取数据时,将此版本号一同读出,之后更新时,对版本号加1。此时,将提交数据的版本号与数据库表对应记录的当前版本号进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据,不予更新。

乐观锁实现举例

操作员A 操作员B
1. 操作员A将用户信息读出(此时version=1),并准备从其账户中扣除$50($100-$50) 2. 在操作员操作的过程中,操作员B也读入此用户信息(此时version=1),并准备从其账户余额中扣除$20($100-$20)
3. 操作员A完成了修改工作,将数据版本号加1(此时version=2),连通账户扣除后余额(balance=$50),提交至数据库更新,此时由于提交数据库版本大于数据库记录当前版本,数据被更新,数据库记录version被更新为2.
4. 操作员B完成了操作,也将版本号加1(version=2)并试图向数据库提交数据(balance=$80),但此时对比数据库记录版本时发现,操作员B提交的数据版本号为2,数据库记录当前版本也为2,不满足“提交版本必须大于记录当前版本才能执行更新”的乐观锁策略,因此,操作员B的提交被驳回。

阻塞和死锁

阻塞和死锁是两个容易被开发人员混淆的概念。

对比项 阻塞 死锁
概念 由于不同锁之间的兼容性的关系,在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源 两个或两个以上的事物在执行过程中,相互占用了对方等待的资源而产生的一种异常。
本质 阻塞事务占用了被阻塞事务的资源 产生死锁的多个事务之间相互占用对方等待的资源

事务

事务并发导致3个现象

名称 简称 描述
脏读 读未提交 一个事务读取了另一个事务改写还未提交的数据,如果另外一个事务在稍后的时刻回滚,则该事务出现了脏读。
不可重复读 读不回去 一个事务在进行相同条件的查询,连续两次或者两次以上,每次结果都不同。
幻读 虚读 一个事务在进行相同条件的查询,连续两次或者两次以上,在稍后会发现一些原来没有的数据。

事务隔离级别

为了避免事务并发导致的3个现象,做了4个应对的手段,这4种手段就是事务隔离级别。

数字 隔离级别 解释 可能会出现的问题
1 Read uncommitted 读未提交 可能出现脏读、不可重复读、幻读
2 Read committed 读已提交(Oracle数据库默认) 可能出现不可重复读、幻读
4 Repeatable read 可以重复读(MySQL数据库默认) 可能会出现幻读
8 Serializable 串行化 -

索引

索引按数据结构分类

类型 说明 适用场景
B-树索引 是mysql使用最频繁的一个索引数据结构,数据结构以平衡树的形式来组织,因为是树型结构,所以更适合用来处理排序,范围查找等功能。 1. 范围查询
2. 排序
Hash索引 Hash索引是基于Hash表实现的,只有查询条件精确匹配Hash索引中的所有列时,才能够使用到hash索引。
并不支持范围查找和排序等功能。
Hash索引存在以下限制:
1. Hash索引必须进行二次查找
2. Hash索引无法用于排序
3. Hash索引不支持部分索引查找
4. Hash索引不支持范围查找
5. Hash索引中的Hash码的计算可能存在Hash冲突
适用于等值查询
注意:不适用于范围查询、模糊查询

索引按功能分类

索引类型 说明
普通索引 最基本的索引,没有任何限制
唯一索引 与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
主键索引 它是一种特殊的唯一索引,不允许有空值。
全文索引 仅可用于 MyISAM 表,针对较大的数据,生成全文索引很耗时好空间。
组合索引 为了更多的提高mysql效率可建立组合索引,遵循“最左前缀”原则。

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

用到B树索引的情况 举例
全值匹配的查询 order_sn='987643234000'
匹配最左前缀的查询 -
匹配列前缀查询 order_sn like '9876%'
匹配范围值的查询 order_sn>'987643234000' and order sn<’987643235000‘
精确匹配左前列并范围匹配到另一列 order_sn='987643234000' and createdate >'2007-3-30' and createdate<'2008-1-20'
只访问索引的查询 -

BTree索引的使用限制

  1. 如果不是按照索引最左列开始查找,则无法使用索引
  2. 使用索引时不能跳过索引中的列
  3. Not in和 <> 操作无法使用索引
  4. 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引
  5. 如果查询中某个列使用了模糊查询,则无法使用索引

索引的选择性

  • 索引的选择性是不重复的索引值和表的记录数的比值。
  • 索引的选择性越高,使用索引查询就会越快。
  • 主键的索引选择性最高。

索引是不是越多越好

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

主键索引跟唯一索引的区别

对比项 主键 唯一索引
从属 主键一定会创建唯一索引 唯一索引不一定是主键
可空 不允许空值 可以空值
单表数量 1 大于1
外键引用 可以 不可以
本质 约束 索引
执行计划 低于主键

使用explain分析索引问题

explain介绍

使用explain命令,来解释和分析SQL查询语句,从而知道MySQL是如何执行你的SQL语句的。如:

1
explain select * from servers;

当type列为All的时候,表示查询将进行全表扫描,这将会导致查询效率非常的低下。而索引优化的主要目的是为了避免使用全表扫描,提高查询速度和效率。

explain返回列介绍

explain返回列 说明
select_type 查询中每个select子句的类型
table 显示这一行的数据是关于哪张表的,有时不是真实的表名字,看到的是derivedx。
type 表示MySQL在表中找到所需行的方式,又称“访问类型”。
possible_keys 指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
key 显示MySQL实际决定使用的键(索引)
key_len 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)
不损失精确性的情况下,长度越短越好
ref 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
rows 表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数
Extra 该列包含MySQL解决查询的详细信息

type列参数说明

性能从上到下越来越好。

列参数 说明
ALL 全表扫描(Full Table Scan)。MySQL将遍历全表以找到匹配的行。
index 索引扫描(Full Index Scan)。index与ALL区别为index类型只遍历索引树
range 只检索给定范围的行,使用一个索引来选择行
ref 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
eq_ref 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
const、system 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system
NULL MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

select_type列参数说明

列参数 说明
SIMPLE 简单SELECT,不使用UNION或子查询等
PRIMARY 查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
UNION UNION中的第二个或后面的SELECT语句
DEPENDENT UNION UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT UNION的结果
SUBQUERY 子查询中的第一个SELECT
DEPENDENT SUBQUERY 子查询中的第一个SELECT,取决于外面的查询
DERIVED 派生表的SELECT, FROM子句的子查询
UNCACHEABLE SUBQUERY 一个子查询的结果不能被缓存,必须重新评估外链接的第一行

大表

什么是大表

  1. 记录行数巨大,单表超过千万行
  2. 表数据文件巨大,表数据文件超过10G

大表对数据库性能有哪些影响?

大表对查询的影响

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

对DDL操作的影响

MySQL版本 风险
MySQL版本<5.5 建立索引会锁表
MySQL版本>=5.5 虽然不会锁表,但会引起主从延迟
  • 建立索引需要很长的时间
  • 修改表结构需要长时间锁表

如何处理数据库中的大表

  • 分表
  • 历史数据归档

大事务

什么是大事务?

运行时间比较长,操作的数据比较多的事物。

大事务对数据库性能的影响

  • 锁定太多的数据,造成大量的阻塞和锁超时
  • 回滚时所需时间比较长
  • 执行时间长,容易造成主从延迟

如何处理大事务

  • 避免一次处理太多的数据
  • 移除不必要在事务中的SELECT操作

连接查询

笛卡尔积

没有任何连接条件

1
select a.*,b.* from t1 a,t2 b

等值连接查询

1
select a.*,b.* from t1 a,t2 b where a.cid = b.id

内连接(inner join on或join on)

1
select a.id,a.name,b.id,b.orderno from t1 a inner join t2 b on a.id = b.cid

inner可以省略。

外连接(left outer join on或right outer join on)

1
select a.id,a.name,b.id,b.orderno,b.price from t1 a left outer join t2 b on a.id = b.cid

外连接分左外连接、右外连接。 左外连接向左看齐,右侧如果没有匹配数据,用空值自动补齐。 右外连接向右看齐,左侧如果没有匹配数据,用空值自动补齐。 MySQL不支持全外连接。

联合查询(union、union all)

纵向合成查询的结果,查询的列数必须相同,union all包含重复数据。

1
select id,name from t1 union select id,orderno from t2

SQL语句

部分替换

替换需求

将 http://192.168.1.1:8080 替换为 http://192.168.1.2:8080

替换SQL语句

1
2
3
update test
set Content= REPLACE('Content', 'http://192.168.1.1:8080', 'http://192.168.1.2:8080')
WHERE INSTR('Content','http://192.168.1.1:8080') > 0

复制

复制功能介绍

  • 基于二进制日志
  • 增量进行
  • 分担读负载
  • 异步方式
  • 为高可用、灾难恢复、备份提供更多的选择

注意:复制并不能代替备份。

复制的方式

复制方式 说明
基于日志点的复制 主从复制时,会指定从哪个二进制日志的偏移量进行增量同步,如果指定错误会造成遗漏或重复,从而造成主从数据不一致
基于GTID的复制(推荐 GTID即全局事务ID,其保证为每一个在主上提交的事物在复制集群中可以生成一个唯一的ID
GTID=source_id:transaction_id
从库会告诉主库已经执行事务的GTID值,主库把未执行的GTID值发送到从库上,可以保证同一个事务只在指定的从库执行一次

复制的工作流程

  1. 主将变更写入二进制日志
  2. 从读取主的二进制日志变更并写入到relay_log中
  3. 在从上重放relay_log中的日志

二进制日志

按照记录日志组件分类

分类 说明
MySQL服务层日志 二进制日志、慢查日志、通用日志
MySQL存储引擎层日志 如:Innodb的重做日志、Innodb回滚日志

二进制日志介绍

记录了所有对MySQL数据库的修改事件,包括增删改查事件和对表结构的修改事件。

各种二进制格式的优缺点

二进制日志格式 优点 缺点
基于段的日志格式(SBR)
(binlog_format=STATEMENT
1. 日志记录量相对较小,节约磁盘及网络I/O
2. 并不强制要求主从数据库的表定义完全相同
3. 相比于基于行的复制方式更为灵活
1. 对于非确定性事件,无法保证主从复制数据的一致性
2. 对于存储过程、触发器、自定义函数进行的修改也可能造成数据不一致
3. 相比于基于行的复制方式在从上执行时需要更多的行锁

原因:
1. 必须要记录上下文信息,才能保证语句在从服务器上执行结果和在主服务器上相同
2. 特定函数如UUID()、user()这样非确定性函数还是无法复制
基于行的日志格式(RBR)
(binlog_format=ROW
推荐
1. 使MySQL主从复制更加安全(可以应用于任何SQL的复制包括非确定函数、存储过程等)
2. 对每一行数据的修改比基于段的复制高效
3. 可以减少数据库锁的使用
4. 当误操作时,可以分析二进制日志反向处理恢复数据
1. 要求主从数据库的表结构相同,否则可能会中断复制
2. 无法在从上单独执行触发器
3. 记录日志量较大
优化方式:binlog_row_image=MINIMAL
混合日志格式
(binlog_format=MIXED
推荐
根据SQL语句由系统决定在基于段和基于行的日志格式中进行选择 -

binlog_row_image参数值

binlog_row_image参数值 说明
FULL 默认。记录所有数据的修改。
MINIMAL 推荐。只会记录被修改的列。
NOBLOB 在表中TEXT和BLOB等大字段如果不修改,就不记录前后镜像了,其他小字段的列的修改依然记录前后镜像。

查看当前MySQL使用的日志格式

1
mysql> show variables like 'binlog_format';

手动刷新binlog日志

1
mysql> flush logs;

使用官方binlog命令行工具查看二进制日志

1
2
3
mysql> mysqlbinlog mysql-bin.000002
# 查看ROW格式的日志
mysql> mysqlbinlog -vv mysql-bin.000002

MySQL复制拓扑

一主一从

MySQL一主一从.png
MySQL一主一从.png

一主多从

MySQL一从多主.png
MySQL一从多主.png

一从多主

MySQL一从多主.png
MySQL一从多主.png

主主模式的主主复制

MySQL主主复制.png
MySQL主主复制.png

主主模式下的主-主复制配置注意事项

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

主主模式下的主-主复制可能产生的问题

  • 产生数据冲突而造成复制链路中断
  • 耗费大量的时间人为选择如何处理
  • 造成数据丢失

主主模式下的主-主复制配置注意事项

  • 两个主中所操作的表最好能够分开
  • 使用下面两个参数控制自增ID的生成
    1
    2
    3
    4
    5
    6
    7
    # 作用:控制自增列的步长
    # 默认值:1
    auto_increment_increment=2
    # 作用:决定了自增ID从哪个值开始
    # 默认值:1
    # 需要在一台主设置1,另一台主设置2
    auto_increment_offset=1|2

主备模式的主主复制

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

拥有备库的主-主复制

MySQL拥有备库的主主复制.png ### 级联复制 MySQL级联复制.png

MySQL复制无法解决的问题

  • 无法分担主数据库的写负载
  • 无法自动进行故障转移及主从切换
  • 不提供读写分离功能

MySQL复制性能优化

影响复制性能因素 解决办法
主库写入二进制的时间 控制主库的事务大小,分割大事务为小事务
二进制日志传输时间 使用MINIMAL或MIXED日志格式
默认情况下从只有一个SQL线程,主上并发的修改在从上变成了串行 使用多线程复制(MySQL版本≥5.6)
MySQL5.7中可以按照逻辑时钟的方式来分配SQL线程

配置

配置多线程复制

1
2
3
4
5
6
7
8
# 停止从库
mysql> stop slave;
# 配置多线程复制采用逻辑时钟的方式,默认:DATABASE
mysql> set global slave_parallel_type='logical_clock';
# 设置线程数
mysql> set global slave_parallel_workers=4;
# 启动从库
mysql> start slave;

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

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

1
2
CREATE USER 'repl' @'IP段' indentified by 'Password';
GRANT REPLICATION SLAVE ON *.* TO 'repl' @'IP段';

配置主数据库服务器

1
2
3
4
# 启动二进制日志,并指定二进制文件的名字
bin_log=mysql-bin
# server_id的值在整个集群中必须是唯一的
server_id=100

配置从数据库服务器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 启动二进制日志,并指定二进制文件的名字
bin_log=mysql-bin
# server_id的值在整个集群中必须是唯一的
server_id=101
# 默认值:主机的名字。
# 强烈建议:配置该参数。
# 原因:如果运维人员修改了主机名,就会报错,从而终端主从链路。
relay_log=mysql-relay-bin
########## 可选参数(建议配置) ##########
# 默认值:OFF
# 作用:决定了是否把SQL线程重放的中继日志记录到从服务器本机的二进制日志中
log_slave_update=on
# 默认值:OFF
# 作用:阻止任何没有super权限的用户对开启了这个选项的数据库进行写操作
# 强烈建议:从服务器配置并开启该选项
read_only=on

初始化从服务器数据

步骤略。

启动复制链路

1
2
3
4
5
mysql> CHANGE MASTER TO MASTER_HOST='master_host_ip',
MASTER_USER='repl',
MASTER_PASSWORD='Password',
MASTER_Log_FILE='mysql_log_file_name',
MASTER_LOG_POS=4;

配置基于GTID的MySQL复制的步骤

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

1
2
CREATE USER 'repl' @'IP段' indentified by 'Password';
GRANT REPLICATION SLAVE ON *.* TO 'repl' @'IP段';

配置主数据库服务器

1
2
3
4
5
6
7
8
9
10
11
12
13
# 启动二进制日志,并指定二进制文件的名字
bin_log=mysql-bin
# server_id的值在整个集群中必须是唯一的
server_id=100
# 作用:开启GTID模式
gtid_mode=on
# 作用:用于开启GTID以后事务的安全
# 注意:1. 该参数会造成create table ...select无法使用,只能先create table,然后使用insert into。
# 注意:2. 在事务中使用Create temporary table建立临时表使用关联更新事务表和非事务表都会报错
enforce-gtid-consiste
# 作用:在从服务器中记录主服务器的发送过来的修改日志
# 注意:如果MySQL版本<5.7需要配置该参数,MySQL版本≥5.7,不需要设置该参数。
log-slave-updates=on

配置从数据库服务器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 启动二进制日志,并指定二进制文件的名字
bin_log=mysql-bin
# server_id的值在整个集群中必须是唯一的
server_id=101
# 默认值:主机的名字。
# 强烈建议:配置该参数。
# 原因:如果运维人员修改了主机名,就会报错,从而终端主从链路。
relay_log=/usr/local/mysql/log/relay_log
gtid_mode=on
enforce-gtid-consiste
########## 可选参数(建议配置) ##########
# 作用:决定了是否把SQL线程重放的中继日志记录到从服务器本机的二进制日志中
# 默认值:OFF
log_slave_update=on
# 作用:阻止任何没有super权限的用户对开启了这个选项的数据库进行写操作
# 默认值:OFF
# 强烈建议:从服务器配置并开启该选项
read_only=on
# 作用:配置以下参数可以在数据库崩溃时,利用Innodb的事物进行快速恢复
# 默认:存储在文件中
master_info_repository=TABLE
relay_log_info_repository=TABLE

初始化从服务器数据

步骤略。

启动复制链路

1
2
3
4
mysql> CHANGE MASTER TO MASTER_HOST='master_host_ip',
MASTER_USER='repl',
MASTER_PASSWORD='Password',
MASTER_AUTO_POSITION=1;

MySQL复制常见错误及处理

错误 解决办法
主库或从库以外宕机引起的错误 1. 使用跳过二进制日志事件
2. 注入空事务的方式先恢复中断的复制链路
3. 再使用其它方法来对比主从服务器上的数据
主库上的二进制文件损坏 通过change master命令来重新指定,但是会丢失主库上的一些更新,使得主从数据出现差异,接下来还是要对比主从服务器上的数据。
备库上的中继日志损坏 只要主库上的二进制文件没有删除,就可以通过change master命令来重新指定从库上的IO线程重新从损坏的位置再次同步主库的二进制日志
在从库上进行数据修改修改造成的主从复制错误 read_only=on
不唯一的server_id或server_uuid 主服务器之间如果出现相同的erver_id或server_uuid,启动时会报错,所以很容易被发现。
如果从服务器之间出现相同的erver_id或server_uuid,就不容易被发现,特别是server_uuid的情况。
从服务器max_allow_packet设置引起的主从复制错误 确保跟主服务器设置相同的max_allow_packet值。

备份

mysqldump

mysqldump是MySQL官方提供的备份工具。

1
2
3
# master-data值为二进制文件偏移量的信息
# single-transaction参数适用于Innodb存储引擎
mysql> mysqldump --master-data=2 -single-transaction

xtrabackup

1
xtrabackup --slave-info

mysqldump与xtrabackup对比

对比项 mysqldump xtrabackup
背景 MySQL官方 第三方
阻塞 会造成大量阻塞 MyISAM存储引擎会造成阻塞
Innodb存储引擎不会造成阻塞
支持存储引擎 MyISAM、Innodb MyISAM、Innodb

高可用

导致MySQL不可用的因素

  • 服务器磁盘耗尽
  • 性能糟糕的SQL
  • 表结构和索引没有优化
  • 主从数据不一致
  • 人为的操作失误

如何实现高可用

  • 建立完善的监控及报警系统
  • 对备份数据进行恢复测试(常常被忽略)
  • 正确配置数据库环境
  • 对不需要的数据进行归档和清理
  • 增加系统冗余,保证发生系统不可用时可以尽快恢复
    • 避免存在单点故障
    • 主从切换及故障转移

如何避免MySQL单点故障

方式 说明
SUN共享存储 不完美(共享存储本身就是个单点)
DRDB磁盘复制 不完美(故障转移时间长、由于磁盘故障)
多写集群 整个集群性能取决于集群中性能最差的服务器的性能
NDB集群 数据存储在内存中,如果内存不足,NDB集群的性能就会非常差(NDB很少被使用到生产环境中)
MySQL主从复制 推荐

解决主服务器的单点问题需要解决的问题

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

MMM(Multi-Master Replication Manager)

MMM介绍

一套Perl语言开发的用语管理MySQL主主同步的工具集。

MMM主要作用

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

MMM功能

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

MMM架构优缺点

MMM架构优点

  • 使用Perl脚本语言开发、完全开源
  • 提供了读写VIP(虚拟IP),使服务器角色的变更对前端应用透明 在从服务器出现大量的主从延迟,主从链路中断时可以把这台从服务器上的读的虚拟IP,漂移到集群中其它正常的服务器上。
  • MMM提供了从服务器的延迟监控
  • MMM提供了主数据库故障转移后从服务器对新主的重新同步功能
  • 很容易对发生故障的主数据库重新上线

MMM架构缺点

  • 发布时间比较早,不支持MySQL新的复制功能(GTID的复制、多线程复制),并且存在一些Bug,需要在使用过程中由DBA根据实际情况进行修复
  • 没有读负载均衡的功能 需要引入LVS、HAProxy这样的工具,增加了集群管理的成本
  • 在进行主从切换时,容易造成数据丢失
  • MMM监控服务存在单点故障 为了解决这个问题,还需要开发MMM监控服务的监控程序

使用MMM需要注意的问题

当面临以下一些问题:

  • 如何找到从库对应的新主库日志点的日志同步点
  • 如果存在多个从库出现数据不一致的情况如何处理

MMM对这块的处理是简单粗暴的找到当前主库的日志点,使所有从库对这个日志点进行同步,在一个繁忙的系统中使用MMM会导致一些数据丢失的情况。 另外,由于MMM不支持多线程复制,当主库写压力过大时,会导致主从延迟过大,而请求全部漂移到主服务器,从而把主服务器连接占满或把主服务器压垮的情况。

MMM部署所需资源

资源名称 数量 说明
主DB服务器 2 用于主备模式的主主复制配置(建议:2台主DB服务器的硬件配置必须相同)
从DB服务器 0-N 可以配置0台或多台从服务器,但不建议太多
监控服务器 1 用于监控MySQL复制集群
Ip地址 2*(n+1) n为MySQL服务器的数量
监控用户 1 用于监控数据库状态的MySQL用户(replication client)
代理用户 1 用户MMM代理的MySQL用户(super、replication client、process)
复制用户 1 用户配置MySQL复制的MySQL用户(replication slave)

MHA(Master High Availability)

介绍

由Perl脚本开发的,用于管理MySQL主从复制,从而实现MySQL高可用的一套工具套装。 MHA可以完成主从的高效切换(30秒内完成主从切换),并且在切换过程中可以最大程度的保证数据一致性,达到真正意义的高可用。

功能

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

MHA是如何进行主从切换的

MHA对主DB进行监控,当发现主DB不可访问,会自动进行故障转移和主从切换操作。大致的切换过程由以下几个主要步骤完成:

  1. 尝试从出现故障的主数据库保存二进制日志
  2. 从多个备选从服务器中选举出新的备选主服务器(可以人为的设置一些服务器不参与选举)
  3. 在备选主服务器和其它从服务器之间同步差异二进制数据
  4. 应用从原主DB服务器上保存的二进制日志(注意:重复的主键等会使MHA停止进行故障转移)
  5. 提升备选主DB服务器为新的主DB服务器
  6. 迁移集群中的其它从DB作为新的主DB的从服务器

MHA配置步骤

  1. 配置集群内所有主机的SSH免认证登陆
  2. 安装MHA-node软件包和MHA-manager软件包

    1
    $ yum -y install perl-Config-Tiny.noarch perl-Time-HiRes.x86_64 perl-Paraller-ForkManager perl-Log-Dispatch-Perl.noarch perl-DBD-MySQL ncftp

  3. 建立主从复制集群 MHA同时支持基于日志点的复制和基于GTID的复制,推荐使用GTID的复制,更安全,不会出现重复回放日志的情况。
  4. 配置MHA管理节点
  5. 使用masterha_check_ssh和masterha_check_repl对配置进行检验
  6. 启动并测试MHA服务

MHA架构优缺点

MHA架构优点

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

MHA架构缺点

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

MMM与MHA对比

对比项 MMM MHA
GTID的复制支持 不支持 支持
多线程复制 不支持 支持
数据丢失 主从切换时,容易丢失数据 故障转移时可以最大限度的避免数据丢失
监控项 主从节点 主节点
从服务器读负载均衡 不支持 不支持

读写分离

读写分离的实现方式对比

读写分离的实现方式 优点 缺点 说明
程序实现读写分离 1. 灵活
2. 性能损耗比较少
1. 增加开发工作量
2. 程序代码更加复杂
3. 人为控制,容易出现错误
由开发人员控制什么样的查询在从库中执行。
适用于分布式数据一致性要求较高的场景
中间件实现读写分离 对程序透明,对于已有程序不用做任何调整 1. 增加了中间层,所以对查询效率有损耗(降低50%-70%的QPS)
2. 对于延迟敏感业务无法自动在主库执行
由中间件根据语法分析,自动完成读写分离

读写分离中间件

mysql-proxy

  • 实验室性质
  • 不但可以解决读写分离,还可以实现多个从服务器的读负载均衡
  • 高并发容易死掉
  • 很多公司基于它开发自己的读写分离中间件
  • 由于性能和稳定性,并不建议在生产环境直接使用

maxScale

参见 maxScale基础

MyCAT

参见 MyCAT基础

架构图

MMM架构图

MMM架构.png
MMM架构.png

MHA架构图

MHA架构.png
MHA架构.png

性能优化

影响数据库的因素

  • 服务器硬件
  • 磁盘IO
  • 网卡流量
  • SQL查询速度
  • 数据库备份
  • 消耗磁盘性能的计划任务
  • 大表
  • 大事务
  • 服务器系统的参数
  • 数据库存储引擎的选择
  • 数据库参数配置(最有影响)
  • 数据库结构设计
  • SQL语句的编写方式
  • 服务器版本(64位适用32位的服务器版本)
  • 内存大小(当内存大小高跟磁盘数据大小相等时,增加内存就毫无意义)

存储介质及选择

存储介质类型 优点 缺点 说明/适用场景
传统机器硬盘 最常见、使用最多、价格低、存储空间大 读、写较慢 -
RAID0 便宜、快速、没有数据冗余 危险 -
RAID1 高速读、简单、安全 空间浪费、数据冗余、读快写慢 -
RAID5 安全、成本折中 读快写效率取决于最慢的盘、数据冗余 -
RAID10 高速、安全 贵、数据冗余 -
固态硬盘 相比机械磁盘有更好的随机读写性能、更好的支持并发 相比机械固态磁盘更容易损坏 1. 存在大量随机I/O的场景
2. 解决单线程负载的I/O瓶颈(从服务器)
3. 由于易损坏,所以不建议在主服务器上使用
PCIE卡 相比固态硬盘性能更快 相比固态硬盘成本更高、需要使用服务器内存 -
SAN 大量顺序读写 随机读写慢、不如本地RAID磁盘 通过光纤连接到服务器,设备通过块接口访问,服务器可以将其当做硬盘使用
NAS 方便 延迟、占用服务器资源、网络传输 1. 使用网络连接,通过基于文件的协议如NFS或SMB来访问
2. 由于网络存储随机读写性能一般,复杂度较高(停机时间长),所以并不适合做数据库存储。
3. 适合做数据库备份

性能排序:PCIE -> SSD -> Raid10 -> 磁盘 -> SAN -> NAS

如何选择传统机器硬盘

  • 存储容量
  • 传输速度
  • 访问时间
  • 主轴转速
  • 物理尺寸

网络优化

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

CentOS系统参数优化

内核相关参数(/etc/sysctl.conf)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
########## 优化队列长度 ##########

# 默认值:128
# 作用:定义了系统中每一个端口最大的监听队列的长度。
net.core.samaxconn=65535

# 默认值:1000
# 作用:网卡设备将请求放入队列的长度
net.core.netdev_max_backlog=65535

# 默认值:128
# 作用:增大SYN队列的长度,容纳更多连接
net.ipv4.tcp_max_syn_backlog=65535

########## 加快TCP回收 ##########

# 默认值:60
# 作用:TCP时间戳,超时等待时间,缩短后可以加快TCP的回收。
net.ipv4.tcp_fin_timeout = 10

# 默认值:0
# 作用:针对TIME-WAIT,开启后可加快TCP的回收。
net.ipv4.tcp_tw_reuse = 1

# 默认值:0
# 作用:针对TIME-WAIT,开启后可加快TCP的回收。
net.ipv4.tcp_tw_recycle = 1

########## 优化TCP连接接收和发送缓冲区默认值、大小 ##########
# 建议:(根据实际情况可适当调整的大一些)

# 默认值:212992
# 作用:默认的TCP数据发送窗口大小(字节)
net.core.wmem_default = 87380

# 默认值:212992
# 作用:最大的TCP数据发送窗口大小(字节)
net.core.wmem_max = 87380

# 默认值:212992
# 作用:默认的TCP数据接收窗口大小(字节)
net.core.rmem_default = 87380

# 默认值:212992
# 作用:最大的TCP数据接收窗口大小(字节)
net.core.rmem_max = 16777216

########## 减少失效连接占用的TCP资源的数量,加快资源回收效率 ##########
# 建议:根据实际情况适当调小

# 默认值:7200
# 作用:间隔多少秒发送1次keepalive探测包
net.ipv4.tcp_keepalive_time = 120

# 默认值:75
# 作用:探测失败后,间隔多少秒后重新探测
net.ipv4.tcp_keepalive_intvl = 30

# 默认值:9
# 作用:探测失败后,最多尝试探测几次
net.ipv4.tcp_keepalive_probes = 3

########## 内存相关的参数 ##########

# 作用:用于定义单个共享内存段的最大值。
# 这个参数应该设置的足够大,以便能在一个共享内存段下容纳下整个的Innodb缓冲池的大小。
# 这个值对于64位Linux系统,可取的最大值为物理内存值-1byte,建议值为大于物理内存的一半。
# 一般取值大于Innodb缓冲池的大小即可,可以取物理内存-1byte。
kernel.shmmax = 4294967295

# 默认值:60
# 作用:数值(0-100)越高,越可能发生swap交换
# 这个参数当内存不足时会对性能产生比较明显的影响
# 在MySQL服务器上是否要使用交换分区有一些争议:在MySQL服务所在的Linux系统上完全禁用交换分区。这样带来的风险:1、降低操作系统性能。2、容易造成内存溢出、崩溃,或被操作系统kill掉。
# 结论:在MySQL服务器上保留交换分区还是很有必要的,但是要控制何时使用交换分区。
# 以下参数作用:告诉Linux内核除非虚拟内存完全满了,否则不要使用交换分区。
vm.swappiness=0

增加资源限制(/etc/security/limit.conf)

这个文件是Linux PAM(插入式认证模块)的配置文件。 注意:这个文件的修改需要重启系统才可以生效。

1
2
3
4
5
6
7
8
########## 打开文件数限制 #########
# * 表示对所有用户有效
# soft 当前系统生效的设置
# hard 表明系统众怒所有设定的最大值
# nofile 表示限制的资源是打开文件的最大数目
# 65535 限制的数量
* soft nofile 65535
* hard nofile 65535

优化磁盘调度策略(/sys/block/devname/queue/scheduler)

磁盘调度策略

调度策略 中文名称 适用场景
cfq Linux默认的cfq策略。
这个策略适用于桌面级系统
noop 电梯式调度策略 NOOP实现了一个FIFO队列,它像电梯的工作方法一样对I/O进行组织,当有一个新的请求到来时,它将请求合并到最近的请求之后,以此来保证请求同一介质。
NOOP倾向饿死读而利于写,因此NOOP对于闪存设备RAM嵌入式系统是最好的选择。
deadline 截止时间调度策略 Deadline确保在一个截止时间内服务请求,这个截止时间是可调整的,而默认读期限短于写期限。这样就防止了写操作因为不能被读取而饿死的现象,Deadline对数据库类应用是最好的选择。
anticipatory 预料I/O调度策略 本质上与Deadline一样,但在最后一次读取操作后,要等待6ms,才能继续进行对其它I/O请求进行调度。
它会在每个6ms中插入新的I/O操作,而会将一些小写入流合并成一个大写入流,用写入延时换取最大的写入吞吐量。
AS适合于写入较多的环境,比如文件服务器,AS对数据库环境表现很差

查看磁盘调度策略

1
2
3
4
# 查看磁盘文件使用的调度策略
$ cat /sys/block/devname/queue/scheduler
noop anticipatory deadline [cfq]
# 这个策略用于桌面级系统是没有问题的,但是用于MySQL服务器系统就不太合适了。

改变磁盘调度策略

1
2
3
$ echo <schedulername> > /sys/block/devname/queue/scheduler
# 例如,设置deadline调度策略为MySQL服务器的调度策略
$ echo deadline > /sys/block/devname/queue/scheduler

选择合适的文件系统

操作系统 文件系统 选择
Windows FAT 不要选择
Windows NTFS 可以选择
Linux EXT3
Linux EXT4
Linux XFS 优先选择(性能更高)

选择EXT3/4需要注意挂载参数(/etc/fstab)

data参数选项 说明 建议
writeback 源数据写入和数据写入不同步 推荐(最快)
Innodb有自己的事务日志,这个选项对Innodb来说是最好的选择
ordered 只会记录源数据,但提供了一些一致性的保证,在写源数据之前会先写数据,使它们保持一致 比writeback稍微慢一些,但如果出现崩溃,会更加安全
journal 提供了原子日志的一种行为,在数据写入到最终日志之前将记录到日志中 这个选项对于Innodb来说是没有必要的
1
2
3
4
# data=writeback | ordered | journal,具体参数选项参见上表中所示。
# noatime,nodiratime用于禁止文件的访问时间和读取目录的时间,可以减少一些写操作
# 完整示例
/dev/sda1/ext4 noatime,nodiratime,data=writeback 1 1

MySQL服务器优化

Innodb I/O相关配置

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

# 0:每秒进行一次log写入cache,并flush log到磁盘。
# 1:默认。在每次事务提交执行log写入cache,并flush log到磁盘。
# 2:建议。每次事务提交,执行log数据写入到cache,每秒执行一次flush log到磁盘。
Innodb_flush_log_at_trx_commit=2

Innodb_flush_method=O_DIRECT
Innodb_file_per_table=1
Innodb_doublewrite=1

MyISAM I/O相关配置

1
2
3
4
# OFF:每次写操作后刷新键缓冲中的脏块到磁盘,最安全、性能最差
# ON:只对在键表时指定了delay_key_write选项的表使用延迟刷新
# ALL:对所有MyISAM表都使用延迟键写入
delay_key_write

安全相关配置参数

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
# 作用:指定自动清理binlog的天数
expire_logs_days

# 作用:控制MySQL可以接收的包的大小。
# 建议:32M
# 注意:如果是主从复制,建议主从的配置保持一致,如果从的值小于主的配置,可能会导致主从复制失败。
max_allowed_packet

# 作用:禁用DNS查找
skip_name_resolve

# 作用:确保sysdate()返回确定性日期
sysdate_is_now

# 禁止非super权限的用户写权限。
# 建议:在主从复制中的从库中启用。
read_only

# 作用:禁用Slave自动恢复
skip_slave_start

# 作用:设置MySQL所使用的SQL模式
# 常用可选项:stict_trans_tables、no_engine_subtitution、no_zero_date、no_zero_in_date、only_full_group_by
# 建议:不要随意改动在生产环境中的sql_mode值,以免造成应用程序出错。
sql_mode

其它常用配置参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 作用:控制MySQL如何向磁盘刷新binlog
# 建议:主DB设置为1(最安全,性能最差)
sync_binlog

# 作用:控制内存临时表大小
# 建议:tmp_table_size和max_heap_table_size设置为相等并且不要设置太大
tmp_table_size

# 作用:控制内存临时表大小
# 建议:tmp_table_size和max_heap_table_size设置为相等并且不要设置太大
max_heap_table_size

# 作用:控制允许的最大连接数
# 默认值:100
# 建议:设置为2000或更大一些
max_connections

索引优化

索引基础

参见 索引

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

1
2
3
4
5
6
# 错误示例
select ...... from product
where to_days(out_date)-to_days(current_date)<=30
# 正确示例
select ...... from product
where out_date<=date_add(current_day,interval 30 day)

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

MySQL中的B树索引对使用键值的大小是有限制的,这个限制根据使用存储引擎的不同而不同。

存储引擎 索引键值大小限制
InnoDB 767字节
MyISAM 1000字节

前缀索引会降低索引的选择性。

1
2
# 创建前缀索引(n为列的宽度)
CREATE INDEX index_name ON table(col_name(n));

联合索引

背景知识

使用索引的误区之一是在每一列上都建立索引。这种情况经常发生在一些对索引不是很了解的开发人员身上。他们了解索引的好处,但是不了解如何恰当的使用索引。

为什么为每一列建立索引不是一种好的优化方式。

即使为每一列都建立索引,也不一定能使用上这些索引。 MySQL5.0之前,每一个查询只能使用到一个列上的索引。 MySQL5.0之后,虽然引入了索引合并的概念,在一个查询中可以使用到多个列上的独立索引进行合并过滤,但是通常意味着这需要更多的内存和磁盘IO来缓存每一个索引所获取的数据,所以这也并不是一种好的优化方式。 更恰当的方式是建立联合索引。

如何选择索引列的顺序

  • 经常会被使用到的列优先
  • 选择性高的列优先
  • 宽度小的列优先

覆盖索引

什么是覆盖索引

B树索引的叶子节点上存储了索引的关键字的值,所以我们可以通过索引直接获取查询中的关键数据。这样就没有必要读取数据行的信息了。这种包含了所有需要查询的字段的全部值的索引,称之为覆盖索引。 这里说的全部值不但包括where语句中出现的值,也包括select、order by、group by语句中的值。

覆盖索引的优点

  • 可以优化缓存,减少磁盘IO操作
  • 可以减少随机IO,将随机IO操作变为顺序IO操作
  • 可以避免对Innodb主键索引的二次查询
  • 可以避免MyISAM表进行系统调用

无法使用覆盖索引的情况

  • 存储引擎不支持覆盖索引(如Memory存储引擎)
  • 不是所有索引类型都能够建立覆盖索引(如Hash索引)
  • 查询中使用了太多的列(特别是对于select * 这样的查询)
  • 使用了双%号的like查询

如何判断是否可以使用覆盖索引

使用explain语句,观察Extra列:

  • 如果是Using index或包含Using index,可以使用覆盖索引
  • 如果是Using where,不可以使用覆盖索引

使用索引来优化查询

使用索引扫描来优化排序

如果explain执行计划中的type列为index,则说明MySQL使用索引扫描来做排序。 使用索引扫描来优化排序需要满足以下条件:

  • 索引的列顺序和Order By子句的顺序完全一致
  • 索引中所有列的方向(升序、降序)和Order by子句完全一致
  • Order by中的字段全部在关联表中的第一张表中

模拟Hash索引优化查询

  • 只能处理键值的全值匹配查找
  • 所使用的Hash函数决定着索引键的大小
1
2
3
4
5
6
7
8
# 模拟Hash索引优化查询示例
mysql> alter table film add title_md5 varchar(32);
mysql> update film set title md5=md5(title);
mysql> create index idx_md5 on film(title_md5);
mysql> explain select * from film where title md5=md5('EGG IGBY') and title='EGG IGBY'\G
mysql>
mysql>
mysql>

利用索引优化锁

  • 索引可以减少锁定的行数
  • 索引可以加快处理速度,同时也加快了锁的释放

删除重复和冗余的索引

错误示例一:

1
2
# 主键索引,唯一索引,单列索引
primary key(id),unique key(id),index(id)

由于主键是一个非空的唯一索引,所以没必要再建立唯一索引了,也没有必要建立二级索引。 错误示例二:

1
2
# 二级索引,联合索引
Index(a),index(a,b)

错误示例三:

1
2
# 主键索引,联合索引
primary key(id),index(a,id)

使用pt-duplicate-key-checker检查索引是否冗余?

官网

1
2
3
4
5
6
# 安装
$ cd /usr/local/
$ wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-1.el7.x86_64.rpm
$ yum localinstall percona-toolkit-3.0.13-1.el7.x86_64.rpm -y
# 开始检查
$ pt-duplicate-key-checker h=127.0.0.1

查找未被使用过的索引

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
SELECT  ind.index_id ,
obj.name AS TableName ,
ind.name AS IndexName ,
ind.type_desc ,
indUsage.user_seeks ,
indUsage.user_scans ,
indUsage.user_lookups ,
indUsage.user_updates ,
indUsage.last_system_seek ,
indUsage.last_user_scan ,
'drop index [' + ind.name + '] ON [' + obj.name + ']' AS DropIndexCommand
FROM sys.indexes AS ind
INNER JOIN sys.objects AS obj ON ind.object_id = obj.object_id
LEFT JOIN sys.dm_db_index_usage_stats indUsage ON ind.object_id = indUsage.object_id
AND ind.index_id = indUsage.index_id
WHERE ind.type_desc <> 'HEAP'
AND obj.type <> 'S'
AND OBJECTPROPERTY(obj.object_id, 'isusertable') = 1
AND ( ISNULL(indUsage.user_seeks, 0) = 0
AND ISNULL(indUsage.user_scans, 0) = 0
AND ISNULL(indUsage.user_lookups, 0) = 0
)
ORDER BY obj.name ,
ind.name
GO

更新索引统计信息及减少索引碎片

1
2
3
mysql> analyze table table_name
# 注意:以下命令使用不当会导致锁表
mysql> optimize table table_name

SQL查询语句优化

慢查询

什么是慢查询日志?

慢查询日志是MySQL提供的一种日志记录功能,能够记录下响应时间超过一定阈值的SQL查询。支持将日志记录写入文件,也支持将日志记录写入数据库表。

配置5.6以下版本慢查询

1
2
3
4
5
[mysqld]
slow_query_log=1
log-slow-queries=/var/log/mysql/mysql-show.log
long_query_time = 4
log-queries-not-using-indexes = true

配置5.6及以上慢查询

1
2
3
4
5
[mysqld]
slow_query_log=1
slow-query-log-file=/var/log/mysql/mysql-show.log
long_query_time = 4
log-queries-not-using-indexes = true
参数 说明
slow_query_log 是否开启慢查询日志,1开启,0关闭。
log-slow-queries 旧版mysql数据库慢查询日志存储路径。
slow-query-log-file 新版mysql数据库慢查询日志存储路径。
long_query_time 慢查询阈值,当查询时间多于设定的阈值时,记录日志。
log-queries-not-using-indexes 未使用索引的查询也被记录到慢查询日志中(可选项)。
log_output 日志存储方式。log_output='FILE'表示将日志存入文件,默认值是'FILE'。log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到mysql.general_log表中。MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE, TABLE'。

检查配置是否生效?

1
show variables like 'slow_query_log'

如果查询结果是OFF,证明未开启,如果查询结果是ON,则证明已经开启成功。

动态开启慢查询日志而不重启数据库?

如果是在生产环境中配置慢日志查询,由于线上数据库不能随便重启,所以不能通过在my.cnf中配置的方式使之生效,但是从5.1.6版本才开始支持这个特性。 做法如下:

1
set global log_slow_queries=ON ;

这样就开启了慢查询而不需要重启数据库。

慢查询日志分析工具(mysqldumpslow)

mysqldumpslow可以汇总除查询条件外其它完全相同的SQL,并将分析结果按照参数中所指定的顺序输出。

1
2
3
4
5
6
7
8
9
10
11
12
# -s:指定输出结果的排序方式。
# 可选值:
# c(总次数)
# t(总时间)
# l(锁的时间)
# r(总数据航)
# at(平均总时间)
# al(平均锁时间)
# ar(平均总数据行)

# -t:(top)指定取前几条为结束输出
$ mysqldumpslow -s r -t 10 show-mysql.log

慢查询日志分析工具(pt-query-digest)

1
2
$ pt-query-digest --explain h=127.0.0.1,u=root,p=password show-mysql.log > slow.rep
$ cat slow.rep

实时获取有性能问题的SQL

1
2
3
4
5
# 当前服务器,执行 时间超过60s的sql
# 通过脚本周期性地执行这句,就可以实时发现执行慢的sql
SELECT id,‘user‘,‘host‘,DB,command,‘time‘,state,info
FROM information_schema.PROCESSLIST
WHERE TIME> =60

查询缓存

对于一个读写频繁的系统使用查询缓存可能会降低查询处理的效率,所以在这种情况下建议不要使用查询缓存。

参数 说明
query_cache_type 设置查询缓存是否启用
query_cache_size 设置查询缓存的内存大小
query_cache_limit 设置查询缓存可用存储的最大值
query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据
query_cache_min_res_unit 设置查询缓存分配的内存块最小单位

使用profile查询处理各个阶段所消耗的时间

1
2
3
4
5
6
7
set profiling=1;
# 执行查询
select * from users;
# 查看每一个查询所消耗的总时间的信息
show profiles;
# 查询每个阶段所消耗的时间
show profile for query N;

使用proformance_schema查询处理各个阶段所消耗的时间

1
2
3
4
5
6
7
8
9
10
11
# 和profile不同的是,该配置是全局的。要使用这个配置需要在performance_schema数据库下执行下面的2条update语句:
UPDATE 'setup_instruments'
SET enabled='YES',TIMED='YES' WHERE NAME LIKE 'stage%';
UPDATE 'set_consumers'
SET enabled='YES' WHERE NAME LIKE 'events%';
# 执行完上面的2句执行任意的SQL,查看这些SQL在各个阶段的执行时间可以采用如下的查询
SELECT a.THREAD_ID,SQL_TEXT,c.EVENT_NAME,(c.TIMER_END - c.TIMER_START)/1000000000 AS 'DURATION(ms)'
FROM events_statements_history_long a
JOIN threads b ON a.THREAD_ID = b.THREAD_ID
JOIN events_stages_history_long c ON c.THREAD_ID = b.THREAD_ID AND c.EVENT_ID BETWEEN a.EVENT_ID AND a.END_EVENT_ID
ORDER BY a.THREAD_ID,c.EVENT_ID;

避免使用in、not in、<>

1
2
3
4
5
6
7
# 错误示例
select * from t1 where phone not in (select phone from t2)
# 正确示例1
select * from t1
where not EXISTS (select phone from t2 where t1.phone =t2.phone)
# 正确示例2
select a.* from t1 a left join t2 on a.id=b.id where b.id is null

分库分表

参见 MyCAT基础

基准测试

什么是基准测试

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

基准测试与压力测试的区别

对比项 基准测试 压力测试
定义 直接、简单、易于比较,用于评估服务器的处理能力 对真实的业务数据进行测试,获得真实系统所能承受的压力
业务逻辑 基准测试可能不关心业务逻辑,所使用的查询和业务的真实性可以和业务环境没关系 压力测试需要针对不同主题,所使用的数据和查询也是真实用到的
目的 建立MySQL服务器的性能基准线
模拟比当前系统更高的负载,以找出系统的扩展瓶颈
获得真实系统所能承受的压力
方法 增加数据库并发,观察QPS、TPS变化,确定并发量与性能最优的关系
测试不同的硬件、软件和操作系统配置
证明新的硬件设备是否配置正确
-

如何进行基准测试

基准测试方法 说明 优点 缺点
对整个系统进行基准测试 从系统入口进行测试 能够测试整个系统的性能,包括Web服务器缓存、数据库等
能反映出系统中各个组件接口间的性能问题,体现真实性能状况
测试设计复杂,消耗时间长
单独对MySQL进行基准测试 测试设计简单,所需耗费时间短 无法全面了解整个系统的性能基线

MYSQL基准测试的常见指标

指标 说明
TPS 单位时间内所处理的事务数
QPS 单位时间内所处理的查询数
响应时间 平均响应时间、最小响应时间、最大响应时间、各时间所占百分比
并发量 同时处理的查询请求的数量
注意:并发量不等于连接数

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

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

MySQL基准测试工具

工具 说明 特点 适用场景
mysqlslap MySQL服务器自带的基准测试工具,随MySQL一起安装 1. 可以模拟服务器负载,并输出相关统计信息
2. 可以指定也可以自动生成查询语句
不适合对服务器的CPU、I/O、内存进行测试
sysbench 可以对服务器的CPU、I/O、内存进行测试,全面、常用。

mysqlslap常用参数

参数 说明
--auto-generate-sql 由系统自动生成SQL脚本进行测试
--auto-generate-sql-add-autoincrement 在生成的表中增加自增ID
--auto-generate-sql-load-type 指定测试中使用的查询类型
--auto-generate-sql-write-number 指定初始化数据时生成的数据量
--concurrency 指定并发线程的数量
--engine 指定要测试表的存储引擎,可以用逗号分隔多个存储引擎
--no-drop 指定不清理测试数据
--iterations 指定测试运行的次数
--number-of-queries 指定每一个线程执行的查询数量
--debug-info 指定输出额外的内存及CPU统计信息
--number-int-cols 指定测试表中包含的INT类型列的数量
--number-char-cols 指定测试表中包含的varchar类型的数量
--create-schema 指定了用于执行测试的数据库的名字
--query 指定自定义SQL的脚本
--only-print 并不运行测试脚本,而是把生成的脚本打印出来

示例:

1
$ mysqlslap --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=sbtest

安装systench

1
2
3
4
5
6
7
8
9
10
11
# 安装依赖包
yum install libtool -y
$ cd /usr/local/
$ wget https://github.com/akopytov/sysbench/archive/1.0.17.tar.gz
$ tar zxvf 1.0.17.tar.gz
$ cd sysbench-1.0.17/
$ ./autogen.sh
$ ./configure
# 或者指定真实mysql的安装路径
$ ./configure --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/
$ make && make install

sysbench常用参数

常用参数 说明
--test 指定要执行的测试类型,支持以下参数
- fileio 文件系统I/O性能测试
cpu cpu性能测试
memory 内存性能测试
Oltp 测试要指定具体的lua脚本
--mysql-db 用于指定执行基准测试的数据库名
--mysql-table-engine 用于指定所使用的存储引擎
--oltp-tables-count 执行测试的表的数量
--oltp-table-size 指定每个表中的数据行数
--num-threads 执行测试的并发线程数量
--max-time 指定最大的测试时间
--report-interval 指定间隔多长时间输出一次统计信息
--mysql-user 指定执行测试的MySQL用户
--mysql-password 指定执行测试的MySQL用户的密码
prepare 用于准备测试数据
run 用于实际进行测试
cleanup 用于清理测试数据

示例1:测试CPU性能

1
$ sysbench --test=cpu --cpu-max-prime=10000 run

示例2:测试内存性能

1
2
3
4
5
6
# 查看系统内存
$ free -m
# 准备测试数据
#注意:生成的测试数据一定要比内存大。
$ sysbench --test=fileio --file-total-size=1G prepare
$ sysbench --test=fileio --num-threads=8 --init-rnq=on --file-total-size=1G --file-test-mode=rndrw --report-interval=1 run

示例3:测试数据库性能

1
2
3
$ sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=数据库名字 --mysql-user=dbusername --mysql-password=dbpassword --oltp-tables-count=10 --mysql-socket=/usr/local/mysql/data/mysql.sock prepare
# 运行系统性能收集脚本(步骤略)
$ sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=数据库名字 --mysql-user=dbusername --mysql-password=dbpassword --oltp-tables-count=10 --mysql-socket=/usr/local/mysql/data/mysql.sock run

监控

Mytop

常见问题

Innodb存储引擎系统表空间和独立表空间如何选择

表空间类型 特点 适用场景
系统表空间 1. 无法简单的收缩文件大小
2. 会产生I/O瓶颈
默认设置,建议改掉,不要使用系统表空间
独立表空间 1. 可以通过optimize table命令收缩系统文件
2. 可以同时向多个文件刷新数据
推荐(强烈建议对Innodb使用独立表空间)

如何查看当前使用的表空间类型

1
2
3
# innodb_file_per_table=On:系统表空间(默认),同时会生成.frm、.ibd文件。
# innodb_file_per_table=OFF:独立表空间(推荐),只会生成.frm文件,不存在.idb文件。
mysql > show variables like 'innodb_file_per_table';

如何将原来存在于系统表空间中的表转移到独立表空间

  1. 使用mysqldump导出所有数据库表数据
  2. 停止MySQL服务,修改参数,并删除Innodb相关文件
  3. 重启MySQL服务,重建Innodb独立表空间
  4. 重新导入数据

为什么不建议使用混合存储引擎

  • 不同存储引擎对事务的支持情况不一样,混合使用会导致回滚时有的回滚了,有的没回滚,使得应用变得混乱。
  • 不同的存储引擎对热备的支持不一样,混合使用会导致整体都不支持热备。
  • 可能会导致其它的一些复杂的问题。

数据库设计中常犯的错误

  • 过分的反范式化为表建立太多的列
  • 过分的范式化造成太多的表关联
  • 在OLTP环境中使用不恰当的分区表
  • 使用外键保证数据的完整性

安装演示数据库

1
2
3
4
$ wget http://downloads.mysql.com/docs/sakila-db.tar.gz
$ tar -zxf sakila-db.tar.gz
$ mysql -uroot -p < sakila-schema.sql
$ mysql -uroot -p < sakila-data.sql

参考

坚持原创技术分享,您的支持将鼓励我继续创作!
0%