Mysql

MySQL 慢速從視圖中選擇

  • May 19, 2019

我會很感激這個問題的方法，我在 mysql 中有一個彩票系統，這個系統每秒接收很多交易..很多！在允許下注之前，我需要檢查每個數字的總金額，為此我有一個看法：

CREATE ALGORITHM=MERGE DEFINER=user@% SQL SECURITY DEFINER 
VIEW view_todays_bet AS select number, sum(bet_amout) as total_bet from salestable;

有了這個視圖，我得到了這個結果，例如：

Number total_bet

=====  ========

01 1500

05 2000

...

99 20

所以當我有一個新的賭注時，我會從視圖中進行選擇，這就是問題出現的時刻，例如，如果我想知道數字 05 的餘額，我會選擇：

select total_bet from view_todays_bet where number = '05';

如果我直接從主表中進行選擇，salestable我會被阻止，因為有很多來自其他投注的插入，但是從視圖中選擇太慢了。請就這個問題提供一些方法。

謝謝。

這是表的真實結構：標題/詳細資訊表和由兩者連接而成的視圖。

CREATE TABLE `sales_details` (
 `codpais` varchar(2) NOT NULL DEFAULT '',
 `numero` varchar(7) NOT NULL DEFAULT '',
 `verificador` varchar(10) NOT NULL DEFAULT '',
 `codterminal` varchar(8) NOT NULL DEFAULT '',
 `item` int(4) NOT NULL DEFAULT '1',
 `codloteria` varchar(3) NOT NULL DEFAULT '',
 `secuencia` varchar(2) NOT NULL DEFAULT '' COMMENT 'Aqui van los numeros jugados',
 `codjuego` varchar(3) NOT NULL DEFAULT '',
 `numeros` varchar(30) NOT NULL DEFAULT '' COMMENT 'Aqui van los numeros jugados',
 `monto` double NOT NULL DEFAULT '0',
 `numsorteos` int(11) NOT NULL DEFAULT '1',
 `horasorteo` varchar(5) NOT NULL DEFAULT '00:00',
 `codloteria2` varchar(3) NOT NULL DEFAULT '',
 `secuencia2` varchar(2) NOT NULL DEFAULT '',
 `nombrecorto2` varchar(30) NOT NULL DEFAULT '',
 `horasorteo2` varchar(5) NOT NULL DEFAULT '',
 `numerosganadores` varchar(10) NOT NULL DEFAULT '',
 `montopremio` double NOT NULL DEFAULT '0',
 `ganaprimero` double NOT NULL DEFAULT '0',
 `ganasegundo` double NOT NULL DEFAULT '0',
 `ganatercero` double NOT NULL DEFAULT '0',
 `ganacuarto` double NOT NULL DEFAULT '0',
 `porciento` double NOT NULL DEFAULT '0',
 PRIMARY KEY (`codpais`,`numero`,`verificador`,`codterminal`,`item`),
 KEY `codloteria` (`codloteria`),
 KEY `codjuego` (`codjuego`),
 KEY `codloteria2` (`codloteria2`)
) ENGINE=InnoDB;

CREATE TABLE `sales_header` (
 `codpais` varchar(2) NOT NULL DEFAULT '',
 `numero` varchar(7) NOT NULL DEFAULT '',
 `verificador` varchar(10) NOT NULL DEFAULT '',
 `codterminal` varchar(8) NOT NULL DEFAULT '',
 `stan` varchar(6) NOT NULL DEFAULT '',
 `tx` varchar(2) NOT NULL DEFAULT '',
 `cajero` varchar(15) NOT NULL DEFAULT '',
 `codempresa` varchar(2) NOT NULL DEFAULT '',
 `codbase` varchar(3) NOT NULL DEFAULT '',
 `codbanca` varchar(7) NOT NULL DEFAULT '',
 `codgrupo` varchar(2) NOT NULL DEFAULT '',
 `codprovincia` varchar(3) NOT NULL DEFAULT '',
 `codciudad` varchar(2) NOT NULL DEFAULT '',
 `codmunicipio` varchar(4) NOT NULL DEFAULT '',
 `codlocal` varchar(3) NOT NULL DEFAULT '',
 `total` double NOT NULL DEFAULT '0',
 `fechasorteo` date NOT NULL DEFAULT '0001-01-01',
 `fechasistema` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
 `fechapos` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
 `fechacancelacion` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
 `numerocancela` varchar(10) NOT NULL DEFAULT '',
 `terminalcancela` varchar(8) NOT NULL DEFAULT '',
 `turno` int(9) unsigned NOT NULL DEFAULT '0',
 `tasa` double NOT NULL DEFAULT '0',
 `esganador` varchar(1) NOT NULL DEFAULT 'N',
 `fuecobrado` varchar(1) NOT NULL DEFAULT 'N',
 `sorteocerrado` varchar(1) NOT NULL DEFAULT 'N',
 `fechacobrado` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
 `terminalcobrado` varchar(8) NOT NULL DEFAULT '',
 `usuariocobrado` varchar(15) NOT NULL DEFAULT '',
 `verificadorcobrado` varchar(20) NOT NULL DEFAULT '',
 `totalcobrado` double NOT NULL DEFAULT '0',
 `estatus` varchar(1) NOT NULL DEFAULT 'A' COMMENT 'A- Activo, R-Reversado, N-Anulada',
 `numrecarga` varchar(30) NOT NULL DEFAULT '',
 `autoincrecarga` varchar(4) NOT NULL DEFAULT '',
 `sincronizada` varchar(1) NOT NULL DEFAULT 'N',
 `reimpreso` varchar(1) NOT NULL DEFAULT 'N',
 `porciento` double NOT NULL DEFAULT '0',
 `bk` varchar(1) NOT NULL DEFAULT 'N',
 `codcliente` varchar(5) NOT NULL DEFAULT '',
 `calcporciento` varchar(1) NOT NULL DEFAULT 'T',
 PRIMARY KEY (`codpais`,`numero`,`verificador`,`codterminal`),
 KEY `stan` (`stan`),
 KEY `tx` (`tx`),
 KEY `fechasorteo` (`fechasorteo`),
 KEY `fechasistema` (`fechasistema`),
 KEY `estatus` (`estatus`),
 KEY `codconsorcio` (`codbase`),
 KEY `codempresa` (`codempresa`),
 KEY `codbanca` (`codbanca`),
 KEY `codgrupo` (`codgrupo`),
 KEY `bk` (`bk`),
 KEY `codcliente` (`codcliente`),
 KEY `esganador` (`esganador`),
 KEY `numrecarga` (`numrecarga`)
) ENGINE=InnoDB;

CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_todays_bet` AS select `vd`.`codpais` AS `codpais`,`vd`.`codloteria` AS `codloteria`,`vd`.`codjuego` AS `codjuego`,sum(`vd`.`monto`) AS `monto`,`fnNumeroCRC`(`vd`.`numeros`) AS `crc` from (`sales_details` `vd` join `sales_header` `vh`) where ((`vd`.`codpais` = `vh`.`codpais`) and (`vd`.`numero` = `vh`.`numero`) and (`vd`.`verificador` = `vh`.`verificador`) and (`vd`.`codterminal` = `vh`.`codterminal`) and (`vh`.`fechasorteo` = date_format(sysdate(),'%Y-%m-%d')) and (`vh`.`estatus` = 'A')) 
group by 1,2,3,5 
order by 1,2,3,5

這裡發生了一些事情：

  • 您的CREATE VIEW語句包含一個SUM()函式。即使您ALGORITHM被指定為MERGE，它也確實會像這裡TEMPTABLE的文件中所討論的那樣：

如果不能使用 MERGE 算法，則必須使用臨時表。如果視圖包含以下任何構造，則不能使用 MERGE：聚合函式（SUM()、MIN()、MAX()、COUNT() 等）

  • 所以我們使用臨時表。我懷疑由於 TEMPTABLE 算法的這種好處，您沒有看到“阻塞”：

顯式選擇 TEMPTABLE 的一個原因是，可以在創建臨時表之後以及在它用於完成語句處理之前釋放基礎表上的鎖。這可能會導致比 MERGE 算法更快的鎖定釋放，因此使用該視圖的其他客戶端不會被阻塞太久。

  • 但是，TEMPTABLE算法的問題是它們沒有/使用索引$$ src $$:

視圖處理未優化：

無法在視圖上創建索引。

索引可用於使用合併算法處理的視圖。但是，使用 temptable 算法處理的視圖無法利用其基礎表上的索引（儘管可以在生成臨時表期間使用索引）。

所以現在我們需要知道你的表的底層結構為什麼直接查詢阻塞：

  • 如果不是 InnoDB，考慮改用行級鎖代替 MyISAM 的表鎖
  • number如果 ( , )上沒有索引，bet_amount請考慮創建一個。

如果上述幾點已經完成，並且由於某種原因您仍然阻塞了直接針對該salestable表的查詢，那麼您可以通過創建觸發器來建立一個窮人的物化視圖，該觸發器使用每個數字和執行總計修改一個臨時表.

慢視圖

用替代解決方案解決

一種功能替代方法是將視圖查詢封裝在一個過程中，並帶有參數傳遞。這是一個簡單的解決方案，用於改善多個表之間的多個連接查詢的慢 VIEWS 問題。

DELIMITER ;;
CREATE PROCEDURE `SP_QUERY_VIEW_WITH_PARAMETERS`(IN p_having VARCHAR(300))
   COMMENT 'Executes the statement'
BEGIN
 SET @v_having = p_having;
 SET @v_sql=CONCAT('SELECT 
                   id    AS id_emp  ,
                   user  AS emp_name,
                   .
                   .
                   .
                   FROM table1
                   UNION ALL
                   SELECT 
                   idtifier_us    AS id_emp  ,
                   description    AS emp_name,
                   .
                   .
                   .
                   FROM table2');
SET @v_sql2 = CONCAT(@v_sql,@v_having);
 PREPARE stmt FROM @v_sql2;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;

CALL `SP_QUERY_VIEW_WITH_PARAMETERS`('having id_emp=63 and emp_name like ''VANDERLEI%'' and created_at between ''2019-05-01'' and ''2019-05-17''  ');

此替代方案在執行查詢時保留了按索引進行的優化

注意：“where”也可以使用，但查詢更簡單

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

相關問答

Mysql

加入兩個 Mysql VIEW 需要很長時間

  • November 21, 2014
檢視問答
Mysql

創建一個跨越 3 個表的 VIEW

  • February 19, 2013
檢視問答
Mysql

MySQL 能否在 10GB 網路儲存上使用 raid 與本地 HDD 表現得更好？

  • May 16, 2022
檢視問答
Mysql

如何提高mysql中頻繁更新表的性能？

  • March 23, 2022
檢視問答
Mysql

MySQL - 簡單更新非常慢並且導致大量伺服器的高負載平均

  • March 16, 2022
檢視問答
Mysql

mysql 簡單更新 4000 萬和 128GB RAM 花費太多時間

  • March 3, 2022
檢視問答