[MySQL高级](三) 索引分析

灰太狼 2022-01-29 03:33 379阅读 0赞

# 1. 单表 #

## 1.1 建表SQL ##

> CREATE TABLE \`article\` (  
>   \`id\` int(10) NOT NULL,  
>   \`author\_id\` int(10) DEFAULT NULL,  
>   \`category\_id\` int(10) DEFAULT NULL,  
>   \`views\` int(10) DEFAULT NULL,  
>   \`comments\` int(10) DEFAULT NULL,  
>   \`title\` varchar(255) DEFAULT NULL,  
>   \`content\` text,  
>   PRIMARY KEY (\`id\`)  
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> 
> INSERT INTO \`test\`.\`article\` (\`id\`, \`author\_id\`, \`category\_id\`, \`views\`, \`comments\`, \`title\`, \`content\`) VALUES ('1', '1', '1', '1', '1', '1', '1');  
> INSERT INTO \`test\`.\`article\` (\`id\`, \`author\_id\`, \`category\_id\`, \`views\`, \`comments\`, \`title\`, \`content\`) VALUES ('2', '2', '2', '2', '2', '2', '2');  
> INSERT INTO \`test\`.\`article\` (\`id\`, \`author\_id\`, \`category\_id\`, \`views\`, \`comments\`, \`title\`, \`content\`) VALUES ('3', '3', '3', '3', '3', '3', '3');  
> INSERT INTO \`test\`.\`article\` (\`id\`, \`author\_id\`, \`category\_id\`, \`views\`, \`comments\`, \`title\`, \`content\`) VALUES ('4', '4', '4', '4', '4', '4', '4');

## 1.2 案例分析 ##

**查询category\_id为1且comments大于1的情况下，views最多的article\_id**

> EXPLAIN SELECT  
>     id,  
>     author\_id  
> FROM  
>     article  
> WHERE  
>     category\_id = 1  
> AND comments > 1  
> ORDER BY  
>     views DESC  
> LIMIT 1;

![70][]

**➤ 结论：**很显然，type是ALL，即最坏的情况。Extra里还出现了Using filesort，也是最坏的情况。优化是必须的。

## 1.3 开始优化 ##

### 1.3.1 创建索引 idx\_article\_ccv ###

首先，我们根据where后面要查询的字段建立复合索引，即category\_id、comments、views。

> CREATE INDEX idx\_article\_ccv ON article (category\_id, comments, views);

那么我们再来执行一下，看看测试结果：

![70 1][]

**✈ 分析：**根据测试结果，key的值为index\_article\_ccv，说明该索引已经被使用，而且type变成了range，这是可以忍受的，但是Extra中的Using filesort仍然在，这是无法接受的。但是，我们已经建立了索引，为啥没用呢？这是因为按照BTree索引的工作原理，先排序category\_id,如果遇到相同的category\_id则再排序comments，如果遇到相同的comments则再排序views。当comments字段在联合索引里处于中间位置时，因comments>1条件是一个范围值（所谓range），MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

那么我们看下面这条SQL的执行情况。

> EXPLAIN SELECT  
>     id,  
>     author\_id  
> FROM  
>     article  
> WHERE  
>     category\_id = 1  
> AND comments = 1   -- 注意这里改为了常量  
> ORDER BY  
>     views DESC  
> LIMIT 1;

![70 2][]

我们发现，当comments字段的查询条件改为常量1的时候，没有了Using filesort。这说明使用 > 这种范围查询会使后面的索引失效。

### 1.3.2 创建索引 idx\_article\_cv ###

删除刚刚建立的索引

> DROP INDEX idx\_article\_ccv ON article

建立category\_id、views索引 idx\_article\_cv

> CREATE INDEX idx\_article\_cv ON article (category\_id, views);

我们再次执行查看执行结果：

![70 3][]

**✈ 分析：**可以看到，type变为了ref,Extra中的Using filesort也消失了，结果非常理想。

# 2. 单表 #

## 2.1 建表SQL ##

> CREATE TABLE \`book\` (  
>   \`id\` int(11) NOT NULL,  
>   \`card\` varchar(255) DEFAULT NULL,  
>   PRIMARY KEY (\`id\`)  
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> 
> CREATE TABLE \`class\` (  
>   \`id\` int(11) NOT NULL,  
>   \`card\` varchar(255) DEFAULT NULL,  
>   PRIMARY KEY (\`id\`)  
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

## 2.2 案例分析 ##

**查询两表中card相等的数据**

> EXPLAIN SELECT  
>     \*  
> FROM  
>     class  
> LEFT JOIN book ON class.card = book.card

![70 4][]

**➤ 结论：**type为ALL

## 2.3 开始优化 ##

### 2.3.1 创建索引 idx\_book\_card ###

这里创建索引有两种方式，我们来看哪种方式更加合适，那么我们先在book表中创建索引idx\_book\_card

> ALTER TABLE book ADD INDEX idx\_book\_card(card)

那么我们再来执行一下，看看测试结果：

![70 5][]

### 2.3.2 创建索引 idx\_class\_card ###

删除book表的索引，然后创建class表的索引idx\_class\_card

> DROP INDEX idx\_book\_card ON book;   
> ALTER TABLE class ADD INDEX idx\_class\_card(card)

那么我们再来执行一下，看看测试结果：

![70 6][]

**✈ 分析：**我们之前的博客说过，ref 要好于 index，所以经过实践证明，使用book表的idx\_book\_card索引更加高效。这是由左连接特性决定的，left join条件用于确定如何从右表搜索行，左边一定都有，所以右表是我们的关键点，一定需要建立索引。需要注意的是，如果条件不允许，只能在左表存在索引，那么我们可以使用右连接，交换两表位置，达到相同的效果。

# 3. 三表 #

## 3.1 建表SQL ##

在上面的基础上，再创建一个表，SQL语句如下：

> CREATE TABLE \`phone\` (  
>   \`id\` int(11) NOT NULL,  
>   \`card\` varchar(255) DEFAULT NULL,  
>   PRIMARY KEY (\`id\`)  
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

## 3.2 案例分析 ##

查询三表中card相等的数据

> EXPLAIN SELECT  
>     \*  
> FROM  
>     class  
> LEFT JOIN book ON class.card = book.card  
> LEFT JOIN phone ON book.card = phone.card

**➤ 结论：**type为ALL

## 2.3 开始优化 ##

### 2.3.1 创建索引 idx\_book\_card 和 idx\_phone\_card ###

> ALTER TABLE phone ADD INDEX idx\_phone\_card(card)  
> ALTER TABLE book ADD INDEX idx\_book\_card(card)

优化后的执行结果为：

![70 7][]

**✈ 分析：**type都是ref且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。

**总结：** JOIN语句的优化

*  尽可能减少join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。
 *  优先优化NestedLoop的内层循环
 *  保证join语句中被驱动表上Join条件字段已经被索引
 *  当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置。

本文原文地址：[https://blog.csdn.net/why15732625998/article/details/80395712][https_blog.csdn.net_why15732625998_article_details_80395712]

[70]: /images/20220129/869dffa868e0416a95a460db6ec80a47.png
[70 1]: /images/20220129/8f918da74d4c43db9839d8b170e9a2e5.png
[70 2]: /images/20220129/d34ec903721046bbb62448d0860bafc5.png
[70 3]: /images/20220129/3f492d09a50a4522b023495b48658062.png
[70 4]: /images/20220129/07cdfed6e71d4d3d8a26d6a202dedc3f.png
[70 5]: /images/20220129/c1fd5ea6f5d748398c2ccdb8b170fc04.png
[70 6]: /images/20220129/5724c7c9fd2246f1b4a186c937ec7f45.png
[70 7]: /images/20220129/473b7a30fe7c4b858d72ba99802ce78e.png
[https_blog.csdn.net_why15732625998_article_details_80395712]: https://blog.csdn.net/why15732625998/article/details/80395712