MySQL高级篇-12-数据库的其他调优策略

数据库调优的措施

调优的目标

• 尽可能 节省系统资源 ，以便系统可以提供更大负荷的服务（吞吐量更大）
• 合理的结构设计和参数调整，以提高用户操作 响应的速度 （响应速度更快）
• 减少系统的瓶颈，提高MySQL数据库整体的性能

如何定位调优问题

用户的反馈（主要）

用户是我们的服务对象，因此他们的反馈是最直接的。虽然他们不会直接提出技术建议，但是有些问题往往是用户第一时间发现的。我们要重视用户的反馈，找到和数据相关的问题。

日志分析（主要）

可以通过查看数据库日志和操作系统日志等方式找出异常情况，通过它们来定位遇到的问题。

服务器资源使用监控

通过监控服务器的CPU、内存、IO等使用情况，可以实时了解服务器的性能使用，与历史情况进行对比。

数据库内部状况监控

在数据库的监控中，活动会话（Active Session）监控是一个重要的指标。通过它，你可以清楚地了解数:据库当前是否处于非常繁忙的状态，是否存在SQL堆积等。

其它

除了活动会话监控以外，也可以对事务 、 锁等待 等进行监控，这些都可以帮助我们对数据库的运 行状态有更全面的认识。

调优的维度和步骤

需要调优的对象是整个数据库管理系统，它不仅包括 SQL 查询，还包括数据库的部署配置、架构等。从这个角度来说，思考的维度就不仅仅局限在 SQL 优化上了。

选择适合的 DBMS

如果对事务性处理以及安全性要求高的话，可以选择商业的数据库产品。这些数据库在事务处理和查询性能上都比较强，如果数据表设计得好，即使不采用分库分表的方式，查询效率也不差。

除此以外，可以采用开源的MySQL进行存储，它有很多存储引擎可以选择，如果进行事务处理的话可以选择InnoDB，非事务处理可以选择MylSAM。

NoSQL阵营包括键值型数据库、文档型数据库、搜索引擎、列式存储和图形数据库。这些数据库的优缺点和使用场景各有不同，比如列式存储数据库可以大幅度降低系统的IO，适合于分布式文件系统，但如果数据需要频繁地增删改，那么列式存储就不太适用。

优化表设计

RDBMS中，每个对象都可以定义为一张表，表与表之间的关系代表了对象之间的关系。如果用的是MySQL，我们还可以根据不同表的使用需求，选择不同的存储引擎。除此以外，还有一些优化的原则可以参考：

• 表结构要尽量遵循三范式的原则。这样可以让数据结构更加清晰规范，减少冗余字段，同时也减少了在更新，插入和删除数据时等异常情况的发生
• 如果查询应用比较多，尤其是需要进行多表联查时可以采用反范式进行优化。反范式采用空间换时间的方式，通过增加冗余字段提高查询的效率
• 表字段的数据类型选择，关系到查询效率的高低以及存储空间的大小。一般来说，如果字段可以采用数类型就不要采用字符类型；字符长度要尽可能设计得短一些。针对字符类型来说，当确定字符长度固定时可以采用CHAR类型；当长度不固定时通常采用VARCHAR类型。

优化逻辑查询

SQL查询优化，可以分为逻辑查询优化和物理查询优化。逻辑查询优化就是通过改变SQL语句的内容让SQL执行效率更高效，采用的方式是对SQL语句进行等价变换，对查询进行重写。
SQL的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。

栗子

比如我们在讲解EXISTS子查询和IN子查询的时候，会根据小表驱动大表的原则选择适合的子查询。在WHERE子句中会尽量避免对字段进行函数运算，它们会让字段的索引失效。

举例:查询评论内容开头为abc的内容都有哪些，如果在WHERE子句中使用了函数，语句就会写成下面这样:

SELECT comment_id, comment_text, comment_time 
FROM product_comment 
WHERE SUBSTRING(comment_text , 1, 3)= 'abc';

采用查询重写的方式进行等价替换：

SELECT comment_id，comment_text，comment_time 
FROM product_comment 
WHERE comment.text LIKE 'abc%';

优化物理查询

物理查询优化是在确定了逻辑查询优化之后，采用物理优化技术（比如索引等），通过计算代价模型对各种可能的访问路径进行估算，从而找到执行方式中代价最小的作为执行计划
SQL查询时需要对不同的数据表进行查询，因此在物理查询优化阶段也需要确定这些查询所采用的路径，具体的情况包括：

1. 单表扫描：对于单表扫描来说，我们可以全表扫描所有的数据，也可以局部扫描
2. 双表连接：常用的连接方式包括了嵌套循环连接、HASH连接和合并连接
3. 多表连接：多张数据表进行连接时顺序很重要，不同的连接路径查询的效率不同，搜索空间也会不同。在进行多表连接时，搜索空间可能会达到很高的数据量级，巨大的搜索空间显然会占用更多的资源，需要通过调整连接顺序，将搜索空间调整在一个可接受的范围内

使用 Redis 或 Memcached 作为缓存

数据都是存放到数据库中，需要从数据库层中取出数据放到内存中进行业务逻辑的操作，当用户量增大时，如果频繁地进行数据查询，会消耗数据库的很多资源。如果将常用的数据直接放到内存中，就会大幅提升查询的效率， 常用的键值存储数据库有 Redis。

通常我们对于查询响应要求高的场景（响应时间短，吞吐量大)，可以考虑内存数据库。传统的RDBMS都是将数据存储在硬盘上，而内存数据库则存放在内存中，查询起来快

库级优化

读写分离

如果读和写的业务量都很大，并且它们都在同一个数据库服务器中进行操作，为了提升系统的性能，可以采用读写分离的方式降低主数据库的负载，比如用主数据库（master）完成写操作，用从数据库（slave）完成读操作。

数据分片

对数据库分库分表。当数据量级达到千万级以上时，有时需要把一个数据库切成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。如果使用的是MySQL，就可以使用MySQL自带的分区表功能，当然也可以考虑自己做垂直拆分（分库）、水平拆分（分表）、垂直+水平拆分（分库分表）。

优化MySQL服务器

优化服务器硬件

• 配置较大的内存：足够大的内存是提高MySQL数据库性能的方法之一。内存的速度比磁盘IO快，可以通过增加系统的缓冲区容量使数据在内存中停留的时间更长，以减少磁盘IO
• 配置高速磁盘系统：以减少读盘的等待时间，提高响应速度
• 合理分布磁盘IO：把磁盘IO分散在多个设备上，减少资源竞争，提高并行操作能力
• 配置多处理器：多处理器可同时执行多个线程，MySQL是多线程的数据库

优化MySQL的参数

MySQL参数	说明
innodb_buffer_pool_size	表示InnoDB类型的表和索引的最大缓存 。它不仅仅缓存索引数据 ，还会缓存表的数据。值越大查询的速度就会越快，但是这个值太大会影响操作系统的性能
key_buffer_size	表示索引缓冲区的大小。索引缓冲区是所有的线程共享 ，增加索引缓冲区可 以得到更好处理的索引。当值不是越大越好，它的大小取决于内存的大小。值太大，就会导致操作系统频繁换页，也会降低系统性能
table_cache	表示同时打开的表的个数
query_cache_size	表示查询缓冲区的大小
query_cache_type	值0时所有的查询都不使用查询缓存区 值1时所有的查询都将使用查询缓存区，除非在查询语句中指定SQL_NO_CACHE，如SELECT SQL_NO_CACHE * FROM tbl_name 值2时只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓存区。
sort_buffer_size	表示每个需要进行排序的线程分配的缓冲区的大小，增加这个参数的值可以 提高ORDER BY 或GROUP BY操作的速度
join_buffer_size	表示联合查询操作所能使用的缓冲区大小
read_buffer_size	表示 每个线程连续扫描时为扫描的每个表分配的缓冲区的大小
innodb_flush_log_at_trx_commit	表示何时将缓冲区的数据写入日志文件 ，并且将日志文件 写入磁盘中 值0时表示每秒1次将数据写入日志文件并将日志文件写入磁盘。每个事务的commit并不会触发前面的任何操作。该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失 值1时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘进行同步。该模式最安全但最慢，因为每次事务提交或事务外的指令都需要把日志写入硬盘 值2时表示每次提交事务时将数据写入日志文件， 每隔1秒将日志文件写入磁盘。该模式速度较快，也比值0时安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数 据才可能丢失
innodb_log_buffer_size	InnoDB 存储引擎的 事务日志所使用的缓冲区
max_connections	表示 允许连接到MySQL数据库的最大数量 ，默认值151
back_log	用于控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySQL的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即 back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错
thread_cache_size	线程池缓存线程数量的大小 ，当客户端断开连接后将当前线程缓存起来， 当在接到新的连接请求时快速响应无需创建新的线程
wait_timeout	指定 一个请求的最大连接时间
interactive_timeout	表示服务器在关闭连接前等待行动的秒数

优化数据库结构

拆分表：冷热数据分离

把1个包含很多字段的表拆分成2个或者多个相对较小的表，这些表中某些字段的操作频率很高（热数据），经常要进行查询或者更新操作，而另外一些字段的使用频率却很低（冷数据），冷热数据分离，可以减小表的宽度。

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节。表越宽，把表装载进内存缓冲池时所占用的内存也就越大，也会消耗更多的lO

冷热数据分离的目的：

• 减少磁盘IO，保证热数据的内存缓存命中率
• 更有效的利用缓存，避免读入无用的冷数据

增加中间表

对于需要经常联合查询的表可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

1. 分析经常联合查询表中的字段

2. 使用这些字段建立一个中间表，并将原来联合查询的表的数据插入中间表中

3. 使用中间表来进行查询

增加冗余字段

设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。 但是，合理地加入冗余字段可以提高查询速度。

表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。尤其在数据量大，而 且需要频繁进行连接的时候，为了提升效率，我们也可以考虑增加冗余字段来减少连接。

优化数据类型

改进表的设计时可以考虑优化字段的数据类型。列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少，在遍历时所需要的IO次数也就越多，索引的性能也就越差。

情况1：对整数类型数据进行优化

整数类型的字段可以用INT，INT 型数据有足够大的取值范围，在数据量很大时数据类型的定义在很大程度上会影响到系统整体的执行效率。 对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型UNSIGNED来存储，无符号相对于有符号，同样的字节数，存储的数值范围更大

情况2：既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型

与文本类型数据相比，大整数往往占用更少的存储空间，在存取和比对时可以占用更少内存空间。在二者皆可用时尽量使用整数类型，可以提高查询的效率

情况3：避免使用TEXT、BLOB数据类型

MySQL内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。并且对于这种数据，MySQL还是要进行二次查询，会使性能变得很差
如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用SELECT *，而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询

情况4：避免使用ENUM类型

修改ENUM值需要使用ALTER语句，ENUM类型的ORDER BY操作效率低，需要额外操作，可以使用TINYINT来代替ENUM类型。

情况5：使用TIMESTAMP存储时间

TIMESTAMP存储的时间范围1970-01-0100:00:01~ 2038-01-19-03:14:07。TIMESTAMP使用4字节，DATETIME使用8个字节，同时TIMESTAMP具有自动赋值以及自动更新的特性。

情况6：用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

• 非精准浮点：float,double
• 精准浮点：decimal

Decimal类型为精准浮点数，在计算时不会丢失精度，尤其是财务相关的金融类数据。占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据

优化插入记录的速度

MyISAM引擎的表

禁用索引

非空表插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引就会降低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引

-- 禁用索引
ALTER TABLE table_name DISABLE KEYS;

-- 重新开启索引
ALTER TABLE table_name ENABLE KEYS;

若对于空表批量导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后才建立索引的

禁用唯一性检查

插入数据时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验会降低插入记录的速度。为了降低对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启

-- 禁用唯一性检查
SET UNIQUE_CHECKS = 0;

-- 开启唯一性检查
SET UNIQUE_CHECKS = 1;

使用批量插入

插入多条记录时可以使用一条INSERT语句插入一条记录，也可以使用一条INSERT语句插入多条记录

-- 插入一条记录
INSERT INTO student VALUES(1,'zhangsan',18,1);
INSERT INTO student VALUES(2,'lisi',17,1);
INSERT INTO student VALUES(3,'wangwu',17,1);
INSERT INTO student VALUES(4,'zhaoliu',19,1);

-- 一条INSERT语句插入多条记录
INSERT INTO student VALUES
(1,'zhangsan',18,1),
(2,'lisi',17,1),
(3,'wangwu',17,1),
(4,'zhaoliu',19,1);

使用LOAD DATA INFILE 批量导入

当需要批量导入数据时，如果能用LOAD DATAINFILE语句，就尽量使用。因为LOAD DATAINFILE语句导入数居的速度比INSERT语句快。

InnoDB引擎的表

禁用唯一性检查

插入数据之前执行SET unique_checks=0来禁止对唯一索引的检查，数据导入完成之后再运行SET unique_checks=1

禁止外键检查

插入数据之间执行禁止堆外键的检查，数据插入完成之后再恢复对外键的检查

--- 禁用外键检查
SET foreign_key_checks = 0;

-- 恢复外键检查
SET foreign_key_checks = 1;

禁止自动提交

插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作

-- 禁止自动提交
SET autocommit = 0;

-- 恢复自动提交
SET autocommit = 1;

使用非空约束

在设计字段时如果业务允许，建议尽量使用非空约束。

• 进行比较和计算时，省去要对NULL值的字段判断是否为空的开销，提高存储效率
• 非空字段容易创建索引，索引NULL列需要额外的空间来保存，使用非空约束，就可以节省存储空间

分析表、检查表与优化表

MySQL提供了分析表、检查表和优化表的语句。分析表主要是分析关键字的分布，检查表主要是检查表是否存在错误，优化表主要是消除删除或者更新造成的空间浪费。

分析表

MySQL中提供ANALYZE TABLE语句分析表

ANALYZE[LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[ , tbl_name ]...

MySQL服务会将ANALYZE TABLE语句写到binlog中，以便在主从架构中，从服务能够同步数据，可以添加参数LOCAL或者NO_WRITE_TO_BINLOG取消将语句写道binlog中。
使用ANALYZE TABLE分析表的过程中，数据库系统会自动对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表，但是不能作用于视图。

ANALYZE TABLE分析后的统计结果会反应到cardinality，该值统计表中某一键所在的列不重复的值的个数。该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使用。

检查表

MySQL中可以使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表 是否存在错误。CHECK TABLE语句在执行过程中也会给表加上 只读锁

对于MyISAM类型的表，CHECK TABLE语句还会更新关键字统计数据。而且，CHECK TABLE也可以检查视图是否有错误， OPTION只对MyISAM类型的表有效，对InnoDB类型的表无效

CHECK TABLE tbl_name [, tbl_name] ... [OPTION] ...
OPTION = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

选项	说明
QUICK	不扫描行，不检查错误的连接
FAST	只检查没有被正确关闭的表
CHANGED	只检查上次检查后被更改的表和没有被正确关闭的表
MEDIUM	扫描行，以验证被删除的连接是有效的。也可以计算各行的关键字校验和，并使用计算 出的校验和验证这一点
EXTENDED	对每行的所有关键字进行一个全面的关键字查找

优化表

OPTIMIZE TABLE

MySQL中使用OPTIMIZE TABLE语句来优化表。但是OPTILMIZE TABLE语句只能优化表中的VARCHAR 、 BLOB或 TEXT 类型的字段。一个表使用了这些字段的数据类型，若已经删除表的一大部 分数据，或者已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行很多更新，则 应使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片 。

OPTIMIZE TABLE 语句对InnoDB和MyISAM类型的表都有效。该语句在执行过程中也会给表加上 只读锁 。

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

大表优化

限定查询的范围

禁止不带任何限制数据范围条件的查询语句

读/写分离

经典的数据库拆分方案，主库负责写，从库负责读。

一主一从模式

双主双从模式

垂直拆分

当数据量级达到千万级以上时，有时需要把一个数据库切成多份，放到不同的数据库服务器上， 减少对单一数据库服务器的访问压力。

垂直拆分优点

可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区 可以简化表的结构，易于维护。

垂直拆分缺点

主键会出现冗余，需要管理冗余列，并会引起 JOIN 操作。此外，垂直拆分会让事务 变得更加复杂。

水平拆分

尽量控制单表数据量的大小，建议控制在1000万以内。1000万并不是MysQL数据库的限制，过大会造成修改表结构、备份、恢复都会有很大的问题。此时可以用历史数据归档（应用于日志数据），水平分表（应用于业务数据）等手段来控制数据量大小

考虑业务数据的水平分表策略，将大的数据表按照某个属性维度分拆成不同的小表，每张小表保持相同的表结构。比如你可以按照年份来划分，把不同年份的数据放到不同的数据表中。2017年、2018年和2019年的数据就可以分别放到三张数据表中。
水平分表仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库，从而达到分布式的目的。

水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，跨节点Join性能较差，逻辑复杂。推荐尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少—次和中间件的网络IO

数据库分片的两种常见方案

• 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现（ 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现）
• 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中（Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现）

其它调优策略

服务器语句超时处理

在MySQL 8.0中可以设置 服务器语句超时的限制 ，单位可以达到 毫秒级别 。当中断的执行语句超过设置的 毫秒数后，服务器将终止查询影响不大的事务或连接，然后将错误报给客户端。

设置服务器语句超时的限制，可以通过设置系统变量MAX_EXECUTION_TIME来实现。默认情况下， MAX_EXECUTION_TIME的值为0，代表没有时间限制。

SET GLOBAL MAX_EXECUTION_TIME=2000;

SET SESSION MAX_EXECUTION_TIME=2000; -- 指定该会话中SELECT语句的超时时间

创建全局通用表空间

MySQL 8.0使用CREATE TABLESPACE语句来创建一个全局通用表空间。全局表空间可以被所有的数据库的表共享，而且相比于独享表空间，使用手动创建共享表空间可以节约元数据方面的内存。可以在创建表时指定属于哪个表空间，也可以对已有表进行表空间修改等。

MySQL 8.0 ：隐藏索引调优

不可见索引的特性对于性能调试非常有用。在MysQL 8.0中，索引可以被”隐藏””和“显示”。”当一个索引被隐藏时，它不会被查询优化器所使用。

需要注意的是当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。