22-MySQL架构与性能优化

架构：C/S

Connectors: 连接器

• 可供Native C API、JDBC、ODBC、NET、PHP、Perl、Python、Ruby、Cobol等连接mysql；
• 站在编程角度可以理解为连入数据库管理系统的驱动，站在mysql角度称作专用语言对应的链接器；
• 任何链接器连入mysql以后，mysql是单进程多线程模型的，因此，每个用户连接，都会创建一个单独的连接线程；
• 用户连入mysql后，创建一个连接线程，完成之后，能够通过这个连接线程完成接收客户端发来的请求，为其处理请求，构建响应报文并发给客户端；
• 由于是单进程模型，就意味着必须要维持一个线程池，需要一个线程池来管理这众多线程是如何对众多客户端的并发请求，完成并发响应的，组件connection pool就是实现这样功能；

connection pool：线程池

• authentication认证，用户发来的账号密码是否正确要完成认证功能；
• thread reuse线程重用功能，一般当一个用户连接进来以后要用一个线程来响应它，而后当用户退出这个线程有可能并非被销毁，而是把它清理完以后，重新收归到线程池当中的空闲线程中去，以完成所谓的线程重用；
• connection limit 线程池的大小决定了连接并发数量的上限，例如，最多容纳100线程，一旦到达此上限后续到达的连接请求则只能排队或拒绝连接；
• check memory用来检测内存；
• caches实现线程缓存；
• 以上内容都属于线程池的功能.当用户请求之后，通过线程池建立一个用户连接，这个线程一直存在，然后用户就通过这个会话，发送对应的SQL语句到服务器端.

SQL Interface：

• 服务器收到SQL语句后，要对语句完成执行，首先要能理解sql语句需要有sql解释器或叫sql接口sqlinterface就可理解为是整个mysql的外壳，就像shell是linux操作系统的外壳一样；用户无论通过哪种链接器发来的基本的SQL请求，当然，事实上通过native C API也有发过来的不是SQL 请求，而仅仅是对API中的传递参数后的调用；不是SQL语句不过都统统理解为sql语句罢了；对SQL而言分为DDL 和DML两种类型，但是无论哪种类型，提交以后必须交给内核，让内核来运行，在这之前必须要告诉内核哪个是命令，哪个是选项，哪些是参数，是否存在语法错误等等；因此，这个整个SQL 接口就是一个完完整整的sql命令的解释器，并且这个sql接口还有提供完整的sql接口应该具备的功能，比如支持所谓过程式编程，支持代码块的实现像存储过程、存储函数，触发器、必要时还要实现部署一个关系型数据库应该具备的基本组件例如视图等等，其实都在sql interface这个接口实现的；SQL接口做完词法分析、句法分析后，要分析语句如何执行让parser解析器或分析器实现.

Parse分析器:

• parser是专门的分析器，这个分析器并不是分析语法问题的，语法问题在sql接口时就能发现是否有错误了，一个语句没有问题，就要做执行分析，所谓叫查询翻译，把一个查询语句给它转换成对应的能够在本地执行的特定操作；比如说看上去是语句而背后可能是执行的一段二进制指令，这个时候就完成对应的指令，还要根据用户请求的对象，比如某一字段查询内容是否有对应数据的访问权限，或叫对象访问权限；在数据库中库、表、字段、字段中的数据有时都称为object，叫一个数据库的对象，用户认证的通过，并不意味着就能一定能访问数据库上的所有数据，所以说，mysql的认证大概分为两过程都要完成，第一是连入时需要认证账号密码是否正确这是authentication，然后，验证成功后用户发来sql语句还要验证用户是否有权限获取它期望请求获取的数据；这个称为object privilege，这一切都是由parser分析器进行的.

Optimizer路径优化：

• 分析器分析完成之后，可能会生成多个执行树，这意味着为了能够达到访问期望访问到的目的，可能有多条路径都可实现，就像文件系统一样可以使用相对路径也可使用绝对路径；它有多种方式，在多种路径当中一定有一个是最优的，类似路由选择，因此，优化器就要去衡量多个访问路径中哪一个代价或开销是最小的，这个开销的计算要依赖于索引等各种内部组件来进行评估；而且这个评估的只是近似值，同时还要考虑到当前mysql内部在实现资源访问时统计数据，比如，根据判断认为是1号路径的开销最小的，但是众多统计数据表明发往1号路径的访问的资源开销并不小，并且比3号路径大的多，因此，可能会依据3号路径访问；这就是所谓的优化器它负责检查多条路径，每条路径的开销，然后评估开销，这个评估根据内部的静态数据，索引，根域根据动态生成的统计数据来判定每条路径的开销大小，因此这里还有statics；一旦优化完成之后，还要生成统计数据，这就是优化器的作用；如果没有优化器mysql执行语句是最慢的，其实优化还包括一种功能，一旦选择完一条路径后，例如用户给的这个命令执行起来，大概需要100个开销，如果通过改写语句能够达到同样目的可能只需要30个开销；于是，优化器还要试图改写sql语句；所以优化本身还包括查询语句的改写；一旦优化完成，接下来就交给存储引擎完成.

Caches和Buffers:

• 事实上，整个存取过程，尤其是访问比较热点的数据，也不可能每一次当用户访问时或当某SQL语句用到时再临时从磁盘加载到内存中，因此，为了能够加上整个性能，mysql的有些存储引擎可以实现，把频繁访问到的热点数据，统统装入内存，用户访问、修改时直接在内存中操作，只不过周期性的写入磁盘上而已，比如像InnoDB，所以caches和buffers组件就是实现此功能的；MySQL为了执行加速，因为它会不断访问数据，而随计算机来说io是最慢的一环，尤其是磁盘io，所以为了加速都载入内存中管理；这就需要MySQL 维护cache和buffer缓存或缓冲；这是由MySQL 服务器自己维护的；有很多存储引擎自己也有cache和buffer

Pluggable Storage Engines: 数据存储引擎插件

• mysql是插件式存储引擎，它就能够替换使用选择多种不同的引擎（已经100种之多），MyISAM是MySQL 经典的存储引擎之一，InnoDB是由Innobase Oy公司所开发，2006年五月由甲骨文公司并购提供给MySQL的，NDB主要用于MySQL Cluster 分布式集群环境，archive做归档的等等，还有许多第三方开发的存储引擎；存储引擎负责把具体分析的结果完成对磁盘上文件路径访问的转换，数据库中的行数据都是存储在磁盘块上的，因此存储引擎要把数据库数据映射为磁盘块，并把磁盘块加载至内存中；进程实现数据处理时，是不可能直接访问磁盘上的数据的，因为它没有权限，只有让内核来把它所访问的数据加载至内存中以后，进程在内存中完成修改，由内核再负责把数据存回磁盘；对于文件系统而言，数据的存储都是以磁盘块方式存储的，但是，mysql在实现数据组织时，不完全依赖于磁盘，而是把磁盘块再次组织成更大一级的逻辑单位，类似于lvm中的PE或LE的形式；其实，MySQL的存储引擎在实现数据管理时，也是在文件系统之上布设文件格式，对于文件而言在逻辑层上还会再次组织成一个逻辑单位，这个逻辑单位称为mysql的数据块datablock 一般为16k ，对于关系型数据库，数据是按行存储的；一般一行数据都是存储在一起的，因此，MySQL 在内部有一个datablock，在datablock可能存储一行数据，也可能存放了n行数据；将来在查询加载一行数据时，内核会把整个一个数据数据块加载至内存中，而mysql存储引擎，就从中挑出来某一行返回给查询者，是这样实现的；所以整个存储是以datablock在底层为其最终级别的.

File system和Files&Logs:

• 一个数据库提供了3种视图，物理视图就是看到的对应的文件系统存储为一个个的文件，MySQL的数据文件类型，常见的有redo log重做日志，undo log撤销日志，data是真正的数据文件，index是索引文件，binary log是二进制日志文件，error log错误日志，query log查询日志，slow query log慢查询日志，在复制架构中还存在中继日志文件，跟二进制属于同种格式；这是mysql数据文件类型，也就是物理视图；逻辑视图这是在mysql接口上通过存储引擎把mysql文件尤其是data文件，给它映射为一个个关系型数据库应该具备组成部分，比如表，一张表在底层是一个数据文件而已，里面组织的就是datablock，最终映射为磁盘上文件系统的block，然后再次映射为本地扇区的存储，但是整个mysql需要把他们映射成一个二维关系表的形式，需要依赖sql接口以及存储引擎共同实现；所以，把底层数据文件映射成关系型数据库的组件就是逻辑视图；DBA 就是关注内部组件是如何运作的，并且定义、配置其运作模式，而链接器都是终端用户通过链接器的模式进入数据库来访问数据；数据集可能非常大，每一类用户可能只有一部分数据的访问权限，这个时候，最终的终端用户所能访问到的数据集合称作用户视图；

Managment Services & Utilites:

• 为了保证MySQL运作还提供了管理和服务工具，例如:备份恢复工具，安全工具，复制工具，集群服务，管理、配置、迁移、元数据等工具

存储引擎

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/storage-engines.html

MyISAM

• MyISAM 引擎特点

• 不支持事务
• 表级锁定
• 读写相互阻塞，写入不能读，读时不能写
• 只缓存索引
• 不支持外键约束
• 不支持聚簇索引
• 读取数据较快，占用资源较少
• 不支持MVCC（多版本并发控制机制）高并发
• 崩溃恢复性较差
• MySQL5.5.5 前默认的数据库引擎

• MyISAM 存储引擎适用场景

• 只读（或者写较少）
• 表较小（可以接受长时间进行修复操作）

• MyISAM 引擎文件

• tbl_name.frm 表格式定义
• tbl_name.MYD 数据文件
• tbl_name.MYI 索引文件

InnoDB

• InnoDB引擎特点

• 行级锁
• 支持事务，适合处理大量短期事务
• 读写阻塞与事务隔离级别相关
• 可缓存数据和索引
• 支持聚簇索引
• 崩溃恢复性更好
• 支持MVCC高并发
• 从MySQL5.5后支持全文索引
• 从MySQL5.5.5开始为默认的数据库引擎

MySQL 中的系统数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |   --> 业务数据库，可做备份对象
| information_schema |   --> 数据字典库，只存在内存中
| mysql              |   --> 主数据库，mysql核心库，可做备份对象
| performance_schema |   --> 性能数据库，收集数据库性能参数
| shopxo             |   --> 业务库
| sys                |   --> 性能数据库简略版,数据来源performance_schema
+--------------------+
6 rows in set (0.70 sec)

mysql 数据库

• 是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

information_schema 数据库

• MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与"数据字典"，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

performance_schema 数据库

• MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

sys 数据库

• MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DataBase的运行情况

服务器配置和状态

https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html

可以通过mysqld选项，服务器系统变量和服务器状态变量进行MySQL的配置和查看状态【存在即是选项也是变量的配置】

• 注意：

• 其中有些参数支持运行时修改，会立即生效
• 有些参数不支持动态修改，且只能通过修改配置文件，并重启服务器程序生效
• 有些参数作用域是全局的，为所有会话设置
• 有些可以为每个用户提供单独（会话）的设置

mysqld服务器选项

特点

• 服务器选项用横线,不用下划线
• 可以通过配置到文件中持久保存

#查看mysqld可用选项列表和及当前值
mysqld --verbose --help
#获取mysqld当前启动选项
mysqld --print-defaults
#选项配置文件--通常需要重启服务
[root@ubuntu2204 ~]#cat /etc/mysql/mysql.conf.d/mysqld.cnf 
#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#
# * Basic Settings
#
user    = mysql
# pid-file  = /var/run/mysqld/mysqld.pid
# socket  = /var/run/mysqld/mysqld.sock
# port    = 3306
# datadir = /var/lib/mysql


# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir    = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address   = 127.0.0.1
#mysqlx-bind-address  = 127.0.0.1
...

服务器系统变量

特点

• 分全局变量和局部变量
• 系统变量使用下划线,不使用横线
• 非选项的变量不可以配置到文件中永久保存

SHOW GLOBAL VARIABLES; #只查看global变量
SHOW [SESSION] VARIABLES;#查看所有变量(包括global和session)
#查看指定的系统变量
SHOW VARIABLES LIKE 'VAR_NAME';
SELECT @@VAR_NAME;
#查看选项和部分变量
[root@centos8 ~]#mysqladmin variables

#修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效
SET GLOBAL system_var_name=value;
SET @@global.system_var_name=value;

修改会话变量：将立即在本地会话生效，具有时效性
SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=value;

服务器状态变量

特点

• 分全局和会话两种
• 状态变量只读，用于保存mysqld运行中的统计数据的变量，不可更改

SHOW GLOBAL STATUS;
SHOW [SESSION] STATUS;

服务器变量 SQL_MODE

特点

• 对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置
• 根据设置的选项值不同，同样的sql语句可能会有不同的查询结果以及报错方式

https://mariadb.com/kb/en/library/sql-mode/

• 常见MODE:

• NO_AUTO_CREATE_USER： 禁止GRANT创建密码为空的用户
• NO_ZERO_DATE：在严格模式，不允许使用'0000-00-00'的时间
• ONLY_FULL_GROUP_BY： 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
• NO_BACKSLASH_ESCAPES： 反斜杠"\"作为普通字符而非转义字符
• PIPES_AS_CONCAT： 将"||"视为连接操作符而非"或"运算符

INDEX 索引

索引：是排序的特殊数据结构，定义在作为查找条件的字段上，又称为键key，索引通过存储引擎实现

• 优点

• 索引可以降低服务需要扫描的数据量，减少了IO次数
• 索引可以帮助服务器避免排序和使用临时表
• 索引可以帮助将随机I/O转为顺序 I/O

• 缺点

• 占用额外空间
• 影响插入速度

索引结构

B+Tree索引：

• 按顺序存储，每一个叶子节点到根结点的距离是相同的
• 左前缀索引，适合查询范围类的数据

经典面试题：InnoDB中一颗的B+树可以存放多少行数据？

假设定义一颗B+树高度为2，即一个根节点和若干叶子节点。那么这棵B+树的存放总行记录数=根节点指针数*单个叶子记录的行数。
这里先计算叶子节点：B+树中的单个叶子节点的大小为16K，假设每一条目为1K，那么记录数即为16(16k/1K=16);
然后计算非叶子节点能够存放多少个指针，假设主键ID为int类型，那么长度为4字节，而指针大小在InnoDB中是设置为6个字节，这样加起来一共是10个字节。那么通过页大小/(主键ID大小+指针大小），即16k/10=1600个指针，所以一颗高度为2的B+树能存放16*1600=25600条这样的记录.根据这个原理就可以算出一颗高度为3的B+树可以存放16*1600*1600=40960000条记录。所以在InnoDB中B+树高度一般为2-3层，它就能满足千万级的数据存储

使用B+Tree索引的方式

• 全值匹配
• 范围匹配，给定开头和结尾
• 从左往右查找原则。比如匹配最左前缀
• 只访问索引的查询
• 限制

• 如不从最左列开始，则无法使用索引
• 不能跳过索引中的列

*建立主键时会默认以主键为索引。

索引优化

#参考资料: 阿里的《Java开发手册》
https://developer.aliyun.com/topic/java2020

1. 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）
2. 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度
3. 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
4. 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
5. 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
6. 对于经常在where子句使用的列，最好设置索引
7. 对于有多个列where或者order by子句，应该建立复合索引
8. 对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引
9. 尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高
10. 不要使用RLIKE正则表达式会导致索引失效
11. 查询时，能不要*就不用*，尽量写全字段名，比如:select id,name,age from students;
12. 大部分情况连接效率远大于子查询
13. 在有大量记录的表分页时使用limit
14. 对于经常使用的查询，可以开启查询缓存
15. 多使用explain和profile分析查询语句
16. 查看慢查询日志，找出执行时间长的sql语句优化

语法

#查看索引
SHOW INDEX FROM [db_name.]tbl_name;
或
DESC table;

mysql> desc students;  --> Key 指索引
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| StuID     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| Name      | varchar(50)      | NO   |     | NULL    |                |
| Age       | tinyint unsigned | NO   |     | NULL    |                |
| Gender    | enum('F','M')    | NO   |     | NULL    |                |
| ClassID   | tinyint unsigned | YES  |     | NULL    |                |
| TeacherID | int unsigned     | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
6 rows in set (0.10 sec)

mysql> show index from students \G;
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY  --> 主键为索引
 Seq_in_index: 1
  Column_name: StuID  --> 索引字段
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.04 sec)

ERROR: 
No query specified

---------------------------------------------------------------------------------------------------
#使用索引
mysql> select * from students where stuid=20;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name      | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
|    20 | Diao Chan |  19 | F      |       7 |      NULL |
+-------+-----------+-----+--------+---------+-----------+
1 row in set (0.02 sec)

#查看索引使用情况
mysql> explain select * from students where stuid=20 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
   partitions: NULL
         type: const  --> 类型
possible_keys: PRIMARY  --> 可能用到的索引
          key: PRIMARY  --> 实际使用索引
      key_len: 4
          ref: const
         rows: 1  --> 扫描行数1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

对比一下未使用索引情况
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
   partitions: NULL
         type: ALL  --> 全表扫描
possible_keys: NULL --> 可能使用索引为null
          key: NULL --> 实际未使用索引
      key_len: NULL
          ref: NULL
         rows: 25 --> 扫描行数25
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

---------------------------------------------------------------------------------------------------
#创建索引
CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
或
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]);

mysql> create index idx_name on students(name);
Query OK, 0 rows affected (1.76 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from students \G;
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: StuID
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: Name
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> explain select * from students where name like 'Lin%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
   partitions: NULL
         type: range  --> 类型是范围查询
possible_keys: idx_name --> 可能索引
          key: idx_name --> 实际索引
      key_len: 152
          ref: NULL
         rows: 2  --> 扫描行数2
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.01 sec)

ERROR: 
No query specified

---------------------------------------------------------------------------------------------------
#删除索引
DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

mysql> drop index idx_name on students;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from students \G;
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: StuID
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

ERROR: 
No query specified

EXPLAIN 工具

可以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询

#参考网址
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

语法

EXPLAIN SELECT clause

#查看索引使用情况
mysql> explain select * from students where stuid=20 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
   partitions: NULL
         type: const  --> 类型
possible_keys: PRIMARY  --> 可能用到的索引
          key: PRIMARY  --> 实际使用索引
      key_len: 4
          ref: const
         rows: 1  --> 扫描行数1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：NULL> system >
const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >
range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref

常见
NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

使用 profile 工具

• 分析SQL执行性能

----------------------------------------------------------------------------
#开启分析工具
mysql> set profiling = ON;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show profiles;
Empty set, 1 warning (0.03 sec)
----------------------------------------------------------------------------
#执行sql语句
mysql> use hellodb
Database changed

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from students where age=20;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     9 | Ren Yingying |  20 | F      |       6 |      NULL |
|    22 | Xiao Qiao    |  20 | F      |       1 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)

----------------------------------------------------------------------------
#查看分析工具抓起的sql执行记录
mysql> show profiles;
+----------+------------+-------------------------------------+
| Query_ID | Duration   | Query                               |
+----------+------------+-------------------------------------+
|        1 | 0.00019575 | SELECT DATABASE()                   |
|        2 | 0.00057075 | select @@profiling                  |
|        3 | 0.01501550 | select * from students where age=20 |
+----------+------------+-------------------------------------+
3 rows in set, 1 warning (0.00 sec)

----------------------------------------------------------------------------
#查看第n条sql详细执行步骤和时长
mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000147 |
| checking permissions | 0.000016 |
| Opening tables       | 0.000017 |
| init                 | 0.000008 |
| optimizing           | 0.000010 |
| executing            | 0.000018 |
| end                  | 0.000006 |
| query end            | 0.000009 |
| closing tables       | 0.000006 |
| freeing items        | 0.000259 |
| cleaning up          | 0.000076 |
+----------------------+----------+
11 rows in set, 1 warning (0.01 sec)

----------------------------------------------------------------------------
#查看第n条sql执行时cpu使用情况
mysql> show profile cpu for query 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000147 | 0.000148 |   0.000000 |
| checking permissions | 0.000016 | 0.000014 |   0.000000 |
| Opening tables       | 0.000017 | 0.000017 |   0.000000 |
| init                 | 0.000008 | 0.000008 |   0.000000 |
| optimizing           | 0.000010 | 0.000009 |   0.000000 |
| executing            | 0.000018 | 0.000018 |   0.000000 |
| end                  | 0.000006 | 0.000006 |   0.000000 |
| query end            | 0.000009 | 0.000009 |   0.000000 |
| closing tables       | 0.000006 | 0.000005 |   0.000000 |
| freeing items        | 0.000259 | 0.000059 |   0.000000 |
| cleaning up          | 0.000076 | 0.000076 |   0.000000 |
+----------------------+----------+----------+------------+
11 rows in set, 1 warning (0.00 sec)

并发控制

机制：

• 读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务） ，多个读互不阻塞；加了S锁，所有会话都不能执行写入操作。

• 比如数据库备份

• 写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写；加了X锁，只能加锁者会话执行读写操作，其他会话不能读写。缺点是并发性差。
• S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容。

• 举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况也称为锁冲突

锁粒度：

• 表级锁：MyISAM
• 行级锁：InnoDB 对同一条数据写入操作时，会执行第一条语句，其他执行失败(行级锁特性)。

实现：

• 存储引擎：自行实现其锁策略和锁粒度
• 服务器级：实现了锁，表级锁，用户可显式请求

分类：

• 隐式锁：由存储引擎自动施加锁
• 显式锁：用户手动请求

锁策略：在锁粒度及数据安全性寻求的平衡机制

语法

#加锁
LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias]
lock_type] ...
lock_type:
READ #读锁
WRITE #写锁

#解锁
UNLOCK TABLES

#关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]

#查询时加写或读锁
SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

----------------------------------------------------------------------------
#范例1
mysql> lock tables students read ;
Query OK, 0 rows affected (0.00 sec)
mysql> update students set classid=2 where stuid=24; --> 会话1
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be
updated

mysql> unlock tables ; --> 解锁
mysql> update students set classid=2 where stuid=24; --> 会话2，在表加共享锁的情况下执行写入操作处于等待状态，如果时间过长则报执行失败错误，或者 等待解锁后执行
Query OK, 1 row affected (1 min 45.52 sec) --> 会话1解锁后会话2执行成功
Rows matched: 1 Changed: 1 Warnings: 0

----------------------------------------------------------------------------
#范例2:同时在两个终端对同一行记录修改
#同时对同一行记录执行update
#在第一终端提示1行成功
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#在第二终端提示0行修改
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1 Changed: 0 Warnings: 0

事务

事务 Transactions：一组原子性的 SQL语句，或一个独立工作单元

事务日志：记录事务信息，实现undo,redo等故障恢复功能

事务特性（ACID）

A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚

C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒定律

I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发

D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

语法

注意：只有事务型存储引擎中的DML语句方能支持此类操作，DDL命令无法撤销。比如drop table 无法回滚

sql执行效率提升

由于原子性的特点，IO操作极大的减少。就像中国式过马路，凑够一拨人就过，比一个一个的过效率更快

#显式启动事务：
BEGIN
BEGIN WORK
START TRANSACTION

#结束事务：
#提交,相当于vi中的wq保存退出
COMMIT
#回滚,相当于vi中的q!不保存退出
ROLLBACK

#自动提交：
set autocommit={1|0}  --> 建议：显式请求和提交事务，而不要使用"自动提交"功能

#事务支持保存点：
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

#查看事务：
#查看当前正在进行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
#以下两张表在MySQL8.0中已取消*
#查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

----------------------------------------------------------------------------
#范例1:找到未完成的导致阻塞的事务
#在第一会话中执行
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> update students set classid=10;
#在第二个会话中执行
MariaDB [hellodb]> update students set classid=20; #因为上面的会话加锁，所以此处卡动不
动，无法执行
#在第三个会话中执行
MariaDB [hellodb]> show engine innodb status;
...省略...
---TRANSACTION 120, ACTIVE 673 sec
2 lock struct(s), heap size 1136, 28 row lock(s), undo log entries 27
MySQL thread id 13, OS thread handle 139719808595712, query id 206 localhost
root
...省略...

#此指令不支持MySQL8.0
MariaDB [hellodb]> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------+-------------+-----------+-----------+----------------------+-------
-----+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table |
lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------+-------------+-----------+-----------+----------------------+-------
-----+------------+-----------+----------+-----------+
| 123:9:3:2 | 123 | X | RECORD | `hellodb`.`students` |
PRIMARY | 9 | 3 | 2 | 1 |
| 120:9:3:2 | 120 | X | RECORD | `hellodb`.`students` |
PRIMARY | 9 | 3 | 2 | 1 |
+-----------+-------------+-----------+-----------+----------------------+-------
-----+------------+-----------+----------+-----------+
2 rows in set (0.001 sec)
#此指令不支持MySQL8.0
MariaDB [hellodb]> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 123 | 123:9:3:2 | 120 | 120:9:3:2 |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.000 sec)
#查看正在进行的事务
MariaDB [hellodb]> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 123
trx_state: LOCK WAIT
trx_started: 2019-11-22 19:17:06
trx_requested_lock_id: 123:9:3:2
trx_wait_started: 2019-11-22 19:18:50
trx_weight: 2
trx_mysql_thread_id: 15 #线程ID
trx_query: update students set classid=20
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 2
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 120
trx_state: RUNNING
trx_started: 2019-11-22 19:08:51
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 29
trx_mysql_thread_id: 13 #线程ID
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 28
trx_rows_modified: 27
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.000 sec)
MariaDB [hellodb]> show processlist;
+----+-------------+-----------+---------+---------+------+----------------------
----+------------------+----------+
| Id | User | Host | db | Command | Time | State
| Info | Progress |
+----+-------------+-----------+---------+---------+------+----------------------
----+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge
coordinator | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker
| NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker
| NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker
| NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown
handler | NULL | 0.000 |
| 11 | root | localhost | hellodb | Query | 0 | Init
| show processlist | 0.000 |
| 13 | root | localhost | hellodb | Sleep | 38 |
| NULL | 0.000 |
| 15 | root | localhost | hellodb | Query | 10 | Updating
| update students set classid=20 | 0.000
+----+-------------+-----------+---------+---------+------+----------------------
----+------------------+----------+
7 rows in set (0.000 sec)
#杀掉未完成的事务
MariaDB [hellodb]> kill 13;
Query OK, 0 rows affected (0.000 sec)
#查看事务锁的超时时长，默认50s
MariaDB [hellodb]> show global variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.001 sec)

事务隔离级别（一般用2和3策略）

READ UNCOMMITTED 读未提交

• 可读取到未提交数据，产生脏读

READ COMMITTED 读提交

• 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致

REPEATABLE READ 可重复读（属于默认策略，适用于备份策略）

• 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置

SERIALIZABLE 序列化

• 可串行化，未提交的读事务阻塞写事务（加读锁，但不阻塞读事务），或者未提交的写事务阻塞读和写事务（加写锁，其它事务的读，写都不可以执行）。加锁独占，虽然不会出现脏读、读取数据变动、幻读等问题，但会导致并发性能差。

策略更改语法

#查询当前策略
#MySQL8.0之前版本
select @@x_isolation

#MySQL8.0
select @@transaction_isolation
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

#更新策略 - 临时设置
#MySQL8.0之前版本
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'
#MySQL8.0
SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'

#持久保存
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

日志管理

#日志类型
事务日志：transaction log
  事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write aheadlogging
  事务日志文件： ib_logfile0， ib_logfile1
错误日志 error log
通用日志 general log
慢查询日志 slow query log
二进制日志 binary log
中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

transaction log 事务日志

redo log：记录某数据块被修改后的值，数据更新前先记录redo log（即使未执行，也会被记录）提供数据的增删改记录

undo log：保存与执行的操作相反的操作,即记录某数据被修改前的值，可以用来在事务失败时进行rollback行rollback

图解

命令

#Innodb事务日志相关配置：
mysql> show variables like '%innodb_log%';
+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| innodb_log_buffer_size             | 16777216 |
| innodb_log_checksums               | ON       |
| innodb_log_compressed_pages        | ON       |
| innodb_log_file_size               | 50331648 | --> 最大容量，可指定
| innodb_log_files_in_group          | 2        | --> 写两个文件，轮流写，一个写满就写另一个，会覆盖
| innodb_log_group_home_dir          | ./       | --> mysql的默认存放路径 /var/lib/mysql
| innodb_log_spin_cpu_abs_lwm        | 80       |
| innodb_log_spin_cpu_pct_hwm        | 50       |
| innodb_log_wait_for_flush_spin_hwm | 400      |
| innodb_log_write_ahead_size        | 8192     |
| innodb_log_writer_threads          | ON       |
+------------------------------------+----------+
11 rows in set (0.01 sec)

[root@ubuntu2204 ~]#ll /var/lib/mysql
总用量 92156
drwx------  9 mysql mysql     4096 11月 10 15:10  ./
drwxr-xr-x 34 root  root      4096 11月  9 20:47  ../
-rw-r-----  1 mysql mysql       56 11月  9 20:25  auto.cnf
-rw-r-----  1 mysql mysql      180 11月  9 20:25  binlog.000001
...
-rw-r-----  1 mysql mysql   196608 11月 11 20:20 '#ib_16384_0.dblwr'
-rw-r-----  1 mysql mysql  8585216 11月 10 20:12 '#ib_16384_1.dblwr'
-rw-r-----  1 mysql mysql     3542 11月  9 21:34  ib_buffer_pool 
-rw-r-----  1 mysql mysql 12582912 11月 11 20:18  ibdata1  
-rw-r-----  1 mysql mysql 12582912 11月  9 21:34  ibtmp1
drwxr-x---  2 mysql mysql     4096 11月  9 21:34 '#innodb_redo'/ --> 存放数据，里面有redolog
drwxr-x---  2 mysql mysql     4096 11月  9 21:34 '#innodb_temp'/

...
-rw-r-----  1 mysql mysql 16777216 11月 10 21:10  undo_001 --> undolog
-rw-r-----  1 mysql mysql 16777216 11月 11 20:20  undo_002

事务日志执行策略优化

innodb_flush_log_at_trx_commit=0|1|2

mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性 --> IO操作最多

2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失

#高并发业务行业最佳实践，是使用第三种折衷配置（=2）：
理由：
  1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快
  2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据

error log 错误日志

• mysqld启动和关闭过程中输出的事件信息
• mysqld运行中产生的错误信息
• event scheduler运行一个event时产生的日志信息
• 在主从复制架构中的从服务器上启动从服务器线程时产生的信息

#路径查看
mysql> SHOW GLOBAL VARIABLES LIKE 'log_error';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| log_error     | /var/log/mysql/error.log |  --> 路径可以自定义 /etc/mysql/mysql.conf.d/mysqld.cnf
+---------------+--------------------------+
1 row in set (0.00 sec)

#格式设置
#CentOS7 mariadb 5.5 默认值为1
#CentOS8 mariadb 10.3 默认值为2
log_warnings=0|1|2|3... #MySQL5.7之前
log_error_verbosity=0|1|2|3... #MySQL8.0

#案例：
[root@ubuntu2204 ~]#vi /etc/mysql/mysql.conf.d/mysqld.cnf --> 配置错误的文件信息
  [mysqld]
  innodb_flush_log_at_trx_commi=2
[root@ubuntu2204 ~]#systemctl restart mysql --> 重启报错
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xeu mysql.service" for details.
[root@ubuntu2204 ~]#tail -f /var/log/mysql/error.log --> 监控一下
2022-11-12T08:53:02.096571Z 0 [ERROR] [MY-000067] [Server] unknown variable  'innodb_flush_log_at_trx_commi=2'. --> 发现具体的错误
2022-11-12T08:53:02.096778Z 0 [ERROR] [MY-010119] [Server] Aborting

general log 通用日志

通用日志：记录对数据库的通用操作过程，包括:错误的SQL语句

负载均衡时可以观察是在那个服务器上执行了sql命令

#通用日志可以保存在：file（默认值）或 table（mysql.general_log表）
相关设置
   general_log=ON|OFF
   general_log_file=HOSTNAME.log
   log_output=TABLE|FILE|NONE

#file
--------------------------------------------------------------------------------------------------
mysql> select @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE         |
+--------------+
1 row in set (0.00 sec)

mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 | --> 默认不记录0，需要手动开启1
+---------------+
1 row in set (0.00 sec)

#启用
mysql> set global general_log=1; --> 当前会话使用，如果长期用写入配置文件
Query OK, 0 rows affected (0.01 sec)
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)

#默认通用日志存放在文件中
mysql> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)

#通用日志存放的文件路径
mysql> select @@general_log_file;
+----------------------------+
| @@general_log_file |
+----------------------------+
| /var/lib/mysql/ubuntu2204.log |
+----------------------------+
1 row in set (0.00 sec)

#监控日志变化
[root@ubuntu2204 ~]#tail -f /var/lib/mysql/ubuntu2204.log 
/usr/sbin/mysqld, Version: 8.0.31-0ubuntu0.22.04.1 ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
2022-11-12T09:13:51.005505Z       8 Query select @@general_log_file
 /usr/sbin/mysqld, Version: 8.0.31-0ubuntu0.22.04.1 ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
2022-11-12T09:19:41.785677Z       9 Query select @@general_log
2022-11-12T09:19:58.716755Z       9 Query select * from students  --> 实时记录会话中的sql语句

#记录到table中
--------------------------------------------------------------------------------------------------
mysql> set global log_output="table";
Query OK, 0 rows affected (0.00 sec)

mysql> select @@log_output;
+--------------+
| @@log_output |
+--------------+
| TABLE        |  --> 可以做针对表的统计查询
+--------------+
1 row in set (0.00 sec)

#general_log表是CSV格式的存储引擎
mysql> show table status like 'general_log'\G;
*************************** 1. row ***************************
           Name: general_log
         Engine: CSV --> 文件可以导入到windos中，方便excel加载分析 SZ命令下载到win环境
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-11-09 20:25:06
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb3_general_ci
       Checksum: NULL
 Create_options: 
        Comment: General log
1 row in set (0.00 sec)

ERROR: 
No query specified

#查询执行次数最多的语句 --> MySQL8.0 argument字段是加密形式 MariaDB会显示
mysql> select argument,count(argument) num  from mysql.general_log group by argument order by num desc limit 3 \G;
*************************** 1. row ***************************
argument: 0x73656C656374202A2066726F6D2067656E6572616C5F6C6F67
     num: 4
*************************** 2. row ***************************
argument: 0x73686F77207461626C6573
     num: 4
*************************** 3. row ***************************
argument: 0x73686F7720646174616261736573
     num: 3
3 rows in set (0.00 sec)

ERROR: 
No query specified

[root@ubuntu2204 ~]#tail -f /var/lib/mysql/mysql/general_log.CSV
"2022-11-12 17:31:02.856488","root[root] @ localhost []",9,1,"Field List","user "
"2022-11-12 17:31:05.457935","root[root] @ localhost []",9,1,"Query","select * from mysql where name like \"gen%\""
"2022-11-12 17:31:12.563000","root[root] @ localhost []",9,1,"Query","select * from mysql"
"2022-11-12 17:31:21.533088","root[root] @ localhost []",9,1,"Query","select * from mysql.user"
"2022-11-12 17:31:25.374741","root[root] @ localhost []",9,1,"Query","select * from mysql.user"
"2022-11-12 17:31:55.992176","root[root] @ localhost []",9,1,"Query","select * from mysql"
"2022-11-12 17:32:03.203557","root[root] @ localhost []",9,1,"Query","show tables"
"2022-11-12 17:32:25.839257","root[root] @ localhost []",9,1,"Query","select * from general_log"
"2022-11-12 17:32:29.500749","root[root] @ localhost []",9,1,"Query","select * from general_log"
"2022-11-12 17:34:18.400716","root[root] @ localhost []",9,1,"Query","show table status like 'general_log'"
"2022-11-12 17:39:01.832538","root[root] @ localhost []",9,1,"Query","SELECT DATABASE()"
"2022-11-12 17:39:01.833465","root[root] @ localhost []",9,1,"Init DB","hellodb"
"2022-11-12 17:39:01.835328","root[root] @ localhost []",9,1,"Query","show databases"
"2022-11-12 17:39:01.836984","root[root] @ localhost []",9,1,"Query","show tables"
"2022-11-12 17:39:01.839174","root[root] @ localhost []",9,1,"Field List","classes "
"2022-11-12 17:39:01.839473","root[root] @ localhost []",9,1,"Field List","coc "
"2022-11-12 17:39:01.839747","root[root] @ localhost []",9,1,"Field List","courses "
"2022-11-12 17:39:01.840092","root[root] @ localhost []",9,1,"Field List","scores "
"2022-11-12 17:39:01.840374","root[root] @ localhost []",9,1,"Field List","students "
"2022-11-12 17:39:01.840770","root[root] @ localhost []",9,1,"Field List","teachers "
"2022-11-12 17:39:01.841009","root[root] @ localhost []",9,1,"Field List","toc "
"2022-11-12 17:39:39.142156","root[root] @ localhost []",9,1,"Query","select * from students"

#MySQL8.0对访问的语句进行排序
[root@ubuntu2204 ~]#awk -F"," '{sql[$NF]++}END{for(i in sql){print sql[i],i}}' /var/lib/mysql/mysql/general_log.CSV |sort -nr|head
4 "show tables"
4 "select * from general_log"
3 "show databases"
3 "SELECT DATABASE()"
2 "user "
2 "time_zone_transition_type "
2 "time_zone_transition "
2 "time_zone_name "
2 "time_zone_leap_second "
2 "time_zone "

slow query log 慢查询日志

记录执行查询时长超出指定时长的操作

开启后生成信息在/var/lib/mysql/xxxx-slow.log文件中

语法

slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值，单位秒,默认为10s
slow_query_log_file=HOSTNAME-slow.log #慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
#上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语
句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain #记录内容
log_slow_queries = OFF #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除

工具

[root@ubuntu2204 ~]#mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

范例
[root@ubuntu2204 ~]#mysqldumpslow -s c -t 2 /var/lib/mysql/xxxx-slow.log
Reading mysql slow query log from /var/lib/mysql/centos8-slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=2.0 (2),
Rows_examined=25.0 (25), Rows_affected=0.0 (0), root[root]@localhost
select * from students where age=N
Count: 1 Time=4.00s (4s) Lock=0.00s (0s) Rows_sent=4.0 (4), Rows_examined=4.0
(4), Rows_affected=0.0 (0), root[root]@localhost
select sleep(N) from teachers --> 执行效率较低，可用explain分析索引使用情况

binary log 二进制日志(备份)*

• 记录导致数据改变或潜在导致数据改变的SQL语句
• 记录已提交的日志
• 不依赖于存储引擎类型

事务日志和二进制日志区别

• 事务日志在线,二进制离线
• 事务日志记录事务执行的过程,包括提交和未提交,二进制日志记录只记提交的过程
• 事务日志只支持innodb,二进制都支持MyiSAM和innoDB

功能：通过"重放"日志文件中的事件来生成数据副本

注意：建议二进制日志和数据文件分开存放

具体的配置方式将和MySQL 备份和恢复章节一起整理。

我是moore 大家一起加油！