mysql block ops in_MySQL学习笔记（18）：SQL优化

本文更新于2019-08-18，使用MySQL 5.7，操作系统为Deepin 15.4。

优化SQL语句的步骤

通过SHOW STATUS了解SQL语句的执行情况

操作的计数，是对执行次数进行计数，不论提交还是回滚都会累加。

Com_xxx形式的参数表示每个xxx语句执行的次数，对所有的存储引擎都会进行累计，如：

Com_select：执行SELECT的次数。

Com_insert：执行INSERT的次数，对批量插入的操作只累加一次。

Com_update：执行UPDATE的次数。

Com_delete：执行DELETE的次数。

Com_commit：事务提交的次数。

Com_rollback：事务回滚的次数。

Innodb_rows_xxx形式的参数只对InnoDB存储引擎进行累计，其累计的方式也与Com_xxx不同：

Innodb_rows_read：执行SELECT返回的行数。

Innodb_rows_inserted：执行INSERT插入的行数。

Innodb_rows_updated：执行UPDATE更新的行数。

Innodb_rows_deleted：执行DELETE删除的行数。

Handler_read_xxx形式的参数可表示索引的使用情况：

Handler_read_key：一个行被索引值读的次数。高表示索引被经常使用。

Handler_read_rnd_next：在数据文件中读下一个行的次数。高表示索引不经常使用，进行大量的表扫描。

以下参数便于了解数据库的基本情况：

Connections：试图连接服务器的次数。

Uptime：服务器工作时间。

Slow_queries：慢查询次数。

定位执行效率低下的SQL语句

通过慢查询日志定位执行效率低下的SQL语句。

通过SHOW PROCESSLIST查看服务器当前的线程，包括线程的状态、是否锁表等，可以实时查看SQL的执行情况。

通过EXPLAIN或DESC分析SQL的执行计划

DESC和EXPLAIN分析SQL执行计划的使用和作用是一样的。

执行EXPLAIN statement后再执行SHOW WARNINGS，可以看到被优化器改写后真正执行的SQL。

一个执行计划包括若干行，每行包括如下的列：

id:值越大越先执行(值越大越位于下方)，一样大从上至下执行。

select_type：查询类型，可取如下值：

DERIVED：派生表的查询。

SIMPLE：简单查询，即不使用子查询和UNION的查询。

SUBQUERY：子查询。

PRIMARY：主查询，即包含子查询的最外层查询，或UNION中的第一个查询。

UNION：UNION中的第二个或之后的查询。

table：输出结果集的表。

partitions：访问的分区。

type：访问类型，即在表中查找所需行的方式。

以下取值性能由最差至最好：

ALL：全表扫描，遍历所有行。

index：索引全扫描，遍历整个索引。

range：索引范围扫描，常见于、>=、BETWEEN等操作符。

ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个值的所有记录行。其经常出现在JOIN操作中。

eq_ref：类似ref，区别在于使用唯一索引。其出现在使用PRIMARY KEY或UNIQUE INDEX作为关联条件的表连接中。

const/system：单表中最多有一个匹配行，因此这个匹配行中的其他列能被优化器当做常量来使用。如根据PRIMARY KEY或UNIQUE INDEX进行过滤的查询。system是const的特例，当表中只有一条记录时的const就为system。

NULL：不用访问表或索引，就能直接得到结果。

还可取其他的值，如：

ref_or_null：与ref类似，区别在于条件中包含对NULL的查询。

index_merge：索引合并。

unique_subquery：IN后面是一个查询唯一索引字段的子查询。

index_subquery：与unique_subquery类型，区别在于IN后面是一个查询非唯一索引字段的子查询。

possible_keys：查询时可能使用的索引。

key：实际使用的索引。

key_len：实际使用到的索引的字节长度。

ref：实际使用的索引在其他表的关联字段。如果是常数等值查询，则为const。

rows：扫描的行数。

filtered：存储引擎返回的数据过滤后，满足查询条件的记录的比例。

Extra：执行情况的说明，包括不适合在其他列中显示但是对执行计划非常重要的额外信息。

Using filesort：filesort排序，而不是通过索引直接返回排序结果。

Using index：覆盖索引扫描，直接访问索引就能获取所需的数据，不需要通过索引回表。

Using index condition：使用ICP(Index Condition Pushdown，参看“索引”章节)优化查询，将某些情况下的条件过滤操作下放到存储引擎层完成，降低不必要的IO访问。

Using where：优化器除了利用索引加速访问外，还需根据索引回表查询数据。

Using union：多次查询后对结果集合并，如使用OR查询。

通过SHOW PROFILES和SHOW PROFILE分析SQL

profiling默认是关闭的，可通过设置变量@@profiling进行打开或关闭。

SHOW PROFILES结果包括以下字段：

Query_ID：查询ID。

Duration：查询耗时。

Query：查询语句。

SHOW PROFILE [ALL|CPU|{BLOCK IO}|{PAGE FAULTS}|SOURCE][, ...] FOR QUERY query_id(query_id为SHOW PROFILES结果的Query_ID字段)结果包括以下字段：

Status：查询执行过程中的状态。各状态含义如下：

starting

Waiting for query cache lock

checking query cache for query

checking permissions

Opening tables

inti

System lock

optimizing

statistics

preparing

executing

Sending data：开始访问数据行并把结果返回客户端，包含大量的磁盘操作。

end

query end

closing tables

removing tmp table

freeing items

storing result in query cache

logging slow query

cleaning up

Duration：耗时。

CPU_user

CPU_system

Context_voluntary

Context_involuntary

Block_ops_in

Block_ops_out

Messages_sent

Messages_received

Page_faults_major

Page_faults_minor

Swaps

Source_function

Source_file

Source_line

通过trace分析优化器如何选择执行计划

需打开trace，设置格式为JSON，设置trace最大能使用的内存大小。如：

SET @@optimizer_trace="enabled=on";

SET @@end_markers_in_json=on;

SET @@optimizer_trace_max_size=1000000;

执行SELECT * FROM information_schema.OPTIMIZER_TRACE即可得到结果。

分析、检查、优化、修复表

ANALYZE、CHECK、OPTIMIZE、REPAIR执行期间都会对表进行锁定。

分析表，使得SQL能够生成正确的执行计划。如果感觉实际的执行计划并不符合预期，执行一次分析表可能会解决问题：

ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tablename[, ...]

检查表，用于检查表或视图是否有错误。如视图定义中被引用的表不存在：

CHECK TABLE tablename[, ...] [{QUICK|FAST|MEDIUM|EXTENDED|CHANGED}[ ...]]

优化表，可以将表中的空间碎片进行合并。如果已经删除表的很大一部分数据，或已经对含有可变长度行(含有VARCHAR、*BLOB或*TEXT的列)的表进行很多更改，则应该进行优化表：

OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tablename[, ...]

修复表，对坏表进行修复：

REPAIR [LOCAL|NO_WRITE_TO_BINLOG] TABLE tablename[, ...] [{QUICK|EXTENDED|USE_FRM}[ ...]]

常用SQL优化

大批量导入数据

对MyISAM存储引擎，可通过关闭和打开非唯一索引的更新提高导入效率：

ALTER TABLE tablename DISABLE KEYS;

# import data

ALTER TABLE tablename ENALBE KEYS;

对InnoDB存储引擎：

因为InnoDB表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以提高导入效率。

在导入数据前执行SET unique_checks=0关闭唯一性校验，在导入结束后执行SET unique_checks=1恢复唯一性校验，可提高导入效率。

如果应用使用自动提交的方式，建议导入前执行SET autocommit=0关闭自动提交，导入结束后执行SET autocommit=1恢复自动提交，可提高导入效率。

优化INSERT语句

当从同一客户端插入很多行时，应尽量使用多个值列表的INSERT语句。

如果从不同客户端插入很多行，可以使用INSERT DELAYED，让INSERT`马上返回(实际上数据被放在MySQL服务器内存队列中)。

将索引文件和数据文件在不同的磁盘存放(利用建表中的选项)。

如果进行批量INSERT，可以增加bulk_insert_buffer_size变量值来提高速度(只对MyISAM表使用)。

当从一个文件装载一个表时，使用LOAD DATA INFILE。

优化ORDER BY语句

MySQL有两种排序方式：

通过有序索引扫描直接返回有序数据。在使用EXPLAIN分析时显示为Using index。

filesort排序。将返回的数据在sort_buffer_size设置的内存排序区进行排序，至于是否使用磁盘文件和临时表等，取决于MySQL服务器对排序参数的设置和需要排序数据的大小。

对于filesort，MySQL比较查询取出的字段总大小和max_length_for_sort_data，判断使用哪种排序算法：

两次扫描算法(Tow passes)：第一次根据条件获取排序字段和行指针信息，并在排序区中排序。第二次根据行指针回表读取记录，可能导致大量随机IO操作。

一次扫描算法(Single Pass)：一次性取出满足条件的行的所有字段，然后在排序区排序后直接输出结果。这会导致内存开销比较大。

优化ORDER BY语句应该：尽量减少额外的排序，通过索引直接返回有序数据。WHERE条件和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或都是降序。否则肯定需要额外的排序操作，这样就会出现filesort排序。

尽量SELECT必要的字段名，而不是SELECT *所有字段，这样可以减少排序区的使用，提高性能。

优化GROUP BY语句

MySQL会对GROUP BY的所有字段进行排序。如果想避免排序的消耗，可以使用ORDER BY NULL禁止排序。

优化子查询

有些情况下，子查询可以被更有效率的表连接代替。因为表连接不需要在内存中创建临时表。

优化OR条件

对于含有OR的查询，如果要利用索引，则OR之间的每个字段都必需能利用索引。此时，实际是对OR的各个字段分别查询的结果进行UNION操作。

优化分页查询

执行LIMIT offset_start, row_count时，MySQL排序出offset_start+row_count条记录后仅仅返回最后row_count条记录，前面的offset_start条记录都会被丢弃，查询和排序的代价非常高。有两种优化思路：

在索引上完成排序分页的操作，最后根据主键关联回表查询所需的其他列内容。

把查询转换成基于某个位置的查询，使用LIMIT row_count代替LIMIT offset_start, row_count。但这种方式对数据集有特定的要求。

使用SQL提示

SQL提示(SQL HINT)就是在SQL语句中加入一些人为提示来达到优化的目的。

SELECT SQL_BUFFER_RESULT * FROM ...

这个语句强制MySQL生成一个临时结果集。生成后所有表上的锁均被释放，这能在遇到表锁问题或要花很长时间将结果传给客户端时有帮助。

SELECT * FROM tablename USE|IGNORE|FORCE INDEX (indexname[, ...]) WHERE ...

USE INDEX提供希望(实际执行时不一定会被选择)查询时使用的索引，IGNORE INDEX忽略指定的索引，FORCE INDEX强制使用指定的索引。

常用SQL技巧

利用ORDER BY RAND()提取随机行。

利用GROUP BY ... WITH ROLLUP获取更多的分组聚合信息。