最新Mysql大厂面试必会的34问题

1、mysql的隔离级别

四种隔离级别:

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

隔离级别脏读不可重复读幻读READ UNCONMMITTED可能可能可能READ COMMITTED不可能可能可能REPRATABLE READ不可能不可能可能SERIALIZABLE不可能不可能不可能

1、 脏读:A,B两事务,A事务会读取到B事务未提交的数据,然后B因为某些原因回滚数据,所以A就读取了B没有提交的数据,也称脏数据。

2、 不可重复读:在A事务中对同一数据两次查询不一致,可能原因是在A事务提交之前B事务对该数据进行了操作

3、 幻读:当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行,就像产生幻觉一样,这就是发生了幻读。

*

不可重复读和脏读的区别 是,脏读是某一事务读取了另一个事务未提交的脏数据,而不可重复读则是读取了前一事务提交的数据。
*
幻读和不可重复读都是读取了另一条已经提交的事务,不同的是不可重复读的重点是修改,幻读的重点在于新增或者删除。
*
事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

2、MYSQL性能优化

  • 1、最大连接数优化
**修改my.ini文件(永久修改)**
  • 2、启用查询缓存

  • 一个论点是不推荐查询缓存,因为查询缓存往往弊大于利。查询缓存失效非常频繁,只要对表进行了更新,该表上的所有查询缓存就会被清空。因此,您可能花了很大力气来保存结果,而在您可以使用它们之前,您就被一次更新彻底掏空了。对于更新压力大的数据库,查询缓存的命中率会很低。除非您的业务有一个长期更新的静态表,例如系统配置表,否则对该表的查询适合于查询缓存。

    [En]

    one argument is that query caching is not recommended, because query caching often does more harm than good. Query cache invalidation is very frequent, as long as there is an update to a table, all query caches on that table will be emptied. So it’s possible that you took great pains to save the results, and before you could use them, you were completely emptied by an update. For databases with high update pressure, the hit rate of the query cache will be very low. Unless your business has a static table that is updated over a long period of time, such as the system configuration table, then the query on this table is suitable for query caching.*

  • 3、引擎优化

MyISAM存储引擎:

场景 :如果表主要是用于插入新记录和读出记录,那么选择MyISAM能实现处理高效率。

优点:MyISAM引擎能提供较高的查询效率,适用于对数据进行频繁查询操作的数据表(InnoDB提供提交、回滚、崩溃恢复能力及并发控制能力,适用于对数据更新操作频率高的数据表)

  • 4、索引优化

这里是便于查询可以设置索引,让查询效率变高.

聚合索引按照从左到右的匹配原则。也就是必须先匹配ID才能匹配name查询。

全文检索的查询方式:

SELECT * FROM article WHERE MATCH(title, content) AGAINST(‘查询字符串’)

全文索引只适合MyISAM引擎的数据表。并且只能对英文进行检索

  • 5、SQL语句优化

SQL优化的重心是查询优化,查询优化的重心是建立索引。所以查询优化主要是避免出现导致索引失效的查询。

①避免在索引列上出现null。

②不要在索引列上进行算术运算。:select age+1 from user

③避免实现!=或者<>、is null或者is not null、in等可能导致全表遍历的操作。

④模糊查询只能使用右边%。

⑤where语句后尽可能少用小括号、或者不要出现小括号嵌套小括号。

3、索引详解

&#x7D22;&#x5F15;是存储引擎用于提高数据库表的访问速度的一种 数据结构 。。常见的索引结构有:Hash、B数,B+树。

&#x7D22;&#x5F15;&#x7684;&#x4F5C;&#x7528;就是相当于目录的作用。打个比方: 我们在查字典的时候,如果没有目录,那我们就只能一页一页的去找我们需要查的 那个字,速度很慢。如果有目录了,我们只需要先去目录里查找字的位置,然后直接翻到那一页就行了。

数据是存储在磁盘上的,查询数据时,如果没有索引,会加载所有的数据到内存,依次进行检索,读取磁盘次数较多。有了索引,就不需要加载所有数据,因为B+树的高度一般在2-4层,最多只需要读取2-4次磁盘,查询速度大大提升。

  • 优点:

    加快数据查找的速度

    为用来排序或者是分组的字段添加索引,可以 加快分组和排序的速度

    加快表与表之间连接的速度

  • 缺点:

    建立索引需要 占用物理空间

    会降低表的增删改的效率,因为每次对表记录进行增删改,需要进行 动态维护索引 ,导致增删改时间变长

注意: 使用索引一定能提高查询性能吗?

在大多数情况下,索引查询比全表扫描更快。但是,如果数据库中的数据量很小,那么使用索引并不一定会带来很大的改进。

[En]

In most cases, index queries are faster than full table scans. However, if the amount of data in the database is small, then the use of indexes may not necessarily bring great improvement.

哈希索引是基于哈希表实现的,对于每一行数据,存储引擎会对索引列进行哈希计算得到哈希码,并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的,将哈希码的值作为哈希表的key值,将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1),一般多用于精确查找。

是键值对的集合,通过键(key)即可快速取出对应的值(value),因此哈希表可以快速检索数据(接近 O(1)

  • *Hash 冲突 问题

​ 也就是说多个不同的 key 最后得到的 index 相同。通常情况下,我们常用的解决办法是 链地址法 。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap就是通过链地址法来解决哈希冲突的。不过,JDK1.8 以后 HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

  • *既然哈希表这么快, 为什么MySQL 没有使用其作为索引的数据结构呢?

1.Hash 冲突问题 :我们上面也提到过Hash 冲突了,不过对于数据库来说这还不算最大的缺点。

2.Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点: 假如我们要对表中的数据进行排序或者进行范围查询,那 Hash 索引可就不行了。

B 树,全称为 多路平衡查找树 ,B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是

Balanced (平衡)的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

在执行搜索操作时,首先在根节点进行二进制搜索,以找到

[En]

When carrying out the search operation, first do a binary search at the root node to find the

key所在的指针,然后递归地在指针所指向的节点进行查找。直到查找到叶子节点,然后在叶子节点上进行二分查找,找出

key所对应的数据项。

  • *B 树& B+树两者有何异同呢?

7、Hash索引和B+树索引的区别?

  • 哈希索引 不支持排序 ,因为哈希表是无序的。
  • 哈希索引 不支持范围查找 。
  • 哈希索引 不支持模糊查询 及多列索引的最左前缀匹配。
  • 因为哈希表中会 存在哈希冲突 ,所以哈希索引的性能是不稳定的,而B+树索引的性能是相对稳定的,每次查询都是从根节点到叶子节点。

8、为什么B+树比B树更适合实现数据库索引?

*

由于B+树的数据都存储在叶子结点中,叶子结点均为索引,方便扫库,只需要扫一遍叶子结点即可,但是B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B+树更加适合在区间查询的情况,而在数据库中基于范围的查询是非常频繁的,所以通常B+树用于数据库索引。
*
B+树的节点只存储索引key值,具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出。
*
B+树的查询效率更加稳定,任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

9、索引有什么分类?

1、 主键索引 :名为primary的唯一非空索引,不允许有空值。

2、 唯一索引 :索引列中的值必须是唯一的,但是允许为空值。唯一索引和主键索引的区别是:唯一约束的列可以为 null且可以存在多个 null值。唯一索引的用途:唯一标识数据库表中的每条记录,主要是用来防止数据重复插入。创建唯一索引的SQL语句如下:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

3、 组合索引 :在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时需遵循最左前缀原则。

4、 普通索引( 单值索引)

5、 全文索引 :只有在 MyISAM引擎上才能使用,只能在 CHARVARCHARTEXT类型字段上使用全文索引。

10、什么是最左匹配原则?

如果 SQL 语句中用到了组合索引中的最左边的索引,那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(><betweenlike)就会停止匹配,后面的字段不会用到索引。

(a,b,c)建立索引,查询条件使用 a/ab/abc 会走索引,使用 bc 不会走索引。如果查询条件为

a = 1 and b > 2 and c = 3,那么a、b个字两段能用到索引,而c无法使用索引,因为b字段是范围查询,导致后面的字段无法使用索引。如下图,对(a, b) 建立索引,a 在索引树中是全局有序的,而 b 是全局无序,局部有序(当a相等时,会根据b进行排序)。

11、MySQL聚簇和非聚簇索引

都是B+树的数据结构

*

1 、聚簇索引: 叶子节点将数据存储和索引存放在一起,并且是按照一定顺序组织的,找到索引也就是找到数据,数据的物理存放顺序和索引顺序是一致的。即:只要索引相邻,那么对应的数据一定也是相邻的存放在磁盘上的
*
2 、非聚簇索引: 将数据存储和索引分开存储的,索引结构的叶子节点指向数据对应的位置

叶子节点不存储数据、存储的是数据的行地址(索引),也就是说根据索引查询到的数据行的位置,再取磁盘查找数据,这个就类似一本树的目录。

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

  • InnoDB使用的是聚簇索引,将主键组织到一个棵树中,而行数据就存储在叶子节点上,若使用”where id = 4″这样的条件查找主键,则按照B+树的检索算法,即可查找到对应的叶子节点,之后获得行数据
  • 若对name列进行条件搜索,则需要两步:
  • 第一步:在辅佐索引B+树中检索name,到达其叶子节点获取对应的主键
  • 第二部:使用主键在主键索引B+树再执行一次B+树检索操作,最终达到叶子节点可获取整行数据(重点:在于通过其他键需要建立辅佐索引)
  • 聚簇索引默认是主键,如果表中没有定义主键InnoDB会选择一个唯一且非空的索引代替。如果没有这样的索引,innoDB会隐式的定义一个主键(类似Oracle的Rowid)来作为聚簇索引。如果已经射设置了主键为聚簇索引,想希望单独设置聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最终在恢复主键设置即可

MYISAM使用的是非聚簇,非聚簇索引的两棵B+树看上去没有太大的区别,节点的结构完全一致,至少2存储的内瑞内容不一样。主键索引B+树的节点的存储了主键,辅佐索引B+树存储了辅佐键,表的数据在独立的地方,这两课B+树的叶子节点都使用地址指向真正的表数据,对表数据来说,这两个键没有任何差别,由于索引树是独立的,通过辅佐键检索无需访问主键的索引树

  • 问题:每次使用辅佐索引检索都需要经过两次的B+树查找,看上去聚簇索引的效率明显低于非聚簇索引,这不是多次一举吗,聚簇索引的优势在哪呢?

1、 由于行数据和聚簇索引的叶子节点存储在一起,在同一页会有多条行数据,访问同一数据页不同行记录时,已经把页的加载到Buffer(缓冲器),再次访问的时,会再内存中完成访问,不必再访问磁盘,这样主键和行数据是一起载入内存的,找到叶子节点就立刻将行数据返回了,如果按照ID来组织数据,获取数据更快

2、 辅佐索引的叶子节点,存储主键值,而不是数据的存放地址。好处是当行数据发生变化时,索引树的节点也需要分裂变化;或者是我们需要查找的数据,在上一次IO读写的缓存中没有,需要发送一次新的IO操作时,可以避免对辅佐索引的维护工作,只需要维护聚簇索引树就好。另外好处就是,因为辅佐索引存放的时主键值,减少辅佐索引占用的存储空间大小

12、什么是覆盖索引?

select的数据列只用从索引中就能够取得,不需要 回表 进行二次查询,也就是说查询列要被所使用的索引覆盖。对于

innodb表的二级索引,如果索引能覆盖到查询的列,那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值,而哈希索引、全文索引不存储索引列的值,所以MySQL使用b+树索引做覆盖索引。

13、索引的设计原则?

*

索引列的 区分度越高 ,索引的效果越好。比如使用性别这种区分度很低的列作为索引,效果就会很差。
*
尽量使用 短索引 ,对于较长的字符串进行索引时应该指定一个较短的前缀长度,因为较小的索引涉及到的磁盘I/O较少,查询速度更快。
*
索引不是越多越好,每个索引都需要额外的物理空间,维护也需要花费时间。
*
利用 最左前缀原则 。

14、什么情况下无法利用索引(索引失效)

导致索引失效的情况:

*

1、对于组合索引,不是使用组合索引最左边的字段,则不会使用索引
*
2、查询语句中使用like关键字

以%开头的like查询如 %abc,无法使用索引;非%开头的like查询如 abc%,相当于范围查询,会使用索引

*

3、查询语句中使用OR关键字

查询条件使用 or连接

*

如果前后条件的列都是索引那么可以利用索引
*
如果前后索引中华有一列不是索引,则无法利用索引
*
4、查询条件中列类型是字符串,没有使用引号,可能会因为类型不同发生隐式转换,使索引失效
*
5、对索引列进行运算

15、什么是前缀索引?

有时需要在很长的字符列上创建索引,这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引,这样索引的长度更短,查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以 保证较高的索引选择性 。索引选择性越高查询效率就越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。

16、常见的存储引擎有哪些?

MySQL中常用的四种存储引擎分别是: MyISAM 、 InnoDB 、 MEMORY 、 ARCHIVE 。MySQL 5.5版本后默认的存储引擎为 InnoDB

17、 Mysql锁有哪些、如何理解

18、 Mysql慢查询如何优化

19、 Explain语句结果中查询的各个字段的意思

20、 Innodb如何实现事务的

Innodb通过Buffer Pool,Redo Log,Undo Log来实现事务,以一个update语句为例:

*

innodb在收到一个update语句后,会根据条件找到数据所在叶。并将该页缓存在buffer Pool中
*
执行update语句,修改Buffer Pool中的数据,也就是内存中的数据
*
针对update语句生成一个RedoLog对象,兵存入LogBuffer中
*
针对update语句生成的undoLog日志,用于事务回滚
*
如果事务提交,则把RedoLog日对象进行持久化,后续还有其他机制将Buffer Pool中修改的数据页持久化到磁盘中
*
如果事务回滚,则利用undoLog日志进行回滚

21、 Redis和Mysql如何保证数据一致

1、先更新Mysql,再更新Redis,如果redis失败,依旧可能数据不一致

2、先删除redis中数据,再更新mysql,再次查询的时候再更新数据添加到缓存中。(这个方案解决了1的问题)。但是再高并发的情况下效率比较低下,并且还是可能有数据不一致的可能。比如线程1删除后,再更新msql时,但是此时有一个线程2进来查询时,就会将mysql中老数据又查询到redis中。

3&#x3001;延迟双删。步骤:先删除redis中的数据,再更新mysql,再隔几百毫秒再删除redis中缓存数据。这样就算在更新mysql时,有其他线程读取mysql时,把老数据读到redis中也会很快被删掉

22、 索引的基本原理

该索引用于快速查询具有特定值的记录。如果没有索引,一般情况下,执行查询时需要查询整个表。

[En]

The index is used to quickly query records with specific values. If there is no index, generally speaking, when executing a query, you need to query the whole table.

索引基本原理: 把无序的数据变成有序的查询

*

1、把创建索引的列的内容进行排序
*
2、对排序结果进行倒排列
*
3、在倒排表后面内容上拼接上数据地址链
*
4、在查询的时候,先拿到倒排表内容,再取出数据地址链,从而就取出具体数据

23、 MyISAM和innoDb的区别

MyISAM:

*

不支持事务,但是每一次的查询都是原子的
*
支持表级锁,即每次操作都会对表进行加锁
*
存储表的总行数
*
一个MyISAM表有三个文件:索引文件、表结构文件、数据文件
*
采用非聚簇索引,索引文件的数据有存储指向数据文件的指针。辅助索引与主索引基本一致,但是辅助索引不用保证唯一性

InnoDb:

*

支持ACID的事务。支持事务的四种隔离级别
*
支持行级锁与外键约束:因此可以支持写并发
*
不存储总行数
*
一个InnoDb引擎存储在一个文件空间(共享表空间。表大小不受操作系统的控制,一个表可能分布在多个文化里),也可能为多个(设置为独立表空,表大小受操作系统文件大小的 控制,一般为2G)。受操作系统文件大小的限制
*
主键索引采用聚簇索引(索引的数据域存储数据文件本身),辅助索引的数据域存储主键的值;因此从辅助索引查找数据,需要先通过辅佐索引找到主键值,再访问主索引;最好使用自增主键。防止插入数据时,为维护B+树结构,文件的大整

24、ACID靠什么保证的?

*

A原子性由undo log日志保证,他记录了需要回滚的日志信息,事务回滚时撤销已近执行成功的sql
*
C 一致性由其他三大特性保证、数据上的一致需要程序代码要保证业务上的一致性
*
I 隔离性 由MVCC来保证的
*
D 持久性 由内存和redo log来保证,mysql修改数据同时在内存和redo log记录这次操作。嘎机的时候可以从redo log恢复

redo log的刷盘会在系统空闲时候进行

25、 什么是MVCC

多版本并发控制:读取数据时通过一种类似快照的方式将数据保存下来,这样读锁和写锁就不冲突了,不同的事务session会看到自己的特定版本的数据,版本链

MVCC只在READ COOMMITED 和REPETABLE READ 两个隔离级别下工作。其他两个隔离级别和MVCC冲突不兼容,因为READ UNCOMMITED 总是读取最新的数据行,而不是 符合当前事务版本的数据行。而SESRIALIZABLE则会对所有的读取行加锁

聚簇索引记录中有两个必要的隐藏列

trx_id:用来存储每次对某条聚簇索引记录进行修改的时候事务id

roll_pointer:每次对哪条索引记录进行修改的时候,都会把老版本写入undo日志中,这个roll_pointer就是保存了一个指针,它指向这条聚簇索引记录的上一个版本的位置,通过它来获取上一个版本的记录信息。(注意插入操作的undo日志没有这个属性,因为它没有老版本)

当您使用事务处理更新行记录时,将生成一个版本链,如下所示:

[En]

When you update a row record with a transaction, a version chain is generated, as follows:

undo log,作为旧版本用于回滚;

已提交读和可重复读的区别在于他们生成的ReadView的策瑜不同

无法复制加载中的内容

接下来了解下read view的概念。

read view可以理解成将数据在每个时刻的状态拍成”照片”记录下来。在获取某时刻t的数据时,到t时间点拍的”照片”上取数据。

read view内部维护一个活跃事务链表,表示生成 read view的时候还在活跃的事务。这个链表包含在创建 read view之前还未提交的事务,不包含创建 read view之后提交的事务。

不同隔离级别创建read view的时机不同。

  • read committed:每次执行select都会创建新的read_view,保证能读取到其他事务已经提交的修改。
  • repeatable read:在一个事务范围内,第一次select时更新这个read_view,以后不会再更新,后续所有的select都是复用之前的read_view。这样可以保证事务范围内每次读取的内容都一样,即可重复读。

总结 :InnoDB 的 MVCC是通过 read view 和版本链实现的,版本链保存有历史版本记录,通过 read view 判断当前版本的数据是否可见,如果不可见,再从版本链中找到上一个版本,继续进行判断,直到找到一个可见的版本。

26、关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化?

在业务系统中,除了使用主键进行查询,还有其他的在测试库上测试其耗时,慢查询的统计一般主要由运维在做,会定期的将业务中的慢查询反馈给我们、慢查询的优化首先需要慢的原因是什么?是查询条件没有命中索引?是load了需要的数据列,还是数据量过大?

因此,优化也是针对这三个方向进行的。

[En]

Therefore, the optimization is also aimed at these three directions.

  • 首先分析语句,看看是不是load了额外不需要的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中 炳不需要的列,对语句进行分析以及重写
  • 分析语句的执行计划以了解其对索引的使用,然后修改语句或索引,以使语句能够尽可能地命中索引
    [En]

    analyze the execution plan of the statement to get its use of the index, and then modify the statement or index so that the statement can hit the index as much as possible*

  • 如果不能再进行语句优化,可以考虑表中数据量是否太大,如果是,可以考虑子表。
    [En]

    if the optimization of the statement can no longer be carried out, you can consider whether the amount of data in the table is too large, and if so, you can consider sub-tables.*

25、mysql中char与varchar的区别

它们都用于存储字符串,但它们的保存方式不同。

[En]

They are all used to store strings, but they are saved in different ways.

  • char有固定的长度,而varchar属于可变长的字符类型。char 长度是固定的,不管你存储的数据是多少他都会都固定的长度。而varchar则处可变长度但他要在总长度上加1字符,这个用来存储位置

  • 1、delete和truncate仅仅删除表数据,trop连表数据和结构一起删除,打个比方delete 是单杀,truncate 是团灭,drop 是把电脑摔了。

  • 2、delete 是 DML 语句,操作完以后如果没有不想提交事务还可以回滚,truncate 和 drop 是 DDL 语句,操作完马上生效,不能回滚,打个比方,delete 是发微信说分手,后悔还可以撤回,truncate 和 drop 是直接扇耳光说滚,不能反悔。
  • 3、执行的速度上, drop>truncate>delete ,打个比方,drop 是神舟火箭,truncate 是和谐号动车,delete 是自行车。

delete 是删除一条数据,truncate是将这个表的所有数据都删除,这两种不删除表的结构,可以用

27、关于sql和MySQL的语句执行顺序

第一步:首先对from子句中的前两个表执行一个笛卡尔乘积,此时生成虚拟表 vt1(选择相对小的表做基础表)。

第二步:接下来便是应用on筛选器,on 中的逻辑表达式将应用到 vt1 中的各个行,筛选出满足on逻辑表达式的行,生成虚拟表 vt2 。

第三步:如果是outer join 那么这一步就将添加外部行,left outer jion 就把左表在第二步中过滤的添加进来,如果是right outer join 那么就将右表在第二步中过滤掉的行添加进来,这样生成虚拟表 vt3 。

第四步:如果 from 子句中的表数目多余两个表,那么就将vt3和第三个表连接从而计算笛卡尔乘积,生成虚拟表,该过程就是一个重复1-3的步骤,最终得到一个新的虚拟表 vt3。

第五步:应用where筛选器,对上一步生产的虚拟表引用where筛选器,生成虚拟表vt4,在这有个比较重要的细节不得不说一下,对于包含outer join子句的查询,就有一个让人感到困惑的问题,到底在on筛选器还是用where筛选器指定逻辑表达式呢?on和where的最大区别在于,如果在on应用逻辑表达式那么在第三步outer join中还可以把移除的行再次添加回来,而where的移除的最终的。举个简单的例子,有一个学生表(班级,姓名)和一个成绩表(姓名,成绩),我现在需要返回一个x班级的全体同学的成绩,但是这个班级有几个学生缺考,也就是说在成绩表中没有记录。为了得到我们预期的结果我们就需要在on子句指定学生和成绩表的关系(学生.姓名=成绩.姓名)那么我们是否发现在执行第二步的时候,对于没有参加考试的学生记录就不会出现在vt2中,因为他们被on的逻辑表达式过滤掉了,但是我们用left outer join就可以把左表(学生)中没有参加考试的学生找回来,因为我们想返回的是x班级的所有学生,如果在on中应用学生.班级=’x’的话,left outer join会把x班级的所有学生记录找回(感谢网友康钦谋__康钦苗的指正),所以只能在where筛选器中应用学生.班级=’x’ 因为它的过滤是最终的。

第六步:group by 子句将中的唯一的值组合成为一组,得到虚拟表vt5。如果应用了group by,那么后面的所有步骤都只能得到的vt5的列或者是聚合函数(count、sum、avg等)。原因在于最终的结果集中只为每个组包含一行。这一点请牢记。

第七步:应用cube或者rollup选项,为vt5生成超组,生成vt6.

第八步:应用having筛选器,生成vt7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。

第九步:处理select子句。将vt7中的在select中出现的列筛选出来。生成vt8.

第十步:应用distinct子句,vt8中移除相同的行,生成vt9。事实上如果应用了group by子句那么distinct是多余的,原因同样在于,分组的时候是将列中唯一的值分成一组,同时只为每一组返回一行记录,那么所以的记录都将是不相同的。

第十一步:应用order by子句。按照order_by_condition排序vt9,此时返回的一个游标,而不是虚拟表。sql是基于集合的理论的,集合不会预先对他的行排序,它只是成员的逻辑集合,成员的顺序是无关紧要的。对表进行排序的查询可以返回一个对象,这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。正因为返回值是游标,那么使用order by 子句查询不能应用于表表达式。排序是很需要成本的,除非你必须要排序,否则最好不要指定order by,最后,在这一步中是第一个也是唯一一个可以使用select列表中别名的步骤。

第十二步:应用top选项。此时才返回结果给请求者即用户。

一个完成的SELECT语句包含可选的几个子句。SELECT语句的定义如下:

SQL代码

 [] [] [] [] [] []

2、SELECT语句执行顺序

SELECT语句中子句的执行顺序与SELECT语句中子句的输入顺序是不一样的,所以并不是从SELECT子句开始执行的,而是按照下面的顺序执行:

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果

28、事务的四大特性

事务特性ACID : 原子性 ( Atomicity)、 一致性 ( Consistency)、 隔离性 ( Isolation)、 持久性 ( Durability)。

*

原子性 是指事务包含的所有操作要么全部成功,要么全部失败回滚。
*
一致性 是指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块,两人之间转账之后无论成功还是失败,它们的账户总和还是1000。
*
隔离性 。跟隔离级别相关,如 read committed,一个事务只能读到已经提交的修改。
*
持久性 是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

29、 大表怎么优化?

一张表有近千万条数据,查询比较慢,如何优化?

[En]

A table has nearly 10 million data, the query is relatively slow, how to optimize?

当MySQL单表记录数过大时,数据库的性能会明显下降,一些常见的优化措施如下:

  • 限制数据范围。例如,当用户查询历史信息时,可以将其控制在一个月内。
    [En]

    limit the scope of the data. For example, when users query historical information, they can control it within one month.*

  • 读写分离:经典的数据库拆分方案,主数据库负责写,从数据库负责读
    [En]

    read-write separation: the classic database split scheme, in which the master database is responsible for writing and the slave database is responsible for reading*

  • 对子数据库和子表进行优化,主要是垂直拆分和水平拆分。
    [En]

    optimized by sub-database and sub-table, mainly vertical split and horizontal split.*

30、bin log/redo log/undo log

mysql日志主要有查询日志、慢查询日志、事务日志、错误日志、二进制日志。其中比较中亚的是bin log(二进制日志)、redo log(重做日志)、undo log(回滚日志)

  • bin log

bin log是MySQL数据库级别的文件 ,记录对MySQL数据库执行修改的所有操作,不会记录select等语句,主要用于恢复数据库和同步数据库

  • redo log

redo log 是innodb引擎级别,用来记录innodb存储引擎的事务日志,不管事务是否提交都会记录下来,用于数据恢复。当数据库发生故障,innodb存储引擎会使用 redo log恢复到发生故障前时刻,以此来保证数据的完整性。将参数 innodb_flush_log_at_tx_commit设置为1,那么在执行commit时会将 redo log同步写到磁盘。

  • undo log

除了记录 redo log外。当进行数据修改时还会记录 undo log, undo log用于数据的撤回操作,他保留了记录修改前的内容。通过 undo log可实现是事务的回滚,并且可以根据 undo log回溯到某个特定的版本数据,实现 MVCC

31、bin log和redo log有什么区别?

32、 分库分表

当单表的数据量达到1000W或100G以后,优化索引、添加从库等可能对数据库性能提升效果不明显,此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担,缩短查询的时间。

数据分割可以分为垂直分割和水平分割两种方式。

[En]

Data segmentation can be divided into two ways: vertical partition and horizontal partition.

  • 垂直划分

垂直划分数据库是根据业务进行划分,例如购物场景,可以将库中涉及商品、订单、用户的表分别划分出成一个库,通过降低单库的大小来提高性能。同样的,分表的情况就是将一个大表根据业务功能拆分成一个个子表,例如商品基本信息和商品描述,商品基本信息一般会展示在商品列表,商品描述在商品详情页,可以将商品基本信息和商品描述拆分成两张表。

优点 :行记录变小,数据页可以存放更多记录,在查询时减少I/O次数。

缺点 :

优点 :行记录变小,数据页可以存放更多记录,在查询时减少I/O次数。

缺点 :

*

主键出现冗余,需要管理冗余列;
*
会引起表连接JOIN操作,可以通过在业务服务器上进行join来减少数据库压力;
*
依然存在单表数据量过大的问题。
* 水平划分

水平划分是根据一定规则,例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致,但是数据得以拆分,从而提升性能。

优点 :单库(表)的数据量得以减少,提高性能;切分出的表结构相同,程序改动较少。

缺点 :

*

分片事务一致性难以解决
*
跨节点 join性能差,逻辑复杂
*
数据分片在扩容时需要迁移

33、 having和where的区别?

*

二者作用的对象不同, where子句作用于表和视图, having作用于组。
*
where在数据分组前进行过滤, having在数据分组后进行过滤。

34、 乐观锁和悲观锁是什么?

数据库中的并发控制是为了确保当多个事务同时访问数据库中的同一数据时,不破坏事务的隔离统一和数据库的统一。乐观锁和悲观锁是并发控制的主要技术手段。

[En]

The concurrency control in the database is to ensure that when multiple transactions access the same data in the database at the same time, it does not destroy the isolation and unity of transactions and the unity of the database. Optimistic lock and pessimistic lock are the main technical means of concurrency control.

*

悲观锁:假定会发生并发冲突,在查询完数据的时候就把事务锁起来,直到提交事务。实现方式:使用数据库中的锁机制。
*
乐观锁:假设不会发生并发冲突,只在提交操作时检查是否数据是否被修改过。给表增加 version字段,在修改提交之前检查 version与原来取到的 version值是否相等,若相等,表示数据没有被修改,可以更新,否则,数据为脏数据,不能更新。实现方式:乐观锁一般使用版本号机制或 CAS算法实现。

Original: https://www.cnblogs.com/zbqblogs/p/16166119.html
Author: 诗风雅韵
Title: 最新Mysql大厂面试必会的34问题

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505468/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • JDK1.7-HashMap原理

    JDK1.7 HashMap JAVA技术交流群:737698533 如何在源码上添加自己的注释 打开jdk下载位置 解压src文件夹,打开idea, ctrl+shift+alt…

    数据库 2023年6月16日
    0139
  • 计算机组成原理——概述篇

    计算机发展历史 电子管计算机(1946 年——1958 年) 第一台计算机为ENIAC 诞生于美国宾夕法尼亚大学 特点: 集成度小,空间占用大 功耗高,运行速度慢 操作复杂,更换程…

    数据库 2023年6月16日
    0109
  • com.mysql.cj.jdbc.Driver和com.mysql.jdbc.Driver的区别

    今天,我在写作考试中发现了一个问题,如下所示: [En] Today, I found a problem during the writing test, as follows:…

    数据库 2023年5月24日
    0122
  • Java线程通信

    Java线程通信 螣蛇乘雾,终为土灰。 多个线程协同工作完成某个任务时就会涉及到线程间通信问题。如何使各个线程之间同时执行,顺序执行、交叉执行等。 一、线程同时执行 创建两个线程a…

    数据库 2023年6月14日
    0140
  • 一篇文章带你掌握主流数据库框架——MyBatis

    一篇文章带你掌握主流数据库框架——MyBatis MyBatis 是一款优秀的持久层框架,它支持自定义 SQL、存储过程以及高级映射。 在之前的文章中我们学习了MYSQL和JDBC…

    数据库 2023年6月14日
    0176
  • java 论坛模块设计方案

    权限管理:点开二级菜单进入三级菜单显示 角色(基础权限)和按钮权限 角色(基础权限): 分角色组和角色,独立分配菜单权限和增删改查权限。(一个用户可以多个角色) 按钮权限: 给角色…

    数据库 2023年6月6日
    0119
  • 在Linux中使用crontab

    查看已存在的任务 查看crontab 输入命令: cat /etc/crontab 在设定编辑之前都建议列出服务查看一下: crontab -l 语法: **** user_nam…

    数据库 2023年6月14日
    0133
  • 网络通信知识地图

    知识地图是一种知识导航系统,并显示不同的知识存储之间重要的动态联系。本篇主要就是从更高的视角将之前的文章的结构思路展现出来。文章结构的思路实际上也是达到架构师程度要掌握的网络通信知…

    数据库 2023年6月6日
    0141
  • VMware下的centOS安装与异常记录

    VMware下的centOS安装与异常记录 随笔 记录在使用虚拟机安装centOs的过程中遇到的一些坑,记录一下,之前发在C**N上的,现在决定在这里重新整理一下,加上一些细节的补…

    数据库 2023年6月6日
    0164
  • Vim使用技巧(持续更新)

    好记性不如烂笔头,在这里记录一些Vim使用技巧 vim配置 "&#x62F7;&#x8D1D;&#x540C;&#x6B65;&#…

    数据库 2023年6月14日
    0137
  • 互联网大厂那些儿“高逼格”词汇

    转载请注明出处❤️ 你好,我是测试蔡坨坨。 目前任职于上海某互联网公司测试开发工程师。 相信在互联网公司上班的同学,日常工作中一定听过不少互联网那些”高(zhuang)…

    数据库 2023年6月11日
    0225
  • Centos7 离线安装K3s

    1、安装前准备 github地址:https://github.com/k3s-io/k3s/releases k3s二进制文件:k3s下载地址:github地址 / 百度网盘地址…

    数据库 2023年6月14日
    0163
  • 双色球系统开发

    Java对彩票双色球系统开发的简单实现 双色球系统 案例: 中奖条件及奖金表 代码及解释 main方法代码: public static void main(String[] ar…

    数据库 2023年6月16日
    0172
  • MySQL 数据库

    内容概要 数据演变 数据存储发展 数据库类型 SQL与NoSQL的由来 下载安装MySQL MySQL文件目录 系统服务 密码相关 8.0版本的小问题 基本SQL语句 数据库的增删…

    数据库 2023年5月24日
    0120
  • MySQL实战45讲 13

    13 | 为什么表数据删掉一半,表文件大小不变? 一个 InnoDB 表包含两部分,即: 表结构定义和 数据。 在 MySQL 8.0 版本以前, 表结构是存在以.frm 为后缀的…

    数据库 2023年6月16日
    0142
  • 2018年最新JAVA面试题总结之基础(1)

    转自于:https://zhuanlan.zhihu.com/p/39322967 1、JAVA中能创建volatile数组吗?volatile能使得一个非原子操作变成原子操作吗?…

    数据库 2023年6月16日
    0145
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球