MySQL优化是在工作中必须要学会的一个技能，在优化之前有一个非常重要的问题，必须要对MySQL进行优化了吗？是否可以从代码层面(业务或者架构)来提高效率呢？假设必须要对MySQL层面优化，那么问题来了——如何下手？先观察后优化！先定位问题出现在哪里，然后才是有针对性的去调优。
定位
一般我们会先观察的MySQL Server服务的查询数量以及线程数量1
mysqladmin -uroot ext -proot | awk '/Queries/{q=$4}/Threads_connected/{tc=$4}/Threads_running/{tr=$4}END{printf("Queries\tThreads_connected\tThreads_running\n%d\t%d\t%d\n",q,tc,tr)}'
还有当前在处理的线程的的状态1
show processlist \G;
重点关注Copying to temp table和Sorting result状态，这两种状态是常见的非常耗资源的状态。如果查询量比较大的话还可能会在Sending data阶段耗费长时间，这个时候就应该要考虑是否可以分页或者减少查询字段了。
我们还可以借助profile来观察每条语句在各个过程消耗的时间,一般profile是OFF的，我们需要将它开启1
2
3
4# 查看profile状态
SHOW VARIABLES LIKE 'profiling';
# 开启profile
SET profiling=ON;
之后的SQL语句都会有详细的记录，可以借助下面两条语句来查看1
2
3
4# 查看全部语句的各自花费时间
SHOW PROFILES;
# 查询某条语句各个过程花费的时间
SHOW PROFILE FOR QUERY {query_id}
但是最重要的分析语句执行过程是explain来分析，后边会重点说这个。
优化过程
了解了定位的时候主要看哪些指标，那么我们在开发过程中就要注意一下，不要等到需要优化了才去explain每条语句优化。我们应该从建表到写SQL语句都需要考虑性能问题。
建表
表结构要合理拆分。
定长与变长分离，常用字段与不常用字段分离，用主键进行关联，根据业务特点合理添加冗余字段，字段的选取尽量选择合适的，存储够用就行，大存储字段浪费内存，影响速度，像核心字段都用int，char，enum等定长结构，非核心字段的话用varchar，text等非定长字段，或者拆出来单独放一张表。
尽量避免使用NULL,既不利于索引也可能更加的耗存储。
如果表结构规划的的不合理，后续一个业务就要连接非常多的表才能查询出数据，这种时候就要从业务角度考虑了，不要为了满足数据库范式而写代码，数据库范式只是提供了一个准则，我们开发人员应该要根据具体的业务来完成表结构的合理规划，不然后续要改数据库表结构将会很痛苦。
索引
索引是为了提高查询效率，下面我们来了解一下索引。
MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。总结一下就是索引是数据结构。既然是一种数据结构的，那么就会有多种实现方式，比如B+Tree索引(平衡树，也可以理解为排好序的快速查找结构)、Hash索引。
常用的是B+Tree索引，Hash索引的理论查询时间是O(1),为什么不用它呢？Hash索引的存储是通过hash值进行计算，即使id是连续增长的，但是hash出来的值千差万别，存储的位置是随机的，还有一个致命的缺点就是不能对范围进行查询，更无法对排序优化，还有就是必须回行，通过索引拿到数据存储位置之后，必须回到表中去取数据。正是这些缺点导致Hash索引的使用场景非常有限，像memory表用的就是hash索引，不需要对数据库进行范围查询，只需要快速定位数据。B+tree是不是就很好了呢？目前来看是的，综合起来首先要满足业务，支持对返回查询，排序优化，然后尽可能快，这些基本上就满足了我们业务上的基本要求。
在MySQL中，索引属于存储引擎级别的概念，不同的存储引擎的实现方式也是不同的，在MySQL中常见的存储引擎有两种：MyISAM和InnoDB。
先来看一下MyISAM,使用B+Tree作为索引结构，也叫非聚簇索引，因为无论是主索引还是辅助索引，其结构都是一样的，叶子节点的data域存放的是数据表记录的地址，因此MyISAM的索引查找结果的过程为按照B+Tree搜索算法搜索索引，如果指定的key存在，取出其data域的值，然后以data域的值为地址，读取相应的数据记录。
再来看一下InnoDB,同样也是使用B+Tree作为索引结构，但是和MyISAM有很大的不同，甚至相反，叫聚簇索引。MyISAM的索引文件和数据文件是分离的，索引文件只保留了数据记录的地址，而在InnoDB中，数据文件本身就是索引文件，数据是直接存储在节点的data域中，所以InnoDB必须要有主键，不然数据找不到地方存储，也因此得名聚簇索引。这就带来了一个问题了，那辅助索引怎么办？InnoDB是这样子处理的，辅助索引的data域存储的是主键的值，这样子所有的辅助索引定位到节点之后就可以拿得到主索引的值，然后再去主索引的B+Tree中查找对应节点，取出节点中data域就是数据记录了。
了解了两种存储引擎实现的索引，我们来对比一下，先从主键索引来看，MyISAM的索引树信息很少，查找相对较快，查找到相应节点之后取出data域信息之后还要根据地址去取数据记录，InnoDB查找相对慢一点，查找过程中遍历的数据量较多，但是查找到相应节点之后直接在节点上去取出数据，不需要在另外的回行取数据，对于主索引来说，这两种方式相差不会太多，数据量大的时候MyISAM的优势就明显了；辅助索引呢就是MyISAM大胜了，InnoDB中的辅助索引找到了只是主索引的值，还得去主索引的B+Tree上查找一次，找到对应节点才能去到data域中的数据记录。这两种的差别是他们的最初的目标决定的，MyISAM的强项是查询，索引是加快查询的最有效的方法，因此MyISAM采取的是非聚簇索引的，使得无论是主索引还是辅助索引，效率基本上是一样。InnoDB是为了平衡查询和事务，MyISAM是不支持事务，如果业务上需要到事务，那还是老实选择InnoDB吧，但是同时也要尽量的发挥InnoDB索引的查询优势，主键的选取就是一个很关键，必须单调并且不宜过长，为什么必须单调呢？这个是数据结构致使的，只要使用的是B+Tree，主键就应该单调，如果不单调，在插入新数据之后，二叉查找树要保持树的平衡，就得重新排序，频繁的分裂调整整棵树的结构，效率是非常低的，使用递增方式可以有效的解决这个问题。为什么不宜过长呢？InnoDB的辅助索引查询效率并不高，尽量能不用就不用，但是现实业务往往是需要辅助索引的，辅助索引的data域存储的是主键的值，过长就会导致额外的存储过多，使得辅助索引非常大。
索引在提升查询效率上的效果是十分明显的，但是在实际项目中我们会发现有时候索引并不生效，那么什么情况下会导致索引失效呢？只有了解了索引没有生效的情况，在开发过程中才能避免。
1.where条件中用or，即使其中有条件带索引，也不会使用索引。可以考虑使用union或者in来替换or。
2.对于多列索引，没有按照索引的定义顺序使用查询，这种时候索引也不生效，可以调整查询条件顺序或者重新创建索引使其生效。
3.like模糊查询使用%开头，尽量避免使用%开头，以%结尾可以生效
4.索引列不要做任何操作，包括计算(包含大于，小于，不等于)，函数，类型转换等等这些操作都会使索引失效。
5.索引列是字符串没有加上引号会使得索引不生效。
6.把where后边的字段都加上了单列索引，是不会所有的索引都生效的，因为是独立索引，生效的只有一个。应该加上多列索引，多列索引生效的前提是要满足前缀要求。
语句
SQL语句我们一般是先满足业务要求，先把业务逻辑完成了，接下来才是有针对性的优化，可以借助Druid数据库连接池的监控或者在开发阶段开启慢日志查询，推荐使用Druid，可以清楚的知道哪些SQL语句比较慢，上线之后可能会出现问题。
接下来我们详细了解一下EXPLAIN
EXPLAIN命令是用来查看SQL语句的执行计划，直接在执行的SQL前面加上EXPLAIN命令之后执行即可。
1 | mysql> explain select * from goods; |
select_type ： 查询类型
- SIMPLE 简单查询，不包含子查询或者UNION
- PRIMARY 查询中若包含子查询，最外层查询类型为PRIMARY
- SUBQUERY 查询中若包含子查询，里层的查询类型为SUBQUERY
- DERIVED from的表是一个子查询来完成的，为派生表，这个其实也属于SUBQUERY
- UNION 该表是通过union完成
table ： 操作的表
partitions ： 操作的分区
type ： 访问类型，是通过何种方式找到数据
- ALL 全表扫描
- index 只遍历索引树
- range 索引范围扫描，查询条件是between、or、in、>、<等这些都是range
- ref 使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
- eq_ref 类似ref，区别在于使用的是唯一索引，多表连接中的唯一索引作为关联条件
- const/system MySQL对查询进行了优化，并转换为一个常量时，使用这些类型进行访问
- NULL MySQL在优化过程中分解语句，执行时甚至不用访问表或者索引，比如找索引列的最小值
possible_keys ：可能用到的索引，但不一定会被使用
key ： 在查询中实际使用的索引
key_length ：索引字段的最大可能长度，并非实际使用长度
ref ： 表的连接匹配条件
rows ： 找到记录需要读取的行数
filtered ：符合条件的记录数的百分比所做的一个悲观估算值
Extra ： 显示上述信息之外的信息，但是确是非常重要的
- Using index 索引覆盖
- Using where 表示MySQL服务器将在存储引擎检索行后在进行过滤
- Using temporary 表示MySQL需要使用临时表存储结果集，常见于排序和分组查询
- Using filesort MySQL无法利用索引完成的排序操作称为文件排序
- Using join buffer 表示获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果
- Impossible where 表示where语句会导致没有符合条件的行
- Distinct 表示找到第一个匹配行之后就会停止搜索其他的行
清楚了explain的各个列的值的含义，哪些值是我们重点关注的呢？
先看type类型，尽量避免ALL全表扫描，对性能有要求的也尽量避免扫描全部索引，这种时候就要在开发的时候加上合适的索引
再来看看Extra列，如果出现了Using temporary或者Using filesort则需要多加关注。
Using temporary往往是因为GROUP BY的列没有索引，或者GROUP BY和ORDER BY的列不一样，这种情况下就会产生临时表，建议加上合适的索引。
Using filesort表示无法利用索引完成排序，进而使用文件排序，也有可能是多表连接时排序的字段不是驱动表的列，建议加上合适的索引。
Using where 通常是因为全表扫描或者全索引扫描(type为ALL或者Index)，又加上了where条件，建议加上合适的索引
怎么都是加索引呢？不是说不能增加太多的索引吗？
确实是这样子，工作中碰到的80%的查询优化问题可以通过加上合适的索引来解决问题，索引就是为了优化查询而诞生的，这是它的职责所在。太多的索引会带来什么问题呢？就是更新数据的时候都要更新索引，这就影响到新增、修改和删除数据的性能，如果对这几个有很高的性能要求的话需要注意索引的数量，但是我工作中还没遇到这种场景，而且大多数表的索引都在3个以内，这个数量应该还好。