我們總要在一定的框架中活著，框架的構成有來自法律，有來自道德的，還有來自潛規則的。大部分人只求安生的活著，玩命的人畢竟是少數，有人打破框架平度青雲，也有人打破框卻架墜落深淵。每每跟開發人員討論業務，就會聽到一大灘框架名稱，覺得很是高上大的樣子。但他山之石可以攻玉，在MySQL當中也是有框架，這便是我們要介紹的common_schema。高性能MySQL一書作者 Baron Schwartz曾如是說：The common_schema is to MySQL as JQuery is to JavaScript。本節僅僅簡單介紹Schema相關部分，畢竟common_schema實在太強悍太廣博。
軟件主頁：code.google.com/p/common-schema軟件安裝：
[mysql@DataHacker ~]$ mysql -uroot -p < common_schema-2.2.sql Enter password: complete - Base components: installed - InnoDB Plugin components: installed - Percona Server components: not installed - TokuDB components: partial install: 1/2 Installation complete. Thank you for using common_schema!軟件信息：
mysql> select attribute_name,substr(attribute_value,1,50) from metadata; +-------------------------------------+----------------------------------------------------+ | attribute_name | substr(attribute_value,1,50) | +-------------------------------------+----------------------------------------------------+ | author | Shlomi Noach | | author_url | http://code.openark.org/blog/shlomi-noach | | base_components_installed | 1 | | innodb_plugin_components_installed | 1 | | install_mysql_version | 5.6.12-log | | install_sql_mode | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGIN | | install_success | 1 | | install_time | 2014-02-05 21:53:55 | | license | common_schema - DBA's Framework for MySQL Copyri | | license_type | GPL | | percona_server_components_installed | 0 | | project_home | http://code.google.com/p/common-schema/ | | project_name | common_schema | | project_repository | https://common-schema.googlecode.com/svn/trunk/ | | project_repository_type | svn | | revision | 523 | | version | 2.2 | +-------------------------------------+----------------------------------------------------+ 17 rows in set (0.00 sec)內建幫助系統：
mysql> desc help_content; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | topic | varchar(32) | NO | PRI | NULL | | | help_message | text | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select topic from help_content; +--------------------------------+ | topic | +--------------------------------+ | auto_increment_columns | | candidate_keys | | candidate_keys_recommended | mysql> select help_message from help_content where topic='innodb_index_stats'\G; *************************** 1. row *************************** help_message: NAME innodb_index_stats: Estimated InnoDB depth & split factor of key's B+ Tree TYPE View DESCRIPTION innodb_index_stats extends the INNODB_INDEX_STATS patch in Percona Server, and presents with estimated depth & split factor of InnoDB keys. Estimations are optimistic, in that they assume condensed trees. It is possible that the depth is larger than estimated, and that split factor is lower than estimated. Estimated values are presented as floating point values, although in reality these are integer types. This view is experimental and in BETA stage. This view depends upon the INNODB_INDEX_STATS patch in Percona Server. Note that Percona Server 5.5.8-20.0 version introduced changes to the INNODB_INDEX_STATS schema. This view is compatible with the new schema, and is incompatible with older releases. ...............<此處省略輸出>.............
再以 _flattened_keys 為基表查看：
FROM INFORMATION_SCHEMA.STATISTICS作者Shlomi Noach便是認為"INFORMATION_SCHEMAprovides with complete info, it is ofter difficult to aggregate. It is sometimes too normalized, and at other times too de-normalized",他的誕生和Perl有些類似，系統管理員沃爾曾想用awk來完成，但其並不能滿足他的需求，結果就是一門新的編程語言要誕生了。
mysql> select * from data_size_per_schema where table_schema='sakila'\G;
*************************** 1. row ***************************
TABLE_SCHEMA: sakila
count_tables: 16
count_views: 7
distinct_engines: 2
data_size: 4297536
index_size: 2581504
total_size: 6879040
largest_table: rental
largest_table_size: 2785280
1 row in set (0.16 sec)
DDL scripts
mysql> select table_name,sql_add_keys from sql_alter_table where table_schema='sakila'\G; *************************** 1. row *************************** table_name: actor sql_add_keys: ADD KEY `idx_actor_last_name`(`last_name`), ADD KEY `idx_actor_last_name_duplicate`(`last_name`), ADD PRIMARY KEY (`actor_id`) *************************** 2. row *************************** table_name: address sql_add_keys: ADD KEY `idx_fk_city_id`(`city_id`), ADD PRIMARY KEY (`address_id`) .................<此處省略輸出>................. mysql> select * from sql_foreign_keys where table_schema='sakila'\G; *************************** 1. row *************************** TABLE_SCHEMA: sakila TABLE_NAME: address CONSTRAINT_NAME: fk_address_city drop_statement: ALTER TABLE `sakila`.`address` DROP FOREIGN KEY `fk_address_city` create_statement: ALTER TABLE `sakila`.`address` ADD CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `sakila`.`city` (`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE ........................<此處省略輸出>.........................
mysql> select * from candidate_keys_recommended where table_schema='sakila'; +--------------+---------------+------------------------+--------------+------------+-----------------------+---------------------+ | table_schema | table_name | recommended_index_name | has_nullable | is_primary | count_column_in_index | column_names | +--------------+---------------+------------------------+--------------+------------+-----------------------+---------------------+ | sakila | language | PRIMARY | 0 | 1 | 1 | language_id | | sakila | customer | PRIMARY | 0 | 1 | 1 | customer_id | | sakila | film_category | PRIMARY | 0 | 1 | 2 | film_id,category_id | | sakila | category | PRIMARY | 0 | 1 | 1 | category_id | | sakila | rental | PRIMARY | 0 | 1 | 1 | rental_id | | sakila | film_actor | PRIMARY | 0 | 1 | 2 | actor_id,film_id | | sakila | inventory | PRIMARY | 0 | 1 | 1 | inventory_id | | sakila | country | PRIMARY | 0 | 1 | 1 | country_id | | sakila | store | PRIMARY | 0 | 1 | 1 | store_id | | sakila | address | PRIMARY | 0 | 1 | 1 | address_id | | sakila | payment | PRIMARY | 0 | 1 | 1 | payment_id | | sakila | film | PRIMARY | 0 | 1 | 1 | film_id | | sakila | film_text | PRIMARY | 0 | 1 | 1 | film_id | | sakila | city | PRIMARY | 0 | 1 | 1 | city_id | | sakila | staff | PRIMARY | 0 | 1 | 1 | staff_id | | sakila | actor | PRIMARY | 0 | 1 | 1 | actor_id | +--------------+---------------+------------------------+--------------+------------+-----------------------+---------------------+ 16 rows in set (0.39 sec)
mysql> call get_view_dependencies('sakila','actor_info'); +-------------+---------------+-------------+--------+ | schema_name | object_name | object_type | action | +-------------+---------------+-------------+--------+ | sakila | actor | table | select | | sakila | category | table | select | | sakila | film | table | select | | sakila | film_actor | table | select | | sakila | film_category | table | select | +-------------+---------------+-------------+--------+ 5 rows in set (0.32 sec) Query OK, 0 rows affected (0.32 sec)
mysql> call help('eval'); +--------------------------------------------------------------------------------+ | help | +--------------------------------------------------------------------------------+ | | | NAME | | | | eval(): Evaluates the queries generated by a given query. | | | | TYPE | ..............<此處省略輸出>...............
mysql> call eval('select concat(\'create table test.\', table_name,\' as select * from sakila.\', table_name) '> from information_schema.tables '> where table_schema = \'sakila\''); Query OK, 0 rows affected (11.30 sec) mysql> show tables in test; +----------------------------+ | Tables_in_test | +----------------------------+ | actor | | actor_info | | address | ...... <此處省略輸出>....... | staff_list | | store | +----------------------------+ 23 rows in set (0.00 sec) mysql> call eval('select concat(\'drop table test.\', table_name) from information_schema.tables '> where table_schema = \'test\''); Query OK, 0 rows affected (0.92 sec) mysql> show tables in test; Empty set (0.00 sec)
mysql> call help('foreach'); +--------------------------------------------------------------------------------+ | help | +--------------------------------------------------------------------------------+ | | | NAME | | | | foreach(): Invoke a script on each element of given collection. $() is a | | synonym of this routine. | | | | TYPE | | | | Procedure | | | | DESCRIPTION | | | | This procedure accepts collections of varying types, including result sets, | | and invokes a QueryScript code per element. | ...............<此處省略N個輸出>.................
mysql> call $('1:3', 'create table test.${1}(id int,name varchar(20))'); Query OK, 0 rows affected, 1 warning (0.59 sec) mysql> show tables in test; +----------------+ | Tables_in_test | +----------------+ | 1 | | 2 | | 3 | +----------------+ 3 rows in set (0.00 sec) mysql> call $('1:3', 'drop table test.`${1}`'); Query OK, 0 rows affected, 1 warning (0.40 sec) mysql> show tables in test; Empty set (0.00 sec)
