Performance Optimization Analysis of mysql Databases

This article mainly introduces some conditions that affect database performance. You can refer to this article.

Root Cause of impact on database performance:

1. Disk I/O read

2. CPU usage

3. Resource Competition

Optimization Method:

1. Design Optimization

2. Operation Optimization

3. Use other optimization technologies

1) Design Optimization:

Split tables with large data volume by category;
Select the most suitable field attribute;
Index design: creates an index for fields that are frequently used for query. This avoids searching for other duplicate and useless data during query and avoids large-scale scanning;

2) Operation Optimization:

Lock table operation;
Avoid subqueries as much as possible and convert subqueries into connection queries;
The where clause query conditions should be used with as few operations as possible;
The effects of A> 2 and A> = 3 are quite different, because when A> 2, the database first finds the record index of 2 and then compares it, when A> = 3, ORACLE directly finds the record Index = 3;
If a is not null is changed to a> 0 or a> '', the index is generally not used to determine whether the field is null;
A <> 0 is changed to a> 0 or a <0. If the operator is not equal to, the index will never be used. Therefore, the processing of the operator will only generate a full table scan; if YY_BH LIKE '% 5400%', a full table scan is generated, if you change to YY_BH LIKE 'x5400% 'OR YY_BH LIKE 'b5400%', the index of YY_BH will be used to query the two ranges, and the performance will be greatly improved;

The effect of the following three SQL statements is equivalent:

The Code is as follows:	Copy code
SELECT * FROM 'logs' WHERE id = 1 or id = 2 or id = 3 SELECT * FROM 'logs' WHERE id between 1 and 3 SELECT * FROM 'logs' WHERE id in (1, 2, 3)

The tested performance is similar. If the id is a large array, the last line of writing will be much simpler and can be used

If the WHERE clause is followed by the condition order, the condition with a small range should be placed before the condition, and the following conditions should be searched in a small range;

3) Other optimization technologies

Use data caching technology, such as memcached;
Static storage is used to store data that is not frequently updated but frequently called;