Shulou(Shulou.com)05/31 Report--

This article mainly introduces the MySql index failure example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. When writing sql statements with MySql, it is often written as follows:

Select * from t_cs_baseinfo a where a.FEE_SUBSCRIB_FLAG is null or trim (a.FEE_SUBSCRIB_FLAG) =''

There is a big problem here. If FEE_SUBSCRIB_FLAG is an index column, because the index column does not store null values, the execution plan does not use the index, as follows

Mysql > explain select * from t_cs_baseinfo a where a.FEE_SUBSCRIB_FLAG is null or trim (a.FEE_SUBSCRIB_FLAG) =''

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 81945 | Using where |

+-- +

1 row in set

In order to make full use of the index, the index column should be set to not null, which can not only eliminate the is null and is not null in the sql statement, improve the retrieval speed by using the index reasonably, but also eliminate the null value judgment on the Java side and simplify the code structure.

2. The Mysql engine will decide whether to use the index according to the index optimizer. If the index optimizer thinks that using full table scan is faster, it will not use the index. In this case, if you still want to use the index, you can achieve this by using FORCE INDEX:

Select * from t_cs_baseinfo a FORCE INDEX (fee_subscrib_flag) where a.FEE_SUBSCRIB_FLAG in

Thank you for reading this article carefully. I hope the article "sample Analysis of MySql Index failure" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.