MySQL 调优 - 图1

体系结构

网络连接层

核心服务层

核心服务层是整个数据库服务器的核心，主要包括了系统管理和控制工具、连接池、SQL 接口、解析器、查询优化器和缓存等部分
系统管理和控制工具：提供数据库系统的管理和控制功能，例如对数据库中的数据进行备份和恢复，保证整个数据库的安全性，提供安全管理，对整个数据库的集群进行协调和管理等
连接池：主要负责存储和管理客户端与数据库的连接信息，连接池里的一个线程负责管理一个客户端到数据库的连接信息
SQL 接口：主要负责接收客户端发送过来的各种SQL命令，并将 SQL 命令发送到其他部分，并接收其他部分返回的结果数据，将结果数据返回给客户端
解析器：主要负责对请求的 SQL 解析成一棵“解析树”，然后根据 MySQL 中的一些规则对“解析树”做进一步的语法验证，确认其是否合法
查询优化器：在 MySQL 中，如果“解析树”通过了解析器的语法检查，此时就会由优化器将其转化（如索引的选择，表的读取顺序等）为执行计划，然后与存储引擎进行交互，通过存储引擎与底层的数据文件进行交互
缓存：MySQL 的缓存是由一系列的小缓存组成的。例如：MySQL 的表缓存，记录缓存，MySQL 中的权限缓存，引擎缓存，Key 缓存等等，如果查询的结果能够命中缓存，则 MySQL 会直接返回缓存中的结果信息，能够提高数据的查询性能

存储引擎层

MySQL 中的存储引擎层主要负责数据的写入和读取，与底层的文件进行交互；MySQL 中的存储引擎是插件式的，服务器中的查询执行引擎通过相关的接口与存储引擎进行通信，同时，接口屏蔽了不同存储引擎之间的差异；MySQL 最常用的存储引擎有 InnoDB 和 MyISAM，开发者可以选择合适的存储引擎，也可以定制化开发存储引擎，存储引擎是基于表的，不是数据库

系统文件层

系统文件层主要包括 MySQL 中存储数据的持久化（物理）文件，与上层的存储引擎进行交互，其存储的文件主要有：日志文件、数据文件、配置文件、MySQL的进行PID 文件和 Socket 文件等

日志文件

主要包括：错误日志、通用查询日志、二进制日志、慢查询日志等

错误日志

主要存储的是 MySQL 运行过程中产生的错误信息。可以使用show variables like '%log_error%';语句来查看 MySQL 中的错误日志

  1. log_error=自定义路径

通用查询日志

主要记录 MySQL 运行过程中的一般查询信息，可以使用语句show variables like '%general%';来查看 MySQL 中的通用查询日志文件

  1. general-log=0|1（禁用|开启）
  2. general_log_file=/路径/文件名
  3. log-output=FILE|TABLE|NONE（文件|表|不存放，默认 FILE）

二进制日志

主要记录对 MySQL 数据库执行的插入、修改和删除操作，并且也会记录 SQL 语句执行的时间、执行的时长，但是二进制日志不记录 SELECT、SHOW 等不修改数据库的 SQL；主要用于恢复数据库的数据和实现MySQL 主从复制

  1. #查看二进制日志是否开启
  2. show variables like '%log_bin%';
  3. #查看二进制日志的参数
  4. show variables like '%binlog%'
  5. #查看日志文件
  6. show binary logs;

慢查询日志

慢查询主要记录的是执行时间超过指定时间的 SQL 语句，这个时间默认是 10 秒

  1. #查看是否开启慢查询日志
  2. show variables like '%slow_query%';
  3. #查看慢查询设置的时长
  4. show variables like '%long_query_time%'

数据文件

主要包括了：db.opt 文件、frm 文件、MYD 文件、MYI 文件、ibd 文件、ibdata 文件、ibdata1 文件、ib_logfile0 和 ib_logfile1 文件等

db.opt

主要记录当前数据库使用的字符集和检验规则等信息

frm

存储数据表的结构信息，主要是数据表相关的元数据信息，包括数据表的表结构定义信息，每张表都会有一个 frm 文件
注意：MySQL8 版本中的 innodb 存储引擎的表没有 frm 文件

MYD

MyISAM 存储引擎专用的文件格式，主要存放 MyISAM 存储引擎数据表中的数据，每张 MyISAM 存储引擎表对应一个 MYD 文件

MYI

MyISAM 存储引擎专用的文件格式，主要存放与 MyISAM 存储引擎数据表相关的索引信息，每张 MyISAM 存储引擎表对应一个 MYI 文件

ibd

存放 Innodb 存储引擎的数据文件和索引文件，主要存放的是独享表空间的数据和索引，每张表对应一个 ibd 文件

ibdata

存放 Innodb 存储引擎的数据文件和索引文件，主要存放的是共享表空间的数据和索引，所有表共用一个（或者多个）ibdata 文件，可以根据配置来指定共用的 ibdata 文件个数

ibdata1

MySQL 的系统表空间数据文件，主要存储 MySQL 的数据表元数据、Undo 日志等信息

ib_logfile0 和 ib_logfile1

MySQL 数据库中的 Redo log 文件，主要用于 MySQL 实现事务的持久性；如果在某个时间点 MySQL 发生了故障，此时如果有脏页没有写入到数据库的 ibd 文件中，在重启 MySQL 的时候，MySQL 会根据 Redo Log 信息进行重做，将写入 Redo Log 并且尚未写入数据表的数据进行持久化操作

配置文件

用于存储 MySQL 所有的配置信息，在 Unix/Linux 环境中是 my.cnf 文件，在 Windows 环境中是 my.ini 文件

PID

pid 文件是存放 MySQL 进程运行时的进程号的文件，主要存在于 Unix/Linux 环境中，具体的存储目录可以在 my.cnf 文件中进行配置

Socket

socket 文件和 pid 文件一样，都是 MySQL 在 Unix/Linux 环境中运行才会有的文件，客户端可以直接通过 Socket 来连接 MySQL

命令

ngram

全文解析器

explain

查看执行计划，查询 SQL 走了哪些索引

show profile

查看 SQL 对系统资源的损耗情况

工具

xbstream

  1. mkdir /data
  2. cat /root/28.xb | xbstream -x -C /data
  4. xtrabackup --decompress --remove-original --target-dir=/data
  5. xtrabackup --prepare  --use-memory=1GB --target-dir=/data
  7. chown -R mysql:mysql /data
  8. xtrabackup --defaults-file=/etc/my.cnf --move-back --target-dir=/data
  9. xtrabackup --datadir=/var/lib/mysql --copy-back --target-dir=/home/mysql/data
  1. yum install https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.24/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm
  2. yum install https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm