Almost all of you can say a few words about the pros and cons of creating an index, and know when to create an index to improve query performance, and when to update the index. But have you noticed that even if you set an index, sometimes it doesn’t work! This not only tests how well you know the index, but also allows you to use it correctly. The following introduces some special cases that may cause index failure, I hope you can pay attention to in the usual development and interview time!

How to determine whether the database index is effective

Before we go on, let’s talk about how to determine whether a database index is valid or not! I believe you should guess, is explain! Explain shows how MySQL uses indexes to process SELECT statements and join tables. It can help select better indexes and write better queries.

For example, if we have a table user, create an index name_index for the name column as follows:

  • Table: as the name implies, shows which table the data for this row is about;

  • Type: This is the important column that shows what type the connection is using. The best to worst connection types are const, eq_reg, ref, range, indexhe, and ALL;

  • Possible_keys: Displays possible indexes that can be applied to this table. If empty, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain;

  • Key: indicates the actual index. If NULL, no index is used. In rare cases, MySQL will select an index that is underoptimized. In this case, USE INDEX (indexName) to force the USE of an INDEX or IGNORE INDEX (indexName) to force MySQL to IGNORE the INDEX.

  • Key_len: the length of the index used. The shorter the length, the better, without losing accuracy;

  • Ref: Shows which column of the index is used, if possible, as a constant;

  • Rows: the number of rows that MySQL considers necessary to check to return the request data;

  • Extra: Additional information about how MySQL parses queries.

Concrete columns can say the value and meaning of can refer to the MySQL official document, address: dev.mysql.com/doc/refman/…

Which scenarios will cause the index to take effect

Avoid using it in where clauses! = or <> operators, otherwise the engine will abandon the index and do a full table scan;

2. Avoid using OR in the WHERE clause to join conditions. Otherwise, the engine will abandon the use of indexes and perform a full table scan, even if there is a conditional index.

3, for a multi-column index that is not the first part of the index, the index will not be used;

4. If the column type is a string, the data must be quoted in the condition, otherwise the index will not be used;

SQL > select * from ‘like’;

Avoid expression operations on fields in the WHERE clause. This will cause the engine to abandon the use of indexes and perform full table scans.

Such as:

select id from t where num/2 = 100 
Copy the code

Should be changed to:

select id from t whereNum = 100 * 2;Copy the code

7. Avoid functional manipulation of fields in the WHERE clause. This will cause the engine to abandon the use of indexes and perform full table scans.

Such as:

select id from t whereThe substring (name, 1, 3) ='abc'- the name;Copy the code

A) ABC B) ABC

select id from t whereThe name like '% ABC'Copy the code

Such as:

select id from t where datediff(day, createdate, '2005-11-30') = 0 -'2005-11-30';
Copy the code

Should be changed to:

select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1';
Copy the code

8. Do not perform functions, arithmetic operations, or other expression operations to the left of the ‘=’ in the WHERE clause, or the system may not use the index properly.

MySQL does not use an index if MySQL expects a full table scan to be faster than an index.

10. Not suitable for columns with less key values (columns with more duplicate data)

If the index column TYPE has five key values and there are 10,000 pieces of data, then WHERE TYPE = 1 will access 2000 blocks of data in the table. Plus access to index blocks, more than 200 data blocks are accessed. If you do a full table scan, say 10 data blocks per block, then you only need to access 1000 data blocks. Since full table scan accesses fewer data blocks, it definitely does not utilize indexes.

Reference article:

1, blog.csdn.net/qq_33774822… Mmbiz. Qpic. Cn/mmbiz_png/U…

Java Backend Technology (ID: JavaITWork)1024, you can get it for free! Includes SSM, Spring family bucket, microservices, MySQL, MyCat, cluster, distributed, middleware, Linux, network, multi-threading, Jenkins, Nexus, Docker, ELK and so on free learning video, continue to update!