MySQL面试题

数据库三大范式是什么

第一范式：每个列都不可以再拆分

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键

数据库的存储引擎

Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。

MyISAM引擎(原本MySQL的默认引擎)：不提供事务的支持，也不支持行级锁和外键。

MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高
    
1. InnoDB : 支持事务, MySQL5.5版本之后, 默认的存储引擎,支持行级锁，支持外键，不支持全文索引，支持集群
2. MyISAM : 不支持事务，不支持外键，不支持行级锁，支持全文索引，效率高，不支持集群
3. Memory : 速度快,最不安全。小量快速访问的数据，不支持行级锁，不支持外键，不支持集群

sql语句的执行顺序

from > where > group by > having > 聚合函数 > distinct > order by > limit > select

mysql外连接、内连接、自连接的区别

1- 交叉连接：交叉连接由称笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配
2- 内连接：是只有条件的交叉连接
3- 外连接：它的结果集不仅 包含符合连接条件的行，而且还会包含左边、右表或者俩个表中的所有数据行，这三种情况依次称为左外连接、右连接和全外连接

drop、delete、truncate的区别

drop delete truncate 在mysql中都表示删除
    -1 delete和truncate只删除表的数据不删除表的结构
    -2 delete这个语句是dml，这个操作需要在事务提交后才可以生效
    -3 truncate和drop 的ddl，操作立即生效
    -4 速度：drop > truncate > delete

什么是索引,优缺点,分类

索引(index)：是帮助mysql高效获取数据的数据数据结构
    
优点：
    可以大大加快数据的检索速度
    通过使用索引，可以在查询的过程中，使用优化器，提高系统的性能
确定：
    对表进行插入，修改，删除的时候索引也要动态维护，会降低增/删/改的执行效率
    索引需要占物理空间

分类：    
	A. 普通索引: 最基本的索引,他没有任何的限制
	B. 唯一索引: 索引列的值必须唯一, 但是允许有空值的情况, 如果是组合索引, 则列值组合必须唯一
	C. 主键索引: 一种特殊的唯一索引, 不允许有空值, 一般在建表时, 同时创建主键索引
	D. 组合索引: 就是将单列索引进行组合
	E. 外键索引: 只有 InnoDB 引擎支持外键索引, 用来保证数据的一致性、完整性和实现级联操作
	F. 全文索引: 快速匹配全部文档的方式。InnoDB 引擎 5.6 版本后, 才能支持全文索引, MEMORY 引擎不支持

索引的数据结构（B+树，Hash）

InnoDB的索引类型目前只有两种：BTREE（B树）索引和HASH索引。B树索引是Mysql数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是（B树）索引。
    
当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储

数据库为什么使用B+树而不是B树

1、B+树的叶子节点存储了所有的数据，非叶子节点中存储的是比较关键字。而B树所有的节点都会存储数据。B+树的叶子节点之间存在一个指针连接，B树不存在指针连接。B+树所有的数据都存储在叶子节点，那么顺着叶子节点从左往右即可完成对数据的遍历，极大了简化了排序操作

2、B树只适合随机检索，而B+树同时支持随机检索和顺序检索
    
3、B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低
   
4、B+树的查询效率更加稳定
    
5、增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率

索引算法有哪些

BTree算法:
	BTree是最常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量。

Hash算法：
    Hash算法只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到叶子节点这样多次IO访问，所以检索效率远高于BTree索引

创建索引的原则

1、为常作为查询条件的字段建立索引，where子句中的列
2、为经常需要排序、分组操作的字段建立索引
3、更新频繁字段不适合创建索引
4、对于定义为text、image和bit的数据类型的列不要建立索引
5、非空字段
6、最左前缀原则，就是最左边的优先。指的是联合索引中，优先走最左边列的索引。对于多个字段的联合索引，如 index(a,b,c) 联合索引，则相当于创建了 a 单列索引，(a,b)联合索引，和(a,b,c)联合索引（但并不是建立了多个索引树）。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间

什么是最左前缀原则

最左前缀原则，就是最左边的优先。指的是联合索引中，优先走最左边列的索引。对于多个字段的联合索引，如 index(a,b,c) 联合索引，则相当于创建了 a 单列索引，(a,b)联合索引，和(a,b,c)联合索引（但并不是建立了多个索引树）。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间

什么是聚簇索引

聚簇索引：将数据与索引放到了一块，索引结构的叶子节点存储了行数据，找到索引也就找到了数据

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点存储的是行数据的地址
    
聚簇索引的优点:
	数据访问更快。聚族索引将索引和数据保存在同一个B+树中，因此从聚族索引中获取数据通常比非聚族索引中查找更快
    当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。
    使用覆盖索引扫描的查询可以直接使用节点中的主键值    

聚簇索引的缺点:
	插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能
    更新主键的代价很高，因为将会导致被更新的行移动
    通过辅助索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

数据库事务(ACID)

在我们使用数据库的时候,通常会围绕着一个业务功能, 展开一组操作, 这种操作中的每个小的步骤, 要么全部成功,要么全部失败. 那么就符合事务的概念。

• 原子性(Atomicity)：事务是一个完整的操作，事务的各步操作是不可以拆分的，要嘛都执行，要嘛不执行
• 一致性(Consistency)：事务执行之前和执行之后都必须处于一致性状态
• 隔离性(Isolation)：多个事务之间互不干扰
• 永久性(Durability)：事务完成后，把数据永久保存到磁盘中

脏读：	(读未提交) 当一个事务正在访问数据并且对数据进行修改的时候，还没有对修改的数据进行提交，然后另外一个事务访问到这个数据
不可重复读： (读已提交) 指在一个事务内多处读取同一个数据，在一个事务还没有结束的时候，另外一个事务也对他进行了修改，导致了俩次读取的数据不一样。
幻读： (不可重复读)   它发生在一个事务读取到了几行数据，接着另外一个事务插入了一些数据，然后第一个事务机会发现多了一些不存在的数据，跟产生了幻觉一样，称为幻读。

什么是事务的隔离级别？MySQL的默认的隔离级别是什么？

SQL 标准定义了四个隔离级别

• READ-UNCOMMITTED(读未提交)：最低的隔离级别，一个事务可以读取另一个事务更新但未提交的数据。可能会导致脏读、不可重复读或幻读。
• READ-COMMITTED(读已提交)：一个事务提交后才能被其他事务读取到，可以阻止脏读，但是不可重复读或幻读仍有可能发生。
• REPEATABLE-READ(可重复读)：对同一记录的多次读取结果都是一致的，除非数据是被本身事务所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
  注意：

MySQL 默认采用的 REPEATABLE_READ隔离级别，Oracle 默认采用的 READ_COMMITTED隔离级别

InnoDB 存储引擎在 分布式事务 的情况下一般会用到**SERIALIZABLE(可串行化)**隔离级别

锁的分类

1. 操作方式分类
	A. 共享锁: 也叫作 读锁。 操作同一份数据,可以查询DQL,但是不能增删改DML。
	B. 排他锁: 也叫作 写锁。 当前的操作,没有完成的情况下,会阻断其他 增删改查 操作。

2. 粒度大小分类
	A. 表级锁: 
		a. 规则: 操作数据的时候,锁住整张表
		b. 特点: 开销小,加锁快,冲突高  
		c. 代表: MyISAM 存储引擎
	B. 行级锁: 
		a. 规则: 操作数据的时候,锁住当前操作行
		b. 特点: 开销大,加锁慢,冲突低  
		c. 代表: InnoDB 存储引擎

3. 使用方式分类
	A. 悲观锁: 每次查询,都会判定数据变化, 很悲观, 所以查询时候会加锁
	B. 乐观锁: 每次查询,都会判断数据不会发生变化, 很乐观, 需要手动加锁

如何定位及优化SQL语句的性能问题？

对于查询语句，最重要的优化方式就是使用索引。而执行计划>，就是显示数据库引擎对于SQL语句执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等

SQL的生命周期？

1、连接器：客户端与数据库server层的连接器进行连接。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

2、获得请求SQL：数据库进程拿到请求sql

3、查询缓存：连接建立完成后，会判断查询缓存是否开启，如果已经开启，会判断sql是select还是update/insert/delete，对于select，尝试去查询缓存，如果命中缓存直接返回数据给客户端， 如果缓存没有命中，或者没有开启缓存， 会进入到下一步分析器

4、分析器：分析器进行词法分析和语法分析，分析器先会做“词法分析”，分析SQL中的字符串分别是什么，校验数据库表和字段是否存在，然后进行语法分析，判断SQL是否满足MySQL语法

5、优化器：优化器对sql执行计划分析，得到最终执行计划，得到优化后的执行计划交给执行器

6、执行器：开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果有权限，执行器调用存储引擎api执行sql，得到响应结果， 将结果返回给客户端，如果缓存是开启状态， 会更新缓存

7、关掉连接，释放资源

常用SQL查询语句优化方法

• 不要使用select * from t，用具体的字段列表代替“*”，使用星号会降低查询效率，如果数据库字段改变，可能出现不可预知隐患
• 应尽量避免在where子句中使用!=或<>操作符，避免在where子句中字段进行null值判断，存储引擎将放弃使用索引而进行全表扫描
• 避免使用左模糊，左模糊查询将导致全表扫描
• IN语句查询时包含的值不应过多，否则将导致全表扫描
• 为经常作为查询条件的字段，经常需要排序、分组操作的字段建立索引
• or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)
• 在使用联合索引字段作为条件时，应遵循最左前缀原则

大表怎么优化？

1、限定数据的查询范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
2、读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
3、缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
4、分库分表

垂直分表：
	垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表
水平分表：
	水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张表的数据拆成多张表来存放

MySQL的主从复制原理以及流程

主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行，从而使得从数据库的数据与主数据库保持一致

主从复制的作用:
	高可用和故障切换：主数据库出现问题，可以切换到从数据库。
	负载均衡：可以进行数据库层面的读写分离
	数据备份：可以在从数据库上进行日常备份
	
复制过程:()	
	主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
	从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；
	从：sql执行线程——执行relay log中的语句

varchar和char有什么区别

char是一个定长字段,假如申请了 char(10) 的空间,那么无论实际存储多少内容.该字段都占用10个字符,
而varchar是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用
了多长的空间
    
在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该
尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char

varchar(10)和int(10)代表什么含义

varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度
    
而int的10只是代表了展示的长度,不足10位以0填充.也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示

SQL 注入漏洞产生的原因？如何防止

原因：程序开发过程中不注意规范书写 sql 语句和对特殊字符进行过滤，导致客户端可以通过全局变量 POST 和 GET 提交一些 sql 语句正常执行

解决方案：使用PreparedStatement预编译执行者对象，用？作为占位符，用set方法进行赋值从1开始

字段为什么要求定义为not null

null值会占用更多的字节,且会在程序中造成很多与预期不符的情况

Explain 性能分析

概念：查看执行计划：使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理SQL 语句的。分析查询语句或是表结构的性能瓶颈。

作用：
	表的读取顺序
	数据读取操作的操作类型
	哪些索引可以使用
	哪些索引被实际使用
	表之间的引用
	每张表有多少行被优化器查询

语法：
	Explain + SQL 语句