MySQL数据库优化总结

概述

本文所指的SQL优化主要是指MySQL优化，当然思想是通用的，其他SQL语言不排除其通用性。谈到SQL优化，最好有一个指导或者规范，照着这个规范去对比，发现可以优化的地方。参考博文MySQL推荐使用规范。

总体思路
优化的总体思路，也可以说是数据库性能优化方法论。遇到性能问题时，要判断是哪一种，然后才能知道需要优化什么，及如何优化。任何计算机应用系统最终性能瓶颈问题可以归结为：

• CPU消耗
• 内存使用
• 对磁盘，网络或其他I/O设备的I/O操作。

导致慢SQL的原因
在遇到慢SQL情况时，不能简单的把原因归结为SQL编写问题，实际上导致慢SQL有很多因素，甚至包括硬件和MySQL本身的bug。出现的概率从大到小排序：

• SQL编写问题
• 锁
• 业务实例相互干绕对 IO/CPU 资源争用
• 服务器硬件
• MySQL BUG

数据库优化
数据库优化可以从架构优化，硬件优化，DB优化，SQL优化。呈倒三角，此上而下，位置越靠前优化越明显，对数据库的性能提升越高。我们常说的SQL优化反而是对性能提高最小的优化。

架构优化

常见的优化手段有：分布式缓存，读写分离，分库分表。

分布式缓存

引入分布式缓存中间件如Redis，需要考虑缓存一致性问题，即如果DB数据变更后，缓存内的数据是否能及时同步更新；也需要应对极端情况，如：缓存穿透、缓存击穿和缓存雪崩的问题。

读写分离

读写分离，常用于读多写少的应用场景，通过增加数据库服务器节点，形成一主多从的架构，主库负责接受写请求，从库负责接受读（查询）请求。

主从之间，通过binlog同步数据。当准备实施读写分离时，为了保证高可用，需要实现故障的自动转移，主从架构会有潜在主从不一致性问题。

分库分表

包括垂直拆分和水平拆分

垂直拆分

垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联。
优点：

• 可以使得行数据变小，一个数据块(Block)就能存放更多的数据，在查询时就会减少I/O次数(每次查询时读取的Block 就少)
• 可以达到最大化利用Cache的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起
• 数据维护简单

缺点：

• 主键出现冗余，需要管理冗余列
• 会引起表连接JOIN操作（增加CPU开销）可以通过在业务服务器上进行join来减少数据库压力
• 依然存在单表数据量过大的问题（需要水平拆分）
• 事务处理复杂

水平拆分

分区也是一种简单的水平拆分。MySQL5.1版本开始就支持分区。参考MySQL分区Partition

水平拆分是通过某种策略将数据分片来存储，分库内分表和分库两部分，每片数据会分散到不同的MySQL表或库，达到分布式的效果，能够支持非常大的数据量。前面的表分区本质上也是一种特殊的库内分表 库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻MySQL服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的IO、CPU、网络，这个就要通过分库来解决

前面垂直拆分的用户表如果进行水平拆分，结果是：

实际情况中往往会是垂直拆分和水平拆分的结合，即将Users_A_M和Users_N_Z再拆成Users和UserExtras，这样一共四张表

优点：
不存在单库大数据和高并发的性能瓶颈
应用端改造较少
提高了系统的稳定性和负载能力

缺点：

• 分片事务一致性难以解决
• 跨节点Join性能差，逻辑复杂
• 数据多次扩展难度跟维护量极大

分片原则

• 能不分就不分，参考单表优化
• 分片数量尽量少，分片尽量均匀分布在多个数据结点上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量
• 分片规则需要慎重选择做好提前规划，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容
• 尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题
• 查询条件尽量优化，尽量避免Select * 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。
• 通过数据冗余和表分区赖降低跨库Join的可能

这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合适用时间范围分片，因为具有时效性的数据，我们往往关注其近期的数据，查询条件中往往带有时间字段进行过滤，比较好的方案是，当前活跃的数据，采用跨度比较短的时间段进行分片，而历史性的数据，则采用比较长的跨度存储。

总体上来说，分片的选择是取决于最频繁的查询SQL的条件，因为不带任何Where语句的查询SQL，会遍历所有的分片，性能相对最差，因此这种SQL越多，对系统的影响越大，所以我们要尽量避免这种SQL的产生。

总结

1. 读写分离主要是用于解决数据库读性能问题
2. 水平切分主要是用于解决数据库数据量大的问题
3. 分布式缓存架构可能比读写分离更适用于高并发、大数据量大场景

硬件优化

以机械硬盘、普通固态硬盘、PCIE固态硬盘三种不同的硬盘为例，来看他们的评测数据：

吞吐率：单位时间内读写的数据量
机械硬盘：约100MB/s ～ 200MB/s
普通固态硬盘：200MB/s ～ 500MB/s
PCIE固态硬盘：900MB/s ～ 3GB/s

IOPS：每秒IO操作的次数
机械硬盘：100 ～200
普通固态硬盘：30000 ～ 50000
PCIE固态硬盘：数十万

可以明显发现：越昂贵的硬盘，其运行效率越高，对SQL执行的效率提升越明显。

DB优化

数据库实例参数优化遵循三句口诀：日志不能小、缓存足够大、连接要够用。

数据库事务提交后需要将事务对数据页的修改刷（fsync）到磁盘上，才能保证数据的持久性。这个刷盘，是一个随机写，性能较低，如果每次事务提交都要刷盘，会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法：

• 先将事务写到日志文件RedoLog（WAL），将随机写优化成顺序写
• 加一层缓存结构Buffer，将单次写优化成顺序写

所以日志跟缓存对数据库实例尤其重要。而连接如果不够用，数据库会直接抛出异常，系统无法访问。

常见的参数配置：

• sync_binlog：可选值，1，100，0，1安全性最好
• max_connections：根据业务调整
• innodb_flush_log_at_trx_commit：2，安全和性能的折中考虑
• interactive_timeout：
• back_log：back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。可以从默认的50升至500
• wait_timeout：数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时
• max_user_connection: 最大连接数，默认为0无上限，最好设一个合理上限
• thread_concurrency：并发线程数，设为CPU核数的两倍
• skip_name_resolve：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问
• key_buffer_size：索引块的缓存大小，增加会提升索引处理速度，对MyISAM表性能影响最大。对于内存4G左右，可设为256M或384M，通过查询show status like 'key_read%'，保证key_reads / key_read_requests在0.1%以下最好
• innodb_buffer_pool_size：缓存数据块和索引块，对InnoDB表性能影响最大。通过查询show status like 'Innodb_buffer_pool_read%'，保证(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests越高越好
• innodb_additional_mem_pool_size：InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小
• innodb_log_buffer_size：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB
• query_cache_size：缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失。根据命中率Qcache_hits/(Qcache_hits + Qcache_inserts)*100)进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大。可以通过命令show status like 'Qcache_%'查看目前系统Query catch使用大小
• read_buffer_size：MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能
• sort_buffer_size：MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小
• read_rnd_buffer_size：MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
• record_buffer：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值
• thread_cache_size：保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的
• table_cache：类似于thread_cache_size，但用来缓存表文件，对InnoDB效果不大，主要用于MyISAM

SQL优化

通过工具可以帮助定位慢SQL，以及针对性地进行SQL优化。

SQL优化技巧有很多，包括单表优化和多表优化，简单分类如下：

• 字段
• 索引
• SELECT
• IN，OR，UNION
• WHERE
• 临时表
• JOIN

字段

• 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
• VARCHAR的长度只分配真正需要的空间
• 使用枚举或整数代替字符串类型
• 尽量使用TIMESTAMP而非DATETIME，
• 单表不要有太多字段，建议在20以内
• 避免使用NULL字段，很难查询优化且占用额外索引空间
• 用整型来存IP

索引

• 检查索引：在SQL语句的WHERE和JOIN部分中用到的所有字段上，都应该加上索引
• 不要在区分度不大的字段建立索引
  在数据区分度不大的字段使用索引，不但不会降低逻辑I/O，相反往往会增加大量逻辑I/O降低性能。如性别列，男和女。经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引。
• 避免在索引列上使用IS NULL或者NOT
  避免在索引中使用任何可以为空的列，导致无法使用索引
• 对于联合索引来说，要遵守最左前缀法则
  举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。
• 必要时使用force index来强制查询走某个索引
  有时MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是想要的。此时采用force index来强制优化器使用指定的索引。
• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
• 如果排序字段没有用到索引，就尽量少排序
• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数较好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
• 尽量使用最左匹配的模糊查询：name like 'ABC%'方式，优于name like '%ABC%'。

SELECT

不用使用select *，务必指明字段名称：

• select * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）
• 每次查询都要回表，不能走覆盖索引
• 当表结构发生改变时，前端也需要更新
• 查询中包含的列越少，IO开销就越小
• 额外（不需要）的字段会导致更多的数据被返回到SQL客户端
• SQL解析时，需要把*依次转换为所有的列名，需查询数据字典，增加解析成本
• 即使是需要查询所有列时，也不要使用select *

IN, OR, UNION

• SQL语句中IN包含的值不应过多
  MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。如：select id from t where num in(1,2,3)，对于连续数值，能用between就不要用in；再或者使用连接来替换。
• OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
• 如果限制条件中其他字段没有索引，尽量少用or
  or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或是union（必要时）的方式来代替or效果更好。
• 尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20，可以这样查询：select id from t where num=10 union all select id from t where num=20
• 尽量用union all代替union
  union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，涉及排序，增加大量的CPU运算，加大资源消耗及延迟。union all的前提是两个结果集没有重复数据。

WHERE

• 不要在where子句中的=左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引
  如：select user_id from user where age*2=36;会造成引擎放弃使用索引，建议改成：select user_id from user where age=36/2;
• 避免在where子句中对字段进行null值判断。对null的判断会导致引擎放弃使用索引而进行全表扫描。如select id from t where num is null，可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0
• 避免隐式类型转换
  where子句中出现字段的类型和传入的参数类型不一致时发生类型转换。
• 注意范围查询语句
  对于联合索引来说，如果存在范围查询，如between、>、<等条件时，会造成后面的索引字段失效。
• 负向条件查询不能使用索引
  举例select * from order where status!=0 and stauts!=1，not in/not exists都不是好习惯，可以优化为in查询：select * from order where status in(2,3)

临时表

• 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
• 避免频繁创建和删除临时表，以减少系统表资源的消耗。
• 临时表并不是不可使用，适当地使用可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，较好使用导出表。
• 在新建临时表时，如果一次性插入数据量很大，那可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
• 如果使用到临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

JOIN

对于查询语句：select A.id, B.name from A join B on A.id = B.user_id

LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。

• MySQL中没有full join，可以用以下方式来解决：select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;
• 尽量使用inner join，避免left join
  参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。
• 合理利用索引
  被驱动表的索引字段作为on的限制字段。
• 利用小表去驱动大表
  
  如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。
• 巧用STRAIGHT_JOIN
  inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。这个方式有时能减少3倍的时间。
  

常见的优化技巧：

1. 合理使用索引
   索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能 选择率高（重复值少）且被where频繁引用需要建立B树索引；一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况
2. 使用UNION ALL替代UNION
   UNION ALL的执行效率比UNION高，UNION执行时需要排重；UNION需要对数据进行排序
3. JOIN字段建议建立索引：一般JOIN字段都提前加上索引
4. 避免复杂SQL语句：提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理
5. 避免where 1=1写法
6. 避免order by rand()类似写法，RAND()导致数据列被多次扫描

工具

在日常开发工作中，可以做一些工作达到预防慢SQL问题，比如在上线前预先用诊断工具对SQL进行分析。常用的工具有：

• mysqldumpslow
• mysql profile
• mysql explain

SQL优化过程

1. 定位有问题的语句
2. 检查执行计划
3. 检查执行计划中优化器的统计信息
4. 分析相关表的记录数、索引情况
5. 改写SQL语句、使用HINT、调整索引、表分析
6. 有些SQL语句不具备优化的可能，需要优化处理方式
7. 达到最佳执行计划

SQL优化方法

1. 优化业务数据
2. 优化数据设计
3. 优化流程设计
4. 优化SQL语句
5. 优化物理结构
6. 优化内存分配
7. 优化I/O
8. 优化内存竞争
9. 优化操作系统

优化目标：

1. SQL语句尽量简单，模块化；
2. 易读，易维护；
3. 节省资源：内存、CPU、扫描的数据块要少、少排序等；
4. 不造成死锁等。

SQL优化层级
应用程序级调优

• sql语句调优
• 管理变化调优

实例级调优

• 内存
• 数据结构
• 实例配置

操作系统交互

• I/O
• swap
• Parameters

执行计划

提到SQL优化，一定要知道执行计划，也要能看懂执行计划。通过EXPLAIN关键词获取执行计划：
desc/explain sql;
extended explain sql;
show warnings：可以查看实际执行的语句，告警信息

重点关注下面5个指标数据：

• type：连接类型，至少要达到range级别，杜绝出现all级别。
• key：使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
• key_len：索引长度。
• rows：扫描行数，是预估值。
• extra：详细说明。常见的不太友好的值：Using filesort，Using temporary。

当只需要一条数据或明确知道只有一条返回结果时，使用limit 1
使EXPLAIN中type列达到const类型；数据库并不知道只有一条数据，加上limit 1让它主动停止游标移动；

提高性能的特性

索引覆盖(covering index)：需要查询的数据在索引上都可以查到不需要回表 EXTRA列显示using index；
ICP特性(Index Condition Pushdown)：本来index仅仅是data access的一种访问模式，存数引擎通过索引回表获取的数据会传递到MySQL Server层进行where条件过滤。5.6版本开始当ICP打开时，如果部分where条件能使用索引的字段，MySQL Server会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤。EXTRA显示using index condition。需要了解MySQL的架构图分为Server和存储引擎层；
索引合并(index merge)：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。一般用OR会用到，如果是AND条件，考虑建立复合索引。EXPLAIN显示的索引类型会显示index_merge，EXTRA会显示具体的合并算法和用到的索引。

extra字段

• using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”，其实不一定是文件排序，内部使用的是快排；
• using temporary：使用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by；
• using index：表示相应的SELECT操作中使用覆盖索引（Covering Index），避免访问表的数据行，效率不错；
• impossible where：WHERE子句的值总是false，不能用来获取任何元组；
• select tables optimized away：在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化；
• distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的操作。

using filesort、using temporary十分耗费性能，在使用group by时，虽然没有使用order by，如果没有索引，是可能同时出现using filesort，using temporary的，因为group by就是先排序在分组，如果没有排序的需要，可以加上一个order by NULL来避免排序，这样using filesort就会去除，能提升一点性能。

type字段

• system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现；
• const：如果通过索引依次就找到了，const用于比较主键索引或者unique索引。因为只能匹配一行数据，所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个常量；
• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体；
• range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为只需要开始于缩印的某一点，而结束于另一点，不用扫描全部索引；
• index：Full Index Scan ，index与ALL的区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小，也就是说虽然ALL和index都是读全表，但index是从索引中读取的，而ALL是从硬盘读取的；
• all：Full Table Scan，遍历全表获得匹配的行。

慢SQL

参考
高效SQL性能优化极简教程
项目中常用的19条MySQL优化