mysql - InnoDB 中是否有估算索引大小的公式？

Question

如何计算 InnoDB 中特定列类型的索引大小，即:

• 可变字符
• 字符
• 时间戳
• 小情报

我找到了 MyISAM ( http://dev.mysql.com/doc/refman/5.7/en/key-space.html ) 的公式:(key_length+4)/0.67

这也适用于 InnoDB 吗？

我正在尝试估算我为调整大小而设计的数据库的大小。

Answer 1

在 InnoDB 中，PRIMARY KEY 嵌入在数据中，因此您可以认为它不占用空间。

对于辅助键...采用 MyISAM 公式，但包括辅助键和PRIMARY KEY 的列。然后乘以 3。(有很多开销。)不过，答案可能在任一方向上相差 2 倍。

请注意，如果您有很多辅助键，则 PK 的大小会对表+索引的整体空间产生很大影响。

示例

SET @db = 'world', @tbl = 'cities';
    SELECT      n_rows AS 'Approx 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 = @db
          AND table_name = @tbl
    UNION
        SELECT  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 = @db
          AND table_name = @tbl
          AND sum_of_other_index_sizes > 0
          ;
-- (Percona has a different way.)

输出:

+-------------+-------------------+-----------+-----------+-------+-----------+
| Approx Rows | Type              | Bytes     | Bytes/row | Pages | Rows/page |
+-------------+-------------------+-----------+-----------+-------+-----------+
|     2637973 | Data & PK         | 179077120 |        68 | 10930 |       241 |
|     2637973 | Secondary Indexes | 232341504 |        88 | 14181 |       186 |
+-------------+-------------------+-----------+-----------+-------+-----------+

该表有两个索引:

PRIMARY KEY(...)  -- 14 bytes
INDEX(state, population)
INDEX(state, city)
  state CHAR(2) CHARACTER SET ascii -- 2 bytes
  population INT UNSIGNED -- 4 bytes
  city  -- AVG(LENGTH(city)) = 1+9.07 bytes

COUNT(*): 2,699,354  (the InnoDB estimate was not too far from this)

First index:  20    bytes * 2.7M rows = 54MB
Second index: 26.07 bytes * 2.7M rows = 70MB
Total:  124MB
Actual: 232MB
Ratio: 1.9x  (note: I skipped the "/0.67")

为了证明另一点，我尝试了OPTIMIZE TABLE。之后的统计数据基本相同:

+-------------+-------------------+-----------+-----------+-------+-----------+
| Approx Rows | Type              | Bytes     | Bytes/row | Pages | Rows/page |
+-------------+-------------------+-----------+-----------+-------+-----------+
|     2685828 | Data & PK         | 179077120 |        67 | 10930 |       246 |
|     2685828 | Secondary Indexes | 232341504 |        87 | 14181 |       189 |
+-------------+-------------------+-----------+-----------+-------+-----------+