MySQL performance optimization tutorial 1 (1)

Editor's Note: This is a MySQL performance optimization tutorial. the DBA of a company was originally used to train employees of the company and is now reproduced for everyone to learn and improve.

Background and objectives

● Used for employee training and sharing.

● Engineers who have used the mysql environment and have some development experience for the user group

● For Internet environments with high concurrency and massive data volumes.

● The language in this article is spoken, not a standard language.

● Aiming at practice and solving specific problems, non-test-taking and non-conventional education. Reminder: students in this tutorial may not be able to improve their performance.

● Non-technical challenges, non-high-end architect training, should be automatically ignored by experts.

Mysql execution optimization

Understanding Data Indexing

1. Why Data Indexing can improve efficiency

■ Data index storage is ordered

■ In an ordered manner, you do not need to traverse index records to query a data record through an index.

■ In extreme cases, the query efficiency of data indexes is the binary query efficiency, which is close to that of log2 (N)

2. How to understand the data index structure

■ Data indexes usually use the btree index by default, and the memory table also uses the hash index ).

■ A single ordered sorting sequence is the most efficient binary search, or semi-query). The purpose of using a tree index is to quickly update and add or delete operations.

■ In extreme cases, for example, the demand for data query is very large, the demand for data update is very small, the real-time requirement is not high, and the data size is limited), a single sorting sequence is directly used, and the half-query speed is the fastest.

◆ Practical example: reverse IP address Lookup

Resource:

IP address table. The source data format is startip, endip, and area.

The number of source data entries is about 0.1 million, which is highly dispersed.

Objectives:

You need to query the region of the ip address through any ip Address

The performance requirement is more than 1000 queries per second.

Challenges:

For example, use... And database operations cannot effectively use indexes.

If you need to traverse 0.1 million records for each query request, it is not feasible.

Method:

One-time sorting is only performed in data preparation, and data can be stored in the memory sequence)

The half-lookup method is used for each request)

■ During Index Analysis and SQL optimization, you can think of the Data Index field as a single ordered sequence and use it as the basis for analysis.

◆ Practical examples: Composite Index query optimization practices, the same city heterosexual list

Resource: user table, sex field; area; lastlogin last logon time; others

Objectives:

Find the opposite sex in the same region, in reverse order according to the Last Logon Time

How to optimize high-frequency queries in high-traffic communities.

Query SQL: select * from user where area = '$ Region' and sex =' $ sex' order by lastlogin desc limit;

Challenges:

It is not difficult to create a composite index. How does one understand the composite index of area + sex + lastlogin?

First, forget about the B-tree and regard the index field as a sort sequence.

What if I only use area? Search will find all the results that match the area, traverse it, select and sort the results that match the sex. Traverse all area = '$ region' data!

If area + sex is used, it is better to traverse all area = '$ Region' and sex =' $ sex 'data and then sort the data based on this !!

When the Area + sex + lastlogin composite index, remember that lastlogin is at the end). The index is sorted Based on the Merging Results of the area + sex + lastlogin fields. The list can be imagined as follows.

Guangzhou female $ time 1

Guangzhou female $ time 2

Guangzhou female $ time 3

...

Guangzhou male

....

Shenzhen female

....
The database easily hits the boundary of area + sex and traces 30 records up based on the bottom boundary! Quickly hit all results in the index without secondary traversal!

3. How to understand the impact of result sets

■ The impact result set is an important intermediate data for data query optimization.

◆ The relationship between Query conditions and indexes determines the impact on the result set.

As shown in the preceding example, even if an index is used for a query, if the query and sorting targets cannot be directly hit in the index, the results may be affected. This directly affects the query efficiency.

◆ Microsecond-level optimization

● Optimized query cannot only view slow query logs. Generally, queries over 0.01 seconds are not optimized.

● Practical examples

Similar to the previous case, a game community needs to display User dynamics. select * from userfeed where uid = $ uid order by lastlogin desc limit; uid is used as the index field by default in the initial stage, the query results that hit all uid = $ uid are sorted by lastlogin. When user behavior is very frequent, this SQL index hit affects hundreds or even thousands of records in the result set. The query efficiency exceeds 0.01 seconds, and the database pressure is high when the concurrency is large.

Solution: Change the index to uid + lastlogin composite index. The index hits 30 result sets directly, and the query efficiency is improved by 10 times. The average value is 0.001 seconds, causing a sudden drop in database pressure.

■ Common mistakes affecting result sets

◆ The impact result set does not mean the number of results queried by data or the number of results affected by operations, but the number of results hit by the index of the query condition.

◆ Practical examples

● A Game Database uses innodb, which is a row-Level Lock and rarely locks tables theoretically. An SQL statement (delete from tabname where xid =…) appears ...), This SQL statement is very useful. It only appears under certain circumstances and appears frequently less frequently every day for about 10 times). The data table capacity is millions, but this xid has not been indexed, so the miserable thing happened. When the delete statement was executed, there were very few records actually deleted, maybe one or two, maybe none;! Because this xid has not been indexed, the delete operation traverses the full table record, the full table is locked by the delete operation, and the select Operation is all locked. Because the Traversal Time of millions of records is long, during this period, a large number of select statements are blocked, and too many database connections crash.

This kind of non-high-risk request requires a very small number of SQL statements to operate on, and the query of the entire database is blocked due to the absence of indexes, which requires great vigilance.

■ Summary:

◆ The affected result set is the result set hit by the Search Condition Index, rather than the output and operation result set.

◆ The more influential the result set approaches the actual output or operation target result set, the higher the indexing efficiency.

◆ Please note that I will never talk about Optimization of Foreign keys and join here, because in our system, this is not allowed at all! The architecture optimization section explains why.