Today, let's take a relatively simple case of SQL optimization to analyze a problem developers often find confusing - slowing down SQL due to view merging

For example:
A maintenance person (in this case, maintenance refers to slightly modifying an existing system)
Due to business changes, a condition has been added to the original SQL. As a result, the original fast-running SQL may become slower or even time out may occur (of course, there are many and many different reasons for this). Only one case is discussed here, that is, the slow SQL caused by view consolidation.
This article is only a case, if you want to solve this kind of problem fundamentally, you need to be familiar with the implementation plan.

CREATE TABLE `salaries09` (
 id bigint not null auto_increment primary key ,
 `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 KEY ix_t1 (`emp_no`,`from_date`),
 KEY `emp_no` (`emp_no`),
 KEY `from_date` (`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

As with the tables and data above, the original SQL was run as follows

select * from ( select * from salaries09 where emp_no between 10001 and 10101 order by emp_no )v ;

If the original SQL runs fast, no problem.

However, due to the subsequent changes in business, some conditions need to be added

The modified SQL is as follows

select * from ( select * from salaries09 where emp_no between 10001 and 10101 order by emp_no )v where from_date = '1986-06-26' ;

Assume that the modification results in slowing down!
What should I do if I encounter such problems?

The easiest way is to compare the execution plans before and after modification.

Then, if you find the difference, you can modify the original execution plan.

In this case, before the modification:

root@mysql3306.sock>[employees]> desc select * from (
 -> select * from salaries09 where emp_no between 10001 and 10101 order by emp_no
 -> )v\G
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: salaries09
 partitions: NULL
 type: range
possible_keys: ix_t1,emp_no
 key: ix_t1
 key_len: 4
 ref: NULL
 rows: 13704
 filtered: 100.00
 Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

After modification:

root@mysql3306.sock>[employees]> desc select * from (
 -> select * from salaries09 where emp_no between 10001 and 10101 order by emp_no
 -> )v where from_date = '1986-06-26' \G
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: salaries09
 partitions: NULL
 type: ref
possible_keys: ix_t1,emp_no,from_date
 key: from_date
 key_len: 3
 ref: const
 rows: 8
 filtered: 3.80
 Extra: Using index condition; Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

Finding that the execution plan has changed,

1. We can use hint or view consolidation to fix it.

2. Deeper, we need to analyze the statistical information, whether it reflects the current data volume relatively truthfully.

3. It is also possible to introduce histograms and so on if the data is skewed heavily.

If I don't know anything about it, I want a simple, direct and effective way, what should I do?

Take the example above for example, just refer to the case where there are views!

Add a limit of 10000000000000

At least in most cases, it is guaranteed that if the original SQL is fast, it will be modified very quickly.

Modify to the following:

desc select * from ( select * from salaries09 where emp_no between 10001 and 10101 order by emp_no limit 10000000000000 )v where from_date = '1986-06-26' \G
*************************** 1. row ***************************
 id: 1
 select_type: PRIMARY
 table: <derived2>
 partitions: NULL
 type: ref
possible_keys: <auto_key0>
 key: <auto_key0>
 key_len: 3
 ref: const
 rows: 10
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
 id: 2
 select_type: DERIVED
 table: salaries09
 partitions: NULL
 type: range
possible_keys: ix_t1,emp_no
 key: ix_t1
 key_len: 4
 ref: NULL
 rows: 13704
 filtered: 100.00
 Extra: Using index condition
2 rows in set, 1 warning (0.00 sec)

This can also solve the problem!

This section is from Teacher Songhua's "SQL Optimization Column"

Zheng Songhua, Zhidutang SQL Optimized Class Teacher

Current CCmediaService DBA, responsible for database optimization

Good at SQL optimization, data checking

Friends interested in articles can add me. Here is a friendly duck!