`
lzj0470
  • 浏览: 1274008 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

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,文件排序是好是坏需要仔细判断,说白了就是看是先过滤再排序划算,还是先排序再过滤划算,正确答案取决与数据分布的情况
分享到:
评论
1 楼 chensiyu04 2012-05-19  
你好。。有关于java 通过关键字搜索视频的问题需要请教。。麻烦你加下QQ

qq:103594501

相关推荐

    mysqlexplain.ppt

    以下是对EXPLAIN命令及其相关知识点的详细解释。 1. **EXPLAIN调用方式**: - **基础形式**:`EXPLAIN SELECT ...` - **EXTENDED形式**:`EXPLAIN EXTENDED SELECT ...`,此形式会显示出经过MySQL优化器优化后的...

    Mysql Explain 详解.txt

    `Explain`是MySQL提供的一种用于分析SQL执行计划的工具,通过它,我们可以查看数据库如何执行SQL语句,从而帮助我们识别潜在的性能瓶颈并进行相应的优化。`Explain`命令可以被添加到任何`SELECT`语句之前,以便分析...

    MySQL必备知识手册

    MySQL是世界上最受欢迎的关系型数据库管理系统之一,广泛应用于各种规模的企业和项目中。这份"MySQL必备知识手册"将帮助你深入理解和掌握MySQL的核心概念、语法和最佳实践。 首先,我们需要了解MySQL的基本架构。...

    mysql基础知识 1.zip

    本压缩包“mysql基础知识 1.zip”显然包含了关于MySQL的基础教程或资料,旨在帮助初学者理解并掌握MySQL的基本概念、操作和使用。 1. **MySQL概述** MySQL是一个快速、可靠且可移植的SQL数据库服务器,它由瑞典的...

    MySQL笔记,比较全,知识点都有,而且有例子

    MySQL是世界上最受欢迎的关系型数据库管理系统之一,用于存储和管理数据。这份"MySQL笔记"涵盖了其主要知识点,并且每个概念都配有实例,...配合实践,这些知识点将帮助你成为一位熟练的MySQL数据库管理员或开发者。

    mysql高级视频教程百度云(2019).txt

    61.MySQL高级_如何锁定一行.avi 60.MySQL高级_间隙锁危害.avi 59.MySQL高级_索引失效行锁变表锁.avi 58.MySQL高级_行锁演示答疑补充.avi 57.MySQL高级_行锁案例讲解.avi 56.MySQL高级_行锁理论.avi 55....

    mysql基础知识详解,完整ppt

    1. **MySQL简介**:MySQL是一个开源、免费的数据库系统,它支持多种操作系统,具有高可扩展性和高性能。PPT可能会介绍MySQL的发展历程、版本更新以及与其他数据库系统的对比。 2. **SQL语言基础**:SQL(结构化查询...

    MySQL面试知识点.docx

    MySQL 是一个广泛使用的关系型数据库管理系统,本文总结了 MySQL 面试中常见的知识点,涵盖了 MySQL 优化、MySQL 引擎、索引、锁等方面的内容,旨在帮助开发者更好地理解和掌握 MySQL 的相关知识。 一、MySQL 优化 ...

    最详细的MySQL知识笔记.pdf

    1. 数据类型:MySQL支持多种数据类型,如整数类型(INT、TINYINT等)、浮点数类型(FLOAT、DOUBLE)、字符串类型(VARCHAR、CHAR)和日期时间类型(DATE、TIMESTAMP)等。 2. 常用函数:MySQL提供了大量的内置函数...

    mysql基础知识2.zip

    1. **数据库和表的概念**:在MySQL中,数据库是一个组织数据的系统,而表是存储具体数据的结构,类似于电子表格。每个表都有列(字段)和行(记录),列定义了数据类型,行则包含实际的数据。 2. **SQL语言**:SQL...

    深入解析:使用EXPLAIN优化MySQL查询之旅

    在MySQL中,`EXPLAIN`是一个极其重要的工具,它能帮助开发者深入了解查询的执行计划,识别并解决性能瓶颈。接下来我们将详细介绍如何利用`EXPLAIN`来优化MySQL查询。 ##### 1. EXPLAIN的基本用法 `EXPLAIN`命令...

    2021年MySQL高级教程视频.rar

    18.MySQL高级锁InnoDB行锁介绍及背景知识.avi 18.MySQL高级锁InnoDB行锁类型.avi 19.MySQL高级锁InnoDB行锁基本演示.avi 20.MySQL高级锁InnoDB行锁行锁升级为表锁.avi 21.MySQL高级锁InnoDB行锁间隙锁危害.avi 22....

    尚硅谷Java视频教程_MySQL高级视频

    尚硅谷_MySQL高级_性能分析前提知识 · 21.尚硅谷_MySQL高级_explain使用简介 · 22.尚硅谷_MySQL高级_explain之id介绍 · 23.尚硅谷_MySQL高级_explain之select_type和table介绍 · 24.尚硅谷_MySQL高级_...

    MySQL8.0参考手册.pdf

    1. MySQL 简介 MySQL 是一个开源的关系型数据库管理系统(RDBMS),可以在多种操作系统平台上运行,包括 Windows、Linux、Unix 等。MySQL 具有高性能、可扩展性强、支持多种编程语言等特点,使其广泛应用于 Web ...

    mysql lunix的安装

    这个是MySQL5.5再contos6下的安装方法 MySQL 是目前最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 也是目前最好的 RDBMS 应用软件之一。随着淘宝去IOE(去除IBM小型机、Oracle数据库及EMC存储设备)化的推进,...

    学习mysql的基础知识,高级部分,连续的思维导图,也适合总结mysql的知识.zip

    MySQL是一种广泛使用的开源关系型数据库管理系统(RDBMS),它以其高效、稳定和易于管理的特点在Web开发领域占据了重要地位。这份压缩包包含了学习MySQL基础知识和高级部分的资源,通过连续的思维导图来帮助学习者...

    MySQL相关知识学习,包括《高性能 MySQL》。.zip

    以上只是MySQL庞大知识体系的一部分。《高性能MySQL》这本书涵盖了更深入的话题,如性能监控、备份策略、高级复制技术、存储优化以及如何处理大数据。对于任何希望提升MySQL技能的人来说,这本书都是宝贵的资源。

    mysql源码(mysql-8.2.0.tar.gz)

    MySQL 8.2.0的源码分析将涉及多个关键知识点: 1. SQL解析与执行:MySQL的核心在于其SQL解析器,它将用户输入的SQL语句转换成可执行的计划。这部分源码揭示了如何解析各种SQL语句,包括SELECT、INSERT、UPDATE、...

Global site tag (gtag.js) - Google Analytics