What is the MySQL index hint

This article introduces the knowledge of "what is the MySQL Index Tip". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

SQL hint is an important means to optimize the database. To put it simply, it is to add some artificial hints to the SQL statement to achieve the purpose of optimizing the operation. The MySQL database supports index hints (INDEX HINT) that explicitly tell the optimizer which index to use. There are several situations in which index hints may be used:

1. The optimizer of MySQL database chooses an index incorrectly, which causes SQL to run very slowly. This is relatively rare in the situation. The optimizer works very effectively and correctly in most cases.

2. There are so many indexes that can be selected by some SQL statements that the cost of the optimizer choosing to execute the plan time may be greater than the SQL statement itself. For example, the optimizer parsing the Range query itself is a time-consuming operation. At this time, DBA or developers analyze the optimal index selection and use index hint to force the optimizer to select the specified index to complete the query without cost analysis of each path.

Index hint species

There are three index hints in MySql: USE INDEX, IGNORE INDEX, and FORCE INDEX. The differences between them are:

1. Use index:use index tells MySql to use one of the indexes in the list to do this query, so that MySQL can no longer consider other available indexes and suggest that MySQL use these indexes, but MySQL may not necessarily use them.

MySQL > show create table test2\ gateway * 1. Row * * Table: test2Create Table: CREATE TABLE `test2` (`id`bigint (16) NOT NULL AUTO_INCREMENT, `order_ seq` bigint (16) NOT NULL, `order_ type` int (11) DEFAULT NULL, `order_ types` int (11) DEFAULT NULL, PRIMARY KEY (`id`) KEY `sec id` (`id`), KEY `idx_id_ orderseq` (`id`, `order_ seq`), KEY `idx_order_ seq` (`order_ seq`) ENGINE=InnoDB AUTO_INCREMENT=15002212 DEFAULT CHARSET=utf8mb41 row in set (0.00 sec) MySQL > explain select * from test2 where id > 10000 and id explain select * from test2 use index (idx_id) where id > 10000 and id explain select * from test2 use index (idx_order_seq) where id=10000 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+-- -+ | 1 | SIMPLE | test2 | NULL | ALL | NULL | 14611349 | Using where | + +-+-+ 1 row in set 1 warning (0.01 sec)

2. Ignore index:ignore index told mysql not to use certain indexes to do this query.

MySQL > show create table test2\ gateway * 1. Row * * Table: test2Create Table: CREATE TABLE `test2` (`id`bigint (16) NOT NULL AUTO_INCREMENT, `order_ seq` bigint (16) NOT NULL, `order_ type` int (11) DEFAULT NULL, `order_ types` int (11) DEFAULT NULL, PRIMARY KEY (`id`) KEY `idx_ id` (`id`), KEY `idx_id_ orderseq` (`id`, `order_ seq`) KEY `idx_order_ seq` (`Getseq`) ENGINE=InnoDB AUTO_INCREMENT=15002212 DEFAULT CHARSET=utf8mb41 row in set (0.00 sec) MySQL > explain select * from test2 where id > 10000 and id explain select * from test2 ignore index (primary) where id > 10000 and id show create table test2\ from test2 ignore index * 1. Row * * Table : test2Create Table: CREATE TABLE `test2` (`id` bigint (16) NOT NULL AUTO_INCREMENT `order_ seq` bigint (16) NOT NULL, `order_ type` int (11) DEFAULT NULL, `order_ seq` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_ id` (`id`), KEY `idx_id_ orderseq` (`id`, `order_ seq`), KEY `idx_order_ seq` (`order_ seq`) ENGINE=InnoDB AUTO_INCREMENT=15002212 DEFAULT CHARSET=utf8mb41 row in set (0.00 sec) MySQL > explain select * from test2 where id > 10000 and id explain select * from test2 force index (idx_id) where id > 10000 and id