Clustered Indexing in MySQL

Question

I am learning indexing in Databases . According to GeeksforGeeks (https://www.geeksforgeeks.org/indexing-in-databases-set-1/) , Index file is created for clustered index . There is a diagram showing index file having semesters 1-8 .

But while reading https://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key , it is mentioned

If a table has a clustered index, it basically means the index is the table

I want to know whether an index file is generated in clustered index and non clustered index or not ?

Also , can we see the index file on a table ,i.e, what it stores ?

Answer 1

MySQL has essentially one indexing method: BTree. (Yeah, there are also Spatial and Fulltext, but that is another discussion.)

Once you understand how a BTree works (see Wikipedia), we can discuss what goes into the leaf nodes in InnoDB.

Case 1: The "data" BTree contains all the columns and is sorted according to the PRIMARY KEY. In MySQL, the PK is by definition, "UNIQUE" and "Clustered". (Other vendors have other options.)

Case 2: A 'secondary' INDEX is stored in a separate BTree. In the leaf nodes are (1) the column(s) defined in the secondary index, plus copies of the PK column(s). To complete a SELECT using a secondary index, it must first fetch the PK using the index BTree, then fetch the data via the data BTree. (If the index is "covering", this second step is not needed.)

There is no "Rownum" in MySQL.

The BTrees are actually B+Trees, thereby making range scans more efficient.

InnoDB puts all the BTrees for a given table (one for data+PK, one for each secondary index) into some tablespace. The tablespace is either the generic one (ibdata1 file), a table-specific one (the file tablename.ibd), or (in newer versions) a "tablespace" file that can contain several tables.

Caveat: What I have described applies to MySQL's InnoDB, and is probably not correct for any other engine.

I don't know of a good tool for inspecting InnoDB's BTrees without getting into gory details. For a Percona version:

SELECT  i.INDEX_NAME as Index_Name,
                IF(ROWS_READ IS NULL, 'Unused',
                    IF(ROWS_READ > 2e9, 'Overflow', ROWS_READ)) as Rows_Read
            FROM (
                SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
                    FROM information_schema.STATISTICS
                 ) i
            LEFT JOIN information_schema.INDEX_STATISTICS s
                     ON i.TABLE_SCHEMA = s.TABLE_SCHEMA
                    AND i.TABLE_NAME = s.TABLE_NAME
                    AND i.INDEX_NAME = s.INDEX_NAME
            WHERE i.TABLE_SCHEMA = ?
              AND i.TABLE_NAME = ?
            ORDER BY IF(i.INDEX_NAME = 'PRIMARY', 0, 1)

For MySQL (Oracle):

SELECT  last_update,
                n_rows,
                'Data & PK' AS 'Type',
                clustered_index_size * 16384 AS Bytes,
                ROUND(clustered_index_size * 16384 / n_rows) AS 'Bytes/row',
                clustered_index_size AS Pages,
                ROUND(n_rows / clustered_index_size) AS 'Rows/page'
        FROM mysql.innodb_table_stats
        WHERE ( ( database_name = ? AND table_name = ? )
          OR    ( database_name = LOWER(?) AND table_name = LOWER(?) 
    UNION
        SELECT  last_update,
                n_rows,
                'Secondary Indexes' AS 'BTrees',
                sum_of_other_index_sizes * 16384 AS Bytes,
                ROUND(sum_of_other_index_sizes * 16384 / n_rows) AS 'Bytes/row',
                sum_of_other_index_sizes AS Pages,
                ROUND(n_rows / sum_of_other_index_sizes) AS 'Rows/page'
        FROM mysql.innodb_table_stats
        WHERE ( ( database_name = ? AND table_name = ? )
          OR    ( database_name = LOWER(?) AND table_name = LOWER(?) 
          AND sum_of_other_index_sizes > 0