有哪些常见的数据库优化方法？

数据库优化的几个方面：SQL语句以及有效索引、数据结构、系统配置、硬件

1. SQL以及索引的优化是最重要的。首先要根据需求写出结构良好的SQL，然后根据SQL在表中建立有效的索引。但是如果索引太多，不但会影响写入的效率，对查询也有一定的影响。
2. 要根据一些范式来进行表结构的设计。设计表结构时，就需要考虑如何设计才能够更有效的查询。
3. 系统配置的优化。MySQL数据库是基于文件的，如果打开的文件数达到一定的数量，无法打开之后就会进行频繁的IO操作。
4. 硬件优化。更快的IO、更多的内存。一般来说内存越大，对于数据库的操作越好。但是CPU多就不一定了，因为他并不会用到太多的CPU数量，有很多的查询都是单CPU。另外使用高的IO（SSD、RAID），但是IO并不能减少数据库锁的机制。所以说如果查询缓慢是因为数据库内部的一些锁引起的，那么硬件优化就没有什么意义。

一、SQL语句优化

1、通过慢查询日志发现有效率问题的SQL

可以通过开启慢查询日志的方式进行定位有问题的SQL

（1）查看MySQL是否开启慢查询日志

show variables like 'slow_query_log';

（2）设置没有索引的记录到慢查询日志

set global log_queries_not_using_indexes=on;

（3）查看超过多长时间的sql进行记录到慢查询日志

show variables like 'long_query_time'

（4）开启慢查询日志

set global slow_query_log=on

（5）设置超时时间

Set global long_query_time=5;--超过5s的语句才记录日志

（6）查看慢查询日志的位置

show variables like 'slow%'

2、慢查询日志内容分析

慢查询日志主要分为5部分，第一部分是慢查询时间，第二部分是慢查询的来源主机和用户，第三部分是查询的执行时间、锁定时间、发送的行数、扫描的行数。最后是时间戳形式记录的命令以及该命令的执行的时间戳。

系统运行一段时间后，慢查询日志可能比较多，需要通过mysqldumpslow、pt-query-digest等工具分析慢查询日志。

3、通过explain查看SQL的执行计划

具体的分析过程可以参考这里，里面的例子描述的很清晰。

二、索引优化

1、选择索引

（1）选择合适的索引列，选择在where，group by，order by，on从句中出现的列作为索引项，对于离散度不大的列没有必要创建索引。

（2）索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好 )

（3）离散度大得列放在联合索引前面

判断离散程度的方法是：

select count(distinct ziduan1),count(distinct ziduan2) from tablename
越大越离散

2、索引优化方法

索引一般情况下都是高效的。不过凡是都有两面性，索引是以空间换时间的一种策略，索引本身在提高查询效率的同时会影响插入、更新、删除的效率。不当的使用索引不仅增加了写操作的负担，也会影响读取的效率。索引越多，数据库分析的越慢。注意点：

（1）InnoDB 每个索引都会加上主键，联合索引不要加上主键，innodb会自动加，否则会冗余。

（2）索引存在的目的是为了加快查询的效率，不过不是索引越多越好，建立索引要适当才好。过多的索引会增加数据库判断使用什么索引来查询的开销，所以，有时候也会出现以去掉重复或者无效的索引为优化手段的优化方式。

（3）主键已经是索引了，所以primay key 的主键不用再设置unique唯一索引了。

3、索引的原理可以参考这里或者这里。理解索引原理对于索引优化有很大帮助。

三、数据表结构优化

1、选择合适的数据类型
（1）使用可存下数据的最小的数据类型。

（2）使用简单地数据类型，int要比varchar类型在mysql处理上更简单。

（3）尽可能使用not null定义字段，这是由innodb的特性决定的，因为非not null的数据可能需要一些额外的字段进行存储，这样就会增加一些IO。可以对非null的字段设置一个默认值。

（4）尽量少用text，非用不可最好分表，将text字段存放到另一张表中，在需要的时候再使用联合查询，这样可提高查询主表的效率。

例子1、用int存储日期时间

from_unixtime()可将Int类型的时间戳转换为时间格式

select from_unixtime(1392178320); 输出为 2014-02-12 12:12:00unix_timestamp()可将时间格式转换为Int类型

select unix_timestamp('2014-02-12 12:12:00'); 输出为1392178320

例子2、存储IP地址——bigInt

利用inet_aton(),inet_ntoa()转换

select inet_aton('192.169.1.1'); 输出为3232301313

select inet_ntoa(3232301313); 输出为192.169.1.1

四、数据库配置优化

这方面目前了解的并不多，参考这里吧。

五、硬件优化

硬件层面的优化是最后的手段。主要需考虑CPU、存储、网络等几个方面。

CPU：CPU并不是越多越好，之前看到网上的分析有说很多的查询都是单CPU的，增加CPU数量并不能提高性能。

存储：机械磁盘 or SSD（当然是SSD更快）；单个大磁盘 or 多个小磁盘组合使用（单个1T的磁盘应该没有2个500G磁盘的组合快，因为磁盘的转速都是固定的，两个磁盘相当于可以并行的读取）

网络：一般不是问题，但是在分布式的集群环境中，各个数据库节点之间的网络环境经常会称为系统的瓶颈。另外，如果服务端和数据库分布在不同的城市，一条简单SQL传输的时间可能就要几十毫秒。

以上答案来自我厂陈庆麟老师的博文《MySQL数据库优化两三事》，文章从一个整体角度阐述下可以从哪些方面优化数据库，提高数据库的效率。

关于我们：

网易数帆产品限时开放试用中，立即0成本体验！

我们帮助各行业客户数字化转型升级，成功实现业务增长。点击查看部分案例，解锁企业专属转型新思路：

哇，竟然看到数据库优化的问题。知乎有救。LOL。

数据库优化分三个等级。

第一级就是常见的简单DDL DML调优。SQL写的好不好啊，group by order by对不对啊，select别用*，记得要要hit index啊。数据库表设计好点啊，主键外键索引啊，执行计划看一看啊。市面上九成九的数据库调优都在这。

第二级是DBA层级调优。MSSQL，ORACLE，MYSQL都各自有几十项配置。在不同的应用场景下需要针对性调整。比如MSSQL 的MAXDOP在有高并发且大SQL的情况下要适当调整限制，而全部是小SQL的情况下可以设置为无限。比如ORACLE的自动优化器在SQL性状相似的时候不开也没太大问题，CPU超过16个core的时候，parallel hint到底设置为几。这些都是学问。都没个定数，都是要DBA拿实际生产数据套研究的。oracle的AWR， entierprise manager， MSSQL的一堆管理SP，trace log，都是必读项。到这层，已经是每个公司每百程序员1，2个人的事情了。

最后一级是数据库中间件和infra层级调优。上个月我处理过一个MSSQL集群死慢死慢，但是单node就没问题。后来查出来是windows 集群的仲裁设置有问题，导致太频繁failover，虽然failover号称无缝，但是实际上还是有10秒左右数据库无法访问的。这一级，基本上是千人以上的公司才会遇到的问题，也就几个人懂，但凡其中有2个人同时休假，就歇了。

点赞再看，养成习惯

面试官：敖丙你简历上写了你会数据库调优，你都是怎么调优的？

敖丙：加索引。

面试官：还有么？

敖丙：没了。

面试官：我们公司的门你知道在哪里吧，自己走还是我送你？

哈哈开头这个场景是我臆想的一个面试场景，但是大家是不是觉得很真实，每个人的简历上但凡写到了数据库，都会在后面顺便写一句，会数据库调优。

但是问题就来了，面试官一问到数据库调优的，大家就说加索引，除了加索引大家还知道别的么？

或者索引相关的点你全部都知道么？聚簇索引，非聚簇索引，普通索引，唯一索引，change buffer，表锁、行锁、间隙锁以及行锁并发情况下的最大TPS是多少？还有索引为啥会选择错误？这些大家知道嘛？

我觉得调优能回答的点还是很多很多的，我自己看了《MySQL实战》、《高性能MySQL》、《丁奇MySQL47讲》之后总结了自己去面试回答的一套逻辑，个人觉得是比较不错的一套combo，这套连招下来，一般面试官都会暗自对你竖起大拇指，反正我面试的时候基本上就是这一套。

内容就是个人理解的总结，还有书中内容的复述，需要一定的数据库知识，不过我想大家都点进来了，肯定都会了。

数据库调优其实一般情况都是我们的SQL调优，SQL的调优就可以解决大部分问题了，当然也不排除SQL执行环节的调优。

我之前在索引和数据库基础环节有介绍过相关的基础知识，这里就不过多的赘述了，但是数据库的组成可能很多小伙伴都忘记了，那我们再看一遍结构图吧。

我们所谓的调优也就是在，执行器执行之前的分析器，优化器阶段完成的，那我们开发工作中怎么去调优的呢？

帅丙一般在开发涉及SQL的业务都会去本地环境跑一遍SQL，用explain去看一下执行计划，看看分析的结果是否符合自己的预期，用没用到相关的索引，然后再去线上环境跑一下看看执行时间（这里只有查询语句，修改语句也无法在线上执行）。

遇SQL不决explain，但是这里就要说到第一个坑了。

因为在MySQL8.0之前我们的数据库是存在缓存这样的情况的，我之前就被坑过，因为存在缓存，我发现我sql怎么执行都是很快，当然第一次其实不快但是我没注意到，以至于上线后因为缓存经常失效，导致rt（Response time）时高时低。

后面就发现了是缓存的问题，我们在执行SQL的时候，记得加上SQL NoCache去跑SQL，这样跑出来的时间就是真实的查询时间了。

我说一下为什么缓存会失效，而且是经常失效。

如果我们当前的MySQL版本支持缓存而且我们又开启了缓存，那每次请求的查询语句和结果都会以key-value的形式缓存在内存中的，大家也看到我们的结构图了，一个请求会先去看缓存是否存在，不存在才会走解析器。

缓存失效比较频繁的原因就是，只要我们一对表进行更新，那这个表所有的缓存都会被清空，其实我们很少存在不更新的表，特别是我之前的电商场景，可能静态表可以用到缓存，但是我们都走大数据离线分析，缓存也就没用了。

大家如果是8.0以上的版本就不用担心这个问题，如果是8.0之下的版本，记得排除缓存的干扰。

最开始提到了用执行计划去分析，我想explain是大家SQL调优都会回答到的吧。

因为这基本上是写SQL的必备操作，那我现在问大家一个我去阿里面试被问过的一个问题：explain你记得哪些字段，分别有什么含义？

当时我就回答上来三个，我默认大家都是有数据库基础的，所以每个我这里不具体讨论每个字段，怕大家忘记我贴一遍图大家自己回忆一下。

那我再问大家一下，你们认为统计这个统计的行数就是完全对的么？索引一定会走到最优索引么？

当然我都这么问了，你们肯定也知道结果了，行数只是一个接近的数字，不是完全正确的，索引也不一定就是走最优的，是可能走错的。

我的总行数大概有10W行，但是我去用explain去分析sql的时候，就会发现只得到了9.4W，为啥行数只是个近视值呢？

看过基础章节的小伙伴都知道，MySQL中数据的单位都是页，MySQL又采用了采样统计的方法，采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

我们数据是一直在变的，所以索引的统计信息也是会变的，会根据一个阈值，重新做统计。

至于MySQL索引可能走错也很好理解，如果走A索引要扫描100行，B所有只要20行，但是他可能选择走A索引，你可能会想MySQL是不是有病啊，其实不是的。

一般走错都是因为优化器在选择的时候发现，走A索引没有额外的代价，比如走B索引并不能直接拿到我们的值，还需要回到主键索引才可以拿到，多了一次回表的过程，这个也是会被优化器考虑进去的。

他发现走A索引不需要回表，没有额外的开销，所有他选错了。

如果是上面的统计信息错了，那简单，我们用analyze table tablename 就可以重新统计索引信息了，所以在实践中，如果你发现explain的结果预估的rows值跟实际情况差距比较大，可以采用这个方法来处理。

还有一个方法就是force index强制走正确的索引，或者优化SQL，最后实在不行，可以新建索引，或者删掉错误的索引。

上面我提到了，可能需要回表这样的操作，那我们怎么能做到不回表呢？在自己的索引上就查到自己想要的，不要去主键索引查了。

覆盖索引

如果在我们建立的索引上就已经有我们需要的字段，就不需要回表了，在电商里面也是很常见的，我们需要去商品表通过各种信息查询到商品id，id一般都是主键，可能sql类似这样：

select itemId from itemCenter where size between 1 and 6

因为商品id itemId一般都是主键，在size索引上肯定会有我们这个值，这个时候就不需要回主键表去查询id信息了。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

还是商品表举例，我们需要根据他的名称，去查他的库存，假设这是一个很高频的查询请求，你会怎么建立索引呢？

大家可以思考上面的回表的消耗对SQL进行优化。

是的建立一个，名称和库存的联合索引，这样名称查出来就可以看到库存了，不需要查出id之后去回表再查询库存了，联合索引在我们开发过程中也是常见的，但是并不是可以一直建立的，大家要思考索引占据的空间。

刚才我举的例子其实有点生硬，正常通过商品名称去查询库存的请求是不多的，但是也不代表没有哈，真来了，难道我们去全表扫描？

大家在写sql的时候，最好能利用到现有的SQL最大化利用，像上面的场景，如果利用一个模糊查询 itemname like ’敖丙%‘，这样还是能利用到这个索引的，而且如果有这样的联合索引，大家也没必要去新建一个商品名称单独的索引了。

很多时候我们索引可能没建对，那你调整一下顺序，可能就可以优化到整个SQL了。

你已经知道了前缀索引规则，那我就说一个官方帮我们优化的东西，索引下推。

select * from itemcenter where name like '敖%' and size=22 and age=20;

所以这个语句在搜索索引树的时候，只能用 “敖”，找到第一个满足条件的记录ID1，当然，这还不错，总比全表扫描要好。

然后呢？

当然是判断其他条件是否满足，比如size。

在MySQL 5.6之前，只能从ID1开始一个个回表，到主键索引上找出数据行，再对比字段值。

而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

这个在我的面试视频里面其实问了好几次了，核心是需要回答到change buffer，那change buffer又是个什么东西呢？

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。

除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率

那么，什么条件下可以使用change buffer呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。

要判断表中是否存在这个数据，而这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

change buffer用的是buffer pool里的内存，因此不能无限增大，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置，这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一，change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好，这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价，所以，对于这种业务模式来说，change buffer反而起到了副作用。

我们存在邮箱作为用户名的情况，每个人的邮箱都是不一样的，那我们是不是可以在邮箱上建立索引，但是邮箱这么长，我们怎么去建立索引呢？

MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

我们是否可以建立一个区分度很高的前缀索引，达到优化和节约空间的目的呢？

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

上面说过覆盖索引了，覆盖索引是不需要回表的，但是前缀索引，即使你的联合索引已经包涵了相关信息，他还是会回表，因为他不确定你到底是不是一个完整的信息，就算你是www.aobing@mogu.com一个完整的邮箱去查询，他还是不知道你是否是完整的，所以他需要回表去判断一下。

下面这个也是我在阿里面试面试官问过我的，很长的字段，想做索引我们怎么去优化他呢？

因为存在一个磁盘占用的问题，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

我当时就回答了一个hash，把字段hash为另外一个字段存起来，每次校验hash就好了，hash的索引也不大。

我们都知道只要区分度过高，都可以，那我们可以采用倒序，或者删减字符串这样的情况去建立我们自己的区分度，不过大家需要注意的是，调用函数也是一次开销哟，这点当时没注意。

就比如本来是www.aobing@qq,com 其实前面的www.基本上是没任何区分度的，所有人的邮箱都是这么开头的，你一搜一大堆出来，放在索引还浪费内存，你可以substring()函数截取掉前面的，然后建立索引。

我们所有人的身份证都是区域开头的，同区域的人很多，那怎么做良好的区分呢？REVERSE（）函数翻转一下，区分度可能就高了。

这些操作都用到了函数，我就说一下函数的坑。

日常开发过程中，大家经常对很多字段进行函数操作，如果对日期字段操作，浮点字符操作等等，大家需要注意的是，如果对字段做了函数计算，就用不上索引了，这是MySQL的规定。

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

需要注意的是，优化器并不是要放弃使用这个索引。

这个时候大家可以用一些取巧的方法，比如 select * from tradelog where id + 1=10000 就走不上索引，select * from tradelog where id=9999就可以。

select * from t where id=1

如果id是字符类型的，1是数字类型的，你用explain会发现走了全表扫描，根本用不上索引，为啥呢？

因为MySQL底层会对你的比较进行转换，相当于加了 CAST( id AS signed int) 这样的一个函数，上面说过函数会导致走不上索引。

还是一样的问题，如果两个表的字符集不一样，一个是utf8mb4，一个是utf8，因为utf8mb4是utf8的超集，所以一旦两个字符比较，就会转换为utf8mb4再比较。

转换的过程相当于加了CONVERT(id USING utf8mb4)函数，那又回到上面的问题了，用到函数就用不上索引了。

还有大家一会可能会遇到mysql突然卡顿的情况，那可能是MySQLflush了。

redo log大家都知道，也就是我们对数据库操作的日志，他是在内存中的，每次操作一旦写了redo log就会立马返回结果，但是这个redo log总会找个时间去更新到磁盘，这个操作就是flush。

在更新之前，当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。

内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页“。

那什么时候会flush呢？

1. InnoDB的redo log写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。
   
2. 系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
   

你一定会说，这时候难道不能直接把内存淘汰掉，下次需要请求的时候，从磁盘读入数据页，然后拿redo log出来应用不就行了？

这里其实是从性能考虑的，如果刷脏页一定会写盘，就保证了每个数据页有两种状态：

• 一种是内存里存在，内存里就肯定是正确的结果，直接返回；
• 另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。 这样的效率最高。

1. MySQL认为系统“空闲”的时候，只要有机会就刷一点“脏页”。
   
2. MySQL正常关闭，这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。
   

那我们怎么做才能把握flush的时机呢？

Innodb刷脏页控制策略，我们每个电脑主机的io能力是不一样的，你要正确地告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。

这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力，这个值建议设置成磁盘的IOPS，磁盘的IOPS可以通过fio这个工具来测试。

正确地设置innodb_io_capacity参数，可以有效的解决这个问题。

这中间有个有意思的点，刷脏页的时候，旁边如果也是脏页，会一起刷掉的，并且如果周围还有脏页，这个连带责任制会一直蔓延，这种情况其实在机械硬盘时代比较好，一次IO就解决了所有问题，

但是现在都是固态硬盘了，innodb_flush_neighbors=0这个参数可以不产生连带制，在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了。

资料参考：《MySQL实战》、《高性能MySQL》、《丁奇MySQL47讲》

在本文中我提到了以下知识点：

应该还不算全，行锁、表锁、间隙锁、同步场景等等都没怎么提到，因为他们的场景比较复杂，每种都可以单独开一篇了，丁奇的MySQL里面算是很全了，还有就是高性能MySQL大家可以展开看看，要是懒也可以等我总结。

每个点我也没多仔细的讲解，主要是篇幅原因，其实每个点在MySQL相关书籍都是很多篇幅才介绍完的，我就做个总结，对具体的概念不了解可以用搜索引擎查询相关概念，不过我想我说得还算通俗易懂。

本文敖丙也就肝了一个多星期吧，主要是知识点的梳理，因为我也忘记得差不多了，我又回头看了一遍，然后总结了一下，还有之前的笔记还在，本文我还是不开赞赏，大家觉得可以点个在看就好了，么么。

我是敖丙，一个在互联网苟且偷生的程序猿。

你知道的越多，你不知道的越多，人才们的 【三连】 就是丙丙创作的最大动力，我们下期见！

注：如果本篇博客有任何错误和建议，欢迎人才们留言！

文章持续更新，可以微信搜索「敖丙 」第一时间阅读，本文 GitHub https://github.com/JavaFamily 已经收录，有大厂面试完整考点，欢迎Star。

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.

笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.

2.1 软优化

2.1.1 查询语句优化

• 1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.
• 2.例:

DESC SELECT * FROM `user`

显示:

其中会显示索引和查询数据读取数据条数等信息.

2.1.2 优化子查询

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

2.1.3 使用索引

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者<MySQL数据库索引>一文,介绍比较详细,此处记录使用索引的三大注意事项:

• LIKE关键字匹配'%'开头的字符串,不会使用索引.
• OR关键字的两个字段必须都是用了索引,该查询才会使用索引.
• 使用多列索引必须满足最左匹配.

2.1.4 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

2.1.5 中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

2.1.6 增加冗余字段 类似于创建中间表,增加冗余也是为了减少连接查询.

2.1.7 分析表,,检查表,优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

• 1.分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;

• Op:表示执行的操作.
• Msg_type:信息类型,有status,info,note,warning,error.
• Msg_text:显示信息.
• 2.检查表: 使用 CHECK关键字,如CHECK TABLE user[option]
• option 只对MyISAM有效,共五个参数值:
• QUICK:不扫描行,不检查错误的连接.
• FAST:只检查没有正确关闭的表.
• CHANGED:只检查上次检查后被更改的表和没被正确关闭的表.
• MEDIUM:扫描行,以验证被删除的连接是有效的,也可以计算各行关键字校验和.
• EXTENDED:最全面的的检查,对每行关键字全面查找.
• 3.优化表:使用OPTIMIZE关键字,如OPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG]TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

2.2 硬优化

2.2.1 硬件三件套

• 1.配置多核心和频率高的cpu,多核心可以执行多个线程.
• 2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
• 3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

2.2.2 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

• key_buffer_size:索引缓冲区大小
• table_cache:能同时打开表的个数
• query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.
• sort_buffer_size:排序缓冲区

2.2.3 分库分表

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

2.2.4 缓存集群

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。建议收藏备查！MySQL 常见错误代码说明

一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.

来源：https://segmentfault.com/a/1190000018631870

最近面试一些小朋友，简历上赫然写着“擅长MySQL数据库优化”。

然后，我每每好奇地问上两句，你都用了什么方式做的数据库优化啊，基本上千篇一律地回复就是三个字：“加索引。”（手动狗头）

下面跟大家成体系化地详谈一下，MySQL数据库的优化方式有哪些？

既然谈到优化，一定想到要从多个维度进行优化。

这里的优化维度有四个：硬件配置、参数配置、表结构设计和SQL语句及索引。

其中 SQL 语句相关的优化手段是最为重要的。

硬件方面的优化可以有 对磁盘进行扩容、将机械硬盘换为SSD，或是把CPU的核数往上提升一些，增强数据库的计算能力，或是把内存扩容了，让Buffer Pool能吃进更多数据， 等等。但这个优化手段成本最高，但见效最快。

有句话怎么说的来着，能通过硬件升级来解决的事情，千万别碰代码。哈哈。

MySQL 会在内存中保存一定的数据，通过 LRU（最近最少使用）算法将不常访问的数据保存在硬盘文件中。尽可能的扩大内存中的数据量，将数据保存在内存中，从内存中读取数据，可以提升 MySQL 性能。

MySQL 使用优化过后的 LRU 算法：

普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰
改进LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间 midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果 数据没有被访问，会逐步向old尾部移动，等待淘汰。每当有新的page数据读取到buffer pool时，InnoDb引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。

LRU 算法针对的是 MySQL 内存中的结构，这里有个区域叫 Buffer Pool（缓冲池） 作为数据读写的缓冲区域。把这个区域进行相应的扩大即可提升性能，当然这个参数要针对服务器硬件的实际情况进行调整。

通过以下命令可以查看相应的BufferPool的相关参数：

show global status like 'innodb_buffer_pool_pages_%'

输入以下命令可以查看 BufferPool 的大小：

show variables like "%innodb_buffer_pool_size%"

在这里我们可以修改这个参数的值，如果该服务器是 MySQL 专用的服务器，我们可以 修改为总内存的 60%~80% ，当然不能影响系统程序的运行。

这个参数是只读的，可以在 MySQL 的配置文件（my.cnf 或 my.ini）中进行修改。Linux 的配置文件为 my.cnf。

# 修改缓冲池大小为750M
innodb_buffer_pool_size=750M

数据预热相当于将磁盘中的数据提前放入 BufferPool 内存缓冲池内。一定程度提升了读取速度。

对于 InnoDB，这里提供一份预热 SQL 脚本：

#mysql5.7版本中，如果DISTINCT和order by一起使用将会报3065错误，sql语句无法执行。这是由于5.7版本语法比之前版本语法要求更加严格导致的。
#推荐在mysql的配置文件my.cnf文件(linux)/my.ini文件(window) 的mysqld中增加或者修改sql_model配置选项
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#重启后生效
SELECT DISTINCT
    CONCAT('SELECT ',rowlist,' FROM ',db,'.',tb,
    ' ORDER BY ',rowlist,';') selectSql
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) rowlist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA 
ORDER BY db,tb;

（1）增大 redo log，减少落盘次数：

redo log 是重做日志，用于保证数据的一致，减少落盘相当于减少了系统 IO 操作。

innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

（2）通用查询日志、慢查询日志可以不开 ，binlog 可开启。

通用查询和慢查询日志也是要落盘的，可以根据实际情况开启，如果不需要使用的话就可以关掉。binlog 用于恢复和主从复制，这个可以开启。

查看相关参数的命令：

# 慢查询日志
show variables like 'slow_query_log%'
# 通用查询日志
show variables like '%general%';
# 错误日志
show variables like '%log_error%'
# 二进制日志
show variables like '%binlog%';

（3）写 redo log 策略 innodb_flush_log_at_trx_commit 设置为 0 或 2

对于不需要强一致性的业务，可以设置为 0 或 2。

• 0：每隔 1 秒写日志文件和刷盘操作（写日志文件 LogBuffer --> OS cache，刷盘 OS cache --> 磁盘文件），最多丢失 1 秒数据
• 1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁 IO 操作
• 2：事务提交，立刻写日志文件，每隔 1 秒钟进行刷盘操作

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。

设计聚合表，一般针对于统计分析功能，或者实时性不高的需求（报表统计，数据分析等系统），这是一种空间 + 时延性换时间的思想。

为减少关联查询，创建合理的冗余字段（创建冗余字段还需要注意数据一致性问题），当然，如果冗余字段过多，对系统复杂度和插入性能会有影响。

分表分为垂直拆分和水平拆分两种。

垂直拆分，适用于字段太多的大表，比如：一个表有100多个字段，那么可以把表中经常不被使用的字段或者存储数据比较多的字段拆出来。

水平拆分，比如：一个表有5千万数据，那按照一定策略拆分成十个表，每个表有500万数据。这种方式，除了可以解决查询性能问题，也可以解决数据写操作的热点征用问题。

数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

• 使用可以存下数据最小的数据类型，合适即可
• 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED；
• VARCHAR的长度只分配真正需要的空间；
• 对于某些文本字段，比如"省份"或者"性别"，使用枚举或整数代替字符串类型；在MySQL中， ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多
• 尽量使用TIMESTAMP而非DATETIME；
• 单表不要有太多字段，建议在20以内；
• 尽可能使用 not null 定义字段，null 占用4字节空间，这样在将来执行查询的时候，数据库不用去比较NULL值。
• 用整型来存IP。
• 尽量少用 text 类型，非用不可时最好考虑拆表。

如果发现SQL查询比较慢，可以开启慢查询日志进行排查。

# 开启全局慢查询日志
SET global slow_query_log=ON;
# 设置慢查询日志文件名
SET global slow_query_log_file='slow-query.log';
# 记录未使用索引的SQL
SET global log_queries_not_using_indexes=ON;
# 慢查询的时间阈值，默认10秒
SET long_query_time=10;

注：索引并不是越多越好，要根据查询有针对性的创建。

• 单表查询：哪个列作查询条件，就在该列创建索引
• 多表查询：left join 时，索引添加到右表关联字段；right join 时，索引添加到左表关联字段
• 不要对索引列进行任何操作（计算、函数、类型转换）
• 索引列中不要使用 !=，<> 非等于
• 字符字段只建前缀索引，最好不要做主键；
• 尽量不用UNIQUE，由程序保证约束
• 不用外键，由程序保证约束
• 索引列不要为空，且不要使用 is null 或 is not null 判断
• 索引字段是字符串类型，查询条件的值要加''单引号，避免底层类型自动转换

这里对explain的结果进行简单说明：

• select_type：查询类型
• SIMPLE 简单查询
• PRIMARY 最外层查询
• UNION union后续查询
• SUBQUERY 子查询
• type：查询数据时采用的方式
• ALL 全表（性能最差）
• index 基于索引的全表
• range 范围 （< > in）
• ref 非唯一索引单值查询
• const 使用主键或者唯一索引等值查询
• possible_keys：可能用到的索引
• key：真正用到的索引
• rows：预估扫描多少行记录
• key_len：使用了索引的字节数
• Extra：额外信息
• Using where 索引回表
• Using index 索引直接满足条件
• Using filesort 需要排序
• Using temprorary 使用到临时表

对于以上的几个列，我们重点关注的是type，最直观的反映出SQL的性能。

一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库。

SELECT id FROM t WHERE num BETWEEN 1 AND 5；

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。如果数值较多，需要在内存进行排序操作，产生的消耗也是比较大的。

SELECT * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；减少了使用覆盖索引的可能性。

limit 相当于截断查询。

例如：对于select * from user limit 1; 虽然进行了全表扫描，但是limit截断了全表扫描，从0开始取了1条数据。

排序的字段建立索引在排序的时候也会用到

union和union all的差别就在于union会对数据做一个distinct的动作，而这个distanct动作的速度则取决于现有数据的数量，数量越大则时间也越慢。而对于几个数据集，要确保数据集之间的数据互相不重复，基本是O(n)的算法复杂度。

如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

limit m n，其中的m偏移量尽量小。m越大查询越慢。

例如：like '%name'或者like '%name%'，这种查询会导致索引失效而进行全表扫描。但是可以使用like 'name%'，这种会使用到索引。

这种不会使用到索引：

select user_id,user_project from user_base where age*2=36;

可以改为：

select user_id,user_project from user_base where age=36/2;

任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

where 子句中出现的 column 字段要和数据库中的字段类型对应

有的时候 MySQL 优化器采取它认为合适的索引来检索 SQL 语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用 forceindex 来强制优化器使用我们制定的索引。

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

因为使用 join，MySQL 不会在内存中创建临时表。

使用小表驱动大表，例如使用inner join时，优化器会选择小表作为驱动表

如：以 A，B 两表为例，两表通过 id 字段进行关联。

#当 B 表的数据集小于 A 表时，用 in 优化 exist；使用 in ，两表执行顺序是先查 B 表，再查 A 表
select * from A where id in (select id from B)

#当 A 表的数据集小于 B 表时，用 exist 优化 in；使用 exists，两表执行顺序是先查 A 表，再查 B 表
select * from A where exists (select 1 from B where B.id=A.id)

上面都是一些常规的优化方法，我们还可以使用：主从和分库。

主从相对比较简单，从运维层面搭建好从库后，工程师要做的就是制定路由策略。

路由策略有如下两种：

读写分离模式，所有写操作和对实时性要求较高的by id查询走主库，剩下的都走从库，从库采用Round Robin模式。

链路隔离模式：写操作和核心操作对应的SQL走主库，耗时大、非核心操作的SQL走从库。

分库策略需要根据业务场景制定，最常见的有两种：按照年月分库和按照角色分库。

按照角色分库，最经典的就是淘宝基于订单的买家库和卖家库。

整体来讲，这篇数据库优化应该总结得还算全面吧，如果有新的方案策略，我再往上添加。

最后，再给知友们来一波福利。

之前看机会的时候，也从网上找遍了各式各类的八股文资料，但总觉得答案还不够准确，深度还有所欠缺，或是内容组织的逻辑性还不够清晰。

于是，我便自己动手，丰衣足食地自己总结了一套博采众家之长的八股文，那可真是字字斟酌，题题验证。

现在，我“大公无私”地把它分享出来，希望更多的同学可以由此受益。

最后，祝大家工作顺利，纵情向前，人人都能收获自己满意的offer。