MySQL Study Notes (3) Performance Optimization 1

MySQL Study Notes (iii) Performance Optimization 11. MySQL database Locking Mechanism

MySQL database locks include table-level locks, row-level locks, and page-level locks. There are four main differences between the InnoDB Storage engine and the MyISAM storage engine. The first is the cache mechanism, and the second is transaction support,
The third is locking implementation, and the last is the difference in data storage methods.

1.1 table-Level Lock

MySQL table-level locks are divided into read locks and write locks. Four queues are used to maintain the two-week locks, including the currently locked read/write queues and the currently waiting read/write Queues:

• Current read-lock Queue (lock-> Read)
• Pending read-lock Queue (lock-> read_wait)
• Current write-lock Queue (lock-> write)
• Pending write-lock Queue (lock-> write_wait)

Read lock:

When applying for a read request, a client must meet two requirements:

1. The requested read lock resource is not written locked;

2. the pending write-lock queue does not have a higher priority lock.

If the preceding two conditions are met, the Read Request is placed in the current read-lock queue. If the conditions are not met, the Read Request is placed in the pending read-lock queue to wait for the resource to be released.

Write lock:

When a client sends a write lock request, it first checks whether the current write-lock queue exists and does not exist, and then checks the pending write-lock queue. If yes, enter pending write-lock queue and pause its own thread. If not found, check the current read-lock queue. If the lock exists, wait. In summary, the check sequence is as follows:

Current read-lock queue --> pending write-lock queue

Optimization:

1. Shorten the lock time (split SQL statements, create indexes, and control fields)

2. Separate parallel operations (concurrent insert attribute concurrent_insert)

3. make proper use of the read/write priority

1.2 row-Level Lock

Row-level locking is not implemented by Mysql itself, but implemented by other storage engines such as InnoDB. InnoDB is currently the most widely used storage engine in transactional storage engines, it uses the "next key locking" gap lock, locking the airspace between the previous index key and the last index key of the record. It has the following features:

1. Table-level locking is used when indexes cannot be used.

2. The index key may be locked when it contains unnecessary values.

3. When the index is used to locate the data, the index key is the same but the data rows are different, and the data will be locked.

Optimization:

1. Try to use indexes as much as possible,

2. Properly design indexes

3. Reduce filtering Conditions

4. Control the transaction size

2. Query Optimization

1. Optimize the query that needs optimization;

2. Locate the performance bottleneck of the optimization object;

3. Clear optimization objectives;

4. Start with explain;

5. Use profile more

6. Always use a small result set to drive a large result set;

7. Complete sorting in the index as much as possible;

8. Retrieve only the columns you need;

9. Use only the most effective filter conditions;

10. Avoid complex join and subqueries as much as possible;

Conditions for creating an index:

1. The fields frequently used as the insert condition are indexed;

2. Fields with poor uniqueness are not suitable for index creation. (multiple records are stored on each data page and different data pages are frequently read)

3. Frequent Updates of data are not suitable for index creation;

4. fields that do not appear in the WHERE clause are not suitable for index creation.

Optimization of join statements:

Joinbuffer concept,

1. Minimize the number of nested loops in Join Operations

2. Optimize the inner loop first

3. Ensure that join condition fields are indexed

4. Increase the JOIN Buffer setting.

Optimization of group by statements:

1. Use loose index scanning to implement group

As shown in:

In this way, the number of reads is the same as the number of groups, but the range is as follows:

(1) The Group by field must be at the top of the index;

(2). Only the max and Min Aggregate functions can be used.

(3) When referencing other fields, they must exist as constants.

2. implement Compact indexing,

The difference is that when you scan an index, read all the index values that meet the conditions and then obtain the results based on the condition group.

3. Use a temporary table

1. Try to allow MySQL to use indexes to perform group by operations. Of course, it is best to use loose index scanning.
If the system permits, we can adjust the index or query to achieve the goal;
2. When you cannot use an index to complete group by, we must
There must be enough sort_buffer_size for MySQL to sort, and do not use group with large result sets as much as possible.
By operation, because if the size of the temporary table exceeds the size set by the system, the temporary table data will be copied to the disk and then executed.
Operation. At this time, the performance of the sorting group operation will be reduced by an order of magnitude;

Distinct is actually very similar to the group by operation, except that only one record is retrieved in each group after group.
. Therefore, the implementation of distinct is similar to that of group by, and there is no big difference. You can also use loose
Hash index scan or compact index scan. Of course, MySQL can complete distinct only when you cannot use indexes.
Only temporary tables can be used. However, the difference from group by is that distinct does not need to be sorted. That is
MySQL uses a temporary table when the query operation is performed only by distinct.
To Cache the data, but does not perform the filesort operation on the data in the temporary table.