优化SQL,是DBA常见的工作之一。如何高效、快速地优化一条语句,是每个DBA经常要面对的一个问题。在日常的优化工作中,我发现有很多操作是在优化过程中必不可少的步骤。然而这些步骤重复性的执行,又会耗费DBA很多精力。
今天抽空总结下mysql数据库常见的一些优化步骤,仅供参考。
执行计划 — EXPLAIN命令
执行计划是语句优化的主要切入点,通过执行计划的判读了解语句的执行过程。在执行计划生成方面,MySQL与Oracle明显不同,它不会缓存执行计划,每次都执行“硬解析”。
1. 基本用法
- EXPLAIN QUERY
当在一个Select语句前使用关键字EXPLAIN时,MySQL会解释了即将如何运行该Select语句,它显示了表如何连接、连接的顺序等信息。
- EXPLAIN EXTENDED QUERY
当使用EXTENDED关键字时,EXPLAIN产生附加信息,可以用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名,重写并且执行优化规则后SELECT语句是什么样子,并且还可能包括优化过程的其它注解。
- EXPLAIN PARTITIONS QUERY
显示的是查询要访问的数据分片——如果有分片的话。它只能在MySQL5.1及更新的版本里使用。
- EXPLAIN FORMAT=JSON (5.6新特性)
另一个格式显示执行计划。可以看到诸如表间关联方式等信息。
2. 输出字段
下面说明一下EXPLAIN输出的字段含义:
(1) id
MySQL选定的执行计划中查询的序列号。如果语句里没有子查询等情况,那么整个输出里就只有一个SELECT,这样一来每一行在这个列上都会显示一个1。如果语句中使用了子查询、集合操作、临时表等情况,会给ID列带来很大的复杂性。
(2) select_type
语句所使用的查询类型。是简单SELECT还是复杂SELECT(如果是后者,显示它属于哪一种复杂类型)。常用有以下几种标记类型。
(3) table
这一步所访问的数据库中表的名称或者SQL语句指定的一个别名表。这个值可能是表名、表的别名或者一个为查询产生的临时表的标识符,如派生表、子查询或集合。
(4) type
表的访问方式。以下列出了各种不同类型的表连接,依次是从最好的到最差的。
(5) possible_keys
该字段是指MySQL在搜索表记录时可能使用哪个索引。如果没有任何索引可以使用,就会显示为null。
(6) key
查询优化器从possible_keys中所选择使用的索引。key字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候,这个字段的值就是NULL。
(7) key_len
被选中使用索引的索引键长度。key_len字段显示了MySQL使用索引的长度。当key字段的值为NULL时,索引的长度就是NULL。
(8) ref
列出是通过常量,还是某个表的某个字段来过滤的。ref字段显示了哪些字段或者常量被用来和key配合从表中查询记录出来。
(9) rows
该字段显示了查询优化器通过系统收集的统计信息估算出来的结果集记录条数。
(10) Extra
该字段显示了查询中MySQL的附加信息。
(11) filtered
这个列式在MySQL5.1里新加进去的,当使用EXPLAIN EXTENDED时才会出现。它显示的是针对表里符合某个条件(WHERE子句或联接条件)的记录数的百分比所作的一个悲观估算。
3. SQL改写
EXPLAIN除了可以显示执行计划外,还可以显示SQL改写。所谓SQL改写,是指MySQL在对SQL语句进行优化前,会基于一些原则进行语句的改写,以方便后面的优化器进行优化生成更优的执行计划。该功能是通过EXPLAIN EXTENDED+SHOW WARNINGS配合使用。
简单做个实验:
从上面示例中,可看到原有语句中的IN子查询被改写成为表间关联的方式。
二、统计信息
查看统计信息也是优化语句中必不可少的一步。通过统计信息可以快速了解对象的存储特征如何。下面说明主要的两类统计信息——表、索引。
1. 表统计信息 — SHOW TABLE STATUS
说明如下:
三、索引统计信息 — SHOW INDEX
说明如下:
四、优化器开关
在MySQL中,还有一些参数是可以用来控制优化器行为的。
1. 参数说明
2. 实验--干预优化器行为(ICP特性)
默认情况下,ICP特性是开启的。查看一下优化器行为。
基于二级索引的过滤查询,使用了ICP特性,从Extra中的”Using index condition”可见。如果通过优化器开关,干预优化器行为,又会如何呢?
- set optimizer_switch="index_condition_pushdown=off";
从Extra可见,ICP特性已经禁用。
五、系统状态(SHOW STATUS)
MySQL中也内置了一些状态,通过这些状态变量也可反映出语句执行的一些情况,方便定位问题。手工执行的话,可以在执行语句的前后分别执行SHOW STATUS命令,查看状态的变化。
状态变量:
状态变量很多,这里只介绍几个常用的。
六、SQL性能分析器(Query Profiler)
MySQL的Query Profiler是一个使用非常方便的Query诊断分析工具,通过该工具可以获取一条Query在整个执行过程中多种资源的消耗情况,如CPU、IO、IPC、SWAP等,以及发生的PAGE FAULTS、CONTEXT SWITCHE等,同时还能得到该Query执行过程中的MySQL所调用的各个函数在源文件中的位置。
1. 开启
- mysql> select @@profiling;
- mysql> set profiling=1;
默认情况下profiling的值为0表示MySQL SQL Profiler处于OFF状态,开启SQL性能分析器后profiling的值为1。
2. 执行SQL语句
- mysql> select count(*) from t1;
3. 获取概要信息
使用"show profile"命令获取当前系统中保存的多个Query的profile的概要信息。
- mysql> show profiles;
4. 针对单个Query获取详细的profile信息
在获取概要信息之后,就可以根据概要信息的Query_ID来获取某个Query的执行过程中详细的profile信息。
- mysql> show profile for query 1;
- mysql> show profile cpu,block io for query 1;
Elasticsearch 是通过 Lucene 的倒排索引技术实现比关系型数据库更快的过滤。特...
4月11日20:30~22:00通过腾讯会议进行了第二次在线学习讨论我把学习笔记整理一下...
正则忽略大小写 – RegexOptions.IgnoreCase 例如: 复制代码 代码如下: Str = R...
本文实例讲述了Laravel框架源码解析之反射的使用。分享给大家供大家参考,具体如...
项目中用到的一些特殊字符和图标 html代码 XML/HTML Code 复制内容到剪贴板 div ...
复制代码 代码如下: % URL="http://news.163.com/special/00011K6L/rss_newstop....
DELETEFROMTablesWHEREIDNOTIN(SELECTMin(ID)FROMTablesGROUPBYName) Min的话保...
错误描述: 在开发.net项目中,通过microsoft.ACE.oledb读取excel文件信息时,报...
工具:Eclipse,Oracle,smartupload.jar;语言:jsp,Java;数据存储:Oracle。...
上篇文章给大家介绍了 Java正则表达式匹配,替换,查找,切割的方法 ,接下来,...