架构：C/S Connectors: 连接器 可供Native C API、JDBC、ODBC、NET、PHP、Perl、Python、Ruby、Cobol等连接mysql； 站在编程角度可以理解为连入数据库管理系统的驱动，站在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.htmlMyISAM
- 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运行中的统计数据的变量，不可更改
服务器变量 SQL_MODE
特点
- 对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置
- 根据设置的选项值不同，同样的sql语句可能会有不同的查询结果以及报错方式
- 常见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/java20201. 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在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. 在有大量记录的表分页时使用limit14. 对于经常使用的查询，可以开启查询缓存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: NULL1 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: NULL1 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: NULL1 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: 0mysql> 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: NULL2 rows in set (0.00 sec)ERROR: No query specifiedmysql> 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 condition1 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: 0mysql> 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: NULL1 row in set (0.00 sec)ERROR: No query specifiedEXPLAIN 工具
可以通过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: NULL1 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执行性能
并发控制
机制：
- 读锁：共享锁，也称为 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]----------------------------------------------------------------------------#范例1mysql> lock tables students read ;Query OK, 0 rows affected (0.00 sec)mysql> update students set classid=2 where stuid=24; --> 会话1ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't beupdatedmysql> 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操作极大的减少。就像中国式过马路，凑够一拨人就过，比一个一个的过效率更快
#显式启动事务：BEGINBEGIN WORKSTART TRANSACTION#结束事务：#提交,相当于vi中的wq保存退出COMMIT#回滚,相当于vi中的q!不保存退出ROLLBACK#自动提交：set autocommit={1|0} --> 建议：显式请求和提交事务，而不要使用"自动提交"功能#事务支持保存点：SAVEPOINT identifierROLLBACK [WORK] TO [SAVEPOINT] identifierRELEASE 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 sec2 lock struct(s), heap size 1136, 28 row lock(s), undo log entries 27MySQL thread id 13, OS thread handle 139719808595712, query id 206 localhostroot...省略...#此指令不支持MySQL8.0MariaDB [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.0MariaDB [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: 123trx_state: LOCK WAITtrx_started: 2019-11-22 19:17:06trx_requested_lock_id: 123:9:3:2trx_wait_started: 2019-11-22 19:18:50trx_weight: 2trx_mysql_thread_id: 15 #线程IDtrx_query: update students set classid=20trx_operation_state: starting index readtrx_tables_in_use: 1trx_tables_locked: 1trx_lock_structs: 2trx_lock_memory_bytes: 1136trx_rows_locked: 2trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_is_read_only: 0trx_autocommit_non_locking: 0*************************** 2. row ***************************trx_id: 120trx_state: RUNNINGtrx_started: 2019-11-22 19:08:51trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 29trx_mysql_thread_id: 13 #线程IDtrx_query: NULLtrx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 1trx_lock_structs: 2trx_lock_memory_bytes: 1136trx_rows_locked: 28trx_rows_modified: 27trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_is_read_only: 0trx_autocommit_non_locking: 02 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 purgecoordinator | 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 shutdownhandler | 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)#查看事务锁的超时时长，默认50sMariaDB [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.0select @@transaction_isolationmysql> 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.0SET 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总用量 92156drwx------ 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 ibtmp1drwxr-x--- 2 mysql mysql 4096 11月 9 21:34 '#innodb_redo'/ --> 存放数据，里面有redologdrwxr-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|2mysql> select @@innodb_flush_log_at_trx_commit;+----------------------------------+| @@innodb_flush_log_at_trx_commit |+----------------------------------+| 1 |+----------------------------------+1 row in set (0.00 sec)
error log 错误日志
- mysqld启动和关闭过程中输出的事件信息
- mysqld运行中产生的错误信息
- event scheduler运行一个event时产生的日志信息
- 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
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.sockTime Id Command Argument2022-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.sockTime Id Command Argument2022-11-12T09:19:41.785677Z 9 Query select @@general_log2022-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: 0Max_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 log1 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: 33 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|head4 "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 #慢查询的阀值，单位秒,默认为10sslow_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 --helpUsage: 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.logReading mysql slow query log from /var/lib/mysql/centos8-slow.logCount: 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]@localhostselect * from students where age=NCount: 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]@localhostselect sleep(N) from teachers --> 执行效率较低，可用explain分析索引使用情况binary log 二进制日志(备份)*
- 记录导致数据改变或潜在导致数据改变的SQL语句
- 记录已提交的日志
- 不依赖于存储引擎类型
事务日志和二进制日志区别
- 事务日志在线,二进制离线
- 事务日志记录事务执行的过程,包括提交和未提交,二进制日志记录只记提交的过程
- 事务日志只支持innodb,二进制都支持MyiSAM和innoDB
功能：通过"重放"日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放
具体的配置方式将和MySQL 备份和恢复章节一起整理。
我是moore 大家一起加油！