Добрый день. Такая проблема, есть БД (размер около 1044 ГБ) забикса. Чтобы не бекапить регулярно террабайт данных, сделана реплика, которая регуляррно стопится и снимается снапшот всей ВМ где реплика крутится.

Проблема в том, что иногда, после некоторых бекапов, оптимизатор перестает использовать индекс item_discovery_1 на таблице item_discovery и почему то начинает использовать индекс PRIMARY который вообще тут не при чем.
+----------------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| item_discovery | 0 | PRIMARY | 1 | itemdiscoveryid | A | 2189577 | NULL | NULL | | BTREE | | |
| item_discovery | 0 | item_discovery_1 | 1 | itemid | A | 2189577 | NULL | NULL | | BTREE | | |
| item_discovery | 0 | item_discovery_1 | 2 | parent_itemid | A | 2189577 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | item_discovery_2 | 1 | parent_itemid | A | 2189577 | NULL | NULL | | BTREE | | |
+----------------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

сответственно запросы вида update item_discovery set lastcheck=1596021740 where itemid between 3304480 and 3304487;
начинают выполняться долго, и начинает расти лаг репликации.
Причем, если создать таблицу новую, по образу и подобию item_discovery перелить туда данные и переименовать новую в старую, а старую убрать, то проблема исчезает, оптимизатор тут же начинает использовать правильный индекс.
Воспроизвести проблему не получается, сколько я не ребутал субд и с остановой слейва, и без остановки, после старта все прекрасно работает, но иногда после ночных остановок происходит такая фигня с индексами и приходится пересоздавать таблицу.
Она хоть и не большая, всего 300мб, но хотелось бы найти корневую причину, почему оптимизатор вдруг именно на этой таблице иногда сбивается.
сама таблица такая
SHOW COLUMNS FROM zabbix_nohist.item_discovery;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| itemdiscoveryid | bigint(20) unsigned | NO | PRI | NULL | |
| itemid | bigint(20) unsigned | NO | MUL | NULL | |
| parent_itemid | bigint(20) unsigned | NO | MUL | NULL | |
| key_ | varchar(255) | NO | | | |
| lastcheck | int(11) | NO | | 0 | |
| ts_delete | int(11) | NO | | 0 | |
+-----------------+---------------------+------+-----+---------+-------+

Akina, согласно документации, FORCE INDEX вставляется в тело запроса :( забикс вряд ли обрадуется попыткам что то в нем переписывать.

Теоретически, если проблема в устаревшей статистике, то при появлении проблемы, после выполнения команды ANALYZE TABLE zabbix_nohist.item_discovery; оптимизатор должен увидеть правильный индекс?

Повторилась ошибка
Запрос такой же - update item_discovery set lastcheck=1596021740 where itemid between 3304480 and 3304487;


analyze table не помог, пробовал и при работающем слейве, и погашенном и после рестарта. Все равно индекс используется PRIMARY

analyze update item_discovery set lastcheck=1596021740 where itemid between 3304480 and 3304487;
+------+-------------+----------------+-------+------------------+---------+---------+------+------+------------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+----------------+-------+------------------+---------+---------+------+------+------------+----------+------------+-------------+
| 1 | SIMPLE | item_discovery | index | item_discovery_1 | PRIMARY | 8 | NULL | 1 | 2960155.00 | 100.00 | 0.00 | Using where |
+------+-------------+----------------+-------+------------------+---------+---------+------+------+------------+----------+------------+-------------+

после пересоздания:
DROP TABLE item_discovery_backup;
CREATE TABLE new_item_discovery LIKE item_discovery;
INSERT INTO new_item_discovery SELECT * FROM item_discovery;
RENAME TABLE item_discovery TO item_discovery_backup;
RENAME TABLE new_item_discovery TO item_discovery;

все нормально становится:
analyze update item_discovery set lastcheck=1596021740 where itemid between 3304480 and 3304487;
+------+-------------+----------------+-------+------------------+------------------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+----------------+-------+------------------+------------------+---------+------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | item_discovery | range | item_discovery_1 | item_discovery_1 | 8 | NULL | 8 | 8.00 | 100.00 | 100.00 | Using where |
+------+-------------+----------------+-------+------------------+------------------+---------+------+------+--------+----------+------------+-------------+


SHOW CREATE TABLE item_discovery\G
*************************** 1. row ***************************
Table: item_discovery
Create Table: CREATE TABLE `item_discovery` (
`itemdiscoveryid` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`parent_itemid` bigint(20) unsigned NOT NULL,
`key_` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`lastcheck` int(11) NOT NULL DEFAULT '0',
`ts_delete` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`itemdiscoveryid`),
UNIQUE KEY `item_discovery_1` (`itemid`,`parent_itemid`),
KEY `item_discovery_2` (`parent_itemid`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

Можно как то использовать force index без вмешательства в код приложения?

Всем спасибо вылечено сменой формата бин логов на ROW