MySQL性能调优（二）：MySQL体系架构（目录和文件）
https://i-blog.csdnimg.cn/direct/e0e02699917a4cccb81a2c173d0b7289.png个人主页：道友老李
接待加入社区：道友老李的学习社区
MySQL性能调优
MySQL 性能调优是一个复杂且多维度的过程，下面从数据库计划、查询优化、配置参数调整、硬件优化几个方面为你先容干系的调优方法。
数据库计划优化
[*]合理计划表结构：确保表结构遵循数据库计划范式，减少数据冗余，同时要根据实际业务需求机动调整，避免太过范式化导致的查询复杂度过高。
[*]选择符合的数据范例：使用符合的数据范例可以减少存储空间，提高查询性能。比方，对于固定长度的字符串使用CHAR，对于可变长度的字符串使用VARCHAR；对于整数范例，根据取值范围选择符合的范例，如TINYINT、SMALLINT等。
[*]创建得当的索引：索引可以加快数据的查找速度，但过多的索引会增加写操作的开销，因此须要根据查询需求创建得当的索引。比方，对于经常用于WHERE子句、JOIN条件和ORDER BY子句的列，可以思量创建索引。
查询优化
[*]避免全表扫描：尽量使用索引来避免全表扫描，比方在WHERE子句中使用索引列进行过滤。
[*]优化子查询：子查询大概会导致性能题目，可以思量使用JOIN来替代子查询。
[*]减少不须要的列：在查询时只选择须要的列，避免使用SELECT *。
配置参数调整
[*]调整内存分配：根据服务器的硬件资源和业务需求，调整innodb_buffer_pool_size、key_buffer_size等参数，以提高缓存命中率。
[*]调整日记参数：根据业务需求调整log_bin、innodb_log_file_size等参数，以平衡数据安全性和性能。
硬件优化
[*]使用高速存储设备：如 SSD 可以明显提高磁盘 I/O 性能。
[*]增加内存：足够的内存可以减少磁盘 I/O，提高查询性能。
2.MySql基础
2.2.MySQL中的目录和文件
2.2.1.bin目录
在MysQL的安装目录下有一个特别特别紧张的bin目录，这个目录下存放着很多可执行文件。
其他体系中的可执行文件与此的类似。这些可执行文件都是与服务器程序和客户端程序干系的。
https://i-blog.csdnimg.cn/img_convert/1c9e5466e809eede34335f5690c89738.png
2.2.1.1.启动MySQL服务器程序
在UNIX体系中用来启动MySOL服务器程序的可执行文件有很多，大多在MySQL安装目录的bin目录下。
mysqld
mysqld这个可执行文件就代表着MySOL服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。但这个下令不常用。
mysqld_safe
mysqld safe是一个启动脚本，它会间接的调用mysqld，而且还顺便启动了另外一个监控进程，这个监控进程在服务器进程挂了的时候，可以帮助重启它。另外,使用mysqld_safe启动服务器程序时，它会将服务器程序的出错信息和其他诊断信息重定向到某个文件中，产生出错日记，这样可以方便我们找出发生错误的原因。
mysql.server
mysql.server也是一个启动脚本，它会间接的调用mysqld_safe，在调用mysql.server时在后边指定start参数就可以启动服务器程序了
就像这样:
mysql.server start
须要注意的是，这个mysql.server文件其实是一个链接文件，它的实际文件是support-files/mysql.server，以是如果在bin目录找不到，到support-files下去找找，而且如果你愿意的话，自行用ln下令在bin创建一个链接。
另外，我们还可以使用mysql.server下令来关闭正在运行的服务器程序，只要把start参数换成stop就好了:
mysql.server stop
mysqld_multi
其实我们一台计算机上也可以运行多个服务器实例，也就是运行多个NySQL服务器进程。mysql_multi可执行文件可以对每一个服务器进程的启动或制止进行监控。
2.2.1.2.客户端程序
在我们乐成启动MysTL服务器程序后，就可以接着启动客户端程序来连接到这个服务器喽， bin目录下有很多客户端程序，比方说mysqladmin、mysqldump、mysqlcheck等等。
我们常用的是可执行文件mysql，通过这个可执行文件可以让我们和服务器程序进程交互，也就是发送请求，吸收服务器的处理结果。
mysqladmin执行管理操作的工具，检查服务器配置、当前运行状态，创建、删除数据库、设置新密码。
mysqldump数据库逻辑备份程序。
mysqlbackup备份数据表、整个数据库、所有数据库，一般来说mysqldump备份、mysql还原。
2.2.2.启动选项和参数
2.2.2.1.配置参数文件
当MySQL实例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件地点位置以及指定某些初始化参数，这些参数通常定义了某种内存结构有多大等。在默认情况下，MySQL实例会按照-定的次序在指定的位置进行读取，用户只需通过下令mysql --help|grep my.cnf来寻找即可。
https://i-blog.csdnimg.cn/img_convert/407a5eb95f62395aea904fd7d5d7ff9b.png
当然，也可以在启动MySQL时，指定配置文件（非yum安装）：
https://i-blog.csdnimg.cn/img_convert/43463efc571a44b9012e4e5762e25d1c.png
这个时候，就会以启动时指定的配置文件为准。
https://i-blog.csdnimg.cn/img_convert/29b1924955c530ecc97e094b555ceb86.png
MySQL数据库参数文件的作用和Oracle数据库的参数文件极其类似，不同的是，Oracle实例在启动时若找不到参数文件，是不能进行装载(mount）操作的。MySQL稍微有所不同，MySQL实例可以不须要参数文件，这时所有的参数值取决于编译MySQL时指定的默认值和源代码中指定参数的默认值。
MySQL数据库的参数文件是以文本方式进行存储的。可以直接通过一些常用的文本编辑软件进行参数的修改。
2.2.2.2.参数的检察和修改
可以通过下令show variables检察数据库中的所有参数，也可以通过LIKE来过滤参数名，前面查找数据库引擎时已经展示过了。从 MySQL 5.1版本开始，还可以通过information_schema架构下的GLOBAL_VARIABLES视图来进行查找，推荐使用下令
show variables，使用更为简单，且各版本的 MySQL数据库都支持。
https://i-blog.csdnimg.cn/img_convert/2b2ee005612cb3030c5b2e7ddbada9fa.png
参数的详细寄义可以参考MySQL官方手册：
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
但是课程中碰到的参数会进行讲解。
MySQL数据库中的参数可以分为两类:动态(dynamic）参数和静态(static）参数。同时从作用范围又可以分为全局变量和会话变量。
动态参数意味着可以在 MySQL实例运行中进行更改，静态参数说明在整个实例生命周期内都不得进行更改，就似乎是只读(read only)的。
全局变量（GLOBAL）影响服务器的团体操作。
会话变量（SESSION/LOCAL）影响某个客户端连接的操作。
举个例子，用default_storage_engine来说明，在服务器启动时会初始化一个名为default_storage_engine，作用范围为GLOBAL的体系变量。之后每当有一个客户端连接到该服务器时，服务器都会单独为该客户端分配一个名为default_storage_engine，作用范围为SESSION的体系变量，该作用范围为SESSION的体系变量值按照当前作用范围为GLOBAL的同名体系变量值进行初始化。
可以通过SET下令对动态的参数值进行修改。
https://i-blog.csdnimg.cn/img_convert/2bc7dc3c87f00a6755ae82bf296bdf2b.png
SET的语法如下：
set system_var_name= expr
或者
set [@@global. || @@session.] system_var_name= expr
比如：
set read_buffer_size=524288;
set session read_buffer_size=524288;
set @@global.read_buffer_size=524288;
MySQL所有动态变量的可修改范围，可以参考MySQL官方手册的 Dynamic System Variables 的干系内容：
https://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html
对于静态变量，若对其进行修改，会得到类似如下错误:https://i-blog.csdnimg.cn/img_convert/9fcc397f1133cbad840191453e17b6a9.png
2.2.3.数据目录
我们知道像InnoDB、MyIASM这样的存储引擎都是把表存储在磁盘上的，而操作体系用来管理磁盘的那个东东又被称为文件体系，以是用专业一点的话来表述就是:像InnoDB、MyISAM这样的存储引擎都是把表存储在文件体系上的。当我们想读取数据的时候，这些存储引擎会从文件体系中把数据读出来返回给我们，当我们想写入数据的时候，这些存储引擎会把这些数据又写回文件体系。
2.2.3.1.确定MySQL中的数据目录
那说了半天，到底MySQL把数据都存到哪个路径下呢?其实数据目录对应着一个体系变量datadir，我们在使用客户端与服务器创建连接之后检察这个体系变量的值就可以了：
show variables like 'datadir';
https://i-blog.csdnimg.cn/img_convert/045a579c31e3ccd3dddad094d0e6c619.png
当然这个目录可以通过配置文件进行修改，由我们自己进行指定。
2.2.3.2.数据目录中放些什么？
MySOL在运行过程中都会产生哪些数据呢?当然会包含我们创建的数据库、表、视图和触发器等用户数据，除了这些用户数据，为了程序更好的运行，MySQL也会创建一些其他的额外数据
2.2.3.2.1.数据库在文件体系中的体现
create database lijin charset=utf8;
https://i-blog.csdnimg.cn/img_convert/6d62c2bc202104de6323cb5f9a608eeb.pngcd
每当我们使用CREATE DATABASE语句创建一个数据库的时候，在文件体系上实际发生了什么呢?其实很简单，每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹，我们每当我们新建一个数据库时，MySQL会帮我们做这两件事儿:
1．在数据目录下创建一个和数据库名同名的子目录（或者说是文件夹)。
2．在该与数据库名同名的子目录下创建一个名为db.opt的文件，这个文件中包含了该数据库的各种属性，比方说该数据库的字符集和比较规则是个啥。
https://i-blog.csdnimg.cn/img_convert/d7bf6b35470dde0ca0654d695650e4dd.png
比方说我们检察一下在我的计算机上当前有哪些数据库︰
https://i-blog.csdnimg.cn/img_convert/aa78078ac116c76e0648e1dc7727a102.png
可以看到在当前有5个数据库，此中mysqladv数据库是我们自定义的，其余4个数据库是属于MySQL自带的体系数据库。我们再看一下数据目录下的内容:
https://i-blog.csdnimg.cn/img_convert/8a6a8be5ba9dc010b969ad95e0c93c66.png
https://i-blog.csdnimg.cn/img_convert/60fd3cd09cb798e219361e1e6c55d47a.png
当然这个数据目录下的文件和子目录比较多，但是如果细致看的话，除了information_schema这个体系数据库外，其他的数据库在数居目录下都有对应的子目录。这个information_schema比较特别，我们后面再讲它的作用。
2.2.3.2.2.表在文件体系中的体现
我们的数据其实都是以记录的形式插入到表中的，每个表的信息其实可以分为两种:
1.表结构的定义
2．表中的数据
表结构就是该表的名称是啥，表里边有多少列，每个列的数据范例是啥，有啥束缚条件和索引，用的是啥字符集和比较规则各种信息，这些信息都体现在了我们的建表语句中了。为了生存这些信息，InnoDB和MyIASM这两种存储引擎都在数据目录下对应的数据库子目录下创建了一个专门用于形貌表结构的文件，文件名是这样:表名.frm
比方说我们在lijin数据库下创建一个名为test的表:
https://i-blog.csdnimg.cn/img_convert/9a9b75a04c565950ecf14dcc2ffca807.png
那在数据库mysqladv对应的子目录下就会创建一个名为test.frm的用于形貌表结构的文件。这个后缀名为.fm是以二进制格式存储的。
https://i-blog.csdnimg.cn/img_convert/236676efda8a77a477a22e07f156c85f.pngcd
那表中的数据存到什么文件中了呢?在这个题目上，不同的存储引擎就产生了有所不同，下边我们分别看一下InnoDB和MyISAM是用什么文件来生存表中数据的。
2.2.3.2.3.lnnoDB是怎样存储表数据的
InnoDB的数据会放在一个表空间或者文件空间（英文名: table space或者file space)的概念，这个表空间是一个抽象的概念，它可以对应文件体系上一个或多个真实文件〈不同表空间对应的文件数目大概不同)。每一个表空间可以被划分为很多很多很多个页，我们的表数据就存放在某个表空间下的某些页里。表空间有好几种范例。
体系表空间(system tablespace)
这个所谓的体系表空间可以对应文件体系上一个或多个实际的文件，默认情况下，InnoDB会在数据目录下创建一个名为ibdata1(在你的数据目录下找找看有木有)、大小为12M的文件，这个文件就是对应的系纳表空间在文件体系上的体现。
https://i-blog.csdnimg.cn/img_convert/cefb72919bd63150c3d701d92fac4cea.png
这个文件是所谓的自扩展文件，也就是当不敷用的时候它会自己增加文件大小，当然，如果你想让体系表空间对应文件体系上多个实际文件，或者仅仅觉得原来的ibdata1这个文件名难听，那可以在MySQL启动时配置对应的文件路径以及它们的大小，我们也可以把体系表空间对应的文件路径不配置到数据目录下，以致可以配置到单独的磁盘分区上。
须要注意的一点是，在一个MySQL服务器中，体系表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中，我们表中的数据都会被默认存储到这个体系表空间。
独立表空间(file-per-table tablespace)
在MySQL5.6.6以及之后的版本中，InnoB并不会默认的把各个表的数据存储到体系表空间中，而是为每一个表创建一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个体现该独立表空间的文件，文件名和表名相同，只不外添加了一个.ibd的扩展名而已，以是完整的文件名称长这样:表名.ibd。
比方说假如我们使用了独立表空间去存储lijin数据库下的test表的话，那么在该表地点数据库对应的lijin目录下会为test表创建这两个文件:
test.frm和test.ibd
https://i-blog.csdnimg.cn/img_convert/65be1a651b60479359a71ab5e667a495.png
此中test.ibd文件就用来存储test表中的数据和索引。当然我们也可以自己指定使用体系表空间还是独立表空间来存储数据，这个功能由启动参数
innodb_file_per_table控制，比如说我们想刻意将表数据都存储到体系表空间时，可以在启动MySQL服务器的时候这样配置:
innodb_file_per_table=0
当imodb_file_per table的值为0时，代表使用体系表空间;当innodb_file_per table的值为1时，代表使用独立表空间。不外inmodb_file_per_table参数只对新建的表起作用，对于已经分配了表空间的表并不起作用。
其他范例的表空间
随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同范例的表空间，比如通用表空间(general tablespace) ,undo表空间(undotablespace)、临时表空间〈temporary tablespace)等。
2.2.3.2.4.MyISAM是怎样存储表数据的
在MyISAM中的数据和索引是分开存放的。以是在文件体系中也是使用不同的文件来存储数据文件和索引文件。而且和InnoDB不同的是，MyISA并没有什么所谓的表空间一说，表数据都存放到对应的数据库子目录下。
https://i-blog.csdnimg.cn/img_convert/d0e22c11becf83d4a0dfac94ba41aa32.png
test_myisam表使用MyISAM存储引擎的话，那么在它地点数据库对应的lijin目录下会为myisam表创建三个文件:
https://i-blog.csdnimg.cn/img_convert/9b871c7dda6d8b50f36d1370b4692ccc.png
此中test_myisam.MYD代表表的数据文件，也就是我们插入的用户记录; test_myisam.MYI代表表的索引文件，我们为该表创建的索引都会放到这个文件中。
2.2.3.3.日记文件
在服务器运行过程中，会产生各种各样的日记，比如常规的查询日记、错误日记、二进制日记、redo日记、Undo日记等等，日记文件记录了影响MySQL数据库的各种范例运动。
常见的日记文件有：错误日记（error log）、慢查询日记（slow query log）、查询日记（query log）、二进制文件（bin log）。
错误日记
错误日记文件对MySQL的启动、运行、关闭过程进行了记录。碰到题目时应该首先检察该文件以便定位题目。该文件不但记录了所有的错误信息，也记录一些告诫信息或正确的信息
用户可以通过下面下令来检察错误日记文件的位置：
show variables like 'log_error'\G;
https://i-blog.csdnimg.cn/img_convert/77f4268c4e614011d18ecd507c5ca69d.png
当MySQL不能正常启动时，第一个必须查找的文件应该就是错误日记文件，该文件记录了错误信息。
慢查询日记
慢查询日记可以帮助定位大概存在题目的SQL语句，从而进行SQL语句层面的优化。
我们已经知道慢查询日记可以帮助定位大概存在题目的SQL语句，从而进行SQL语句层面的优化。但是默认值为关闭的，须要我们手动开启。
show VARIABLES like 'slow_query_log';
https://i-blog.csdnimg.cn/img_convert/48c641ad21bd46eded47f3782d34193a.png
set GLOBAL slow_query_log=1;
开启1，关闭0
但是多慢算慢？MySQL中可以设定一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日记中。long_query_time参数就是这个阈值。默认值为10，代表10秒。
show VARIABLES like '%long_query_time%';
当然也可以设置
set global long_query_time=0;
默认10秒，这里为了演示方便设置为0
同时对于运行的SQL语句没有使用索引，则MySQL数据库也可以将这条SQL语句记录到慢查询日记文件，控制参数是：
show VARIABLES like '%log_queries_not_using_indexes%';
https://i-blog.csdnimg.cn/img_convert/5765fa31a47afccbb44648eb6b58e934.png
开启1，关闭0（默认）
https://i-blog.csdnimg.cn/img_convert/5b8ed2bb12ccec108698cf1bfcecdcb6.png
show VARIABLES like '%slow_query_log_file%';
https://i-blog.csdnimg.cn/img_convert/503390ae20f709dc22b69630b3010c36.png
https://i-blog.csdnimg.cn/img_convert/ae1b5b03ffc3ff50a33a04c479cab0df.png
查询日记
检察当前的通用日记文件是否开启
show variables like '%general%'
https://i-blog.csdnimg.cn/img_convert/5e55aff206cf0fb88ecf8c4797c62889.png
开启通⽤⽇志查询： set global general_log = on;
关闭通⽤⽇志查询：set global general_log = off;
https://i-blog.csdnimg.cn/img_convert/e00fb0c44611751179fbe1a098c66069.pngsele
查询日记记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。
默认文件名：主机名.log
https://i-blog.csdnimg.cn/img_convert/62b2485316fe6632a037664857ad4164.png
二进制日记（binlog）
二进制日记记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执⾏的消耗的时间，MySQL的⼆进制⽇志是事件安全型的
二进制日记的几种作用：
规复（recovery）：某些数据的规复须要二进制日记，比方，在一个数据库全备文件规复后，用户可以通过二进制文件进行point-in-time的规复
复制（replication）：其原理与规复类似，通过复制和执行二进制日记使一台远程的MySQL数据库（一般称为slave或standby）与一台MySQL数据库（一般称为master或primary）进行及时同步
审计（audit）：用户可以通过二进制日记中的信息来进行审计，判断是否有对数据库进行注入的攻击
log-bin参数该参数用来控制是否开启二进制日记，默以为关闭
如果想要开启二进制日记的功能，可以在MySQL的配置文件中指定如下的格式：
“name”为二进制日记文件的名称
如果不提供name，那么数据库会使用默认的日记文件名（文件名为主机名，后缀名为二进制日记的序列号），且文件生存在数据库地点的目录（datadir下）
–启用/设置二进制日记文件(name可省略)
log-bin=name;
配置以后，就会在数据目录下产生类似于：
https://i-blog.csdnimg.cn/img_convert/6013e514763ec4ec7d3ebb26782a38a2.png
bin_log.00001即为二进制日记文件；bin_log.index为二进制的索引文件，用来存储过往产生的二进制日记序号，通常情况下，不建议手动修改这个文件。
二进制日记文件在默认情况下并没有启动，须要手动指定参数来启动。开启这个选项会对MySQL的性能造成影响，但是性能损失非常有限。根据MySQL官方手册中的测试指明，开启二进制日记会使性能下降1%。
检察binlog是否开启
show variables like 'log_bin';
https://i-blog.csdnimg.cn/img_convert/69f1a197064e543ed6ad61e7ef66dade.png
mysql安装目录下修改my.cnf
log_bin=mysql-bin
binlog-format=ROW
server-id=1
expire_logs_days =30
https://i-blog.csdnimg.cn/img_convert/faf71fb3b4fc75a776f94e6b2a8fab94.png
https://i-blog.csdnimg.cn/img_convert/b9f34268342f72240492775cb96e0a4d.png
2.2.3.3.其他的数据文件
除了我们上边说的这些用户自己存储的数据以外，数据文件下还包括为了更好运行程序的一些额外文件，当然这些文件不一定会放在数据目录下，而且可以在配置文件或者启动时另外指定存放目录。
主要包括这几种范例的文件:
·服务器进程文件。
我们知道每运行一个MySQL服务器程序，都意味着启动一个进程。MySQL服务器会把自己的进程ID写入到一个pid文件中。
socket文件
当用UNIX域套接字方式进行连接时须要的文件。
https://i-blog.csdnimg.cn/img_convert/eb99a39c9c0f49c54a937af53bb37764.png
·默认/自动生成的SSL和RSA证书和密钥文件。
https://i-blog.csdnimg.cn/img_convert/ffc874cba7a7d87d4352ae827b669e04.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]