mysql新增列并同时增加数据_什么？数据库面试太难？看这一篇就够了！

索引相关

1.什么是索引

索引是一种数据结构，可以帮助我们快速的进行数据查找。

2.索引是什么样的数据结构

索引的存储结构跟具体的存储引擎实现有关，在mysql中使用较多的索引有Hash索引，B+树索引等。而我们经常使用的InnoDB存储引擎的默认索引实现是：B+树索引。

3.Hash索引和B+树索引有什么区别或者劣势

首先要知道Hash索引和B+树索引的实现原理。

Hash索引的实现原理：

hash索引底层是hash表，进行查找时，调用一次hash函数就可以取到相应的键值，之后进行回表查询获得实际数据。

B+树索引实现原理：

B+树索引底层实现是多路平衡查找树，对于每一次查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

区别：

• hash 索引进行等值查询更快（一般情况下），但是无法进行范围查询

因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的所有节点皆遵循（左节点小于父节点，右节点大于父节点）天然支持范围。

• hash索引不支持使用索引进行排序，原理同上

4.什么是聚簇索引

在B+数索引中，叶子节点可能存储了当前key的值，也可能存储了当前的key值以及整行的数据，这就是非聚簇索引和聚簇索引。

在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引，如果没有唯一键，则隐式生成一个键来建立聚簇索引。

当查询使用聚簇索引时，在对应的叶子节点上，可以获取到整行数据，因此不用再次回表查询。

5.非聚簇索引一定会回表查询吗

不一定，如果查询是覆盖索引，也就是查询的字段全部命中了索引，那么就不需要回表查询。

举个例子：假设我们在员工表的年龄列上建立了索引，那么当进行

select age from employee where age < 20

查询时，在索引子节点已经包含了age信息，不会再进行回表查询。

6.建立索引有哪些需要考虑的因素

建立索引一般需要考虑到字段的使用频率，经常作为条件进行查询的字段比较适合作为索引。如果需要建立联合索引的话，还需要考虑联合索引中的顺序。

除此之外还需要考虑，防止过多的索引对表造成太大压力，这些都和实际的表结构以及查询方式有关。

那些列需要建立索引

（1）在经常需要搜索的列上，可以加快搜索的速度；

（2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

（3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

（4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

（5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

（6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

对于有些列不应该创建索引：

（1）对于那些在查询中很少使用或者参考的列不应该创建索引。

这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

（2）对于那些只有很少数据值的列也不应该增加索引。

这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

（3）对于那些定义为text, image和bit数据类型的列不应该增加索引。

这是因为，这些列的数据量要么相当大，要么取值很少。

(4)当修改性能远远大于检索性能时，不应该创建索引。

这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

7.联合索引是什么？为什么需要注意联合索引中的顺序

MySql中可以使用多个字段同时建立一个索引，叫作联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

具体原因如下：

MySql使用索引时需要索引有序，假设现在建立了“name，age，school”的联合索引。

那么索引的排序为从左到右：先按照name排序，如果name相同则使用age排序，如果age也相等则使用school排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以按照age字段用作索引查找，以此类推。

因此在建立联合索引时应该把频繁查询的字段或者选择性高的列放到前面。此外还可以根据特例查询或者表结构进行单独的调整。

8.创建的索引有没有被使用到？或者说怎么才可以知道这条语句运行很慢的原因

MySql提供了explain命令来查看语句的执行计划，MySql在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。

可以通过其中和索引有关的信息来分析是否命中了索引，例如possible_key，key，key_len等字段。分别说明了此语句可能用到的索引，实际使用的索引，以及使用索引的长度。

注解：

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

9.何时索引失效

• 使用不等查询
• 列参与了数***算或者函数
• 字符串like时最左边是通配符："%aaa"
• 当mysql分析全表扫描比使用索引快时不使用索引
• 当使用联合索引时，前面一个条件为范围查询，后面的即使是符合最左前缀原则也不使用索引。

10.冷门考点 索引只有好处？说说索引的缺点 数据的写入过程，会涉及索引的更新，这是索引导致数据写入变慢的主要原因。建立索引后，索引就必须维持它的结构，即索引需要满足 B+Tree 的结构，而在 B+Tree 中，k 值是根据页的大小事先计算好的（一个磁盘页所能容纳索引的大小），也就是说，每个节点最多只能有 k 个子节点。在往数据库中写入数据的过程中，这样就有可能使索引中某些节点的子节点个数超过 k，这个节点的大小超过 1 个磁盘页的大小，读取这样 1 个节点， 就会导致多次磁盘 IO 操作。我们该如何解决这个问题呢?实际上，我们只需要将这个节点分裂成两个节点。但是，节点分裂之后，其上层父节点的子节点个数就有可能超过 k 个。不过这也没关系，我们可以用同样的方法，将父节点也分裂成两个节点。这种级联反应会从下往上，一直影响到根节点。

事务相关

1.什么是事务

事务最经典的例子是转账，从A账号转到B账号100块钱，要保证A账号减去了100同时B账号增加了100，这个事务才算是完成了，任何一方失败两方的操作都要回滚，否则就会出现不一致。

事务是一些列的操作，他们要符合ACID特性，最常见的理解就是，事务中的操作要么全部成功，要么全部失败，但是只是这样还不够。

2.ACID是什么？可以详细说一下嘛？

A=Atomicity

原子性：就是说，要么完全成功，要么全部失败，

C=Consistency

一致性：系统总是从一个一致性的状态转移到另一个一致性的状态，不会存在中间状态。一致性表示事务完成后，符合逻辑运算。

I=Isolation

隔离型：通常来说，一个事务在完全提交之前，对其他事务是不可见的，注意是通常来说（如果隔离级别很低的话是可以看到其他事务未提交的数据的）。

D=Durability

持久性：一旦事务提交了，那么永远都是这样子了，哪怕系统崩溃也不会影响到这个事务的结果。

3.同时多个事务运行会怎么样

多事务并发一般会造成以下几个问题：

• 脏读：A事务读取了B事务未提交的内容，而B事务后面进行了回滚。
• 不可重复读：A事务读取了B事务提交的内容（这里指修改）。读取了修改数据。
• 幻读：A事务读取了一个范围的内容，而同时事B事务在此期间插入了一条新数据，造成了幻觉。读取了新增数据。

4.怎么解决这些问题，MySql的隔离级别了解吗？

MySql四种隔离级别：

• 未提交读

这就是上面所说的例外情况，这种隔离级别下，其他事务可以看到本事务没有提交的部分修改，因此会造成脏读问题。这个级别的性能没有足够大的优势，但是又有很多问题，因此很少使用。

• 已提交读

其他事务只能读取到本事务已经提交的部分，这个隔离解绑有不可重复读的问题，在同一个事务内的两次读取，拿到的结果可能不一样，因为另外一个事务对数据进行了修改。

• 可重复读

可重复读隔离级别解决了上面不可重复读的问题，但是仍然有一个问题就是幻读，当你读取id>10的数据行是，对涉及到的行加上了读锁，此时另外的一个事务新插入了一条数据id=11的数据，因为是新插入的，所以不会触发上面的锁的排斥。那么进行本事务的下一次的查询时会发现有一条id=11的数据，而上次查询操作并没有获取到。

• 可串行化

这事最高的隔离级别，可以解决上面提到的所有问题，因为它强制所有的操作串行执行，这会导致并发能力，因此也不常用。

5.InnoDB使用的是那种隔离级别？

InnoDB默认使用的是可重复读的隔离级别。

6.对MySql锁了解吗？

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样一个机制。

就像酒店的房间，如果大家随意进出，就会出现多个人抢夺同一个房间的情况，而在房间上装上锁，申请到钥匙的人才可以入住并且将房间锁起来，其他人只能等其他人使用完毕才可以再次使用。

7.MySql都有哪些锁？

从锁的类别上来讲，有共享锁和排他锁

共享锁：

又叫做读锁，当用户要进行数据的读取时，对数据加上共享锁，共享锁可以同时加上多个。

排他锁：

又叫做写锁，当用户要进行数据的写入时，对数据加上排它锁，排它锁值可以加一个，他和其他的排它锁，共享锁都互斥。

用上面的例子来说就是用户的行为有2种，一种是来看房，多个用户一起看房是可以接受的。一种是真正的入住一晚，在这期间，无论是想入住还是想看房都不可以。

锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁、页级锁、表级锁。

他们的加锁开销从大到小，并发能力也是从大到小。

表结构设计

1.为什么要尽量设定一个主键

主键是数据库确保数据行在整张表唯一性的保障，及时业务上本张表没有主键，也建议添加一个自增长的ID列作为主键。设定了主键后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全。

2.主键使用自增ID还是UUID

推荐使用自增ID，不要使用UUID，因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+数叶子节点上存储了主键索引以及全部的数据（按照顺序），如果主键是自增ID，那么需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成很多的数据插入，数据移动，然后导致产生很多的内存碎片进而造成插入性能下降。

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

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

4.如果要存储用户的密码散列，应该使用寿命字段进行存储

密码散列,盐,用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

存储引擎相关

1.mysql支持哪些存储引擎

MySql支持多种存储引擎，比如InnoDB，MyISAM，Memory，Archive等等。

在大多数情况下，直接选择使用InnoDB引擎都是最合适的，InnoDB也是MySql的默认存储引擎。

2.InnoDB和MyISAM有什么区别？

• InnoDB支持事务，MyISAM不支持
• InnoDB支持行级锁，而MyISAM不支持
• InnoDB支持MVCC，而MyISAM不支持
• InnoDB支持外键，而MyISAM不支持
• InnoDB不支持全文索引，而MyISAM支持

零散问题

1.MySql中的varchar和char有什么区别？

char是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用10个字符,而varchar是变长的

也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间.

在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char.

2.varchar(10)和int(10)各代表什么含义？

varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度,而int的10只是代表了展示的长度,不足10位以0填充.

也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示。

3.MySql的binlog有几种录入格式？有什么区别？

有三种格式：statement、row、mixed

• statement模式

记录单元为语句，即每一条sql造成的影响都会记录，由于sql执行是有上下文的，因此保存的时候需要保存相关信息，同时还有一些使用了函数之类的语句无法被记录复制。

• row模式

row模式下记录单元为每一行的改动，基本是全部可以记录下来，但是由于很多操作，会导致大量行的改动（比如alter table），因此这种模式下的文件保存的信息太多，日志量太大。

• mixed模式

一种折中的方案，普通的操作使用statement记录，当无法使用statement记录的时候使用row。

此外新版本的mysql对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

4.超大分页怎么处理？

超大分页可以从三个方向来解决：

• 数据库层面

数据库层面的优化可能性有许多种,但是核心思想都一样,就是减少load的数据。

像下面这种查询可以进行优化：

select * from table where age > 20 limit 1000000,10 

这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢。

把查询所有修改为查询主键，之后根据主键id去查询行数据，这样一次查询的数据量就会小很多

select * from table where id in (select id from table where age > 20 limit 1000000,10) 

这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快。

如果ID连续的好，那么我们还可以使用如下语句查询：记录最大的id，效率很好

select * from table where id > 1000000 limit 10 

• 从需求角度减少

主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.

• 缓存

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可。

5.sql耗时怎么统计，统计过慢查询吗？对慢查询怎么优化？

慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?

所以优化也是针对这三个方向来的。

• load额外的数据

看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写。

• 没有命中索引

分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。

• 数据量是不是太大

如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

6.横向分表和纵向分表，可以分别举一个例子吗？

横向分表

横向分表是按照行进行分表，假设我们有一张用户表，主键是自增ID同时是用户ID，数据量较大，如果有1亿多条，那么此时放在一张表里面查询效果就不太理想。

我们可以根据主键ID进行分表，无论是按照尾号分，或者按照ID区间分都是可以的。

假设按照尾号0-99分为100个表，那么每张表中只有100w，这时的查询效率无疑是可以满足要求的。

纵向分表

纵向分表是按照列进行分表，假设我们有一张文章表，包含字段id-标题-摘要-内容，而系统中的展示形式是刷新出一个列表，列表中仅包含标题和摘要。

当用户点击某篇文章进入详情时才需要正文内容，此时，如果数据量大，将内容这个很大切不常用的列放在一起会拖慢原表的查询速度。

我们可以将上述表分为两张表：列表：id-标题-摘要-content-id，文章表：id-文章内容。当用户点击详情，根据content-id再取一次内容即可。而增加的存储量只是很小的content-id字段，代价很小。

当然，分表其实和业务的关联度很高，在分表之前，一定要做好调研和benchmark，不要按照自己的猜想盲目操作。

7…说说三范式

第一范式: 每个列都不可以再拆分，两列的属性相近或相似或一样，尽量合并属性一样的***保不产生冗余数据。

留言表

名称	字段

名称	字段

如果需求知道那个省那个市并按其分类，那么显然第一个表格是不容易满足需求的，也不符合第一范式。

名称	字段

名称	字段

显然第一个表结构不但不能满足足够多物品的要求，还会在物品少时产生冗余。也是不符合第一范式的。

第二范式: 非主键列完全依赖于主键，而不能是依赖于主键的一部分。

每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。

名称	字段

一个人同时订几个房间，就会出来一个订单号多条数据，这样子联系人都是重复的，就会造成数据冗余。我们应该把他拆开来。
订单表

名称	字段

用户表

名称	字段

这样便实现一条数据做一件事，不掺杂复杂的关系逻辑。同时对表数据的更新维护也更易操作。

第三范式: 非主键列只依赖于主键,不依赖于其他非主键。

数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。像：a–>b–>c 属性之间含有这样的关系，是不符合第三范式的。

比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）

这样一个表结构，就存在上述关系。 学号–> 所在院校 --> (院校地址，院校电话)

这样的表结构，我们应该拆开来，如下。

（学号，姓名，年龄，性别，所在院校）–（所在院校，院校地址，院校电话）

在设计数据库结构的时候,要尽量遵守三范式,如果不遵守,必须有足够的理由.比如性能. 事实上我们经常会为了性能而妥协数据库的设计.

9.MyBatis 中的 # 和$区别

#会将传入的内容当做字参数值,而$会直接将传入值拼接在sql语句中。

10.如何防范SQL注入攻击

• 普通用户与系统管理员用户的权限要有严格的区分。
• 强迫使用参数化语句。
• 加强对用户输入的验证。
• 多多使用SQL Server数据库自带的安全参数。

11.MVCC
MVCC MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。 MVCC主要是为Repeatable-Read事务隔离级别做的。在此隔离级别下，A、B事务所示的数据相互隔离，互相更新不可见。
我们可以通过InnoDB的MVCC实现来分析MVCC使怎样进行并发控制的.
innodb存储的最基本row中包含一些额外的存储信息 DATA_TRX_ID，DATA_ROLL_PTR，DB_ROW_ID，DELETE BIT
6字节的DATA_TRX_ID 标记了最新更新这条行记录的transaction id，每处理一个事务，其值自动+1
7字节的DATA_ROLL_PTR 指向当前记录项的rollback segment的undo log记录，找之前版本的数据就是通过这个指针
6字节的DB_ROW_ID，当由innodb自动产生聚集索引时，聚集索引包括这个DB_ROW_ID的值，否则聚集索引中不包括这个值.，这个用于索引当中
DELETE BIT位用于标识该记录是否被删除，这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在commit的时候
具体的执行过程
begin->用排他锁锁定该行->记录redo log->记录undo log->修改当前行的值，写事务编号，回滚指针指向undo log中的修改前的行
12.MySQL主从复制过程

Binary log：主数据库的二进制日志。 Relay log：从服务器的中继日志。
第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。
第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。
第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。