Why is your SQL query so slow?

Program for background development apes usually need to write a variety of SQL, but many times the SQL can be written to meet functional requirements, performance is unsatisfactory. If the business is complex, the table structure and the index design is unreasonable, the written SQL execution time can reach dozens of or even hundreds of seconds, for the production environment, this is a very scary thing. Therefore, it is necessary to understand some common MySQL optimization techniques. This article will be from the table structure and index design, SQL execution principle, SQL writing Optimization 3 aspects of analysis and explanation, hope to be helpful to everyone.

1, table structure, the field design is reasonable?

This is the most basic and the most easy to ignore a link. Good table structure design is the foundation of SQL optimization, in this storage inexpensive, space enough time, design table process, not necessarily fully satisfy the paradigm theory, we can through the appropriate redundancy design, avoid the query, to achieve space to exchange time for the purpose. When designing a table, we decide to build several tables based on business requirements, and which foreign keys are associated with the tables. And usually take into account the size of the data (the number of single-table records is best not to exceed tens of millions, if more than possible to partition table partitions, including vertical and horizontal table), query update frequency (which fields are often used for queries, which are often used for updating), the type and length of each field to value, on which fields to build which type

For example, if you are a InnoDB storage engine, your primary key is best designed to be self-increasing, which is the most efficient. Because the index of the InnoDB storage engine is based on the B + tree implementation, if the self-increment design, can quickly find the location of the insertion node to insert or delete, less impact on other nodes, avoid frequent splitting tree structure. Some companies like to use UUID as a primary key when designing a table, so the advantage is that when the data is migrated, the primary key will not change, can find the corresponding relationship, but there will be 2 questions: 1, the length of the UUID is 36 bits, takes a long time, especially for InnoDB, to establish a secondary index, The secondary index stores the value of the primary key, which causes the secondary index to occupy a larger space. 2, UUID is unordered, each time inserting or deleting a record, in order to maintain the characteristics of the index, may lead to frequent division of nodes, which greatly affect efficiency.

When designing a field, try to use plastic, such as tinyint instead of char (1), so that it is easy to store and calculate. In order to satisfy the business premise, the shorter the better, if there are large objects, such as text or blob types of fields, and these fields are less frequent query frequency, you can consider splitting the table to separate storage (that is, vertical table), to avoid affecting the main table. Also, when designing a table, it is best to design not NULL, because when NULL is allowed, MySQL also needs to have a byte to identify whether it is null, and the MySQL index cannot store null if you use where Colum is null in a column of nullable indexes. Then MySQL is not going to go all the way. What if there is no value for the field? This can be replaced with an empty string or 0.

2. Principles of SQL Execution

After writing SQL, how is SQL executed? When we run SQL, we go through the client sending the request, the server accepts the request and parses the SQL, generates the SQL execution plan, executes and returns the result to the client. To optimize SQL, you first need to know exactly what the SQL is going to take for a long time. Not to analyze the impact of network factors on SQL, we only need to focus on the SQL generated execution plan, which can help us to find the direction of optimizing SQL to a great extent. So what do you think of the SQL execution plan? Explain your SQL. For example, the following SQL is executed on the MySQL Sakila database:

You can see the contents of Id,select_type,partitions,type,possible_keys and so on. First of all, it is more important to have id,select_type,type (quite important), key (quite important), key_len,extra these columns. The other columns are not covered. What does all this mean?

IDs usually indicate the order of execution, for example, there are 3 rows, the IDs are 1,1,2, then the order of execution is 1,1,2, and the number of IDs corresponds to the number of select.

Select_type represents the type of query, mainly in the following ways:

Simple: Easy Select (Do not use union or subquery, etc.)

PRIMARY: The outermost Select

The second or subsequent SELECT statement in a union:union

DEPENDENT the second or subsequent SELECT statement in the Union:union, depending on the outside query

The result of the UNION result:union.

Subquery: The first select in a subquery

DEPENDENT subquery: The first select in a subquery, depending on the outside query

DERIVED: Export table's SELECT (subquery FROM clause)

Type: Indicates which category of connection is used, whether or not the index is used, and is one of the keys to analyzing performance bottlenecks using the explain command, with performance from Good to Bad: System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_subquery > Range > Index > All. In general, you must ensure that the query reaches at least the range level, preferably ref, or performance issues may occur.

Key: Represents the index used, or null if no index is selected.

Key_len: For index length, this value is not significant for single-column indexes, it is important for federated indexes, the size of Key_len shows which columns are actually used in the federated index, and if it is a federated index, the larger the value indicates the more index columns to walk, the higher the query efficiency, This involves the knowledge of the index prefix, which is available later in this section. There is also a formula for the value of the column: if it is a single-column index, the length of the key_len= index column * character encoding takes up the number of bytes (UTF8 encoded as 3 bytes, GBK is 2 bytes, Latin is 1 bytes) + The number of bytes (1 bytes) + Content Length (for variable-length columns) that are allowed to be null , 1 bytes), for example:

In this table, CITY_ID is the primary key, the City field is a varchar type, the length is 50, the default is null, and the explain select city from Sakila.city is executed as follows:

Can be found here to go to overwrite the index, by the way, the overwrite index is the SQL query content through the SQL index to be able to find out, this is the overwrite index, so here we see, even if we do not add where conditions can also go index. The index column is city_name,key_len to 152, how come? Compare the above formula: 50 length * (UTF8 encoding one character 3 bytes) +1 (identity is null) +1 (identifies the length of the content), is it clear?

This last column extra: Contains the details of the MySQL solution query and is also one of the key references. When this column appears with the Using Filesort (which happens narrow escape, it is necessary to optimize) and using temporary (here is the ten dead 0 born, must be optimized!). ) requires extra attention.

3. Optimize your SQL

When you have completed the above 2 steps, you will have to optimize our SQL if you find that your SQL is slow. The 2 Big idea is to ask yourself first: is the index built? is the index built to fit? When we analyze a SQL slow, we need to consider whether the content of this SQL query is indexed? If not, what kind of index should be built? For example, we want to check a user from the user table (>100w record) according to the name, if not indexed, obviously will be very slow, then how to build index it? You might say it's easy, just build an index on your name. if (just if) the name of this column, 100W users, there are 50W called Zhang San, 20W called John Doe, 30W Harry, you build it right here? Obviously inappropriate, or simply a single-column index is inappropriate, because the selectivity is too poor. And this leads to a problem when the SQL storage engine finds that walking a full table scan is faster than indexing, it discards the index and scans the table directly. Here is one of the most important keywords: selectivity, which can be understood as: The number of distinct/Total records of the column in the table, the ratio between 0-1, the closer to 1, the better the selectivity, the selectivity of the unique index is 1, so the unique index is the best performance index . As in the user table above, the selectivity of the table we can look for: select COUNT (distinct name)/count (*) from customer; So what we're going to do is try to improve the selectivity of the index, you can use a federated index, or a partial index (that is, the N-character Fulai Jian index of the column, but the index cannot be used in group by) and so on, and so on, we understand that some developers in the Gender column index, is not a good choice, because the selectivity is too poor. To build an efficient index, it must be an index of good selectivity.

Dragon Boat Festival Holiday The first day, the morning to see the World Cup, the afternoon idle write this blog, welcome to make brick Exchange, reproduced please be sure to indicate the source, thank you.

Why is your SQL query so slow?