MySQL之表空间

  • 查看数据库的索引空间大小
  • 查看数据库中各表的使用汇总信息
  • innodb_buffer_pool_size
  • innodb_buffer_pool_instances
  • MySQL表空间

默认情况下，InnoDB包含一个系统表空间，由一系列文件组成，当数据需要更多空间时动态扩展文件，建议在创建文件时将其扩展到最大，避免动态扩展而影响数据的性能。

序号

内容

1

InnoDB数据字典

2

DoubleWrite缓冲区

3

改变缓冲区

4

撤消日志

查看数据库的索引空间大小

mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 6), ' GB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'mybase';
+------------------+
| Total Index Size |
+------------------+
| 0.042267 GB      |
+------------------+
1 row in set

查看数据库中各表的使用汇总信息

表名、表行数、数据空间大小、索引空间大小和总大小

mysql> SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',
table_rows AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024),6),' MB') AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024),6),' MB') AS 'Index Size',
CONCAT(ROUND((data_length+index_length)/(1024*1024),6),' MB') AS'Total Size'
FROM information_schema.TABLES
WHERE table_schema LIKE 'mysql';
+---------------------------------+----------------+-------------+-------------+-------------+
| Table Name                      | Number of Rows | Data Size   | Index Size  | Total Size  |
+---------------------------------+----------------+-------------+-------------+-------------+
| mysql.columns_priv              |              0 | 0.000000 MB | 0.003906 MB | 0.003906 MB |
| mysql.db                        |              2 | 0.000839 MB | 0.004883 MB | 0.005722 MB |
| mysql.event                     |              0 | 0.000000 MB | 0.001953 MB | 0.001953 MB |
| mysql.func                      |              0 | 0.000000 MB | 0.000977 MB | 0.000977 MB |
| mysql.general_log               |              2 | 0.000000 MB | 0.000000 MB | 0.000000 MB |
| mysql.help_category             |             43 | 0.001171 MB | 0.004883 MB | 0.006054 MB |
| mysql.help_keyword              |            825 | 0.154996 MB | 0.028320 MB | 0.183316 MB |
| mysql.help_relation             |           1658 | 0.014231 MB | 0.025391 MB | 0.039621 MB |
| mysql.help_topic                |            603 | 0.590572 MB | 0.020508 MB | 0.611080 MB |
| mysql.ndb_binlog_index          |              0 | 0.000000 MB | 0.000977 MB | 0.000977 MB |
| mysql.plugin                    |              0 | 0.000000 MB | 0.000977 MB | 0.000977 MB |
| mysql.proc                      |              1 | 0.001614 MB | 0.003906 MB | 0.005520 MB |
| mysql.procs_priv                |              0 | 0.000000 MB | 0.003906 MB | 0.003906 MB |
| mysql.proxies_priv              |              1 | 0.000661 MB | 0.004883 MB | 0.005544 MB |
| mysql.servers                   |              0 | 0.000000 MB | 0.000977 MB | 0.000977 MB |
| mysql.slow_log                  |              2 | 0.000000 MB | 0.000000 MB | 0.000000 MB |
| mysql.tables_priv               |              0 | 0.000000 MB | 0.003906 MB | 0.003906 MB |
| mysql.time_zone                 |              0 | 0.000000 MB | 0.000977 MB | 0.000977 MB |
| mysql.time_zone_leap_second     |              0 | 0.000000 MB | 0.000977 MB | 0.000977 MB |
| mysql.time_zone_name            |              0 | 0.000000 MB | 0.000977 MB | 0.000977 MB |
| mysql.time_zone_transition      |              0 | 0.000000 MB | 0.000977 MB | 0.000977 MB |
| mysql.time_zone_transition_type |              0 | 0.000000 MB | 0.000977 MB | 0.000977 MB |
| mysql.user                      |              5 | 0.000450 MB | 0.001953 MB | 0.002403 MB |
+---------------------------------+----------------+-------------+-------------+-------------+
23 rows in set

innodb_buffer_pool_size

默认的设置只有8M，缓存innodb表的索引，数据，插入数据时的缓冲，是InnoDB最重要的设置，对InnoDB性能有决定性的影响。在高并发高I/O时，正确的配置会带来性能提升。在InnoDB存储引擎的数据库服务器上，通常设置60-80%的物理内存。
innodb_data_file_path
指定innodb 共享表空间文件，如果不指定innodb_data_home_dir和innodb_data_file_path那么默认会在data目录下创建ibdata1 作为innodb tablespace。

innodb_data_file_path = ibdata1:1G;ibdata2:2G:autoextend:max:10G
innodb_data_home_dir = "D:/mysql-5.6.49-winx64/data"

innodb_buffer_pool_instances

配置内存缓冲池的数量，对于具有多GB范围的缓冲池的系统，将缓冲池划分为多个缓冲池可以提高并发性，减少对不同线程读取和写入到缓存时页面的争用。

mysql> show variables like '%innodb_data%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+
2 rows in set

MySQL表空间

MySQL有共享表空间和独立表空间，各有优缺点，根据业务类型和需求进行选择，统计和大量删除操作时，使用独立表空间更合理。每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。mysql的共享表空间ibdata1类似于oracle的undo表空间，默认就是自动扩展的，每次扩展8M，可以把mysql的数据和索引放到不同的磁盘下，分散io，进而提高性能，不能如oracle动态添加表空间文件，否则需要重启mysql服务才能生效。通过innodb_file_per_table查看当前数据库的表空间管理类型， ON代表独立表空间管理，OFF代表共享表空间管理；查看单表的表空间管理方式，需要查看每个表是否有单独的数据文件，MySQL单表最大限制就已经扩大到了MyISAM 64PB和Innodb 64TB了，引擎对单表大小限制已经不是有MySQL数据库本身来决定，而是由所在主机的OS上面的文件系统来决定了。

mysql>  show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set

通过在my.ini修改参数，更改表空间的管理类型

  1. innodb_file_per_table=1 为使用独占表空间
  2. innodb_file_per_table=0 为使用共享表空间
    当Drop table和turncate table操作自动回收表空间，删除大量数据后可以通过:alter table TableName engine=innodb回缩不用的空间。