Summary of MySQL database optimization methods (Must SEE)

This article brings to you the content is about MySQL database optimization method Summary, has certain reference value, has the need friend can refer to, hoped to be helpful to you.

Learning, database optimization is divided into all aspects, here, I have a comparison of the overall summary, sharing to work or study peers.

Database optimization is divided into the following seven major areas:

1, the design of the table to conform to the three normal forms ( appropriate anti-three normal form can also );

2, add the appropriate index, the index on the query speed has a large impact, you must add index ( primary key index, unique index, ordinary index, full-text index );

3, add appropriate stored procedures, triggers, transactions, etc.;

4, Read and write separation (master-slave database);

5, some optimization of SQL statements, (query execution speed is relatively slow SQL statement);

6, sub-table partition ( table: A large table is divided into multiple tables. Partitioning: Assigning a table to a different area of storage );

7, the MySQL server hardware upgrade operation.

Next I'll explain how to optimize.

One or three paradigm

First paradigm:

Atomicity: The field in the table can no longer be segmented, as long as the relational database, the natural automatically meet the first paradigm

relational database (with row and column concepts): MySQL, SQL Server, Oracle, DB2, Infomix, Sybase, PostgreSQL, at design time, table-and-field, library-- Specific record (content): To design a field when storing data.

Non-relational database (refers to NoSQL database): Memcache, Redis, Momgodb and so on.

Second paradigm:

There is no identical record in a table, which can be resolved by a primary key

The third paradigm:

The table cannot store redundant data

Inverse three-paradigm design:

Album Table

Id	Album Name	Album View volume
1	Life Photos	100
2	Work photos	100

Photo table

ID	Photo name	Album ID	Browse Volume
1	My little dog.	1	49
2	My little kitty.	1	51
3	My colleague.	2	100

If you want to count the number of views of a photo album, we can add the album view field to the album table, while browsing the photos to update the volume of the album.

Second, open the slow query

MySQL slow query is off by default, and the default record is more than 10 seconds of SQL statements.

1. Check the slow query record time:

Show variables like ' long_query_time ';

2. Modify the slow query time:

Set long_query_time=2;

3. Use one of the following functions to test:

The benchmark (count,expr)   function can test the time required to perform a count of expr operations

Iii. Building an Index

1, the main key index features:

(1) There is at most one primary key index in a table

(2) A primary key index can point to multiple columns

(3) Column with primary key index, cannot have duplicate value, and cannot have null

(4) The primary key index is highly efficient.

2, the unique index features:

(1) There can be multiple unique indexes in a table

(2) A unique index can point to multiple columns,

(3) If not NULL is not specified on a unique index, the column can be empty and can have more than one null.

(4) The unique index is more efficient.

3. Normal index:

Using normal indexes is primarily to improve query efficiency

4. Full-Text Indexing

MySQL's own full-text index mysql5.5 does not support Chinese, supports English, and requires the table's storage engine to be MyISAM. If you want to support Chinese, there are two options,

(1) Use Aphinx Chinese version coreseek (to replace full-text indexing)

(2) Plugin mysqlcft.

The main problem with adding indexes:

(1) More frequent as the query criteria field should create an index, the uniqueness of the field is not appropriate to create an index alone , even if frequently as a query criteria, updated very frequent fields are not suitable for the creation of indexes

(2) does not appear in the WHERE clause the field should not create a cable, the index is at the cost, although the query speed increased, but it will affect the efficiency of the deletion . And the index file takes up space.

Four, sub-table, partition

Vertical sub-table (Content Main Table + additional table):

Content Main Table: some common information that stores various kinds of data, such as the name of the data, add time, etc.

You can use multiple attached tables, and additional tables store unique information for some data.

The main reason: the data access in the content Main table is more frequent.

Features: Table structure is different

Horizontal Sub-table:

Table data is present in a different table .

Features: Same table structure

Partition:

is to store a table in a different area of the disk, which is still a table.

The basic concept:

(1) Range – This mode allows data to be divided into different ranges. For example, you can divide a table into several partitions by year.

(2) List (pre-defined list) – This mode allows the system to split the data through predefined list values.

(3) Hash (hash) – This mode allows the calculation of the hash key of one or more columns of the table, and finally partitions the data regions of the hash code with different values. For example, you can create a table that partitions the primary key of a table.

(4) Key (key value)-an extension of the above hash mode, where the hash key is generated by the MySQL system.

Limitations of Partitioned Tables:

(1) You can only partition an integer column of a data table, or a data column can be converted to an integer column by a partition function.

(2) The maximum number of partitions cannot exceed 1024.

(3) If a unique index or primary key is included, the partition column must be contained in all unique indexes or primary keys.

(4) Partitioning by date is very good, because many date functions can be used. However, there are not too many partitioning functions that are appropriate for a string.

V. Locking mechanism for concurrent processing

Lock mechanism: At execution time, only one user obtains the lock, the other user is in a blocking state, and needs to wait for unlocking.

The MySQL lock is available in the following ways:

Table-level Lock: The cost is small, lock fast, the probability of lock conflict is the highest, the least concurrency. The MyISAM engine belongs to this type.

Row-level locks: high overhead, slow locking, the lowest probability of a lock collision, and the highest degree of concurrency. InnoDB belongs to this type.

Table Lock Demo:

1. Read operations on the MyISAM table (read-Lock) do not block other processes from reading requests to the same table, but block write requests to the same table. The operation of other processes is performed only when the read lock is released.

2. When a read lock is added to a table, other processes can only query for the table and will be blocked when modified.

3. The current process can perform query operations and cannot perform modification operations. You cannot operate on a table that is not locked.

4. The syntax of the lock table:

Lock table Name Read|write

5. You can also lock multiple tables

6. Write to the MyISAM table (write lock), blocking other processes from any operation on the lock table, cannot read and write,

7. After the table is added to the lock, only the current process can perform any action on the locked table. Operations on other processes are blocked.

A demonstration of a row lock:

The 1.INNODB storage engine is implemented by locking the index entries on the index, which means that InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise innodb uses table locks.

2. When a row lock is turned on, the current process cannot manipulate the record with the same ID as the current process while performing an operation on a record.

PHP has a file lock, in the actual project most of the use of file locks, because the table lock, will block , when adding a write lock on some tables, other processes can not be manipulated. This can block the entire Web site, slowing down the speed of the site.

Related recommendations: