SQL Auto-audit-self-service online platform

in order to free the DBA from the daily tedious work, through the SQL self-service platform, can let the development from on-line, the development submits the SQL to automatically return the optimization suggestion, does not need the DBA re-audit, thus enhances the on-line efficiency, facilitates establishes the database development specification.

It draws on the idea of which network inception to go and integrates the Sqladvisor (Index optimization recommendations) and incorporates the previously written "DBA's 40 military regulations" into the audit rules, implemented in PHP. Currently in use within our company.

Here is the homepage interface:

Instructions for use:

1, for Select/insert/update/create/alter added rules, delete needs approval.

2, between the statements to have a space, example where id = 100, no space will affect the accuracy of judgment.

3, after the SQL statement to add a semicolon; The MySQL parser requires semicolons to execute SQL.

4, anti-quote ' may cause the online failure, need to be replaced with a text editor.

5, support multiple SQL parsing, with ##### #六个井号分割.

• Select Audit
  

1 , developers can submit SQL statements directly to the platform for risk assessment

2 , the platform analyzes the SQL statement, and automatically gives the improvement opinion that it does not conform to the development norm.

3 , Applicable scenario: application development Phase

Check items:

1, select * Is it necessary to query all the fields? 2, Warning! There is no where condition, note that the field after the Where to add index 3, no limit will query more data 4, warning! Sub-query performance is low, please switch to join Table Association 5, hint: in the value of not more than 1000 6, hint: The Join association, note that the associated fields to be indexed, such as on A.ID=B.ID7, hint: MySQL Association for multi-table join performance is poor, It is recommended not to exceed 3 tables above the Association 8, warning! Like '% ' double percent can not be used in the index, such as ' mysql% ' is available to the index of 9, hint: By default, MySQL for all GROUP by col1,col2 ... To sort the fields. If the query includes group by and you want to avoid the consumption of sort results, you can specify order by NULL to prohibit sorting. 10, Warning! MySQL uses order by rand () in the data volume is very slow, because it will cause a full table of MySQL scan, it will not be used to index 11, hint: Do you want to add a have to filter? 12, Warning! The unnecessary order by ordering is forbidden, because the previous count has counted 13, warning! The function index is not supported in MySQL, example date_format (' Create_time ', '%y-%m-%d ') = ' 2016-01-01 ' is not available for indexing and needs to be rewritten as create_time>= ' 2016-01-01 00:00:00 ' and create_time<= ' 2016-01-01 23:59:59 '

The sqladvisor will then be called for index checking.

Usage Overview:

1. Select your database name
2. Enter the SQL you want to submit in the dialog box
3. Click the Submit Audit button

After submission, the system automatically returns the SQL optimization improvement comments.

• Insert Auditing

Check items:

1, Warning: Insert Table 1 Select Table 2, will cause the lock table.

After the audit, the user name and password will be displayed, prompting the online:

Click the I want to go online button, the MySQL client will be called to check the syntax and the existence of the table check.

SQL Auto-audit-self-service online platform