MySQL Schema設計（四）一個MySQL裡的JQuery：common_schema

我們總要在一定的架構中活著，架構的構成有來自法律，有來自道德的，還有來自潛規則的。大部分人只求安生的活著，玩命的人畢竟是少數，有人打破架構平度青雲，也有人打破框卻架墜落深淵。每每跟開發人員討論業務，就會聽到一大灘架構名稱，覺得很是高上大的樣子。但他山之石可以攻玉，在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.sqlEnter password:complete- Base components: installed- InnoDB Plugin components: installed- Percona Server components: not installed- TokuDB components: partial install: 1/2Installation 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 MySQLCopyri || 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:NAMEinnodb_index_stats: Estimated InnoDB depth & split factor of key's B+ TreeTYPEViewDESCRIPTIONinnodb_index_stats extends the INNODB_INDEX_STATS patch in Percona Server, andpresents with estimated depth & split factor of InnoDB keys.Estimations are optimistic, in that they assume condensed trees. It ispossible that the depth is larger than estimated, and that split factor islower than estimated.Estimated values are presented as floating point values, although in realitythese 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 theINNODB_INDEX_STATS schema. This view is compatible with the new schema, and isincompatible with older releases................<此處省略輸出>.............

那麼資料來源來自哪裡？我們以redundant_keys為例追蹤其源碼：

  FROM    _flattened_keys AS redundant_keys    INNER JOIN _flattened_keys AS dominant_keys    USING (TABLE_SCHEMA, TABLE_NAME)

再以 _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來完成，但其並不能滿足他的需求，結果就是一門新的程式設計語言要誕生了。

我把common_schema涉及到schema的歸檔一類，如所示：


接著我們對這5個分類展開大致介紹1 Data Size per Schema   ● data_size_per_schema

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: rentallargest_table_size: 27852801 row in set (0.16 sec)

2 Schema Object Analysis: Tables

   DDL scripts
   ● sql_alter_table
   ● sql_foreign_keys

mysql> select table_name,sql_add_keys from sql_alter_table where table_schema='sakila'\G;*************************** 1. row ***************************  table_name: actorsql_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: addresssql_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: addressCONSTRAINT_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........................<此處省略輸出>.........................

3 Schema Object Analysis: Columns
   Column overviews   ● auto_increment_columns
   ● text_columns

mysql> select table_name,column_name,data_type,max_value,auto_increment value,auto_increment_ratio ratio    ->   from auto_increment_columns    ->  where table_schema='sakila';+------------+--------------+-----------+------------+-------+--------+| TABLE_NAME | COLUMN_NAME  | DATA_TYPE | max_value  | value | ratio  |+------------+--------------+-----------+------------+-------+--------+| actor      | actor_id     | smallint  |      65535 |   201 | 0.0031 || address    | address_id   | smallint  |      65535 |   606 | 0.0092 || category   | category_id  | tinyint   |        255 |    17 | 0.0667 || city       | city_id      | smallint  |      65535 |   601 | 0.0092 || country    | country_id   | smallint  |      65535 |   110 | 0.0017 || customer   | customer_id  | smallint  |      65535 |   600 | 0.0092 || film       | film_id      | smallint  |      65535 |  1001 | 0.0153 || inventory  | inventory_id | mediumint |   16777215 |  4582 | 0.0003 || language   | language_id  | tinyint   |        255 |     7 | 0.0275 || payment    | payment_id   | smallint  |      65535 | 16050 | 0.2449 || rental     | rental_id    | int       | 2147483647 | 16050 | 0.0000 || staff      | staff_id     | tinyint   |        255 |     3 | 0.0118 || store      | store_id     | tinyint   |        255 |     3 | 0.0118 |+------------+--------------+-----------+------------+-------+--------+13 rows in set (0.90 sec)

4 Schema Object Analysis: Indexes
   Keys and Indexes
   ● candidate_keys
   ● candidate_keys_recommended
   ● no_pk_innodb_tables
   ● rendundant_keys

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)

5 Schema Object Analysis: Dependencies
   Dependency Routines
   ● get_event_dependencies(schema, name)
   ● get_routine_dependencies(schema, name)
   ● get_view_dependencies(schema, name)
   ● get_sql_dependencies(sql, schema)

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)

以上都是common_schema分內之事，以下再介紹2種建立Schema的方法，這對common_schema而言，也是小菜一碟。1 eval()具體用法，可查看協助：

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)

2 foreach同理，查看foreach協助：

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)

By DataHacker2014-2-7Good Luck！