MySQL資料庫優化（五）——MySQL查詢優化

一、mysql查詢類型（預設查詢出所有資料列）

1、内連接配接 

     預設多表關聯查詢方式，查詢出兩個表中所有字段；可省略inner join 關鍵字

2、外連接配接 查詢出某一張表中的所有資料

（1）左連接配接

      查詢出第一張表的所有字段

（2）右連接配接

 查詢出第二張表的所有字段，表一比對資料為空的傳回null

3、子連接配接

--内連接配接  查詢出bookID=book類型ID的記錄    
SELECT tb.bookName,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id;  
  
--外連接配接（左連接配接）  
SELECT  tb.bookName,tby.bookTypeName FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id where ......;  
--外連接配接（右連接配接）  
SELECT  tb.bookName,tby.bookTypeName FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id where ......;  
  
--子查詢  
SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);  
SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);  
SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);  
SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);  
SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);  
SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);  
SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);      

二、查詢優化思路

1、為什麼會慢？

     在嘗試做查詢優化之前，得明白是什麼讓查詢變慢？如果把查詢看作一個由n個子任務組成的任務，随着子任務增多，sql關聯查詢也增多；優化查詢，實際上就是優化這n個子任務，要麼消除一些子任務，要麼減少子任務執行次數。

2、有哪些子任務？

     mysql執行一個查詢一般有這麼幾個流程：用戶端發送查詢語句到服務端——>伺服器解析查詢語句——>生成執行計劃——>執行查詢；其中執行時整個生命周期最重要的階段，其中包括對資料庫引擎的調用、排序、分組的資料處理過程。

三、優化方向

1、優化查詢資料

     低效的查詢基本原因：通路請求的資料太多，不可避免的需要進行大量篩選工作；

     錯誤嘗試：select * from t_user t inner join t_role r inner join t_permission p where .....

     錯誤原因：使用内連接配接，查詢出三個表中的所有資料列

     正确方式：select t.name,r.rolename,p.pname from t_user t inner join t_role r inner join t_permission p where

2、切分查詢

      分而治之，将複雜的查詢切分成小查詢，每次查詢隻傳回一小部分結果

      錯誤嘗試：select * from t_user t where createData>DATE_SUB(NOW(),INTERVAL 3 MONTH)

                       delete from t_user t where createData>DATE_SUB(NOW(),INTERVAL 3 MONTH)

     錯誤原因：當user表中資料量巨大時，一次性查詢或删除表中大量資料均會導緻等待停頓；

     正确方式：select * from t_user t  where createData>DATE_SUB(NOW(),INTERVAL 3 MONTH)  limit 0，1000；

                     先查詢表中符合條件的前一1000條資料；

3、分解關聯查詢

      錯誤嘗試：select t.name,r.rolename,p.pname from t_user t inner join t_role r inner join t_permission p where

      錯誤原因：當user表中資料量巨大時，一次性查詢或删除表中大量資料均會導緻等待停頓；

      正确方式：select * from t_user where  t.age=10;

                       select t. rolename from t_user where ...

                       select t. pname from t_user where ...

       分解關聯查詢表面上好像原本1個sql就幹完的事，現在非得由3個sql去完成，實際上它比關聯查詢更有優勢：

（1）提高緩存效率:對于第一條查詢中age=10這條記錄的所有字段已經緩存在mysql中，供第二個查詢語句使用

（2）減少單個查詢間的鎖競争

（3）減少備援字段的查詢     

四、總結

     在平時的應用中，尤其在在java開發提供了良好的資料持久化架構，對于mysql的查詢優化并未過分關系，并且在使用sql執行查詢時，可能也經常使用到多表關聯查詢，在使用這些sql拼接的過程中，對于sql優化不能不知曉，在應對高并發問題中，除了在web伺服器前做負載、平行擴充等措施之外，資料庫高并發的解決方案也與其并重，而這一個一個sql就是應多高并發的優化基礎，不容小觑。

非學，無以緻疑；非問，無以廣識