MySQL面试题总结与理解

1. 面试经典提问场景

1.索引
2.锁
3.事务隔离级别
4.由浅到深，问到你不会为止，本文也是从简单到复杂

2.简单描述一下当前流行的数据库MySQL与Oeacle的区别，为什么都选择MySQL？

简述：MySQL开源免费，对于绝大多数公司能够节省成本，便于管理
（其实免费就行了，初级程序员才会问）

3.MySQL的数据类型

简单的分为三大类：字符，日期，数值

3.1 MySQL中varchar与char的区别？varchar中的数据

简述：char是固定长度的类型，varchar是一种可变长度的类型
varchar是最多存放50个字符串
varchar在排序时消耗内存较多，排序时，采用order by 采用fixed_length计算对应的长度
实际应用中，一般采用varchar

3.2 在数据定义时，int（11）代表什么含义

11不影响数据的存储，只影响展示效果，长度只和显示有关，

3.3 在金融类的业务中，关于金钱的数据，存储一般选择什么数据？

简述：使用int类型或者bigint类型，大整数，如果存储金额为分的数据，需要*100倍放大，存储，为了保证数据的精度
还有一种使用decimal存储，避免精度丢失，开发中Java程序员一般使用bigDevcimal

3.4 一张表里面有自增主键，当insert10条数据之后，删除第8，9，10条数据，再把MySQL重启，再添加一条数据，这条记录是11还是8？简述其原理

MySQL5.5以后默认的存储引擎为InnoDB，不重启数据库，添加的数据为11，如果重启了，存储的数据为8，因为innoDB把数据缓存到内存中，关闭数据库，释放资源，对数据进行操作，使最大的数据信息失效
在mqsql5.5 以前的版本，默认的存储引擎为myisam，不管是否重启数据库，因为myisam底层数据存储在一个表中，关闭数据库，最大的id也不会丢失
删除数据时，不建议真实删除数据

3.5 表中的大字段数据，且不会经常更新，以读为主的数据，请问是拆成子表还是继续放在一起，说出你的理由。

如果拆存在的问题：连接消耗+存储拆分空间
如果能够容忍缓存性能带来的问题，要拆的数据要和经常查询的数据的主键分区放在一起，减少资源的消耗，最后加上全文索引，来尽量抵消资源消耗。
不拆带来的问题：
如果不能容忍拆分数据带来的性能的损失，上面的问题在个别情况下一定会存在，不拆，相对节省性能的问题
在实际开发中，在商品表数据量比较大的情况下，会将商品的信息，单独存放在一个人表中，使用拆的方案。

4.MySQL有哪些搜索引擎

INnoDB：
MuISAM：
MRG_MYISAM
MEMORY
CVS
ARCHIVE
BLOCKOLE
PERFORMANCE_SCHEMA
FEDERATED
具体详解看：
https://github.com/jaywcjlove/mysql-tutorial/blob/master/chapter3/3.5.md

4.1如何选择适合本阶段开发的搜索引擎？

简述：看开发要求：比如说是否支持事务，一个数据库中，多个表可以使用不同的搜索引擎，可以提供实际的性能需求。
主要看：
1.是否需要需要事务支持。
2.有没有对索引和缓存的事务支持
3.是否需要使用热备
4.能否接受奔溃服务
5.存储的限制
6.是否需要外键的支持
目前MySQL5.5及以后版本，默认使用的是InnoDB，5.5以前的版本默认使用的是MYISAM，MUISAM不支持事务，使用锁为表级锁，插入数据时，其他的请求处于阻塞状态。InnoDB使用的是行级锁和表级锁，支持事务，查询sql不加锁，不影响其他的查询。支持奔溃后的恢复。

	InnoDB	MyISAM
事务	支持	不支持
存储限制	64TB	无限制
锁粒度	行级锁	表级锁
奔溃后恢复	支持	不支持
外键	支持	不支持
全文检索	5.7以后的版本支持	支持

4.2简述InnoDB的四大特性？

插入缓冲
二次写
自适用哈希索引
预读
--------问的非常少

4.3 为什么select count（*）from table在InnoDB中比在MyISAM中慢？简述其原理

对于selectcount（*）from table在没有条件的情况下，InnoDB要比MyISAM快，尤其数据量较大的情况下，因为InnoDB是全表扫描，而MyISAM是内部使用计数器，预存了结果，直接返回的结果。

4.4 简述不同版本的MySQL的InnoDB的改进？

以MySQL5.6举例
1.online DDL
2. memcahed NoSQL接口
3. transporttable tablespace
4. MySQL正常关闭时，可以dump出buffer pool 重启时，加快预热熟读，
5. compressed InnoDB支持压缩表格
MySQL5.7以后的改变：
6. buffer pool支持在线改变大小
7. buffer pool支持部分导出
8. 支持创建innodb tablespace 并可以在其中创建多张表
9. 透明表有压缩功能
10.磁盘临时采用innodb存储，并且存储在innodb temp tablespace里面，以前MyISAM是存储在直接存储

5.什么是索引

索引是类似于书的目录，可以快速找到对应的内容

5.1索引的好处

1.提升检索的效率，降低数据库的IO成本，索引的意义在于通过缩小表的记录加快搜索表的记录。
2.降低数据排序的成本，降低cpu的压力。索引之所以查的快，因为将数据事先拍好了顺序，如果查询的内容需要排序，则降低了查询成本。

5.2索引的弊端

占用存储空间：索引的实际做功为，一张表，一般以索引的方式存储在磁盘上
降低表的更新速度：表的数据发生改变，对应的索引也会发生改变，增删改，否则可能引起物理数据不对，这也是索引失效的一个重要原因。

5.3索引的使用场景

1.对于中大型表：索非常有效，能较大程度的提升查询的效率
2. 对于较小的表，使用全表扫描更加有效，
3. 对于特大型表：建立索引代价也会增加，后期可以使用分区技术来解决
注意：实际开发中，一般不使用分区技术，因为有全局索引的解决方案，MyCat等

5.4添加索引的类型

主要有六种：
普通索引：基本的索引不加任何的约束。
唯一索引：确保索引的唯一性
主键索引：不能为空，且唯一
复合索引：多个列组合到一起创建的索引，可以覆盖多个例
外键索引：只有InnoDB类型的数据才能使用外键索引，保证了数据的一致性，完整性，保证了数据的完整性，实现了数据的级联操作。
全文索引：只能适用于InnoDB，MYISAM并且只对英文有效，对于全英文的，一般使用全文索引

5.5MySQL的索引创建原则：

最适合出现索引的位置为，带有条件的语句，或者子查询中的子句，而不是出现在select关键词后的列
索引列的基数越大，索引的效果更好，
根据条件创建复合型索引
避免创建太多索引而额外占用磁盘空间，降低了读写操作额效率
主键尽量选择更短的数据类型，可以有效的减少磁盘的占用，提高查询的效率。
对字符串进行索引，应指定一个前缀长度，节省索引空间，减少磁盘的占用。

5.6MySQL索引使用时注意的事项

1. 应当在where条件语句中，避免使用！=或者<>否则将引擎将放弃使用索引，而进行全表扫描。
2. 尽量避免where条件语句中，使用or，否则导致引擎放弃索引而进行全表扫描
3. 避免在where语句中是使用表达式操作，这将使引擎放弃索引进行全表扫描。
4. 应尽量避免在where条件中进行函数操作，这会使索引失效，引擎会放弃索引，进行全表扫描。
5. 不要在where 条件=的左边进行函数，算数运算，否则系统将无法使用索引，进行全表扫描。
6. 复合索引遵循前缀原则。
7. 在MySQL中，如果使用索引比全表更慢，会放弃使用索引，如果使用索引，如果要强制使用索引，要加强制条件。
8. 列类是字符串的行为，查询前面不能使用% ，否则会使索引失效
9. like语句，不能在前面使用% 会使索引失效，如果模糊匹配，可以使用全文索引。

5.7索引的创建

where a=1 and  b=1
where b=1
where b=1 order by time DESC

分析思路：
以顺序创建索引，ba，time 创建复合型索引，create index table_b_a_time on index_test(b,a,time).
对于第一条数据语句，MySQL会自动优化，匹配复合索引的顺序。

5.8 如何判断一条数据，会使用了索引？

explain 显示MySQL如何使用索引来处理select 语句以及表连接，可以选择更好的索引优化语句

6.MySQL的索引原理

MySQL的索引原理后期单独更新，这里只列出相关的面试题

6.1 MySQL的索引都有哪些？

MySQL提供两种索引方式：
B-Tree索引
hash索引

1. B-Tree：B-Tree是为磁盘存储一种平衡查找树，
   介绍一下磁盘相关的知识，便于深入理解
   系统从磁盘读取信息时，是先读取到内存，再以磁盘块为基本单位进行管理，位于同一个磁盘块中的数据，会一次性全部提取，不分需求，全部获取
   InnoDB有页的概念，页是磁盘读取的最小单位，每个也买你innodb默认的大小是16kb，可以通过设置修改其大小。
   而系统盘的磁盘块没有这么大，因此innodb每次申请清理磁盘空间都会若干个磁盘地址，来达到每个分页16kb。innodb读取会以页为基本单位，在查询中每页的每条数据都有对应的定位，这样会减少io的次数，提高查询的效率
   B树可以让系统高效的查询到系统对应的磁盘块，为了描述B树。首先是以keyvalues的方式存储，对应表的主键，data为记录的数据的数据，对于不同的记录，key的值互不相同。
   B树每个节点最多有m个节点，每个节点至少有m/2个子孩子。因为除了根节点，每个必须有2个子孩子。
   每一个叶子节点必须含一层，且不含有关键字的信息。
   每一个非叶子节点都至少包含n个节点。关键字升序排列，
   
   每一个节点占用一个内存空间，一个内存空间中，有两个升序排序，key和三个指向树根节点的point，point存储的是子节点所在的磁盘块地址，两个key划分为三个地址，三个point对应的数据范围，以根节点为例，key为17 35，p1指针的范围为小于17 p2的范围为17-35，p3的指针范围为大于35
   模拟查找key的过程：
   

总共经历了三次磁盘IO，和三次内存查找操作，由于key内部是一个有序的结构，可以利用二分法提高查询效率，而且三次磁盘IO，操作会影响整个Btree的执行效率，缩减了节点个数。使每一次的磁盘IO内存数据都发挥了作用，从而提升查询效率。

6.2什么是B+树索引？

B+树索引是在B_树索引的一种优化，使其适合实现外存储索引结构，InnoDB存储引擎就是实现其索引结构，
从B树结构可以看出，每个值不仅含有key，还包含对应的数据data，每页存储空间是有限制的，如果叶子节点数据比较大，会导致每个叶子节点所存储的数据变小，数据量很大时会使树的深度增加，增加了磁盘的IO次数，使查询的速度降低，而B+树，数据记录节点都是按照键值大小顺序存放到一个叶子节点上，而非叶子节点只存储对应的data，这样可以大大的增加每个节点存储的key值数量，降低B+树的高度，数据量较大的情况下B+一般信息，经历三次磁盘IO就能读取到数据。
B树与B+树的区别：
B+树，非叶子节点只能存储键值信息。
所有叶子节点之间都有一个链指针。
数据存储在叶子节点中。

6.3B树有哪些索引

主键索引，非主键索引
主键索引：主键索引的叶子节点存储的数据是整行数据的具体数据，在INnnoDB中，主键索引又称聚集索引。
非主键索引：叶子节点存储的数据为整行数据的主键，键值是索引，在InnoDB中，非主键索引又称辅助索引。