文章目录
- MySQL面试题
- 数据库三大范式是什么
- 数据库的存储引擎
- sql语句的执行顺序
- mysql外连接、内连接、自连接的区别
- drop、delete、truncate的区别
- 什么是索引,优缺点,分类
- 索引的数据结构(B+树,Hash)
- 数据库为什么使用B+树而不是B树
- 索引算法有哪些
- 创建索引的原则
- 什么是最左前缀原则
- 什么是聚簇索引
- 数据库事务(ACID)
- 什么是事务的隔离级别?MySQL的默认的隔离级别是什么?
- 锁的分类
- 如何定位及优化SQL语句的性能问题?
- SQL的生命周期?
- 常用SQL查询语句优化方法
- 大表怎么优化?
- MySQL的主从复制原理以及流程
- varchar和char有什么区别
- varchar(10)和int(10)代表什么含义
- SQL 注入漏洞产生的原因?如何防止
- 字段为什么要求定义为not null
- Explain 性能分析
MySQL面试题
数据库三大范式是什么
第一范式:列不可再分
第二范式:行可以唯一区分,主键约束
第三范式:表的非主属性不能依赖与其他表的非主属性 外键约束且三大范式是一级一级依赖的,第二范式建立在第一范式上,第三范式建立第一第二范式上。
数据库的存储引擎
Innodb:行级锁,提供了具有提交、回滚和崩溃回复能力的事务安全,支持自动增长列,支持外键约束,并发能力强,占用空间是MYISAM的2.5倍,处理效率相对会差一些。
MYISAM:全表锁,拥有较高的执行速度,不支持事务,不支持外键,并发性能差,占用空间相对较小,对事务完整性没有要求,以select、insert为主的应用基本上可以使用这引擎。
Memory:全表锁,存储在内容中,速度快,但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失,默认使用HASH索引,检索效率非常高,但不适用于精确查找,主要用于那些内容变化不频繁的代码表
1. InnoDB : 支持事务, MySQL5.5版本之后, 默认的存储引擎,支持行级锁,支持外键,不支持全文索引,支持集群
2. MyISAM : 不支持事务,不支持外键,不支持行级锁,支持全文索引,效率高,不支持集群
3. Memory : 速度快,最不安全。小量快速访问的数据,不支持行级锁,不支持外键,不支持集群
sql语句的执行顺序
from > where > group by > having > 聚合函数 > distinct > order by > limit > select
mysql外连接、内连接、自连接的区别
1- 交叉连接:交叉连接由称笛卡尔积,它是指不使用任何条件,直接将一个表的所有记录和另一个表中的所有记录一一匹配
2- 内连接:是只有条件的交叉连接
3- 外连接:它的结果集不仅 包含符合连接条件的行,而且还会包含左边、右表或者俩个表中的所有数据行,这三种情况依次称为左外连接、右连接和全外连接
drop、delete、truncate的区别
drop delete truncate 在mysql中都表示删除
-1 delete和truncate只删除表的数据不删除表的结构
-2 delete这个语句是dml,这个操作需要在事务提交后才可以生效
-3 truncate和drop 的ddl,操作立即生效
-4 速度:drop > truncate > delete
什么是索引,优缺点,分类
索引(index):是帮助mysql高效获取数据的数据数据结构
优点:
可以大大加快数据的检索速度
通过使用索引,可以在查询的过程中,使用优化器,提高系统的性能
确定:
对表进行插入,修改,删除的时候索引也要动态维护,会降低增/删/改的执行效率
索引需要占物理空间
分类:
A. 普通索引: 最基本的索引,他没有任何的限制
B. 唯一索引: 索引列的值必须唯一, 但是允许有空值的情况, 如果是组合索引, 则列值组合必须唯一
C. 主键索引: 一种特殊的唯一索引, 不允许有空值, 一般在建表时, 同时创建主键索引
D. 组合索引: 就是将单列索引进行组合
E. 外键索引: 只有 InnoDB 引擎支持外键索引, 用来保证数据的一致性、完整性和实现级联操作
F. 全文索引: 快速匹配全部文档的方式。InnoDB 引擎 5.6 版本后, 才能支持全文索引, MEMORY 引擎不支持
索引的数据结构(B+树,Hash)
InnoDB的索引类型目前只有两种:BTREE(B树)索引和HASH索引。B树索引是Mysql数据库中使用最频繁的索引类型,基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是(B树)索引。
当我们在mysql中用哈希索引时,主要就是通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法),将数据库字段数据转换成定长的Hash值,与这条数据的行指针一并存入Hash表的对应位置;如果发生Hash碰撞(两个不同关键字的Hash值相同),则在对应Hash键下以链表形式存储
数据库为什么使用B+树而不是B树
1、B+树的叶子节点存储了所有的数据,非叶子节点中存储的是比较关键字。而B树所有的节点都会存储数据。B+树的叶子节点之间存在一个指针连接,B树不存在指针连接。B+树所有的数据都存储在叶子节点,那么顺着叶子节点从左往右即可完成对数据的遍历,极大了简化了排序操作
2、B树只适合随机检索,而B+树同时支持随机检索和顺序检索
3、B+树空间利用率更高,可减少I/O次数,磁盘读写代价更低
4、B+树的查询效率更加稳定
5、增删文件(节点)时,效率更高。因为B+树的叶子节点包含所有关键字,并以有序的链表结构存储,这样可很好提高增删效率
索引算法有哪些
BTree算法:
BTree是最常用的mysql数据库索引算法,也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上,而且还可以用于like操作符,只要它的查询条件是一个不以通配符开头的常量。
Hash算法:
Hash算法只能用于对等比较,例如=,<=>(相当于=)操作符。由于是一次定位数据,不像BTree索引需要从根节点到枝节点,最后才能访问到叶子节点这样多次IO访问,所以检索效率远高于BTree索引
创建索引的原则
1、为常作为查询条件的字段建立索引,where子句中的列
2、为经常需要排序、分组操作的字段建立索引
3、更新频繁字段不适合创建索引
4、对于定义为text、image和bit的数据类型的列不要建立索引
5、非空字段
6、最左前缀原则,就是最左边的优先。指的是联合索引中,优先走最左边列的索引。对于多个字段的联合索引,如 index(a,b,c) 联合索引,则相当于创建了 a 单列索引,(a,b)联合索引,和(a,b,c)联合索引(但并不是建立了多个索引树)。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间
什么是最左前缀原则
最左前缀原则,就是最左边的优先。指的是联合索引中,优先走最左边列的索引。对于多个字段的联合索引,如 index(a,b,c) 联合索引,则相当于创建了 a 单列索引,(a,b)联合索引,和(a,b,c)联合索引(但并不是建立了多个索引树)。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间
什么是聚簇索引
聚簇索引:将数据与索引放到了一块,索引结构的叶子节点存储了行数据,找到索引也就找到了数据
非聚簇索引:将数据与索引分开存储,索引结构的叶子节点存储的是行数据的地址
聚簇索引的优点:
数据访问更快。聚族索引将索引和数据保存在同一个B+树中,因此从聚族索引中获取数据通常比非聚族索引中查找更快
当你需要取出一定范围内的数据时,用聚簇索引也比用非聚簇索引好。
使用覆盖索引扫描的查询可以直接使用节点中的主键值
聚簇索引的缺点:
插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能
更新主键的代价很高,因为将会导致被更新的行移动
通过辅助索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据
数据库事务(ACID)
在我们使用数据库的时候,通常会围绕着一个业务功能, 展开一组操作, 这种操作中的每个小的步骤, 要么全部成功,要么全部失败. 那么就符合事务的概念。
- 原子性(Atomicity):事务是一个完整的操作,事务的各步操作是不可以拆分的,要嘛都执行,要嘛不执行
- 一致性(Consistency):事务执行之前和执行之后都必须处于一致性状态
- 隔离性(Isolation):多个事务之间互不干扰
- 永久性(Durability):事务完成后,把数据永久保存到磁盘中
脏读: (读未提交) 当一个事务正在访问数据并且对数据进行修改的时候,还没有对修改的数据进行提交,然后另外一个事务访问到这个数据
不可重复读: (读已提交) 指在一个事务内多处读取同一个数据,在一个事务还没有结束的时候,另外一个事务也对他进行了修改,导致了俩次读取的数据不一样。
幻读: (不可重复读) 它发生在一个事务读取到了几行数据,接着另外一个事务插入了一些数据,然后第一个事务机会发现多了一些不存在的数据,跟产生了幻觉一样,称为幻读。
什么是事务的隔离级别?MySQL的默认的隔离级别是什么?
SQL 标准定义了四个隔离级别
- READ-UNCOMMITTED(读未提交):最低的隔离级别,一个事务可以读取另一个事务更新但未提交的数据。可能会导致脏读、不可重复读或幻读。
- READ-COMMITTED(读已提交):一个事务提交后才能被其他事务读取到,可以阻止脏读,但是不可重复读或幻读仍有可能发生。
- REPEATABLE-READ(可重复读):对同一记录的多次读取结果都是一致的,除非数据是被本身事务所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。
- SERIALIZABLE(可串行化):最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读。
注意:
MySQL 默认采用的 REPEATABLE_READ隔离级别,Oracle 默认采用的 READ_COMMITTED隔离级别
InnoDB 存储引擎在 分布式事务 的情况下一般会用到**SERIALIZABLE(可串行化)**隔离级别
锁的分类
1. 操作方式分类
A. 共享锁: 也叫作 读锁。 操作同一份数据,可以查询DQL,但是不能增删改DML。
B. 排他锁: 也叫作 写锁。 当前的操作,没有完成的情况下,会阻断其他 增删改查 操作。
2. 粒度大小分类
A. 表级锁:
a. 规则: 操作数据的时候,锁住整张表
b. 特点: 开销小,加锁快,冲突高
c. 代表: MyISAM 存储引擎
B. 行级锁:
a. 规则: 操作数据的时候,锁住当前操作行
b. 特点: 开销大,加锁慢,冲突低
c. 代表: InnoDB 存储引擎
3. 使用方式分类
A. 悲观锁: 每次查询,都会判定数据变化, 很悲观, 所以查询时候会加锁
B. 乐观锁: 每次查询,都会判断数据不会发生变化, 很乐观, 需要手动加锁
如何定位及优化SQL语句的性能问题?
对于查询语句,最重要的优化方式就是使用索引。而执行计划>,就是显示数据库引擎对于SQL语句执行的详细情况,其中包含了是否使用索引,使用什么索引,使用的索引的相关信息等
SQL的生命周期?
1、连接器:客户端与数据库server层的连接器进行连接。连接器负责跟客户端建立连接、获取权限、维持和管理连接。
2、获得请求SQL:数据库进程拿到请求sql
3、查询缓存:连接建立完成后,会判断查询缓存是否开启,如果已经开启,会判断sql是select还是update/insert/delete,对于select,尝试去查询缓存,如果命中缓存直接返回数据给客户端, 如果缓存没有命中,或者没有开启缓存, 会进入到下一步分析器
4、分析器:分析器进行词法分析和语法分析,分析器先会做“词法分析”,分析SQL中的字符串分别是什么,校验数据库表和字段是否存在,然后进行语法分析,判断SQL是否满足MySQL语法
5、优化器:优化器对sql执行计划分析,得到最终执行计划,得到优化后的执行计划交给执行器
6、执行器:开始执行的时候,要先判断一下你对这个表 T 有没有执行查询的权限,如果没有,就会返回没有权限的错误,如果有权限,执行器调用存储引擎api执行sql,得到响应结果, 将结果返回给客户端,如果缓存是开启状态, 会更新缓存
7、关掉连接,释放资源
常用SQL查询语句优化方法
- 不要使用select * from t,用具体的字段列表代替“*”,使用星号会降低查询效率,如果数据库字段改变,可能出现不可预知隐患
- 应尽量避免在where子句中使用!=或<>操作符,避免在where子句中字段进行null值判断,存储引擎将放弃使用索引而进行全表扫描
- 避免使用左模糊,左模糊查询将导致全表扫描
- IN语句查询时包含的值不应过多,否则将导致全表扫描
- 为经常作为查询条件的字段,经常需要排序、分组操作的字段建立索引
- or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时,union all会更好)
- 在使用联合索引字段作为条件时,应遵循最左前缀原则
大表怎么优化?
1、限定数据的查询范围: 务必禁止不带任何限制数据范围条件的查询语句。比如:当用户在查询订单历史的时候,我们可以控制在一个月的范围内;
2、读/写分离: 经典的数据库拆分方案,主库负责写,从库负责读;
3、缓存: 使用MySQL的缓存,另外对重量级、更新少的数据可以考虑使用应用级别的缓存;
4、分库分表
垂直分表:
垂直拆分是指数据表列的拆分,把一张列比较多的表拆分为多张表
水平分表:
水平拆分是指数据表行的拆分,表的行数超过200万行时,就会变慢,这时可以把一张表的数据拆成多张表来存放
MySQL的主从复制原理以及流程
主从复制:将主数据库中的DDL和DML操作通过二进制日志(BINLOG)传输到从数据库上,然后将这些日志重新执行,从而使得从数据库的数据与主数据库保持一致
主从复制的作用:
高可用和故障切换:主数据库出现问题,可以切换到从数据库。
负载均衡:可以进行数据库层面的读写分离
数据备份:可以在从数据库上进行日常备份
复制过程:()
主:binlog线程——记录下所有改变了数据库数据的语句,放进master上的binlog中;
从:io线程——在使用start slave 之后,负责从master上拉取 binlog 内容,放进 自己的relay log中;
从:sql执行线程——执行relay log中的语句
varchar和char有什么区别
char是一个定长字段,假如申请了 char(10) 的空间,那么无论实际存储多少内容.该字段都占用10个字符,
而varchar是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用
了多长的空间
在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该
尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char
varchar(10)和int(10)代表什么含义
varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度
而int的10只是代表了展示的长度,不足10位以0填充.也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示
SQL 注入漏洞产生的原因?如何防止
原因:程序开发过程中不注意规范书写 sql 语句和对特殊字符进行过滤,导致客户端可以通过全局变量 POST 和 GET 提交一些 sql 语句正常执行
解决方案:使用PreparedStatement预编译执行者对象,用?作为占位符,用set方法进行赋值从1开始
字段为什么要求定义为not null
null值会占用更多的字节,且会在程序中造成很多与预期不符的情况
Explain 性能分析
概念:查看执行计划:使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理SQL 语句的。分析查询语句或是表结构的性能瓶颈。
作用:
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
语法:
Explain + SQL 语句