MySQL

---

第一章 数据结构

索引 ----- 二叉树、平衡二叉树、b-tree、b+tree详解

1. 二叉搜索树：
   特点：左子树的键值小于根节点的键值，右子树的键值大于根节点的键值。
   缺点：二叉搜索树结构任意，甚至可能连成链表，因此效率低。
2. 平衡二叉搜索树：
   特点：在二叉搜索树的基础上，通过旋转切换根节点的方式，使左子树与右子树的高度差不超过1。
3. B-Tree（多路平衡搜索树）：
   m叉B-Tree的特点： 每个节点最多包含m个子节点；
   除根节点与叶子节点外，每个节点至少有ceil(m/2)个子节点；
   若根节点不是叶子节点，则至少有两个子节点；
   所有的叶子节点都在同一层；
   每个非叶子节点由n个key、data和n+1个子节点指针组成，其中 ceil(m/2) - 1 <= n <= m-1，
   当 n > m时，节点由中间key分裂成两个节点，且中间key上升为父节点的key。
4. B+Tree：
   所有数据记录按照键值大小顺序存放在叶子节点上，非叶子节点上只保存键值和子节点指针。
5. Mysql B+Tree:
   在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，所有叶子节点之间是一种链式环结构。
6. 红黑树：

• 每个节点要么是黑色，要么是红色。
• 根节点是黑色。
• 每个叶子节点（NIL）是黑色。
• 每个红色结点的两个子结点一定都是黑色。
• 任意一结点到每个叶子结点的路径都包含数量相同的黑结点。

第二章 SQL优化

1. 通过explain分析执行计划
   
   • id表示select子句的执行顺序，相同则表示执行的顺序从上到下，不同则值越大优先级越高。如果为NULL，表示产生临时表，需要根据SQL来判断产生的顺序。
   • select_type表示查询的类型，SIMPLE、SUBQUERY、UNION
   • table表示数据来源哪张表
   • type 访问类型
     • null 不访问任何表和索引，直接返回结果
     • system、const 将查询优化为一个常量
     • eq_ref 经过多次索引，且只返回一条数据（主键索引、唯一索引）
     • ref 经过多次索引，且返回多条数据
     • range 索引查询指定行，between、in、大于小于
     • index 遍历索引树
     • all 全盘扫描
       效率：null > system > const > eq_ref > ref > range > index > ALL（通常保证查询效率最低为range）
   • possible_keys 可能使用到的索引
   • key 实际使用的索引，NULL表示没有使用索引
   • key_len 索引字段的最大长度
   • rows 扫描的行数
   • extra 额外信息
     • Using index：覆盖索引，效率高；
     • Using where：将在存储引擎检索行后再过滤；
     • Using temporary：对查询结果排序时会产生临时表；
     • using index conditoin：查找使用了索引，但是需要回表查询数据。
2. 正在执行的效率低的SQL：

• 使用show processlist的命令查看正在运行线程的耗时；（正直执行的SQL）
• 使用show profile for query id查看SQL执行时每个线程的耗时；

3. 已执行的效率低的SQL：

• 启用慢查询日志；
• 通过trace追踪分析优化器对SQL执行计划的选择。

第三章 索引失效

1. 全值匹配 ，对索引中所有列都指定具体值；
2. 最左前缀原则，对于组合索引，查询从索引的最左前列开始，并且不跳过索引中的列；
3. 范围查询右边的列，不能使用索引；
4. 不要在索引列上进行运算操作，如计算、函数等；
5. 字符串不加单引号，造成索引失效；
6. 尽量使用覆盖索引（索引列完全包含查询列），减少select *；
7. 建议使用 union 替换 or，只有当or左右查询字段均为索引时，才会生效；
8. 以%开头的Like模糊查询，索引失效；
9. 如果MySQL评估使用索引比全表更慢，则不使用索引；
10. in走索引， not in 不走索引；
11. 在索引字段上使用not，<>，!=不会用到索引；
12. order by违反最左前缀原则、含有非索引字段，都会导致文件排序；
13. group by违反最左前缀原则、含有非索引字段，都会导致产生临时表。

第四章 Mysql查询的执行过程

1. 客户端发送一条查询给服务器；
2. 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
5. 将结果返回给客户端。

第五章 Mysql查询的执行顺序

1. from 表名 where 条件 group by having select order by limit。

第六章 索引

1. 索引是什么？
   索引是存储引擎用于快速查找记录的一种数据结构。
2. 聚簇索引是什么？
   聚簇索引不是一种单独的索引类型，而是一种数据存储方式。聚簇索引将所有的数据记录都保存在叶子节点，非叶子节点只保存键值和子节点指针。不是所有的存储引擎都支持聚簇索引，InnoDB默认选择主键作为聚簇索引，如果没有主键，则选择一个唯一且非空的索引作为聚簇索引，如果都没有，则自定义一个主键作为聚簇索引。

• 优点：把相关数据保存在一起，访问速度更快；
• 缺点：内存占用高、更新聚簇索引代价高、可能造成页分裂、二级索引（非聚簇索引）访问需要两次索引，效率低（二级索引/辅助索引的叶子节点保存的不是数据记录的指针，而是主键值，因此需要再根据主键索引找到叶子页的数据）。

3. 非聚簇索引是什么？
   非聚簇索引的索引与数据文件分开存储，索引的叶子节点保存数据记录的指针。
4. 索引的结构：

• B+Tree：适合范围查询、键前缀匹配，数据记录有序存储；
• Hash：只适合精确匹配，数据记录无序存储。

5. 索引的分类：

• 单列索引：普通索引、唯一索引、主键索引；
• 组合索引。

第七章 FAQ

1. 读写分离如何实现？
   spring维护两个数据库连接池，使用AOP对读写操作进行数据源动态切换。
2. 多表连接查询？
   内连接查看两表交集数据，左外连接查看左表全部数据和右表交集数据。
3. MyISAM与InnoDB比较？

	事务	外键	聚簇索引	select count(*) from table	锁
MyISAM	不支持	不支持	不支持	直接读取变量	表锁
InnoDB（5.5后默认选择）	支持	支持	支持	全表扫描	表锁、行锁（默认）

读多写少用MyISAM，其它都用InnoDB（InnoDB需要维护MVCC等冗余信息，查询效率不如MyISAM）。

4. 为什么使用B+Tree而不是B-Tree？

• B-Tree非叶子节点存储数据记录，增大了IO次数（Mysql IO大小固定的，一次读取一页默认16K）；
• B-Tree不支持范围查询。

5. 为什么使用B+Tree而不是红黑树？
   红黑树的深度过大，造成磁盘 IO 读写频繁。
6. 最左前缀原则的原理？
   最左前缀原则是针对使用组合索引时遵守的规范，组合索引在构建B+索引树时优先根据组合索引的最左侧字段排序，在最左侧字段有序的基础上再对第二个字段排序，以此类推。因此，查询的时候必须遵守最左前缀原则方能使用到索引。
7. mybatis是如何防止SQL注入的？
   mybatis是如何防止SQL注入的
8. 数据库分表后，如何维护ID？

• 通过修改数据库的起始ID和步长，如分3个库，则库1起始ID为0，步长为3；
• 通过redis、zk来维护一个自增ID；
• 使用UUID、雪花算法。

9. 使用连接池的优势？

• 复用连接，避免频繁创建和销毁连接产生的性能损耗；
• 控制连接，避免创建过多的连接。

10. MVCC实现原理？
    轻松理解MYSQL MVCC 实现机制
    MVCC浅析