【MySQL面试题pro版-6】

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

数据库的三范式

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

第一范式（1NF）：属性不可再分
- 每个字段都是原子性的，即字段不可再分。
- 例如，一个地址字段应该拆分为街道、城市、省份和邮编等独立的字段。
第二范式（2NF）：完全依赖于主键
- 满足第一范式的基础上，非主键字段必须完全依赖于主键，而不是部分依赖。
- 例如，如果有一个订单详情表，其中包含订单ID、产品ID、数量和价格等字段，那么这个表应该满足第二范式，因为所有非主键字段都完全依赖于主键（订单ID和产品ID）。
第三范式（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聚集索引的重要特点：

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

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

Mysql的非聚集索引

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

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

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

MySql的回表查询是什么？

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