Mysql DOC閱讀筆記

標籤：

Mysql DOC閱讀筆記

轉自我的Github

Speed of SELECT Statements

1. 合理利用索引
2. 隔離調試查詢中花費高的部分，例如函數調用是在結果集中的行執行還是全表中的行執行
3. 最小化全表掃描的數量，特別是在大表中
4. 定期使用ANALYZE TABLE語句更新表的統計資料，使查詢最佳化工具可以做出更合理的最佳化。
5. 學習各個儲存引擎的特殊調試，索引和配置
6. 最佳化單條唯讀查詢事務
7. 避免使用難以理解的方式編寫QUERY,因為查詢最佳化工具可能使用的還是同樣的最佳化決策,不要犧牲SQL的可讀性
8. 如果效能問題不能通過基本的GUIDELINES解決，那麼使用EXPLAIN語句查看更具體的資訊
9. 調整MySQL所使用到的用作緩衝的記憶體地區，例如(InnoDB buffer pool, MyISAM key cache, MySQL query cache)
10. 小心處理鎖的問題

EXPLAIN Statement

結果欄位

• id : SELECT的標識符
• select_type : SELECT的類型
  • SIMPLE : Simple SELECT (not using UNION or subqueries)
  • PRIMARY : Outermost SELECT
  • UNION : Second or later SELECT statement in a UNION
  • DEPENDENT UNION : Second or later SELECT statement in a UNION, dependent on outer query
  • UNION RESULT RESULT of a UNION
  • SUBQUERY First SELECT in subquery
  • DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query.
  • DERIVED Derived table SELECT (subquery in FROM clause)
  • MATERIALIZED Materialized subquery
  • UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
• table : The name of the table to which the row of output refers
  • <unionM,N> M,N為子查詢的id
  • <deriveN> N為子查詢的id
  • <subqueryN> N為子查詢的id
• partitions : 使用的分區
• type : JOIN TYPE
• possible_keys : 可選的索引
• key : 查詢用到的key或者index
• key_len : The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL. Note that the value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses
• ref : The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.
• rows : The rows column indicates the number of rows MySQL believes it must examine to execute the query
• filtered : The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables.
• Extra : This column contains additional information about how MySQL resolves the query. See Explain Extra Information

MySQL Index

1. 多列索引，查詢條件需要按照列的順序並且起始列為索引的起始列，中間不能有間隔

2. 使用MIN()或者MAX()查詢的時候多列索引的使用也遵循最左列的原則，即WHERE中要先使用多列索引中的最左列，那麼查詢最佳化工具會將MIN()和MAX()做一個key上的掃描，然後將常數的結果進行替換。例如: SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;

3. LIKE也能使用索引，只要參數是常量，並且不以萬用字元開頭
4. 在不通列的對比中如果要使用索引，需要兩個列的資料類型一樣，CHAR(10)和VARCHAR(10)被認為是一樣的。
5. 多列索引的實現: A multiple-column index can be considered a sorted array, the rows of which contain values that are created by concatenating the values of the indexed columns.
6. 多列索引詳情見Multiple Index
7. Mysql對Where的最佳化詳情見Where Optimization
8. InnoDB的聚簇索引含有列資料，優先建立複合式索引而不是每一列的單獨索引。InnoDB的二級索引的葉子節點中儲存的是PK，而不是行號。MyISAM的索引是非聚簇索引，葉子節點中是行指標。See more
9. 在where條件中查詢條件對索引使用的影響:

Any index that does not span all AND levels in the WHERE clause is not used to optimize the query. In other words, to be able to use an index, a prefix of the index must be used in every AND group. The following WHERE clauses use indexes:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3   /* index = 1 OR index = 2 */... WHERE index=1 OR A=10 AND index=2   /* optimized like "index_part1=‘hello‘" */... WHERE index_part1=‘hello‘ AND index_part3=5   /* Can use index on index1 but not on index2 or index3 */... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;These WHERE clauses do not use indexes:   /* index_part1 is not used */... WHERE index_part2=1 AND index_part3=2   /*  Index is not used in both parts of the WHERE clause  */... WHERE index=1 OR A=10   /* No index spans all rows  */... WHERE index_part1=1 OR index_part2=10

buffer pool

buffer pool 是Mysql中用來存放InnoDB的表和索引資料的記憶體地區， 這些記憶體地區被劃分成頁。buffer pool使用使用linked list作為pages的實現。頁的交換使用LRU演算法，在大記憶體的系統中，你可以將buffer pool劃分成多個buffer pool instances。 innodb_buffer_pool_size中配置的記憶體大小被所有buffer pool執行個體所分割，多buffer pool執行個體更適給Innodb buffer pool劃分幾個G以上空間的應用，每一個執行個體都可以得到一個1G或者以上大小的空間。

Read-Only Transactions

如果InnoDB可以提前知道一個事務是唯讀事務，那麼它就可以避免使用transaction ID(TRX_ID)。Transaction ID只需要在語句存在更新或者讀語句中存在鎖的情況中使用。 InnoDB是這樣檢測唯讀事務的:

1. 事務以START TRANSACTION READ ONLY語句開始，但是這種情況仍可以對session-specific暫存資料表進行更新，因為對這些表進行更新不影響其他事務。
2. autocommit標誌位開啟的情況下，所有事務都是一條但語句，那麼沒有更新的語句將會被封裝成唯讀事務。
3. 事務沒有用READ ONLY選項，但是還沒有更新語句或者明確的鎖語句，這時候事務是一個唯讀事務，知道遇到了一個明確的鎖或者更新語句。 所以，對於查詢語句可以將它們放在一個READ ONLY事務中，或者在執行select語句之前開啟autocommit選項，或者只是簡單的不要吧DML放在DQL中間。

Otherinnodb行鎖的實現方式

對於innodb的表而言，insert、update、delete等操作雖然都是加行級鎖，但這些行鎖都是通過給索引上的索引項目加鎖來實現的，這就意味著：只有通過索引條件檢索資料，innodb才能使用行級鎖，否則，innodb將使用表級鎖。 * 在不通過索引條件檢索的時候，innodb使用的是表鎖，不是行鎖。 例如：

create table tab_no_index(id int,name varchar(10)) engion=innodb; create table tab_with_index(id int,name varchar(10),key id_idx(id)) engion=innodb; 語句select * from tab_no_index where id=1 for update;會加表鎖，而select * from tab_with_index where id=1 for update;會加行鎖。

• 雖然訪問不同行的記錄，但是如果是使用相同的索引鍵，仍然會出現鎖衝突。 例如，上例表tab_with_index中有兩條記錄為(1,’1’)和(1,’4’)，則select * from tab_with_index where id=1 and name=’1’ for update;會對這兩條記錄都加鎖。

• 當表有多個索引時，不同的事務可以使用不同的索引鎖定不同的行。此外，不論是使用主鍵索引、唯一索引或普通索引，innodb都會使用行鎖對資料加鎖。

innodb的間隙鎖（Next-key鎖）

當使用範圍條件而不是相等條件檢索資料，並請求共用或獨佔鎖定時，innodb會給合格已有資料記錄的索引項目加鎖；對於索引值在條件範圍內但並不 存在的記錄，叫做“間隙”，innodb也會對這個“間隙”加鎖。例如，emp表中只有101條記錄，其中empid為索引鍵，值分別為 1,2,……,100,101，語句select * from emp where empid>100 for update;不僅會對101記錄加鎖，還會對empid大於101（這些記錄不存在）的“間隙”加鎖。

 

Mysql DOC閱讀筆記