MySQL performance optimization

Performance optimization

Principles of MYQL Database optimization:

1. Reduce the bottleneck of the system.

2. Reduce the use of resources.

3. Increase the reaction speed of the system.

Optimizing queries

1. Impact of indexes on queries

For example, there is a table student table, there are ID, name, sex, no,4 fields, there are 20 records, if there is no index, query no=20 students.

The SQL statement is a SELECT * from student WHERE no = ' 20 '. Parse Current sql: Execute explain SELECT * FROM student WHERE no = ' 20 '

This allows you to see that rows is 20, which means that the query scanned 20 records.

• Select_type:simple, this is not very critical, just means that this is a simple query, no join, no union, no intermediate table
• Type:all, which indicates that the SQL performed a full table query

After the No field uses the index

See that the result is 1, which proves that only one record was scanned when the query was made. Query speed is naturally faster than not using indexes. And both Possible_keys and key can indicate that the index is used.

2. Using index queries

(1) When using a like query

If the first character is%, the index does not work, as long as% is not in the first position index.

(2) Querying with multi-column indexes

MySQL can create indexes for multiple fields, an index can contain 16 fields, and the index is useful only if the first field of these fields is used in the query criteria.

(3) query with or keyword

The query uses the index only if the OR keyword is the query condition, and the two conditions are indexed.

3. Avoid using SELECT *

The more content you query, the slower the nature is.

4. Always set an ID for each table

The table sets an ID as the primary key, preferably an int type, plus a self-increment identity. Using varchar as the primary key can degrade performance.

Database structure Optimization

1. Decomposing a table with many fields into multiple tables

For tables with many fields, if some fields are used at a low frequency, you can separate the fields to form a new table. Because a table has a large amount of data, it can be slowed by the presence of a field with low frequency, and by decomposing the fields, the query efficiency of the table is improved.

2. Increase the intermediate table

For tables that require frequent federated queries, an intermediate table can be established to improve the efficiency of the query. Increase the query efficiency of a table by inserting data that is frequently federated into the intermediate table and then changing the original federated query to the query for the intermediate table.

3. Optimize the speed of inserting records

Affects the insertion speed is mainly index, uniqueness check, the number of inserts at one time. Take the InnoDB table for example.

(1) Prohibition of uniqueness checks

When you insert data, MySQL will record the insertion. A uniqueness check reduces the speed at which records are inserted. When the record is inserted, turn it on again.

0. Open

1. Close

(2) Prohibit foreign key check

Check the foreign key before inserting the data, and then restore the foreign key after inserting the data.

0;

1;

(3) No automatic submission

Prevents automatic commit of transactions before data is inserted and resumes after data import.

0;

1;

Database optimization

1. Optimize the structure of the table and store characters commonly used and non-common fields separately.

2. Optimize SQL for proper use of indexes.

3. Do the database read and write separation, reduce IO pressure. Because the database is persisted to the record and stored on disk, the IO on the disk is a very performance-intensive operation, so reading and writing in a library can greatly increase the pressure on Io.

4. Try using the cache and don't let the data go to the database.

5. Do a vertical split of the business.

6. Do a horizontal split of the table.

Reasonable use, therefore, is essential for performance improvement.

If the index is established on a field with few data duplication, it is not likely to increase efficiency for fields with large data volumes.

Using a function on an indexed column causes the index to become invalid. Tip: Using a function on a constant item without using a function on an indexed column, the index is valid.

MySQL performance optimization