MySQL Information_schema Table Usage Considerations

For MySQL information_schema table, actually just some views, is not a real table, so there is no actual file, not even a database directory. Additions and deletions should not be done in such a way that it is not possible to set up triggers.

This table is used more often when querying or monitoring database meta data.

Usage considerations:

Because it is just some views, each query will search the database directory, search database tables, when the amount of the table is quite large, will produce a certain amount of delay, the key is to increase the system IO. So select Information_schema or show status, reduce the fuzzy query, accurate query object. The optimization method can refer to official documents: http://dev.mysql.com/doc/refman/5.1/en/information-schema-optimization.html

If the storage engine room InnoDB, also note the parameter: Innodb_stats_on_metadata

When you use Show status or select ... information_schema, INNODB updates Its statistics information, and the effect is the same as the Analyze statement. This is a time-consuming operation when the volume of data is large and the number of tables is larger. It is recommended to close, which will result in a stable MySQL execution plan.

Case Processing sharing: http://dinglin.iteye.com/blog/1575840

This article is from the "Technology Achievement Dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1153910

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/