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消失了。
