Summary of MySQL Query table data size in linux

Summary of MySQL Query table data size in linux-Linux general technology-Linux technology and application information. for details, refer to the following section. · 1. mysql table data size
1.

2. we know that mysql generally uses table space storage to store data files.
3. when mysql uses the innodb storage engine,
4. mysql uses table store data in two ways: shared tablespace and exclusive tablespace.
5. · shared tablespace: All data in Innodb is stored in a single tablespace, which can be composed of multiple files. a table can exist across multiple files.
6. Therefore, its size limit is no longer a file size limit, but its own limit
7 .-- >Innodb officially displays a maximum of 64 TB tablespaces
8. exclusive tablespace: the data in each table is stored in a single file. at this time, the single table limit is changed to the file system size limit.
9. by default, the maximum size of the MyISAM table created by MySQL is 4 GB.
· II. Explanation of the content displayed in show table stauts \ G;
1. Data_length: 150032 --- >Table data size
2. Index_length: 183107584 --- >Table index size
3. Data_free: 25238175744 --- >Tablespace size
1. data_Free: if it is a shared tablespace, data_free is the size of the shared tablespace rather than the data size.
2. if it is an exclusive tablespace, it is the remaining space of the table.
3. if the table is partitioned, data_free is an approximate value rather than an exact value.
4. select sum (data_free) from information_schema.partitions where table_schema = 'DB _ name' and table_name = 'Tab _ name ';
5. query the size of all databases
6. 1: use information_schema;
7. select concat (round (sum (data_length/1024/1024), 2), 'mb') as data from tables;
8. 2: select concat (round (sum (data_length/1024/1024), 2), 'mb') as data from tables where table_schema = 'database _ name ';
: Query the size of a specified table
10. select concat (round (sum (data_length/1024/1024), 2), 'mb') as data from tables where table_schema = 'database _ name' and table_name = 'Table _ name ';
· III. One way to judge mysql I/0 performance (Network collection for reference)
1. show global status like 'innodb _ dblwr % '\ G
2. if innodb_dblwr_pages_writen/innodb_dblwr_writes is much less than 64: 1, the disk write pressure is low.
3. show engine innodb status \ G to view the buffer pool method.
4. select table_name, data_length + index_length, table_rows from tables where table_schema = 'database _ name' and table_name = 'Table _ name ';


Http://www.magedu.com/