DBA SQL Review

DBA SQL Review

Considerations for Schema Review

Considerations for SQL Review

Optimization techniques for on-line schema analysis

DBA Review Work Content

table field, index design optimization

field type (to determine a field for a business, fault, etc type)

Annotation Standard Degree

Partitioned table constraints

SQL Authoring Specification

DML Authoring Specification

Child query constraints

function uses

Purpose of Optimization:

Give developers a higher level of advice

Considerations for Schema Review

The goal of Schema review

Feature-based implementation

Guaranteed Resource Savings

Balancing all aspects of business technology, making a good choice

Let the database do its job

Do not operate in db

Reduce complex operations

Number of fields

No more than 20-50 recommendations

Good data assessment

Recommended Pure int not more than 10 million, contain char not more than 8 million

Non-core table other discussion

Can consider inverse paradigm design

Suitable redundancy design, reducing join

The core table is as streamlined as possible

The log table can be divided into a horizontal table

Note Engine Differences Innodb & TOKUDB

TOKUDB Reduce Update operation (the Update data table becomes very large)

Field design

Primary key InnoDB table is the primary key to sort storage IoT as far as possible using a short, self-increasing column to index, copy the structure of row

Format, if the table has a primary key, you can speed up replication.

INT unsigned self-increment can consider big int

Use Uuid_short () instead of UUID to turn bigint storage

Beware of potential risks

Tinyint Big table primary key may cause MySQL crashed

Type transformation results in very low query efficiency

MySQL in the development of the above features

(1) Each query can only use one core (processing layer)

(2) Cache not executed

(3) MySQL by default, as the number of connections increases. Performance drops (stress test based on connection number)

(4) Check type nesting processing no hashjoin

Selecting the primary key from the library in the master-slave copy structure

(1) The primary key is selected

(2) A valid index is selected

(3) Full table scan

Primary key design for high-speed write environments

Character Set issues

Emoji expression expressed with utf8mb4

Convert character to digital storage

Use int to store IP instead of char (15)

Inet_aton () &inet_ntoa ()

Convert dates to numbers

From_unixtime ()

Unix_timestamp ()

What pits are null and NOT NULL?

C1 Vchar (+) default NULL not recommended

C1 Vchar (+) default NOT NULL is not recommended

C1 Vchar (+) default NOT NULL default ' ' recommendation

Schema Review

Tools:

Specifying DB analysis with Pt-mysql-summar

Use Pt-duplicate-key-checker to specify DB to view duplicate index, duplicate primary key official manual

Functional environment records full-scale slow log for analysis

SQL Review Considerations

General principles of SQL Review

Avoid large operations on the online system

Full Use Index

Optimize join

Remove meaningless logic

Focus on viewing where conditions

In addition to the SELECT statement, there is no where condition can be removed directly

Where condition fields are highly distinguished fields, pay attention to building indexes

Like do not show queries that begin with%

For SQL that appears subqueries, to determine the MySQL version on-line, use explain to confirm

Avoid using sslect *,fa to easily adjust the field list and reduce unnecessary I/O

Insert to write to the field

The entire SQL must be confirmed with explain

Get rid of meaningless operations

A lot of SQL is generated. SQL for classes such as Ibatis,hibernate generated

SQL generated by other frameworks

Meaningless logic removal in SQL for complex classes

Unnecessary parentheses can also be removed.

Optimize join

Control up to three levels of join recommendations 2 below

Small Table Driver Large table

Dictionary Common table Other table sort

Controls the number of rows selected by the Where condition after a join, as much as 1000 rows below

Use UNION ALL instead of union

Reduce the appearance of temporary tables

Avoid large operations on the line

Multiple operations in batches

Large transactions split into multiple transaction inter-partition operations

Frequent queries consider the appropriate cache

For the Text,blob field. Proper splitting of the

DBA SQL Review