MySQL查询优化的方式

本篇内容介绍了"MySQL查询优化的方式"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

在分析查询性能时，考虑EXPLAIN关键字同样很管用。EXPLAIN关键字一般放在SELECT查询语句的前面，用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数。explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们查询,让查询优化器能够更好的工作。

一、MySQL 查询优化器是如何工作的
MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。最终目标是提交 SELECT 语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快,那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试,查询就可以执行地更快。
EXPLAIN 的每个输出行提供一个表的相关信息,并且每个行包括下面的列:

extra 中出现以下 2 项意味着 MYSQL 根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

下面来举一个例子来说明下 explain 的用法。
先来一张表:

复制代码 代码如下:

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'),
(1, 1, 3, 3, '3', '3');

需求:
查询 category_id 为 1 且 comments 大于 1 的情况下,views 最多的 article_id。
先查查试试看:

复制代码 代码如下:

EXPLAIN
SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1\G

看看部分输出结果:

复制代码 代码如下:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: article
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where; Using filesort
1 row in set (0.00 sec)

很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

嗯,那么最简单的解决方案就是加索引了。好,我们来试一试。查询的条件里即 where 之后共使用了 category_id,comments,views 三个字段。那么来一个联合索引是最简单的了。

复制代码 代码如下:

ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` );

结果有了一定好转,但仍然很糟糕:

复制代码 代码如下:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: article
type: range
possible_keys: x
key: x
key_len: 8
ref: NULL
rows: 1
Extra: Using where; Using filesort
1 row in set (0.00 sec)

type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。但是我们已经建立了索引,为啥没用呢?这是因为按照 BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。当 comments 字段在联合索引里处于中间位置时,因comments > 1 条件是一个范围值(所谓 range),MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
那么我们需要抛弃 comments,删除旧索引:

复制代码 代码如下:

DROP INDEX x ON article;

然后建立新索引:

复制代码 代码如下:

ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;

接着再运行查询:

复制代码 代码如下:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: article
type: ref
possible_keys: y
key: y
key_len: 4
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。
再来看一个多表查询的例子。
首先定义 3个表 class 和 room。

复制代码 代码如下:

CREATE TABLE IF NOT EXISTS `class` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`bookid`)
);
CREATE TABLE IF NOT EXISTS `phone` (
`phoneid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`phoneid`)
) engine = innodb;

然后再分别插入大量数据。插入数据的php脚本：

复制代码 代码如下: