Optimize the summary

  • Do the index
  • When using EXPLAIN analysis, type must be at least range level and all level is not allowed
  • Left-most prefix rule
  • Do not use select *
  • Try to use union all instead of union
  • Use reasonable paging methods to improve the efficiency of paging, such as adding index fields as query criteria
  • Fuzzy query with % prefix is not recommended
  • Avoid null values for fields in the WHERE clause (which invalidates indexes)
  • When only one piece of data is needed, limit 1 is used and the type column in EXPLAIN is const
  • If the sort field does not use an index, try to sort as little as possible
  • Use or sparingly if other fields in the constraint do not have indexes
  • Segmented query, using between to narrow query access
  • If necessary, you can use force index to force a query to move an index
  • Avoid implicit type conversions
  • Join optimization that uses small tables to drive large tables
  • Depots table
  • Master from, read and write separate

EXPLAIN

The column name describe
type The access method for a single table must reach the range level at least, and the all level is not allowed
key The actual index used, if no index is selected, is NULL
ref When indexed column equivalence query is used, information about the object with which the index column is matched
rows Estimated number of records to read
Extra Additional information

Left-most prefix rule

For example, if the designed union index is name_birthday_phone, we need to follow where in SQL in this order

SELECT * FROM person_info WHERE name ='Owater' AND birthday = '2020-04-07'; SELECT * FROM person_info WHERE birthday ='1990-09-27';
Copy the code

So what does the left-most prefix rule do?

  • Make full use of index and save index space
  • Name_birthday_phone is established, which means that there is no need to create separate indexes for name and name_birthday

So why don’t we go in that order and the index is invalidated?

  • The B+ index is sorted by name, then birthday, then phone

Matching column prefix

It’s not usually recommended to use LIKE, so how do you use LIKE correctly?

First, we need to clarify how a string index is stored in B+ tree.

Aaple
Apple
Azple
..
Baple
Bbple
Copy the code
  • From the above sorting, we know that a string index is sorted by letter size
  • So the program search naturally needs to follow this order
SELECT * FROM user WHERE name LIKE'As%'; SELECT * FROM user WHERE name LIKE SELECT * FROM user WHERE name LIKE'%As%';
Copy the code

Why can’t you use select *

SELECT * FROM user WHERE name > 'Owater' AND name < 'Water';
Copy the code

As in SQL, even if the index is used, the efficiency is still low because the process occurs back to the table

  1. Example Obtain the user records whose name is between Owater and Water from the B+ tree corresponding to the name_birthday_phone index. Sequential I/O is used to access secondary indexes
  2. If the query list is ==*==, all data must be found in the clustered index, which is called the back table. In this case, random I/O is used to access the clustered index, and sequential I/O performance is much higher than random I/O.

Try to use union all instead of union

  • The union combines the set of results and then performs a unique filtering operation, which involves sorting and adds a lot of CPU processing.
  • Union all is the combination of two result sets, and there will be duplication.

Use proper pagination to increase pagination efficiency

Assuming a table with 100W rows, using limit alone becomes less efficient as the offset becomes larger, because retrieving all rows from an offset to the end of the recordset is required

Select ID, name, phone from userlimitSelect id, name, phone from user select id, name, phone from userwhere id > 100000 limit 100000, 20
Copy the code

Use range queries with caution

For example, between,>,<, and so on, if the range of multiple columns is searched, only the leftmost column of the index can be used for the B+ tree index.

SELECT * FROM user WHERE name > 'Owater' AND name < 'Water' AND birthday > '2020-04-09';
Copy the code
  • The name query uses an index, but the birthday column doesn’t because it only works if the name value is the same, and the records that range by name in this query may not be sorted by the birthday column.

Segmented query

When the number of scanned rows is more than millions of levels, you can loop through the program, segment to the database to find the data, and then merge the result set, which can reduce the performance consumption of a single execution of the database.

Distinguish between in and exists

  • In executes the subquery first and is suitable for cases where the outer table is large and the inner table is small.
  • An exists is an outer table that drives a table and is accessed first. It is suitable for a small outer table but a large inner table.