mysql order by 索引问题

MySQL 是一款非常优秀的数据库软件,它有着出色的性能,但是如果使用不好的话,也可能会导致查询效率缓慢,笔者以工作中遇到的一个案例为由,讲解如何优化和创建索引。

案例

两个表如下:

CREATE TABLE `cmf_portal_category_post` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `post_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '文章id',
    `category_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '分类id',
    `list_order` FLOAT(12) NOT NULL DEFAULT '10000' COMMENT '排序',
    `status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT '状态,1:发布;0:不发布',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `term_taxonomy_id` (`category_id`) USING BTREE,
    INDEX `index_category_id` (`category_id`) USING BTREE,
    INDEX `index_post_id` (`post_id`) USING BTREE
)
COMMENT='portal应用 分类文章对应表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
CREATE TABLE `cmf_portal_post` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `parent_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '父级id',
    `post_type` TINYINT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT '类型,1:文章;2:页面',
    `post_format` TINYINT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT '内容格式;1:html;2:md',
    `user_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '发表者用户id',
    `post_status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT '状态;1:已发布;0:未发布;',
    `comment_status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT '评论状态;1:允许;0:不允许',
    `is_top` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否置顶;1:置顶;0:不置顶',
    `recommended` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否推荐;1:推荐;0:不推荐',
    `post_hits` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '查看数',
    `post_favorites` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '收藏数',
    `post_like` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '点赞数',
    `comment_count` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '评论数',
    `create_time` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '创建时间',
    `update_time` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '更新时间',
    `published_time` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '发布时间',
    `delete_time` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '删除时间',
    `post_title` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'post标题' COLLATE 'utf8_unicode_ci',
    `post_keywords` VARCHAR(150) NOT NULL DEFAULT '' COMMENT 'seo keywords' COLLATE 'utf8_unicode_ci',
    `post_excerpt` VARCHAR(500) NOT NULL DEFAULT '' COMMENT 'post摘要' COLLATE 'utf8_unicode_ci',
    `post_source` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '转载文章的来源' COLLATE 'utf8_unicode_ci',
    `thumbnail` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '缩略图' COLLATE 'utf8_unicode_ci',
    `post_content` TEXT(65535) NULL DEFAULT NULL COMMENT '文章内容' COLLATE 'utf8_unicode_ci',
    `post_content_filtered` TEXT(65535) NULL DEFAULT NULL COMMENT '处理过的文章内容' COLLATE 'utf8_unicode_ci',
    `more` TEXT(65535) NULL DEFAULT NULL COMMENT '扩展属性,如缩略图;格式为json' COLLATE 'utf8_unicode_ci',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `type_status_date` (`post_type`, `post_status`, `create_time`, `id`) USING BTREE,
    INDEX `parent_id` (`parent_id`) USING BTREE,
    INDEX `user_id` (`user_id`) USING BTREE,
    INDEX `create_time` (`create_time`) USING BTREE,
    INDEX `index_create_time` (`create_time`) USING BTREE,
    INDEX `index_update_time` (`update_time`) USING BTREE,
    INDEX `index_published_time` (`published_time`) USING BTREE,
    INDEX `index_post_status` (`post_status`) USING BTREE,
    INDEX `index_post_type` (`post_type`) USING BTREE,
    INDEX `index_delete_time` (`delete_time`) USING BTREE
)
COMMENT='portal应用 文章表'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=1
;

从上面的表结构可以看出,cmf_portal_post表没有保存category_id,因此查询条件中含有category_id字段的时候,就需要关联cmf_portal_category_post表进行关联查询,下面我们给出一个查询语句。

explain SELECT post.* FROM `cmf_portal_post` `post` left JOIN `cmf_portal_category_post` `category_post` ON `post`.`id`=`category_post`.`post_id` WHERE  `post`.`post_status` = 1 AND `post`.`post_type` = 1 AND `post`.`delete_time` = 0 AND `category_post`.`category_id` IN ('18','30','31','32','34') AND ( `post`.`is_top` = 0 )  ORDER BY `post`.`published_time` DESC LIMIT 3;

查询结果如下:

cmf_portal_post

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE post index_merge PRIMARY,type_status_date,index_post_status,index_post_type,index_delete_time,id_published_time index_post_status,index_post_type,index_delete_time 1,1,4 \N 2993 Using intersect(index_post_status,index_post_type,index_delete_time); Using where; Using filesort
1 SIMPLE category_post ref term_taxonomy_id,index_category_id,index_post_id index_post_id 8 furniture_80shih.post.id 1 Using where

我们从结果中可以看出,查询post的时候,使用了Using filesort,也就是使用了文件排序,我们知道文件排序是操作硬盘的,因此效率非常低,笔者在只有3万多条数据的时候,查询执行时间竟然用了0.7秒,这是非常高的时间消耗。为什么我们明明在published_time创建了索引,但是没有起作用呢?

这是因为order by的原理决定的。

因为我们查询的结果字段不仅仅包含published_time字段,还包含其它字段,因此单独的索引查询之后,我们还需要返回回表查询其它字段。

更据查询字段,我们可以建立联合索引

create INDEX `index_news` (`post_type`, `post_status`, `published_time`, `delete_time`, `post_title`, `post_excerpt`(255)) 

创建这样的索引之后,我们再执行sql语句,就会发现结果如下:

cmf_portal_post

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE post range PRIMARY,type_status_date,index_post_status,index_post_type,index_delete_time,index_news index_news 2 \N 11975 Using index condition; Using where
1 SIMPLE category_post ref term_taxonomy_id,index_category_id,index_post_id index_post_id 8 furniture_80shih.post.id 1 Using where

从结果中可以看到经过这样的修改,using filesort已经不见了。

索引类型

mysql 有多种索引类型,下面我们简单介绍一下。

  • 聚集索引,顾名思义,索引是聚集的,就是在物理结构上是连续的,类似于我们使用汉语字典通过拼音查询。mysql中的主键就是聚集索引,当然它也是唯一的。

  • 非聚集索引,物理结果上是不连续的,类似于我们使用汉语字典通过偏旁部首进行查询。

  • 联合索引,就是多个字段索引,如上面那个案例,当我们返回的字段需要一次返回多个的时候,我们可以根据查询字段创建联合索引。联合索引需要注意的就是where查询时候的条件顺序。比如我们建立了一个name,age,sex的联合索引,那么下面的语句有些是不会使用索引的。

select name,age,sex from test where name = '扎根';-- 会使用索引
select name,age,sex from test where name ='tom' and age = 16; --会使用索引
select name,age,sex from test where age = 16;--不会使用索引
select name,age,sec from test where age = 15 and sex = 1; -- 不会使用索引

从上面的规律我们可以看出,要想使用联合索引,必须从最左侧开始匹配,我们把这个规则叫做最左前缀原则。

  • 全文索引,也叫做倒排索引,也就是我们搜索引擎常用的存储。它存储的是单词在数据库中的位置,和根据内容找单词不同的是,它是根据单词找内容,是相反的,因此叫做倒排。

索引本质

有序的文件查询就会非常快,而乱序的文件查询就会慢,而索引本质就是对文件进行整理排序,当文件有序了,我们查询的时候效率自然就高了。因此排序算法为什么很多公司面试都会问,因为它们不仅基础,而且太重要了。