Under what circumstances does Mysql hit the index

1. Use OR conditions

-- Assumptionsnamecolumn with index，ageColumns have no index
SELECT * FROM employees WHERE name = 'John' OR age > 30;

In this query,ageThe column has no index, and the optimizer may choose to use full table scan instead of usingnameIndex on the column.

2. Implicit type conversion

-- AssumptionsidIt's an integer type，But compare strings in query
SELECT * FROM users WHERE id = '123';

althoughidThere is an index on the column, but MySQL may not be able to use that index due to implicit type conversion.

3. The leading wildcard for fuzzy query

-- Assumptionsusernamecolumn with index
SELECT * FROM users WHERE username LIKE '%john';

Because the mode%Starting, so the index cannot be used to speed up queries.

4. Perform function operations on index columns

-- Assumptionscreated_atcolumn with index
SELECT * FROM orders WHERE YEAR(created_at) = 2020;

rightcreated_atAppliedYEAR()function, causing index failure.

5. Composite index does not follow the leftmost prefix principle

-- Create a composite index (first_name, last_name)
CREATE INDEX idx_name ON employees(first_name, last_name);
-- Query onlylast_name
SELECT * FROM employees WHERE last_name = 'Doe';

Since the query condition does not start from the first field of the composite index, this index will not be used.

6. Low index selectivity

-- AssumptionsstatusThere are many duplicate values ​​for the column（likeactive/inactive），And have indexes
SELECT * FROM accounts WHERE status = 'active';

ifstatusColumn selectivity is low (i.e., large numbers of records have the same value), and MySQL may choose a full table scan instead of an index scan.

7. Index length exceeds limit

-- Assumptionstext_columnIt's a very longVARCHARList，And try to create an index that is too long
CREATE INDEX idx_long_text ON articles(text_column(255));

iftext_columnThe length exceeds the maximum length of the InnoDB index, index creation will fail.

8. Table statistics expired

-- If no analysis table is performed for a long time，Statistics may be inaccurate
ANALYZE TABLE employees;

Run regularlyANALYZE TABLECan help optimizers make better decisions.

9. Index columns participate in NULL check

-- Assumptionsnullable_columnallowNULLvalue，And have indexes
SELECT * FROM items WHERE nullable_column IS NULL;

Although B-tree index can be processedIS NULL, but if designed improperly, this may still cause the index to not be used.

10. Query result set is too large

-- Even if there is an index，If the query returns a very large result set
SELECT * FROM logs WHERE log_date BETWEEN '2020-01-01' AND '2020-12-31';

In this case, full table scanning may be a more efficient choice.

The above example shows how to affect the use of indexes in different scenarios. To ensure that the index works effectively, these factors need to be carefully considered and throughEXPLAINCommand Verification Query Plan

This is the article about Mysql's index under what circumstances will not hit index. For more related content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!