This is the 9th day of my participation in Gwen Challenge

preface

Creating an appropriate index through a WHERE condition is only the first step; designing a good index should consider the entire query.

Definition of overrides index

MySQL can also use indexes to retrieve column data directly, eliminating the need to read rows. If an index contains (or overwrites) the values of all the fields to be queried, it is called a “overwrite index” because the leaves of the index already contain the data to be queried, and there is no need to look up the data in the table.

Overwriting indexes can greatly improve performance

1. Data visits can be reduced because index entries are typically much smaller than the number of data rows 2. Reduce the number of random reads of IO. Indexes are typically stored in order of column values, which is much less than the number of random reads of each row from disk. 3. When the engine is InnoDB, it can avoid the secondary query on the primary key index, because the data is directly read from the index, no need to return to the table. The cost of satisfying a query in an index is generally much less than a query row. Note that MySQL can only use b-tree index to do overwrite index.

The data structure of a table

CREATE TABLE `chinese_score` (
  `exam_id` int(11) NOT NULL COMMENT 'Test Id, foreign key',
  `exam_code` int(11) NOT NULL COMMENT 'candidate number',
  `user_name` char(5) DEFAULT NULL,
  `class_name` int(11) DEFAULT NULL COMMENT 'Class Name',
  `total_score` int(11) DEFAULT NULL,
  `area_ranking` int(11) DEFAULT NULL,
  `school_ranking` int(11) DEFAULT NULL,
  `class_ranking` int(11) DEFAULT NULL.PRIMARY KEY (`exam_code`) USING BTREE,
  UNIQUE KEY `idx_exam_id_name` (`exam_id`,`class_name`,`user_name`) USING BTREE,
  KEY `idx_total_score` (`total_score`) USING BTREE,
  KEY `idx_exam_id` (`exam_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

When do we use index override queries

Like this SQL

SELECT exam_id,class_name,user_name FROM `chinese_score` where exam_id=1
Copy the code

Using index is used in Extra, which means that an overwrite index is used.

InnoDB overwrite indexes can contain primary key columns

One of the interesting things about InnoDB is that it can contain primary key columns that do not exist in the index, because the leaf nodes in the secondary index actually contain primary key values in addition to the values in the index. Such as the following SQL

SELECT exam_id,exam_code,user_name FROM `chinese_score` where exam_id=1
Copy the code

Exam_code is not included in the index, but since it is the primary key, we can still use idx_exam_id_name to override the index query.

conclusion

Today we covered overwriting indexes in MySQL, and tomorrow we will cover other ways to build high performance indexes. See you in the next article!