引用连接：https://www.cnblogs.com/duanxz/p/3724120.html
对于innodb存储引擎而言，其数据文件最小的存储单位为页。默认为16KB大小。在页的基础上又分为区，段，和表空间。innodb存储引擎对于空间的申请不是每次以16KB的方式申请，而是以区的方式。一个区的大小为1MB，总共有64个页。这样的目的是提高空间的申请效率。如果数据是按照减值顺序存放的，那么读取这些页，将在一个连续的地址中，这样可以避免磁头的大量的寻址时间。
1.表空间
| 1. 一个逻辑概念，由之前的页，区，段组成，一个表空间可以由多个文件组成。
2. 使用共享表空间存储方式时，Innodb的所有数据保存在一个单独的表空间里面。 3. 而在使用单独表空间存储方式时，每个表的数据以一个单独的文件来存放。 开启独立表空间的参数: innodb_file_per_table =1 |
2.Innodb与myisam表文件比较及其他文件说明
| Innodb独立表空间文件里表结构文件t1.frm .
表的数据文件:t1.ibd(--数据+索引)
[root@lbg test1]# ll t1*
-rw-r----- 1 mysql mysql 8586 Oct 13 01:01
t1.frm
-rw-r----- 1 mysql mysql 98304 Oct 15 21:09 t1.ibd
mysql> show create table test1; -----查看创建表语句. 创建myisam的表test4: CREATE TABLE `test4` ( `id` int(11) , `name` varchar(10) ) ENGINE=myisam [root@lbg test1]# ll test4*
-rw-r----- 1 mysql mysql 8586 Oct 17 23:35 test4.frm
-rw-r----- 1 mysql mysql
0 Oct 17 23:35 test4.MYD
-rw-r----- 1 mysql mysql 1024 Oct 17 23:35 test4.MYI
MyISAM数据文件: test4.frm ---二进制的表结构文件. test4.MYD --myisam的数据data文件. test4.MYI --myisam的索引index文件. 可知Myisam存储引擎的数据和索引是分开存放的. ibdata1 是innodb的系统表空间. ibtmp1是临时表空间. ib_buffer_pool是放数据预热的文件. 文件auto.cnf记录的是数据库server的uuid(唯一标识符.) |
3.配置多个系统表空间的操作
| ibdata1 只能扩大不能缩小,路径:/home/mysql3307/mysql3307/ibdata1
ibdata1是可以配置多个:innodb_data_home_dir设置为空, 原文件信息: innodb_data_home_dir = /home/mysql3306/mysql3306/ innodb_data_file_path = ibdata1:512M:autoextend 修改后信息: innodb_data_home_dir = innodb_data_file_path = /home/mysql3307/mysql3307/ibdata1:512M;/home/mysql3307/mysql3307/ibdata2:512M:autoextend 两个路径之间用分号隔开.只允许最后一个表空间文件进行自动扩展. |
2.段
| 1. 每个用户至少有两个段
2. 一个是聚集索引的叶子节点段 3. 非叶子节点段 4. 段根据区的形式组织存储空间 |
3.区（按区分配储存空间）
| 1. 区是innodb存储引擎访问的最小单位
2.区是innodb寻找存储空间的最小单位。 3.一个区有64个连续的页组成，大小为1MB。 |
4.页(据库I/O的基本单位,16KB)
| 1. 就是MySQL的聚簇索引的叶子节点
2. 每个数据页默认是16KB 3. 物理上64个连续的页面可以连成一个区 4. 数据页、Undo页、系统页、事务数据页、插入缓冲位图页等 |
5。统计每个表大小
| SELECT TABLE_NAME,SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS
TOTAL_SIZE
FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' GROUP BY TABLE_NAME; |
6.什么是表碎片及表空间碎片的判断及清除
| 表在不断的使用中，尤其是多DML的环境中，会因为不断的分配，回收空间而产生很多碎片，如果碎片过多，很造成磁盘性能的影响。 1. 一般有较多的DML操作的表，是值得注意的，因为这些数据频繁的表动，需要进行空间的再分配，容易产生大量的碎片 2. 有些数据量很大的表，但是在业务上几乎没有用到delete的那种，即使很大，也可能没啥碎片 3. 有varchar类型的列比较多的需要注意，该数据类型容易造成表空间碎片 4. 表碎片的回收时间很长，不会要经常进行此类操作 5. 表碎片没有可以准确度量的公式 统计表的碎片数字:单位是字节 select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0; 表空间碎片的清除: 1.一般表 alter table TableName engine=innodb; 回收表空间，对于大表就不能直接采用这种方式，因为会造成长时间的锁表。 2.大表 可以先建立新表，然后进行抽取数据，然后修改表名 可以采用新建表转移数据，然后删除旧表的形式，然后再重命名表。 |
7.truncate drop delete的区别
| truncate 删除数据同时立即清除空间
Drop 删除表结构，删除数据，清除空间 Delete 进行标记 记录进行删除 |
8.临时表空间
| 临时表分：外部临时表和内部临时表
外部临时表： 通过CREATE TEMPORARY TABLE 创建的临时表，这种临时表称为外部临时表。这种临时表只对当前用户可见，当前会话结束的时候，该临时表会自动关闭。这种临时表的命名与非临时表可以同名（同名后非临时表将对当前会话不可见，直到临时表被删除）。 内部临时表： 是一种特殊轻量级的临时表，用来进行性能优化。这种临时表会被MySQL自动创建并用来存储某些操作的中间结果。这些操作可能包括在优化阶段或者执行阶段。这种内部表对用户来说是不可见的，但是通过EXPLAIN或者SHOW... STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。 内部临时表有两种类型：一种是HEAP(堆)临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要IO操作。另一种是OnDisk临时表，顾名思义，这种临时表会将数据存储在磁盘上。OnDisk临时表用来处理中间结果比较大的操作。如果HEAP临时表存储的数据大于MAX_HEAP_TABLE_SIZE，HEAP临时表将会被自动转换成OnDisk临时表。OnDisk临时表在5.7中可以通过INTERNAL_TMP_DISK_STORAGE_ENGINE系统变量选择使用MyISAM引擎或者InnoDB引擎。 MySQL在以下几种情况会创建临时表： 1、UNION查询； 2、用到TEMPTABLE算法或者是UNION查询中的视图； 3、ORDER BY和GROUP BY的子句不一样时； 4、表连接中，ORDER BY的列不是驱动表中的； 5、DISTINCT查询并且加上ORDER BY时； 6、SQL中用到SQL_SMALL_RESULT选项时； 7、FROM中的子查询； 8、子查询或者semi-join时创建的表； 在以下几种情况下，会创建磁盘临时表： 1、数据表中包含BLOB/TEXT列； 2、在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列（或者超过 512字节的 二进制类型列，在5.6.15之前只管是否超过512字节）； 3、在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列（对于字符串类型是512个字符，对于二进制类型则是512字节）； 4、执行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令，因为它们的执行结果用到了BLOB列类型。 从5.7.5开始，新增一个系统选项 internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 InnoDB，而在这以前，只能使用 MyISAM。而在5.6.3以后新增的系统选项 default_tmp_storage_engine 是控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型，在以前默认是MEMORY，不要把这二者混淆了。 |