Mysql SQL优化实战之 EXPLAIN篇

曾经终败给现在 2022-11-03 04:16 260阅读 0赞

**目录**

EXPLAIN

一.单表优化

1.首先创建一个单表

2.插入数据

3.根据条件写sql

4.使用EXPLAIN分析SQL

5.开始优化

6.优化完成

--------------------

# EXPLAIN #

**ps:本篇博客记录sql优化实战 explain 相关理论知识在其他文章中总结，一些关键字或者知识点就不一一讲解了。**

## 一.单表优化 ##

### 1.首先创建一个单表  ###

CREATE TABLE `article` (
      `id` int(10) NOT NULL,
      `author_id` int(10) DEFAULT NULL COMMENT '作者ID',
      `category_id` int(10) DEFAULT NULL COMMENT '分组ID',
      `views` varchar(10) DEFAULT NULL COMMENT '浏览次数',
      `comments` varchar(10) DEFAULT NULL COMMENT '备注',
      `title` varchar(255) DEFAULT NULL COMMENT '标题',
      `content` varchar(1000) DEFAULT NULL COMMENT '正文',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

### 2.插入数据 ###

INSERT INTO `explain`.`article` (`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES ('1', '1', '1', '1', '1', '1', '1');
    INSERT INTO `explain`.`article` (`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES ('2', '2', '2', '2', '2', '2', '2');
    INSERT INTO `explain`.`article` (`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES ('3', '3', '3', '3', '3', '3', '3');
    INSERT INTO `explain`.`article` (`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES ('4', '4', '4', '4', '4', '4', '4');
    INSERT INTO `explain`.`article` (`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES ('5', '5', '5', '5', '5', '5', '5');

### 3.根据条件写sql ###

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

SELECT a.author_id FROM `article` a WHERE a.category_id=2 AND a.comments >1 ORDER BY a.views LIMIT 1;

### 4.使用EXPLAIN分析SQL ###

![2021022616322957.png][]

**1.没有使用索引，全表查询 最坏结果 2.Usingfilesort  需要优化 具体原因可看之前博客**

### **5.开始优化 ** ###

**1.创建索引**

上图用到了三个字段，创建一个组合索引包含这三个字段

![20210226164044746.png][]

**2.查看索引结构**

![20210226164142121.png][]

**3.再次查看**

![2021022616424486.png][]

分析：索引是使用到了，全表扫描处理好了，但是Using filesort 未解决，但是由于comments 是范围查询 导致索引失效，举例：三个字段为一二三楼，一楼正常使用，二楼无法使用，导致排序无法使用索引内部排序，使用了文件排序。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70][]

**4.索引删除**

由于创建的索引未能解决Extra中的Using filesort，该索引需要删除重新创建

![20210226170114839.png][]

查看索引结构

![20210226170141138.png][]

**5.创建新索引**

由于ccv索引无法解决 假设跳过中间范围查询 创建cv索引

![20210226170851623.png][]

### 6.优化完成 ###

![20210226171024166.png][]

![20210226171112338.png][]

[2021022616322957.png]: /images/20221024/4378585b1fe9485da022daa721c79250.png
[20210226164044746.png]: /images/20221024/7508e2bfb6514172bc9ad9fae31cc104.png
[20210226164142121.png]: /images/20221024/cb6399d7636b45daad5784a2a1b1df3c.png
[2021022616424486.png]: /images/20221024/ebea1188a8bd458cad80257e8fcc3e19.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xpdVk1MjE_size_16_color_FFFFFF_t_70]: /images/20221024/1c7340d9367b4893890f70c34729ddb2.png
[20210226170114839.png]: /images/20221024/06a0dcf2623f47f7825243a77e2c7327.png
[20210226170141138.png]: /images/20221024/c229668b335e4570b93d2ea5d0257cb1.png
[20210226170851623.png]: /images/20221024/05e33f0623f74876a862efef514666fc.png
[20210226171024166.png]: /images/20221024/8ca17afc476c47a5957dc969ffdade5b.png
[20210226171112338.png]: /images/20221024/b95a560d11cf4cb587417bcb3a8f67c9.png