Automate MySQL SQL Review: Self‑Service Platform for Faster Deployments

Tool Overview

The SQL auto‑review self‑service platform allows developers to submit MySQL statements and receive automatic optimization suggestions. It eliminates the need for DBA re‑review, improves release efficiency, and enforces database development standards.

The design draws from Qunar Inception, Percona online SQL review, and Meituan SQLAdvisor (index‑optimization) and incorporates a set of "DBA's 40 Rules" implemented in PHP.

Primary Goals

Avoid poorly performing SQL from entering production, which would degrade overall system performance.

Verify that developers design reasonable indexes and add missing ones when necessary.

Implementation Idea

Capture the SQL submitted by developers.

Analyze the SQL against a predefined rule set; if it passes, the review is automatic, otherwise manual handling is required.

Usage Guidelines

Rules cover SELECT, INSERT, UPDATE, CREATE, and ALTER statements; DELETE statements require manual approval.

SQL statements must contain spaces (e.g., WHERE id = 100) to ensure accurate parsing.

Each statement must end with a semicolon ( ;) as required by the MySQL parser.

Backticks ( `) can cause deployment failures and should be removed.

Multiple statements can be processed by separating them with semicolons.

JSON strings must escape double quotes, e.g., {\"dis_text\":\"nba\"}.

Underlying Mechanism

The platform implements audit rules using regular‑expression matching.

Feature Implementation

SELECT Review

Detect unnecessary SELECT *.

Warn when a WHERE clause is missing and suggest adding an indexed filter.

Require a LIMIT clause to prevent large data scans.

Flag sub‑queries for low performance and suggest converting them to JOIN statements.

Limit the size of IN lists to 1000 values.

Ensure that fields used in JOIN conditions (e.g., ON a.id=b.id) have indexes.

Recommend not joining more than three tables in a single query.

Detect patterns like LIKE '%%' that cannot use indexes; suggest a prefix such as LIKE 'mysql%'.

For GROUP BY, optionally add ORDER BY NULL to suppress sorting.

Avoid ORDER BY RAND() on large datasets because it forces a full table scan.

Suggest adding a HAVING filter when appropriate.

Remove unnecessary ORDER BY if the result is already aggregated.

Rewrite functional expressions (e.g., DATE_FORMAT(...)) into range conditions because MySQL does not support functional indexes. Example:

create_time>='2016-01-01 00:00:00' AND create_time<='2016-01-01 23:59:59'

.

After rule checks, invoke Meituan SQLAdvisor for index analysis.

INSERT Review

Warn that INSERT INTO table1 SELECT ... FROM table2 can cause table locks.

UPDATE Review

Prohibit updates without a WHERE clause (full‑table update).

If the affected rows are fewer than 1000, developers may execute the update themselves; otherwise DBA intervention is required.

Prevent bypassing rules with WHERE 1=1.

Check that columns being updated have appropriate indexes.

CREATE Review

Require a primary key; it should be AUTO_INCREMENT with a default start value of 1.

Encourage a reasonable number of indexes (≤5).

All columns and the table itself should have Chinese comments (e.g., COMMENT '姓名').

Use UTF‑8 charset to avoid garbled text.

Set the storage engine to InnoDB.

Timestamp columns should default to the current system time.

ALTER Review

Disallow CREATE INDEX; use ALTER TABLE ... ADD INDEX instead.

Combine multiple index additions in a single statement, e.g.,

ALTER TABLE t1 ADD INDEX IX_uid(uid), ADD INDEX IX_name(name);

.

Tables with fewer than 1 million rows may be altered by developers; larger tables require DBA assistance.

Support index deletion; column deletion is not supported.

Installation & Deployment

Environment Setup

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

pecl install -f ssh2

Append extension=ssh2.so to /etc/php.ini and disable SELinux:

# vim /etc/selinux/config
SELINUX=disabled

Install Meituan SQLAdvisor following its QUICK_START guide (URL: https://github.com/Meituan-Dianping/SQLAdvisor/blob/master/doc/QUICK_START.md).

Deployment Steps

Unzip php-sqlreview.zip into /var/www/html/.

Import dbinfo.sql (DB configuration table) and operation.sql (SQL work‑order table).

Edit db_config.php to set DB IP, port, username, password, and database name.

Edit sqladvisor_config.php to configure the SQLAdvisor server’s IP, SSH port, username, and password.

Modify sql_submit.php to set the work‑order table connection details and the MySQL client’s SSH credentials.

Script Overview

index.html

– entry page for submitting SQL. sql_review.php – applies the audit rules. sql_submit.php – submits approved SQL for deployment. order.php – queries work‑order records (only successful submissions). order_result1.php – paginated search by username. order_result2.php – paginated search by time range.