本文更新于2019-08-18,使用Mysql 5.7,操作系统为Deepin 15.4。 目录优化sql语句的步骤通过SHOW STATUS了解SQL语句的执行情况定位执行效率低下的SQL语句通过EXPLaiN或DESC分析SQ
本文更新于2019-08-18,使用Mysql 5.7,操作系统为Deepin 15.4。
目录
SHOW STATUS
了解SQL语句的执行情况操作的计数,是对执行次数进行计数,不论提交还是回滚都会累加。
Com_xxx形式的参数表示每个xxx语句执行的次数,对所有的存储引擎都会进行累计,如:
SELECT
的次数。INSERT
的次数,对批量插入的操作只累加一次。UPDATE
的次数。DELETE
的次数。Innodb_rows_xxx形式的参数只对InnoDB存储引擎进行累计,其累计的方式也与Com_xxx不同:
SELECT
返回的行数。INSERT
插入的行数。UPDATE
更新的行数。DELETE
删除的行数。Handler_read_xxx形式的参数可表示索引的使用情况:
以下参数便于了解数据库的基本情况:
EXPLAIN
或DESC
分析SQL的执行计划DESC
和EXPLAIN
分析SQL执行计划的使用和作用是一样的。
执行EXPLAIN statement
后再执行SHOW WARNINGS
,可以看到被优化器改写后真正执行的SQL。
一个执行计划包括若干行,每行包括如下的列:
id:值越大越先执行(值越大越位于下方),一样大从上至下执行。
select_type:查询类型,可取如下值:
UNION
的查询。UNION
中的第一个查询。UNION
中的第二个或之后的查询。table:输出结果集的表。
partitions:访问的分区。
type:访问类型,即在表中查找所需行的方式。
以下取值性能由最差至最好:
<
、<=
、>
、>=
、BETWEEN
等操作符。JOIN
操作中。PRIMARY KEY
或UNIQUE INDEX
作为关联条件的表连接中。PRIMARY KEY
或UNIQUE INDEX
进行过滤的查询。system是const的特例,当表中只有一条记录时的const就为system。还可取其他的值,如:
NULL
的查询。IN
后面是一个查询唯一索引字段的子查询。IN
后面是一个查询非唯一索引字段的子查询。possible_keys:查询时可能使用的索引。
key:实际使用的索引。
key_len:实际使用到的索引的字节长度。
ref:实际使用的索引在其他表的关联字段。如果是常数等值查询,则为const。
rows:扫描的行数。
filtered:存储引擎返回的数据过滤后,满足查询条件的记录的比例。
Extra:执行情况的说明,包括不适合在其他列中显示但是对执行计划非常重要的额外信息。
SHOW PROFILES
和SHOW PROFILE
分析SQLprofiling默认是关闭的,可通过设置变量@@profiling
进行打开或关闭。
SHOW PROFILES
结果包括以下字段:
SHOW PROFILE [ALL|CPU|{BLOCK IO}|{PAGE FAULTS}|SOURCE][, ...] FOR QUERY query_id
(query_id为SHOW PROFILES
结果的Query_ID字段)结果包括以下字段:
需打开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}[ ...]]
对MyISAM存储引擎,可通过关闭和打开非唯一索引的更新提高导入效率:
ALTER TABLE tablename DISABLE KEYS;
# import data
ALTER TABLE tablename ENALBE KEYS;
对InnoDB存储引擎:
SET unique_checks=0
关闭唯一性校验,在导入结束后执行SET unique_checks=1
恢复唯一性校验,可提高导入效率。SET autocommit=0
关闭自动提交,导入结束后执行SET autocommit=1
恢复自动提交,可提高导入效率。INSERT
语句INSERT
语句。,让
INSERT`马上返回(实际上数据被放在mysql服务器内存队列中)。INSERT
,可以增加bulk_insert_buffer_size变量值来提高速度(只对MyISAM表使用)。LOAD DATA INFILE
。ORDER BY
语句MySQL有两种排序方式:
EXPLAIN
分析时显示为Using index。对于filesort,MySQL比较查询取出的字段总大小和max_length_for_sort_data,判断使用哪种排序算法:
优化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 HINT)就是在SQL语句中加入一些人为提示来达到优化的目的。
SELECT SQL_BUFFER_RESULT * FROM ...
这个语句强制MySQL生成一个临时结果集。生成后所有表上的锁均被释放,这能在遇到表锁问题或要花很长时间将结果传给客户端时有帮助。
SELECT * FROM tablename USE|IGNORE|FORCE INDEX (indexname[, ...]) WHERE ...
USE INDEX
提供希望(实际执行时不一定会被选择)查询时使用的索引,IGNORE INDEX
忽略指定的索引,FORCE INDEX
强制使用指定的索引。
ORDER BY RAND()
提取随机行。GROUP BY ... WITH ROLLUP
获取更多的分组聚合信息。--结束END--
本文标题: MySQL学习笔记(18):SQL优化
本文链接: https://www.lsjlt.com/news/7187.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0