MySQL Schema设计(四)一个MySQL里的JQuery:common_schema (先存着)

来源:http://blog.itpub.net/26515977/viewspace-1208257/

 

我们总要在一定的框架中活着,框架的构成有来自法律,有来自道德的,还有来自潜规则的。大部分人只求安生的活着,玩命的人毕竟是少数,有人打破框架平度青云,也有人打破框却架坠落深渊。每每跟做开发的同学沟通,就会听到一大滩框架名称,觉得很是高上大的样子。但他山之石可以攻玉,在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.
...............<此处省略输出>.............


那么数据源来自哪里?我们以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: rental
largest_table_size: 2785280
1 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: 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
........................<此处省略输出>.........................

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 DataHacker
2014-2-7
Good Luck!

转载于:https://www.cnblogs.com/stevenzeng/p/5154977.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值