Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "mysql which query situation does not go index", the editor shows you the operation process through the actual case, the operation method is simple and fast, practical, hope that this "mysql which query situation does not go index" article can help you solve the problem.

Which queries in mysql are not indexed?

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

SELECT `username` FROM `t _ user` WHERE age=20;-- will use index SELECT `username` FROM `t _ user` WHERE age+10=30;-- will not use index! Because all the index columns participate in the calculation of SELECT `username` FROM `t _ user` WHERE age=30-10 indexing-will use the index

2. Index columns use functions and may not walk the index.

-- the index will not be used because the function operation is used and the principle is the same as above SELECT username FROM t_user WHERE concat (username,'1') = 'admin1';-- index SELECT username FROM t_user WHERE username = concat (' admin','1')

3. Index columns use like statements, which may not leave the index.

SELECT * FROM USER WHERE username LIKE 'mysql test%'-- index walking SELECT * FROM USER WHERE username LIKE'% mysql test'--no index SELECT * FROM USER WHERE username LIKE'% mysql test%'--no index

4. Implicit conversion of data types, direct comparison of string columns and numbers, and no index

-- stock_code string type indexed SELECT * FROM `stock_ data` WHERE stock_code = '600538'-- indexed SELECT * FROM `stock_ data` WHERE stock_code = 600538-- No indexing

5, try to avoid OR operation, as long as there is a field without an index, change the statement will not go the index, do not go the index!

-- stock_code with index, open without index SELECT * FROM `stock_ data`WHERE `stock_ code` = '600538' OR `open` = 6.62-no index-stock_code with index, up_down_pre with index SELECT * FROM `stock_ data`WHERE `stock_ code` =' 600538' OR `up_down_ pre` = 5.1-use index

6. Where id! = 2 or where id 2, do not take the index!

SELECT * FROM t_user WHERE username 'mysql Test'

7, is null,is not null can not use the index, do not go the index!

SELECT * FROM t_user WHERE username IS NULL-- No index SELECT * FROM t_user WHERE username IS NOT NULL-- No index

8. Index columns use in statements, which may not leave the index.

-- stock_code data type is varcharSELECT * FROM `stock_ data` WHERE `stock_ code` IN ('600538')-indexing SELECT * FROM `stock_ data` WHERE `stock_ code` IN ('600538')-indexing SELECT * FROM `stock_ data`WHERE `stock_ code` IN (a large amount of data)-- No index SELECT * FROM `stock_ data`WHERE `stock_ code` IN (600538)-- No index or no index:

1. There is no query condition, or the query condition is not indexed in the business database, especially the table with a large amount of data.

Recommendations:

1 replace the indexed column as the query condition

2 or index the frequently queried columns

two。 The query result set is most of the data in the original table and should be more than 25%.

The result set of the query exceeds 25% of the total number of rows, and the optimizer does not feel the need to index.

Recommendations:

1 if the business permits, you can use limit control.

2 combined with business judgment, there is no better way. If there is no better rewriting plan

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

3. The index itself is invalid and the statistics are not true.

The index has the ability of self-maintenance, and index failure may occur when the contents of the table change frequently.

Change the scheme:

Backup table data, delete and rebuild related tables.

4. Query conditions use functions to perform operations on or on index columns, including (+, -, *, /,! Etc.)

Change method:

Reduce the use of addition, subtraction, multiplication and division in mysql.

5. Implicit conversion results in index invalidation. This point should be paid attention to. It is also a common mistake in development.

The indexed field is varchar ()

Select * from stu where name = '111indexes; walk index select * from stu where name = 111; do not index

Change method:

In consultation with R & D, the sentence query is in line with the specification.

6. Not in does not walk the index (secondary index)

Change method:

Try not to query in the above way, or choose to have an index as a filter condition.

Separate >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.