优化mysql数据字典表查询
查询数据字典表时应添加足够的过滤条件且尽量避免模糊查询，以避免扫描多个目录或打开多个文件
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'test%';
--不同于oracle，mysql没有专门的系统表空间集中存放数据字典信息，而是每个表都有各自的.frm文件，同一个数据字典表，有些列需要打开相应文件才能获取信息；
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
--table_name列不需要额外打开文件，而engine则需要打开表的.frm文件才能获取
具体规则可访问http://dev.mysql.com/doc/refman/5.5/en/information-schema-optimization.html
mysql> EXPLAIN SELECT B.TABLE_NAME
-> FROM INFORMATION_SCHEMA.TABLES AS A, INFORMATION_SCHEMA.COLUMNS AS B
-> WHERE A.TABLE_SCHEMA = 'test'
-> AND B.TABLE_NAME = A.TABLE_NAME\G
*************************** 1. row ***************************
Extra: Using where; Skip_open_table; Scanned 0 databases
*************************** 2. row ***************************
Extra: Using where; Open_frm_only; Scanned all databases;
mysql> EXPLAIN SELECT B.TABLE_NAME
-> FROM INFORMATION_SCHEMA.TABLES AS A, INFORMATION_SCHEMA.COLUMNS AS B
-> WHERE A.TABLE_SCHEMA = 'test'
-> AND B.TABLE_NAME = 'test'\G
*************************** 1. row ***************************
Extra: Using where; Skip_open_table; Scanned 0 databases
*************************** 2. row ***************************
Extra: Using where; Open_frm_only; Scanned 0 databases;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15480802/viewspace-757962/ ，如需转载，请注明出处，否则将追究法律责任。
