1. 1. 事务
  2. 2. mysql中的事务特性
  3. 3. 隔离等级以及脏读幻读不可重复读
  4. 4. MVCC在MySQL的InnoDB中的实现
  5. 5. MVCChttps://blog.csdn.net/weixin_34341117/article/details/91425439
  6. 6. 乐观锁悲观锁的实现
  7. 7. 间隙锁https://blog.csdn.net/qq_42214953/article/details/106148054
  8. 8. 共享锁、排他锁
  9. 9. 缓存穿透 空结果缓存 布隆过滤器
  10. 10. 缓存击穿
  11. 11. 缓存雪崩 大量缓存相同的过期时间
  12. 12. 使用集群缓存，保证缓存服务的高可用
  13. 13. ehcache本地缓存 + Hystrix限流&降级,避免MySQL被打死
  14. 14. 什么时候建立索引？
  15. 15. 聚簇索引和非聚簇的区别
  16. 16. 为什么B+不用B
  17. 17. MySQL的自增ID用完了，会出现什么问题？
  18. 18. mysql主从复制原理
  19. 19. binlog日志的格式？row格式的优点
  20. 20. 索引分类
  21. 21. mysql索引的底层实现https://www.cnblogs.com/boothsun/p/8970952.html#autoid-6-1-0
  22. 22. 聚簇索引的优缺点
  23. 23. mysql联合索引的结构 联合索引失效的场景
  24. 24. 什么是覆盖索引？
  25. 25. 当前读(current read)
  26. 26. 快照读
  27. 27. 如何快速插入百万级数据
  28. 28. 建立了哪些索引，联合索引哪个放在了前面
  29. 29. 哪些情况下数据库索引会失效？
  30. 30. BinLog
  31. 31. Redo Log 持久化
  32. 32. Undo Log 回滚
  33. 33. 什么是DCL？
  34. 34. DDL？
  35. 35. DML？
  36. 36. DQL？
  37. 37. 分库分表
  38. 38. SQL在是如何在mysql运行的？
  39. 39. 一条SQL语句执行得很慢的原因有哪些
  40. 40. MYSQL的调优
  41. 41. Explain命令
  42. 42. SQL慢查询
  43. 43. 数据库设计三大范式
  44. 44. SQL写横标纵表的转换？
  45. 45. 查看建表语句

mysql

, ,

阅读这个https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

事务

事务是数据库访问并可能操作数据项的一个操作序列，该序列的操作要么执行要么全部不执行。

mysql中的事务特性

[四大特性ACID]:

A 原子性**一次操作是不可分割的，要么全部成功，要么全部失败。比如我们的转账操作，不允许出款方成功，收款方失败这种情况，要么都成功，要么多失败，不可能出现中间状态。**InnoDB 引擎使用 undo log（归滚日志）来保证原子性操作，你对数据库的每一条数据的改动（INSERT、DELETE、UPDATE）都会被记录到 undo log 中，执行失败的时候就会回滚。

C一致性 使数据在执行前后处于合法的状态。（通过原子性、隔离性、持久性来实现一致性）

I 隔离性 多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。隔离性可能会引入脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）等问题，为了解决这些问题就引入了“隔离级别”的概念。

​ InnoDB 引擎是如何保证隔离性的？利用锁和 MVCC 机制。这里简单的介绍一下 MVCC 机制，也叫多版本并发控制，在使用 READ COMMITTD、REPEATABLE READ 这两种隔离级别的事务下，每条记录在更新的时候都会同时记录一条回滚操作，就会形成一个版本链，在执行普通的 SELECT 操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

D 持久性

事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。当数据库宕机重启的时候，会将 redo log 中的内容恢复到数据库中，再根据 undo log和 binlog 内容决定回滚数据还是提交数据。

https://yq.aliyun.com/articles/531757

隔离等级以及脏读幻读不可重复读

脏读：还没提交就读了，对应读未提交，未提交读

不可重复读：A事务在提交之前，两次读取得到的值不一样，可能是B事务对数据进行了修改（侧重编辑或删除）-对应读已提交

​ 隔离等级为读已提交的时候会出现不可重复读的问题

幻读：A事务在提交之前，两次读取得到的值不一样，可能是B事务对数据进行了修改（侧重于插入）-

​ 隔离等级为可重复读时会出现幻读问题

该sql第一次读取到数据后。就将这些数据加锁，其他事务无法改动这些数据。就能够实现可反复读了。但这样的方法却无法锁住insert的数据。所以当事务A先前读取了数据，或者改动了所有数据，事务B还是能够insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据。这就是幻读。不能通过行锁来避免。

须要Serializable隔离级别 。读用读锁，写用写锁，读锁和写锁相互排斥，这么做能够有效的避免幻读、不可反复读、脏读等问题，但会极大的减少数据库的并发能力。

MVCC在MySQL的InnoDB中的实现

在InnoDB中，会在每行数据后加入两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。 在实际操作中。存储的并非时间，而是事务的版本，每开启一个新事务，事务的版本就会递增。 在可重读Repeatable reads事务隔离级别下：

  • SELECT时。读取创建版本<=当前事务版本。删除版本为空或>当前事务版本。
  • INSERT时，保存当前事务版本为行的创建版本
  • DELETE时，保存当前事务版本为行的删除版本
  • UPDATE时，插入一条新纪录。保存当前事务版本为行创建版本，同一时候保存当前事务版本到原来删除的行
  • 通过MVCC，尽管每行记录都须要额外的存储空间，很多其它的行检查工作以及一些额外的维护工作。但能够降低锁的使用，大多数读操作都不用加锁，读数据操作非常easy，性能非常好，而且也能保证仅仅会读取到符合标准的行。也仅仅锁住必要行。

MVCChttps://blog.csdn.net/weixin_34341117/article/details/91425439

乐观锁悲观锁的实现

https://www.cnblogs.com/kyoner/p/11318979.html

  1. 乐观锁是一种思想，具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。 之所以叫乐观，因为这个模式没有从数据库加锁。

    可重复读的时候，如果Ainsert 但B提前Insert并且提交，则A会报错。

  2. 悲观锁是读取的时候为后面的更新加锁，之后再来的读操作都会等待。这种是数据库锁 乐观锁优点程序实现，不会存在死锁等问题。他的适用场景也相对乐观。阻止不了除了程序之外的数据库操作。 悲观锁是数据库实现，他阻止一切数据库操作。

    序列化的实现方式

间隙锁https://blog.csdn.net/qq_42214953/article/details/106148054

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)” ，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）。举例来说， 假如 user 表中只有 101 条记录， 其 userid 的值分别是 1,2,…,100,101， 下面的 SQL：

Select * from user where userid > 100 for update;

是一个范围条件的检索，InnoDB 不仅会对符合条件的 userid 值为 101 的记录加锁，也会对userid 大于 101（但是这些记录并不存在）的“间隙”加锁，防止其它事务在表的末尾增加数据。

InnoDB 使用间隙锁的目的，为了防止幻读，以满足串行化隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了 userid 大于 100 的任何记录，那么本事务如果再次执行上述语句，就会发生幻读。

但是只能解决部分幻读问题

共享锁、排他锁

共享锁 select in share mode 是一个行级读锁 并发读，写要加锁

排他锁 select for update 行级写锁 加完锁之后其余事务不能加锁

缓存穿透 空结果缓存 布隆过滤器

https://www.cnblogs.com/lazyegg/p/12857374.html

BloomFilter 是由一个固定大小的二进制向量或者位图（bitmap）和一系列映射函数组成的。

在初始状态时，对于长度为 m 的位数组，它的所有位都被置为0，如下图所示：

缓存击穿

0.热点数据设置为永远不过期

1.后台刷新 后台定义一个job(定时任务)专门主动更新缓存数据.

2.分级缓存

采用 L1 (一级缓存)和 L2(二级缓存) 缓存方式，L1 缓存失效时间短，L2 缓存失效时间长。 请求优先从 L1 缓存获取数据，如果 L1缓存未命中则加锁，只有 1 个线程获取到锁,这个线程再从数据库中读取数据并将数据再更新到到 L1 缓存和 L2 缓存中，而其他线程依旧从 L2 缓存获取数据并返回。

3.多个线程同时去查询数据库的这条数据，那么我们可以在第一个查询数据的请求上使用一个 互斥锁来锁住它。其他的线程走到这一步拿不到锁就等着，等第一个线程查询到了数据，然后做缓存。后面的线程进来发现已经有缓存了，就直接走缓存。

缓存雪崩 大量缓存相同的过期时间

4.2.1 事前：

  • 使用集群缓存，保证缓存服务的高可用

这种方案就是在发生雪崩前对缓存集群实现高可用，如果是使用 Redis，可以使用 主从+哨兵 ，Redis Cluster 来避免 Redis 全盘崩溃的情况。

4.2.2 事中：

  • ehcache本地缓存 + Hystrix限流&降级,避免MySQL被打死

使用 ehcache 本地缓存的目的也是考虑在 Redis Cluster 完全不可用的时候，ehcache 本地缓存还能够支撑一阵。

使用 Hystrix进行限流 & 降级 ，比如一秒来了5000个请求，我们可以设置假设只能有一秒 2000个请求能通过这个组件，那么其他剩余的 3000 请求就会走限流逻辑。

然后去调用我们自己开发的降级组件（降级），比如设置的一些默认值呀之类的。以此来保护最后的 MySQL 不会被大量的请求给打死。

4.2.3 事后：

开启Redis持久化机制，尽快恢复缓存集群

一旦重启，就能从磁盘上自动加载数据恢复内存中的数据。

什么时候建立索引？

主键 外键需要建立索引

经常join的需要建立索引

数据量比较大的表应该创建索引；

经常在where中的列

聚簇索引和非聚簇的区别

**聚簇索引：**将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

**非聚簇索引：**将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

为什么B+不用B

一般来说索引非常大，所以为了减少内存的占用，索引也会被存储在磁盘上。

那么Mysql如何衡量查询效率呢？磁盘IO次数，B-树（B类树）的特定就是每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数，当查询数据的时候，最好的情况就是很快找到目标索引，然后读取数据，使用B+树就能很好的完成这个目的，但是B-树的每个节点都有data域（指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时啊！），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。这是优点之一。 　　另一个优点是什么，B+树所有的Data域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦。 (数据库索引采用B+树的主要原因是 B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）)

MySQL的自增ID用完了，会出现什么问题？

不会在增加，报错

mysql主从复制原理

做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。 架构的扩展。业务量越来越大,I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的评率，提高单个机器的I/O性能。 读写分离，使数据库能支持更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

1–在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力;（主库写，从库读，降压） 2–在从主服务器进行备份，避免备份期间影响主服务器服务;（确保数据安全） 3–当主服务器出现问题时，可以切换到从服务器。（提升性能）

1.binlog输出线程:每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。在从库里，当复制开始的时候，从库就会创建两个线程进行处理： 2.从库I/O线程:当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。

3.从库的SQL线程:从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

binlog日志的格式？row格式的优点

undo log的主要作用是用作事务的回滚和实现mvcc功能，因为mvcc的问题，需要对undolog随机读

redolog用来保证事务的原子性和持久性数据库运行阶段不需要读redolog进行读取。 所有线程公用一份redolog buffer。 在事务执行期间，redolog是记录在redolog buffer中的

binlog 其实是数据库server层的日志，一般用作主从同步和数据恢复，数据格式有statement、row、还有mixed.statement是sql格式的，row记录了行的变更（从什么到什么），mixed是这两种的混合形式.binlog是在两阶段提交 preparre 和 commit之间，也是要先write到page cache（文件系统缓冲区），然后调用fsync进行刷盘

索引分类

BTree索引，哈希索引，全文索引

索引的本质：索引是数据结构。

mysql索引的底层实现https://www.cnblogs.com/boothsun/p/8970952.html#autoid-6-1-0

这个比较好http://blog.codinglabs.org/articles/theory-of-mysql-index.html

基本上都有

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

聚簇索引的优缺点

聚簇索引的优点 1、聚簇索引将索引和数据行保存在同一个B-Tree中，查询如果是通过聚簇索引查询的，可以直接获取数据，而非聚簇索引查到的是数据所在的指针，还需要进行一次I/O获取数据，因此聚簇索引通常比非聚簇索引查找更快。 2、对主键进行范围查询的效率很高，因为其数据是按照主键排列的 3、减小了移动数据或者数据页面分裂时维护二级索引的开销，因为InnoDB不需要更新二级索引的行指针

聚簇索引的缺点 1、聚簇索引最大限度地提高了I/O密集型应用的性能，但如果数据都存放在内存中，则访问顺序就不那么重要了，非聚簇索引也能很快在内存中查到数据，那聚簇索引也没什么优势。 2、插入速度严重依赖于插入顺序。按照主键顺序往InnoDB中进行数据导入是最快的。如果不是按照主键插入，最好在导入完成后使用OPTIMIZE TABLE命令重新组织一下表。事实上，如果不是按照顺序插入，可能会引起大量的页面分裂和数据移动 3、聚簇索引在插入新行和更新主键时，可能导致“页分裂”问题：当插入到某个已满的叶子结点时，B+树会分裂成两个页来容纳新插入的行数据。页分裂会导致表占用更多的磁盘空间（不要用UUID或随机数做主键，而应该使用单调递增的值做主键）。 4、聚簇索引可能导致全表扫描速度变慢，因为可能需要加载物理上相隔较远的页到内存中（需要耗时的磁盘寻道操作）。 5、二级索引访问数据行需要两次索引查找，解决办法是实现索引覆盖，直接在二级索引就能获取要所有需要的字段了，这样就不用再去主键索引搜索。 6、由于二级索引保存了主键列，二级索引会占更大的空间(所以选用一个短主键是有利的)。

mysql联合索引的结构 联合索引失效的场景

https://www.zhihu.com/question/304037770/answer/541460420

比如有一个表的索引是（年龄、姓名）的联合索引。

它也是一颗B+树，和其他非聚簇索引不同的是，它是按多个列从左往右进行排序的，叶子节点还是存储主键的值。所以如果没有使用最左边的那一列来筛选索引就会失效。最左匹配原则

什么是覆盖索引？

如果一个索引包含了所有需要select的信息，那么就不用回表了。

MySQL的引擎有哪几种，它们有什么区别（常规题）

innoDB 支持事务、外键 聚集索引 支持表、行级锁

MyISAM 非聚集索引 可以压缩数据 仅支持表级锁

当前读(current read)

select … lock in share mode

select … for update

insert

update

delete

快照读

RR级别下的select

如何快速插入百万级数据

java可以使用 预编译PreparedStatement 批次执行

建立了哪些索引，联合索引哪个放在了前面

哪些情况下数据库索引会失效？

https://www.cnblogs.com/itsharehome/p/4972948.html

最左匹配复合索引的时候

使用Like 且%在 最前面的时候

查询条件中含有函数或者表达式

条件带or

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

如果它认为全表扫描比走索引快

BinLog

是服务层的Server Layer

Redo Log 持久化

是引擎层的

重做日志是在崩溃恢复期间使用的基于磁盘的数据结构，以纠正由不完整事务编写的数据。

因为mysql一开始只是对内存中的数据进行操作，然后将命令写入RedoLog

Undo Log 回滚

撤消日志是与单个读写事务关联的撤消日志记录的集合。 undo log记录包含有关如何撤消事务到群集索引记录的最新更改的信息。如果另一个事务需要将原始数据视为一致读取操作的一部分，则从撤消日志记录中检索未修改的数据。 undo日志存在于undo日志段中，这些段包含在回滚段内。回滚段驻留在系统表空间中，在撤消表空间中以及临时表空间中。

什么是DCL？

数据库控制语言 grant user

REVOKE user

commit

DDL？

craete table/view/index/

DML？

insert/update/delete

DQL？

select

分库分表

在项目中我将包裹表分表，因为包裹表列数太多，而有刚好可以通过寄件人信息、收件人信息、包裹信息来拆分为三个表，然后通过主键关联；联合查询性能不好

id 、select_type、table、type、possible keys、key、keylen、ref、rows、extra

SQL在是如何在mysql运行的？

image-20210421141757250

分析器

优化器

执行器（引擎提供）

一条SQL语句执行得很慢的原因有哪些

https://zhuanlan.zhihu.com/p/62941196

MYSQL的调优

http://blog.codinglabs.org/articles/theory-of-mysql-index.html

sql语句的优化，like or in 慎用 避免*

对where中经常判断的建立索引，并分析可能失效的时候

Explain命令

Id：代表执行select子句或操作表的顺序 Select_type：查询的类型,主要用于区别普通查询,联合查询,子查询等复杂查询

simple:简单的select查询,查询中不包含子查询或union查询 primary:查询中若包含任何复杂的子部分,最外层查询则被标记为primary Subquery： 在select 或where 列表中包含了子查询 Derived： 在from列表中包含的子查询被标记为derived,mysql会递归这些子查询,把结 果在临时表里 Union： 做第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为derived union result： 从union表获取结果的select Table：显示一行的数据时关于哪张表的

Type：查询类型从最好到最差依次是:system>const>eq_ref>ref>range>index>All,一般情况下,得至少保证达到range级别,最好能达到ref

system:表只有一行记录,这是const类型的特例,平时不会出现 const:表示通过索引一次就找到了,const即常量,它用于比较primary key或unique 索引,因为只匹配一行数据,所以效率很快,如将主键置于where条件中,mysql就能将该 查询转换为一个常量 eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯 一索引扫描 ref:非唯一性索引扫描,返回匹配某个单独值的行,它可能会找到多个符合条件的行,所以 他应该属于查找和扫描的混合体 range:只检索给定范围的行,使用一个索引来选择行,如where语句中出现了 between,<,>,in等查询,这种范围扫描索引比全表扫描要好，因为它只需要开始于索引 的某一点，而结束于另一点，不用扫描全部索引。 index:只遍历索引树,这通常比All快,因为索引文件通常比数据文件小,index是从索引 中读取,all从硬盘中读取 all:全表扫描,是最差的一种查询类型 Possible_keys：显示可能应用在这张表中的索引,一个或多个,查询到的索引不一定是真正被用到的

Key：实际使用的索引,如果为null,则没有使用索引,因此会出现possible_keys列有可能被用到的索引,但是key列为null,表示实际没用索引。

Key_len：表示索引中使用的字节数,而通过该列计算查询中使用的 索引长度,在不损失精确性的情况下,长度越短越好,key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即,key_len是根据表定义计算而得么不是通过表内检索出的

Ref：显示索引的哪一列被使用了,如果可能的话是一个常数,哪些列或常量被用于查找索引列上的值

Rows：根据表统计信息及索引选用情况,大只估算出找到所需的记录所需要读取的行数

Extra：包含不适合在其他列中显式但十分重要的额外信息

SQL慢查询

主要的原因：全表扫描、全索引扫描、索引过滤性不好。

超过 long_query_time 参数设定的时间阈值（默认10s），就被认为是慢查询。

通过set global slow_query_log=1开启慢查询日志。

通过set log_queries_not_using_indexes=1设置是否记录未使用索引的sql。

即使使用到了索引也有可能会导致慢查询，比如有id为主键的一个表，id全部>0，但是你如果使用where id >0那就其实还是走了全表扫描，主键只使用在了第一个地方。

数据库设计三大范式

  • 第一范式(确保每列保持原子性) 地址拆分为省市区
  • 第二范式(确保表中的每列都和主键相关)
  • 第三范式(确保每列都和主键列直接相关,而不是间接相关) 去除冗余 a->b -> c 拆为ab和bc

SQL写横标纵表的转换？

纵表变横表

聚合函数[max或sum]配合case语句

1
2
3
4
5
6
select 姓名,
sum (case 课程 when '语文' then 成绩 else 0 end) as 语文,
sum (case 课程 when '数学' then 成绩 else 0 end) as 数学,
sum (case 课程 when '英语' then 成绩 else 0 end) as 英语
from Table_A
group by 姓名

横表变纵表

1
2
3
4
select 姓名,'语文' as 课程,语文 as 成绩 from Table_B union all
select 姓名,'数学' as 课程,数学 as 成绩 from Table_B union all
select 姓名,'英语' as 课程,英语 as 成绩 from Table_B
order by 姓名,课程 desc

查看建表语句

SHOW CREATE TABLE package