MySQL 第九天（核心最佳化三）

標籤：大小   margin   特性   節點   存在   引擎   技術   values   查詢   

一．昨天內容回顧

1. 索引設計依據

   與資料表有關係的sql語句都統計出來

   where order by or等等條件的欄位適當做索引

   原則：

   頻率高的sql語句

   執行時間長的sql語句

   商務邏輯重要的sql語句

    

   什麼樣子欄位不適合做索引？

   內容比較單調的欄位不適合做索引

2. 首碼索引

   一個欄位只取前邊的幾位內容做索引

   好處：索引空間比較少、運行速度快

   前n位做索引，前n位要具備唯一標識目前記錄的特點

3. 全文索引

   Mysql5.5 只MYisam儲存引擎可以實現

   Mysql5.6 Myisam和Innodb儲存引擎都可以實現

   fulltext index 索引名稱 (欄位,欄位)

    

   select * from 表名 where 欄位 like ‘%內容%‘ or 欄位 like ‘%內容%‘;

   select * from 表名 match(欄位,欄位) against("內容1,內容2");

   match(欄位,欄位) against("內容1,內容2")

    

4. 索引結構

   Mysql的索引結構是B+Tree結構

   索引就是資料結構(自然有演算法)，演算法可以保證資料非常快速被找到

    

   非聚集(Myisam)

   葉子節點的關鍵字(索引欄位內容) 與 記錄的物理地址對應

   聚集(Innodb)

   主(鍵)索引：葉子節點的關鍵字 與 整條記錄對應

   非主(唯一/普通/全文)索引：葉子節點的關鍵字 與 主鍵關鍵字對應

5. 查詢快取

   開啟緩衝，開闢緩衝空間(64MB)

   緩衝失效：表 或 資料 內容改變

   不使用緩衝：sql語句有變化的資訊，例如目前時間、隨機數

   同一個商務邏輯的多個sql語句，有不同結構(空格變化、大小寫變哈)的變化，每個樣子的sql語句會分別設定緩衝

6. 分區、分表設計

   分區表演算法(Mysql)：key hash range list

   （php代碼不會發生變化）

    

   分區增加或減少：

   減少：hash/range/list類型演算法會丟失對應的資料

    

7. 垂直分表

   把一個資料表的多個欄位進行拆分，分別分配到不同的資料表中

   涉及的演算法是php層面的

    

8. 架構設計

   主從模式(讀寫分離、一主多從)

   主伺服器負責"寫"資料，從伺服器負責"讀"資料

   "主" 會 自動 給"從" 同步資料(mysql本身技術)

   通過"負載平衡"可以平均地從 從伺服器 獲得資料

9. 慢查詢日誌設定

   show variables like ‘slow_query_log%‘;

   開啟慢查詢日誌開關

   設定時間閥值

   二．Mysql最佳化
   1. 大量寫入記錄資訊

   保證資料非常快地寫入到資料庫中

   insert into 表名 values (),(),(),();

   以上一個insert語句可以同時寫入多條記錄資訊，但是不要寫入太多

   避免意外情況發生。

   可以一次少寫一些,例如每次寫入1000條，這樣100萬的記錄資訊，執行1000次insert語句就可以了。

   分批分時間把資料寫入到資料庫中。

    

   以上設計寫入大量資料的方法損耗的時間：

   寫入資料(1000條)----->為1000條資料維護索引

   寫入資料(1000條)----->為第2個1000條資料維護索引

   ......

   寫入資料(1000條)----->為第1000個1000條資料維護索引

    

   以上設計寫入100萬條記錄資訊，時間主要都被"維護索引"給佔據了

   如果做最佳化：就可以減少索引的維護，達到整體已耗用時間變少。

   (索引維護不需要做1000次，就想做一次)

    

   解決：

   先把索引給停掉，專門把資料先寫入到資料庫中，最後在一次性維護索引

    

   1.1 Myisam資料表

10. 資料表中已經存在資料(索引已經存在一部分)

    alter table 表名 disable keys;

    大量寫入資料

    alter table 表名 enable keys; //最後統一維護索引

11. 資料表中沒有資料(索引內部沒有東西)

    alter table 表名 drop primary key ,drop index 索引名稱(唯一/普通/全文);

    大量寫入資料

    alter table 表名 add primary key(id),(唯一/全文)index 索引名 (欄位);

    1.2 Innodb資料表

    該儲存引擎支援"事務"

    該特性使得我們可以一次性寫入大量sql語句

    具體操作：

    start transaction;

    大量資料寫入(100萬條記錄資訊 insert被執行1000次)

    事務內部執行的insert的時候，資料還沒有寫入到資料庫

    只有資料真實寫入到資料庫才會執行"索引"維護

    commit;

    commit執行完畢後最後會自動維護一次"索引";

    2. 單表、多表查詢

    資料庫操作有的時候設計到 連表查詢、子查詢操作。

    複合查詢一般要涉及到多個資料表，

    多個資料表一起做查詢好處：sql語句邏輯清晰、簡單

    其中不妥當的地方是：消耗資源比較多、時間長

    不利於資料表的並發處理,因為需要長時間鎖住多個表

    例如：

    查詢每個品牌下商品的總數量（Goods/Brand）

    Goods:id name bd_id

    Brand: bd_id name

    select b.bd_id,b.name,count(g.*) from Brand b join Goods g on b.bd_id=g.bd_id group by b.bd_id;

    以上sql語句總已耗用時間是5s

    但是業務要求是資料庫的並發性要高,就需要把"多個查詢" 變為 "單表查詢"

    步驟：

    ① select bd_id,count(*) from Goods group by bd_id; //查詢每個品牌的商品數量 //3s

    ② select bd_id,name from Brand; //3s

    ③ 在php通過邏輯代碼整合① 和 ② //1s

    3. limit使用

    資料分頁使用limit;

    limit 位移量，長度(每頁條數);

    位移量：（當前頁碼-1）*每頁條數

     

    分頁實現：

    每頁獲得10條資訊：

    limit 0,10;

    limit 10,10;

    limit 20,10;

    limit 30,10;

    limit 990,10; //第100頁

    limit 9990,10; //第1000頁

    limit 99990,10; //第10000頁

    limit 999990,10; //第100000頁

    limit 1499990,10; //第150000頁

     

    limit 1500000,10; //第150001頁

    select * from emp limit 1500000,10; //慢 1秒多時間

    select * from emp where empno>1600001 limit 10; //快 0.00秒級

     

     

    資料表目前有empno主鍵索引：

     

     

    limit 位移量，長度；已耗用時間較長：

    單純運行limit 已耗用時間比較長，內部沒有使用索引，翻頁效果 之前頁碼的資訊給獲得出來，但是"越"過去，因此比較浪費時間

     

    現在對獲得相同頁碼資訊的sql語句進行最佳化

    由單純limit變為 where 和 limit的組合：

    執行速度明顯加快，因為其有使用where條件欄位的索引

     

    4. order by null

    強制不排序

    有的sql語句在執行的時候，本身預設會有排序效果

    但是有的時候我們的業務不需要排序效果，就可以進行強制限制，進而"節省預設排序"帶來的資源消耗。

     

    group by 欄位；

    獲得的結果在預設情況下會根據"分組欄位"進行排序：

    order by null強制不排序，節省對應資源：

     

MySQL 第九天（核心最佳化三）