时间戳

首页 > 解决方案 > MySQL 中的聚集索引

mysql - MySQL 中的聚集索引

问题描述

我正在学习数据库中的索引。根据 GeeksforGeeks ( https://www.geeksforgeeks.org/indexing-in-databases-set-1/ )，为聚集索引创建索引文件。有一个图表显示具有学期 1-8 的索引文件。

但是在阅读https://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key时，提到了

如果一个表有聚集索引，基本上意味着索引就是这个表

我想知道在聚集索引和非聚集索引中是否生成索引文件？

另外，我们可以看到表上的索引文件，即它存储的内容吗？

标签： mysqldatabaseindexingclustered-index

解决方案

MySQL 本质上只有一种索引方法：BTree。（是的，还有空间和全文，但这是另一个讨论。）

一旦你了解了 BTree 的工作原理（参见 Wikipedia），我们就可以讨论进入 InnoDB 中叶节点的内容。

情况 1：“数据”BTree 包含所有列，并根据PRIMARY KEY. 在 MySQL 中，PK根据定义是“UNIQUE”和“Clustered”。（其他供应商有其他选择。）

情况 2：“辅助”INDEX存储在单独的 BTree 中。叶节点中有 (1) 二级索引中定义的列，以及 PK 列的副本。要完成SELECT使用二级索引，必须先使用索引 BTree 获取 PK，然后通过数据 BTree 获取数据。（如果索引是“覆盖”，则不需要这第二步。）

MySQL 中没有“Rownum”。

BTrees 实际上是 B+Trees，从而使范围扫描更有效。

InnoDB 将给定表的所有 BTree（一个用于数据+PK，一个用于每个二级索引）放入某个表空间。表空间是通用的（ibdata1文件）、特定于表的（文件tablename.ibd）或（在较新版本中）可以包含多个表的“表空间”文件。

警告：我所描述的适用于 MySQL 的 InnoDB，并且可能不适用于任何其他引擎。

我不知道有什么好工具可以在不深入细节的情况下检查 InnoDB 的 BTree。对于 Percona 版本：

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)

对于 MySQL（甲骨文）：

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

推荐阅读