总结关于MySQL数据库优化的哪些事儿

很多程序员视 SQL 为洪水猛兽。SQL 是一种为数不多的声明性语言（也称关系型语言），它的运行方式完全不同于我们所熟知的命令行语言、面向对象的程序语言、甚至是函数语言（尽管有些人认为 SQL 语言也是一种函数式语言）。

前言

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷。

1.优化一览图

2.优化

笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置。

2.1 软优化

2.1.1 查询语句优化

• 1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.




• 2.例:

DESC SELECT * FROM `user`
#切记关于多条数据查询select的一定要用limit进行分页限制

显示:

其中会显示索引和查询数据读取数据条数等信息.

2.1.2 优化子查询

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

2.1.3 使用索引

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者一文,介绍比较详细,此处记录使用索引的三大注意事项:

• LIKE关键字匹配'%'开头的字符串,不会使用索引.




• OR关键字的两个字段必须都是用了索引,该查询才会使用索引.




• 使用多列索引必须满足最左匹配.

2.1.4 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

2.1.5 中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

2.1.6 增加冗余字段 类似于创建中间表,增加冗余也是为了减少连接查询.

2.1.7 分析表,,检查表,优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

• 1.分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;

  

  
  

  • Op:表示执行的操作.

  
  

  • Msg_type:信息类型,有status,info,note,warning,error.

  
  

  • Msg_text:显示信息.

  
  



• 2.检查表: 使用 CHECK关键字,如CHECK TABLE user [option]

  
  

  • option 只对MyISAM有效,共五个参数值:

  
  

  • QUICK:不扫描行,不检查错误的连接.

  
  

  • FAST:只检查没有正确关闭的表.

  
  

  • CHANGED:只检查上次检查后被更改的表和没被正确关闭的表.

  
  

  • MEDIUM:扫描行,以验证被删除的连接是有效的,也可以计算各行关键字校验和.

  
  

  • EXTENDED:最全面的的检查,对每行关键字全面查找.

  
  



• 3.优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

2.2 硬优化

2.2.1 硬件三件套

• 1.配置多核心和频率高的cpu,多核心可以执行多个线程.




• 2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.




• 3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

2.2.2 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

• key_buffer_size:索引缓冲区大小




• table_cache:能同时打开表的个数




• query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.




• sort_buffer_size:排序缓冲区

2.2.3 分库分表（是建立在做过主从的基础之上）

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

2.2.4 缓存集群（缓存如redis最为重要一环）

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。

2.2.4 数据库限流

很多做服务接口的人或多或少的遇到这样的场景，由于业务应用系统的负载能力有限，为了防止非预期的请求对系统压力过大而拖垮业务应用系统。

服务接口的流量控制策略：分流、降级、限流等。行为执行比如：延迟处理，拒绝处理，或者部分拒绝处理等等。

 实际场景中常用的限流策略：
    1.Nginx前端限流
         按照一定的规则如帐号、IP、系统调用逻辑等在Nginx层面做限流
    2.业务应用系统限流
        1、客户端限流
        2、服务端限流
    3.数据库限流
        红线区，力保数据库

常见的限流算法有：令牌桶、漏桶和分布式限流。 计数器也可以进行粗暴限流实现。注意mysql数据库限流版本至少为MySQL 5.6以上版本。

令牌桶和漏桶对比：    

1.令牌桶是按照固定速率往桶中添加令牌，请求是否被处理需要看桶中令牌是否足够，当令牌数减为零时则拒绝新的请求；    
2.漏桶则是按照常量固定速率流出请求，流入请求速率任意，当流入的请求数累积到漏桶容量时，则新流入的请求被拒绝；   
3.令牌桶限制的是平均流入速率（允许突发请求，只要有令牌就可以处理，支持一次拿3个令牌，4个令牌），并允许一定程度突发流量；   
4.漏桶限制的是常量流出速率（即流出速率是一个固定常量值，比如都是1的速率流出，而不能一次是1，下次又是2），从而平滑突发流入速率；   
5.令牌桶允许一定程度的突发，而漏桶主要目的是平滑流入速率；    6.两个算法实现可以一样，但是方向是相反的，对于相同的参数得到的限流效果是一样的。

创建SQL限流规则

说明 目标SQL规则创建后，当应用端使用了同时包含所有关键词的SQL，RDS MySQL 5.6、RDS MySQL 5.7以及PolarDB MySQL 5.6的实例会返回1317错误（query execution was interrupted），RDS MySQL 8.0和PolarDB MySQL 8.0不会返回错误，但相关SQL会处于Concurrency control waitting状态。

分布式限流
方案一、redis存储可用数量和上一次放入token的时间（对比）
方案二、redis+lua
分布式限流最关键的是要将限流服务做成原子化，而解决方案可以使使用redis+lua或者技术进行实现，通过这两种技术可以实现的高并发和高性能。
方案三、nginx+lua
实现中我们需要使用lua-resty-lock互斥锁模块来解决原子性问题(在实际工程中使用时请考虑获取锁的超时问题)，并使用ngx.shared.DICT共享字典来实现计数器。如果需要限流则返回0，否则返回1。使用时需要先定义两个共享字典（分别用来存放锁和计数器数据）

应用级限流(Tomcat)
对于一个应用系统来说一定会有极限并发/请求数，即总有一个TPS/QPS阀值，如果超了阀值则系统就会不响应用户请求或响应的非常慢，因此我们最好进行过载保护，防止大量请求涌入击垮系统。 
如果你使用过Tomcat，其Connector 其中一种配置有如下几个参数：

acceptCount：如果Tomcat的线程都忙于响应，新来的连接会进入队列排队，如果超出排队大小，则拒绝连接；
maxConnections： 瞬时最大连接数，超出的会排队等待；
maxThreads：Tomcat能启动用来处理请求的最大线程数，如果请求处理量一直远远大于最大线程数则可能会僵死。
详细的配置请参考官方文档。另外如Mysql（如max_connections）、Redis（如tcp-backlog）都会有类似的限制连接数的配置。

结语

一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了。

【参考资料】高并发系统中的限流应该如何实现？_华为云官方博客 https://blog.csdn.net/devcloud/article/details/103454463