MySQL面试

1.为什么要使用数据库

数据保存在内存

优点： 存取速度快

缺点： 数据不能永久保存

数据保存在文件

优点： 数据永久保存

缺点：1）速度比内存操作慢，频繁的IO操作。2）查询数据不方便

数据保存在数据库

1）数据永久保存

2）使用SQL语句，查询方便效率高。

3）管理数据方便

2.什么是SQL？

结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。

3.什么是MySQL?

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。

4.数据库三大范式是什么

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

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

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

5.mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。

user权限表： 记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表： 记录各个帐号在各个数据库上的操作权限。
table_priv权限表： 记录数据表级的操作权限。
columns_priv权限表： 记录数据列级的操作权限。
host权限表： 配合db权限表对给定主机上数据库级操作权限作更细致的控制。

6.MySQL存储引擎MyISAM与InnoDB区别

Innodb引擎： Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyISAM引擎(原本Mysql的默认引擎)： 不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎： 所有的数据都在内存中，数据的处理速度快，但是安全性不高。

7.InnoDB引擎的4大特性

插入缓冲（insert buffer)

二次写(double write)

自适应哈希索引(ahi)

预读(read ahead)

8.存储引擎选择

查询存储引擎：

show engines \G;

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

9.什么是索引？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

10.索引有哪些优缺点？

索引的优点

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。

11.如何创建和删除索引

主键和使用unique的字段默认添加索引

12.查看索引

type为all时全表扫描

13.索引实现原理

14.索引分类

一、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。
1：普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。

2：唯一索引：索引列中的值必须是唯一的，但是允许为空值。

3：主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。

二、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询。

三、全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引

15.主键索引和唯一索引区别

1.主键索引不能为null值，而唯一索引可以
2.主键索引只有一个，唯一索引可以有多个
3.主键索引可以作为其他表的外键，唯一索引不行

16.创建索引的三种方式

第一种方式：在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);

第二种方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

第三种方式：使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

17.什么时候添加索引

1.数据量庞大
2.该字段有很少的DML操作
3.该字段经常出现在where语句中

18.什么时候索引失效

模糊查询

-- 只要它的查询条件是一个不以通配符开头的常量
select * from user where name like 'jack%'; 
-- 如果一通配符开头，或者没有使用常量，则不会使用索引，例如： 
select * from user where name like '%jack'; 

19.百万级别或以上的数据如何删除

索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作
1.先删除索引
2.然后删除其中无用数据
3.删除完成后重新创建索引(此时数据较少了)创建索引

20.最左前缀匹配原则

非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的

21.B树

B树层数一般为3-4层，由MySQL决定
如果阶数为3，每个格最多不超过3个

下图为B树结构图，其中p1、p2、p3是存储子节点的位置信息，相当于指针，16，34指的是主键值，data中存储的是除了主键以外的数据

上图为B树结构，如果为三层的话，每个磁盘最多为16KB, 如果每个data数据占用1KB,那么B树总共能够存储的数据量为4096KB，达不到要求，只能够通过增加层数，这会增加IO量（data占用太大的内存空间）

22.B+树

数据都在最下面的叶子节点进行实际存放，B+树的叶子节点为双向链表结构，所以B+树的查找方式有两个，一个指针位于根节点，一个位于key最小的叶子节点

data不再存储在每个磁盘空间中，而是存储在每个叶子节点，加入p+键值占用内存为10个子节，那么一个磁盘块可以存储1600个p+键值，三层B+树可以存储16x1600x1600个data

23.B树和B+树的区别

1.在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。

2.B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

24.回表

如果在查找过程中为name添加索引，会发生关联主键，通过name关联到主键值，然后再通过主键查找。整个过程叫做回表

25.索引覆盖

如果查询语句为select id，name from table where name =“xxx”;在关联主键的时候B+树已经查找出来了id，name，没有必要再进行主键索引，这种情况叫做索引覆盖（using index）

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

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

27.哈希表索引缺点

1.需要将所有的数据都存储在内存中，耗费内存空间
2.等值查询比较快，但是范围查询很麻烦
3.哈希冲突

28.二叉树索引缺点

增加了IO的次数

29.聚簇索引和非聚簇索引

InnoDB中是聚簇类型的，也就是数据文件和索引文件是放在一起的，文件后缀.idb
在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

MyISAM是非聚簇类型的，索引文件和数据文件不是存放再同一个位置，索引文件中存放的是地址，通过地址查找相应的数据文件

30.什么是数据库事务？

事务是一个不可分割的数据库操作序列，事务是逻辑上的一组操作，要么都执行，要么都不执行。

31.事物的四大特性(ACID)

A：原子性，事务是最小的执行单位，不可再分
C：一致性，多条DML语句要么同时成功，要么同时失败
I：隔离性，处理多个事务时，事务是相互隔离的
D：持久性，数据只有最终持久化到硬盘中，事务才算执行成功

提交事务后，历史操作清空，同时文件发生改变，回滚只是历史记录清空，磁盘上的文件不发生改变

32.事务的隔离级别

MySQL中的DML语句是自动提交的，如果要关闭自动提交，start transaction
READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。（Oracle默认）

REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。（MySQL默认）

SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

33.SQL语句主要分为哪几类

数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER
数据查询语言DQL（Data Query Language）SELECT
数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE
数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK

34.SQL 约束有哪几种？

5种
NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK: 用于控制字段的值范围。

35.drop、delete与truncate的区别

36.数据库结构优化

1.将字段很多的表分解成多个表
对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

2.增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。

3.增加冗余字段
合理的加入冗余字段可以提高查询速度。