是否有一个公式可以估算InnoDB中的索引大小? [英] Is there a formula to estimate index size in InnoDB?

问题描述

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

How is it possible to calculate index size for specific column types in InnoDB i.e.:

• VARCHAR
• CHAR
• 时间戳
• SMALLINT

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

I've found a formula for MyISAM (http://dev.mysql.com/doc/refman/5.7/en/key-space.html): (key_length+4)/0.67

这对于InnoDB也有效吗?

Does this work for InnoDB as well?

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

I'm trying to estimate the size of a database I'm designing for sizing purposes.

推荐答案

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

In InnoDB, the PRIMARY KEY is embedded with the data, so you can think of it as taking no space.

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

For a secondary key... Take the MyISAM formula, but include the columns of both the secondary key and the PRIMARY KEY. Then multiply by 3. (There is a lot of overhead.) Still, the answer can be off by a factor of 2 in either direction.

请注意，如果您有很多辅助键，则PK的大小会在表+索引的总体空间中产生很大的差异.

Note that if you have a lot of secondary keys, the size of the PK makes a big difference in the overall space for the table+indexes.

示例

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.之后的统计信息基本上是相同的:

Just to prove another point, I tried OPTIMIZE TABLE. The stats after there were essentially the same:

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

这篇关于是否有一个公式可以估算InnoDB中的索引大小?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！