1. Query optimization

1.1. MySQL Optimization Principles

Mysql tuning outline

  1. Slow query open and capture
  2. Explain + slow SQL analysis
  3. Run the show profile command to query the execution details and life cycle of SQL on the Mysql server
  4. Tuning parameters of the SQL database server

A permanent small table drives a larger table, similar to Nested Loop Nested Loop

  1. The EXISTS grammar:
    • SELECT ... FROM table WHERE EXISTS(subquery)
    • This syntax can be interpreted as: put the query data into the sub-query to perform conditional verification. According to the verification result (TRUE or FALSE), the data result of the main query can be preserved.
  2. EXISTS(subquery) returns only TRUE or FALSE, therefore in the subquerySELECT *It can also beSELECT 1The official statement is that the actual execution will ignore the SELECT list, so there is no difference
  3. The actual execution of the EXISTS subquery may be optimized rather than compared item by item in our understanding. If there are concerns about efficiency, actual tests can be performed to determine efficiency.
  4. The EXISTS subquery can also be replaced by conditional expression, other subquery or JOIN. The optimal type needs to be analyzed on a case-by-case basis

Conclusion:

  1. Always remember that small tables drive large tables
  2. When the table B dataset is smaller than the table A dataset, use in
  3. When the data set of table A is smaller than that of table B, exist is used

Usage of in and exists

  • Tbl_emp table and TBL_DEPT table
select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
8 rows in set (0.00 sec)

select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | RD       | 11     |
|  2 | HR       | 12     |
|  3 | MK       | 13     |
|  4 | MIS      | 14     |
|  5 | FD       | 15     |
+----+----------+--------+
5 rows in set (0.00 sec)
Copy the code
  • In the writing of
select * from tbl_emp e where e.deptId in (select id from tbl_dept);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
+----+------+--------+
7 rows in set (0.00 sec)
Copy the code
  • The exists of writing
select * from tbl_emp e where exists (select 1 from tbl_dept d where e.deptId = d.id);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
+----+------+--------+
7 rows in set (0.00 sec)
Copy the code

1.2, ORDER BY optimization

ORDER BY clause, try to use Index rather than FileSort

Create a table

  • Build table SQL
create table tblA(
    #id int primary key not null auto_increment,
    age int,
    birth timestamp not null
);

insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());

create index idx_A_ageBirth on tblA(age, birth);
Copy the code
  • Test data in tblA tables
select * from tblA;
+------+---------------------+
| age  | birth               |
+------+---------------------+
|   22 | 2020- 08- 05 10:36:32 |
|   23 | 2020- 08- 05 10:36:32 |
|   24 | 2020- 08- 05 10:36:32 |
+------+---------------------+
3 rows in set (0.00 sec)
Copy the code
  • Indexes in TBL
SHOW INDEX FROM tblA;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+ ------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+ ------------+---------+---------------+
| tblA  |          1 | idx_A_ageBirth |            1 | age      `   | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| tblA  |          1 | idx_A_ageBirth |            2 | birth       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+ ------------+---------+---------------+
2 rows in set (0.00 sec)
Copy the code

CASE1: The case where you can sort using an index

  • Only the lead brother age
EXPLAIN SELECT * FROM tblA where age>20 order by age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

EXPLAIN SELECT * FROM tblA where birth>'2016-01-28 00:00:00' order by age;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Copy the code
  • Age + birth
EXPLAIN SELECT * FROM tblA where age>20 order by age,birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Copy the code
  • Mysql default ascending order, full ascending or full descending order, can withstand
EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth ASC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

EXPLAIN SELECT * FROM tblA ORDER BY age DESC, birth DESC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.01 sec)
Copy the code

CASE2: A case in which an index cannot be used for sorting

  • The head brother age is dead
EXPLAIN SELECT * FROM tblA where age>20 order by birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------- ---------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------- ---------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------- ---------------+
1 row in set (0.01 sec)
Copy the code
  • How dare I birth precede Age
EXPLAIN SELECT * FROM tblA where age>20 order by birth,age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------- ---------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------- ---------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------- ---------------+
1 row in set (0.00 sec)
Copy the code
  • Mysql default ascending order, if full ascending or full descending order, ok, but a rise or fall mysql can not carry
EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth DESC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+---------------------------- -+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+---------------------------- -+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+---------------------------- -+
1 row in set (0.00 sec)
Copy the code

conclusion

  1. MySQL supports two kinds of sorting methods, FileSort and Index. Index is more efficient, which means that MySQL scans the Index itself to complete sorting. FileSort is less efficient.
  2. ORDER BY satisfies both cases (the best left prefix rule) and will be sorted BY Index
    • The ORDER BY statement uses the left-most front row of the index
    • Use the where clause in combination with the OrderBy clause condition column to satisfy the left-most front of the index
  3. Do the sorting on the index column as much as possible, following the best left prefix for the index

If the index column is not sorted, mysql will start filesort’s two algorithms: double-path sort and single-path sort

  1. Dual sorting
    • MySQL4.1 used double-path sorting, which literally means scanning the disk twice for data. Read the row pointer and the columns for the Order Derby operation, sort them, then scan the sorted list and re-read the corresponding data transfer from the list according to the values in the list
    • Fetch the sort fields from disk, sort them in buffer, and fetch the other fields from disk.
  2. Single way sorting
    • Fetching a batch of data requires two scans of the disk, which is notoriously time consuming for I/O, so after mysql4.1, there was an improved algorithm called single-way sort.
    • It reads all the columns needed for the query from disk, sorts them by the columns to be Derby, and then scans the sorted list for output. It is faster, avoids a second read, and turns random I/O into sequential I/O, but uses more space. Because it keeps every line in memory.
  3. Conclusions and problems:
    • Since single-path is an improved algorithm, it is generally better than dual-path
    • In sort_buffer, method B takes up A lot more space than method A. Because method B takes out all fields, it is possible that the total size of the retrieved data exceeds the capacity of sort_buffer. As A result, only sort_buffer size can be retrieved each time for sorting (create TMP file, Sort_buffer = sort_buffer = sort_buffer = sort_buffer This results in multiple I/ OS.
    • Conclusion: An attempt to save an I/O operation resulted in a large number of /O operations that were not worth the cost.
  4. Further optimization strategies:
    • Increases the sort_buffer_size parameter setting
    • Increases the setting of the max_LENGTH_FOR_sorT_DATA parameter

Follow these rules to increase the speed of Order By

  1. It is important to note that select * is a no-no in Order by and only Query the required fields. The impact here is:
    • When total field size less than max_length_for_sort_data, Query and sort field is not a TEXT | BLOB, will use the improved algorithm, single channel sorting, or with the old algorithm, multiple sort.
    • Both algorithms may exceed the sort_buffer capacity. After that, TMP files will be created for merge sorting, resulting in multiple I/ OS. However, using single-path sorting algorithm is more risky, so sort_buffer_size should be increased.
  2. Try increasing sort_BUFFer_SIZE Regardless of which algorithm you use, increasing this parameter will improve efficiency, depending on the system’s capabilities, of course, since this parameter is per-process
  3. Trying to increase max_LENGTH_FOR_sort_DATA Increasing this parameter increases the probability of using the improved algorithm. However, if set too high, the probability of total data capacity exceeding sort_BUFFer_size increases, with high disk I/O activity and low processor utilization evident.

Order By sort index optimization summary

1.3. GROUP BY optimization

Group by keyword optimization

  1. Group by is essentially sorted and then grouped, following the best left prefix for the index
  2. When index columns are unavailable, increase the max_LENGTH_FOR_sort_data parameter setting + increase the sort_BUFFer_SIZE parameter setting
  3. Where having is higher than where having can be qualified
  4. Other rules are consistent with Order by

2. Slowly query logs

2.1 introduction to slow Query logs

What is a slow query log?

  1. The slow query log of MySQL is used to record statements whose response time exceeds the threshold. In particular, SQL statements whose response time exceeds the value of long_query_time are recorded in the slow query log.
  2. The default value of long_query_time is 10, which means that SQL statements running for more than 10 seconds are logged
  3. He will check which SQL exceeds our maximum endurance time value. For example, if an SQL is executed for more than 5 seconds, we will consider it as slow SQL. We hope to collect SQL of more than 5 seconds and conduct comprehensive analysis based on previous explain.

2.2. Enable slow query logs

How to play?

Description:

  1. By default, slow query logging is disabled for the MySQL database. You need to set this parameter manually.
  2. Of course, it is generally not recommended to enable this parameter unless it is necessary for tuning, because slow query logging can have some performance impact. Slow query logs can be written to a file

Check whether and how to enable it

  • Checking whether slow query logs are enabled:
    • By default, the value of slow_query_log is OFF, indicating that slow query logging is disabled
    • This can be enabled by setting the value of slow_query_log
    • throughSHOW VARIABLES LIKE '%slow_query_log%';Check whether slow query logs of mysql are enabled
SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
Copy the code
  • How to Enable slow Query logs:
    • set global slow_query_log = 1;Enable slow log query
    • useset global slow_query_log=1Slow log query takes effect only for the current database. If MySQL is restarted, it becomes invalid.
set global slow_query_log = 1;
Query OK, 0 rows affected (0.07 sec)

SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
Copy the code

  • To be permanent, you must modify the configuration file my.cnf (as well as other system variables)

    • Change the slow_query_log and slow_query_log_file parameters in the my.cnf file under [mysqld] and restart MySQL server.
    • Also configure the following two lines into the my.cnf file

    “`sql [mysqld] slow_query_log =1 slow_query_log_file=/var/lib/mysql/Heygo-slow.log “`

    • Slow_query_log_file specifies the path where the slow_query_log_file log file will be stored. By default, host_name-slow.log is given (if slow_query_log_file is not specified).

After slow query logging is enabled, what KIND of SQL will be recorded in slow query?

  • This is controlled by the long_query_time parameter, which by default is 10 seconds, command:SHOW VARIABLES LIKE 'long_query_time%';Check the threshold of slow SQL
SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
Copy the code
  • You can change it using commands or in the my.cnf parameter.
  • If the running time is exactly long_query_time, it will not be recorded. In other words, the mysql source code is greater than long_query_time, not greater than or equal to.

2.3. Example of Slow Query logs

Case on

  • Check the threshold time of slow SQL. The default threshold time is 10s
SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
Copy the code
  • To set the threshold time for slow SQL, we set it to 3s
set global long_query_time=3;
        Query OK, 0 rows affected (0.00 sec)
<div class="hljs-button {2}" data-title="Copy" data-report-click="{&quot; spm&quot; :&quot; 1001.2101.3001.4259 & quot; }"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li></ul></pre>
<ul><li>Why does the threshold time not change after setting?<ul><li>You need to reconnect or open a new callback to see the changed value.</li><li>Check the global long_query_time value:<code>show global variables like 'long_query_time';</code>Discovery has taken effect</li></ul> </li></ul>` ` `sql
set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)	
Copy the code