mysql 千万条记录查询_如何使用超过两千万条记录来优化mysql查询

我在项目中使用MySQL。并且在“ mixpanel_data”表中有超过2000万条记录。因此，当我尝试获取最近6个月的记录时，它就中断了请求。仅证明我仅持续5-10天的记录

我正在使用以下MySQL查询。

SELECT `sb_users`. `id`,`sb_users`. `name`, SUM(`mixpanel_data`.duration) as timeCount, COUNT(`mixpanel_data`.spread_id) as PageCount,`mixpanel_data`.`language`,`mixpanel_data`.`created_at`, `mixpanel_data`.`book_name`, `mixpanel_data`.`email`, `mixpanel_data`.`ip_address`, `mixpanel_data`.`event_date`, `mixpanel_data`.`type`,'Read', `mixpanel_data`.`unique_session_id`, `mixpanel_data`.`operating_system`, `mixpanel_data`.`country`, `mixpanel_data`.`region`, `mixpanel_data`.`city`, `mixpanel_data`.`device`, `mixpanel_data`.`browser`, `mixpanel_data`.`browser_version`

FROM `mixpanel_data`

LEFT JOIN `sb_users` ON `mixpanel_data`.`first_name` = `sb_users`. `username`

WHERE `mixpanel_data`.`email` !='' AND `mixpanel_data`.`created_at` Between '2019-03-24' AND '2020-03-24' and `mixpanel_data`.`action` IN('PauseAudio')

GROUP BY `mixpanel_data`.`email`, `mixpanel_data`.`book_name` , `mixpanel_data`.`language`

UNION

SELECT `sb_users`. `id`,`sb_users`. `name`, SUM(`mixpanel_data`.duration) as timeCount, COUNT(`mixpanel_data`.spread_id) as PageCount,`mixpanel_data`.`language`,`mixpanel_data`.`created_at`, `mixpanel_data`.`book_name`, `mixpanel_data`.`email`, `mixpanel_data`.`ip_address`, `mixpanel_data`.`event_date`, `mixpanel_data`.`type`,'Read', `mixpanel_data`.`unique_session_id`, `mixpanel_data`.`operating_system`, `mixpanel_data`.`country`, `mixpanel_data`.`region`, `mixpanel_data`.`city`, `mixpanel_data`.`device`, `mixpanel_data`.`browser`, `mixpanel_data`.`browser_version`

FROM `mixpanel_data`

LEFT JOIN `sb_users` ON `mixpanel_data`.`first_name` = `sb_users`. `username` WHERE `mixpanel_data`.`email` !='' AND `mixpanel_data`.`created_at` Between '2019-03-24' AND '2020-03-24' and `mixpanel_data`.`action` NOT IN('PlayAudio','PauseAudio') AND `mixpanel_data`.`spread_id` !=''

GROUP BY `mixpanel_data`.`email`, `mixpanel_data`.`book_name` , `mixpanel_data`.`language`

我尝试使用以下查询更改查询，但不适用于我。它也中断了请求，并且给了我更少的记录。

SELECT sb_users.id,

sb_users.NAME,

Count(mixpanel_data.spread_id) AS PageCount,

SUM(CASE When action IN ('PauseAudio') Then duration Else 0 End) as total, SUM(CASE When action NOT IN ('PlayAudio', 'PauseAudio') Then duration Else 0 End) as Sectotal,

mixpanel_data.language,

mixpanel_data.created_at,

mixpanel_data.book_name,

mixpanel_data.email,

mixpanel_data.ip_address,

mixpanel_data.event_date,

mixpanel_data.type,

'Read',

mixpanel_data.unique_session_id,

mixpanel_data.operating_system,

mixpanel_data.country,

mixpanel_data.region,

mixpanel_data.city,

mixpanel_data.device,

mixpanel_data.browser,

mixpanel_data.browser_version

FROM `mixpanel_data`

LEFT JOIN sb_users

ON `mixpanel_data`.`first_name` = `sb_users`. `username`

WHERE

mixpanel_data.email != '' AND mixpanel_data.`created_at` Between '2019-03-24' AND '2020-03-24'

AND `mixpanel_data`.`spread_id` !='' GROUP BY mixpanel_data.email,

mixpanel_data.book_name,

mixpanel_data.language

我也尝试在first_name，username和created_at列上添加索引器。但是查询需要很多时间(超过15-16秒)

有人可以帮我优化查询吗？