深入理解MySQL-SQL优化

慢sql

参数	描述
slow_query_log	是否启用慢sql查询
long_query_time	设置sql时间阈值，超过阈值即为慢sql
slow_query_log_file	指定查询日志的路径以及名字
log_output	慢日志输出到文件还是表中

log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。
log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。
MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，建议优先记录到文件

慢sql状态查询
show variables like "%slow_query_log%"
开启慢sql
1.set global slow_query_log = on;数据库重启失效
2.修改配置文件my.ini

[mysqld]
log_output='FILE,TABLE'
slow_query_log='ON'

使用mysqldumpslow来分析慢sql日志

explain执行计划

找出慢sql后使用explain分析sql语句，
在sql语句之前加上explain关键字

执行计划各个字段含义：
id

• id相同执行顺序由上至下。
• id不同，id值越大优先级越高，越先被执行。
• id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中

select_type
表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
SIMPLE 简单的select查询，查询中不包含子查询或者UNION

• PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
• SUBQUERY 在SELECT或WHERE列表中包含了子查询
• DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
• UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
• UNION RESULT 从UNION表获取结果的SELECT

table
当前执行的表

partition
表分区、表创建的时候可以指定通过那个列进行表分区

type
访问类型

• ALL 扫描全表数据
• index 遍历索引
• range 索引范围查找
• index_subquery 在子查询中使用 ref
• unique_subquery 在子查询中使用 eq_ref
• ref_or_null 对Null进行索引的优化的 ref
• fulltext 使用全文索引
• ref 使用非唯一索引查找数据
• eq_ref 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。

ppossible_keys
可能使用的索引，不一定会使用，为null考虑优化

key
查询中实际使用的索引

key_len
索引长度

ref
表的连接匹配条件

rows
估算的结果集数目

filtered
存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，是百分比

Extra
额外信息

• Using index 使用覆盖索引
• Using where 使用了用where子句来过滤结果集
• Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
• Using temporary 使用了临时表

sql优化

join优化
left join、right ioin
在优化关联查询时，在被驱动表上建立索引才有效
left join 时，左侧的为驱动表，右侧为被驱动表
right ioin是，左侧的为被驱动表，右侧为驱动表

inner join
把小结果集的表选为驱动表（小表驱动大表）

union优化
union all不去重但是性能高于union，能用union all就用union all

in、exists
in 是把外表和内表作hash 连接，先查询子表，子表再与外表做笛卡尔积(子表为驱动表)，in不出来null
exists是对外表作loop循环，每次loop循环再对内表进行查询(外表为驱动表)

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时使用in, 如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists

LIMIT
可以记录上次查询的最大ID，下次查询时直接根据该ID来查询

WHERE

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

select id from t where num is null
-- 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

• 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
• 应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

select id from t where num=10 or num=20
-- 可以这样查询：
select id from t where num=10 union all select id from t where num=20

• in 和 not in 也要慎用，否则会导致全表扫描

select id from t where num in(1,2,3) 
-- 对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

• 下面的查询也将导致全表扫描：select id from t where name like ‘%李%’若要提高效率，可以考虑全文检索。
• 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项

  select id from t where num=@num
  -- 可以改为强制查询使用索引：
  select id from t with(index(索引名)) where num=@num
  

• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

select id from t where num/2=100
-- 应改为:
select id from t where num=100*2

• 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

select id from t where substring(name,1,3)=’abc’
-- name以abc开头的id应改为:
select id from t where name like ‘abc%’

• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引

其它性能优化

除此之外的优化就要考虑
表拆分、分库分表、主从、集群了