I am fat brother, an unprofessional interviewer!

I am embarrassed, a rookie who is actively looking for a job!

Embarrassed said: the most afraid of the interview is that the interviewer asked the knowledge point is too general, they can not quickly locate the key question point!!

This is the main interview question

Under what circumstances will the index fail?Copy the code

This installment examines the following common index failure scenarios

Mysql > select * from ‘where’; mysql > select * from ‘where’; mysql > select * from ‘where’; mysql > select * from ‘where’; mysql > select * from ‘where’; mysql > select * from ‘where’; SQL > alter table select * from ‘where’; SQL > alter table select * from ‘where’; Negative queries include: NOT,! =, <>! <,! > < p style = “max-width: 100%; clear: both; <! > SQLServer syntax. SQL > alter table select * from MySQL; SQL > alter table select * from MySQL; SQL > alter table select * from MySQL; SQL > alter table select * from MySQL Don’t walk index

Validation for

Prepare the table and create the common index idx_user_name

CREATE TABLE 't_user' (' id 'int(11) NOT NULL,' user_name 'varchar(32) CHARACTER DEFAULT NULL COMMENT' username ', 'address' varchar(255) CHARACTER DEFAULT NULL COMMENT 'address ', 'create_time' datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'create time ', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Copy the code

Mysql > select * from table_name where table_name = ‘1’ and table_name = ‘1’; mysql > select * from table_name where table_name = ‘1’; mysql > select * from table_name where table_name = ‘1’; mysql > select * from table_name where table_name = ‘1’;

CREATE PROCEDURE user_INSERT () -- define the start of the stored PROCEDURE BEGIN -- define the variable I, int, The DEFAULT value is 1 DECLARE I INT DEFAULT 1; DO INSERT INTO T_user (id, user_name, address, create_time) VALUES(I, CONCAT('mayun', I), CONCAT(' zhejiang hangzhou ', I), now()); SET i=i+1; END WHILE; COMMIT; END; CALL user_Insert ();Copy the code

OR index validation

If or is used in the where condition, then the index must be invalid.

OR joins the same field with the same index

explain select * from t_user where user_name = 'mayun10' or user_name = 'mayun1000'
Copy the code

OR joins two different fields. Different indexes are invalid

Explainicon select * from t_user where user_name = 'mayun10' or address = 'hangzhou'Copy the code

Add index to address column

alter table t_user add index idx_address (address)
Copy the code

OR joins two different fields. If both fields have indexes, go to the index

Verify the summary

Or may cause index failures, but not necessarily. MySQL index merge

1. Before MySQL5.0, only one index can be used for a table at a time, and multiple indexes cannot be used for conditional scanning.

2. Since 5.1, MySQL has introduced index merge optimization technology, which can use multiple indexes to perform conditional scans on the same table. Their respective results are then merged (INTERSECT/Union).

What happens when an OR index takes effect?

The first or joins the same index field on both sides

The second type of OR connects two index fields, that is, the two fields are respectively indexed

LIKE wildcard index invalidation

One of the most common query scenarios is to create the idx_user_name index

select * from t_user where user_name like '%mayun100%';
Copy the code

Is this query indexed?

select * from t_user where user_name like 'mayun100%';
Copy the code

Is this query indexed?

Verify the summary

The "like" wildcard can be used to open and close the matching query. If the "like" wildcard is used to open and close the matching query, the "like" wildcard can be used to open and close the matching queryCopy the code

Why index invalidation in left open cases? Please introduce the principle!

We know that MySQL will create an ordered B+Tree after the index is created. The index Tree is ordered, and the index columns are matched from left to right. Use % and _ matches, which indicate that the left-hand matching value is indeterminate. Uncertain, which means full of possibilities. How do you compare?

Of course, it can only be compared one by one, that is equivalent to, full match, full match in the view of the optimizer, rather than go through the index tree query, and then continue to return to the table operation, it is better to directly perform a full table scan cost!

Use mysql’s built-in functions for index columns in where

Create idx_age index,

alter table t_user add index idx_age(age);
Copy the code

Do not use built-in functions

explain select * from t_user where age = 80
Copy the code

Using built-in functions

explain select * from t_user where abs(age) = 80
Copy the code

Verify the summary

The optimizer decides not to use the tree-searching function because functional operations on index fields can break the order of index values.

MySQL is no longer able to use index quick location, but can only use full index scan.

Where (+, -, *, /)

No operations on indexed columns are involved

alter table t_user add index idx_age(age);
explain select * from t_user where age = 80;
Copy the code

Index column for operations

explain select * from t_user where age + 5 = 80
Copy the code

Fifth, the type is inconsistent, implicit type conversion, resulting in index failure

alter table t_user add index idx_user_name(user_name);

explain select * from t_user where user_name = 'mayun1';
Copy the code

Modify the data, explain again

update t_user set user_name = '100' where user_name = 'mayun1';
explain select * from t_user where user_name = 100;
Copy the code

User_name = 100, because the user_name field is defined as vARCHAR, the index will implicitly call the case() function to cast the match condition to user_name = ‘100’

SQL > select * from ‘where’;

Negative queries include: NOT,! =, <>! <,! > < p style = “max-width: 100%; clear: both; <! > SQLServer syntax.

alter table t_user add index idx_age(age);
explain select * from t_user where age in (100, 50);
Copy the code

explain select * from t_user where age not in (100, 50);
Copy the code

7. Index fields can be null. Using IS NULL or IS not NULL may cause index invalidation

In the first case, the table structure allows the user_NAME field to be NULL

explain select * from t_user where user_name is null;
Copy the code

explain select * from t_user where user_name is not null;
Copy the code

In the second case, the table structure dictates that the user_name field cannot be considered null

explain select * from t_user where user_name is null;
Copy the code

explain select * from t_user where user_name is not null;
Copy the code

Index invalidation caused by implicit character encoding conversion

When two tables are joined, if the character encodings of the two tables are different, index invalidation may occur.

Mysql > alter table UTF8mb4; mysql > alter table UTF8; mysql > alter table UTF8; mysql > alter table UTF8mb4; mysql > alter table UTF8; mysql > alter table UTF8;

If the reader reappears to this scene, welcome to comment on the discussion or concern if the reader reappears to this scene, welcome to comment on the discussion or concern about the public account 囧 mE fat matter

In a joint index, if the index column in the where column violates the leftmost matching principle, it will definitely cause the index to fail

Create a federated invocation idx_user_name_deposit, following the leftmost matching rule

alter table t_user add index idx_user_name_deposit(user_name, deposit);

explain select * from t_user where user_name like 'mayun86%'
Copy the code

Follow the left-most matching a b type

Explain select * from t_user where user_name like 'mayun86%' and deposit = 5620.26; explain select * from t_user where user_name like 'mayun86%' and deposit = 5620.26;Copy the code

Swap index positions to test joint index writing rules

Explain select * from t_user where deposit = 5620.26 and user_name like 'mayun86%'; explain select * from t_user where deposit = 5620.26 and user_name like 'mayun86%';Copy the code

Violate the leftmost matching rule

Explain select * from t_user where deposit = 5620.26;Copy the code

Verify the summary

The union index establishes an index tree based on the left-most matching principle. The index values are matched according to the order of the union indexes. If the left-most matching principle is violated during the query, the index will be invalid.

If the index idx_A_b_C is created, it is equivalent to creating three indexes (a), (a,b), and (a,b, C). The matching sequence is A, B, and C. If no a field is filtered during the query, the index will be invalidCopy the code

Lift chestnut, go index situation

select * from test where a=1 
select * from test where a=1 and b=2 
select * from test where a=1 and b=2 and c=3
Copy the code

What about index failure?

select * from test where b=2 and c=3
Copy the code

The query condition must contain the first index. Otherwise, the index is invalid

select * from test where b=1 and a=1

select * from test where m='222' and a=1
Copy the code

What are the reasons why these two queries go to the index?

The left-most prefix indicates that the matching index columns are created in the same order as the union index, but the MySQL optimizer does not need to write in the same order as the union index columns, so the above two indexes are valid.

The MySQL optimizer’s ultimate choice is not to walk the index

explain select * from t_user where age > 59;
Copy the code

explain select * from t_user where age > 99;
Copy the code

Verify the summary

MySQL query index failure there are many cases, even if other cases are avoided, but after the optimization to determine the query scheme, still may be index failure.

The optimizer considers the cost of the query to determine what it thinks is the best way to execute the query

When there is a small amount of data or a large number of rows to access

The optimizer will discard the index tree when it considers that a full table scan is better than a full table scan.

Index failure scenario validation

Follow fate update, collation is not easy, welcome to contact xiaobai discussion, god baba please detour! For more exciting content, please pay attention to wechat public number: ** Jiongmefeishi ** (or search: Jiongmefeishi)Copy the code