MySQL八股

最左匹配

这个数据库表创建了（a，b，c）这个联合索引，要能使其生效必须保证 where 条件里最左边是 a 字段，比如以下这几种情况：

  • where a = 0;

  • where a = 0 and b = 0;

  • where a = 0 and c = 0;

  • where a = 0 and b = 0 and c = 0;

  • where a = 0 and c = 0 and b = 0;

而如果 where 条件里最左边的字段不是 a 时，就无法使用到联合索引，比如以下这种情况，就是不符合最左匹配规则：

  • where b = 0;

  • where c = 0;

  • where b = 0 and c =0;

  • where c = 0 and b = 0;

为什么索引可以提高查询速度

B+ 树中，非叶子节点只保存索引数据，叶子节点保存索引数据与业务数据。这样即保证了叶子节点的简约干净，数据量大大减小，又保证了最终能查到对应的业务数。既提高了单次 I/O 数据的有效性，又减少了 I/O 次数，还实现了业务

 

而B树是所有节点都保存数据

事务

特性

原子性：所有操作要么全完成，要么全不完成

一致性：数据的一致性

隔离性：运行多个并发事务对数据进行读写

持久性：修改是永久的

并发会出现的问题：

脏读

如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。

不可重复读

一个事务在两次读取数据时不一致，由于途中有另一个事务修改了数据所以导致数据不一致

 

幻读

在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。

和不可重复读的区别：幻读读的是数量，重复读读的是数据

事务隔离级别

读未提交

读提交

可重复读：默认Innodb

串行化：并发时性能差，尽量不使用，可以在可重复读的基础上加next-key lock

 

 

这四种隔离级别具体是如何实现的呢？

  • 对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；

  • 对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；

  • 对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在每个读取数据前都生成一个 Read View，而「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

 

ReadView主要就是记录了一下这条数据的更改历史，如果是和自己同时期更改的那就不理会，不同时期的要比自己早的为基准。

 解决幻读

  • 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
  • 针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。
  • 在 MySQL 的可重复读隔离级别下，针对「当前读」的查询语句会对索引加记录锁+间隙锁，这样可以避免其他事务执行「增、删、改」时导致幻读的现象。
  • 有一点要注意的是，在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

 

如果遇到这种情况，快照读也是不能防止幻读的

实验验证

MySql锁

根据加锁范围可分

全局锁

加锁后数据库就只读了，主要用于数据库备份，防止篡改，但是如果数据库过大就会导致业务停滞

但也可先创建一个ReadView,用可重复读隔离级别 这样备份的时候都是一样的数据，不会被打扰

 

表级锁

锁的是表

分为

表锁

元数据锁

意向锁

AUTO-INC锁

行锁

锁的是单个数据

场景题

如果对update操作不加where或者加的where不是索引，那么就会导致全表扫描，全表进行锁定，其他数据更改不了，会导致事务迟滞，所以必须对索引进行扫描

如果发现即使在 where 条件中带上了列索引列，优化器走的还是全标扫描，这时我们就要使用 force index([index_name]) 可以告诉优化器使用哪个索引。

聊聊优化思路

 

mysql架构

利用缓存可以优化效率

  • 用多个小表代替一个大表，注意不要过度设计

  • 批量插入代替循环单条插入

  • 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适

  • 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

为什么使用B+树

但是 MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：

  • B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

  • B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；

  • B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树

Mysql语句执行流程

连接

查询缓存，有没有对应语句的缓存

解析sql

执行sql

索引下推：

索引下推主要用于联合索引

联合索引遇到范围查询时会失效，正常情况下查到第一个满足条件的会返回给server层，由server层判断剩下的调剂是否满足，而索引下推则是在引擎层就完成了判断，无需多次回表

Mysql数据存在哪

存在磁盘

frm文件用来存表结构，ibd文件用来存表数据

 

表空间分为

段 区 页 行

 

怎么存null值的？

会存在一个null值表，一条记录有一个用二进制表示的null值，0代表没有null，1代表有null

 

 

行溢出怎么办

如果数据页存不下，那么会将溢出的数据存到溢出页中，使用20个字节的指针指向溢出页

 

为什么记录太多会搜索缓慢

由于记录都是以页的形式存放，在数据库中以B+树形式存放，如果太多会导致层数加深，IO次数增多，一般建议两层即可，三层会大大加大搜索时间

索引失效

介绍索引

在创建表时，InnoDB 存储引擎默认会创建一个主键索引，也就是聚簇索引，其它索引都属于二级索引。

用其他索引的时候查询是先根据索引查到主键，再去主键B+树查具体数据

失效场景

1.当使用左右模糊匹配的时候会失效

Why？

因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。

2.对索引使用函数

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了

 

当然，后续版本也有函数索引，可以针对函数计算后的值建立索引。

 

3.对索引进行表达式计算

原因和2类似，都是进行函数运算

4.对索引隐式类型转换

比如phone字段是char，但是对其查询时用int类型查询，则会将phone转换为int再进行查询，相当于对字段索引进行函数运算

5.联合索引最左匹配

联合索引如果不完整就不会使用索引

6.Where子句中的OR

 在or的情况下必须所有条件列都是索引，不然就会进行全表搜索，因为只要有一个列满足条件就行。

Count（）

count() 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。

count（1）则是统计表中有多少记录

count(主键)执行过程？

server层会维护一个count变量，读取记录时会加以，读完后会发给客户端。

如果表里有二级索引，那么循环遍历的对象就是二级索引，比聚簇索引快

count(1)执行过程？

循环遍历主键索引，不会读取字段值，如果有二级索引，还是读取二级索引，因为不需要进行判断，所以执行效率高

count(*)执行过程？

和count(1)一样，mysql会将*转化为0处理

count(字段)执行过程？

进行全表扫描，效率最低

对于大表记录统计有没有好方法？

1.近似值

给出近似个数

这时，我们就可以使用 show table status 或者 explain 命令来表进行估算

他不会真正去查询，只会给出一个估计值

2.使用额外表保存计数值

需要额外维护一个表，在增删的时候都要更新该表，该表记录表的记录数

SQL基础

三大范式

1NF：

　　每一列都是不可分割的原子数据项

2NF

　　每一列都与主键有关

3NF

　　每一列都和主键直接有关，不能间接

连表查询

内连接

　　两边互相匹配

左连接

　　以左边为主，右边没有的就为null

右连接

　　和左连接相反

全外连接

　　结合左右连接

如何避免重复插入数据 

1.使用UNIQUE约束，保证唯一

 2.使用INSERT ... ON DUPLICATE KEY UPDATE

如果遇到重复的key以现在为主

 3.使用INSERT IGNORE

插入时如果插入错误那么就忽略该条数据

CHAR 和VARCHAR区别

CHAR是固定长度字符串，如果不满则会在尾部补充

VARCHAR是变长字符串，以实际长度为准

VARCHAR后面是字符还是字节

字符，如果是ASCII那就是按照ASCII，UTF-8则按照普通字符

 

int(1) int(10) 在mysql有什么不同？

主要是显示宽度，比如说10的话就会自动补全，与int本身无关，

IP地址如何在数据库里存储？

1.直接用字符串存储

优点：直观，不需要进行额外处理

缺点：占用空间大

2.使用32位无符号int存储

优点：占用空间小

缺点：需要进行转换

sql查询语句执行顺序

 

mysql的引擎

InnoDB：InnoDB是MySQL的默认存储引擎，具有ACID事务支持、行级锁、外键约束等特性。它适用于高并发的读写操作，支持较好的数据完整性和并发控制。

MyISAM：MyISAM是MySQL的另一种常见的存储引擎，具有较低的存储空间和内存消耗，适用于大量读操作的场景。然而，MyISAM不支持事务、行级锁和外键约束，因此在并发写入和数据完整性方面有一定的限制。

Memory：Memory引擎将数据存储在内存中，适用于对性能要求较高的读操作，但是在服务器重启或崩溃时数据会丢失。它不支持事务、行级锁和外键约束。

MySQL主键是聚簇索引吗

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

  • 如果有主键，默认会使用主键作为聚簇索引的索引键；
  • 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
  • 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

主键ID用自增ID还是UUID？

一般建议还是自增ID，注意是主键，因为UUID不能保证每次都比之前的大，所以在插入主键索引树的时候每次都需要查找，而自增就可以直接查到索引最后。同时UUID太长太占用内存

B+树的特性是什么？

1.所有叶子节点都在同一层

2.非叶子节点存储键值

3.叶子节点存储数据记录

4.自平衡

创建联合索引时需要注意什么？

尽量把区分度大的放前面

什么情况下会回表查询

如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

什么是覆盖索引？

就是你查的东西就是索引，所以直接给你返回索引键就行了

数据库和缓存的同步问题

建议先更新数据库，再删缓存，但如果缓存删除失败，那么可以用消息队列来重试（查看待删除数据，如果没删就重试），还可以订阅MySQL操作，获取消息后再删

LRU（淘汰最久没使用的）

传统的 LRU 算法的实现思路是这样的：

  • 当访问的页在内存里，就直接把该页对应的 LRU 链表节点移动到链表的头部。
  • 当访问的页不在内存里，除了要把该页放入到 LRU 链表的头部，还要淘汰 LRU 链表末尾的页。

缓存污染

当大量仅查询个别次数的数据进入缓存，会将热点数据挤出去，会导致缓存雪崩。

解决办法：访问第二次及以上次数才会放入active list里，相当于提高门槛

posted @ 2025-03-29 15:52  hbhhbh  阅读(73)  评论(0)    收藏  举报