mysql - 清除 MYSQL 慢查询日志 - Rails

Question

以下抛出慢查询日志。

APN::Notification.
    select('apn_notifications.*, devices.device_uid').
    joins('INNER JOIN apn_devices ON (apn_notifications.device_id = apn_devices.id) INNER JOIN devices ON (apn_devices.device_id = devices.id)').
    where(['apn_notifications.sent_at IS NULL AND apn_notifications.badge > 0 AND devices.customer_id = ? AND devices.device_type IN (?)', customer.id, Object::Device.platform_device_types('ios')])

解释的输出

EXPLAIN for: SELECT apn_notifications.*, devices.device_uid FROM `apn_notifications` INNER JOIN apn_devices ON (apn_notifications.device_id = apn_devices.id) INNER JOIN devices ON (apn_devices.device_id = devices.id) WHERE (apn_notifications.disabled_at IS NULL) AND (apn_notifications.sent_at IS NULL AND apn_notifications.badge > 0 AND devices.customer_id = 1 AND devices.device_type IN ('iphone4','ipad','iphone3'))

“show create table apn_notifications”的输出

| apn_notifications | CREATE TABLE `apn_notifications` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`device_id` int(11) DEFAULT NULL,
`errors_nb` int(11) DEFAULT NULL,
`device_language` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`sound` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`alert` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`badge` int(11) DEFAULT NULL,
`custom_properties` text COLLATE utf8_unicode_ci,
`sent_at` datetime DEFAULT NULL,
`disabled_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_apn_notifications_on_device_id` (`device_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12984412 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

apn_notifications 表有 150 万条记录。因此，如果我尝试添加索引，则需要更长的时间。从慢速查询中删除此问题的最佳方法是什么？

同样从Mysql 5.6开始，添加索引不会导致任何停机。我说得对吗？

Answer 1

通常“复合”索引更好:

apn_notifications: INDEX(device_id, sent_at, badge)
apn_notifications: INDEX(sent_at, badge)
devices: INDEX(customer_id, device_type)

apn_devices 是多对多映射吗？如果是这样，请检查它是否遵循指南 here .