MySQL查看索引和数据所占内存

背景

我们在使用数据库的时候有时候需要看一下当前表中数据占用了多少内存，索引占用了多少内存。这里介绍一下如何查询到。

解决

假设我现在要查询的表名叫做trade，可以使用如下SQL语句进行查询

select * from `information_schema`.`TABLES` where `TABLE_NAME` = 'trade';

这句sql可以查询出来当前数据库实例中所有数据库中的trade表的信息。在这个问题中我们需要关注的字段是DATA_LENGTH和INDEX_LENGTH。这里就是我们想要看的数据大小和索引大小，单位是byte。

引用官网对这两个字段的描述，感兴趣的小伙伴可以看下：

• DATA_LENGTH
  For MyISAM, DATA_LENGTH is the length of the data file, in bytes.
  For InnoDB, DATA_LENGTH is the approximate amount of space allocated for the clustered index, in bytes. Specifically, it is the clustered index size, in pages, multiplied by the InnoDB page size.
  Refer to the notes at the end of this section for information regarding other storage engines.
• INDEX_LENGTH
  For MyISAM, INDEX_LENGTH is the length of the index file, in bytes.
  For InnoDB, INDEX_LENGTH is the approximate amount of space allocated for non-clustered indexes, in bytes. Specifically, it is the sum of non-clustered index sizes, in pages, multiplied by the InnoDB page size.
  Refer to the notes at the end of this section for information regarding other storage engines.