Prepare environment

create index idx_seller_name_sta_addr on tb_seller(name,status,address);

1) . full value matching is the most efficient

explain select * from tb_seller where name='Xiaomi Tech ' and status='1' and address='Beijing'

2) . leftmost prefix rule
If you index multiple columns, follow the leftmost prefix rule. This means that the query starts at the top left of the index and does not skip columns in the index.
Example

explain select * from tb_seller where name='Xiaomi Tech ' and status='1' and address='Beijing';
--Walk index
explain select * from tb_seller where name='Xiaomi Tech ' and status='1';
-- Walk index
explain select * from tb_seller where name='Xiaomi Tech ' and address='Beijing';
-- be gone name Index, but address The index does not go and becomes invalid. If the leftmost rule is met, but a column jumps, only the leftmost column index takes effect:
explain select * from tb_seller where status='1' and address='Beijing';
-- No index
explain select * from tb_seller where status='1' and address='Beijing' and name='Xiaomi Tech ';
-- Go index, and where Order independent in Clause

It's like climbing stairs. You can't go up to the next floor until you go up one floor

3) . the column on the right of range query cannot use index.

explain select * from tb_seller where name='Xiaomi Tech ' and status>'1' and address='Beijing';
--Just walk name and status Index, index after range query is invalid

4) . the operation is performed on the index column. When the front and rear indexes are invalid.

explain select * from tb_seller where substring(name,3,2) = 'science and technology' and status='1' and address='Beijing';

explain select * from tb_seller where substring(name,3,2) = 'science and technology'
-- Neither goes

5) . the string is not enclosed in single quotation marks, resulting in index invalidation.
A numeric string can be found without single quotation marks, but the current column and the index behind the current column will fail, because the underlying layer will judge that it is a string type and perform implicit type conversion, that is, operation, so the index will fail

explain select * from tb_seller where name='Xiaomi Tech ' and status=1 and address='Beijing';
-- Just go name Indexes

6) . use the overlay index as much as possible to avoid select * use the overlay index as much as possible (only access the query of the index (the index column completely contains the query column)), reduce select *, and reduce the operation of returning to the table.

EXPLAIN select sellerid,name,status,address from tb_seller where name = 'Xiaomi Tech '
-- For example, the query composite index column and primary key column are in the same tree, and there is no need to return to the table. However, after adding other fields, the return to the table is required, which is inefficient

7) . for the condition split by or, if the column in the condition before or has an index and there is no index in the subsequent column, the involved index will not be used.

For example, the name field is an index column, while createtime is not an index column. or is in the middle. The connection does not go through the index, and the exchange order is the same:

explain select * from tb_seller where name='Xiaomi Tech ' or createtime = '2088-01-01 12:00:00';
-- No index

8) . Like fuzzy query starting with%, index invalid.

If it is only tail fuzzy matching, the index will not fail. If it is a header fuzzy match, the index is invalid.

explain select * from tb_seller where name like '%Rice technology';

9) . if MySQL evaluation uses indexes more slowly than full tables, indexes are not used.

explain select * from tb_seller where address='Beijing';
-- No index
explain select * from tb_seller where address='Xi'an City';
-- Walk index

Most of the databases are Beijing indexes. It's better to use the index than to directly scan the whole table, but how do you know that most of them are Beijing before database scanning?
10). is NULL, is NOT NULL sometimes the index fails.

explain select * from tb_seller where address is NULL;-- Walk index

explain select * from tb_seller where address is not NULL;-- No index

Because most of the data in the table is not null, it is not as good as scanning the whole table. Vice versa. According to the specific situation, if there are many nulls, null does not go through the index and not null goes through the index

11). in index, not in index is invalid.
12) . single column index and composite index.

Try to use composite indexes instead of single column indexes.
create index idx_name_sta_address on tb_seller(name, status, address);

This is equivalent to creating three indexes:
name
name + status
name + status + address
If a single column index is used, the database will select an optimal index (the most recognizable index) to use, and will not use all indexes.