如果面试官问你：你会从哪些维度优化MySQL性能？你会怎么回答？所谓性能优化一般是针对MySQL查询的优化。既然是优化查询，我们自然要知道查询操作会经过哪些环节，然后再思考哪些环节可以优化。我用一张图来展示查询操作需要经过的基本环节。在SQL查询环节，下面从5个角度介绍MySQL优化的一些策略。1、连接配置优化处理连接是MySQL客户端与MySQL服务器建立亲密关系的第一步。第一步做不好，后面的故事就不说了。既然连接是双方的事情，我们自然要从服务端和客户端两方面进行优化。1.服务器配置服务器需要做的就是接受尽可能多的客户端连接。也许您遇到过错误1040：连接过多？是服务员的心胸不够宽广，版面太小了！我们可以从两个方面解决连接数不足的问题：增加可用连接数和修改环境变量max_connections。服务器端默认最大连接数为151；mysql>showvariableslike'max_connections';+---------------+------+|变量名|值|+----------------+-------+|最大连接|151|+----------------+--------+1rowinset(0.01sec)及时释放非活动连接，默认客户端超时系统是28800秒（8小时），我们可以稍微降低这个值。mysql>showvariableslike'wait_timeout';+----------------+------+|变量名|值|+-----------------+--------+|等待超时|28800|+----------------+--------+1rowinset(0.01sec)MySQL配置参数很多，大部分参数提供默认值值。默认值是MySQL作者精心设计的，完全可以满足大部分情况的需要。在不了解参数含义的情况下不建议贸然行动。修订。2.客户端优化客户端能做的就是尽量减少与服务器的连接数。已经建立的连接，能用多久就用多久。不要在每次执行SQL语句时都创建一个新连接。服务器和客户端之间的连接资源太多了。解决方案是使用连接池来重用连接。常见的数据库连接池有DBCP、C3P0、阿里的Druid、Hikari。前两者很少使用，后两者目前正在如火如荼地进行。但是需要注意的是，连接池越大越好。比如Druid默认的最大连接池大小是8，Hikari默认的最大连接池大小是10，如果盲目增加连接池的大小，反而会降低系统的执行效率。为什么？对于每个连接，服务器都会创建一个单独的线程来处理。连接越多，服务器创建的线程就越多。当线程数超过CPU数时，CPU必须为线程上下文切换分配时间片，频繁的上下文切换会造成较大的性能开销。Hikari官方给出了PostgreSQL数据库连接池大小的建议取值公式，CPU核数*2+1。假设服务器的CPU核数为4，连接池设置为9就可以了。这个公式在一定程度上也适用于其他数据库，面试的时候可以炸一下。二、架构优化1、缓存系统中难免会出现一些慢查询。这些查询要么数据量大，要么查询复杂（关联表多，计算复杂），导致查询占用连接时间长。如果这类数据的有效性不是特别强（不是每时每刻都在变化，比如日报），我们可以把这类数据放到缓存系统中，在有效期内直接从缓存系统中获取数据缓存数据，可以减轻数据库的压力，提高查询效率。缓存的使用2.读写分离（集群，主从复制）在项目初期，数据库通常运行在一台服务器上，用户所有的读写请求都会直接影响这台数据库服务器，并且单台服务器会承担毕竟并发量是有限的。为了解决这个问题，我们可以同时使用多台数据库服务器，将其中一台作为组长，称为master节点，其他节点作为组成员，称为slaves。用户写入的数据只写入主节点，而读请求分配给各个从节点。这种方案称为读写分离。给组长和组员一个小组起个名字叫集群。这也是为什么集群中很多开发者对master-slave这个攻击性词汇感到不满（因为他们认为这会和种族歧视、黑奴等联系起来），所以发起了更名运动。受此影响，MySQL将逐渐停止使用master和slave等术语，取而代之的是source和replica。你只需要在遇到他们的时候就明白。使用集群不可避免地面临一个问题，就是如何保持多个节点之间的数据一致性。毕竟写请求只是发送给master节点，只有master节点的数据才是最新的数据。如何将对master节点的写操作同步到各个slave节点？主从复制技术来了！Binlog是实现MySQL主从复制功能的核心组件。主节点会将所有的写操作记录在binlog中，从节点会有专门的I/O线程读取主节点的binlog并将写操作同步到当前从节点。主从复制的集群架构对于缓解主数据库服务器的压力有很好的效果，但是随着业务数据越来越多，如果某个表的数据量激增，单表的查询性能会下降明显下降，这个问题不是读写分离就能解决的。毕竟，所有节点都存储完全相同的数据。单表查询性能差，自然是所有节点性能差。这时候我们可以将单个节点的数据分散到多个节点存储，也就是分库分表。3、分库、分表、分库、分表，节点的含义比较广。如果一个数据库作为一个节点，它就是一个子数据库；如果单表作为节点，则为子表。分库分表大家都知道，分为垂直分库、垂直分表、水平分库和水平分表，但是我每次都记不住这些概念，下面详细解释一下帮助你理解。1）垂直分片垂直分片是基于单体数据库，根据业务逻辑拆分到不同的数据库。这就是垂直分片。垂直分库2）垂直分表垂直分表垂直分表是在单表的基础上垂直切一刀（或几刀），将一个表的多个短词拆分成若干个小表。该操作需要根据具体的业务判断，通常将经常使用的字段（热字段）分表，将不经常使用或不立即使用的字段（冷字段）分表以提高查询速度。以上图为垂直分表为例：通常商品的详细信息比较长，查看商品列表时，往往不需要立即显示商品详情（一般是点击详情按钮显示），但会更新商品的重要信息（价格等）显示。根据这个业务逻辑，我们把原来的商品列表做成竖直的分表。3）水平分表是将单表的数据按照一定的规则（行话叫shardingrule）保存到多个数据表中，一刀（或几刀）水平分到数据表就是水平分表。Horizo??ntalsubtablehorizo??ntalsubtable4)Horizo??ntalsub-database水平分库是将单个数据库水平切分，往往伴随着水平分表。Horizo??ntalshardingHorizo??ntalsharding5)总结Horizo??ntalsharding主要是为了解决存储瓶颈；垂直分片主要是为了降低并发压力。4、消息队列调峰一般情况下，用户请求会直接访问数据库。如果同时在线人数非常多，极有可能压垮数据库（参考明星出轨或公布恋情时的微博状态）。这种情况下，可以通过使用消息队列来降低数据库的压力。无论同时有多少个用户请求，都会先存储到消息队列中，然后系统从消息队列中有序的消费请求。Queuepeakshaving3.Optimizer——SQL分析和优化经过连接处理和缓存优化后，SQL查询语句进入解析器和优化器的领域。如果这一步有问题，那只能是SQL语句的问题。只要你的语法正确，解析器就没问题。另外，为了防止你写的SQL运行效率低下，优化器会自动进行一些优化，但是如果太差，优化器也救不了你，你只能眼睁睁看着你的SQL查询变成一个慢查询。1.慢查询慢查询就是执行的很慢的查询（这句话就像废话。。。）。只有知道MySQL中存在哪些慢查询，才能有针对性地进行优化。因为开启慢查询日志是有性能开销的，所以MySQL默认关闭了慢查询日志功能。使用以下命令查看当前慢查询状态。mysql>showvariableslike'slow_query%';+--------------------+--------------------------------------+|变量名|值|+--------------------+------------------------------------+|慢查询日志|关闭||慢查询日志文件|/var/lib/mysql/9e74f9251f6c-slow.log|+--------------------+-------------------------------------+2rowsinset(0.00sec)slow_query_log表示当前慢查询日志是否开启，slow_query_log_file表示存储位置慢查询日志。除了以上两个变量，我们还需要判断“慢”指标是什么，即执行一个慢查询需要多长时间。默认为10S。如果改为0，则记录所有的SQL。mysql>showvariableslike'%long_query%';+----------------+------------+|变量名|值|+----------------+----------+|long_query_time|10.000000|+-----------------+------------+1rowinset(0.00sec)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;QueryOK,0rowsaffected(0.06sec)mysql>set@@global.long_query_time=2;QueryOK,0rowsaffected(0.00sec)2)慢日志分析MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询工具mysqldumpslow，为了演示这个工具，我们先构造一个慢查询：mysql>SELECTsleep(5);然后我们查询耗时最多的慢查询：[root@iZ2zejfuakcnnq2pgqyzowZ~]#mysqldumpslow-st-t1-g'select'/var/lib/mysql/9e74f9251f6c-slow.logReadingmysqlslowquerylogfrom/var/lib/mysql/9e74f9251f6c-slow.logCount:1Time=10.00s(10s)Lock=0.00s(0s)Rows=1.0(1),root[root]@localhostSELECTsleep(N)其中，Count:表示数量此SQL被执行的次数。时间：表示执行时间，括号内为累计时间。Locks：表示锁定时间，括号内为累计时间。Rows：表示返回的记录数，括号中的数字为累计数。关于mysqldumpslow的更多使用方法，可以参考官方文档，或者执行mysqldumpslow--help获取帮助。2、查看运行线程我们可以运行showfullprocesslist来查看MySQL中所有运行的线程，查看它们的状态和运行时间，把不顺眼的杀掉。其中，Id：线程的唯一标识，可以通过Id来kill指定的线程。用户：发起本帖的用户，普通账号只能查看自己的帖。Host：发起连接的ip和端口。db：线程操作的数据库。命令：线程的命令。时间：操作持续时间，以秒为单位。状态：线程的状态。信息：SQL语句的前100个字符。3.检查服务器的运行状态。使用SHOWSTATUS查看MySQL服务器的运行状态。有两个作用域：会话和全局。一般使用like+通配符来过滤。--查看selectmysql的个数>SHOWGLOBALSTATUSLIKE'com_select';+----------------+--------+|变量名|值|+---------------+--------+|com_select|168241|+----------------+--------+1rowinset(0.05sec)4.查看存储引擎运行信息SHOWENGINE用于显示存储引擎当前运行信息，包括事务持有的表锁和行锁信息；事务锁等待状态；线程信号量等待；文件IO请求；缓冲池统计信息和其他数据。例如：SHOWENGINEINNODBSTATUS；上面的语句可以显示innodb存储引擎当前运行的各种信息，你可以据此找出MySQL当前存在的问题。限于篇幅，这里不再解释信息的含义。你只需要知道MySQL提供了这样一个监控工具就可以了，需要的时候用。5.EXPLAIN执行计划通过慢查询日志，我们可以知道哪些SQL语句执行的比较慢，但是为什么会慢呢？慢在哪里？MySQL提供了一个执行计划查询命令EXPLAIN。通过这条命令，我们可以查看SQL的执行计划。所谓执行计划就是：优化器会不会对我们写的SQL语句进行优化（比如outerjoin而不是innerjoin查询，子查询优化为连接查询...），优化器估计执行哪些索引的成本这条SQL，最后决定使用哪个索引（或者最后选择不使用索引，而是全表扫描），优化器对单表执行的策略是什么，blahblahblah。EXPLAIN在MySQL5.6.3之后也可以解析UPDATE、DELETE、INSERT语句，但通常我们还是用它来进行SELECT查询。本文主要从多个角度介绍MySQL的优化策略，EXPLAIN的细节这里不再赘述。6、SQL和索引优化1）SQL优化SQL优化是指SQL本身的语法没有问题，但是有更好的方法可以达到同样的目的。例如：用小表带动大表；使用join重写子查询；或改为工会；在连接查询中，尽量减少驱动表的扇出（记录数），访问驱动表的开销要尽可能低。在表的连接列上创建索引，降低访问成本；被驱动表的连接列最好是表的主键或者唯一的二级索引列，这样会降低被驱动表的开销。偏移量限制大，先过滤再排序。最后一个我们举个简单的例子。下面两条语句可以达到同样的目的，但是第二条的执行效率要比第一条高很多（存储引擎使用的是InnoDB）。感受一下：--1.大偏移量查询mysql>SELECT*FROMuser_innodbLIMIT9000000,10;空集(8.18sec)--2.先过滤ID（因为ID使用了索引），然后limitmysql>SELECT*FROMuser_innodbWHEREid>9000000LIMIT10;空集(0.02秒)2)索引优化为慢速查询创建合适的索引是一种非常常用且非常有效的方法，但是索引是否会被有效地使用是另一门学问。四、存储引擎和表结构1、选择存储引擎一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求不断提高的时候，存储引擎的选择也成为了一个关键的影响因素.建议根据不同的业务选择不同的存储引擎。例如，对于查询操作和插入操作较多的业务表，建议使用MyISAM；内存用于临时表；InnoDB用于并发量大，更新多的业务；如果你不知道选择什么，就默认。2.字段优化字段优化的最终原则是：使用能正确存储数据的最小数据类型。1）整数类型MySQL提供了六种整数类型，分别是：tinyintsmallintmediumintintintegerbigint不同存储类型的最大存储范围不同，占用的存储空间自然不同。比如建议使用tinyint而不是bigint来表示是否删除。2）字符类型你是不是直接把string字段都设置成varchar格式了？就算不够也直接设置成varchar(1024)的长度？如果不确定字段的长度，必须选择varchar，但是varchar需要额外的空间来记录当前字段的长度；所以如果字段的长度是固定的，尽量使用char，这样会为你节省大量的内存空间。3）Non-empty非空字段尽量设置为NOTNULL，并提供默认值，或者用特殊值代替NULL。因为NULL类型的存储和优化性能会很差，具体原因这里就不展开了。4）不要使用外键、触发器和视图函数。这也是《阿里巴巴开发手册》中提到的原则。原因有3个：可读性降低，检查代码的同时必须检查数据库的代码；测试的工作应该由开发人员完成，而不是依赖外键。一旦使用了外键，你会发现在测试时要删除一些垃圾数据是极其困难的。5）图片、音频、视频存储不要直接存储大文件，而是存储大文件的访问地址。6）大字段拆分和数据冗余大字段拆分其实就是上面说的垂直表拆分，拆分出不常用的字段或者数据量大的字段，避免列数过多，数据量过大，尤其是你在使用的时候到写SELECT*，列数多，数据量大的问题会被严重放大！字段冗余原则上不符合数据库设计范式，但非常有利于快速检索。例如，在合约表中存储客户ID的同时，可以冗余存储客户名称，这样查询时就不需要根据客户ID获取用户名了。因此，对业务逻辑进行一定程度的冗余也是一种很好的优化技术。5.业务优化业务优化严格来说不再是MySQL调优的手段，业务优化可以有效降低数据库访问压力。这方面的典型例子是淘宝。下面举几个简单的例子，给大家提供一些思路：过去，买买买的模式是从双11当晚开始的，近年来，双11的预售战线越来越长，越来越多。时间更长，提前半个多月开始，各种存款红包模式层出不穷。这种方法称为预售导流。这样可以分流客服请求，不用等到双十一凌晨集体下单；双十一凌晨想查看当天以外的订单，查询失败；就连支付宝的吃鸡口粮也都延迟了。这是一种降级策略，为不重要的服务聚集计算资源，以保证当前的核心业务；双十一期间，支付宝强烈推荐使用花呗支付，不要使用银行卡支付。虽然有一部分考虑是为了提高软件的粘性，但另一方面，使用余额宝实际使用的阿里内部服务器访问速度快，而使用银行卡需要调用银行的接口，这就多了比较慢。
