MySQLSchema设计（四）一个MySQL里的JQuery：common_schema_MySQL

jQuery

bitsCN.com

我们总要在一定的框架中活着，框架的构成有来自法律，有来自道德的，还有来自潜规则的。大部分人只求安生的活着，玩命的人毕竟是少数，有人打破框架平度青云，也有人打破框却架坠落深渊。每每跟开发人员讨论业务，就会听到一大滩框架名称，觉得很是高上大的样子。但他山之石可以攻玉，在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=&#39;innodb_index_stats&#39;/G;*************************** 1. row ***************************help_message:NAMEinnodb_index_stats: Estimated InnoDB depth & split factor of key&#39;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................<此处省略输出>.............

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来完成，但其并不能满足他的需求，结果就是一门新的编程语言要诞生了。

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)

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 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)

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. | ................................

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)

bitsCN.com