優化mysql資料字典表查詢

myownstars發表於2013-04-08
優化MySql

查詢資料字典表時應新增足夠的過濾條件且儘量避免模糊查詢，以避免掃描多個目錄或開啟多個檔案

 

減少掃描目錄

優

--只查詢test資料庫目錄

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'test'

劣

--掃描data目錄以查詢所有匹配test%的資料庫

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA LIKE 'test%';

 

減少開啟檔案數量

--不同於oracle，mysql沒有專門的系統表空間集中存放資料字典資訊，而是每個表都有各自的.frm檔案，同一個資料字典表，有些列需要開啟相應檔案才能獲取資訊；

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'test'

--table_name列不需要額外開啟檔案，而engine則需要開啟表的.frm檔案才能獲取

具體規則可訪問http://dev.mysql.com/doc/refman/5.5/en/information-schema-optimization.html

 

使用explain檢視是否用到相應優化

劣

--tables表的table_schema/table_name列都是skip_open_table，故訪問tables採用skip_open_table，即不開啟任何表檔案；因為columns表所有列資訊都存放在frm檔案中，故訪問columns則用了open_frm_only，但是掃描了所有資料庫，這應是mysql優化器的缺陷所致(oracle早期版本也有類似問題，即謂詞不能傳遞)，參見第2個sql

mysql> EXPLAIN SELECT B.TABLE_NAME

    -> FROM INFORMATION_SCHEMA.TABLES AS A, INFORMATION_SCHEMA.COLUMNS AS B

    -> WHERE A.TABLE_SCHEMA = 'test'

    -> AND A.TABLE_NAME = 't1'

    -> AND B.TABLE_NAME = A.TABLE_NAME\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: A

         type: ALL

possible_keys: NULL

          key: TABLE_SCHEMA,TABLE_NAME

      key_len: NULL

          ref: NULL

         rows: NULL

        Extra: Using where; Skip_open_table; Scanned 0 databases

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: B

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: NULL

        Extra: Using where; Open_frm_only; Scanned all databases;

               Using join buffer

 

優

--訪問columns表時不需掃描所有資料庫

mysql> EXPLAIN SELECT B.TABLE_NAME

    -> FROM INFORMATION_SCHEMA.TABLES AS A, INFORMATION_SCHEMA.COLUMNS AS B

    -> WHERE A.TABLE_SCHEMA = 'test'

-> AND A.TABLE_NAME = 't1'

-> AND B.TABLE_NAME = 't1'

    -> AND B.TABLE_NAME = 'test'\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: A

         type: ALL

possible_keys: NULL

          key: TABLE_SCHEMA,TABLE_NAME

      key_len: NULL

          ref: NULL

         rows: NULL

        Extra: Using where; Skip_open_table; Scanned 0 databases

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: B

         type: ALL

possible_keys: NULL

          key: TABLE_SCHEMA,TABLE_NAME

      key_len: NULL

          ref: NULL

         rows: NULL

        Extra: Using where; Open_frm_only; Scanned 0 databases;

               Using join buffer

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15480802/viewspace-757962/，如需轉載，請註明出處，否則將追究法律責任。

相關文章