Description:
Index statistics currently uses the total index size in pages, which includes all allocated pages, many of which are empty (very low fill rates are common, I see things in the 60-70% range all the time). while finding the total size of an index in a tablespace is useful and necessary functionality, in the case of index stats it's not the appropriate metric. it should be using the number of used pages, which is useful for estimating the cost of a lookup in pages read (index height), etc - things relevant to the use case of index stats.
The code is already calculating this value:
https://github.com/mysql/mysql-server/blob/5.6/storage/innobase/fsp/fsp0fsp.cc#L2147
but the current method chosen, checking total size, just throws that value away and uses the returned value, which is total reserved pages:
https://github.com/mysql/mysql-server/blob/5.6/storage/innobase/btr/btr0btr.cc#L1216
How to repeat:
look at your indexes! this applies globally. if you want to get a quick shock, make a table with a single large primary key (around the max of 3K), and insert a few hundred rows. you'd expect it to be bad, as a key that large will have a low max branching factor, but if you take a look, index stats will claim it has more internal nodes than leaves. in reality, of course, this is not the case - it will have a bunch of allocated empty pages.
Suggested fix:
we don't want to change functionality for anything else looking at btree data, so we want to leave existing calls in place, but introduce new elseif condition for a new option, constant named what you want, like "BTR_USED_SIZE"
https://github.com/mysql/mysql-server/blob/5.6/storage/innobase/btr/btr0btr.cc#L1211
change the arg here from BTR_TOTAL_SIZE to a newly introduced value for BTR_USED_SIZE
https://github.com/mysql/mysql-server/blob/5.6/storage/innobase/dict/dict0stats.cc#L1910
you should change line 840 as well for non-persistent stats, to maintain similar functionality, consistency is good
