深圳做网站公司排名,ui设计需要学哪些内容,网络营销证书有什么用,如何设计产品网站建设MySQL知识整理 基础第一讲#xff1a;基础架构#xff1a;一条SQL查询语句是如何执行的#xff1f;架构尽量减少长连接的原因和方案为什么尽量不要依赖查询缓存 索引第四讲#xff1a;深入浅出索引#xff08;上#xff09;第五讲#xff1a;深入浅出索引#xff08;下… MySQL知识整理 基础第一讲基础架构一条SQL查询语句是如何执行的架构尽量减少长连接的原因和方案为什么尽量不要依赖查询缓存 索引第四讲深入浅出索引上第五讲深入浅出索引下对覆盖索引的理解最左前缀原则索引下推 第九讲普通索引和唯一索引应该怎么选择changebuffer及其应用场景普通索引和唯一索引应该怎么选择 第十讲MySQL为什么有时候会选错索引MySQL优化器选错索引的解决方法 第十一讲怎么给字符串字段加索引字符串应该使用什么类型索引 第十六讲“orderby”是怎么工作的全字段排序 VS rowid排序 第十八讲为什么这些SQL语句逻辑相同性能却差异巨大 事务第三讲事务隔离为什么你改了我还看不见事务隔离级别 第八讲事务到底是隔离的还是不隔离的 锁第六讲全局锁和表锁 给表加个字段怎么有这么多阻碍第七讲行锁功过怎么减少行锁对性能的影响全局锁表级锁行锁两阶段锁协议死锁 第二十讲幻读是什么幻读有什么问题并发事务中存在的问题如何解决幻读间隙锁临键锁Next-Key Lock 日志与主备第二讲日志系统一条SQL更新语句是如何执行的redo log 和 binlog 的不同 第十五讲答疑文章一日志和索引相关问题MySQL崩溃恢复时的判断规则两阶段提交的不同时刻在MySQL异常重启会出现什么现象 临时表第十三讲为什么表数据删掉一半表文件大小不变数据库中收缩表空间的方法 第三十四讲 到底可不可以使用join第三十五讲join语句怎么优化 实用性第十四讲count(*)这么慢我该怎么办count(*)在不同引擎中的实现方法不同count的性能问题select count(?) from t 第三十二讲为什么还有kill不掉的语句 基础
第一讲基础架构一条SQL查询语句是如何执行的
架构 MySQL分为Server层和存储引擎层两部分。 Server层包括连接器、查询缓存在MySQL8.0被移除、分析器、优化器、执行器等涵盖MySQL的大多数核心服务功能以及所有的内置函数如日期、时间、数学和加密函数等所有跨存储引擎的功能都在这一层实现比如存储过程、触发器、视图等。 存储引擎层负责数据的存储和提取。其架构模式是插件式的支持InnoDB、MyISAM、Memory等多个存储引擎。从MySQL5.5.5版本开始InnoDB成为了默认存储引擎。InnoDB提供了事务支持、行级锁定和外键约束等功能非常适合处理大量数据的应用程序。 可以在创建表的时候选择不同的存储引擎例如CREATETABLEmy_table(…)ENGINEInnoDB;
从上图可以看出不同的存储引擎共用一个Server层也就是从连接器到执行器的部分。
尽量减少长连接的原因和方案
长连接是指连接成功后如果客户端持续有请求则一直使用同一个连接。 短连接则是指每次执行完很少的几次查询就断开连接下次查询再重新建立一个。 长连接可以减少频繁建立连接的开销但它可能导致随着时间和数据交换的增加数据库占用的内存越来越大因为每个连接都会消耗资源。如果不及时释放资源确实可能会出现内存溢出的情况导致操作系统为了保护系统稳定性而触发OOMOutOfMemory机制杀掉占用大量内存的进程MySQL就可能会因此异常重启。 1.定期断开长连接或者定期重置连接通过执行mysql_reset_connection()来重新初始化连接资源。这个过程不需要重连和重新做权限验证但是会将连接恢复到刚刚创建完时的状态。 2.设置非交互式连接编程在关闭之前等待活动的秒数wait_timeout 3.设置交互式连接shell未发出任何请求并处于空闲状态时超过interactive_timeout设置的时间后MySQL服务器会自动关闭该连接 4.通过定期执行FLUSHHOSTS来清理错误的连接 5.在编程中使用连接池的策略。连接池可以提供自动的连接管理功能包括最大连接数的限制、连接的创建和销毁、连接的获取和释放并且可以自动处理长时间不活跃连接的断开和重置。
为什么尽量不要依赖查询缓存
查询缓存的失效非常频繁只要有对一个表的更新这个表上所有的查询缓存都会被清空。MySQL也提供了这种“按需使用”的方式。你可以将参数query_cache_type设置成DEMAND这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句用SQL_CACHE显式指定
select SQL_CACHE * from T where ID 10MySQL8.0开始没有查询缓存功能
索引
第四讲深入浅出索引上
主键索引的叶子节点存的是整行数据。在InnoDB里主键索引也被称为聚簇索引。 非主键索引的叶子节点内容是主键的值。在InnoDB里非主键索引也被称为二级索引。
第五讲深入浅出索引下
回表非主键索引树回到主键索引树的过程
对覆盖索引的理解
覆盖索引是数据库查询优化中的一个概念指的是一个索引包含了查询所需要的所有数据因此查询可以直接通过索引来获取结果而无需再回表查询实际的数据行。在InnoDB存储引擎中使用覆盖索引可以大大提高查询效率由于数据可以直接从索引中获取数据库不必执行额外的I/O操作去读取数据表中的行。
最左前缀原则
在建立联合索引的时候如何安排索内的字段顺序 第一原则是如果通过调整顺序可以少维护一个索引那么这个顺序往往就是需要优先考虑采用的。 再就是需要考虑索引的空间了如果既有联合查询又有基于a、b各自的查询将字段小的单独建索引
索引下推
在索引遍历过程中对索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。
第九讲普通索引和唯一索引应该怎么选择
changebuffer及其应用场景
Changebuffer是InnoDB存储引擎中的一个特殊数据结构其主要用途是缓存对二级索引的修改操作非唯一索引的插入、更新或删除操作减少磁盘I/O操作并提升性能。当对二级索引进行修改时如果对应的索引页不在缓冲池中比如在磁盘上使用changebuffer可以避免立即从磁盘读取索引页从而提高性能。 在下次查询需要访问这个数据页的时候将数据页读入内存然后执行changebuffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。 将changebuffer中的操作应用到原数据页得到最新结果的过程称为merge。除了访问这个数据页会触发merge外系统有后台线程会定期merge。在数据库正常关闭shutdown的过程中也会执行merge操作。
当表中的非唯一索引用得比较少或者是I/O系统非常快时changebuffer带来的性能提升可能就不那么明显。
普通索引和唯一索引应该怎么选择
两类索引在查询能力上是没差别的主要考虑的是对更新性能的影响。普通索引和changebuffer的配合使用对于数据量大的表的更新优化还是很明显的。
第十讲MySQL为什么有时候会选错索引
MySQL优化器选错索引的解决方法
1.对于由于索引统计信息不准确导致的问题你可以用analyzetable来解决。 2.对于其他优化器误判的情况你可以在应用端用forceindex来强行指定索引
第十一讲怎么给字符串字段加索引
字符串应该使用什么类型索引
1.直接创建完整索引这样可能比较占用空间 2.创建前缀索引节省空间但会增加查询扫描次数并且不能使用覆盖索引 3.倒序存储再创建前缀索引用于绕过字符串本身前缀的区分度不够的问题例如身份证 4.创建hash字段索引查询性能稳定有额外的存储和计算消耗跟第三种方式一样都不支持范围扫描。
第十六讲“orderby”是怎么工作的
MySQL会给每个线程分配一块内存用于排序称为sort_buffer。sort_buffer_size就是MySQL为排序开辟的内存sort_buffer的大小。如果要排序的数据量小于sort_buffer_size排序就在内存中完成。但如果排序数据量太大内存放不下则不得不利用磁盘临时文件辅助排序
全字段排序 VS rowid排序
1.如果MySQL认为内存足够大会优先选择全字段排序把需要的字段都放到sort_buffer中这样排序后就会直接从内存里面返回查询结果了不用再回到原表去取数据。 2.如果MySQL实在是担心排序内存太小会影响排序效率才会采用rowid排序算法这样排序过程中一次可以排序更多行但是需要再回到原表去取数据。
第十八讲为什么这些SQL语句逻辑相同性能却差异巨大
索引字段做函数操作可能会破坏索引值的有序性因此优化器就决定放弃走树搜索功能。 字符集utf8mb4是utf8的超集所以当这两个类型的字符串在做比较的时候MySQL内部的操作是先把utf8字符串转成utf8mb4字符集再做比较。
事务
第三讲事务隔离为什么你改了我还看不见
事务就是要保证一组数据库操作要么全部成功要么全部失败。在MySQL中事务支持是在引擎层实现的。
事务隔离级别
1.读未提交readuncommitted一个事务还没提交时它做的变更就能被别的事务看到。最低的隔离级别可能会导致脏读、幻读或不可重复读 2.读提交readcommitted一个事务提交之后它做的变更才会被其他事务看到。Oracle默认隔离级别 3.可重复读repeatableread一个事务执行过程中看到的数据总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下未提交变更对其他事务也是不可见的。InnoDB默认隔离级别 4.串行化serializable对于同一行记录“写”会加“写锁”“读”会加“读锁”。
第八讲事务到底是隔离的还是不隔离的
begin/start transaction 命令并不是一个事务的起点在执行到它们之后的第一个操作 InnoDB 表的语句事务才真正启动。 在可重复读隔离级别下事务在启动的时候就“拍了个快照”。注意这个快照是基于整库的。 更新数据都是先读后写的而这个读只能读当前的值称为“当前读”currentread。
锁
第六讲全局锁和表锁 给表加个字段怎么有这么多阻碍
第七讲行锁功过怎么减少行锁对性能的影响
全局锁
对数据库实例加锁。应用于全库逻辑备份。命令是 Flush tables with read lock (FTWRL)。整个库处于只读状态其他线程的以下语句会被阻塞数据更新语句数据的增删改、数据定义语句包括 建表、修改表结构等和更新类事务的提交语句。
表级锁
表锁一般是在数据库引擎不支持行锁的时候才会被用到的。在MySQL中表级别的锁主要有两种类型 **表锁可以通过LOCK TABLES … READ/WRITE语法来实现这个命令可以限制其他线程对特定表的读写操作。**比如当你在一个线程中使用LOCK TABLES t1 READ, t2 WRITE;时别的线程对t1的写操作或对t2的读写操作都会被阻塞。同时持有锁的线程也受到限制它只能对t1进行读操作对t2进行读写操作。 元数据锁Metadata Lock, MDL是另一种表级锁它被自动加上而不需要显式的操作。**MDL的目的是确保在表被访问时其结构不会发生变化。**例如在读取或写入表数据时会加上MDL读锁而在执行表结构变更操作时会加上MDL写锁。这样可以确保数据的一致性和安全性。MDL的读锁之间不会相互阻塞而MDL的读锁和写锁之间、写锁和写锁之间则是互斥的。
行锁
InnoDB存储引擎的行锁是自动加上的。当你执行一个涉及数据修改的语句比如UPDATE或DELETEInnoDB会对所涉及的数据行自动加锁以防止其他事务并发修改这些同一行的数据。
如果你想显式地设置某些锁的级别可以在事务中设置隔离级别例如
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;这样可以控制锁的行为防止其他的事务干扰当前事务。 还有一些特殊的锁定读操作比如SELECT … FOR UPDATE和SELECT … LOCK IN SHARE MODE这些操作会在读取数据的同时加上行锁或共享锁以防止数据在事务结束前被其他事务修改或获取不一致的数据。 总的来说InnoDB行锁的使用大部分是自动的依赖于InnoDB的事务和隔离级别机制而无需手动控制。
两阶段锁协议
两阶段锁协议是一种在事务性数据库系统中维持数据一致性和事务隔离的锁定协议。它规定了一个事务在其生命周期内如何对数据加锁和解锁的标准过程分为两个阶段
加锁阶段Growing Phase在这个阶段中事务可以获得所需的任何锁但它不能释放任何锁。
解锁阶段Shrinking Phase在这个阶段事务可以释放所持有的锁但不可以再获得新的锁。
应用两阶段锁协议的关键要点在于一旦事务开始释放它所持有的锁它就不能再获得新的锁。这个协议可以防止多个事务同时更新同一数据而导致的数据不一致问题并且帮助避免死锁的发生。不过需要注意的是即使遵守了两阶段锁协议死锁的情况也是有可能发生的这时候就需要额外的死锁检测机制来处理。在InnoDB事务中只要修改了数据就会对相关的行加行锁而这些锁会一直持有到事务结束即执行COMMIT或ROLLBACK才释放。这是遵循两阶段锁协议的一部分确保在整个事务期间数据的一致性。 因此在设计事务时你应该注意锁的顺序。理想情况下你应该尽量推迟那些最有可能导致锁冲突和影响并发度的锁的获得这样可以最大限度地减少它们持有锁的时间从而提高系统的并发性能。 例如对于需要更新多行的业务逻辑将对竞争最激烈的行的更新放在事务的最后一部分可以减少这些行被锁定的时间从而减少其他事务需要等待的时间这对于性能是有利的。
死锁
当并发系统中不同线程出现循环资源依赖涉及的线程都在等待别的线程释放资源时就会导致这几个线程都进入无限等待的状态称为死锁。 解决策略
设置超时时间innodb_lock_wait_timeout: 如果出现死锁系统会在超时后中断其中一个事务从而使其他事务继续执行。但是超时时间不能设置得太短以避免误中断那些只是简单等待锁释放的正常事务。。启用死锁检测innodb_deadlock_detect: 当设置为ON时会在事务被锁的时候检测是否有死锁发生。如果检测到死锁数据库会自动回滚死锁链中的某个事务来解开死锁。
第二十讲幻读是什么幻读有什么问题
并发事务中存在的问题
赃读一个事务读到另外一个事务还没有提交的数据。 不可重复读一个事务先后读取同一条记录但两次读取的数据不同称之为不可重复读。 幻读与不可重复读类似一个事务A查询数据时另一个事务在插入数据时事务A后续的查询中,发现了不该存在的记录,就是所谓的幻读
如何解决幻读
产生幻读的原因是行锁只能锁住行但是新插入记录这个动作要更新的是记 录之间的 “ 间隙 ” 。因此为了解决幻读问题 InnoDB 只好引入新的锁也就是 间隙锁(Gap Lock)。 但是间隙锁的引入会影响系统的并发度也增加了锁分析 的复杂度
间隙锁
间隙锁Gap Lock不锁定具体的数据行而是锁定两个数据行之间的间隙主要目的是防止幻读。
例如假设有一个表里的数据行是按照某一列的值顺序排列的现在有一个事务A想要在值10和20之间插入一条数据。如果同时有另一个事务B正在执行涉及到这个范围的查询操作并对这个范围加了间隙锁那么事务A就不能在10和20之间插入行直到事务B释放间隙锁为止。这就有效防止了在事务B的查询操作中出现由于事务A的插入操作而导致的幻读问题。
在REPEATABLE READ可重复读下InnoDB默认会使用间隙锁。在进行范围查询时不仅对查询到的数据行加锁还会对查询范围内的间隙加锁。这种机制可以有效防止幻读但是也可能会导致资源锁定的范围更大进而影响到数据库的并发处理能力。 在READ COMMITTED读已提交下InnoDB不使用间隙锁除非是显式地通过某些SQL语句要求锁定某个范围例如使用SELECT … FOR UPDATE进行加锁查询。这样做降低了资源的锁定范围提高了数据库操作的并发性但是牺牲了一部分事务的隔离性能允许了幻读的发生。
临键锁Next-Key Lock
行锁和间隙锁的结合体 前提间隙锁只能阻止行的插入并不能解决所有的幻读问题。比如说如果只是锁住了两个存在数据行之间的空隙那么对这些实际存在的行的修改还是可以发生的导致可能仍然会发生幻读现象。 临键锁锁定的是一个行记录以及前面的间隙即区间
特点
REPEATABLE READ下对于普通的SELECT语句InnoDB不会使用临键锁。但对于使用了FOR UPDATE或LOCK IN SHARE MODE的查询InnoDB会对符合条件的索引记录以及在这些记录之间的间隙设置临键锁。
当一个事务通过一个索引来查找并锁定一行时不仅这一行记录会被锁定防止其他事务进行修改或删除它之前的间隙也会被锁定防止其他事务插入新的行。
当对表进行范围查询并加锁时比如使用SELECT ... FOR UPDATE命令跟一个范围条件InnoDB会为查询范围内的所有存在的行以及所有间隙加上临键锁。日志与主备
第二讲日志系统一条SQL更新语句是如何执行的
redo log 和 binlog 的不同
redo log 是 InnoDB 引擎特有的 binlog 是 MySQL 的 Server 层实现的所有引擎都可以使用。redo log 是物理日志记录的是 “ 在某个数据页上做了什么修改 ” binlog 是逻辑日志记录的是这个语句的原始逻辑比如 “ 给 ID2 这一行的 c 字段加 1 ” 。redo log 是循环写的空间固定会用完 binlog 是可以追加写入的。 “ 追加写 ” 是指 binlog 文件写到一定大小后会切换到下一个并不会覆盖以前的日志。 当有一条记录需要更新的时候 InnoDB 引擎就会先把记录写到 redo log里面并更新内存这个时候更新就算完成了。同时 InnoDB 引擎会在适当的时候将这个操作记录更新到磁盘里面有了 redo log InnoDB 就可以保证即使数据库发生异常重启之前提交的记录都不会丢失这个能力称为 crash - safe
第十五讲答疑文章一日志和索引相关问题
MySQL崩溃恢复时的判断规则
1.如果redolog里面的事务是完整的也就是已经有了commit标识则直接提交 2.如果redolog里面的事务只有完整的prepare则判断对应的事务binlog是否存在并完整 a.如果是则提交事务 b.否则回滚事务。
两阶段提交的不同时刻在MySQL异常重启会出现什么现象 1.如果在图中时刻A的地方也就是写入redolog处于prepare阶段之后、写binlog之前发生了崩溃crash由于此时binlog还没写redolog也还没提交所以崩溃恢复的时候这个事务会回滚。这时候binlog还没写所以也不会传到备库。
2.在时刻Bbinlog写完redolog还没commit前发生crash崩溃恢复过程中事务会被提交。
临时表
第十三讲为什么表数据删掉一半表文件大小不变
一个 InnoDB 表包含两部分即表结构定义和数据。 因为表结构定义占用的空间很 小所以我们今天主要讨论的是表数据。
数据库中收缩表空间的方法
如果要收缩一个表只是 delete 掉表里面不用的数据的话表文件的大小是不会变的你还要通过 alter table 命令重建表才能达到表文件变小的目的。
第三十四讲 到底可不可以使用join
如果可以使用被驱动表的索引 join 语句还是有其优势的不能使用被驱动表的索引只能使用 Block Nested-Loop Join 算法这样的语句就尽量不要使用在使用 join 的时候应该让小表做驱动表。
第三十五讲join语句怎么优化
BKA 优化是 MySQL 已经内置支持的建议你默认使用BNL 算法效率低建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引基于临时表的改进方案对于能够提前过滤出小数据的 join 语句来说效果还是很好的如果需要 left join 的语义就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断必须都写在 on 里面。
实用性
第十四讲count(*)这么慢我该怎么办
count(*)在不同引擎中的实现方法
1.MyISAM 引擎把一个表的总行数存在了磁盘上因此执行 count() 的时候会直接返回这个数效率很高 2. InnoDB 引擎执行 count() 的时候需要把数据一行一行地从引擎里面读出来然后累积计数。因为 InnoDB的事务默认的隔离级别是可重复读在代码上就是通过多版本并发 控制也就是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见因此对于 count(*) 请求说 InnoDB 只好把数据一行一行地读出依次判断可见的行才能够用于计算 “ 基 于这个查询 ” 的表的总行数。
不同count的性能问题select count(?) from t
count() 的语义 count() 是一个聚合函数对于返回的结果集一行行地 判断如果 count 函数的参数不是 NULL 累计值就加 1 否则不加。最后返回累计值。 count() 、 count( 主键 id) 和 count(1) 都表示返回满足条件的 结果集的总行数 而 count( 字 段则表示返回满足条件的 数据行里面参数“字段”不为NULL的总个数 。 按照效率排序的话 count( 字段 )count( 主键 id)count(1)≈count() 所以我建议 你尽量使用 count(*) 。
第三十二讲为什么还有kill不掉的语句
在 MySQL 中有两个 kill 命令 kill query线程id 表示终止这个线程中正在执行的语句 kill connection 线程id 这里 connection 可缺省表示断开这个线程的连接当然如果这个线程有语句正在执行也是要先停止正在执行的语句的。
参考 MySQL实战45讲总结https://blog.csdn.net/liyifan687/article/details/114224787 极客时间MySQL实战45讲学习笔记https://blog.csdn.net/weixin_47887421/article/details/124065427 MySQL 实战 45 讲https://time.geekbang.org/column/intro/139
