mysql 5.6中sql语句统计每个索引的具体情况 http://aadant.com/blog/2014/02/04/how-to-calculate-a-specific-innodb-index-size/ 在 mysql中 ,使用 show table status like '表名'\G;可以看出具体表的情况,比如: Name: t Engine: InnoDB Version: 10 Row_
mysql 5.6中sql语句统计每个索引的具体情况
http://aadant.com/blog/2014/02/04/how-to-calculate-a-specific-innodb-index-size/
在 mysql中 ,使用
show table status like '表名'\G;可以看出具体表的情况,比如:
Name: t
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 4186170
Avg_row_length: 34
Data_length: 143310848
Max_data_length: 0
Index_length: 146030592
Data_free: 6291456
Auto_increment: NULL
Create_time: 2014-02-04 15:40:54
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
其中可以看出Data_length和Index_length,显示的是索引段的总的大小,那么一个表中如果有多个索引,要如何查看某个索引的大小,页呢?可以使用SQL语句完成,但要先分析下表,步骤如下:
CREATE TABLE `t` (
`a` smallint(6) DEFAULT NULL,
`b` smallint(6) DEFAULT NULL,
`c` smallint(6) DEFAULT NULL,
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
ANALYZE table t;
SELECT
sum(stat_value) pages,
index_name,
sum(stat_value) * @@innodb_page_size size
FROM
mysql.innodb_index_stats
WHERE
table_name = 't'
AND database_name = 'test'
AND stat_description = 'Number of pages in the index'
GROUP BY
index_name;
+-------+-----------------+-----------+
| pages | index_name????? | size????? |
+-------+-----------------+-----------+
|? 8747 | GEN_CLUST_INDEX | 143310848 |
|? 4456 | a?????????????? |? 73007104 |
|? 4457 | b?????????????? |? 73023488 |
+-------+-----------------+-----------+
3 rows in set (0.00 sec)
其中用到的是mysql 5.6中的 mysql.innodb_table_stats和mysql.innodb_index_stats表 ,里面有很多有用的统计信息
要注意的是,上面的方法对于分区表也是同样适用的
Statement:The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn