從 MySQL 5.1 遷移到 MariaDB 10.4 後出現複雜查詢的問題

我完成了從 MySQL 5.1 主從系統到 MariaDB 10.4 Galera Cluster 的遷移。自從每個文件在 InnoDB 上以來，所有被複製的數據庫都執行良好。然而，一些複雜的查詢在新系統上非常慢，因為它們完全沒用而且我有點失落……我通過強制索引修復了其中一些，但我不知道如何修復像這樣的其他查詢。 ..

這是查詢：

SELECT * FROM (
   SELECT * FROM abvalue WHERE deviceid='XXX'
) AS abvalue LEFT JOIN (
   SELECT * FROM abperson WHERE deviceid='XXX'
) AS abperson ON abvalue.person_id=abperson.person_id

（我以這種方式進行查詢，因為這是當時最有效的方式）

這個查詢在舊系統上有點慢（大約 2 秒），但它完成了。但是在新系統中，我在 60 秒後放棄了……我嘗試強制索引，但我無法修復它。

這是 MySQL 5.1 的解釋

+----+-------------+------------+------+----------------+----------------+---------+------+-------+-------------+
| id | select_type | table      | type | possible_keys  | key            | key_len | ref  | rows  | Extra       |
+----+-------------+------------+------+----------------+----------------+---------+------+-------+-------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL           | NULL           | NULL    | NULL | 12428 |             |
|  1 | PRIMARY     | <derived3> | ALL  | NULL           | NULL           | NULL    | NULL |   694 |             |
|  3 | DERIVED     | abperson   | ref  | deviceid_index | deviceid_index | 36      |      |   693 | Using where |
|  2 | DERIVED     | abvalue    | ref  | deviceid_index | deviceid_index | 36      |      | 16484 | Using where |
+----+-------------+------------+------+----------------+----------------+---------+------+-------+-------------+

這是 MariaDB 10.4 的解釋

+------+-------------+----------+------------+-----------------------------+-----------------------------+---------+--------------------------+---------+---------------------------------+
| id   | select_type | table    | type       | possible_keys               | key                         | key_len | ref                      | rows    | Extra                           |
+------+-------------+----------+------------+-----------------------------+-----------------------------+---------+--------------------------+---------+---------------------------------+
|    1 | SIMPLE      | abvalue  | ref        | deviceid_index              | deviceid_index              | 36      | const                    | 22780   | Using where                     |
|    1 | SIMPLE      | abperson | ref|filter | deviceid_index,person_index | person_index|deviceid_index | 5|36    | ownspy.abvalue.person_id | 39 (0%) | Using where; Using rowid filter |
+------+-------------+----------+------------+-----------------------------+-----------------------------+---------+--------------------------+---------+---------------------------------+

這是表的結構：

CREATE TABLE `abvalue` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `deviceid` char(32) NOT NULL,
 `value` char(128) DEFAULT NULL,
 `type` int(11) DEFAULT NULL,
 `person_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `deviceid_index` (`deviceid`(12)) USING BTREE,
 KEY `person_index` (`person_id`) USING BTREE,
 KEY `value_index` (`value`(5)) USING BTREE
) ENGINE=InnoDB

CREATE TABLE `abperson` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `deviceid` char(32) NOT NULL,
 `first` text DEFAULT NULL,
 `last` text DEFAULT NULL,
 `person_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `deviceid_index` (`deviceid`(12)) USING BTREE,
 KEY `person_index` (`person_id`) USING BTREE
) ENGINE=InnoDB

任何幫助都非常受歡迎！

我建議擺脫設備和人員的單列索引，並將它們替換為：

INDEX device_person_index (deviceid, person_id)

如果您有關於 person_id 而不是 deviceid 的謂詞的查詢，您可以為此添加另一個索引：

INDEX device_person_index (person_id, deviceid)

上述建議適用於兩個表。我發現 KEY 在這種情況下非常混亂，所以我用我認為是同義詞的 INDEX 替換它。

我不知道 MySQL/Mariadb 是否可以進行索引與運算，但是複合索引無論如何比兩個單獨的索引更有效。

• 不要使用索引前綴；它通常比索引整個列效率低。
• 不需要時不要使用子查詢。優化器必須站在它的頭上，並且沒有好的技術。 優化器可能做出了與您的特定查詢分崩離析的改進。
• CHAR 僅用於真正固定長度的數據。改為使用VARCHAR。

 SELECT *
     FROM      abvalue  WHERE deviceid='XXX'
     LEFT JOIN abperson WHERE deviceid='XXX'
           ON  abvalue.person_id =
              abperson.person_id

• 在每張桌子上： INDEX(deviceid, person_id)

並擺脫

   KEY `deviceid_index` (`deviceid`(12)) USING BTREE,

• 如果first和last是名稱，TEXT可能是矯枉過正。它會減慢查詢速度。切換到VARCHAR具有實際最大長度的 a。
• 從36vsdeviceid(12)中，我推斷兩個版本都在使用CHARSET utf8. 我敢打賭你的設備 ID 只有 ascii 字元。如果是這樣，請在適當的情況下更改該列和任何其他列的字元集。這將提高速度和空間。

我的建議可能同時適用於 5.1 和 10.4。

引用自：https://dba.stackexchange.com/questions/269519