First, preparation

  • Create a tablet_index, the script is as follows:
CREATE TABLE `t_index` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Table record ID, database primary key, not used for real business'.`key1` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT ' ' COMMENT 'field 1'.`key2` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT ' ' COMMENT Fields' 2 '.`key3` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT ' ' COMMENT 'field 3'.`del_flag` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Delete flag. 0 is not deleted, and 1 is deleted. '.`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time'.`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Record modification time',
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_update_time` (`update_time`))ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Index table';
Copy the code

2. Common index

1. Preparation

  • In the fieldkey1Create index on:
ALTER TABLE `t_index` ADD INDEX idx_key1(key1);
Copy the code

2. Run the index normally

  • Query script
SELECT * FROM `t_index` WHERE key1 = '1';
Copy the code
  • Viewing the Execution Plan
EXPLAIN SELECT * FROM `t_index` WHERE key1 = '1';
Copy the code
  • The result shows the index query, as shown below

3. The following scenarios may result in the query using all tables but not indexes

(1) The query conditions use inequalities

  • Query script
SELECT * FROM `t_index` WHERE key1 <> '1';
Copy the code

  • View the execution plan. A full table scan is displayed, as shown in the following figure

  • Summary: Inequality <> or! = causes index invalidation

(2) The types of query conditions are inconsistent

  • Query script
SELECT * FROM `t_index` WHERE key1 = 1;
Copy the code

  • View the execution plan. A full table scan is displayed, as shown in the following figure

  • Summary: Field KEY1 is a string, and the value passed in is numeric, which invalidates the index

(3) Query conditions are calculated using functions

  • Query script
SELECT * FROM `t_index` WHERE key1 + 1 = 1;
SELECT * FROM `t_index` WHERE CHAR_LENGTH(key1) = 1;
Copy the code

  • View the execution plan. A full table scan is displayed, as shown in the following figure

  • Summary: The function evaluates x+1, x-1, CHAR_LENGTH(x), etc., causing index invalidation

(4) Fuzzy query

  • Query script
SELECT * FROM `t_index` WHERE key1 LIKE  '3';
SELECT * FROM `t_index` WHERE key1 LIKE  '% 3';
SELECT * FROM `t_index` WHERE key1 LIKE  '3%';
Copy the code

  • View the execution plan as shown in the following figure

  • Summary: Fuzzy query query condition prefix fuzzy does not go index

Compound index

1. Preparation

  • Delete the previous index in the fieldkey1, key2, key3To create a composite index, run the following script:
DROP INDEX idx_key1 ON `t_index`;
ALTER TABLE `t_index` ADD INDEX idx_key123(key1, key2, key3);
Copy the code

2. Run the index normally

  • Take a look at the query script for normal indexing
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = '3';
Copy the code
  • Viewing the Execution Plan
EXPLAIN SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = '3';
Copy the code
  • The result shows the index query, as shown below

3. The following scenarios may result in the query using all tables but not indexes

(1) The query conditions use inequalities

  • Query scripts do not move indexes if one of the conditions contains an inequality
SELECT * FROM `t_index` WHERE key1 <> '1' AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 <> '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 <> '3';
Copy the code

  • When you view the execution plan, a full table scan is displayed. The result is the same in the following figure

  • Summary: Logical plain index

(2) The types of query conditions are inconsistent

  • Query script
SELECT * FROM `t_index` WHERE key1 = 1 AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = 2 AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = 3;
Copy the code

  • If the type of the first parameter is inconsistent, scan the entire table. If the type of the second parameter is inconsistent, the index can only use the first column. If the type of the third parameter is inconsistent, the index can use the first two columns

  • Summary: From the first query condition, the NTH parameter type is inconsistent, the index can use the first n-1 column

(3) Query conditions are calculated using functions

  • Query script
SELECT * FROM `t_index` WHERE key1 + 1 = '1' AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 + 1 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 + 1 = '3';
Copy the code

  • View the execution plan. The result is the same as above (if the type of the first parameter is inconsistent, the whole table is scanned; if the type of the second parameter is inconsistent, the index can only use the first column; if the type of the third parameter is inconsistent, the index can use the first two columns), as shown in the following figure

  • Summary: Logical plain index

(4) do not use index first column as query condition

  • Query script
SELECT * FROM `t_index` WHERE key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key2 = '2';
SELECT * FROM `t_index` WHERE key3 = '3';
Copy the code

  • When you view the execution plan, the result is displayed. The result is the same in the three cases, as shown in the following figure

  • Summary: Query criteria that do not use the first column of a composite index will invalidate the index

In this paper, to the end.