SQL优化

  • 优化概述
  • 优化器成本
  • EXPLAIN
  • 执行计划
  • id
  • select_type
  • table
  • type
  • possible_keys
  • key
  • key_len
  • ref
  • rows
  • extra
  • 优化器选择过程
  • 慢日志查询
  • 慢查询日志参数
  • 开启
  • mysqldumpslow
  • 总结


优化概述

数据库性能取决于数据库级别的多个因素，例如表、查询和配置设置。这些软件构造会导致硬件级别的 CPU 和 I/O 操作，您必须将其最小化并尽可能高效。

典型用户的目标是从其现有软件和硬件配置中获得最佳数据库性能。高级用户寻找机会改进MySQL软件本身，或开发自己的存储引擎和硬件设备来扩展MySQL生态系统。
（1）在数据库级别进行优。
（2）在硬件级别进行优化。
（3）平衡便携性和性能。

优化器成本

MySQL 优化器主要针对 IO 和 CPU 会计算语句的成本；可能不会按照分析的原理来执行语句。

成本分析步骤：
（1）找出所有可能需要使用到的索引。
（2）计算全表扫描的代价。
（3）计算不同索引执行查询的代价。
（4）对比找出代价最小的执行方案。

EXPLAIN

用来查看 SQL 语句的具体执行过程。
原理：模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。

执行计划

Column

Meaning

id

The SELECT identifier （查询id）

select_type

The SELECT type （查询类型）

table

The table for the output row （输出结果集的表）

partitions

The matching partitions （匹配的分区）

type

The join type （表的连接类型）

possible_keys

The possible indexes to choose（可能使用的索引）

key

The index actually chosen （实际使用的索引）

key_len

The length of the chosen key （索引字段的长度）

ref

The columns compared to the index （列与索引的比较）

rows

Estimate of rows to be examined （预估扫描行数）

filtered

Percentage of rows filtered by tablecondition （按表条件过滤的行百分比）

extra

Additional information （额外信息，如是否使用索引覆盖）

示例：

DROP TABLE IF EXISTS `covering_index_t`;
CREATE TABLE `covering_index_t` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) DEFAULT NULL,
	`cid` INT(11) DEFAULT NULL,
	`age` SMALLINT DEFAULT 0,
	`score` SMALLINT DEFAULT 0,
	PRIMARY KEY (`id`),
	KEY `name_cid_idx` (`name`, `cid`)
)ENGINE = INNODB AUTO_INCREMENT=0 DEFAULT CHARSET = utf8;


INSERT INTO `covering_index_t` (`name`, `cid`, `age`, `score`)
VALUES
	('FLY', 10001, 12, 99),
	('fly', 10002, 13, 98),
	('cc', 10003, 14, 97),
	('qq', 10004, 15, 100);

EXPLAIN SELECT * FROM `covering_index_t` WHERE `name` = 'mark';

id

select 查询的序列号，包含一组数字，表示查询中执行select 子句或者操作表的顺序。
id 号分为三种情况：

  1. id 相同，那么执行顺序从上到下。
  2. id 不同，id 越大越先执行。
  3. id 有相同的也有不同的，id 相同的按 1 执行，id 不同的按 2 执行。

select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询。

select_type value

Meaning

SIMPLE

简单查询-没有联合查询和子查询

PRIMARY

最外层select

UNION

若第二个select出现在union之后，则被标记为union

DEPENDENT UNION

union或union all联合而成的结果会受外部表影响

UNION RESULT

从union表获取结果的select

SUBQUERY

select或者where列表中包含子查询

DEPENDENT SUBQUERY

subquery的子查询要受到外部表查询的影响

DERIVED

from子句中出现的子查询，也叫做派生表

UNCACHEABLE SUBQUERY

一个子查询，其结果不能被缓存，必须为外部查询的每一行重新求值。表示使用子查询的结果不能被缓存。

UNCACHEABLE UNION

表示union的查询结果不能被缓存：sql语句未验证

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union 合并结果集。

  1. 具体表名或者表的别名，从具体的物理表中获取数据。
  2. 表明为 derivedN 的形式，表示 id 为 N 的查询产生的衍生表。
  3. 当有 union result 的时候，表名是 union n1,n2 等的形式，n1,n2 表示参与 union的 id。

type

type 显示访问类型；采用怎么样的方式来访问数据；效率从好到坏依次为：

system > const > eq_ref > ref > fulltext > ref_or_null >index_merge > unique_subquery > index_subquery > range> index > ALL

type value

meaning

ALL

全表扫描；如果数据量大则需要进行优化

index

全索引扫描这个比 ALL 的效率要好，主要有两种情况，一种是当前的查询是覆盖索引，即需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序。

range

表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了 index 的全索引扫描，适用的操作符：= , <> , > , >= , < , <= , IS NULL , BETWEEN , LIKE , or IN()

index_subquery

利用索引来关联子查询，不再扫描全表

unique_subquery

该连接类型类似与 index_subquery，使用的是唯一索引

index_merge

在查询过程中需要多个索引组合使用

ref_or_null

对于某个字段即需要关联条件，也需要 null值的情况下，查询优化器会选择这种访问方式

ref

使用了非唯一性索引进行数据的查找

eq_ref

使用唯一性索引进行数据查找

const

这个表至多有一个匹配行

system

表只有一行记录（等于系统表），这是 const 类型的特例

possible_keys

查询涉及到字段的索引，则这些索引都会列举出来，但是不一定采纳。

key

实际使用的索引，如果为 NULL，则没有使用索引。

key_len

表示索引中使用的字节数；查询中使用的索引长度；在不损失精度的情况下长度越短越好。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。

rows

大致估算出找出所需记录需要读取的行数，反映了sql找了多少条数据，该值越小越好。

extra

额外信息。

extra value

meaning

using filesort

使用了文件排序

using temporary

建立临时表来保存中间结果，查询完成之后把临时表删除

using index

采用覆盖索引，直接从索引中读取数据，而不用访问数据表。如果同时出现 using where 表明索引被用来执行索引键值的查找；如果没有，表明索引被用来读取数据，而不是真的查找

using index condition

采用索引下推，减少回表次数

using where

使用 where 进行条件过滤

using join buffer

使用连接缓存

impossible where

where 语句的结果总是 false

优化器选择过程

优化器根据解析树可能会生成多个执行计划，然后选择最优的的执行计划。

SHOW VARIABLES LIKE 'optimizer_trace';
-- 启用优化器的追踪
SET optimizer_trace='enabled=on';
-- 执行一条查询语句
SELECT * FROM information_schema.optimizer_trace;
-- 用完关闭
SET optimizer_trace="enabled=off";
SHOW VARIABLES LIKE 'optimizer_trace';

慢日志查询

慢查询日志由执行时间超过 long_query_time 秒且至少需要检查 min_examined_row_limit 行的 SQL 语句组成。慢查询日志可用于查找需要很长时间才能执行的查询，因此是优化的候选项。但是，检查较长的慢查询日志可能是一项耗时的任务。为了简化此操作，您可以使用 mysqldumpslow 命令来处理慢查询日志文件并汇总其内容。

慢查询日志参数

long_query_time的最小值和默认值分别为 0 和 10。可以将该值指定为微秒的分辨率。

缺省情况下，不记录管理语句，也不记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和log_queries_not_using_indexes更改此行为，默认情况下，慢查询日志处于禁用状态。

开启

（1）查看：

SHOW GLOBAL VARIABLES LIKE 'slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query%';

（1）设置：

SET GLOBAL slow_query_log = ON;  -- on 开启 off，关闭
SET GLOBAL long_query_time = 4;   -- 单位秒；默认 10s；此时设置为4s

或者修改配置：

slow_query_log = ON
long_query_time = 4
slow_query_log_file = D:/mysql/mysql57-slow.log

mysqldumpslow

查找最近10条慢查询日志：

mysqldumpslow -s t -t 10 -g 'select' D:/mysql/mysql57-slow.log

总结

当出现SQL比较慢时，需要进行如下步骤进行优化：
（1）找到SQL语句。通过show processlist和开启慢查询日志。
（2）分析SQL语句。
a. 查看where、group by、order by里面的字段是否创建了索引。
b. in 优化成联合查询，减少联合查询等。