MySQL 第八天（核心最佳化二）

標籤：資料檢索   like   tin   多個   密碼   伺服器   phi   總結   資料   

一．昨天內容回顧

1. 儲存引擎

   儲存資料的格式(技術)，不同格式體現特性不一樣

   myisam

   ① 結構、資料、索引 檔案單獨儲存

   ② 存入資料順序(不考慮主鍵順序) ，寫入資料速度快

   ③ 並發性，低，鎖整張表

   ④ 壓縮機制

   innodb

   ① 結構有單獨檔案，資料和索引合并到一個檔案中

   (通過設定，可以給每個表設定一個"資料/索引"檔案)

   ② 存入資料順序(給考慮主索引值的先後順序，數值大小排序儲存)

   ③ 並發性，高，鎖行

2. 欄位選擇

   ① 欄位選取佔據空間小的(bigint int mediumint smallint tinyint)

   ② 內容長度固定，選取char類型使用 varchar()

   ③ 資料最好儲存為整型的(時間、set/enum、ip地址)

3. 逆範式

    

4. 索引

   索引是資料結構，其內部有演算法(規律、規則)

   1. 四種類型、建立、刪除

   四種：主鍵、唯一[例如使用者名稱唯一]、普通、全文索引 ，複合索引

   1. 執行計畫

   針對查詢select語句起作用

   sql語句在沒有執行之前把所有需要的資源都準備好，我們可以預先 查看下

   我們關心sql語句是否用到索引

   explain sql語句\G;

   1. 適合場合

   ① where

   ② 排序欄位

   select * from 表名 order by 欄位 limit 1000000,50; 不會用索引

   select * from 表名 order by 欄位 limit 50; 用索引

   select * from 表名 order by 欄位 limit 100，50; 用索引

   select * from 表名 order by 欄位 limit 位移量，50; 用索引

   位移量如果超過10萬，就不會使用索引。

   ③ 索引覆蓋

   ④ 連表查詢，外鍵設定索引

   1. 使用原則

   ① 欄位獨立

   ② 左原則(模糊查詢)

   ③ 複合索引

   ④ or原則

5. 索引
   1. 設計依據

   ① 被頻繁使用的欄位設定索引

   欄位被頻繁用在where和order等條件裡邊。

   資料表建立完畢，要預估那些欄位被經常使用，就給其建立索引

   ② 執行時間長的sql語句考慮設計索引

   可以利用"慢查詢日誌"收集這樣的sql語句並最佳化設計索引

   ③ 邏輯非常重要的sql語句考慮設計索引

   例如商城系統裡邊，會員給自己賬戶儲值就比較重要

   還有會員下訂單購物，進行付款的時候也比較重要。

    

   ④ 欄位內容足夠花樣化，可以考慮設計索引

   反面教材，性別不能設計索引(內容的取值非常少)

   2. 首碼索引

   什麼是首碼索引：

   如果一個欄位的內容的前邊的n位資訊已經足夠可以標識當前的欄位內容，就可以把欄位的前n位獲得出來並建立索引，

   通過欄位內容前n位建立的索引就稱為"首碼索引"。

    

   例如：

   關偉

   呂紀無

   劉尚

   王雲斐

    

    

   以上欄位內容，通過前1位，就可以唯一標識當前欄位內容，這樣就把前1位取出來建立索引

   好處：索引佔據的物理空間要比較小，這樣的索引運行速度快、效率高，對mysql整體效能提升有很大協助。

    

   具體操作：

   alter table 表名 add key (欄位(位元))

    

   思考：

       到底是前幾位可以標識當前欄位內容

       通過substring獲得欄位的前n為資訊，從1開始遞增時擷取

       擷取的時候去除重複的，計算總個數，不斷增加n的數值，直至總個數穩定

       此時n的數值就是適合做"首碼索引"的數字。

    

       索引是給sql語句做最佳化

       首碼索引，是給索引做的最佳化

    

       獲得欄位的前n位：substring(欄位,開始位置,長度n) mysql函數

   同上可以知道，通過前11位，可以給epassword欄位設定索引。

    

    

   3. in條件索引使用
   4. 全文索引

   什麼是全文索引：

   其他索引是把欄位的內容作為一個整體進行索引設計

   全文索引，類似我們有一篇作文，把作文中的一些關鍵字給擷取出來當成是索引內容。

   具體理解，就是做like模糊查詢，類似baidu搜尋一些關鍵字效果。

    

    

    

    

    

   全文索引注意：

   ① 欄位類型必須為varchar/char/text類型

   ② mysql 5.6.4之前只有Myisam支援，5.6.4之後則Myisam和innodb都支援。

   ③ mysql中的全文索引目前只支援英文(不支援中文)，如果需要支援中文可以使用sphinx

   ④ 現實生產中mysql的全文索引不經常使用，sphinx常使用

   mysql全文索引自作聰明，對關鍵字的收錄有自己的考慮。

    

   版本是5.5.27，因此只有Myisam支援全文索引。

    

    

   4.1 具體操作

   CREATE TABLE articles (

   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

   title VARCHAR(200),

   body TEXT

   )engine=myisam charset utf8;

    

   INSERT INTO articles (title,body) VALUES

   (‘MySQL Tutorial‘,‘DBMS stands for DataBase ...‘),

   (‘How To Use MySQL Well‘,‘After you went through a ...‘),

   (‘Optimizing MySQL‘,‘In this tutorial we will show ...‘),

   (‘1001 MySQL Tricks‘,‘1. Never run mysqld as root. 2. ...‘),

   (‘MySQL vs. YourSQL‘,‘In the following database comparison ...‘),

   (‘MySQL Security‘,‘When configured properly, MySQL ...‘);

    

   alter table articles add fulltext index `index_body` (body);

    

   現在已經有了一個index_body的全文索引：

   因此類似如下sql語句就可以使用到索引了

   select * from articles where body like ‘%內容%‘;

   但是sql語句需要變形為：

   select * from articles where match(欄位body) against(內容);

    

   4.2 複合全文索引

   alter table articles add fulltext index `index_fu` (title,body);

    

   select * from articles where title like ‘%內容%‘ and body like ‘%內容%‘
   

    

   5. 索引結構(瞭解)

   索引的資料結構

   主要討論兩種：Myisam 和 Innodb

   Mysql的資料結構都是B+tree結構

    

   資料結構：資料以一種規律的、規則的方式組織在一個格式裡邊，可以保證我們獲得資料的快速性。

   5.1 Myisam索引結構

   該Myisam索引結構稱為"非彙總型"

    

   說明，主鍵內容在該索引裡邊通過演算法開始被查詢、獲得，並藉助"指標"向下級尋找，直到找到對應的葉子節點。

   葉子節點裡邊有該關鍵字對應的記錄的物理地址，進而獲得對應的記錄資訊。

    

   索引運行原理：快速定位主鍵id值，獲得對應記錄物理地址，獲得記錄資訊。

    

   每個主鍵id值都是一個節點，節點本身有指標。

   最底層的節點稱為"葉子節點"，該節點與記錄的物理地址直接聯絡

   節點從上到下的層次數是索引結構的高度

   每層節點的數目稱為結構的寬度

   結構的寬度、高度的數目由mysql底層演算法計算獲得(過高、過寬都不利於資料的快速擷取)

    

   Myisam其他索引結構與主鍵索引結構一致。

    

   通過索引結構，我們看到了 索引部分 和 資料部分是分離的，它們之間通過物理地址進行聯絡。

    

   5.2 Innodb索引結構

    

   該Innodb索引結構稱為"彙總型"

   彙總在："索引"和"資料"是合并在一個檔案裡邊的。

    

   表示innodb的其他索引(唯一、普通)

   該(使用者名稱字)其他索引運行原理：

   ① 通過索引結構內部的演算法快速定位該名字對應的"葉子節點"

   ② 葉子節點 裡邊對應的是關鍵字的記錄主鍵id值

   ③ 通過記錄主鍵id值走主鍵索引即可

    

    

   innodb(主鍵)索引運行原理：

   ① 通過索引結構快速定位主鍵id值對應的"葉子節點"

   ② 該葉子節點 裡邊直接與整條記錄資訊進行對應(而在Myisam裡邊，葉子節點與物理地址對應)

    

   innodb索引：

   普通索引關鍵字----->記錄的主鍵id值------>記錄的整條資訊

    

   三．緩衝設定

   有的被頻繁執行的sql語句，比較消耗時間、消耗系統資源(沒的最佳化可做)

   並且每次獲得資料還不太發生變化

   那麼就把這個sql語句獲得資訊給緩衝起來，供後續執行使用

   這樣非常節省系統資源。

   1. 具體使用

    

    

    

    

6. 緩衝失效

   資料表的資料有變化 或者 資料表結構有變化，則緩衝失效。

    

   3. 什麼情況不使用緩衝

   sql語句每次獲得資料有變化。

   例如有 時間資訊、隨機數等

    

   4. 產生多個緩衝

   產生緩衝的sql語句對"空格"、"大小寫"比較敏感

   相同結果的sql語句，由於空格、大小寫問題就會分別產生緩衝。

    

   5. 不使用緩衝

   sql_no_cache 不進行緩衝

    

   6. 查看緩衝空間使用方式

    

   總結：

7. 索引依據：頻繁、時間長、邏輯重要
8. 首碼索引
9. 全文索引(搜尋引擎 baidu google 資料檢索)
10. 索引結構:Myisam innodb(主鍵、其他索引)
11. 緩衝設定

     

    四．分表設計

    一個資料表裡邊儲存的記錄資訊太多了，記錄的條數達到1-2億條資訊。

    這時該資料表的活性就大大降低，資料表的運行速度就比較慢、效率低下，影響mysql資料庫的整體效能。

    現在設定一個水平分割，把許多的記錄資訊分別儲存到不同的資料表裡邊，這樣每個表格儲存體的記錄就比較少，該表的活性大大提高。

     

    分表設計的兩種模式：

    ① 邏輯方式分表

        mysql資料庫本身就有分表技術，該方式的分表可以節省php的邏輯處理。

     

        

    ② 物理方式分表

        自己手動建立多個資料表出來

        php程式需要考慮分表演算法：資料往哪個表寫，從哪個表讀

        

     

    1. 示範邏輯分表設計

    mysql本身就支援的分表技術

     

    以上邏輯分表設計，在php程式裡邊無需設定額外演算法代碼，還像以往一樣直接對goods資料表進行操作即可，mysql本身會考慮分表的演算法。

    2. 四種格式的邏輯分表

    具體為：key hash ---->[求餘方式]

    range list ---->[範圍方式]

     

    注意：分表欄位必須是主鍵 或 主鍵的一部分

    2.1 key分表

    partition by key(條件欄位id) partitions 10;

    2.2 hash分表

    根據運算式/欄位方式進行分表設計

    partition by hash(運算式/欄位) partitions 數量;

     

     

     

     

     

     

    2.3 range分表

    根據 欄位/運算式 是否滿足某個範圍條件進行分表設計

    partition by range(year(pubdate))(

    partition hou70 values less than (1980),

    partition hou80 values less than (1990),

    partition hou90 values less than (2000),

    partition hou00 values less than (2010)

    )

     

     

    2.4 list分表

    根據 運算式/欄位 的內容值是否在某個"列表"中進行分表設計。

    partition by list(month(pubdate))(

    partition spring values in (3,4,5),

    partition summer values in (6,7,8),

    partition autumn values in (9,10,11),

    partition winter values in (12,1,2)

    )

    3. 分表管理

    具體就是對已經存在的分表進行增加、減少操作。

     

    增加分表 不會引起資料丟失。

    減少分表 在range/list領域會造成資料丟失

    在key/hash領域不會造成資料丟失，

             這兩個領域在進行減少分表的同時就把全部資料重新整合到存在的表中，

    key/hash兩種分表與商務邏輯關聯不緊密。

     

    增加分表

        求餘方式： key/hash

        > alter table 表名 add partitions 數量;

     

        範圍方式： range/list

        > alter table 表名 add partition(

    partition 名稱 values less than (常量)

    或

    partition 名稱 in (n,n,n)

    );

    3.2 刪除分表

    求餘方式(key/hash):

    >alter table 表名 coalesce partition 12;

    刪除分表不會造成資料丟失，每次分表資料都重新整合到存在的分表裡邊。

    範圍方式(range/list):

    >alter table 表名 drop partition 分區名稱;

    刪除分表會造成資料丟失

    ① key/hash方式不會遺失資料

     

    ② range/list會遺失資料

    給range分表寫4條記錄：

     

     

    把"hou80"的分區刪除,從查詢結果看對應分表的資料也丟失了。

     

     

    4. 物理分表設計

     

    以上goods的物理分表需要增加php的演算法邏輯：

    需要計算記錄從哪個表讀、給哪個表寫。

    4.1 php對物理分表的操作

     

     

    5. 垂直分表

    水平分表：是把一個表的全部記錄資訊分別儲存到不同的分表之中。

    垂直分表：是把一個表的全部欄位分別儲存到不同的表裡邊。

     

    有的時候，一個資料表設計好了，裡邊有許多欄位，但是這些欄位有的是經常使用的，有的是不常用的。

    例如，一個資料表有20個欄位，其中10個欄位是常用的，後10個欄位是不常用的

    那麼在操作常用欄位的時候，就不得不給其他不常用的欄位也分配一定的資源進行操作。

     

    資料表：

    會員表： user_id 登入名稱 密碼 郵箱 手機號碼身高 體重 性別 家庭地址 社會安全號碼碼

    以上表，紅色是常用的，藍色的是不常用的

    為了使得常用欄位運行速度更快、效率更高，把常用欄位給挑選出來,因此資料表做以下設計：

    會員表(主)欄位：user_id 登入名稱 密碼 郵箱 手機號碼

    會員表(輔)欄位：user_id身高 體重 性別 家庭地址 社會安全號碼碼

     

    以上把會員表根據欄位是否常用給分為兩個表的過程就是垂直分表。

12. 架構設計

    架構設計也稱為叢集設計：由多台mysql伺服器共同支撐網站的運行，每台伺服器分擔的工作就比較少，運行速度快、效率高。

     

    mysql資料庫在啟動並執行時候一般查詢/寫入的sql語句比例為：7/1

    並且查詢消耗的資源比寫入要更多。

    因此可以設計一個"主從模式"的叢集，與之前redis的主從模式使用模式一致。

     

    維護備份的叢集架構：

     

    主從模式的叢集架構：

    六．慢查詢日誌收集

    我們要把系統裡邊一些執行速度非常慢的sql語句給收集起來，並做分析最佳化，使得其執行速度加快。

     

     

     

     

     

     

     

    總結：

13. 水平分表設計

    分的是資料記錄

    1. 邏輯分表

    求餘：key/hash 範圍：range/list

    建立/增加分表：建立表時、分表建立完畢還可以增加

    刪除分表：key/hash不會造成資料丟失

    range/list能造成資料丟失

     

    1. 物理分表

    php代碼需要增加演算法邏輯

14. 垂直分表

    分的是表的欄位

     

15. 架構設計

    架構叢集有兩種方式：

    ① 互為備份

    ② 主從模式[常用]

16. 慢查詢日誌收集

     

MySQL 第八天（核心最佳化二）