提高 MySql 在大表中的查詢性能
我有一個 MySql 數據庫，用於儲存來自應用程序的事件，因為我們創建它，我們只插入和選擇數據，我們從未刪除任何行。我不是數據庫管理員，我的組織中也沒有數據庫管理員，所以如果我缺少一些基本的東西，請多多包涵。數據庫有一個像這樣的表：
CREATE TABLE `eventlogs` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `LogType` int(11) NOT NULL, `ProductId` longtext, `Username` varchar(128) CHARACTER SET utf8 DEFAULT NULL, `ClientVersion` longtext, `Message` longtext, `Referrer` longtext, `UserAgent` longtext, `CreatedDate` datetime NOT NULL, PRIMARY KEY (`Id`), KEY `IX_LogType` (`LogType`), KEY `IX_CreatedDate` (`CreatedDate`), KEY `IX_Username` (`Username`) ) ENGINE=InnoDB AUTO_INCREMENT=180712975 DEFAULT CHARSET=latin1;這曾經工作得很好，但它達到了幾乎不可能執行任何查詢的地步，它們需要超過 15 分鐘，有時甚至更長時間！這是我們執行的典型查詢：
SELECT * FROM customily_logs.eventlogs WHERE CreatedDate > '2020-06-01' and Username = 'myuser' and LogType = 3這是查詢的執行計劃：
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "14073888.06" }, "table": { "table_name": "eventlogs", "access_type": "index_merge", "possible_keys": [ "IX_LogType", "IX_CreatedDate", "IX_Username" ], "key": "intersect(IX_Username,IX_LogType)", "key_length": "387,4", "rows_examined_per_scan": 15809639, "rows_produced_per_join": 7904819, "filtered": "50.00", "cost_info": { "read_cost": "12492924.16", "eval_cost": "1580963.90", "prefix_cost": "14073888.06", "data_read_per_join": "3G" }, "used_columns": [ "Id", "LogType", "ProductId", "Username", "ClientVersion", "Message", "Referrer", "UserAgent", "CreatedDate" ], "attached_condition": "((`customily_logs`.`eventlogs`.`LogType` = 3) and (`customily_logs`.`eventlogs`.`CreatedDate` > '2020-06-01') and (`customily_logs`.`eventlogs`.`Username` = 'myuser'))" } } }這是 Windows Server 2012 R2 x64 上的 MySQL 5.7.29。該表有 1.77 億行，根據 MySql Workbench，它有 65.6 GiB 的數據和 8.8 GiB 的索引。Windows 機器是 AWS 上的 EC2 實例，具有 32 個核心、128 GB 的 RAM 和 2 TB 的 EBS 儲存和 9000 IOPS，雖然它與其他正在執行的應用程序共享，但 CPU 使用率很少超過 50%，並且總是在 50 GB 左右的空閒 RAM。這些是伺服器設置
my.ini：[client] port=3307 [mysql] no-beep [mysqld] port=3307 datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" log-output=FILE general-log=0 general_log_file="WIN-2BKHQL88U78.log" slow-query-log=1 slow_query_log_file="WIN-2BKHQL88U78-slow.log" long_query_time=10 log-error="WIN-2BKHQL88U78.err" relay_log="WIN-2BKHQL88U78-relay" server-id=1 report_port=3307 lower_case_table_names=1 secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads" max_connections=151 table_open_cache=2000 tmp_table_size=3G thread_cache_size=10 myisam_max_sort_file_size=100G myisam_sort_buffer_size=4G key_buffer_size=8M read_buffer_size=64K read_rnd_buffer_size=256K innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=1M innodb_buffer_pool_size = 24G innodb_log_file_size=48M innodb_thread_concurrency=33 innodb_autoextend_increment=64 innodb_buffer_pool_instances=8 innodb_concurrency_tickets=5000 innodb_old_blocks_time=1000 innodb_open_files=300 innodb_stats_on_metadata=0 innodb_file_per_table=1 innodb_checksum_algorithm=0 back_log=80 flush_time=0 join_buffer_size=256K max_allowed_packet=4M max_connect_errors=100 open_files_limit=4161 sort_buffer_size=256K table_definition_cache=1400 binlog_row_event_max_size=8K sync_master_info=10000 sync_relay_log=10000 sync_relay_log_info=10000我嘗試將其
innodb_buffer_pool_size增加到 32 GB，但沒有幫助。我也執行ANALYZE它幾乎立即完成，我檢查了數據庫中的碎片，但它似乎真的很低（用這個檢查它）。我希望將查詢時間減少到 1 分鐘或更短，但我不知道這是否不切實際。多longtext列的使用會影響查詢時間嗎？非常感謝您的幫助！
好消息是您需要更好地使用多列索引來更好地對應您的查詢（參考：MySQL 如何使用索引）。
通過使用您的常量 ref 值（
WHERE條件是=），如果您創建組合索引Username，則LogType在範圍之前，CreatedDate這將有助於查詢。ALTER TABLE eventlogs DROP INDEX IX_Username, ADD INDEX IX_Username_LogType_CreatedDate(Username,LogType,CreatedDate), ALGORITHM=INPLACE, LOCK=NONE;我已經替換了現有索引，因為具有相同前綴的類似大小的索引是等效的。
如果返回的數據集大小不是很大，那麼應該可以進行 <1 分鐘的查詢。
longtext列在這裡不會有太大影響，除非有很多行的大小超過〜8k（即儲存在行外 - ref），這將影響基於返回的行數的查詢時間，而不是搜尋時間（因為索引不是長文本）。