创建表
/*
Navicat MySQL Data Transfer
Source Server : CentOS6.7--Clone
Source Server Version : 50554
Source Host : 192.168.1.179:3306
Source Database : db01
Target Server Type : MYSQL
Target Server Version : 50554
File Encoding : 65001
Date: 2017-12-15 14:58:26
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `article`
-- ----------------------------
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varchar(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of article
-- ----------------------------
INSERT INTO `article` VALUES ('1', '1', '1', '1', '1', '1', '1');
INSERT INTO `article` VALUES ('2', '2', '2', '2', '2', '2', '2');
INSERT INTO `article` VALUES ('3', '1', '1', '3', '3', '3', '3');
查询开始
查询category为1且comments大于1的情况下,views最多的article_id
mysql> EXPLAIN SELECT id,author_id from article where category = 1 and comments > 1 ORDER BY views DESC limit 0,1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | article | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
# type为All,且Extra中出现了Using filesort,待优化
建立索引,进行优化
图片.png
# 查看该表索引
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 | 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 | | |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.03 sec)
# 再次解析sql语句
mysql> EXPLAIN SELECT id,author_id from article where category = 1 and comments > 1 ORDER BY views DESC limit 0,1;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | article | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 1 | Using where; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
#type由All变成range,但Extra中出现了Using filesort
#已经建立索引,为什么仍然出现Using filesort
# 按照BTree索引的工作原理
1 先排序category,如果遇到相同的category则再排序comments,如遇到相同的comments则再排序views
2 当comments字段在联合索引的中间位置,因为comments>1条件是一个范围值(range),MySQL无法利用索引在对后面的views部分进行检索,即range类型查询字段后面的索引无效
修改索引,继续优化
图片.png
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 | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_cv | 2 | views | A | 3 | NULL | NULL | | BTREE | | |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> EXPLAIN SELECT id,author_id from article where category = 1 and comments > 1 ORDER BY views DESC limit 0,1;
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| 1 | SIMPLE | article | ref | idx_article_cv | idx_article_cv | 4 | const | 2 | Using where |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
# 优化完成
网友评论