mysql order by file,MySQL单表SELECT查询ORDER BY导致FILESORT

我浏览了多个类似的帖子,试图获得有关如何重新定义索引的意见,但无法弄清楚.每当我包含ORDER BY语句时,它都会使用filesort返回结果集.

这是表的定义和查询：

SELECT

`s`.`title`,

`s`.`price`,

`s`.`price_sale`

FROM `style` `s`

WHERE `s`.`isactive`=1 AND `s`.`department`='women'

ORDER

BY `s`.`ctime` DESC

CREATE TABLE IF NOT EXISTS `style` (

`id` mediumint(6) unsigned NOT NULL auto_increment,

`ctime` timestamp NOT NULL default CURRENT_TIMESTAMP,

`department` char(5) NOT NULL,

`isactive` tinyint(1) unsigned NOT NULL,

`price` float(8,2) unsigned NOT NULL,

`price_sale` float(8,2) unsigned NOT NULL,

`title` varchar(200) NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_grid_default` (`isactive`,`department`,`ctime`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=47 ;

另外,这是我得到的解释结果集：

+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------------+

| 1 | SIMPLE | s | ref | idx_grid | idx_grid | 6 | const,const | 3 | Using where; Using filesort |

+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------------+

解决方法:

为什么s.isactive不用作索引？

如果MySQL(或其他任何SQL)的基数较低,则不会使用密钥.

用简单的英语来说,如果许多行共享一个键的相同值,则(My)SQL将不使用索引,而只使用真实表.

因此,布尔字段几乎永远不会被选作索引.太多的行共享相同的值.

为什么MySQL在ctime上不使用索引？

ctime包含在多字段索引或复合索引中. MySQL仅在使用全部索引或最左侧部分时才使用复合索引*)

如果您在组合索引的中间或最右边的字段上进行排序,则MySQL无法使用该索引,因此必须诉诸于文件排序.

因此,按isactive的订单,部门将使用索引；

按部门订购不会.

order by isactive也不会使用索引,但这是因为布尔字段isactive的基数太低.

*)有一些例外,但这涵盖了97％的情况.

标签：sql-order-by,filesort,mysql,sorting

来源： https://codeday.me/bug/20191102/1991967.html