Mysql
MySQL 5.6 中的頻繁損壞索引。這是一個 MySQL 錯誤嗎？
我在使用 INNODB 損壞的 mysql 索引時遇到問題。最初我通過重建表來解決它，但現在表太大而無法重建，我厭倦了 mysql 沒有顯示一致的結果。
我每天在 4 或 5 個表中插入近 10000 條記錄，並且我有一些工作不斷更新這些記錄上的數據（可能每天更新 100000 條）
這是 MySQL 中的錯誤嗎？你會如何解決這個問題？
在以下語句中，您可以看到差異，因為具有 3 個條件的行顯示 3433 個結果，如果我刪除一個條件，我會得到 0 個結果。我使用 UNION 來表明查詢幾乎是同時完成的，因此記錄實際上不太可能在查詢之間消失並在之後立即返回，因為當我重複查詢時，我得到了相同的結果。
mysql> SELECT count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7 and tipo_id > 0 UNION SELECT count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7; +----------+ | count(*) | +----------+ | 3433 | | 0 | +----------+ 2 rows in set (0.16 sec)這是mysql版本…順便說一句，我正在使用INNODB。
mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 5.6.33-0ubuntu0.14.04.1 | +-------------------------+ 1 row in set (0.00 sec)只是為了澄清。UNION 只是為了暴露行沒有從一個語句更改為另一個的事實，但是如果在單獨的語句中執行查詢，結果是相同的：
mysql> SELECT count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7 and tipo_id > 0; +----------+ | count(*) | +----------+ | 3436 | +----------+ 1 row in set (0.55 sec) mysql> SELECT count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.06 sec)更多資訊：
mysql> EXPLAIN SELECT count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7 and tipo_id > 0; +----+-------------+--------------+-------------+------------------------------+----------------------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------------+------------------------------+----------------------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | licitaciones | index_merge | entidad_id,tipo_id,estado_id | entidad_id,estado_id | 5,4 | NULL | 3557 | Using intersect(entidad_id,estado_id); Using where | +----+-------------+--------------+-------------+------------------------------+----------------------+---------+------+------+----------------------------------------------------+ mysql> EXPLAIN SELECT count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7; +----+-------------+--------------+-------------+----------------------+----------------------+---------+------+------+-----------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------------+----------------------+----------------------+---------+------+------+-----------------------------------------------------------------+ | 1 | SIMPLE | licitaciones | index_merge | entidad_id,estado_id | entidad_id,estado_id | 5,4 | NULL | 3557 | Using intersect(entidad_id,estado_id); Using where; Using index | +----+-------------+--------------+-------------+----------------------+----------------------+---------+------+------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table licitaciones; +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | licitaciones | CREATE TABLE `licitaciones` ( `id` int(11) NOT NULL AUTO_INCREMENT, `detected` datetime DEFAULT CURRENT_TIMESTAMP, `num_constancia` varchar(60) DEFAULT NULL, `url` varchar(255) DEFAULT NULL, `fila` int(11) DEFAULT NULL, `num_proceso` varchar(255) DEFAULT NULL, `tipo_id` tinyint(4) DEFAULT NULL, `estado_id` mediumint(9) DEFAULT NULL, `entidad_id` int(11) DEFAULT NULL, `objeto` longtext, `cuantia` bigint(20) DEFAULT NULL, `tipo_fecha_id` smallint(6) DEFAULT NULL, `fecha` date DEFAULT NULL, `extra_updated` date DEFAULT NULL, `extra_json` mediumtext, `fastcodes` text, PRIMARY KEY (`id`), UNIQUE KEY `num_constancia` (`num_constancia`), KEY `entidad_id` (`entidad_id`), KEY `tipo_id` (`tipo_id`), KEY `estado_id` (`estado_id`), KEY `tipo_fecha_id` (`tipo_fecha_id`), KEY `fecha` (`fecha`), KEY `cuantia` (`cuantia`), KEY `num_proceso` (`num_proceso`), KEY `extra_updated` (`extra_updated`), KEY `detected` (`detected`), FULLTEXT KEY `fulltextidx` (`objeto`,`fastcodes`,`extra_json`) ) ENGINE=InnoDB AUTO_INCREMENT=32050336 DEFAULT CHARSET=latin1 | +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
里克詹姆斯的回答是對的，我只是無法添加評論。
我們遇到了一個非常相似的問題，特殊條件的查詢找不到記錄，但是該記錄在其他where條件下。問題是“index_merge”，它通過避免“index_merge_intersection”來解決：
SET SESSION optimizer_switch='index_merge_intersection=off';要麼
SET GLOBAL optimizer_switch='index_merge=off';它還可以通過添加或刪除一些索引來避免“index_merge”。
索引沒有損壞。
沒有任何東西可以控制行的順序。事實上，如果這兩個計數相同，您將只得到一行輸出——因為
UNION意味著UNION DISTINCT.以這種方式進行查詢：
SELECT "with tipo" As the_case, count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7 and tipo_id > 0 UNION DISTINCT SELECT "without", count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7;更好的是，同時做這兩個
SELECT：SELECT count(*) AS "without tipo test", SUM(tipo_id > 0) AS "with tipo test" FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7;