在日常生活组织工作中你会从什么样层次展开MySQL操控性强化呢？
简而言之的操控性强化，通常特别针对的是MySQL查阅的强化。难道是强化查阅，他们大自然要先晓得查阅操作方式要历经什么样各个环节，接着思索能在什么样各个环节展开强化。
布季谢两张图展现查阅操作方式须要历经的基本上各个环节。
SQL查阅的各个环节
上面从5个视角如是说呵呵MySQL强化的许多思路。
1. 相连实用性强化
处置相连是MySQL应用程序和MySQL服务器端接吻的第二步，第二步都迈不太好，也就年迈体弱而后的故事节了。
难道相连是两方的事，他们大自然从服务器端和应用程序三个各方面来展开强化喽。
1.1 服务器端实用性
服务器端须要做的是尽量地多拒绝接受应用程序的相连，也许你碰到过error 1040: Too many connections的严重错误？是服务器端的胸襟不如宽阔引致的，新格局太少！
他们能从三个各方面化解相连数不如的难题：
减少需用相连数，修改环境变量max_connections，默认情况下服务器端的最大相连数为151个mysql> show variables like max_connections; +—————–+——-+| Variable_name | Value | +—————–+——-+ | max_connections | 151 | +—————–+——-+ 1 row inset (0.01 sec) 及时释放不活动的相连，系统默认的应用程序超时时间是28800秒（8小时），他们能把这个值调小一点mysql> show variables like wait_timeout; +—————+——-+ | Variable_name | Value | +—————+——-+ | wait_timeout | 28800 | +—————+——-+ 1 row in set (0.01 sec)MySQL有非常多的实用性参数，并且大部分参数都提供了默认值，默认值是MySQL作者历经精心设计的，完全能满足大部分情况的需求，不建议在不清楚参数含义的情况下贸然修改。
1.2 应用程序强化
应用程序能做的是尽量减少和服务器端建立相连的次数，已经建立的相连能凑合用就凑合用，别每次执行个SQL语句都创建个新相连，服务器端和应用程序的资源都吃不消啊。
化解的方案是使用相连池来复用相连。
常见的数据库相连池有DBCP、C3P0、阿里的Druid、Hikari，前两者用得很少了，后两者目前如日中天。
但是须要注意的是相连池并不是越大越好，比如Druid的默认最大相连池大小是8，Hikari默认最大相连池大小是10，盲目地加大相连池的大小，系统执行效率反而有可能降低。为什么？
对于每一个相连，服务器端会创建一个单独的线程去处置，相连数越多，服务器端创建的线程大自然也就越多。而线程数超过CPU个数的情况下，CPU势必要通过分配时间片的方式展开线程的上下文切换，频繁的上下文切换会造成很大的操控性开销。
Hikari官方给出了一个PostgreSQL数据库相连池大小的建议值公式，CPU核心数*2+1。假设服务器的CPU核心数是4，把相连池设置成9就能了。这种公式在一定程度上对其他数据库也是适用的，大家面试的时候能吹一吹。
2. 架构强化
2.1 使用缓存
系统中难免会出现许多比较慢的查阅，这些查阅要么是数据量大，要么是查阅复杂（关联的表多或者是计算复杂），使得查阅会长时间占用相连。
如果这种数据的实效性不是特别强（不是每时每
缓存的使用
2.2 读写分离（集群、主从复制）
项目的初期，数据库通常都是运行在一台服务器上的，用户的所有读写请求会直接作用到这台数据库服务器，单台服务器承担的并发量毕竟是有限的。
特别针对这个难题，他们能同时使用多台数据库服务器，将其中一台设置为为小组长，称之为master节点，其余节点作为组员，叫做slave。用户写数据只往master节点写，而读的请求分摊到各个slave节点上。这个方案叫做读写分离。给组长加上组员组成的小团体起个名字，叫集群。
这是集群
注：很多开发者不满master-slave这种具有侵犯性的词汇（因为他们认为会联想到种族歧视、黑人奴隶等），所以发起了一项更名运动。
受此影响MySQL也会逐渐停用master、slave等术语，转而用source和replica替代，大家碰到的时候明白即可。
使用集群必然面临一个难题，是多个节点之间怎么保持数据的一致性。毕竟写请求只往master节点上发送了，只有master节点的数据是最新数据，怎么把对master节点的写操作方式也同步到各个slave节点上呢？
binlog是实现MySQL主从复制功能的核心组件。master节点会将所有的写操作方式记录到binlog中，slave节点会有专门的I/O线程读取master节点的binlog，将写操作方式同步到当前所在的slave节点。
主从复制
这种集群的架构对减轻主数据库服务器的压力有非常好的效果，但是随着业务数据越来越多，如果某张表的数据量急剧减少，单表的查阅操控性就会大幅下降，而这个难题是读写分离也无法化解的，毕竟所有节点存放的是一模一样的数据啊，单表查阅操控性差，说的大自然也是所有节点操控性都差。
这时他们能把单个节点的数据分散到多个节点上展开存储，这是分库分表。
2.3 分库分表
分库分表中的节点的含义比较宽泛，要是把数据库作为节点，那是分库；如果把单张表作为节点，那是分表。
大家都晓得分库分表分成垂直分库、垂直分表、水平分库和水平分表，但是每次都记不住这些概念，我就给大家详细说一说，帮助大家理解。
2.3.1 垂直分库
垂直分库
在单体数据库的基础上垂直切几刀，按照业务逻辑拆分成不同的数据库，这是垂直分库啦。
垂直分库
2.3.2 垂直分表
垂直分表
垂直分表是在单表的基础上垂直切一刀（或几刀），将一个表的多个字短拆成若干个小表，这种操作方式须要根据具体业务来展开判断，通常会把经常使用的字段（热字段）分成一个表，不经常使用或者不立即使用的字段（冷字段）分成一个表，提升查阅速度。
垂直分表
拿上图举例：通常情况下商品的详情信息都比较长，而且查看商品列表时往往不须要立即展现商品详情（通常都是点击详情按钮才会展开显示），而是会将商品更重要的信息（价格等）展现出来，按照这个业务逻辑，他们将原来的商品表做了垂直分表。
2.3.3 水平分表
把单张表的数据按照一定的规则（行话叫分片规则）保存到多个数据表上，横着给数据表来一刀（或几刀），是水平分表了。
水平分表
水平分表
2.3.4 水平分库
水平分库是对单个数据库水平切一刀，往往伴随着水平分表。
水平分库
水平分库
2.3.5 总结
水平分，主要是为了化解存储的瓶颈；垂直分，主要是为了减轻并发压力。
2.4 消息队列削峰
通常情况下，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，极有可能压垮数据库（参考明星出轨或公布恋情时微博的状态）。
这种情况下能通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，接着系统有条不紊地从消息队列中消费请求。
队列削峰
3. 强化器——SQL分析与强化
处置完相连、强化完缓存等架构的事，SQL查阅语句来到了解析器和强化器的地盘了。在这一步如果出了任何难题，那就只能是SQL语句的难题了。
只要你的语法不出难题，解析器就不会有难题。此外，为了防止你写的SQL运行效率低，强化器会自动做许多强化，但如果实在是太烂，强化器也救不了你了，只能眼睁睁地看着你的SQL查阅沦为慢查阅。
3.1 慢查阅
慢查阅是执行地很慢的查阅（这句话说得跟废话似的。。。），只有晓得MySQL中有什么样慢查阅他们才能特别针对性地展开强化。
因为开启慢查阅日志是有操控性代价的，因此MySQL默认是关闭慢查阅日志功能，使用以下命令查看当前慢查阅状态
mysql> show variables like slow_query%; +———————+————————————–+ | Variable_name | Value | +———————+————————————–+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log | +———————+————————————–+ 2 rows in set (0.00 sec)slow_query_log表示当前慢查阅日志是否开启，slow_query_log_file表示慢查阅日志的保存位置。
除了上面三个变量，他们还须要确定“慢”的指标是什么，即执行超过多长时间才算是慢查阅，默认是10S，如果改成0的话是记录所有的SQL。
mysql> show variables like %long_query%; +—————–+———–+ | Variable_name | Value | +—————–+———–+ | long_query_time | 10.000000| +—————–+———–+ 1 row in set (0.00 sec)3.1.1 打开慢日志
有两种打开慢日志的方式
修改实用性文件my.cnf此种修改方式系统重启后依然有效
# 是否开启慢查阅日志 slow_query_log=ON # long_query_time=2 slow_query_log_file=/var/lib/mysql/slow.log 动态修改参数（重启后失效）mysql> set @@global.slow_query_log=1; Query OK, 0 rows affected (0.06 sec) mysql> set @@global.long_query_time=2;Query OK, 0 rows affected (0.00 sec)3.1.2 慢日志分析
MySQL不仅为他们保存了慢日志文件，还为他们提供了慢日志查阅的工具mysqldumpslow，为了演示这个工具，他们先构造一条慢查阅：
mysql> SELECT sleep(5);接着他们查阅用时最多的1条慢查阅：
[root@iZ2zejfuakcnnq2pgqyzowZ~]# mysqldumpslow -s t -t1 -g select /var/lib/mysql/9e74f9251f6c-slow.log Reading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.log Count: 1 Time=10.00s (10s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost SELECT sleep(N)其中，
Count：表示这个SQL执行的次数Time：表示执行的时间，括号中的是累积时间Locks：表示锁定的时间，括号中的是累积时间Rows：表示返回的记录数，括号中的是累积数更多关于mysqldumpslow的使用方式，能查阅官方文档，或者执行mysqldumpslow –help寻求帮助。
3.2 查看运行中的线程
他们能运行show full processlist查看MySQL中运行的所有线程，查看其状态和运行时间，找到不顺眼的，直接kill。
image-20220405182328247
其中，
Id：线程的唯一标志，能使用Id杀死指定线程User：启动这个线程的用户，普通账户只能查看自己的线程Host：哪个ip和端口发起的相连db：线程操作方式的数据库Command：线程的命令Time：操作方式持续时间，单位秒State：线程的状态Info：SQL语句的前100个字符3.3 查看服务器运行状态
使用SHOW STATUS查看MySQL服务器的运行状态，有session和global两种作用域，通常使用like+通配符展开过滤。
— 查看select的次数 mysql> SHOW GLOBAL STATUS LIKE com_select; +—————+——–+| Variable_name | Value | +—————+——–+ | Com_select | 168241 | +—————+——–+ 1 row in set (0.05 sec)3.4 查看存储引擎运行信息
SHOW ENGINE用来展现存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；Buffer pool统计信息等等数据。
例如：
SHOW ENGINE INNODB STATUS;上面这条语句能展现innodb存储引擎的当前运行的各种信息，大家能据此找到MySQL当前的难题，限于篇幅不在此意义说明其中信息的含义，大家只要晓得MySQL提供了这样一个监控工具就行了，等到须要的时候再来用就好。
3.5 EXPLAIN执行计划
通过慢查阅日志他们能晓得什么样SQL语句执行慢了，可是为什么慢？慢在哪里呢？
MySQL提供了一个执行计划的查阅命令EXPLAIN，通过此命令他们能查看SQL执行的计划，简而言之执行计划是：强化器会不会强化他们自己书写的SQL语句（比如外相连改内相连查阅，子查阅强化为相连查阅…）、强化器针对此条SQL的执行对什么样索引展开了成本估算，并最终决定采用哪个索引（或者最终选择不用索引，而是全表扫描）、强化器对单表执行的思路是什么，等等等等。
EXPLAIN在MySQL5.6.3之后也能特别针对UPDATE、DELETE和INSERT语句展开分析，但是通常情况下他们还是用在SELECT查阅上。
这篇文章主要是从宏观上多个视角如是说MySQL的强化思路，因此这里不详细说明EXPLAIN的细节，之后单独成篇。
3.6 SQL与索引强化
3.6.1 SQL强化
SQL强化指的是SQL本身语法没有难题，但是有实现相同目的的更好的写法。比如：
使用小表驱动大表；用join改写子查阅；or改成union相连查阅中，尽量减少驱动表的扇出（记录数），访问被驱动表的成本要尽量低，尽量在被驱动表的相连列上建立索引，降低访问成本；被驱动表的相连列最好是该表的主键或者是唯一二级索引列，这样被驱动表的成本会降到更低大偏移量的limit，先过滤再排序特别针对最后一条举个简单的例子，上面两条语句能实现同样的目的，但是第二条的执行效率比第一条执行效率要高得多（存储引擎使用的是InnoDB），大家感受呵呵：
— 1. 大偏移量的查阅 mysql> SELECT * FROM user_innodb LIMIT 9000000,10; Empty set (8.18 sec) — 2.先过滤ID（因为ID使用的是索引），再limit mysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10; Empty set (0.02 sec)3.6.2 索引强化
为慢查阅创建适当的索引是个非常常见并且非常有效的方式，但是索引是否会被高效使用又是另一门学问了。
4. 存储引擎与表结构
4.1 选择存储引擎
通常情况下，他们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库操控性要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。
建议根据不同的业务选择不同的存储引擎，例如：
查阅操作方式、插入操作方式多的业务表，推荐使用MyISAM；临时表使用Memory；并发数量大、更新多的业务选择使用InnoDB；不晓得选啥直接默认。4.2 强化字段
字段强化的最终原则是：使用能正确存储数据的最小的数据类型。
4.2.1 整数类型
MySQL提供了6种整数类型，分别是
tinyintsmallintmediumintintintegerbigint不同的存储类型的最大存储范围不同，占用的存储的空间大自然也不同。
例如，是否被删除的标识，建议选用tinyint，而不是bigint。
4.2.2 字符类型
你是不是直接把所有字符串的字段都设置为varchar格式了？甚至怕不如，还会直接设置成varchar(1024)的长度？
如果不确定字段的长度，肯定是要选择varchar，但是varchar须要额外的空间来记录该字段目前占用的长度；因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。
4.2.3 非空
非空字段尽量设置成NOT NULL，并提供默认值，或者使用特殊值代替NULL。
因为NULL类型的存储和强化都会存在操控性不佳的难题，具体原因在这里就不展开了。
4.2.4 不要用外键、触发器和视图功能
这也是「阿里巴巴开发手册」中提到的原则。原因有三个：
降低了可读性，检查代码的同时还得查看数据库的代码；把计算的组织工作交给程序，数据库只做好存储的组织工作，并把这件事做好；数据的完整性校验的组织工作应该由开发者完成，而不是依赖于外键，一旦用了外键，你会发现测试的时候随便删点垃圾数据都变得异常艰难。4.2.5 图片、音频、视频存储
不要直接存储大文件，而是要存储大文件的访问地址。
4.2.6 大字段拆分和数据冗余
大字段拆分其实是前面说过的垂直分表，把不常用的字段或者数据量较大的字段拆分出去，避免列数过多和数据量过大，尤其是习惯编写SELECT *的情况下，列数多和数据量大引致的难题会被严重放大！
字段冗余原则上不符合数据库设计范式，但是却非常有利于快速检索。比如，合同表中存储客户id的同时能冗余存储客户姓名，这样查阅时就不须要
5. 业务强化
严格来说，业务各方面的强化已经不算是MySQL调优的手段了，但是业务的强化却能非常有效地减轻数据库访问压力，这各方面一个典型例子是淘宝，上面举几个简单例子给大家提供呵呵思路：
以往都是双11当晚开始买买买的模式，最近几年双11的预售战线越拉越长，提前半个多月就开始了，而且各种定金红包模式丛出不穷，这种方式叫做预售分流。这样做能分流客户的服务请求，不必等到双十一的凌晨一股脑地集体下单；双十一的凌晨你也许想查阅当天之外的订单，但是却查阅失败；甚至支付宝里的小鸡的口粮都被延迟发放了，这是一种降级策略，集结不重要的服务的计算资源，用来保证当前最核心的业务；双十一的时候支付宝极力推荐使用花呗支付，而不是银行卡支付，虽然一部分考量是提高软件粘性，但是另一各方面，使用余额宝实际使用的阿里内部服务器，访问速度快，而使用银行卡，须要调用银行接口，相比之下操作方式要慢了许多。
