- 浏览: 88618 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
xiaoyi829:
应该可以grzrt 写道分区表partition,能用hand ...
初识mysql插件之HandlerSocket -
grzrt:
分区表partition,能用handlersocket查询指 ...
初识mysql插件之HandlerSocket
前记:很多东西看似简单,那是因为你并未真正了解它。
Explain命令用于查看执行效果。虽然这个命令只能搭配select类型语句使用,如果你想查看update,delete类型语句中的索引效果,也不是太难的事情,只要保持条件不变,把类型转换成select就行了。
explain的语法如下:
explain [extended] select ... from ... where ...
如果使用了extended,那么在执行完explain语句后,可以使用show warnings语句查询相应的优化信息。
==============================================================
mk-visual-explain工具扩展了explain,它提供了一种更直观的树形表现形式,使用方法很简单:
mk-visual-explain <file_containing_explain_output>
mk-visual-explain -c <file_containing_query>
mysql -e "explain select * from mysql.user" | mk-visual-explain
也可以在MySQL命令行里通过设置pager的方式来执行:
mysql> pager mk-visual-explain
mysql> explain [extended] select ... from ... where ...
==============================================================
进入正题,为了让例子更具体化,我们先来建一个表,插入一点测试数据:
CREATE TABLE IF NOT EXISTS `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varbinary(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `article`
(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2');
缺省只建了一个主键,没有建其他的索引。测试时,如果你时间充裕,应该尽可能插入多一点的测试数据,怎么说也应该保证几千条。如果数据量过少,可能会影响MySQL在索引选择上的判断。如此一来,一旦产品上线,数据量增加。索引往往不会按照你的预想工作。
下面让我们设置一个任务:查询category_id为1且comments大于1的情况下,views最多的article_id。
问题很简单,SQL也很简单:
SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1
下面让我们用explain命令查看索引效果:
EXPLAIN SELECT author_id
FROM `article`
WHERE category_id = 1
AND comments > 1
ORDER BY views DESC
LIMIT 1
这时explain部分结果如下:
type: ALL
key: NULL
Extra: Using where; Using filesort
显示数据库进行了全表扫描,没有用到索引,并且在过程中文件排序。这样的结果肯定是糟糕的,下面让我们通过建立索引优化一下它:
ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` ) ;
这时explain部分结果如下:
type: range
key: x
Extra: Using where; Using filesort
虽然不再是全表扫描了,但是仍然存在文件排序。一般来说,文件排序都是由于ORDER BY语句一起的,而我们已经把views字段放到了联合索引里面,为什么没有效果呢?这是因为按照BTree的工作原理,先排序category_id,如果遇到相同的category_id则再排序comments,如果遇到相同的comments则再排序views。当comments字段在联合索引里处于中间位置时,因为comments > 1条件是一个范围值(所谓range),MySQL目前无法利用索引再对后面的views部分进行检索,如果换成是是comments in ('a', 'b', 'c')这样的多等情况则可以,关于这一点,在High Performance MySQL一书中专门有过叙述,名为Avoiding Multiple Range Conditions,在复合索引里,仅仅只能保存一个range类型的查询字段,并且要放到复合索引的末尾,否则,range类型查询字段后面的索引无效。详细的介绍大家可以自己查阅。从这个意义上来说,此时的category_id, comments, views复合索引的效果不会比category_id, comments复合索引的效果好。
文件排序是否会引起性能问题要视数据分布情况而定。这里有一个案例可供参考:Using index for ORDER BY vs restricting number of rows.
多数情况下应该避免出现它。此时可以这样设置索引:
ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;
这时explain部分结果如下:
type: range
key: x
Extra: Using where; Using filesort
很奇怪,系统无视我们刚建立的y索引,还使用x索引。导致仍然存在文件排序。
如果你也出现了类似的情况,可以使用强制索引:
EXPLAIN SELECT author_id
FROM `article`
FORCE INDEX ( y )
WHERE category_id = 1
AND comments > 1
ORDER BY views DESC
LIMIT 1
这时explain部分结果如下:
type: ref
key: y
Extra: Using where
也可以删除x索引,那样系统会自动使用y索引(有时候MySQL比较傻,所以你得会使用FORCE INDEX)。
后记:Explain的type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref,否则就可能会出现性能问题。
Explain的Extra信息也相当重要,如果此信息显示Using filesort或者Using temporary的话,噩梦即将开始,不过也不尽然,比如说在一个WHERE ... ORDER BY ... 类型的查询里,很多时候我们无法创建一个兼顾WHERE和ORDER BY的索引,此时如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,文件排序是好是坏需要仔细判断,说白了就是看是先过滤再排序划算,还是先排序再过滤划算,正确答案取决与数据分布的情况,具体的情况可以参考Using index for ORDER BY vs restricting number of rows。
Explain命令用于查看执行效果。虽然这个命令只能搭配select类型语句使用,如果你想查看update,delete类型语句中的索引效果,也不是太难的事情,只要保持条件不变,把类型转换成select就行了。
explain的语法如下:
explain [extended] select ... from ... where ...
如果使用了extended,那么在执行完explain语句后,可以使用show warnings语句查询相应的优化信息。
==============================================================
mk-visual-explain工具扩展了explain,它提供了一种更直观的树形表现形式,使用方法很简单:
mk-visual-explain <file_containing_explain_output>
mk-visual-explain -c <file_containing_query>
mysql -e "explain select * from mysql.user" | mk-visual-explain
也可以在MySQL命令行里通过设置pager的方式来执行:
mysql> pager mk-visual-explain
mysql> explain [extended] select ... from ... where ...
==============================================================
进入正题,为了让例子更具体化,我们先来建一个表,插入一点测试数据:
CREATE TABLE IF NOT EXISTS `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varbinary(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `article`
(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2');
缺省只建了一个主键,没有建其他的索引。测试时,如果你时间充裕,应该尽可能插入多一点的测试数据,怎么说也应该保证几千条。如果数据量过少,可能会影响MySQL在索引选择上的判断。如此一来,一旦产品上线,数据量增加。索引往往不会按照你的预想工作。
下面让我们设置一个任务:查询category_id为1且comments大于1的情况下,views最多的article_id。
问题很简单,SQL也很简单:
SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1
下面让我们用explain命令查看索引效果:
EXPLAIN SELECT author_id
FROM `article`
WHERE category_id = 1
AND comments > 1
ORDER BY views DESC
LIMIT 1
这时explain部分结果如下:
type: ALL
key: NULL
Extra: Using where; Using filesort
显示数据库进行了全表扫描,没有用到索引,并且在过程中文件排序。这样的结果肯定是糟糕的,下面让我们通过建立索引优化一下它:
ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` ) ;
这时explain部分结果如下:
type: range
key: x
Extra: Using where; Using filesort
虽然不再是全表扫描了,但是仍然存在文件排序。一般来说,文件排序都是由于ORDER BY语句一起的,而我们已经把views字段放到了联合索引里面,为什么没有效果呢?这是因为按照BTree的工作原理,先排序category_id,如果遇到相同的category_id则再排序comments,如果遇到相同的comments则再排序views。当comments字段在联合索引里处于中间位置时,因为comments > 1条件是一个范围值(所谓range),MySQL目前无法利用索引再对后面的views部分进行检索,如果换成是是comments in ('a', 'b', 'c')这样的多等情况则可以,关于这一点,在High Performance MySQL一书中专门有过叙述,名为Avoiding Multiple Range Conditions,在复合索引里,仅仅只能保存一个range类型的查询字段,并且要放到复合索引的末尾,否则,range类型查询字段后面的索引无效。详细的介绍大家可以自己查阅。从这个意义上来说,此时的category_id, comments, views复合索引的效果不会比category_id, comments复合索引的效果好。
文件排序是否会引起性能问题要视数据分布情况而定。这里有一个案例可供参考:Using index for ORDER BY vs restricting number of rows.
多数情况下应该避免出现它。此时可以这样设置索引:
ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;
这时explain部分结果如下:
type: range
key: x
Extra: Using where; Using filesort
很奇怪,系统无视我们刚建立的y索引,还使用x索引。导致仍然存在文件排序。
如果你也出现了类似的情况,可以使用强制索引:
EXPLAIN SELECT author_id
FROM `article`
FORCE INDEX ( y )
WHERE category_id = 1
AND comments > 1
ORDER BY views DESC
LIMIT 1
这时explain部分结果如下:
type: ref
key: y
Extra: Using where
也可以删除x索引,那样系统会自动使用y索引(有时候MySQL比较傻,所以你得会使用FORCE INDEX)。
后记:Explain的type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref,否则就可能会出现性能问题。
Explain的Extra信息也相当重要,如果此信息显示Using filesort或者Using temporary的话,噩梦即将开始,不过也不尽然,比如说在一个WHERE ... ORDER BY ... 类型的查询里,很多时候我们无法创建一个兼顾WHERE和ORDER BY的索引,此时如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,文件排序是好是坏需要仔细判断,说白了就是看是先过滤再排序划算,还是先排序再过滤划算,正确答案取决与数据分布的情况,具体的情况可以参考Using index for ORDER BY vs restricting number of rows。
发表评论
-
mysql dump 备份及脚本!
2011-06-10 13:38 1518导出多张表的时候表之间用空格分开: # mysqldump ... -
mysql备份脚本
2011-06-03 17:32 659!/bin/sh # mysql_backup.sh: bac ... -
CentOS挂载移动硬盘
2011-06-03 15:12 10431, 首先确认fuse,CentOS 5.5 带有fuse,可 ... -
MySQL 左连接 右连接
2011-06-03 14:03 798表A记录如下: aID aNum 1 ... -
[转]CentOS5 下安装与配置飞鸽传书(Ipmsg)完美完结篇
2011-05-27 10:29 1524CentOS5 下安装与配置飞鸽传书(Ipmsg)完美完结篇 ... -
深入SQL语句性能调整
2011-05-17 13:20 804本文sqlserver为例 有 ... -
windows和linux下开启mysql日志
2011-05-11 10:24 2238mysql有以下几种日志: 错误日志: -log-err 查询 ... -
MYSQL数据库设计的一点总结
2011-04-13 14:48 672选表类型: 大家都知道 ... -
mysql 清理碎片
2011-04-13 09:59 830显示你数据库中存在碎片的全部列表: select tab ... -
MySQL 建表语法
2011-04-12 14:21 7261、最简单的: CREATE TABLE t1( id ... -
排序时最快的取出尽量少的字段且索引字段
2011-04-11 15:51 743select company_albums.id,compan ... -
MySQL性能优化
2011-04-02 10:53 697作者:andyao 原文link: http://andyao ... -
Mysql Innodb 引擎优化-参数
2011-03-30 16:49 732介绍: InnoDB给MySQL提供了具有提交,回滚和崩溃 ... -
MySQL前端和后台的系统优化
2011-03-30 16:39 766本文中介绍的系统优化 ... -
MySQL配置文件my.cnf 做笔记用
2011-03-30 16:33 736MySQL配置文件my.cnf 例子最详细翻译,可以保存做笔记 ... -
测试脚本mysql_插入100万行数据
2011-03-29 16:31 1312CREATE DEFINER=`root`@`localhos ... -
Mysql日期和时间函数
2011-03-29 15:50 638这里是一个使用日期函 ... -
MySQL数据库优化的具体方法说明
2011-03-29 15:39 721以下的文章主要讲述的是实现MySQL数据库简单实用优化的具体方 ... -
MySQL维护命令集锦--查看表的状态(show table status)
2011-03-29 15:11 1155查看表的引擎类型等状态信息: show table statu ... -
mysql show status参数详解
2011-03-29 15:03 869Aborted_clients 由于客户没有正确 ...
相关推荐
mysqlexplain
Mysql Explain
语法格式如下 EXPLAIN tbl_name 或者: EXPLAIN SELECT select_options EXPLAIN 语句可以被当作 DESCRIBE 的同义词来用,也可以用来获取一个MySQL要执行的 SELECT 语句的相关信息。 EXPLAIN tbl_name 语法和 DESCRIBE...
Mysql Explain 使用
mysql explain
EXPLAIN关键字一般放在SELECT查询语句的前面,用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数。explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们查询,让...
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法,在select语句前加上explain就可以了,如: explain select * from statuses_status where ...
MySQL explain 笔记整理 MySQL explain 笔记整理
Mysql Explain详细解析
看懂MySQL的SQL EXPLAIN
MySQL-Explain 详解
Mysql Explain 详解
MySQL的EXPLAIN执行计划.pdf
MySQL 性能优化 Explain ,MySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 Explain
博文链接:https://zhengdl126.iteye.com/blog/424090
mysql执行计划解读,刚要: 1、mysql执行计划调用方式; 2、执行计划包含的信息; 3、执行计划显示内容解读; 4、mysql执行计划的局限;
Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看SQL语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句。
MySQL的Explain总结.doc