Mysql database Optimization (i)

One to avoid Web Access errors

1 database Connection timeout produces page 5xx error

2 slow query causes page not to load

3 blocking causes data not to be committed

Second, increase the stability of the database

Three optimized user experience

1 Smooth page access speed

2 Good website function experience

Several optimization aspects

Hardware-System configuration-database table structure-sql and Index

1 Cost: High > Bottom

2 effect: Bottom > High

Optimization aspects

An SQL statement optimization

1 SQL and Index optimization

How do I find the problematic SQL?

Use the MySQL slow query log to monitor efficiency issues with SQL

The following statement can open the full query log:

Show variables like ' Slow_query_log ' see if this turns on slow query log

Where is the file location for set global slow_query_log_file= '/home/mysql/sql_log/mysql-slow.log ' Slow query log storage

Set global log_queries_not_using_indexs=on Specifies whether to log SQL that does not use an index to a slow query

Set Global Long_query_time = 1 specifies a time after which the query is logged to the query log after the time, such as 1s

What the slow query log contains

1 host content for SQL execution

2 SQL execution Information

3 time of SQL execution

4 content of SQL execution

MySQL Slow Query log

Tool One

Pt-query-digest

How to parse SQL statements

Use explain to query the execution plan for SQL such as explain select username from user;

Count () and Max () optimizations

....

SQL and Index optimization

How do I select the appropriate column to index?

1. In the WHERE clause, the GROUP BY clause, the ORDER BY clause, the column appearing in the ON clause

2. The smaller the index field, the better

3 A column with a high degree of dispersion is placed before the federated index

index maintenance and optimization---duplicate and redundant indexes

index maintenance and optimization---duplicate and redundant indexes

Redundant index refers to the same prefix column for multiple indexes, or the index of the primary key in the Federated index, in this example key (Name,id)

is a redundant index.

index maintenance and optimization---looking for duplicate and redundant indexes

Use the Pt-duplicate-key-checker tool to check for duplicate and redundant indexes

Maintenance and optimization of indexes---delete without index

Database structure Optimization

Choose the right data type

Data type selection, charging lies in the right word, how to determine whether the selected data type is appropriate?

1. Use the smallest data type that can save your data.

2. Use a simple data type. int is simpler than the varchar type in MySQL processing.

3. Use NOT NULL to define fields whenever possible.

4. Minimize the use of the text type, when it is not necessary to consider the sub-table.

Choose the right data type

Use int to store datetime, convert using From_unixtime (), Unix_timestamp () two functions

Choose the right data type

Use bigint to IP address

The normalization and inverse normalization of tables

Normalization refers to the specification of database design, which is generally referred to as the third design paradigm, which requires that no non-keywords exist in the data table.

The transfer function dependence of the segment on any candidate key field conforms to the third normal form

The normalization and inverse normalization of tables

Tables that do not meet the requirements of the third paradigm have the following problems:

1. Data redundancy: (classification, classification description) for each item will be recorded,

2. Inserting exceptions to Data

3. Update exception for data

4. Deletion of data exception

The normalization and inverse normalization of tables

Anti-normalization refers to the consideration of the efficiency of the query to the third normal form is appropriate to increase the redundancy of the table, has reached the goal of query efficiency, anti-

Normalization is an operation that takes space to change time.

Vertical splitting of a table

The so-called vertical split, that is, the original table has a lot of columns split into multiple tables, which solves the table width problem. Usually

Vertical splitting can be done by the following principle:

1. Store infrequently used fields in a single table.

2. Store large print segments in a single table.

3. Put together the fields that you often use together.

Horizontal split of the table

Horizontal split of table if you want to solve the problem of too large a single table, the structure of each table in a horizontal split table is exactly the same.

Horizontal split of the table

The common horizontal splitting methods are:

1. Hash the customer_id, if you want to split into 5 tables use mod (customer_id,5) to remove 0-4 values

2. Save the data to a different table for different Hashid.

Mysql database Optimization (i)