Does MySQL create an extra index for primary key or uses the data itself as an "index"

Question

Cant find a explicit answer of that. I know that when you create a primary key, MySQL orders the data according to that primary key, question is, does it actually create another index, or uses the actual data as an index since it should be ordered by the primary key?

EDIT:

if I have a table with has index A and index B and no primary key, I have the data + index A + index B. If I change the table to have columns of index A as the primary key, I will only have data (which is also used as an index) + index B right? The above is in terms of memory usage

Answer 1

Clustered and Secondary Indexes

Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table.

• When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index

• If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

• If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

How the Clustered Index Speeds Up Queries

Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record.

Answer 2

if I have a table with has index A and index B and no primary key, I have the data + index A + index B. If I change the table to have columns of index A as the primary key, I will only have data (which is also used as an index) + index B right? The above is in terms of memory usage

Yes, the index for the clustered index is the table itself. That's the only place other non-indexed columns are stored. When you SHOW TABLE STATUS you see this reported as Data_length. Secondary indexes are reported as Index_length.

mysql> show table status like 'redacted'\G
*************************** 1. row ***************************
           Name: redacted
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 100217
 Avg_row_length: 1168
    Data_length: 117063680    <-- clustered index
Max_data_length: 0
   Index_length: 3653632      <-- secondary index(es)

InnoDB always stores a clustered index. If you have no PRIMARY KEY defined on any columns of your table, InnoDB creates an artificial column as the key for the clustered index, and this column cannot be queried.