默认情况下,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修改参数,更改表空间的管理类型
- innodb_file_per_table=1 为使用独占表空间
- innodb_file_per_table=0 为使用共享表空间
当Drop table和turncate table操作自动回收表空间,删除大量数据后可以通过:alter table TableName engine=innodb回缩不用的空间。