Mysql面试题

MySQL面试题

整理一些常见面试题

mysql使用的是哪种存储引擎

MySQL支持很多种存储引擎，MySQL5.5版本之前默认使用的是MyISAM存储引擎，从MySQL5.5版本之后，MySQL的默认内置存储引擎就是InnoDB了

MyISAM和InnoDB有什么区别

• InnoDB支持事务，MyISAM不支持
• InnoDB支持外键，MyISAM不支持
• 查询总记录数（select count（*））时，InnoDB效率更低，因为InnoDB需要去统计行数，而MyISAM将行数单独存储了
• InnoDB灾难恢复性比MyISAM好
• InnoDB最小锁粒度为行级锁，可以支持更高的并发；MyISAM最小锁粒度为表级锁，并发度很差，加锁快，锁冲突较少，不太容易发生死锁
• InnoDB 是聚集索引，MyISAM 是非聚集索引。聚集索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。非聚集索引，数据文件是分离的，索引保存的是数据文件的指针

MyISAM和InnoDB如何选择

1、是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM

2、如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，就用InnoDB

3、系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB

4、MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差

聚集索引和非聚集索引

• 聚集索引的索引顺序与表数据存储顺序一致，叶子节点存储的是真实的数据
• 非聚集索引的索引顺序与表数据存储顺序无关，叶子节点存储的是指向真实数据的指针(主键的值)
• 当查询使用聚集索引时，在对应的叶子节点，可以获取到整行数据，不用再次进行回表查询

聚集索引的优点是查询速度快，因为一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后

聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度

非聚集索引一定会回表查询吗

不一定，这涉及到查询语句所要求的字段是否全部命中了索引（覆盖索引），如果全部命中了索引，那么就不必再进行回表查询，eg：

select id,name from user where name='shenjian'; # 创建了name索引，能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高

MySQL 5.6中，对索引做了哪些优化

Index Condition Pushdown（索引下推）优化，eg：

people表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

如果没有使用索引下推技术，则MySQL会通过zipcode='95054'从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件

如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054'的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接筛掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数

MySQL常用存储引擎的底层原理

InnoDB和MyISAM这两种引擎底层都是采用B+树的数据结构来构建索引

B树是一棵多路平衡查找树，简单来说，B树可以看做平衡二叉树的进阶版，它与平衡二叉树的不同点主要在B树的一个节点可以存放多个关键字，并且B树的每个节点可以有两个以上的子节点，而这些都取决于B树的阶数，当B树的阶数为2时，它就是一个普通的平衡二叉树

B+树是B树的变种，在B+树中，所有的关键字都会保存在叶子节点中，叶子节点之间也会有指针进行连接，形成一个链表的形式，和B树相比，这样的结构方便范围查找。比如要查询大于3的关键字，我们从根节点往下遍历，找到关键字为3的叶子节点之后，直接读取3之后的叶子节点就可以了，而不用一次次的从根节点去遍历大于3的关键字。当我们进行的范围查找进行倒序操作的时候，凭借叶子节点的单向链表是无法实现的，因此MySQL中的B+树结构做了一些调整，MySQL将B+树叶子节点的单向链表改为双向链表

Hash索引和B+树索引有什么区别

• hash索引底层就是hash表，B+树底层实现是多路平衡查找树
• 一般情况下hash索引进行等值查询更快，调用一次hash函数就可以获取到相应的键值，但是无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持-致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，天然支持范围
• hash索引不支持使用索引进行排序，原理同上

什么时候索引会失效

• 违反最左前缀法则
• 对索引列进行运算（计算、函数、（自动or手动）类型转换）
• 索引列的使用在范围条件之后
• 索引列使用不等于（!=或者<>）
• is null或is not null有可能导致索引失效
• like以通配符开头（可以使用覆盖索引解决此索引失效）
• 字符串类型不加单引号（mysql会隐式转换成字符串，导致对索引列进行了运算）
• or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

事务的特性

• 原子性（Atomicity）

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

• 一致性（Consistency）

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

• 隔离性（Isolation）

隔离性是指并发的事务是相互隔离的，一个事务的执行不能被其他事务干扰

• 持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

MySQL同时有多个事务可能会产生什么问题

• 脏读：A事务读取到了B事务未提交的内容，而B事务后面进行了回滚.
• 不可重复读：B事务读取了两次数据，在这两次的读取过程中A事务修改了数据，B事务的这两次读取出来的数据不一样
• 幻读：B事务读取了两次数据，在这两次的读取过程中A事务添加了数据，B事务的这两次读取出来的集合不一样，看起来和不可重复读差不多，幻读强调的集合的增减，而不是单独一条数据的修改

MySQL事务隔离级别

• 读未提交（Read Uncommitted）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别是最低的隔离级别，虽然拥有超高的并发处理能力及很低的系统开销，但很少用于实际应用。因为采用这种隔离级别只能防止更新丢失问题，不能解决脏读，不可重复读及幻读问题

• 读已提交（Read Committed）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别可以防止脏读问题，但会出现不可重复读及幻读问题

• 可重复读（Repeatable Read）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。这种隔离级别可以防止除幻读外的其他问题

• 可串行化（Serializable）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读、第二类更新丢失问题。在这个级别，可以解决上面提到的所有并发问题，但可能导致大量的超时现象和锁竞争，通常数据库不会用这个隔离级别，我们需要其他的机制来解决这些问题:乐观锁和悲观锁

四种隔离级别会产生的问题如下图

mysql中on和where的区别

1. 先执行on，后执行where
2. on是建立关联关系，where是对关联关系的筛选
   • on是在生成临时表的时候使用的条件
   • where则是在生成临时表之后使用的条件

修改表结构

修改表：前缀：ALTER TABLE 表名

• 修改之添加列： ALTER TABLE 表名 ADD ( 列名 列类型, 列名 列类型, ... );
• 修改之修改列类型(如果被修改的列已存在数据，那么新的类型可能会影响到已存在数据)：ALTER TABLE 表名 MODIFY 列名 列类型;
• 修改之修改列名：ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;
• 修改之删除列：ALTER TABLE 表名 DROP 列名;
• 修改表名称：ALTER TABLE 原表名 RENAME TO 新表名;

数据库连接池的工作机制

数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中

数据库优化方法

• 选取最适用的字段属性（char、varchar）
• 使用连接（JOIN）来代替子查询(Sub-Queries)
• 使用联合(UNION)来代替手动创建的临时表
• 事务

JDBC中如何进行事务处理

Connection提供了事务处理的方法，通过调用setAutoCommit(false)可以设置手动提交事务；当事务完成后用commit()显式提交事务；如果在事务处理过程中发生异常则通过rollback()进行事务回滚