Mysql

報告查詢阻止其他查詢，但 ISOLATION LEVEL READ UNCOMMITTED SET

  • December 29, 2016

在帶有 InnoDB 的 Amazon RDS 上執行的 MySQL 5.7.11

我有一個相當繁重的報告查詢，執行大約需要 3 分鐘。在此期間，我無法訪問我的報告螢幕，該螢幕會從報告中包含的一個表格中讀取一些最小和最大日期。

我會認為通過設置“SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED”只讀查詢根本不會相互影響？我在會話級別和 RDS 上的參數組都有這個設置

有什麼我想念的嗎？

顯示引擎 INNODB 狀態

http://pastebin.com/XvU1AdNM

顯示創建表…

auto_increment number 表示粗略的行數

CREATE TABLE `transaction` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `file_id` int(11) DEFAULT NULL,
 `countid` int(11) NOT NULL,
 `txn_date` datetime NOT NULL,
 `txn_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `user_rmn` bigint(20) NOT NULL,
 `customer_no` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `aggregator_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `trans_amount` decimal(15,4) NOT NULL,
 `incoming_commission` decimal(15,4) NOT NULL,
 `mmplt_txn_id` int(11) NOT NULL,
 `product_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `txn_category` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `circle` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `status` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `role` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `number` int(11) DEFAULT NULL,
 `user_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `city_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `state_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `retailer_commission` decimal(15,4) NOT NULL,
 `total_commission` decimal(15,4) NOT NULL,
 `net_revenue` decimal(15,4) NOT NULL,
 `ad_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `ad_commission` decimal(15,4) DEFAULT NULL,
 `md_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `md_commission` decimal(15,4) DEFAULT NULL,
 `cnf_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `cnf_commission` decimal(15,4) DEFAULT NULL,
 `ad_id` bigint(20) DEFAULT NULL,
 `md_id` bigint(20) DEFAULT NULL,
 `cnf_id` bigint(20) DEFAULT NULL,
 `operator_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `txnId` (`txn_id`),
 KEY `IDX_723705D193CB796C` (`file_id`),
 KEY `date_idx` (`txn_date`),
 KEY `user_idx` (`user_id`),
 KEY `cnf_idx` (`cnf_id`),
 KEY `md_idx` (`md_id`),
 KEY `ad_idx` (`ad_id`),
 KEY `user_rmn_idx` (`user_rmn`),
 KEY `trans_amount_idx` (`trans_amount`),
 KEY `incoming_commission_idx` (`incoming_commission`),
 KEY `retailer_commission_idx` (`retailer_commission`),
 KEY `ad_commission_idx` (`ad_commission`),
 KEY `md_commission_idx` (`md_commission`),
 KEY `cnf_commission_idx` (`cnf_commission`),
 KEY `cnf_date_idx` (`txn_date`,`cnf_id`),
 KEY `md_date_idx` (`txn_date`,`md_id`),
 KEY `ad_date_idx` (`txn_date`,`ad_id`),
 KEY `user_rmn_date_idx` (`txn_date`,`user_rmn`),
 CONSTRAINT `FK_723705D193CB796C` FOREIGN KEY (`file_id`) REFERENCES `file_to_sync` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11370410 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `operator` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 `category_low_id` int(11) DEFAULT NULL,
 `category_medium_id` int(11) DEFAULT NULL,
 `category_high_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `IDX_D7A6A781B596C062` (`category_low_id`),
 KEY `IDX_D7A6A78125326495` (`category_medium_id`),
 KEY `IDX_D7A6A7818196AB83` (`category_high_id`),
 CONSTRAINT `FK_D7A6A78125326495` FOREIGN KEY (`category_medium_id`) REFERENCES `operator_category_medium` (`id`),
 CONSTRAINT `FK_D7A6A7818196AB83` FOREIGN KEY (`category_high_id`) REFERENCES `operator_category_high` (`id`),
 CONSTRAINT `FK_D7A6A781B596C062` FOREIGN KEY (`category_low_id`) REFERENCES `operator_category_low` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `operator` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 `category_low_id` int(11) DEFAULT NULL,
 `category_medium_id` int(11) DEFAULT NULL,
 `category_high_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `IDX_D7A6A781B596C062` (`category_low_id`),
 KEY `IDX_D7A6A78125326495` (`category_medium_id`),
 KEY `IDX_D7A6A7818196AB83` (`category_high_id`),
 CONSTRAINT `FK_D7A6A78125326495` FOREIGN KEY (`category_medium_id`) REFERENCES `operator_category_medium` (`id`),
 CONSTRAINT `FK_D7A6A7818196AB83` FOREIGN KEY (`category_high_id`) REFERENCES `operator_category_high` (`id`),
 CONSTRAINT `FK_D7A6A781B596C062` FOREIGN KEY (`category_low_id`) REFERENCES `operator_category_low` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


CREATE TABLE `operator_category_medium` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `operator_category_high` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `depositor` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `depositor_id` bigint(20) NOT NULL,
 `name` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
 `amount` decimal(15,4) NOT NULL,
 `deposited` datetime NOT NULL,
 `details` longtext COLLATE utf8_unicode_ci NOT NULL,
 `netsuite_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `depositor_idx` (`depositor_id`),
 KEY `netsuite_id_idx` (`netsuite_id`),
 KEY `deposited_idx` (`deposited`)
) ENGINE=InnoDB AUTO_INCREMENT=62650 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

查詢

DROP temporary TABLE IF EXISTS `depositor_type`;

CREATE temporary TABLE `depositor_type` (
 `depositor_id` bigint(20) NOT NULL,
 `type` varchar(4) NOT NULL,
 amount decimal(20,4) NULL,
 count_deposit int(11) NULL,
 PRIMARY KEY (depositor_id, type),
 KEY type_idx (type),
 KEY amount_idx (amount)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into depositor_type (type, depositor_id )  select distinct 'u',user_rmn as depositor_id from transaction where txn_date between :from and :to and user_rmn is not null  union DISTINCT  select distinct 'a',ad_id as depositor_id from transaction where txn_date between :from and :to  and ad_id is not null union DISTINCT  select distinct 'm',md_id as depositor_id from transaction where txn_date between :from and :to  and md_id is not null union DISTINCT  select distinct 'c',cnf_id as depositor_id from transaction where txn_date between :from and :to  and cnf_id is not null;

update depositor_type set  amount=(select sum(amount) from depositor d where d.depositor_id=depositor_type.depositor_id),  count_deposit=(select count(amount) from depositor d where d.depositor_id=depositor_type.depositor_id) ;

DROP  TABLE IF EXISTS `9bf92fsums`;
CREATE TABLE `9bf92fsums` (  `cnf_id` bigint(20) NOT NULL,
                            `md_id` bigint(20) NOT NULL,
                            `ad_id` bigint(20) NOT NULL,
                            `user_rmn` bigint(20) NOT NULL,
                            `operator_id` int(11) not null,
                            `trans_amount` decimal(20,4) NOT NULL,
                            `incoming_commission` decimal(20,4) NOT NULL,
                            `retailer_commission` decimal(20,4) NOT NULL,
                            `ad_commission` decimal(20,4) NOT NULL,
                            `md_commission` decimal(20,4) NOT NULL,
                            `cnf_commission` decimal(20,4) NOT NULL,
                            `count_trans` int(11) not null,

 PRIMARY KEY (`cnf_id`,`md_id`,`ad_id`,`user_rmn`, `operator_id`),
 KEY `md_id_idx` (`cnf_id`),
 KEY `ad_id_idx` (`ad_id`) USING BTREE,
 KEY `user_rmn_idx` (`user_rmn`) USING BTREE,
 KEY `operator_id_idx` (`operator_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into 9bf92fsums select distinct coalesce(cnf_id,0), coalesce(md_id,0), coalesce(ad_id,0), coalesce(user_rmn,0), operator_id,  sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission,  sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), count(txn_id) as count_trans from transaction  where txn_date between :from and :to  group by coalesce(cnf_id,0), coalesce(md_id,0), coalesce(ad_id,0), coalesce(user_rmn,0), operator_id

select 'User' as type, user_rmn as phone, t.amount,  sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission,  sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s      inner join depositor_type t on s.user_rmn=t.depositor_id and t.type='u'      inner join operator o on s.operator_id=o.id      left join operator_category_high och on (o.category_high_id=och.id)     left join operator_category_medium ocm on (o.category_medium_id=ocm.id)     left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0  group by user_rmn, och.name, ocm.name, ocl.name

select 'AD' as type, ad_id as phone, t.amount,  sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission,  sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s      inner join depositor_type t on s.ad_id=t.depositor_id and t.type='a'      inner join operator o on s.operator_id=o.id      left join operator_category_high och on (o.category_high_id=och.id)     left join operator_category_medium ocm on (o.category_medium_id=ocm.id)     left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0  group by ad_id, och.name, ocm.name, ocl.name

select 'MD' as type, md_id as phone, t.amount,  sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission,  sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s      inner join depositor_type t on s.md_id=t.depositor_id and t.type='m'      inner join operator o on s.operator_id=o.id      left join operator_category_high och on (o.category_high_id=och.id)     left join operator_category_medium ocm on (o.category_medium_id=ocm.id)     left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0  group by md_id, och.name, ocm.name, ocl.name

select 'CNF' as type, cnf_id as phone, t.amount,  sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission,  sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s      inner join depositor_type t on s.cnf_id=t.depositor_id and t.type='c'      inner join operator o on s.operator_id=o.id      left join operator_category_high och on (o.category_high_id=och.id)     left join operator_category_medium ocm on (o.category_medium_id=ocm.id)     left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0  group by cnf_id, och.name, ocm.name, ocl.name

select distinct 'no deposits' as type, null as depositor_id, 0 as sum_dep_amount,  sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission,  sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), 0 as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s      inner join operator o on s.operator_id=o.id      left join operator_category_high och on (o.category_high_id=och.id)     left join operator_category_medium ocm on (o.category_medium_id=ocm.id)     left join operator_category_low ocl on (o.category_low_id=ocl.id) group by 'no deposits', och.name, ocm.name, ocl.name

select distinct 'no transactions' as type, depositor_id, sum(amount) as sum_dep_amount, 0 as trans_amount,  0 as incoming_commission, 0 as retailer_commission, 0 as ad_commission, 0 as md_commission, 0 as cnf_commission, count(d.id) as cnt_depositors, 0 as cnt_txn_id,  'n/a' as operator_category_high, 'n/a' as operator_category_medium, 'n/a' as operator_category_low from depositor d where  depositor_id not in (select user_rmn from 9bf92fsums)  and depositor_id not in (select ad_id from 9bf92fsums)  and depositor_id not in (select md_id from 9bf92fsums)  and depositor_id not in (select cnf_id from 9bf92fsums) group by 'no transactions', depositor_id

我將解決一些優化問題，而不是按要求解決問題。

  • 不要混合字元集——我看到latin1和utf8. 字元集轉換可以防止使用索引。
  • 更小導致更快…
  • 在可行的地方縮小數據類型——你真的需要BIGINT（8 個字節）嗎？“操作員”可能會使用TINYINT UNSIGNED（1 個字節）。等等。
  • 小數（20,4）——不錯的佣金！高達9999999999999999.9999；我想不出任何一種貨幣不會比世界上所有的貨幣都大。它需要 10 個字節。

我想您是在擴充而不是重建“匯總表”？更多討論在這裡。正確使用匯總表是從“報告”中獲取性能的主要方式。

每個語句的時間——也許其中一個（UPDATE帶有子查詢的？）花費了大部分時間。

從您的輸出：

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 328780908334688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328780908336512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328780908335600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328780908333776, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328780908332864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

這 5 個“空閒”（注意引號，InnoDB 上活動事務的定義很明顯，您可以執行START TRANSACTION並且在執行第一個查詢之前您不會處於活動事務中）可能表示 2 之一：您一定已經得到問題消失後的輸出或問題與 InnoDB 事務行寫入/序列化無關。我需要確認後者，但是，根據您的查詢，我懷疑這個問題是一個叫做元數據鎖定SHOW PROCESSLIST的老朋友（它僅與事務處理間接相關）。

當您執行類似 DDL 的語句（CREATE、DROP 等）並且存在（長）時，會發生元數據鎖定問題（您可以通過執行來確認SHOW PROCESSLIST，並在“等待元數據鎖定”狀態下獲得一堆查詢）執行查詢（甚至讀取！）。DDL 必須等待所有讀取完成才能獲取元數據鎖，這反過來又會阻塞所有後續查詢（SELECT 也是如此）。

這些是緩解這些問題的策略：

  • 不要執行 DDL，除非是在低負載下（特別是在長時間執行的事務之外，如類似分析的查詢或備份）。
  • 不要刪除或修改現有表，不要對臨時（僅限會話）或程序專用表進行計算
  • 限制在查詢失敗之前 DDL 可以被阻止的時間量。您可以將它從預設的 1 年(!)減少到，比如說，lock_wait_timeout到幾秒鐘。我們在 Wikipedia 上為線上模式更改執行此操作：https ://phabricator.wikimedia.org/diffusion/OSOF/browse/master/dbtools/osc_host.sh;2cd94501c64236d871a19ceec6173d3f6c5ec197 $132 查詢將失敗，但您可以擷取它並嘗試 2 或在抱怨之前 3 次（比使用該表創建所有查詢的可怕堆積要好）。這是一個非常常見的問題——我們在 enwiki 的 mediawiki 安裝的修訂表上遇到了這個問題，我們計劃引入某種分區來緩解這種情況。

MySQL 的更高版本開始引入元數據鎖監控作為 performance_schema 的一部分，但在過去（並且現在，並非每個機構都有最新版本）調試起來很痛苦。啟用 P_S，如果你還沒有，它 1-4% 的成本值得花在盲目調試問題上的時間。

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

相關問答

Mysql

將 innodb_flush_log_at_trx_commit = 2 與 RDS MultiAZ 一起使用 - 安全嗎？

  • March 17, 2022
檢視問答
Mysql

使用 InnoDB 隨機慢速插入

  • December 29, 2019
檢視問答
Mysql

RDS 實例上的 CPU 使用率單調增加，而查詢量沒有變化

  • October 1, 2018
檢視問答
Mysql

隨著時間的推移，刪除 MySQL 中的舊數據變得越來越慢

  • June 20, 2018
檢視問答
Mysql

MySQL 送出需要時間

  • May 4, 2017
檢視問答
Mysql

在 Amazon RDS 上進行 mysqldump 還原的最快方法是什麼？

  • October 27, 2016
檢視問答