mysql常规面试题1-33

目录

1.列举常见的关系型数据库和非关系型都有那些?

关系型数据库(需要有表结构)
mysql、oracle 、 spl、server、db2、sybase
非关系型数据库(是以key - value存储的,没有表结构)
mongod : mongod 是一个高性能,开源,无模式的文档型数据库,开发语言是c++,它在许多场景下替代传统的关系
型数据库或键/值的存储方式
redis:是一个开源的,使用ANSI C语言编写,支持网络,可基于内存亦可持久化的日志型,并提供多种语言的API。目
前由VMware主持开发工作。

2.MySQL存储引擎及比较?

mysql存储引擎
Innodb: 5.5之后默认的存储引擎 查询速度较myisam慢 但是更安全，支持事务，支持行锁，支持外键 行锁：同一时间只能一个用户操作这行数据 2两个文件 表结构和真实数据

myisam: mysql老版本用的存储引擎 5.5之前 3个文件 表结构，真实数据和索引
memory: 内存引擎(数据全部存在内存中)，断电或者服务端重启之后所有数据都没有了 1个文件，表结构
blackhole: 无论存什么 都立马消失(黑洞) 1个文件 表结构

3.存储引擎索引结构

Innodb:B+树
myisam:B+树
memory:hash表
blackhole:不知道

4.简述事务及其特性,mysql 如何实现事务?

事务特效：

原子性
一致性
隔离性
持续性

开启事务：

tx, err := db.Begin() // 开始
err = tx.Exec(...) //执行操作
tx.Rollback() // 错误退回
err = tx.Commit() // 无错误，提交

事务简介: 事务是应用程序中一系列严密的操作,所有操作必须成功完成,否则在每个操作中所有的操作都会被撤销
事务特性 (ACID):
原子性(Atomicity): 表示组成一个事务的多个数据库操作是一个不可分割的原子单位,只有所有的操作执行成功,
整个事务才提交,事务中任何一个数据库操作失败,已经执行的任何操作都必须撤销,让数据 库 返回到初始状态
一致性(Consistency):事务操作成功后，数据库所处的状态和它的业务规则是一致的，即数据不会被破坏。
隔离性(Isolation):在并发数据操作时，不同的事务拥有各自数据空间，它们的操作不会对对方产生干扰。数据 库规定了多种事务隔 离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一 致性 越好，但并发性越弱
持续性(Durability):一旦事务提交成功后，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务 后，数据库马上崩溃，在数据库重启时，也必须能保证能够通过某种机制恢复数据

5.事务的隔离级别有哪些？

读未提交（脏读）：可以读取到事务未提交的数据，还没有commit就能读的
读已提交（不可重复读）：只能读取到事务提交之后到数据，commit之后的
可重复读：事务在执行过程中看到的数据都是在事务开始时的快照，就算被修改了读取的也是原来的数据
幻读：update user set age=18 where name = 'xiaomin'; 事务执行过程中，新增了一条name='小明'的数据，这条不会被修改。就像发生了幻觉一样

问题：可重复读。如果就是想要读取事务提交之后的值，可以使用读已提交。一般情况：
事务对某个数据进行修改时 1.对数据进行行锁 2.代码层面加锁 保护

事务隔离级别详情

7.Mysql高可用方案有哪些?

1.基于主从复制；(常用)

2.基于Galera协议；

3.基于NDB引擎；

4.基于中间件/proxy；

5.基于共享存储；

6.基于主机高可用；

8.简述触发器,函数,视图,存储过程

1.触发器: 对某个表进行(增/删/改)操作的前后触发一些操作即为触发器,(触发器用于自定义用户对表的行进行(增/删/改)前后的行为) 触发器必须定义在特定的表上

触发器分为六种情况:
增加前、增加后 before insert 、 after insert

删除前、删除后 before delete 、after delete

修改前、修改后 before update 、after updata

2.函数: 内置函数

自定义函数 3.视图

视图是查询命令结果构成的一个虚拟表(非真实存在)，其本质是【根据SQL语句获取动态的数据集，并为其命 名】，用户使用时只需使用【名称】即可获取结果集合，并可以当作表来查询使用。

4.存储过程:存储过程(procedure)，概念类似于函数，就是把一段代码封装起来，当要执行这一段代码的时候，可以 通过调用该存储过程来实现。在封装的语句体里面，可以同if/else ,case,while等控制结构，可以进行 sql编程，查看现有的存储过程。

9.mysql常见的函数

ABS() 
BIN() 
CEILING向上取整 FLOOR()
EXP(x)
e的x次方
0-1的随机数 圆周率
RAND()
PI()
LEAST(X1, X2 ...) GREATEST(X1, X2)
LN() 返回x的自然对数
LOG(x, y) 返回x的以y为底的对数
取模(余数) 四舍五入
 返回集合中最小的值
返回集合中最大的值
MOD()
round()
SIGN(x)
SQRT(X)
TRUNCATE(X,Y) 返回数字x截短为y位小数的结果
聚合函数
AVG
COUNT
MIN
MAX
SUM
GROUP_CONCATE 返回集合中最小的值
日期和时间函数
curdate() current_date() 当前日期 curtime() current_time() 当前时间

10.数据库备份导入命令(结构+数据)

1.数据库备份与恢复 mysqldump命令用于备份数据库数据
[root@master ~]# mysqldump -u root -p --all-databases > /tmp/db.dump

2.导出db1、db2两个数据库的所有数据
mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql 
进入mariadb数据库，删除一个db
[root@master ~]# mysql -uroot -p
MariaDB [(none)]> drop database s11;
进行数据恢复，吧刚才重定向备份的数据库文件导入到mysql中 [root@master ~]# mysql -uroot -p < /tmp/db.dump

11.char 和varchar的区别

char定长字段, 创建所有记录的值存储的长度一致, 读取速度快
varchar 变长 记录的值存储长度为本身的长度, 省空间\

char的存储方式是，对英文字符(ASCII)占用1个字节，对一个汉字占用两个字节;而varchar的存储方式是，对每个英 文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据

12.mysql-explain执行计划

explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

-- 实际SQL，查找用户名为Jefabc的员工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';

13.1000w条数据, 使用limit分页, 为什么越往后越慢

	LIMIT 451350 , 30 扫描了45万多行，怪不得慢的都堵死了
	当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by，并且order by字段需要建立索引。 
如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，
where会过滤数据，使数据失去连续性。 
	如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。
	SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);
	如果limit语句的offset较大，你可以通过传递pk键值来减小offset = 0，这个主键最好是int类型并
且 auto_increment
	SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;
	这条语句，大意如下:
	SELECT * FROM users WHERE uid >= (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10; 如果limit的offset值过大，用户也会翻页疲劳，你可以设置一个offset
最大的，超过了可以另行处理，一般连续翻页过大，用户体验很差，则应该提供更优的用户体验给用户。

14.读写分离

读写分离就是主从架构，一个主(写),两个从(读).分担读读压力

压力分析：
	比如订单表，只增不减的数据。后面就越来越多。考虑分库分表，水平切分

15.什么是读写分离?

	其实就是将数据库分为了主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过某种机制进行数据的同步，是一种常见的数据库架构。
一个组从同步集群，通常被称为是一个“分组”。

16.数据库分组架构解决什么问题?

 	大多数互联网业务，往往读多写少，这时候，数据库的读会首先称为数据库的瓶颈，这时，如果我们希望能够线性的提升数据库的读性能，消除读写锁冲突从而提升数据库的写性能，那么就可以使用“分组架构”(读写分离架构)。 
#读写分离是用来解决数据库的读性能瓶颈的

17.sql半同步复制原理

异步复制(Asynchronous replication)
	MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心
从库是否已经接收 并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有
传到从上，如果此时，强行将从提 升为主，可能导致新主上的数据不完整。

全同步复制(Fully synchronous replication) 
	指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该
事务才能返回，所以全同步复制的性能必然会收到严重的影响。 

半同步复制(Semisynchronous replication)
	介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至
少一个从库接收到 并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全
性，同时它也造成了一定程度的延 迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在
低延时的网络中使用。

mysql有4种同步方式：
1.异步复制
  优点：搭建简单，使用非常广泛，从mysql诞生就有这种架构，性能非常好。
	缺点：数据是异步的，有丢失数据库的风险
2.全同步复制
	优点：保证数据安全
	缺点：损失性能
3.传统半同步复制
	性能、功能都介于一步和全同步中间。从mysql5.5开始诞生。目的是为了折中前两种架构的性能和优缺点
4.无损复制，增强版的半同步复制
数据零丢失，性能好，mysql5.7诞生

18.sql注入攻击原理，代码层防止sql注入

# 原理
通过前端的表单提交的数据中携带sql语句, 欺骗服务器, 在后端对数据进行存储时, 执行恶意的sql语句
# 出现在哪里
比如在使用pymysql操作数据库是使用字符串拼接生产sql语句, 就会出现sql注入漏洞
# 如何防止
1. 使用pymysql时, 不要使用字符串拼接, 而是使用execute方法, pymysql模块已经对提交的数据做了处理, 不会出 现sql注入
2. 不要相信前端提交的任何数据, 要严格校验
3. 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4. 不要把机密信息直接存放，加密或者hash掉密码和敏感的信息
5. 应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
6. 使用辅助软件对网站进行检测, 软件一般采用sql注入检测工具jsky, 网站平台就有亿思网站安全平台检测

19.慢日志

	MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具
体指运行时间超过 long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。

	默认情况下，MySQL数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需
要的话，一般不建 议启动该参数，因为开启慢查询日志或多或少会带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志 记录写入数据库表。
05202127

20.乐观锁与悲观锁

悲观锁：在拿到这数据时就进行锁定。例如：golang的mutext是悲观锁
	适用场景：适合查少改多，短事务，长事务一直被阻塞，影响性能。
	加锁：for update
	select * from user where id =1 for update

乐观锁：在每次操作数据之前，不锁定数据。而是在修改数据的时候，判断一下在此期间别人有没有去更新这个数据
	适用场景：适合查多改少

21.表锁

表加锁的命令Lock Tables，给表解锁的命令Unlock Tables
不允许其他会话查询本表、修改本表、插入记录。

InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。

22.行锁

select * from table where ? for update;
不允许其他会话查询本行、修改本行

23.在普通索引中，什么情况下会引发表锁

常用的索引有三类:主键、唯一索引、普通索引。
	主键:自带最高效的索引属性; 
	唯一索引:指的是该属性值重复率为0，一般可作为业务主键，例如学号;
	普通索引:与前者不同的是，属性值的重复率大于0，不能作为唯一指定条件，例如学生姓名。

1.结论:
	当 Where 查询条件中的字段没有索引时，更新操作会锁住全表! 可以看到，在有索引的情况下，更新不同的行，InnoDB 默认的行锁不会阻塞。
	当“值重复率”低时，甚至接近主键或者唯一索引的效果，“普通索引”依然是行锁;当“值重复率”高时，MySQL 不会把这个“普通索引”当做索引，即造成了一个没有索引的 SQL，此时引发表锁。
	所有explain查看sql执行计划

24.聚集索引和非聚集索引

聚集索引：一个表只有一个，比如主键
非聚集索引：普通索引，额外创建的索引

聚集索引查询：直接查到数据
非聚集索引查询：先查询主键，在查到数据。查到主键的过程叫做回表

25.explain sql执行计划详解？

explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

-- 实际SQL，查找用户名为Jefabc的员工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

概要描述：
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

26.sql断点调试怎么开启？

set profiling=1;   # 打开断点调试

select * from table1;
shou profile for query 1;  # 查看每个步骤消耗的时间，以此来优化

27.mysql为什么选择B+树？

B树在提⾼了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应⽤⽽⽣。
B+树只需要去遍历叶⼦节点就可以实现整棵树的遍历。⽽且在数据库中基于范围的查询是⾮常频繁的，⽽B树不⽀持这样的操作或者说效率太低

28.B树

B树特点：
1.所有健值分布在整棵树中
2.搜索有可能在非叶子结点结束，效率接近二分查找
3.每个节点最多拥有m(无穷)颗子树
4.根结点最少有两颗子树
5.分支节点至少有m/2颗子树(除根结点和叶子结点外都是分支节点)


缺点：
1.每个磁盘空间有4k,如果数据data太大，那么导致树的深度加深，查询变慢

29.B+树

特点：B+树是在B树基础之上的优化
1.B+树每个节点包含更多的节点，原因1:降低树的高度，原因2:将数据的范围变为多个区间，区间越多数据检索越快
2.非叶子结点存储key，叶子结点存储key和数据
3.叶子结点俩俩指针相互连接(符合磁盘的预读特性)，顺讯查询时性能更高

Innodb每次预读16k，4k的整数倍

30.mysql索引数据结构

31.Mysql架构图

32.当mysql执行一条sql时，内部流程

1.语法分析：MySQL 接收到 SQL 语句后，首先进行语法分析。这个过程会检查语句的语法是否正确，并将其转换为内部数据结构，以便后续处理。

2.查询优化：在执行查询之前，MySQL 会进行查询优化。这个过程的目标是选择最优的执行计划，以提高查询性能。MySQL 使用统计信息和索引等信息来评估不同的执行计划，并选择成本最低的计划。

3.查询执行：一旦选择了最优的执行计划，MySQL 就开始执行查询。执行过程中可能涉及以下步骤：
a. 访问权限检查：MySQL 需要验证用户是否有权限执行该查询。
b. 查询解析：MySQL 解析查询，并根据查询计划进行数据访问。
c. 数据读取：如果查询需要读取数据，MySQL 会根据查询计划从磁盘或内存中读取数据。
d. 连接操作：如果查询涉及多个表之间的连接操作，MySQL 会执行连接操作来获取匹配的数据。
e. 筛选和排序：MySQL 根据查询条件对数据进行筛选，并按照排序规则进行排序。
f. 返回结果：MySQL 将查询结果返回给客户端。

4.事务处理：如果 SQL 语句在事务中执行，MySQL 会在适当的时候处理事务提交或回滚操作，以确保数据的一致性和持久性。
5.锁管理：在多用户并发访问下，MySQL 会使用锁来管理对数据的访问。这包括读锁和写锁的管理，以确保数据的一致性和并发性。
6.日志记录：MySQL 在执行 SQL 语句的同时，会将相关的操作和变更记录到日志文件中，以便在需要时进行故障恢复或回滚操作。

33.mysql与es在阿里数据同步服务

dts数据同步服务
posted @ 2022-01-16 16:12  Jeff的技术栈  阅读(98)  评论(0)  编辑  收藏  举报
回顶部