索引单表优化实战

傷城~ 2022-09-11 15:17 189阅读 0赞

# 一 脚本 #

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'),
    (3,3,3,3,'3','3');
    SELECT * FROM ARTICLE;
    mysql> SELECT * FROM ARTICLE;
    +----+-----------+-------------+-------+----------+-------+---------+
    | id | author_id | category_id | views | comments | title | content |
    +----+-----------+-------------+-------+----------+-------+---------+
    |  1 |         1 |           1 |     1 |        1 | 1     | 1       |
    |  2 |         2 |           2 |     2 |        2 | 2     | 2       |
    |  3 |         3 |           3 |     3 |        3 | 3     | 3       |
    +----+-----------+-------------+-------+----------+-------+---------+
    3 rows in set (0.00 sec)

# 二 实战 #

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

mysql> explain SELECT id,author_id from article where category_id = 1 and COMMENTS >1 ORDER BY VIEWS DESC LIMIT 1;
    +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
    | id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
    +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
    |  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using filesort |
    +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
    1 row in set, 1 warning (0.00 sec)
    mysql> show index from article;
    +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | article |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
    +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    1 row in set (0.00 sec)

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

2 开始优化，建立索引

mysql> CREATE index idx_article_ccv on article(category_id,COMMENTS,VIEWS);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql> show index from article;
    +---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | article |          0 | PRIMARY         |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
    | article |          1 | idx_article_ccv |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
    | article |          1 | idx_article_ccv |            2 | comments    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
    | article |          1 | idx_article_ccv |            3 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
    +---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

性能分析结果

mysql> explain SELECT id,author_id from article where category_id = 1 and COMMENTS >1 ORDER BY VIEWS DESC LIMIT 1;
    +----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
    | id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                 |
    +----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
    |  1 | SIMPLE      | article | NULL       | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    1 |   100.00 | Using index condition; Using filesort |
    +----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
    1 row in set, 1 warning (0.00 sec)

产生了文件内排序，说明索引对排序失效了。

type 变成了 range，这是可以忍受的。但是 extra 里使用了Using filesort 仍是无法接受的。

我们建立的索引失效了，这是因为按照 BTree索引的工作原理，先排序 category\_id，如果遇到相同的 category\_id，再排序 COMMENTS，如果遇到相同的 COMMENTS，再排序 VIEWS。

当 COMMENTS 字段在联合索引里处于中间位置时，因为 COMMENTS >1 条件是一个范围值（rang），MySQL 无法利用索引再对后面的 views 部分进行检索，即 range 类型查询字段后面的索引无效。

3 继续优化，解决 filesort 问题

mysql> drop index idx_article_ccv on article;
    Query OK, 0 rows affected (0.00 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql> CREATE index idx_article_cc on article(category_id,VIEWS);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql> show index from article;
    +---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | article |          0 | PRIMARY        |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
    | article |          1 | idx_article_cc |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
    | article |          1 | idx_article_cc |            2 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
    +---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    3 rows in set (0.00 sec)
    mysql> explain SELECT id,author_id from article where category_id = 1 and COMMENTS >1 ORDER BY VIEWS DESC LIMIT 1;
    +----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
    | id | select_type | table   | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
    +----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
    |  1 | SIMPLE      | article | NULL       | ref  | idx_article_cc | idx_article_cc | 4       | const |    1 |    33.33 | Using where |
    +----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

可以看到，type 变成了 ref，Extra  中的 Using filesort 也消失了，结果很好。

发表评论取消回复

表情：

评论列表（有 0 条评论，189人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL索引优化实战

mark：今天同事提到了索引的优化，自己感觉挺模糊，去网上找到一篇挺不错的文章，记录一下~ 原文：[http://www.mamicode.com/info-detail-2

我会带着你远行/ 2022年10月26日 14:15/ 0 赞/ 235 阅读

相关索引优化实战二

一不要在索引列上做任何计算不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描。二实战等号左边

短命女/ 2022年09月12日 02:53/ 0 赞/ 235 阅读

相关索引优化实战一

一防止索引失效口诀 ![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaX

╰半橙微兮°/ 2022年09月12日 02:53/ 0 赞/ 252 阅读

相关索引三表优化实战

一脚本 CREATE TABLE IF NOT EXISTS `phone`( `phoneid` INT(10) UNSIGNED NOT NULL

ゞ浴缸里的玫瑰/ 2022年09月12日 00:54/ 0 赞/ 213 阅读

相关索引两表优化实战

一脚本 CREATE TABLE IF NOT EXISTS `class`( `id` INT(10) UNSIGNED NOT NULL PRI

た入场券/ 2022年09月11日 15:18/ 0 赞/ 182 阅读

相关索引单表优化实战

一脚本 CREATE TABLE IF NOT EXISTS `article`( `id` INT(10) UNSIGNED NOT NULL PRI

傷城~/ 2022年09月11日 15:17/ 0 赞/ 190 阅读

相关索引优化实战七

一 like 的前后模糊匹配 like 以通配符开头（'%abc...'），mysql 索引失效会变成全表扫描的操作。二实战 % 作为 like 的

水深无声/ 2022年08月28日 13:46/ 0 赞/ 238 阅读

相关 SQL-索引（二）- 单表建立索引以及优化

单表建立索引 1. 创建表 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cH

今天药忘吃喽~/ 2022年02月03日 10:57/ 0 赞/ 355 阅读

相关 MySQL索引优化（索引单表优化案例）

1、单表查询优化建表SQL CREATE TABLE IF NOT EXISTS `article` ( `id` INT(10) UNSIGNED NO

淡淡的烟草味﹌/ 2021年11月24日 01:34/ 0 赞/ 527 阅读

相关 MySQL--索引单表优化案例

索引单表优化建表及插入数据： CREATE TABLE IF NOT EXISTS `article`(文章表 `id` INT(10) UNSIGN

短命女/ 2021年08月13日 21:31/ 0 赞/ 614 阅读