Which query situations in mysql do not use the index?

mysql In which query situations do not use the index

1. The index column participates in the calculation and does not use the index

SELECT `username` FROM `t_user` WHERE age=20;-- 会使用索引
SELECT `username` FROM `t_user` WHERE age+10=30;-- 不会使用索引！！因为所有索引列参与了计算
SELECT `username` FROM `t_user` WHERE age=30-10;-- 会使用索引

2. The index column uses a function and may not use the index

-- 不会使用索引,因为使用了函数运算,原理与上面相同
SELECT username FROM t_user WHERE concat(username,'1') = 'admin1'; 
-- 会使用索引
SELECT username FROM t_user WHERE username = concat('admin','1');

3. Use the like statement for the index column, and may not use the index

SELECT * FROM USER WHERE username LIKE 'mysql测试%'   --走索引
SELECT * FROM USER WHERE username LIKE '%mysql测试'   --不走索引
SELECT * FROM USER WHERE username LIKE '%mysql测试%'  --不走索引

4. Implicit conversion of data types, direct comparison between string columns and numbers, without using the index

-- stock_code字符串类型带索引
SELECT * FROM `stock_data` WHERE stock_code = '600538'  --走索引
SELECT * FROM `stock_data` WHERE stock_code = 600538  --不走索引

5. Try to avoid OR operation, as long as one field does not have an index, the index will not be used when changing the statement, and the index will not be used!

-- stock_code带索引，open不带索引
SELECT * FROM `stock_data` WHERE `stock_code` = '600538' OR `open` = 6.62  -- 不走索引
-- stock_code带索引，up_down_pre带索引
SELECT * FROM `stock_data` WHERE `stock_code` = '600538' OR `up_down_pre` = 5.1  -- 走索引

6. where id !=2 or where id <> 2, no indexing!

SELECT * FROM t_user WHERE username <> &#39;mysql测试&#39;

7. If it is null or is not null, you cannot use the index. Do not use the index!

SELECT * FROM t_user WHERE username IS NULL -- 不走索引
SELECT * FROM t_user WHERE username IS NOT NULL -- 不走索引

8. The index column uses the in statement, and the index may not be used

-- stock_code数据类型为varchar
SELECT * FROM `stock_data` WHERE `stock_code` IN ('600538')  -- 走索引
SELECT * FROM `stock_data` WHERE `stock_code` IN ('600538','688663','688280')  -- 走索引
SELECT * FROM `stock_data` WHERE `stock_code` IN (大量数据)  -- 不走索引
SELECT * FROM `stock_data` WHERE `stock_code` IN (600538)  -- 不走索引

The situation of not using the index:

1. There is no query condition, or the query condition No indexes are created in the business database, especially tables with relatively large amounts of data.

Suggestions:

1 Change to indexed columns as query conditions

2 Or index frequently queried columns

2. The query result set is most of the data in the original table. It should be more than 25% of the

query result set. If it exceeds 25% of the total number of rows, the optimizer feels that there is no need to use the index. .

Suggestion:

1 If the business allows, you can use limit control.

2 Based on business judgment, is there a better way? If there is no better rewriting solution

3 Try not to store this data in mysql. Put it in redis.

3. The index itself is invalid and the statistical data is unreal

The index has the ability to self-maintain. When the table content changes frequently, the index may appear. Invalid.

Change plan:

Back up table data, delete and rebuild related tables.

4. Query conditions use functions on index columns, or perform operations on index columns. Operations include (, -, *, /,!, etc.)

Change Method:

Reduce the use of calculation operations such as addition, subtraction, multiplication and division in mysql.

5. Implicit conversion causes index failure. This should be taken seriously. It is also a mistake often made in development.

The field created by the index is varchar() ;

select * from stu where name = ‘111'；走索引
select * from stu where name = 111；不走索引

Change method:

Consult with R&D, and the statement query complies with the specifications.

6.<>, not in without indexing (auxiliary index)

Change method:

Try not to use the above method to query , or select the index column as a filter condition.

Individual >,<,in may or may not go. It depends on the result set. Try to add limit

or or in according to the business. Try to change it to union

7.like “%” The percent sign is not placed at the front

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘31%' 走索引
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘%110' 不走索引

Change method:

For search requirements of %linux% class, you can use elasticsearch mongodb is a database product specializing in search services

The above is the detailed content of Which query situations in mysql do not use the index?. For more information, please follow other related articles on the PHP Chinese website!