Оптимизировать запросы SQL на большом столе ⇐ Php

[Anonymous]

Я работаю с очень большой таблицей - в настоящее время мне поручено читать журналы всех устройств в базу данных и запуск выборов для выполнения метрик. Текущие определения таблицы следующие: < /p>
mysql> describe device_events;
+-------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+-------------------+-----------------------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| device_type | varchar(255) | NO | MUL | NULL | |
| device_id | bigint(20) unsigned | NO | MUL | NULL | |
| message | json | NO | | NULL | |
| source | text | NO | MUL | NULL | |
| created_at | timestamp | NO | MUL | CURRENT_TIMESTAMP | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| file_date | date | YES | MUL | NULL | |
+-------------+---------------------+------+-----+-------------------+-----------------------------+```

Indexes:
+---------------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| device_events | 0 | PRIMARY | 1 | id | A | 40932772 | NULL | NULL | | BTREE | | |
| device_events | 1 | device_events_device_id_index | 1 | device_id | A | 44021 | NULL | NULL | | BTREE | | |
| device_events | 1 | device_events_device_type_index | 1 | device_type | A | 621 | NULL | NULL | | BTREE | | |
| device_events | 1 | device_events_source_index | 1 | source | A | 3085 | 255 | NULL | | BTREE | | |
| device_events | 1 | device_events_created_at_index | 1 | created_at | A | 2846551 | NULL | NULL | | BTREE | | |
| device_events | 1 | device_events_file_date_index | 1 | file_date | A | 25017 | NULL | NULL | YES | BTREE | | |
+---------------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
< /code>
Я изучал разделение и в идеале хотел бы создать раздел для каждого устройства и каждого источника журнала, но не думаю, что я могу сделать это из -за фиксированных диапазонов разделения MySQL. В настоящее время выбранные выбирают около минуты и занимают около 15 минут, чтобы генерировать все метрики, но хотел бы ускорить это. Есть ли у кого -нибудь другие идеи о том, как оптимизировать крупные выборы? Я ожидаю, что в базе данных будет более триллион записей. Большинство выборов будут сделаны на событиях за последние 30 дней и используют json_extract в сообщении . Обратите внимание, что я уже использую между временем, чтобы избежать расчета месяца (created_at), и, скорее всего, оптимизировал запросы как можно больше - я в первую очередь ищу оптимизацию структуры в отношении этого вопроса. < /P>
--
-- Table structure for table `device_events`
--

DROP TABLE IF EXISTS `device_events`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `device_events` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`device_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`device_id` bigint(20) unsigned NOT NULL,
`message` json NOT NULL,
`source` text COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`file_date` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `device_events_device_id_index` (`device_id`),
KEY `device_events_device_type_index` (`device_type`),
KEY `device_events_source_index` (`source`(255)),
KEY `device_events_created_at_index` (`created_at`),
KEY `device_events_file_date_index` (`file_date`)
) ENGINE=InnoDB AUTO_INCREMENT=42771939 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-04-19 17:55:35
< /code>
Elostrent orm Query < /p>

public function scopeLastMonth($query) {
$lastMonth = Carbon::now()->subMonth();
$start = $lastMonth->firstOfMonth()->startOfDay()->toDateTimeString();
$end = $lastMonth->lastOfMonth()->endOfDay()->toDateTimeString();
return $query->whereBetween("created_at", [$start, $end]);
}

$topIdentitiesQuery = (clone $lastMonthEvents)->selectRaw("JSON_EXTRACT(message, '$.policy_identity') as identity")->selectRaw("count(*) as aggregate")->groupBy("identity")->orderBy("aggregate", "desc");
$topIdentities = [];
foreach($topIdentitiesQuery->take(self::NUM_TOP_IDENTITIES)->get() as $topIdentity) {
array_push($topIdentities, $topIdentity->identity);
}
$topIdentities = array_pad($topIdentities, self::NUM_TOP_IDENTITIES, "");


Подробнее здесь: https://stackoverflow.com/questions/671 ... -big-table