- 浏览: 1243641 次
- 性别:
- 来自: 深圳
文章分类
- 全部博客 (608)
- 数据结构 (2)
- AJAX (3)
- 设计模式 (3)
- java (117)
- js (14)
- css (9)
- jsp (10)
- 杂文 (49)
- htmlparser (6)
- 数据库 (29)
- 算法 (14)
- 数据挖掘 (11)
- 电脑杂症 (12)
- 网络爬虫 (7)
- 应用服务器 (9)
- PHP (2)
- C# (14)
- 测试 (3)
- WEB高性能开发 (3)
- swt (1)
- 搜索引擎 (16)
- HttpClient (4)
- Lite (1)
- EXT (1)
- python (1)
- lucene (4)
- sphinx (9)
- Xapian (0)
- linux (44)
- 问题归类 (1)
- Android (6)
- ubuntu (7)
- SEO (18)
- 数学 (0)
- 农业资讯 (12)
- 游戏 (3)
- nginx (1)
- TeamViewer (1)
- swing (1)
- Web前 端 (1)
- 主页 (0)
- 阿萨德发首发身份 (0)
- 软件设计师 (0)
- hibernate (5)
- spring3.0 (5)
- elastic (1)
- SSH (3)
- ff (0)
- oracle 10g (9)
- 神经网络 (1)
- struts2.0 (2)
- maven (1)
- nexus (1)
- 辅助工具 (3)
- Shiro (1)
- 联通项目 (0)
- 2014年专业选择 (0)
- freemarker (1)
- struts1.2 (8)
- adfasdfasfasf (0)
- TortoiseSVN (1)
- jstl (1)
- jquery (1)
- eclipse plugin (0)
- 游戏外挂 (1)
- 推广 (0)
- 按键精灵 (1)
- ibatis3.0 (1)
最新评论
-
水野哲也:
不不不, 这个您真错了!其实是你引用的那个jsp和本身的jsp ...
解析关于jsp页面指令冲突问题contentType="text/html;charset=UTF-8" -
caobo_cb:
import xx.cn.weibo.Util;
[ java版]新浪微博之ruquest_token篇 -
caobo_cb:
你好 Util包没有
[ java版]新浪微博之ruquest_token篇 -
小桔子:
你好!我遇到个问题 max_allowed_packet值总是 ...
mysql查询占用内存,优化的技巧 -
donghustone:
谢谢大神!
用JSmooth制作java jar文件的可执行exe文件教程(图文)
mysql explain 知识一
- 博客分类:
- 数据库
前记:很多东西看似简单,那是因为你并未真正了解它。
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,文件排序是好是坏需要仔细判断,说白了就是看是先过滤再排序划算,还是先排序再过滤划算,正确答案取决与数据分布的情况
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,文件排序是好是坏需要仔细判断,说白了就是看是先过滤再排序划算,还是先排序再过滤划算,正确答案取决与数据分布的情况
发表评论
-
数据导入报错:Got a packet bigger than‘max_allowed_packet’bytes的问
2012-09-07 12:12 3039数据导入报错:Got a packet bigger than ... -
mysql 创建 数据库时指定编码
2012-09-03 17:17 795mysql 创建 数据库时指定编码很重要,很多开发者都使用了默 ... -
mysql MERGE 错误(differently defined or of non-MyISAM type)
2012-02-15 11:35 5507错误代码: Error Code : 1168 Unable ... -
Mysql中索引和UNION ALL的使用
2012-02-14 09:52 1429MYSQL描述: 一个文章库,里面有两个表:categor ... -
mongodb
2012-01-23 12:41 2025mongodb 操作记录 mongod --dbpath & ... -
MongoDB2.0.1 出现严重数据丢失问题
2011-12-15 14:05 1679再插入1000W的数据,既然丢失100W多数据。太夸张了吧。 ... -
SQL注入攻击的种类和防范手段
2011-12-03 10:34 1177观察近来的一些安全事件及其后果,安全专家们已经得到一个结论,这 ... -
mysql分表的3种方法
2011-10-21 10:11 815一,先说一下为什么要 ... -
libmysqlclient.la
2011-07-17 21:15 1374collect2: ld returned 1 exit st ... -
mysql备份
2011-07-09 02:43 1043备份MySQL数据库的命令 mysqldump -hhost ... -
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLIC
2011-06-06 17:08 1588今天,在写触发器的时候,添加了一个更新语句,实在想不通。把更新 ... -
mysql explain 笔记整理
2011-06-04 10:16 955explain是用来分析sql语句,帮助优化的一个命令。 e ... -
MySQL: ERROR 13 (HY000): Can't get stat of
2011-04-11 19:42 3164但是将LOAD DATA INFILE 换成 LOAD D ... -
mysql查询占用内存,优化的技巧
2011-01-20 15:18 10857在Apache, PHP, MySQL的体系 ... -
linux mysql 启动失败(Can't create/write to can't create PID file: >Permission denied
2011-01-07 10:42 9780>071117 20:53:26 mysqld st ... -
MySQL触发器更新本表数据异常:Can't update table 'tbl' in stored function/trigger because it
2011-01-07 10:17 7495如果你在触发器里面对刚刚插入的数据进行了 insert/upd ... -
MYSQL 学习参考资料
2011-01-05 23:19 703http://dev.mysql.com/doc/refman ... -
mysql 定时执行
2010-10-15 13:44 1137首先,这个功能只能在5.1.X之上才能用 CREATE T ... -
安装、重装时出现could not start the service mysql error:0的错误或者start service停止不动时
2010-04-02 00:43 3730关于XP安装mysql-5.1.45-win3 ... -
mysql集群
2009-08-18 18:53 1210相关文章: mysql clus ...
相关推荐
61.MySQL高级_如何锁定一行.avi 60.MySQL高级_间隙锁危害.avi 59.MySQL高级_索引失效行锁变表锁.avi 58.MySQL高级_行锁演示答疑补充.avi 57.MySQL高级_行锁案例讲解.avi 56.MySQL高级_行锁理论.avi 55....
尚硅谷_MySQL高级_性能分析前提知识 · 21.尚硅谷_MySQL高级_explain使用简介 · 22.尚硅谷_MySQL高级_explain之id介绍 · 23.尚硅谷_MySQL高级_explain之select_type和table介绍 · 24.尚硅谷_MySQL高级_...
这个是MySQL5.5再contos6下的安装方法 MySQL 是目前最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 也是目前最好的 RDBMS 应用软件之一。随着淘宝去IOE(去除IBM小型机、Oracle数据库及EMC存储设备)化的推进,...
18.MySQL高级锁InnoDB行锁介绍及背景知识.avi 18.MySQL高级锁InnoDB行锁类型.avi 19.MySQL高级锁InnoDB行锁基本演示.avi 20.MySQL高级锁InnoDB行锁行锁升级为表锁.avi 21.MySQL高级锁InnoDB行锁间隙锁危害.avi 22....
MySQL是目前流行的关系型数据库管理系统,在WEB应用方面 MySQL 也是目前好的 RDBMS 应用软件之一。随着淘宝去IOE(去除IBM小型机、Oracle数据库及EMC存储设备)化的推进,MySQL 数据库在当前的互联网应用中变得越来越...
2.10.3. 将MySQL数据库拷贝到另一台机器 2.11. 降级MySQL 2.12. 具体操作系统相关的注意事项 2.12.1. Linux注意事项 2.12.2. Mac OS X注意事项 2.12.3. Solaris注意事项 2.12.4. BSD注意事项 2.12.5. 其它Unix注意...
主要介绍了Mysql中explain的相关内容,涉及索引的部分知识,具有一定参考价值,需要的朋友可以了解下。
2.10.3. 将MySQL数据库拷贝到另一台机器 2.11. 降级MySQL 2.12. 具体操作系统相关的注意事项 2.12.1. Linux注意事项 2.12.2. Mac OS X注意事项 2.12.3. Solaris注意事项 2.12.4. BSD注意事项 2.12.5. 其它Unix注意...
2.10.3. 将MySQL数据库拷贝到另一台机器 2.11. 降级MySQL 2.12. 具体操作系统相关的注意事项 2.12.1. Linux注意事项 2.12.2. Mac OS X注意事项 2.12.3. Solaris注意事项 2.12.4. BSD注意事项 2.12.5. 其它Unix注意...
第一部 MySQL基础入门(21节) 01-老男孩运维DBA课程开班.avi 02-数据库概念介绍.avi 03-数据库种类介绍及关系型数据库原理.avi 04-非关系型数据库介绍.avi 05-非关系型数据库种类介绍.avi 06-关系型数据库产品介绍...
2.10.3. 将MySQL数据库拷贝到另一台机器 2.11. 降级MySQL 2.12. 具体操作系统相关的注意事项 2.12.1. Linux注意事项 2.12.2. Mac OS X注意事项 2.12.3. Solaris注意事项 2.12.4. BSD注意事项 2.12.5. 其它Unix...
2.10.3. 将MySQL数据库拷贝到另一台机器 2.11. 降级MySQL 2.12. 具体操作系统相关的注意事项 2.12.1. Linux注意事项 2.12.2. Mac OS X注意事项 2.12.3. Solaris注意事项 2.12.4. BSD注意事项 2.12.5. 其它Unix注意...
将MySQL数据库拷贝到另一台机器 2.11. 降级MySQL 2.12. 具体操作系统相关的注意事项 2.12.1. Linux注意事项 2.12.2. Mac OS X注意事项 2.12.3. Solaris注意事项 2.12.4. BSD注意事项 2.12.5. ...
2.10.3. 将MySQL数据库拷贝到另一台机器 2.11. 降级MySQL 2.12. 具体操作系统相关的注意事项 2.12.1. Linux注意事项 2.12.2. Mac OS X注意事项 2.12.3. Solaris注意事项 2.12.4. BSD注意事项 2.12.5. 其它...
文章目录推荐阅读Explain查询执行计划作用使用方法分析包含信息idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra EXPLAIN 显示了 MySQL 如何使用索引来...MySQL高级性能优化知识,这些面
MySQL是目前流行的关系型数据库管理系统,在WEB应用方面 MySQL 也是目前好的 RDBMS 应用软件之一。随着淘宝去IOE(去除IBM小型机、Oracle数据库及EMC存储设备)化的推进,MySQL 数据库在当前的互联网应用中变得越来越...
全新的MySQL视频教程,课程对MySQL的核心知识点进行了大量深入的展开,细致的讲解和总结 较以往的MySQL核心基础课程有更好的优势,是MySQL运维人员 编程人员及相关技术人员的必备学习课程 ├─10、课程:进阶...