MySQL面试题整合

1. MySQL索引的底层数据结构是？

1. 为什么要设计索引？
   答：加快数据的查询效率

2. 设计索引的时候，使用什么数据结构，为什么？
   答：B+树
   问：为什么？
   哈希表也能存储，但是
   
   补充：扰动函数，让高位尽可能参与哈希值的运算，让数据更加散列
   
   二叉树和红黑树也能存储，但是
   缺点：深度太深，会频繁地在磁盘和内存间io，影响读取效率

B树也可以存储，但是


一个磁盘块的空间有限，data的存在会浪费空间，所以我们只用叶子节点来存数据，这样就构成了B+树。
B+树：
非叶子节点只负责搜索，这样存储的数据会更多，叶子节点负责存储，索引字段尽可能短，这样能存储更多的数据到B+树上。

3. 聚簇索引和非聚簇索引有什么区别？
   答：聚簇索引：Innodb使用的索引，数据和索引放在一起的
   非聚簇索引：MyIsam使用的索引，数据和索引没有放在一起

2. ACID

2.1. 什么是ACID

2.2. MySQL的四个隔离级别

2.3. 脏读，不可重复读，幻读

2.4. 隔离级别对应的解决

3. 索引的种类

聚簇索引和非聚簇索引
聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

4. Mysql 中 MyISAM 和 InnoDB 的区别有哪些

1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
2. InnoDB 支持外键，而 MyISAM 不支持。
3. InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的数据文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
   聚簇索引的叶节点就是数据节点，而非聚簇索引的叶节点仍然是索引节点，并保留一个链接指向对应数据块。
4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

5. 索引失效的场景

1. 对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引

   alter table student add index my_index(name, age)   // name左边的列， age 右边的列                                                              
   
   select * from student where name = 'aaa'     // 会用到索引
   
   select * from student where age = 18          //  不会使用索引
   

2. 对于使用 like 查询， 查询如果是 ‘%aaa’ 不会使用索引，而 ‘aaa%’ 会使用到索引。

   select * from student where name like ‘aaa%’ // 会用到索引

   select * from student where name like '%aaa'        或者   '_aaa'   //  不会使用索引
   

3. 如果条件中有 or， 有条件没有使用索引，即使其中有条件带索引也不会使用，换言之， 就是要求使用的所有字段，都必须单独使用时能使用索引。

4. 如果列类型是字符串，那么一定要在条件中使用引号引用起来，否则不使用索引。

5. 如果mysql认为全表扫面要比使用索引快，则不使用索引。

   如：表里只有一条数据。

6. 数据库三范式

第一范式：第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项
第二范式：第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式要求每一行都要有唯一标识存在，这个唯一属性列被称为主关键字或主键、主码。实体的属性完全依赖于主关键字。
第三范式：第三范式是在第二范式的基础上建立起来的。第三范式指：属性不依赖于其他非主属性。

举例说明：
现有一张表：

7. MySQL优化

1. 选取最适用的字段属性，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
2. 使用连接（JOIN）来代替子查询(Sub-Queries)
3. 使用联合(UNION)来代替手动创建的临时表
4. 事务，关闭事务自动提交，改为手动提交
5. 使用外键
6. 使用索引
7. 优化的查询语句，绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用（引出索引失效的情况）

8. count(1)和count(*)的区别

count(*) 和 count(1)和count(列名)区别

执行效果上：

1. count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
2. count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
3. count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：

1. 列名为主键，count(列名)会比count(1)快
2. 列名不为主键，count(1)会比count(列名)快
3. 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
4. 如果有主键，则 select count（主键）的执行效率是最优的
5. 如果表只有一个字段，则 select count（*）最优。

9. B+树与B树区别

区别有两点：

1. B+树中只有叶子节点会带有指向记录的指针（ROWID），而B树则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。
2. B+树中所有叶子节点都是通过指针连接在一起，而B树不会。

B+树的优点：

1. 非叶子节点不会带上ROWID，这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。

2. 叶子节点之间通过指针来连接，范围扫描将十分简单，而对于B树来说，则需要在叶子节点和内部节点不停的往返移动。

B树的优点：

对于在内部节点的数据，可直接得到，不必根据叶子节点来定位。

10. MySQL三种log日志及作用

MySQL三种log日志及作用

11. 理解MVCC

12. mysql回表查询

12.1. 什么是回表查询

通俗的讲就是，如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。