【Mysql面试题】

Mysql面试题

1.char和varchar的区别

（1）char设置多少长度就是多少长度，varchar可以改变长度，所以char的空间利用率低于varchar。
（2）因为char的长度固定所以char的存取速度比varchar快。
（3）char适用于固定长度的字符串，如身份证号、手机号等，varchar适用于不固定的字符串。

2.数据库的三大范式

（1）第一范式(1NF)：保证字段不可再分，保证了原子性。
（2）第二范式(2NF)：保证第一范式的前提下，表的每一列都必须和主键有关系，消除了部分依赖。
（3）第三范式(3NF)：保证第二范式的前提下，标的每一列都必须和主键有直接关系，不能是间接关系，消除传递依赖。

3.sql的执行顺序

（1）from
（2）on
（3）inner join | left join | right join
（4）where
（5）group by
（6）聚合函数
（7）having
（8）select
（9）distant
（10）order by
（11）limit

4.inner join | left join | right join的区别

（1）inner join (内连接/等值连接)：只返回两个表中联结字段相等的行
（2）left join (左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录 。
（3）right join (右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录。

5.索引是什么

索引是一种高效获取数据的数据结构，相当于目录，可以快速的找到数据，索引是一个文件所以会占用物理空间。

6.索引有几种类型

（1）普通索引：基本索引类型，允许索引的字段为空值或重复值。
（2）唯一索引：索引的值必须唯一，允许索引的字段为空值。
（3）主键索引：索引的值必须唯一，不可以为空。
（4）复合索引：多个字段加索引，遵循最左匹配原则。
（5）全局索引: 只有在 MyISAM 引擎上才能使用。

7.最左匹配原则

最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、<、between and、like）就会停止匹配。
如：Z表建立联合索引（a,b,c）

//Z表创建联合索引(a，b，c)
CREATE INDEX index_abc ON Z ( `a`, `b`, `c`)

//这样索引abc列都起效，因为符合最左匹配原则，where子句几个搜索条件顺序调换不影响查询结果，因为Mysql中有查询优化器，会自动优化查询顺序
select  *  from Z where a = 1 and b = 2 and c = 3 

//因为a列是起点，没有a列匹配不上，所以索引失效
select * from table_name where  b = 2 and c = 3 

//因为连续不到b，所以只有a列索引生效
select * from table_name where  a = 1 and c = 3 

8.索引的优缺点

优点：
（1）提高检索速度。
（2）索引列对数据排序，降低排序成本。
（3）（词条不常用的话可以不说）mysql 8之后引入了，隐藏索引，当一个索引被隐藏就不会被优化器所使用，就可以看出来索引对数据库的影响，有利于调优。
缺点：
（1）由于索引本身是一个文件，所以会占用物理空间。
（2）当对数据进行更新操作时会降低更新的操作，因为不仅要更新数据还要更新索引。

9.索引的高效设计及优化

（1）选择唯一性索引：值是唯一的，查询的更快。
（2）对经常作为查询条件的字段加索引。
（3）对经常需要排序、分组和联合操作、去重操作的字段建立索引：order by、group by、union(联合)、distinct(去重)等。
（4）限制索引个数：索引数量多，需要的磁盘空间就越多，更新表时，对索引的重构和更新就很费劲。
（5）表数据少的不建议使用索引(百万级以内)：数据过少，有可能查询的速度，比遍历索引的速度都快。
（6）删除不常用和不再使用的索引。
（7）用类型小的类型做索引：比如：int和BIGINT能用int就使用int。因为类型小，查询速度快和索引占用的空间更少。
（8）使用前缀索引，要是字符串越长，那么索引占的空间越大，并且比较起来就时间就越长。

10.避免索引失效的方法

（1）某列使用范围查询(>、<、like、between and)时, 右边的所有列索引也会失效。
（2）不对索引字段进行运算。
（3）在where子句中不使用 OR、!=、<>和对值null的判断。
（4）避免使用’%'开头的like的模糊查询。
（5）字符串不加单引号，会造成索引失效。

11.索引的数据类型

（1）Hash： 查询时调用Hash函数获得地址，回表查询实际数据。(InnoDB和MylSAM不支持，Memory支持)。
（2）B+树： 每次从根节点出发去查询，然后得到地址，回表查询实际数据。

12.索引为什么使用树结构

（1）加快查询效率。
（2）保持有序。

13.二叉树、B树、B+树的区别

（1）二叉查找树(二叉排序树、二叉搜索树)： 一个节点最多两个子节点(左小右大)，查询次数和比较次数都是最小的，但是索引是存在磁盘的，当数据量过大的时候，不能直接把整个索引文件加载到内存，需要分多次IO，最坏的情况IO的次数就是树的高度，为了减少IO，需要把树从竖向变成横向。
（2）B树( B- )： 是一种多路查询树，每个节点包含K个子节点，节点都存储索引值和数据，K是B树的阶(树高被称为树的阶)。虽然比较的次数比较多，但是是在内存的比较，可以忽略不计，但是B树IO的次数要比二叉查找树要少，因为B树的高度可以更低。
（3）B+树： B树的升级版，只有叶子节点储存的是索引值指向的数据库的数据。

14.为什么索引的数据结构使用的是B+树，而不是B树

（1）树只适合随机检索，而B+树同时支持随机检索和顺序检索(因为叶子节点相当于链表，保存索引值都是有序的)。
顺序检索： 按照序列顺序遍历比较找到给定值。
随机检索： 不断从序列中随机抽取数据进行比较，最终找到结果。
（2）减少了磁盘IO，提高空间利用率： 因为B+树非叶子节点不会存放数据，只有索引值，所以非叶子节点可以保存更多的索引值，这样B+树就可以更矮，减少IO次数。
（3）B+树适合范围查找： 这才是关键，因为数据库大部分都是范围查找，B+树的叶子节点是有序链表，直接遍历就行，而B树的范围查找可能两个节点距离很远，只能通过中序遍历去查找，所以使用B+树更合适。
中序遍历： (根在中，从左往右，一棵树的左子树永远在根前面，根永远在右子树前面）

15.MySQL数据的存储引擎

（1）MylSAM： 表锁（悲观锁）级别的存储引擎，不支持事务和外键。（mysql5.5之前的存储引擎）。
（2）InnoDB： 行锁（乐观锁）级别的存储引擎，支持事务和外键。（mysql5.5之后的存储引擎，）
（3）Memory： 内存数据库引擎，因为在内存操作，所以读写很快，但是Mysql服务重启，会丢失数据，不支持事务和外键。

16.数据库事务

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

17.事务的四大特性(ACID)

（1）原子性（Atomicity）   ： 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

（2）一致性（Consistency） ：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。

（3）隔离性（Isolation）   ：事务与事务之间互不干扰,隔离性是当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

（4）持久性（Durability）   ：事务一旦被提交了，那么对数据库中的数据的改变就是永久的。即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成。否则的话就会造成我们虽然看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。这是不允许的。

18.事务的隔离级别

（1）read uncommited(读取未提交内容)： 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据，也被称之为脏读（Dirty Read）

（2）read committed(读取已提交内容)： 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。一个事务只能看见已经提交事务所做的改变。可解决脏读

（3） repeatable read(可重读)： 这是MySQL的默认事务隔离级别，同一事务的多个实例在并发读取数据时，会看到同样的数据。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。可解决脏读、不可重复读

（4）serializable(可串行化) ： 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。可解决脏读、不可重复读、幻读。

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

19.脏读、不可重复读、幻读

（1）脏读：也叫"读未提交"，顾名思义，就是某一事务A读取到了事务B未提交的数据。
（2）不可重复读：在一个事务内，多次读取同一个数据，却返回了不同的结果。实际上，这是因为在该事务间隔读取数据的期间，有其他事务对这段数据进行了修改，并且已经提交，就会发生不可重复读事故。
（3）幻读：在同一个事务中，第一次读取到结果集和第二次读取到的结果集不同。像幻觉一样所以叫幻读。

20.悲观锁、乐观锁

（1）悲观锁：悲观锁，指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制。也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统的数据访问层中实现了加锁机制，也无法保证外部系统不会修改数据。

（2）乐观锁：乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以只会在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回用户错误的信息，让用户决定如何去做。

21.悲观锁的使用方式

悲观锁并不是适用于任何场景，它也有它存在的一些不足，因为悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是对长事务而言，这样的开销往往无法承受。所以与悲观锁相对的，我们有了乐观锁。

使用场景举例：以MySQL InnoDB为例
商品t_items表中有一个字段status，status为1代表商品未被下单，status为2代表商品已经被下单（此时该商品无法再次下单），那么我们对某个商品下单时必须确保该商品status为1。假设商品的id为1。
如果不采用锁，那么操作方法如下：

//1.查询出商品信息
select status from  t_items where id=1;
//2.根据商品信息生成订单,并插入订单表 t_orders 
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_items set status=2;

这种场景在高并发访问的情况下很可能会出现问题。例如当第一步操作中，查询出来的商品status为1。但是当我们执行第三步Update操作的时候，有可能出现其他人先一步对商品下单把t_items中的status修改为2了，但是我们并不知道数据已经被修改了，这样就可能造成同一个商品被下单2次，使得数据不一致。所以说这种方式是不安全的。

使用悲观锁来解决问题
在上面的场景中，商品信息从查询出来到修改，中间有一个处理订单的过程，使用悲观锁的原理就是，当我们在查询出t_items信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为t_items被锁定了，就不会出现有第三者来对其进行修改了。需要注意的是，要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。我们可以使用命令设置MySQL为非autocommit模式：set autocommit=0;
设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_items where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_items set status=2;
//4.提交事务
commit;/commit work;

上面的begin/commit为事务的开始和结束，因为在前一步我们关闭了mysql的autocommit，所以需要手动控制事务的提交。
上面的第一步我们执行了一次查询操作：select status from t_items where id=1 for update;与普通查询不一样的是，我们使用了select…for update的方式，这样就通过数据库实现了悲观锁。此时在t_items表中，id为1的那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。需要注意的是，在事务中，只有SELECT … FOR UPDATE 或LOCK IN SHARE MODE 操作同一个数据时才会等待其它事务结束后才执行，一般SELECT … 则不受此影响。拿上面的实例来说，当我执行select status from t_items where id=1 for update;后。我在另外的事务中如果再次执行select status from t_items where id=1 for update;则第二个事务会一直等待第一个事务的提交，此时第二个查询处于阻塞的状态，但是如果我是在第二个事务中执行select status from t_items where id=1;则能正常查询出数据，不会受第一个事务的影响。

Row Lock与Table Loc（行锁 和 表锁）
使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键或者索引，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。举例如下：

1、select * from t_items where id=1 for update; 
这条语句明确指定主键（id=1），并且有此数据（id=1的数据存在），则采用row lock。只锁定当前这条数据。 
2、select * from t_items where id=3 for update; 
这条语句明确指定主键，但是却查无此数据，此时不会产生lock（没有元数据，又去lock谁呢？）。 
3、select * from t_items where name='手机' for update; 
这条语句没有指定数据的主键，那么此时产生table lock，即在当前事务提交前整张数据表的所有字段将无法被查询。 
4、select * from t_items where id>0 for update; 或者select * from t_items where id<>1 for update;（注：<>在SQL中表示不等于） 
上述两条语句的主键都不明确，也会产生table lock。 
5、select * from t_items where status=1 for update;（假设为status字段添加了索引） 
这条语句明确指定了索引，并且有此数据，则产生row lock。 
6、select * from t_items where status=3 for update;（假设为status字段添加了索引） 
这条语句明确指定索引，但是根据索引查无此数据，也就不会产生lock。

22.乐观锁的使用方式

（1）使用版本号version

使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

（2）使用时间戳

乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

23.优化数据库

（1）SQL优化
（2）增加缓存
（3）分表
（4）读写分离

24.SQL语句优化

（1）SQL语句中IN包含的值不应过多。
MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。
（2）尽量不要用select *，要使用具体字段。
（3）避免返回大量数据，采用分页最好。limit
（4）如果限制条件中其他字段没有索引，尽量少用or。
or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。
（5）尽量使用union all替代union，因为union会自动去重。
union自动去重就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。
（6）避免在where子句中对字段进行null值判断
对于null的判断会导致引擎放弃使用索引而进行全表扫描。
（7）不建议使用%前缀模糊查询
例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。
（8）避免隐式类型转换
where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型。
（9）使用索引，提升查询速度，不宜建太多索引，不能建在重复数据比较多的字段上。
（10）批量插入比单条插入要快，因为事务只需要开启一次，数据量太小体现不了。
（11）避免子查询，优化为多表连接查询。
（12）对于联合索引来说，要遵守最左前缀法则
（13）注意范围查询语句
对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

25.常用的聚合函数

（1）sum(列名) 求和 　　　　
（2）max(列名) 最大值 　　　　
（3）min(列名) 最小值 　　　　
（4）avg(列名) 平均值 　　　　
（5）first(列名) 第一条记录
（6）last(列名) 最后一条记录
（7）count(列名) 统计记录数不包含null值 count(*)包含null值。

26.drop、truncate、delete的区别

（1）速度： drop > truncate > delete。
（2）回滚： delete支持，truncate和drop不支持。
（3）删除内容： delete表结构还在，删除部分或全部数据，不释放空间。truncate表结构还在，删除全部数据，释放空间。drop表结构和数据不在，包括索引和权限，释放空间。

27.Mysql数据库事务的一致性怎么解释？

一致性是指，事务必须是使数据库从一个一致性状态变到另一个一致性状态。（从一个正常状态转换为另一个正常状态）。
一致性体现在两个层面：
（1）数据库机制层面
数据库层面的一致性是，在一个事务执行之前和之后，数据会符合你设置的约束（唯一约束，外键约束,Check约束等)和触发器设置。比如:A有90，向B转了100，这时A的账户余额就是负数，而金额的字段设置了unsigned(即非负数)，这时数据库就会报错，违反了非负约束的一致性。
（2）业务层面
于业务层面来说,一致性是保持业务的一致性。比如：A有100，B有0，A向B转了100，这时AB的账户之和还得是100。如果不是就违反了业务逻辑的一致性。再比如扣了2块手续费，B只收到了98，那么业务上就必须处理考虑手续费的这2块，不然也就违反了业务的一致性。