一、Mysql相关面试问题

 

1、mysql数据结构(B+Tree)（理解透彻）

 1）、什么是索引（Index）？

​        本质是帮助MySQL高效获取数据的数据结构，MySql中主要应用的索引数据结构为B+Tree。

 2）、索引结构类型有哪些？

​         Mysql存储引擎主要有MySIAM、InnoDB。而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如B+Tree索引，哈希索引，全文索引等等

 3）、mysql数据和索引存储位置？

​         MySIAM的索引和数据分两个文件进行存储：MYI文件存储索引(B+树)、MYD文件存储数据 

​         InnoDB的索引和数据存储在一个文件中：ibd文件（B+树）
    SHOW VARIABLES LIKE 'innodb_page_size'
    SHOW GLOBAL STATUS LIKE 'innodb_page_size'

 4）、存储数据结构？

​                   MySIAM、InnoDB两种存储引擎都是基于B+树数据结构存储表数据的。不同之处是：

​                   MySIAM的B+树只存储了索引key值，真正的数据存储在别的地方 

​                   InnoDB 存储引擎中的B+树既存储了索引也存储了数据        

​                   B+Tree结构示例图

​                       

  5）、节点数据项存储的是什么？

​            InnoDB：

​                对于主键索引（聚集索引）：非叶子结点存储的是ID值，叶子结点存储的为完整的数据

​                对于非主键索引（非聚集索引）：非叶子结点存储的是索引列的值，叶子结点存储的为主键ID值

​             MySIAM：非叶子节点存储的是索引列的值，叶子结点存储的为真实数据所在的地址。

  6）、为什么使用B+tree

​             由于数据存储于物理磁盘，所以要尽量减少从磁盘IO数据的次数，有效手段有：

​                    1、磁盘局部预读原理，预读的大小通常为Page的倍数；

​                    2、选取合适的数据结构存储数据，从磁盘IO次数越小越好，如BTree，渐进复杂度为O(h)=O(logdN)O(h)=O(logdN)，一般实际应用中，出度d 

​                   是非常大的数字，通常超过100，因此树的高度h非常小（通常不超过3层可容纳千万级数据）。所以，B+tree作为mysq数据库的数据结构搜索效率是很高的

  7）、B+tree与B-tree联系和区别

​     （1）、B+tree是B-tree的变体；

​     （2）、在B-tree的基础上增加了叶子结点间的顺序访问指针，B+Tree提高了顺序访问的性能；

​     （3）、B+tree非叶子结点只存储索引值，叶子结点存储真实数据，B-tree所有结点上都存储数据；

​           所以B+tree的高度更低，数据查找性能也更加稳定

  8）、聚集索引、非聚集索引

​            聚集索引：又叫主键索引，有个特点是数据库表行中数据的物理存储顺序与数据的逻辑顺序相同，一张表中只允许存在一个聚集索引，对于mysql来说一般就是主键， 若无主键则为表中第一个非空的唯一索引，还是没有，就采用InnoDB存储引擎为每行数据内置的ROWID 作为聚集索引 ；聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据。聚集索引的叶子节点称为数据页，聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

​           非聚集索引：又叫辅助索引、二级索引。是指除了聚集索引外的其他索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。数据的逻辑顺序可能相邻，但是数据的实际存储物理地址可能相差十万八千里 ；非聚集索引的叶子节点存储主键

  9）、覆盖索引

select  id   from table where phone = 1800000000;
(age,name,phone）  

select   sex,name,phone,id   from table where age = 23;

​       这个概念就是指select的数据列只用从索引中就能够取得，不必从聚集索引中的叶子结点数据项中读取，换句话说查询列要被所使用的索引覆盖。 索引是高效找到行的一 个方法，当能通过检索 索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。在mysql中只能使用BTree索引做覆盖索引、Hash索引不行 如果实现了覆盖索引，在explain的Extra列可以看到**“Using index”**的信息

 10）、查询过程

​          1）、对于聚集索引（主键索引）来说，也就是通过主键查询，一次查询就能查询到具体的行数据信息；

​           2）、对于非聚集索引来说（唯一索引、普通索引、全文索引）

​                   如果需要查询的数据列在索引中，如A+B联合索引，根据A去查询B，则通过一次查询就能直接拿到索引上的数据，也就是覆盖索引现象；

​              如果需要查询的数据不在索引中，则需要先去普通索引树中进行第一次查找得到行数据的主键值，然后通过主键值去 主键索引树中第二次搜索得到真实数据，这种需要二次查询的现象叫做回表查询。

详细解释：            

​              从普通索引无法直接定位行记录，那普通索引的查询过程是怎么样的呢？通常情况下，需要扫码两遍索引树。

例如：select * from t where phone='18809098989'; 是如何执行的呢？

​               

 11）、B+Tree性质

​             （1）.我们知道IO次数取决于B+树的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

​              （2）.当B+树的数据项是复合的数据结构，也就是联合索引比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性原则。

 12）、B+Tree  IO数据查找示意图

select name

select  name,age

select  name,age,phone

select  phone,age,name

select  name,phone 

 2、mysql优化
 1）、优化SQL语句层面（理解透彻）
​              1）、尽量避免使用select  *；

​              2）、规范sql语句大小写，sql是有缓存的，避免每次都需要解析；

​              3）、使用exsits代替in，要更高效（在外表大的时用in效率更快，内表大用exists更快。）；

​              4）、mysql sql where 和 from解析执行过程从右至左，基于这个规则，from后面能过滤掉更多数据的基础表放后面，where后面能过滤掉更多数据的查询条件放后面；

​                    FROM、ON、JOIN、WHERE、GROUPBY、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT

​              6）、合理使用索引  

1、为合适的列添加索引(主键、唯一索引、组合索引、普通索引)；
    1)、索引字段不宜过大
    2)、查询频繁字段建索引（查询频繁业务字段，关联字段）
    3)、区分度要高
    4)、索引不是越多越好     
2、尽量建立联合索引，也省空间成本；   a,b,c  （a    a and b    a and b and c    a  AND C）  

    b   b and c   c                a and c      b and a 
3、尽量使用覆盖索引；  a,b,c      SELECT C  FROM TABLE WHERE A= '' AND B = '';
4、避免以下会使索引失效的操作
    1）、使用is not null不走索引
    2）、各种负向查询not ，not in， not like ，<> ,!= ,!> ,!<  不会使用索引
    3）、like将%放左边不走索引
    4）、查询条件的数据类型做了隐式转换(比如varchar类型字段用int去查)
    5）、使用in或union代替or，or除了两侧都是索引才会走索引，其他不会走索引
    6）、尽量保持索引列干净，不在索引列上使用函数转换、运算
    7）、联合索引要遵循最左匹配原则.
            如建立联合索引（A,B,C）,查询顺序如下：
        ABC会走索引，AB会走索引，A也会走索引，但是不能断开，如|BC|CB|B|C都不会走索引
    8）、使用比较运算或between会使联合索引从使用比较运算的下一个索引处断开

​             7）、explain命令进行sql慢查询排查        

​                  对于执行查询很慢的sql，通常对sql语句执行explain命令进行分析，具体信息如下：

​                  1. id //select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，id相同，执行顺序从上至下 //id值越大，优先级越高，越先执行

​                  2. select_type //查询类型 SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT

​                  3. table   //正在访问哪个表

​                  4. partitions //匹配的分区

​                  5. type //访问的类型  效率从快到慢：    const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

​                  6. possible_keys //显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到

​                  7. key    //实际使用到的索引，如果为NULL，则没有使用索引

​                  8. key_len //表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

​                  9. ref   //显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

​                 10. rows //根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数，这个数量越小越好

​                  11. filtered //查询的表行占表的百分比

​                  12. Extra //包含不适合在其它列中显示但十分重要的额外信息

 2）、优化架构方面（了解）
​         在数据达到一定量级以后，需要对数据库从主从 、分库分表数据分片方面进行优化：

​                 读写分离：主节点写，从节点读    sharding-jdbc

​                  分库：根据业务或者其他维度把数据存放到不同数据库

​                  分表：

​                  1、水平分表：字段都一样，分多张表存放不同时间范围或不同维度的数据，如实时数据表、历史数据                                                                    表。

​                  2、垂直分表：将不同字段放在多张表，使用外键关联。

​                  常用分库分表中间件：阿里的Cobar及开源社区基于Cobar维护的Mycat等。

 3、mysql事务（理解透彻）
 1）、事务有哪些特性？
​            原子性：事务中所有操作要么全部提交成功，要么全部失败回滚，不能出现一部分失败，一部分成功的现象；

​            一致性：指在事务的执行前后保持数据库的一致性；

​            隔离性：一个事务所做的修改在最终提交之前，对其他事务是不可见的；

​            永久性：一旦事务提交，它所做的修改将会永久保存到数据库中，及时系统发生崩溃，事务执行结果也不会丢失；

 2）、哪几种事务隔离级别，会出现的问题？

 3）、脏读、不可重复读、幻读
​            脏读：一个事物读到了另一个事务尚未提交的数据，不符合事务的隔离性。

​           不可重复读：同一个事务中针对同一行记录两次读出来的结果不一样，原因就是第二次读到了其他事务修改提交的数据。

​            幻读：同一个事务中针对同一范围内的数据两次读出来的结果不一样，原因就是第二次读到了其他事务新增提交的数据。

 4）、mysql和oracle默认事务隔离级别
​            mysql默认隔离级别：repeatable-read，但是一般会设置为read-committed，因为在实际业务中常常是：一个事务中需要读到别的事务提交修改的数据。

​            oraclel默认隔离级别：read-committed

 4、mysql其它问题
 1）、MyISAM与 Innodb 两种存储引擎的区别?
​          InnoDB  支持事务和外键，MyISAM不支持

​          InnoDB  支持行锁，MyISAM只支持表锁

​          InnoDB  的真实数据和索引存储在同一个文件中，而MyISAM存储在两个文件中

​          InnoDB  5.6之前不支持全文索引，而 MyISAM 支持。

 2）、乐观锁与悲观锁
​      乐观锁：

​              思想为：乐观的认为本次事务操作数据不会有别的事务干扰，操作数据前不进行加锁，只是预先记录版本号，真正修改数据时再进行比对，

​               如果版本号没变则修改数据，版本号变了则表明别的事务在本次事务过程中修改了数据，本次事务不修改数据。

​                   具体步骤：

​                   1、先查出版本号：select status,version from t_goods where id=#{id} 

​                   2、真正修改数据时进行版本号比对修改：update t_goods set status=2,version=version+1  where id=#{id} and version=#{version};    

​        悲观锁：

​                思想为：悲观的认为本次事务一定会有别的事务干扰，操作数据前必须先加锁

​                常见实现方式为 for update加行锁： select ... from table where id = #{id} for update，这里注意，由于mysql锁是建立在索引上面的，所以查询条件必须用主键索引或者是唯一索引，否则会造成表锁。

 3）、char和vachar区别
​             char列长度固定，为创建表时声明的长度，长度值范围是1到255，当char值未填满指定长度时，其他空间会用空格进行填充，检索CHAR值时需删除尾随空格。

​             vachar长度为可变的，实际使用多少空间就占多少空间。

 4）、使用varchar（255）的字段建索引可以吗？
​          理论上是可以的，拿Innodb和utf编码场景来说，索引长度极限为 767字节，utf8 编码时1个字符占3个字节，varchar 类型能建立索引的最大长度为

​          767/3 = 255，这也是为什么varchar一般设置最大长度为255的原因之一，但是实际业务场景中一般不会建如此大的索引，影响检索效率。