Shulou(Shulou.com)06/03 Report--

This article introduces the knowledge about "Mysql does not take the date field index". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

directory

background

explore

summary

background

In a table, the dataTime field is set to type varchar, the stored data is in date format, and the index is set for that field. But in the log record, there is a slow query about the table. The query statement is:

select * from digitaltwin_meteorological where dataTime > '2021-10-15';

Explain analyzes sql statements and finds that sql statements perform a full table scan. Why does sql use the dataTime index column and why does it take a full table scan?

explore

1: At first, it was thought that the dataTime field type was varchar, so mysql sorted the index in string order, not in date size order, so in range query, the index range partition could not be performed in date order. Therefore, dataTime is changed to datatime type, and in the analysis statement, it is found that it is still a full table scan.

Two: Change the value of the query condition,

select count(*) from digitaltwin_meteorological where dataTime > '2021-10-15';

The result is 3910.

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-15';

SQL statement analysis results in full table scan:

Let's change the query condition to number 16 and see how many pieces of data there are:

select count(*) from digitaltwin_meteorological where dataTime > '2021-10-16';

The query result is 2525. Below we analyze the query statement No. 16:

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-16';

The result of execution is range query, using index:

It can be seen that when the number of records queried is large, mysql will scan the whole table and think that the efficiency of full table scanning is faster. MySQL uses index queries when fewer records are retrieved.

The data volume of the whole table is 19714 pieces of data, that is to say, when 2525/19714=13%, mysql takes index query. When 3910/19714=20%, mysql goes full table scan.

Three: We put dataTime as datetime data type, then whether the query condition still needs quotation marks, we remove the quotation marks of dataTime query condition, see the result:

EXPLAIN select * from digitaltwin_meteorological where dataTime > 2021-10-16;

It can be seen that after removing the quotation marks, it has become a full table scan. Therefore, regardless of whether the field type is varchar or datetime, the value of the query condition needs to be quoted. Without quotation marks, mysql will do some arithmetic operations on this value. In fact, 2021-10-16 is no longer the date of the 16th without quotation marks. We can see the following sql:

select count(*) from digitaltwin_meteorological where dataTime > 2021-10-16;

The calculation result is 19714, the data of the whole table, so the datetime query condition also needs to be quoted.

IV: The analysis above is a discussion of dataTime in the case of datetime type. And the initial field type is varchar, then change to varchar type, such as the above conclusion still exists, we modify the type, and then execute sql:

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-16';

As you can see, after changing to varchar type, query 16 becomes a full table scan instead of a range scan.

Change the condition to No.17 and see the execution result:

EXPLAIN select * from digitaltwin_meteorological where dataTime > '2021-10-17';

Query number 17 went off the index query. Let's look at number 17. It's 1749.

Therefore, when the field type is varchar, 1749/19714=9% of the cases, the index will be taken, and 2525/19714=13% of the cases, the full table will be scanned.

That is to say, when it is datetime type, the query result accounts for 13% of the case, will go index query, and when it is varchar type, the query result accounts for 13% of the full table data, will go full table scan. This is one of the reasons why we set the date type to datetime instead of varchar.

summary

From the above analysis, the following conclusions can be summarized:

1. In a range query, when the amount of data queried reaches a certain range, mysql thinks that full table scanning is more efficient and will go full table scanning instead of indexing.

The value of datetime field type should also be quoted when querying, otherwise mysql will not process it by date.

3. For data in date format, set to varchar type, the threshold value of range query using index or full table scan is lower than the threshold value of datetime type query using index query or full table scan, so if date type data is set to datetime type, there will be a higher probability of index query.

"Mysql does not go to the date field index what is the reason" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.