Mysql数据库（二）

MySQL面试常问知识点

1.数据库范式

• 第一范式（1NF）

  在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之第一范式就是无重复的列。

• 第二范式（2NF）

  第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性部分依赖于主关键字。EG：不能出现A->B，(A,C)->B（A决定B，A、C决定B），因为这不是完全依赖

• 第三范式（3NF）

  满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。（我的理解是消除冗余）

2.多表查询

• 交叉连接查询(笛卡尔乘积:4*3=12，产生笛卡尔积的原因是没有足够的连接条件)(一般不用)

    				需求:查询员工及其部门名称
  
    				SELECT employee.name,dept.name FROM employee,dept;
  

• 内连接查询(使用最多)

    				多表查询的步骤:1)确定查询哪些表
  
    				               2)确定查询哪些字段
  
    				               3)确定连接条件(规则:条件=表数量-1)
  
    				SELECT employee.name,dept.name FROM employee,dept WHERE employee.deptId=dept.id;
  
    				另一种语法
  
    				SELECT e.name,d.name FROM employee e INNER JOIN dept d ON e.deptId=d.id;
  

• 左外连接查询(左表数据全部显示，如果右边不满足，则显示null)

    				需求:查询部门及其部门的员工
  
    				SELECT d.name,e.name FROM dept d LEFT OUTER JOIN employee e ON d.id=e.deptId;
  
    		- 右外连接查询(右表数据全部显示，如果左边不满足，则显示null)
  
    				SELECT d.name,e.name FROM employee e RIGHT OUTER JOIN dept d ON e.deptId=d.id;
  

3.主键 超键 候选键 外键

• 超键(super key) ：在关系中能唯一标识元组的属性集称为关系模式的超键

• 候选键(candidate key)：不含有多余属性的超键称为候选键

• 主键(primary key)：用户选作元组标识的一个候选键程序主键

• 外键(foreign key)：如果关系模式R1中的某属性集不是R1的主键，而是另一个关系R2的主键，则该属性集是关系模式R1的外键

    		实例讲解
  
    		假设有如下两个表：
  
    		         学生（学号，姓名，性别，身份证号，教师编号）
  
    		         教师（教师编号，姓名，工资）
  
    		超键：由超键的定义可知，学生表中含有学号或者身份证号的任意组合都为此表的超键。如：（学号）、（学号，姓名）、（身份证号，性别）等。
  
    		候选键：候选键属于超键，它是最小的超键，就是说如果再去掉候选键中的任何一个属性它就不再是超键了。学生表中的候选键为：（学号）、（身份证号）。
  
    		主键：主键就是候选键里面的一个，是人为规定的，例如学生表中，我们通常会让“学号”做主键，教师表中让“教师编号”做主键。
  
    		外键：外键比较简单，学生表中的外键就是“教师编号”。外键主要是用来描述两个表的关系。
  

4.数据库事务

• 数据库事务，是由有限的数据库操作序列组成的逻辑执行单元，这一系列操作要么全部执行，要么全部放弃执行。

• 数据库事务transanction正确执行的四个基本要素：ACID，原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。

  • 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样

  • 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

  • 隔离性：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

  • 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

5.视图

• 视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。

• 视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖

  创建视图：create view XXX as XXXXXXXXXXXXXX;（删除也是类似的）

• 于某些视图比如未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；

• 但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

6.drop,delete与truncate的区别

drop直接删掉表；truncate删除表中数据，再插入时自增长id又从1开始 ；delete删除表中数据，可以加where字句。

• DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

• 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

• 一般而言，drop > truncate > delete

• 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

• TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

• truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

• delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

• truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

• 在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或想触发trigger,还是用delete。

• Truncate table 表名 速度快,而且效率高,因为:truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

• TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

• 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

7.索引的工作原理及其种类

• 数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

• 为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间（因为索引也要随之变动）。

• 使用：

  • create index 索引名 on 表名（列名）

  • drop index 索引名;

  • 索引优点

    1）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

    2）可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

    3）可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

    4）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

    5）通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

  • 索引缺点

    1）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

    2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

    3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

  • 建议创建索引列

    1）在经常需要搜索的列上，可以加快搜索的速度；

    2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

    3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

    4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序， 加快排序查询时间；

    5）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

  • 不建议创建索引列

  1） 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

  2） 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

  3） 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

  4） 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

  • 索引分类

    • 唯一索引：唯一索引是不允许其中任何两行具有相同索引值的索引。

      当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

    • 主键索引

      数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

    • 聚集索引

      在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

  • 局部性原理与磁盘预读

    • 由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

    • 由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页的大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

  • B-/+Tree索引的性能

    • 上文说过一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

    • 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

    • 综上所述，用B-Tree作为索引结构效率是非常高的。

8.数据库优化的思路

• SQL语句优化

  1）应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

  2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null。可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

  3）很多时候用 exists 代替 in 是一个好的选择

  4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

• 索引优化

  看上文索引

• 数据库结构优化

  1）范式优化： 比如消除冗余（节省空间。。）

  2）反范式优化：比如适当加冗余等（减少join）

  3）拆分表： 垂直拆分和水平拆分

• 服务器硬件优化

  这个么多花钱咯！

9.MySql的复制原理以及流程（主从复制）

基本原理流程，3个线程以及之间的关联；（主节点、从节点）
1） 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；

2） 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；

3） 从：sql执行线程——执行relay log中的语句；

10.MySQL中myisam与innodb的区别，至少5点

			1）InnoDB支持事物，而MyISAM不支持事物

			2）InnoDB支持行级锁，而MyISAM支持表级锁

			3）InnoDB支持MVCC（多版本并发控制）, 而MyISAM不支持

			4）InnoDB支持外键，而MyISAM不支持

			5）InnoDB不支持全文索引，而MyISAM支持。

11.innodb引擎的4大特性

			插入缓冲（insert buffer)、二次写(double write)、自适应哈希索引(ahi)、预读(read ahead)

12.两者select count(*)哪个更快，为什么

			myisam更快，因为myisam内部维护了一个计数器，可以直接调取。

13.MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

1）varchar与char的区别

char是一种固定长度的类型，varchar则是一种可变长度的类型

2）varchar(50)中50的涵义

最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)

3）int（20）中20的涵义

指显示字符的长度，但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加0。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

14.innodb的事务与日志的实现方式

			1）有多少种日志

			       错误日志：记录出错信息，也记录一些警告信息或者正确的信息。

			       查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行

			       慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。

			       二进制日志：记录对数据库执行更改的所有操作。

			       中继日志

			       事务日志：

			2）事物的4种隔离级别

			       读未提交(RU)

			       读已提交(RC)

			       可重复读(RR)

			       串行

			3）事务是如何通过日志来实现的

			       事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 
			       当事务执行时会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”。

15.关于两种引擎的介绍

• Innodb引擎，Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。它本身实际上是基于Mysql后台的完整的系统。Mysql运行的时候，Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是，该引擎是不支持全文搜索的。同时，启动也比较的慢，它是不会保存表的行数的。当进行Select count(*) from table指令的时候，需要进行扫描全表。所以当需要使用数据库的事务时，该引擎就是首选。由于锁的粒度小，写操作是不会锁定全表的。所以在并发度较高的场景下使用会提升效率的。

• MyIASM引擎，它是MySql的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行Insert插入和Update更新语句时，即执行写操作的时候需要锁定这个表。所以会导致效率会降低。不过和Innodb不同的是，MyIASM引擎是保存了表的行数，于是当进行Select count(*) from table语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的。可以将MyIASM作为数据库引擎的首先。

  补充2点：

  • 大容量的数据集时趋向于选择Innodb。因为它支持事务处理和故障的恢复。Innodb可以利用数据日志来进行数据的恢复。主键的查询在Innodb也是比较快的。

  • 大批量的插入语句时（这里是INSERT语句）在MyIASM引擎中执行的比较的快，但是UPDATE语句在Innodb下执行的会比较的快，尤其是在并发量大的时候。

16.两种引擎所使用的索引的数据结构是什么？

• 都是B+树!

• MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

• Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。