从哪些维度进行MySQL性能优化？
面试官如果问你：你会从哪些维度进行MySQL性能优化？你会怎么回答？
所谓的性能优化，一般针对的是MySQL查询的优化。既然是优化查询，我们自然要先知道查询操作要经过哪些环节，然后思考可以在哪些环节进行优化。
我之前写过一条SQL查询语句是如何执行的？，感兴趣的朋友可以阅读一下，我用其中的一张图展示查询操作需要经历的基本环节。
SQL查询的环节
下面从5个角度介绍一下MySQL优化的一些策略。
我们可以从两个方面解决连接数不够的问题：
- 增加可用连接数，修改环境变量max_connections，默认情况下服务端的最大连接数为151个；
mysql> show variables like 'max_connections';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 151 |+-----------------+-------+1 row in set (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作者经过精心设计的，完全可以满足大部分情况的需求，不建议在不清楚参数含义的情况下贸然修改。
常见的数据库连接池有DBCP、C3P0、阿里的Druid、Hikari，前两者用得很少了，后两者目前如日中天。
但是需要注意的是连接池并不是越大越好，比如Druid的默认最大连接池大小是8，Hikari默认最大连接池大小是10，盲目地加大连接池的大小，系统执行效率反而有可能降低。为什么？
对于每一个连接，服务端会创建一个单独的线程去处理，连接数越多，服务端创建的线程自然也就越多。而线程数超过CPU个数的情况下，CPU势必要通过分配时间片的方式进行线程的上下文切换，频繁的上下文切换会造成很大的性能开销。
Hikari官方给出了一个PostgreSQL数据库连接池大小的建议值公式，CPU核心数*2+1。假设服务器的CPU核心数是4，把连接池设置成9就可以了。这种公式在一定程度上对其他数据库也是适用的，大家面试的时候可以吹一吹。
缓存的使用
这就是集群
主从复制
这种集群的架构对减轻主数据库服务器的压力有非常好的效果，但是随着业务数据越来越多，如果某张表的数据量急剧增加，单表的查询性能就会大幅下降，而这个问题是读写分离也无法解决的，毕竟所有节点存放的是一模一样的数据啊，单表查询性能差，说的自然也是所有节点性能都差。
这时我们可以把单个节点的数据分散到多个节点上进行存储，这就是分库分表。
1）垂直分库
垂直分库
在单体数据库的基础上垂直切几刀，按照业务逻辑拆分成不同的数据库，这就是垂直分库啦。
垂直分库
2）垂直分表
垂直分表
垂直分表就是在单表的基础上垂直切一刀（或几刀），将一个表的多个字短拆成若干个小表，这种操作需要根据具体业务来进行判断，通常会把经常使用的字段（热字段）分成一个表，不经常使用或者不立即使用的字段（冷字段）分成一个表，提升查询速度。
垂直分表
拿上图举例：通常情况下商品的详情信息都比较长，而且查看商品列表时往往不需要立即展示商品详情（一般都是点击详情按钮才会进行显示），而是会将商品更重要的信息（价格等）展示出来，按照这个业务逻辑，我们将原来的商品表做了垂直分表。
3）水平分表
把单张表的数据按照一定的规则（行话叫分片规则）保存到多个数据表上，横着给数据表来一刀（或几刀），就是水平分表了。
水平分表
水平分表
4）水平分库
水平分库就是对单个数据库水平切一刀，往往伴随着水平分表。
水平分库
水平分库
5）总结
水平分，主要是为了解决存储的瓶颈；垂直分，主要是为了减轻并发压力。
队列削峰
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)
1）打开慢日志
有两种打开慢日志的方式。
- 修改配置文件my.cnf
此种修改方式系统重启后依然有效。
# 是否开启慢查询日志slow_query_log=ON#long_query_time=2slow_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)
2）慢日志分析
MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询的工具mysqldumpslow，为了演示这个工具，我们先构造一条慢查询：
SELECT sleep(5);然后我们查询用时最多的1条慢查询：
[root@iZ2zejfuakcnnq2pgqyzowZ ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.logReading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.logCount: 1 Time=10.00s (10s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhostSELECT sleep(N)
其中，
- Count：表示这个SQL执行的次数。
- Time：表示执行的时间，括号中的是累积时间。
- Locks：表示锁定的时间，括号中的是累积时间。
- Rows：表示返回的记录数，括号中的是累积数。
更多关于mysqldumpslow的使用方式，可以查阅官方文档，或者执行mysqldumpslow --help寻求帮助。
其中，
- Id：线程的唯一标志，可以使用Id杀死指定线程。
- User：启动这个线程的用户，普通账户只能查看自己的线程。
- Host：哪个ip和端口发起的连接。
- db：线程操作的数据库。
- Command：线程的命令。
- Time：操作持续时间，单位秒。
- State：线程的状态。
- Info：SQL语句的前100个字符。
-- 查看select的次数mysql> SHOW GLOBAL STATUS LIKE 'com_select';+---------------+--------+| Variable_name | Value |+---------------+--------+| Com_select | 168241 |+---------------+--------+1 row in set (0.05 sec)
SHOW ENGINE INNODB STATUS;上面这条语句可以展示innodb存储引擎的当前运行的各种信息，大家可以据此找到MySQL当前的问题，限于篇幅不在此意义说明其中信息的含义，大家只要知道MySQL提供了这样一个监控工具就行了，等到需要的时候再来用就好。
EXPLAIN在MySQL5.6.3之后也可以针对UPDATE、DELETE和INSERT语句进行分析，但是通常情况下我们还是用在SELECT查询上。
这篇文章主要是从宏观上多个角度介绍MySQL的优化策略，因此这里不详细说明EXPLAIN的细节。
- 使用小表驱动大表；用join改写子查询；or改成union；
- 连接查询中，尽量减少驱动表的扇出（记录数），访问被驱动表的成本要尽量低，尽量在被驱动表的连接列上建立索引，降低访问成本；被驱动表的连接列最好是该表的主键或者是唯一二级索引列，这样被驱动表的成本会降到更低；
- 大偏移量的limit，先过滤再排序。
针对最后一条举个简单的例子，下面两条语句能实现同样的目的，但是第二条的执行效率比第一条执行效率要高得多（存储引擎使用的是InnoDB），大家感受一下：
-- 1. 大偏移量的查询mysql> SELECT * FROM user_innodb LIMIT 9000000,10;Empty set (8.18 sec)-- 2.先过滤ID（因为ID使用的是索引），再limitmysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10;Empty set (0.02 sec)
2）索引优化
为慢查询创建适当的索引是个非常常见并且非常有效的方法，但是索引是否会被高效使用又是另一门学问了。
- 查询操作、插入操作多的业务表，推荐使用MyISAM；
- 临时表使用Memory；
- 并发数量大、更新多的业务选择使用InnoDB；
- 不知道选啥直接默认。
MySQL提供了6种整数类型，分别是：
- tinyint
- smallint
- mediumint
- int
- integer
- bigint
不同的存储类型的最大存储范围不同，占用的存储的空间自然也不同。
例如，是否被删除的标识，建议选用tinyint，而不是bigint。
2）字符类型
你是不是直接把所有字符串的字段都设置为varchar格式了？甚至怕不够，还会直接设置成varchar(1024)的长度？
如果不确定字段的长度，肯定是要选择varchar，但是varchar需要额外的空间来记录该字段目前占用的长度；因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。
3）非空
非空字段尽量设置成NOT NULL，并提供默认值，或者使用特殊值代替NULL。
因为NULL类型的存储和优化都会存在性能不佳的问题，具体原因在这里就不展开了。
4）不要用外键、触发器和视图功能
这也是「阿里巴巴开发手册」中提到的原则。原因有三个：
- 降低了可读性，检查代码的同时还得查看数据库的代码；
- 把计算的工作交给程序，数据库只做好存储的工作，并把这件事情做好；
- 数据的完整性校验的工作应该由开发者完成，而不是依赖于外键，一旦用了外键，你会发现测试的时候随便删点垃圾数据都变得异常艰难。
5）图片、音频、视频存储
不要直接存储大文件，而是要存储大文件的访问地址。
6）大字段拆分和数据冗余
大字段拆分其实就是前面说过的垂直分表，把不常用的字段或者数据量较大的字段拆分出去，避免列数过多和数据量过大，尤其是习惯编写SELECT *的情况下，列数多和数据量大导致的问题会被严重放大！
字段冗余原则上不符合数据库设计范式，但是却非常有利于快速检索。比如，合同表中存储客户id的同时可以冗余存储客户姓名，这样查询时就不需要再根据客户id获取用户姓名了。因此针对业务逻辑适当做一定程度的冗余也是一种比较好的优化技巧。
- 以往都是双11当晚开始买买买的模式，最近几年双11的预售战线越拉越长，提前半个多月就开始了，而且各种定金红包模式层出不穷，这种方式叫做预售分流。这样做可以分流客户的服务请求，不必等到双十一的凌晨一股脑地集体下单；
- 双十一的凌晨你或许想查询当天之外的订单，但是却查询失败；甚至支付宝里的小鸡的口粮都被延迟发放了，这是一种降级策略，集结不重要的服务的计算资源，用来保证当前最核心的业务；
- 双十一的时候支付宝极力推荐使用花呗支付，而不是银行卡支付，虽然一部分考量是提高软件粘性，但是另一方面，使用余额宝实际使用的阿里内部服务器，访问速度快，而使用银行卡，需要调用银行接口，相比之下操作要慢了许多。
