如果面试官问你：你会从哪些维度优化MySQL性能？你会怎么回答？所谓性能优化一般是针对MySQL查询的优化。既然是优化查询，我们自然要知道查询操作会经过哪些环节，然后再思考哪些环节可以优化。在SQL查询环节，下面从5个角度介绍MySQL优化的一些策略。1、连接配置优化处理连接是MySQL客户端与MySQL服务器建立关系的第一步。第一步做不好，后面的故事就不说了。既然连接是双方的事情，我们自然要从服务端和客户端两方面进行优化。1.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作者精心设计的，完全可以满足大部分情况的需要。在不了解参数含义的情况下不建议贸然行动。修订。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读写分离（集群，主从复制）在项目初期，数据库通常运行在一台服务器上，用户所有的读写请求都会直接影响到这台数据库服务器，单机server承接毕竟并发量是有限的。为了解决这个问题，我们可以同时使用多台数据库服务器，将其中一台作为组长，称为master节点，其他节点作为组成员，称为slaves。用户写入的数据只写入主节点，而读请求分配给各个从节点。这种方案称为读写分离。给组长和组员一个小组起个名字叫集群。这是集群注：很多开发者不满master-slave这个攻击性词汇（因为他们认为这会和种族歧视、黑奴等联系在一起），所以发起了更名运动。受此影响，MySQL将逐渐停止使用master和slave等术语，取而代之的是source和replica。你只需要在遇到他们的时候就明白。使用集群不可避免地面临一个问题，就是如何保持多个节点之间的数据一致性。毕竟写请求只是发送给master节点，只有master节点的数据才是最新的数据。如何将对master节点的写操作同步到各个slave节点？主从复制技术来袭！我在一条SQL更新语句中是如何实现的？简单介绍了下binlog日志，我直接搬过来了。Binlog是实现MySQL主从复制功能的核心组件。主节点会将所有的写操作记录在binlog中，从节点会有专门的I/O线程读取主节点的binlog并将写操作同步到当前从节点。主从复制的集群架构对于缓解主数据库服务器的压力有很好的效果，但是随着业务数据越来越多，如果某个表的数据量激增，单表的查询性能会下降明显下降，这个问题不是读写分离就能解决的。毕竟，所有节点都存储完全相同的数据。单表查询性能差，自然是所有节点性能差。这时候我们可以将单个节点的数据分散到多个节点存储，也就是分库分表。2.3分库分表分库分表节点的含义比较广泛。如果数据库作为节点，则为子数据库；如果单表作为节点，则为子表。分库分表大家都知道，分为垂直分库、垂直分表、水平分库和水平分表，但是我每次都记不住这些概念，下面详细解释一下帮助你理解。2.3.1垂直分片垂直分片是在单个数据库的基础上，按照业务逻辑拆分成不同的数据库的垂直分片。这就是垂直分片。垂直分库2.3.2垂直分表垂直分表垂直分表是在单表的基础上垂直切分一个（或多个），将一个表中的多个字符拆分成若干个小表。这个操作需要根据具体的业务来判断，通常将经常使用的字段（热字段）分表，不经常使用或不立即使用的字段（冷字段）分表以提高查询速度。以上图为垂直分表为例：通常商品的详细信息比较长，查看商品列表时，往往不需要立即显示商品详情（一般是点击详情按钮显示），但产品将被更新。显示重要信息（价格等）。根据这个业务逻辑，我们把原来的product表做成了垂直分表。2.3.3水平分表将单表的数据按照一定的规则（行话叫shardingrules）保存到多个数据表中，并在数据表中横向加一刀（或几刀），即水平分表。Horizo??ntalTableShardingHorizo??ntalTableSharding2.3.4Horizo??ntalDatabaseShardingHorizo??ntaldatabasesharding是对单个数据库进行水平切分，通常伴随着水平分表。水平分库horizo??ntalsubdatabase2.3.5小结水平分库主要是为了解决存储瓶颈；垂直分库主要是为了降低并发压力。2.4MessageQueueShaving一般情况下，用户请求会直接访问数据库。如果同时在线人数非常多，极有可能压垮数据库（参考明星出轨或公布恋情时的微博状态）。这种情况下，可以通过使用消息队列来降低数据库的压力。无论同时有多少个用户请求，都会先存储到消息队列中，然后系统从消息队列中有序的消费请求。Queuepeakshaving3.Optimizer——SQL分析和优化经过连接处理和缓存优化后，SQL查询语句就来到了parser和optimizer的领域。如果这一步有问题，那只能是SQL语句的问题。只要你的语法正确，解析器就没问题。另外，为了防止你写的SQL运行效率低下，优化器会自动进行一些优化，但是如果太差，优化器也救不了你，你只能眼睁睁看着你的SQL查询变成一个慢查询。3.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)3.1.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)3.1.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获取帮助。3.2查看正在运行的线程我们可以运行showfullprocesslist来查看MySQL中所有正在运行的线程，查看它们的状态和运行时间，找出不顺眼的，直接杀掉。其中，Id：线程的唯一标识，可以通过Id来kill指定的线程。用户：发起本帖的用户，普通账号只能查看自己的帖。Host：发起连接的ip和端口。db：线程操作的数据库。命令：线程的命令。时间：操作持续时间，以秒为单位。状态：线程的状态。信息：SQL语句的前100个字符。3.3检查服务器运行状态。使用SHOWSTATUS查看MySQL服务器的运行状态。有两个作用域：会话和全局。一般使用like+通配符来过滤。--查看selectmysql的个数>SHOWGLOBALSTATUSLIKE'com_select';+----------------+--------+|变量名|值|+---------------+--------+|com_select|168241|+----------------+--------+1rowinset(0.05sec)3.4查看存储引擎运行信息SHOWENGINE用于显示存储引擎当前运行信息，包括事务持有的表锁和行锁信息；事务的锁等待状态；线程信号等待量；文件IO请求；缓冲池统计信息和其他数据。例如：SHOWENGINEINNODBSTATUS；上面的语句可以显示innodb存储引擎当前运行的各种信息，你可以据此找出MySQL当前存在的问题。限于篇幅，这里不再解释信息的含义。你只需要知道MySQL提供了这样一个监控工具就可以了，需要的时候用。3.5EXPLAIN执行计划通过慢查询日志，我们可以知道哪些SQL语句执行起来比较慢，但是为什么慢呢？慢在哪里？MySQL提供了一个执行计划查询命令EXPLAIN，通过它我们可以查看SQL的执行计划，所谓执行计划就是：优化器会不会优化我们自己写的SQL语句（比如把outerjoin改成innerjoinquery,subqueryoptimizedasajoinquery...),优化器对执行这条SQL有哪些索引的估计，最后决定使用哪个索引（或者选择不使用索引，而是全表）扫描），优化器对单个表执行什么策略，等等。EXPLAIN在MySQL5.6.3之后也可以解析UPDATE、DELETE、INSERT语句，但通常我们还是用它来进行SELECT查询。本文主要从宏观层面多角度介绍MySQL的优化策略，EXPLAIN的细节这里不再赘述，后面会另开一篇文章。3.6SQL和索引优化3.6.1SQL优化SQL优化是指SQL本身的语法没有问题，但是有更好的方法可以达到同样的目的。例如：用小表驱动大表；使用join重写子查询；或更改为工会。在连接查询中，尽量减少驱动表的扇出（记录数），访问驱动表的成本应该尽可能低。尽量在被驱动表的连接列上建立索引，降低访问成本；从动表的连接列最好是表的主键或唯一的二级索引列，这样从动表的成本会降低。对于offset大的limit，先过滤再排序。作为最后一条的简单例子，下面两条语句可以达到同样的目的，但是第二条的执行效率要比第一条高很多（存储引擎使用的是InnoDB），大家感受一下：--1.大偏移量查询mysql>SELECT*FROMuser_innodbLIMIT9000000,10;Emptyset(8.18sec)--2.先过滤ID（因为ID使用了索引），然后limitmysql>SELECT*FROMuser_innodbWHEREid>9000000限制10；空集（0.02秒）3.6.2索引优化为慢速查询创建合适的索引是一种很常用也很有效的方法，但是索引是否能被有效利用又是一门学问。4.存储引擎和表结构4.1存储引擎的选择一般我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求不断提高的时候，存储引擎的选择也成为一个关键的影响因素。建议根据不同的业务选择不同的存储引擎。例如，对于查询操作和插入操作较多的业务表，建议使用MyISAM；内存用于临时表；InnoDB用于并发量大，更新多的业务；如果你不知道选择什么，就默认。4.2字段优化字段优化的最终原则是：使用能正确存储数据的最小数据类型。4.2.1整数类型MySQL提供了六种整数类型，即tinyintsmallintmediumintintintegerbigint，不同的存储类型最大存储范围不同，占用的存储空间自然不同。比如建议使用tinyint而不是bigint来表示是否删除。4.2.2字符类型是否直接将所有字符串字段设置为varchar格式？不够就算了，你就直接设置varchar(1024)的长度？如果不确定字段的长度，必须选择varchar，但是varchar需要额外的空间来记录字段当前占用的长度；所以如果字段的长度是固定的，尽量使用char，这样会为你节省大量的内存空间。4.2.3非空非空字段应尽可能设置为NOTNULL，并提供默认值，或使用特殊值代替NULL。因为NULL类型的存储和优化性能会很差，具体原因这里就不展开了。4.2.4不要使用外键、触发器和视图函数这也是《阿里巴巴开发手册》中提到的原则。原因有3个：可读性降低，检查代码的同时必须检查数据库的代码；测试的工作应该由开发人员完成，而不是依赖外键。一旦使用了外键，你会发现在测试时要删除一些垃圾数据是极其困难的。4.2.5图片、音视频存储不要直接存储大文件，而是存储大文件的访问地址。4.2.6大字段拆分和数据冗余大字段拆分实际上就是前面提到的垂直表拆分，将不常用的字段或数据量大的字段拆分出来，避免列数和数据量过多。大，尤其是习惯写SELECT*的时候，列数多、数据量大带来的问题会被严重放大！字段冗余原则上不符合数据库设计范式，但非常有利于快速检索。例如，在合约表中存储客户ID的同时，可以冗余存储客户名称，这样查询时就不需要根据客户ID获取用户名了。因此，对业务逻辑进行一定程度的冗余也是一种很好的优化技术。5.业务优化业务优化严格来说不再是MySQL调优的手段，业务优化可以有效降低数据库访问压力。一个典型的例子就是淘宝。下面举几个简单的例子，给大家提个醒：过去，买买买的模式是从双11当晚开始的，近年来，双11的预售战线越来越长，提前半个多月开始，各种存款红包模式应运而生。没有限制，这种方式叫做预售导流。这样可以分流客户的服务请求，不用等到双十一凌晨再集体下单；双十一凌晨想查看当天以外的订单，查询失败；就连支付宝里的鸡粮都延迟了。这是一种降级策略，为不重要的服务聚集计算资源，以保证当前的核心业务；双十一期间，支付宝强烈推荐使用花呗支付，不要使用银行卡支付。虽然有一部分考虑是为了提高软件的粘性，但另一方面，使用余额宝实际使用的阿里内部服务器访问速度快，而使用银行卡需要调用银行的接口，这就多了比较慢。MySQL优化总结到此结束。还有很多细节没有提到，让我觉得这篇文章不够完美。不过有些知识点太多拆散了，不可能一下子写完，以后还是慎重写吧。
