【MySQL面试题pro版-6】

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 【MySQL面试题pro版-6】


MySQL是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的RDBMS (Relational Database Management System,关系数据库管理系统)应用软件之一。

数据库的三范式

数据库的三范式是设计关系型数据库时常用的一种规范,它有助于确保数据的一致性和减少数据冗余。三范式分别是:

  1. 第一范式(1NF):属性不可再分
  • 每个字段都是原子性的,即字段不可再分。
  • 例如,一个地址字段应该拆分为街道、城市、省份和邮编等独立的字段。
  1. 第二范式(2NF):完全依赖于主键
  • 满足第一范式的基础上,非主键字段必须完全依赖于主键,而不是部分依赖。
  • 例如,如果有一个订单详情表,其中包含订单ID、产品ID、数量和价格等字段,那么这个表应该满足第二范式,因为所有非主键字段都完全依赖于主键(订单ID和产品ID)。
  1. 第三范式(3NF):消除传递依赖
  • 满足第二范式的基础上,非主键字段之间不能有传递依赖关系。
  • 例如,如果有一个员工表,其中包含员工ID、姓名、部门ID和部门经理ID等字段,那么这个表应该满足第三范式,因为部门经理ID不应该直接依赖于员工ID,而是应该依赖于部门ID。

遵循三范式的数据库设计可以减少数据冗余,提高数据的一致性和可维护性。然而,在实际应用中,为了提高查询性能,有时需要对三范式进行适当的权衡和调整。

Mysql优化建议

  • 对查询进行优化,应尽量避免全表扫描,首先应考虑在 WHERE 及 ORDER BY 涉及的列上建立索引。
  • 应尽量避免在 WHERE 子句中对字段进行 NULL 值判断,创建表时 NULL 是默认值,但大多数时候应该使用NOT NULL,或者使用一个特殊的值,如 0,-1 作为默认值。
  • 应尽量避免在 WHERE 子句中使用 != 或 <> 操作符。MySQL 只有对以下操作符才使用索引:<,<=,=,>,=,BETWEEN,IN,以及某些时候的 LIKE。、
  • 应尽量避免在 WHERE 子句中使用 OR 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,可以使用UNION 合并查询:select id from t where num=10 union all select id from t where num=20。
  • IN 和 NOT IN 也要慎用,否则会导致全表扫描。对于连续的数值,能用 BETWEEN 就不要用 IN:select idfrom t where num between 1 and 3。IN肯定会走索引,但是当IN的取值范围较大时会导致索引失效,走全表扫描。
  • 下面的查询也将导致全表扫描:select id from t where name like‘%abc%’ 或者select id from t where namelike‘%abc’若要提高效率,可以考虑全文检索。而select id from t where name like‘abc%’才用到索引。like只有
    在右面才会走索引
  • 如果在 WHERE 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:
    select id from t where num=@num
    – 可以改为强制查询使用索引:
    select id from t with(index(索引名)) where num=@num
  • 应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。
  • 很多时候用 EXISTS 代替 IN 是一个好的选择:select num from a where num in(select num from b)。用下面
    的语句替换:select num from a where exists(select 1 from b where num=a.num)。
  • 索引固然可以提高相应的 SELECT 的效率,但同时也降低了 INSERT 及 UPDATE 的效。因为 INSERT 或 UPDATE
    时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过 6 个,
    若太多则应考虑一些不常使用到的列上建的索引是否有必要。

Mysql聚集索引

在MySQL中,聚集索引(Clustered Index)是指索引的一种类型,它决定了数据行的物理存储顺序。每个InnoDB表都有一个主键,而这个主键的索引就是聚集索引。聚集索引的叶节点包含了完整的数据行,而非叶节点则包含指向子节点的指针。

以下是一些关于MySQL聚集索引的重要特点:

  1. 数据行存储顺序:数据行按照聚集索引的顺序存储在磁盘上,这意味着聚集索引决定了数据的物理存储方式。
  2. 主键作为聚集索引:如果表定义了主键,那么主键就是该表的聚集索引。如果没有显式定义主键,InnoDB会选择一个唯一非空索引作为聚集索引,如果也没有这样的索引,InnoDB会隐式地生成一个名为GEN_CLUST_INDEX的自动递增列作为聚集索引。
  3. 叶节点包含数据行:在聚集索引中,叶节点包含了完整的数据行信息,而不仅仅是索引列。这意味着查询时,如果只需要访问索引列,就不需要再单独去查找数据行。
  4. 一张表只有一个聚集索引:由于聚集索引决定了数据的物理存储方式,因此每张表只能有一个聚集索引。这也是为什么InnoDB表的主键只能有一个的原因。
  5. 辅助索引(二级索引):除了聚集索引之外,其他的所有索引都称为辅助索引或二级索引。辅助索引的叶节点包含的是指向相应数据行的指针,而不是数据行本身。当查询使用辅助索引时,InnoDB会先查找辅助索引,然后通过指针找到聚集索引中的完整数据行。
  6. 插入优化:由于聚集索引决定了数据的物理存储顺序,新插入的数据行通常会被放置在已有数据行的末尾。如果新插入的数据行需要放在中间位置,可能会导致大量的数据移动,影响性能。

了解聚集索引的特点对于数据库设计和查询优化非常重要。例如,合理地选择主键可以使得常用的查询更加高效,因为聚集索引能够直接影响到数据行的访问速度。

Mysql的非聚集索引

在MySQL中,非聚集索引(Non-Clustered Index)也被称为二级索引或辅助索引。与聚集索引不同,非聚集索引并不决定数据行的物理存储顺序,而是包含指向数据行的指针。每条索引记录都包含了一个指向实际数据行的地址,以及索引列的值。

以下是一些关于MySQL非聚集索引的重要特点:

  1. 独立于数据行存储:非聚集索引的叶节点包含了指向数据行的指针,而不是数据行本身。这意味着非聚集索引的结构和数据行的物理存储是独立的。
  2. 可以有多个:与聚集索引不同,一张表可以有多个非聚集索引。这是因为非聚集索引不决定数据行的物理存储方式,所以可以为不同的列创建多个索引以提高查询效率。
  3. 访问路径:当查询使用非聚集索引时,InnoDB会先查找非聚集索引,然后通过指针找到聚集索引中的完整数据行。如果查询只需要访问索引列,就不需要再访问数据行。
  4. 覆盖索引:如果查询只需要访问非聚集索引中的列,而不需要访问数据行中的其他列,这种情况被称为覆盖索引(Covering Index)。覆盖索引可以提高查询效率,因为不需要访问数据行。
  5. 插入优化:由于非聚集索引不直接影响数据行的物理存储顺序,新插入的数据行对非聚集索引的影响通常比对聚集索引小。这意味着在某些情况下,插入操作可能会更快。
  6. 维护成本:每个非聚集索引都需要额外的存储空间,并且在插入、更新和删除操作时需要维护。因此,过多的非聚集索引可能会增加写操作的开销。

了解非聚集索引的特点对于数据库设计和查询优化非常重要。合理地选择和使用非聚集索引可以提高查询性能,但同时也要注意不要过度使用,以免增加写操作的负担和维护成本。

MySql的回表查询是什么?

除了聚集索引以外,其他建立索引的方式都是非聚集索引,就是普通索引,二级索引。二级索引要进行回表。二级索 引存储的并不是本身的数据,而是聚集索引中的主键值。第一次查询,找到主键值,再通过主键值找到真正的数据。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8天前
|
消息中间件 关系型数据库 MySQL
MySQL 到 Kafka 实时数据同步实操分享(1),字节面试官职级
MySQL 到 Kafka 实时数据同步实操分享(1),字节面试官职级
|
8天前
|
机器学习/深度学习 关系型数据库 MySQL
MySQL 到 Greenplum 实时数据同步实操分享,2024年最新【Python面试题
MySQL 到 Greenplum 实时数据同步实操分享,2024年最新【Python面试题
|
10天前
|
存储 关系型数据库 MySQL
MySQL第五战:常见面试题(下)
MySQL第五战:常见面试题(下)
|
10天前
|
关系型数据库 MySQL
MySQL第四战:视图以及常见面试题(上)
MySQL第四战:视图以及常见面试题(上)
|
10天前
|
SQL 关系型数据库 MySQL
Python与MySQL数据库交互:面试实战
【4月更文挑战第16天】本文介绍了Python与MySQL交互的面试重点,包括使用`mysql-connector-python`或`pymysql`连接数据库、执行SQL查询、异常处理、防止SQL注入、事务管理和ORM框架。易错点包括忘记关闭连接、忽视异常处理、硬编码SQL、忽略事务及过度依赖低效查询。通过理解这些问题和提供策略,可提升面试表现。
37 6
|
10天前
|
存储 Oracle 关系型数据库
【MySQL面试题pro版-12】
【MySQL面试题pro版-12】
15 0
|
8天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
85 0
|
10天前
|
关系型数据库 MySQL 数据库
docker MySQL删除数据库时的错误(errno: 39)
docker MySQL删除数据库时的错误(errno: 39)
67 0
|
3天前
|
存储 SQL 关系型数据库
【MySQL】数据库基础 -- 详解
【MySQL】数据库基础 -- 详解
|
10天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(下)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
51 0