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

大数据技术与架构

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

大数据真好玩

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

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

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

大表导致的问题

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

大表解决方案

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

大事务导致的问题

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

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

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

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

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

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

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

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

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

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

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

大事务解决方案

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

影响性能的几个因素

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

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

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

2. 磁盘的配置和选择

•使用传统机械硬盘

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

如何选择机械硬盘：

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

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

主要的RAID级别：

如何选择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系统的优化：

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

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

6. Mysql体系结构

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

•MyISAM存储引擎

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

优点：

缺点：

•InnoDB存储引擎

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

InnoDB有自己的表空间:

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

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

什么是锁？

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

•CSV存储引擎

•Archive存储引擎

•Memory存储引擎

•Federated存储引擎

7. 如何选择存储引擎？

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

8. Mysql服务器参数

•Mysql获取配置信息路径

•Mysql配置参数的作用域

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

•IO相关的配置参数

InnoDB存储引擎：

MyISAN存储引擎：

安全相关参数配置：

MySQL基准测试

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

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

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

基准测试的目的

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

基准测试的方法

对整个系统进行测试：

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

对Mysql进行基准测试：

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

常见的测试指标

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

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

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

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

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

基准测试工具

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

•sysbench

MySQL数据库结构优化

数据库结构优化的目的

数据库设计的步骤

数据库设计范式

•第一范式：

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

•第二范式：

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

•第三范式：

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

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

•范式化的缺点：

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

•物理设计

定义表的命名规范

•字段数据类型的选择

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

MySQL高可用架构设计

Mysql数据复制

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

实现数据读取的负载均衡

增加数据安全性

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

实现数据库在线升级

二进制日志相关问题

Mysql复制的工作方式

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

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

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

基于日志点的复制

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

从服务器读取relay_log进行重放

基于日志点复制的优缺点

基于GTID复制的优缺点

如何选择复制模式

Mysql复制拓扑架构

一主多从复制拓扑

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

级联复制

Mysql复制性能优化

主从延迟问题

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

Mysql复制常见问题

高可用架构

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

造成不可用的常见因素：

高可用手段

如何增加系统的冗余？

MMM(Multi-Master Replication Manager)

架构图：

部署步骤

特点

MHA(Master High Availability)

读写分离与负载均衡

BTree索引/Hash索引

BTree索引

BTree索引的限制

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。

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

利用information_schema数据库中的processlist表。

Mysql处理查询请求的过程

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

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

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

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

•Mysql可以优化的SQL类型

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

执行两个SQL语句：

将结果返回给客户端

优化特定的SQL

更新或插入多条数据

分小批次更新或插入

修改大表的结构

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

优化not in和 <>查询

使用连接优化

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

分库分表

数据库监控

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

对数据库性能进行监控

对主从复制进行监控

对服务器资源进行监控