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

jQuery

gaodaima.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                       <em style="color:transparent">本文来源[email protected]搞@^&代*@码)网9</em>      | 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.............................

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)

喜欢 (0)赏

[搞代码]

分享 (0)