JAVA面经整理(MYSQL篇)

JAVA面经整理(MYSQL篇)"/>

JAVA面经整理(MYSQL篇)

索引:

索引是帮助MYSQL高效获取数据的排好序的数据结构

1)假设现在进行查询数据，select * from user where userID=89

2)没有索引是一行一行从MYSQL进行查询的，还有就是数据的记录都是存储在MYSQL磁盘上面的，比如说插入数据的时候是向磁盘上面的某一个点位去存放，当去尝试插入第二条记录的时候并不是挨着点(第一条记录)位去存放的，磁盘写数据是一个磁道一个磁道写的，如果是过了好几天才插入第二条记录，一张表的所有记录是可能随机分布在磁盘上的，在磁盘上并不是连续的，所以会产生随机IO；

3)一次读取一条记录，一次IO，如果表中的数据非常多，就需要进行多次IO；

4)二叉树，key是主键值，value是数据所在的物理磁盘的地址；

5)红黑树也是二叉平衡树，一个节点只能存放一条记录，高度也非常高，当数据量高的时候，高的高度也是比较高，也是不可控的，所以可以在一个页里面，存储更多的数据让高度变得更低，B+树的叶子节点存储着所有的索引记录，非叶子节点就是一些冗余索引，来辅助B+数据的增删改查，但是B树叶子节点和叶子节点之间没有使用指针链接，MYSQL在进行查找的时候会加载一个目录页中的数据，然后使用二分法快速找到下一个数据的文件地址；

6)出现一个索引，那么旁边就会增加一个叶子结点的地址，假设使用bigInt来存储，是8个字节，指针是占据着6个字节，那么一个页中大概存放的数据就是16KB/8+6=1170条记录，但是叶子节点可能存放的数据量比较少，一张表大概一行记录大概是1KB，所以叶子节点大概一可以存放16KB，所以B+树可以存放的数据载荷的个数就是1170*1170*16条数据，此时数据的高度就是3，最多经过三次的磁盘IO就可以找到最终想要的数据，所以千万级别的数据，如果要是走索引，那速度是非常快的，相比于磁盘遍历要扫描1000W次，但是使用到了索引，此时查询的速度就会非常快，B+树还支持范围查询，但是B树只能优先遍历，B树没有冗余索引；

7)在同等条件下，假设现在要存放2000万条记录使用B树，那么此时的树的高度，一个数据载荷是1KB，一个页数据只能存放16条索引数据，所以计算树的高度16^K=2000W，K>3，MYSIM存储引擎有三个文件，.frm是表结构文件，.myd是数据文件，.MYI是索引文件，但是innodb也是存储了两个文件，.frm是表结构文件，.ibd是索引+数据

二分折半查找算法找到30位于15和56之间，然后中间存放的是下一行的地址

8)哈希索引:不支持范围查询，大于小于

为啥要一定使用自增主键？

1)创建主键，默认采用主键索引充当聚簇索引，MYSQL就不会判断唯一索引，生成6个字节的隐藏索引，这样子可以减少MYSQL的工作

2)如果使用UUID，使用UUID的ASCILL码的值来进行比较，如果说有的UUID的前面很长串字符都相等，唯独后面的不相等，那么此时比较的效率就非常低下，但是整数比较效率很高，防止聚簇索引和非聚簇索引的空间非常大，显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

3)整形数据占用的空间小，要使用UUID做主键要解决固态硬盘存储空间的问题

4)防止造成页分裂

为什么非主键索引叶子节点存放的是主键值呢？

首先是为了节省内存空间，其次是为了一致性，如果主键索引和非主键索引都是放的是数据的完整记录，那么再进行数据更新的时候，一致性更新的成本就会非常高，更新值得话，都更新成功才算成功，这样子就会非常麻烦；

联合索引:假设此时根据age字段查询select * from user where age=30，此时会发现age字段不是有序的，此时就不能利用到索引有序的特性，非叶子节点的辅助索引就是叶子结点的第一条记录

Explain执行:

explains中的rows:查询出来的行数

explain执行计划+show warning会展现出MYSQL得优化思路，可以看看MYSQL得优化思路

select_type:SQL语句执行的一种类型，在select后面 from前面查询出来的表是子查询，from后面要是还有SQL语句就是衍生查询，他们都会产生临时表，primary是复杂查询，通常这种查询要关联好几种查询，如果后面的table字段是derived，那么代表着这条SQL语句是从衍生表中查询出来的，后面的derived+数字，代表着是从拿一张表进行衍生过来的，id越大，越先执行，要是id相等的记录，那么实际的执行顺序就是谁在上面谁先执行

1)system:和查询一个常量一样效率非常高，只有一行记录，是const一种特殊情况；

2)const:唯一索引或者是主键索引和常数进行比较的时候，所以表最多有一个匹配行，读取1次，速度比较快，system是const的特例，表里只有一条元组匹配时为system；

用于primarykey或uniquekey的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快，system是const的特例，表里只有一条元组匹配时为system；

3)eq_ref:使用主键或者是唯一索引进行关联查询，去查询记录的时候直接使用主键ID去关联就可以了，最多只会返回一条符合条件的记录，这可能是在const之外最好的连接类型了，简单的select查询不会出现这种type

4)ref:查询条件没使用主键索引或者是唯一索引，使用非唯一二级索引查询，并且记录有多条，使用普通索引或者是唯一索引的前缀，或者是联合索引的一部分，相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行

简单的select查询，name是非唯一索引

5)range:范围扫描通常出现在in()，between，>，<，>=等操作中，使用一个索引来检索给定范围的行，虽然使用到了索引，但是要在索引树向后查找；

6)index:扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些，查询的字段全部在索引中，如果使用覆盖索引(查询的所有字段在二级索引和在主键索引中)，优先使用二级辅助索引，因为主键索引扫描的数据量比较大，但是如果要查询的数据不在二级索引中都有而在主键索引中都有，那么此时走主键索引，而不走二级索引，从而保证避免回表查询，遍历索引效率也很低；

7)All:就是扫描主键索引的叶子节点，从头开始找扫描聚簇索引的所有叶子节点；

注意select name，age from user和select name age from user where id=1，操作索引的区别

All和Index的区别:通常Index是二级索引扫描，效率相对来说比较高一些，All是主键索引从头开始扫描，因为遍历的数据太大，扫描的数据量更多，所以效率最低；

而说用到索引时从根节点开始向下折半查找来确定某一个值，这样效率才高，索引一次可能只是找几条记录，几次折半查找搞定了，虽然有可能都使用到了索引，但是使用ref和使用index对于索引的操作是不同的，index是遍历索引的叶子节点，ref是通过二分法来查询要找的数据；

possible_key:可能用到的索引

key:真正用到的索引 

ref:这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有:const常量字段名

rows:这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数;

key-len:使用到的索引的长度，通常对联合索引有效，如果字段允许是null，需要使用到1个字节来记录null，索引的最大长度是768字节，当字符串长度过长的时候，MYSQL会做一个类似于最左前缀索引的处理，将左半部分的字符提取出来做索引；

1)Using index:mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有usingindex；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

2)Usingwhere:使用where语句来处理结果，并且查询的列未被索引覆盖，没有使用到索引，如图下面的name字段就没有索引

3)Using index condition:查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

4)Usingtemporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化

a)使用distinct查询:需要使用临时表进行去重，先把查询所有结果加载到内存里面，然后简称一张临时表进行去重，想要进行优化，直接在去重字段上加上覆盖索引，因为索引树本身是有序的，再扫描索引树的时候，因为数据有序，所以直接可以去重；

5)文件排序:Using fileSort，使用到文件排序

6)Selecttablesoptimizedaway:使用某些聚合函数比如max，min来访问存在索引的某个字段

1)在索引树的非叶子节点里面都没有这么一个东西，left(name，3)是无法走索引的，把字符串截取三个字符在索引树里面进行查找根本就查找不到对应的字符串，无法进行比较，如果那索引的一部分来查找数据，是没有顺序的，因为根本无法定位，如果前三个字符相同呢？，根本无法通过二分法来定位到对应的数据，最终还有可能找不到最终的数据；

2)可能有时候可能全表扫描比走索引扫描更好，MYSQL会进行一个评估，因为有时候查询二级索引的时候本来表中就没有几条记录，这个时候还要通过二级索引来进行回表查询，还要回表，此时还不如全表扫描走得快，MYSQL会选择最小成本，范围查询数据量太大，可以将大的查询范围拆分成小范围；

3)在第二个字段中如果是范围查询，那么第三个字段就不是有序的，第三个字段因为不是有序的，所以就不走索引；

4)不等于结果集太多了，基本不走索引，也有可能走索引；

5)%是因为不知道要跳过多少字符，%后面的字符串还是无序的，但是XXX%在索引树是有序的，用的是最左前缀的那一段字符串，本身就是有序的；

6)or和in有可能走索引也有可能不走索引，in条数不要太多，in条数太多不走索引；

SQL执行流程:

1)连接器:管理连接和权限校验，用户密码是否正确，连接的方式有很多JDBC，mysql -uroot敲命令的过程中，MYSQL客户端和服务器建立连接 

1.1)根据IP地址和password和用户名，根据用户名去系统User表查询可以查询到IP地址和密码，先进行校验IP地址是否可以连接MYSQL服务器，还需要校验密码，校验成功以后，客户端和服务器建立连接；

1.2)MYSQL服务器会给当前用户开辟一段会话存储空间Session，这个空间把用户的权限以及用户的信息全都缓存到这块开辟的会话空间内存里面，以后每一次客户端执行DML语句，都会判断权限；