mysql面试题

mysql面试题

1.B树和B+树的区别是什么？

答：B树：一个节点中有多个元素，元素的个数的最大值等于分支数减一。左边的分支上面的数都比他小，右边的都比他大。

B+树：B+树叶子节点有指针，并且非叶子节点在叶子节点上都已一份备份。但是MySQL中的B+树是双向指针。

Mysql中的B+树：B树的叶子节点直接加了双向指针

2.Innodb中的B+树是怎么生成的？

答：Mysql中的一页是16kb，每次存取都是最少按一页进行的，可以有效地提高查询效率。在往一页里面插入数据的时候，MySQL会按照主键自增的顺序插入。这也是为什么mysql要用自增的id去插入，对于效率比较高。页目录会保存用户数据区域的每组记录的最小的主键值，方便进行查询。对于Mysql每一页都是B+树的叶子节点，而同一个表中的页都会通过指针相连接，那么我们要查询哪一页，则需要我们在上一层建立一个目录，存放我们的页地址。

3.Innodb如何支持范围查找？

答：根据我们的索引把他按照主键自增的顺序建立页，然后形成B+树，这样我们对于>主键或者<主键，我们会先找到等于主键的字段，然后根据B+树的指针，也就是页指针，前后进行遍历。

4.为什么要遵循最左前缀原则才能利用到索引？

答：当我们利用到组合索引的时候，我们会按照从左到右进行排序。即查询条件的内容应该匹配我们索引的顺序。假如索引是bcd，那么where字句里面应该有bcd（顺序无所谓），即条件里面必须有最左前缀。即给条件是bd是可以的。

5.聚簇索引和非聚簇索引

答：以Innodb和MyIsIm两种数据引擎为例。

MyISAM的B+树的叶子节点中的data域存储的是相应数据记录的地址而不是数据文件，需要先找到地址，再去取数据，这就是非聚簇索引。而Innodb树的叶节点的data域保存了完整的数据记录，这个索引的key是数据表的主键。这就是聚簇索引。

6.范围查找导致索引失效分析

答：由于条件不精确，导致需要进行多次回表操作，效率会不如全盘扫描，这个时候mysql会直接走全局扫描。所以我们的条件应该越精确越好。但是如果是覆盖索引不需要回表，那么就会走索引。

7.覆盖索引

答：即我们需要的查找的字段就是索引，这个时候就不需要回表操作。

8.索引扫描底层原理

答：当我们不加where条件进行搜索字段的时候，如果走直接的聚簇索引的全盘扫描会比较慢，因为数据大导致页面数量多（一页只能存少量的数据）。这个时候系统会走辅助索引的全盘扫描，因为辅助索引的叶节点中每页能存储更多的字段（字段不完全），这个时候走辅助节点的全盘扫描，然后找到对应的字段，再进行一次回表会更快。

9.为什么orderby会导致索引失效？

答：比如：

select * from t1 order by b,c,d

—走索引，不需要排序，回表多次

—全盘扫描，不需要回表，但是需要排序

所以mysql会选择全盘扫描，回表太慢了。所以失效与否看速度，如果：

select b from t1 order by b,c,d

这种不需要回表，他会选择走索引。

10.mysql数据类型转换需要有哪些注意的？

答：首先是字符转数字，一般的字符会转换成0，而数字对应的字符会转换成相应的数字。比如：

select 'a' = 0   //正确的
select '123' = 123 //正确的
select 'b' = 1 //错误的

但是因为涉及到一个转换，所以MySQL中所有的字符都会变成数字，索引就会失效。因此我们如果对条件中的字段进行修改操作，就会导致索引失效。

11.MyIsam和Innodb的区别？

答：

1.Innodb支持事务，但是MyIsam不支持事务。（这是mysql选择Innodb作为默认存储引擎的重要原因）

2.Innodb支持外键，而MyISAM不支持。

3.Innodb是聚集索引，MyIsam是非聚集索引。

聚簇索引的文件存放在主键索引的叶子节点上，因此Innodb必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后通过主键查询到数据。因此主键不应该过大，因为主键过大，其他索引也会很大。

而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4.Innodb不保存表的具体行数，执行select count（*）from table需要全表扫描。

而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度更快。

5.Innodb最小的锁粒度是行锁，MyISAM最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是MYSQL将默认存储引擎换成Innodb的重要原因。

12.数据库表设计时，字段你会如何选择？

答：整型 > date，time > enum char > varchar >blob ,text

选用字段长度最小，优先选用定长型、数值型字段中避免使用“ZEROFILL”。

time ： 定长运算快，节省时间、考虑时区，写sql不方便

enum ：能约束值的目的，内部用整形来存储，但与char联查时，内部要经历串与值得变化。

char：定长，考虑字符集和校对集。

varchar：不定长，要考虑字符集的转换与排序时的校对集，速度慢

text，blob：无法使用内存临时表（排序操作只能在磁盘上进行）

13.MySQL中varchar最多能存储多少数据？

答：对于varchar（M）类型的列最多可以定义65535个字节。其中的M代表该类型最多存储的字符数量。除了blob和text其他所有列所占的内存不能大于65536byte。

14.说一下事务的基本特性？

答：事务应该具有四种属性：原子性，一致性，隔离性，持久性——ACID原则。

15.事务并发可能引发什么问题？

答：

1. 脏读：读取到另一个数据未提交的数据称为脏读。
2. 不可重复读：在同一个事务中，前后两次读取的数据不一致现象
3. 幻读：事务前后读取同一个范围的数据，在事务B两次读取的过程中，事务A增加了新的数据，导致事务B读取到前一次查询没有读取到的行。幻读和不可重复读类似，但是强调的是增减。

16.简单描述一下MySQL的各种索引？

答：MySQL索引按字段特性分类可分为：主键索引、普通索引、前缀索引。

1. 主键索引
2. 唯一索引
3. 前缀索引：MySQL无法使用order by和group by做索引。

17.什么是三星索引

答：对于一个查询而言，一个三星索引可能是最好的索引。

如果查询使用三星索引，一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描，因此其响应时间通常比一个普通索引少几个数量级。

• 一个查询相关的索引行是相邻的或者至少相距足够靠近的则获得一星
• 如果索引中的数据顺序和查找顺序一致则获得二星
• 如果索引中的列包含了查询中需要的全部列则获得三星

三星索引在实际的业务中无法同时到达，一般我们认为第三颗星最重要，第一颗和第二颗星重要性差不多，根据业务情况调整这两颗星的优先度。

18.Innodb一颗B+树可以存放多少行数据？

答：约2000万行。Innodb的最小存储单元是页，16K.Innodb的所有数据文件，他的大小都是16K的整数倍。对于B+树而言，只有叶子节点存放数据，非叶子节点存放的只有索引信息和下一层节点的指针信息。

19.如何提高Insert的性能？

答：

1. 合并多条insert为一条：多条insert合并以后，日志量减少了，降低日志刷盘的数据量和频率，从而提高效率。也能减少sql语句的解析次数，减少网络传输的IO。
2. 修改参数buik_insert_buffer_size，调大批量插入的缓存
3. 设置innodb_flush_log_at_trx_commit = 0;
4. 手动使用事务：mysql默认是自动提交事务的autocommit，每插入一条数据，都会进行一次commit。为了减少创建事务的消耗，我们可以手工使用事务。即START TRANSACTION，执行多个Insert以后一起commit。

20.什么是全局锁、共享锁、排它锁？

答：全局锁就是对整个数据库实例加锁，典型应用场景就是对整个数据库进行备份。这个命令可以让整个库处于一种只读的状态。

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但是任何事务都不能对数据进行修改，直到已释放所有共享锁。

排它锁又叫写锁。若某个事务对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其加任何锁。他是悲观锁的一种实现。

21.谈一下MySQL中的死锁

答：死锁指两个或两个以上的进行在执行过程中，因为争夺资源而造成的一种相互等待的现象，若无外力作用，他们都将无法推进下去。此时系统处于死锁状态或系统产生了死锁。

如何查看死锁？

使用命令 show engine innodb status

Innodb lock Monnitoe打开锁监控

对待死锁常见的两种策略

通过innodbblockwait_timeout来设置超时时间，一直等待直到超时。

发起死锁检测，发现思索以后，主动回滚死锁中的某一个事务，让其他事务执行下去。