MySql面试题总结

MySql面试题总结

1.连接查询（必会）

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

SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;
1

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

SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
1

3.内连接
MySQL中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 from 子句中使用关键字 inner join 来连接多张表，并使用 on 子句设置连接条件。 内连接是系统默认的表连接，所以在 from 子句后可以省略 inner 关键字，只使用关键字 join ，同时有多个表时，也可以连续使用 inner join 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表。

 select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段
1

2.聚合函数（必会）

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

2.分类
**COUNT：**统计行数量 count（1）统计不包括null的行数，count（*)统计所有
**SUM：**获取单个列的合计值
**AVG：**计算某个列的平均值
**MAX：**计算列的最大值
**MIN：**计算列的最小值

3.SQL 关键字(必会)

1.分页
MySQL 的分页关键词 limit
SELECT * FROM student3 LIMIT 2,6; 查询学生表中数据，从第三条开始显示，显示6 条
2.分组
MySQL 的分组关键字：group by
SELECT sex, count(*) FROM student3 GROUP BY sex;
3. 去重
去重关键字：distinct
select DISTINCT NAME FROM student3;

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

查询中用到的关键词主要包含如下展示，并且他们的顺序依次为
form…on…left join…where…group by…avg()/sum()…having…
select…order by…asc/desc…limit…

from: 需要从哪个数据表检索数据
where: 过滤表中数据的条件
group by: 如何将上面过滤出的数据分组算结果
order by : 按照什么样的顺序来查看返回的数据

5. 数据库三范式(了解)

第一范式：
1NF 原子性，列或者字段不能再分，要求属性具有原子性，不可再分解；
第二范式：
2NF 唯一性，一张表只说一件事，是对记录的惟一性约束，要求记录有惟一标识，
第三范式：
3NF 直接性，数据不能存在传递关系，即每个属性都跟主键有直接关系，而不是间接关系。

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

1.MyISAM 存储引擎
主要特点：
MySQL5.5 版本之前的默认存储引擎
支持表级锁（表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁）；
不支持事务，外键。
适用场景：对事务的完整性没有要求，或以 select、insert 为主的应用基本都可以选用
MYISAM。在 Web、数据仓库中应用广泛。
特点：
1、不支持事务、外键
2、每个 myisam 在磁盘上存储为 3 个文件，文件名和表名相同，扩展名分别是
.frm -------存储表定义
.MYD --------MYData，存储数据
.MYI --------MYIndex，存储索引
2.InnoDB 存储引擎
主要特点：
MySQL5.5 版本之后的默认存储引擎；
支持事务；
支持行级锁（行级锁是 Mysql 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加
锁）；
支持聚集索引方式存储数据。

7.数据库事务（必会）

1.事务特性

**原子性：**即不可分割性，事务要么全部被执行，要么就全部不被执行。
**一致性：**事务必须使数据库从一个一致性状态变换到另一个一致性状态，即一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还是5000，这就是事务的一致性。
**隔离性：**一个事务执行的过程中,不应该受到其他事务的干扰
**持久性：**事务一旦结束,数据就持久到数据库。

2.隔离级别
(1）读未提交（read Uncommited）:
在该隔离级别，所有的事务都可以读取到别的事务中未提交的数据，会产生脏读问题，在项目中基本不怎么用， 安全性太差；
脏读：所谓的脏读，其实就是读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。
也就是说，当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。
(2) 读已提交（read commited）:
处于 READ COMMITTED 级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的多个 SELECT 语句可能返回不同的结果。在一个事务内，能看到别的事务提交的数据。出现不可重复读。
不可重复读：事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。
(3 ) 可重复读（Repeatable read）：
这是 MySQL 的默认隔离级别，它确保了一个事务中多个实例在并发读取数据的时候会读取到一样的数据；不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。
幻读：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。
InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
(4) 可串行化（serializable）：
事物的最高级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争，一般为了提升程序的吞吐量不会采用这个.
“串行化(Serializable)”能预防啥? 排排坐，吃果果，有效避免“脏读”、“不可重复读”、“幻读”，不过效果谁用谁知道。
不可重复读和幻读比较：
两者有些相似，但是前者针对的是update或delete，后者针对的insert。

8.索引

1. 索引的概念和优点（了解）
概念：
索引存储在内存中，为服务器存储引擎为了快速找到记录的一种数据结构。索引的主要作用是加快数据查找速度，提高数据库的性能。
空间换时间
优点：
(1) 创建唯一性索引，保证数据库表中每一行数据的唯一性
(2) 大大加快数据的检索速度，这也是创建索引的最主要的原因
(3) 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
(4) 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
缺点：
1.占用内存空间
2. 影响增删改，效率低下.

2. 索引的分类（必会）
(1) **普通索引：**最基本的索引，它没有任何限制。
(2) 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有
空值。如果是组合索引，则列值的组合必须唯一。
(3) 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记
录，不允许有空值，一般用 primary key 来约束。
(4) 联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询
条件的检索。
(5) 全文索引：老版本 MySQL 自带的全文索引只能用于数据库引擎为MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

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 结构
（特点：根节点和子节点只存放索引，叶子节点存放值；mysql是千万级别就是因为B+trees结构；叶子结点为链表结构，范围查找）
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
MySql 索引数据结构对经典的 B+Tree 进行了优化, 在原 B+Tree 的基础上, 增加了一个指向相邻叶子节点的链表指针, 就形成了带有顺序指针的 B+Tree, 提高区间访问的性能.
MySql 中的 B+Tree 索引结构示意图:

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

(1) 范围条件查询会造成失效

explain select *  from tb where  age >= 1 and name ='张三'
1

(2) 不要在索引上使用运算, 否则索引也会失效.
比如在索引上使用切割函数, 就会使索引失效.

(3) 字符串不加引号, 造成索引失效.（会当成编码去全表找值）

(4) 尽量使用覆盖索引, 避免 select *, 这样能提高查询效率.
如果索引列完全包含查询列, 那么查询的时候把要查的列写出来, 不使用 select *

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

(5) or 关键字连接（这条总结的是废话）
用 or 分割开的条件, 如果 or 前面的列有索引, or 后面的列没有索引, 那么查询的时
候前后索引都会失效

索引失效案例:

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

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

1.行锁和表锁
1.1主要是针对锁粒度划分的，一般分为：行锁、表锁、库锁
行锁：访问数据库的时候，锁定整个行数据，防止并发错误。
表锁：访问数据库的时候，锁定整个表数据，防止并发错误。
1.2行锁 和 表锁 的区别：
表锁：
开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
行锁：
开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

2.悲观锁和乐观锁
（1）悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在
拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。
传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是
在做操作之前先上锁。
（2）乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会
上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等
机制。
乐 观 锁 适 用 于 多 读 的 应 用 类 型 ， 这 样 可 以 提 高 吞 吐 量 ， 像 数 据 库 如 果 提 供 类 似 于
write_condition 机制的其实都是提供的乐观锁。

10.MySql 优化(高薪常问)

1 定位执行效率慢的 sql 语句.(了解)
（1）命令:show status like 'Com_动作',通过这条命令, 我们可以知道当前数据库是以查询为主
还是更新为主. 如果是查询为主, 就重点查询; 如果增删改多就重点优化写入操作.
（2） explain + sql语句查询sql执行过程, 通过执行计划，我们能得到哪些信息：
A：哪些步骤花费的成本比较高
B：哪些步骤产生的数据量多，数据量的多少用线条的粗细表示，很直观
C：这条sql语句是否走索引
（3）show profile 分析 SQL,可以查看所有 sql 语句的执行效率(所用时间). 前提是这个命令需要
被打开, 严格的说也就是打开这个命令后执行的所有 sql 语句, 它都能记录下执行时间, 并
展示出来. 可以通过这个命令分析哪些 sql 语句执行效率低. 耗时长, 就更有针对性的优化
这条 sql.
（4）慢查询日志(常用的工具)
慢 查 询 日 志 记 录 了 所 有 执 行 时 间 超 过 参 数 long_query_time 的 sql 语 句 的 日 志 ,long_query_time 默认为 10 秒(可以通过配置文件设置), 日志保存在 /var/lib/mysql/目录下,有个 slow_query.log 文件

2 优化索引(高薪)

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

复合索引命名规则 index _ 表名 _ 列名 1 _ 列名 2 _ 列明 3
比如:create index idx seller name sta addr on tb seller(name, status, address）

2.2 避免索引失效
（1）如果在查询的时候, 使用了复合索引, 要遵循最左前缀法则, 也就是查询从索引的最左列开
始, 并且不能跳过索引中的列.
（2）尽量不要在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进
行全表扫描，应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全
表扫描。
（3）不做列运算where age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函
数.计算表达式等, 都会是索引失效.
（4）查询 like，如果是 ‘%aaa’ 也会造成索引失效.
（5）应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

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

4合理的数据库设计(了解)
根据数据库三范式来进行表结构的设计。设计表结构时，就需要考虑如何设计才能更有效的查询, 遵循数据库三范式：
i. 第一范式：数据表中每个字段都必须是不可拆分的最小单元，也就是确保每一列的原子性；
ii. 第二范式：满足一范式后，表中每一列必须有唯一性，都必须依赖于主键；
iii. 第三范式：满足二范式后，表中的每一列只与主键直接相关而不是间接相关(外键也是直接相关)，字段没有冗余。
注意：没有最好的设计，只有最合适的设计，所以不要过分注重理论。三范式可以作为一个基本依据，不要生搬硬套。有时候可以根据场景合理地反规范化：

A：保留冗余字段。当两个或多个表在查询中经常需要连接时，可以在其中一个表上增加若干冗余的字段，以 避免表之间的连接过于频繁，一般在冗余列的数据不经常变动的情况下使用。

B：增加派生列。派生列是由表中的其它多个列的计算所得，增加派生列可以减少统计运算，在数据汇总时可以大大缩短运算时间, 前提是这个列经常被用到, 这也就是反第三范式。

C：分割表。
数据表拆分：主要就是垂直拆分和水平拆分。
水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。
垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。

D: 字段设计

1. 表的字段尽可能用 NOT NULL
2. 字段长度固定的表查询会更快
3. 把数据库的大表按时间或一些标志分成小表