Sharing some details about MySQL indexes

A few days ago, a colleague asked me a question about MySQL index. Although I know it roughly, I still want to practice it. Is it possible to use indexes for queries such as is null and is not null? Maybe some articles on the Internet said that indexes cannot be used, but in fact, it is not. Let's take a look at a small experiment

CREATE TABLE `null_index_t` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `null_key` varchar(255) DEFAULT NULL,
 `null_key1` varchar(255) DEFAULT NULL,
 `null_key2` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_1` (`null_key`) USING BTREE,
 KEY `idx_2` (`null_key1`) USING BTREE,
 KEY `idx_3` (`null_key2`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Use a stored procedure to insert data

delimiter $ #Use delimiter to mark the end of the stored procedure. $ indicates the end of the stored procedure. create procedure nullIndex1()
begin
declare i int;	
declare j int;	
set i=1;
set j=1;
while(i<=100) do	
	while(j<=100) do	
		IF (i % 3 = 0) THEN
	   INSERT INTO null_index_t ( `null_key`, `null_key1`, `null_key2` ) VALUES (null , LEFT(MD5(RAND()), 8), LEFT(MD5(RAND()), 8));
  ELSEIF (i % 3 = 1) THEN
			 INSERT INTO null_index_t ( `null_key`, `null_key1`, `null_key2` ) VALUES (LEFT(MD5(RAND()), 8), NULL, LEFT(MD5(RAND()), 8));
	 ELSE
			 INSERT INTO null_index_t ( `null_key`, `null_key1`, `null_key2` ) VALUES (LEFT(MD5(RAND()), 8), LEFT(MD5(RAND()), 8), NULL);
  END IF;
		set j=j+1;
	end while;
	set i=i+1;
	set j=1;	
end while;
end 
$
call nullIndex1();

Then look at our is null query

EXPLAIN select * from null_index_t WHERE null_key is null; 

Let's look at another

EXPLAIN select * from null_index_t WHERE null_key is not null;

What can we see from here? Think about it.

From the above, we can see that is null should be indexed, so at least it is not a blanket rule. However, is not null does not seem to work. Let's make a small change and change the data in this table to 9100 null and the remaining 900 have values, and then execute the following command:

Then let's look at the execution results

EXPLAIN select * from null_index_t WHERE null_key is null;

EXPLAIN select * from null_index_t WHERE null_key is not null; 

Is it different? Here I would like to add that the MySQL I used in the experiment is 5.7, and the consistency of other versions is not guaranteed.
In fact, it can be seen that as the amount of data changes, whether MySQL will use the index will change. It does not mean that is not null will definitely be used, nor will it definitely not be used. Instead, the optimizer will make a prediction based on the query cost. This prediction will reduce the query cost as much as possible, mainly including table return, but it may not be completely accurate.

The above is the detailed content of sharing some details about MySQL index. For more information about MySQL index, please pay attention to other related articles on 123WORDPRESS.COM!