MySQL相关面试收集整理

1.数据库的两种引擎及其区别

 mysql5.6之前是MyISAM，5.6及之后使用的是InnoDB

InnoDB和MyISAM

InnoDB	MyISAM
支持事务	不支持事务
支持行级锁	支持表级锁
支持MVCC（多版本并发控制）	不支持MVCC
支持外键	不支持外键
5.6之前不支持全文搜索，5.6后支持	支持全文搜索
不保存表的总行数，查询count（*）时需要全表扫描	MyISAM用一个变量保存表的总行数，查总行数速度快
聚集索引，数据文件是和索引绑定在一起的，必须要有主键通关主键所以效率高。辅助索引需要查询两次，先查询主键，再通过主键查询到数据。主键太大，其他索引也会很大。	MyISAM是非聚集索引，数据文件时分离的，索引保存的时数据文件的指针，主键索引和辅助索引是独立的

InnoDB存储引擎提供了具有提交，回滚，崩溃恢复能力的事务安全，与MyISAM比InnoDB写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

MyISAM不支持事务，也不支持外键，优势是访问的速度更快。对事务的要求完整性唯有要求，以select和insert为主的应用可以使用这个存储引擎。

2.MySQl索引的实现原理及聚簇索引和飞聚簇索引的区别

使用B+tree来实现，B+tree的叶子节点储存数据，非叶子节点只用来储存索引，

https://blog.csdn.net/u013308490/article/details/83001060

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

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

3.sql优化

通过慢查询日志判断哪些sql的执行效率低

使用explain获取低效率sql的执行计划

结合sql与执行计划，进行分析与优化

1.首先使用explain查询sql语句是否走了索引，未走索引则添加相关索引（普通索引，唯一索引，主键索引，复合索引，全文索引，覆盖索引）

主键选取的字段要有可辨识性，频繁出现在查询条件中，比如在身份证，电话号码相关字段添加索引，不能选取性别一类的重复性字段添加索引，要知道我们添加索引的目的时为了给快的去检索表中的数据辨识性越高，查询的效率越快，否则则是白白浪费资源

索引建立的场景选取

中到大数据量的表格适合添加索引，小数据量表的话，全文索引的效率可能更高，而且数据量

过大的话，建议使用复合索引，将两个字段作为一个索引使用，可以大大提高索引效率。再大的话就要考虑分库分表了（分库分表待整理）

---------------

2.索引失效问题

查询过慢也可能是sql书写不规范导致索引失效，索引失效的相关场景

a.模糊查询（like）通配符%后添加了索引字段（like %索引字段）

b.对索引字段进行数学或函数运算处理 + - * / count() max() min() ..

c.索引的最左匹配性质 比如当你使用abcd建立联合索引时，使用ac会导致索引失效建议使用

d.使用<>或!=或is null或is not null会导致索引失效

e.使用in和not in也会导致索引失效

f.字符串不加单引号会导致索引失效

---------------

3.一张表中的索引尽量不要超过五个

---------------

4.避免使用select*，返回自己或者客户想要的数据

---------------

5.sql执行满也可能是数据量太大，这时我们可以进行分页查询（在索引上完后曾排序分页操作。借助主键进行关联）；单表数据量过大，考虑上面的分库分表操作；不重要的数据可以考虑非关系数据库存取数据，提高查询效率的同时减轻数据库的压力

---------------

6.应尽量避免在 where 子句中对字段进行 null 值判断，将null值设置为默认值，否则将导致引擎放弃使用索引而进行全表扫描

---------------

7.尽量避免创建和删除临时表，增大对数据库的消耗

8.in和not in尽量避免使用 建议使用  EXISTS ， NOT EXISTS 或BETWEEN

琐碎面试题：

1.char和verchar的区别

char是一种固定长度的字符串类型，verchar是一种可变长度的字符串类型。当字段选用char类型时，当填入的数据不足设置的长度时，系统会用隐藏的空格占用内存，造成不必要的内存消耗

2.脏读 幻读 不可重复读

脏读：一个事务读取另外一个事务还没有提交的数据。
幻读：指在一个事务内两次读取同一条件的数据，两次读取的数据条数不同。
不可重复读：指在一个事务内，多次读同一条件的数据，数据条数相同但数据的值发生了改变。
幻读与不可重复读的区别就是：幻读是由于其他事务    insert 或    delete 导致的；不可重复读是由于其他事 务    update 导致的

3.数据库的隔离级别

1.读未提交（Read Uncommitted）：是最低的事务隔离级别，它允许另外一个事务可以看到这个事务未提 
交的数据。会出现脏读，幻读，不可重复读，所有并发问题都可能遇到。
2.读已提交（Read Committed）：保证一个事物提交后才能被另外一个事务读取。另外一个事务不能读取该 
事物未提交的数据。不会出现脏读现象，但是会出现幻读，不可重复读。
3.可重复读（Repeatable Read）：这种事务隔离级别可以防止脏读，不可重复读，但是可能会出现幻象 
读。它除了保证一个事务不能被另外一个事务读取未提交的数据之外还避免了不可重复读。
4.序列化（串行化）（Serializable）：这是花费最高代价但最可靠的事务隔离级别。事务被处理为顺序执行。防止脏读、不可重复读、幻读。

4.外连接和内连接的区别

内连接返回两表都存在的数据，外（左右）连接返回是主（左右）表所有字段的数据，没有数据的字段用null来填充

5.事务的四大要素

原子性（Atomicity）：事务开始后所有操作，要么全部完成，要么全部不完成，不可能停滞在中间环节。事务执行过程中出错，会回滚（Rollback）到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位
一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到
隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账
持久性（Durability）：事务完成后，该事务所对数据库所作的更改将被保存到数据库之中，不能回滚