MySql （重点）（聚合非聚合索引，join，MySql函数，SQL 关键字，数据库三范式，存储引擎，数据库事务，隔离级别，索引，如何避免索引失效，数据库锁，乐观锁悲观锁，MySql 优化）

这孩子叫逆

已于 2024-09-26 21:54:41 修改

阅读量1.4k

点赞数 23

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-09-26 21:51:20 首次发布

本文链接：https://blog.youkuaiyun.com/zhzjn/article/details/142578402

版权

MySQL 专栏收录该内容

31 篇文章

订阅专栏

你们MySQL面试，真的不准备吗。# MySQL # My SQL面试题 # mysql教程 # 运维 # Linux运维 https://v.douyin.com/ire8Vcb2/ 复制此链接，打开Dou音搜索，直接观看视频！

1.聚集索引和非聚集索引

聚集索引和非聚

聚集索引和非聚集索引

2.连接查询（-

（左外连接）以左表为基准进行查询,左表数据会全部显示出来,右表如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则显示为 NULL;

2.右连接 right join

（右外连接）以右表为基准进行查询,右表数据会全部显示出来,左表如果和右表匹配的数据则显示相应字段的数据,如果不匹配,则显示为 NULL;

3.聚合函数（必会）

1.聚合函数

SQL 中提供的聚合函数可以用来统计、求和、求最值等等。

2.分类

COUNT：统计行数量

SUM：获取单个列的合计值

AVG：计算某个列的平均值

MAX：计算列的最大值

MIN：计算列的最小值

3.SQL 关键字(必会)

1.分页

MySQL 的分页关键词 limit

SELECT * FROM student limit 100,6; 查询学生表中数据，跳过100条，从第101条开始显示，取6 条

倒序

select * from user order by id desc limit 0 6

2.分组

MySQL 的分组关键字：group by

SELECT sex , count(*) FROM student group by sex

3. 去重

去重关键字：distinct

select distinct name FROM student;/

4. SQL Select 语句完整的执行顺序: (必会)

查询中用到的关键词主要包含如下展示，并且他们的顺序依次为

from...left join...on...where...group by...having..select...avg()/sum()...order by...asc/desc...limit...

from: 需要从哪个数据表检索数据

where: 过滤表中数据的条件

group by: 如何将上面过滤出的数据分组算结果

order by : 按照什么样的顺序来查看返回的数据

5. 数据库三范式(掌握)

第一范式：

1NF 原子性，列或者字段不能再分，要求属性具有原子性，不可再分解；

第二范式：

2NF 唯一标识。即每个表只描述一种实体，每个记录都有唯一标识，不存在部分依赖关系。每个表必须有一个主键，并且每个非主键字段都要完全依赖于主键。

(非主键字段必须依赖于主键字段)，主要是解决行的冗余。

每一行数据有唯一的主键

2. 非主键字段必须依赖于主键字段

第三范式：

3NF 直接性，字段不依赖于非主键字段。（非主键字段不依赖于其它非主键字段）主要是解决列的冗余

6. 存储引擎（高薪常问）

1.MyISAM 存储引擎与 InnoDB 引擎区别

1. 事务支持：MyISAM 不支持事务处理，而 InnoDB 支持事务处理，可以通过使用事务来确保数据的完整性和一致性。

2. 锁定机制（锁的粒度）：MyISAM 表级锁在执行 SELECT 操作时会对表进行读锁定，而执行 INSERT、UPDATE 或 DELETE 操作时会对表进行写锁定，因此在写操作执行时，读操作会被阻塞。而 InnoDB 支持行级锁，不会对整个表进行锁定，可以减少锁定冲突和死锁的发生。

3. 外键支持：MyISAM 不支持外键约束，而 InnoDB 支持外键约束，可以通过外键约束来保证数据的引用完整性

4. 并发性能：在并发性能方面，InnoDB 要优于 MyISAM。由于 InnoDB 支持行级锁定和事务处理，因此在高并发情况下，InnoDB 的并发性能更高。

因此，在设计数据库时，需要考虑具体情况选择适合的存储引擎。如果需要支持事务处理和外键约束，以及具有更好的并发性能，则应选择 InnoDB。如果只是进行简单的读写操作，并且需要更快的查询速度，则可以选择 MyISAM。

7.数据库事务（必会）

1.事务特性(ACID)

原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。

一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态，即一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还是5000，这就是事务的一致性。

隔离性：即一个事务执行之前和执行之后都必须处于一致性状态。

持久性：事务一旦结束,数据就持久到数据库。

undo_log AB redo_log D

undo_log 具有原子性，一致性（因为有事务失败了会回滚） redo_log 具有持久性

隔离级别

(1）读未提交（read Uncommited）:

在该隔离级别，所有的事务都可以读取到别的事务中未提交的数据，会产生脏读问题，在项目中基本不怎么用，安全性太差；

脏读：所谓的脏读，其实就是读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。

也就是说，当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。

(2) 读已提交（read commited）: 针对 update 或 delete

处于 READ COMMITTED 级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的数据，那么同一个事务的多个 SELECT 语句可能返回不同的结果。在一个事务内，能看到别的事务提交的数据。出现不可重复读。

不可重复读：事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。

(3 ) 可重复读（Repeatable read）：针对 insert

这是 MySQL 的默认隔离级别，它确保了一个事务中多个实例在并发读取数据的时候会读取到一样的数据；不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。

幻读：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。

InnoDB 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

(4) 可串行化：

“串行化(Serializable)”能预防啥? 排排坐，吃果果，有效避免“脏读”、“不可重复读”、“幻读”，不过效果谁用谁知道。

不可重复读和幻读比较：

两者有些相似，但是前者针对的是update或delete，后者针对的insert 。

1. 索引的概念和优点（必会）

概念：

索引存储在内存中，为服务器存储引擎为了快速找到记录的一种数据结构。索引的主要作用是加快数据查找速度，提高数据库的性能。 空间换时间

优点：加快查询效率

(1) 创建唯一性索引，保证数据库表中每一行数据的唯一性

(2) 大大加快数据的检索速度，这也是创建索引的最主要的原因

(3) 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

(4) 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

缺点：

1.占用内存空间

影响增删改，效率低下.

2. 索引的分类（必会）

(1) 普通索引：最基本的索引，它没有任何限制。（值可重复）

(2) 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。（会员表里面的手机号，身份证号）

(3) 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用 primary key 来约束。

(4) 联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询条件的检索。（手机号和密码）

(5) 全文索引：老版本 MySQL 自带的全文索引只能用于数据库引擎为MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 1支持全文索引。默认 MySQL不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

ElasticSearch做搜索引擎库

3. 索引的底层实现原理（高薪常问）

1. 索引结构

索引是在 Mysql 的存储引擎(InnoDB,MyISAM)层中实现的, 而不是在服务层实现的. 所以每种存储引擎的索引都不一定完全相同, 也不是所有的存储引擎都支持所有的索引类型的,

Mysql 目前提供了以下 4 种索引:

B+Tree 索引: 最常见的索引类型, 大部分索引都支持 B+树索引.

Hash 索引: 只有 Memory 引擎支持, 使用场景简单.

R-Tree 索引(空间索引): 空间索引是 MyISAM 引擎的一个特殊索引类型, 主要地理空间数据, 使用也很少.

S-Full-text(全文索引): 全文索引也是 MyISAM的一个特殊索引类型, 主要用于全文索引, InnoDB 从 Mysql5.6 版本开始支持全文索引.

2. BTree 结构

B+Tree 是在 BTree 基础上进行演变的, 所以我们先来看看 BTree, BTree 又叫多路平衡搜索树, 一颗 m 叉 BTree 特性如下:

(1) 树中每个节点最多包含 m 个孩子.

(2) 除根节点与叶子节点外, 每个节点至少有[ceil(m/2)] 个孩子(ceil 函数指向上取整).

(3) 若根节点不是叶子节点, 则至少有两个孩子.

(4) 每个非叶子节点由 n 个 Key 和 n+1 个指针组成, 其中 [ceil(m/2) -1 ] <= n <= m-1.

以 5 叉 BTree 为例, key 的数量: 公式推导 [ceil(m/2) -1 ] <= n <= m-1.

所以 2 <= n <= 4, 中间节点分裂父节点,两边节点分裂.

3.B+Tree 结构

B+Tree 为 BTree 的变种, B+Tree 与 BTree 的区别:

1.B+Tree 的叶子节点保存所有的 key 信息, 依 key 大小顺序排列.

2.B+Tree 叶子节点元素维护了一个单项链表.

所有的非叶子节点都可以看作是 key 的索引部分.

由于 B+Tree 只有叶子节点保存 key 信息, 查询任何 key 都要从 root 走的叶子. 所以B+Tree 查询效率更稳定.

MySql 中的 B+Tree 索引结构示意图:

Data Structure Visualization

以下是B树与B+树的主要区别：（高薪面试题）

存储数据的位置:
B树: 数据既存储在所有节点中（叶子节点和非叶子节点都有数据）
B+树: 所有的数据记录都存储在叶子节点中，非叶子节点仅包含索引信息。叶子节点包含了完整的数据和索引键。
叶子节点之间的链接:
B树: 叶子节点之间没有链接。
B+树: 叶子节点之间通过指针相互链接，形成一个链表或循环链表，这使得范围查询和遍历变得高效。

4. 如何避免索引失效（高薪常问）

(1) 范围条件查询

(2) 索引列上操作（使用函数、计算等）导致索引失效

比如在索引上使用切割函数, 就会使索引失效.

(3) 字符串不加引号, 造成索引失效.

(4) 尽量使用索引覆盖,**`避免 select *, 这样能提`**高查询效率.

如果索引列完全包含查询列, 那么查询的时候把要查的列写出来, 不使用 select *

通俗讲: 、

索引覆盖: 通过索引就能找到你要的资料信息

回表：通过索引不能完全拿出你要的信息，需要通过数据表再次查询一次才能获取到.

explain select age,name,id,tel  from tb where age=22 and  name ='张三'

SQL

(5) or 关键字连接

只要有一个or条件没有索引就全表扫描

索引失效案例:

如果一定要 or 查用询, 可以考虑下 or 连接的条件列都加索引, 这样就不会失效了.

(6)使用 !=

(7)like以通配符开头('%abc...')导致索引失效

最左匹配原则

（8）排序列包含非同一个索引的列

用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序

9.数据库锁(高薪常问)

MySql锁的类型

1.行锁和表锁

1.1主要是针对锁粒度划分的，一般分为：行锁、表锁、库锁

行锁：访问数据库的时候，锁定整个行数据，防止并发错误。

表锁：访问数据库的时候，锁定整个表数据，防止并发错误。

1.2行锁和表锁的区别：

表锁：

开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

行锁：

开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

2.悲观锁和乐观锁

（1）悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。

传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

（2）乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。

乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition 机制的其实都是提供的乐观锁。

10.MySql 优化(高薪常问)

1) 定位执行效率慢的 sql 语句.(了解)

命令: n ,通过这条命令, 我们可以知道当前数据库是以查询为主还是更新为主. 如果是查询为主, 就重点查询; 如果增删改多就重点优化写入操作.

explain + sql语句查询sql执行过程, 通过执行计划，我们能得到哪些信息：

A：哪些步骤花费的成本比较高

C：这条sql语句是否走索引

show profile 分析 SQL,可以查看所有 sql 语句的执行效率(所用时间). 前提是这个命令需要被打开, 严格的说也就是打开这个命令后执行的所有 sql 语句, 它都能记录下执行时间, 并展示出来. 可以通过这个命令分析哪些 sql 语句执行效率低. 耗时长, 就更有针对性的优化这sql.
慢查询日志(常用的工具)

慢查询日志记录了所有执行时间超过参数 long_query_time 的 sql 语句的日志 ,long_query_time 默认为 10 秒(可以通过配置文件设置), 日志保存在 /var/lib/mysql/目录下,有个 slow_query.log 文件

2) 优化索引(高薪)

2.1 索引设计原则

索引的设计需要遵循一些已有的原则, 这样便于提升索引的使用效率, 更高效的使用索引.

对查询频次较高, 且数据量比较大的表, 建立索引.
索引字段的选择, 最佳候选列应当从 where 子句的条件中提取, 如果 where 子句中的组合比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.
使用唯一索引, 区分度越高, 使用索引的效率越高，能建唯一索引就建唯一索引，或者普通索引
索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维护效率. 不是越多越好
使用短索引, 提高索引访问时的 I/O 效率, 因此也相应提升了 Mysql 查询效率.
如果 where 后有多个条件经常被用到, 建议建立复合索引, 复合索引需要遵循最左前缀法则, N 个列组合而成的复合索引, 相当于创建了 N 个索引.

复合索引命名规则 index _ 表名 _ 列名 1 _ 列名 2 _ 列名 3

3) Sql 语句调优(高薪)

根据业务场景建立复合索引只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率.
多表连接的字段上需要建立索引，这样可以极大提高表连接的效率.
where 条件字段上需要建立索引, 但 Where 条件上不要使用运算函数，以免索引失效.
排序字段上, 因为排序效率低, 添加索引能提高查询效率.
优化 order by 语句: 在使用 order by 语句时, 不要使用 select *, select 后面要查有索引的列, 如果一条 sql 语句中对多个列进行排序, 在业务允许情况下, 尽量同时用升序或同时用降序.
优化 group by 语句: 在我们对某一个字段进行分组的时候, Mysql 默认就进行了排序,但是排序并不是我们业务所需的, 额外的排序会降低效率. 所以在用的时候可以禁止排序, 使用 order by null 禁用.
select age, count(*) from emp group by age order by null
尽量避免子查询, 可以将子查询优化为 join 多表连接查询.