Mysql

從 MySQL 到 MariaDB：MyISAM 表的性能不佳問題

  • January 28, 2020

我目前正在將 Debian 6 MySQL 伺服器 (5.1.73) 遷移到全新的 Debian 10 MariaDB 伺服器 (10.3.18)。請注意，我不是數據庫專家。

所有表都在 MyISAM 引擎儲存下，總共大約 5 Gb。計劃遷移到 InnoDB，但現在我想在新伺服器上遷移而不更新表並確保保持性能。

這是我的問題：我有一個SELECT在舊伺服器上執行約 0.2 秒的查詢，而相同的查詢在新伺服器上執行約 1.4 秒。我不知道如何解釋：這大約是全新安裝的 7 倍！

這是（簡化的）查詢：

SELECT   NOW(), uid, 
        ((MATCH (title) AGAINST ('\"keyword1\" \"keyword2\" \"keyword3\"')) * 4 + (MATCH (metakeywords) AGAINST ('\"keyword1\" \"keyword2\" \"keyword3\"')) * 2 AS MATCH 
FROM     news 
WHERE    wfplace = 'live' 
AND      deleted = 0 
AND      hidden = 0 
AND      (magazine IS NOT NULL AND magazine <> '') 
AND      (start_time = 0 OR start_time <= 1577454941) 
AND      (end_time = 0 OR end_time  >= 1577454941) 
ORDER BY MATCH DESC LIMIT 10

MATCH...AGAINST沒有這些東西的兩台伺服器之間的性能似乎相同。我使用該NOW()部分來確保結果中不涉及記憶體。

請注意，要遷移數據，我只需將 .frm、.MYD 和 MYI 文件從mysqlhotcopy轉儲文件同步到新的 .frm 文件/var/lib/mysql/my_db。

這是舊的 my.cnf（僅限自定義設置）：

skip-external-locking
skip-name-resolve
skip-innodb
skip-locking

key_buffer_size = 2048M
max_allowed_packet = 32M
table_open_cache = 2048
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 256M
thread_cache_size = 256
query_cache_size = 256M
query_cache_limit = 8M

thread_concurrency = 8

interactive_timeout     = 90
connect_timeout         = 90
wait_timeout            = 90
max_connections         = 150
long_query_time         = 2

Conf：Intel(R) Xeon(R) CPU，16 核和 47 Gb RAM。

這是新的自定義設置：

performance_schema=off
skip-name-resolve

query_cache_size=0
query_cache_type=0

key_buffer_size=20G

innodb_buffer_pool_size=20G
innodb_log_file_size=2G
innodb_buffer_pool_instances=20

table_open_cache=2048
max_allowed_packet=32M

sort_buffer_size=8M
myisam_sort_buffer_size=256M
read_buffer_size=8M
read_rnd_buffer_size=8M

wait_timeout=90
interactive_timeout=90
connect_timeout=90
long_query_time=2

Conf：Intel(R) Xeon(R) Silver 4110 CPU，16 核和 93 Gb RAM。

我試圖保留一些舊設置以具有相似的速度，但沒有成功。

我唯一的線索是EXPLAIN最後一個“額外”列上兩台伺服器之間的同一查詢的不同結果：

舊伺服器 （舊伺服器）

新伺服器 （新伺服器）

我錯過了什麼？謝謝你的幫助！

編輯：

原始EXPLAIN（舊伺服器）：

+----+-------------+-------+------+--------------------------------------------------------------------------------------+---------+---------+-------+-------+-----------------------------+
| id | select_type | table | type | possible_keys                                                                        | key     | key_len | ref   | rows  | Extra                       |
+----+-------------+-------+------+--------------------------------------------------------------------------------------+---------+---------+-------+-------+-----------------------------+
|  1 | SIMPLE      | t0_   | ref  | hidden,deleted,optim1,deleted_2,topcanonique,sort_date,idx_magazine,wfplace,magazine | wfplace | 27      | const | 28749 | Using where; Using filesort |
+----+-------------+-------+------+--------------------------------------------------------------------------------------+---------+---------+-------+-------+-----------------------------+

原始EXPLAIN（新伺服器，禁用索引條件下推）：

+------+-------------+-------+------+----------------------------------------------------------------------------------------------+---------+---------+-------+-------+---------------------------------------------------------------------+
| id   | select_type | table | type | possible_keys                                                                                | key     | key_len | ref   | rows  | Extra                                                               |
+------+-------------+-------+------+----------------------------------------------------------------------------------------------+---------+---------+-------+-------+---------------------------------------------------------------------+
|    1 | SIMPLE      | t0_   | ref  | hidden,deleted,optim1,deleted_2,topcanonique,sort_date,idx_magazine,wfplace,endtime,magazine | wfplace | 27      | const | 25572 | Using where; Using temporary; Using filesort                        |
+------+-------------+-------+------+----------------------------------------------------------------------------------------------+---------+---------+-------+-------+---------------------------------------------------------------------+

flush status; SELECT ... (query); SHOW STATUS LIKE 'Hand%';（舊伺服器）的結果：

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_next          | 34980 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 10    |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

flush status; SELECT ... (query); SHOW STATUS LIKE 'Hand%';（新伺服器）的結果：

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 34960 |
| Handler_read_prev          | 0     |
| Handler_read_retry         | 0     |
| Handler_read_rnd           | 10    |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 33195 |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_delete         | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 33194 |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

EXPLAIN FORMAT=JSON SELECT正如@Rick James 所問的結果：

{
 "query_block": {
   "select_id": 1,
   "filesort": {
     "sort_key": "(match title against ('"keyword1" "keyword2" "keyword3"')) * 4 + (match metakeywords against ('"keyword1" "keyword2" "keyword3"')) * 2",
     "temporary_table": {
       "table": {
         "table_name": "news",
         "access_type": "ref",
         "possible_keys": [
           "hidden",
           "deleted",
           "optim1",
           "deleted_2",
           "topcanonique",
           "sort_date",
           "idx_magazine",
           "wfplace",
           "magazine"
         ],
         "key": "wfplace",
         "key_length": "27",
         "used_key_parts": ["wfplace"],
         "ref": ["const"],
         "rows": 28727,
         "filtered": 100,
         "index_condition": "wfplace = 'live'",
         "attached_condition": "deleted = 0 and hidden = 0 and magazine is not null and magazine <> '' and (starttime = 0 or starttime <= 1577454941) and (endtime = 0 or endtime >= 1577454941)"
       }
     }
   }
 }
}

嘗試在新伺服器上禁用索引條件下推（預設情況下在 5.6+ 上啟用，在 <=5.5 上不可用）。

MyISAM 在較新版本的 MySQL 上並沒有得到太多的喜愛，您可能想在單獨的伺服器上測試遷移，包括 InnoDB 轉換。否則，您修復的所有優化最終都可能毫無用處，因為某些查詢功能並非在所有引擎上都可用。

（評論太大了）

這將提高性能：將`開始時間的測試更改為簡單

AND start_time &lt;= 1577454941

並且有

INDEX(deleted, hidden, place,   -- in any order
     start_time)               -- after the others

您會發現 (of MATCH) 的順序與 InnoDB 和 MyISAM 不同。

此外，當涉及多個表時，InnoDB 的行為也會有所不同。將需要兩個單獨的單列索引：FULLTEXT(title), FULLTEXT(metakeywords).

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

相關問答

Mysql

如何將 engline 類型從 InnoDb 更改為現有數據庫的 MyISAM？

  • August 4, 2022
檢視問答
Mysql

在 MySQL 中查找列的值之間的間隙

  • April 10, 2022
檢視問答
Mysql

使用映射表時獲取未映射記錄的計數

  • March 3, 2022
檢視問答
Mysql

在 Mariadb - 你可以記憶體 Ar ia 儲存引擎表的索引和數據嗎？

  • December 16, 2021
檢視問答
Mysql

TRUNCATE TABLE 語句有時會掛起

  • November 27, 2021
檢視問答
Mysql

mysql vs mariadb查詢

  • September 24, 2021
檢視問答