作为DBA了解InnoDB的页组织方式是最基础的，在实际工作中，免不了会评估SQL会消耗多少IO，怎么评估呢？

作为InnoDB表和树的高度或者深度有关系。

查看树的高度?

之前研究了半天：

 

 

根据

Scholmi notes that there are two main features determining the depth of a B-tree (or B+-tree):The number of rows in the database. We’ll call that N.The size of the indexed key. Let’s call B the number of key that fit in a B-tree node. (Sometimes B is used to refer to the node size itself, rather than the number of keys it holds, but I hope my choice will make sense directly.)Given these quantities, the depth of a B-tree is logB N, give or take a little. That’s just (log N)/log B. Now we can rephrase Scholmi’s point as noting that small keys means a bigger B, which reduces (log N)/log B. If we cut the key size in half, then the depth of the B-tree goes from (log N)/log B to (log N)/log 2B (twice as many keys fit in the tree nodes), and that’s just (log N)/(1+log B).Let’s put some numbers in there. Say you have a billion rows, and you can currently fit 64 keys in a node. Then the depth of the tree is (log 109)/ log 64 ≈ 30/6 = 5. Now you rebuild the tree with keys half the size and you get log 109 / log 128 ≈ 30/7 = 4.3. Assuming the top 3 levels of the tree are in memory, then you go from 2 disk seeks on average to 1.3 disk seeks on average, for a 35% speedup.

里面算的不对，人肉算也没达到这个高度。也可能是我没有理解作者的意思，没有用对公式。那么根据结合前面的Innodb页结构，如何正确的获取数的高度呢？继续拿这个表举例子：

mysql> show create table sbtest1\G*************************** 1. row ***************************       Table: sbtest1Create Table: CREATE TABLE `sbtest1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `gmt_create` datetime NOT NULL,  `gmt_modified` datetime NOT NULL,  `k` int(11) NOT NULL DEFAULT '0',  `c` varchar(500) NOT NULL DEFAULT '',  `pad` char(60) NOT NULL DEFAULT '',  `is_used` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `k_1` (`k`),  KEY `idx_is_used` (`is_used`),  KEY `idx_gmt_create` (`gmt_create`)) ENGINE=InnoDB AUTO_INCREMENT=69313841 DEFAULT CHARSET=latin11 row in set (0.00 sec)