优化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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-757962/