Mysql進階知識概括

• Mysql簡介
• MysqlLinux版本的安裝
• Mysql配置檔案
• Mysql邏輯架構介紹
• Mysql存儲引擎
• 索引優化分析
• • 索引介紹
  • 性能分析
  • • Explain各個字段解釋
  • 子查詢和連接配接查詢
  • 索引優化
• 查詢截取分析
• • 查詢優化
  • 慢查詢日志
  • 批量資料腳本
  • Show profiles
  • 全局查詢日志
• Mysql鎖機制
• 主重複制
• 讀寫分離

Mysql簡介

簡介：

進階MySQL：

• mysql核心
• sql優化工程師
• mysql伺服器的優化
• 查詢語句優化
• 主重複制
• 軟硬體更新
• 容災備份
• sql程式設計

完整的mysql優化需要很深的功底,大公司甚至有專門的DBA寫上述

MysqlLinux版本的安裝

Mysql配置檔案

二進制日志log-bin:

• 主從複制

錯誤日志log-error:

• 預設是關閉的,記錄嚴重的警告和錯誤資訊,每次啟動和關閉的詳細資訊等.

查詢日志log:

• 預設關閉,記錄查詢的sql語句，如果開啟會減低mysql的整體性能，因為記錄日志也是需要消耗系統資源的(對應下面介紹的慢查詢日志)

資料檔案:

• 兩系統:

  windows:D:\ProgramFiles\MySQL\MySQLServer5.5\data目錄下可以挑選很多庫

  linux:看看目前系統中的全部庫後再進去,預設路徑：/var/lib/mysql

• frm檔案:

  存放表結構

• myd檔案:

  存放表資料

• myi檔案:

  存放表索引

如何配置:

• windows:

  my.ini檔案

• Linux:

  /etc/my.cnf檔案

Mysql邏輯架構介紹

• 1、Connectors

  指的是不同語言中與SQL的互動

• 2、 Management Serveices & Utilities：

  系統管理和控制工具

• 3、 Connection Pool: 連接配接池

  管理緩沖使用者連接配接，線程處理等需要緩存的需求。

  負責監聽對 MySQL Server 的各種請求，接收連接配接請求，轉發所有連接配接請求到線程管理子產品。每一個連接配接上 MySQL Server 的用戶端請求都會被配置設定（或建立）一個連接配接線程為其單獨服務。而連接配接線程的主要工作就是負責 MySQL Server 與用戶端的通信，

  接受用戶端的指令請求，傳遞 Server 端的結果資訊等。線程管理子產品則負責管理維護這些連接配接線程。包括線程的建立，線程的 cache 等。

• 4、 SQL Interface: SQL接口。

  接受使用者的SQL指令，并且傳回使用者需要查詢的結果。比如select from就是調用SQL Interface

• 5、 Parser: 解析器。

  SQL指令傳遞到解析器的時候會被解析器驗證和解析。解析器是由Lex和YACC實作的，是一個很長的腳本。

  在 MySQL中我們習慣将所有 Client 端發送給 Server 端的指令都稱為 query ，在 MySQL Server 裡面，連接配接線程接收到用戶端的一個 Query 後，會直接将該 query 傳遞給專門負責将各種 Query 進行分類然後轉發給各個對應的處理子產品。

  主要功能：

  a . 将SQL語句進行語義和文法的分析，分解成資料結構，然後按照不同的操作類型進行分類，然後做出針對性的轉發到後續步驟，以後SQL語句的傳遞和處理就是基于這個結構的。

  b. 如果在分解構成中遇到錯誤，那麼就說明這個sql語句是不合理的

• 6 、Optimizer: 查詢優化器。

  SQL語句在查詢之前會使用查詢優化器對查詢進行優化。就是優化用戶端請求的 query（sql語句） ，根據用戶端請求的 query 語句，和資料庫中的一些統計資訊，在一系列算法的基礎上進行分析，得出一個最優的政策，告訴後面的程式如何取得這個 query 語句的結果

  他使用的是“選取-投影-聯接”政策進行查詢。

  用一個例子就可以了解： select uid,name from user where gender = 1;

  這個select 查詢先根據where 語句進行選取，而不是先将表全部查詢出來以後再進行gender過濾

  這個select查詢先根據uid和name進行屬性投影，而不是将屬性全部取出以後再進行過濾

  将這兩個查詢條件聯接起來生成最終查詢結果

• 7、 Cache和Buffer： 查詢緩存。

  他的主要功能是将用戶端送出 給MySQL 的 Select 類 query 請求的傳回結果集 cache 到記憶體中，與該 query 的一個 hash 值 做一個對應。該 Query 所取資料的基表發生任何資料的變化之後， MySQL 會自動使該 query 的Cache 失效。在讀寫比例非常高的應用系統中， Query Cache 對性能的提高是非常顯著的。當然它對記憶體的消耗也是非常大的。

  如果查詢緩存有命中的查詢結果，查詢語句就可以直接去查詢緩存中取資料。這個緩存機制是由一系列小緩存組成的。比如表緩存，記錄緩存，key緩存，權限緩存等

• 8 、存儲引擎接口

  存儲引擎接口子產品可以說是 MySQL 資料庫中最有特色的一點了。目前各種資料庫産品中，基本上隻有 MySQL 可以實作其底層資料存儲引擎的插件式管理。這個子產品實際上隻是 一個抽象類，但正是因為它成功地将各種資料處理高度抽象化，才成就了今天 MySQL 可插拔存儲引擎的特色。

  從圖2還可以看出，MySQL差別于其他資料庫的最重要的特點就是其插件式的表存儲引擎。MySQL插件式的存儲引擎架構提供了一系列标準的管理和服務支援，這些标準與存儲引擎本身無關，可能是每個資料庫系統本身都必需的，如SQL分析器和優化器等，而存儲引擎是底層實體結構的實作，每個存儲引擎開發者都可以按照自己的意願來進行開發。

  注意：存儲引擎是基于表的，而不是資料庫。

Mysql存儲引擎

檢視指令：

MyISAM和InnoDB：

阿裡巴巴，淘寶用哪個：

索引優化分析

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

• 查詢語句寫的爛
• 索引失效
• 關聯查詢太多join(設計缺陷或不得已的需求)
• 伺服器調優及各個參數設定(緩沖\線程數等)

SQL執行順序：

• 手寫：

  

• 機讀：

  

• 總結：

  

常見通用的join查詢：

索引介紹

索引簡介：

• MySQL官方對索引的定義為：索引(Index)是幫助MySQL高校擷取資料的資料結構。 可以得到索引的本質：索引是資料結構。

  

• 你可以簡單了解為

  "排好序的快速查找資料結構"

  。

  

• 結論：資料本身之外,資料庫還維護着一個滿足特定查找算法的資料結構，這些資料結構以某種方式指向資料，這樣就可以在這些資料結構的基礎上實作進階查找算法,這種資料結構就是索引。
• 一般來說索引本身也很大，不可能全部存儲在記憶體中，是以索引往往以檔案形式存儲在硬碟上。
• 我們平時所說的索引，如果沒有特别指明，都是指B樹(多路搜尋樹，并不一定是二叉樹)結構組織的索引。其中聚集索引，次要索引，覆寫索引，複合索引，字首索引，唯一索引預設都是使用B+樹索引，統稱索引。當然,除了B+樹這種類型的索引之外，還有哈希索引(hash index)等。

索引優勢：

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

索引劣勢：

• 實際上索引也是一張表，該表儲存了主鍵和索引字段，并指向實體表的記錄,是以索引列也是要占用空間的

• 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度,如果對表INSERT,UPDATE和DELETE。

  因為更新表時，MySQL不僅要不存資料，還要儲存一下索引檔案每次更新添加了索引列的字段，都會調整因為更新所帶來的鍵值變化後的索引資訊。

• 索引隻是提高效率的一個因素，如果你的MySQL有大資料量的表，就需要花時間研究建立優秀的索引，或優化查詢語句

mysql索引分類：

• 單值（單列）索引：

  ①

  普通索引

  ： MySQL中基本索引類型，沒有什麼限制，允許在定義索引的列中插入重複值和空值，純粹為了查詢資料更快一 點。

  ②

  唯一索引：

  索引列的值必須唯一，但允許有空值。（

  唯一限制就是一個唯一索引

  ）

  <1>

  主鍵索引：是一種特殊的唯一索引

  ，不允許有空值。（

  主鍵限制就是一個主鍵索引

  ）

• 複合（組合）索引：

  ① 即一個索引包含多個列

• 全文索引：

  ①全文索引主要用來查找文本中的關鍵字，而不是直接與索引中的值相比較。
• 建議：一張表索引不要超過5個且優先考慮複合索引
• 注意：當索引的列名太長可以轉換成字首索引。
• 連結：索引的類型分類、差別、優缺點

添加PRIMARY KEY（主鍵索引） 
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

添加UNIQUE(唯一索引) 
ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

添加INDEX(普通索引) 
ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

添加FULLTEXT(全文索引) 
ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

添加多列索引 
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
           

唯一索引：

• 普通索引允許被索引的資料列包含重複的值。比如說，因為人有可能同名，是以同一個姓名在同一個“員工個人資料”資料表裡可能出現兩次或更多次。
• 如果能确定某個資料列将隻包含彼此各不相同的值，在為這個資料列建立索引的時候就應該用關鍵字UNIQUE把它定義為一個唯一索引。
• 這麼做的好處：一是簡化了MySQL對這個索引的管理工作，這個索引也是以而變得更有效率；二是MySQL會在有新記錄插入資料表時，自動檢查新記錄的這個字段的值是否已經在某個記錄的這個字段裡出現過了；如果是,MySQL将拒絕插入那條新記錄。也就是說，唯一索引可以保證資料記錄的唯一性。
• 事實上，在許多場合，人們建立唯一索引的目的往往不是為了提高通路速度，而隻是為了避免資料出現重複。

字首索引：

• 當要索引的列字元很多時，索引則會很大且變慢。（

  對于 BLOB、TEXT 和 VARCHAR 類型的列

  ）
• 是以可以隻用索引列

  開始的部分字元串

  ，節約索引空間，進而提高索引效率。

• 索引的選擇性，是指不重複的索引數量除以總記錄數，範圍是(0,1]。

• 但前提要保證截取後的索引列與截取前

  選擇性是一樣的！

• 字首索引詳細連結

全文索引：

• 通過數值比較、範圍過濾等就可以完成絕大多數我們需要的查詢，但是，如果希望通過關鍵字的比對來進行查詢過濾，那麼就需要基于相似度的查詢，而不是原來的精确數值比較。全文索引就是為這種場景設計的。
• 你可能會說，用 like + % 就可以實作模糊比對了，為什麼還要全文索引？like + %在文本比較少時是合适的，但是對于大量的文本資料檢索，是不可想象的。全文索引在大量的資料面前，能比 like + % 快 N倍，速度不是一個數量級，但是全文索引可能存在精度問題。
• 你可能沒有注意過全文索引，不過至少應該對一種全文索引技術比較熟悉：各種的搜尋引擎。雖然搜尋引擎的索引對象是超大量的資料，并且通常其背後都不是關系型資料庫，不過全文索引的基本原理是一樣的。
• 全文索引詳解

mysql索引基本文法：

• 建立：

  ①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\G

• 使用ALTER指令：

  

mysql索引結構(按索引底層的資料結構分類)：

• 連結：MySQL索引資料結構
• 正如上文中說到，索引是提高查詢效率的資料結構，而能夠提高查詢效率的資料結構有很多，如二叉搜尋樹，紅黑樹，跳表，哈希表（散清單）等，而MySQL中用到了

  B+Tree

  和

  散清單（Hash表）

  作為索引的底層資料結構。

• Hash索引：

  ①需要注意的是，

  MySQL并沒有顯式支援Hash索引，而是作為内部的一種優化，對于熱點的資料會自動生成Hash索引，也叫自适應Hash索引。

  ②Hash索引在等值查詢中，可以O（1）時間複雜度定位到資料，效率非常高，但是不支援範圍查詢。在許多程式設計語言以及資料庫中都會用到這個資料結構，如Redis支援的Hash資料結構。具體結構如下：

  

• B+Tree索引：

  ①提到B+Tree首先不得不

  提B-Tree

  ，B-Tree（多路搜尋樹，并不是二叉的）是一種常見的資料結構。使用B-tree結構可以顯著減少定位記錄時所經曆的中間過程，進而加快存取速度。

  

  ②

  B+ 樹是基于B-Tree更新後的一種樹資料結構

  ，通常用于資料庫和作業系統的檔案系統中。B+ 樹的特點是能夠保持資料穩定有序，其插入與修改擁有較穩定的對數時間複雜度。B+ 樹元素自底向上插入，這與二叉樹恰好相反。

  ③MySQL索引的實作也是基于這種高效的資料結構。具體資料結構如下：

  

  ④首先要聲明一下，

  不要将B樹，B-Tree以及B+Tree弄混淆

  。首先，

  B-Tree就是B樹

  ，中間的“-”是一個中劃線，而不是減号，并不存在"B減樹"這種資料結構。其次，就是B+Tree和B-Tree實作索引時有兩個差別：

  B+Tree隻在葉子節點存儲資料

  ，

  而B-Tree的資料存儲在各個節點中

  。具體可見下圖：

  

Hash索引：

• 哈希表也為散清單，又直接尋址改進而來。在哈希的方式下，一個元素k處于h(k)中，即利用哈希函數h，根據關鍵字k計算出槽的位置。函數h将關鍵字域映射到哈希表T[0…m-1]的槽位上。

  

• 上圖中哈希函數h有可能将兩個不同的關鍵字映射到相同的位置，這叫做碰撞，在資料庫中一般采用連結法來解決。在連結法中，将散列到同一槽位的元素放在一個連結清單中，如下圖所示：

  

BTree索引和哈希索引的差別：

• Hash索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像B-Tree索引需要從根節點到枝節點，最後才能通路到頁節點這樣多次的IO通路，是以Hash索引的查詢效率要遠高于B-Tree索引。

• Hash索引僅僅能滿足"=",“IN"和”<=>"查詢，不能使用範圍查詢。哈希索引隻支援等值比較查詢，包括＝、 IN 、<=> (注意<>和＜＝＞是不同的操作）。 也不支援任何範圍查詢，例如WHERE price > 100。　　

  由于Hash索引比較的是進行Hash運算之後的Hash值，是以它隻能用于等值的過濾，不能用于基于範圍的過濾，因為經過相應的Hash算法處理之後的Hash值的大小關系，并不能保證和Hash運算前完全一樣。

• Hash索引無法被用來避免資料的排序操作。　　

  由于Hash索引中存放的是經過Hash計算之後的Hash值，而且Hash值的大小關系并不一定和Hash運算前的鍵值完全一樣，是以資料庫無法利用索引的資料來避免任何排序運算;

• Hash索引不能利用部分索引鍵查詢。　　

  對于組合索引，Hash索引在計算Hash值的時候是組合索引鍵合并後再一起計算Hash值，而不是單獨計算Hash值，是以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash索引也無法被利用。

• Hash索引在任何時候都不能避免表掃描。　　

  前面已經知道，Hash索引是将索引鍵通過Hash運算之後，将 Hash運算結果的Hash值和所對應的行指針資訊存放于一個Hash表中，由于不同索引鍵存在相同Hash值，是以即使取滿足某個Hash鍵值的資料的記錄條數，也無法從Hash索引中直接完成查詢，還是要通過通路表中的實際資料進行相應的比較，并得到相應的結果。

• Hash索引遇到大量Hash值相等的情況後性能并不一定就會比BTree索引高。　

  對于選擇性比較低的索引鍵，如果建立Hash索引，那麼将會存在大量記錄指針資訊存于同一個Hash值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表資料的通路，而造成整體性能低下。

Mysql索引（資料存儲方式分類）：

• 聚簇索引(聚集索引)

  ①聚簇索引就是按照每張表的主鍵構造一顆B+樹，

  同時葉子節點中存放的就是整張表的行記錄資料，也将聚集索引的葉子節點稱為資料頁。

  這個特性決定了索引組織表中資料也是索引的一部分，

  每張表隻能擁有一個聚簇索引。

  ②

  Innodb通過主鍵聚集資料，如果沒有定義主鍵，innodb會選擇非空的唯一索引代替。如果沒有這樣的索引，innodb會隐式的定義一個主鍵來作為聚簇索引。

  ③優點：

  1.資料通路更快，因為聚簇索引将索引和資料儲存在同一個B+樹中，是以從聚簇索引中擷取資料比非聚簇索引更快

  2.聚簇索引對于主鍵的排序查找和範圍查找速度非常快

  ④缺點：

  1.插入速度嚴重依賴于插入順序，按照主鍵的順序插入是最快的方式，否則将會出現頁分裂，嚴重影響性能。是以，對于InnoDB表，我們一般都會定義一個自增的ID列為主鍵

  2.更新主鍵的代價很高，因為将會導緻被更新的行移動。是以，對于InnoDB表，我們一般定義主鍵為不可更新。

  3.二級索引通路需要兩次索引查找，第一次找到主鍵值，第二次根據主鍵值找到行資料。

  

• 輔助索引（非聚簇索引）

  ①在聚簇索引之上建立的索引稱之為輔助索引，輔助索引通路資料總是需要二次查找。

  輔助索引葉子節點存儲的不再是行的實體位置，而是主鍵值。

  通過輔助索引首先找到的是主鍵值，再通過主鍵值找到資料行的資料頁，再通過資料頁中的PageDirectory找到資料行。

  ②Innodb輔助索引的葉子節點并不包含行記錄的全部資料，葉子節點除了包含鍵值外，還包含了相應行資料的聚簇索引鍵。

  ③輔助索引的存在不影響資料在聚簇索引中的組織，

  是以一張表可以有多個輔助索引。在innodb中有時也稱輔助索引為二級索引。

  

• 詳細連結：

  聚簇索引和非聚簇索引詳解

  聚簇索引和非聚簇索引比較

• 案例：

  ①InnoDB使用的是聚簇索引，将主鍵組織到一棵B+樹中，而行資料就儲存在葉子節點上，

  若使用"where id = 14"

  這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找到對應的葉節點，之後獲得行資料。

  ②若對Name列進行條件搜尋，則需要兩個步驟：第一步在輔助索引B+樹中

  檢索Name，

  到達其葉子節點擷取

  對應的主鍵

  。第二步

  使用主鍵

  在主索引B+樹種再執行一次B+樹檢索操作，最終到達葉子節點即可擷取整行資料。（重點在于通過其他鍵需要建立輔助索引）

MyISAM索引和InnoDB索引總結：

• Innobd存儲引擎中的索引：

  <1>

  Innobd中的主鍵索引是一種聚簇索引，

  <2>

  Innobd非聚簇索引都是輔助索引，像複合索引、字首索引、唯一索引。

• MyISAM存儲引擎中，資料檔案和索引檔案是單獨分離的在MyISAM中，無論是主鍵索引還是輔助索引，都是非聚簇索引，

  在結構上兩者沒有什麼差別

  ，它們各自存放對應主鍵或輔助鍵，最後指向對應位址的表資料。不過，主鍵索引中key是唯一的，而輔助索引中key是可重複的。
• MyISAM，不支援資料庫ACID事務，也不支援行級鎖和外鍵的限制，但是會儲存表的行數。

  

哪些情況需要建立索引:

• 1.主鍵自動建立唯一索引
• 2.頻繁作為查詢的條件的字段應該建立索引
• 3.查詢中與其他表關聯的字段，外鍵關系建立索引
• 4.查詢中統計或者分組字段
• 5.查詢中排序的字段，排序字段若通過索引去通路将大大提高排序的速度
• 6.單間/組合索引的選擇問題，who？（在高并發下傾向建立組合索引）

哪些情況不要建立索引：

• 1.表記錄太少
• 2.經常增删改的表

• 3.資料重複且分布平均的表字段，是以應該隻為經常查詢和經常排序的資料列建立索引。

  注意，如果某個資料列包含許多重複的内容，為它建立索引就沒有太大的實際效果。

• 4.頻繁更新的字段不适合建立索引，因為每次更新不單單是更新了記錄還會更新索引，加重IO負擔
• 5.Where條件裡用不到的字段不建立索引

性能分析

MySQL常見瓶頸：

• CPU:CPU在飽和的時候一般發生在資料裝入在記憶體或從磁盤上讀取資料時候
• IO:磁盤I/O瓶頸發生在裝入資料遠大于記憶體容量時
• 伺服器硬體的性能瓶頸：top,free,iostat和vmstat來檢視系統的性能狀态

Explain：

• 簡介：

  使用EXPLAIN關鍵字可以模拟優化器執行SQL語句，進而知道MySQL是如何處理你的SQL語句的。分析你的查詢語句或是結構的性能瓶頸。

• 能幹嘛：

  ①表的讀取順序

  ②資料讀取操作的操作類型

  ③哪些索引可以使用

  ④哪些索引被實際使用

  ⑤表之間的引用

  ⑥每張表有多少行被優化器查詢

• 怎麼玩：

  ① Explain+SQL語句

  ② 執行計劃包含的資訊：

  

Explain各個字段解釋

id:

• 簡介：

  select查詢的序列号，包含一組數字，表示查詢中執行select子句或操作表的順序。

• 三種情況：

  ①id相同，執行順序由上至下

  

  ②id不同，如果是子查詢，id的序号會遞增，id值越大優先級越高，越先被執行

  

  ③id相同不同，同時存在

  

select_type:

簡介：查詢的類型，主要用于差別普通查詢、聯合查詢、子查詢等的複雜查詢

• 1.SIMPLE:

  簡單的select查詢，查詢中不包含子查詢或者UNION

• 2.PRIMARY:

  查詢中若包含任何複雜的子部分，最外層查詢則被标記為

• 3.SUBQUERY:

  在SELECT或者WHERE清單中包含了子查詢

• 4.DERIVED:

  在FROM清單中包含的子查詢被标記為DERIVED（衍生）

  MySQL會遞歸執行這些子查詢，把結果放在臨時表裡。

• 5.UNION:

  若第二個SELECT出現在UNION之後，則被标記為UNION;

  若UNION包含在FROM子句的子查詢中，外層SELECT将被标記為：DERIVED

• 6.UNION RESULT:

  從UNION表擷取結果的SELECT

table：

簡介：

①顯示這一行的資料是關于哪張表的

type:

通路類型排列:

詳解：

• system：

  表隻有一行記錄（等于系統表），這是const類型的特例，平時不會出現，這個也可以忽略不計

• const：

  表示通過索引一次就找到了，const用于比較primary key或者unique索引。因為隻比對一行資料，是以很快。如将主鍵至于where清單中，MySQL就能将該查詢轉換為一個常量。（用在單表查詢時）

• eq_ref：

  唯一性索引，對于每個索引鍵，表中隻有一條記錄與之比對，常見于主鍵或唯一索引掃描。（用在多表查詢時）const和ref_eq差別

• ref：

  非唯一索引掃描，傳回比對某個單獨值的所有行。

  本質上也是一種索引通路，它傳回所有比對某個單獨值的行，然而，

  它可能會找到多個符合條件的行，是以他應該屬于查找和掃描的混合體

• range：

  隻檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引

  一般就是在你的where語句中出現了between、<、>、in等的查詢

  這種範圍掃描索引掃描比全表掃描要好，因為他隻需要開始索引的某一點，而結束語另一點，不用掃描全部索引

• index：

  Full Index Scan,index與ALL差別為index類型隻周遊索引樹。這通常比ALL快，因為索引檔案通常比資料檔案小。

  （也就是說雖然all和index都是讀全表，但index是從索引中讀取的，而all是從硬碟中讀的）

• all：

  FullTable Scan,将周遊全表以找到比對的行

possible_keys：

簡介：

①顯示可能應用在這張表中的索引,一個或多個。

②查詢涉及的字段上若存在索引，則該索引将被列出，但不一定被查詢實際使用。

key：

簡介：

①實際使用的索引。如果為null則沒有使用索引。

②查詢中若使用了覆寫索引，則索引和查詢的select字段重疊。

key_len：

簡介：

①表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度。在不損失精确性的情況下，長度越短越好。

②key_len顯示的值為索引最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表内檢索出的。

ref：

簡介：

①顯示索引那一列被使用了，如果可能的話，是一個常數。那些列或常量被用于查找索引列上的值。

rows：

簡介：

①根據表統計資訊及索引選用情況，大緻估算出找到所需的記錄所需要讀取的行數。

Extra：

• 1.Using filesort：

  說明mysql會對資料使用一個外部的索引排序，而不是按照表内的索引順序進行讀取。

  MySQL中無法利用索引完成排序操作成為“檔案排序”

• 2.Using temporary：

  使用了臨時表儲存中間結果，MySQL在對查詢結果排序時使用臨時表。常見于排序order by 和分組查詢 group by

• 3.USING index：

  表示相應的select操作中使用了覆寫索引（Coveing Index）,避免通路了表的資料行，效率不錯！

  如果同時出現using where，表明索引被用來執行索引鍵值的查找；

  如果沒有同時出現using where，表明索引用來讀取資料而非執行查找動作。

• 4.Using where：

  表明使用了where過濾

• 5.using join buffer：

  使用了連接配接緩存

• 6.impossible where：

  where子句的值總是false，不能用來擷取任何元組

• 7.select tables optimized away：

  在沒有GROUPBY子句的情況下，基于索引優化MIN/MAX操作或者

  對于MyISAM存儲引擎優化COUNT(*)操作，不必等到執行階段再進行計算，

  查詢執行計劃生成的階段即完成優化。

• 8.distinct：

  優化distinct，在找到第一比對的元組後即停止找同樣值的工作

覆寫索引（Covering Index）:

子查詢和連接配接查詢

簡介：

• 連接配接查詢

  ①優點是可以用盡可能少的SQL進行查詢。簡化了應用和資料庫之間的IO調用。

  ②缺點是如果表設計不好，SQL寫得差，會造成資料庫大量的内部IO操作，特别是大量沒必要的全表掃描。使用這種方式必須要麼是确實要讀取的資料量非常大，要麼是能夠通過索引等方式控制住全表掃描的數量。全表掃描在連接配接情況下的消耗可以說是指數性的升高的。

• 子查詢

  ①缺點是應用和資料庫之間的IO調用比較多，損耗了資料庫的帶寬。

  ②優點是對原來的被驅動表來說資料是明确的，可以通過大量的索引，特别是主鍵索引避免全表掃描。

• 總結：

  ①

  用哪種沒有一定之規，要看讀取的資料量、表設計結構、資料庫規模、程式設計等多種因素綜合考慮。

索引優化

案例分析：

案例（索引失效）：

• 1.全值比對我最愛

  

• 2.最佳左字首法則

  如果索引了多例，要遵守最左字首法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。

  

• 3.不在索引列上做任何操作（計算、函數、（自動or手動）類型轉換），會導緻索引失效而轉向全表掃描

  

• 4.存儲引擎不能使用索引中範圍條件右邊的列

  

• 5.盡量使用覆寫索引（隻通路索引的查詢（索引列和查詢列一緻）），減少select*

  

• 6.mysql在使用不等于（！=或者<>）的時候無法使用索引會導緻全表掃描

  

• 7.is null,is not null 也無法使用索引

  

• 8.like以通配符開頭（’$abc…’）mysql索引失效會變成全表掃描操作

  

• 9.字元串不加單引号索引失效

  

• 10.少用or,用它連接配接時會索引失效

  

問題：解決like’%字元串%'索引不被使用的方法？？

• 1、可以使用主鍵索引
• 2、使用覆寫索引，查詢字段必須是建立覆寫索引字段
• 3、當覆寫索引指向的字段是varchar(380)及380以上的字段時，覆寫索引會失效！

小總結:

一般性建議:

• 對于單鍵索引，盡量選擇針對目前query過濾性更好的索引
• 在選擇組合索引的時候，目前Query中過濾性最好的字段在索引字段順序中，位置越靠前越好。
• 在選擇組合索引的時候，盡量選擇可以能包含目前query中的where子句中更多字段的索引
• 盡可能通過分析統計資訊和調整query的寫法來達到選擇合适索引的目的

查詢截取分析

查詢優化

永遠小表驅動大表類似嵌套循環Nested Loop：

order by關鍵字優化：

• ORDER BY子句，盡量使用Index方式排序，避免使用FileSort方式排序
• 盡可能在索引列上完成排序操作，遵照索引建的最佳左字首
• 如果不在索引列上，filesort有兩種算法：mysql就要啟動雙路排序和單路排序。
• 優化政策：

  

GROUP BY關鍵字優化：

• groupby實質是先排序後進行分組，遵照索引建的最佳左字首。
• 當無法使用索引列，增大max_length_for_sort_data參數的設定+增大sort_buffer_size參數的設定
• where高于having,能寫在where限定的條件就不要去having限定了。

慢查詢日志

慢查詢日志是什麼：

慢查詢日志說明：

檢視是否開啟及如何開啟：

開啟慢查詢日志後，什麼樣的SQL參會記錄到慢查詢裡面？

檢視目前多少秒算慢：

SHOW VARIABLES LIKE ‘long_query_time%’;

設定慢的阙值時間：

為什麼設定後看不出變化？

• ①需要重新連接配接或者新開一個回話才能看到修改值。 SHOW VARIABLES LIKE ‘long_query_time%’;
• ②show global variables like ‘long_query_time’;

記錄慢SQL并後續分析：

查詢目前系統中有多少條慢查詢記錄：

日志分析工具mysqldumpshow：

• 檢視mysqldumpshow的幫助資訊：

  

• 工作常用參考：

  

批量資料腳本

Show profiles

Show profiles概述：

①Show profiles是mysql提供可以用來分析目前會話中語句執行的資源消耗情況。可以用于SQL的調優測量。

②預設情況下，參數處于關閉狀态，并儲存最近15次的運作結果。

步驟：

備注：

全局查詢日志

注意：

• 永遠不要在生産環境開啟這個功能。
• 一般在測試環境中使用此功能。

Mysql鎖機制

概述：

鎖的分類：

• 從資料操作的類型（讀、寫）分：

  ①讀鎖（共享鎖）：針對同一份資料，多個讀操作可以同時進行而不會互相影響

  ②寫鎖（排它鎖）：目前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

• 從對資料操作的顆粒度：

  ①表鎖

  ②行鎖

表鎖（偏讀）:

• 特點:

  偏向MyISAM存儲引擎，開銷小，加鎖快，無死鎖，鎖定粒度大，發生鎖沖突的機率最高，并發最低

  

  

  

行鎖（偏寫）:

• 特點:偏向InnoDB存儲引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低，并發度也最高。
• InnoDB與MyISAM的最大不同有兩點：一是支援事務（TRANSACTION）;二是采用了行級鎖。
• 無索引或索引失效時行鎖更新為表鎖。（因為Innodb引擎是根據索引來實作行鎖的，如果不存在索引，那麼就不能對此行加record lock，而mysql為了防止錯誤，隻好全部鎖定了。）
• 間隙鎖危害

  

  

• 面試題：常考如何鎖定一行。

  

• 結論：

  

• 觸發機制：

  innodb的行鎖是根據索引觸發，如果沒有相關的索引，那行鎖将會退化成表鎖（即鎖定整個表裡的行）。而鎖鎖定的是索引即索引樹裡面的資料庫字段的值。

• 連結：mysql 如何觸發行鎖_MySQL的鎖到底有多少内容？

• 優化建議：

  ①盡可能讓所有資料檢索都通過索引來完成，避免無索引行鎖更新為表鎖

  ②合理設計索引，盡量縮小鎖的範圍

  ③盡可能較少檢索條件，避免間隙鎖

  ④盡量控制事務大小，減少鎖定資源量和時間長度

  ⑤盡可能低級别事務隔離

頁鎖：

• 開銷和加鎖時間界于表鎖和行鎖之間：會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。

主重複制

複制的基本原理：

• 底層主要依靠二進制日志檔案（binary log）

  。

複制的基本原則：

• 每個slave隻有一個master
• 每個slave隻能有一個唯一的伺服器ID
• 每個master可以有多個salve

複制最大問題：

• 延時

一主一從常見配置：

注意：

• ①mysql版本一緻且背景以服務運作
• ②主從都配置在【mysqld】結點下，都是小寫

步驟：

• ①主機修改my.ini配置檔案
• ②從機修改my.cnf配置檔案
• ③主機從機都關閉防火牆
• ④在Windows主機上履歷賬戶并授權slave
• ⑤在Linux從機上配置需要複制的主機
• ⑥主機建立庫、建立表、insert記錄，從機複制
• ⑦如何停止從服務複制功能：stop slave;

主從複制的作用：

• 一是

  確定資料安全；做資料的熱備

  ，作為後備資料庫，主資料庫伺服器故障後，可切換到從資料庫繼續工作，避免資料的丢失。

  ①資料分為主表和

  曆史表

  ，

  曆史表是容災備份表

  。
• 二是提升I/O性能；随着日常生産中業務量越來越大,I/O通路頻率越來越高，單機無法滿足，此時做多庫的存儲，有效降低磁盤I/O通路的頻率，提高了單個裝置的I/O性能。
• 三是讀寫分離，使資料庫能支援更大的并發；在報表中尤其重要。由于部分報表sql語句非常的慢，導緻鎖表，影響前台服務。如果前台使用master，報表使用slave，那麼報表sql将不會造成前台鎖，保證了前台速度。

MYCAT簡介：

• 資料庫中間件,前身是阿裡的 cobar
• MYCAT 原理 “攔截”：Mycat 的原理中最重要的一個動詞是“攔截”，它攔截了使用者發送過來的 SQL 語句，首先對 SQL 語 句做了一些特定的分析：如

  分片分析、路由分析、讀寫分離分析、緩存分析

  等，然後将此 SQL 發往後端的真實資料庫，并将傳回的結果做适當的處理，最終再傳回給使用者。
• 這種方式把資料庫的分布式從代碼中解耦出來，程式員察覺不出來背景使用 mycat 還是 mysql。

讀寫分離

• MySQL讀寫分離是指讓master處理寫操作，讓slave處理讀操作，非常适用于讀操作量比較大的場景，可減輕master的壓力。
• 使用mysql-proxy實作mysql的讀寫分離，mysql-proxy實際上是作為後端mysql主從伺服器的代理，它直接接受用戶端的請求，對SQL語句進行分析，判斷出是讀操作還是寫操作，然後分發至對應的mysql伺服器上。

• mysql-proxy是官方提供的mysql中間件産品可以實作負載平衡，讀寫分離，failover等

• MySQL Proxy就是這麼一個中間層代理，簡單的說，MySQL Proxy就是一個連接配接池，負責将前台應用的連接配接請求轉發給背景的資料庫，并且通過使用lua腳本，可以實作複雜的連接配接控制和過濾，進而實作讀寫分離和負載平衡。對于應用來說，MySQL Proxy是完全透明的，應用則隻需要連接配接到MySQL Proxy的監聽端口即可。
• 當然，這樣proxy機器可能成為單點失效，但完全可以使用多個proxy機器做為備援，在應用伺服器的連接配接池配置中配置到多個proxy的連接配接參數即可。