MySQL高級知識-查詢與索引優化分析

性能下降SQL慢、執行時間長、等待時間長

• 查詢語句寫的爛
• 索引失效
  • 單值索引
  • 復合索引
• 關聯查詢太多join（設計缺陷或不得已的需求，除非你能干的過你的產品經理）
• 服務器調優及各個參數設置（緩沖、線程數等）

常見通用的Join查詢

SQL執行順序

• 手寫
  • 

    手寫SQL順序

SELECT DISTINCT
    <select_list>
FROM
     <left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE 
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT <limit_number>

• 機讀（MySQL讀取順序）
  • 

    機讀順序

FROM
     <left_table>
ON <join_condition>
<join_type> JOIN <right_table> 
WHERE 
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
SELECT DISTINCT
    <select_list>
ORDER BY
    <order_by_condition>
LIMIT <limit_number>

• 總結-SQL解析順序
  • 

    SQL解析

SQL JOINs

• 

  七種JOIN圖解

• 實驗：

-- 建表和數據SQL
CREATE TABLE `tbl_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `locAdd` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `tbl_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;



INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);

• 練習
  • 1、A、B兩表共有
    • select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
  • 2、A、B兩表共有+A的獨有
    • select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
  • 3、A、B兩表共有+B的獨有
    • select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
  • 4、A的獨有
    • select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
  • 5、B的獨有
    • select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
  • 6、AB全有
    • MySQL Full Join的實現 因為MySQL不支持FULL JOIN,下面是替代方法
    • left join + union(可去除重復數據)+ right join
    • 實現如下面代碼
  • 7、A的獨有 + B的獨有

-- 6、AB全有
SELECT *
FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id
UNION 
SELECT *
FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id;


-- 7、A的獨有 + B的獨有
SELECT *
FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id
WHERE b.id IS NULL
UNION
SELECT *
FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id
WHERE a.`deptId` IS NULL;

索引簡介

什么是索引

• MySQL官方對索引的定義為： 索引（Index）是幫助MySQL高效獲取數據的數據結構 。
  • 可以得到索引的本質： 索引是數據結構 。
  • 索引的目的在于提高查詢效率，可以類比字典，
  • 如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然后從下往下找到y字母，再找到剩下的sql。
  • 如果沒有索引，那么你可能需要a----z，如果我想找到Java開頭的單詞呢？或者Oracle開頭的單詞呢？
  • 是不是覺得如果沒有索引，這個事情根本無法完成？
• 你可以簡單理解為“排好序的快速查找結構”
  • 在數據之外， 數據庫系統還維護著滿足特定查找算法的數據結構 ，這些數據結構以某種方式引用（指向）數據，
    這樣就可以在這些數據結構上實現高級查找算法。這種數據結構，就是索引。
  • 下圖就是一種可能的索引方式示例：

    • 

  • 為了加快Col2的查找，可以維護一個右邊所示的二叉查找樹，每個節點分別包含索引鍵值和一個指向對應數據記錄物理地址的指針，這樣就可以運用二叉查找在一定的復雜度內獲取到相應數據，從而快速的檢索出符合條件的記錄。
• 一般來說索引本身也很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲的磁盤上
• 我們平常所說的索引，如果沒有特別指明，都是指B+樹結構組織的索引 。其中

  聚集索引，次要索引，覆蓋索引，

  復合索引，前綴索引，唯一索引默認都是使用B+樹索引，統稱索引

  。當然，除了B+樹這種類型的索引之外，還有哈稀索引(hash index)等。

索引的優勢

• 類似大學圖書館建書目索引， 提高數據檢索的效率 ，降低數據庫的IO成本
• 通過索引列對數據進行排序 ，降低數據排序的成本 ，降低了CPU的消耗

索引的劣勢

• 實際上索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄，所以索引列也是要占用空間的
• 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件每次更新添加了索引列的字段，都會調整因為更新所帶來的鍵值變化后的索引信息
• 索引只是提高效率的一個因素，如果你的MySQL有大數據量的表， 就需要花時間研究建立最優秀的索引，或優化查詢語句

MySQL索引分類

• 單值索引
  • 即一個索引只包含單個列，一個表可以有多個單列索引
• 唯一索引
  • 索引列的值必須唯一，但允許有空值
• 復合索引
  • 即一個索包含多個列
• 基本語法
  • 創建，兩種方式
    • CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));
      • 如果是CHAR，VARCHAR類型，length 可以小于字段實際長度；
      • 如果是 BLOB 和 TEXT 類型，必須指定 length。
    • ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
  • 刪除
    • DROP INDEX [indexName] ON mytable;
  • 查看
    • SHOW INDEX FROM table_name\G
  • 使用 Alter 命令
    • 有四種方式來添加數據表的索引：
      • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
        • 該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為NULL。
      • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
        • 這條語句創建索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）。
      • ALTER TABLE tbl_name ADD INDEX index_name (column_list)
        • 添加普通索引，索引值可出現多次。
      • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
        • 該語句指定了索引為 FULLTEXT ，用于全文索引。

MySQL索引結構

• BTree索引
  • 檢索原理

    • 

    • 【初始化介紹】
      • 一顆b+樹，淺藍色的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個 數據項 （ 深藍色 所示）和 指針 （ 黃色 所示），如磁盤塊1包含數據項17和35，包含指針P1、P2、P3。 P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。
      • 真實的數據存在于葉子節點 即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。
      • 非葉子節點只不存儲真實的數據，只存儲指引搜索方向的數據項 ，如17、35并不真實存在于數據表中。
    • 【查找過程】
      • 如果要查找數據項29，那么首先會把磁盤塊1由磁盤加載到內存，此時發生一次IO，在內存中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指針，內存時間因為非常短（相比磁盤的IO）可以忽略不計，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內存，發生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內存，發生第三次IO，同時內存中做二分查找找到29，結束查詢，總計三次IO。
      • 真實的情況是，3層的b+樹可以表示上百萬的數據，如果上百萬的數據查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個數據項都要發生一次IO，那么總共需要百萬次的IO，顯然成本非常非常高。

未完待續

來自：http://www.jianshu.com/p/2717d4fbd474