mysql大表查询分组_mysql – 如何优化查询的执行计划,使用多个外连接到大表,分组和按顺序排序？...

我有以下数据库(简化)：

CREATE TABLE `tracking` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`manufacture` varchar(100) NOT NULL,

`date_last_activity` datetime NOT NULL,

`date_created` datetime NOT NULL,

`date_updated` datetime NOT NULL,

PRIMARY KEY (`id`),

KEY `manufacture` (`manufacture`),

KEY `manufacture_date_last_activity` (`manufacture`, `date_last_activity`),

KEY `date_last_activity` (`date_last_activity`),

) ENGINE=InnoDB AUTO_INCREMENT=401353 DEFAULT CHARSET=utf8

CREATE TABLE `tracking_items` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`tracking_id` int(11) NOT NULL,

`tracking_object_id` varchar(100) NOT NULL,

`tracking_type` int(11) NOT NULL COMMENT 'Its used to specify the type of each item, e.g. car, bike, etc',

`date_created` datetime NOT NULL,

`date_updated` datetime NOT NULL,

PRIMARY KEY (`id`),

KEY `tracking_id` (`tracking_id`),

KEY `tracking_object_id` (`tracking_object_id`),

KEY `tracking_id_tracking_object_id` (`tracking_id`,`tracking_object_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1299995 DEFAULT CHARSET=utf8

CREATE TABLE `cars` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`car_id` varchar(255) NOT NULL COMMENT 'It must be VARCHAR, because the data is coming from external source.',

`manufacture` varchar(255) NOT NULL,

`car_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,

`date_order` datetime NOT NULL,

`date_created` datetime NOT NULL,

`date_updated` datetime NOT NULL,

`deleted` tinyint(4) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

UNIQUE KEY `car_id` (`car_id`),

KEY `sort_field` (`date_order`)

) ENGINE=InnoDB AUTO_INCREMENT=150000025 DEFAULT CHARSET=utf8

这是我的“有问题”查询,运行速度非常慢.

SELECT sql_no_cache `t`.*,

count(`t`.`id`) AS `cnt_filtered_items`

FROM `tracking` AS `t`

INNER JOIN `tracking_items` AS `ti` ON (`ti`.`tracking_id` = `t`.`id`)

LEFT JOIN `cars` AS `c` ON (`c`.`car_id` = `ti`.`tracking_object_id`

AND `ti`.`tracking_type` = 1)

LEFT JOIN `bikes` AS `b` ON (`b`.`bike_id` = `ti`.`tracking_object_id`

AND `ti`.`tracking_type` = 2)

LEFT JOIN `trucks` AS `tr` ON (`tr`.`truck_id` = `ti`.`tracking_object_id`

AND `ti`.`tracking_type` = 3)

WHERE (`t`.`manufacture` IN('1256703406078',

'9600048390403',

'1533405067830'))

AND (`c`.`car_text` LIKE '%europe%'

OR `b`.`bike_text` LIKE '%europe%'

OR `tr`.`truck_text` LIKE '%europe%')

GROUP BY `t`.`id`

ORDER BY `t`.`date_last_activity` ASC,

`t`.`id` ASC

LIMIT 15

这是以上查询的EXPLAIN的结果：

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

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

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

| 1 | SIMPLE | t | index | PRIMARY,manufacture,manufacture_date_last_activity,date_last_activity | PRIMARY | 4 | NULL | 400,000 | Using where; Using temporary; Using filesort |

| 1 | SIMPLE | ti | ref | tracking_id,tracking_object_id,tracking_id_tracking_object_id | tracking_id | 4 | table.t.id | 1 | NULL |

| 1 | SIMPLE | c | eq_ref | car_id | car_id | 767 | table.ti.tracking_object_id | 1 | Using where |

| 1 | SIMPLE | b | eq_ref | bike_id | bike_id | 767 | table.ti.tracking_object_id | 1 | Using where |

| 1 | SIMPLE | t | eq_ref | truck_id | truck_id | 767 | table.ti.tracking_object_id | 1 | Using where |

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

这个查询试图解决的问题是什么？

基本上,我需要找到跟踪表中可能与tracking_items(1：n)中的记录相关联的所有记录,其中tracking_items中的每个记录可能与左连接表中的记录相关联.过滤标准是查询中的关键部分.

我上面的查询有什么问题？

如果有order by和group by子句,则查询运行速度非常慢,例如完成上述配置需要10-15秒.但是,如果我省略这些子句中的任何一个,查询运行得非常快(~0.2秒).

我已经尝试过了什么？

>我试图使用FULLTEXT索引,但它没有多大帮助,因为LIKE statemenet评估的结果被JOIN使用索引缩小.

>我试图使用WHERE EXISTS(…)来查找左连接表中是否有记录,但遗憾的是没有运气.

关于这些表之间关系的几点注释：

tracking -> tracking_items (1:n)

tracking_items -> cars (1:1)

tracking_items -> bikes (1:1)

tracking_items -> trucks (1:1)

所以,我正在寻找一种优化该查询的方法.