mysql引擎层存储层_什么影响了mysql的性能-存储引擎层

判断独立表空间和共享表空间的方法

1、在配置文件my.cnf里面设置innodb_file_per_table=0，重启MySQL服务，创建表common_tablespace，你会在数据目录看到只有common_tablespace.frm文件。

如果表的存储引擎是InnoDB，而且表空间(tablespace)是共享表空间的话，那么数据库对应目录下面是没有"表名.ibd"文件的。独立表空间的表的话，则有"表名.ibd"文件。只是这个方法很笨，对于生产环境，大量的表通过这种方式判别，确实不是一个好方法。

2、MySQL 5.6 INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES 关于这个系统表提供了有关表格的格式和存储特性，包括行格式，压缩页面大小位级别的信息(如适用)，INNODB的表空间信息。

# 共享表空间

SELECT TABLE_SCHEMA

,TABLE_NAME

,TABLE_TYPE

,N'共享表空间' AS TABLE_SPACE

,ENGINE

,VERSION

,TABLE_ROWS

,AVG_ROW_LENGTH

,CREATE_TIME

,UPDATE_TIME

FROM INFORMATION_SCHEMA.TABLES T

LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME

WHERE I.NAME IS NULL AND T.TABLE_SCHEMA='MyDB' AND T.ENGINE='InnoDB';

#独立表空间

SELECT TABLE_SCHEMA

,TABLE_NAME

,TABLE_TYPE

,N'独立表空间' AS TABLE_SPACE

,ENGINE

,VERSION

,TABLE_ROWS

,AVG_ROW_LENGTH

,CREATE_TIME

,UPDATE_TIME

FROM INFORMATION_SCHEMA.TABLES T

INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME

WHERE T.TABLE_SCHEMA='MyDB' AND T.ENGINE='InnoDB';

3、如果是MySQL 5.7的话， 比MySQL 5.6有多了一种方法，MySQL 5.7 的INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中多了SPACE_TYPE字段，不过其值全部为Single，而INFORMATION_SCHEMA.INNODB_SYS_TABLES中也多了字段SPACE_TYPE， 其值有Single与System 分别表示单独表空间和共享表空间。

#独立表空间

SELECT * FROM

INFORMATION_SCHEMA.INNODB_SYS_TABLES

WHERE SPACE_TYPE='Single';

#共享表空间

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES

WHERE SPACE_TYPE='System';

4、如果是MySLQ 8.0的话，还多了一种方法， 那就是通过INFORMATION_SCHEMA.INNODB_TABLES来判断，这个新增的系统表可以通过SPACE_TYPE字段来区分共享表空间与独立表空间

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE SPACE_TYPE=’Single’;

使用表空间进行数据存储

innodb_file_per_table

ON: 独立表空间： tablename. ibd

OFF: 系统表空间：ibdataX

系统表空间和独立表空间要如何选择

比较:

系统表空间无法简单的收缩文件大小

系统表空间会产生IO瓶颈

独立表空间可以收缩文件大小

独立表空间可以同时向多个文件刷新数据

表转移的步骤:

使用mysqldump导出所有数据库表数据

停止Mysql服务，修改参数，并删除Innodb相关文件

重启Mysql服务，重建Innodb系统表空间

Innodb存储引擎的特性

Innodb是一种事务性存储引擎

完全支持事务的ACID特性

Redo Log 和 Undo Log

Innodb支持行级锁

行级锁可以最大程度的支持并发

行级锁是由存储引擎层实现的

什么是锁

锁对主要作用是管理共享资源的并发访问

锁用于实现事务的隔离性

打个比方: 邮件系统，当两个用户向同一地址发送邮件时，这时候只会有一个用户发送成功，另外一个会暂时阻塞，这就是锁也就是事务特性中的隔离性，避免了损坏数据。

锁的类型

共享锁(也称为读锁,多个线程可以同时进行)

独占锁(也称写锁,只能单线程访问是互斥)

锁的粒度

相互之间不存在阻塞，锁的单元越小支持并发越高，比如innodb支持行级锁，这样当需要写入数据时只会对单行操作而不影响其他数据的并行操作，粒度主要就是对操作数据的锁的单元。

表级锁(服务器层)

行级锁

阻塞和死锁

什么是阻塞(一个连接在等待另一个连接资源断开)

什么是死锁(两个或两个以上相互占用对方占用的资源)

Innodb状态检查

show engine innodb status

适用场景

Innodb适合于大多数OLTP应用

存储引擎之Memory

文件系统储存特点

也称为HEAP存储引擎，所以数据保存在内存中

当服务器重启后数据会丢失表结构不会丢失(数据索引保存在内存中)

功能特点

支持HASH索引和BTree索引

所有字段为固定长度varchar(10)=char(10)

不支持BLOG和TEXT等打字段(浪费很大资源空间)

Memory存储引擎使用表级锁

最大大小是由max_heap_table_size参数决定

使用场景(数据易丢失，所以要求数据可再生)

用于查找或者是映射表，例如邮编和地区的对应

用于保存数据分析中产生的中间表

用于缓存周期性聚合数据的结果表