所谓的性能优化，一般针对的是MySQL查询的优化。既然是优化查询，我们自然要先知道查询操作要经过哪些环节，然后思考可以在哪些环节进行优化。
1.服务端配置
服务端需要做的就是尽可能地多接受客户端的连接。
（1）增加可用连接数，修改环境变量max_connections，默认情况下服务端的最大连接数为151个
（2）及时释放不活动的连接，系统默认的客户端超时时间是28800秒（8小时），我们可以把这个值调小一点
2.客户端优化
客户端能做的就是尽量减少和服务端建立连接的次数，已经建立的连接能凑合用就凑合用，别每次执行个SQL语句都创建个新连接，服务端和客户端的资源都吃不消啊。
解决的方案就是使用连接池来复用连接。Hikari官方给出了一个PostgreSQL数据库连接池大小的建议值公式，CPU核心数*2+1。假设服务器的CPU核心数是4，把连接池设置成9就可以了。这种公式在一定程度上对其他数据库也是适用的，大家面试的时候可以吹一吹。
3.架构优化
3.1使用缓存
系统中难免会出现一些比较慢的查询，这些查询要么是数据量大，要么是查询复杂（关联的表多或者是计算复杂），使得查询会长时间占用连接。
如果这种数据的实效性不是特别强（不是每时每刻都会变化，例如每日报表），我们可以把此类数据放入缓存系统中，在数据的缓存有效期内，直接从缓存系统中获取数据，这样就可以减轻数据库的压力并提升查询效率。
3.2读写分离（主从复制）
项目的初期，数据库通常都是运行在一台服务器上的，用户的所有读写请求会直接作用到这台数据库服务器，单台服务器承担的并发量毕竟是有限的。
针对这个问题，我们可以同时使用多台数据库服务器，将其中一台设置为为小组长，称之为master节点，其余节点作为组员，叫做slave。用户写数据只往master节点写，而读的请求分摊到各个slave节点上。这个方案叫做读写分离。给组长加上组员组成的小团体起个名字，叫集群。
使用集群必然面临一个问题，就是多个节点之间怎么保持数据的一致性。毕竟写请求只往master节点上发送了，只有master节点的数据是最新数据，怎么把对master节点的写操作也同步到各个slave节点上呢？
主从复制技术！
binlog是实现MySQL主从复制功能的核心组件。master节点会将所有的写操作记录到binlog中，slave节点会有专门的I/O线程读取master节点的binlog，将写操作同步到当前所在的slave节点。
这种集群的架构对减轻主数据库服务器的压力有非常好的效果，但是随着业务数据越来越多，如果某张表的数据量急剧增加，单表的查询性能就会大幅下降，而这个问题是读写分离也无法解决的，毕竟所有节点存放的是一模一样的数据啊，单表查询性能差，说的自然也是所有节点性能都差。
3.3分库分表
数据库作为节点，那就是分库；如果把单张表作为节点，那就是分表
3.3.1垂直分库
在单体数据库的基础上垂直切几刀，按照业务逻辑拆分成不同的数据库，这就是垂直分库
3.3.2垂直分表
垂直分表就是在单表的基础上垂直切一刀（或几刀），将一个表的多个字短拆成若干个小表，这种操作需要根据具体业务来进行判断，通常会把经常使用的字段（热字段）分成一个表，不经常使用或者不立即使用的字段（冷字段）分成一个表，提升查询速度。
3.3.3水平分库
水平分库就是对单个数据库水平切一刀，往往伴随着水平分表。即将数据存放到不同的数据库中，比如mysqDB1，mysqlDB2一般都在不通的服务器上，比如100W条订单数据，50W条存在DB1服务器上，50W条存在DB2服务器上。但是对业务代码逻辑编写较高
3.3.4水平分表
把单张表的数据按照一定的规则（行话叫分片规则）保存到多个数据表上，横着给数据表来一刀（或几刀），就是水平分表了。比如有2022年的订单数据，我们可以按订单产生的月份将该数据分摊到不同的表中，一月份在一张表，二月份在一张表......以此类推
总结：水平分，主要是为了解决存储的瓶颈；垂直分，主要是为了减轻并发压力。
4.使用消息队列削峰
通常情况下，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，极有可能压垮数据库（参考明星出轨或公布恋情时微博的状态）。
这种情况下可以通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求
5. 优化器——SQL分析与优化
5.1慢日志分析
处理完连接、优化完缓存等架构的事情，SQL查询语句来到了解析器和优化器的地盘了。在这一步如果出了任何问题，那就只能是SQL语句的问题了。
只要你的语法不出问题，解析器就不会有问题。此外，为了防止你写的SQL运行效率低，优化器会自动做一些优化，但如果实在是太烂，优化器也救不了你了，只能眼睁睁地看着你的SQL查询沦为慢查询。
慢查询就是执行地很慢的查询（这句话说得跟废话似的。。。），只有知道MySQL中有哪些慢查询我们才能针对性地进行优化。
因为开启慢查询日志是有性能代价的，因此MySQL默认是关闭慢查询日志功能，使用以下命令查看当前慢查询状态
slow_query_log表示当前慢查询日志是否开启，slow_query_log_file表示慢查询日志的保存位置。
除了上面两个变量，我们还需要确定“慢”的指标是什么，即执行超过多长时间才算是慢查询，默认是10S，如果改成0的话就是记录所有的SQL。
有两种打开慢日志的方式
- 修改配置文件
my.cnf
此种修改方式系统重启后依然有效
2. 动态修改参数（重启后失效）
最后进行慢日志分析
MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询的工具mysqldumpslow，为了演示这个工具，我们先构造一条慢查询：
其中，
- Count：表示这个SQL执行的次数
- Time：表示执行的时间，括号中的是累积时间
- Locks：表示锁定的时间，括号中的是累积时间
- Rows：表示返回的记录数，括号中的是累积数
5.2查看运行中的线程
我们可以运行show full processlist查看MySQL中运行的所有线程，查看其状态和运行时间，找到不顺眼的，直接kill。
其中，
- Id：线程的唯一标志，可以使用Id杀死指定线程
- User：启动这个线程的用户，普通账户只能查看自己的线程
- Host：哪个ip和端口发起的连接
- db：线程操作的数据库
- Command：线程的命令
- Time：操作持续时间，单位秒
- State：线程的状态
- Info：SQL语句的前100个字符
5.3查看存储引擎运行信息
SHOW ENGINE用来展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；Buffer pool统计信息等等数据。
SHOW ENGINE INNODB STATUS;
上面这条语句可以展示innodb存储引擎的当前运行的各种信息，大家可以据此找到MySQL当前的问题，限于篇幅不在此意义说明其中信息的含义，大家只要知道MySQL提供了这样一个监控工具就行了，等到需要的时候再来用就好。
5.4EXPLAIN执行计划
通过慢查询日志我们可以知道哪些SQL语句执行慢了，可是为什么慢？慢在哪里呢？
MySQL提供了一个执行计划的查询命令EXPLAIN，通过此命令我们可以查看SQL执行的计划，所谓执行计划就是：优化器会不会优化我们自己书写的SQL语句（比如外连接改内连接查询，子查询优化为连接查询...）、优化器针对此条SQL的执行对哪些索引进行了成本估算，并最终决定采用哪个索引（或者最终选择不用索引，而是全表扫描）、优化器对单表执行的策略是什么，等等等等。
5.5SQL优化
SQL优化指的是SQL本身语法没有问题，但是有实现相同目的的更好的写法。比如：
- 使用小表驱动大表；用join改写子查询；or改成union
- 连接查询中，尽量减少驱动表的扇出（记录数），访问被驱动表的成本要尽量低，尽量在被驱动表的连接列上建立索引，降低访问成本；被驱动表的连接列最好是该表的主键或者是唯一二级索引列，这样被驱动表的成本会降到更低
- 大偏移量的limit，先过滤再排序
针对最后一条举个简单的例子，下面两条语句能实现同样的目的，但是第二条的执行效率比第一条执行效率要高得多（存储引擎使用的是InnoDB），大家感受一下：
5.6索引优化
为慢查询创建适当的索引是个非常常见并且非常有效的方法，但是索引是否会被高效使用又是另一门学问了。
6. 存储引擎与表结构
6.1存储引擎优化
一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。
建议根据不同的业务选择不同的存储引擎，例如：
- 查询操作、插入操作多的业务表，推荐使用
MyISAM； - 临时表使用
Memory； - 并发数量大、更新多的业务选择使用
InnoDB； - 不知道选啥直接默认。
6.2优化字段
字段优化的最终原则是：使用可以正确存储数据的最小的数据类型
6.2.1整数类型
MySQL提供了6种整数类型，分别是
- tinyint
- smallint
- mediumint
- int
- integer
- bigint
不同的存储类型的最大存储范围不同，占用的存储的空间自然也不同。
例如，是否被删除的标识，建议选用tinyint，而不是bigint。
6.2.2字符类型
你是不是直接把所有字符串的字段都设置为varchar格式了？甚至怕不够，还会直接设置成varchar(1024)的长度？
如果不确定字段的长度，肯定是要选择varchar，但是varchar需要额外的空间来记录该字段目前占用的长度；因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。
6.2.3非空
非空字段尽量设置成NOT NULL，并提供默认值，或者使用特殊值代替NULL。
因为NULL类型的存储和优化都会存在性能不佳的问题，具体原因在这里就不展开了。
6.2.4不要用外键、触发器和视图功能
这也是「阿里巴巴开发手册」中提到的原则。原因有三个：
- 降低了可读性，检查代码的同时还得查看数据库的代码；
- 把计算的工作交给程序，数据库只做好存储的工作，并把这件事情做好；
- 数据的完整性校验的工作应该由开发者完成，而不是依赖于外键，一旦用了外键，你会发现测试的时候随便删点垃圾数据都变得异常艰难。
6.2.5图片、音频、视频存储
不要直接存储大文件，而是要存储大文件的访问地址。
6.2.6大字段拆分和数据冗余
大字段拆分其实就是前面说过的垂直分表，把不常用的字段或者数据量较大的字段拆分出去，避免列数过多和数据量过大，尤其是习惯编写SELECT *的情况下，列数多和数据量大导致的问题会被严重放大！
字段冗余原则上不符合数据库设计范式，但是却非常有利于快速检索。比如，合同表中存储客户id的同时可以冗余存储客户姓名，这样查询时就不需要再根据客户id获取用户姓名了。因此针对业务逻辑适当做一定程度的冗余也是一种比较好的优化技巧。
---------------转载微信公众号:程序员鱼皮
标签：存储,数据库,MYSQL,查询,SQL,优化,节点 From： https://www.cnblogs.com/powfu/p/16961718.html