MySQL schema Design (iv) a MySQL Jquery:common_schema (save first)

Source: http://blog.itpub.net/26515977/viewspace-1208257/

We always have to live in a certain frame, the structure of the framework comes from the law, from the moral, and from the unspoken rules. Most people just live, life is a few people, after all, someone broke the framework of Pingdu, but also someone broke the box fell into the abyss. Often with the development of students to communicate, you will hear a big beach frame name, feel very high on the big look. But the stone of the mountain can attack Jade, there is a framework in MySQL, this is the common_schema we want to introduce. High performance MySQL author Baron Schwartz once said: The Common_schema is to MySQL as JQuery are to JavaScript. This section simply introduces the relevant parts of the schema, after all, Common_schema is too strong and too broad.

Software Home: Code.google.com/p/common-schema Software Installation：

[Email protected] ~]$ Mysql-uroot-p < common_schema-2.2.sqlenter password:complete-base Components:installed-inno DB Plugin Components:installed-percona Server components:not installed-tokudb components:partial Install:1/2installa tion complete. Thank 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;+--------------------------------+| 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 is optimistic, in that they assume condensed trees. It ispossible that the depth are larger than estimated, and that split factor islower than estimated. Estimated values is presented as floating point values, although in Realitythese is 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 Introduc Ed changes to Theinnodb_index_stats schema. This view is compatible with the new schema, and isincompatible with older releases................< here omit the output; ....

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

  From    _flattened_keys as Redundant_keys    INNER JOIN _flattened_keys as Dominant_keys    USING (Table_schema, TABLE_NAME)

And then the _flattened_keys as the base table view:

  From INFORMATION_SCHEMA. STATISTICS

Author Shlomi noach that "information_schemaprovides with complete info, it's ofter difficult to aggregate. It was sometimes too normalized, and at the other times too de-normalized ", his birth was similar to Perl, and system administrator Wall had wanted to do it with awk, but it did not meet his needs, The result is a new programming language to be born. I put Common_schema into the archive class for the Schema, as shown in: Then we introduce the 5 categories roughly 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 ') .................< omitted here output >.................mysql> select * from Sql_foreign_keys where table_ Schema=\ ' sakila\ ' \g;*************************** 1. Row *************************** Table_schema:sakila TABLE_NAME:addressCONSTRAINT_NAME:fk_address_city Drop_sta Tement: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 the DELETE RESTRICT on the UP DATEcascade........................< the 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-fr Om 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< c11/>| Table       | select | | Sakila      | film_category | table       | Select |+-------------+---------------+------------- +--------+5 rows in Set (0.32 sec) Query OK, 0 rows affected (0.32 sec)

These are common_schema mightily, the following 2 ways to create a schema, which is a piece of cake for Common_schema. 1 eval () specific usage to see Help:

Mysql> call help (\ ' eval\ '); +--------------------------------------------------------------------------------+ | Help                                                                           |+--------------------------------------------------------------------------------+|                                                                                | | NAME | | | |                                                                                eval (): evaluates the queries generated by a given query.                      |                                                                                | | | The TYPE                                                                           |..............< here omits the output, .......

Method Demo:

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                    | ...  < omit output >.......| 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 foreach Similarly, view the foreach Help:

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. |...............< here omit n output, ........ .....

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} '); Q Uery OK, 0 rows affected, 1 Warning (0.40 sec) mysql> show tables in test; Empty Set (0.00 sec)

by Datahacker2014-2-7good luck!

MySQL schema Design (iv) a MySQL Jquery:common_schema (save first)