四、MYSQl调优

Explain 详解

Id	该语句的唯一标识
select_type（重点）	查询类型
table	所访问的数据库中表的名称
partitions	匹配的分区，查询表有分区时会显示
type（重点）	联合查询使用的类型，是较重要的指标。一般来说，保证查询至少达到range级， 最好能达到ref级别。ALL为全表扫描是最坏的情况，这种情况往往是没用上索引。 结果值从好到坏依次是
possible_keys	指出 MySQL 能使用哪个索引在该表中找到该行。如果这个值是空的则表示没有相关的索引。 这时要提高性能，可通过检测where子句，看看是否引用了某些字段，或者检查字段是否适合索引
key	MySQL 实际决定使用的键。如果没有索引被选择，键是 NULL
key_len	显示 MySQL 决定使用的键长度。如果键是 NULL，长度就是NULL。 注意这个值可以反映出一个多主键（复合主键）里的 MySQL 实际使用了哪些部分
ref	显示哪个字段或常数与 key 一起被使用
rows（重点）	这个值表示 MySQL 要遍历多少数据才能找到所需的结果集，在 InnoDB 上是不准确的，值越小越好
filtered	按表条件过滤的行百分比
Extra	附加信息

性能调优解释

一、 性能的定义

        mysql的性能是什么，简单来说性能就是一条sql语句的响应时间

二、 什么情况下需要性能优化

         1 mysql缓存层的缓存过期，导致请求命中mysql数据重新生成缓存。

         2 索引失效

         3 互斥锁争用

         4 并发太大导致innonDb的查询优化计划时间变长

         5 多个查询请求命中同一行数据，导致进程排队等待

         6 同一时间大量的请求冲击

三、  性能优化

      性能优化需要清楚一个概念，大部分人认为性能优化就是降低cpu的使用率，因为这样可以减少对资源的消耗，来使cpu进行更多的工作。

         这样其实是不对的，因为cpu就是用来工作的，当你一条sql使用更多的资源时，其实也变相的等于是在加快sql的响应速度。如果你发现你的cpu资源使用的比较大，不能说明你得性能出现了问题，只能代表你得sql使用资源比较大。

         而真正的性能优化，是要确定你的sql执行响应时间是多少，是否存在慢SQL，并且我们应该如何去减少响应时间。

        而如何确定你得服务器中那条sql属于慢查询，skywalling 链路追踪工具，或者其实监控工具。 当然，如果你不想使用链路追踪工具的话，也可以查看mysql服务器自带的慢查询日志+ Percona Serve 工具。Percona Serve工具提供了对日志内容和查询捕获的更多操作, 例如查询执行计划、锁、IO操作等。所以如果你需要查看你得每条sql响应如何，推荐使用Percona Serve 工具。

四、  慢查询日志

         慢查询日志在当前版本中，是开销最低，精度最高的测量查询时间工具， 响应时间单位现在是毫秒。慢查询日志工具对于服务器io的消耗可以忽略不计，这个不需要担心。但是你需要注意的是日志会大量占用磁盘空间，如果你长期开启慢查询工具，则需要部署日志轮转工具，或者在你发现问题需要分析sql时开启。当然，你也可以定期清理磁盘。

         同时需要注意的是，系统中的慢查询日志看起来很复杂，你可以借助一些工具生成系统性的报告。例如 pt-query-digest。

         相关慢查询日志的操作自行查阅资料。当你通过慢查询日志找到单个的慢sql时，你就可以使用 explain 来具体查看分析原因了。 explian相关资料

对于数据类型上的优化

1、尽可能在保证正确数据存储的情况下使用最小数据类型。(因为数据类型越小占用的磁盘、cpu、内存就越小，并且处理cpu周期也更少)

2、 如果不存在字符的情况下尽量使用整型和系统内置的数据类型。(例如整型的字符集和排序规则比字符串更简单，当你使用时间时尽量使用系统内置的数据类型(time，datetime, date))

3、 字段默认值尽量避免使用NULL。(因为如果查询中的字段条件有NULL值，会影响mysql索引失效，也会使mysql的查询优化变得更加复杂。并且NULL也会使用更多的存储空间) ，但是也不是完全的摒弃NULL，如果你得列确实会有NULL值那么就可以使用，因为如果你使用一个虚拟的假值来替代NULL的话也会增大你得代码复杂度

4、 尽量保证关联列之间的数据类型保持一致。这样做可以减少mysql之间进行隐式转换避免不必要的问题和避免性能问题。如果数据类型不同会导致索引失效。

5、尽量避免使用字符串作为标识列(标识列也就是关联列，例如外键/主键)，因为字符串耗费空间更大，并且也比整型慢。

    如果你的表数量在你可控的范围大小之内，推荐使用整数，但是也需要注意因为整数意外耗尽的情况发生，如果你不清楚你得表大小范围，那么可以使用uuid作为标识列， 去除多余符号，使用UNHEX()函数将UUID值转换为16字节的数字， 并将其存储在一个BINARY（16）列， 可以使用HEX()函数以十六进制格式检索值（注意，如果你使用BINARY，那么检索比较时比较的是二进制的值，而不是字符内容）

6、存储ip地址时，尽量不要使用字符串，可以使用整型存储，mysql提供专门的ip地址转换函数。 INET_ATON('IP地址') 转换为整数    INET_NTOA(整数)转换为ip地址。

7、不要有太多的列，不要有太多的关联表，如果你因为数据表列太多而拆分为多张数据表导致的多个关联表，那么没必要在一个sql上全部进行检索，可以通过代码程序来拆分优化。

Sql语句的调优

一、SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。例如：select id from table_name where num in(1,2,3)，对于连续的数值，能用 between 就不要用  in

二、SELECT语句务必指明字段名称

SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。

三、当只需要一条数据的时候，使用limit 1

这是为了使EXPLAIN中type列达到const类型

四、给排序字段增加索引，如果排序字段没有用到索引，就尽量少排序

五、如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果

六、尽量用union all代替union

union 和 union all 的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all 的前提条件是两个结果集没有重复数据。

七、不使用ORDER BY RAND()

select id from `table_name`  order by rand() limit 1000;

上面的sql语句，可优化为

select id from `table_name` t1 join  (select rand() * (select max(id) from `table_name`) as nid) t2  on t1.id > t2.nid limit 1000

八、区分in和exists， not in和not exists

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。