MySQL数据库性能优化史诗级大总结

点击上方蓝色字体，选择“设为星标”

回复”资源“获取更多资源

大数据技术与架构

点击右侧关注，大数据开发领域最强公众号！

大数据真好玩

点击右侧关注，大数据真好玩！

影响数据库服务器性能的因素

•超高的QPS（每秒钟处理的查询量）和TPS导致SQL处理效率下降。•大量的并发导致的数据库连接数被占满和超高的CPU占用率导致资源耗尽服务器宕机。•磁盘IO性能瓶颈导致数据传输效率下降，计划任务导致磁盘IO下降。•网卡IO性能瓶颈，要减少从服务器数量，缓存要分级，避免使用 select * 这样的查询。

大表导致的问题

不同数据库引擎对于大表的概念是不一样的。InnoDB存储引擎没有明确的大表概念。实际使用中发现当一个数据表中的数据超过千万行的时候会造成影响。但是对于日志存储引擎来说可能达到10亿条数据也不会出现问题，但是更改表结构会很耗时。大表往往会造成慢查询的产生，因为很难在一定时间内过滤出需要的数据。大表也会对DDL操作造成影响，比如建立索引会很长时间，在Mysql5.5版本之前建立索引会锁表，在5.5及其以后会造成主从延迟。修改表结构也会造成正常的数据操作，因为要锁表，所有会导致短时间内所有SQL操作都被阻塞，连接数会猛增导致Web服务器5xx错误。

大表解决方案

分库分表。跨分区的数据库查询的解决。另一种解决方案就是历史数据归档。归档时间点的选择。如何进行归档操作。

大事务导致的问题

•原子性：一个事务是一个不可分割的最小单元，执行时要么全部成功，要么全部失败。

一个银行账户向另一个账户转账操作，只能两个操作同时成功才算成功，否则回滚。

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

两个账户转账完成后不应该影响两个账户的总金额。

•隔离性：要求一个事务对数据库的修改，在未完成提交之前对于其他事务是不可见的。

当在第一个账户中取款以后还没向第二个账户存款时，另一个查询事务统计第一个存款余额时应该没有任何改动。SQL标准中定义的四种隔离级别：（隔离性从低到高，并发性从高到低）

+ 未提交读
+ 已提交读（Mysql不是默认，SQLServer和Oracle是默认）
+ 可重复读（InnoDB默认）
+ 可串行化

•持久性：一旦事务提交，数据将永久保持到数据库，即使系统崩溃也不会丢失。

大事务：运行时间比较长，操作数据比较多。

锁定的数据太多导致大量的阻塞和锁超时。

回滚所需要的时间比较长。

执行时间长，容易造成主从延迟。

大事务解决方案

避免一次性处理较多的数据 移除不必要的select操作

影响性能的几个因素

•服务器的硬件•服务器的操作系统•数据库系统的存储引擎•数据库系统的配置参数•数据库表结构设计和SQL语句优化

1. CPU资源和可用内存资源

CPU的频率高还是CPU的数量大的选择 看应用是否是CPU密集型的，那么就需要更好的CPU，并且当前Mysql也不支持多CPU并行处理 提高并发量就需要多个CPU 为了支持多CPU，尽可能选择新版本Mysql 尽量选择x64位CPU，尽量不要将64位Mysql安装到32位操作系统上 缓存不仅仅会对读取有好处，对写入也有好处，多次缓存数据后一次写入，减少IO操作 尽量选择容量较大的内存，内存频率要尽量高

2. 磁盘的配置和选择

•使用传统机械硬盘

传统机械硬盘读取数据的过程 移动磁头到磁表面上的正确位置 等待磁盘旋转，使得所需的数据在磁头之下 等待磁盘旋转过去，所需数据被读出

如何选择机械硬盘：

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

•使用RAID卡+传统机械硬盘

磁盘冗余队列技术将多个容量较小的磁盘组合成为一个容量较大的磁盘，并且提供数据冗余技术

主要的RAID级别：

RAID0 是最早出现的模式，成为数据条带，是最简单的一种方式，只需要2块以上的硬盘即可，成本较低，可以提高整个磁盘的性能的吞吐量。RAID没有提供荣誉或者错误修复能力，但是实现成本较高。容量是所有磁盘的总和，并且性能是之前的N倍。
RAID1 是磁盘镜像，原理就是把一个数据镜像到另一个磁盘上，最大限度的保证系统的可靠性和可修复性。
RAID5 是分布式奇偶校验磁盘阵列，主要就是把数据分散到多个磁盘上，如果任何一个磁盘损坏都可以快速重建，但是如果2块失效就全部无法恢复。最好使用在以读取为主要任务的服务器上，比如从数据库服务器。
RAID10 分片的磁盘镜像，对磁盘先进行RAID1，然后对两组RAID1进行RAID0，对读写都有较好的性能，重建速度快于RAID5。

如何选择RAID级别：

•使用SSD和PCIe卡

相对于机械磁盘来说，SSD具备更好的随机读写的性能。

更好的支持并发。

更容易损坏，每次进行写操作之前都要进行擦除操作，大批量的写操作会导致使用寿命问题，而且当磁盘容量所剩无几的时候性能也会下降。

•SSD（固态硬盘）：

直接使用SATA接口，当然也会限制SSD的速度。直接使用SATA接口，同样支持RAID技术。选择RAID卡的时候要支持SSD硬盘

•固态存储PCIe技术：

1.无法使用SATA接口，需要独特的驱动和配置2.性能高于普通SSD的RAID阵列，但是价格要贵，而且占用CPU和内存资源

•固态存储的使用场景：

1.适用于存在大量的随机IO的场景2.适用于解决单线程负载的IO瓶颈

•使用网络存储NAS或SAN

SAN（Storage Area Network）和NAS（Network-Attached Storage）是两种外部文件存储设备加载到服务器上的方法

SAN的访问通过光纤接口连接到服务器，服务器可以当做硬盘使用

SAN可以承载大量的顺序读写操作，但是在随机读写方面性能不高

NAS使用网络来连接，通过基于文件的协议，比如NFS或SMB

通常NAS具备更高的网络延迟

网络存储的使用场景：数据库的访问需要承载大量的随机IO，因此SAN和NAS都不太适合。但是适用于数据库备份。

3. 网络的配置和选择

•网络的带宽

采用高性能和高带宽的网络接口设备和交换机，最好是万兆级别的。

•网络的质量

对个网卡进行绑定，增强带宽和可用性。尽可能对网络进行隔离

4. 操作系统对性能的影响

•由于Mysql的Schema数据库实际上是一个目录，在Windows系统上对大小写不敏感，在类Unix上对大小写敏感，可能造成错误。•通过配置Mysql参数来强制使用小写•旧版本的FreeBSD对Mysql的支持不够好•CentOS系统的优化：

内核相关参数：/etc/sysctl.conf
net.core.somaxconn=65535
net.core.netdev_max_backlog=65535
net.ipv4.tcp_max_syn_backlog=65535
net.ipv4.tcp_fin_timeout=10
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.core.wmem_default=87380
net.core.wmem_max=16777216
net.core.rmem_default=87380
net.core.rmem_max=16777216
net.ipv4.tcp_keepalive_time=120
net.ipv4.tcp_keepalive_intvl=30
net.ipv4.tcp_keepalive_probes=3
kernel.shmmax=4294967295    # 用于定义单个共享内存段的最大值, # 这个参数应该设置的足够大, 以便能在一个共享内存段中容纳下整个InnoDB缓冲池# 对于x64的系统, 可取的最大值为物理内存的大小减去1字节，建议值为大于物理内存的一半
vm.swappiness=0             # 该参数当内存不足时对性能产生比较明显的影响, 设置内存交换区大小# 0表示除非Linux内核非虚拟内存完全满了, 否则就不使用交换区
资源限制：/etc/security/limit.conf
该文件是Linux PAM，也就是插入式认证模块的配置文件, 打开文件数的限制。
* soft nofile 65535
* hard nofile 65535参数：
* 表示对所有用户有效
soft 表示当前系统生效的设置
hard 表示系统中所能设置的最大值
nofile 表示所限制的资源是打开文件的最大数目
65535 表示限制的数量磁盘调度策略：/sys/block/devname/queue/scheduler
默认是使用cfq策略
noop（电梯式调度策略）, 实现了一个FIFO队列, 像电梯一样对IO请求进行组织, 当有一个新的请求到来时, 将会合并到最近的请求之后
以此来保证同一请求同一个介质。noop倾向饿死读而利于写, 一次noop对于闪存设备,RAM,嵌入式系统是最好的选择。
deadline（截止时间调度策略）确保在一个时间内服务请求, 这个截止时间是可以调整的, 而默认读期限短于写期限。
这样就会防止写操作因为不能被读取而饿死的现象, deadline对数据库类应用是最好的选择。
anticipatory（预料IO调度策略）本质上与deadline一样, 但是最后一个读操作后要等待6毫秒才能继续进行其他的IO请求进行调度。
写入流合并成为一个大的写入流, 用写入延时换区最大的写入吞吐量, 这种策略适合于写入较多的环境，比如文件服务器，该策略对数据环境表现较差

5. 文件系统对性能的影响

•文件系统的选择依赖于操作系统，实际上Windows只有一种文件系统——NTFS•Linux系统支持EXT3、EXT4、XFS等，但是XFS性能更高•EXT3/4系统的挂载参数：

配置文件：/etc/fstab
data=writeback|ordered|journal
wirteback对于InnoDB引擎来说是最好的
noatime和nodiratime用来禁止记录时间

6. Mysql体系结构

Mysql采用插件是存储引擎。Mysql客户端完成连接处理授权认证等相关的功能。Mysql服务层包括：连接管理器+查询缓存+查询解析+查询优化器，改层与存储引擎无关。Mysql存储引擎层：改成定义了一堆接口，用户可以开发第三方引擎。存储引擎是针对于表的而不是库的。

•MyISAM存储引擎

Mysql5.5之前版本默认使用该引擎。并且系统表和在排序分组操作当数量超过一定大小之后由查询优化器建立的临时表也使用MyISAM存储引擎。该引擎使用MYD和MYI组成。frm文件用来记录表结构。

优点：

并发性与锁级别：使用表级锁，读取表中数据时也需要全表加共享锁。
表损坏修复：支持对于任意关闭和其他情况导致的损坏修复，对MyISAM表进行数据恢复可能造成数据丢失，因为不支持事务。
使用 check table 表名称 来检查，使用 repair table 表名 来进行恢复。
支持全文索引，支持数据压缩，适用于只读操作的数据表，可以通过myisampack -b -f xxx.MYI进行压缩。
对于已经压缩的表只能读取。

缺点：

在Mysql5.0之前的版本中默认表大小为最大4GB。
存储大表则需要修改MAX_Rows和AVG_ROW_LENGTH参数。
在Mysql5.0以后，默认表大小支持256TB。
使用场景：
适用于非事务应用
适用于只读类应用
适用于空间类应用

•InnoDB存储引擎

InnoDB在Mysql5.5版本之后成为默认存储引擎。InnoDB是一种事务性的存储引擎， 也就是说InnoDB是支持事务的ACID特性的。InnoDB的设计更适合处理大量的小事务。InnoDB支持行级锁，行级锁可以更大限度的支持并发，行级锁是由存储引擎实现的。

InnoDB有自己的表空间:

具体参数配置在innodb_file_per_file。
参数值为on的时候：表空间为TableName.ibd。
参数值为off的时候：表空间存储在共享表空间：ibdataN,N表示一个数字。

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

在Mysql5.5版本之前的innodb_file_per_file默认是off。
在一个繁忙的系统中，系统表空间会增大，但是删除一部分无用的数据时系统表空间并没有缩小，要通过复制数据来解决，也就是说系统表空间无法简单的收缩文件。
系统表空间由于只有一个文件，多余多个表同时操作来说，系统表空间会产生IO瓶颈。
独立表空间可以同时向多个文件刷新数据。
在Mysql5.6之后，默认使用独立表空间进行管理，建议使用独立表空间。

如何将系统表空间转换为独立表空间？

使用mysqldump导出所有数据, 存储过程/触发器/视图也要导出。
使用停止Mysql服务，修改参数，删除InnoDB相关文件。
重启Mysql服务，重建InnoDB系统表空间。
重新导入数据。

什么是锁？

主要针对管理共享资源的并发访问而设计。
实现事务的隔离性。
分为共享锁（读锁）和独占锁（写锁）。
阻塞：一个锁需要等待另一个锁的释放就会导致阻塞。
死锁：两个锁及以上相互等待对方释放锁。

查看InnoDB状态检查：show engine innodb status。InnoDB适合用于大多数的OLTP（在线处理）应用。

•CSV存储引擎

以文件方式存储
csv文件存储表内容
csm文件存储元数据和数据量
frm文件存储表结构
CSV所有列不允许为NULL
不支持索引，不适合大表和OLTP
可以直接编辑
适用于作为数据交换的中间表

•Archive存储引擎

支持插入和查询操作
只允许在自增ID列上加索引
以zlib对数据表进行压缩，磁盘IO更少
数据存储在arz文件中
支持行级锁和标准的缓冲区，支持高并发插入
适用于日志和数据采集类的应用

•Memory存储引擎

也称之为Heap存储引擎，所以数据保存在内存中。
只有一个表结构frm文件。
支持Hash索引（默认，适用于等值查找）和BTree索引（适用于范围查找）。
所有字段都为定长。
不支持Blob和Text等大字段类型。
使用表级锁。
数据表的最大大小由max_heap_table_size参数决定， 对于已经存在的数据表是不生效的。
Memory存储引擎表和临时表的区别是临时表对于当前会话是可见的，而Memory临时表对于所有会话可见。
系统使用临时表如果没有超过限制将会使用Memory表否则使用MyISAM引擎的数据表。
会话临时表使用create temporary table来创建。
用于查找或者是映射表。
用于保存数据分析产生的中间数据。
用于缓存周期性聚合数据的结果表。

•Federated存储引擎

提供远程访问其他Mysql服务器上数据表的方法。
本地不保存数据，数据全部存放到远程服务器上。
本地需要保存远程表结构和远程服务器连接信息。
默认是禁止的，启动时需要指定参数federated
适用于偶尔的数据查询中
engine=federated connection='mysql://UserName[:PassWord]@HostName[:PortName]/DBName/TableName';
查看是否支持该存储引擎：

7. 如何选择存储引擎？

考察因素：事务、备份、崩溃恢复、特性。除非万不得已，否则不要混合使用存储引擎。

8. Mysql服务器参数

•Mysql获取配置信息路径

命令行参数：mysqld_safe --datadir=/data/sql_data。
配置文件， 查看配置文件读取顺序及位置：
mysqld --help --verbose | grep -A 1 'Default options' /etc/my.cnf

•Mysql配置参数的作用域

全局参数：set global 参数名=参数值;和set @@global.参数名 := 参数值;
会话参数：set [session] 参数名=参数值;和set @@session.参数名=参数值;

•内存相关的配置参数（需要时才会分配，而且是为每个线程分配的）

确定可以使用的内存上限。
确定Mysql的每个连接使用的内存。
sort_buffer_size设置排序需要的内存。
join_buffer_size设置连接缓冲区的内存。
read_buffer_size设置全表扫描时需要的内存大小。
read_rnd_buffer_size设置索引缓冲区的大小。
如何为缓存池分配内存：innodb_buffer_pool_size定义了InnoDB缓存池的大小。总内存-(每个线程所需要的内存 x 连接数)-系统保留内存key_buffer_size是为MyISAM缓存池设置大小的，该引擎只会缓存索引，数据还会依赖于操作系统的缓存通过下面的查询来确定MyISAM所占用的空间大小：select sum(index_length) from information_schema.tables where engine='myisam';

•IO相关的配置参数

InnoDB存储引擎：

innodb_log_file_size控制事务日志的大小。
innodb_log_files_in_groups控制事务日志的个数。
事务日志的总大小=innodb_log_file_size x innodb_log_files_in_groups。
innodb_log_buffer_size控制日志缓存区的大小。
innodb_flush_log_at_trx_commit控制刷新日志缓冲区的频率：0表示每秒进行一次log写入cache，并flush log到磁盘。1默认，表示在每次事务提交执行log写入cache，并flush log到磁盘。2建议使用该值，每次事务提交执行log数据写入到cache，每秒执行一次flush log磁盘。
innodb_flush_method=O_DIRECT,对于Linux系统建议使用该选项，避免操作系统和InnoDB都对数据进行缓存。
innodb_file_per_table=1, 控制InnoDB的表空间。
innodb_doublewrite=1, 控制是否使用双写缓存，启用后稍微影响性能，但是安全性提高。

MyISAN存储引擎：

delay_key_write：
off表示每次写操作后刷新键缓冲中的脏块到磁盘。
on表示只对键表时指定了delay_key_write选项的表使用延迟刷新。
all表示对所有MyISAM表都是用延迟键写入。

安全相关参数配置：

expire_logs_days指定自动清理binlog的天数。
max_allowed_packet控制Mysql可以接受的包大小。
skip_name_resolve禁用DNS查找。
sysdate_is_now确保sysdate()函数返回确定性日期。
read_only禁止非super权限的用户写入权限。
skip_slave_start禁用slave自动恢复。
sql_mode设置Mysql所使用的SQL模式，默认很宽松的:strict_trans_tablesno_engine_subtitutionno_size_dateno_zero_in_dateonly_full_group_by其他常用参数sync_binlog控制Mysql如何向磁盘刷新binlog，默认使用操作系统刷新策略。tmp_table_size和max_heap_table_size控制内存临时表大小。max_connections控制允许的最大连接数, 通常设置为2000。数据库的结构和SQL优化（影响最大）过分的反范式化为表建立太多的列。过分的范式化造成太多关联的表, 一般建议不超过10个关联表。在OLTP环境中使用不恰当的分区表。使用外键保证数据完整性。总体优化步骤：数据库表结构设计和SQL语句数据库存储引擎和参数配置的优化系统优化硬件升级

MySQL基准测试

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

基准测试和压力测试的区别：

•基准测试直接、简单、易于比较，用于评估服务器的处理能力。•压力测试对真实的业务数据进行测试，获得真实系统所能承受的压力。

基准测试的目的

建立Mysql服务器的性能基准线。模拟比当前系统更高的负载，以找出系统的扩展瓶颈。测试不同的硬件软件和操作系统配置。证明新的硬件设备是否配置正确。

基准测试的方法

对整个系统进行测试：

•能够测试整个系统的性能测试•直接反映出系统各个组件接口之前的性能问题•测试设计复杂，消耗时间长

对Mysql进行基准测试：

•测试简单，消耗时间短•无法全面了解整个系统的性能基线

常见的测试指标

•单位时间内处理的事务数（TPS）•单位时间内处理的查询数（QPS）•响应时间：

1.平均响应时间2.最小响应时间3.最大响应时间4.各时间所占百分比

•并发量：同时处理的查询请求的数量

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

•使用生产环境数据时只使用了部分数据•再多用户场景中，只做了单用户的测试•在单服务器上测试分布式应用要使用分布式架构测试•反复使用了同一查询

基准测试工具

•mysqlslap，在Mysql5.1之后自带的工具

--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指定测试表中CHAR类型列的数量。
--create-schema指定用于执行测试的数据库的名字。
--query用于自定义SQL的脚本。
only-print并不运行测试脚本，而是把生成的脚本打印出来。

•sysbench

安装：使用wget进行下载
使用unzip进行解压
进入解压目录
运行./autogen.sh脚本
运行./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
编译安装make && make install
常用参数：--test用于指定所要执行的测试类型：
fileio 用于文件系统的IO性能测试
cpu CPU性能测试
memory 内存性能测试
--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用于清理测试数据。

MySQL数据库结构优化

数据库结构优化的目的

尽量减少数据冗余。
尽量避免数据维护中出现的更新插入和删除异常。插入异常：如果表中的某个实体随着另一个实体而存在就会产生插入异常。更新异常：如果更新某个表中的某个实体的单独属性时，需要对多行进行更新。删除异常：如果删除某个表中的实体，也会导致其他实体被删除。
节约数据存储空间
提高查询的效率

数据库设计的步骤

需求分析：全面了解产品设计的存储需求
存储需求。
数据处理需求。
数据安全性和完整性。
逻辑设计：设计数据的逻辑存储结构
数据实体之家的逻辑关系，解决数据冗余和数据维护异常。
物理设计：
根据所使用的数据库特点进行表结构设计
维护优化：
根据实际情况对索引、存储结构等进行优化

数据库设计范式

•第一范式：

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

•第二范式：

要求一个表中只具有一个业务主键，也就是说符合第二范式的表不能存在非主键列对只对部分主键的依赖关系。

•第三范式：

每一个非主属性既不部分依赖也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主属性对主键的传递依赖。范式化的优点：

尽量的减少数据冗余 范式化操作比反范式化更快 范式化的表通常比反范式化更小

•范式化的缺点：

查询需要关联多个表 更难进行索引优化

•物理设计

定义表的命名规范

可读性原则
表意性原则
长名原则（尽量避免过多使用缩写）
选择合适的存储引擎

•字段数据类型的选择

当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

MySQL高可用架构设计

Mysql数据复制

实现在不同服务器上数据分布

利用二进制日志增量进行
不需要太多的带宽
但是使用基于行的复制在进行大批量的更改时会对带宽带来一定压力
特别是在跨IDC环境下进行复制
应该分批复制

实现数据读取的负载均衡

需要其他组件配合完成
利用DNS轮询的方式把程序的读连接到不同的备份数据库
使用LVS、HAProxy等代理方式

增加数据安全性

利用备库的备份来减少主库的负载
复制并不能替代备份

实现数据库高可用和故障切换

实现数据库在线升级

二进制日志相关问题

1. Mysql服务层日志二进制日志
慢查询日志
通用日志2. Mysql存储引擎层日志InnoDB的重做日志
InnoDB的回滚日志
二进制日志记录了对Mysql数据库的修改事件，包括增删改查事件和表结构修改事件。在binlog中记录的日志都是成功执行过的日志。3. 二进制日志的记录格式无论使用哪一种日志格式，进行DDL操作时都是使用基于段的日志格式。如果同一条SQL对N条数据进行修改，基于段的日志之后记录1条，基于行的会记录N条。SBR 基于段的格式binlog_format=SATEMENT（Mysql5.7之前默认使用的格式）优点：
日志量相对较小，节约磁盘网络IO。
并不强制要求主从数据库的表定义的完全相同。
相对于基于行的复制更加灵活。
缺点：
非确定性事件、存储过程、触发器、自定义函数、UUID()或user()等函数可能造成Mysql复制的主备服务器数据不一致。
需要更多地锁。RBR 基于行的日志格式binlog_format=ROW
优点：
使Mysql主从复制更加安全，在备用服务器上复制效率高，可以通过反向处理方式恢复某些数据。
可以应用于任何SQL的复制。可以减少从服务器上锁的使用。
缺点：
记录日志量大。binlog_row_image=[FULL|MINIMAL|NOBLOB]
要求主从数据库服务器的表结构一致，否则可能造成中断复制。
无法在从服务器上单独执行触发器，因为直接应用了SQL执行后的修改。SBR+RBR 混合日志格式binlog_format=MINED
根据SQL语句由系统决定基于段和基于行的日志格式进行选择
数据量大小根据所执行的SQL语句决定
建议选择混合日志格式，如果选择为ROW格式，建议设置binlog_row_image=MINIMAL

Mysql复制的工作方式

主服务器将数据的修改记录到主服务器的二进制日志中

从数据库服务器读取主服务器的二进制日志，保存在自己的relay_log中

在从服务器启动一个IO工作线程。
IO工作线程与主库建立普通的连接。
在主数据库服务器上启动特殊的二进制日志转储线程，成为binlog_dump线程。
然后从服务器读取主服务器上的事件，当从服务器追赶上主服务器的时候从服务器会进入sleep状态。

•根据复制日志的方式可以分为：

基于日志点的复制

在主数据库服务器建立复制账号：create user 'repl'@'IP地址段' identified by '密码';。
授权用户：grant replication slave on *.* to 'repl'@'IP地址段';。
主服务器配置BinLog：bin_log=存储目录。
主服务器配置ID：server_id=100。
从服务器配置BinLog：bin_log=存储目录。
从服务器配置ID：server_id=101。
从服务器配置RelayLog：relay_log=存储目录。
从服务器配置LogSlaveUpdate:log_slave_update=on可选。
从服务器配置ReadOnly:read_only=on可选。
常用的工具：mysqldump、xtrabackup。
从服务器启动复制链路：change master to master_host='主服务器IP地址', master_password='主服务器密码', master_log_file='主服务器日志文件名称', master_log_pos=偏移量;。
主服务器日志文件名称和偏移量在主服务器中导出的sql文件中可以获得。

基于GTID的复制(GTID：全局事务ID，由source_id:transaction_id构成，保证为每一个在主服务器上提交的事务在复制集群中可以生成一个唯一的ID)

从服务器首先会告诉主服务器从服务器已经执行的事务的GTID值。
主服务器返回给从服务器没有执行的GTID值。
同一个事务只在指定的从库执行一次。
主服务器配置BinLog：bin_log=存储目录。
主服务器配置ID：server_id=100。
主服务器配置GTID：gtid_mode=on。
主服务器配置：enforce-gtid-console和log-slave-updates=on。
从服务器配置ID：server_id=101。
从服务器配置RelayLog：relay_log=存储目录。
从服务器配置:gtid_mode=on和enforce-gtid-consistency。
从服务器配置:log-slave-updates=on建议。
从服务器配置:read_only=on建议。
从服务器配置:master_info_repository=表名建议。
从服务器配置:relay_log_info_repository=表名建议。
常用的工具：mysqldump、xtrabackup。
从服务器启动复制链路：change master to master_host='主服务器IP地址', master_password='主服务器密码' , master_auto_position=偏移量;。

从服务器读取relay_log进行重放

基于SQL段的日志是在从数据库上重新执行SQL
基于行的日志是在从数据库上直接应用修改

基于日志点复制的优缺点

是Mysql最早使用的复制技术，Bug相对较少
对SQL查询没有任何限制
故障处理比较容易
故障转移时重新获取新主服务器日志点信息比较困难

基于GTID复制的优缺点

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

如何选择复制模式

所使用的Mysql版本，Mysql5.6+以上可以使用GTID。
复制架构以及主从切换的方式。
所使用的高可用管理组件。
应用的支持程度。

Mysql复制拓扑架构

一主多从复制拓扑

特点：
配置简单。
可以用多个从库分担读负载。
用途：
为不同业务使用不同的从库。
将一台从库放到远程IDC中，用于灾备恢复。
分担主库的读负载。

主主复制拓扑（主备方式和主主方式）

主主方式注意事项：两台同时提供服务
两个主中所操作的表最好能够分开
配置一下两个参数:auto_increment_increment=2 // 控制自增列ID步长和auto_increment_offest=1|2 // 控制自增列ID起始数值，一台为1，一台为2
主备方式注意事项：只有一台服务器对外服务
一台处于只读或者是热备状态
确保两台服务器上的初始数据相同
确保两台服务器上已经启动binlog并且有不同的server_id。
在两台服务器上启用log_slave_updates参数。
初始化的备库上启用read_only参数。

级联复制

在主从数据库之间建立分发主库，主数据库数据同步到分发主库，分发主库向多个从库分发。

Mysql复制性能优化

主从延迟问题

主库写入binlog的时间，要控制主库事务大小，分隔大事务。binlog的传输时间，要使用mixed日志格式或者设置set binlog_row_image=minimal; 默认情况下从数据库只有一个SQL线程，主服务器上并发的修改在从数据库变成了串行，因此可以使用多线程复制。在Mysql5.7中可以按照逻辑时钟的方式来分配SQL线程。

stop slave;
set global slave_parallel_type='logical_clock';
set global slave_parallel_workers=4;
start slave;

Mysql复制常见问题

由于数据损坏或丢失所引起的主从复制错误主库或从库以外宕机引起的错误使用跳过二进制日志事件注入空事务的方式先恢复中断的复制链路使用其他方法来对比主从服务器上的数据
主库上的二进制日志文件损坏通过change master命令来重新指定检查修复数据
从库上中继日志损坏
在从库上进行了数据修改
不唯一的server_id和server_uuid, 比如多个从库使用相同的server_uuid问题
max_allow_packet设置引起的主从复制错误

高可用架构

高可用是指通过尽量缩短因为日常维护（计划）或者是突发的系统崩溃（非计划）所导致的停机时间，以提高系统和应用的可用性。通常使用服务器正常可用的时间和全年时间产生的百分比来表示高可用程度。

造成不可用的常见因素：

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

高可用手段

建立完善的监控报警系统
对备份数据进行恢复测试
正确配置数据库环境
对不需要的数据进行归档和清理

如何增加系统的冗余？

解决系统的单点故障
利用SUN共享存储解决
DRDB（以Linux内核模式块级别的同步复制技术）磁盘复制解决
使用多写集群，目前主要是Percona公司提供的PXC集群，所有事务提交以后才算提交完成，取决于性能最差的服务器
使用NDB集群，所有节点都进行主主复制，可以在任何节点进行写入。目前NDB集群的数据都要求存储在内存中。
利用Mysql主从复制来解决，可以使用第三方的复制管理组件主库切换以后如何通知应用新的主库的IP地址如何检查Mysql主库是否可用如何处理从库和新的主库之间的复制关系

MMM(Multi-Master Replication Manager)

主要作用就是监控和管理Mysql主主复制拓扑，并在当前的主服务器失效时，进行主和主备库之间的主从切换和故障转移。
特点：
MMM中同一时间只有一个主库是对外服务的。
MMM可以监控Mysql主从复制健康情况。
在主库出现宕机时进行故障转移并自动配置其他从库对新主库的复制。
MMM提供了主写虚拟IP，在主库出现问题时可以自动迁移虚拟IP。

架构图：

部署步骤

配置主主复制以及主从同步集群
安装主从节点所需要的支持包
安装及配置MMM工具集
运行MMM监控服务
测试

特点

[优点]使用Perl语言开发，完全开源
[优点]提供了读写VIP，是服务器角色变更时对前端应用透明
[优点]MMM提供了从库延迟监控
[优点]提供了主库故障转移后服务器对新库的重新同步功能
[优点]同时监控多个从库
[缺点]发布时间早导致Mysql新的复制功能支持不够
[缺点]MMM对于Mysql5.6+的多线程同步不支持
[缺点]对于写负载较大的情况下可能会出现读VIP全部迁移到主服务器上
[缺点]没有读负载均衡
[缺点]在主从切换时，容易造成数据丢失
[缺点]MMM监控服务存在单点故障

MHA(Master High Availability)

MHA基本上可以在30s之内完成主从切换，最大程度的保证数据一致性。
只能监控主库是否可用。
当主库不可用时，可以在多个从库中选出新的主库。
提供主从切换和故障转移，尝试在故障机器保存二进制日志。主从切换的过程：
应用从原生DB服务器上保存的二进制日志。
提升备选主DB服务器为新的主DB服务器。
迁移集群中其他的从DB作为新的主DB的从服务器。部署步骤：
配置集群内所有主机的SSH免认证登录。
配置主从复制集群(推荐基于GTID的复制)。
配置HMA管理节点。
对配置进行检验。特点：
[优点]同样适用Perl语言开发的开源工具, 也提供其他语言的接口。
[优点]可以支持基于GTID的复制模式。
[优点]在故障转移时更不易产生数据丢失。
[优点]同一个监控节点可以监控多个集群。
[缺点]没有VIP的实现，需要自己开发相关脚本。
[缺点]需要基于SSH免密登录，存在安全隐患。
[缺点]没有提供多个从库的负载均衡的功能。

读写分离与负载均衡

为什么要进行读写分离？因为写操作只能在主库上进行，而读操作可以迁移到从库。方式：由开发人员控制：直接控制什么样的查询在从库进行，因此比较灵活，由于程序直接连接数据库，因此性能损耗低。但是也加大了工作量。人为控制可能出错较多。
中间件实现：比如mysql-proxy(高并发情况下可能会崩溃)或maxScale(由MariaDB公司提供)。中间件可以根据SQL的语法分析出世读操作还是写操作来实现负载均衡，但是存储过程等并不能分析出来。使用中间件对程序是透明的，程序不需要进行调整。数据库查询处理能力依赖于中间件。对延迟敏感业务无法自动在主库中执行。
读写分离实现了，那么下一步就是读负载均衡了。负载均衡在软件上可以使用LVS、HAProxy、MaxScale，硬件可以使用F5。

BTree索引/Hash索引

BTree索引

使用B+树存储数据
B+树索引能够加快数据的查询的速度
更加适合进行范围查找什么情况下用到BTree索引:
全值匹配的查询
匹配最左前缀的查询
匹配列前缀查询
范围查找
精确匹配左前列并范围匹配另外一列
只访问索引的查询

BTree索引的限制

如果不是按照索引最左列开始查找，那么无法使用索引。
使用索引时不能跳过索引中的列。
not in、<>、!=操作无法使用索引。
如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引。

Hash索引

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

Hash索引的限制

Hash索引中包含的只是Hash码与行指针，因此必须进行二次查找。Hash索引的建立是由Hash码构成的，因此Hash索引无法用于排序。Hash索引不支持部分索引查找也不适合范围查找。Hash索引中Hash码的计算可能存在Hash冲突。

为什么使用索引

索引可以减少存储引擎需要扫描的数据量。索引可以帮助我们进行排序以避免临时表。索引可以把随机IO变为顺序IO。

索引的性能成本

由于在写入数据时也要维护索引，因此索引会增加写操作的成本。太多的索引会导致查询优化器的时间，因为查询优化器要在很多索引中选择出最合适的索引。

索引优化策略

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

对于InnoDB来说，索引列大小限制767Byte，对于MyISAM来说是1000Byte。

前缀索引或索引列的选择性，索引的选择性是不重复的索引值和表的记录数的比值。

建立联合索引如何选择索引列的顺序？

经常会被使用到的列优先。选择性高的列优先。宽度小的列优先使用。覆盖索引，包含需要查询的所有行的值

可以优化缓存，减少磁盘IO 可以减少随机IO，变成顺序IO 可以避免对InnoDB主键索引的二次查询 可以减少MyISAM表进行系统调用 无法使用覆盖索引的情况

存储引擎不支持覆盖索引 查询中使用了太多的列 使用了双%号的like查询 使用索引扫描来优化排序

索引的列顺序和order by子句的顺序完全一致。索引红所有列的升序降序和order by子句完全一致。order by中字段全部在关联表中的第一张表中。使用BTree索引模拟Hash索引优化查询

只能处理键值的全值匹配查找。所使用的Hash函数决定着索引键的大小。利用索引优化锁

索引可以减少锁定的行数。索引可以加快处理速度，同时也加快了锁的释放。删除重复和冗余的索引

primary key(id), unique key(id), index(id) index(a), index(a,b) primary key(id), index(a,id) 使用工具pt-duplicate-key-checker h=127.0.0.1来检查 更新索引统计信息及减少索引碎片

analyze table 表名，InnoDB存储引擎执行该命令不会锁表只是粗略估算值。optimize table 表名，使用不当会导致锁表。

SQL查询优化

通过用户反馈获取存在性能问题的SQL。通过慢日志获取存在性能问题的SQL。

在Mysql配置参数中指定slow_query_log。启动停止记录慢查询日志。
在Mysql配置参数中指定slow_query_log_file。指定慢查询日志记录存储路径及文件。
在Mysql配置参数中指定long_query_time。指定记录慢查询日志SQL执行时间的阈值。默认值为10秒。通常改为0.0001秒比较合适。
在Mysql配置参数中指定log_queries_not_using_indexs。是否记录未使用索引的SQL。
使用慢日志查询分析工具mysqldumpslow。
使用慢日志查询分析工具pt-query-digest。

实时获取存在性能问题的SQL

利用information_schema数据库中的processlist表。

Mysql处理查询请求的过程

客户端发送SQL请求给服务器

服务器检查是否可以在查询缓存中命中该SQL，通过对大小写敏感的哈希查找实现的。

query_cache_type指定查询缓存是否可用。在一个比较繁忙的系统中建议关闭查询缓存。
query_cache_size指定查询缓存的内存大小。
query_cache_limit指定查询缓存可用存储的最大值。
query_cache_wlock_invalidate指定数据表被锁定以后是否返回缓存中的数据。
query_cache_min_res_init指定查询缓存分配的内存块最小单位。

服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划

•通过关键字进行SQL的解析工作， 并生成一棵解析树。•Mysql解析器将使用Mysql的语法规则进行验证和解析查询，这包括检查语法是否使用了正确的关键字或者关键字的顺序是否正确。•造成Mysql生成错误的查询计划的原因：

统计信息的不准确
执行计划的成功估算并不等于实际的执行计划的成本，因为Mysql并不知道哪些页面在磁盘上，哪些页面在内存上，哪些需要顺序读取，哪些需要随机读取。
Mysql查询优化器所认为的最优化的解决方案并不是开发者认为最优化的。因为Mysql只是基于成本模型选择最优的执行计划。
Mysql不会考虑其他的并发查询，可能会影响当前的查询速度。
Mysql有时候也会基于某些固定的规则来生成执行计划。
Mysql不会考虑不受控制的成本的，比如存储过程，用户定义的函数。

•Mysql可以优化的SQL类型

重新定义表的关联顺序，因为Mysql优化器会根据统计信息来决定表的关联顺序。
将外连接转换为内连接。
使用等价的规则变换。
优化count()、min()、max()等函数进行优化。
将一个表达式转换为一个常数。
子查询优化。
提前终止查询。
对in()条件进行优化。

•根据执行计划，调用存储引擎API来查询数据

如何确定Mysql在各个阶段所消耗的时间？
使用profile方法
使用set profiling=1启动profile，这是一个session级别的设置
执行查询
通过show profiles来查看每一个查询所消耗总时间的信息
通过show profile for query ID来查询第ID个阶段所消耗的时间
使用information_schema引擎
在Mysql5.5-开销较大，因此需要在配置文件修改。在Mysql5.7+以后就可以了。

执行两个SQL语句：

update `setup_instruments` set enabled='YES', TIMED='YES' where name like 'stage%';
update `setup_consumers` set enabled='YES' where name like 'events%';

将结果返回给客户端

优化特定的SQL

更新或插入多条数据

分小批次更新或插入

修改大表的结构

先在从库修改，切换主从库，再修改主库，再切换回去。在主库上创建新表，将旧表中的数据导入新表，然后在旧表中设置触发器，进行同步。然后在旧表加入排它锁，重新命名新表。通过pt-online-schema-change工具实现。

优化not in和 <>查询

使用连接优化

维护统计数据 使用汇总表进行查询优化， 每次凌晨可以维护这个表。

分库分表

把一个实例中的多个数据库拆分到多个数据库实例中
把一个实例的数据库的表分离到不同实例的数据库中
对一个数据表进行分片水平拆分到不同的数据库实例中
1, 如何选择分区键
尽可能避免数据的跨分区查询的发生
分片键尽可能保证每个实例的数据是均衡的
2.如何存储不用分片的表
每个分片存储一份相同的数据，使用多写的方式更新数据
使用额外节点统一存储
3, 如何部署分片
每个分片使用单一的数据库，并且数据库名也相同
将多个分片的数据表存储在一个数据库中，并在表名中加入分片号后缀
在一个节点读个数据库，每个数据库包含一个分片
4. 如何分配分片的数据
通过分区键的Hash值取模来分配分片数据
按照分区键的范围来分配分片的数据
利用分区键和分区的映射表的方式来分配分片的数据，由于该映射表存在很大的读取压力，因此可以缓存该映射表
5. 如何生成分片以后的全局唯一ID
使得auto_increment_increment的值等于分片的数量，auto_increment_offset设置为不同的值
使用全局节点的方式来生成ID，但是该节点也会承受较大压力
在redis等缓存服务器中创建全局ID
使用oneProxy进行定制化开发

数据库监控

对数据库服务可用性进行监控

通过使用mysqladmin -umonitor_user -p -h ping来确认是否可以建立网络连接
使用telnet ip db_port来确认是否可以建立网络连接
通过检查数据库配置文件read_only来确认是否可以读写

对数据库性能进行监控

通过show variables like 'max_connections';获取最大连接值
通过show global status like 'Threads_connected';获取已经连接的线程数
通过以上两个值的百分比确定是否报警

对主从复制进行监控

通过检查show slave status;来判断Slave_IO_Running和Slave_SQL_Running监控主从复制链路的状态。
监控主从复制延迟, 检查Seconds_Bebind_Master，但是数据不是特别准确。
通过在主库上获取日志文件名和偏移量和已经在主上传输完成的日志文件名和偏移量获取精确的值。
通过pt-table-checker来验证主从复制的数据是否一致。

对服务器资源进行监控

版权声明：

本文为大数据技术与架构整理，原作者独家授权。未经原作者允许转载追究侵权责任。

编辑｜冷眼丶

微信公众号｜import_bigdata

欢迎点赞+收藏+转发朋友圈素质三连

文章不错？点个【在看】吧！ ????