• mysql 单表索引优化


    建表语句

    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');
    

    查询索引

    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)
    
    
    • Table
    • Non_unique: 非唯一索引(0为唯一索引, 1 为非唯一索引)
    • Key_name: 表示索引的名称
    • Seq_in_index: 表示该字段在索引中的位置,单列索引改值该值为1,组合索引为每个字段在索引中定义的顺序
    • Column_name: 列名
    • Collation: 字符序的规则
    • Cardinality: 基数
    • Sub_part: 表示索引的长度
    • Packed: 是否创建压缩的索引(1, 0 与 Default)
    • Null: 表示该字段是否能为空值
    • Index_type: 表示索引类型
    • Comment: 注释
    • Index_comment: 索引注释

    用explain查询sql

    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)
    
    
    • 可以看到type为all(全表查询), Extra中有Using filesort(使用了外部索引排序, 而不是按照表内索引顺序进行读取)。
    • 需要进行优化, 不然数据量大后就比较危险。

    创建复合索引

    mysql> create index idx_article_ccv on article(category_id,comments,views);
    Query OK, 0 rows affected (0.01 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         |           2 |     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      |         |               |
      +---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      4 rows in set (0.00 sec)
      
      
    • 再次使用explain查询sql

      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从all变成了range, 但是依旧走的外部索引排序, 而不是按照表内索引顺序进行读取。
      • 索引失效的原理:
        • 使用的是B+树
        • 先排序 category_id
        • 如果遇到相同的 category_id 则再排序 comments, 如果遇到相同的 comments 则再排序 views。
        • 当 comments 字段在联合索引里处于中间位置时, 因comments > 1 条件是一个范围值(所谓range)
        • MySQL 无法利用索引再对后面的 views 部分进行检索, 即range 类型查询字段后面的索引无效。

    删除原先索引, 构建新索引

    • 删除索引
    drop index idx_article_ccv on article;
    
    • 创建新索引(避开了有范围条件的comments, 从而不会造成索引失效)
    create index idx_article_cv on article(category_id, views);
    
    • 再次查看索引

      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_cv |            1 | category_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
      | article |          1 | idx_article_cv |            2 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
      +---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      3 rows in set (0.00 sec)
      
    • 再次使用explain查看sql语句

      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_cv | idx_article_cv | 4       | const |    2 |    33.33 | Using where |
      +----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
      1 row in set, 1 warning (0.00 sec)
      
      
      • 可以看到type变为了ref, extra中的file sorting消失了。
  • 相关阅读:
    三阶幻方
    夺冠概率
    2013年5月5号蓝桥杯画图
    回型嵌套
    Modelsim6.5g SE
    Modelsim存波形文件
    Modelsim仿真时用Hex格式显示数据的方法
    Modelsim仿真时的Debug命令:$display和$monitor
    Modelsim报错(一)
    【转】为什么有的LDO的输出输入必须用陶瓷电容 ,而有的却规定必须用钽电容?
  • 原文地址:https://www.cnblogs.com/ronnieyuan/p/12160291.html
Copyright © 2020-2023  润新知