性能下降SQL慢、執(zhí)行時(shí)間長(zhǎng)、等待時(shí)間長(zhǎng)

• 查詢語(yǔ)句寫(xiě)的爛
• 索引失效
  • 單值索引
  • 復(fù)合索引
• 關(guān)聯(lián)查詢太多join（設(shè)計(jì)缺陷或不得已的需求，除非你能干的過(guò)你的產(chǎn)品經(jīng)理）
• 服務(wù)器調(diào)優(yōu)及各個(gè)參數(shù)設(shè)置（緩沖、線程數(shù)等）

常見(jiàn)通用的Join查詢

SQL執(zhí)行順序

• 手寫(xiě)
  • 手寫(xiě)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>

• 機(jī)讀（MySQL讀取順序）
  • 機(jī)讀順序

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>

• 總結(jié)-SQL解析順序
  • SQL解析

SQL JOINs

• 七種JOIN圖解
• 實(shí)驗(yàn)：

-- 建表和數(shù)據(jù)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);

• 練習(xí)
  • 1、A、B兩表共有
    • select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
  • 2、A、B兩表共有+A的獨(dú)有
    • select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
  • 3、A、B兩表共有+B的獨(dú)有
    • select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
  • 4、A的獨(dú)有
    • select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
  • 5、B的獨(dú)有
    • 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的實(shí)現(xiàn) 因?yàn)镸ySQL不支持FULL JOIN,下面是替代方法
    • left join + union(可去除重復(fù)數(shù)據(jù))+ right join
    • 實(shí)現(xiàn)如下面代碼
  • 7、A的獨(dú)有 + B的獨(dú)有

-- 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的獨(dú)有 + B的獨(dú)有
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;

索引簡(jiǎn)介

什么是索引

• MySQL官方對(duì)索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。
  • 可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)。
  • 索引的目的在于提高查詢效率，可以類(lèi)比字典，
  • 如果要查“mysql”這個(gè)單詞，我們肯定需要定位到m字母，然后從下往下找到y(tǒng)字母，再找到剩下的sql。
  • 如果沒(méi)有索引，那么你可能需要a----z，如果我想找到Java開(kāi)頭的單詞呢？或者Oracle開(kāi)頭的單詞呢？
  • 是不是覺(jué)得如果沒(méi)有索引，這個(gè)事情根本無(wú)法完成？
• 你可以簡(jiǎn)單理解為“排好序的快速查找結(jié)構(gòu)”
  • 在數(shù)據(jù)之外，數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)，
    這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引。
  • 下圖就是一種可能的索引方式示例：
  • 為了加快Col2的查找，可以維護(hù)一個(gè)右邊所示的二叉查找樹(shù)，每個(gè)節(jié)點(diǎn)分別包含索引鍵值和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針，這樣就可以運(yùn)用二叉查找在一定的復(fù)雜度內(nèi)獲取到相應(yīng)數(shù)據(jù)，從而快速的檢索出符合條件的記錄。
• 一般來(lái)說(shuō)索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤(pán)上
• 我們平常所說(shuō)的索引，如果沒(méi)有特別指明，都是指B+樹(shù)結(jié)構(gòu)組織的索引。其中聚集索引，次要索引，覆蓋索引，
  復(fù)合索引，前綴索引，唯一索引默認(rèn)都是使用B+樹(shù)索引，統(tǒng)稱(chēng)索引。當(dāng)然，除了B+樹(shù)這種類(lèi)型的索引之外，還有哈稀索引(hash index)等。

索引的優(yōu)勢(shì)

• 類(lèi)似大學(xué)圖書(shū)館建書(shū)目索引，提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)的IO成本
• 通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了CPU的消耗

索引的劣勢(shì)

• 實(shí)際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，所以索引列也是要占用空間的
• 雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段，都會(huì)調(diào)整因?yàn)楦滤鶐?lái)的鍵值變化后的索引信息
• 索引只是提高效率的一個(gè)因素，如果你的MySQL有大數(shù)據(jù)量的表，就需要花時(shí)間研究建立最優(yōu)秀的索引，或優(yōu)化查詢語(yǔ)句

MySQL索引分類(lèi)

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

MySQL索引結(jié)構(gòu)

• BTree索引
  • 檢索原理
    • 【初始化介紹】
      • 一顆b+樹(shù)，淺藍(lán)色的塊我們稱(chēng)之為一個(gè)磁盤(pán)塊，可以看到每個(gè)磁盤(pán)塊包含幾個(gè)數(shù)據(jù)項(xiàng)（深藍(lán)色所示）和指針（黃色所示），如磁盤(pán)塊1包含數(shù)據(jù)項(xiàng)17和35，包含指針P1、P2、P3。 P1表示小于17的磁盤(pán)塊，P2表示在17和35之間的磁盤(pán)塊，P3表示大于35的磁盤(pán)塊。
      • 真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。
      • 非葉子節(jié)點(diǎn)只不存儲(chǔ)真實(shí)的數(shù)據(jù)，只存儲(chǔ)指引搜索方向的數(shù)據(jù)項(xiàng)，如17、35并不真實(shí)存在于數(shù)據(jù)表中。
    • 【查找過(guò)程】
      • 如果要查找數(shù)據(jù)項(xiàng)29，那么首先會(huì)把磁盤(pán)塊1由磁盤(pán)加載到內(nèi)存，此時(shí)發(fā)生一次IO，在內(nèi)存中用二分查找確定29在17和35之間，鎖定磁盤(pán)塊1的P2指針，內(nèi)存時(shí)間因?yàn)榉浅６蹋ㄏ啾却疟P(pán)的IO）可以忽略不計(jì)，通過(guò)磁盤(pán)塊1的P2指針的磁盤(pán)地址把磁盤(pán)塊3由磁盤(pán)加載到內(nèi)存，發(fā)生第二次IO，29在26和30之間，鎖定磁盤(pán)塊3的P2指針，通過(guò)指針加載磁盤(pán)塊8到內(nèi)存，發(fā)生第三次IO，同時(shí)內(nèi)存中做二分查找找到29，結(jié)束查詢，總計(jì)三次IO。
      • 真實(shí)的情況是，3層的b+樹(shù)可以表示上百萬(wàn)的數(shù)據(jù)，如果上百萬(wàn)的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的，如果沒(méi)有索引，每個(gè)數(shù)據(jù)項(xiàng)都要發(fā)生一次IO，那么總共需要百萬(wàn)次的IO，顯然成本非常非常高。