MySQL優化一例

来源:http://www.cnblogs.com/digdeep/archive/2016/08/18/5783591.html
-Advertisement-
Play Games

在老系統中該函數調用一次需要話20多秒到30秒左右。 拿到sql之後，首先要確定思路。不能著急這下手。 1. 首先查看各個表的數據量： select count(*) from xxx; 發現只有 a21 的數據量達到了十幾萬，其他表數據量都比較小。所以重點是 a21表，仔細閱讀了一遍函數的定義，發 ...


DELIMITER $$

USE `xxx`$$

DROP FUNCTION IF EXISTS `F_getBuluDates`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `F_getBuluDates`(`PuserId` INT(11)) RETURNS VARCHAR(3000) CHARSET gbk
BEGIN
DECLARE buluDate,buluDateTime,LiWaibuluDates,nowdate,bldate VARCHAR(3000) DEFAULT "";
DECLARE rtMsg,RuserName,maxRiZhiDate,btjrqs,blyqrq,tmpdate,yxblsj VARCHAR(255) DEFAULT "";
DECLARE Ra51count INT(30);
DECLARE RbeginDate,RendDate,RtmpDate VARCHAR(2550);
DECLARE u,gqcount,a21Count INT(30) DEFAULT 0;
DECLARE allReadRows,readRows,mySYL,blCount,recordCount,btjryCount,btjrqCount,yxblts,i INT(11) DEFAULT 0;
SELECT a5204,a5205 INTO yxblts,yxblsj FROM a52 WHERE a5218="xxxxxxx" LIMIT 0,1;    

SELECT username INTO RuserName FROM USER WHERE id=PuserId;

SELECT COUNT(id) INTO btjryCount FROM a52 WHERE a5214=PuserId AND a5218='xxxxx';   
IF btjryCount=0 THEN    

SELECT GROUP_CONCAT(a5215) INTO btjrqs FROM a52 WHERE  a5218='btjrq';   
SET tmpdate=CURDATE();
SET nowdate=DATE_SUB(CURDATE(),INTERVAL yxblts DAY);

REPEAT 
SELECT MAX(a2104) INTO maxRiZhiDate FROM a21 WHERE issubmit='y' AND a2104<tmpdate AND FIND_IN_SET(a2104,bldate)=0; 
SET tmpdate=maxRiZhiDate;
IF tmpdate>=nowdate THEN 
	IF F_isWorkDay(maxRiZhiDate)=1 OR ISNULL(maxRiZhiDate) THEN
		IF NOT ISNULL(maxRiZhiDate) AND LENGTH(maxRiZhiDate)>0 THEN
			IF FIND_IN_SET(maxRiZhiDate,btjrqs)=0 THEN    
				SELECT COUNT(id) INTO recordCount FROM a21 WHERE a2104=maxRiZhiDate AND creatorid=PuserId;  
				IF recordCount=0 THEN  
					IF DAYOFWEEK(maxRiZhiDate)=6 THEN
						SET buluDateTime=CONCAT(FROM_DAYS(TO_DAYS(maxRiZhiDate)+2+yxblts)," ",yxblsj);   
					ELSEIF DAYOFWEEK(maxRiZhiDate)=7 THEN
						SET buluDateTime=CONCAT(FROM_DAYS(TO_DAYS(maxRiZhiDate)+1+yxblts)," ",yxblsj);   
					ELSE
						SET buluDateTime=CONCAT(FROM_DAYS(TO_DAYS(maxRiZhiDate)+yxblts)," ",yxblsj);   
					END IF;

					IF(CURRENT_TIMESTAMP()<buluDateTime) THEN  
						SET buluDate=maxRiZhiDate;
					END IF;
				END IF;                     
			END IF;  
		END IF;  

	END IF;
	IF CHAR_LENGTH(buluDate)>0 THEN 
		IF CHAR_LENGTH(bldate)>0 THEN
			SET bldate=CONCAT(buluDate,",",bldate);
		ELSE
			SET bldate=buluDate;
		END IF;
	END IF;
END IF;

SET buluDate='';
UNTIL tmpdate<=nowdate
END REPEAT;



SELECT COUNT(id) INTO gqcount FROM a52 WHERE a5216=CURDATE() AND a5218='xxxxxx';
IF gqcount>0 THEN
SELECT a5215 INTO blyqrq FROM a52 WHERE a5216=CURDATE() AND a5218='xxxxxx';  
SELECT COUNT(id) INTO a21Count FROM a21 WHERE a2104=blyqrq AND creatorid=PuserId;
IF a21Count=0 THEN
IF FIND_IN_SET(blyqrq,bldate)=0 THEN 
IF CHAR_LENGTH(bldate)>0 THEN
SET bldate=CONCAT(blyqrq,",",bldate);
ELSE
SET bldate=blyqrq;
END IF;
END IF;
END IF;
END IF;




IF ISNULL(btjrqs) THEN
SET btjrqs="";
END IF;

SELECT GROUP_CONCAT(a5108) INTO LiWaibuluDates FROM a51 WHERE a5110='y' AND F_ifInSet(a5108,btjrqs)=0 AND a5106 LIKE RuserName AND a5112='否' AND CONCAT(a5108," ",a5113)<CURRENT_TIMESTAMP() AND CURRENT_TIMESTAMP()<CONCAT(a5114," ",a5113);
IF NOT ISNULL(LiWaibuluDates) AND LENGTH(TRIM(LiWaibuluDates))>0 THEN 
IF CHAR_LENGTH(bldate)>0 THEN
SET bldate=CONCAT(LiWaibuluDates,",",bldate);
ELSE
SET bldate=LiWaibuluDates;
END IF;
END IF;       


SELECT COUNT(id) INTO RA51count FROM a51 WHERE a5110='n' AND F_getid1value2(a5105,1)=PuserId; 
IF RA51count>0 THEN 
SET u=0;
WHILE u< RA51count DO           
SELECT a5108,a5109 INTO RbeginDate,RendDate FROM a51 WHERE a5110='n' AND F_getid1value2(a5105,1)=PuserId ORDER BY id LIMIT u,1;
SET RtmpDate=RbeginDate;
WHILE RtmpDate<=RendDate DO

IF FIND_IN_SET(RtmpDate,bldate)>0 THEN 
SET bldate=F_removeFromSet(RtmpDate,bldate);
END IF;
SET RtmpDate=DATE_ADD(RtmpDate, INTERVAL 1 DAY); 
END WHILE;
SET u=u+1;
END WHILE;                      
END IF;
              

 

  
END IF;  



RETURN bldate;

END$$

DELIMITER ;

在老系統中該函數調用一次需要話20多秒到30秒左右。

拿到sql之後，首先要確定思路。不能著急這下手。

1. 首先查看各個表的數據量：

select count(*) from xxx;

發現只有 a21 的數據量達到了十幾萬，其他表數據量都比較小。所以重點是 a21表，仔細閱讀了一遍函數的定義，發現涉及 到 a21 的有一處迴圈：

REPEAT 
SELECT MAX(a2104) INTO maxRiZhiDate FROM a21 WHERE issubmit='y' AND a2104<tmpdate AND FIND_IN_SET(a2104,bldate)=0;

很顯然使用了 find_in_set 函數，所以改語句無法使用索引。所以想要簡單的通過增加索引來解決問題，應該是行不通的。

這樣就定位到了函數運行慢的問題。結合業務理解改函數的含義。

為了使用索引，我們需要去掉 find_in_set 函數，理解了業務和原函數的功能之後，對改函數的 repeat 迴圈部分進行了重寫：

DELIMITER $$

USE `oa`$$

DROP FUNCTION IF EXISTS `F_getBuluDates_inner`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `F_getBuluDates_inner`(`PuserId` INT(11), tmpdate VARCHAR(32), nowdate VARCHAR(32)) RETURNS VARCHAR(3000) CHARSET gbk
BEGIN
DECLARE buluDate,buluDateTime,LiWaibuluDates,bldate VARCHAR(3000) DEFAULT "";
DECLARE rtMsg,RuserName,maxRiZhiDate,btjrqs,blyqrq,yxblsj VARCHAR(255) DEFAULT "";
DECLARE Ra51count INT(30);
DECLARE RbeginDate,RendDate,RtmpDate VARCHAR(2550);
DECLARE u,gqcount,a21Count INT(30) DEFAULT 0;
DECLARE allReadRows,readRows,mySYL,blCount,recordCount,btjryCount,btjrqCount,yxblts,i INT(11) DEFAULT 0;
DECLARE no_more_data INT DEFAULT 0;

DECLARE my_cursor CURSOR FOR SELECT DISTINCT a2104 FROM a21 WHERE issubmit='y' AND a2104 < tmpdate AND a2104 >= nowdate ORDER BY a2104 DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_data = 1;

SELECT a5204,a5205 INTO yxblts,yxblsj FROM a52 WHERE a5218="gzrz" LIMIT 0,1;    

SELECT username INTO RuserName FROM USER WHERE id=PuserId;

SELECT COUNT(id) INTO btjryCount FROM a52 WHERE a5214=PuserId AND a5218='xxxxx';   
IF btjryCount=0 THEN    

SELECT GROUP_CONCAT(a5215) INTO btjrqs FROM a52 WHERE  a5218='xxx';   

OPEN my_cursor;
FETCH my_cursor INTO maxRiZhiDate;

REPEAT 
    IF F_isWorkDay(maxRiZhiDate)=1 OR ISNULL(maxRiZhiDate) THEN
        IF NOT ISNULL(maxRiZhiDate) AND LENGTH(maxRiZhiDate)>0 THEN
            IF FIND_IN_SET(maxRiZhiDate,btjrqs)=0 THEN    
                SELECT COUNT(id) INTO recordCount FROM a21 WHERE a2104=maxRiZhiDate AND creatorid=PuserId;  
                IF recordCount=0 THEN  
                    IF DAYOFWEEK(maxRiZhiDate)=6 THEN
                        SET buluDateTime=CONCAT(FROM_DAYS(TO_DAYS(maxRiZhiDate)+2+yxblts)," ",yxblsj);   
                    ELSEIF DAYOFWEEK(maxRiZhiDate)=7 THEN
                        SET buluDateTime=CONCAT(FROM_DAYS(TO_DAYS(maxRiZhiDate)+1+yxblts)," ",yxblsj);   
                    ELSE
                        SET buluDateTime=CONCAT(FROM_DAYS(TO_DAYS(maxRiZhiDate)+yxblts)," ",yxblsj);   
                    END IF;

                    IF(CURRENT_TIMESTAMP()<buluDateTime) THEN  
                        SET buluDate=maxRiZhiDate;
                    END IF;
                END IF;                     
            END IF;  
        END IF;  

    END IF;
    IF CHAR_LENGTH(buluDate)>0 THEN 
        IF CHAR_LENGTH(bldate)>0 THEN
            SET bldate=CONCAT(buluDate,",",bldate);
        ELSE
            SET bldate=buluDate;
        END IF;
    END IF;

FETCH my_cursor INTO maxRiZhiDate;
SET buluDate='';
UNTIL no_more_data = 1
END REPEAT;


SELECT COUNT(id) INTO gqcount FROM a52 WHERE a5216=CURDATE() AND a5218='xxx';
IF gqcount>0 THEN
SELECT a5215 INTO blyqrq FROM a52 WHERE a5216=CURDATE() AND a5218='xxxxxxxxxx';  
SELECT COUNT(id) INTO a21Count FROM a21 WHERE a2104=blyqrq AND creatorid=PuserId;
IF a21Count=0 THEN
IF FIND_IN_SET(blyqrq,bldate)=0 THEN 
IF CHAR_LENGTH(bldate)>0 THEN
SET bldate=CONCAT(blyqrq,",",bldate);
ELSE
SET bldate=blyqrq;
END IF;
END IF;
END IF;
END IF;

IF ISNULL(btjrqs) THEN
SET btjrqs="";
END IF;

SELECT GROUP_CONCAT(a5108) INTO LiWaibuluDates FROM a51 WHERE a5110='y' AND F_ifInSet(a5108,btjrqs)=0 AND a5106 LIKE RuserName AND a5112='否' AND CONCAT(a5108," ",a5113)<CURRENT_TIMESTAMP() AND CURRENT_TIMESTAMP()<CONCAT(a5114," ",a5113);
IF NOT ISNULL(LiWaibuluDates) AND LENGTH(TRIM(LiWaibuluDates))>0 THEN 
IF CHAR_LENGTH(bldate)>0 THEN
SET bldate=CONCAT(LiWaibuluDates,",",bldate);
ELSE
SET bldate=LiWaibuluDates;
END IF;
END IF;       

SELECT COUNT(id) INTO RA51count FROM a51 WHERE a5110='n' AND F_getid1value2(a5105,1)=PuserId; 
IF RA51count>0 THEN 
SET u=0;
WHILE u< RA51count DO           
SELECT a5108,a5109 INTO RbeginDate,RendDate FROM a51 WHERE a5110='n' AND F_getid1value2(a5105,1)=PuserId ORDER BY id LIMIT u,1;
SET RtmpDate=RbeginDate;
WHILE RtmpDate<=RendDate DO

IF FIND_IN_SET(RtmpDate,bldate)>0 THEN 
SET bldate=F_removeFromSet(RtmpDate,bldate);
END IF;
SET RtmpDate=DATE_ADD(RtmpDate, INTERVAL 1 DAY); 
END WHILE;
SET u=u+1;
END WHILE;                      
END IF;      

END IF;  

RETURN bldate;

END$$

DELIMITER ;

然後為了讓新的 sql 使用索引：

DECLARE my_cursor CURSOR FOR

SELECT DISTINCT a2104 FROM a21 WHERE issubmit='y' AND a2104 < tmpdate AND a2104 >= nowdate ORDER BY a2104 DESC;

新增了索引：

add index issubmit_a2104(issubmit,a2104);

最終的提供給外部的調用函數：

DELIMITER $$
CREATE FUNCTION F_getBuluDates_new(`PuserId` INT(11)) RETURNS VARCHAR(3000) CHARSET gbk
BEGIN
    DECLARE bldate VARCHAR(3000) DEFAULT "";
    DECLARE tmpdate,nowdate VARCHAR(32) DEFAULT "";
    DECLARE yxblts INT;  
    SELECT a5204 INTO yxblts FROM a52 WHERE a5218="gzrz" LIMIT 0,1;  
    SET tmpdate = CURDATE();
    SET nowdate = DATE_SUB(tmpdate,INTERVAL yxblts DAY);  
    SET bldate = F_getBuluDates_inner(PuserId,tmpdate,nowdate);   
    RETURN bldate;
END
$$

測試效果：

mysql> select F_getBuluDates_new(10687);
+---------------------------------------------------------------------------------------------------------------+
| F_getBuluDates_new(10687)                                                                                    |
+---------------------------------------------------------------------------------------------------------------+
| 2016-08-04,2016-08-05,2016-08-08,2016-08-09,2016-08-10,2016-08-11,2016-08-12,2016-08-15,2016-08-16,2016-08-17 |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (1.10 sec)

mysql> select F_getBuluDates(10687);
+---------------------------------------------------------------------------------------------------------------+
| F_getBuluDates(10687)                                                                                         |
+---------------------------------------------------------------------------------------------------------------+
| 2016-08-04,2016-08-05,2016-08-08,2016-08-09,2016-08-10,2016-08-11,2016-08-12,2016-08-15,2016-08-16,2016-08-17 |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (13.10 sec)

性能提升了 11 倍多。客戶對效果很滿意。

新的執行計劃：

mysql> explain SELECT DISTINCT a2104 FROM a21 WHERE issubmit='y' AND a2104 < '2016-08-16' AND a2104 >= '2016-08-10' ORDER BY a2104 DESC;
+----+-------------+-------+-------+----------------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys        | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | a21   | range | a2104,issubmit_a2104 | issubmit_a2104 | 516     | NULL |  834 | Using where; Using index |
+----+-------------+-------+-------+----------------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

總結：

從原函數實現來看，在數量小時，還是可以的。但是一旦數據量大了，就會出現性能問題。主要問題是在 where 條件中使用了太多的自定義函數，而且這個函數的參數還是表的數據列。
導致無法使用索引。無法使用索引就會進行全表掃描，所以數據量大時會導致性能問題。

 

 

 

 

 

 

 

　　


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Install MySQL on Mac
    1. 可參考此文章：http://www.cnblogs.com/macro-cheng/archive/2011/10/25/mysql-001.html 2. 目前MySQL（我用的mysql 5.7.14）已經預設是utf-8編碼； 3. ...
  • 淺析MySQL基於ROW格式的二進位日誌
    上文分析的二進位日誌實際上是基於STATEMENT格式的，下麵我們來看看基於ROW格式的二進位日誌，畢竟，兩者對應的binlog事件類型也不一樣，同時，很多童鞋反映基於ROW格式的二進位日誌無法查到原生的DML語句，關於這個問題，其實官方也給出瞭解決方案，下麵，將一一揭曉。 首先，來幾條測試數據 首 ...
  • DB操作-用批處理執行Sql語句
    用批處理在命令行狀態下調用查詢分析器來執行這個sql文件里的語句。下麵就是sqlserver幫助里對於查詢分析器(isqlw)命令行參數的解釋：isqlw 實用工具（SQL 查詢分析器）使您得以輸入 Transact-SQL 語句、系統存儲過程和腳本文件。通過設置快捷方式或創建批處理文件，可以啟動預 ...
  • MySQL5.7 windows二進位安裝
    介紹 1.下載解壓 下載地址：http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.14-winx64.zip 將下載的mysql解壓重命名放在C:\Program Files\MySQL\MySQL Server 5.7” 2.創建目錄 創建 ...
  • SQL Server 使用OPENROWSET訪問ORACLE遇到的各種坑總結
    在SQL Server中使用OPENROWSET訪問ORACLE資料庫時，你可能會遇到各種坑，下麵一一梳理一下你會遇到的一些坑。 1：資料庫沒有開啟"Ad Hoc Distributed Queries"選項，那麼你就會遇到下麵坑。 SELECT TOP 10 * FROM OPENROWSET('... ...
  • MySQL5.6 windows msi安裝介紹
    一、功能介紹 1.MySQL Servers 該功能是mysql主要的服務，也是必須安裝的功能。 2.Mysql WorkBench 這個是mysql的客戶端工具，可以單獨下載安裝程式安裝。 3.Mysql Notifier 該功能可以控制mysql啟動，安裝了該功能會在電腦右下角的圖標中有控制my ...
  • Mysql 自定義隨機字元串
    前幾天在開發一個系統，需要用到隨機字元串，但是mysql的庫函數有沒有直接提供，就簡單的利用現有的函數東拼西湊出隨機字元串來.下麵簡單的說下實現當時. 1.簡單粗暴. 上訴示例產生的是：6位長度的隨機字元串. 函數解釋: rand() :產生 0-1之間的小數,簡稱種子.rand()*25 產生的數 ...
  • MySQL binlog中的事件類型
    MySQL binlog記錄的所有操作實際上都有對應的事件類型的，譬如STATEMENT格式中的DML操作對應的是QUERY_EVENT類型，ROW格式下的DML操作對應的是ROWS_EVENT類型。 首先，看看源碼中定義的事件類型 源碼位置：mysql-5.7.14/libbinlogevents ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類