SQL Automatic Auditing

I. Overview of TOOLS

SQL Automatic audit-self-service platform, can be developed from the launch, the development of SQL will automatically return to the optimization recommendations, no need to re-audit DBA, so as to improve on-line efficiency, to establish database development norms, so that DBAs from the daily tedious work of liberation.

SQL automatic auditing mainly accomplishes two purposes:

1, avoid poor performance of SQL into the production system, resulting in reduced overall performance.

2, check the development design index is reasonable, whether need to add an index.

The idea is actually simple:

1. Get SQL for development submission.

2, to perform the analysis of SQL, touch the predefined rules to determine whether the SQL can be automatically audited, not approved by the need for manual processing.

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 ' will cause the online failure, need to be replaced with a text editor.

5, support multiple SQL parsing, with a semicolon; For example:

INSERT into T1 values (1, ' a ');

INSERT into T1 values (2, ' B ');

6. Double quotes in JSON format are escaped with backslashes, for example: {\ "dis_text\": \ "Nba\"}.

Note: The audit rules are based on the circumstances of our company, non-inception audit rules (only for reference ideas), use please note!

Its internal principle is mainly implemented by regular expression matching rules.

Second, the function realization

Select Audit

• Developers can submit SQL statements directly to the Platform for risk assessment

• The platform analyzes the SQL statements and automatically gives the improvement suggestions which are not in accordance with the development specifications.

• 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 fields following the Where are indexed

3, no limit will be queried for more data

4, Warning! Sub-query performance is low, please switch to join Table Association

5, note: In the value of not more than 1000

6. Tip: Use Join association, note that the associated fields are indexed, such as on a.id=b.id

7. Note: MySQL has poor performance associated with multiple table joins, and it is recommended not to have more than 3 tables associated

8, Warning! Like '% ' percent ' double percent ' can not be used in the index, such as ' mysql% ' is available to the index

9, Hint: By default, MySQL to 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 the order by rand () is very slow when the amount of data is more, because it will cause the MySQL full table scan, it will not use the index

11, Hint: Do you want to add a have to filter under?

12, Warning! The unnecessary order by order is forbidden because the count is already counted in the previous

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 '

14, then will call the U.S. network Sqladvisor for index check

Insert Auditing

Check items:

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

Update Audit rules

1, Warning! There is no where condition, update will be updated all table, prohibit execution!!!

2. The number of rows updated is less than 1000 lines and can be performed by development self-service. Otherwise please contact DBA for execution!!!

3. Prevent where 1=1 from bypassing audit rules

4, check the Update field has no index

Create audit rules

Check items:

1, Warning! Table does not have a primary key

2, Warning! Table primary key should be self-increasing, missing auto_increment

3, Hint: ID self-increment field default value is 1,auto_increment=1

4, Warning! Table has no index

5, Warning! The number of indexes in the table has exceeded 5, the index is a double-edged sword, it can improve query efficiency but also reduce the speed of inserting and updating and occupy disk space

6, Warning! Table fields do not have Chinese comments, comment should have default values, such as comment ' name '

7, Warning! Table has no Chinese comment

8, Warning! Table is missing UTF8 character set, otherwise garbled

9, Warning! Table storage engine should be set to InnoDB

10, Warning! The table should be the timestamp type plus the default system current time

Alter audit rules

Check items:

1, Warning! The CREATE INDEX syntax is not supported, please change to ALTER TABLE add index syntax.

2, Warning! Change table structure to reduce the number of interactions with the database, change to, for example, ALTER TABLE T1 add index Ix_uid (UID), add index Ix_name (name)

3, table records less than 1 million lines, can be developed self-help execution. Otherwise the table is too large please contact DBA for execution!

4, support to delete the index, but does not support the deletion of fields

For a specific demonstration, please visit

http://blog.51cto.com/hcymysql/2053798

III. Installation and Deployment

Environment installation

1. Installation of PHP environment

# yum install httpd php mysql php-mysql php-devel php-pear libssh2 libssh2-devel-y

2. Install php SSH2 Extension

Pecl install-f SSH2

3, modify the/etc/php.ini

Add on last line

Extension=ssh2.so

4. Turn off SELinux

# Vim/etc/selinux/config

Selinux=disabled

5, the United States network Sqladvisor installation

Please go.

Https://github.com/Meituan-Dianping/SQLAdvisor/blob/master/doc/QUICK_START.md

Deployment

Extract the Php-sqlreview.zip into the/var/www/html/directory

1. Import dbinfo.sql (DB configuration information table) and Operation.sql (SQL Ticket record table)

2, modify the db_config.php (DB configuration information IP, port, user name, password, library name)

3, modify the sqladvisor_config.php (access to the Sqladvisor server IP, ssh port, SSH user name, ssh password)

4, modify the sql_submit.php (log single table IP, port, user name, password) and (call the MySQL client's IP, SSH port, SSH user name, ssh password)

Script interpretation

1. index.html (SQL pass-through entry)

2, sql_review.php (over the audit rules)

3, sql_submit.php (after the adoption, SQL online submission)

4, order.php (ticket query-only the successful storage of SQL)

5, order_result1.php (according to the user's page search)

6. order_result2.php (page search by Time range)

Click here for "Download tool script" or login link

Https://pan.baidu.com/s/1eUct4Bo can download tool scripts

Now through the Dbaplus community free for everyone to provide SQL automatic audit-self-launched platform download use. If you have any questions or suggestions to use the process can contact us at any time, welcome to try!

SQL Automatic Auditing