課程目的：寫出高質(zhì)量SQL語句随静，數(shù)據(jù)庫參數(shù)調(diào)優(yōu)，建立使用索引

1.MySQL架構(gòu)介紹

高級(jí)內(nèi)容包括：內(nèi)核可以優(yōu)化改寫慷吊，服務(wù)器配置文件可以優(yōu)化改寫袖裕，參數(shù)常量優(yōu)化，sql優(yōu)化溉瓶，主從復(fù)制急鳄，軟硬件升級(jí)谤民，容災(zāi)備份，sql編程

linux安裝mysql：儲(chǔ)存位置

圖片.png

Mysql配置文件

log-bin：二進(jìn)制日志疾宏，用于主從復(fù)制

log-error：默認(rèn)關(guān)閉张足，記錄嚴(yán)重的警告和錯(cuò)誤信息

log：默認(rèn)關(guān)閉，記錄查詢的sql語句坎藐，用于查詢分析

數(shù)據(jù)文件：

• /var/lib/mysql 或用語句查詢 ls -lF | grep ^d
• frm文件：存放一個(gè)數(shù)據(jù)庫的表結(jié)構(gòu)
• myd文件：數(shù)據(jù)
• myi文件：數(shù)據(jù)索引

如何配置：win：my.ini linux:/etc/my.cnf

Mysql邏輯架構(gòu)

• 連接層
• 服務(wù)層
• 引擎層
• 存儲(chǔ)層

圖片.png

Parser：將SQL語句重組過濾为牍，從from開始解析（ 轉(zhuǎn)化為關(guān)系代數(shù)表達(dá)式？）

optimizer：優(yōu)化器岩馍。找到更好的等價(jià)的關(guān)系代數(shù)表達(dá)式俱尼。僅僅優(yōu)化為mysql自己認(rèn)為好的狸眼，可導(dǎo)致索引失效

cache & buffer：緩存緩沖劈愚。

存儲(chǔ)引擎：可插拔式的存儲(chǔ)引擎將查詢處理和其他的系統(tǒng)任務(wù)以及數(shù)據(jù)存儲(chǔ)提取相隔離镜硕。常用的是MyISAM和InnoDB。

存儲(chǔ)引擎介紹

圖片.png

InnoDB支持事務(wù)双谆，行鎖壳咕，表鎖，外鍵

MyISAM關(guān)注讀

InnoDB關(guān)注寫

2.索引優(yōu)化分析

性能下降

四個(gè)原因：

• 查詢語句寫的爛
• 索引失效
• 關(guān)聯(lián)查詢太多join（設(shè)計(jì)缺陷或不得已的需求）
• 服務(wù)器調(diào)優(yōu)及各個(gè)參數(shù)的設(shè)置（緩沖顽馋、線程數(shù)等）

join相關(guān)

SQL執(zhí)行順序

手寫順序：

圖片.png

機(jī)讀順序：

圖片.png

總結(jié)：

圖片.png

七種JOIN理論

圖片.png

圖片.png

注：Mysql不支持FULL OUTER全連接查詢谓厘。要得到這個(gè)結(jié)果需要使用UNION（能把兩個(gè)查詢結(jié)果并起來而且去重）

select 語句1 UNION select 語句2

索引

官方定義：幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。

即：

排好序的快速查找數(shù)據(jù)結(jié)構(gòu)（兩大功能：查的塊寸谜，排好序）

類比于字典 ：如果要查找”mysql“這個(gè)單詞庞呕，先定位到m字母，再往下找到y(tǒng)程帕，再找到剩下的sql住练。

索引會(huì)影響到：

• where后面的條件
• order by 后面的排序字段

數(shù)據(jù)庫系統(tǒng)維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這種數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)愁拭。

圖片.png

二叉查找樹的每一個(gè)節(jié)點(diǎn)包含 索引鍵值 和 一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針讲逛。這就能快速地檢索符合要求的記錄。

我們平常說的索引岭埠，如果沒有特別指明盏混，都是指B樹（多路搜索樹，不一定是二叉的）結(jié)構(gòu)組織的索引惜论。其中聚集索引许赃、次要索引、覆蓋索引馆类、復(fù)合索引混聊、前綴索引、唯一索引默認(rèn)都是用B+數(shù)乾巧。此外還有哈希索引句喜。

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

• 提高數(shù)據(jù)庫檢索效率预愤，降低數(shù)據(jù)庫IO成本
• 通過索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序成本咳胃，降低了CPU消耗

索引劣勢(shì)：

• 實(shí)際上索引也是一張表植康，此表保存了主鍵和索引字段，并指向?qū)嶓w表的記錄展懈，所以索引列也是要占空間的
• 降低了表的更新速度销睁，如insert、updata存崖、delete冻记。因?yàn)椴粌H要保存數(shù)據(jù)，還要改動(dòng)索引文件金句。
• 索引只是提高效率的一個(gè)因素檩赢，如果有大數(shù)據(jù)量的表吕嘀，需要花時(shí)間研究建立最優(yōu)秀的索引违寞。

索引分類

• 單值索引：一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引（復(fù)合索引優(yōu)于單值索引）（一張表索引不要超過5個(gè)）（一次查詢只能用一個(gè)索引）

• 唯一索引：索引列的值必須唯一偶房，但允許有空值

• 復(fù)合索引：一個(gè)索引包含多個(gè)列

• 基本語法：

  • 創(chuàng)建：create [unique] index indexName ON mytable(columnName(length)); alter mytable add [unique] index [indexName] ON (columnName(length));
  • 刪除：drop index [indexName] on mytable;
  • 查看：show index from table_name;

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

四種索引結(jié)構(gòu)：BTree索引趁曼、Hash索引、full-text全文索引棕洋、R-TREE索引

BTREE：

圖片.png

圖片.png

需要與不需要建立索引的情況

需要建立：

• 主鍵自動(dòng)作為唯一索引
• 頻繁作為查找條件的字段
• 查詢中與其他表關(guān)聯(lián)的字段挡闰，外鍵關(guān)系建立索引。
• 頻繁更新的字段不應(yīng)該建立索引
• where條件里用不到的字段不適合建立索引
• 高并發(fā)情況應(yīng)該建立組合索引
• 查詢中排序的字段掰盘。（排序字段通過索引去訪問能大大提高排序速度）
• 查詢中統(tǒng)計(jì)和分組的字段（分組之前必須排序）

不需要建立：

• 表記錄太少（三百萬以上效率就開始下降）
• 經(jīng)常增刪改的表摄悯。
• 數(shù)據(jù)重復(fù)且分布平均的字段。（如果某個(gè)數(shù)據(jù)列包含太多重復(fù)的內(nèi)容愧捕，建立索引沒太大效果）
• • 索引的選擇性：索引列中不同值的數(shù)目與表中記錄數(shù)的比奢驯。越接近1，索引效率就越高次绘。

性能分析

優(yōu)化器模塊的作用： 通過計(jì)算分析系統(tǒng)中收集到的統(tǒng)計(jì)信息瘪阁，為客戶端請(qǐng)求的Query提供它認(rèn)為最優(yōu)的執(zhí)行計(jì)劃

MySQL常見瓶頸：

• CPU：CPU飽和經(jīng)常發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤讀取數(shù)據(jù)的時(shí)候
• IO:IO瓶頸發(fā)生在裝入數(shù)據(jù)量遠(yuǎn)大于內(nèi)存的時(shí)候
• 服務(wù)器硬件性能瓶頸：用top、free邮偎、iostat和vmstat來查看系統(tǒng)的性能狀態(tài)

explain（查看執(zhí)行計(jì)劃）：模擬優(yōu)化器執(zhí)行sql查詢語句管跺，從而知道m(xù)ysql是如何處理sql語句的，分析性能瓶頸禾进。

能查出：

• 表的讀取順序:id
• 數(shù)據(jù)讀取操作的操作類型：select_type
• 哪些索引可以使用：possible_keys
• 哪些索引被實(shí)際使用：key
• 表之間的引用：ref
• 每張表有多少行被優(yōu)化器查詢：rows（越小越好）

• id：序列號(hào)豁跑，表示查詢中執(zhí)行select子句或操作表的順序
• id相同，執(zhí)行順序由上至下
• 如果是子查詢泻云，id的序號(hào)會(huì)遞增贩绕，id越大優(yōu)先級(jí)越高火的，越先被執(zhí)行
• id如果相同，可以認(rèn)為是一組淑倾，從上往下執(zhí)行馏鹤。所有組中，id越大娇哆，優(yōu)先級(jí)越高湃累。

• select_type:查詢類型
  • SIMPLE:簡單的select查詢，不包含子查詢或者UNION
  • PRIMARY:****最外層的查詢（包含復(fù)雜子查詢的情況）
  • SUBQUERY:在Select或where列表中包含了子查詢
  • DERIVED:在from中包含的子查詢被標(biāo)記為“衍生”碍讨，mysql會(huì)遞歸地執(zhí)行這些子查詢治力，把結(jié)果放在臨時(shí)表里。后面的數(shù)字勃黍，指的是前面的id序列號(hào)宵统。
  • UNION: 若第二個(gè)select出現(xiàn)在union后，則被標(biāo)記為union覆获。若union包含在from子句的子查詢中马澈，外層的select將被標(biāo)記為：DERIVED。
  • UNION RESULT:從UNION獲取的result

• table : 顯示這一行數(shù)據(jù)是關(guān)于哪張表的弄息。

• type:訪問類型痊班，顯示查詢使用了何種類型。最好到最差依次是：system >const>eq_ref>range>index >ALL.
  • 大查詢至少達(dá)到range級(jí)別摹量，最好能達(dá)到ref
  • system：表只有一行記錄涤伐，等于系統(tǒng)表，const類型的特例
  • const：表示通過索引一次就找到了缨称。用于比較primary key或者unique索引凝果。因?yàn)?strong>只匹配一行數(shù)據(jù)，所以很快睦尽。如將主鍵置于where列表中器净，mysql就能將該查詢轉(zhuǎn)化為一個(gè)常量。
  • eq_ref：唯一性索引掃描骂删，對(duì)于每個(gè)索引鍵掌动，表中只有一條記錄與之匹配。常見于 主鍵索引或唯一索引掃描宁玫。
  • ref：非唯一性索引掃描粗恢。返回匹配某個(gè)單獨(dú)值的所有行。（希望達(dá)到的級(jí)別）
  • range：檢索給定范圍的列欧瘪，使用一個(gè)索引來選擇行眷射。key顯示使用了哪個(gè)索引。一般就是在where語句里出現(xiàn)了 between、<>妖碉、in等的查詢涌庭。它開始于索引的某一點(diǎn)，結(jié)束語另一點(diǎn)欧宜，不用掃描全部索引坐榆。
  • index：只遍歷索引樹，通常比all全表查快冗茸。
  • all：遍歷全表席镀。超過百萬就明顯慢了。

• possible_keys:顯示可能應(yīng)用到這個(gè)表上的索引夏漱，可能有一個(gè)或多個(gè)豪诲。但該索引不一定被使用
• key：實(shí)際使用的索引。如果為null挂绰，則沒有使用索引屎篱。 查詢中若使用了覆蓋索引，則該索引僅出現(xiàn)在key列表中葵蒂，possible_keys里沒有交播。（ 覆蓋索引：所查的字段正好與索引字段個(gè)數(shù)、順序一致刹勃，或者where條件字段加上所查字段正好是復(fù)合索引字段堪侯，直接從索引里讀數(shù)據(jù)）

• key_len:表示索引中使用的字節(jié)數(shù)嚎尤，可通過該列計(jì)算查詢中使用的索引長度荔仁。在不損失精確性的情況下，長度越短越好芽死。為最大可能長度乏梁，并非實(shí)際使用長度。通過表定義計(jì)算得到关贵，非通過表內(nèi)檢索找到遇骑。

• ref：顯示索引的哪一列被使用了，如果可能的話揖曾，是一個(gè)常數(shù)落萎。 哪些列或者常量被用于查找索引列上的值。

• rows:根據(jù)表的統(tǒng)計(jì)信息和索引選用情況炭剪，大致估計(jì)出找到所需記錄所需要讀取的行數(shù)练链。越小越好！奴拦！

• Extra：其他重要的信息媒鼓。
  • Using filesort：表示mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是用表內(nèi)的索引順序。表示排序時(shí)沒有用上索引Ｂ堂＞毋濉！ 常見于：建了組合索引潮模，order by或group by 沒有全部用上索引的字段亮蛔。
  • Using temporary：使用臨時(shí)表保存了中間結(jié)果。常見于order by 和group by擎厢。絕對(duì)不好６恕！锉矢！
  • Using index：表示相應(yīng)的select操作使用了覆蓋索引梯嗽，避免了訪問表的數(shù)據(jù)行， 效率不錯(cuò)沽损！ 如果同時(shí)出現(xiàn)了using where 表示索引被用來執(zhí)行索引鍵值的查找灯节；如果沒有同時(shí)出現(xiàn)using where，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找绵估。
  • Using where：使用了where
  • Using join buffer：使用了連接緩存
  • impossible where：where子句的值總是false 不能用于獲取值

索引優(yōu)化

單表優(yōu)化：

題目：查詢category_id為1且comments大于1的情況下炎疆，views最多的article_id

建立索引1：針對(duì)category_id、comments国裳、views建立復(fù)合索引

結(jié)果1：type變?yōu)閞ange形入，key字段顯示了索引，但Extra出現(xiàn)了Using filesort缝左，說明針對(duì)views排序時(shí)亿遂，索引失效。

原理1：按照B樹工作原理渺杉，先排序category_id蛇数，遇到相同時(shí)再排序comments，相同時(shí)再排序views是越。當(dāng)comments字段在聯(lián)合索引中處于中間位置時(shí)耳舅，因comments>1是一個(gè)范圍條件（range），Mysql無法里利用索引再對(duì)后面的views部分進(jìn)行檢索倚评，即range類型查詢字段后的索引失效浦徊。

建立索引2：只針對(duì)category_id、views建立索引

結(jié)果2：type變?yōu)閞ef天梧，Extra中的using filesort也消失了盔性，結(jié)果理想

兩表優(yōu)化：

題目：左連接的時(shí)候，索引應(yīng)該加在左表的id列還是右表的id列腿倚？

結(jié)果1：加在右表纯出，右表的type變成ref蚯妇，rows變成了1，結(jié)果理想

結(jié)果2：加在左表暂筝，左表的type變成index箩言，row還是沒變，結(jié)果不理想

原理：這是由左連接的特性決定的焕襟，left join條件用來決定如何從右表搜索行,左邊一定都有陨收，左表一定都是all。所以右表是關(guān)鍵點(diǎn)鸵赖，一定要在右表建立索引务漩。

同理，right join on 條件用于確定如何從左表搜索行它褪，右邊一定都有饵骨。所以索引一定要建在左表上。

三表優(yōu)化：

結(jié)論：**如多個(gè)left join 并列 茫打，則在兩個(gè)右表上建立索引居触。 ** 永遠(yuǎn)用小表驅(qū)動(dòng)大表，因?yàn)橹鞅砣珤呙枋遣豢杀苊獾摹?/strong>

原則：

• 減少join嵌套的次數(shù)老赤，用小表驅(qū)動(dòng)大表
• 優(yōu)化嵌套的內(nèi)層循環(huán)
• 保證join語句中被驅(qū)動(dòng)表上的條件字段已經(jīng)被索引
• 當(dāng)無法保證被驅(qū)動(dòng)表上的條件字段被索引時(shí)轮洋，不要太吝惜于joinbuffer的設(shè)置