面试题-mysql

关于自增主键

问：

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

答：

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

MySQL的技术特点是什么？

MySQL数据库软件是一个客户端或服务器系统，其中包括：支持各种客户端程序和库的多线程SQL服务器、不同的后端、广泛的应用程序编程接口和管理工具。

Heap表是什么？

概述

Heap表，即使用MEMORY存储引擎的表，这种表的数据存储在内存中，由于硬件问题或者断电，数据容易丢失，所以只能从其他数据表中读取数据作为临时表或者只读缓存来使用。

使用场景

涉及瞬态，非关键数据的操作，例如会话管理或缓存。
内存中存储可实现快速访问和低延迟的数据。数据量可以完全容纳在内存中，而不会导致操作系统换出虚拟内存页面。
只读或者只读为主的数据访问模式。

使用限制

BLOB 或 TEXT 字段是不允许的
只能使用比较运算符=，<，>，=>，= <
HEAP 表不支持 AUTO_INCREMENT
索引不可为 NULL

MySQL服务器默认端口是什么？

3306

与Oracle相比，Mysql的优势是什么？

+ mysql是开源软件，随时可用，无需付费。
+ Mysql是便携式的
+ 带有命令提示符的GUI
+ 使用mysql查询浏览器支持管理

如何区分FLOAT和DOUBLE？

在内存中占有的字节数不同: 单精度的float内存占4个字节, 双精度的double内存占8个字节
有效数字位数不同(尾数) : 单精度的float小数点后有效位数7位, 双精度的double小数点后有效位数16位
数值取值范围不同根据IEEE标准来计算!
在程序中处理速度不同: 一般来说，CPU处理单精度浮点数的速度比处理双精度浮点数快
首先: 能用单精度时不要用双精度以省内存，加快运算速度!
float: 当然你需要小数部分并且对精度的要求不高时，选择float单精度浮点型比较好!

区分char_length 和 length?

char_length是字符数, 不管汉字还是数字或是字母都算是一个字符。
length是字节数，在UTF-8编码中一个汉字占用三个字节，一个数字或字母占有一个字节；而在GBK编码中，一个汉字占有两个字节，一个数字或字母一个字节。

数据库事务四大原则ACID

A-原子性：事务是一个原子性质的操作单元，事务里面的对数据库的操作要么都执行，要么都不执行。
C-一致性：在事务开始之前和完成之后，数据都必须保持一致状态，必须保证数据库的完整性。也就是说，数据必须符合数据库的规则。
I-隔离性：数据库允许多个并发事务同时对数据进行操作，隔离性保证各个事务相互独立，事务处理时的中间状态对其它事务是不可见的，以此防止出现数据不一致状态。可通过事务隔离级别设置：包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）、
D-持久性：一个事务处理结束后，其对数据库的修改就是永久性的，即使系统故障也不会丢失。

Mysql四种事务隔离级别

请简洁描述 Mysql 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别？

读未提交（会导致出现脏读、不可重复读、幻读）
允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。
读已提交（会导致出现不可重复读、幻读）
允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。
可重复读（会导致出现幻读）
禁止不可重复读取和脏读取，但是有时可能出现幻读数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。
可串行化：隔离级别中最严格的，解决前面三种所有的问题，但是效率太低
提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

脏读、不可重复读、幻读区别？

所谓脏读是指一个事务中访问到了另外一个事务未提交的数据
一个事务读取同一条记录2次，得到的结果不一致
一个事务读取2次，得到的记录条数不一致

关于mysql的“四大原则”、“隔离级别”、“脏读等问题”详细可以查看文章：https://juejin.cn/post/684490...

在 Mysql 中 ENUM 的用法是什么？

限定值的取值范围，比如性别（男，女，未知）等。
但是不推荐使用ENUM这个数据类型，会有很多坑。

CHAR 和 VARCHAR 的区别?

char的长度是不可变的，而varchar的长度是可变的。(varchar更空间）
char的存取速度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找。（char更省时间）

列的字符串类型可以是什么？

set
blob
enum
char
text
varchar

如何获取当前的 Mysql 版本？

select version();

Mysql常见的存储引擎，及其区别

mysql常见的存储引擎有InnoDB、MyISAM、MERGE等等
其中常用的也就InnoDB、MyISAM两个引擎，下面我们谈谈这两个引擎的区别：

InnoDB支持事务，而MyISAM不支持事务。
InnoDB支持表锁、行级锁，而MyISAM只支持到表锁，不支持行级锁。
InnoDB适合频繁修改以及安全性较高的应用，而MyISAM适合查询、插入为主的业务场景。
InnoDB主键采用聚簇索引，而MyISAN采用非聚簇索引。
InnoDB不存储总行数，而MyISAN存储总行数。

超建、主键和候选键有什么区别？

超键：在关系中能惟一标识元组的属性集称为关系模式的超键。
候选键：不含有多余属性的超键称为候选键。也就是在候选键中，若要再删除属性就不是键了。
主键：用户选作元组标识的一个候选键称为主键。

如果一个表有一列定义为 TIMESTAMP，将发生什么？

每当行被更改时，时间戳字段将获取当前时间戳。

列设置为 AUTO INCREMENT 时，如果在表中达到最大值，会发生什么情况？

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。

怎样才能找出最后一次插入时分配了哪个自动增量？

LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值，并且不需要指定表名称。

如何查询表所有的索引？

show index from 表名;

LIKE 声明中的％和_是什么意思？

%表示0个或更多字符，而_表示一个字符。

如何在 Unix 和 Mysql 时间戳之间进行转换？

UNIX_TIMESTAMP 是从 Mysql 时间戳转换为 Unix 时间戳的命令
FROM_UNIXTIME 是从 Unix 时间戳转换为 Mysql 时间戳的命令

Mysql 查询是否区分大小写？

不区分
SELECT VERSION(), CURRENT_DATE;
SeLect version(), current_date;
seleCt vErSiOn(), current_DATE;
所有这些例子都是一样的，Mysql 不区分

LIKE 和 REGEXP 操作有什么区别？

like中的%和regexp中的^作用一致

BLOB 和 TEXT 有什么区别？

都是保存大数据的数据类型
BLOB保存的是二进制数据，而TEXT保存的是字符数据。

mysql_fetch_array和mysql_fetch_object的区别是什么？

mysql_fetch_array()：将结果作为关联数组或来自数据库的常规数组返回。
mysql_fetch_object()：从数据库返回结果行为作为对象。

一个表最多可以创建多少个索引？

任何标准表最多可以创建 16 个索引列。

NOW（）和 CURRENT_DATE（）有什么区别？

NOW()：精确到时分秒，yyyy-MM-dd hh:mm:ss
CURRENT_DATE()：只有年月日，没有时分秒

Mysql 中有哪几种锁？

全局锁、表级锁、页级锁、行级锁，四种锁。

全局锁：全局锁就是对整个数据库实例加锁。
表级锁：当前操作的整张表加锁
页级锁：页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。BDB 引擎支持页级锁。
行级锁：行级锁是粒度最低的锁，发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大，容易发生死锁现象。MySQL中只有InnoDB支持行级锁

MySQL 数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？

设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提供效率。
选择合适的表字段数据类型和存储引擎，适当的添加索引。
Mysql库主从读写分离。
找规律分表，减少单表中的数据量提供查询速度
添加缓存机制
不经常改动的页面，生成静态页面
书写高效率的sql，比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE

锁的优化策略

读写分离
分段加锁
减少锁持有的时间
多个线程尽量以相同的顺序去获取资源
不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。

优化数据库的大体流程方法

使用连接(JOIN)来代替子查询
适用联合(UNION)来代替手动创建的临时表
添加事务处理
建立索引
优化查询语句

sql语句以及索引优化

SQL语句的优化

尽量避免使用子查询，采用join的方法
用IN来替换OR，但是最好用between。
使用limit截取数据时，只截取所需要的数据范围内的，不要截取过多。
禁止不必要的Order By排序，如果我们对结果没有排序的要求，就尽量少用排序
在两个结果集没有重复数据的时候，采用union all 代替 union。
如果要插入多条数据，采用一条sql同时插入多条数据，而不是使用多条sql，逐条插入数据。
只返回必要的列，用具体的字段列表代替 select * 语句

索引的优化/如何避免索引失效

最佳左前缀法则
使用!=、<>、not in、not exists、not like等关键字会导致索引索引失效。
like 以通配符开头(%abc..)时，mysql索引失效会变成全表扫描的操作。
少用or，在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

更多关于《SQL语句及索引的优化》的内容推荐查看：https://blog.csdn.net/qq_3564...

索引，主键，唯一索引，联合索引的区别？

索引是一种特殊的文件，它们包含着对所有记录的引用指针。索引可以极大地提高数据查询速度，但是也会降低插入删除更新表的速度。因为在执行这些操作时，还要操作索引文件的维护，所以索引并不是建立的越多越好，而是要建立在合理的字段上。

普通索引：由关键字key或index定义的索引，唯一的任务就是加快对数据访问的速度。
唯一索引：保证数据记录的唯一性，不允许出现两行相同的数据。
主键：是一种特殊的唯一索引，一张表中只能定一个主键索引，用于唯一标识一条记录，使用关键字primary key来创建。
联合索引：可以覆盖多个数据列，像index(columnA, columnB)索引，这就是联合索引。

数据库中的事务是什么？

事务是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，这任务事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。

原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。
一致性：事务的执行使得数据库上从一种正确状态转换成另一种正确状态。
隔离性：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

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

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

开启配置文件中的magic_quotes_gpc和magic_quotes_runtime设置执行SQL语句时使用addslashes进行SQL语句转换。
SQL语句书写尽量不要省略双引号和单引号。
过滤掉SQL中的一些关键词：update、insert、delete、select、*。
提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的。

索引问题四连问

索引的目的是什么？

建立普通索引，是为了快速访问数据表中指定数据，提高查询数据。
创建唯一索引：保证表中数据的唯一性。
我们在进行分组和排序时，可以在group by和order by语句后加上的字段上加上索引，提高效率。

索引的负面影响是什么？

索引虽然会增加我们查询数据的速度，但是会降低我们更新、删除数据时的速度。

为哪些数据建立索引？

频繁需要被访问的数据，需要被建立索引
需要分组的数据
需要排序的数据

什么情况下不宜建立索引？

对于查询中很少设计的列或者重复值比较多的列，不宜建立索引。
对于一些特殊的数据类型，不宜建立索引，如text、BLOB等

MySQL 左连接、右连接、内连接的区别

内连接：两个表交叉匹配，只保留两个表一致得到数据。
左连接：左边的表是主表，右边的表是副表，保留主表全部的数据，只保留副标匹配到主表的数据。
右连接：右边的表是主表，左边的表是副表，保留主表全部的数据，只保留副标匹配到主表的数据。

Myql 中的事务回滚机制概述

事务就是一组数据库完整的操作序列，要么这些操作完全执行，要么一个都不执行。一旦有一个操作执行失败，就回滚到执行之前的状态。

SQL 语言包括哪几部分？每部分都有哪些操作关键字？

四部分：DDL语句（数据定义语句）、DML语句（数据操作语句）、DCL语句（数据控制语句）、DQL（数据查询语句）

Mysql索引

什么是索引？

一种帮助mysql提高查询效率的数据结构

索引的优缺点？

优点：大大加快数据查询速度
缺点：
- 维护索引需要耗费数据库资源
- 索引需要占用磁盘空间
- 当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

索引分类

主键索引：主键索引只能有一个，且不能为空，为行数据唯一的标识。
单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列
Full Text 全文索引 (My5.7版本之前只有MYISAM引擎有此索引)

索引的基本操作

主键索引

--建表 主键自动创建主键索引
create table t_user(id varchar(20) primary key, name varcher(20));

--查看索引
show index from t_user;

单列索引(普通索引|单值索引)

--建表时创建
create table t_user(id varchar(20) primary key,name varchar(20),key(name));  

--建表后创建
create index nameindex on t_user(name);

--删除索引
drop index 索引名 on 表名

唯一索引

--建表时创建
create table t_user(id varchar(20) primary key,name varchar(20),unique(name));

--建表后创建
create unique index nameindex on t_user(name);

复合索引

---建表时创建
create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));
 
--建表后创建
create index nameageindex on t_user(name,age);

索引的底层原理

1.思考
---建表
create table t_emp(id int primary key,name varchar(20),age int);

--插入数据
insert into t_emp values(5,'d',22);
insert into t_emp values(6,'d',22);
insert into t_emp values(7,'e',21);
insert into t_emp values(1,'a',23);
insert into t_emp values(2,'b',26);
insert into t_emp values(3,'c',27);
insert into t_emp values(4,'a',32);
insert into t_emp values(8,'f',53);
insert into t_emp values(9,'v',13);

--查询
select * from t_emp;

# 5.为什么上面数据明明没有按顺序插入,为什么查询时却是有顺序呢?
- 原因是:mysql底层为主键自动创建索引,一定创建索引会进行排序
- 也就是mysql底层真正存储是这样的
- 为什么要排序呢?因为排序之后在查询就相对比较快了 如查询 id=3的我只需要按照顺序找到3就行啦(如果没有排序大海捞针,全靠运气😸!)

# 6.为了进一步提高效率mysql索引又进行了优化
-  就是基于页的形式进行管理索引
-  如 查询id=4的 直接先比较页 先去页目录中找,再去 数据目录中找

# 7.上面这种索引结构称之为B+树数据结构,那么什么是B+树呢?
- 参考资料: https://www.cnblogs.com/lianzhilei/p/11250589.html

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 10^3 10^3 = 10亿条记录。
InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 10^3 10^3 = 10亿条记录。

聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

注意:在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

img

InnoDB中

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

MYISAM

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

使用聚簇索引的优势

问题: 每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？
1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

聚簇索引需要注意什么?

当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出线新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

为什么主键通常建议使用自增id

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

什么情况下无法利用索引呢?

查询语句中使用LIKE关键字
在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。
查询语句中使用多列索引
多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。
查询语句中使用OR关键字
查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。