mysql物理文件与性能查询
- 物理文件
数据库文件： - '.frm’文件
- '.MYD’文件
- '.MYI’文件
- ‘.ibd’文件和’.ibdata’文件
日志文件主要包括： - 错误日志(Error Log)
-
- 二进制日志(Binary Log)
-
- 事务日志(InnoDB redo Log & undo Log)
-
- 慢查询日志(Slow Query Log)
-
- 查询日志(Query Log)
1.1 数据库文件
MySQL数据库会在data目录下面简历一个以数据库为名的文件夹，用来存储数据库中的表文件数据。不同的数据库引擎，每个表的扩展名也不一样 ，例如： MyISAM用“.MYD”作为扩展名，Innodb用“.ibd”，Archive 用“.arc”，CSV 用“.csv
".FRM"文件
无论是那种存储引擎，创建表之后就一定会生成一个以表明命名的’.frm’文件。frm文件主要存放与表相关的数据信息，主要包括表结构的定义信 息。当数据库崩 溃时，用户可以通过frm文件来恢复数据表结构。
".MYD"文件
“.MYD”文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个“.MYD”文件与之对应，同样存放于所属数据库的文件夹 下， 和“.frm”文件在一起。
".MYI"文件
“.MYI”文件也是专属于MyISAM存储引擎的，主要存放MyISAM表的索引相关信息。对于MyISAM存储来说，可以被cache 的内容主要就是来源 于“.MYI”文件中。 每一个MyISAM表对应一个“.MYI”文件，存放于位置和“.frm”以及“.MYD”一样。
这两种文件都是存放Innodb数据的文件，之所以有两种文件来存放Innodb的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决 定是使用共享 表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件 ，文件存放在和MyISAM数据相 同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata文件来存放，所有表共同使用一个（或者多个， 可自行配置）ibdata文件。
ibdata文件可以通过innodb_data_home_dir（数据存放目录）和innodb_data_file_path(配置每个文件的名称)两个参数配置组成
innodb_data_file_path中可以一次配置多个ibdata文件 #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend 配置方 式
共享表空间以及独占表空间都是针对数据的存储方式而言的。
共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中。
独占表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。其中这个文件包括了 单独一个表的数据 内容以及索引内容。
两者对比
（1.）共享表空间：
优点： 可以放表空间分成多个文件存放到各个磁盘上。数据和文件放在一起方便管理。
缺点： 所有的数据和索引存放到一个文件中，多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空 隙，特别是对 于统计分 析，日值系统这类应用最不适合用共享表空间。
（2.）独立表空间：
优点:
- 查询日志(Query Log)
- 每个表都有自已独立的表空间。
-
- 每个表的数据和索引都会存在自已的表空间中。
-
- 可以实现单表在不同的数据库中移动。
-
- 空间可以回收
a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:altertable TableName engine=innodb;回 缩不用的空间。
b) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。 缺点：单表增加过大，如超过100 个G。 相比较之 下，使用独占表空间的效率以及性能会更高一点
共享表空间和独立表空间之间的转换
show variables like “innodb_file_per_table”; ON代表独立表空间管理，OFF代表共享表空间管理；
修改数据库的表空间管理方式 修改innodb_file_per_table的参数值即可，但是修改不能影响之前已经使用过的共享表空间和独立表空间；
innodb_file_per_table=1 为使用独占表空间
innodb_file_per_table=0 为使用共享表空间
1.2 日志文件
1.2.1 错误日志
在mysql数据库中，错误日志功能是默认开启的。默认情况下，错误日志存储在mysql数据库的数据目录中。错误日志文件通常的名称 为hostname.err。其中， hostname表示服务器主机名。
错误日志信息可以自己进行配置的，错误日志所记录的信息是可以通过 log_error 和 log_warnings 来定义的，其中log-error是定义是否启 用错误日志的功能和 错 误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。
默认情况下错误日志大概记录以下几个方面的信息：
- 空间可以回收
- 服务器启动和关闭过程中的信息（未必是错误信息，如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等）、
- 服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的-信息
show global variables like “log_error”;
我们可以根据错误日志查找操作过程的异常情况
1.2.2 二进制文件
二进制日志，也就是我们常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录日志在日志文件中，其中还包 括没调语句 所 执行的时间和消耗的资源，以及相关的事务信息。
默认情况下二进制日志功能是没有开启的，启动可以配置log-bin[=file_name]开启，
show global variables like “%log_bin%”;
作用 - 以二进制形式记录更改数据库的SQL语句（insert，update，delete，create，drop，alter等）。
- 用于MySQL主从复制。
- 增量数据备份及恢复
添加配置在my.cnf或者my.ini
因为bug必须添加server-id数值随意指定
server-id=1
log_bin=mysql-bin
启用该选项数据库性能降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整。有些类似于oracle开启归档模式。
命令
– 查看所有二进制文件信息
show binary logs; – 查看最新二进制文件
show master status; – 刷新日志 flush logs;
– 查看二进制日志信息
语法格式：
SHOW BINLOG EVENTS[IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
– show binlog events用于在二进制日志中显示事件。如果未指定’log_name’，则显示第一个二进制日志。
help show binlog events; --获取帮助信息
show binlog events\G;
show binlog events in ‘mysql-bin.000014’\G;
命令行查看： mysqlbinlog D:\phpstudy\PHPTutorial\MySQL\data\mysql-bin.000002
官网二进制文件恢复数据 https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html
1.2.3 事务日志
查看存储引擎： show engines ;
InnoDB引擎在线Redo日志记录了InnoDB所做的所有物理变更和事务信息。通过Redo日志和Undo信息，InnoDB大大地加强了事务的安全性。InnDB 在线Redo 日志默认存放在data命令下，可通过设置innodb_log_griyo_home_dir选项来更改日志文件存放位置，通 过innodb_log_files_in_group选项来说何止日志的数量
使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据 本身持久到 磁 盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁 头，所以采用事务日志的 方式 相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引 擎都是这样实现的。
1.2.4 慢查询日志
顾名思义，慢查询日志中记录的是执行时间较长的query，也就是我们常说的slow query。
专业一点：慢查询日志是值所有SQL执行的实际超过long_query_time变量的语句和达到min_examined_row_limit条举例的语句。用户可以针对 这部分语句性能调 优。慢查询日志通过设置log-slow_queries[=file_name]选项开启后，将记录日志所在的路劲和名称。MySQL系统默认的慢 查询日志的文件名是show.log，默认目 录是data目录。
– 查看慢查询是否开启
show variables like “%slow_query_log%”;
– 查看慢查询日志的定义：
show global variables like ‘%slow_query_log%’;
– 查看long_query_time设置时间
show global variables like ‘%long%’;
– 查看具体信息
– slow_query_log： off关闭状态 on开启状态
– slow_query_log_file
慢查询日志存放地点
show variables like “%slow%” ;
开启慢查询：
slow_query_log
slow_query_log_file=[file_name] 文件地址
long_query_time=3 – 最大等待时间
查询睡眠时间5秒：
select sleep(5);
可以看到在慢查询日志中记录了这条查询时间多达5秒的sql；
1.2.5 查询日志
查询日志记录MySQL中所有的query，通过"–log[=file_name]"来打开该功能。由于记录了所有的query，包括所有的select，体积比较大，开 启后对性能也有比 较大的影响，所以请大家慎用该功能。一般只用于跟踪某些特殊的sql性能问题才会短暂打开该功能。默认的查询日志文件名为 ：hostname.log
查看查询日志是否开启：show variables like “%general_log%”;
设置开启查询日志：(在mysql配置文件中设置)
general_log=ON
设置查询日志文件
general_log_file=D:/phpstudy/Extensions/MySQL8.0.12/data/query_log.pid
查看数据库表结构：
–新增数据：
insert intouser(username,age,status)values(‘php’,111,1);
insert intouser(username,age,status)values(‘dvv’,111,1);
insert intouser(username,age,status)values(‘pvrbhp’,111,1);
insert intouser(username,age,status)values(‘pbrtbhp’,111,1);
insert intouser(username,age,status)values(‘pbtbhp’,111,1);
–查询数据
select * fromuserwhere id=1;
查看查询日志：
- 存储引擎
2.1 myisam 与 innodb
MyISAM是MySQL的默认数据库引擎（5.5版之前），由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽 然性能极佳，但 却有一个缺点：不支持事务处理（transaction）。不过，在这几年的发展下，MySQL也导入了InnoDB（另一种数据库引擎），以 强化参考完整性与并发违规处 理机制，后来就逐渐取代MyISAM。
InnoDB，是MySQL的数据库引擎之一，为MySQL AB发布binary的标准之一。InnoDB由Innobase Oy公司所开发，2006年五月时由甲骨文公司并购 。与传统的 ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能，类似于PostgreSQL。目前InnoDB采用双 轨制授权，一是GPL授权， 另一是专有软件授权。
2.2 myisam与innodb的区别 - 事务支持 MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响 速度 ，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。MyISAM是非事务安全型的，而InnoDB是事务安全型的，默认开启自动提交，宜合并事务，一同提交，减小数据库多次提交导致的开销，大大提高性 能。
- 存储结构 MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的 扩展 名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空 间文件），InnoDB表的大小只受限于操作系统文件的大小， 一般为2GB。
- 存储空间 MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。 InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
14.可移植性、备份及恢复 MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。 InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
5.事务支持 MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。 InnoDB：提供事务支持事务，外部键等 高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。 - AUTO_INCREMENT MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据 前面几列进 行排序后递增。 InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一 列。
- 表锁差异 MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并 发的 情况下，可以在表的尾部插入新的数据。 InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的 行锁，只是在WHERE的主键是有效的 ，非主键的WHERE都会锁全表的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。简单来说就是, InnoDB支持数据行锁定，而MyISAM不支持行锁定，只支持锁定整个表。 即MyISAM同一个 表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到 ，所以MyISAM不适合于有 大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程 饿死。 - 全文索引 MyISAM：支持(FULLTEXT类型的)全文索引 InnoDB：不支持(FULLTEXT类型的)全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效 果更好。
17.全文索引是指对char、varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须 由使用者分词 后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。
另外，MyIsam索引和数据分离，InnoDB在一起，MyIsam天生非聚簇索引，最多有一个unique的性质，InnoDB的数据文件本身就是主键索引文件， 这样的索引 被称为“聚簇索引” - 表主键 MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。 InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的 主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引 的值。InnoDB的主键范围更大，最大是MyISAM的2倍。
- 表的具体行数 MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。 InnoDB：没有保存表的总行数(只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后 ，myisam和innodb处理的方式都一样。
- CURD操作 MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。 InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该 使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有 大量数据的表，最好使用truncate table这个命令 。
- 外键 MyISAM：不支持 InnoDB：支持
- 查询效率 没有where的count()使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count()时它直接从计数器中读，而InnoDB必须扫描全表 。 所以在InnoDB上执行count()时一般要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB 中primary index是 和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向primary key。所以只是count()的话使 用secondary index扫描更快，而 primary key则主要在扫描索引同时要返回raw data时的作用较大。MyISAM相对简单，所以在效率上要优 于InnoDB，小型应用可以考虑使用MyISAM。
通过上述的分析，基本上可以考虑使用InnoDB来替代MyISAM引擎了，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视图、行级 锁定等等， 在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合 适的表类型，才能最 大的发挥MySQL的性能优势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟 酌。
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
