3.面试题——MySQL

Retrograde-lx

已于 2024-07-25 11:44:39 修改

阅读量832

点赞数 25

分类专栏：面试题文章标签： mysql 数据库

于 2024-07-25 10:43:03 首次发布

本文链接：https://blog.csdn.net/qq_54698124/article/details/140657208

版权

面试题专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1.数据库的三范式是什么?

第一范式：列不可再分
第二范式：行可以唯一区分，主键约束
第三范式：表的非主属性不能依赖与其他表的非主属性外键约束
且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

2.数据库常用的引擎有哪些?

如何查看mysql提供的所有存储引擎

show engines;

mysql常用引擎包括：MYISAM、Innodb、Memory、MERGE

MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎
Innodb:行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些
Memory:全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表
MERGE：是一组MYISAM表的组合

3.InnoDB与MyISAM的区别

InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

如何选择引擎？

如果没有特别的需求，使用默认的 Innodb 即可。
MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如 OA自动化办公系统。

4.什么是数据库的事务？

多条sql语句，要么全部成功，要么全部失败。
事务的特性：
数据库事务特性：原子性(Atomic)、一致性(Consistency)、隔离性(Isolation)、持久性(Durabiliy)。
简称ACID。

原子性：组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功，整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。
一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。
隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰
持久性：一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。

5.什么是索引？

索引是对数据库表中一个或多个列的值进行排序的结构，建立索引有助于快速获取信息。

mysql 有4种不同的索引：

主键索引（PRIMARY）
- 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引（UNIQUE）
- 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
- 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
- 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
普通索引（INDEX）
- 可以通过 ALTER TABLE table_name ADD INDEX index_name (column); 创建普通索引
- 可以通过 ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3); 创建组合索引
全文索引（FULLTEXT）
- 可以通过 ALTER TABLE table_name ADD FULLTEXT (column); 创建全文索引

索引并非是越多越好，创建索引也需要耗费资源，一是增加了数据库的存储空间，二是在插入和删除时要花费较多的时间维护索引

索引加快数据库的检索速度
索引降低了插入、删除、修改等维护任务的速度
唯一索引可以确保每一行数据的唯一性
通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
索引需要占物理和数据空间

6.SQL优化

1、查询语句中不要使用select *
2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代
3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union
all会更好)
5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫
描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

7.并发事务带来哪些问题?

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

脏读（Dirty read）:当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）:指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatableread）:指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接
着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了
一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

8.SQL Select 语句完整的执行顺序是什么？

select              # 5
	... 
from                # 1
	... 
where               # 2
	.... 
group by            # 3
	... 
having              # 4
	... 
order by            # 6
	... 
limit               # 7
	[offset]

9.主要的七种关联查询

准备工作，先创建两个表并插入一些测试数据

-- 部门表
CREATE TABLE IF NOT EXISTS department ( 
	id BIGINT auto_increment COMMENT '主键' PRIMARY KEY, 
	department VARCHAR ( 50 ) NULL COMMENT '部门名称'
) COMMENT '部门表';

-- 用户表
CREATE TABLE IF NOT EXISTS user ( 
	id BIGINT auto_increment COMMENT '主键' PRIMARY KEY, 
	username VARCHAR ( 256 ) NULL COMMENT '用户昵称', 
	department_id BIGINT NULL COMMENT '部门id'
) COMMENT '用户表';


insert into department values(null, '销售部');
insert into department values(null, '开发部');
insert into department values(null, '财务部');
insert into department values(null, '运维部');
insert into department values(null, '测试部');



insert into user values(null,'张三',1);
insert into user values(null,'李四',2);
insert into user values(null,'王五',3);
insert into user values(null,'桂柳',6);
insert into user values(null,'狗七',5);

insert into user values(null,'勾八',1);
insert into user values(null,'李九',2);
insert into user values(null,'王10',8);
insert into user values(null,'李三',4);
insert into user values(null,'十七',5);

insert into user values(null,'罗三',1);
insert into user values(null,'雷四',2);
insert into user values(null,'王1五',3);
insert into user values(null,'桂2柳',4);
insert into user values(null,'狗3七',5);

insert into user values(null,'张4三',1);
insert into user values(null,'李5四',2);
insert into user values(null,'王6五',3);
insert into user values(null,'桂7柳',4);
insert into user values(null,'狗8七',9);

9.1 内连接

MySQL中的内连接（INNER JOIN）是一种SQL语句的组成部分，用于根据两个或多个表之间的共同字段（通常是主键和外键的关系）来合并表中的数据。内连接只会返回那些在连接条件上有匹配的行。如果在一个表中有行在另一个表中没有匹配的行，那么这些行就不会出现在查询结果中。

语法：

SELECT columns FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;

示例：

select * from user inner join department on user.department_id = department.id;

9.2 左连接

左连接返回左表（FROM 子句中指定的表）的所有行，即使右表中没有匹配的行。如果右表中没有匹配的行，则结果中这些行的右表部分将包含NULL。

语法：

SELECT columns FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field;

示例：

select * from user left join department on user.department_id = department.id;

9.3 右连接

右连接与左连接相反，它返回右表（JOIN子句中指定的表）的所有行，即使左表中没有匹配的行。如果左表中没有匹配的行，则结果中这些行的左表部分将包含 NULL。

语法：

SELECT columns FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field;

示例：

select * from user right join department on user.department_id = department.id;

9.4 全连接

MySQL 原生并不直接支持 FULL JOIN，但可以通过 UNION 关键字结合 LEFT JOIN 和 RIGHT JOIN 来模拟。全连接返回左表和右表中所有的行。当某行在另一表中没有匹配行时，则另一表的部分将包含 NULL。
示例：

select * from `user` left join `department` on user.department_id = department.id
union
select * from `department` right join `user` on user.department_id = department.id
where user.department_id is not null;

9.5 自连接

自连接是一种特殊的连接查询，其中表与其自身进行连接。这通常通过为表指定别名来实现，以便在查询中区分表的多个实例。

语法：

SELECT a.column1, b.column2 FROM table_name AS a JOIN table_name AS b ON a.common_field = b.related_field;

示例：

select * from `user` join `department` on user.department_id = department.id

9.6 交叉连接

交叉连接返回第一个表中的每一行与第二个表中的每一行的笛卡尔积。如果第一个表有 X 行，第二个表有 Y 行，那么结果集将有 X*Y 行。如果没有指定 ON 子句，或者 ON 子句总是为真（如 ON 1=1），则执行交叉连接。

语法：

SELECT 字段列表 FROM 表名1 CROSS JOIN 表名2;
SELECT 字段列表 FROM 表名1, 表名2;

示例：

select * from user cross join department;
select * from user, department;

9.7 自然连接

自然连接类似于内连接，但它自动使用两个表中所有具有相同名称的列进行连接，而不需要显式指定 ON 子句。由于这种自动匹配可能导致意外的结果，因此在实际应用中较少使用。

语法：

SELECT column_name(s) FROM table1 NATURAL JOIN table2;

示例：

select * from user NATURAL join department;

9.8 总结

七种查询

/* 1 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

/* 2 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 3 */
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

/* 4 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

/* 5 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

/* 6 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 1+2 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 7 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 4+5 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

10.MySql 优化

10.1 优化索引

索引设计原则

对查询频次较高, 且数据量比较大的表, 建立索引
索引字段的选择, 最佳候选列应当从 where 子句的条件中提取, 如果where 子句中的组合比较多, 那么应当挑选最常用,过滤效果最好的列的组合
使用唯一索引, 区分度越高, 使用索引的效率越高.  索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引,
过多索引会降低表维护效率
使用短索引, 提高索引访问时的 I/O 效率, 因此也相应提升了 Mysql 查询效率
如果 where 后有多个条件经常被用到, 建议建立符合索引, 复合索引需要遵循最左前缀法则, N 个列组合而成的复合索引,相当于创建了 N 个索引. 复合索引命名规则 index_表名_列名 1_列名 2_列明 3 比如:create index idx_seller_name_sta_addr on tb_seller(name, status, address)

避免索引失效