Java学习之Mysql面试问题整理
Mysql常见存储引擎
命令：SHOW ENGINES;
Mysql版本5.5以后默认引擎是 InnoDB，5.5版本以前的是 MyISAM
InnoDB和MyISAM的区别
1、InnoDB支持事务，MyISAM不支持。对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2、InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3、InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大；MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4、Innodb不支持全文索引（Mysql版本5.6以后支持），而MyISAM支持全文索引。
5、InnoDB支持表、行级锁，而MyISAM支持表级锁。
6、InnoDB表必须有唯一索引，而Myisam可以没有。
7、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI。（Innodb：frm是表定义文件，ibd是数据文件；Myisam：frm是表定义文件，myd是数据文件，myi是索引文件）
总结：
1、如果数据需要支持事务则只能选InnoDB，不需要支持的话两者都可以；
2、因为MyISAM只支持表级锁，所以如果对数据更新操作多的话应该选择支持行级锁的InnoDB。如果对数据以读写为主，则应选择MyISAM。
Mysql索引结构
1、什么是索引
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
优点：
（1）提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因。
（2）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
（3）在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。
缺点:
（1）创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
（2）索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
（3）虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
2、索引有哪些类型
1、数据结构上分：二叉树、红黑树、平衡二叉树、B树、B+树、Hash索引
索引是对数据库表中一列或多列的值进行处理后的一种结构，使用索引可快速访问数据库表中的特定信息。Hash索引的结构和HashMap相类似，键值key通过Hash映射找到桶bucket。在这里桶（bucket）指的是一个能存储一条或多条记录的存储单位。一个桶的结构包含了一个内存指针数组，桶中的每行数据都会指向下一行，形成链表结构，当遇到 Hash 冲突时，会在桶中进行键值的查找。
MySql为什么不用hash索引。
1、在确定值查询的速度上hash快，只需要计算出hash值就能定位数据；
2、hash索引不支持范围查询，因为相邻的数据hash值可能会相差很远，很难放在一起查询。
3、不支持模糊查询，因为要计算hash值
4、不支持排序，因为值的hash值的大小顺序不一定能保持一致
5、不支持联合索引，还是因为计算hash值，只能一起计算，不能灵活区分单个索引的hash值
MySql用B+数而不用数据类型类似的二叉树、红黑树、平衡二叉树
普通的二叉树因为有从左到右依次升序的特征，查询的复杂度为O(logn)，相对于hash索引，支持范围的有效查询，但是极端情况下的二叉树会变成线性链表，就导致查询复杂度变成O(N)，所以二叉树也不能作为mysql的底层数据索引结构。
红黑树是一种弱的平衡二叉树，通过左旋右旋保持基本的平衡状态，拥有不错的平均查找效率，不会存在O(N)的情况，但是红黑树存在右倾的可能性，数据量大的时候，右倾的查找效率也会很低，所以也不考虑。
AVL是一种绝对平衡的二叉树，不会有红黑树的右倾缺点，可以实现范围查找，数据排序。但是AVL的缺点是每个节点只存放一个数据，如果使用AVL树作为myql底层索引数据结构的话，磁盘IO的次数会很多，效率仍然很低。磁盘IO的特点是读取1b的数据和读取1kb的数据消耗时间一致，根据这个特点，后面有了B树和B+树。
B树相对于AVL平衡二叉树，每个节点存放了很多数据，减少了磁盘IO的次数，支持范围查找，时间复杂度：h*logN，就是树高和节点的数量。但是B树的缺点是节点存放数据，造成树高会很高在大量数据的情况下。
B+树相对B树，非叶子节点存储的是索引和指针，能够存储很多，只有叶子节点存放的是数据，这样就保证了树的高度不高，减少磁盘的IO次数，在加上叶子节点是链表链接起来的，这样查找效率就提升上来了。所以B+树作为Myql的底层的索引数据结构非常的合理。
B+树能放多少数据（Mysql中B+树高一般不超过3层）
1、数据持久化存储磁盘里，磁盘的最小单元是扇区，一个扇区的大小是 512个字节
2、文件系统的最小单元是块，一个块的大小是 4KB
3、InnoDB存储引擎的最小存储单元称之为页，一个页的大小是16KB（16384）
假设一行数据为1K，则一页可以存16条数据。（一个叶子节点）
假设主键为integer类型，大小为8个字节，指针大小在innoDB为6个字节，则一页能放16384/(8+6)=1170 个指针。（非叶子节点）
所以2层B+数为： 1170*16 = 18720
3层B+数为：1170*1170*16 = 21902400
2、物理存储上分：
- 聚簇索引（Clustered Index，也称聚类索引、簇集索引、聚簇索引）：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。
- 非聚簇索引（Nonclustered Index，也称非聚类索引、非簇集索引。创建的索引，如复合索引、前缀索引、唯一索引，都是属于非聚簇索引）：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
3、逻辑角度上分：
1、普通索引
普通索引是最基本的索引，它没有任何限制，值可以为空；仅加速查询。
2、唯一索引
唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。简单来说：唯一索引是加速查询 + 列值唯一（可以有null）。
3、主键索引
主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。简单来说：主键索引是加速查询 + 列值唯一（不可以有null）+ 表中只有一个。
4、组合索引
组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
可以说：组合索引是多列值组成的一个索引，专门用于组合搜索，其效率大于索引合并。
5、全文索引
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。
Mysql有哪些锁
为什么要加锁？
当多个事务并发操作同一批数据的时候，如果不加锁，就无法保证事务的隔离性，最后导致数据错乱。加锁是为了保证并发操作下数据的正确性。
锁的分类有哪些？
- 按锁的粒度可分为：表锁、页面锁、行锁、记录锁、间隙锁、临键锁
- 按锁的属性可分为：共享锁、排它锁
- 按加锁机制可分为：乐观锁、悲观锁
表锁：
MyISAM和InnoDB引擎均支持表锁。
优点：开销小，加锁快，不会出现死锁。
缺点：锁定力度大，发生锁冲突概率高，并发度最低。
加锁方式：
# 对user表加读锁
lock table user read;
什么情况下需要用到表锁？
当需要更新表中的大部分数据
事务涉及到多张表，业务逻辑复杂，加表锁可以避免死锁。
页面锁：
优点：开销和加锁速度介于表锁和行锁之间。
缺点：会出现死锁，锁定粒度介于表锁和行锁之间，并发度一般。
目前只有BDB引擎支持页面锁，应用场景较少。
行锁：
只有InnoDB引擎支持行锁，另外锁是加在索引上面的。
优点： 开销大，加锁慢；会出现死锁。
缺点：锁定粒度小，发生锁冲突的概率低，并发度高。
另外记录锁、间隙锁、临键锁均属于行锁。
记录锁（Record Locks）：
即对某条记录加锁。
# 对id=1的用户加锁
update user set age=age+1 where id=1;
间隙锁（Gap Locks）：
即对某个范围加锁，但是不包含范围的临界数据。
# 对id大于1并且小于10的用户加锁
update user set age=age+1 where id>1 and id<10;
上面SQL的加锁范围是(1,10)。
临键锁（Next-Key Locks）：
由记录锁和间隙锁组成，既包含记录本身又包含范围，左开右闭区间。
# 对id大于1并且小于等于10的用户加锁
update user set age=age+1 where id>1 and id<=10;
共享锁（又称读锁、S锁）：
作用：防止其他事务修改当前数据。
加锁方式：
在select语句末尾加上lock in share mode关键字。
# 对id=1的用户加读锁
select * from user where id=1 lock in share mode;
排他锁（又称写锁、X锁）：
作用：防止其他事务读取或者更新当前数据。
加锁方式：
在select语句末尾加上for update关键字。
# 对id=1的用户加写锁
select * from user where id=1 for update;
乐观锁：
总是假设别人不会修改当前数据，所以每次读取数据的时候都不会加锁，只是在更新数据的时候通过version判断别人是否修改过数据，Java的atomic包下的类就是使用乐观锁（CAS）实现的。
适用于读多写少的场景。
加锁方式：
读取version
select id,name,age,version from user id=1;
更新数据，判断version是否修改过。
update user set age=age+1 where id=1 and version=1;
悲观锁：
总是假设别人会修改当前数据，所以每次读取的时候，总是加锁。
适用于写多读少的场景。
加锁方式：
# 加读锁
select * from user where id=1 lock in share mode;
# 加写锁
select * from user where id=1 for update;
补充：
Update时，where中的过滤条件列，如果用索引，锁行，无法用索引，锁表。按照索引规则，如果能使用索引，锁行，不能使用索引，锁表；Insert，delete时，锁行。
Mysql死锁的产生和解决办法
一、 什么是死锁
死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等的进程称为死锁进程.
二、 死锁产生的四个必要条件（四个条件缺一个都不会死锁）
•互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放
•请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放
•不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放
•环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源
三、解决办法
1、先找DB组同事核实是否死锁，然后看能否先杀死进程，让程序先正常运行；
2、根据时间、查询日志，分析导致死锁的原因；
3、业务调整或者代码优化，避免死锁再次出现；（改表形成死锁的必要条件）
其他常见问题整理
1、什么叫回表查询
1、如果是通过主键索引来查询数据，那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。
2、如果是通过非主键索引来查询数据，那么此时需要先搜索存储主键索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。对于第二种查询方式而言，一共搜索了两棵 B+Tree，第一次搜索 B+Tree 拿到主键值后再去搜索主键索引的 B+Tree，这个过程就是所谓的回表。
不用主键索引不一定回表！如果是覆盖索引查询就不用回表。
2、什么叫覆盖索引
1、覆盖索引是一种数据查询方式，不是索引类型
2、在索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，那么就叫覆盖索引
3、查询的字段被使用到的索引树全部覆盖到
3、MySql内部结构
（1）客户端发送一条查询给服务器
（2）服务器先检查缓存，如果命中缓存，立即返回缓存结果。否则进入下一阶段
（3）然后服务器进行sql解析、预处理，再由优化器生产对应的执行计划
（4）查找到结果，存入查询缓存中并返回给客户端
连接器：我们知道由于MySQL是开源的，他有非常多种类型的客户端：navicat、mysql fromt，jdbc，SQLyog等非常丰富的客户端，这些客户端要想MySQL发起通信都必须先跟Server端建立通信链接，而建立链接的工作就是连接器完成的。
查询解析器（parser)：就是负责对SQL语句进行解析的。按照SQL语法，对我们按照SQL语法编写的SQL语句进行解析。(解析sql哪些表、哪些字段、什么条件)
查询优化器：生成执行计划，选择索引
执行器：先判断查询权限，调用存储引擎接口去执行sql。
4、MySql缓存机制
MySQL缓存机制即缓存sql 文本及缓存结果，用Key-Value形式保存再服务器内存中，如果运行相同的sql，服务器直接从缓存中去获取结果，不需要再去解析、优化、执行sql。
缓存的数据结构是hash表以SQL、数据库名和客户端协议等作为KEY。在判断命中前，MySQL不会解析SQL，而是使用SQL去查询缓存，SQL上的任何字符的不同，如空格、注释等都会导致缓存不命中，如果查询有不确定的数据，如like now()、current_date()，那么查询完成后结果都不会被缓存。
5、Mysql数据删除后索引会跟着删除吗
Mysql删除数据命令有三种：
1、drop (删除表)：删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。
drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
2、truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已。
注意:truncate 不能删除行数据,要删就要把表清空。
3、delete (逐条删除表中的数据)：delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
所以：truncate 和delete只会删除数据，drop会删除数据和数据的索引。
