一份平民化的MySQL性能优化指南

前言

近期在重新学习总结MySQL数据库性能优化的相关知识，本文是根据自己学习以及日常性能测试调优过程中总结的经验整理了一份平民化的优化指南，希望对大家在进行MySQL调优分析时有帮助！

 

MySQL查询执行原理

一般情况下，我们优化MySQL,目的就是为了查询数据更快，在优化MySQL性能之前，有必要先弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

如上图，整个MySQL查询执行过程，总的来说分为5个步骤：

1. 客户端向MySQL服务器发送一条查询请求。
2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3. 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划。
4. MySQL根据执行计划，调用存储引擎的API来执行查询。
5. 将查询结果返回给客户端，同时缓存查询结果。

 

MySQL性能优化导图

先总结一下，MySql性能优化思路主要从以下几个大的层面考虑优化：

【系统层面；存储引擎；设计层面；架构层面；参数配置；SQL层面；缓存机制】

 

 

系统优化    

硬件环境

• 升级多核CPU,主频高的CPU
• 更大的内存
• 更大的IOPS
• 集群，双机热备，多机互备

网络环境

• 尽量将网络整体系统部署在局域网内
• SLB设备优化
• 网络带宽升级

软件环境

• 开启mysql复制，实现读写分离，负载均衡
• 分库分表（垂直分表，水平分表）
• 利用分区功能进行大数据的拆分
• 系统内核优化，大多数MySQL都部署在linux系统上，所以操作系统的一些参数也会影响到MySQL性能

 

存储引擎

在实际应用系统中，结合存储引擎的优缺点选择合适的存储引擎（MyISAM，InnoDB，Memory）。

MySQL常用有两种存储引擎:

• 一个是MyISAM，不支持事务处理，读性能处理快，表级别锁。
• 一个是InnoDB，支持事务处理（ACID），设计目标是为处理大容量数据发挥最大化性能，行级别锁。

 表锁：开销小，锁定粒度大，发生死锁概率高，相对并发也低。

 行锁：开销大，锁定粒度小，发生死锁概率低，相对并发也高。

为什么会出现表锁和行锁呢？主要是为了保证数据的完整性，举个例子，一个用户在操作一张表，其他用户也想操作这张表，那么就要等第一个用户操作完，其他用户才能操作，表锁和行锁就是这个作用。否则多个用户同时操作一张表，肯定会数据产生冲突或者异常。

根据一般公司的业务要求看来，使用InnoDB存储引擎是最好的选择，也是MySQL5.5以后版本中默认存储引擎。

 

设计优化

表结构设计

总的原则：选择合适的字段属性， 尽量使用数字型字段。若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时只需更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

 

八点意见清单：

1. 通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。
   
2. 无需对整数类型指定宽度，比如INT(11)，用处不大。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。
3. UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。
   
4. 通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。
   
5. TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
   
6. 大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE。
7. schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。
   
8. 大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。

 

高性能索引设计

索引是提高MySQL查询性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。可见索引的添加也是非常有技术含量的。

 

九点意见清单：

1. 非独立的列，mysql不会使用索引， “独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数，如select * from where id + 1 = 10；
2. 使用短索引，如果列很长，通常可以指定一个前缀长度只索引开始的部分字符 ，这样可以有效节约索引空间，从而提高索引效率。 例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
   
3. 使用组合索引，在多数情况下，在多个列上建立独立的索引并不能提高查询性能。理由非常简单，MySQL不知道选择哪个索引的查询效率更好。
4. 避免多范围条件查询，如select * from user where createtime > '2020-01-01' and age between 18 and 30;
5. 冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。
   
6. 定期删除一些长时间未使用过的索引。
7. 索引不包含有NULL列的值。
8. 排序的索引问题， mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
   
9. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

 

架构调优

• 主从复制（读写分离）

一台MySQL服务器同一时间点支持的并发数是有限的，当大量并发(如秒杀活动等，很多用户都同一时刻访问数据库)时，一台数据库处理不过来，所以增加MySQL服务器的数量也是一种增强数据库性能的方式。通过使用MySQL主从复制，增删改操作走Master主服务器，查询走Slaver从服务器，这样就减少了只有一台MySQL服务器的压力。

 

• 分库分表

当一个表的数据量很大的时候，查询就变的很慢，所以减少表里的记录的数量是优化的一种方式，大表对DDL操作有一定的影响，如创建索引，添加字段；修改表结构需要长时间锁表，会造成长时间的主从延迟，影响正常的数据操作。

分库

把db分为按分布式划分几个DB来执行。

分表

水平分表：如按月份分。

垂直分表：如按订单号取模，存储视频图片，按字段切分，形成多个路径。

 

• 增加缓存层

减少数据库连接也是一种优化手段，有些查询可以不用访问数据库，可以通过使用缓存服务器如redis、memcache、elasticsearch等增加缓存，减少对数据库服务的直接访问连接。

 

参数配置调优

1.全局参数设置

查询mysql服务器运行各种状态：show global status

查询mysql服务器配置信息：show variables

查看是否启用了日志：show variables like 'log_%'

查看日志状态：show master logs show master status

查看当前会话的查询成本：show status like 'last_query_cost'

 

2连接数设置

show variables like 'max_connection'

show global status like 'max_used_connections'

修改连接数：set GLOBAL max_connections=2000

 

3.表扫描

show global status like 'handler_read_rnd_next'

show global status like 'com_select'

表扫描率：handler_read_rnd_next/com_select <4000,超过4000，说明进行了太多的表扫描

 

4.日志相关

检查日志打印类型：show variables like 'innodb_flush_log_at_trx_commit' ，当类型为0的时候，mysql crash可能会出现数据丢失，可靠性不高，主要测试1和2两种模式

查看每个日志文件大小参数：show variables like 'innodb_log_file_size' ,一般设置为64~512M

 

5.超时设置

show variables like 'wait_timeout' ,根据业务场景修改超时时间vi /etc/my.cnf

show variables like 'interactive_time'

 

SQL优化

主要定位SQL瓶颈的方式：

1.检查是否开启了慢查询

show variables like '%query%'

常用的分析慢查询的语句：

mysqldumpslow -s c -t 20 /var/log/mysql/slowquery.log   查看访问次数最多的20个SQL语句

mysqldumpslow -s r -t 20 /var/log/mysql/slowquery.log   查看返回记录集最多的20个SQL语句

mysqldumpslow -t 10 -s t -g "left join" /var/log/mysql/slowquery.log 按照时间返回前10条包含左链接的SQL语句

参数说明：-s 表示按照什么方式排序，c,t,l,r 表示按照次数，时间，查询时间，返回记录数来排序；-t 表示top n的意思；-g 后面可以写正则表达式，大小写不敏感。

 

2.explain语句分析

 

SQL优化总的调优策略及注意事项：

1.检查select语句中涉及到的表字段上的索引，若没有就考虑加上

2.涉及到范围字段不需要加索引，有则去掉

3.主键无需增加索引

4.select语句中有左右连接，应该首先考虑给后面关联的表增加索引

5.sql语句过长且用到临时表时，可拆分sql语句；（使用in操作拼接sql）

6.尽量避免select * 查全表字段

7.尽量用join代替子查询

8.尽量少使用or,用in或者out代替

9.尽量inout代替out

10.尽量早的将无用数据过滤，选择更优化的索引，先分页再join

 

20条奇技淫巧：

1. 避免在索引列上进行运算， 这将导致引擎放弃使用索引而进行全表扫描。
2. 不使用NOT IN和<>操作， NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>9则可使用id>9 or id<9来代替。
3. 检查where条件与order by 字段，避免全表扫描。
4. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num = 0
   
5. 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。可以拆分条件，进行子句的union all查询，如：select id from t where num=10 or name = 'admin'  拆分select id from t where num = 10 union all select id from t where name = 'admin'
6. in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3，
   
7. 用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b) 换成select num from a where exists(select 1 from b where num=a.num)
   
8. like语句的%不要前置， 否则索引失效将导致全表扫描。
9. 如果在 where 子句中使用参数，也会导致全表扫描。 因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
10. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
11. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
    
12. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
    
13. Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
    
14. 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。
    
15. select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。
    
16. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
    
17. 避免频繁创建和删除临时表，以减少系统表资源的消耗。临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件， 最好使用导出表。
    
18. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
    
19. 尽量拆分大的 DELETE 或INSERT 语句，批量提交SQL语句。
20. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
    

 

缓存机制分析优化

从4.0.1开始，MySQL提供了查询缓冲机制。使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的 SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238％的效率。

 

设置innodb_buffer_pool_size

这个参数主要作用是缓存innodb表的索引，数据，插入数据时的缓冲，默认值128M，推荐操作系统内存的70%~80%为佳，设置方法vi my.cnf文件。

 

query cache的运行状态分析

show status like '%qcache%'

     qcache_free_blocks：数目大说明可能有碎片。

     query_cache_min_res_unit：是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小 query_cache_min_res_unit。

     qcache_free_memory：缓存中的空闲内存。

     qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数，这个数字如果在不断增长，就表示可能碎片非常严重，或者内存很少 ，如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。

     qcache_hits：每次查询在缓存中命中时值增加，即命中缓存数。

     qcache_inserts：每次插入一个查询到缓存时值增加，即没有命中缓存数。

     qcache_total_blocks：缓存中块的总数量。

 

query cache的设置状态分析

show status like '%query_cache%'

     query_cache_limit：允许cache的单条query结果集的最大容量，默认是1MB。

     query_cache_size：设置query cache 所使用的内存大小，默认为0，大小必须是1024的整数倍。

     query_cache_type：控制query cache功能的开关，可设置为0（OFF）,1(ON),2(DEMAND),其中0表示关闭query cache功能，任何情况下都不会使用query cache,1表示开启query cache功能，但当select语句中使用sql_no_cache提示后，将不再使用query cache;2表示开启query cache功能，但只有当select语句中使用了sql_cache提示后，才使用query cache，另外，对于写密集型应用，不要轻易打开查询缓存。如果你实在是忍不住，可以将query_cache_type设置为DEMAND。

 

几个常用分析公式

• query cache命中率=qcache_hits/(qcache_hits+qcache_inserts)*100%
• 查询缓存碎片率=(qcache_free_blocks/qcache_total_blocks)*100%  ,如果查询缓存碎片率超过20%，可以用FLUSH QUERYCACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
• 查询缓存利用率=（query_cache_size-qcache_free_memory）/query_cache_size*100%， 查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes >50的话说明query_cache_size可能有点小，要不就是碎片太多。

 

优化提示

如果Qcache_lowmem_prunes 值比较大，表示查询缓存区大小设置太小，需要增大。

如果Qcache_free_blocks 较多，表示内存碎片较多，需要清理，flush query cache

高性能mysql中关于query_cache_min_res_unit大小调优给出的计算公式 query_cache_min_res_unit = (query_cache_size - Qcache_free_memory)/ Qcache_queries_in_cache

mysql在判断是否命中缓存时，任何字符的不同 ，不确定的函数都将导致缓存无法命中，子查询，存储过程也将导致查询缓存无法命中。