11.MySQL逻辑架构

11.1 逻辑架构

• 连接层
  • 连接池：提供用于客户端与服务器交互的线程
• 服务层
  • SQL接口：接收SQL语句，返回最终查询结果
  • 解析器：语法解析，语义检测，语法正确生成语法树
  • 优化器：对查询进行优化，生成执行计划。一般分物理查询优化(通过索引、表连接方式进行优化)和逻辑查询优化(通过修改SQL语句实现执行效率更高的等价查询)
  • 查询缓存：以键值对的形式存储SQL语句和对应的查询结果，不同客户端共享缓存。如果查询中包含系统函数、自定义变量或函数、查询系统表，不会缓存，如果表经过修改则缓存无效
• 引擎层
  • 插件式的存储引擎：与底层文件进行交互，比如：InnoDB，甚至可以自己创建存储引擎，所有的存储引擎使用SHOW ENGINES查看

MySQL逻辑结构

11.2 SQL执行顺序

• SQL执行顺序如下：

  • 处理请求：客户端发出请求，连接池提供一个线程
  • 与SQL接口对接
  • 查询缓存：如果在查询缓存中发现了这条SQL语句，就会直接通过SQL接口返回；如果没有，就进入到解析器阶段
    • 需要说明的是，因为查询缓存往往效率不高，命令必须完全相同才能命中，所以在MySQL8.0之后就抛弃了这个功能
    • 之前也可以设置query_cache_type=0关闭，1表示开启，2表示按需开启(通过SELECT SQL_CACHE查询才会使用缓存)
    • 可以使用SHOW status like ''%Qcache%查询命中率
  • 解析器解析：进行语法分析、语义分析
  • 优化器：决定SQL语句的实际执行顺序，是否使用和使用哪个索引，生成执行计划，一般为选取-投影-连接的策略
  • 执行器：在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就调用存储引擎执行SQL查询并返回结果
  • 如果有缓存将结果存入查询缓存
  • 通过SQL接口返回结果

  详细过程和时间可以通过set profiling=1;看到

  

  MySQL执行流程

11.3 性能分析与优化

• 常用分析参数：可以使用SHOW STATUS LIKE '名称';的方式查询
  • Connections：连接MysQL服务器的次数
  • Uptime：MySQL服务器的上线时间
  • Slow_queries：慢查询的次数
  • Innodb_rows_read：InnoDB引擎Select查询返回的数据行数
  • Innodb_rows_inserted：InnoDB引擎执行INSERT操作插入的数据行数
  • Innodb_rows_updated：InnoDB引擎执行UPDATE操作更新的数据行数
  • Innodb_rows_deleted：InnoDB引擎执行DELETE操作删除的数据行数
  • Com_select：查询操作的次数
  • Com_insert：插入操作的次数。对于批量插入的INSERT操作，只累加一次
  • Com_update：更新操作的次数
  • Com_delete：删除操作的次数
  • Last_query_cost：上一次查询的相对成本，一般和需要读取的数据页数量正相关，时间和下面两个因素也有关，这个值无法代表效率
    • 位置决定效率：缓冲池中数据读取时间最快，其次是内存，硬盘中读取最慢
    • 批量决定效率：随机读的效率很低，连续读取可以快很多。如果批量读取磁盘多页，平均时间甚至短于单页内存随机读取

---

• 性能分析：MySQL提供了性能分析工具，可以通过设置SET profiling=1;(可选择为全局或会话设置)开启性能分析，支持以下方式

  -- 查看最近语句执行时间
  show profiles;
  
  -- 查看对应Query_id的执行时间
  -- type表示希望查看的参数，比如：
  -- cpu、block io、context switches
  -- I/O wait、I/O read、I/O write、I/O misc
  -- rows sent、rows examined、tmp disk
  show profile [type, type] for query Query_id;

---

• 语句执行解析EXPLAIN：使用EXPLAIN或DESCRIBE关键字可以查看SQL语句的执行计划，不会真的执行这个语句，比如：

  EXPLAIN select * from t1;
  
  -- 还支持json格式和tree格式
  -- json格式信息最详细，还包含执行的成本信息cost_info
  EXPLAIN FORMAT=JSON select * from t1;
  EXPLAIN FORMAT=TREE select * from t1;
  
  -- 官方工具MySQL workbench中也支持EXPLAIN可视化查看
  
  -- 查看上次执行计划优化后的语句(非标准语句，可能无法直接复制执行)
  SHOW WARNINGS;

  在MySQL 5.6.3之前只能显示SELECT的执行计划，之后版本才能查询增删改的执行计划

• EXPLAIN直接查询到的列信息：json格式得到的列名称和直接查询是不同的，见官方文档EXPLAIN输出列表格

  • id：一般在查询语句中每个SELECT关键字都对应一个唯一的id，有时优化器会重写语句减少查询次数
    • id如果相同，可以认为是一组，组内从上往下顺序执行
    • 在所有组中，id值越大，优先级越高，越先执行
    • 每个id号，表示一趟独立的查询，查询趟数越少越好
  • select_type：对应查询的类型，用来了解查询在整个语句中的角色，注意子查询可能被优化器转换为多表连接的方式

  名称	描述
  SIMPLE	简单查询(未使用UNION或子查询)
  PRIMARY	最外层或UNION查询中最左边的SELECT
  UNION	UNION中的第二个或后续SELECT语句
  UNION RESULT	UNION的结果集
  SUBQUERY	子查询中的第一个SELECT
  DEPENDENT SUBQUERY	子查询中的第一个SELECT，依赖外部查询中的表，可能会执行多次
  DEPENDENT UNION	UNION中的第二个或后续SELECT语句，依赖外部查询
  DERIVED	派生表，也就是查询当表FROM SELECT ...
  MATERIALIZED	物化子查询，也就是将子查询预先查询出来作为临时表
  UNCACHEABLE SUBQUERY	结果不可缓存的子查询，需针对外部查询的每一行重新计算
  UNCACHEABLE UNION	属于不可缓存子查询中的UNION语句(参见UNCACHEABLE SUBQUERY)

  • table：表名，查询过程中涉及到的表，可能有额外创建的表(比如去重合并UNION操作)。有多少个表会有几行记录，按排列顺序第一个表称为驱动表，其他表是被驱动表

  • partitions：命中的分区信息，非分区表该项为NULL，一般可忽略

    -- 分区表要在创建时创建分区
    CREATE TABLE t1 (
      id INT PRIMARY KEY,
    ) PARTITION BY RANGE(id) (
      PARTITION p0 VALUES LESS THAN (100),
      PARTITION p1 VALUES LESS THAN MAXVALUE,
    )

  • type：针对单表的访问方法，按照性能从好到差，依次为：

    • system：表中只有一条记录，且存储引擎有精确统计记录条数(比如：MyISAM和Memory)

    • const：使用主键或UNIQUE索引与对应类型常量进行等值匹配

    • eq_ref：被驱动表是通过主键或UNIQUE索引进行等值匹配访问的

    • ref：通过普通的索引与对应类型常量进行等值匹配时

    • fulltext：使用全文索引

    • ref_or_null：通过普通的索引与对应类型的常量进行等值匹配且使用或OR操作允许NULL值时

    • index_merge：使用AND、OR操作同一个表的多个列 进行匹配时，即同时使用多个索引

      -- 一般有三种情况
      SELECT * FROM table 
      -- Intersection（交集）索引合并
      WHERE index1_col = 'A' AND index2_col = 'B';
      -- Union（并集）索引合并
      WHERE index1_col = 'A' OR index2_col = 'B';
      -- Sort-Union（排序并集）索引合并
      WHERE index1_col < 10 OR index2_col > 100;

    • unique_subquery：查询优化器将IN()中的子查询优化为EXISTS且子查询可以使用主键等值匹配时

    • index_subquery：子查询中列包含非主键索引且结果集较小，查询优化器将IN()中的子查询改为扫描这个索引的方式时

    • range：使用索引获取某些满足范围区间(比如>、<、IN)的记录

    • index：可以进行索引覆盖，但需要扫描所有的索引记录(实际上未命中索引)时

    • ALL：全表扫描

  • possible_keys：可能用到的索引

  • key：实际上使用的索引

  • key_len：实际使用到的索引长度，联合索引只包含使用到的前缀索引长度，长度计算时包含数据占用空间，可能有变长字段增加2B长度空间、空标识增加1B空间

  • ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息，比如某个常量const、函数func或另一个列db1.t1.c1

  • rows：预估的需要读取的记录条数，越小越好

  • filtered：某个表经过搜索条件过滤后剩下的被读取记录条数的百分比，一般越大越好

  • Extra：一些额外的信息，常见的有

    • No Table used：没有使用到任何表
    • Impossible where：查询条件总是为false，即查询结果为空
    • Using where：查询条件中存在某个条件没有使用任何索引
    • No match min/max row：查询结果中无数据，无法得到最小或最大值
    • Using index：索引覆盖
    • Using index condition：索引条件下推，即在对二级索引进行无法利用索引的查询时，在二级索引进行进一步扫描遍历筛选，避免直接回表全局遍历
    • Using join buffer：在被驱动表无法利用索引访问时，分配join buffer加快查询速度
    • Not exists：查询限制被驱动表某些列为NULL，但实际上这些列非空
    • Using intersect/union/sort_union：查询结果为多个查询合并、交集
    • Zero limit：limit子句参数为$0$
    • Using filesort：无法使用索引排序，只能在内存或磁盘上进行排序
    • Using temporary：查询结果需要使用临时表，比如在进行UNION、DISTINCT、GROUP BY等操作且无法使用索引时

  EXPLAIN的缺陷

  • EXPLAIN不考虑缓存、不显示优化器的优化工作
  • EXPLAIN无法了解函数、存储过程、触发器对查询的影响
  • EXPLAIN的rows和filtered统计信息是估算的

• 如果希望详细地了解执行情况，可以考虑使用trace，需要设置

  -- 启用trace，设置格式为JSON
  SET optimizer_trace ='enabled=on', end_markers_in_json=on;
  -- 设置trace最大可使用内存
  SET optimizer_trace_max_mem_size = 1000000;

  • 设置完成后会记录执行的增删查改和流程控制语句相关的信息
  • trace的输出结果会保存在INFORMATION_SCHEMA.optimizer_trace表中
  • 包含查询语句query、跟踪信息TRACE、由于信息过长被截断的字节数MISSING_BYTES_BYOND_MAX_MEM_SIZE、是否有查看权限INSUFFICIENT_PRIVILEGES(如果没有权限显示为1)四部分

• 如果希望更好地分析，还可以参考sys数据库，比如分析索引

---

• 定位执行慢的查询：MySQL中可以通过慢查询日志分析慢查询

  -- 查看慢查询日志配置，默认是关闭的
  SHOW VARIABLES LIKE '%slow_query_log';
  -- 开启慢查询日志，是全局变量
  set GLOBAL slow_query_log = ON;
  
  -- 默认慢查询阈值为10s，也就是记录超过10s的查询
  SET LONG_QUERY_TIME=10;
  -- 默认最小扫描记录数为0，也就是扫描超过0条数据的查询才会被记录
  SET min_examined_row_limit=0;
  
  -- 慢查询日志位置查看
  -- 如果log_output不是file，则不会保存在这里
  SHOW VARIABLES LIKE '%slow_query_log_file';

  • 慢查询日志分析工具mysqldumpslow

  mysqldumpslow -help

  • 日志文件误删可以重新生成

  # 指定仅重新生成slow日志，需确保slow_query_log打开
  mysqladmin -u用户名 -p flush-log slow

---

• 分析过程
  • 观察状态是否存在周期波动
    • 如果性能有周期性波动，先尝试修改缓存策略，查看是否能改善
    • 如果没有改善，需要开启慢查询日志，设置记录慢查询日志
  • 查看慢查询日志，并结合SHOW profile和EXPLAIN判断
    • 如果是等待时间长，调整服务器参数
    • 如果是执行时间长，查看执行计划，确认是否需要优化，可能是索引设计问题、多表查询过多、表设计问题
  • 如果仍未解决，考虑数据库查询是否达到瓶颈，如果是的话，需要使用主从架构读写分离或者分库分表(垂直分库、垂直分表、水平分表)的方式进行优化

11.4 数据字典

数据字典也就是系统表，在MySQL中主要有4个系统自带数据库，分别是information_schema、mysql、performance_schema和sys

生产时不应该频繁查询information_schema、performance_schema和sys，最坏的情况下会导致业务请求被堵塞

• information_schema：用于存储数据库的元数据，元数据是描述数据的数据，比如数据库的表结构、列名、列类型、索引信息等
• mysql：用于存储MySQL的元数据，比如用户名、密码、权限等
• performance_schema：用于存储MySQL的性能数据，比如查询语句、执行时间、执行次数等
• sys：用于存储MySQL的系统数据，比如服务器状态、服务器配置、服务器资源使用情况等。是在MySQL5.7增加的，也就是监控分析视图，可以降低查询proformance_schema的复杂度

  • 可以进行索引分析

    -- 查询元余索引
    SELECT FROM sys.schema_redundant_indexes;
    -- 查询未使用过的索引
    SELECT FROM sys.schema_unused_indexes;
    -- 查询索引的使用情况
    SELECT index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
    FROM sys.schema_index_statistics WHERE table_schema='dbname';

  • 查询表相关信息

    -- 查询表的访问量 
    select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc; 
    -- 查询占用bufferpool较多的表 
    select object_schema,object_name,allocated,data
    from sys.innodb_buffer_stats_by_table order by allocated limit 10; 
    -- 查看表的全表扫描情况 
    select * from sys.statements_with_full_table_scans where db='dbname';

  • 查询特定的语句执行相关信息

    -- 监控SQL执行的频率 
    select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
    -- 监控使用了排序的SQL 
    select db,exec_count,first_seen,last_seen,query
    from sys.statements_with_sorting limit 1; 
    -- 监控使用了临时表或者磁盘临时表的SQL 
    select db,exec_count,tmp_tables,tmp_disk_tables,query
    from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

  • IO相关

    -- 查看消耗磁盘IO的文件 
    select file,avg_read,avg_write,avg_read+avg_write as avg_io
    from sys.io_global_by_file_by_bytes order by avg_read limit 10;

  • InnoDB相关

    -- 行锁阻塞情况 
    select * from sys.innodb_lock_waits;

11.5 日志

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为二进制日志、错误日志、通用查询日志和慢查询日志，这也是常用的4种。MySQL 8又新增两种支持的日志：中继日志和数据定义语句日志。使用这些日志文件可以查看MySQL内部发生的事情

• 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化，见性能分析与优化部分定位执行慢的查询

• 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助

• 查看状态和开启

  -- 包括是否开启和文件位置
  -- 默认文件为数据目录下的名字为主机名的.log文件
  show variables like 'general_log%';
  
  -- 临时方式
  -- 开启
  SET GLOBAL general_log = 'ON';
  SET GLOBAL general_log_file = 'path/filename';
  -- 关闭
  SET GLOBAL general_log = 'OFF';
  
  -- 永久方式开启，如果是关闭设置为off
  -- 编辑my.cnf文件
  -- [mysqld]
  -- general_log=ON
  -- general_log_file=path/filename

• 备份并刷新日志

  # 进入文件夹后
  mv hostname.log hostname.log.$(date +%Y%m%d%H%M%S)
  # 刷新日志，需要开启通用查询日志后执行
  mysqladmin -uroot -p flush-logs

• 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护

  • 错误日志是发现问题、解决问题的首选，默认开启且无法禁用

    SHOW variables like 'log_err%';
    
    -- [mysqld]
    -- # 错误日志位置
    -- log-error=path/filename

  • 刷新错误日志

    # 也可以和通用查询日志一样，进行移动备份
    # 但重新flush-logs会报错：无法打开错误日志文件进行记录
    # 需要先执行此步
    install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log
    
    # 再执行
    mysqladmin -uroot -p flush-logs

  flush-logs指令的操作

  • MySQL5.5.7以前的版本，flush-logs将错误日志文件重命名为filename.err_old,并创建新的日志文件
  • 从MySQL5.5.7开始，flush-logs只是重新打开日志文件，并不做日志备份和创建的操作
  • 如果日志文件不存在，MySQL启动或者执行flush-logs时会自动创建新的日志文件。重新创建错误日志，大小为0

• 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复

  • 一般称为binlog即binary log，也叫作变更日志(update log)。它记录了数据库所有执行的DDL和DML等数据库更新事件的语句，但不包含没有修改任何数据的语句(如数据查询语句select、show等)

  • 它以事件形式记录并保存在二进制文件中。通过这些信息，我们可以再现数据更新操作的全过程

    如果想要记录所有语句(例如，为了识别有问题的查询),需要使用通用查询日志

  • 主要场景：

    • 一是用于数据恢复，如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器，数据恢复见数据恢复
    • 二是用于数据复制，由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据一致

    可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog,需要依靠binlog来同步数据，保证数据一致性

  • 查看相关参数show variables like 'log_bin%';

    • log_bin：二进制日志是否开启，静态变量，修改必须重启
    • log_bin_basename：是binlog日志的基本文件名，后面会追加数字标识来表示每一个文件
    • log_bin_index：是二进制日志文件的索引文件，这个文件管理了所有的binlog文件的目录
    • log_bin_trust_function_creators：限制存储过程，因为binlog可用于主从复制，而存储函数可能导致主从的数据不一致。当开启binlog进行主从复制后，需要限制存储函数的创建、修改、调用
    • log_bin_use_v1_row_events：此只读系统变量已弃用。ON和OFF表示使用不同的日志行格式(MySQL5.6开始的默认值为ON)
    • sql_log_bin：是否记录日志，默认为ON，此值可以动态控制，恢复时应该设置为OFF，避免重复记录
    my.cnf

    [mysqld]
    # 二进制日志位置
    # 如果文件夹是新建的，需要给 mysql 用户权限或将文件夹所属用户修改为 mysql
    # 使用命令修改文件夹所属：sudo chown -R -v mysql:mysql binlog
    log-bin=path/filename
    # 保留时长
    binlog_expire_logs_seconds=600
    # 单个文件大小
    max_binlog_size=100M

  • 查看日志情况

    命令行

    # 使用 mysqlbinlog 命令查看文件内容，-v表示以执行事件的形式显示
    mysqlbinlog -v "path/filename"
    
    # 帮助
    mysqlbinlog --no-defaults --help

    sql连接

    -- 查看日志文件情况
    show binary logs;
    
    -- 查看特定内容
    -- 文件路径：需要查找的日志位置，默认为最早的文件
    -- pos位置：起始的pos位置，pos在查询中可读取到
    -- offset偏移：偏移量，相对pos的事件的额外偏移，按事件数计
    -- 条数：限制返回的事件条数
    SHOW binlog events [IN "文件路径"] [FROM 起始pos位置 [LIMIT offset偏移] 条数];

  • 数据根据binlog进行数据恢复

  # 最好在发现上一条命令错误之后，立即在sql连接中执行 flush logs; 新建文件记录之后的日志
  # 重新执行这些事件
  mysqlbinlog [选项] 日志路径|mysql路径 -u用户名 -p密码 数据库名称;
  # 选项
  # --start-date 和 --stop-date 指定开始时间和结束时间,格式为"YYYY-MM-DD HH:MM:SS"
  # --start-position 和 --stop-position 确定开始和结束位置,格式为数字
  # --database 确定要恢复的数据库名称和命令最后的名称一致
  
  # 范围选择，左右都是闭区间
  # 时间戳通过 mysqlbinlog -v 日志文件路径 查看，需要转换成时间
  # pos 通过 SHOW binlog events IN 文件路径 查看
  mysqlbinlog --start-position=1 --stop-position=1000 --database=db1 /var/lib/mysql/binlog.001 | mysql -uroot -p123456 db1;

  恢复的困难

  由于恢复需要指定起始和结尾，在数据量大的数据库进行恢复存在不小的困难，因为起止位置不好管理，更好的办法是使用主从服务器，确保数据的完整性

  • binlog格式：show variables like 'binlog_format';可查看binlog的格式

    • row：不记录语句上下文信息，只记录数据变化，记录了执行的细节，不会出现存储过程和函数在某些情况下无法正确复制的问题，使多线程从机复制成为可能
    • statement：默认，记录了所有修改数据的语句，不记录每行的变化，减少日志量，从机可以比主机版本高，但涉及服务器信息的变量如@@hostname会不一致
    • mixed：混合日志格式，即row和statement的结合，如果部分函数无法通过statement格式复制，则使用row格式保存

  • binlog删除：日志会占用不少的空间，可以删除部分无用的日志

    -- 删除到这个文件前的日志
    purge master logs to 'binlog_file_name';
    
    -- 删除在某个日期之前的日志
    purge master logs before 'date';
    
    -- 删除所有日志，谨慎使用
    reset master;

  • 写入机制：事务执行过程中，先把日志写到binlog cache,事务提交时，再把binlog cache写入文件系统缓存page cache，再同步binlog文件中

    • 一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache
    • 事务提交之后，如果写入的内容会超过文件的大小限制，所有同事务的日志内容都会被写入这个文件
    • 写入缓存和文件的时机可以通过sync_binlog参数来控制
      • 默认为0，表示每次提交事务都只写入缓存,由系统自行判断什么时候执行同步
      • 如果为了安全考虑可以设置为正整数$N$，表示每次提交了$N$个事务后同步文件

  与 redo log

  • redo log它是物理日志，记录内容是在某个数据页上做了什么修改，属于InnoDB存储引擎层产生的。
  • binlog是逻辑日志，记录内容是语句的原始逻辑，类以于对一行的某个字段修改，属于MySQL Server层。
  • 虽然它们都属于持久化的保证，但是则重点不同。
    • redo log让InnoDB存储引擎拥有了崩溃恢复能力。
    • binlog保证了MySQL集群架构的数据一致性。
  • redo log和binlog记录时机不同，redo log在事务执行时就记录，binlog在之后的某个事务提交时才同步文件
    • redo log设计了两阶段提交，第一阶段(binlog写入之前)是prepare预提交，第二阶段(在binlog写入之后)是commit提交
    • 如果发现只有预提交，事务在根据redo log恢复时会回滚
    • 如果发现binlog写入后但无commit提交，事务会正常执行

• 中继日志：只在主从服务器架构中存在，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作

  • 文件名格式是从服务器名-relay-bin.序号，也和binlog一样有一个索引文件从服务器名-relay-bin.index
  • 中继日志和binlog日志格式相同，可以使用mysqlbinlog读取

• 数据定义语句日志：记录数据定义语句执行的元数据操作

除二进制日志外，其他日志都是文本文件。默认情况下，所有日志创建于MySQL数据目录中；另一方面，日志会降低数据库性能，占用大量磁盘空间

11.6 区、段和表空间

• 页：是磁盘和内存交互的基本单位，一次查询和修改最少会读取一个页，页的类型包括数据页，系统页，事务数据页、undo页等
• 区：是文件系统的一个连续空间，在InnoDB一个区会分配64个连续的页，也就是默认大小是1MB
  • 尽量让相邻的页在物理存储上是连续的，减少范围查找的寻道时间
  • 在数据量特别大时，甚至会一次性申请连续的多个区，虽然有许多空闲空间没有使用，但从性能上看，消除了许多随机IO
• 段：是数据库中分配的基础单位，不同的数据库对象以不同的段形式创建，比如数据段、索引段、回滚段
  • 段用来对不同类型的数据进行区别处理，如果不同类型的数据都存放在连续的区中，范围扫描的性能会下降，因此需要对不同类型的数据进行区别对待，比如将叶子节点和非叶子节点分到不同的段存储
  • 段实际上是逻辑上的概念，存储不一定连续
• 表空间：是一个逻辑容器，数据库由一个或多个表空间构成，一个表空间中能有多个段，一个段只属于一个表空间
  • 表空间可以划分为系统表空间、用户表空间、撤销表空间和临时表空间
  • MySQL5.7和MySQL8.0在创建表时，数据和索引信息默认存放在独立表空间中，独立表空间可以在不同数据库之间迁移
    • 除了删除表操作外，独立表空间不会自动回收，这些空间之后有可能重用，对性能影响不大，如果有必要回收，可以使用ALTER TABLE name ENGINE=InnoDB;
    • 在MySQL5.7中，创建时独立表空间.ibd文件大小为96KB(6个页面)，在MySQL8.0中，独立表空间和表结构都存储在.ibd文件中，这个文件大小为112KB(7个页面)
    • 表空间会自动扩展
  • InnoDB系统表：当插入数据时，需要对插入表、列、约束和语法进行判断，这需要很多额外信息，在系统中保存了这些信息，并特意为InnoDB引擎定义了一些表
    • 比如SYS_TABLES存储了所有使用InnoDB的表的信息，SYS_COLUMNS存储了所有列的信息，除此之外常见的像索引SYS_INDEXES、文件系统路径SYS_FIELDS都有对应的表
    • 这些系统表以B+树结构存储在系统表空间的页面中
    • 用户无法直接访问这些表，为了分析的需要，系统提供了一系列对应的以INNODB_SYS_开头的用于分析参考的表，存储引擎在启动时会读取SYS_开头的表并将部分信息加载到这些参考表中
  • 考虑到区每次申请都是1MB的空间，对于少量数据，这很浪费，在表空间中有一个碎片区，这个区域中的页可以用于不同的目的
    • 刚开始为某个段分配时，段使用碎片区中的一页为单位分配空间
    • 当某段使用了32个碎片区的页时，会申请完整的区为单位分配空间

  区有四种类型：空闲的区、有剩余空间的碎片区、没有剩余空间的碎片区、附属于某个段的区

MySQL 表空间

11.7 MySQL 8.0

• MySQL8.0是MySQL5.7的下一个版本，比5.7新增了很多特性：
  • 更好的NoSQL(非关系数据库)支持
  • 更好的索引：新增隐藏索引和降序索引
  • 更完善的json支持，提供了将参数聚合成json的聚合函数JSON_OBJECTAGG()和JSON_ARRAYAGG()，新增行内操作符->>
  • 安全和账号管理：新增caching_sha2_password授权插件、角色、密码和FIPS模式支持，提高了数据库安全性
  • InnoDB引擎变化：InnoDB引擎做了大量的改进和优化，并且支持原子数据定义语言DDL，为事务提供更好的支持
  • 新增事务数据字典
  • 优化器增强
  • 资源管理：支持创建和管理资源组
  • 临时表、日志子系统、备份锁支持
  • 窗口函数、公用表表达式和正则表达式支持
  • 默认字符集从latin1修改为utf8mb4
• 同时移除了许多功能，相关功能语句可能无效或报错
  • 删除加密函数和空间函数相关内容
  • 删除查询缓存
  • 移除分区处理器、通用分区处理程序和mysql_plugin工具
  • INFORMATION_SCHEMA数据库中的系统状态和变量信息，这些内容可以在性能模式(在配置文件中设置performance_schema=ON)获取