这可能是MySQL比较全的知识整理

最新推荐文章于 2023-07-14 19:16:38 发布
最新推荐文章于 2023-07-14 19:16:38 发布
阅读量278 收藏
点赞数
分类专栏： 面试 mysql 计算机基础
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sinat_19131703/article/details/109171495
版权
面试 同时被 3 个专栏收录
10 篇文章 0 订阅
订阅专栏
计算机基础
2 篇文章 0 订阅
订阅专栏
mysql
1 篇文章 0 订阅
订阅专栏

MySQL索引

  1. 什么是索引

    高效获取数据的数据结构，相当于书的目录，使用B+树结构，索引是存储在磁盘文件中的（可能单独的索引文件中，也可能和数据一起存储在数据文件中）

  2. 索引的分类

    1. 单列索引
    • 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数
      据更快一点。
    • 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
    • 主键索引：是一种特殊的唯一索引，不允许有空值
    1. 组合索引
    • 在表中的多个字段组合上创建的一个索引
    • 组合索引的使用，需要遵循最左前缀原则（最左匹配原则）。

  3. 索引的优缺点

    优点：快速数据的查询速度

    缺点：空间换时间，索引也需要占空间

    ​ 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

MySQL存储引擎

存储引擎优点缺点
InnoDB5.5版本后MySQL默认数据库，支持事务，比MyISAM处理速度稍慢非常复杂，性能较一些简单的引擎要差一点儿。空间占用比较多。
MyISAM高速引擎，拥有极高的插入，查询速度不支持事务，不支持行锁、崩溃后数据不容易修复
Memory内存存储引擎，拥有极高的插入，更新和查询效率占用和数据量成正比的内存空间，只在内存上保存数据，意味着数据可能会丢失

最常用的是InnoDB和MyISAM，InnoDB和MyISAM存储引擎区别

类别InnoDBMyISAM
存储文件.frm 表定义文件
.idb 数据文件和索引文件		.frm 表定义文件
.myd 数据文件
.myi 索引文件
锁表锁、行锁表锁
事务支持不支持
索引结构B+ Tree（聚簇索引，叶子节点存储主键，所以有回表操作）B+ Tree（非聚簇索引，叶子结点存储地址的指针）
支持MVCC是否

并发事务带来的问题？

  • 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
  • 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
  • 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
  • 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

事务的隔离级别

  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

事务的四大特性

事务是逻辑上的一组操作，要么都执行，要么都不执行。

  1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  2. 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
  3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

大表优化

  1. 限定数据的范围（例如查询近3个月的）
  2. 读/写分离（主库负责写，从库负责读；）
  3. 垂直分区（数据表列的拆分，把一张列比较多的表拆分为多张表。）
  4. 水平分区（保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，）

分库分表之后,id 主键如何处理？

  1. UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
  2. 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
  3. 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
  4. Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。
  5. 美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比

一条SQL语句在MySQL中如何执行的

  1. MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。
  2. 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
  3. SQL 等执行过程分为两类，一类对于查询等过程如下：权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
  4. 对于更新等语句执行流程如下：分析器----》权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit

MySQL高性能优化

  1. 数据库设计规范
    1. 所有表必须使用 Innodb 存储引擎
    2. 数据库和表的字符集统一使用 UTF8，避免转化造成索引失效（emoji 表情的需要，字符集需要采用 utf8mb4 字符集。）
    3. 尽量控制单表数据量的大小,建议控制在 500 万以内。
    4. 谨慎使用 MySQL 分区表
    5. 禁止在数据库中存储图片,文件等大的二进制数据
    6. 禁止在线上做数据库压力测试
  2. 数据库字段设计规范
    1. 优先选择符合存储需要的最小的数据类型
    2. 避免使用 TEXT,BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据
    3. 尽可能把所有列定义为 NOT NULL（mysql难以优化，而且它会使索性、索引统计和值更加复杂。可空列需要更多的存储空间）
    4. 同财务相关的金额类数据必须使用 decimal 类型
  3. 索引设计规范
    1. 限制每张表上的索引数量
    2. 禁止给表中的每一列都建立单独的索引
    3. 每个 Innodb 表必须有个主键
    4. 常见索引列建议（出现在 SELECT、多表 join 的关联列）
    5. 科学选择索引列的顺序
      1. 区分度最高的放在联合索引的最左侧
      2. 尽量把字段长度小的列放在联合索引的最左侧
      3. 使用最频繁的列放到联合索引的左侧
  4. 数据库开发规范
    1. 避免数据类型的隐式转换
    2. 充分利用表上已经存在的索引
    3. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询
    4. 避免使用子查询，可以把子查询优化为 join 操作
    5. 避免使用 JOIN 关联太多的表
    6. 拆分复杂的大 SQL 为多个小 SQL
  5. Explain优化SQL语句

MVCC多版本并发控制

https://tech.meituan.com/2014/08/20/innodb-lock.html

MVCC是为了实现数据库的并发控制而设计的一种协议。并发访问控制，最简单的做法就是加锁访问，即基于锁的并发控制（Lock-Based Concurrent Control），是四种隔离级别中级别最高的Serialize隔离级别。MVCC可以认为是行级锁的一个变种, 但是它在很多情况下避免了加锁操作, 因此开销更低。设计思想有：

版本号

  • 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
  • 事务版本号：事务开始时的系统版本号。

隐藏的列

MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

  • 创建版本号：指示创建一个数据行的快照时的系统版本号；

  • 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

  • 每行数据都存在一个版本，每次数据更新时都更新该版本

  • 修改时Copy出当前版本, 然后随意修改，各个事务之间无干扰

  • 保存时比较版本号，如果成功(commit)，则覆盖原记录, 失败则放弃copy(rollback)

  • 就是每行都有版本号，保存时根据版本号决定是否成功，听起来含有乐观锁的味道, 因为这看起来正是，在提交的时候才能知道到底能否提交成功

Mysql explain有哪些字段，分别代表什么含义，如何判断是否走索引

type：联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序

  • system：表仅有一行(=系统表)。这是const联接类型的一个特例。
  • const：表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
  • eq_ref：对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
  • ref：对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
  • ref_or_null：该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
  • index_merge：该联接类型表示使用了索引合并优化方法。
  • unique_subquery：该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
  • index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
  • range：只检索给定范围的行,使用一个索引来选择行。
  • index：该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
  • ALL：对于每个来自于先前的表的行组合,进行完整的表扫描。

possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

rows：它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数

Extra：该列包含MySQL解决查询的详细信息

  • Distinct：MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
  • Not exists：MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
    range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
  • Using filesort：MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
  • Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
  • Using temporary：为了解决查询,MySQL需要创建一个临时表来容纳结果。
  • Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
  • Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
  • Using index for group-by：类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

说下主键索引和非主键索引在查询时候的区别

主键索引和非主键索引的区别是：非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。

  1. 如果查询语句是 select * from table where ID = 100,即主键查询的方式，则只需要搜索 ID 这棵 B+树。

  2. 如果查询语句是 select * from table where k = 1，即非主键的查询方式，则先搜索k索引树，得到ID=100,再到ID索引树搜索一次，这个过程也被称为回表。

索引的非叶子结点存的是什么信息

非叶子结点是一页数据，16kb，

定义任意非叶子结点最多只有M个儿子，且M>2；

根结点的儿子数为[2, M]；

除根结点以外的非叶子结点的儿子数为[M/2, M]；

非叶子结点的关键字个数=指向儿子的指针个数-1；

非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；

MySQL 数据库锁

https://tech.meituan.com/2014/08/20/innodb-lock.html

#####为什么用 B+ 树做索引而不用哈希表做索引?

  1. 哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。
  2. 如果我们要进行范围查找，例如查找ID为100 ~ 400的人，哈希表同样不支持，只能遍历全表。
  3. 索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。

MySQL的锁机制 - 记录锁、间隙锁、临键锁

读锁是共享的，或者说是相互不阻塞的

写锁是排他的，一个写锁会阻塞其他的写锁和读锁

在实际的数据库系统中，每时每刻都发生锁定，当某个用户在修改某部分数据时，mysql会通过锁定阻止其他用户对同一数据的读取

记录锁、间隙锁、临键锁都是排它锁，而记录锁的使用方法跟之前的一篇文章 共享/排它锁 里的排它锁介绍一致，这里就不详细多讲。

1. 记录锁

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

SELECT * FROM `test` WHERE `id`=1 FOR UPDATE;
2. 间隙锁

间隙锁是封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

如果是唯一索引：

  1. 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE id = 5 FOR UPDATE;
  2. 对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE id BETWEEN 5 AND 7 FOR UPDATE;

如果是普通索引：

  1. 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
  2. 在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序。
3. 临键锁

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

行锁和表锁
  • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少索引条件，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度

MySQL 范式

函数依赖：

异常：

  • 冗余数据：例如 学生-2 出现了两次。
  • 修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
  • 删除异常：删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。
  • 插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

第一范式 (1NF)：属性不可分。

第二范式 (2NF)：每个非主属性完全函数依赖于键码。可以通过分解来满足。

第三范式 (3NF)：非主属性不传递函数依赖于键码。

事务传播行为

**1.PROPAGATION_REQUIRED：**如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是最常用的设置。

**2.PROPAGATION_SUPPORTS：**支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行。

**3.PROPAGATION_MANDATORY：**支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常。

**4.PROPAGATION_REQUIRES_NEW：**创建新事务，无论当前存不存在事务，都创建新事务。

**5.PROPAGATION_NOT_SUPPORTED：**以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。

**6.PROPAGATION_NEVER：**以非事务方式执行，如果当前存在事务，则抛出异常。

**7.PROPAGATION_NESTED：**如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。

数据库主从复制

主从复制的几种方式:

同步复制:

所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,…,slave-n完成后才能返回。这样，显然不可取，也不是MySQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。

异步复制:

如同AJAX请求一样。master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志，是否完成操作，不用关心,MySQL的默认设置。

半同步复制:

master只保证slaves中的一个操作成功，就返回，其他slave不管。这个功能，是由google为MySQL引入的。

https://segmentfault.com/a/1190000012650596

https://github.com/Snailclimb/JavaGuide/blob/master/docs/database/MySQL.md

数据库崩溃时事务的恢复机制（REDO日志和UNDO日志）

Undo Log:

Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用了Undo Log来实现多版本并发控制(简称：MVCC)。

事务的原子性(Atomicity)事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发生了错误，要回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过。

原理Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为UndoLog）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

Redo Log:

原理和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

Mysql索引缺点

  1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
  2. 索引需要占用物理空间，数据量越大，占用空间越大
  3. 会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护

Mysql语句执行顺序

from -> join -> on -> where -> group by -> avg,sum… -> having -> select -> distinct -> order by -> limit

Mysql实际优化案例

遵循的要点：

  • 最大化利用索引。
  • 尽可能避免全表扫描。
  • 减少无效数据的查询。

实战经验：

  1. 避免回表查询，首先把主键查询出来，然后根据id批量查询，封装map拼接

  2. 避免函数查询，不用例如DATE_ADD这样的函数，在代码把参数封装好

  3. 避免子查询，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。

  4. LIKE双百分号无法使用到索引，最左匹配原则

  5. 用in代替or查询（or会导致扫表，对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的）

  6. 使用合理的分页方式解决深度分页的问题（可以取前一页的行数id，然后根据这个的id来限制下一页的起点。比如此列中，上一页的id是866612。SQL可以采用如下的写法：）

    select id,name from product where id> 866612 limit 20

  7. 多表关联查询时，小表驱动大表，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。

  8. 拆分复杂 SQL 为多个小 SQL，避免大事务（用户选择的时间范围过大，造成查询缓慢，分段进行查询）

  9. 尽量少的使用非索引字段进行排序

  10. 避免隐式转换，造成的索引失效

  11. update或者delete的sql一定要根据主键进行更新，先把id查询出来在更新（因为可能会用到表锁或者间歇锁，临界锁）

  12. DDL操作尽量在业务低峰时候操作，以免影响正常业务（如果当前表正在执行一个慢查询，此时添加索引，就可能会锁表）（要不就是先创建副本，再将表名改掉。）

  13. 批量insert语句代替循环单个insert，可以减少网络传输的 IO。但是insert语句最好不要过多数据（综合考虑sql语句大小，服务器最大数据包大小）

Mysql怎么高可用

  1. 主从复制

    binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。

    I/O 线程 ：负责从主服务器上读取- 二进制日志，并写入从服务器的中继日志（Relay log）。

    SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

  2. 读写分离

    • 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
    • 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
    • 增加冗余，提高可用性。

一条update语句怎么加锁的

  1. id主键 + RR

    id是主键是，此SQL语句只需要在id = 10这条记录上加上X锁即可。

  2. id唯一索引 + RR

    若id列是Unique列，其上有Unique索引，那么SQL需要加两个X锁，一个对应于id Unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的(name = ‘e’, id = 10)的记录。

  3. id不唯一索引+RR

    在RR隔离级别下，id列上有非唯一索引，对于上述的SQL语句；首先，通过id索引定位到第一条满足条件的记录，给记录加上X锁，并且给Gap加上Gap锁，然后在主键聚簇索引上满足相同条件的记录加上X锁，然后返回；之后读取下一条记录重复进行。直至第一条出现不满足条件的记录，此时，不需要给记录加上X锁，但是需要给Gap加上Gap锁吗，最后返回结果。

  4. id不唯一索引+RR

    在RR隔离级别下，id列上有非唯一索引，对于上述的SQL语句；首先，通过id索引定位到第一条满足条件的记录，给记录加上X锁，并且给Gap加上Gap锁，然后在主键聚簇索引上满足相同条件的记录加上X锁，然后返回；之后读取下一条记录重复进行。直至第一条出现不满足条件的记录，此时，不需要给记录加上X锁，但是需要给Gap加上Gap锁吗，最后返回结果。

  5. 无索引+RR

    在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表上的所有记录，并且所有的Gap加上Gap锁，杜绝所有的 delete/update/insert 操作。当然在MySQL中，可以触发 semi-consistent read来缓解锁开销与并发影响，但是semi-consistent read本身也会带来其他的问题，不建议使用。

创建索引注意什么

  1. 限制表上的索引数目。
  2. 避免在取值朝一个方向增长的字段（例如：日期类型的字段）上，建立索引
  3. 对复合索引，按照字段在查询条件中出现的频度建立索引
  4. 不要在有大量相同取值的字段上，建立索引。
  5. 对于那些定义为text, image和bit数据类型的列不应该增加索引。
  6. 当修改性能远远大于检索性能时，不应该创建索引。
二俊89757
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
MySQL必备的常见知识点汇总整理
09-08
MySQL是世界上最流行的关系型数据库管理...理解并熟练运用这些MySQL知识点，将有助于你在实际开发和管理数据库时更加得心应手。不断实践和深入学习，可以进一步提升你的数据库技能，应对更复杂的数据库设计和管理挑战。
MySQL数据库基础知识整理
10-01
MySQL数据库基础知识整理，增删改查,排序、分组、连接查询、对表的增删改查、条件查询、数据库的安装、卸载、下载、配置，对表结构的修改，约束等等
MySql 比较难的一些知识点,开发笔记（基于注解开发）
qq_43916164的博客
06-10 283
MySql 笔记（基于注解开发） 1，动态SQL，实现不同的条件情况下，执行不同的SQL操作 @Select({"<script>", "select vi.*,ci.*,co.user_name as counselorName,de.user_name as dealName " + "from sy...
mysql innodb 写锁_JAVA面试系列 - Mysql InnoDB 锁机制介绍
weixin_34532234的博客
01-19 144
概述在mysql中，锁机制看起来很复杂，一堆名词：排他锁、共享锁、表锁、间隙锁、意向锁等等，搞的初学者云里雾里。同时锁的相关知识又跟事务隔离级别、索引等概念有千丝万缕的关系，是面试中的常规问题。上面的脑图是对mysql锁相关知识的一个梳理，希望能够帮到大家，让大家能够：能让我们在特定的场景下派得上用场更好把控自己写的程序在跟别人聊数据库技术的时候可以搭上几句话构建自己的知识库体系！在面试的时候不虚...
MySQL中使用INNER JOIN来实现Intersect交集操作
alanzyy的技术路程
06-17 1万+
MySQL中使用INNER JOIN来实现Intersect交集操作
[mysql] 7.普通索引与唯一索引的选择
陈守一的博客
01-18 131
普通索引： 唯一任务是加快对数据的访问速度，因此，应该只为那些最经常出现在查询条件（WHERE column=）或者排序条件（ORDERBY column）中的数据列创建索引 唯一性索引： 如果确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候，就应该用关键字UNIQUE把它定义为一个唯一索引，Mysql会在有新纪录插入数据表时，自动检查新纪录的这个字段的值是否已经在某个记录的这个字段里出现过了 两者区别以以下场景为例 假如有一个表： 主键列为 ID ，表中有字段 k，并且在 k 上有索引
MySQL高频基本面试问题整理
热门推荐
张彦峰的博客
04-07 170万+
总结MySQL高频基本面试问题整理，用于快速查缺补漏
Mysql数据库基础知识整理
最新发布
Kenneth0617的博客
07-14 7923
关系型数据库；以表格形式存储数据；一个数据库中包含多张表；在mysql中，事务是一种机制、一个操作序列，是访问和更新数据库的程序执行单元。事务中包含一个或多个数据库操作命令，会把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行.MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
MySQL的知识点整理（学习笔记）
随心笔记
06-06 7051
MySQL是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。Mysql是开源的，所以你不需要支付额外的费用。Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
【MySQL】数据库基础知识
m0_51064412的博客
05-20 5万+
文章目录一. 什么是数据库二. 为什么要有数据库三. 数据库分类四. 数据库的基本使用1. MySQL安装2. 检查MySQL后端服务器是否启动3. 连接MySQL服务器 一. 什么是数据库 数据库（database）是一整套的数据存储容灾解决方案。是用来组织、存储和管理数据的仓库。 当今世界是一个充满着数据的互联网世界，充斥着大量的数据。数据的来源有很多，比如出行记录、消费记录、浏览的网页、发送的消息等等。除了文本类型的数据，图像、音乐、声音都是数据。 为了方便管理互联网世界中的数据，就有了数据库管理系统
最全MySql知识整理 一
sison1999的博客
04-07 2697
MySQL 整理 数据库概念 ACID ACID是事物的四个特性。分别是 原⼦性 (Atomicity)、⼀致性(*Consistency)、隔离性 (Isolation)、持久性(Durability) 。 原⼦性是指事物是⼀个不可分割的⼯作单位，事 物中的操作要么都发⽣，要么都不发⽣。最经典 的就是转账案例，我们把转⼊和转出当做⼀个事 物的话，就需要在SQL中显式指定开启事务。 ⼀致性是说数据库事务不能破坏关系数据的完整 性以及业务逻辑上的⼀致性 。我们可以从数据库 层⾯和业务层⾯两⽅⾯来保证，数据库
mysql基础知识和mysql优化整理
12-24
本文将深入探讨MySQL的基础知识和优化策略。 一、MySQL基础知识 1. 数据库与表：MySQL中的数据库是一个逻辑存储单元，用于组织相关数据。表是数据库中的基本元素，由列和行构成，用来存储具体的数据。 2. 数据...
MySQL知识整理.md
08-07
MySQL知识整理，包含一些常见面试题和基本知识点。对自己一年的学习做个总结，不喜勿喷，欢迎交流。本人小白一枚，只算入门级别。留下第一份足迹
最全的mysql查询语句整理
09-10
MySQL查询语句是数据库操作的...以上内容涵盖了MySQL查询语句的多种常见用法，包括选择、过滤、排序、分组、计算等操作，是数据库查询的基础知识。理解并熟练掌握这些语句，能够有效地在MySQL数据库中获取和处理数据。
MySQL数据库基础知识，基础操作详细整理
GCTTTTTT的博客
11-16 1万+
1. 数据库概念介绍，应用场景 2. 关系型数据库Mysql a) 数据库的组成部分介绍（服务器、数据库、表、行和列) b) Mysql数据库安装、navicat数据库安装与基本使用 c) mysql数据类型 d) 数据库级别操作（查看现有数据库、建库、删库、切换数据库等） e) 表级别操作（建表、删表、清空表格、增加删除字段等） f) 约束(非空、主键、外键、唯一键等约束、自增字段） g) 增加数据：insert 语句 h) 查找数据：select语句 i) 条件：where子句、逻辑运算符等
这可能是Solr比较全的知识整理
山楂小王的博客
10-19 4508
Solr介绍 Solr是建立在Apache Lucene ™之上的流行，快速，开放源代码的企业搜索平台。Solr具有高度的可靠性，可伸缩性和容错性，可提供分布式索引，复制和负载平衡查询，自动故障转移和恢复，集中式配置等。Solr为许多世界上最大的互联网站点提供搜索和导航功能。 文档 Solr官网 Solr8.3官方文档 Solr W3Cschool官方文档 用法 SolrJ SolrJ是一个API，它使用Java（或任何基于JVM的语言）编写的应用程序可以轻松地与Solr交谈。SolrJ隐藏了许多连接到So
聊聊接口幂等性
山楂小王的博客
10-19 479
一、什么是幂等性 幂等性是系统服务对外一种承诺，承诺只要调用接口成功，外部多次调用对系统的影响是一致的。声明为幂等的服务会认为外部调用失败是常态，并且失败之后必然会有重试。 二、接口幂等性 对于同一笔支付信息如果我其中某一次处理成功了，我虽然又接收到了消息，但是这时我不处理了，即保证接口的 幂等性。 维基百科上的定义： 幂等（idempotent、idempotence）是一个数学与计算机学概念，常见于抽象代数中。 **在编程中一个幂等操作的特点是其任意多次执行所产生的影响均与一次执行的影响相同。**幂等
这可能是JVM比较全的知识整理
山楂小王的博客
10-19 294
Java内存结构 线程私有的： 程序计数器（当前线程所执行的字节码的行号指示器） 虚拟机栈（每次方法调用的数据都是通过栈传递的。局部变量表/对象引用/实例方法） 本地方法栈（有一个native关键字修饰，而且不存在方法体，这种用native修饰的方法就是本地方法） 线程共享的 堆（栈管运行，堆管存储，堆是垃圾收集器管理的主要区域） 方法区（存放类的元数据信息，常量和静态变量） 直接内存（非运行数据区的一部分） Java内存模型 概念 Java内存模型(Java Memory
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值