母婴用品网站建设规划,莞城东莞网站建设,wordpress页面模板目录文件下载,网站域名建设费进什么科目导航#xff1a; 【黑马Java笔记踩坑汇总】JavaSEJavaWebSSMSpringBoot瑞吉外卖SpringCloud黑马旅游谷粒商城学成在线MySQL高级篇设计模式牛客面试题 目录
请你说说MySQL索引,以及它们的好处和坏处
请你说说MySQL的索引是什么结构,为什么不用哈希表
请你说说数据库索引的底… 导航 【黑马Java笔记踩坑汇总】JavaSEJavaWebSSMSpringBoot瑞吉外卖SpringCloud黑马旅游谷粒商城学成在线MySQL高级篇设计模式牛客面试题 目录
请你说说MySQL索引,以及它们的好处和坏处
请你说说MySQL的索引是什么结构,为什么不用哈希表
请你说说数据库索引的底层数据结构
请你讲讲B树和B树
数据库为什么不用红黑树而用B树
请你说说索引怎么实现的B树,为什么选这个数据结构
请你说说聚簇索引和非聚簇索引
请你说说数据库引擎有哪些,各自有什么区别
请你说说InnoDB的MVCC
请你说说乐观锁和悲观锁
请你介绍一下数据库的ACID
请你说说MySQL的事务隔离级别
MySQL主从同步是如何实现的
MySQL调优 请你说说MySQL索引,以及它们的好处和坏处 得分点 检索效率、磁盘资源、维护索引对性能的影响、最左前缀原则、三种索引介绍和区别 索引是一种用于快速查询的排好序的数据结构索引是存储引擎实现的不同索引的存储引擎不一定相同。
优点
查询效率高磁盘I/O次数低每行数据索引唯一联合、分组、排序查询效率高
缺点
创建索引耗费时间维护索引耗费时间每次增删改时要维护索引索引占用磁盘空间索引文件可能比数据文件更占空间。例如innoDB的1个聚簇索引和多个非聚簇索引加起来肯定比原数据文件占的内存多联合索引查询时没有遵循最左前缀原则将会导致索引不起作用从而出现严重的性能问题。
innoDB索引按存储方式可以分为聚集索引、二级索引、联合索引严格说是二级索引。
聚集索引索引即数据因为记录就存在B树叶节点数据即索引。
数据访问更快因为索引和数据保存在同一个B树排序查找和范围查找很快因为叶节点之间由双向链表链接分段加载节省内存建议主键短、自增、不可更新从而提高效率
非聚簇索引辅助、二级索引B树叶节点只存查询字段的值和主键的值通过主键值回表查聚集索引。
联合索引同时为多个列建立非聚簇索引。先按c2字段排序c2字段相同时按c3字段排序。
聚簇索引和非聚簇索引区别
叶节点聚簇索引叶子节点存记录非聚簇索引叶子节点存数据位置 。非聚簇索引不会影响数据表的物理存储顺序。数量一个表只能有一个聚簇索引因为只能有一种排序存储的方式但可以有多个非聚簇索引也就是多个索引目录提供数据检索。效率使用聚簇索引的时候数据的查询效率高 但如果对数据进行插入删除更新等操作效率会比非聚簇索引低。因为聚簇索引存的完整记录移动起来慢。 标准回答 在关系数据库中索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录可以根据目录中的页码快速找到所需的内容。 在表中建立索引然后在索引中找到符合查询条件的索引值最后通过保存在索引中的ROWID相当于页码快速找到表中对应的记录。 索引就像指向表行的指针是一种允许查询操作快速确定哪些行符合WHERE子句中的条件并检索到这些行的其他列值的数据结构 索引主要有普通索引、唯一索引、主键索引、外键索引、全文索引、复合索引几种 在大数据量的查询中合理使用索引的优点非常明显不仅能大幅提高匹配where条件的检索效率还能用于排序和分组操作的加速。 当时索引如果使用不当也有比较大的坏处比如索引必定会增加存储资源的消耗同时也增大了插入、更新和删除操作的维护成本因为每个增删改操作后相应列的索引都必须被更新。 加分回答-只要创建了索引就一定会走索引吗 不一定。比如在使用组合索引的时候如果没有遵从“最左前缀”的原则进行搜索则索引是不起作用的。 最左前缀原则指的是由多个列组成的联合索引在查询时只会对最左边使用的几个列进行索引查询。具体来说如果索引键包含A、B、C三个列而查询语句仅使用了A和B列则最左前缀的原则会指导MySQL只使用A、B两列进行索引搜索而不会使用C列。 当使用“最左前缀”原则时指定的列的顺序非常重要。如果指定的列顺序不正确就无法利用索引进行查询会产生全表扫描的情况这会造成非常严重的性能问题。 举例假设在id、name、age字段上已经成功建立了一个名为MultiIdx的组合索引。索引行中按id、name、age的顺序存放索引可以搜索id、idname、idnameage字段组合。如果列不构成索引最左面的前缀那么MySQL不能使用局部索引如age或者nameage组合则不能使用该索引查询。 请你说说MySQL的索引是什么结构,为什么不用哈希表 得分点 B树、自适应的哈希、内存耗费 MySQL中的索引是B树结构可以分段加载节点数据省内存查询效率也高。
自适应的哈希InnoDB底层索引是B树但他支持自适应的哈希索引。对于经常被访问的数据MySQL会把经常被访问数据的数据页地址存到哈希表里这样下次再查到这个热点数据就会直接从哈希表找提高查询效率
不用哈希表的原因哈希表耗费内存要把所有数据载入内存。 标准回答 MySQL中的索引B树实现的 哈希表的查询效率的确最高时间复杂度O(1)但是它要求将所有数据载入内存而数据库存储的数据量级可能会非常大全部载入内存基本上是不可能实现的 B树可以分段加载需要的节点数据可以在内存资源有限的前提下极大提高查询效率。 分段加载将整个B树划分成若干个段每个段包含多个数据页。在查询过程中只需要将当前需要查询的节点所在的段加载到内存中而不必将整棵树都加载到内存中从而减少内存的占用和I/O操作次数。 请你说说数据库索引的底层数据结构 得分点 介绍并比较优缺点、二叉树、红黑树、哈希表、B树、B树 数据库不单指MySQL索引可选的底层数据机构包括
- 二叉树某些场景下会退化成链表查询慢
- 红黑树红黑树是二叉树索引树层数会很高查询次数和IO不如B树
- Hash表增删改查时间复杂度O1但①太占空间②难以排序、范围查询③无法对单独一个字段索引④重复值多时处理冲突耗费性能和IO
- B-Tree多路平衡查找树非叶节点也存数据左小右大一般层数比B树深查询速度和IO次数也就不如B树可以分段加载节点数据。
- BTree多路平衡查找树非叶节点存目录叶节点存记录。查询效率更高比B树矮胖层数很难超过4层IO次数也少很难超过3次更稳定范围查询效率高因为叶节点之间由双向链表链接。可以分段加载节点数据。 标准答案 数据库不单指MySQL索引可选的底层数据机构包括 - 二叉树 - 红黑树 - Hash表 - B-Tree 但mysql索引的底层用的并不是二叉树和红黑树。因为二叉树和红黑树在某些场景下都会暴露出一些缺陷。 首先二叉树在某些场景下会退化成链表而链表的查找需要从头部开始遍历而这就失去了加索引的意义。 不使用红黑树的原因是红黑树作为底层数据结构在面对在些表数据动辄数百万数千万的场景时会导致索引树的层数很高。索引从根节点开始查找而如果我们需要查找的数据在底层的叶子节点上那么树的高度是多少就要进行多少次查找数据存在磁盘上访问需要进行磁盘IO这会导致效率过低 而B树由B树和索引顺序访问方法演化而来它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树在B树中所有记录节点都是按键值的大小顺序存放在同一层的叶子节点各叶子节点通过指针进行链接。 B树索引在数据库中的一个特点就是高扇出性例如在InnoDB存储引擎中每个页的大小为16KB。在数据库中B树的高度一般都在24层这意味着查找某一键值最多只需要2到4次IO操作这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作24次的IO操作意味着查询时间只需0.020.04秒。 为什么B树的高度一般不会超过4层 真实一个数据页可以存100条记录 一个目录页能存1000条数据4层B树能存一千万条数据我们用到的B树都不会超过4层每个页内部可以用二分查找更快查找。 InnoDB非聚簇索引情况 InnoDB 页的大小为 16KB一般表的主键类型为 INT(占用4个字节)或 BIGINT (占用8个字节)指针类型也一般为4或8个字节也就是说一个页 (BTree 中的一个节点)中大概存储16KB/(8B8B)1K个键值 (因为是估值为方便计算这里的K取值为 10^3。也就是说一个深度为3的BTree 索引可以维护 10^3*10^3*10^3 1 亿条记录(这里假定一个数据页也存储10A3条行记录数据了) 实际情况中每个节点可能不能填充满因此在数据库中BTree 的高度一般都在 2~4 层。MySQL的lnnoDB 存储引擎在设计时是将根节点常驻内存的也就是说查找某一键值的行记录时最多只需要 2~4 次磁盘IO操作。 数据页大小 MyISAM 的数据页大小是固定的是 1KB也就是说MyISAM 存储引擎的数据都是以 1KB 的块进行管理的。 InnoDB 存储引擎的数据页大小是可调的默认是 16KB。在 MySQL 5.7 版本之前InnoDB 的数据页大小默认是 8KB。可以通过参数 innodb_page_size 来设置 InnoDB 的数据页大小取值范围是 4KB、8KB、16KB 和 32KB。 请你讲讲B树和B树 得分点 平衡多路查找树、记录存储位置、非叶节点存储内容、叶节点关系、查询性能和IO次数 1. 非叶节点的值数量和孩子数量k阶树B树非叶节点有k个值、k个孩子B树非叶节点有k-1个值、k个孩子
2. 记录存储位置B树记录全存在叶节点B树记录存在所有节点
3. 非叶节点存储内容B树非叶节点存索引B树非叶节点存记录
4. 叶子结点之间关系B树所有叶子节点构成一个有序双向链表B树叶节点之间没指针只是有序
查询性能B树查询效率更高比B树矮胖层数很难超过4层IO次数也少很难超过3次更稳定范围查询效率高因为叶节点之间由双向链表链接。
B树和B树都可以分段加载节点数据。 标准回答 它们都是平衡多路查找树是在二叉查找树基础上的改进数据结构。在二叉查找树上查找一个数据时最坏情况的查找次数为树的深度当数据量很大时查询次数可能还是很大造成大量的磁盘IO从而影响查询效率 为了减少磁盘IO的次数必须降低树的深度因此在二叉查找树基础上将树改成了多叉加上一些限制条件就形成了B树 B树是B树的变种区别主要是对于k阶的B树每个中间节点只存k-1个值、k个指针是那k-1个值的范围而B树存k个值和k个指针B树中所有节点中值的总集是全部关键字集合而B树中所有叶子节点值的总集就是全部关键字集合B树为所有叶子节点增加了链接从而实现了快速的范围查找 加分回答-B树优点 B树由B树和索引顺序访问方法演化而来它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树在B树中所有记录节点都是按键值的大小顺序存放在同一层的叶子节点各叶子节点通过指针进行链接。 B树索引在数据库中的一个特点就是高扇出性例如在InnoDB存储引擎中每个页的大小为16KB。在数据库中B树的高度一般都在24层这意味着查找某一键值最多只需要1到3次IO操作根节点被缓存不算IO次数。因为现在一般的磁盘每秒至少可以做100次IO操作24次的IO操作意味着查询时间只需0.020.04秒。 数据库为什么不用红黑树而用B树 得分点 介绍红黑树和B树、叉数、高度、查询性能、磁盘IO次数、范围查询、分段加载数据 因为B树是多叉红黑树是二叉B树更矮胖查询性能更高IO次数更少。
B树是多路平衡查找树非叶节点存目录叶节点存记录。查询效率更高比B树矮胖层数很难超过4层IO次数也少很难超过3次更稳定范围查询效率高因为叶节点之间由双向链表链接。 可以分段加载节点数据。
m阶B树
每个非叶节点关键字个数和孩子个数相等根结点关键字个数2到m非跟结点关键字个数⌈m/2⌉到m所有叶子在同一层
红黑树 近似平衡二叉树左右子树高差有可能大于 1查找效率略低于平衡二叉树但增删效率高于平衡二叉树适合频繁插入删除。
结点非黑即红根结点是黑色叶节点是黑色空节点常省略任何相邻节点不能同时为红色从任一结点到其每个叶子的所有路径都包含相同数目的黑色结点查询性能稳定O(logN)高度最高2log(n1) 标准回答 首先红黑树是一种近似平衡二叉树不完全平衡结点非黑即红的树它的树高最高不会超过 2*log(n)因此查找的时间复杂度为 O(log(n))无论是增删改查它的性能都十分稳定 但是红黑树本质还是二叉树在数据量非常大时需要访问判断的节点数还是会比较多同时数据是存在磁盘上的访问需要进行磁盘IO导致效率较低 而B树是多叉的可以有效减少磁盘IO次数同时B树增加了叶子结点间的连接能保证范围查询时找到起点和终点后快速取出需要的数据。 加分回答-红黑树做索引底层数据结构的缺陷 试想一下以红黑树作为底层数据结构在面对在些表数据动辄数百万数千万的场景时创建的索引它的树高得有多高 索引从根节点开始查找而如果我们需要查找的数据在底层的叶子节点上那么树的高度是多少就要进行多少次查找数据存在磁盘上访问需要进行磁盘IO这会导致效率过低 那么红黑树作为索引数据结构的弊端即是树的高度过高导致查询效率变慢。 请你说说索引怎么实现的B树,为什么选这个数据结构 得分点 B树、查询效率、IO次数、范围排序查询、分段加载数据页 B树
B树是多路平衡查找树非叶节点存目录叶节点存记录。
为什么选B树
查询效率更高层数很难超过4层IO次数也少很难超过3次更稳定范围、排序查询效率高因为叶节点之间由双向链表链接。可以分段加载节点数据。
m阶B树
每个非叶节点关键字个数和孩子个数相等根结点关键字个数2到m非跟结点关键字个数⌈m/2⌉到m所有叶子在同一层 标准回答 索引本质上就是通过预排序树型结构来加快检索的效率而MySQL中使用InnoDB和MyISAM引擎时都使用了B树实现索引。 它是一棵平衡多路查找树是在二叉查找树基础上的改进数据结构。在二叉查找树上查找一个数据时最坏情况的查找次数为树的深度当数据量很大时查询次数可能还是很大造成大量的磁盘IO从而影响查询效率 为了减少磁盘IO的次数必须降低树的深度因此在二叉查找树基础上将树改成了多叉加上一些限制条件就形成了B树 B树中所有叶子节点值的总集就是全部关键字集合B树为所有叶子节点增加了链接从而实现了快速的范围查找 在B树中所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上由各叶子节点指针进行连接。在数据库中B树的高度一般都在24层这也就是说查找某一键值的行记录时最多只需要2到4次IO。这很不错因为当前一般的机械磁盘每秒至少可以做100次IO24次的IO意味着查询时间只需0.020.04秒。 在数据库中B树索引还可以分为聚集索引和辅助索引但不管是聚集索引还是辅助索引其内部都是B树的即高度平衡的叶子节点存放着所有的数据。聚集索引与辅助索引不同的是叶子节点存放的是否是一整行的信息。 请你说说聚簇索引和非聚簇索引 得分点 介绍、区别、叶节点、数量、效率、回表 innoDB索引按存储方式可以分为聚集索引、二级索引、联合索引严格说是二级索引。
聚集索引索引即数据因为记录就存在B树叶节点数据即索引。
数据访问更快因为索引和数据保存在同一个B树排序查找和范围查找很快因为叶节点之间由双向链表链接分段加载节省内存建议主键短、自增、不可更新从而提高效率
非聚簇索引辅助、二级索引B树叶节点只存查询字段的值和主键的值通过主键值回表查聚集索引。
联合索引同时为多个列建立非聚簇索引。先按c2字段排序c2字段相同时按c3字段排序。
聚簇索引和非聚簇索引区别
叶节点聚簇索引叶子节点存记录非聚簇索引叶子节点存数据位置 。非聚簇索引不会影响数据表的物理存储顺序。数量一个表只能有一个聚簇索引因为只能有一种排序存储的方式但可以有多个非聚簇索引也就是多个索引目录提供数据检索。效率使用聚簇索引的时候数据的查询效率高 但如果对数据进行插入删除更新等操作效率会比非聚簇索引低。因为聚簇索引存的完整记录移动起来慢。 标准回答 两者主要区别是数据和索引是否分离。聚簇索引是将数据与索引存储到一起找到索引也就找到了数据而非聚簇索引是将数据和索引存储分离开索引树的叶子节点存储了数据行的地址。 在InnoDB中一个表有且仅有一个聚簇索引因为原始数据只留一份而数据和聚簇索引在一起并且该索引是建立在主键上的即使没有指定主键也会特殊处理生成一个聚簇索引其他索引都是辅助索引使用辅助索引访问索引外的其他字段时都需要进行二次查找。 而在MyISAM中所有索引都是非聚簇索引叶子节点存储着数据的地址对于主键索引和普通索引在存储上没有区别。 加分回答 在InnoDB存储引擎中可以将B树索引分为聚簇索引和辅助索引非聚簇索引。无论是何种索引每个页的大小都为16KB且不能更改。 聚簇索引是根据主键创建的一棵B树聚簇索引的叶子节点存放了表中的所有记录。辅助索引是根据索引键创建的一棵B树与聚簇索引不同的是其叶子节点仅存放索引键值以及该索引键值指向的主键。也就是说如果通过辅助索引来查找数据那么当找到辅助索引的叶子节点后很有可能还需要根据主键值查找聚簇索引来得到数据这种查找方式又被称为书签查找。因为辅助索引不包含行记录的所有数据这就意味着每页可以存放更多的键值因此其高度一般都要小于聚簇索引。 请你说说数据库引擎有哪些,各自有什么区别 得分点 InnoDB、MyISAM外键、事务、高并发、锁级别、内存要求、磁盘耗费、查询性能、增删改性能 InnoDB支持外键和事务行锁适合高并发缓存索引和数据内存要求高因为要缓存索引和记录适合存大数据量增删改性能更优行级锁高并发耗费磁盘因为有多个非聚簇索引索引可能比记录空间还大。
MyISAM不支持外键和事务表锁不适合高并发缓存索引和数据地址内存要求低因为不用缓存记录查询性能更优因为查询时InnoDB要维护MVCC一致而且多缓存了记录节省磁盘因为磁盘不存完整记录。 标准回答 InnoDB 引擎是 MySQL 的事务安全ACID 兼容存储引擎具有提交、回滚和崩溃恢复功能来保护用户数据行级锁定读取增加了多用户并发性和性能将用户数据存储在聚集索引中以减少基于主键的常见查询的 I/O还支持 FOREIGN KEY 维护数据完整性。 MyISAM引擎的表占用空间较小表级锁定限制了读/写工作负载的性能因此它通常用于只读或以读取为主的场景。 Memory引擎是将所有数据存储在 RAM 中以便在需要快速查找非关键数据的环境中进行快速访问以前被称为 HEAP 引擎。 Archive引擎非常适合存储大量的独立的作为历史记录的数据因为它们不经常被读取。它 拥有高效的插入速度但其对查询的支持相对较差。 Cluster/NDB是高冗余的存储引擎用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大安全和性能要求高的应用。 Federated引擎提供连接单独的 MySQL 服务器从多个物理服务器创建一个逻辑数据库的能力非常适合分布式或数据集市环境。 标准回答2 InnoDB是具有事务、回滚和崩溃修复能力的事务安全型引擎它可以实现行级锁来保证高性能的大量数据中的并发操作MyISAM是具有默认支持全文索引、压缩功能及较高查询性能的非事务性引擎。具体来说可以在以下角度上形成对比 事务InnoDB支持事务MyISAM不支持。 数据锁InnoDB支持行级锁MyISAM只支持表级锁。 读写性能InnoDB增删改性能更优行级锁高并发MyISAM查询性能更优。 全文索引InnoDB不支持但可通过插件等方式支持MyISAM默认支持。 外键InnoDB支持外键MyISAM不支持。 存储结构InnoDB在磁盘存储为一个文件MyISAM在磁盘上存储成三个文件表定义、数据、索引。 存储空间InnoDB需要更多的内存和存储MyISAM支持支持三种不同的存储格式静态表(默认)、动态表、压缩表。 移植InnoDB在数据量小时可通过拷贝数据文件、备份binlog、mysqldump工具移植数据量大时比较麻烦可单独对某个表通过拷贝表文件移植。 崩溃恢复InnoDB有崩溃恢复机制MyISAM没有。 默认推荐InnoDB是MySQL5.5之后的默认引擎。 加分回答-InnoDB中行级锁是怎么实现的 InnoDB行级锁是通过给索引上的索引项加锁来实现的。只有通过索引条件检索数据InnoDB才使用行级锁否则InnoDB将使用表锁。 当表中锁定其中的某几行时不同的事务可以使用不同的索引锁定不同的行。另外不论使用主键索引、唯一索引还是普通索引InnoDB都会使用行锁来对数据加锁。 请你说说InnoDB的MVCC 得分点 概念、三剑客、流程、读提交和可重复读MVCC原理 MVCC多版本并发控制通过管理记录的多个版本实现了数据库事务并发时的一致性读即当前事务读取正在被其他事务更新的行时能读到该记录被更新之前的版本。解决了读写冲突。
MVCC三剑客隐藏字段、Undo Log、Read View。
隐藏字段innoDB聚簇索引叶节点记录有两个隐藏字段trx_id和roll_pointer。
trx_id事务id。事务修改记录时会将事务id写在trx_id列。roll_pointer回滚指针。指向回滚日志里该记录版本链的最近结点。用于找到回滚日志里该记录被事务修改前的版本。
Undo Log回滚日志存储各记录的版本链用于回滚和存档。记录的一个版本就是版本链的一个结点每个结点的数据域是整个记录指针域指向上个版本的结点。首结点是最近记录。
Read View事务快照读时生成的数据系统读视图记录并维护当前活跃事务的id列表。事务id是按创建时间递增的。
快照读不加锁的查询语句可能读到旧版本数据。当前读加锁的查询语句读到的一定是最新版本数据 Read View的规则即可见性算法
通过读视图可以判断当前查询中记录的某个版本是否可见。
判断方法是比较各版本的trx_id和读视图里的活跃事务id如果某版本trx_id小于读视图的最小事务id则代表那个版本是生成读视图之前的已提交版本当前查询就可以访问那个版本。
MVCC流程
查询生成读视图用读视图的活跃事务id依次对比各版本的事务id找到符合规则的数据。
应用 事务隔离级别里的读提交和可重复读底层是由MVCC实现的。并且MySQL InnoDB 引擎的可重复读解决了幻读问题快照读由MVCC解决当前读通过 next-key lock解决。
读提交的MVCC原理事务每次读到的都是最新已提交的数据。每次读取数据前都生成一个ReadView。快照读生成Read View不断对比版本链各版本的trx_id直到发现某版本trx_id比Read View的活跃事务列表里最小trx_id还小该版本则是快照读前最新已提交的数据。
可重复读的MVCC原理只在第一次查询时生成ReadView之后查询用第一次快照读时生成的ReadView。 标准回答 全称 Multi-Version Concurrency Control 即多版本并发控制逻辑是维持一个数据的多个版本使得读写操作没有冲突。MVCC主要是为了提高数据库并发性能用更好的方式去处理读-写冲突做到即使有读写冲突时也能做到不加锁非阻塞并发读。 它是一种用来解决读-写冲突的无锁并发控制机制。在并发读写数据库时可以做到在读操作时不用阻塞写操作写操作也不用阻塞读操作提高了数据库并发读写的性能还可以解决脏读、幻读、不可重复读等事务隔离问题但不能解决更新丢失问题。 加分回答-三剑客 InnoDB默认的隔离级别是RRREPEATABLE READRR解决脏读、不可重复读、幻读等问题使用的是MVCC。MVCC全称Multi-Version Concurrency Control即多版本的并发控制协议。它最大的优点是读不加锁因此读写不冲突并发性能好。InnoDB实现MVCC多个版本的数据可以共存主要基于以下技术及数据结构 1. 隐藏列InnoDB中每行数据都有隐藏列隐藏列中包含了本行数据的事务id、指向undo log的指针等。 2. 基于undo log的版本链每行数据的隐藏列中包含了指向undo log的指针而每条undo log也会指向更早版本的undo log从而形成一条版本链。 3. ReadView通过隐藏列和版本链MySQL可以将数据恢复到指定版本。但是具体要恢复到哪个版本则需要根据ReadView来确定。所谓ReadView是指事务记做事务A在某一时刻给整个事务系统trx_sys打快照之后再进行读操作时会将读取到的数据中的事务id与trx_sys快照比较从而判断数据对该ReadView是否可见即对事务A是否可见。 请你说说乐观锁和悲观锁 得分点 定义、乐观锁三种实现方式、使用场景 MySQL数据库的锁机制主要有两种乐观锁和悲观锁。
乐观锁每次拿数据时候乐观地认为别人不会改这个数据更新时再判断锁。因为读的时候不判断锁所以读效率高因为更新时才判断锁所以也不会死锁。
实现方式版本号机制、时间戳机制、CAS机制。
版本号机制表中有一个version字段 读时取version字段更新时如果发现版本号没变就给版本号加一并更新如果发现版本号变了就拒绝更新。
时间戳机制表中有一个时间戳字段更新时对比时间戳得知是否被更改。
CAS机制使用像compare and swap这样的原子操作来不断尝试修改记录如果数据没有被其他线程修改则会更新成功否则需重试。
应用场景
git等版本控制工具是悲观锁在pull到push期间如果别人已经push过那么我会push失败。
库存多、高并发的秒杀场景适合用乐观锁为防止主从同步出错要强制从主机查数据。在提交订单业务的最后一步查库存余量。
悲观锁每次拿数据时候悲观地认为别人正在改这个数据拿数据时候就判断锁拿到锁后阻塞其他线程。因为阻塞所以并发差特别是长事务、可能死锁。因为阻塞所以有排他性精度高。
库存少、低并发、高价格、高精度的秒杀场景适合用悲观锁。订单业务刚开始查库存的时候就加锁然后生成订单减库存释放锁。 标准回答 乐观锁乐观锁总是假设最好的情况每次去拿数据的时候都认为别人不会修改所以不会上锁但是在更新的时候会判断一下在此期间别人有没有去更新这个数据可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型这样可以提高吞吐量单位时间内完成的任务、处理的数据量、传输的信息量等像数据库提供的类似于write_condition机制其实都是提供的乐观锁。 UPDATE ... SET versionversion1 WHERE version${version}; 悲观锁悲观锁总是假设最坏的情况每次去拿数据的时候都认为别人会修改所以每次在拿数据的时候都会上锁这样别人想拿这个数据就会阻塞直到它拿到锁共享资源每次只给一个线程使用其它线程阻塞用完后再把资源转让给其它线程。传统的关系型数据库里边就用到了很多这种锁机制比如行锁表锁等读锁写锁等都是在做操作之前先上锁。 加分回答-两种锁的使用场景 乐观锁 GITSVNCVS等代码版本控制管理器就是一个乐观锁使用很好的场景例如A、B程序员同时从SVN服务器上下载了code.html文件当A完成提交后此时B再提交那么会报版本冲突此时需要B进行版本处理合并后再提交到服务器。这其实就是乐观锁的实现全过程。如果此时使用的是悲观锁那么意味者所有程序员都必须一个一个等待操作提交完才能访问文件这是难以接受的。 悲观锁 悲观锁的好处在于可以减少并发但是当并发量非常大的时候由于锁消耗资源、锁定时间过长等原因很容易导致系统性能下降资源消耗严重。因此一般我们可以在并发量不是很大并且出现并发情况导致的异常用户和系统都很难以接受的情况下会选择悲观锁进行。 请你介绍一下数据库的ACID 得分点 原子性、一致性、隔离性、持久性 数据库的ACID是指事务四个特性原子性Atomicity、一致性Consistency、隔离性Isolation和持久性Durability。
原子性事务的所有操作要么全部成功要么全部失败。
一致性事务前后数据库的约束没有被破坏保持前后一致。
隔离性操作同一资源的并发事务之间相互隔离不会互相干扰。
持久性事务的结果最终一定会持久化到数据库宕机等故障也无法影响。 标准回答 事务可由一条非常简单的SQL语句组成也可以由一组复杂的SQL语句组成。在事务中的操作要么都执行修改要么都不执行这就是事务的目的也是事务模型区别于文件系统的重要特征之一。 事务需遵循ACID四个特性 - Aatomicity原子性。原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功整个事务的执行才算成功。事务中任何一个SQL语句执行失败那么已经执行成功的SQL语句也必须撤销数据库状态应该退回到执行事务前的状态。 - Cconsistency一致性。一致性指事务将数据库从一种状态转变为另一种一致的状态。在事务开始之前和事务结束以后数据库的完整性约束没有被破坏。 - Iisolation隔离性。事务的隔离性要求每个读写事务的对象与其他事务的操作对象能相互分离即该事务提交前对其他事务都不可见这通常使用锁来实现。 - Ddurability 持久性。事务一旦提交其结果就是永久性的即使发生宕机等故障数据库也能将数据恢复。持久性保证的是事务系统的高可靠性而不是高可用性。 加分回答-事务分类 事务可以分为以下几种类型 - 扁平事务是事务类型中最简单的一种而在实际生产环境中这可能是使用最为频繁的事务。在扁平事务中所有操作都处于同一层次其由BEGIN WORK开始由COMMIT WORK或ROLLBACK WORK结束。处于之间的操作是原子的要么都执行要么都回滚。 - 带有保存点的扁平事务除了支持扁平事务支持的操作外允许在事务执行过程中回滚到同一事务中较早的一个状态这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效放弃整个事务不合乎要求开销也太大。保存点savepoint用来通知系统应该记住事务当前的状态以便以后发生错误时事务能回到该状态。 - 链事务可视为保存点模式的一个变种。链事务的思想是在提交一个事务时释放不需要的数据对象将必要的处理上下文隐式地传给下一个要开始的事务。注意提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果就好像在一个事务中进行的。 - 嵌套事务是一个层次结构框架。有一个顶层事务top-level transaction控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务subtransaction其控制每一个局部的变换。 - 分布式事务通常是一个在分布式环境下运行的扁平事务因此需要根据数据所在位置访问网络中的不同节点。对于分布式事务同样需要满足ACID特性要么都发生要么都失效。 对于MySQL的InnoDB存储引擎来说它支持扁平事务、带有保存点的扁平事务、链事务、分布式事务。对于嵌套事务MySQL数据库并不是原生的因此对于有并行事务需求的用户来说MySQL就无能为力了但是用户可以通过带有保存点的事务来模拟串行的嵌套事务。 请你说说MySQL的事务隔离级别 得分点 隔离级别概念、三种读问题、四种隔离级别的特点、底层、解决读问题 事务隔离级别是指操作同一资源的并发事务之间的隔离度。隔离级别越高事务之间的相互干扰就越小安全性就越高。
读问题
脏读读到了脏数据。当前事务读到另一个未提交事务刚改的数据。只有读未提交会脏读。不可重复读前后重复读的数据不一样。前后两次读同数据这期间数据被其他事务改了导致前后读取的数据不同。幻读前后读的数据是一样但多了几行或少了几行像幻觉一样。事务前后读的数据集合不同导致出现“幻像”行。仅串行化能解决幻读问题。
事务隔离级别
读未提交事务能读到所有未提交事务的数据。压根不加锁、没隔离性能最高。读提交事务能读到已提交事务的数据。底层由MVVC实现每次快照读都生成读视图 。解决脏读问题。可重复读默认前后读的数据相同。底层由MVVC实现仅第一次快照读生成读视图。解决脏读、不可重复读问题。MySQL InnoDB 引擎的可重复读解决了幻读问题。快照读由MVCC解决当前读通过 next-key lock解决。串行化事务一拿到锁阻塞其他事务直到释放锁。读时共享锁写时排它锁。阻塞导致性能最差。解决脏读、不可重复读、幻读问题。
MVVC多版本并发控制。MVCC三剑客隐藏字段、Undo Log、Read View。
共享锁读锁在共享锁下多个线程可以同时读取数据但只有一个线程能够修改数据。当一个线程在修改数据时必须获得独占锁以便其他线程不能访问数据。
排它锁写锁在排它锁下只有一个线程可以修改数据其他线程不允许访问数据。 标准回答 SQL 标准定义了四种隔离级别这四种隔离级别分别是 - 读未提交READ UNCOMMITTED - 读提交 READ COMMITTED - 可重复读 REPEATABLE READ - 串行化 SERIALIZABLE。 下面是四种隔离级别在解决脏读、不可重复读、幻读问题方面的情况 隔离级别脏读不可重复读幻读读未提交存在存在存在读已提交不存在存在存在可重复读不存在不存在存在串行化不存在不存在不存在 脏读Dirty Read指一个事务读取了另一个未提交的事务所写入的数据如果隔离级别越高则越不容易出现脏读问题。 不可重复读Non-Repeatable Read指一个事务在读取同一数据时由于另外一个事务的修改或删除导致两次读取的数据不同。如果隔离级别越高则越不容易出现不可重复读问题。 幻读Phantom Read指一个事务多次执行同一个查询但每次返回的数据集合都不同导致出现“幻像”行。如果隔离级别越高则越不容易出现幻读问题。 加分回答-锁 READ UNCOMMITTED读未提交 它是性能最好、也最野蛮的方式因为它压根儿就不加锁所以根本谈不上什么隔离效果可以理解为没有隔离。 REPEATABLE READ READ COMMITTED 为了解决不可重复读MySQL 采用了 MVVC (多版本并发控制) 的方式。 我们在数据库表中看到的一行记录可能实际上有多个版本每个版本的记录除了有数据本身外还要有一个表示版本的字段记为 row trx_id而这个字段就是使其产生的事务的 id事务 ID 记为 transaction id它在事务开始的时候向事务系统申请按时间先后顺序递增。 SERIALIZABLE 读的时候加共享锁其他事务可以并发读但是不能写。写的时候加排它锁其他事务不能并发写也不能并发读。 MySQL主从同步是如何实现的 步骤、延时问题、原理三个线程、主从库数量 主从同步实现步骤
主服务器把数据更改记录到二进制日志binlog记录改不记录读用于数据复制和数据恢复中从服务器异步近似实时地把主服务器的二进制日志复制到自己的中继日志relay log中从服务器重做中继日志中的操作把更改应用到自己的数据库上以达到数据的最终一致性。
复制最大的问题是主服务器压力大导致的复制延时问题。
复制原理
主库二进制日志转储线程负责将二进制日志发给从库。强制从主库读取数据的时会给二进制日志加锁 读完解锁。从库I/O 线程负责连接主库并向主库发送请求和复制二进制日志到中继日志。从库SQL 线程负责读取并执行中继日志中的更新语句实现主从同步。
主从库数量
每个 Master 可以有多个 Slave每个 Slave 只能有一个唯一的服务器ID只有一个 Master。 标准答案 复制replication是MySQL数据库提供的一种高可用高性能的解决方案一般用来建立大型的应用。总体来说replication的工作原理分为以下3个步骤 1.主服务器master把数据更改记录到二进制日志binlog中。 2.从服务器slave把主服务器的二进制日志复制到自己的中继日志relay log中。 3.从服务器重做中继日志中的事件把更改应用到自己的数据库上以达到数据的最终一致性。 复制的工作原理并不复杂其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是复制不是完全实时地进行同步而是异步实时。这中间存在主从服务器之间的执行延时如果主服务器的压力很大则可能导致主从服务器延时较大。 MySQL调优 得分点观察、调优 关键字观察、 查看状态、慢查询日志、定位慢sql、执行成本、执行计划。调优、缓存、mysql参数、定时清理数据、存储引擎、索引、表结构、分表 观察查看状态、开启并分析慢查询日志、定位慢sql语句查询sql执行成本、分析sql执行计划
查看状态如sql查询成本、连接次数、慢查询次数等性能参数 show status like 参数; 开启慢查询日志修改慢查询阈值mysqldumpslow分析慢查询日志里查询慢的语句 set slow_query_logON; #开启慢查询日志
set long_query_time 1; #设置慢查询阈值 #命令行按照查询时间排序查看前五条 慢查询SQL 语句
mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log 定位慢sql语句查询sql执行成本。开启并使用show profiles和show profile查询sql语句列表和指定sql语句执行成本持续时间、cpu、io情况explan分析sql执行计划访问类型、记录条数、索引长度等
调优缓存、mysql参数、定时清理数据、存储引擎、索引、表结构、分表
缓存优化。mysql调整缓冲池大小等参数引入redis。InnoDB使用缓冲池缓存记录和索引配置MySQL参数。关闭不必要的服务和日志调优结束关闭慢查询日志调整最大连接数。定时清理数据。对于不再使用的表、数据、日志、缓存等应该及时清理避免占用过多的MySQL资源从而提高MySQL的性能。使用合适的存储引擎。MyISAM比较适合读取频繁写入较少的场景因为表级锁、B树叶存地址而InnoDB比较适合并发写入的场景因为行级锁、B树叶存记录。索引优化。在频繁查询特别是分组、范围查询的列、唯一特性的列上建立索引避免创建过多索引、联合索引把频繁查询的列放左边。表结构优化。遵循第一范式每个属性不可再分第二范式表必须有一个主键第三范式非主键列必须直接依赖于主键避免无限制的增加列尽量使用枚举类型。分表。将大表分成较小的表局限范围在大量查询时优化性能。 MySQL的缓冲池被分为多个不同的缓存池其中包括 查询缓存用来缓存查询结果。InnoDB缓存池用来缓存热点表和索引数据页。MyISAM缓存池用来缓存表数据块。 MySQL的缓冲池使用的是LRU最近最少使用淘汰策略它会优先缓存最近使用的数据。当缓冲池的空间不足时MySQL会将最不常用的数据从缓冲池中替换出去以腾出空间缓存新的数据。 MySQL的缓存设置包括多个参数其中比较常见的缓存参数包括以下几个 key_buffer_size该参数用来设置MyISAM索引的缓存大小。如果应用程序中涉及到大量的索引查询可以适当提高该值。一般来说key_buffer_size占用总内存的1/4到1/3比较合适。 innodb_buffer_pool_size该参数用来设置InnoDB缓冲池的大小。InnoDB存储引擎使用缓冲池来缓存数据和索引文件。如果InnoDB表的读写频次较高建议将该值设置为物理内存的70%到80%。 sort_buffer_size该参数用来设置排序缓冲区大小。如果查询中涉及到ORDER BY或GROUP BY操作可以适当提高该值。一般来说sort_buffer_size占用总内存的1/4到1/3比较合适。 read_buffer_size和read_rnd_buffer_size这两个参数是用来设置读取缓冲区大小的默认值为128 KB。如果应用程序中经常进行大文件的读取操作可以适当提高这两个参数。 binlog_cache_size该参数是用来设置二进制日志的缓存大小。如果应用程序中需要持久化一些数据可以开启二进制日志并适当调整该参数。 总的来说MySQL的缓存设置需要根据应用程序的实际情况进行调整例如根据读写频次调整InnoDB缓冲池大小根据查询特点调整sort_buffer_size大小等。合理的缓存设置可以大大提高MySQL的性能和稳定性。 配置MySQL参数 max_connections是MySQL的一个系统参数它用来限制同一时刻可以与MySQL服务器建立的最大连接数。默认情况下max_connections的值是100可以通过设置my.cnf文件或动态修改来调整该值。如果应用程序需要处理大量的请求可以适当提高max_connections的值。但是需要注意的是随着max_connections的增加MySQL服务器需要消耗更多的内存、CPU资源以及线程资源。如果过多的连接数不仅会降低系统的响应速度还可能导致系统崩溃或者出现僵尸进程。innodb_flush_log_at_trx_commit是MySQL InnoDB存储引擎的一个重要参数用来控制事务提交时的日志写入行为。该参数的默认值为1表示每次提交事务都会立即将事务日志写入磁盘。在这种模式下可以保证事务的持久化和一致性但同时会降低系统性能特别是在高并发写入的场景下。
