目录
3、SQL语句中‘相关子查询’与‘非相关子查询’有什么区别?
1、简述在MySQL数据库中MyISAM和InnoDB的区别
11、Hash 索引和 B+ 树索引区别是什么?你在设计索引是怎么抉择的?
13、简单描述mysql中,索引,主键,唯一索引,联合索引的区别,对数据库的性能有什么影响(从读写两方面)
2、怎么看执行计划(explain),如何理解其中各个字段的含义?
一、MySQL原理
1、说说MySQL 的基础架构图
Mysql逻辑架构图主要分三层:
- 连接层:负责连接处理,授权认证,安全等等
- 服务层:负责编译,优化SQL
- 存储引擎层:负责存储数据,提供读写接口
2、SQL查询语句在MySQL中如何执行的?
- 先检查该语句是否有权限,如果没有权限,直接返回错误信息,如果有权限会先查询缓存(MySQL8.0 版本以前)。
- 如果没有缓存,分析器进行词法分析,提取 sql 语句中 select 等关键元素,然后判断 sql 语句是否有语法错误,比如关键词是否正确等等。
- 最后优化器确定执行方案进行权限校验,如果没有权限就直接返回错误信息,如果有权限就会调用数据库引擎接口,返回执行结果。
二、SQL语句
1、SQL语言包括哪几部分?每部分都有哪些操作关键字?
答:SQL语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询(DQL)四个部分。
- 数据定义:Create Table,Alter Table,Drop Table, Craete/Drop Index等
- 数据操纵:Select ,insert,update,delete,
- 数据控制:grant,revoke
- 数据查询:select
2、解释MySQL外连接、内连接与自连接的区别
- 交叉连接: 交叉连接又叫笛卡尔积,它是指不使用任何条件,直接将一个表的所有记录和另一个表中的所有记录一一匹配。
- 内连接 则是只有条件的交叉连接,根据某个条件筛选出符合条件的记录,不符合条件的记录不会出现在结果集中,即内连接只连接匹配的行。
- 外连接 其结果集中不仅包含符合连接条件的行,而且还会包括左表、右表或两个表中
- 的所有数据行,这三种情况依次称之为左外连接,右外连接,和全外连接。
- 左外连接,也称左连接,左表为主表,左表中的所有记录都会出现在结果集中,对于那些在右表中并没有匹配的记录,仍然要显示,右边对应的那些字段值以NULL来填充。右外连接,也称右连接,右表为主表,右表中的所有记录都会出现在结果集中。左连接和右连接可以互换,MySQL目前还不支持全外连接。
3、SQL语句中‘相关子查询’与‘非相关子查询’有什么区别?
子查询:嵌套在其他查询中的查询称之。
子查询又称内部,而包含子查询的语句称之外部查询(又称主查询)。
所有的子查询可以分为两类,即相关子查询和非相关子查询。
- 非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。
- 相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。故非相关子查询比相关子查询效率高
三、MySQL语言基础
1、如何通俗地理解三个范式?
- 第一范式:1NF是对属性的原子性约束,要求属性具有原子性,不可再分解;
- 第二范式:2NF是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;
- 第三范式:3NF是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。。
2、范式化设计优缺点
优点
可以尽量得减少数据冗余,使得更新快,体积小
缺点
对于查询需要多个表进行关联,减少写得效率增加读得效率,更难进行索引优化
3、反范式化
优点
可以减少表得关联,可以更好得进行索引优化
缺点
数据冗余以及数据异常,数据得修改需要更多的成本
4、char和varchar的区别?
是一种固定长度的类型,varchar则是一种可变长度的类型,它们的区别是:
char(M)类型的数据列里,每个值都占用M个字节,如果某个长度小于M,MySQL就会在它的右边用空格字符补足。(在检索操作中那些填补出来的空格字符将被去掉)在varchar(M)类型的数据列里,每个值只占用刚好够用的字节再加上一个用来记录其长度的字节(即总长度为L+1字节)。
varchar得适用场景
字符串列得最大长度比平均长度大很多 2.字符串很少被更新,容易产生存储碎片 3.使用多字节字符集存储字符串
Char得场景
存储具有近似得长度(md5值,身份证,手机号),长度比较短小得字符串(因为varchar需要额外空间记录字符串长度),更适合经常更新得字符串,更新时不会出现页分裂得情况,避免出现存储碎片,获得更好的io性能
5、为表中得字段选择合适得数据类型(物理设计)
字段类型优先级: 整型>date,time>enum,char>varchar>blob,text
优先考虑数字类型,其次是日期或者二进制类型,最后是字符串类型,同级别得数据类型,应该优先选择占用空间小的数据类型
6、存储时期
- Datatime:以 YYYY-MM-DD HH:MM:SS 格式存储时期时间,精确到秒,占用8个字节得存储空间,datatime类型与时区无关
- Timestamp:以时间戳格式存储,占用4个字节,范围小1970-1-1到2038-1-19,显示依赖于所指定得时区,默认在第一个列行的数据修改时可以自动得修改timestamp列得值
- Date:(生日)占用得字节数比使用字符串.datatime.int储存要少,使用date只需要3个字节,存储日期月份,还可以利用日期时间函数进行日期间得计算
- Time:存储时间部分得数据
注意:不要使用字符串类型来存储日期时间数据(通常比字符串占用得储存空间小,在进行查找过滤可以利用日期得函数)
使用int存储日期时间不如使用timestamp类型
7、主键、外键和索引的区别?
主键--唯一标识一条记录,不能有重复的,不允许为空
外键--表的外键是另一表的主键, 外键可以有重复的, 可以是空值
索引--该字段没有重复值,但可以有一个空值
作用
- 主键--用来保证数据完整性
- 外键--用来和其他表建立联系用的
- 索引--是提高查询排序的速度
个数
- 主键--主键只能有一个
- 外键--一个表可以有多个外键
- 索引--一个表可以有多个唯一索引
四、存储引擎
1、简述在MySQL数据库中MyISAM和InnoDB的区别
区别于其他数据库的最重要的特点就是其插件式的表存储引擎。
切记:存储引擎是基于表的,而不是数据库。
InnoDB与MyISAM的区别:
InnoDB存储引擎: 主要面向OLTP(Online Transaction Processing,在线事务处理)方面的应用,是第一个完整支持ACID事务的存储引擎(BDB第一个支持事务的存储引擎,已经停止开发)。
MyISAM存储引擎: 是MySQL官方提供的存储引擎,主要面向OLAP(Online Analytical Processing,在线分析处理)方面的应用。
- InnoDB 支持事务;MyISAM 不支持事务
- InnoDB 支持行级锁;MyISAM 支持表级锁
- InnoDB 支持 MVCC(多版本并发控制);MyISAM 不支持
- InnoDB 支持外键,MyISAM 不支持
- MySQL 5.6 以前的版本,InnoDB 不支持全文索引,MyISAM 支持;MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引
- InnoDB 不保存表的总行数,执行 select count(*) from table 时需要全表扫描;MyISAM 用一个变量保存表的总行数,查总行数速度很快
- InnoDB 是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高。辅助索引需要两次查询,先查询到主键,再通过主键查询到数据。主键太大,其他索引也会很大;MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针,主键索引和辅助索引是独立的
五、索引
1、哪些字段需要建立索引
SQL语句中的条件和排序里面用到的字段建立索引。
2、什么情况需要给字段建立索引?
- 表的主键、外键必须有索引;
- 数据量超过300的表应该有索引;
- 经常与其他表进连接的表,在连接字段上应该建索引;
- 经常出现在Where句 order by中的字段,特别是表的字段,应该建索
- 引;
- 索引应该建在选择性的字段上;
- 索引应该建在字段上,对于的本字段甚超字段,不要建索引;
3、索引失效
口诀:模型属空运最快
- 使用LIKE关键字进行模糊查询时,以%开头
- 数据类型错误
- 对索引字段使用内部函数,应该建立基于函数的索引
- 不限制索引列是NOT NULL,数据库认为索引列可能为空
- 对索引列进行加减乘除等运算
- 在复合索引中,不遵循最左原则进行查找
- 数据库使用全表扫描时预计比使用索引更快
4、什么是最左前缀原则?什么是最左匹配原则?
最左前缀原则,就是最左优先,在创建多列索引时,要根据业务需求,where 子句中使用最频繁的一列放在最左边。
最左匹配原则,就是当我们创建一个组合索引的时候,如 (a1,a2,a3),相当于创建了(a1)、(a1,a2)和(a1,a2,a3)三个索引。
5、索引不适合哪些场景?
- 数据量少的不适合加索引
- 更新比较频繁的也不适合加索引
- 区分度低的字段不适合加索引(如性别)
6、索引有哪些优缺点?
优点
- 唯一索引可以保证数据库表中每一行的数据的唯一性
- 索引可以加快数据查询速度,减少查询时间
缺点
- 创建索引和维护索引要耗费时间
- 索引需要占物理空间,除了数据表占用数据空间之外,每一个索引还要占用一定的物理空间
- 以表中的数据进行增、删、改的时候,索引也要动态维护。
7、为什么要用 B+ 树,为什么不用普通二叉树?
可以从几个维度去看这个问题,查询是否够快,效率是否稳定,存储数据多少,以及查找磁盘次数,为什么不是普通二叉树,为什么不是平衡二叉树,为什么不是B树,而偏偏是 B+ 树呢?
8、为什么不是普通二叉树?
如果二叉树特殊化为一个链表,相当于全表扫描。平衡树相比于查找树来说,总体的查找速度更快,查找效率也更稳定。
9、为什么不是平衡二叉树呢?
在磁盘比在内