mysql相关面试题

Mysql基础面试题

1.描述主键、外键、超键是什么

主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。
超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以作为一个超键，多个属性组合在一起也可以作为一个超键。

2.数据库设计的三大范式

1. 第一范式(确保每列保持原子性)
2. 第二范式(确保表中的每列都和主键相关)
3. 第三范式(确保每列都和主键列直接相关,而不是间接相关)

3.drop,delete与truncate的区别

1. delete和truncate都是只能删除表的内容而不能删除表的结构，而drop则是删除表的结构和内容(表将不复存在)；
2. delete可以删除整个表的数据也可以有选择性地删除某一条或多条数据，而truncate则是一次性删除整个表的数据；
3. 使用delete删除的表数据并没有真正被删掉，数据占用的表空间还存在，日后有需要还可以恢复；
4. 使用truncate删除的表数据会连同其占用的表空间一起直接被删掉，无法恢复

4.UNION 和 UNION ALL 区别

union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct(去重),会对获取的结果进行排序操作
union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示。
union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高

5.exists、in、any、all区别

exists是表示子查询是否返回结果，而不管返回的具体内容
in表示值是否存在于子查询结果集中
any表示子查询结果中的任意一个
all表示子查询结果的所有

6.sql语句的执行顺序

1.from子句组装来自不同数据源的数据
2.where子句基于指定的条件对记录行进行筛选
3.group by 子句将数据划分为多个分组
4.使用聚合函数进行计算
5.使用having子句筛选分组
6.计算所有表达式的值
7.select 的字段筛选
8.使用 order by 对结果集进行排序

7.count(*)和count(1)和count(id)区别

count(字段名称)扫描全表，判断字段可空，拿出该字段所有值，判断每一个值是否为空，不为空则累加。
count(1)扫描全表，但不取值，收到的每一行都是1，判断不可能是null，按值累加。
MySQL执行count(*)在优化器做了专门优化。因为count(*)返回的行一定不是空。扫描全表，但是不取值，按行累加。
性能方面：
count(可空字段) < count(非空字段) = count(主键 id) < count(1) ≈ count(*)

8.SQL语句优化

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描
2. order by 条件要与where中条件一致，否则order by不会利用索引进行排序
3. 对where条件后的查询条件尽量添加索引
4. 多表关联查询时，小表在前，大表在后。
5. 避免在索引列上使用内置函数
6. 对于复杂的查询，可以使用中间临时表 暂存数据；
7.避免在where子句中使用 or 来连接条件
8.尽量使用数值替代字符串类型(例如性别（sex）：0代表女，1代表男;)
9.避免在where子句中使用!=或<>操作符

9.SQL常用函数有哪些？

数学函数 ABS(x) 返回x的绝对值 CEILING(x) 返回大于x的最小整数值 FLOOR(x) 返回小于x的最大整数值 GREATEST(x1，x2，...，xn) 返回集合中最大的值 LEAST(x1，x2，...，xn) 返回集合中最小的值
聚合函数 avg 返回指定列的平均值 count 返回指定列中非NULL值的个数 min 返回指定列的最小值 max 返回指定列的最大值 sum 返回指定列的所有值之和 group_concat 返回由属于一组的列值连接组合而成的结果
日期和时间函数 CURDATE()或CURRENT_DATE() 返回当前的日期 CURTIME()或CURRENT_TIME() DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7) DAYOFMONTH(date) 返回date是一个月的第几天(1~31) DAYOFYEAR(date) 返回date是一年的第几天(1~366) DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE); FROM_UNIXTIME(ts，fmt) 根据指定的fmt格式，格式化UNIX时间戳ts HOUR(time) 返回time的小时值(0~23) MINUTE(time) 返回time的分钟值(0~59) MONTH(date) 返回date的月份值(1~12)返回当前的时间

10.左连接 右连接 内连接的区别

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小
inner join 内连接，只保留两张表中完全匹配的结果集；
left join会返回左表所有的行，即使在右表中没有匹配的记录；
right join会返回右表所有的行，即使在左表中没有匹配的记录；

11.Mysql三种常见引擎的区别

MySQL常见的三种存储引擎为InnoDB、MyISAM和MEMORY。其区别体现在事务安全、存储限制、空间使用、
内存使用、插入数据的速度和对外键的支持。
1. 事务安全：InnoDB支持事务安全，MyISAM和MEMORY两个不支持。
2. 存储限制：InnoDB有64TB的存储限制，MyISAM和MEMORY要是具体情况而定。
3. 空间使用：InnoDB对空间使用程度较高，MyISAM和MEMORY对空间使用程度较低。
4. 内存使用：InnoDB和MEMORY对内存使用程度较高，MyISAM对内存使用程度较低。
5. 插入数据的速度：InnoDB插入数据的速度较低，MyISAM和MEMORY插入数据的速度较高。
6. 对外键的支持：InnoDB对外键支持情况较好，MyISAM和MEMORY两个不支持外键。

Mysql面试题扩展

1.mysql中explain是什么？有什么作用？描述下type

explain:模拟Mysql优化器是如何执行SQL查询语句的，分析查询语句或表结构的性能瓶颈。

Type：type是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是： system > const> eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery >index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref。

2.mysql中有哪几种锁？表锁、行锁、页锁区别？

MySQL大致可归纳为以下3种锁：
- 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
- 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
- 页面锁：开销和加锁时间以及锁定粒度界于表锁和行锁之间；会出现死锁；并发度一般。

3.悲观锁 for update 、 乐观锁(version) 的区别

悲观锁（Pessimistic Lock），每次在拿数据的时候都会上锁，在sql语句添加for update,就可以实现悲观锁。
乐观锁（Optimistic Lock），每次使用数据的时候不会上锁，在sql语句添加version（版本号）就可以实现乐观锁。
两种锁各有优缺点，乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样就可以省去锁的开销，加大系统的整个吞吐量。

4.什么是索引？索引的种类有哪些，描述索引的优缺点?

索引是为了加速对数据行的检索，是数据库管理系统中一个排序的数据结构。
索引的种类：
1.普通索引：加速对数据的访问
2.唯一索引：索引列的值唯一，但可以为null
3.组合索引：列的组合值必须唯一
4.主键索引：特殊的唯一索引，不能为空，一张表只有一个主键，一般同表创建时一同创建

索引的优点：
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时 间。
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
索引的缺点：
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空 间，如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

5.设计索引的原则？(不少于4条)

1.对于经常查询的字段，建议创建索引。
2.索引不是越多越好，一个表如果有大量索引，不仅占用磁盘空间，而且会影响INSERT，DELETE，UPDATE等语句的性能。
3.避免对经常更新的表进行过多的索引，因为当表中数据更改的同时，索引也会动态更新，十分消耗系统资源。
4.数据量小的表建议不要创建索引，数据量小时索引不仅起不到明显的优化效果，对于索引结构的维护反而消耗系统资源。
5.不要在区分度低的字段建立索引。比如性别字段，只有 “男” 和 “女” ，建索引完全起不到优化效果。
6.当唯一性是某字段本身的特征时，指定唯一索引能提高查询速度。
7.在频繁进行跑排列分组（即进行 group by 或 order by操作）的列上建立索引，如果待排序有多个，可以在这些列上建立组合索引

6.什么情况下索引会失效？（不少于4条）

1.如果mysql估计使用全表扫描要比使用索引快,则不使用索引。
2.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
3.防止因字段类型不同造成的隐式转换，导致索引失效
4.like查询是以%开头，索引失效；以%结尾，索引有效
5.如果条件中有or，即使其中有条件带索引也不会使用
6.复合索引未用左列字段
7.where中索引列有运算;
8.where中索引列使用了函数;

7.Btree和Hash区别

BTree:BTree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量。
Hash:Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索

8.Btree和B+tree的区别

btree的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；

b+tree的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。

9.数据库优化方案？

优化索引，sql语句，分析慢查询
设计表的时候严格按照数据库设计规范来设计数据库
如果单表数据时过多采用分表策略。单表字段个数过多进行冷热数据拆分
使用缓存，把经常访问并且不需要经常变化的数据放在缓存中，能够节约磁盘IO
数据量很大的时候可采用分库分表

10.什么是存储过程？有什么优缺点？

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

优点:
1. 存储过程是预编译过的，执行效率高。
2. 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
3. 安全性高，执行存储过程需要有一定权限的用户。
4. 存储过程可以重复使用，减少数据库开发人员的工作量。
缺点:
1. 调试麻烦，但是用 PL/SQL Developer 调试可以弥补这个缺点。
2. 移植性差。
3. 重新编译问题，如果带有引用关系的对象发生改变时，包将需要重新编译（可设置成运行时自动编译）。
4. 在一个程序系统中大量的使用存储过程，随着用户需求的增加会导致数据结构的变化，维护该系统很难很麻烦、而且代价很大

11.什么是视图，视图有什么作用？

视图也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。
视图的具体作用：
1. 可以定制用户数据，聚焦特定的数据。
2. 可以简化数据操作。
3. 基表中的数据有一定的安全性
4. 可以合并分离的数据，创建分区视图