由于程序员写的东西不一定是最优的所以我们只要启用 mysql 慢查询日志功能就可以分析每一条 sql 语句所运行相关性能了,下面大家一起来看看
MySQL 是一个开放源码的小型关联式数据库管理系统,开发者为瑞典 MySQL AB 公司。MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。
在 MySQL 中,慢查询的界定时间是由 MySQL 内置参数变量 long_query_time 来指定的,其默认值为 10(单位:秒),我们可以通过 show variables like 'long_query_time'; 指令来查看该参数变量的信息:
long_query_time 的默认值为 10 秒
不过,在程序开发过程中,我们认为慢速查询的界定时间并没有 10 秒这么长,依据不同项目的不同需求,我们一般将慢查询的界定时间设定为 1~5 秒之间。我们可以使用指令 set long_query_time = 秒数来设定 long_query_time 变量的值。
设定 long_query_time 的值为 1 秒
修改了 long_query_time 参数后,我们还需要让 MySQL 能够记录下慢查询的日志信息。因为,在默认情况下,MySQL 并不会记录慢查询的日志信息。如果要记录慢查询日志,我们需要以命令行方式进入 MySQL 安装目录in 目录 (也可将该目录添加到 PATH 环境变量中),使用如下命令重新启动 MySQL:
- #中括号[]内的部分是可选的,file_name表示日志文件路径
- #在5.5及以上版本的MySQL中,使用如下命令启动:
- mysqld --show-query-log[=1] [--show-query-log-file=file_name]
- #在5.0、5.1等低版本的MySQL中,使用如下命令启动:
- mysqld --log-slow-queries[=file_name]
在上述命令中,如果没有指定日志文件名,则日志文件名称默认为主机名 - slow.log;如果没有指定文件路径或者指定的文件路径不是绝对路径,则日志文件将默认存放在 MySQL 配置文件 my.ini 中参数 datadir 所指定的目录下。
此外,你也可以将上述命令行启动命令配置到 my.ini 中的 [mysqld] 节点下,这样无需每次手动键入上述命令来启动。
- [mysqld]
- #设置慢查询界定时间为1秒
- long_query_time=1
- #5.0、5.1等版本配置如下选项
- log-slow-queries="mysql_slow_query.log"
- #5.5及以上版本配置如下选项
- slow-query-log=On
- slow_query_log_file="mysql_slow_query.log"
注意:虽然慢查询的名字中只包含了 "查询",实际上并不仅仅表示 SELECT 查询操作,诸如 INSERT、UPDATE、DELETE、CALL 等其他 DML 操作,只要是超过指定时间的,都可以称之为 "慢查询",并且会记录在慢查询日志中。
这个时候,我们就需要使用 EXPLAIN 指令来获取 SELECT 语句执行的细节信息。在 MySQL 中数据库中,EXPLAIN 指令的用法非常简单,如下所示:
EXPLAIN SELECT 语句
- --例如:EXPLAIN SELECT * FROM admin WHERE user_id = 1;
如果是在 Oracle 数据库中,我们同样可以使用如下 SQL 语句来获取 SELECT 语句的执行计划信息:
- EXPLAIN PLAN FOR SELECT语句
- --例如:EXPLAIN PLAN FOR SELECT * FROM admin WHERE user_id = 1;
在这里,我们以 MySQL 为例,在数据库中执行上述 EXPLAIN 指令,将会输出如下结果 (user_id 是 admin 表的主键列):
为了进一步说明上述执行计划信息所表示的含义,我们再次执行带格式化的 EXPLAIN 命令
- EXPLAIN SELECT * FROM admin WHERE user_id = 1G;
(G 在 MySQL 中表示格式化输出结果),我们将会得到如下信息:
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: admin
- type: const
- possible_keys: PRIMARY
- key: PRIMARY
- key_len: 1
- ref: const
- rows: 1
- Extra:
- 1 row in set (0.00 sec)
下面,我们来详细介绍上述输出结果中每个字段信息所表示的含义:
id
SELECT 识别符。这是 SELECT 的查询序号。
select_type
SELECT 类型,可以为以下任何一种:
SIMPLE
简单 SELECT(不使用 UNION 或子查询)。
PRIMARY
最外面的 SELECT
UNION
UNION 中的第二个或后面的 SELECT 语句。
DEPENDENT UNION
UNION 中的第二个或后面的 SELECT 语句,取决于外面的查询。
UNION RESULT
UNION 的结果。
SUBQUERY
子查询中的第一个 SELECT。
DEPENDENT SUBQUERY
子查询中的第一个 SELECT,取决于外面的查询。
DERIVED
导出表的 SELECT(FROM 子句的子查询)。
table
输出的行所引用的表。
type
联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
system
表仅有一行 (= 系统表)。这是 const 联接类型的一个特例。
const
表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const 表很快,因为它们只读取一次!
eq_ref
对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了 const 类型。它用在一个索引的所有部分被联接使用并且索引是 UNIQUE 或 PRIMARY KEY。
ref
对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是 UNIQUE 或 PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用 ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。ref 可以用于使用 = 或 <=> 操作符的带索引的列。
ref_or_null
该联接类型如同 ref,但是添加了 MySQL 可以专门搜索包含 NULL 值的行。在解决子查询中经常使用该联接类型的优化。
index_merge
该联接类型表示使用了索引合并优化方法。在这种情况下,key 列包含了使用的索引的清单,key_len 包含了使用的索引的最长的关键元素。
unique_subquery
该类型替换了下面形式的 IN 子查询的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)。 unique_subquery 是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery
该联接类型类似于 unique_subquery。可以替换 IN 子查询,但只适合下列形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
range
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引。key_len 包含所使用索引的最长关键元素。在该类型中 ref 列为 NULL。 当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range。
index
该联接类型与 ALL 相同,除了只有索引树被扫描。这通常比 ALL 快,因为索引文件通常比数据文件小。 当查询只使用作为单索引一部分的列时,MySQL 可以使用该联接类型。
ALL
对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记 const 的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用 ALL,使得行能基于前面的表中的常数值或列值被检索出。
possible_keys
possible_keys 列指出 MySQL 能使用哪个索引在该表中找到行。注意,该列完全独立于 EXPLAIN 输出所示的表的次序。这意味着在 possible_keys 中的某些键实际上不能按生成的表次序使用。 如果该列是 NULL,则没有相关的索引。在这种情况下,可以通过检查 WHERE 子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用 EXPLAIN 检查查询。
key
key 列显示 MySQL 实际决定使用的键 (索引)。如果没有选择索引,键是 NULL。要想强制 MySQL 使用或忽视 possible_keys 列中的索引,在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。
key_len
key_len 列显示 MySQL 决定使用的键长度。如果键是 NULL,则长度为 NULL。注意通过 key_len 值我们可以确定 MySQL 将实际使用一个多部关键字的几个部分。
ref
ref 列显示使用哪个列或常数与 key 一起从表中选择行。
rows
rows 列显示 MySQL 认为它执行查询时必须检查的行数。
Extra
该列包含 MySQL 解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串:
Distinct
MySQL 发现第 1 个匹配行后,停止为当前的行组合搜索更多的行。
Not exists
MySQL 能够对查询进行 LEFT JOIN 优化,发现 1 个匹配 LEFT JOIN 标准的行后,不再为前面的的行组合在该表内检查更多的行。 下面是一个可以这样优化的查询类型的例子:SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL; 假定 t2.id 定义为 NOT NULL。在这种情况下,MySQL 使用 t1.id 的值扫描 t1 并查找 t2 中的行。如果 MySQL 在 t2 中发现一个匹配的行,它知道 t2.id 绝不会为 NULL,并且不再扫描 t2 内有相同的 id 值的行。换句话说,对于 t1 的每个行,MySQL 只需要在 t2 中查找一次,无论 t2 内实际有多少匹配的行。
range checked for each record (index map: #)
MySQL 没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。对前面的表的每个行组合,MySQL 检查是否可以使用 range 或 index_merge 访问方法来索取行。 这并不很快,但比执行没有索引的联接要快得多。
Using filesort
MySQL 需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配 WHERE 子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。
Using index
从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时,可以使用该策略。
Using temporary
为了解决查询,MySQL 需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的 GROUP BY 和 ORDER BY 子句时。
Using where
WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果 Extra 值不为 Using where 并且表联接类型为 ALL 或 index,查询可能会有一些错误。 如果想要使查询尽可能快,应找出 Using filesort 和 Using temporary 的 Extra 值。
Using sort_union(...), Using union(...), Using intersect(...)
这些函数说明如何为 index_merge 联接类型合并索引扫描。
Using index for group-by
类似于访问表的 Using index 方式,Using index for group-by 表示 MySQL 发现了一个索引,可以用来查询 GROUP BY 或 DISTINCT 查询的所有列,而不要额外搜索硬盘访问实际的表。并且,按最有效的方式使用索引,以便对于每个组,只读取少量索引条目。
来源: http://www.phperz.com/article/17/0318/318460.html