mysql查詢詳解

標籤：mysql查詢過程   查詢快取   命中率   

mysql查詢詳解

                                         ——以下內容摘自馬哥教育課堂

SELECT

SELECT查詢流程

用戶端SELECT語句<==>查詢快取--緩衝未命中-->解譯器--前置處理器-->解析樹（多條訪問路徑）

-->查詢最佳化工具（擇優選擇路徑）-->查詢執行計畫-->查詢執行引擎--API調用儲存引擎--資料-->查詢執行引擎返回結果並使用雜湊儲存於查詢快取

select now();這是無需查詢快取的，查詢當前緩衝

多台MYSQL伺服器，如何提高MYSQL的快取命中率

前端程式可使用一致性雜湊演算法，目的使同樣的SELECT語句將發往同一台MYSQL伺服器

也可引入第三方的緩衝來緩衝SELECT的執行結果而非使用MYSQL自身的緩衝。程式中可以指明先到memcached中查詢SELECT語句是否有緩衝結果，沒有則與MYSQL伺服器做互動。引入多台memcached時，同樣需要使用一致性雜湊演算法來保證SELECT語句的命中

如果業務規模繼續增大，可以使用如下架構

        --快取服務器

應用程式--MYSQL讀寫分離伺服器（自行開發，使用一致性雜湊）--MYSQL一主多從伺服器架構

SQL語句格式

SELECT ... FROM ... ORDER BY ...

SELECT ... FROM ... GROUP BY ... HAVING ...

SELECT ... FROM ... WHERE ...

SELECT ... FROM ... HAVING ...

SELECT語句完整執行流程

SELECT ... FROM ... WHERE ... GROUP BY ...  HAVING ... ORDER BY ... SELECT 欄位 LIMIT ...

WHERE是選擇，“SELECT欄位”是投影

===

MYSQL單表查詢——SELECT詳解

HELP SELECT

    DISTINCT：資料去重複

        SELECT DISTINCT Gender FROM students;

    SQL_CACHE：顯示指定儲存查詢結果於緩衝之中

    SQL_NO_CACHE：顯示指定儲存查詢結果不予緩衝

        SELECT的結果符合緩衝條件即會緩衝，否則，不予緩衝

            查詢快取有相關參數

            SHOW GLOBAL VARIABLES LIKE ‘query%‘;

                query_cache_type  ON：查詢快取功能開啟

                query_cache_type  DEMAND：由命令中指定是否要緩衝，SQL_CACHE才會緩衝

                query_cache_size  16M：預設，可以引入memcached

        快取命中率：查詢叫用次數/查詢總次數

            SHOW GLOBAL STATUS LIKE ‘Qcache%‘;查詢叫用次數

                Qcache_hits 2;

            SHOW GLOBAL STATUS ‘Com_se%‘;查詢總次數

                Com_select  42;

            查詢快取需要預熱後才能提升命中率

    欄位顯示別名：col1 AS alias1，僅顯示時修改

        SELECT Name AS StuName FROM students;

    WHERE子句，指明過濾條件以實現選擇的功能；布爾型

        算術操作符：+,-,*,/,%

        比較操作符：=,!=,<>,<=>；空值安全比較>,>=,<,<=

        SELECT Name,Age FROM students WHERE Age+30 > 50;

        

        BETWEEN 下限 AND 上限

        IN (元素1,元素2)

        SELECT Name,Age FROM students WHERE Age IN (18,100)

    

        IS NULL/IS NOT NULL：判斷取值是否為空白

        SELECT Name,ClassID FROM students WHERE ClassID IS NULL;

        SELECT Name,ClassID FROM students WHERE ClassID = NULL;（錯誤）

        

        LIKE:模糊比對，可以使用萬用字元%表示任意多個字元,_任意單個字元

        RLIKE：不熟練不用

        REGEXP:不熟練不用，匹配字串可使用Regex書寫入模式

        

        邏輯操作符：NOT, AND, OR, XOR

        

    GROUP:根據指定的條件把查詢結果進行“分組”以用於“彙總”運算

        avg(),max(),min(),count(),sum()

        SELECT avg(Age),Gender FROM students GROUP BY Gender;

        按性別分組，那麼每組的話有若干個人，計算平均年齡

        

        SELECT avg(Age) as AAge,Gender FROM students GROUP BY Gender HAVING AAge>20;

        只顯示平均年齡>20分組

        

        SELECT count(StuID) AS NumberOfStu FROM students GROUP BY ClassID;

        SELECT count(StuID) AS NumberOfStu,ClassID FROM students GROUP BY ClassID;

        統計每個班級多少個同學        

        SELECT count(StuID) AS NumberOfStu,ClassID FROM students GROUP BY ClassID HAVING NumberOfStu>2;

        統計每個班級多少個同學，並只顯示班級人數2個以上的

        HAVING: 對分組彙總運算後的結果指定過濾條件；

        

    ORDER BY：根據指定的欄位對查詢結果進行排序；

        升序：ASC，降序：DESC

        SELECT count(StuID) AS NumberOfStu,ClassID FROM students GROUP BY ClassID HAVING NumberOfStu>2 ORDER BY NumberOfStu [DESC];

    LIMIT [[offset,]row_count]: 對顯示的結果限制行數

        SELECT Name,Age FROM students ORDER BY Age DESC LIMIT 10;

        SELECT Name,Age FROM students ORDER BY Age DESC LIMIT 9,10;去掉前面9個後再取10個

    

    FOR UPDATE：對查詢結果中的資料施加“寫鎖”，“獨佔鎖定”，其他使用者無法讀寫

    LOCK FOR SHARE MODE：對查詢結果中的資料施加“讀鎖”，其他使用者只能讀

    練習：54min

    

    

    

多表查詢

交叉串連：笛卡爾乘積，最消耗資源。通常表間串連是兩表之間按照指定欄位的關係進行串連

    SELECT * FROM students,teachers;

    students表行數*teachers表行數

    

等值串連：最常用的方式

    笛卡爾乘積的串連其實沒什麼意義，那怎樣串連才是有意義的呢？students表中的TeacherID和teachers表中的TID代表的都是老師ID，是相同意義的，所以可以根據此欄位進行表串連

    SELECT * FROM students,teachers WHERE students.TeacherID=teachers.TID;

    

    此時，可以只顯示特定欄位形成對應關係，尋找某同學對應的老師

    SELECT s.Name AS StuName,t.Name AS TeaName FROM students AS s,teachers AS t WHERE s.TeacherID=t.TID;    表也可以取別名

    EXPLAIN SELECT s.Name AS StuName,t.Name AS TeaName FROM students AS s,teachers AS t WHERE s.TeacherID=t.TID\G

    

    SELECT * FROM students;

    SELECT * FROM classes;這兩個表都有ClassID欄位，綜合這兩個表查詢學生和班級名的對應關係

    SELECT s.Name,c.Class FROM students AS s,classes AS c WHERE s.ClassID=c.ClassID;

除此之外，還有不等值串連，自然串連。以上兩者加上等值串連三者稱為內串連

外串連

左外串連：左表等值串連右表時，如果左表某行資料對應右表的欄位為空白，那麼也要顯示左表的該行資料

SELECT s.Name,c.Class FROM students AS s,classes AS c WHERE s.ClassID=c.ClassID;即使沒有班級的學生也要顯示其名字，而不是不顯示

FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col

SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c WHERE s.ClassID=c.ClassID;最後結果包含

****    NULL

****    NULL

右外串連：左表等值串連右表時，如果右表某行資料對應左表的欄位為空白，那麼也要顯示右表的該行資料

FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col

SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c WHERE s.ClassID=c.ClassID;

NULL    ****

NULL    ****

全外串連：結合左外串連和右外串連

NULL    ****

NULL    ****

****    NULL

****    NULL

自串連，用表中某欄位和另一欄位的等值關係建立串連

SELECT s.Name,t.Name FROM students AS s,students AS t WHERE s.TeacherID=t.StuID;

子查詢：基於某語句的查詢結果重新查詢，類似視圖

不建議使用，因為MYSQL子查詢的實現不佳

=

用在WH ERE子句中的子查詢

（1）用於比較運算式的子查詢：子查詢僅能返回單個值；

查詢大於平均年齡的學生

SELECT Name,Age FROM students WHERE Age>(SELECT  avg(Age) FROM students;)

EXPLAIN SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students;)\G

（2）用在IN中的子查詢：子查詢應該返回一個或多個值構成列表；

老師年齡和學生年齡相同的

SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);

（3）用於EXISTS

=

用於FROM子句中的子查詢，這個和視圖類似，視圖是把查詢句子儲存下來了，FROM子查詢是沒有存

使用格式：SELECT tb_alias.col1,... FROM (SELECT  clause) AS tb_alias WHERE Clause;

SELECT AAge,ClassID FROM (SELECT avg(Age) AS AAge,ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;

===

聯集查詢

SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;

mysql查詢詳解