The article was first published on an official account (Yuebanfeiyu), and then synchronized to the personal website: xiaoflyfish.cn/

If you like, I will share more articles later!

Feel there is harvest, hope to help like, forward ha, thank you, thank you

  • Wechat search: month with flying fish, make a friend, into the interview exchange group

  • The public account responds to 666 backstage, you can get free electronic books

preface

A few days ago, I helped the company solve the online slow SQL alarm problem and encountered several cases.

Next, I will analyze the slow queries I have encountered at work in this period of time with case analysis to talk about how to optimize the slow queries in the database.

Most of the slow SQL we encounter is caused by the improper use of indexes, so I will introduce the index related knowledge

The index is introduced

The index concept

(if not specified, we usually refer to B tree index, which is clustered index, secondary index, overwrite index, compound index, prefix index, unique index default use B+ tree index, in addition to B+ tree type index and hash index etc.)

Index advantages and disadvantages

Advantages:

  • Search: improve data retrieval efficiency and reduce IO costs.
  • Sort: Sort data by index, reducing sorting cost and CPU consumption

Disadvantages:

  • In fact, the index is also a table that holds the primary key and index fields and points to the index’s records, so the index column also takes up space.
  • Updating a table (INSERT, UPDATE, DELETE) not only saves data but also updates newly added index columns in the save index file.

The index classification

  • Single-valued index (single-column index) : An index contains only a single column, and a table can have multiple single-column indexes.
  • Unique index: Index columns must be unique, but empty values can be allowed
  • Composite index: An index contains multiple columns

Index structure

  • BTree index
  • A Hash index
  • Full-text Full text retrieval
  • R – Tree indexes

Which cases to index

  • Primary key Automatically creates a primary key index
  • Fields that are frequently queried should be indexed
  • The foreign key relationship is used to index the fields associated with other tables in the query
  • Composite indexes tend to be built under high concurrency
  • A sort field in a query that can be accessed through an index greatly improves sorting speed
  • Query statistics or grouped data

Which situations are not suitable for indexing

  • Fields that are frequently updated
  • Indexes are not created for fields that are not used by the WHERE condition
  • Too few table records
  • A watch that is often added, deleted, or modified
  • Data repeat too many fields, for it makes little sense to build index (if a table is 100000, there was a field the only two values, T and F the distribution probability of each value is only about 50%, then indexed on this field does not generally improve query efficiency, selectivity refers to the different index column of the index value data and index record than that in the table, If there are 2000 records in a table and 1980 records with different values in the index column, the index selectivity is1980/2000 = 0.99, the closer the index is to 1, the more efficient the index is.)

Explain field analysis

Explain is one of the most common ways to troubleshoot slow SQL

mysql> EXPLAIN SELECT 1;
Copy the code

Id: indicates the order of the SELECT clause or operation

  • The ids are the same: the execution sequence is top-down
  • Different ids: A larger ID has a higher priority and is executed earlier
  • Same ID Different: The larger the ID, the earlier the execution, the same top-down execution

Select_type: identifies common query, federated query, and sub-query.

  • SIMPLE: A SIMPLE select query, excluding subqueries and unions
  • PRIMARY: The query contains complex subsections, the outermost layer being marked as PRIMARY
  • SUBQUERY: Contains subqueries in a SELECT or WHERE list
  • DERIVED: DERIVED tables from subqueries contained in the FROM list
  • UNION: If the second select appears after the UNION, it is marked as UNION
  • UNION RESESULT: Select the result from the UNION table

Table: Indicates the data of which table this row belongs to

Type: What type is used in the query

Results range from best to worst: system>const>eq_ref>ref>fulltext> ref_or_NULL >index_merge>unique_subquery>index_subquery>range>index>all

  • In general, make sure the query is at least range and preferably ref
  • System: The table has only one row of records (equal to the system table). This is a special case of const type
  • Const: indicates that it can be found by indexing once
  • Eq_ref: unique index scan. For each index key, only one record matches it. This is common in primary key or unique index scans
  • Ref: a non-unique index scan that returns all rows matching a single value
  • Range: Retrives only rows in a given range, using an index to select rows, typically between, <, >, in, etc., in a WHERE statement
  • Index: Index is faster than all because index is read from an index and all is read from a hard disk
  • “All” : the search result can only be found by traversing the entire table

Possible_key: Displays indexes that could be applied to the table, but aren’t actually needed

Key: The index actually used, or null if none is present

Key_len: Indicates the number of bytes (possibly used, not actual) used in the index. The length of the index that can be queried by this column. The shorter the length is, the better, without losing accuracy

Ref: Shows which column of the index is used, if possible a constant, and which constants are used to find values on the index column

Rows: Roughly estimate the number of rows to read for the desired record

Extra: Contains important additional information that is not suitable for display in other columns

  • Using filesort indicates that mysql uses an external index sort for data, rather than reading the data in the order of the indexes in the table
  • Mysql uses temporary tables to hold intermediate results. Mysql uses temporary tables to sort query results, such as order by and group by
  • Using index indicates that the corresponding select operation uses an overwrite index to avoid accessing rows of the table, which is efficient
  • Using WHERE indicates that where is used for filtering
  • The Using Join buffer uses the join cache
  • Impossible WHERE If the value of the WHERE clause is always false, it cannot be used to get any tuples
  • Select Table Optimized Away Optimized min/ Max operation based on index or optimized for MyISAM storage engine without group by clausecount(*)Operation, do not wait until the execution phase to perform the calculation

For more details, see my previous post:

The most complete Explain summary, SQL optimization is no longer difficult

The most complete Explain summary, SQL optimization is no longer difficult

Index of the failure

  • Full value matches should be possible
  • Composite optimal left prefix rule (first index cannot drop, middle cannot break)
  • Not doing anything on an index column (calculation, function, cast) will cause the index to fail and move to a full table scan
  • The storage engine cannot use the column to the right of the range condition in the index
  • Try to use overridden indexes (queries that only access the index (the index column is the same as the query column)), reduceselect*
  • Mysql in use does not equal (! = or <>) will cause a full table scan
  • Is null, is not NULL will also fail to use the index
  • Like begins with a wildcard
  • The string is not quoted
  • Use less or

The order by optimization

  • Avoid filesort and try to sort by index, following the best left prefix rule

Filesort has two sorts:

  • Dual-path sort: Scans disks twice

  • Single-path sort: read data stored in memory at one time, and pull data again

  • Single-path sorting is generally better than two-path sorting

  • Optimize strategy: 1, increase sort_BUFFer_SIZE parameter Settings, 2, increase max_LENGTH_FOR_SORT_DATA parameter Settings, as much as possible to get memory at one time

Case analysis

Case a

There are too many parameters in in

select * from goods_info where goods_status = ? and id in(11.22.33....).Copy the code

In contains a large amount of ID data, resulting in a large amount of query data. This is a common type of slow query, and this statement is not slow query when the amount of service data is small.

Because parameters are passed into a List set, when there are too many parameters, the List set can be split into multiple sets of smaller length at the business layer and queried for multiple times. The specific length of each split may need to be evaluated according to the business and data volume

My solution: Business code to add split set operation, LIMIT_SIZE set to 1000

List<List<Integer>> partitionGoodsIdList = Lists.partition(goodsIdList, LIMIT_SIZE);
Copy the code

When SQL queries have too many parameters, I think we can consider using the above split method

Case 2

Too many query results are returned

select from goods where goods_status = ? and poi_id = ?
Copy the code

Solution: Change the SQL to paging query and change the business code to paging query, the modified SQL statement is as follows:

select from goods where goods_status = 1 and poi_id = 11 and goods_id > 22 order by goods_id limit 2000
Copy the code

Paging can reduce the amount of data to avoid slow queries, but can result in multiple query requests instead of one query request. You need to carefully evaluate the size of the limit

Case 3

Order by Slow query

SELECT * FROM order FORCE INDEX (orderId)  WHERE userId = 11 AND status IN (0.22) ORDER BY id ASC ;
Copy the code

This SQL is forced to specify the use of orderId index, but orderId is not in the condition, resulting in full table scan (type: ALL);

The following is the execution plan of the problem SQL:

The immediate cause is the argument that is eventually passed to the SQL query function. OrderId is not included in the WHERE clause, but forceIndex remains in effect

Four cases

Join the slow query

select * from useract join userinfo order by useracct.id desc limit 11;
Copy the code

SQL scan table SQL scan table SQL scan table SQL scan table SQL scan table SQL

Select * from table where type = ALL; select * from table where type = ALL

Optimization:

Case 5

Different index attempts

select id from goods_info where id > ? and activity_id = ? and goods_switch in(?+) limit ?
select id from goods_info where id > 123991510 and activity_id = 0 and goods_switch in (2.3) limit 1000
Copy the code

As you can see from the execution plan, this statement takes the activity_id and primary key index, but this hit ratio is low and a lot of data is screened out by Goods_switch

Solution: In the case of uncertainty about the optimal index, you can add different indexes in the test environment to observe the execution plan and the execution time of the statement.

An attempt to force a primary key index is not effective. An attempt to add a federated index for activity_id_id does not work well; Try adding activity_id,goods_switch synth index, problem solved!

Therefore, when you are not sure which index is optimal, you can try to create different indexes and observe the execution of statements under different indexes for tradeoffs.

Case 6

MySQL select an incorrect index

select * from goods_info
where goods_source = ? and goods_switch ! = ? and id > ? order by id limit ?

select * from goods_info  
where goods_source = 2 and goods_switch ! = 8 and id > 12395070 order by id limit 1000
Copy the code

Idx_goods_source = idx_goods_source; idx_goods_source = idx_goods_source;

Solution: Modify the SQL statement to forcibly remove the primary key index and view the execution plan. If the primary key index is removed, the query time is greatly reduced.

The MySQL optimizer selects the optimal index based on the number of rows scanned, whether they are sorted, and index differentiation. The number of rows scanned may not be accurate, but it is only an estimate of MySQL

conclusion

Slow query optimization is a long-term process, long-term patience!

The last

It takes a lot of time to write an article. If you like it, I hope you can like it and forward it. Thank you

Month with flying fish, make a friend

Public account back to 666, get free electronic books

Reference Books:

  • High performance MySQL