Is mysql's innodb, overwritten by index anyway through clustered index queries?

I use only one primary key on the innodb, table and a clustered index (the default is a clustered index). In my query statement, the where condition is this primary key, but the query column is arbitrary. I found that the extra of the execution plan is not using index,. According to theory, the data field of the leaf node of the clustered index stores the whole row of data, no matter what my query column is, as long as the where condition is the primary key, you can get it from the clustered index.

but the result is that using index, occurs only if the query column is a primary key, otherwise extra is empty

one more question, does innodb support federated clustered indexes?

Innodb mysql

Mar.04,2021

The primary key leaf node of

innodb stores the entire row of data, but we should pay attention to the concept of overwriting index. the so-called overlay index is only a concept given that index columns cover the fields of your select. For innodb primary keys, although the entire row of data is stored, the primary key is not made up of all columns. Your example is that an id column is a primary key, and the overlay index is very simple. The fields of your select and the fields filtered by where are within the range of the index column, or you simply have no where condition. Instead, you only select a few columns in the index column. That is the overlay index

if the index contains all the data needed by the query , it becomes an overlay index, that is, it is usually said that there is no need to return to the table operation, that is, the leaf node of the index contains the data they are looking for (the hash index is not allowed). If any of your columns are not on the index node, they will not become an overlay index.

criteria: using explain , you can judge by the output extra column. For an index overlay query, it is displayed as using index , and the MySQL query optimizer determines whether there is an index override query before executing the query.

Previous: Apache performs reverse proxy based on parameters

Next: How to easily maintain two similar sets of components in react

Does the MySQL primary key index leaf node store the row data itself or its physical address?
such as the title. see that MySQL generates a tree for the primary key, and the leaf node holds the row data corresponding to the primary key. The secondary index leaf node holds the value of the primary key. excuse me: does the leaf node of th...

Mysql-Index mysql-optimized innodb mysql

Mar.04,2021
Does innodb storage engine index page and data page exist together?
as shown in the question, is there only data or indexes on the hard disk and in a data page in innodb_pool_buffer? is it impossible for a data page to have both data and indexes? ...

Innodb mysql

Mar.10,2021
What is the locking mechanism of mysql transaction serialization
what is the locking mechanism of mysql transaction serialization isolation level Open transactions in both An and B clients, if 1 if A transaction deletes a record (), B transaction cannot read deleted and uncommitted transactions in A transaction not...

Db lock transaction innodb mysql

Mar.11,2021
What is the automatic locking strategy for InnoDB and Myisam in MySQL?
What is the automatic locking strategy for InnoDB and Myisam in RT,MySQL, respectively? When do we need to explicitly add read-write locks? ...

Myisam innodb mysql

Mar.17,2021
What locks will be added by default when mysql innodb engine insert data
I recently did a raffle everyone can only draw once I want to insert a record for each lucky draw request, and then read the first record for comparison to determine whether he is drawing the lottery for the first time, delete the useless record if n...

Lock insert innodb mysql php

May.11,2021
[the problem is not established] Why deadlocks occur among successive insert under multiple processes in mysql
Sorry, there is a mistake in the question. An explanation has been made in the answer the scenario is that there is a script task, that starts multiple processes these processes operate on multiple tables at the same time (only insert, for these t...

Deadlock innodb mysql

Aug.27,2021