"MySQL optimization" index overlay

Index Overrides

means that if the column of the query is exactly part of the index, then the query needs to be done only on the index file, without having to go back to the disk and find the data. This query is very fast, called "Index overlay", less than the usual query to disk read Data operation . (The index exactly overwrites the data in the query)

For example, the following:

mysql> use exam9;database changedmysql> desc options;+----------------+---------------+------+-----+--------- +-------+| Field | Type | Null | Key | Default | Extra |+----------------+---------------+------+-----+---------+-------+| optionID | varchar +)   | NO | PRI |       NULL | || QuestionID | varchar +)   | YES | MUL |       NULL | || optioncontent | varchar -) |     YES | |       NULL | || Optionwithtag | varchar -) |     YES | |       NULL | || Optionsequence | varchar2)    |     YES | |       NULL | || Isanswer | varchar2)    |     YES | |       NULL | || Description | varchar -)  |     YES | |       NULL | |+----------------+---------------+------+-----+---------+-------+7Rows in Set (0.00SEC) mysql> reset query cache;#清空缓存Query OK,0Rows Affected (0.00SEC) mysql> set profiling=on;#打开profiling功能Query OK,0Rows affected,1Warning (0.00SEC) mysql> Select optioncontent from options where optionid= ' 000406aa1b89461d8cfd85fb0e5d9e01 '; +----------------- -------+| Optioncontent |+------------------------+| After the foundation project is finished backfilling |+------------------------+1Row in Set (0.03SEC) mysql> Select optionID from options where optionid= ' 000406aa1b89461d8cfd85fb0e5d9e01 '; +---------------------- ------------+| optionID |+----------------------------------+| 000406AA1B89461D8CFD85FB0E5D9E01 |+----------------------------------+1Row in Set (0.03SEC) mysql> Show profiles;#显示概要信息+----------+------------+-------------------------------------------------------------------------------------+ | query_id | Duration | Query |+----------+------------+------------------------------------------------------------- ------------------------+|1|0.03480675| Select Optioncontent from options where optionid= ' 000406aa1b89461d8cfd85fb0e5d9e01 ' | |2|0.03624525| Select optionID from options where optionid= ' 000406aa1b89461d8cfd85fb0e5d9e01 ' |+----------+------------+----------- --------------------------------------------------------------------------+2Rows in Set,1Warning (0.00SEC) mysql> Explainselect optionID from options where optionid= ' 000406aa1b89461d8cfd85fb0e5d9e01 ' \gerror1064(42000): You have the error in your SQL syntax; Check the manual thatcorresponds to your MySQL server version for the right syntax to use near ' explainselect optionID fro M options where optionid= ' 000406aa1b89461d8cfd85fb0e5d ' atline 1mysql> explain select optionID from options where optio Nid= ' 000406aa1b89461d8cfd85fb0e5d9e01 ' \g#explain分析语句1. Row *************************** id:1 select_type:simple table:options partitions:null type : Constpossible_keys:primary key:primary key_len:122 ref:const rows:1 filtered:10 0.00 Extra:using Index#表示索引覆盖1 row in Set, 1 warning (0.03 sec) mysql> explain select optioncontent from options where optionid= ' 000406AA1B89461D8CFD 85fb0e5d9e01 ' \g#分析语句1. Row *************************** id:1 select_type:simple table:options partitions:null type : Constpossible_keys:primary key:primary key_len:122 ref:const rows:1 filtered:10 0.00 Extra:null1 Row in Set, 1 warning (0.03 sec)

"MySQL optimization" index overlay