SQL优化

Posted on | In

SQL优化

发现问题-分析执行计划-优化索引-改写SQL-数据库垂直切分-数据库水平切分

慢查询日志

配置MySQL慢查询日志
1
2
3
4
5
6
7
8
9
10
set global slow_query_log = [ON | OFF]

// 慢查询日志记录slowlog.log
set global slow_query_log_file = /sql_log/slowlog.log

// 执行时间超过设置时间，记录到慢查询中
set global long_query_time = xxx.xxx秒

// 未使用索引的SQL记录到慢查询中
set global log_queries_not_using_indexes = [ON | OFF]

分析执行计划

了解SQL如何访问表中的数据、索引、查询类型。

1
2
3
EXPLAIN

SQL语句
  1. 执行计划内容分析

  • ID表示查询执行的顺序。

  • ID相同时由上到下执行。

  • ID不同时，由大到小执行。

select_type

值 含义
SIMPLE 不包含子查询或是UNION操作的查询
PRIMARY 查询中如果包含任何子查询，那么最外层的查询则被标记为PRIMARY
SUBQUERY SELECT列表中的子查询
DEPENDENT SUBQUERY 依赖外部结果的子查询

select_type

值 含义
UNION union操作的第二个或是之后的查询的值为union
DEPENDENT UNION 当UNION做为子查询时，第二个或是第二个后的查询的select_type值
UNION RESULT UNION产生的结果值
DERIVED 出现在FROM子句中的子查询

table

  • 指明是从那个表中获取数据。

partitions

  • 对于分区表，显示查询的分区ID。

  • 对于非分区表，显示NULL。

type

性能 值 含义
高 system 这是const连接类型的一个特例，当查询的表只有一行时使用
const 表中有且只有一个匹配的行时使用，如对主键或是唯一索引的查询，这是效率最高的联接方式。
eq_ref 唯一索或主键查找，对于每个索引建，表中只有一条记录与之匹配
ref 非唯一索引查找，返回匹配某个单独值的所有行
ref_or_null 类似于ref类型的查询，但是附加了对NULL值列的查询
index_merge 该联接类型表示使用了索引合并优化方法
range 索引范围扫描，常见于between、>、<这样的查询条件
index FULL index Scan全索引扫描,同ALL的区别是，遍历的是索引树
低 ALL FULL TALBLE Scan全表扫描，这是效率最差的联接方式

possible_keys

  • 指出查询中可能会用到的索引。

key

  • 指出查询时实际用到的索引。

key_len

  • 实际使用索引的最大长度。

ref

  • 指出那些列或常量被用于索引查找。

rows

  • 指出那些列或常量被用于索引查找

  • 根据统计信息预估的扫描的行数、

filtered

  • 表示返回结果的行数占需读取行数的百分比

Extra

值 含义
Distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
Not exists 使用not exists来优化查询
Using filesort 使用文件来进行排序，通常会出现在order by或group by 查询中
Using index 使用了覆盖索引进行查询
Using temporary MySQL需要使用临时表来处理查询，常见于排序，子查询，和分组查询
Using where 需要在MySQL服务器层使用WHERE条件过滤数据
select tables optimized away 直接通过索引来获得数据，不用访问表