客户端发出SQL指令后,首先在缓存中查询是否有可用记录,如果没有,指令被解析器解析再进行优化,然后送到存储引擎对数据库文件中的数据进行处理。
数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以 获得特定的功能。 本文描述内容仅在MariaDB10.3.27中进行了验证。
MariaDB root@(none):hellodb> select version(); +-----------------+ | version() | +-----------------+ | 10.3.27-MariaDB | +-----------------+ 1 row in set Time: 0.007s
查看MariaDB10.3.27支持的存储引擎
MariaDB root@(none):hellodb> show engines; +--------------------+---------+----------------------------------------------------------------------------------+--------------+-----+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------------+--------------+-----+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | Stores tables as CSV files | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO | | ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO | | FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES | | InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------------+--------------+-----+------------+
查看系统正在使用的存储引擎
MariaDB root@(none):hellodb> show variables like 'default_storage_engine'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+ 1 row in set Time: 0.007s
同的存储引擎都有各自的特点,以适应不同的需求,如表所示
功能 | MylSAM | MEMORY | InnoDB | Archive |
---|---|---|---|---|
存储限制 | 256TB | RAM | 64TB | None |
支持事务 | No | No | Yes | No |
支持全文索引 | Yes | No | No | No |
支持树索引 | Yes | Yes | Yes | No |
支持哈希索引 | No | Yes | No | No |
支持数据缓存 | No | N/A | Yes | No |
支持外键 | No | No | Yes | No |
可以根据以下的原则来选择 MySQL 存储引擎:
MyISAM
使用这个存储引擎,每个MyISAM在磁盘上存储成三个文件。
(1)frm文件:存储表的定义数据
(2)MYD文件:存放表具体记录的数据
(3)MYI文件:存储索引
frm和MYI可以存放在不同的目录下。MYI文件用来存储索引,但仅保存记录所在页的指针,索引的结构是B+树结构。下面这张图就是MYI文件保存的机制:
从这张图可以发现,这个存储引擎通过MYI的B+树结构来查找记录页,再根据记录页查找记录。并且支持全文索引、B树索引和数据压缩。
支持数据的类型也有三种:
(1)静态固定长度表
这种方式的优点在于存储速度非常快,容易发生缓存,而且表发生损坏后也容易修复。缺点是占空间。
(2)动态可变长表
优点是节省空间,但是一旦出错恢复起来比较麻烦。
(3)压缩表
上面说到支持数据压缩,说明肯定也支持这个格式。在数据文件发生错误时候,可以使用check table工具来检查,而且还可以使用repair table工具来恢复。
有一个重要的特点那就是不支持事务,但是这也意味着他的存储速度更快,如果你的读写操作允许有错误数据的话,只是追求速度,可以选择这个存储引擎。
MyISAM引擎特点
MySQL5.5.5前默认的数据库引擎
MyISAM存储引擎适用场景
InnoDB是默认的数据库存储引擎,他的主要特点有:
(1)可以通过自动增长列,方法是auto_increment。
(2)支持事务。默认的事务隔离级别为可重复度,通过MVCC(并发版本控制)来实现的。
(3)使用的锁粒度为行级锁,可以支持更高的并发。
(4)支持外键约束;外键约束其实降低了表的查询速度,但是增加了表之间的耦合度。
(5)配合一些热备工具可以支持在线热备份;
(6)在InnoDB中存在着缓冲管理,通过缓冲池,将索引和数据全部缓存起来,加快查询的速度;
(7)对于InnoDB类型的表,其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块,都位于B+数的叶子节点上。
当然InnoDB的存储表和索引也有下面两种形式:
(1)使用共享表空间存储:所有的表和索引存放在同一个表空间中。
(2)使用多表空间存储:表结构放在frm文件,数据和索引放在IBD文件中。分区表的话,每个分区对应单独的IBD文件,分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率。
对于InnoDB来说,最大的特点在于支持事务。但是这是以损失效率来换取的。
InnoDB引擎特点
缓存SELECT操作或预处理查询的结果集和SQL语句,当有新的SELECT语句或预处理查询语句请求,先去查询缓存,判断是否存在可用的记录集,判断标准:与缓存的SQL语句,是否完全一样(比较select语句的hash值),区分大小写。
此外,不是所有的SQL语句都是能够被缓存的,比如:
查询缓存相关的服务器变量
SELECT语句的缓存控制
查询缓存相关的状态变量
命中率和内存使用率估算
查询缓存中内存块的最小分配单位query_cache_min_res_unit :
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
查询缓存命中率 :
Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
查询缓存内存使用率:
(query_cache_size – qcache_free_memory) / query_cache_size * 100%
查询优化路径图
根据优化路径,结合上面内容进行查询优化配置。尽管MySQL Query Cache旨在提高性能,但它存在严重的可伸缩性问题,并且很容易成为严重的瓶颈。自MySQL 5.6(2013)以来,默认情况下已禁用查询缓存,其不能与多核计算机上在高吞吐量工作负载情况下进行扩展。目前大多数应用都把缓存做到了应用逻辑层,比如:使用redis或者memcache。
MySQL 的日志类型主要包括以下几种:
3.1 Innodb事务日志相关配置:
https://mariadb.com/kb/en/full-list-of-mariadb-options-system-and-status-variables/
在此网站可以确定是参数是服务器选项、全局变量还是会话变量。服务器选项需要在写在配置文件中(/etc/my.cnf /etc/my.cnf.d/*.cnf),并且需要重启服务(systemctl restart mariadb)。
MariaDB root@(none):(none)> 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 | ./ | | innodb_log_optimize_ddl | OFF | | innodb_log_write_ahead_size | 8192 | +-----------------------------+----------+ 8 rows in set Time: 0.020s
事务日志性能优化
innodb_flush_log_at_trx_commit=0|1|2
1 此为默认值,日志缓冲区将写入日志文件,并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性。此模式安全性最高,性能最差。
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能,但服务器崩溃可能丢失最后一秒的事务。
2 每次提交后都会写入OS的缓冲区,但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些,但操作系统或停电可能导致最后一秒的交易丢失。
配置为2和配置为0,性能差异并不大,因为将数据从Log Buffer拷贝到OS cache,虽然跨越用户态与内核态,但毕竟只是内存的数据拷贝,速度很快。但二者安全性差异巨大,操作系统崩溃的概率相比MySQL应用程序崩溃的概率,小很多,设置为2,只要操作系统不奔溃,也绝对不会丢数据。
MariaDB root@(none):(none)> show variables like 'innodb_flush_log_at_trx_commit'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ 1 row in set Time: 0.018s
3.2 错误日志
错误日志中记录了mysqld启动和关闭过程中输出的事件信息,mysqld运行中产生的错误信息,event scheduler运行一个event时产生的日志信息,在主从复制架构中的从服务器上启动从服务器线程时产生的信息等。
#错误日志位置 MariaDB root@(none):(none)> show variables like 'log_error'; +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | log_error | /var/log/mariadb/mariadb.log | +---------------+------------------------------+ 1 row in set Time: 0.019s #记录哪些警告信息至错误日志文件 MariaDB root@(none):(none)> show variables like 'log_warnings'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_warnings | 2 | +---------------+-------+ 1 row in set Time: 0.019s
https://mariadb.com/kb/en/server-system-variables/#log_warnings
上面链接描述了1 2 3 4 9分别记录了那些内容。
3.3 通用日志
通用日志:记录对数据库的通用操作,包括:错误的SQL语句。通用日志可以保存在:file(默认值)或 table(mysql.general_log表)。
通用日志相关设置:
general_log=ON|OFF 开启、关闭通用日志
general_log_file=HOSTNAME.log 通用日志文件名字
log_output=TABLE|FILE|NONE 日志记录到何处
mariadb10.3.27通用日志相关参数默认值
MariaDB root@(none):(none)> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | OFF | +---------------+-------+ 1 row in set Time: 0.019s MariaDB root@(none):(none)> show variables like 'general_log_file'; +------------------+------------+ | Variable_name | Value | +------------------+------------+ | general_log_file | master.log | +------------------+------------+ 1 row in set Time: 0.025s MariaDB root@(none):(none)> show variables like 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+ 1 row in set Time: 0.023s
3.4 慢查日志
慢查询日志记录执行查询时长超出指定时长的操作,慢查询相关变量:
slow_query_log=ON|OFF #开启或关闭慢查询,支持全局和会话,只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值,单位秒
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 版后已删除
MariaDB root@(none):(none)> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | OFF | +----------------+-------+ 1 row in set Time: 0.026s MariaDB root@(none):(none)> show variables like 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set Time: 0.018s MariaDB root@(none):(none)> show variables like 'slow_query_log_file'; +---------------------+-----------------+ | Variable_name | Value | +---------------------+-----------------+ | slow_query_log_file | master-slow.log | +---------------------+-----------------+ 1 row in set Time: 0.026s MariaDB root@(none):(none)> show variables like 'log_slow_filter'; +-----------------+--------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +-----------------+--------------------------------------------------------------------------------------------------------------------------------------+ | log_slow_filter | admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | +-----------------+--------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set Time: 0.016s MariaDB root@(none):(none)> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF | +-------------------------------+-------+ 1 row in set Time: 0.020s MariaDB root@(none):(none)> show variables like 'log_slow_rate_limit'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | log_slow_rate_limit | 1 | +---------------------+-------+ 1 row in set Time: 0.012s MariaDB root@(none):(none)> show variables like 'log_slow_verbosity'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | log_slow_verbosity | | +--------------------+-------+ 1 row in set Time: 0.025s
慢查询分析工具的使用
打开后,会显示语句执行详细的过程
set profiling = ON
查看语句,注意结果中的query_id值
show profiles ;
显示语句的详细执行步骤和时长
Show profile for query #
显示cpu使用情况
Show profile cpu for query #
3.5 二进制日志
功能:通过“重放”日志文件中的事件来生成数据副本
注意:建议二进制日志和数据文件分开存放
二进制日志记录三种格式
MariaDB root@(none):(none)> show variables like 'binlog_format'; Reconnecting... +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | MIXED | +---------------+-------+ 1 row in set Time: 0.017s
二进制日志相关的服务器变量
sql_log_bin=ON|OFF: #是否记录二进制日志,默认ON,支持动态修改,系统变量,而非服务器选项
log_bin=/PATH/BIN_LOG_FILE: #指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED: #二进制日志记录的格式,默认STATEMENT
max_binlog_size=1073741824: #单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G,文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小(每次连接)
max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0: #设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘
expire_logs_days=N: #二进制日志可以自动删除的天数。 默认为0,即不自动删除
MariaDB root@(none):(none)> show variables like 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set Time: 0.028s
二进制日志文件的构成
[root@master my.cnf.d]# vim mariadb-server.cnf [mysqld] log_bin=/data/maradb-bin [root@master data]# ls -l total 8 -rw-rw---- 1 mysql mysql 329 Apr 10 05:27 maradb-bin.000001 -rw-rw---- 1 mysql mysql 24 Apr 10 05:27 maradb-bin.index
查看mariadb自行管理使用中的二进制日志文件列表,及大小
SHOW {BINARY | MASTER} LOGS
MariaDB root@(none):(none)> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | maradb-bin.000001 | 329 | +-------------------+-----------+ 1 row in set Time: 0.008s
查看使用中的二进制日志文件
SHOW MASTER STATUS
MariaDB root@(none):(none)> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | maradb-bin.000001 | 329 | | | +-------------------+----------+--------------+------------------+ 1 row in set Time: 0.017s
在线查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
MariaDB root@(none):(none)> SHOW BINLOG EVENTS in 'maradb-bin.000001'; +-------------------+-----+-------------------+-----------+-------------+------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+-------------------+-----------+-------------+------------------------------------------------+ | maradb-bin.000001 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.27-MariaDB-log, Binlog ver: 4 | | maradb-bin.000001 | 256 | Gtid_list | 1 | 285 | [] | | maradb-bin.000001 | 285 | Binlog_checkpoint | 1 | 329 | maradb-bin.000001 | +-------------------+-----+-------------------+-----------+-------------+------------------------------------------------+ 3 rows in set Time: 0.011s
离线二进制日志的客户端命令工具
mysqlbinlog [OPTIONS] log_file…
--start-position=# 指定开始位置
--stop-position=#
--start-datetime= #时间格式:YYYY-MM-DD hh:mm:ss
--stop-datetime=
--base64-output[=name]
-v -vvv
[root@master data]# mysqlbinlog --start-position=285 --stop-position=329 /data/maradb-bin.000001 -v /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #210410 5:27:06 server id 1 end_log_pos 256 CRC32 0x1be1cb3e Start: binlog v 4, server v 10.3.27-MariaDB-log created 210410 5:27:06 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' am9xYA8BAAAA/AAAAAABAAABAAQAMTAuMy4yNy1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABqb3FgEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAEEwQADQgICAoKCgE+y+Eb '/*!*/; # at 285 #210410 5:27:06 server id 1 end_log_pos 329 CRC32 0xbb19b113 Binlog checkpoint maradb-bin.000001 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@master data]#
切换日志文件
FLUSH LOGS;
清除与删除二进制日志操作要非常慎重
清除指定二进制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
删除所有二进制日志,index文件重新记数
RESET MASTER [TO #]; #删除所有二进制日志文件,并重新生成日志文件,文件名从#开始记数,默认从
1开始,一般是master主机第一次启动时执行,MariaDB 10.1.6开始支持TO #