我知道有很多关于Order By的问题,也有各种各样的解决方案,但是我想要的是针对我下面设置的数据结构的具体问题。
我已经设置了以下2个表:
表设置
CREATE TABLE `record` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`file_group_id` INT(11) NOT NULL,
`status_id` INT(11) NOT NULL,
`title` VARCHAR(3000) NOT NULL,
`date_created` DATETIME NOT NULL,
`user_created` INT(11) NOT NULL,
`publish_date` DATETIME NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_RECORD_FGID` (`file_group_id`)
);
CREATE TABLE `record_meta_text` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`record_id` INT(11) NOT NULL,
`column_id` INT(11) NOT NULL,
`value` VARCHAR(3000) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IDX_RMT_VALUE` (`value`(800)),
KEY `IDX_RMT_COL_ID` (`column_id`),
KEY `IDX_RMT_RECORD_ID_COL_ID` (`record_id`,`column_id`,`value`(800))
)
Query without Order By
SELECT
r.title AS col_title,
rmt_2780.value AS col_2780,
rmt_2781.value AS col_2781,
rmt_18474.value AS col_18474
FROM
record r
INNER JOIN record_meta_text AS rmt_2780 ON rmt_2780.record_id = r.id AND rmt_2780.column_id = 2780
INNER JOIN record_meta_text AS rmt_2781 ON rmt_2781.record_id = r.id AND rmt_2781.column_id = 2781
INNER JOIN record_meta_text AS rmt_18474 ON rmt_18474.record_id = r.id AND rmt_18474.column_id = 18474
WHERE
r.file_group_id = 2350
AND r.status_id = 1
LIMIT 0, 50
输出和解释
执行时间:0.004秒
这是EXPLAIN输出:
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- --------- ------ --------------------------------------- ------------------------ ------- ---------------------------- ------ -------------
1 SIMPLE r ref PRIMARY,IDX_RECORD_FGID IDX_RECORD_FGID 4 const 527895 Using where
1 SIMPLE rmt_18474 ref IDX_RMT_COL_ID,IDX_RMT_RECORD_ID_COL_ID IDX_RMT_RECORD_ID_COL_ID 8 file_cabinet_data.r.id,const 1 (NULL)
1 SIMPLE rmt_2780 ref IDX_RMT_COL_ID,IDX_RMT_RECORD_ID_COL_ID IDX_RMT_RECORD_ID_COL_ID 8 file_cabinet_data.r.id,const 1 (NULL)
1 SIMPLE rmt_2781 ref IDX_RMT_COL_ID,IDX_RMT_RECORD_ID_COL_ID IDX_RMT_RECORD_ID_COL_ID 8 file_cabinet_data.r.id,const 1 (NULL)
按顺序查询
SELECT
r.title AS col_title,
rmt_2780.value AS col_2780,
rmt_2781.value AS col_2781,
rmt_18474.value AS col_18474
FROM
record r
INNER JOIN record_meta_text AS rmt_2780 ON rmt_2780.record_id = r.id AND rmt_2780.column_id = 2780
INNER JOIN record_meta_text AS rmt_2781 ON rmt_2781.record_id = r.id AND rmt_2781.column_id = 2781
INNER JOIN record_meta_text AS rmt_18474 ON rmt_18474.record_id = r.id AND rmt_18474.column_id = 18474
WHERE
r.file_group_id = 2350
AND r.status_id = 1
ORDER BY col_2780
LIMIT 0, 50
输出和解释
执行时间:35.237秒
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- --------- ------ --------------------------------------- ------------------------ ------- ---------------------------- ------ ----------------------------------------------
1 SIMPLE r ref PRIMARY,IDX_RECORD_FGID IDX_RECORD_FGID 4 const 527895 Using where; Using temporary; Using filesort
1 SIMPLE rmt_2780 ref IDX_RMT_COL_ID,IDX_RMT_RECORD_ID_COL_ID IDX_RMT_RECORD_ID_COL_ID 8 file_cabinet_data.r.id,const 1 (NULL)
1 SIMPLE rmt_2781 ref IDX_RMT_COL_ID,IDX_RMT_RECORD_ID_COL_ID IDX_RMT_RECORD_ID_COL_ID 8 file_cabinet_data.r.id,const 1 (NULL)
1 SIMPLE rmt_18474 ref IDX_RMT_COL_ID,IDX_RMT_RECORD_ID_COL_ID IDX_RMT_RECORD_ID_COL_ID 8 file_cabinet_data.r.id,const 1 (NULL)
问题和解决方案
我的问题是,如何使这个order by不要花费太长时间。在多种情况下,我将有多个order by条件,以及where语句。LIMIT / OFFSET用于分页。
目前record
表有1,139,119条记录。
目前file_meta_text
表有7,584,428条记录。
我有一个带有基本模式和查询的SQLFiddle,但没有添加数据(记录太多):
http://sqlfiddle.com/#!2/6ffc3/1
任何帮助都将不胜感激。