小伙伴们又到金三银四了，问到SQL优化怎么办

1.一条SQL查询时Mysql的处理

客户端/服务端通信协议->查询缓存->解析器->查询优化器->执行计划、查询执行引擎->存储引擎

2.执行一条查询sql时Mysql的执行顺序

从from->join->on->where->group by->SUM->having->select->distinct->ordery by

3.执行计划分析

• Id: MySQL QueryOptimizer 选定的执行计划中查询的序列号。表示查询中执行select 子句或操作表的顺序,id 值越大优先级越高,越先被执行。id 相同,执行顺序由上至下
  Select_type: 一共有9中类型,只介绍常用的4种: SIMPLE: 简单的 select 查询,不使用 union 及子查询；PRIMARY: 最外层的 select 查询；UNION: UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集；DERIVED: 用于 from 子句里有子查询的情况。 MySQL 会递归执行这些子查询, 把结果放在临时表里
• Table: 输出行所引用的表
• Type: 从优到差的顺序如下:（* 的是常见的级别。）system–>const *–>eq_ref *–>ref *–>ref_or_null–>index_merge–>unique_subquery–>index_subquery–>range *–>index *–>all *

1）开发考虑

• 一般需要达到 ref、eq_ref 级别，范围查找需要达到 range

type的细致解释

2）其他字段描述

• possible_keys : 哪些索引可能有助于查询。如果为空,说明没有可用的索引
• key: 实际从 possible_key 选择使用的索引，如果为 NULL,则没有使用索引。其中key为null、all 、index时，需要调整、优化索引。很少的情况下,MYSQL 会选择优化不足的索引。这种情况下,可以在 SELECT语句中使用 USE INDEX (indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
• key_len: 使用的索引的长度。在不损失精确性的情况下,长度越短越好
• ref: 显示索引的哪一列被使用了
• rows: 请求数据返回的大概行数。根据rows可以直观看出优化结果
• extra: 其他信息，出现Using filesort、Using temporary 意味着不能使用索引,效率会受到重大影响。应尽可能对此进行优化。三者区别如下：
• Using filesort: 没有办法利用现有索引进行排序，需要额外排序，建议：根据排序需要，创建相应合适的索引，联合索引解决此类问题
• Using temporary: 需要用临时表存储结果集，通常是因为group by的列上没有索引。也有可能是因为同时有group by和order by，但group by和order by的列又不一样
• Using index ：利用覆盖索引，无需回表即可取得结果数据（即数据直接从索引文件中读取），这种结果是好的

4.性能优化

1）表结构优化

• 合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容

2）增加中间表

• 对于需要经常联合查询的表，可以建立中间表以提高查询效率

3）优化数据类型

• 整形比字符串操作代价更低。应该使用mysql内部类型存储时间类型，使用整形存储ip地址

4）表设计不能有太多的列

5）关联操作设计的表不要太多

5.存储引擎的选择

• 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM。
• 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。
• 系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB。

6.查询优化

1）查询SQL尽量不要使用select *，而是具体字段，减少网络开销

2）尽量使用数值替代字符串类型

• 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；
• 而对于数字型而言只需要比较一次就够了；
• 字符会降低查询和连接的性能，并会增加存储开销；

3）避免在where子句中使用!=或<>操作符

• 但是业务优先，实在没办法，就只能使用，并不是不能使用

4）IS NULL，IS NOT NULL无法使用索引（高级的版本依然能用上索引）

5）不在索引列上做任何操作

• 任何操作是指 计算、函数、类型转换（包括自动），这会导致索引失效

6）关联查询利用Using join buffer

• Block Nested-Loop Join算法：
  BNL算法原理：将外层循环的行/结果集存入join buffer，内存循环的每一行数据与整个buffer中的记录做比较，可以减少内层循环的扫描次数。 出现这个应该在关联字段添加索引（如果加上索引仍然没有命中，可能是数据类型不一致（两表字符集不一样），导致索引没有生效）

7）like模糊查询以通配符开头导致索引失效

8）字符串不加单引号导致索引失效

9）操作delete或者update语句，加个limit或者循环分批次删除

• 降低写错SQL的代价
• SQL效率很可能更高
• 避免长事务
• 数据量大的话，容易把CPU打满
• 锁表

10）少用or 用它来连接时会导致索引失效，可用Union或者Union All代替

11）批量插入性能提升

12）表连接不宜太多，索引不宜太多，一般5个以内，但还是要考虑实际情况

13）索引不适合建在有大量重复数据的字段上，比如性别

14）排序字段应创建索引