MySQL常见面试题

1.请写出什么是事务？

将很多事件或者sql语句看作一个整体，每条语句都成功则都执行，当有一条语句失败都回滚

2.事务的特性是什么？

Atomic（原子性）：所有语句作为一个单元全部成功执行或全部取消。
Consistent（一致性）：如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
Isolated（隔离性）：事务之间不相互影响。
Durable（持久性）：事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

3.MySQL事务的隔离级别有几种，分别是什么？

1.RU：READ UNCOMMITTED（独立提交）未提交读，允许事务查看其他事务所进行的未提交更改
2.RR：REPEATABLE READ 可重复读，确保每个事务的 SELECT 输出一致  InnoDB 的默认级别  #commit之后，其他窗口看不到数据，必须退出重新登录查看
3.RC：READ COMMITTED 允许其他事务查看已经提交的事务
4.串行化：SERIALIZABLE 将一个事务与其他事务完全隔离   #当一个事务没有提交，查询也不行

4.什么是脏读，幻读，不可重复读？

1.脏读（RU级别）：执行一个事务，还没有提交就被读取，可是事务回滚了，那么之前被读取到的数据就是脏数据

2.幻读：删除数据库所有内容，刚删除完，其他事物添加了数据，好像没有删除干净一样，以为是幻觉

3.不可重复读：执行事物的时候需要读取两次数据，第一次读取与第二次读取之间数据被修改，导致第一次读取和第二次读取数据不同

5.如何解决脏读，幻读，不可重复读的问题？

1.RU：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
2.RC：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
3.RR：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
4.串行化：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

6.MySQL中索引的分类有几种？分别是什么？

1.普通索引
2.唯一键索引
3.主键索引
4.前缀索引
5.联合索引

7.请写出如下慢查询SQL语句的排查过程？及解决办法

Select * from world.city where population > 100;

#排查过程
1.查询语句的条件population是否有索引
2.有索引是否使用了索引
3.如果有看看是什么级别
4.看看为什么没有走索引
5.查看数据量

#解决方式
1.建立索引
2.提高索引级别
3.优化sql语句
4.使用limit进行分批查询数据

8.MySQL 存储引擎，innodb与myisam的区别？

#MyISAM：
不支持事务，但是每次查询都是原子的；
支持表级锁，即每次操作是对整个表加锁；
物理文件有三个
不支持热备

#InnoDb：
支持ACID的事务，支持事务的四种隔离级别；
支持行级锁及外键约束：因此可以支持写并发；
物理文件有两个
支持热备

9.数据类型，varchar和char的区别？

#长度不同
1.char类型：char类型的长度是固定的。
2.varchar类型：varchar类型的长度是可变的。
#效率不同
1.char类型：char类型每次修改的数据长度相同，效率更高。
2.varchar类型：varchar类型每次修改的数据长度不同，效率更低。
#存储不同
1.char类型：char类型存储的时候是初始预计字符串再加上一个记录字符串长度的字节，占用空间较大。
2.varchar类型：varchar类型存储的时候是实际字符串再加上一个记录字符串长度的字节，占用空间较小。

#总结：
char的长度是不可变的，而varchar的长度是可变的，定义一个char(10)和varchar(10)，如果存进去的是'linux',那么char所占的长度依然为10，除'linux'外，后面跟5个空格，而varchar就立马把长度变为5了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的，char的存取速度要比varchar要快得多，因为其长度固定，方便程序的存储与查找；但是char存储空间要耗费更多。

10.Int类型的范围是多少？

int在32位环境中，取值范围为-(2^31)~(2^31-1)，即-2147483648~2147483647；

11.请写出以下锁的功能？

排他锁：保证在多事务操作时，数据的一致性。(在我修改数据时，其他人不得修改)
共享锁：保证在多事务工作期间，数据查询时不会被阻塞。
乐观锁：多事务操作时，数据可以被同时修改，谁先提交，谁修改成功。
悲观锁：多事务操作时，数据只有一个人可以修改。

12.请写出innodb存储引擎锁粒度是什么？

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

13.请写出，delete、drop、truncate的区别

1.delete是DML语句，可以选择删除部分数据，也可以选择删除全部数据；删除的数据可以回滚；不会释放空间
2.drop是DDL语句，删除表结构和所有数据，同时删除表结构所依赖的约束、触发器和索引；删除的数据无法回滚；会释放空间
3.truncate是DDL语句，删除表的所有数据，不能删除表的部分数据，也不能删除表的结构；删除的数据无法回滚；会释放空间

执行速度：drop > truncate > delete
一般使用场景：如果一张表确定不再使用，我们使用drop来操作；如果只是删表中的全部数据，一般使用truncate；如果删除的是表中的部分数据，一般使用delete

14.如何使用update代替delete？

数据库中添加一列状态列，删除数据时只需要修改状态列的值即可，开发只需要在查询时加上该列的值作为查询条件即可避免真的删除数据

15.请写出MySQL主从复制原理，画图+文字描述

1.主库配置server_id和binlog
2.主库授权从库连接的用户
3.主库查看位置点个binlog名字
4.从库配置serevr_id
5.通过change master to语句告诉从库主库的信息：host,user,password,port,binlog_file,binlog_pos
6.从库开启IO线程和SQL线程（start slave）
7.从库连接主库后，IO线程会去询问主库的dump线程，是否有比从库master.info里面记录的更新的binlog名字或位置点
8.主库接收IO线程的询问后，dump线程会去查询binlog，如果有新数据就会将新数据的binlog传给IO线程
9.IO线程拿到新数据的binlog以后，会将内容存储到TCP\IP缓存
10.TCP\IP缓存收到数据，会返回给IO线程一个ACK
11.IO线程会更新master.info新的位置点或binlog名，然后继续去主库获取新数据
12.TCP\IP会将新数据的binlog存到中继日志relay_log
13.SQL线程会去读取中继日志的内容，并且去relay-log.info对比，如果有新数据就执行新数据的内容
14.SQL线程执行完新数据，将新数据执行完的位置点更新到relay-log.info

16.请写出半同步复制和延时从库的原理？

#半同步复制
1.从库连接主库后，IO线程会去询问主库的dump线程，是否有比从库master.info里面记录的更新的binlog名字或位置点
2.主库接收IO线程的询问后，dump线程会去查询binlog，如果有新数据就会将新数据的binlog传给IO线程
3.IO线程拿到新数据的binlog以后，会将内容存储到TCP\IP缓存
4.TCP\IP会将新数据的binlog存到中继日志relay_log
5.会返回给IO线程一个ACK
6.IO线程将ACK返回给主库
7.然后主库继续写入binlog
8.SQL线程会去读取中继日志的内容，并且去relay-log.info对比，如果有新数据就执行新数据的内容
9.SQL线程执行完新数据，将新数据执行完的位置点更新到relay-log.info
10.IO线程会更新master.info新的位置点或binlog名，然后继续去主库获取新数据

#延时从库
1.SQL线程会去读取中继日志的内容，并且会读取延时时间，到达时间后才会执行

17.请写出MySQL主从复制的相关文件和线程名

#主库
1.主库的binlog：记录主库发变化的过程
2.dump线程：对比binlog是否更新，并将新数据的binlog发送给主库
#从库
1.IO线程：连接主库，接收主库发送过来新数据的binlog日志
2.SQL线程：执行主库传过来的新数据的binlog日志
3.relay-log（中继日志）：存储所有主库传过来的binlog日志
4.master.info：记录上一次请求到主库的binlog名字和位置点
5.relay-log.info：记录上一次执行relay-log的位置点，下一次从该点执行后面的内容

18.MySQL如何保证数据的安全性和一致性？

#安全性
CSR自动故障恢复
#一致性
ACID

19.请写出MySQL二进制日志的工作模式有几种？区别是什么？

#语句模式
	1.不严谨，不安全
	2.语句容易理解
	3.日志文件不是很大，不占用磁盘
	4.记录的是sql语句
#行级模式
	1.严谨，安全
	2.语句不容易理解
	3.日志文件很大，比较占用磁盘
	4.记录的是数据的变化过程
#混合模式 MIXED
	结合上面两中模式使用
	
binlog_format="ROW"
binlog_format="MIXED"
binlog_format="STATEMENT"

20.请写出mysqldump常用参数及含义

1.不加参数
2.-A
3.-B
4.-F
5.--master-data
6.--single-transaction
7.-R
9.--triggers

21.请写出如何截取二进制日志和中继日志？

mysqlbinlog --start-position=120 --stop-position=400 mysql-bin.000001 > /tmp/full.sql

22.如何在数据库中只备份单表？

mysqldump world city > /tmp/city.sql

23.如何在已运行在生产环境中的集群，添加一台从库？

1.搭建数据库
2.导出主库数据并打点
3.将主库数据导入到新的数据库
4.新搭建的库执行change master to

24.请写出MHA的工作原理？

1.保存master主库上面的所有binlog（node节点）
2.找到数据最新的从库（通过对比relay-log）
3.将数据最新的从库数据同步到其他从库
4.提升一个从库为主库（一般情况提升数据最新的，二般情况提升我们指定的从库为主库）
5.通过原来主库的binlog补全新的主库数据
6.其他从库以新的主库为主做主从复制

25.在阿里云中如何使用MHA？

使用弹性网卡作为MHA中的VIP，当数据库挂掉，则解除弹性网卡，绑定到另一台机器上

26.请写出MySQL 主从复制IO线程报错的排查思路？

1.检测网络
2.检测端口
3.查看防火墙
4.测试主从用户能否连接
5.反向解析
6.server_id或者UUID是否相同

27.请写出MySQL 主从复制SQL线程报错的解决办法？

#原因：
1.主库有的数据，从库没有
	主库：有a库
	从库：没有a库
	对主库的a库操作时，从库会出现问题
2.主库没有的数据，从库有
	从库：有b库
	主库：刚打算创建b库
	主库创建从库已有的数据库，从库会出现错误
3.主库与从库数据库结构不一致

#解决方式：
1.停掉主从
2.清空从库信息
3.同步主库所有数据
4.重新配置主从

28.如何设置MySQL的会话变量及全局变量？

#session变量（会话变量）
set session autocommit=1;
set autocommit=1;

#全局变量
set global autocommit=1;

#global变量和session变量在mysql服务重启之后，又会失效，所以都是临时生效的，如果想要永久生效，需要配置my.ini的内容定义变量

29.什么是水平分表？什么是垂直分表？表真的分了么分成什么样的了？

数据表拆分：主要就是垂直拆分和水平拆分。
水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。
垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。

30.请写出，你在企业中，MySQL遇到数据不一致问题时，是如何解决的？

#原因：
1.网络的延迟
2.主从两台机器的负载不一致
3.max_allowed_packet设置不一致
4.自增键不一致
5.双一标准同步参数设置问题
6.版本不一致

#解决：
重新做主从

31.什么是存储过程？用什么来调用？

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升

#存储过程优缺点
#优点：
1.在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑或bug，而不用重启服务器。
2.执行速度快，存储过程经过编译之后会比单独一条一条编译执行要快很多。
3.减少网络传输流量。
4.便于开发者或DBA使用和维护。
5.在相同数据库语法的情况下，改善了可移植性。

#缺点：
1.过程化编程，复杂业务处理的维护成本高。
2.调试不便。
3.因为不同数据库语法不一致，不同数据库之间可移植性差。

#存储过程的语法
1.创建视图
delimiter //
create procedure 存储过程名([in|out|inout]参数名 类型....)
begin
SQL 语句;
end//
delimiter;

2.删除视图
drop procedure 存储过程名

3.调用存储过程
call 存储过程名(参数)

#语法说明：
1.delimiter//
	是将SQL代码的结束符号设置为//，因为存储过程中有多条SQL语句以分号结束，如果定义存储过程也以分号结束，就存在混淆，所以先将//作为定义存储过程的结束符号，创建完成后再定义为原来的分号。
2.[in|out|inout]
	参数前可以添加in、out、inout关键字设置参数的类型：
	in 代表输入参数，用于传入数据，默认
	out 代表输出参数，用于返回数据，类似return的作用
	inout 代表输入和输出都可以

32.什么是触发器？触发器的作用是什么？

触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（insert，delete， update）时就会激活它执行。

#创建触发器的四要素:
监视地点（table）
监视事件（update、insert、delete）
触发时间（before、after）
触发事件（update、insert、delete）

#语法：
CREATE TRIGGER <触发器名> <触发时间> <监视事件>
ON 监视地点 FOR EACH ROW 
begin
    触发事件
end;

33.主键和唯一键还有候选键有什么区别？

主键:一个表只有一个主键，主键是唯一且非空的
唯一键:唯一键在一个表中可以有多个，数据是唯一的，可以为空
候选键：主键也是候选键，按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

34.AUTO_INCREMENT 可不可以设置最大值，可以的话怎么设置

可在建表时可用"AUTO_INCREMENT=n"选项来指定一个自增的初始值
可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值
没有最大值上限。

35.查询时运算符都有哪些

=，<>，<=，<，> =，>，AND，IN，OR或LIKE运算符。

36.列出我们用过的函数（最少4个）

password()		#密码加密
database()		#当前数据库
now()			#当前时间
count()			#统计数量
distinct()		#数据去重后数量
max()			#最大值
min()			#最小值
sum()			#求和
avg()			#求平均值

CONCAT(A, B) 	#连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
FORMAT(X, D) 	#格式化数字X到D有效数字。
CURRDATE(), CURRTIME() 	#返回当前日期或时间。
MONTH()，DAY()，YEAR()，WEEK()，WEEKDAY() 	#从日期值中提取给定数据。
HOUR()，MINUTE()，SECOND() 	#从时间值中提取给定数据。
DATEDIFF(A，B) 	#确定两个日期之间的差异，通常用于计算年龄
SUBTIMES(A，B) 	#确定两次之间的差异。
FROMDAYS(INT) 	#将整数天数转换为日期值。

37.怎么查看一个表的字符集（最少两种方法）

show create table tablename;
show charset;

#字符集常用的种类：
1.ascii：共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码
2.latin1：共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。
3.gb2312: 收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个，兼容ASCII字符集。这是一个变长字符集，如果该字符在ascii字符集中，则采用1字节编码，否则采用两字节。
4.gbk: GBK是在gb2312基础上扩容后的标准。收录了所有的中文字符。同样的，这是一个变长字符集，如果该字符在ascii字符集中，则采用1字节编码，否则采用两字节。
5.utf8和utf8mb4: 收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1～4个字节。MySQL为了节省空间，其中的utf8是标准 UTF8 阉割后的，只有1~3字节编码的字符集，基本包含了所有常用的字符。如果还要使用 emoji 表情，那么需要使用utf8mb4，这个是完整的 UTF8 字符集。
6.utf16: 不同于utf8，utf16用两个字节或者四个字节编码字符，可以理解为utf8的不节省空间的一种形式
7.utf32: 固定用四个字节编码字符，可以理解为utf8的不节省空间的一种形式

38.B+tree索引对比Btree索引在哪里进行了优化

1.B+TREE对比Btree的变化就是在也叶子节点添加了相邻节点的指针，不需要从根节点重新读取数据
2.B+tree优化了范围查询

39.Mysql优化该怎么做？

1.硬件的优化
2.系统配置的优化
3.数据库表结构的优化
4.配置文件的优化
5.SQL语句及索引的优化

#优化的原则：
查询时，能不要*就不用*，尽量写全字段名
多使用explain和profile分析查询语句
查看慢查询日志，找出执行时间长的sql语句优化
多表连接时，尽量小表驱动大表，即小表 join 大表
在千万级分页时使用limit
对于经常使用的查询，可以开启缓存
把数据库的大表按时间或一些标志分成小表
将表拆分

40.SQL语言包括哪几部分？每部分都有哪些命令？

1.数据查询语言DQL
	select、desc
2.数据操作语言DML
	insert、	update、	delete
3.数据控制语言DCL
	grant、revoke
4.数据定义语言DDL
	create、drop、alter、rename

41.请说明主键、外键和索引的区别？

#应用范畴不同:
主键是用来标记数据唯一性的，而索引是加快查询速度的，外键是为了关联两个表的

#种类不同：
根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。而主键只是其中的一种。外键与他们都不同

#创建方式不同
主键只能创建一个，索引有多种类型可以创建多种，外键不能在自己当前表创建

1.主键：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

2.索引：是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

3.外键：在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键；外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键，而是仅靠应用程序自身来保证逻辑的正确性

42.索引有哪些优缺点？

#优点：
1.可以加快数据的检索速度。
2.可以加速表和表之间的连接。
3.使用分组和排序子句进行数据检索时，可以减少查询中分组和排序的时间。

#缺点
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。
3.当对表中的数据进行增加、删除和修改的时候，索引需要动态的维护，这样就降低了数据的维护速度。

43.索引有哪几种类型？

#常见的索引扫描类型，前提条件，查询的条件必须有索引
1.index		全索引扫描
2.range		范围查询
3.ref		精确查找
4.eq_ref	类似于ref，使用join on的时候
5.const		查询的条件的列是唯一索引或主键索引
6.system	跟const同级别
7.null		执行过程中不访问表或者索引

44.请写出给table表id字段添加索引和删除索引的命令？

#添加普通索引
alter table table add index idx_name(id);
#添加唯一建索引
alter table table add unique key uni_name(name);
#添加主键索引
alter table table add primary key pri_id(id);

#删除索引
alter table table drop index idx_name;

45.一般创建索引你会考虑到那些？

1.选择唯一性索引
2.为经常需要排序、分组和联合操作的字段建立索引
3.对于查询操作中频繁使用的列建立索引，不对很少或从来不作为查询条件的列建立索引
4.限制索引的数目
5.尽量使用数据量少的索引
6.对于数据较长的列，尽量使用前缀索引
7.删除不再使用或者很少使用的索引
8.尽量的扩展索引，不要新建索引
9.当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率
10.先存数据，再建索引
11.不要对规模小的数据表建立索引，数据量超过300的表应该有索引；
12.在不同值较少的字段上不必要建立索引，如性别字段
13.经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
14.索引应该建在选择性高的字段上；
15.在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；

46.什么是临时表

临时表与实体表类似，只是在使用过程中，临时表是存储在系统数据库缓存中，当我们不再使用临时表的时候，临时表会自动删除，临时表并不是真实存在的，是sql将我们的查询结果以表格的形式展示给我们的数据

47.数据库分几种？分别都有哪些？

关系型数据库和非关系型数据库

#关系型数据库
MySQL、Oracle、mariadb

#非关系型数据库
1.键值存储数据库（key-value）
	Memcached、Redis
2.列存储（Column-oriented）数据库
	HBase
3.面向文档（Document-Oriented）数据库
	MongoDB
4.搜索引擎式数据库
	Elasticsearch

48.什么情况下索引会不生效

1.没有查询条件，或者查询条件没有建索引
2.查询的结果集是原表中的大部分数据，25%以上
3.索引本身失效
4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
5.隐式转换，会导致索引失效
6.<> 和 not in 和 or 也不走索引
7.like模糊查询 % 位置决定走不走索引
8.联合索引查询的条件不是按照顺序排列的，或者条件没有联合索引的第一个列

49.一条sql语句执行时间过长你会优化？

查询时，能不要*就不用*，尽量写全字段名
多使用explain和profile分析查询语句
查看慢查询日志，找出执行时间长的sql语句优化
多表连接时，尽量小表驱动大表，即小表 join 大表
在千万级分页时使用limit
对于经常使用的查询，可以开启缓存
把数据库的大表按时间或一些标志分成小表
将表拆分

50.你们数据库不支持emoji表情，你该怎么办？怎么操作？

升级字符集至utf8_mb4

#操作
1.导出数据库中需要需要支持emoji表情的库
2.批量修改建库语句中的字符集
3.将数据库导入新的数据库
4.将业务请求发送到新库
5.将新增的数据通过binlog同步至新的数据库

51.当服务器出现问题，你该如何入手排查问题，排查思路是什么？

#思考
1.故障的表现是什么？无响应？报错？
2.故障是什么时候发现的？
3.故障是否可重现？
4.有没有出现的规律（比如每小时出现一次）
5.最后一次对整个平台进行更新的内容是什么（代码、服务器等）？
6.故障影响的特定用户群是什么样的(已登录的, 退出的, 某个地域的…)?
7.基础架构（物理的、逻辑的）的文档是否能找到?
8.是否有监控平台可用? （比如Munin、Zabbix、 Nagios、 New Relic… 什么都可以）
9.是否有日志可以查看?

#操作
10.查看访问用户
11.查看历史命令
12.现在有哪些进程运行
13.检查CPU和内存
14.检查磁盘
15.有没有挂载文件服务器
16.是否有定时任务导致问题
17.查看日志