Shulou(Shulou.com)06/01 Report--

Editor to share with you the reasons for the failure of the mysql index. I hope you will gain a lot after reading this article. Let's discuss it together.

Index is a very important chapter for MySQL. There are also a lot of index knowledge points, and if you want to master them thoroughly, you need to break them one by one. Today, let's talk about what circumstances will lead to index failure.

Picture summary version

Related free learning recommendation: mysql video tutorial

Full value matching (best index) explain select * from user where name = 'zhangsan' and age = 20 and pos =' cxy' and phone = '18730658760'

Regardless of the index order, the optimizer at the bottom of MySQL will optimize it, adjusting the order of indexes explain select * from user where name = 'zhangsan' and age = 20 and pos =' cxy' and phone = '18730658760'

1. Violate the leftmost prefix rule if the index has multiple columns, follow the leftmost prefix rule, that is, the query starts from the leftmost front column of the index and does not skip the column explain select * from user where age = 20 and phone = '18730658760' and pos = 'cxy'

2. Any operation on the index column, such as calculation, function, (automatic or manual) type conversion, etc., will result in index failure and full table scan explain select * from user where left (name,5) = 'zhangsan' and age = 20 and phone =' 18730658760'

3. The column to the right of the index range condition will fail explain select * from user where name = 'zhangsan' and age > 20 and pos =' cxy'

4. Try to use the overlay index to access only the index query (the index column is the same as the query column), reducing select*explain select name,age,pos,phone from user where age = 20

5. Use is not equal to (! =,) mysql cannot use an index when it is not equal to (! =,) will cause a full table scan (except for overwriting the index) explain select * from user where age! = 20 * explain select * from user where age 20

6. Like begins with a wildcard ('% abc') index fails explain select * from user where name like'% zhangsan'

Index takes effect explain select * from user where name like 'zhangsan%'

7. Indexing invalid explain select * from user where name = 2000 without single quotation marks

8. Orexplain select * from user where name = '2000' or age = 20 or pos =' cxy' is rarely used in or connections.

9. Order by is normal (index participates in sorting) explain select * from user where name = 'zhangsan' and age = 20 order by age,pos; Note: indexes have two functions: sorting and lookups lead to additional file sorting (which degrades performance) explain select name,age from user where name =' zhangsan' order by pos;// violates the leftmost prefix rule explain select name,age from user where name = 'zhangsan' order by pos,age / / violates the leftmost prefix rule explain select * from user where name = 'zhangsan' and age = 20 order by created_time,age;// with non-indexed fields

10. Group by is normal (index participates in sorting) explain select name,age from user where name = 'zhangsan' group by age; Note: must be sorted before grouping (sorting is the same as order by)

Results in a temporary table (which degrades performance) explain select name,pos from user where name = 'zhangsan' group by pos;// violates the leftmost prefix rule explain select name,age from user where name =' zhangsan' group by pos,age;// violates the leftmost prefix rule explain select name,age from user where name = 'zhangsan' group by age,created_time;// contains non-indexed fields

Sample data used: mysql > show create table user\ gateway * Table: userCreate Table: CREATE TABLE `user` (`id` int (10) NOT NULL AUTO_INCREMENT, `name` varchar (20) DEFAULT NULL, `age` int (10) DEFAULT '0examples, `pos` varchar (30) DEFAULT NULL, `phone` varchar (11) DEFAULT NULL `created_ time`datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name_age_pos_ phone` (`name`, `age`, `pos`, `phone`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci has finished reading this article I believe you have a certain understanding of the reasons for the failure of the mysql index, want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.