宏观来看

  • MySQL 8.0采用GPL协议
  • 单进程多线程
  • 查看MySQL查找文件的顺序：mysql --help | grep my.cnf
1
2
3
$ mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

多个配置文件，以最后为准；缺失配置文件，按照编译时的默认参数设置。

  • 启动方式：mysqld、mysqld_safe、mysqld_multi(主要用于多实例启动)

首先当使用service mysqld start或者/etc/init.d/mysqld start这样的方式启动的时候，其实是使用了mysql.server这个脚本，这个脚本默认会调用mysqld_safe来启动mysqld，所以通常我们启动mysql之后查看进程的时候会发现有mysqld和mysqld_safe这两个进程存在。这两种通常都是单实例的启动方式，当然也可以使用mysqld来启动多实例的。而mysqld_multi用来启动多实例，也是通过先调用mysqld_safe和mysqld来启动mysql的。参考：MySQL Server

  • 启动原理：启动程序为mysql.server，用到的程序my_print_defaults、myslqd_safe和函数parse_server_arguments
    • my_print_defaults:读取my.cnf配置文件，输出参数传递给parse_server_arguments，该程序只读my.cnf中[mysqld]中的参数。
    • parse_server_arguments：该函数处理my_print_defaults传递过来的参数赋值给–basedir、–datadir、–pid-file、–server-startup-timeout
    • myslqd_safe:mysqld_safe程序调用mysqld程序来启动mysql服务,[mysqld_safe]会覆盖mysqld部分中的参数
    • mysqld_multi会读取配置文件中的[mysqld_muti],[mysqldN]下面的参数，N需要时一个整数，建议用端口号表示，该部分的配置会覆盖[mysqld]部分中的配置
    • 在mysqld进程挂掉的时候，mysqld_safe进程会监测到并重新将mysqld启动起来

My SQL Programs Overview

  • 查看数据库存放路径：show variables like '%datadir%'\G
  • 数据库是由 一个个文件组成 （一般来说都是二进制的文件） 的，如果要对这些文件执行诸如 SELECT 、 INSERT 、UPDATE和DETELE 之类的操作 ，不能通过简单的操作文件来更改数据库的内容 ， 需要通过数据库实例来完成对数据库的操作 。

体系结构

  • 架构
    • 连接池组件
    • 管理服务和工具组件
    • SQL接口组件
    • 查询分析器组件
    • 优化器组件
    • 缓冲（cache）组件
    • 插件式存储引擎
    • 物理文件

MySQL最重要的特点就是其插件式的表存储引擎，这是与其他数据库最重要的区别，它提供了一些列标准的管理和服务支持，这些标准与存储引擎无关，例如SQL分析器和优化器等。存储引擎是基于表的。MySQL的核心是存储引擎。

InnoDB存储引擎

完整支持ACID事务，主要面向在线事务处理(OLTP)方面的应用

架构

InnoDB Architecture

特点

  • 行锁设计
  • 支持外键
  • 支持非锁定读
    • 默认情况下读取操作不会产生锁
  • InnoDB存储引擎将数据放在一个逻辑的表空间中，由InnoDB自身进行管理。从MySQL4.1版本开始，它可以将每个InnoDB存储引擎的表单独存放到一个独立的ibd文件中。
  • InnoDB通过使用多版本并发控制(MVCC)来获得高并发性，并实现了SQL标准的4种隔离级别，默认为REPEATABLE级别。同时使用一种被称为next-key locking的策略来避免幻读(phantom)现象的产生。
  • InnoDB引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead)等高性能和高可用的功能。
  • 表中数据的存储，采用聚集(clustered)的方式。每张表的存储都按主键的顺序存放，如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此为主键。
  • 最有效的利用内存和CPU

最佳实践

  • 指定主键或自增的值
  • 关闭自动提交
  • 将一组相关的DML操作分组到事务中
  • 不使用LOCK TABLES语句
    • InnoDB可以同时处理多个会话，同时读取和写入同一个表，而不会牺牲可靠性或高性能。要获得对一组行的独占写访问权，请使用SELECT … FOR UPDATE语法仅锁定要更新的行。
  • 启用innodb_file_per_table选项或使用通用表空间将表的数据和索引放入单独的文件中，而不是系统表空间。
  • 在不牺牲读/写功能的情况下压缩InnoDB表
  • 使用选项–sql_mode = NO_ENGINE_SUBSTITUTION运行服务器，以防止在CREATE TABLE的ENGINE =子句中指定的引擎出现问题时使用其他存储引擎创建表。

架构模型

  • InnoDB有很多内存块，这些内存块组成了一个大的内存池，负责
    • 维护所有进程/线程需要访问的多个内部数据结构
    • 缓存磁盘上的数据，方便快速地读取，并且在对磁盘文件的数据进行修改之前在这里缓存
    • 重做日志(redo log)缓冲

后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。此外，将已修改的数据文件刷新到磁盘文件，同时保存在数据库发生异常情况下InnoDB能恢复到正常状态。

后台线程

InnoDB存储引擎是在master thread的线程上几乎实现了所有功能。默认情况下，InnoDB存储引擎的后台线程有7个：

  • IO Thread：4
    • Windows平台可以修改：配置文件中的innodb_file_io_threads，默认为4
    • Linux平台不能调整
  • master thread：1
  • 锁(lock)监控线程：1
  • 错误监控线程：1

查询配置

当前环境：

版本：5.7.22

平台：MacOS

  • 查询InnoDB引擎状态：show engine innodb status\G;`
1
2
3
4
5
6
7
8
9
10
11
12
13
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
  • 查询引擎版本：show variables like 'innodb_version'\G
1
2
Variable_name | innodb_version
Value         | 5.7.22
  • 查询IO线程数量：show variables like 'innodb_%io_threads'\G
1
2
3
4
5
6
***************************[ 1. row ]***************************
Variable_name | innodb_read_io_threads
Value         | 4
***************************[ 2. row ]***************************
Variable_name | innodb_write_io_threads
Value         | 4

线程调优

thread_pool_size

  • 线程池数量决定最佳性能
  • 在启动时设置
  • InnoDB
    • 建议值24-36
    • 对于DBT2和Sysbench等工作负载：36左右
    • 对于非常高写的工作负载，最佳设置有时可能更低。
  • MyISAM
    • 最佳性能：4-8
    • 较高的值往往会对性能产生轻微负面影响但不会产生显着影响。

thread_pool_stall_limit

  • 确保服务器不会被完全阻止
  • 运行时可修改
  • 上限为6秒，以防止出现死锁服务器的风险
  • 假设服务器执行工作负载，即使服务器加载，99.9％的语句在100ms内完成，其余语句在100ms到2小时之间相当均匀地传播。在这种情况下，将thread_pool_stall_limit设置为10（意味着100ms）是有意义的。
  • 对于主要执行非常简单的语句的服务器，默认值为60ms是可以的。

如果启用了tp_thread_group_stats表，查询确定已停止的已执行语句的比例：SELECT SUM（STALLED_QUERIES_EXECUTED）/ SUM（QUERIES_EXECUTED） FROM performance_schema.tp_thread_group_stats;这个数字应该尽可能低。要降低语句停顿的可能性，请增加thread_pool_stall_limit的值。

当一个语句到达时，它在实际开始执行之前可以延迟的最长时间是多少？假设以下条件适用：

在低优先级队列中排队有200个语句。

在高优先级队列中排队有10个语句。

thread_pool_prio_kickup_timer设置为10000（10秒）。

thread_pool_stall_limit设置为100（1秒）。

在最坏的情况下，10个高优先级语句代表10个持续执行很长时间的事务。因此，在最坏的情况下，没有语句将被移动到高优先级队列，因为它总是已经包含等待执行的语句。 10秒后，新语句有资格移动到高优先级队列。但是，在可以移动它之前，它之前的所有语句也必须移动。这可能需要另外2秒，因为每秒最多100个语句被移动到高优先级队列。现在，当语句到达高优先级队列时，可能会有许多长时间运行的语句。在最坏的情况下，每一个都会陷入停滞状态。

其他

MySQL Scalability Architecture

High Performance MySQL