mysql知识点

hi wei

已于 2022-06-07 16:38:25 修改

阅读量305

点赞数 5

分类专栏：数据库文章标签： mysql 数据库服务器

于 2022-05-17 10:27:37 首次发布

本文链接：https://blog.csdn.net/nianqrzhanghw/article/details/118194746

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

mysql 架构

在这里插入图片描述

连接器

mysql的连接器负责处理mysql客户端的连接请求及维护连接。

传输协议

mysql支持多种传输协议，不同的平台可以选择不同的协议：
在这里插入图片描述

连接压缩控制

mysql建立的连接可以对客户端和服务器之间的流量进行压缩，以减少通过连接发送的字节数。默认情况下，连接是未压缩的。启用连接压缩会导致客户端和服务端CPU负载上升，因为双方都要执行压缩和解压缩操作。但是在网络带宽较低，结果集很大的情况下可以考虑使用连接压缩。
参数配置：

客户端程序–compress来指定使用压缩连接到服务器。
对于使用mysql C API 的程序，启用MYSQL_OPT_COMPRESS的mysql_options来指定。
对于主从之间的机器，启用slave_compressed_protocol 系统变量指定master的连接使用压缩。
在每种情况下，当指定使用zlib压缩时，如果双方都支持，则连接使用压缩算法，否则回退到未压缩的连接。

解析器

mysql解析器主要是对sql语句进行解析。如果要从事数据库开发工作，对于语句解析知识的学习必不可少，可以了解的框架有：antlr和calcite。

优化器

优化器即查询优化器，查询优化器的任务就是找到执行SQL查询的最佳计划。一条sql的执行在不同的执行计划之间的性能差异可能是几秒几分钟甚至几小时。大多数查询优化器，包括mysql，是在所有可能的查询计划中或多或少的进行穷举搜索最佳执行计划。对于查询计划的数量，会随着查询表的数量增长而呈指数级增长，在这么多的查询计划中进行穷举搜索，查询优化所耗的时间就可能成为主要的瓶颈。
所以，查询计划越少，查询最优计划的耗时就越少。可以跳过一些计划，但是可能会出现错过最优查询计划。

mysql使用两个参数来控制优化器

optimizer_prune_level：默认开启。开启后优化器会跳过一些计划，大大减少了查询最优计划的时间。根据mysql官方指出，错过最优计划的概率很小，所以默认开启。如果认为错过了最优计划，可以关闭该配置，但是查询优化的时间可能会增长。请注意，即使使用此启发式方法，优化器探索仍会大致呈指数级数量的计划。
optimizer_search_depth：在多表join的场景下，为了避免优化器占用太多时间，MySQL提供了一个参数 optimizer_search_depth 来控制递归深度。例如：20张表join联查，当depth = 4时，优化器只取前4个表来进行排列优化搜索。

优化器配置

命令optimizer_switch可以配置优化器参数:

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,
                    index_merge_intersection=on,
                    engine_condition_pushdown=on,
                    index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,
                    block_nested_loop=on,batched_key_access=off,
                    materialization=on,semijoin=on,loosescan=on,
                    firstmatch=on,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,
                    condition_fanout_filter=on,derived_merge=on,
                    prefer_ordering_index=on

设置：

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

优化器模型

数据库常用的优化器模型有：

RBO(Rule-Base Optimization)：基于规则的优化器
该优化器按照硬编码在数据库中的一系列规则来决定SQL的执行计划。
以Oracle数据库为例，RBO根据Oracle指定的优先顺序规则，对指定的表进行执行计划的选择。比如在规则中：索引的优先级大于全表扫描。通过Oracle的这个例子我们可以感受到，在RBO中，有着一套严格的使用规则，只要你按照规则去写SQL语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”，也就是说RBO对数据不“敏感”。这就要求开发人员非常了解RBO的各项细则，不熟悉规则的开发人员写出来的SQL性能可能非常差。
但在实际的过程中，数据的量级会严重影响同样SQL的性能，这也是RBO的缺陷所在。毕竟规则是死的，数据是变化的，所以RBO生成的执行计划往往是不可靠的，不是最优的。
CBO(Cost-Base Optimization)：基于成本的优化器
该优化器通过根据优化规则对关系表达式进行转换，生成多个执行计划，然后CBO会通过根据统计信息(Statistics)和代价模型(Cost Model)计算各种可能“执行计划”的“代价”，即COST，从中选用COST最低的执行方案，作为实际运行方案。
CBO依赖数据库对象的统计信息，统计信息的准确与否会影响CBO做出最优的选择。
以Oracle数据库为例，统计信息包括SQL执行路径的I/O、网络资源、CPU的使用情况。
目前各大数据库和大数据计算引擎都倾向于使用CBO，例如从Oracle 10g开始，Oracle已经彻底放弃RBO，转而使用CBO；而Hive在0.14版本中也引入了CBO。

mysql使用CBO作为优化器。

执行器

mysql通过解析器知道了要做什么，通过优化器知道了该怎么做，现在要mysql就要去通过执行器来干活了。在干活之前要做一些权限验证及表、字段等验证。验证全部通过之后，执行器就会执行数据库操作。

Innodb存储引擎

架构：
在这里插入图片描述

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎。在 MySQL 8.0 中，InnoDB是默认的 MySQL 存储引擎。除非您配置了不同的默认存储引擎，否则发出CREATE TABLE不带ENGINE 子句的语句会创建一个InnoDB表。

Innodb的主要优势

DML操作支持ACID模型，事务具有提交、回滚和崩溃恢复功能。
支持行锁，提高了并发能立。
使用B+ tree作为索引，每个表都有一个聚簇索引。
支持外键约束来保证数据完整性。

Innodb内存结构

缓冲池

缓冲池是主内存中的一片区域，用于在Innodb访问时缓存表和索引数据。缓冲池允许直接从内存访问数据，从而加快处理速度。在专用服务器上，多达80%的物理内存会分配给缓冲池。
缓冲池中存储的是多个数据页，在Innodb中一个数据页默认是16k。数据页在缓冲池中以链表的形式组合存储，采用LRU算法淘汰数据页。
缓冲池结构：
在这里插入图片描述

缓冲池的3/8用于旧数据页。
中位点是位于new sublist和old sublist的交界处。
当数据页第一次读取时，会将数据插入到中位点，也就是old sublist的head处。数据页的访问会使old sublist中的数据变得年轻，而移动到new sublist中去。
随着数据库得运行，很久未被读取的数据会被逐渐老化，最后淘汰。

优化点

默认情况下，查询读取会立即将数据页刷新到new sublist，所以当查询不带where条件的时候，大量数据页都会刷新到缓冲池，即使后面不会再使用。那如何优化呢？

使缓冲池扫描具有抵抗力
配置Innodb缓冲池预读

更改缓冲区（change buffer）

change buffer是一种在buffer pool中特殊的数据存储空间。当Innodb的二级索引页不在buffer pool中的时候，change buffer会缓存对二级索引的更改操作（insert、update、delete），稍后在其它读取操作的时候，这些数据页会合并到buffer pool中去。在大量的进行增删改的应用场景下，使用change buffer能减少频繁的磁盘I/O操作，显著提高了性能。
在这里插入图片描述

为什么二级索引使用change buffer？
因为二级索引通常为非唯一索引，二级索引的修改往往是随机插入的，对二级索引操作，可能会影响索引树上不相邻的数据页，所以这些都是随机I/O，速度较慢。mysql对二级索引的操作缓存在change buffer中，待下次读取的时候再进行合并，就能显著提高操作效率。
什么时候change buffer会合并？

当数据页被读取进缓冲池时，该数据页会被读取完成后合并，然后数据页可用。
后台任务执行合并。
在崩溃恢复期间执行更改缓冲区合并。当索引页被读入缓冲池时，更改从更改缓冲区（在系统表空间中）应用到二级索引的叶页。
更改缓冲区是完全持久的，可以在系统崩溃时幸免于难。重新启动后，更改缓冲区合并操作将作为正常操作的一部分恢复。
作为缓慢服务器关闭的一部分，可以使用强制完全合并更改缓冲区 --innodb-fast-shutdown=0。

mysql异常宕机了，change buffer中的数据怎么办？
change buffer是完全持久化的。

自适应哈希索引

哈希索引使用索引键的前缀构建索引，前缀可以是任意长度。如果一个表几乎完全适合主内存（表内数据能完全读入内存，且数据经常被访问），哈希索引通过启用任何元素直接查找来加速，将索引值转换为某种指针。Innodb具有监视索引搜索的机制。如果Innodb注意到查询可以从构建哈希索引中受益，他会自动这样做。哈希索引适用于按值匹配的搜索，如=或者<=>。

日志缓冲区（Log buffer）

日志缓冲区是保存要写入磁盘上日志文件的数据的内存区域。日志缓冲区大小由innodb_log_buffer_size变量定义。默认大小为 16MB。日志缓冲区的内容会定期刷新到磁盘。大型日志缓冲区使大型事务能够运行，而无需在事务提交之前将重做日志数据写入磁盘。因此，如果您有更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。
该 innodb_flush_log_at_trx_commit 变量控制如何将日志缓冲区的内容写入和刷新到磁盘。该 innodb_flush_log_at_timeout 变量控制日志刷新频率。

Innodb中的锁

共享锁和排它锁

共享锁：S锁，读锁。A事务对数据行d1加S锁，B事务只能读取d1数据，不能修改，能继续对的d1加S锁，不能加X锁。
排它锁：X锁，写锁。A事务对数据行d1加S锁，其它事务不能对d1执行任何读写。

意向锁

意向锁是表锁。Innodb支持多粒度锁定，允许行锁与表锁共存。
意向锁主要用来解决，事务对表加表锁需要检测表内行数据是否加行锁的性能损耗。
例如：事务A对表中的一些数据加了行锁，事务B要对表加表锁做一些操作，这时，事务B在加锁前要检查表内数据是否被其它事务加锁了，可以一行一行检查，但是这样的效率太低了。这时意向锁就很好解决了这个问题。事务A可以对表加IX锁，表明自己正在对表内某些数据加锁，当事务B来申请表锁的时候，只需要检查该表是否有IX锁即可，就不用一行行检查了。
表级锁的兼容性：

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

记录锁

记录锁是对索引上锁。例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;可以防止修改该条记录。

SHOW ENGINE INNODB STATUS

查看锁情况。

间隙锁

间隙锁是对索引记录之间的间隙的锁定。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 阻止将数据c1值在10-20之间的数据插入到表中。间隙可能跨越单个索引值、多个索引值，甚至是空的。间隙锁是性能和并发之间权衡的一部分，用于可重复读事务隔离级别。

临键锁（next-key）

临键锁存在可重复读隔离级别下。
假设一个索引包含值 10、11、13 和 20。此索引可能的 next-key 锁涵盖以下区间，其中圆括号表示排除区间端点，方括号表示包含端点：左开右闭

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

插入意向锁

插入意向锁是一种由insert操作之前设置的间隙锁。此锁表示插入意图，如果多个事务插入的数据不在同一个区间内，则不需要相互等待。因为行是不冲突的。

以下演示了插入意向锁：
Client A 创建一个包含两条索引记录（90 和 102）的表，然后启动一个事务，该事务对 ID 大于 100 的索引记录放置排他锁。排他锁包括记录 102 之前的间隙锁：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

客户端 B 开始一个事务以在间隙中插入一条记录。事务在等待获得排他锁时使用插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

自增锁（AUTO-INC 锁）

自增锁使用表级锁AUTO_INCREMENT列。主要用于插入事务能获取连续的主键值。

Innodb中的死锁

死锁演示

事务A：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 50 for update;
+----+------------+---------+------+
| id | uname      | address | age  |
+----+------------+---------+------+
| 50 | ooBQzZxssN | LsFXWO  |  101 |
+----+------------+---------+------+
1 row in set (0.00 sec)

mysql> delete from student where id = 40;
Query OK, 1 row affected (11.99 sec)

mysql>

事务 B：

mysql>  START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 40 for update;
+----+------------+---------+------+
| id | uname      | address | age  |
+----+------------+---------+------+
| 40 | FRriPBnLRY | tWCWdy  |  101 |
+----+------------+---------+------+
1 row in set (0.01 sec)

mysql> delete from student where id = 50;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死锁检测

当启用死锁检测（默认）时，Innodb会自动检测事务死锁并回滚一个或多个事务以打破死锁。Innodb尝试选择回滚小事务，其中事务的大小由插入、修改或更新的行数决定。
如果禁用死锁检测，mysql会根据设置的innodb_lock_wait_timeout来回滚事务。
禁用死锁检测场景：
在开启死锁检测的时候，事务阻塞的时候会循环检测其它事务是否等待本事务持有的锁来检测是否发生死锁，所以比较耗cpu性能。所以在高并发系统上，当大量线程等待同一个锁时，死锁检测会导致速度减慢。有时，禁用死锁检测，采用锁等待超时来回滚事务可能更高效。可以使用innodb_deadlock_detect 变量禁用死锁检测。

如何避免死锁和处理死锁

死锁是数据库事务中经典的问题，但死锁并不危险，除非死锁非常频繁以至于无法运行某些事务。可以使用以下方式来处理死锁并降低死锁发生的可能性：

在发生死锁后，使用SHOW ENGINE INNODB STATUS命令来查看最近死锁的原因，并调整应用程序来避免死锁。
如果频繁的发生死锁，通过启用innodb_print_all_deadlocks变量来收集死锁信息。所有的死锁信息都记录在mysql的错误日志中，完成调试后禁用此选项。
如果是由于死锁而失败，可以再次重新发出事务。死锁并不危险，再试一次。
保持事务小而短，以减少它们冲突的可能性。
在进行一组更改后立即提交事务，避免mysql长时间打开未提交的事务。
如果使用锁定读取（select… for update或者select… for share），可以使用RC隔离级别。
不同的事务操作同一张表时，操作顺序应保持一致。
使用合适的索引，以减少加锁的数量。

事务调度

Innodb采用CATS算法来调度事务。

CATS算法

Contention-Aware Transaction Scheduling，争抢感知事务调度。
可以看下这篇文章：
CATS简介

简单理解就是说，FIFO算法是先来等待锁的事务先获取锁，依次排队，这样算法有很大的优化空间，因为后面的事务获取锁后释放的数据可能比先来的事务释放更多的数据。一个事务操作数据上的锁可能被后面的多个事务等待，所以CATS根据评估事务加锁等待事务数来设置事务权重，这样可以将锁分配给权重大的事务以提高MySQL的吞吐量。如果权重相等，则分配给等待时间更长的事务。

有一个比喻的例子：如果有一个出租车司机和一个公交车司机都在等咖啡，那么先给公交车司机做咖啡（即使公交车司机比出租车司机迟来）可能会让更多的人尽早到达他们的目的地。因为公交车上的乘客比出租车上的乘客多。这看起来似乎对出租车司机不公平，但是这种策略可以使得整个系统运行的更快，这对于系统内的每个人都是有利的。

mysql优化

优化概述

数据库性能取决于多种因素，例如表的结构、查询语句和服务器配置等。
总结起来有三个优化点：

在数据库级别进行优化：如表结构的优化，存储引擎的选择，mysql的配置等。
在硬件级别进行优化：如使用SSD硬盘，CPU和内存优化。
在使用级别优化：优化sql语句，集群架构，读写分离。

优化sql语句

优化select语句

临时表

mysql在执行语句中经常用到临时表，一般情况下用到临时表，就意味着查询效率较低。
临时表只在当前会话有效，会话关闭，临时表删除。临时表分为内存临时表和磁盘临时表，其中内存临时表使memory引擎用的是，磁盘临时表使用的是MyISAM/Innodb引擎。mysql先使用的是内存临时表，当临时表中的数据大于内存临时表最大值时，会转换为磁盘临时表。

临时表是要根据mysql优化评估是否使用的，要根据时间情况，看是否会使用临时表。
使用临时表的场景：

order by和group by字段不同，例如：order by price group by name;
在JOIN查询中，ORDER BY或者GROUP BY使用了不是第一个表的列例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price)

直接使用磁盘临时表的场景：