MYSQL调优总结

一.MYSQL逻辑架构

优化mysql我们需要知道一个道理，那就是mysql是如何工作的，了解它如何工作，让他用最舒服的方式进行工作，就是我们需要做到的。

下面从一个mysql查询过程来分析他的逻辑工作原理：

1.客户端/服务端通信协议：
通信协议采用的是半双工（要么是客户端发送消息给服务器，要么是服务器发送消息给客户端，双方不能同时进行），并且客户端发送过去的消息是一个数据包，而接收的数据是多个数据包，这就有一个问题，那就是客户端发送的语句实在太大，服务器会拒绝接收。

建议： 在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

2.查询缓存：
首先给大家一个建议：不要轻易打开mysql的缓存查询，因为缓存消耗服务器的资源不小；实在想用，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

当一句sql进入逻辑架构的时候，如果mysql查询缓存打开，那么先会去缓存中，看是否会命中该sql，如果命中了，检查权限是否够，然后返回。需要注意的是，哪怕你的sql多了一个空格，都会倒是命中失败；还有sql中存在函数，那么也不会存入缓存。

3.语法解析和预处理：
MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。

4.查询优化：
一种sql在执行器中有多种执行过程，查询优化就是要找出其中最优的一种，常见优化策略为重新定义表的关联顺序，优化MIN()和MAX()函数，提前终止查询，优化排序

5.查询执行引擎：

6.返回结果给客户端：
无论有没有查到数据，MYSQL都会返回一个消息给客户端，包括执行时间等信息。如果缓存被打开，那么该结果有可能被存入缓存。
返回数据可能在查出第一条数据的时候就已经开始了，以多个数据包的形式返回，使用tcp协议。

二.创表建议：

核心思想为小而简单，越小越好：

1. 小的数据类型，会占用更少的磁盘、内存，处理时需要的CPU周期也更少。

2. 越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。

常见错误：

1. 把可为NULL的列改为NOT NULL不会对性能提升有多少帮助；但是如果是在索引列上，那就可以设置为not null.
2. 对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。
3. 没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。
4. schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。

三.调优

1. MySQL不会使用索引的情况：
   非独立的列
   独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数，如下

select * from where id + 1 = 5

2. 前缀索引：
   简单来说，就比如索引列的数据存储格式是BLOB,TEXT，或者很长的varchar，那么就可以使用该字段是前面一部分数据来做索引。
3. 多列索引和索引顺序：
   在多数情况下，在多个列上建立独立的索引并不能提高查询性能。理由非常简单，MySQL不知道选择哪个索引的查询效率更好。

索引选择性是指不重复的索引值和数据表的总记录数的比值，选择性越高查询效率越高，
因为选择性越高的索引可以让MySQL在查询时过滤掉更多的行。唯一索引的选择性是1，这时最好的索引选择性，性能也是最好的。

当出现多个索引做相交操作时（多个AND条件），通常来说一个包含所有相关列的索引要优于多个独立索引。

当出现多个索引做联合操作时（多个OR条件），对结果集的合并、排序等操作需要耗费大量的CPU和内存资源，特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高。所以这种情况下还不如走全表扫描。

4. 避免多个范围条件
5. 覆盖索引
   如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。
6. 冗余和重复索引
   比如有一个索引(A,B)，再创建索引(A)就是冗余索引。
7. 删除长期未使用的索引
8. 优化关联查询
   在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用JOIN有更好的性能。如果确实需要使用关联查询的情况下，需要特别注意的是：

确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。
当表A和表B用列c关联的时候，如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上创建索引。
没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引

确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。

这里来解释一下关联查询的匹配策略，mysql一般使用的是嵌套循环关联操作，就拿上面的A和B表来解释，先从A表中循环取出单条数据，再去B表中比对，所以这就是为什么在A表中设置索引没意义。

9. 优化LIMIT
   limit+order by来做分页查询效率很可观，但是存在一个问题，就说当偏移量特别大的时候，LIMIT 10000 20这样的查询，MySQL需要查询10020条记录然后只返回20条记录，前面的10000条都将被抛弃，这样的代价非常高。优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。

SELECT film.film_id,film.description

FROM film INNER JOIN (

    SELECT film_id FROM film ORDER BY title LIMIT 50,5

) AS tmp USING(film_id)

10. 优化UNION
    MySQL处理UNION的策略是先创建临时表，然后再把各个查询结果插入到临时表中，最后再来做查询。使用时记得使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。

参考：java金融