MySQL性能优化基础

MySQL性能影响的分析

服务器的硬件
服务器系统的不同
数据库存储引擎的选择（MyISAM(不支持事务，表级锁)、InnoDB（事务级存储引擎，完美的支持行级锁，事务ACID特性））。
数据库参数的配置
数据库结构的设计和SQL语句的优化

RAID

磁盘冗余队列，其实就是把多个容量较小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术。

高并发的场景下CPU的数量比主频更重要，而对于CPU密集型场景和复杂的SQL频率越高越好。

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 :TCP 链接处理的等待时间

1 2	net.ipv4.tcp.tw.reuse = 1 net.ipv4.tcp.tw.recyle = 1

上面这两个呢就是为了加快TCP的回收

net.core.wemem_default = 87380
net.core.wemem_max = 16777216
net.core.rmem_default = 87380
net.core.rmem_max = 16777216

上面四个参数就是设置TCP接收和发送的峰值，设定的大一些

1
2
3

net.ipv4.tcp_keepalive_time = 120     //这是表示tcp发送keepalive的时间间隔(s)
net.ipv4.tcp_keepalive_intvl = 30 //探测消息未响应的时候重发消息的时间间隔
net.ipv4.tcp_keepalive_probes = 3//发送几个keepalive无响应就可以认定TCP链接无效

上面三个参数用于减少失效TCP连接占用的系统资源，尽量小。

内存参数

kernel.shmmax = 4294967295

这个参数是用于定义单个内存共享段的最大值。尽量大以便能容纳下整个Innodb缓冲池的大小。

vm.swappiness = 0

内存不足时对系统性能影响的大小。这个参数就表示除非我的Linux参数占满了否则就不使用交换分区。使用交换分区就是把一部分数据写到硬盘上，肯定会很慢，但是不使用交换分区的话一旦发生内存溢出系统崩溃。

资源限制

（/etc/security/limit.conf）

打开文件数的限制

1 2	* soft nofile 65535 * hard nofile 65535

把这两个添加到limit.conf末尾就可以了，这就把文件的限制数量提高了

磁盘调度策略

noop(电梯式的调度策略)
deadline（截止时间调度策略）
anticipatory（预料I/O调度策略）

Innodb引擎的特性

Innodb通过使用Redo_log和undo_log实现了系统的原子性，一致性和持久性

然后通过锁实现了事务的隔离性

锁：分为共享锁和独占锁，共享锁（读锁）可以并发访问，独占锁（写锁）在执行一个写操作的时候其他的写操作会被阻塞。

锁的粒度

表级锁

开销最小的锁，但是一般开销越小的话，并发性能就差

行级锁

能够最大程度的支持并发，但是开销也大

阻塞和死锁

阻塞是一个链接阻塞其他连接的访问，一般若是发生了大量的阻塞的时候就是系统出现问题

而死锁是两个及以上的连接相互占用了对方等待的资源而造成的数据库的不能访问。死锁是可以有系统自动处理的，就是系统会自动选择一个占用资源最小的锁进行释放。

MySQL服务器参数

MySQL配置参数的作用域

全局参数

1 2	set global 参数名=参数值 set @@global.参数名：参数值

会话参数

1 2	set [session] 参数名=参数值 set @@session.参数名：参数值

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

过分的反范式化为表建立太多的列
过分的范式化造成太多表的关联
在OLTP环境中使用了不同的分区表（分区键的设计会影响分区查询，若是跨很多个分区的话就容易造成查询过慢）
使用外键保证数据的完整性，这样的话其实修改的时候需要先对外键进行查询，就会导致额外的开销。备份恢复也会有问题。

数据库的设计范式

第一范式：

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

第二范式

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

比如一个表中有学号和课程名称两个主键组成的复合主键，然后有一列的数据如学分这个非主键，他只和课程名称这一个主键有关，这就是非主键只对部分主键有依赖。不符合第二范式的设计要求的。

第三范式

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

举个例子，一个表中一个学生的学号可以查询到学院的名称和学院的电话，但是学院的名称和学院的电话和学院是相关的，因此存在传递依赖。解决的话就是将学院信息的这个表单独拆分出来。

数据库的反范式化设计

有的时候遵循范式化的设计，会导致很多表的拆分和关联，这样的话查询效率会很慢，因此有的时候为了性能和读取效率的考虑需要进行一些反范式化的设计，允许存在少量的数据冗余，其实反范式化的设计就是用空间来换取时间。

物理设计

为表中字段选择合适的数据类型

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

VARCHAR和CHAR类型：

VARCHAR用于可变长字符串，存储空间大小是以字符为存储单位的，只占用必要的存储空间。一般适用于字符串列的最大长度比平均长度大很多，字符串列很少被更新和使用了多字节字符集存储字符串。

CAHR是定长的（最大255字节），字符串存储在CAHR类型的列中会删除末尾的空格。适合存储长度近似的值，存储短字符串，存储经常更新的字符串列。

存储日期格式

DATATIME类型：与时区无关，占用8字节的存储空间

TIMESTAMP类型：只占用4字节，存储的时间范围是1970-01–1—2038-01-19，显示依赖于多指定的时区，在行的数据修改时可以自动修改timeStamp列的值

date:只占用3字节，还可以用时间函数进行计算。

time:存储时分秒微秒

MySQL复制功能

MySQL复制功能提供分担读负载并且可以实现不同服务器上数据库的高可用和故障切换。

并且MySQL的复制是用二进制日志增量进行的

MySQL二进制日志

二进制日志的格式

基于段的格式 binlob_format= STATEMENT:

优点是：日志记录量性对较小，节约磁盘及网络IO

缺点是：可能造成MySQL复制的主备服务器数据不一致

1	必须要记录上下文的信息，保证语句在服务器上执行结果和在主服务器上执行的结果相同，但是对于UUID这种函数可能还是无法复制。

基于行的日志格式binlog_format=ROW

优点是：MySQL的主从复制更加安全。

1	对每一行数据的修改比基于段的复制高效

缺点：记录的日志量较大；

混合日志格式（混合了基于行和基于段的日志格式）binlog_format=MIXED

特点是：根据SQL语句由系统决定在基于段和基于行的日志格式中进行选择

1	数据量的大小有所执行的SQL语句决定的。

对复制的影响

基于SQL语句的复制（基于段的格式的日志）：

优点：

生成的日志量少，节省网络传输IO
不强制要求主从数据库的表定义完全相同
相比于基于行的复制更加灵活

缺点：

对于非确定性事件，无法保证主从复制数据的一致性
对于存储过程，触发器，自定义函数进行的修改也可能造成数据不一致。
相比于基于行的复制方式在从执行时需要更多的行锁

基于行的复制（使用基于行的日志格式）

优点：

可以应用于任何SQL的复制包括非确定性函数，存储过程等
可以减少数据库锁的使用

缺点：

要求主从数据库的表结构相同，否则可能会中断复制
无法再从服务器上单独执行触发器

MySQL的复制方式

步骤：

主服务器将变更写进二进制日志
从读取主的二进制日志变更并写入到relay_log中
在从服务器上重放relay中的日志

方式：

基于日志点的复制配置
基于gtid的复制：GTID就是全局事务ID，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID

优点：

可以很方便的进行故障转移
从库不会丢失主库上的任何修改

缺点：

故障处理比较复杂

MySQL复制拓扑

一主多从的复制拓扑（为不同业务使用不同的从库，从库可以作为灾备恢复）

优点：

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

主主复制拓扑（主主模式下的主主复制拓扑：数据冲突和数据丢失，两个表中的数据最好能分开）

可以使用两个参数来控制自增ID的生成，一个2468，一个1357这样增长。（auto_incrment_increment = 2 auto_increment_offset = 1|2）

主备模式下的主主复制模式：只有一台主服务器对外提供服务，另外一台处于只读并只作为热备使用。一般可以作为高可用来做。

注意事项：

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

有备库的主主复制模式
级联复制（存在一个分发主库，其实就是一个分发主机的二进制日志的从库）

MySQL复制性能优化

影响主从延迟的因素

主库写入二进制日志的时间

优化：控制主库事务的大小，分割大事务

二进制日志的传输时间

优化：使用MIXED日志格式：设置 set binlog_row_image=minimal;

从上默认只有一个SQL线程，主上并发的修改在从上变成了串行

优化：使用多线程复制

stop slave;

set global_parallel_type = 'logical_clock'就是使用逻辑时钟的方式来实现多线程复制

set global slave_parallel_workers = 4 设置线程的数量

start slave

MySQL 高可用

避免导致系统不可用的因素导致的系统不可用的时间

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

增加系统冗余，保证发生故障时可以尽快恢复

避免存在单点故障

使用SUN共享存储或DEDB磁盘复制

使用多写集群（Percona）或NDB集群(大内存)

使用主从复制

主从切换及故障转移

MMM

管理主主复制拓扑结构，并工作在主备复制模式下。

监控MySQL主从复制健康情况

在主库出现宕机时进行故障转移并自动配置其他从对新主的复制

提供了主写虚拟IP，在主服务器出现问题时可以自动迁移虚拟IP

配置所需的资源：

MHA

监控主数据库服务器是否可用
当主DB不可用时，从多个服务器中选举出新的主数据库服务器
提供了主从切换和故障转移功能

MHA主从切换的过程：

尝试从出现故障的主数据库保存二进制日志
从多个备选服务器中选举出新的备选主服务器
在备选主服务器和其他的从服务器之间同步差异二进制数据
应用从原主DB服务器上保存的二进制日志
提升备选主DB服务器作为新的主DB服务器

优点：

可以支持GTID的复制模式
在故障转移的时候更不易产生数据丢失
同一个节点可以监控多个集群

但是没有提供负载均衡的操作

MaxScale实现读写分离和负载均衡

读写分离是分析哪些语句需要在哪些数据库上执行，尽量的写操作在主库上进行，读操作尽量在从数据库上执行。

读的负载均衡是尽量的让读操作分散在多个从数据库上执行。

MySQL支持的索引类型

B-tree索引

B-tree索引的特点

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

使用场景：

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

使用限制

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

Hash索引

特点：

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

使用限制

Hash索引必须进行二次查找
Hash索引无法用于排序查找
Hash索引不支持部分索引查找也不支持范围查找
产生Hash冲突，产生查询错误

MySQL索引优化

索引上不能使用表达式或函数
索引长度过长时可以使用前缀索引（索引的选择性：指的是不重复的索引值和表达记录数的比值）
联合索引

顺序选择：

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

覆盖索引（包含了查询字段的所有值的索引，就是在使用B-tree索引时通过索引的键值还能够查询到该索引对应的值，Hash索引不能用覆盖索引），使用覆盖索引可以优化缓存，减少磁盘IO操作，也可以减少随机IO，把随机IO改为顺序IO的操作，避免对主键的二次查询。
使用索引来优化查询

使用索引扫描来优化排序：

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

使用Hash索引优化查询，当搜索引擎不支持Hash索引的时候可以使用B-tree索引来模拟Hash索引。

使用索引优化锁

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

删除重复和冗余的索引

可以使用pt-duplicate-key-checker h=127.0.0.1实现重复和冗余的查询

清理未被使用过的索引
更新索引信息和减小索引的碎片

获取有性能问题的SQL

慢查询日志获取存在性能问题的SQL

默认是不启动的

slow_query_log    启动停止记录慢查日志
slow_query_log_file 指定慢查日志的存储路径及文件
long_query_time  指定记录慢查日志SQL执行时间的阀值
long_queries_not_using_indexs  是否记录未使用索引的SQL

分析工具：

mysqldumpslow
pt-query-digest

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

information_schema 和PROCESSLIST表的方式通过执行SQL语句然后查询哪一些语句执行到的时间过长，找到性能问题的存在的地方。
MySQL优化器可以优化的SQL类型

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

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

使用performance_schema

数据库的分库分表

方式

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

其实就是把一个MySQL节点（MySQL集群）的数据库拆分到其他节点上去。

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

数据库表的水平拆分

尽量不分片

选择分区键

分区键要尽量避免跨分片查询
分区键要尽量保证各个分片内的数据量是均匀的

存储无需分片的表

每个分片中存储一份相同的数据

如何在节点上部署分片

每个分片使用单一数据库，并且数据库名字相同
将多个分片表存储在一个数据库中

如何分配分片中的数据

按分区键的Hash值取模来分配

分片工具：oneProxyp

数据库监控

数据库可用性监控

是否可以通过网络连接

在远程服务器执行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’

可以对上面的两个参数设置一个阈值，进行预警

性能监控

计算QPS和TPS

监控并发请求数量

show global status like ‘Threads_running’

监控Innodb的阻塞

可以使用SQL语句来查询被阻塞线程阻塞的时间，当大于某个值的时候就可以认为阻塞了。

主从复制监控

监控链路状态

检测从数据库的IO状态

监控主从复制的延迟

show glave status 这种方式不准确的
show master staus查询主服务器上的二进制日志文件的偏移量，然后在从服务器上执行show glave status获取用户栈的信息

主从复制数据一致性检查

使用pt-table-checksum工具