MySQL Optimization recommendations

SQL optimization

1 MySQL optimization direction

IO total elapsed Time index setting

2 SQL and Index optimization

How to analyze

Table type Possible_keys Key_len ref rows

Explain returns the meaning of each column

Using Filesort

3 count () and Max ()

File IO, Max can be optimized by user index

4 Optimization method of sub-query

Can be optimized as a join method

5 Group BY optimization

temporary table/file sorting is generally generated

adding processing within subqueries

6 Limit Optimization

Using a primary key or an order by operation

Increased primary key ID filtering multiple times

Optimization of indexes

1 in the WHERE clause \group BY clause ORDER BY clause in the column in the clause

2 The smaller the index field, the better

3 The signature of a large, discrete column placed on a federated index

index maintenance and optimization-repetitive and redundant indexes

The primary key is the unique index

Query for duplicate index deletions.

Pt-duplicate-key-checker Tool detects duplicate indexes

To delete an index that is not in use

database table Optimization

Data type

1 can save the smallest time form

2 Simple data types

3 define fields with NOT NULL whenever possible

4 Minimize the use of text type, non-use, it is best to consider the table

Storing date time with int, using From_unixtime () Unix_timestamp ()

Use bigint to IP address, with Inet_aton () Inet_ntoa ()

Paradigm Talk and double time

1 Data redundancy

2 data Insert, write, delete exception.

Inverse paradigm Design.

The main purpose is to reduce the operation of the Table association

Vertical split

More than one column, can be split into multiple tables

Infrequently used, large fields are placed in a table

Split horizontally

Resolving data volume problems in a table

Front desk to find the remainder of the split, background combination

System Configuration Optimization

Number of open files now

Soft Nofile 65535

Hard Nofile 65535

It is best to use a hardware firewall to reduce software loss

MySQL configuration file optimization

/etc/my.cnf

C:/windos/my.ini

$/usr/sbin/mysqld--verbose--help | Grep-a 1 ' Default options '

Innodb_buffer_pool_size

A very important parameter, total memory 75%.

Innodb_buffer_pool_instances

mysql5.5 new parameters, you can control the number of buffer pool, MO Ah, only one buffer pool in the case of the day

Innodb_log_buffer_size

InnoDB Log buffer size

Innodb_flush_log_at_trx_commit

Key parameters, the InnoDB io efficiency is very large, the default 1, can be valued 0,1,2 General recommendation 2, this can be balanced. 1 high requirement for safety line

Innodb_read_io_threads

Innodb_write_io_threads

The above two parameters determine the INNODB thread

Innodb_file_per_table

Key parameters, Control InnoDB No table uses a separate table space, default is off, that is, all tables are built into the shared table space

Innodb_stats_metadata

Determines when MySQL will refresh the statistics of the InnoDB table

MYQL Configuration Wizard Tool

Https://tools.percona.com/wizard

Hardware optimization

CPU selection, single core faster, or multicore

More single cores are recommended faster, some actions do not support multicore

Best not to exceed 32 cores CPU

Disk IO optimization

Common raid0,1,5

0 bands, Io best

1 Image 2 sub-allocation, better security

5 minimum of 3 pieces of information for the storage of efficacy

Whether SNA and NAT are suitable for databases

1 frequently used for highly available solutions

2 sequential Read and write efficiency is very high, but random read and write unsatisfactory

3 database Random Read-write ratio is very high

MySQL Optimization recommendations