盒子
盒子
文章目录
  1. MySQL面试篇
    1. 1. 解释下超键、候选键、主键、外键
    2. 2. 数据库事务的四个特性及含义
    3. 3. 视图的作用,可以更改吗
    4. 4. drop delete truncate的区别
    5. 5. 索引的工作原理及其种类(太保面试题)
      1. 5.1 不走索引的情况(多次面试题目)
    6. 6. 连接的种类
    7. 7. 数据库范式
    8. 8. 数据库优化的思路
    9. 10. 解释left join和right join[阿里巴巴新零售技术质量部]

Mysql面试篇

MySQL面试篇

1. 解释下超键、候选键、主键、外键

超键: 在关系中能唯一标识元组的属性集称, 如学号、姓名
候选键: 不含有多余属性的超键,如学号
主键: 用户选择的候选键
外键: 外键是相对于主键的,如学生表中学号为主键,成绩表中学号为外键

总结:主键为候选键的子集,候选键为超键的子集,而外键是相对于主键的

2. 数据库事务的四个特性及含义

ACID: 原子性、一致性、隔离性、持久性

+原子性: 事务不可分割,要么全部执行、要么全部不执行
+一致性: 事物执行的前后数据完整性保持不变
+隔离性:一个事务的执行过程中,不应该受到其他事务的干扰
+持久性:事务一旦结束,就持久化到数据库中,不会被回滚

3. 视图的作用,可以更改吗

视图是虚拟的表,与包含数据的表不同,视图只包含使用时动态检索数据的查询,不包含任何列或数据;
视图可以简化复杂的sql语句,隐藏细节,保护数据;
视图创建后,可以使用与表相同的方式使用它们

视图不能被索引,也不能有关联的触发器或默认值

对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等是可以更新的,对视图的更新将对基本表更新;
但是视图主要用于简化检索、保护数据,并不用于更新,而且大部分视图都不可以更新

4. drop delete truncate的区别

drop直接删除表,truncate删除表中的数据,再插入时自增长id又从1开始,delete删除表中的数据,可以加where条件,自增id标识保留

  • delete时每次删除一行,同时将操作记录至日志以便回滚,事务提交后才生效;truncate一次性删除数据并不记录日志,不能回滚且不触发与表相关的删除触发器,速度快
  • 表和索引所占空间 truncate后表和索引空间恢复至初始大小,delete后表和索引空间不变,drop后释放表和索引的全部空间
  • 一般来说,drop > truncate > delete
  • 应用范围 truncate只能对table, drop与delete可用于table与view
  • truncate与delete只删除数据,而drop则删除整个表(结构与数据)
  • truncate与不带where的delete: 只删除数据,而不删除表的结构,drop删除表的结构被依赖的约束、触发器、索引,依赖与该表的存储过程/函数不会被删除,但其状态会变为invalid
  • 由于foreign key约束引用的表,不能使用truncate table,而应使用不带where子句的delete语句。由于truncate table不记录在日志中,不能激活触发器删除外键信息

5. 索引的工作原理及其种类(太保面试题)

数据库索引,是数据库系统中一个排序的数据结构,以便快速查询、更新数据库中数据,通常实现为B树及B+树
数据库设计者巧妙利用了磁盘预读原理,将一个节点的大小设置为一个页(主存与磁盘间以页为单位交换数据),这样每个节点只需一次I/0就可以完全载入
B-Tree一次检索最多需要h-1次I/0(根节点常驻内存),渐进复杂度O(h)=O(logdN)

种类
普通索引: 最基本的索引,没有任何限制
唯一索引: 索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一
主键索引: 在数据库中为表定义主键则自动创建主键索引,主键索引是唯一索引的特例
组合索引: 指多个字段上创建的索引,遵循最左前缀原则
全文索引

主键索引与唯一索引区别:
一个表只能有一个主键,可以有多个唯一索引
主键列值不允许为空,唯一索引列值可以为空
主键可以作为其他表的外键
主键是逻辑键,实际不存在,唯一索引是物理键,实际存在

5.1 不走索引的情况(多次面试题目)

  • 没有查询条件,或者查询条件没有建立索引
  • 在查询条件上没有使用引导列(引导列是组合索引重要概念,如果将要使用的索引列不是复合索引列表中的第一部分,则不会使用索引)
  • 查询的数量是大表的大部分,应该是30%以上
  • 对小表查询
  • 隐式转换导致索引失效,这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20)

    1
    2
    错误的例子:select * from test where tu_mdn=13333333333;   
    正确的例子:select * from test where tu_mdn='13333333333';
  • 对索引列进行运算导致索引失效

    1
    2
    错误的例子:select * from test where id-1=9;   
    正确的例子:select * from test where id=10;
  • 使用mysql内部函数导致索引失效.对于这样情况应当创建基于函数的索引

    1
    2
    3
    4
    错误的例子:select * from test where round(id)=10; 
    说明,此时id的索引已经不起作用了 正确的例子:首先建立函数索引,
    create index test_id_fbi_idx on test(round(id));
    然后 select * from test where round(id)=10; 这时函数索引起作用了
  • 如果MySQL估计使用索引比全表扫描更慢,则不使用索引

  • 如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列,那么不会用到索引。Heap表只有在“=”的条件下会使用索引。因为用的是哈希索引
  • 用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到
  • 如果like是以%开始,MySQL不会采用这个索引

    1
    2
    3
    问题:解决like ‘%字符串%’时,索引失效问题的方法(么么直播面试题目)
    使用覆盖索引(只查询索引的列(索引列和查询列一致))即可
    SELECT id,name,age FROM user WHERE name LIKE '%aa%'
  • 在JOIN操作中,MYSQL只有在主键和外键的数据类型相同时才能使用索引,否则即使建立了索引也不会使用

  • 在ORDER BY操作中,MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此,在涉及多个数据表的查询里,即使有索引可用,那些索引在加快ORDER BY操作方面也没什么作用

    1
    mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引
  • 索引字段上使用 is null / is not null 判断时,会导致索引失效

  • mysql存储引擎不能继续使用索引中范围条件(bettween、<、>、in等)右边的列
  • 使用短索引

    1
    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
  • 索引不会包含有NULL值的列

    1
    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL
  • 使用ENUM而不是字符串

    1
    ENUM保存的是TINYINT,别在枚举中搞一些“中国”“北京”“技术部”这样的字符串,字符串空间又大,效率又低。

MySQL高级之索引失效与优化详解

6. 连接的种类

左连接(left join)、右连接(right join)、 完整外部连接(full join返回左右连接和)、内连接(join)、交叉连接(cross join产生笛卡尔积)

7. 数据库范式

第一范式: 列不可分割
第二范式: 非主属性完全依赖于主属性
第三范式: 非主属性不传递依赖于主属性

8. 数据库优化的思路

  1. SQL语句优化
  • 尽量避免在where子句中使用!=或<>操作符,否则引擎将放弃索引的使用从而全表扫描
  • 很多时候用exists代替in是一个好的选择
    [辅助理解]A表比B表数据量大,则使用in, 反之,使用exists,如 select a. from A a exists(select 1 from B b on b.id=a.id)
    `select
    from A where id in(select id from B)如:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历10000x1000000次,效率很差. 如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000x100次,遍历次数大大减少,效率大大提升.select a.* from A a where exists(select 1 from B b where a.id=b.id)`
    如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等.
    如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合
  • 用where代替having子句,因为having子句先检索出所有记录在进行过滤
  1. 索引优化
  • 每次新建节点时,直接申请一个页的空间
  1. 数据库结构优化
  • 范式优化:消除冗余,节省空间
  • 反范式优化:适当增加冗余,减少join
  • 拆分表:
    案例: 简单的购物系统涉及如下表 1.产品表(数据量10w,稳定)2.订单表(数据量200w,有增长趋势)3.用户表(数据量100w,有增长趋势)
    • 垂直拆分:
      • 解决问题: 表与表之间的io竞争
      • 未解决问题:单表中数据量增长出现的压力
      • 方案:把产品表与用户表放到一个server上,订单表单单独放到一个server上
    • 水平拆分:
      • 解决问题:单表中数据量增长出现的压力
      • 未解决问题:表与表之间的io竞争
      • 方案: 用户表通过性别拆分为男用户表和女用户表,订单表通过已完成和完成中拆分已完成订单表和未完成订单表, 产品表和未完成订单表放到一个server上,已完成订单和男用户表放到一个sever上,女用户表放在一个server上(女的爱购物,多赚钱的行业奥)

10. 解释left join和right join[阿里巴巴新零售技术质量部]

left join与right join都是两个表进行merge操作:left join将右边的表merge到左边,right join将左边的表merge到右边,通常我们会指定按照哪几个列进行merge

支持一下
扫一扫,支持沈健
  • 微信扫一扫
  • 支付宝扫一扫