mysql面试题总结

1. 主键 超键 候选键 外键

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

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

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

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

实例讲解

假设有如下两个表：

         学生（学号，姓名，性别，身份证号，教师编号）

        教师（教师编号，姓名，工资）

超键：由超键的定义可知，学生表中含有学号或者身份证号的任意组合都为此表的超键。如：（学号）、（学号，姓名）、（身份证号，性别）等。

候选键：候选键属于超键，它是最小的超键，就是说如果再去掉候选键中的任何一个属性它就不再是超键了。学生表中的候选键为：（学号）、（身份证号）。

主键：主键就是候选键里面的一个，是人为规定的，例如学生表中，我们通常会让“学号”做主键，教师表中让“教师编号”做主键。

外键：外键比较简单，学生表中的外键就是“教师编号”。外键主要是用来描述两个表的关系。

2. 数据库事务

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

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

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

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

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

3. 视图

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

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

       创建视图：create view XXX as XXXXXXXXXXXXXX;

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

4. drop,delete与truncate的区别

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

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

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

3） 一般而言，drop > truncate > delete

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

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

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

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

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

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

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

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

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

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

        数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
       为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动。

        图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

索引优点

       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作为索引结构效率是非常高的。

6. 连接种类

外连接

1）左连接：left join 或 left outer join

select * from table1 left join table2 on table1.id=table2.id

注释：包含table1的所有子句，根据指定条件返回table2相应的字段，不符合的以null显示

2）右连接：right join 或 right outer join

select * from table1 right join table2 on table1.id=table2.id

注释：包含table2的所有子句，根据指定条件返回table1相应的字段，不符合的以null显示

3）完整外部联接:full join 或 full outer join

select * from table1 full join table2 on table1.id=table2.id

注释：返回左右连接的和（见上左、右连接）

内连接：join 或 inner join

select * from table1 join table2 on table1.id=table2.id

注释：只返回符合条件的table1和table2的列

等价：

1）select a.*,b.* from table1 a,table2 b where a.id=b.id

2）select * from table1 cross join table2 where table1.id=table2.id (注：cross join后加条件只能用where,不能用on)

交叉连接(完全)

1） 概念：没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。（table1和table2交叉连接产生3*3=9条记录）

2） 交叉连接：cross join (不带条件where...)

3） sql语句

select * from table1 cross join table2

注释：笛卡尔积

4） 等价（与下列执行效果相同）

select * from table1,table2

7. 数据库范式

第一范式（1NF）

       在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复

属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之

第一范式就是无重复的列。

第二范式（2NF）

        第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行

必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离

来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖

于主关键字。

第三范式（3NF）

      满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部

门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加

入工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。（我的理解是消除冗余）

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. MySQL binlog的几种日志录入格式以及区别

1）Statement：每一条会修改数据的sql都会记录在binlog中

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入

row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使

用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执

行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以user-defined-functions(udf)会出现问题).

使用以下函数的语句也无法被复制：
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁

2）Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节

而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题

缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改

都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

3）Mixedlevel: 是以上两种level的混合使用

       一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区

分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变

更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

16. MySQL数据库cpu飙升到500%的话他怎么处理

1）列出所有进程：show processlist  

2）观察所有进程：多秒没有状态变化的(干掉)

3）查看超时日志或者错误日志 (做了几年开发，一般会是查询以及大批量的插入会导致cpu与i/o上涨 .... 当然不排除网络状态突然断了，导致一个请求服务器只接受到一半，比如where子句或分页子句没有发送，当然的一次被坑经历)

17. 一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录

1）如果A表TID是自增长,并且是连续的,B表的ID为索引

select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2）如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引

select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

18. MySQL中InnoDB引擎的行锁是通过加在什么上完成

答：InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,

如果 id 不是索引键那么InnoDB将完成表锁,,并发将无从谈起

19. xtrabackup实现原理

        在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检

查数据文件的事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

20. 存储过程与触发器的区别

        触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发

（激活）执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于

用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，

存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的

SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。