mysql 進階學習筆記

標籤：位置   做了   順序   最佳化器   複合   left join   會話   time   appname   

進階知識講解：

一、索引知識：

　　1. 索引的概念：一種用於快速尋找（排序）的資料結構。

　　2. mysql innerdb引擎試用Btree樹來儲存索引值。

　　3. 聚簇索引概念：一般都是主鍵做聚簇索引，它的特點就是表行資料的儲存位置順序和索引存的值的順序一樣。

　　注意：1.主鍵預設會建立聚簇索引 2.sqlserver中好像還可以自行建立聚簇索引，此時主鍵索引就不會是聚簇索引了。

　　4.唯一索引：就是列資料是唯一的，比如社會安全號碼碼。主鍵索引預設也是唯一的資料。

　　5.覆蓋索引（索引覆蓋）的概念：
　　覆蓋索引不是指某種索引類型，而是說 select 欄位剛好和索引建立的欄位在個數、順序一樣。如果存在這樣的話，那麼都會使用到對應的索引。並且取值都是在索引中獲得而不需要從資料行中得到。
　　ps: explain select appname,appprovider from tb_app
　　

從中可以看到 possible_keys是null,但是key實際是使用到了idx_appname_appprovider 這個索引.

6.建立索引的注意點：
　　1. 一般都是在where、group by、order by 和 聯表查詢的欄位建立索引。
　　2. 非主鍵索引尋找資料的方式是間接通過主鍵索引的值尋找？？？
　　3. 聯表查詢碰到left、right join 的方式時，一般都是在詞表建立索引，比如 A left join B 因為A表行是返回所有行資料，根據小表驅動大表的原則，所以要在B表中建立索引。
　　4. 一般都是以建立複合索引（多個列組成的索引）為優先,原因是：1. 複合索引可以節約空間 2.表的索引越多對錶更新表的效率越差。3. 可能有時候使用到覆蓋索引。

7. 索引失效問題：
　　1. where 條件欄位的查詢順序必須要和建立索引的欄位順序一樣，才能使用到索引。比如 建立的 idx_name_age（建立在name和age的索引），那麼where 欄位的必須要有name （第一個並必須要有才能用到這個索引尋找順序？？？
　　mysql 實踐：name欄位在第二個或是第三個位置都是可以使用到索引的，為什麼呢 因為mysql最佳化器自動解析索引的正確使用方式），age 欄位如果不存在還是會用到這個索引，單使用到這個索引的部分索引。
　　length 欄位值會小。（帶頭大哥不能斷，就是說複合索引的第一個欄位必須出現）

二、鎖
　　1.鎖的分類：讀、寫鎖兩種分類

1.1 表鎖
　　讀表鎖文法： lock table 表名 read 。特點：讀鎖是一個共用鎖定，也就是說不同的會話都可以select 表的內容,但是不能更新表的內容，表鎖是MyIsim 支援。沒有事務層級的說法。
　　寫表鎖文法： lock table 表名 write。特點：寫鎖是排它鎖，也就是說只要給表設定了寫鎖，那麼會話之間的操作就是排斥的。給表A設定寫鎖，那麼兩個會話對A的操作是不能同時進行的（除了select操作）
　　解鎖文法： unlock tables.

2.鎖的粒度：表鎖，行鎖，MyISAM引擎表鎖和InnoDB行鎖.

　　表級鎖：開銷小，加鎖塊；不會出現死結，鎖定粒度大，發生鎖衝突的機率最高，並發度最低。

　　　　行級鎖：開銷大，加鎖慢；會出現死結；鎖定粒度最小，發生鎖衝突的機率最低，並發性也最高。

　　頁面鎖：開銷和加鎖界於表鎖和行鎖之間，會出現死結；鎖定粒度界與表鎖和行鎖之間，並發一般。

2.1 行鎖
　　在操作行鎖的時候，首先要設定innodb 引擎的自動認可為0 set autocommite=0，然後手動提交更新操作 commit。

2.2 行鎖支援事務.

2.2.1 事務有四種隔離等級：為了有效保證並發讀取資料的正確性，提出的交易隔離等級.
　　資料庫事務的隔離等級有4個，由低到高依次為Read uncommitted(未授權讀取、讀未提交)、Read committed（授權讀取、讀提交）、Repeatable read（可重複讀取）、Serializable（序列化），這四個層級可以逐個解決髒讀、不可重複讀取、幻讀這幾類問題。

2.2.2 並發事務引起的幾種問題：（表鎖是沒有事務並發問題出現的，因為每次都是鎖住表，不存在髒讀或是幻讀的情況）
　　1、更新丟失
　　兩個事務都同時更新一行資料，一個事務對資料的更新把另一個事務對資料的更新覆蓋了。這是因為系統沒有執行任何的鎖操作，因此並發事務並沒有被隔離開來。
　　2、髒讀
　　一個事務讀取到了另一個事務未提交的資料操作結果。
　　3、不可重複讀取（Non-repeatable Reads）：一個事務對同一行資料重複讀取兩次，但是卻得到了不同的結果。
　　包括以下情況：
　　（1） 虛讀：事務T1讀取某一資料後，事務T2對其做了修改，當事務T1再次讀該資料時得到與前一次不同的值。
　　（2） 幻讀（Phantom Reads）：事務在操作過程中進行兩次查詢，第二次查詢的結果包含了第一次查詢中未出現的資料或者缺少了第一次查詢中出現的資料（這裡並不要求兩次查詢的SQL語句相同）。這是因為在兩次查詢過程中有另外一個事務插入資料造成的。


3.查看資料庫鎖的狀態：
　　show open tables：查看資料庫那些表上鎖了。



三、 慢查詢日誌

　　1.常用explain sql 來分析sql的執行情況

　　2. show profile（首先要設定資料庫的profile為1） 來查看sql的執行循環情況

　　　　show profiles;

　　　　show profile cpu,block io for query 1; 查看執行的query id 等於1 的那條sql執行情況（cpu,block io CPU和I/O 操作的情況）。

3.開啟慢查詢日誌：

　　1.查看慢查詢查詢是否開啟情況： show variables like ‘%slow_query_log%‘;

　　2.設定全域的慢查詢日誌開啟: set global slow_query_log=1;

　　3. 查看慢查詢的設定時間： show variables like ‘%long_time_log%‘; 預設是10s

　　4. 設定慢查詢的闕值時間: set global long_time_log=3； 設定慢查詢的闕值時間為3s

mysql 進階學習筆記