MySQL高级

1. mysql explain是什么？有什么作用？描述下type

   EXPLAIN :模拟Mysql优化器是如何执行SQL查询语句的，分析查询语句或表结构的性能瓶颈。
   type是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到 ref。

2. mysql中有哪几种锁？表锁、行锁、页锁区别？

   • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，并发度最低；但是发生锁冲突的概率最高
   • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，并发度最高；但是发生锁冲突的概率最低
   • 页面锁：开销、加锁速度、锁定粒度、并发度都界于表锁和行锁之间；会出现死锁

3. 悲观锁 for update 乐观锁(version) 的区别

   • 悲观锁: 基于数据库层面的锁,每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block阻塞。
   • 乐观锁: 每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据。

4. 什么是索引？索引的种类有哪些，描述索引的优缺点?

   在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
   索引分为：

   ​ 普通索引(非主键)、唯一索引、聚集索引、主键(聚簇)索引、全文索引等。

   • 优点
     • 提高数据的搜索速度
     • 加快表与表之间的连接速度
     • 提高检索效率
   • 缺点
     • 需要花费时间去建立和维护索引
     • 在创建索引的时候会占用存储空间
     • 修改表中的数据时索引需要进行动态的维护

5. 设计索引的原则？(不少于4条)

   1. 选择唯一性索引
   2. 为经常需要排序、分组和联合操作的字段建立索引
   3. 为常作为查询条件的字段建立索引
   4. 限制索引的数目
   5. 尽量使用数据量少的索引
   6. 数据量小的表最好不要使用索引

6. 什么情况下索引会失效？（不少于4条）

   1. 复合索引未用左列字段;
   2. like以%开头;idea的maven在pluging出现两个clean2，
   3. 需要类型转换;
   4. where中索引列有运算;
   5. where中索引列使用了函数;

7. Btree和Hash区别

   • BTree索引是最常用的mysql数据库索引算法，它不仅可以被用在=,>,>=,<,<=和between这 些比较操作符上，还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量
   • Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引

8. Btree和B+tree的区别

   B-Tree是一种多叉平衡查找树，类似普通的二叉树。
   B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

   两者区别

   1. B-Tree的关键字，指针和数据都是存储在一起的；而B+Tree的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。所以非叶子结点中可以存储更多的索引项，进而提高搜索的效率。
   2. B-Tree中不同的叶子之间没有连在一起；B+Tree 中所有的叶子结点通过指针连接在一起。
   3. 在B-Tree中，越靠近根节点的记录查找越快，只要找到关键字即可确定记录的存在；而B+Tree中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。

9. 数据库优化方案？

   1. 优化索引，sql语句，分析慢查询
   2. 设计表的时候严格按照数据库设计规范来设计数据库
   3. 使用缓存，把经常访问并且不需要经常变化的数据放在缓存中，能够节约磁盘IO
   4. 数据量很大的时候分库分表

10. 什么是存储过程？有什么优缺点？

    存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。
    优点

    1. 存储过程是预编译过的，执行效率高。
    2. 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
    3. 安全性高，执行存储过程需要有一定权限的用户。
    4. 存储过程可以重复使用，减少数据库开发人员的工作量
       缺点
    5. 调试麻烦，但是用 PL/SQL Developer 调试可以弥补这个缺点。
    6. 移植性差。
    7. 重新编译问题，如果带有引用关系的对象发生改变时，包将需要重新编译（可设置成运行时自动编译）。
    8. 在一个程序系统中大量的使用存储过程，随着用户需求的增加会导致数据结构的变化，维护该系统很难很 麻烦、而且代价很大。

11. 什么是视图，视图有什么作用？

    • 视图也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。
    • 视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。
    1. 可以定制用户数据，聚焦特定的数据。
    2. 可以简化数据操作。
    3. 基表中的数据有一定的安全性
    4. 可以合并分离的数据，创建分区视图

12. 索引

也是一张表，该表保存了主键与索引字段，并指向实体表的记录。eg.通过首字母查字典、通过身份证号查个人信息。

索引可以大大提高MySQL的检索速度，但是会降低更新表的速度，也会占用磁盘空间的索引文件。实际应用中一般给需要经常查询的字段建立索引。

1. 主键索引：又叫聚簇索引，叶子节点存储的是一行完整的记录；
2. 非主键索引，又叫普通索引，不是主键索引的索引都叫做非主键索引，两者使用的数据结构都是B+Tree，叶子节点存储主键值，即id；

回表

先通过普通索引定位到主键值id，再通过主键索引定位到行记录。查了两次，性能更低。

如何避免回表

将需要的字段放在索引中去，查询的时候就能避免回表。

为什么mysql的索引使用B+树而不是B树呢？

B+树更适合磁盘等外部存储设备,由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。

mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。

其他知识点：
三层的B+Tree可以存储2100万条数据。
单列索引：一个索引只包含单个列。
组合索引：一个索引包含多个列。
关系数据库会自动对主键创建主键索引。