MYSQL数据库⾯试题

1、数据库备份和还原？
第一种方法，建议操作

mysqldump -A > all_back.sql 完全备份
mysql < all_back.sql 导入备份
set sql_log_bin=off; 临时禁止生成二进制文件
mysqlbinlog mysql-bin.00001 > /data/incr.sql 二进制文件导出，存入一个文件
vim /data/incr.sql 找到删除命令，加注释
mysql < /data/incr.sql 二进制文件导入
set sql_log_bin=on; 生成二进制文件
mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --
triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql InnoDB建
议完全备份策略
-u，用户名,-p，密码;–A 完全备份; -F, 备份前滚动日志;E, --events：备份相关的所有event scheduler，
计划任务;-R, --routines：备份所有存储过程和自定义函数;--master-data=#：此选项须启用二进制日志; --
flush-privileges 刷新MySQL的系统权限相关表,;--triggers：备份表相关触发器，默认启用,用--skiptriggers，
不备份触发器;--default-character-set=utf8 指定字符集; --hex-blob 使用十六进制符号转储
二进制列

第二种方法，适用于InnoDB数据库文件

直接找到数据库原文件datadir路径，拷贝对应的数据库文件，比如数据库是test，那么就拷贝test文件夹，
里面是frm表结构描述文件，恢复test文件后，发现数据库登录进去，已经有这个数据库了，表也有了，
但是查表会报错找不到，ERROR 1146 (42S02): Table 'xx' doesn't exist，原因是没有拷贝数据库的真实数据文件，ibdata1，ibdata2，
将ibdata1拷贝过去，重启mysql服务后，就正常了。

2、数据库中锁的优化策略?

① 读写分离
② 分段加锁
③ 减少锁持有的时间
④ 多个线程尽量以相同的顺序去获取资源

3、什么是主键？ 超键？ 候选键？ 外键？

主 键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键不能为空值（Null）。
超 键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
外 键：在一个表中存在的另一个表的主键称此表的外键。
作用： 主键--用来保证数据完整性
外键--用来和其他表建立联系用的
索引--是提高查询排序的速度
个数：主键--主键只能有一个
外键--一个表可以有多个外键

4、什么情况下设置了索引但⽆法使⽤ ?

① 以“%”开头的LIKE语句，模糊匹配
② OR语句前后没有同时使用索引
③ 数据类型出现隐式转化

5、存储过程与触发器的区别?

触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。
触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。

6、区别 MyISAM&InnoDB?

MyISAM引擎特点：
不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5.5前默认的数据库引擎
MyISAM存储引擎适用场景
只读（或者写较少）、表较小（可以接受长时间进行修复操作）
MyISAM引擎文件
tbl_name.frm 表格式定义
tbl_name.MYD 数据文件
tbl_name.MYI 索引文件
InnoDB引擎特点
行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引 所谓聚簇索引，就是指主索引文件和数据文件为同一份文件
崩溃恢复性更好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5.5开始为默认的数据库引擎
InnoDB数据库文件
所有InnoDB表的数据和索引放置于同一个表空间中
表空间文件：datadir定义的目录下
数据文件：ibddata1, ibddata2, ...
每个表单独使用一个表空间存储表的数据和索引

7、数据库连接池的原理。为什么要使⽤连接池。

1，连接池可以使多个操作共享一个连接。
2，数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要
建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。我们可以通过设定连接池最大连接数来
防止系统无尽的与数据库连接。更为重要的是我们可以通过连接池的管理机制监视数据库的连接的数量、使用情
况，为系统开发，测试及性能调整提供依据。
3，使用连接池是为了提高对数据库连接资源的管理

8、数据库优化的思路?

1.SQL语句优化
1查询时，能不要*就不用*，尽量写全字段名，大部分情况连接效率远大于子查询
2多表连接时，尽量小表驱动大表，即小表 join 大表
3在有大量记录的表分页时使用limit 挑出前几个
4对于经常使用的查询，可以开启缓存query_cache
5多使用explain和profile分析查询语句 是否用到了索引
6查看慢查询日志，找出执行时间长的sql语句优化
2. 索引优化议
只要列中含有NULL值，就最好不要在此例设置索引
尽量不要在列上进行运算
尽量使用短索引，
对于经常在where子句使用的列，最好设置索引
对于有多个列where或者order by子句，应该建立复合索引
对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
尽量不要使用not in
3.结构优化
1）范式优化： 比如消除冗余（节省空间。。）
2）反范式优化：比如适当加冗余等（减少join）
3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。
4.硬件优化

9、MySQL的连接类型?

解释MySQL外连接、内连接与自连接的区别
内连接：连接的数据表相对应的匹配字段完全相等的连接。连接关键字是 inner join
外连接：分为左外连接与右外连接、全连接。
左连接的结果集包括指定的左表全部数据与匹配的右表数据,右表中没匹配的全为空值.关键字 left join
右连接的结果集包含指定的右表全部数据与匹配的左边数据,左边中没匹配的全为空值.关键字 right join
全连接返回左右数据表的所有行.关键字 full join
自连接：不需要人为指定连接字段，自然会自动找同名字段进行连接，会删除连接后的重复列。 关键字 natural

10、数据库中的事务是什么?

事务（transaction）是作为一个单元的一组有序的数据库操作。如果所有操作完成，事务则提交，其修改将作用
于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。
ACID（数据库事务正确执行的四个基本要素的缩写）四大特性,原子性、隔离性、一致性、持久性。

11、 分布式数据库产品的特点?

1>.数据分布在多个异地点，抗灾性强
2>.并发性高
3>.受网络影响很大
4>.单机的性能不是特别重要，但是总体成本很高。
5>.扩展性强

12、Mysql中的事务回滚机制概述?

事务回滚是指将该事务已经完成的对数据库的更新操作撤销。
要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出
现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们
设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和第二个表都要回到未修
改的状态，这就是所谓的事务回滚

13、SQL语⾔包括哪⼏部分？每部分都有哪些操作关键字？

数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index等
数据操纵：Select ,insert,update,delete,
数据控制：grant,revoke
数据查询：select

14、如何通俗地理解三个范式？

1NF：无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值。
2NF：属性完全依赖于主键，要求表中的每个行必须可以被唯一地区分。
3NF：表中不包含已在其它表中已包含的非主关键字信息.

15、事物的四个隔离级别的区别。

READ UNCOMMITTED 可读取到未提交数据，产生脏读会读到这些未被提交的数
READ COMMITTED 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差

16、HAVNG⼦句和WHERE的异同点?

1>.语法上： where 用表中列名，having用select结果别名
2>.影响结果范围： where从表读出数据的行数，having返回客户端的行数
3>.索引： where 可以使用索引，having不能使用索引，只能在临时结果集操作
4>.聚集函数： where后面不能使用聚集函数，having是专门使用聚集函数的。

17、char和varchar的区别？什么叫视图？游标是什么？

char是一种固定长度的类型，varchar则是一种可变长度的类型，
视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，对视图的修改不影响基本表。
游标：是对查询出来的结果集作为一个单元来有效的处理

18、⼀个6亿的表a，⼀个3亿的表b，通过外键tid关联，你如何最快的查询出满⾜条件的第50000到第50200中的这200条数据记录。

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

19、⼤量数据的分页?order by 和 group by 的区别?

1，order by 从英文里理解就是行的排序方式，默认的为升序。 order by 后面必须列出排序的字段名，可以是多个字段名。
2，group by 从英文里理解就是分组。必须有“聚合函数”来配合才能使用，使用时至少需要一个分组标志字段。
sum()、count()、avg()等都是“聚合函数
select * from table order by id limit(pagesize*(pageNumbe-1,pagesize));

20、⽤⼀条 SQL 语句查询出每门课都⼤于 80 分的学⽣姓名?

name	kecheng	fenshu
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

select name from student where name not in (select name from student where fenshu <=80);

21、mysql的主从复制原理?

从库生成两个线程，一个I/O线程，一个SQL线程；
i/o线程去请求主库的binlog二进制日志，并将得到的binlog日志写到relay log（中继日志）文件中；主库会生成一个log dump 线程，用来给从库 i/o线程传binlog；
SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而使最终数据一致；

22、mysql备份的原理？

冷备：读写操作均不可进行
温备：读操作可执行；但写操作不可执行
热备：读写操作均可执行
物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度