將正確的索引添加到 MySQL 數據庫中的表
我們的數據庫伺服器中的 MySQL 程序的 CPU 使用率很高，經過調查我們發現很大一部分慢查詢與特定表有關，並且大量查詢具有相同的結構，只是欄位 targetPk正在從查詢更改為另一個
SELECT item_t1.PK FROM product2division item_t0 JOIN division item_t1 ON item_t0.SourcePK = item_t1.PK WHERE ( item_t0.Qualifier = 'Product2Division' AND item_t0.TargetPK = 8799116853249 AND item_t0.LanguagePK IS NULL) AND (item_t1.TypePkString=8796130967634 ) order by item_t0.RSequenceNumber ASC , item_t0.PK ASC跑步
explain SELECT item_t1.PK FROM product2division item_t0 JOIN division item_t1 ON item_t0.SourcePK = item_t1.PK WHERE ( item_t0.Qualifier = 'Product2Division' AND item_t0.TargetPK = 8799116853249 AND item_t0.LanguagePK IS NULL) AND (item_t1.TypePkString=8796130967634 ) order by item_t0.RSequenceNumber ASC , item_t0.PK ASC \G我得到這個結果：
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: item_t0 partitions: NULL type: ref possible_keys: linksource_20002,qualifier_20002,linktarget_20002 key: qualifier_20002 key_len: 767 ref: const rows: 1 filtered: 100.00 Extra: Using where; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: item_t1 partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: hybris.item_t0.SourcePK rows: 1 filtered: 100.00 Extra: Using where 2 rows in set, 1 warning (0.00 sec)MySQLtunner 的輸出
在確定自己在做什麼之前，我不想做任何事情，你認為在 item_t0.TargetPK 列中添加索引會優化查詢，因為它是將值從查詢更改為另一個的人嗎？或者是其他東西 ？
更新
我知道我以前必須這樣做，但我發現索引 targetPk 已經實現
我不知道下一步該怎麼做！幫助
*************************** 6. row *************************** Table: product2division Non_unique: 1 Key_name: linktarget_20002 Seq_in_index: 1 Column_name: TargetPK Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 6 rows in set (0.05 sec)這是 product2division 表中的所有索引：
Key_name: linktarget_20002 Seq_in_index: 1 Column_name: TargetPK Key_name: qualifier_20002 Seq_in_index: 1 Column_name: Qualifier Key_name: seqnr_20002 Seq_in_index: 1 Column_name: SequenceNumber Key_name: rseqnr_20002 Seq_in_index: 1 Column_name: RSequenceNumber Key_name: linksource_20002 Seq_in_index: 1 Column_name: SourcePK Key_name: PRIMARY Seq_in_index: 1 Column_name: PK顯示創建表輸出
mysql> show create table product2division \G *************************** 1. row *************************** Table: product2division Create Table: CREATE TABLE `product2division` ( `hjmpTS` bigint DEFAULT NULL, `TypePkString` bigint NOT NULL, `PK` bigint NOT NULL, `createdTS` datetime NOT NULL, `modifiedTS` datetime DEFAULT NULL, `OwnerPkString` bigint DEFAULT NULL, `aCLTS` bigint DEFAULT '0', `propTS` bigint DEFAULT '0', `Qualifier` varchar(255) NOT NULL, `SourcePK` bigint NOT NULL, `TargetPK` bigint NOT NULL, `RSequenceNumber` int DEFAULT '0', `SequenceNumber` int DEFAULT '0', `languagepk` bigint DEFAULT NULL, `sealed` tinyint(1) DEFAULT NULL, PRIMARY KEY (`PK`), KEY `linksource_20002` (`SourcePK`), KEY `rseqnr_20002` (`RSequenceNumber`), KEY `seqnr_20002` (`SequenceNumber`), KEY `qualifier_20002` (`Qualifier`), KEY `linktarget_20002` (`TargetPK`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8第二張桌子
SHOW CREATE TABLE division \G *************************** 1. row *************************** Table: division Create Table: CREATE TABLE `division` ( `hjmpTS` bigint DEFAULT NULL, `TypePkString` bigint NOT NULL, `PK` bigint NOT NULL, `createdTS` datetime NOT NULL, `p_isconstruction` tinyint(1) DEFAULT NULL, PRIMARY KEY (`PK`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)my.cnf 文件屬性：
innodb_log_files_in_group = 3 innodb_fast_shutdown = 0 innodb_buffer_pool_size = 7168M innodb_buffer_pool_instances = 4 innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_lock_wait_timeout = 50 join_buffer_size = 8M max_connections = 250 max_heap_table_size = 48M sort_buffer_size = 48M table_definition_cache = 600 thread_cache_size = 8 thread_stack = 192K tmp_table_size = 64M wait_timeout = 28800 # * MyISAM settings myisam-recover-options = BACKUP read_buffer_size = 4M low_priority_updates = 1 read_rnd_buffer_size = 1M key_buffer_size = 16M [mysqldump] quick max_allowed_packet = 16M [isamchk] key_buffer = 16MRAM CPU 和交換使用率
提前致謝 ！！
“您是否認為在 item_t0.TargetPK 列中添加索引會優化查詢，因為它是將值從查詢更改為另一個的人？”
這是索引的一個不好的理由。
重要的是索引將在多大程度上幫助減少計算查詢所需的工作量。您提出的索引是否有幫助將主要取決於這些結果中的哪一個更大：
select count(*) from product2division item_t0 where item_t0.Qualifier = 'Product2Division' select count(*) from product2division item_t0 where item_t0.TargetPK = 8799116853249假設
TargetPk值分佈均勻。如果
TargetPK是唯一的，那麼第二個查詢將始終返回 1（或零），這意味著在表中最多只能查看 1 行，這很好。當然，如果差異很小，那麼決定者將取決於匹配這些過濾器的行的聚集程度。如果兩個計數都相對較高，那麼包含兩列的複合索引可能是一個好主意（如果每個過濾器找到的行上沒有很多交叉）。
另一種可能性是您可以從
division表中驅動查詢。TypePkString如果要返回少量行並且SourcePK過濾器（來自連接）在減少中訪問的行數方面也相對較好，這可能是明智的product2division。對於此表順序，您將編制索引：division (TypePkString) product2division (SourcePK)
我只會關注 cnf 選項。
這四個選項與儲存引擎無關，但與客戶端的會話有關，因此我的建議是將它們保存在 cnf 文件的單獨段落中。它們的值組合併乘以並發連接數（可能的最大值和達到的最大值）會影響 RAM 消耗。
read_buffer_size = 4M read_rnd_buffer_size = 1M join_buffer_size = 8M sort_buffer_size = 48M48M 對於排序緩衝區來說是相當大的價值。可能你不需要那麼大。您可以從每個變數的 1M 值開始。這將導致每個連接有 4M（約 5M 的成本）緩衝區（而不是現在的 77M），並將 RAM 消耗減少 5GB 達到/最大 18GB。
此外，該變數對於您的硬體來說太高了：
innodb_buffer_pool_size = 7168M您有 7.8GB 的總 RAM，由主機上的作業系統、數據庫和其他服務共享。該值的良好起點是總 RAM 量的一半或 4G。
innodb_buffer_pool_size = 4G請記住，
innodb_log_file_size值應接近innodb_buffer_pool_size值的 1/8。它目前的512M值已經足夠了。大約 mysql RAM 消耗將是
4G + (75 * 5M) = 5G (預期)
4G + (250 * 5M) = 6G (最大)
並且 2-3G 可免費用於作業系統需求和微調。如果您的 RAM 消耗足夠低，您可以從以下開始調整您的選項
innodb_buffer_pool_size- 分別將其設置為 5G 而不是 4G，並innodb_log_file_size分別進行校正。你可以參考
InnoDB metricsmysqltuner 的報告部分來查看InnoDB buffer pool / data size比例。innodb_buffer_pool_size如果數據大小接近或大於您擁有的物理 RAM，那麼您不會從修改中獲得太多收益。但我看到你沒有慢查詢和 tmp 表，所以這對你來說不是問題。您可以將 join/sort/read/rnd 緩衝區值提高到總共 6-8MB。需要增加哪些值取決於您的查詢，並且應該就地測試。