Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Slow query and Explain optimization

The slow query

Slow query logs consist of SQL statements that take longer than a few seconds to execute.

Slow query logs can be used to find queries that take a long time to execute and are therefore candidates for optimization.

• Slow Query configuration

-- // View the slow Query configuration
show variables like "%slow%"; 
 -- // View the time definition of slow query
show variables like "long%";  

 -- // Set the time definition for slow query
set long_query_time=2;
-- // Enable slow logging
set global slow_query_log='ON';
Copy the code

• Query Indicates the status of the slow query log and the location of the slow query log

mysql> show variables like "%slow%";
+---------------------------+-----------------------------------------------+
| Variable_name             | Value                                         |
+---------------------------+-----------------------------------------------+
| log_slow_admin_statements | OFF                                           |
| log_slow_slave_statements | OFF                                           |
| slow_launch_time          | 2                                             |
| slow_query_log            | OFF                                           |
| slow_query_log_file       | F:\mysql5.725.\data\DESKTOP7 -TLR5VO-slow.log |
+---------------------------+-----------------------------------------------+
5 rows in set.1 warning (0.00 sec)
Copy the code

• View slow query logs

Explain

Depending on the details of the conditions in the tables, columns, indexes, and WHERE clauses, the MySQL optimizer considers a number of techniques to efficiently perform the lookups involved in SQL queries. The set of operations that the optimizer chooses to execute the most effective query is called the Query execution plan, also known as the EXPLAIN plan.

EXPLAIN returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order in which MySQL reads them as it processes statements.

The EXPLAIN output columnsOfficial Documentation

mysql> explain select * from orders where order_num = 20005\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set.1 warning (0.00 sec)
Copy the code

MySQL index usage precautions

MySQL indexes are usually used to speed up the search for rows matching WHERE conditions. There are some details and considerations when using indexes.

1. Do not use functions and operations on columns

Do not use functions on columns as this will cause index invalidation and a full table scan.

select * from news where year(publish_time) = 2017
Copy the code

To use indexes and prevent full table scans, modifications can be made.

select * from news where publish_time = '2017-01-01'
Copy the code

It is also recommended not to perform operations on columns, as this will also cause index invalidation and a full table scan.

select * from news where id / 100 = 1
Copy the code

To use indexes and prevent full table scans, modifications can be made.

select * from news where id = 1 * 100
Copy the code

2. Avoid it! Negative operators such as = or not in or <>

Use in where clauses should be avoided! = or not in or <> operators, because all of these operators invalidate the index and perform a full table scan.

3. Try to avoid using OR to join conditions.

The use of OR to join conditions in the WHERE clause should be avoided as this can lead to index invalidation and a full table scan.

select * from news where id = 1 or id = 2
Copy the code

4. Multiple single-column indexes are not optimal

MySQL can only use one index and selects the most restrictive index from multiple indexes. Therefore, creating a single index for multiple columns does not improve MySQL query performance.

Assume that there are two single-column indexes, news_YEAR_idx (news_year) and news_month_idx(news_month). Now, in a scenario where you need to query for information by year and month, the SQL statement could be written as:

select * from news where news_year = 2017 and news_month = 1
Copy the code

In fact, MySQL can only use a single column index. To improve performance, you can use the compound index news_YEAR_month_idx (news_year, news_month) to ensure that both columns news_year and news_month are covered by indexes.

5. The left-most prefix rule for composite indexes

Compound indexes comply with the left-most prefix principle, that is, the index is used only when the first field of the compound index is used in the query condition. Therefore, the order of index columns in a composite index is critical. An index cannot be used unless the search starts in the leftmost column of the index.

Suppose you have a scenario where you only need to query for the month of the information, then the SQL statement could be written as:

select * from news where news_month = 1
Copy the code

In this case, the news_YEAR_MONTH_idx (news_year, news_month) index cannot be used, because the left-most prefix rule is followed. If the first field of the compound index is not used in the query condition, the index will not be used.

6. Benefits of overwriting indexes

If an index contains the values of all required query fields, it can greatly improve performance by directly returning data based on the query results of the index without reading the table. Therefore, you can define an additional column that the index contains, even if the column is useless to the index.

7. Impact of range query on multi-column index (composite index)

If a column in the query has a range query, all columns to its right cannot be found using index optimization.

As an example, suppose you have a scenario where you need to query information articles published this week, with the condition that they are enabled and published within this week. So, the SQL statement can be written as:

select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1
Copy the code

In this case, all columns to the right of publish_time in the news_publish_idx(publish_time, enable) index cannot be found using index optimization because of the effect of the range query on the multi-column query. In other words, the news_publish_idx(publish_time, enable) index is equivalent to news_publish_idx(publish_time).

In this case, my advice is to pay attention to the side effects of range queries, and minimize the use of range queries, so that the business scenario can be satisfied by curving the country.

For example, in the example above, we need to query the news articles published this week, so we can create a news_weekth field to store the news articles’ weekly information, so that the range query becomes a normal query, and the SQL can be rewritten as:

select * from news where  news_weekth = 1 and enable = 1
Copy the code

However, not all range queries can be modified, and in cases where range queries must be used but cannot be modified, my recommendations are: Instead of trying to solve all problems with SQL, you can use other data storage techniques to control the timeline, such as Redis’ SortedSet ordered collection retention time, or cache query results to improve performance.

8. Index does not contain columns with NULL values?

Any column that contains a NULL value will not be included in the index, and any column in the composite index that contains a NULL value is invalid for the composite index.

Therefore, in database design, try not to default to NULL fields unless there is a very specific reason to use NULL values.

9. Impact of implicit conversions

When the types on the left and right sides of the query condition do not match, implicit conversion will occur. The influence of implicit conversion is that it may cause index failure and carry out full table scan. In the following case, date_str is a string, but matches an integer type, thus implicitly converting.

select * from news where date_str = 201701    
Copy the code

Therefore, keep in mind the dangers of implicit conversions and always be aware of comparisons by type.

10. Index invalidation of like statement

If the value of a table is like %value%, the index can be used. If the value of a table is like %value%, the index can be used. If the value of a table is like %value%, the index can be used. So, depending on your business needs, it’s a good idea to consider ElasticSearch or Solr.

-- Why does the following SQL not match the phone index?

select uid from user where phone=13811223344;

Copy the code

conclusion

• SQL statement optimization
  • Avoid nested queries (subqueries)
  • Avoid multi-table queries
• The index optimization
  • Index appropriately
  • Fair use index
    • The use of indexes described above
• 4) Database structure optimization
  • Columns, tables, libraries
• System Configuration Optimization
  • The mysql configuration is tuned
• Server hardware optimization
  • Read/write separation (master/slave replication)

Finally, welcome to pay attention to my personal wechat public account “Little Ape Ruochen”, get more IT technology, dry goods knowledge, hot news