前言
关于MySql优化的文章已经不少，无非就是常见的加索引，以及避免使用索引失效的or、like%等，看得多了自己也能说上个三五条。
但是，这些优化的小点缺少系统的归纳和整理，导致我们在平时处理实际慢查询问题或者是面试的时候无法从大局、整体地视角来处理之。
这篇文章旨在对MySql调优形成一套系统的方法论，让大家在工作或者面试中能够有所帮助。这是我在掘金的第一篇文章，也是对自己掌握知识的一个总结，希望对读者朋友来说也是开卷有益的。
思路
既然限定了MySql数据库，就不去扯技术选型，各数据库性能比较之类的了，至少在现在时间点的国内，MySql还是最主流、使用率最高的数据库。
其次，基于InnoDB引擎对事务、行锁等方面的支持，MylSAM的使用场景也非常少，一般仅存在于面试题当中，只要知道MylSAM有一个全文索引的特点就行了。如下图片中给出了两大存储引擎的对比。
因此本文默认的前提是MySql数据库中的MylSAM引擎下的性能优化，会从以下几个方面展开：服务器参数配置、数据类型优化、查询优化、索引优化、性能监控。
服务器参数设置
这一块平时开发工程师们基本遇不到，数据库的搭建、参数配置一般都由公司里专业的运维或者DBA团队负责，自己只要负责建表开始的工作就行了。另外，现在公司一般也会使用云厂商提供的数据库服务来降低自己的运维成本。
因此，这个点不是重点，只要了解即可，平时和同事或者面试官能简单地说两句就成。以下列举了一些InnoDB相关的配置。
1 | shell复制代码innodb_buffer_pool_size |
该参数用于指定固定大小的内存来缓冲数据和索引，最大可设置为物理内存的80%
1 | shell复制代码innodb_flush_log_at_trx_commit |
该参数控制InnoDB将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为0、1、2：
0：每秒从缓冲区将log写入操作系统cache，并flush log到磁盘
1：每次提交事务，从缓冲区将log写入操作系统cache，并flush log到磁盘（默认）
2：每次提交事务，从缓冲区将log写入操作系统cache，每秒flush log到磁盘（建议）
1 | shell复制代码innodb_thread_concurrency |
该参数设置InnoDB线程的并发数，默认为0意为不受限制，如果要设置建议为cpu核心线程数的一倍或两倍
1 | shell复制代码innodb_log_buffer_size |
该参数用于确定日志文件所占用的内存大小，单位M
1 | shell复制代码innodb_log_file_size |
该参数用于确定日志文件的大小，单位M
1 | shell复制代码innodb_file_per_table |
该参数用于为每张表分配一个数据空间，即单独存储
数据类型优化
参数设置完成后的步骤是建表，根据开发设计文档来建表和建字段，这里面就有一些文章可作了，一个好的数据类型和合理的大小能够在这张表越来越大的时候体现它的作用。
- 使用能够满足业务需求的最小数据类型。即应该尽量用可以正确存储数据的最小数据类型，更小的数据类型通常更快，占用更少的磁盘、内存和CPU缓存，并且处理室需要的CPU周期更少。但是，要确保没有低估需要存储值的范围，即不会造成业务不可用。虽然身处在大宽表横行和磁盘存储成本越来越低的情况下，这些占用空间几乎微乎足道，但这并不妨碍我们做一个优雅的、严谨的程序员。
- 尽量避免null。如果查询中包含可为null的列，对mysql优化执行器来说很难优化，因为可为null的列使得索引、索引统计和值都更加复杂，因此建议在设计字段的时候就可以规定某些列为not null。
- 在范式和反范式中寻求平衡。三范式原则诞生于磁盘比较昂贵的年代，在如今人们更希望能从一张表里查询一整个对象实体而避免关联，这就允许冗余甚至是反范式。在企业实战场景中，一般要根据业务场景的需要做到两者的混合使用。
- 适当的冗余和适当的拆分。基于第三点提出了这一点建议，具体来说，适当的冗余是指当有两张或以上的表被频繁关联查询时，或者每次关联只为了取得部分小字段的值，但是join得到的记录很大，会造成不必要的IO时，就可以考虑冗余字段了。适当的拆分是指当表中存在类似于TEXT后者大VARCHAR类型的大字段时，如果大部分对该表的查询都不需要这个字段，就可以考虑拆分该字段到独立的表中，等要用时再关联查询。
查询优化
把查询优化和索引优化分开来讲是因为，查询优化不仅仅限于加索引和索引优化。查询优化还包括查询缓存、语法解析和预处理等。而索引优化也是一个大的点，因此分开来讲一下。
首先，优化查询要知道查询慢的原因是什么，才能针对性地做优化。一个慢查询的主要原因有网络、CPU、IO、上下文切换、系统调用、锁等待等等。有些我们能优化，有些我们也无能为力，因此我们要把力使在能使的地方。
不妨从头开始思考，查询性能低下的原因主要是访问的数据太多了，某些查询不可避免地要筛选大量的数据，我们可以通过减少访问的数据量的方式进行优化：1.确认应用程序是否在检索大量超过需要的数据2.确认MySql服务器是都在分析大量超过需要的数据行。就是说，是不是我们向数据库要的太多了，或者数据库处理的太多了。
针对第一点，我们要自查，是否向数据库请求了不需要的数据。例如，查询了不需要的记录，我们常常误以为MySql会只返回需要的数据，实际上MySql却是先返回全部结果再进行计算，再日常开发习惯中，经常是先用select语句查询大量的结果，然后获取前面的N行，这是不可取得，不妨直接在select后加limit限制。
此外，是否在多表关联时返回了全部的列或者总是取出全部的列（Mapper 中的BaseResult）。这和select * 如出一辙。虽然简化了写和筛选n多字段的工作，但是却是会影响查询的性能，不要这样做。
在应用层加缓存的方案不在此文章讨论中，因为通过redis等缓存技术方案或者布隆过滤器等拦截已经过滤掉了查询没有打到数据库。这里说的查询缓存是指MySql的查询缓存。在解析一个查询语句之前，如果查询缓存是打开的，那么MySql会优先检查这个查询是否命中缓存中的数据，如果恰好命中，那么会在返回结果之前检查用户权限，如果权限没有问题，MySql会跳过查询的阶段直接从缓存中拿到结果返回给客户端。
MySql查询完缓存后悔经过一下几个步骤：解析SQL、预处理、优化SQL执行计划。没错，MySql执行器悔帮我们优化SQL，这依托于内部的查询优化器，当然只是一些初级的优化，包括但不限于：重新定义关联表的顺序、使用等价变换规则来简化查询语句中的表达式、子查询优化等。更加深层次的优化需要开发者自己去完成。
索引优化
说了这么多，终于进入了索引篇章了，众所周知，优化查询最直给的方式就是加索引，或者分析已存在的索引做更改。
加索引可不是简单三个字就能糊弄过去的，索引怎么加，给哪些字段加，加了为什么没生效，这些都是随之未来的问题。就像文章开头提到的，or会使索引失效，那怎么办，有人会说用union all替代，那么这一定是最佳方案吗，要知道union all是要对一张表查两次，它的性能一定高于使用or的一次全表扫描吗，因为肯定还有别的where条件，能够确保都走索引吗，又或者，where条件里既有or又有like “%%”，这种情况呢？
索引是个大学问，几乎能单独出本书，可以从索引的定义、用处、分类、数据结构一节节讲下来，还有回表、索引覆盖、下推、聚簇索引等面试技术名词，都值得能够好好唠上一唠。本文篇幅不够，读者看到这里估计也没多少耐心。这篇文章只能说帮大家建立一个系统的方法论，从高处和大盘着眼，至于里面细枝末节的东西，无法面面俱到。
索引这么重要的东西，每篇MySQL优化的文章都会涉及到，大家出门左转右转都能看到，或者等工作中问题遇到了再针对性解决处理即可。这里就不谈细节了，总之，索引是个好东西，能够加快查询，我们在写sql的时候尽量要避免索引失效的问题。大家想看的话在评论区留言，之后我会再更新，包括explain看执行计划呀，每个列的释义呀，索引的匹配方式等等。
性能监控
对MySQL服务器的性能监控主要体现在两方面，事前和事后。一条sql可能在数据量小的时候没什么问题，返回还挺快，等系统运行时间久了，业务量上来了之后，就开始暴露问题了。可能我们的业务或者测试说点开哪个页面或者按钮的时候特别慢，特别卡，那就要注意了，可能有慢查询。但是sql那么多，具体怎么定位呢，这时候就有一条神命令：show profile直接在客户端命令行里敲就行，它是查询剖析工具，还可以指定具体的type,回车之后可以显示最近100条信息，包括客户端建立的连接耗时，和sql查询耗时，cpu占用情况等。在系统卡顿定位到慢查询的时候利用它就能迅速定位到具体的sql语句。show processlist用于查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征。
另外一方面，性能监控的意思是在防患于未然，在系统卡死前通知运维和开发人员，可以设置服务器运行卡顿的阈值来未雨绸缪，早知道，早排查，早处理。
总结
本文从以下几个方面：服务器参数配置、数据类型优化、查询优化、索引优化、性能监控对MySql调优这一经典问题进行了较高维度的梳理和总结，希望大家看完后能够后面试官或者技术老大侃侃而谈，battle两三个回合，而不是加索引、不要select * 这种老掉牙的回复，最后祝大家都能够升职加薪！Bug越来越少!
本文转载自: 掘金