Query optimization is not achieved overnight, need to learn to use the corresponding tools, learn from the experience of others to optimize SQL, and improve their own.

preface

First, to consolidate the advantages of index, fast data retrieval, stable query, sequential storage to avoid the server to create temporary tables, random I/O into ordered I/O.

However, once the index is created improperly, it will cause the following problems, occupying extra space, wasting memory, and reducing the performance of adding, deleting, and modifying data.

Therefore, an efficient index can only be created based on an understanding of the index data structure.

** All operations in this article are in MySQL8.0.12**

Create index specifications

Before learning index optimization, you need to have a certain understanding of the specification for creating indexes, which comes from the Alibaba development manual.

Primary key index: pk_column_column

Unique index: uk_column_column

Common index: idx_column_column

Reasons for index failure

When creating an index, you need to know under what circumstances the index will fail. Only when you understand the cause of the index failure can you create the index without some known errors.

1. The leader must not die

The classic statement of this game is to include the left-most rule when creating indexes.

For example, the table structure is u_id, u_name, u_age, u_sex, u_phone, and u_time

Create index idx_user_name_AGe_sex.

The query condition must contain the u_name column.

2. Do not perform any operation on the index column

Do not perform any calculations, functions, or automatic or manual type conversions on index columns, otherwise a full table scan will be performed. In short, do nothing on the index column.

3. The two sides have different types

For example, if the index idx_user_name is created, the name field type is vARCHar

If you use where name = kaka in the query, the index will be invalid.

Correct usage is where name = “kaka”.

4. Improper like query will cause index invalidation

Create index idx_user_name

Select * from user where name like “kaka%”; Indexes can be hit.

Select name from user where name like “%kaka”; Indexes are available (8.0 + only).

Select * from user where name like ‘%kaka ‘; select * from user where name like ‘%kaka ‘; The index will be invalidated

5. Indexes after a range condition are invalidated

Create index idx_user_name_AGe_sex

Select * from user where name = ‘kaka’ and age > 11 and sex = 1;

SQL > select * from ‘sex’; select * from ‘sex’;

The length of key_len is required for compound index failure.

Summary: % is followed by the command index. When an overwrite index is used, any query method can hit the index.

In many articles, the MySQL version is not marked, so you may see the conclusion that is null, or the index is invalid.

Third, SQL optimization killer maceExplain

One thing you must do after writing an SQL statement is test the SQL statement using Explain to see if the index hits.

The following figure shows the use of the Explain output format, which is explained briefly.

1. The id column is the id of the query. This identifier is always 1 if there are no subqueries or federated queries in the query.

This number increases if there are subqueries or federated queries.

2.select_type

The most common types are SIMPLE and PRIMARY, and this column should be known.

3.table

Table name

4.**type

This column is one of the most important columns to watch when tuning SQL statements, and it shows what type the query uses.

The following is sorted from best to worst.

  • System: the table contains only one row of data
  • Const: At most one record is matched. This is often used in conditional queries with a primary key or unique index
  • Eq_ref: occurs when the index used by the connection is primary key and unique
  • Ref: Comparisons will occur using the plain index = or <=> operators
  • Fulltext: Uses full-text indexes
  • Ref_or_null: the ref_or_null type is similar to the ref type, except that null values are added. Where name = ‘kaka’ and name is null, name is a normal index.
  • Index_merge: A query that uses more than two indexes. This is common when using AND, or. The official documentation places this type after ref_OR_NULL, but in many cases performance may be lower than range due to reading too many indexes
  • Unique_subquery: Used for IN queries in WHERE, completely replacing subqueries with higher efficiency. Statement forvalue IN (SELECT primary_key FROM single_table WHERE some_expr)
  • Index_subquery: The return result field combination in a subquery is an index (or combination of indexes), but not a primary key or unique index
  • Range: index range query, common IN queries using the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN(), or like operators.
  • Index: indicates a full table scan. The index is scanned from beginning to end
  • All: indicates full table scan, with the worst performance.

5.possible_keys

This column shows the indexes that might be used

6.**key

Index hit by the optimizer from Possible_keys

7.key_len

The length of the index used by the query (in bytes). Key_len only calculates the length of the index used by the WHERE condition. Sorting and grouping does not count the length of the index used by key_len.

8.ref

If the constant equivalent query is used, const is displayed here.

If it is a join query, the execution plan of the driven table shows the associated fields of the driven table.

If the condition uses an expression or a function, or if the condition column is implicitly converted, it might be shown as func.

9.**rows

This is mysql’s estimate of the number of rows that need to be scanned (not an exact value).

This value is a very intuitive indication of how efficient SQL is, and in principle the fewer rows the better.

10.filtered

This column represents the percentage of the number of records that can be queried after the data returned by the storage engine is filtered at the server layer. Note that it is a percentage, not a specific number of records

11.**extra

In most cases, the following situations occur.

  • Using index: An overwrite index is used, and the query columns are index fields
  • Using WHERE: The WHERE statement is used
  • Using temporary: A temporary table is used to sort the query results
  • Using filesort: Sort data Using an external index
  • Using index condition: Index push-down is used. For index push-down you can see the previous articleMySQL > select * from 'MySQL';

12. To summarize

The above is the explanation about all the columns. In the normal development process, only the four columns of Type, key, rows and extra will be focused.

  • Type optimization target at least reach range level, the requirement is ref level, if consts can be the best.

  • Key is the index used by the query. If this column is empty, the index is not created or invalid.

  • Rows is the number of rows scanned by this SQL statement, and the fewer the better.

  • Extra: This column extends columns and needs to be optimized if temporary tables and file sorting occur.

Fourth, SQL optimization killer maceThe slow query

The above mentioned can directly use explain to analyze their SQL statement is reasonable, next to talk about a point that is slow query.

Check whether slow query is enabled

Check whether SQL statements that do not use indexes are logged

Enable slow query and SQL statements that do not use indexes

set global log_queries_not_using_idnexes=’on’;

set global log_queries_not_using_indexes=’on’;

Check whether the preceding two configurations are enabled

Set globle long_query_time=1;

If the time is not changed, the client can reconnect again.

View Slow Query the storage location

Then execute any statement that does not perform the index and you will see that statement in the log

The main things to look at in the figure above are Query_time, SQL statement content.

This is the SQL statement on how to use slow queries to see problems in projects.

Fifth, optimization method

Here to talk to you about some common SQL statement optimization scheme, the above two tools to make good use of, assist us to play strange.

  • Select * is not allowed. What fields are required to query what fields
  • The WHERE field sets the index
  • Group by, Order BY fields set index
  • Instead of offset and limit paging, use deferred association to achieve paging (not necessary when there is a small amount of data)
  • When count is 0 when writing pages, return to avoid paging statements
  • Use overwrite indexes for queries to avoid back to the table
  • The most discriminating compound index is placed on the far left
  • Use only count(*) as the number of rows in the statistics
  • For IN and exist, if the size of the two queried tables is the same, the performance difference can be ignored; if exist is used, otherwise IN is used
  • Add limit 1 to query a row of data
  • Select a reasonable data type, and the smaller the data type, the better
  • A federated query joins a maximum of three tables and requires that the data types of the fields to be joined be consistent
  • The in operation can be avoided as far as possible. If it is unavoidable, the in element should be controlled within 1000
  • Columns with low differentiation and frequent data updates are not suitable for indexing
  • The type in explain must be at least range, which is ref
  • The union index satisfies the leftmost rule

Adhering to learning, blogging and sharing is the belief that Kakha has been upholding since she started her career. I hope the articles on the Internet can bring you a little help. I’m Kaka. See you next time.