Mysql数据库相关面试题总结（30道）

作者: SlashBoyMr_wang | 来源:发表于2018-08-31 22:25 被阅读21次

1、列举常见的关系型数据库和非关系型都有那些？

关系型数据库：Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL
非关系型数据库：NoSql、Cloudant、MongoDb、redis、HBase

两种数据库之间的区别：

关系型数据库：

关系型数据库的特性
1、关系型数据库，是指采用了关系模型来组织数据的数据库；
2、关系型数据库的最大特点就是事务的一致性；
3、简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。
关系型数据库的优点
1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；
2、使用方便：通用的SQL语言使得操作关系型数据库非常方便；
3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；
4、支持SQL，可用于复杂的查询。
关系型数据库的缺点
1、为了维护一致性所付出的巨大代价就是其读写性能比较差；
2、固定的表结构；
3、高并发读写需求；
4、海量数据的高效率读写；

非关系型数据库

非关系型数据库的特性
1、使用键值对存储数据；
2、分布式；
3、一般不支持ACID特性；
4、非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合。
非关系型数据库的优点
1、无需经过sql层的解析，读写性能很高；
2、基于键值对，数据没有耦合性，容易扩展；
3、存储数据的格式：nosql的存储格式是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，
而关系型数据库则只支持基础类型。
非关系型数据库的缺点
1、不提供sql支持，学习和使用成本较高；
2、无事务处理，附加功能bi和报表等支持也不好；

2、MySQL常见数据库引擎及比较？

常见的数据库引擎是MyiSAM和inmoDB
比较：
- MyISAM：
  1、它不支持事务，也不支持外键，其优势是访问的速度快
- innoDB：
  1、InnoDB支持事务安全，对比MyISAM引擎，InnoDB写的效率差一些，并且会占据更多的磁盘空间。
  2、MySQL支持外键的存储引擎只有InnoDB

3、简述数据三大范式？

第一范式（ 1NF）： 字段具有原子性,不可再分。
第二范式（ 2NF）：是在第一范式（ 1NF）的基础上建立起来的，要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。
第三范式（ 3NF）： 必须先满足第二范式（ 2NF）。简而言之，第三范式（ 3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。所以第三范式具有如下特征：1，每一列只有一个值。2，每一行都能区分。3，每一个表都不包含其他表已经包含的非主关键字信息。

4、什么是事务？什么是锁？MySQL如何支持事务？

事务：主要用于处理操作量大，复杂度高的数据。一般来说，事务是必须满足4个条件（ACID）：
- 原子性：所谓原子操作是指不会被线程调度机制打断的操作；这种操作一旦开始，就一直运行到结束，中间不会有任何 context switch （切换到另一个线程）。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行，而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

- 锁：是实现事务的关键，所可以保证事务的完整性和并发行，与现实生活中的锁一样，可以使某些数据的拥有者，在某段时间内不能使用某些数据或者数据结构。

注意：MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

5、简述数据库设计中一对多和多对多的应用场景？

一对多：当在数据库的设计中，涉及到一个对象可以包含多个另外对象，而单个另外对象只能对应一个对象时就产生了一对多的关系，例学生和班级，这种情况通常将外键设在多的对象表结构中。
多对多：当在数据库的设计中，涉及到一个对象可以包含多个另外对象，而单个另外对象也对应多个对象时就产生了多对多的关系，例书籍和作者，这种情况通常将外键设在多的对象表结构中。

6、简述触发器、函数、视图、存储过程？

触发器:使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为,触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的
函数:是MySQL数据库提供的内部函数(当然也可以自定义函数)。这些内部函数可以帮助用户更加方便的处理表中的数据
视图:视图是虚拟表或逻辑表，它被定义为具有连接的SQL SELECT查询语句。
存储过程:存储过程是存储在数据库目录中的一坨的声明性SQL语句，数据库中的一个重要对象,有效提高了程序的性能

7、MySQL索引种类

普通索引、唯一索引(主键索引、唯一索引)、联合索引、全文索引、空间索引

8、索引在什么情况下遵循最左前缀的规则？

在建立了联合索引的前提条件下，数据库会一直从左向右的顺序依次查找，直到遇到了范围查询(>,<,between,like等)

9、主键和外键的区别？

主键：是指表中一个列或者列的组合，其值能够唯一的标识表中的每一个行。这样的一列或者多列成为表的主键，通过它可以强制表的实体完整性。当创建或者更改表时可以通过定义PRIMARY KEY约束来创建主键，一个表只能有一个主键约束，而且主键约束中的列不能是空值，由于主键约束确保唯一数据，所一经常来定义标识列。
外键：外键是建立于表与表之间的联系。外键保证了数据的完整性，使用外键，简单直观，可以直接在数据模型中体现，无论是设计、维护等。

10、MySQL常见的函数？

聚合函数：avg,min,max,count,sum,
字符串处理函数：
合并字符串函数：concat(str1,str2,str3…)
比较字符串大小函数：strcmp(str1,str2)
获取字符串字节数函数：length(str)
获取字符串字符数函数：char_length(str)
字母大小写转换函数：大写：upper(x),ucase(x)；小写lower(x),lcase(x)
处理数值的函数:
绝对值函数：abs(x)
向上取整函数：ceil(x)
向下取整函数:floor(x)
取模函数：mod(x,y)
随机数函数：rand()
四舍五入函数：round(x,y)
数值截取函数：truncate(x,y)
用于处理时间日期的函数:
获取当前日期：curdate()，current_date()
获取当前时间：curtime()，current_time()
获取当前日期时间：now()
从日期中选择出月份数：month(date),monthname(date)
从日期中选择出周数：week(date)
从日期中选择出周数：year(date)
从时间中选择出小时数：hour(time)
从时间中选择出分钟数：minute(time)
从时间中选择出今天是周几：weekday(date),dayname(date)

11、列举创建索引但是无法命中索引的8种情况。

1、查询条件中有or、not in、not exist等
2、小表查询
3、like查询是以%开头
4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5、没有使用索引字段查询
6、对索引列进行运算，需要建立函数索引
7、单独引用联合索引中的非第一位置的索引
8、没有查询条件

12、如何开启慢日志查询？

参数说明：
- slow_query_log： 慢查询开启状态
- slow_query_log_file：慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
- long_query_time：查询超过多少秒才记录
开启慢日志查询方法一：全局变量设置
1、将 slow_query_log 全局变量设置为“ON”状态。指令示例：mysql> set global slow_query_log='ON';
2、设置慢查询日志存放的位置：mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';
3、查询超过1秒就记录：mysql> set global long_query_time=1;
开启慢日志查询方法二：配置文件设置
修改配置文件my.cnf，在[mysqld]下的下方加入：

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

修改完参数后重启数据库服务就可以了

13、数据库导入导出命令（结构+数据）？

导出数据库结构和数据：mysqldump -u用户名 -p密码数据库名> 路径+导出的文件名.sql
导出数据库所有表结构：mysqldump -u用户名 -p密码 -d 数据库名>路径+文件名.sql
导出数据表结构和数据：mysqldump -u用户名 -p密码数据库名表名>路径+文件名.sql
导出数据表结构：mysqldump -u用户名 -p密码 -d 数据库名表名>路径+文件名.sql

14、数据库优化的思路

- 1.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 只会在检索出所有记录之后才对结果集进行过滤

- 2.索引优化
合理使用：普通索引、唯一索引(主键索引、唯一索引)、联合索引、全文索引、空间索引

- 3.数据库结构优化
1）范式优化：
比如消除冗余（节省空间。。）
2）反范式优化：
比如适当加冗余等（减少join）
3）拆分表：
分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
4）拆分其实又分垂直拆分和水平拆分：
案例：简单购物系统暂设涉及如下表：
1.产品表（数据量10w，稳定）
2.订单表（数据量200w，且有增长趋势）
3.用户表（数据量100w，且有增长趋势）以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万。
垂直拆分：解决问题：表与表之间的io竞争。不解决问题：单表中数据量增长出现的压力。
方案：把产品表和用户表放到一个server上订单表单独放到一个server上
水平拆分： 解决问题：单表中数据量增长出现的压力不解决问题：表与表之间的io争夺。
方案：用户表通过性别拆分为男用户表和女用户表，订单表通过已完成和完成中拆分为已完成订单和未完成订单，产品表、未完成订单放一个server上；已完成订单表和男用户表放一个server上；女用户表放一个server上(女的爱购物哈哈)
- 4.服务器硬件优化
略

15、char和varchar的区别？

char的长度是不可变的，而varchar的长度是可变的。char的存取速度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。再者，char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节。

16、简述MySQL的执行计划？

在工作过程中，有时候会对慢查询进行调优。对于MySQL的SQL语句调优，MySQL本身提供了强大的explain关键字用于查询分析执行计划。

语法：从语法角度explain和describe/desc是相同的，只是一般更常用desc看表结构，explain来看查询计划。

详细了解请点链接：http://www.cnblogs.com/micrari/p/6583482.html

17、在对name做了唯一索引前提下，简述以下区别：

select * from tb where name = ‘Oldboy-Wupeiqi’ # 全局遍历找所有
select * from tb where name = ‘Oldboy-Wupeiqi’ limit 1 # 锁定一条就结束

18、1000w条数据，使用limit offset 分页时，为什么越往后翻越慢？如何解决？

原因：越是向后,扫描的数据也就越多
解决方案:
- 1、limit限制优化法：把limit偏移量限制低于某个数。。超过这个数等于没数据
- 2、存储本页数据两端的主键,按主键查找后向前或向后取多少条

19、什么是索引合并？

索引合并:是把几个索引的范围扫描合并成一个索引。索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

20、什么是覆盖索引？

MySQL可以利用索引返回SELECT 列表中的字段。而不必根据索引再次读取数据文件。包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index)。也就是平时所说的不需要回表操作

21、left join、right join以及inner join的区别

left join 左关联，主表在左边，右边为从表。如果左侧的主表中没有关联字段，会用null 填满
right join 右关联主表在右边和letf join相反
inner join 内关联只会显示主表和从表相关联的字段，不会出现null

22、写出一条Sql语句：取出表A中第31到第40记录（Mysql）

select * from A limit 30, 10

23、什么是数据库约束,常见的约束有哪几种?

数据库约束用于保证数据库、表数据的完整性（正确性和一致性）。可以通过定义约束\索引\触发器来保证数据的完整性。
总体来讲,约束可以分为:
主键约束：primary key；
外键约束：foreign key；
唯一约束：unique；
检查约束：check；
空值约束：not null；
默认值约束：default；

24、从数据库中随机取50条数据

select * from 表 order by rand() limit50;

25、什么是sql注入？

SQL注入攻击指的是通过构建特殊的输入作为参数传入Web应用程序，而这些输入大都是SQL语法里的一些组合，通过执行SQL语句进而执行攻击者所要的操作，其主要原因是程序没有细致地过滤用户输入的数据，致使非法数据侵入系统。

26、关于sql语句应该考虑哪些安全性？

1.防止sql注入，对特殊字符进行转义，过滤或者使用预编译的sql语句绑定变量。
2.最小权限原则，特别是不要用root账户，为不同的类型的动作或者组建使用不同的账户。
3.当sql运行出错时，不要把数据库返回的错误信息全部显示给用户，以防止泄漏服务器和数据库相关信息。

27、一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 ？

如果表的类型是MyISAM，那么是18。
因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失。
如果表的类型是InnoDB，那么是15。
InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失。

28、怎么把这样一个数据库表

year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year m1   m2   m3   m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

答案、
select year, 
(select amount from   aaa m where month=1   and m.year=aaa.year) as m1,
(select amount from   aaa m where month=2   and m.year=aaa.year) as m2,
(select amount from   aaa m where month=3   and m.year=aaa.year) as m3,
(select amount from   aaa m where month=4   and m.year=aaa.year) as m4
from aaa   group by year

29、简述数据库的读写分离？

读写分离为了确保数据库产品的稳数据定性，很多数据库拥有双机热备功能。也就是，第一台数据库服务器，是对外提供增删改业务的生产服务器；第二台数据库服务器，主要进行读的操作。

30、简述数据库分库分表？（水平、垂直）

垂直分库：就是按照功能的不同，把没有关联的数据放到不同的数据库和服务器中
水平分表：根据一定的规则将一个表的数据划分到不同的数据库中，两个数据库的表结构一样。