Mysql
從 MySQL 遷移到 MariaDB，硬體更好但性能更慢。為什麼？
我正在將我們的伺服器遷移到新的託管服務提供商，他們在最新的 Debian 10 和 MariaDB 10.3.25 的新節點上為我提供了卓越的硬體。該數據庫位於其自己的虛擬機上，具有 16GB RAM 和 170GB SSD 磁碟空間。該數據庫大小約為 57GB，包含數百個不同的表。
我已經將數據庫從我們目前使用 Debian 7 和 MySQL 5.5.50（CPU 速度較慢但記憶體和 SSD 磁碟相同）的舊實時伺服器遷移到新伺服器上，沒有出現任何問題，並且我在my.cnf 文件到 MariaDB 的設置文件中。
但是，我必須說，使用 MariaDB 的新伺服器上簡單選擇語句的性能要慢得多。與使用 MySQL 的實時伺服器相比，我在新的 MariaDB 上註冊了 25-300% 的速度範圍。如果您認為舊伺服器的使用者負載非常高（平均任何時候都有超過 100 個唯一使用者，每分鐘有數千次點擊），而新伺服器由於尚未上線而負載為零，那麼它是讓我吃驚的是有如此糟糕的結果。
這是一個關於 InnoDB 表的簡單語句（不使用記憶體）：
SELECT SQL_NO_CACHE id, title, composer, instruments, PDFdir, PDFs FROM virtualsheetmusic_optimizations.scores3_new2_optINNODB WHERE type LIKE '%Christmas%' AND (instruments LIKE '%Accordion%' OR AltInstruments LIKE '%Accordion%' ) AND tempo < 1606032000 ORDER BY product_source_id ASC, instruments LIKE 'Accordion %' DESC, popularity DESC, title LIKE '%Christmas%' DESC LIMIT 20;在舊伺服器上執行（第一次和後續執行）需要 0.3 秒。在新伺服器上，第一次執行需要超過 1 秒，後續執行大約需要 0.6 秒。奇怪的是，如果在幾分鐘內執行第一個查詢，MariaDB 執行第一個查詢總是比後續查詢花費更多的時間，即使不使用記憶體並且查詢完全相同。
使用更複雜的查詢會得到更糟糕的結果，但我想從上面更簡單的查詢開始。
這裡解釋一下舊伺服器上的上述 select 語句：
mysql> explain SELECT SQL_NO_CACHE id, title, composer, instruments, PDFdir, PDFs FROM virtualsheetmusic_optimizations.scores3_new2_optINNODB WHERE type LIKE '%Christmas%' AND (instruments LIKE '%Accordion%' OR AltInstruments LIKE '%Accordion%') AND tempo < 1606032000 ORDER BY product_source_id ASC, instruments LIKE 'Accordion %' DESC, popularity DESC, title LIKE '%Christmas%' DESC LIMIT 20; +----+-------------+------------------------+-------+---------------+-------+---------+------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+-------+---------------+-------+---------+------+-------+-----------------------------+ | 1 | SIMPLE | scores3_new2_optINNODB | range | Tempo | Tempo | 4 | NULL | 90943 | Using where; Using filesort | +----+-------------+------------------------+-------+---------------+-------+---------+------+-------+-----------------------------+ 1 row in set (0.00 sec)在新伺服器上也是如此（我沒有發現有趣的差異）：
MariaDB [(none)]> explain SELECT SQL_NO_CACHE id, title, composer, instruments, PDFdir, PDFs FROM virtualsheetmusic_optimizations.scores3_new2_optINNODB WHERE type LIKE '%Christmas%' AND (instruments LIKE '%Accordion%' OR AltInstruments LIKE '%Accordion%') AND tempo < 1606032000 ORDER BY product_source_id ASC, instruments LIKE 'Accordion %' DESC, popularity DESC, title LIKE '%Christmas%' DESC LIMIT 20; +------+-------------+------------------------+-------+---------------+-------+---------+------+-------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------------------+-------+---------------+-------+---------+------+-------+----------------------------------------------------+ | 1 | SIMPLE | scores3_new2_optINNODB | range | Tempo | Tempo | 4 | NULL | 85893 | Using index condition; Using where; Using filesort | +------+-------------+------------------------+-------+---------------+-------+---------+------+-------+----------------------------------------------------+ 1 row in set (0.001 sec) MariaDB [(none)]>這是該表的 CREATE 語句：
CREATE TABLE `scores3_new2_optINNODB` ( `Composer` varchar(256) NOT NULL, `compURL` varchar(50) NOT NULL DEFAULT '', `URL` varchar(50) NOT NULL DEFAULT '', `title` varchar(256) DEFAULT NULL, `Instruments` varchar(150) NOT NULL DEFAULT '', `instrURL` varchar(50) NOT NULL DEFAULT '', `Type` varchar(300) NOT NULL, `Skill` varchar(50) NOT NULL DEFAULT '', `SkillNo` int(11) NOT NULL DEFAULT '0', `keywords` varchar(3000) DEFAULT NULL, `Free` varchar(5) NOT NULL DEFAULT '', `AltTitle` varchar(3000) NOT NULL, `Tempo` int(11) NOT NULL DEFAULT '0', `CompOrdine` varchar(256) NOT NULL, `AltInstruments` varchar(300) DEFAULT NULL, `ContEnsemble` varchar(50) DEFAULT NULL, `Exclusive` varchar(5) DEFAULT NULL, `ID` varchar(60) DEFAULT NULL, `Price` decimal(8,2) DEFAULT NULL, `MemberPrice` decimal(9,2) NOT NULL, `Sub_Title` text, `sub_instrument` varchar(500) DEFAULT NULL, `Arrangement_Type` varchar(30) DEFAULT NULL, `Alt_Image` varchar(30) DEFAULT NULL, `PDFs` text NOT NULL, `PDFdir` varchar(10) NOT NULL DEFAULT '', `SkillOrd` int(11) NOT NULL DEFAULT '0', `rating` int(11) NOT NULL DEFAULT '0', `scorch` tinyint(4) NOT NULL, `has_pdf` tinyint(4) NOT NULL, `has_mp3` tinyint(4) NOT NULL, `has_mp3acco` tinyint(4) NOT NULL, `has_midi` tinyint(4) NOT NULL, `has_key_video` tinyint(4) NOT NULL DEFAULT '0', `product_source_id` int(11) NOT NULL DEFAULT '1', `product_type_id` tinyint(4) NOT NULL DEFAULT '0', `description` text NOT NULL, `restricted` tinyint(4) NOT NULL, `new_viewer` tinyint(4) NOT NULL, `extra_score` tinyint(4) NOT NULL, `minimum_quantity` int(11) NOT NULL DEFAULT '1', `TOTnoterange` text NOT NULL, `TOTnoterangen` varchar(7) NOT NULL, `TOTtemporangebpm` varchar(7) NOT NULL, `TOTtemporealrangebpm` varchar(7) NOT NULL, `TOTtimesignaturerange` text NOT NULL, `TOTkeysignaturerange` text NOT NULL, `TOTduration` varchar(8) NOT NULL, `TitleOrdine` varchar(256) DEFAULT NULL, `display_priority` int(11) DEFAULT NULL, `is_interactive` tinyint(4) NOT NULL, `hidden` tinyint(4) NOT NULL DEFAULT '0', `preview_width` int(11) NOT NULL DEFAULT '0', `preview_height` int(11) NOT NULL DEFAULT '0', `performer` varchar(100) NOT NULL, `duration` int(11) NOT NULL, `page_tempo_update` int(11) NOT NULL, `meta_title` varchar(200) NOT NULL, `meta_description` text NOT NULL, `header` varchar(100) NOT NULL, `subheader` varchar(100) NOT NULL, `link_id` varchar(200) NOT NULL, `link_text` varchar(300) NOT NULL, `has_custom_mp3` tinyint(4) NOT NULL, `title_dup_fix` tinyint(4) NOT NULL, `popularity` int(11) NOT NULL, `icon` tinyint(4) NOT NULL, `exclusive_OPT` int(11) NOT NULL, `free_OPT` int(11) NOT NULL, `Composer_OPT` varchar(300) NOT NULL, `title_OPT` varchar(200) NOT NULL, `Instruments_OPT` varchar(100) NOT NULL, `Type_OPT` varchar(350) NOT NULL, `Skill_OPT` varchar(50) NOT NULL, `keywords_OPT` varchar(3500) NOT NULL, `AltTitle_OPT` varchar(3500) NOT NULL, `AltInstruments_OPT` varchar(400) NOT NULL, `Sub_Title_OPT` varchar(3500) NOT NULL, `sub_instrument_OPT` varchar(600) NOT NULL, KEY `ID` (`ID`), KEY `title` (`title`), KEY `Composer` (`Composer`), KEY `Instruments` (`Instruments`), KEY `has_mp3acco` (`has_mp3acco`), KEY `exclusive_OPT` (`exclusive_OPT`), KEY `free_OPT` (`free_OPT`), KEY `display_priority` (`display_priority`), KEY `rating` (`rating`), KEY `CompOrdine` (`CompOrdine`), KEY `Skill` (`Skill`), KEY `SkillOrd` (`SkillOrd`), KEY `Price` (`Price`), KEY `MemberPrice` (`MemberPrice`), KEY `Tempo` (`Tempo`), KEY `Composer_OPT` (`Composer_OPT`), KEY `title_OPT` (`title_OPT`), KEY `Instruments_OPT` (`Instruments_OPT`), KEY `Type_OPT` (`Type_OPT`), KEY `Skill_OPT` (`Skill_OPT`), KEY `keywords_OPT` (`keywords_OPT`(767)), KEY `AltTitle_OPT` (`AltTitle_OPT`(767)), KEY `AltInstruments_OPT` (`AltInstruments_OPT`), KEY `Sub_Title_OPT` (`Sub_Title_OPT`(767)), KEY `sub_instrument_OPT` (`sub_instrument_OPT`), KEY `product_source_id` (`product_source_id`), KEY `is_interactive` (`is_interactive`), KEY `product_type_id` (`product_type_id`), KEY `popularity` (`popularity`), KEY `display_priority_2` (`display_priority`,`product_type_id`,`Instruments`,`popularity`, `title_OPT`,`Composer_OPT`,`exclusive_OPT`, `keywords_OPT`(767),`title`,`rating`,`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;這是舊伺服器上的 SHOW CREATE TABLE ：
mysql> SHOW CREATE TABLE scores3_new2_optINNODB; +------------------------+ | Table | Create Table | +-----------------------+ | scores3_new2_optINNODB | CREATE TABLE `scores3_new2_optINNODB` ( `Composer` varchar(256) NOT NULL, `compURL` varchar(50) NOT NULL DEFAULT '', `URL` varchar(50) NOT NULL DEFAULT '', `title` varchar(256) DEFAULT NULL, `Instruments` varchar(150) NOT NULL DEFAULT '', `instrURL` varchar(50) NOT NULL DEFAULT '', `Type` varchar(300) NOT NULL, `Skill` varchar(50) NOT NULL DEFAULT '', `SkillNo` int(11) NOT NULL DEFAULT '0', `keywords` varchar(3000) DEFAULT NULL, `Free` varchar(5) NOT NULL DEFAULT '', `AltTitle` varchar(3000) NOT NULL, `Tempo` int(11) NOT NULL DEFAULT '0', `CompOrdine` varchar(256) NOT NULL, `AltInstruments` varchar(300) DEFAULT NULL, `ContEnsemble` varchar(50) DEFAULT NULL, `Exclusive` varchar(5) DEFAULT NULL, `ID` varchar(60) DEFAULT NULL, `Price` decimal(8,2) DEFAULT NULL, `MemberPrice` decimal(9,2) NOT NULL, `Sub_Title` text, `sub_instrument` varchar(500) DEFAULT NULL, `Arrangement_Type` varchar(30) DEFAULT NULL, `Alt_Image` varchar(30) DEFAULT NULL, `PDFs` text NOT NULL, `PDFdir` varchar(10) NOT NULL DEFAULT '', `SkillOrd` int(11) NOT NULL DEFAULT '0', `rating` int(11) NOT NULL DEFAULT '0', `scorch` tinyint(4) NOT NULL, `has_pdf` tinyint(4) NOT NULL, `has_mp3` tinyint(4) NOT NULL, `has_mp3acco` tinyint(4) NOT NULL, `has_midi` tinyint(4) NOT NULL, `has_key_video` tinyint(4) NOT NULL DEFAULT '0', `product_source_id` int(11) NOT NULL DEFAULT '1', `product_type_id` tinyint(4) NOT NULL DEFAULT '0', `description` text NOT NULL, `restricted` tinyint(4) NOT NULL, `new_viewer` tinyint(4) NOT NULL, `extra_score` tinyint(4) NOT NULL, `minimum_quantity` int(11) NOT NULL DEFAULT '1', `TOTnoterange` text NOT NULL, `TOTnoterangen` varchar(7) NOT NULL, `TOTtemporangebpm` varchar(7) NOT NULL, `TOTtemporealrangebpm` varchar(7) NOT NULL, `TOTtimesignaturerange` text NOT NULL, `TOTkeysignaturerange` text NOT NULL, `TOTduration` varchar(8) NOT NULL, `TitleOrdine` varchar(256) DEFAULT NULL, `display_priority` int(11) DEFAULT NULL, `is_interactive` tinyint(4) NOT NULL, `hidden` tinyint(4) NOT NULL DEFAULT '0', `preview_width` int(11) NOT NULL DEFAULT '0', `preview_height` int(11) NOT NULL DEFAULT '0', `performer` varchar(100) NOT NULL, `duration` int(11) NOT NULL, `page_tempo_update` int(11) NOT NULL, `meta_title` varchar(200) NOT NULL, `meta_description` text NOT NULL, `header` varchar(100) NOT NULL, `subheader` varchar(100) NOT NULL, `link_id` varchar(200) NOT NULL, `link_text` varchar(300) NOT NULL, `has_custom_mp3` tinyint(4) NOT NULL, `title_dup_fix` tinyint(4) NOT NULL, `popularity` int(11) NOT NULL, `icon` tinyint(4) NOT NULL, `exclusive_OPT` int(11) NOT NULL, `free_OPT` int(11) NOT NULL, `Composer_OPT` varchar(300) NOT NULL, `title_OPT` varchar(200) NOT NULL, `Instruments_OPT` varchar(100) NOT NULL, `Type_OPT` varchar(350) NOT NULL, `Skill_OPT` varchar(50) NOT NULL, `keywords_OPT` varchar(3500) NOT NULL, `AltTitle_OPT` varchar(3500) NOT NULL, `AltInstruments_OPT` varchar(400) NOT NULL, `Sub_Title_OPT` varchar(3500) NOT NULL, `sub_instrument_OPT` varchar(600) NOT NULL, KEY `ID` (`ID`), KEY `title` (`title`), KEY `Composer` (`Composer`), KEY `Instruments` (`Instruments`), KEY `has_mp3acco` (`has_mp3acco`), KEY `exclusive_OPT` (`exclusive_OPT`), KEY `free_OPT` (`free_OPT`), KEY `display_priority` (`display_priority`), KEY `rating` (`rating`), KEY `CompOrdine` (`CompOrdine`), KEY `Skill` (`Skill`), KEY `SkillOrd` (`SkillOrd`), KEY `Price` (`Price`), KEY `MemberPrice` (`MemberPrice`), KEY `Tempo` (`Tempo`), KEY `Composer_OPT` (`Composer_OPT`), KEY `title_OPT` (`title_OPT`), KEY `Instruments_OPT` (`Instruments_OPT`), KEY `Type_OPT` (`Type_OPT`), KEY `Skill_OPT` (`Skill_OPT`), KEY `keywords_OPT` (`keywords_OPT`(767)), KEY `AltTitle_OPT` (`AltTitle_OPT`(767)), KEY `AltInstruments_OPT` (`AltInstruments_OPT`), KEY `Sub_Title_OPT` (`Sub_Title_OPT`(767)), KEY `sub_instrument_OPT` (`sub_instrument_OPT`), KEY `product_source_id` (`product_source_id`), KEY `is_interactive` (`is_interactive`), KEY `product_type_id` (`product_type_id`), KEY `popularity` (`popularity`), KEY `display_priority_2` (`display_priority`,`product_type_id`,`Instruments`,`popularity`, `title_OPT`,`Composer_OPT`,`exclusive_OPT`,`keywords_OPT`(767), `title`,`rating`,`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +------------------------+ 1 row in set (0.00 sec)在新伺服器上也是如此：
MariaDB [virtualsheetmusic_optimizations]> SHOW CREATE TABLE scores3_new2_optINNODB; +------------------------+ | Table | Create Table | +------------------------+ | scores3_new2_optINNODB | CREATE TABLE `scores3_new2_optINNODB` ( `Composer` varchar(256) CHARACTER SET latin1 NOT NULL, `compURL` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '', `URL` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '', `title` varchar(256) CHARACTER SET latin1 DEFAULT NULL, `Instruments` varchar(150) CHARACTER SET latin1 NOT NULL DEFAULT '', `instrURL` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '', `Type` varchar(300) CHARACTER SET latin1 NOT NULL, `Skill` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '', `SkillNo` int(11) NOT NULL DEFAULT 0, `keywords` varchar(3000) CHARACTER SET latin1 DEFAULT NULL, `Free` varchar(5) CHARACTER SET latin1 NOT NULL DEFAULT '', `AltTitle` varchar(3000) CHARACTER SET latin1 NOT NULL, `Tempo` int(11) NOT NULL DEFAULT 0, `CompOrdine` varchar(256) CHARACTER SET latin1 NOT NULL, `AltInstruments` varchar(300) CHARACTER SET latin1 DEFAULT NULL, `ContEnsemble` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `Exclusive` varchar(5) CHARACTER SET latin1 DEFAULT NULL, `ID` varchar(60) CHARACTER SET latin1 NOT NULL, `Price` decimal(8,2) DEFAULT NULL, `MemberPrice` decimal(9,2) NOT NULL, `Sub_Title` text CHARACTER SET latin1 DEFAULT NULL, `sub_instrument` varchar(500) CHARACTER SET latin1 DEFAULT NULL, `Arrangement_Type` varchar(30) CHARACTER SET latin1 DEFAULT NULL, `Alt_Image` varchar(30) CHARACTER SET latin1 DEFAULT NULL, `PDFs` text CHARACTER SET latin1 NOT NULL, `PDFdir` varchar(10) CHARACTER SET latin1 NOT NULL DEFAULT '', `SkillOrd` int(11) NOT NULL DEFAULT 0, `rating` int(11) NOT NULL DEFAULT 0, `scorch` tinyint(4) NOT NULL, `has_pdf` tinyint(4) NOT NULL, `has_mp3` tinyint(4) NOT NULL, `has_mp3acco` tinyint(4) NOT NULL, `has_midi` tinyint(4) NOT NULL, `has_key_video` tinyint(4) NOT NULL DEFAULT 0, `product_source_id` int(11) NOT NULL DEFAULT 1, `product_type_id` tinyint(4) NOT NULL DEFAULT 0, `description` text CHARACTER SET latin1 NOT NULL, `restricted` tinyint(4) NOT NULL, `new_viewer` tinyint(4) NOT NULL, `extra_score` tinyint(4) NOT NULL, `minimum_quantity` int(11) NOT NULL DEFAULT 1, `TOTnoterange` text CHARACTER SET latin1 NOT NULL, `TOTnoterangen` varchar(7) CHARACTER SET latin1 NOT NULL, `TOTtemporangebpm` varchar(7) CHARACTER SET latin1 NOT NULL, `TOTtemporealrangebpm` varchar(7) CHARACTER SET latin1 NOT NULL, `TOTtimesignaturerange` text CHARACTER SET latin1 NOT NULL, `TOTkeysignaturerange` text CHARACTER SET latin1 NOT NULL, `TOTduration` varchar(8) CHARACTER SET latin1 NOT NULL, `TitleOrdine` varchar(256) DEFAULT NULL, `display_priority` int(11) DEFAULT NULL, `is_interactive` tinyint(4) NOT NULL, `hidden` tinyint(4) NOT NULL DEFAULT 0, `preview_width` int(11) NOT NULL DEFAULT 0, `preview_height` int(11) NOT NULL DEFAULT 0, `performer` varchar(100) NOT NULL, `duration` int(11) NOT NULL, `page_tempo_update` int(11) NOT NULL, `meta_title` varchar(200) NOT NULL, `meta_description` text NOT NULL, `header` varchar(100) NOT NULL, `subheader` varchar(100) NOT NULL, `link_id` varchar(200) NOT NULL, `link_text` varchar(300) NOT NULL, `has_custom_mp3` tinyint(4) NOT NULL, `title_dup_fix` tinyint(4) NOT NULL, `popularity` int(11) NOT NULL, `icon` tinyint(4) NOT NULL, `exclusive_OPT` int(11) NOT NULL, `free_OPT` int(11) NOT NULL, `Composer_OPT` varchar(300) NOT NULL, `title_OPT` varchar(200) NOT NULL, `Instruments_OPT` varchar(100) NOT NULL, `Type_OPT` varchar(350) NOT NULL, `Skill_OPT` varchar(50) NOT NULL, `keywords_OPT` varchar(3500) NOT NULL, `AltTitle_OPT` varchar(3500) NOT NULL, `AltInstruments_OPT` varchar(400) NOT NULL, `Sub_Title_OPT` varchar(3500) NOT NULL, `sub_instrument_OPT` varchar(600) NOT NULL, KEY `title` (`title`), KEY `Composer` (`Composer`), KEY `Instruments` (`Instruments`), KEY `has_mp3acco` (`has_mp3acco`), KEY `exclusive_OPT` (`exclusive_OPT`), KEY `free_OPT` (`free_OPT`), KEY `display_priority` (`display_priority`), KEY `rating` (`rating`), KEY `CompOrdine` (`CompOrdine`), KEY `Skill` (`Skill`), KEY `SkillOrd` (`SkillOrd`), KEY `Price` (`Price`), KEY `MemberPrice` (`MemberPrice`), KEY `Tempo` (`Tempo`), KEY `Composer_OPT` (`Composer_OPT`), KEY `title_OPT` (`title_OPT`), KEY `Instruments_OPT` (`Instruments_OPT`), KEY `Type_OPT` (`Type_OPT`), KEY `Skill_OPT` (`Skill_OPT`), KEY `keywords_OPT` (`keywords_OPT`(768)), KEY `AltTitle_OPT` (`AltTitle_OPT`(768)), KEY `AltInstruments_OPT` (`AltInstruments_OPT`), KEY `Sub_Title_OPT` (`Sub_Title_OPT`(768)), KEY `sub_instrument_OPT` (`sub_instrument_OPT`), KEY `product_source_id` (`product_source_id`), KEY `is_interactive` (`is_interactive`), KEY `product_type_id` (`product_type_id`), KEY `popularity` (`popularity`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +------------------------+ 1 row in set (0.001 sec)最後，這是使用 MySQL 的舊伺服器上的 my.cnf（我已刪除評論）：
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking bind-address = 0.0.0.0 key_buffer = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP query_cache_limit = 1M query_cache_size = 16M expire_logs_days = 10 max_binlog_size = 100M symbolic-links=0 max_allowed_packet=1000M net_buffer_length=100M read_buffer_size=16M max_connections=300 query-cache-type=1 query-cache-size=20M myisam_recover_options=OFF thread_stack=262144 innodb_buffer_pool_size=1GB innodb_io_capacity = 2000 innodb_read_io_threads = 64 innodb_thread_concurrency = 0 innodb_write_io_threads = 64 innodb_buffer_pool_instances = 8 innodb_flush_log_at_trx_commit = 2 [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/在新伺服器（MariaDB）上也是如此：
log_error = /var/log/mysql/error.log expire_logs_days = 10 max_binlog_size = 100M character-set-server = utf8mb4 collation-server = utf8mb4_general_ci symbolic-links=0 net_buffer_length=100M read_buffer_size=16M max_connections=300 query-cache-type=1 query-cache-size=20M myisam_recover_options=OFF thread_stack=262144 innodb_buffer_pool_size=10GB innodb_io_capacity = 2000 innodb_read_io_threads = 64 innodb_thread_concurrency = 0 innodb_write_io_threads = 64 innodb_buffer_pool_instances = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 64M innodb_thread_sleep_delay = 0 innodb_file_per_table = 1 skip-name-resolve innodb_strict_mode = 0 [embedded] [mariadb] [mariadb-10.3]渴望知道你對這一切的看法。
提前感謝大家！
一切順利，法布。
（這解決瞭如何顯著加快查詢速度。）
緩慢的部分是
WHERE. 從改變WHERE type LIKE '%Christmas%' AND (instruments LIKE '%Accordion%' OR AltInstruments LIKE '%Accordion%' ) AND tempo < 1606032000到
WHERE MATCH(type, instruments, AltInstruments) AGAINST ("+Christmas +Accordion" IN BOOLEAN MODE) AND type LIKE '%Christmas%' AND (instruments LIKE '%Accordion%' OR AltInstruments LIKE '%Accordion%' ) AND tempo < 1606032000並添加
FULLTEXT(type, instruments, AltInstruments)將
MATCH首先執行，並且非常快。其餘的將仔細檢查。使用“前綴索引”，例如
(767)，實際上是無用的，在新版本中變得不那麼必要了。見http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes改成
767不會768有太大區別。前導萬用字元
LIKE可防止使用索引。在索引的任何地方都有一個前綴可以防止它被“覆蓋”。
執行第一個查詢的時間比後續查詢多
這僅僅是因為第一次執行需要從磁碟獲取數據；隨後的沒有。這適用於 MySQL 和 MariaDB。我猜 MySQL 計時執行是在已經記憶體了表的機器上。
我看到
latin1了 vsutf8mb4。可能會對此查詢和其他查詢產生影響。
TOTtemporangebpmvarchar(7) – 如果這是一個“時間”，使用varchar可能會受到傷害。表的**
ROW_FORMAT**for 更改COMPACT為DYNAMIC?兩者之間的主要區別在於當行大於大約 8KB 時會發生什麼。看起來你的桌子經常發生這種情況。對於 Compact，它嘗試將最多 768 個字節放入行的“記錄”部分，將其餘字節溢出到“記錄外”的位置（一個單獨的塊）。對於 Dynamic，任何需要解除安裝的列都會被完全移動。
當您
SELECT以不同方式處理任何此類列時，就會產生這種影響。這是額外磁碟命中的差異；這些可以安裝。尤其糟糕的是
SELECT *，當您並不真正需要所有列時。SELECT a,b,c僅僅因為其餘列的儲存方式，這將執行得更快。80列很多。特別是因為許多都是“大”
VARCHARs的。看了一眼列名，我沒有看到任何明顯的建議。但是請考慮一下…如果您有一些很少使用或“屬於一起”或經常使用的列
NULL，請考慮將此類列移到另一個表中。PRIMARY KEY (ID)為方便起見，新表將共享相同的JOINing。另外，我們應該談談ID的樣子。它是一個 UUID 嗎？還有什麼？真的有那麼長嗎？等等這裡有表演要討論。