h5网站设计,银川市住房和城乡建设厅网站,360优化大师历史版本,上海软件有限公司文章目录1. 索引2. 事务3. 存储引擎4. 锁机制5. MySQL其他知识点文章字数大约1.27万字#xff0c;阅读大概需要42分钟#xff0c;建议收藏后慢慢阅读#xff01;#xff01;#xff01;1. 索引 为什么使用索引 通过创建唯一性索引#xff0c;可以保证数据库表中每一行数据…
文章目录1. 索引2. 事务3. 存储引擎4. 锁机制5. MySQL其他知识点文章字数大约1.27万字阅读大概需要42分钟建议收藏后慢慢阅读1. 索引 为什么使用索引 通过创建唯一性索引可以保证数据库表中每一行数据的唯一性。可以大大加快数据的检索速度这也是创建索引的最主要的原因。帮助服务器避免排序和临时表。将随机IO变为顺序IO。可以加速表和表之间的连接特别是在实现数据的参考完整性方面特别有意义。 索引的分类 普通索引仅加速查询唯一索引加速查询 列值唯一可以有null主键索引加速查询 列值唯一不可以有null 表中只有一个组合索引多列值组成一个索引专门用于组合搜索其效率大于索引合并全文索引对文本的内容进行分词进行搜索索引合并使用多个单列索引组合搜索覆盖索引select的数据列只用从索引中就能够取得不必读取数据行换句话说查询列要被所建的索引覆盖聚簇索引表数据是和主键一起存储的主键索引的叶结点存储行数据(包含了主键值)二级索引的叶结点存储行的主键值。使用的是B树作为索引的存储结构非叶子节点都是索引关键字但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容) 什么时候需要/不需要创建索引 索引最大的好处是提高查询速度但是索引也是有缺点的比如 需要占用物理空间数量越大占用空间越大创建索引和维护索引要耗费时间这种时间随着数据量的增加而增大会降低表的增删改的效率每次增删改索引B 树为了维护索引有序性需要进行动态维护。 所以索引不是万能钥匙它也是根据场景来使用的。 什么时候适用索引 字段有唯一性限制的比如商品编码经常用于 WHERE 查询条件的字段这样能够提高整个表的查询速度如果查询条件不是一个字段可以建立联合索引。经常用于 GROUP BY 和 ORDER BY 的字段这样在查询的时候就不需要再去做一次排序了因为我们都已经知道了建立索引之后在 BTree 中的记录都是排序好的。 什么时候不需要创建索引 WHERE 条件GROUP BYORDER BY 里用不到的字段索引的价值是快速定位如果起不到定位的字段通常是不需要创建索引的因为索引是会占用物理空间的。字段中存在大量重复数据不需要创建索引比如性别字段只有男女如果数据库表中男女的记录分布均匀那么无论搜索哪个值都可能得到一半的数据。在这些情况下还不如不要索引因为 MySQL 还有一个查询优化器查询优化器发现某个值出现在表的数据行中的百分比很高的时候它一般会忽略索引进行全表扫描。表数据太少的时候不需要创建索引经常更新的字段不用创建索引因为索引字段频繁修改由于要维护 BTree的有序性那么需要频繁的重建索引这个过程是会影响数据库性能的。 优化索引的方法 前缀索引优化 前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引。 使用前缀索引是为了减小索引字段大小可以增加一个索引页中存储的索引值有效提高索引的查询速度。在一些大字符串的字段作为索引时使用前缀索引可以帮助我们减小索引项的大小。 覆盖索引优化 覆盖索引是指 SQL 中 query 的所有字段在索引 BTree 的叶子节点上都能找得到的那些索引从二级索引中查询得到记录而不需要通过聚簇索引查询获得可以避免回表的操作。 使用覆盖索引的好处就是不需要查询出包含整行记录的所有信息也就减少了大量的 I/O 操作。 主键索引最好是自增的 如果我们使用自增主键那么每次插入的新数据就会按顺序添加到当前索引节点的位置不需要移动已有的数据当页面写满就会自动开辟一个新页面。因为每次插入一条新记录都是追加操作不需要重新移动数据因此这种插入数据的方法效率非常高。 主键字段的长度不要太大因为主键字段长度越小意味着二级索引的叶子节点越小二级索引的叶子节点存放的数据是主键值这样二级索引占用的空间也就越小。 防止索引失效 用上了索引并不意味着查询的时候会使用到索引所以我们心里要清楚有哪些情况会导致索引失效从而避免写出索引失效的查询语句否则这样的查询效率是很低的。 发生索引失效的情况 当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效当我们在查询条件中对索引列做了计算、函数、类型转换操作这些情况下都会造成索引失效联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效。在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。 索引使用的注意事项 MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度在索引的使用过程中存在一些使用细节和注意事项。 函数运算否定操作符连接条件多个单列索引最左前缀原则范围查询不会包含有NULL值的列like 语句不要在列上使用函数和进行运算 1不要在列上使用函数这将导致索引失效而进行全表扫描。2尽量避免使用 ! 或 not in或 等否定操作符3多个单列索引并不是最佳选择4复合索引的最左前缀原则5覆盖索引的好处6范围查询对多列查询的影响7索引不会包含有NULL值的列8隐式转换的影响9like 语句的索引失效问题 索引为什么使用B树作为索引 主要原因B树只要遍历叶子节点就可以实现整棵树的遍历而且在数据库中基于范围的查询是非常频繁的而B树只能中序遍历所有节点效率太低。 Btree的磁盘读写代价更低Btree的查询效率更加稳定 数据库索引采用B树而不是B树的主要原因B树只要遍历叶子节点就可以实现整棵树的遍历而且在数据库中基于范围的查询是非常频繁的而B树只能中序遍历所有节点效率太低。 B树的特点 所有关键字都出现在叶子结点的链表中(稠密索引)且链表中的关键字恰好是有序的;不可能在非叶子结点命中;非叶子结点相当于是叶子结点的索引(稀疏索引)叶子结点相当于是存储(关键字)数据的数据层; 索引失效有哪些 当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效当我们在查询条件中对索引列使用函数就会导致索引失效。当我们在查询条件中对索引列进行表达式计算也是无法走索引的。MySQL 在遇到字符串和数字比较的时候会自动把字符串转为数字然后再进行比较。如果字符串是索引列而条件语句中的输入参数是数字的话那么索引列会发生隐式类型转换由于隐式类型转换是通过 CAST 函数实现的等同于对索引列使用了函数所以就会导致索引失效。联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效。在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。 MyISAM和InnoDB实现B树索引方式的区别是什么 MyISAMBTree叶节点的data域存放的是数据记录的地址在索引检索的时候首先按照BTree搜索算法搜索索引如果指定的key存在则取出其data域的值然后以data域的值为地址读取相应的数据记录这被称为“非聚簇索引” InnoDB其数据文件本身就是索引文件相比MyISAM索引文件和数据文件是分离的其表数据文件本身就是按BTree组织的一个索引结构树的节点data域保存了完整的数据记录这个索引的key是数据表的主键因此InnoDB表数据文件本身就是主索引这被称为“聚簇索引”或者聚集索引而其余的索引都作为辅助索引辅助索引的data域存储相应记录主键的值而不是地址这也是和MyISAM不同的地方。 在根据主索引搜索时直接找到key所在的节点即可取出数据在根据辅助索引查找时则需要先取出主键的值再走一遍主索引。因此在设计表的时候不建议使用过长的字段为主键也不建议使用非单调的字段作为主键这样会造成主索引频繁分裂。
2. 事务 事务的四大特性 原子性事务是最小的执行单位不允许分割。事务的原子性确保动作要么全部完成要么完全不起作用一致性执行事务前后数据库从一个一致性状态转换到另一个一致性状态。隔离性并发访问数据库时一个用户的事物不被其他事务所干扰各并发事务之间数据库是独立的持久性一个事务被提交之后。它对数据库中数据的改变是持久的即使数据库 发生故障也不应该对其有任何影响。 事务的脏读、不可重复读、幻读问题 脏读如果一个事务「读到」了另一个「未提交事务修改过的数据」就意味着发生了「脏读」现象。 幻读在一个事务内多次查询某个符合查询条件的「记录数量」如果出现前后两次查询到的记录数量不一样的情况就意味着发生了「幻读」现象。 丢弃修改两个写事务T1 T2同时对A0进行递增操作结果T2覆盖T1导致最终结果是1 而不是2事务被覆盖 不可重复读在一个事务内多次读取同一个数据如果出现前后两次读到的数据不一样的情况就意味着发生了「不可重复读」现象。 事务的隔离级别有哪些 READ_UNCOMMITTED未提交读: 最低的隔离级别允许读取尚未提交的数据变更可能会导致脏读、幻读或不可重复读READ_COMMITTED提交读: 允许读取并发事务已经提交的数据可以阻止脏读但是幻读或不可重复读仍有可能发生REPEATABLE_READ可重复读: 对同一字段的多次读取结果都是一致的除非数据是被本身事务自己所修改可以阻止脏读和不可重复读但幻读仍有可能发生SERIALIZABLE串行化: 最高的隔离级别完全服从 ACID 的隔离级别。所有的事务依次逐个执行这样事务之间就完全不可能产生干扰也就是说该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。 隔离级别脏读不可重复读幻影读READ-UNCOMMITTED 未提交读√√√READ-COMMITTED 提交读×√√REPEATABLE-READ 重复读××√SERIALIZABLE 可串行化读×××MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ可重读 这里需要注意的是与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ可重读事务隔离级别 下使用的是Next-Key Lock 锁算法因此可以避免幻读的产生这与其他数据库系统(如 SQL Server)是不同的。所以 说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ可重读 已经可以完全保证事务的隔离性要 求即达到了 SQL标准的SERIALIZABLE(可串行化)隔离级别。 Read View的作用 Read View 有四个重要的字段 m_ids 指的是在创建 Read View 时当前数据库中「活跃事务」的事务 id 列表注意是一个列表“活跃事务”指的就是启动了但还没提交的事务。min_trx_id 指的是在创建 Read View 时当前数据库中「活跃事务」中事务 id 最小的事务也就是 m_ids 的最小值。max_trx_id 这个并不是 m_ids 的最大值而是创建 Read View 时当前数据库中应该给下一个事务的 id 值也就是全局事务中最大的事务 id 值 1creator_trx_id 指的是创建该 Read View 的事务的事务 id。 对于使用 InnoDB 存储引擎的数据库表它的聚簇索引记录中都包含下面两个隐藏列 trx_id当一个事务对某条聚簇索引记录进行改动时就会把该事务的事务 id 记录在 trx_id 隐藏列里roll_pointer每次对某条聚簇索引记录进行改动时都会把旧版本的记录写入到 undo 日志中然后这个隐藏列是个指针指向每一个旧版本记录于是就可以通过它找到修改前的记录。 通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC多版本并发控制。 MySQL可重复读级别完全解决幻读了吗 针对快照读普通 select 语句是通过 MVCC 方式解决了幻读因为可重复读隔离级别下事务执行过程中看到的数据一直跟这个事务启动时看到的数据是一致的即使中途有其他事务插入了一条数据是查询不出来这条数据的所以就很好了避免幻读问题。针对当前读select … for update 等语句是通过 next-key lock记录锁间隙锁方式解决了幻读因为当执行 select … for update 语句的时候会加上 next-key lock如果有其他事务在 next-key lock 锁范围内插入了一条记录那么这个插入语句就会被阻塞无法成功插入所以就很好了避免幻读问题。 对于「读提交」和「可重复读」隔离级别的事务来说它们是通过 Read View 来实现的它们的区别在于创建 Read View 的时机不同 「读提交」隔离级别是在每个 select 都会生成一个新的 Read View也意味着事务期间的多次读取同一条数据前后两次读的数据可能会出现不一致因为可能这期间另外一个事务修改了该记录并提交了事务。「可重复读」隔离级别是启动事务时生成一个 Read View然后整个事务期间都在用这个 Read View这样就保证了在事务期间读到的数据都是事务启动前的记录。 这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对来控制并发事务访问同一个记录时的行为这就叫 MVCC多版本并发控制。 两个发生幻读场景的例子。 第一个例子对于快照读 MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录那么事务 A 前后两次查询的记录条目就不一样了所以就发生幻读。 第二个例子对于当前读如果事务开启后并没有执行当前读而是先快照读然后这期间如果其他事务插入了一条记录那么事务后续使用当前读进行查询的时候就会发现两次查询的记录条目就不一样了所以就发生幻读。 所以MySQL 可重复读隔离级别并没有彻底解决幻读只是很大程度上避免了幻读现象的发生。 MySQL中为什么要有事务回滚机制 在 MySQL 中恢复机制是通过回滚日志undo log实现的所有事务进行的修改都会先记录到这个回滚日志然后在对数据库中的对应行进行写入。 当事务已经被提交后就无法再次回滚了。 回滚日志作用 1)能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息 2) 在整个系统发生崩溃、数据库进程直接被杀死后当用户再次启动数据库进程时还能够立刻通过查询回滚日志将之前未完成的事务进行回滚这也就需要回滚日志必须先于数据持久化到磁盘上是我们需要先写日志后写数据库的主要原因。
3. 存储引擎 InnoDB介绍 InnoDB是事务型数据库的首选引擎支持事务安全表ACID支持行锁定和外键InnoDB是默认的MySQL引擎。 InnoDB主要特性有 InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全ACID兼容存储引擎。 InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来甚至在同一个查询中也可以混合。 InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的。 InnoDB存储引擎完全与MySQL服务器整合InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中表空间可以包含数个文件或原始磁盘文件。这与MyISAM表不同比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸即使在文件尺寸被限制为2GB的操作系统上。 InnoDB支持外键完整性约束存储表中的数据时每张表的存储都按主键顺序存放如果没有显示在表定义时指定主键InnoDB会为每一行生成一个6字节的ROWID并以此作为主键。 InnoDB被用在众多需要高性能的大型数据库站点上。InnoDB不创建目录使用InnoDB时MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件以及两个名为 ib_logfile0 和 ib_logfile1 的5MB大小的日志文件。 MyISAM介绍 MyISAM基于ISAM存储引擎并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度但不支持事物。 MyISAM主要特性有 大文件达到63位文件长度在支持大文件的文件系统和操作系统上被支持。当把删除和更新及插入操作混合使用的时候动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块以及若下一个块被删除就扩展到下一块自动完成。每个MyISAM表最大索引数是64这可以通过重新编译来改变。每个索引最大的列数是16。最大的键长度是1000字节这也可以通过编译来改变对于键长度超过250字节的情况一个超过1024字节的键将被用上。BLOB和TEXT列可以被索引。NULL被允许在索引的列中这个值占每个键的0~1个字节。所有数字键值以高字节优先被存储以允许一个更高的索引压缩。每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列当INSERT和UPDATE操作的时候该列被更新同时AUTO_INCREMENT列将被刷新。所以说MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快。可以把数据文件和索引文件放在不同目录。每个字符列可以有不同的字符集。有VARCHAR的表可以固定或动态记录长度。VARCHAR和CHAR列可以多达64KB。 MEMORY介绍 MEMORY存储引擎将表中的数据存储到内存中未查询和引用其他表数据提供快速访问。 MEMORY主要特性有 MEMORY表的每个表可以有多达32个索引每个索引16列以及500字节的最大键长度。MEMORY存储引擎执行HASH和BTREE缩影。可以在一个MEMORY表中有非唯一键值。MEMORY表使用一个固定的记录长度格式。MEMORY不支持BLOB或TEXT列。MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引。MEMORY表在所由客户端之间共享就像其他任何非TEMPORARY表。MEMORY表内存被存储在内存中内存是MEMORY表和服务器在查询处理时的空闲中创建的内部表共享。当不再需要MEMORY表的内容时要释放被MEMORY表使用的内存应该执行DELETE FROM或TRUNCATE TABLE或者删除整个表使用DROP TABLE。 Archive介绍 archive储存引擎的应用场景就是它的名字的缩影主要用于归档。archive储存引擎仅支持select和insert最出众的是插入快查询快占用空间小。 文件系统存储特性 以zlib对表数据进行压缩磁盘I/O更少几Tinnodb表在archive中只需要几百兆数据存储在.ARZ为后缀的文件中.frm文件 功能特点 只支持insert、replace和select支持行级锁和专用的缓存区可实现高并发只允许在自增ID列上加索引支持分区不支持事务处理 数据库引擎InnoDB与MyISAM的区别 InnoDB 是 MySQL 默认的事务型存储引擎只有在需要它不支持的特性时才考虑使用其它存储引擎。实现了四个标准的隔离级别默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下通过多版本并发控制(MVCC) 间隙锁(Next-Key Locking)防止幻影读。主索引是聚簇索引在索引中保存了数据从而避免直接读取磁盘因此对查询性能有很大的提升。内部做了很多优化包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。支持真正的在线热备份。其它存储引擎不支持在线热备份要获取一致性视图需要停止对所有表的写入而在读写混合场景中停止写入可能也意味着停止读取。 MyISAM 设计简单数据以紧密格式存储。对于只读数据或者表比较小、可以容忍修复操作则依然可以使用它。提供了大量的特性包括压缩表、空间数据索引等。不支持事务。不支持行级锁只能对整张表加锁读取时会对需要读到的所有表加共享锁写入时则对表加排它锁。但在表有读取操作的同时也可以往表中插入新的记录这被称为并发插入(CONCURRENT INSERT)。 总结 事务: InnoDB 是事务型的可以使用 Commit 和 Rollback 语句。并发: MyISAM 只支持表级锁而 InnoDB 还支持行级锁。外键: InnoDB 支持外键。备份: InnoDB 支持在线热备份。崩溃恢复: MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多而且恢复的速度也更慢。其它特性: MyISAM 支持压缩表和空间数据索引。 适用场景 MyISAM适合 插入不频繁查询非常频繁如果执行大量的SELECTMyISAM是更好的选择 没有事务。 InnoDB适合 可靠性要求比较高或者要求事务 表更新和查询都相当的频繁 大量的INSERT或UPDATE
4. 锁机制 MySQL有哪些锁全局锁/表级锁/行级锁 全局锁 MyISAM 只支持表锁InnoDB 支持表锁和行锁默认为行锁。 表级锁开销小加锁快不会出现死锁。锁定粒度大发生锁冲突的概率最高并发量最低。 表锁表级别的锁元数据锁MDL 全称为 metadata lock即元数据锁一般也可称为字典锁。MDL 的主要作用是为了管理数据库对象的并发访问和确保元数据一致性。意向锁意向锁是放置在资源层次结构的一个级别上的锁以保护较低级别资源上的共享锁或排它锁。AUTO-INC锁AUTO-INC 锁是特殊的表锁机制锁不是再一个事务提交后才释放而是再执行完插入语句后就会立即释放。 行级锁开销大加锁慢会出现死锁。锁粒度小发生锁冲突的概率小并发度最高。 Record lock单个行记录上的锁Gap lock间隙锁锁定一个范围不包括记录本身Next-key lockrecordgap 锁定一个范围包含记录本身。插入意向锁插入意向锁名字虽然有意向锁但是它并不是意向锁它是一种特殊的间隙锁属于行级别锁。 MySQL是怎么加锁的 MySQL 行级锁的加锁规则。 唯一索引等值查询 当查询的记录是「存在」的在索引树上定位到这一条记录后将该记录的索引中的 next-key lock 会退化成「记录锁」。当查询的记录是「不存在」的在索引树找到第一条大于该查询记录的记录后将该记录的索引中的 next-key lock 会退化成「间隙锁」。 非唯一索引等值查询 当查询的记录「存在」时由于不是唯一索引所以肯定存在索引值相同的记录于是非唯一索引等值查询的过程是一个扫描的过程直到扫描到第一个不符合条件的二级索引记录就停止扫描然后在扫描的过程中对扫描到的二级索引记录加的是 next-key 锁而对于第一个不符合条件的二级索引记录该二级索引的 next-key 锁会退化成间隙锁。同时在符合查询条件的记录的主键索引上加记录锁。当查询的记录「不存在」时扫描到第一条不符合条件的二级索引记录该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录所以不会对主键索引加锁。 非唯一索引和主键索引的范围查询的加锁规则不同之处在于 唯一索引在满足一些条件的时候索引的 next-key lock 退化为间隙锁或者记录锁。非唯一索引范围查询索引的 next-key lock 不会退化为间隙锁和记录锁。 MySQL记录锁间隙锁解决幻读问题 在 MySQL 的可重复读隔离级别下针对当前读的语句会对索引加记录锁间隙锁这样可以避免其他事务执行增、删、改时导致幻读的问题。 有一点要注意的是在执行 update、delete、select … for update 等具有加锁性质的语句一定要检查语句是否走了索引如果是全表扫描的话会对每一个索引加 next-key 锁相当于把整个表锁住了这是挺严重的问题。 死锁的四个必要条件 互斥条件一个资源每次只能被一个进程使用 请求与保持条件一个进程因请求资源而阻塞时对已获得的资源保持不放 不剥夺条件:进程已获得的资源在末使用完之前不能强行剥夺 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系 如何解决MySQL死锁问题 死锁是指两个或多个事务在同一资源上相互占用并请求锁定对方的资源从而导致恶性循环的现象。 常见的解决死锁的方法 如果不同程序并发存取多个表尽量约定 以相同的顺序访问表可以大大降低死锁机会在同一个事务中尽可能做到 一次锁定所需要的所有资源减少死锁产生概率对于非常容易产生死锁的业务部分可以尝试使用 升级锁定颗粒度通过 表级锁 定来减少死锁产生的概率。 数据库悲观锁和乐观锁的原理和应用场景 悲观锁先获取锁再进行业务操作一般就是利用类似 SELECT … FOR UPDATE 这样的语句对数据加锁避免其他事务意外修改数据。 当数据库执行SELECT … FOR UPDATE时会获取被select中的数据行的行锁select for update获取的行锁会在当前事务结束时自动释放因此必须在事务中使用。 乐观锁先进行业务操作只在最后实际更新数据时进行检查数据是否被更新过。Java 并发包中的 AtomicFieldUpdater 类似也是利用 CAS 机制并不会对数据加锁而是通过对比数据的时间戳或者版本号来实现乐观锁需要的版本判断。
5. MySQL其他知识点 MySQL的内部构造一般可以分为哪两个部分 可以分为服务层和存储引擎层两部分其中 服务层包括连接器、查询缓存、分析器、优化器、执行器等涵盖MySQL的大多数核心服务功能以及所有的内置函数如日期、时间、数学和加密函数等所有跨存储引擎的功能都在这一层实现比如存储过程、触发器、视图等。 存储引擎层负责数据的存储和提取。其架构模式是插件式的支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB它从MySQL 5.5.5版本开始成为了默认的存储引擎。 undo log、redo log、binlog有什么用 redo log是InnoDB引擎特有的只记录该引擎中表的修改记录。binlog是MySQL的Server层实现的会记录所有引擎对数据库的修改。 redo log是物理日志记录的是在具体某个数据页上做了什么修改binlog是逻辑日志记录的是这个语句的原始逻辑。 redo log是循环写的空间固定会用完binlog是可以追加写入的binlog文件写到一定大小后会切换到下一个并不会覆盖以前的日志。 补充 1、redolog记录修改内容哪一页发生了什么变化写于事务开始前用于数据未落磁盘但数据库挂了后的数据恢复 2、binlog记录修改SQL写于事务提交时可用于读写分离 3、undolog记录修改前记录用于回滚和多版本并发控制 什么是Buffer pool Innodb 存储引擎设计了一个缓冲池*Buffer Pool*来提高数据库的读写性能。 当读取数据时如果数据存在于 Buffer Pool 中客户端就会直接读取 Buffer Pool 中的数据否则再去磁盘中读取。当修改数据时首先是修改 Buffer Pool 中数据所在的页然后将其页设置为脏页最后由后台线程将脏页写入到磁盘。 缓存什么 InnoDB 会把存储的数据划分为若干个「页」以页作为磁盘和内存交互的基本单位一个页的默认大小为 16KB。因此Buffer Pool 同样需要按「页」来划分。 在 MySQL 启动的时候InnoDB 会为 Buffer Pool 申请一片连续的内存空间然后按照默认的16KB的大小划分出一个个的页 Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的之后随着程序的运行才会有磁盘上的页被缓存到 Buffer Pool 中。 Innodb 通过三种链表来管理缓页 Free List 空闲页链表管理空闲页Flush List 脏页链表管理脏页LRU List管理脏页干净页将最近且经常查询的数据缓存在其中而不常查询的数据就淘汰出去。 InnoDB 对 LRU 做了一些优化我们熟悉的 LRU 算法通常是将最近查询的数据放到 LRU 链表的头部而 InnoDB 做 2 点优化 将 LRU 链表 分为young 和 old 两个区域加入缓冲池的页优先插入 old 区域页被访问时才进入 young 区域目的是为了解决预读失效的问题。当**「页被访问」且「 old 区域停留时间超过 innodb_old_blocks_time 阈值默认为1秒」**时才会将页插入到 young 区域否则还是插入到 old 区域目的是为了解决批量数据访问大量热数据淘汰的问题。 可以通过调整 innodb_old_blocks_pct 参数设置 young 区域和 old 区域比例。 DROP、DELETE 与 TRUNCATE 的区别 三种都可以表示删除其中的细微区别之处如下 DROPDELETETRUNCATESQL 语句类型DDLDMLDDL回滚不可回滚可回滚不可回滚删除内容从数据库中 删除表所有的数据行索引和权限也会被删除表结构还在删除表的 全部或者一部分数据行表结构还在删除表中的 所有数据删除速度删除速度最快删除速度慢需要逐行删除删除速度快因此在不再需要一张表的时候采用 DROP在想删除部分数据行时候用 DELETE在保留表而删除所有数据的时候用 TRUNCATE。 SQL语法中内连接、自连接、外连接左、右、全、交叉连接的区别分别是什么 内连接只有两个元素表相匹配的才能在结果集中显示。 外连接 左外连接: 左边为驱动表驱动表的数据全部显示匹配表的不匹配的不会显示。 右外连接:右边为驱动表驱动表的数据全部显示匹配表的不匹配的不会显示。 全外连接连接的表中不匹配的数据全部会显示出来。 交叉连接 笛卡尔效应显示的结果是链接表数的乘积。 MySQL中CHAR和VARCHAR的区别有哪些 char的长度是不可变的用空格填充到指定长度大小而varchar的长度是可变的。char的存取数度还是要比varchar要快得多char的存储方式是对英文字符ASCII占用1个字节对一个汉字占用两个字节。varchar的存储方式是对每个英文字符占用2个字节汉字也占用2个字节 数据库中的主键、超键、候选键、外键是什么 超键在关系中能唯一标识元组的属性集称为关系模式的超键候选键不含有多余属性的超键称为候选键。也就是在候选键中若再删除属性就不是键了主键用户选作元组标识的一个候选键程序主键外键如果关系模式R中属性K是其它模式的主键那么k在模式R中称为外键。 主键为候选键的子集候选键为超键的子集而外键的确定是相对于主键的。 MySQL优化 为搜索字段创建索引避免使用 Select *列出需要查询的字段垂直分割分表选择正确的存储引擎 SQL语句执行流程 Server层按顺序执行sql的步骤为 客户端请求-连接器验证用户身份给予权限 -查询缓存存在缓存则直接返回不存在则执行后续操作-分析器对SQL进行词法分析和语法分析操作 -优化器主要对执行的sql优化选择最优的执行方案方法 -执行器执行时会先看用户是否有执行权限有才去使用这个引擎提供的接口-去引擎层获取数据返回如果开启查询缓存则会缓存查询结果 简单概括 连接器管理连接、权限验证查询缓存命中缓存则直接返回结果分析器对SQL进行词法分析、语法分析判断查询的SQL字段是否存在也是在这步优化器执行计划生成、选择索引执行器操作引擎、返回结果存储引擎存储数据、提供读写接口。 数据库三范式是什么 第一范式强调的是列的原子性即数据库表的每一列都是不可分割的原子数据项第二范式要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性第三范式任何非主属性不依赖于其它非主属性。 对MVCC的了解 数据库并发场景 读-读不存在任何问题也不需要并发控制读-写有线程安全问题可能会造成事务隔离性问题可能遇到脏读幻读不可重复读写-写有线程安全问题可能会存在更新丢失问题。 多版本并发控制MVCC是一种用来解决读-写冲突的无锁并发控制也就是为事务分配单向增长的时间戳为每个修改保存一个版本版本与事务时间戳关联读操作只读该事务开始前的数据库的快照。 MVCC 可以为数据库解决以下问题 在并发读写数据库时可以做到在读操作时不用阻塞写操作写操作也不用阻塞读操作提高了数据库并发读写的性能同时还可以解决脏读幻读不可重复读等事务隔离问题但不能解决更新丢失问题。 主从复制中涉及到哪三个线程 主要涉及三个线程binlog 线程、I/O 线程和 SQL 线程。 binlog 线程 负责将主服务器上的数据更改写入二进制日志Binary log中。I/O 线程 负责从主服务器上读取二进制日志并写入从服务器的重放日志Relay log中。SQL 线程 负责读取重放日志并重放其中的 SQL 语句。 数据库如何保证持久性 主要是利用Innodb的redo log。重写日志 正如之前说的MySQL是先把磁盘上的数据加载到内存中在内存中对数据进行修改再写回到磁盘上。如果此时突然宕机内存中的数据就会丢失。 怎么解决这个问题 简单啊事务提交前直接把数据写入磁盘就行啊。 这么做有什么问题 只修改一个页面里的一个字节就要将整个页面刷入磁盘太浪费资源了。毕竟一个页面16kb大小你只改其中一点点东西就要将16kb的内容刷入磁盘听着也不合理。毕竟一个事务里的SQL可能牵涉到多个数据页的修改而这些数据页可能不是相邻的也就是属于随机IO。显然操作随机IO速度会比较慢。 于是决定采用redo log解决上面的问题。当做数据修改的时候不仅在内存中操作还会在redo log中记录这次操作。当事务提交的时候会将redo log日志进行刷盘(redo log一部分在内存中一部分在磁盘上)。当数据库宕机重启的时候会将redo log中的内容恢复到数据库中再根据undo log和binlog内容决定回滚数据还是提交数据。 采用redo log的好处 其实好处就是将redo log进行刷盘比对数据页刷盘效率高具体表现如下 redo log体积小毕竟只记录了哪一页修改了啥因此体积小刷盘快。redo log是一直往末尾进行追加属于顺序IO。效率显然比随机IO来的快。 数据库如何保证原子性 主要是利用 Innodb 的undo log。 undo log名为回滚日志是实现原子性的关键当事务回滚时能够撤销所有已经成功执行的 SQL语句他需要记录你要回滚的相应日志信息。 例如 当你delete一条数据的时候就需要记录这条数据的信息回滚的时候insert这条旧数据当你update一条数据的时候就需要记录之前的旧值回滚的时候根据旧值执行update操作当年insert一条数据的时候就需要这条记录的主键回滚的时候根据主键执行delete操作 undo log记录了这些回滚需要的信息当事务执行失败或调用了rollback导致事务需要回滚便可以利用undo log中的信息将数据回滚到修改之前的样子。 数据库如何保证一致性 从数据库层面数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中C(一致性)是目的A(原子性)、I(隔离性)、D(持久性)是手段是为了保证一致性数据库提供的手段。数据库必须要实现AID三大特性才有可能实现一致性。例如原子性无法保证显然一致性也无法保证。从应用层面通过代码判断数据库数据是否有效然后决定回滚还是提交数据 数据库高并发的解决方案 在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层将高频访问的数据存入缓存中减少数据库的读取负担。增加数据库索引进而提高查询速度。不过索引太多会导致速度变慢并且数据库的写入会导致索引的更新也会导致速度变慢主从读写分离让主服务器负责写从服务器负责读。将数据库进行拆分使得数据库的表尽可能小提高查询的速度。使用分布式架构分散计算压力。 数据库结构优化的手段 范式优化 比如消除冗余节省空间。。反范式优化比如适当加冗余等减少join限定数据的范围 务必禁止不带任何限制数据范围条件的查询语句。比如我们当用户在查询订单历史的时候我们可以控制在一个月的范围内。读/写分离 经典的数据库拆分方案主库负责写从库负责读拆分表分区将数据在物理上分隔开不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样当对这个表进行查询时只需要在表分区中进行扫描而不必进行全表扫描明显缩短了查询时间另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。 关系型和非关系型数据库的区别 非关系型数据库也叫NOSQL采用键值对的形式进行存储。 它的读写性能很高易于扩展可分为内存性数据库以及文档型数据库比如 RedisMongodbHBase等等。 合使用非关系型数据库的场景 日志系统、地理位置存储、数据量巨大、高可用 关系型数据库的优点 容易理解。因为它采用了关系模型来组织数据。可以保持数据的一致性。数据更新的开销比较小。支持复杂查询带where子句的查询 非关系型数据库的优点 不需要经过SQL层的解析读写效率高。基于键值对数据的扩展性很好。可以支持多种类型数据的存储如图片文档等等 数据库为什么要进行分库和分表 分库与分表的目的在于减小数据库的单库单表负担提高查询性能缩短查询时间。 通过分表可以减少数据库的单表负担将压力分散到不同的表上同时因为不同的表上的数据量少了起到提高查询性能缩短查询时间的作用此外可以很大的缓解表锁的问题。 分表策略可以归纳为垂直拆分和水平拆分 水平分表取模分表就属于随机分表而时间维度分表则属于连续分表。 如何设计好垂直拆分我的建议将不常用的字段单独拆分到另外一张扩展表. 将大文本的字段单独拆分到另外一张扩展表, 将不经常修改的字段放在同一张表中将经常改变的字段放在另一张表中。 对于海量用户场景可以考虑取模分表数据相对比较均匀不容易出现热点和并发访问的瓶颈。 库内分表仅仅是解决了单表数据过大的问题但并没有把单表的数据分散到不同的物理机上因此并不能减轻 MySQL 服务器的压力仍然存在同一个物理机上的资源竞争和瓶颈包括 CPU、内存、磁盘 IO、网络带宽等。 分库与分表带来的分布式困境与应对之策 数据迁移与扩容问题----一般做法是通过程序先读出数据然后按照指定的分表策略再将数据写入到各个分表中。 分页与排序问题----需要在不同的分表中将数据进行排序并返回并将不同分表返回的结果集进行汇总和再次排序最后再返回给用户。
