MySQL Schema Design (4) JQuery in MySQL: common_schema

We always have to live in a certain framework. The composition of the Framework comes from law, morality, and potential rules. Most people only want ansheng to live. After all, there are a few desperate people. Some people break the framework and flat Qingyun, while others break the frame but fall into the abyss. Every time I discuss the business with developers, I will hear the name of a big beach framework, which is very tall. However, there is also a framework in MySQL, which is the common_schema we will introduce. Baron Schwartz, author of high-performance MySQL, once said: The common_schema is to MySQL as JQuery is to JavaScript. This section briefly introduces the Schema-related sections. After all, common_schema is too powerful and extensive.

Software Homepage: Code.google.com/p/common-schemaSoftware Installation:

[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!

Software Information:

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)

Built-in Help System:

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; + keys + | topic | + keys + | 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 ................ <output omitted here> .............

So where does the data source come from? We use redundant_keys as an example to trace its source code:

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

Check the table with _ flattened_keys as the base:

  FROM INFORMATION_SCHEMA.STATISTICS

The author Shlomi Noach thinks that "INFORMATION_SCHEMAprovides with complete info, it is ofter difficult to aggregate. it is sometimes too normalized, and at other times too de-normalized ", his birth is somewhat similar to Perl. System Administrator wall once wanted to use awk to complete It, but it cannot satisfy his needs. The result is that a new programming language is coming into being.

I archive the schema involved in common_schema, as shown in:


Next, we will show a general introduction to these five categories: 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 ')................. <output omitted here> ................. 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 ........................ <output is omitted here> .........................

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)

The above is a matter of intra-common_schema. The following describes two methods for creating a Schema. This is also a piece of cake for common_schema. 1 eval () specific usage, you can view the help:

Mysql> call help ('eval'); + response + | help | + response + | NAME | eval (): Evaluates the queries generated by a given query. | TYPE | .............. <output omitted here> ...............

Method Demonstration:

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 | ...... <output omitted here> ....... | 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. Similarly, view the foreach help:

Mysql> call help ('foreach '); + response + | 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 outputs are omitted here> .................

Specific demonstration process:

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!