常见SQL优化案例（一）

冷不防 2022-12-05 09:56 215阅读 0赞

### \--------------------目录直通车------------------------- ###

*   *  1、新建表
     *  2、查询案例

## 1、新建表 ##

CREATE TABLE IF NOT EXISTS `article` (
    `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY 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
    );
     
    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');
     
    SELECT * FROM article;

## 2、查询案例 ##

**使用上表，完成查询 category\_id 为1 且 comments 大于 1 的情况下,views 最多的文章。**

SELECT * FROM article 
    WHERE category_id=1 AND comments>1 
    ORDER BY views DESC LIMIT 1;

尝试运行一下：  
![SQL查询结果][SQL]  
 我们的SQL写得好不好，那还得由MySQL的优化器说了算，通过MySQL提供的 EXPLAIN 来获取当前的SQL信息。

EXPLAIN 
    SELECT * FROM article 
    WHERE category_id=1 AND comments>1 
    ORDER BY views DESC LIMIT 1;

分析结果如下：  
![未建立索引SQL分析结果][SQL 1]  
 type 值为 ALL 的意思是这条 SQL 进行了全表扫描，key 值为NULL 表示没有使用索引，Using filesort 的意思是进行了文件排序表示当前这条 sql 写得不好。

到这里，正确的思路是建立索引来让 Using filesort 消失。一般初学者建立索引的思路是从前往后，按照最左原则建立索引，也就是说包含 category\_id、comments、views 三个字段。但是，在我们执行 sql 的时候如果进行范围查询（type 值为 range）的话会使索引失效。我们先建立包含这三个字段的索引，再次查询：

![建立索引后查询分析][20200909165040992.png_pic_center]  
 仍然出现 Using filesort，此时 type 的值为 range，表示进行了范围查询导致索引失效。最终，将comments 从索引中删去，重新建立索引只包含 category\_id、views 字段，即可解决问题。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ3OTk5_size_16_color_FFFFFF_t_70_pic_center]

[SQL]: /images/20221123/535ee41ace6c4098aa8ac94bacb02983.png
[SQL 1]: /images/20221123/dad3f2b752aa4a58a7936d872fba13cf.png
[20200909165040992.png_pic_center]: /images/20221123/1468ffc6a27c42599d2ef90442a97835.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ3OTk5_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/54d908f5acb648ad8be3eaf187d3d7f9.png