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