Mysql学习笔记

一、索引的概述

1.为什么使用索引

在海量数据中进行查询某条记录的场景是经常发生的，如何提升查询性能，就跟要查询的数据字段是否有索引有关系。如果字段加了索引，那么查询的性能就会非常快！

• 索引为什么快？
• 索引到底是什么？
• 使用索引需要注意什么？

2.索引是什么

查字典的方式？“树”shu–通过目录来查，能够快速定位到目标数据所在的页码。

没有使用索引的时候，数据的查询需要进行多次IO读写，这样的性能较差，这样的性能较差——全表扫描的过程。

疑问：

• 索引存放的位置？
• 索引的分类及如何创建？
• 索引使用了哪种数据结构？各种数据结构的查询性能分析。

3.索引存放的位置

对于mac系统在/usr/local/mysql文件夹中，对于win系统c:/programdata/mysql（隐藏文件夹）

• InnoDB存储引擎的表：将索引和数据存放在同一个文件里。*.ibd
• MyISAM存储引擎的表：索引和数据分开两个文件来存储。索引：*.MYI； 数据： *.MYD

二、索引使用的数据结构

1.线性表

线性的维护数据的顺序。

对于线性表来说，有两种数据结构来支撑：

• 线性顺序表：相邻两个数据的逻辑关系和物理存放位置是相同的。（数组）

• 线性链式表：相邻两个数据的逻辑关系和物理存放位置没有关系。数据是有先后的逻辑关系，但是数据的物理存储位置并不连续。

  • 单向链表：能够通过当前节点找到下一个节点的位置，以此来维护链表的逻辑关系。

    节点结构：数据内容+下一个数据的指针

  • 双向链表：能够通过当前节点找到上一个或下一个节点的位置，双向都可找。

    节点结构：上一个数据的指针+数据内容+下一个数据的指针

顺序表和链式表区别：

数组：进行数据的查询性能（可以通过数组的索引/下标）：时间复杂度（比较次数）/空间复杂度（算法需要使用多少个变量空间））。

数组的查询性能非常好：时间复杂度是O(1)。但是增删性能是非常差的。

链表：查询性能非常差：时间复杂度是O(n)。增删性能是非常好的。

2.栈（电梯）和队列（排队）

• 顺序栈、链栈

• 顺序队列、链式队列

3.串 String

定长串（String)、动态串(StringBuilder、StringBuffer)

疑问：

• String、StringBuilder、StringBuffer的底层实现（char型数组存储）
• StringBuilder、StringBuffer区别是什么。

String对象一旦创建，其值是不能修改的，如果要修改，会重新开辟内存空间来存储修改之后的对象，即修改了String的引用。因为 String 的底层是用数组来存值的，数组长度不可改变这一特性导致了上述问题。如果我们在实际开发过程中需要对某个字符串进行频繁的修改，使用 String 就会造成内存空间的浪费

• 应该怎样解决这个问题呢？

  ‌StringBuffer。

StringBuffer

‌StringBuffer 和 String 类似，底层也是用一个数组来存储字符串的值，并且数组的默认长度为 16，即一个空的 StringBuffer 对象，数组长度为 16。实例化一个 StringBuffer 对象即创建了一个大小为 16 个字符的字符串缓冲区。但是当我们调用有参构造函数创建一个 StringBuffer 对象时，数组长度就不再是 16 了，而是根据当前对象的值来决定数组的长度，数组的长度为“当前对象的值的长度+16”。所以一个 StringBuffer 创建完成之后，有 16 个字符的空间可以对其值进行修改。如果修改的值范围超出了 16 个字符，会先检查StringBuffer 对象的原 char 数组的容量能不能装下新的字符串，如果装不下则会对 char 数组进行扩容。

• 那StringBuffer是怎样进行扩容的呢？

  扩容的逻辑就是创建一个新的 char 数组，将现有容量扩大一倍再加上2，如果还是不够大则直接等于需要的容量大小。扩容完成之后，将原数组的内容复制到新数组，最后将指针指向新的 char 数组。

StringBuilder

StringBuilder 和 StringBuffer 拥有同一个父类 AbstractStringBuilder，同时实现的接口也是完全一样，都实现了java.io.Serializable, CharSequence 两个接口。

• 那它俩有什么区别呢？

  最大的区别在于 StringBuffer 对几乎所有的方法都实现了同步，线程比较安全，在多线程系统中可以保证数据同步；而StringBuilder 没有实现同步，线程不安全，在多线程系统中不能使用 StringBuilder。

StringBuffer和StringBuilder 的使用场景

当需要考虑线程安全的场景下使用 StringBuffer，如果不需要考虑线程安全，追求效率的场景下可以使用 StringBuilder。

4.数组和广义表

广义表：更加灵活的多维数组，可以在不同的元素中创建不同的维度的数组。

5.树

查找树的查找性能是明显比线性表的性能要好。

1）多叉树：非二叉树

2）二叉树：一个节点最多只能有2个子节点。可以是 0、1、2个子节点。

3）二叉查找树：二叉查找树的查找性能是ok的，查询性能跟树的高度有关，树的高度又跟插入的数据的顺序有关。

特点：根节点的数值比所有左子树的节点的数值大，比右子树的节点的数值小。这样的规律同样满足他的所有子树。

4）平衡二叉树（AVL树）（理想概念的树）：二叉查找树不能非常智能的维护树的高度，因此二叉查找树在某些情况下性能是不ok的，因此平衡二叉树出现了。

• 特点：平衡二叉树中的树及其所有子树都应该满足：左子树和右子树的深度差不超过1。如果平衡二叉树不满足这个特点，那么平衡二叉树要进行自己旋转，如何自己旋转：

  左旋：树右边过深，将右边中间节点作为根节点，往左边旋转，即可平衡。

  

  右旋：树左边过深，将左边中间节点作为根节点，往右边旋转，即可平衡。

  

  双向（先左后右、先右后左）：旋转一次无法平衡的情况。看左右哪边过深，执行先左后右或先右后左旋转。

  

5）红黑树（平衡二叉树的一种体现）

平衡二叉树为了维护树的平衡，在一旦不满足平衡的情况下就要进行自旋，但是自旋会造成一定的系统开销。因此红黑树在自旋造成的系统开销和减少查询次数之间做了权衡。因此红黑树有时候并不是一颗平衡二叉树。

特点：

​ 1.节点是红色或者黑色。

​ 2.根节点是黑色。

​ 3.不可能有连在一起的红色节点。

​ 4.每个红色节点的两个子节点都是黑色。

​ 5.叶子节点都是黑色（nil-黑色的空节点）。

红黑树已经是在查询性能上得到了优化，但索引依然没有使用红黑树作为数据结构来存储数据，因为红黑树在每一层上存放的数据内容是有限的，导致数据量一大，树的深度就变得非常大，查询性能就会非常差。因此索引没有使用红黑树。

6）B树

B树允许一个节点存放多个数据，这样可以使更小的树的深度来存放更多的数据。但是，B树的一个节点中到底能存放多少个数据，决定了树的深度

通过数值计算，B树的一个节点最多只能存放15个数据，因此B树依然不能满足海量数据的查询优化。

7）B+树

B+树的特点：

​ 1.非叶子节点冗余了叶子节点中的键。

​ 2.叶子节点是从小到大、从左到右排列的。

​ 3.叶子节点之间提供了指针，提高了区间访问的性能。（缓存）

​ 4.只有叶子节点存放数据，非叶子节点是不存放数据的，只存放键。

8）哈希表

使用哈希表来存储数据的性能是最快的，查找时间复杂度O(1)，但是不支持范围查找（区间访问）

三、InnoDB和MyISAM的区别

InnoDB和MyISAM都是数据库表的存储引擎。在互联网公司，或者追求查询性能的场景下，都会使用InnoDB作为表的存储引擎。

为什么？

1.InnoDB引擎

把索引和数据存放在一个文件中，通过找到索引后就能直接在索引树上的叶子节点中获得完整的数据。——聚簇索引

可以实现行锁/表锁

2.MyISAM存储引擎

把索引和数据存放在两个文件中，查找到索引后还要去另一个文件中找数据，性能会慢一些——非聚簇索引。

除此之外，MyISAM天然支持表锁，而且支持全文索引。

四、索引常见的面试题

问题1：为什么非主键索引的叶子节点存放的数据是主键值

1.数据冗余，虽然提升了查询性能，但是需要更多的空间来存放冗余的数据，造成空间的浪费。

2.维护麻烦，一个地方修改了数据，需要在多棵索引树上修改。

问题2：为什么InnoDB表必须创建主键

如果没有主键，MySQL优化器会给一个虚拟的主键，于是普通索引会使用这个虚拟主键——也会造成性能的开销。为了性能考虑和设计初衷，创建表的时候就应该创建主键。

问题3：为什么使用主键时推荐使用整型的自增主键

1）为什么使用整型

主键索引树，树里的叶子节点和非叶子节点的键存放的是主键的值，而且这颗树是一个二叉查找树。数据的存放是有大小顺序的。

• 整型：大小顺序是很好比较的。
• 字符串：字符串的自然顺序的比较是要进行一次编码成为数值后再进行比较的。（字符串的自然顺序 A < Z）

2）为什么自增

如果不用自增，那么主键索引树会使用更多的自旋次数来保证索引树的叶子节点中的数据是从小到大，从左到右排列，因此性能必然比使用了自增主键的性能要差！

五、联合索引和最左前缀法则

1.联合索引的特点

使用一个索引来实现多个表中字段的索引效果。

2.联合索引是如何存储的

3.最左前缀法则

最左前缀法则是表示一条sql语句在联合索引中有没有走索引（命中索引/不会全表扫描）

六、SQL优化

SQL优化的目的是为了SQL语句能够具备优秀的查询性能，实现这样的目的有很多途径：

• 工程优化如何实现：数据库标准、表的结构标准、字段的标准、创建索引
• SQL语句的优化：当前SQL语句有没有命中索引。

1.工程优化如何实现

参考 《MySQL军规升级版》

2.Explain执行计划——SQL优化神器

得知道当前系统里有哪些SQL是慢SQL（查询性能超过1s的sql），再通过Explain工具，可以对当前SQL语句的性能进行判断——为什么慢，怎么解决。

要想知道哪些SQL是慢SQL，有两种方式：

• 开启本地MySQL的慢日志查询日志。

• 阿里云提供的RDS（第三方部署的MySQL服务器），提供了查询慢SQL的功能。

  explain select * from t_user where name like 'wangjie%'

通过在SQL语句前面加上explain关键字，执行后并不会真正的执行sql语句本身，而是通过explain工具来分析当前这条SQL语句的性能细节：比如是什么样的查询类型、可能用到的索引及实际用到的索引，和一些额外的信息。

3.MySQL的内部优化器

在SQL查询开始之前，MySQL内部优化器会进行一次自我优化，让这一次的查询性能尽可能的好。

当前执行的SQL：

explain select * from t_user where id = 1;
show WARNINGS;

内部优化器优化后的效果：

/* select#1 */ select '1' AS `id`,'wangjie' AS `name`,'25' AS `age` from `study`.`t_user` where true

4.select_type列

关闭MySQL对衍生表的合并优化：

set session optimizer_switch='derived_merge=off';

执行了这样的计划：

• derived：

  第一条执行的sql是from后面的字查询，该子查询只要在from后面，就会生成一张衍生表，因此他的查询类型：derived。

• subquery：

  在select之后from之前的子查询。

• primary：

  最外部的select。

• simple：

  不包含子查询的简单查询。

• union:

  使用union进行的联合查询。

5.table列

当前查询正在查哪张表。

6.type列

type列可以直观的判断出当前的sql语句的性能。type里的取值和性能的优劣顺序如下：

null > system > const > eq_ref > ref > range > index > all

对于SQL优化来说，要尽量保证type列的值是属于range级别级以上。

• null

  性能是最好的，一般在使用了聚合函数操作索引列，结果直接从索引树获取即可，因此性能是最好的。

  explain select min(id) from t_user 

  

• system

  很少见。直接和一条记录进行匹配。

• const

  使用主键索引或唯一索引和常量进行比较，这种性能非常好。

  explain select * from t_user where id = 1

  

• eq_ref

  在进行多表连接查询时。如果查询条件是使用了主键进行比较，那么当前查询类型是eq_ref。

  explain select * from tb_book_author a left join tb_book b on a.book_id = b.id

• ref

  • 简单查询：

    如果查询条件是普通列索引，那么类型ref

    explain select * from tb_book where name = 'book1'

  • 复杂查询：

    在进行连接查询时，如果查询条件是普通列索引，那么类型ref

    explain select * from tb_book_author a left join tb_book b on a.book_id = b.id

• range

  使用索引进行范围查找

  explain select * from t_user where id > 1

• index

  查询没有进行条件判断。但是所有的数据都可以直接从索引树上获取。(book表中所有列都有索引)

  explain select * from tb_book

• all

  没有走索引，进行了全表扫描。

  explain select * from tb_author

7.id列

在多个select中，id越大越先执行，如果id相同。上面先执行。

8.possible keys列

这一次的查询可能会用到的索引。也就是说mysql内部优化器会进行判断，如果这一次查询走索引的性能比全表扫描的性能要差，那么内部优化器就让此次查询进行全表扫描——这样的判断依据可以通过trance工具来查看。

explain select * from employees where name like 'custome%'

这条sql走索引查询的行数是500多万，那么总的数据行数也就500多万，走索引还需要回表查询，因此直接进行全表扫描性能更快。

9.key列

实际该sql语句使用的索引

10.rows列

该sql语句可能要查询的数据条数

11.key_len列

键的长度，通过这一列可以让我们知道当前命中了联合索引中的哪几列。

name长度是74，也就是当看到key_len是74，表示使用了联合索引中的name列

计算规则：

- 字符串
1.char(n)：n字节长度
2.varchar(n)：2字节存储字符串长度，如果是utf-8,则长度3n+2

- 数值类型
1.tinyint：1字节
2.smallint：2字节
3.int：4字节
4.bigint：8字节

- 时间类型
1.date：3字节
2.timestamp：4字节
3.datetime：8字节

如果字段允许NULL，需要1字节记录是否为NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引（前缀索引）

12.extra列

extra列提供了额外的信息，是能够帮助我们判断当前sql是否使用了覆盖索引、文件排序、使用了索引进行查询条件等等的信息。

• Using index：使用了覆盖索引。

  所谓的覆盖索引，指的是当前查询的所有数据字段都是索引列，这就意味着可以直接从索引列中获取数据，而不需要回表查询。使用覆盖索引进行性能优化这种手段是之后sql优化经常要用到的。

  explain select book_id,author_id from tb_book_author where book_id = 1 -- 覆盖索引
  explain select * from tb_book_author where book_id = 1 -- 没有使用覆盖索引

• Using where

  使用了普通索引列做查询条件。

  explain select * from tb_author where name > 'a'

• Using index condition

  查询结果没有使用覆盖索引，建议可以使用覆盖索引来优化。

  explain select * from tb_author where book_id > 1

• Using temporary

  在非索引列上进行去重操作就需要使用一张临时表来实现，性能是非常差的。

  explain select distinct name from tb_author

• Using filesort

  使用文件排序：会使用磁盘+内存的方式进行文件排序，会涉及到两个概念：单路排序、双路排序。

  explain select * from tb_author order by name

• select tables optimized away

  直接在索引列上进行聚合函数的操作，没有进行任何的表操作

  explain select min(id) from tb_book

七、Trance工具

在执行计划中，我们发现有的sql会走索引，有的sql即使明确使用了索引也不会走索引。这是因为mysql的内部优化器认为走索引的性能比不走索引全表扫描的性能要差，因此mysql内部优化器选择了使用全表扫描。依据来自于trace工具的结论。

set session optimizer_trace="enabled=on",end_markers_in_json=on; -- 会话级别临时开启，只在当前会话生效。
select * from t_student where std_name > 'a' order by age; -- 执行查询
SELECT * FROM information_schema.OPTIMIZER_TRACE; -- 获得trace的分析结果

{
  "steps": [
    {
      "join_preparation": {  -- 第一阶段：SQL准备阶段，格式化sql
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t_student`.`id` AS `id`,`t_student`.`std_name` AS `std_name`,`t_student`.`age` AS `age`,`t_student`.`class_id` AS `class_id`,`t_student`.`gmt_create` AS `gmt_create` from `t_student` where (`t_student`.`std_name` > 'a') order by `t_student`.`age`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {  -- 第二阶段：SQL优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": {  -- 条件处理
              "condition": "WHERE",
              "original_condition": "(`t_student`.`std_name` > 'a')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`t_student`.`std_name` > 'a')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`t_student`.`std_name` > 'a')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`t_student`.`std_name` > 'a')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [  -- 表依赖详情
              {
                "table": "`t_student`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [  -- 预估表的访问成本
              {
                "table": "`t_student`",
                "range_analysis": {
                  "table_scan": {   -- 全表扫描
                    "rows": 100300,  -- 行数
                    "cost": 20351  -- 查询消耗
                  } /* table_scan */,
                  "potential_range_indexes": [  -- 查询可能使用的索引
                    {
                      "index": "PRIMARY",  -- 主键索引
                      "usable": false,  -- 未使用
                      "cause": "not_applicable"  -- 原因：不适合
                    },
                    {
                      "index": "idx_std_age",  -- age索引
                      "usable": false,  -- 未使用
                      "cause": "not_applicable"  -- 原因：不适合
                    },
                    {
                      "index": "idx_std_name_age_class",  -- stdname,age,class的组合索引
                      "usable": true,  -- 使用
                      "key_parts": [
                        "std_name",
                        "age",
                        "class_id",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {  -- group 用到的索引
                    "chosen": false,  -- 未使用
                    "cause": "not_group_by_or_distinct"  -- 原因：未使用group by 或者 distinct
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {   -- 分析各个索引使用成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_std_name_age_class",
                        "ranges": [
                          "a < std_name"  -- 索引使用范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,  -- 使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,
                        "index_only": false,  -- 是否使用覆盖索引
                        "rows": 50150,  -- 索引扫描行数
                        "cost": 60181,   -- 索引使用成本
                        "chosen": false,  -- 是否选择该索引：否
                        "cause": "cost"  -- 原因：消耗
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {  -- 分析使用索引合并的成本
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [  -- 分析出的执行计划
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t_student`",
                "best_access_path": {  -- 最优访问路径
                  "considered_access_paths": [  --分析出的最终访问路径
                    {
                      "rows_to_scan": 100300,
                      "access_type": "scan",  -- 访问类型：为scan，全表扫描
                      "resulting_rows": 100300,
                      "cost": 20349,
                      "chosen": true,  -- 确定选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 100300,
                "cost_for_plan": 20349,
                "sort_cost": 100300,
                "new_cost_for_plan": 120649,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {   -- 为查询的表添加条件
              "original_condition": "(`t_student`.`std_name` > 'a')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [    -- 添加条件结果
                {
                  "table": "`t_student`",
                  "attached": "(`t_student`.`std_name` > 'a')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {   -- order by 处理
              "clause": "ORDER BY",
              "original_clause": "`t_student`.`age`",
              "items": [
                {
                  "item": "`t_student`.`age`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`t_student`.`age`"
            } /* clause_processing */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {    -- 重构索引处理顺序
              "clause": "ORDER BY",
              "steps": [
              ] /* steps */,
              "index_order_summary": {
                "table": "`t_student`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "unknown",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "refine_plan": [
              {
                "table": "`t_student`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {    -- 第三阶段：SQL执行阶段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`t_student`",
                "field": "age"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 100000,
              "examined_rows": 100000,
              "number_of_tmp_files": 14,
              "sort_buffer_size": 262016,
              "sort_mode": "<sort_key, packed_additional_fields>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

八、SQL优化实战

1.Order by 优化

在Order by中，如果排序会造成文件排序（在磁盘中完成排序，这样的性能会比较差），那么就说明sql没有命中索引，怎么解决？可以使用最左前缀法则，让排序遵循最左前缀法则，避免文件排序。

EXPLAIN select * from student where name = 'wangjie' order by age,phone;

优化手段：

• 如果排序的字段创建了联合索引，那么尽量在业务不冲突的情况下，遵循最左前缀法则来写排序语句
• 如果文件排序没办法避免，那么尽量想办法使用 覆盖索引（查询的列存在于联合索引中，避免回表查询）。 all -> index

2.Group by 优化

group by 的原理是先排序后分组，因此对于group by的优化参考 order by

3.文件排序的原理

在执行文件排序的时候，会把查询的数据的大小与系统变量：max_length_for_sort_data的大小进行比较（默认是1024字节），如果比系统变量小，那么执行单路排序，反之则执行双路排序。

• 单路排序

  把所有的数据扔到sort_buffer内存缓冲区中，进行排序，然后结束

• 双路排序

  取数据的排序字段和主键字段，在内存缓冲区中排序完成后，将主键字段做一次回表查询，获取完整数据。

4.分页优化

explain select * from student order by name limit 1000000,10
-- 通过先进行覆盖索引的查找，然后再使用join做连接查询获取所有数据。这样比全表扫描要快
explain select * from student a inner join (select id from student order by name limit 1000000,10) b on a.id = b.id;

5.join查询优化

explain select * from t1 inner join t2 on t1.a = t2.a
-- t1 有1万行记录（大表）
-- t2 只有100行记录（小表）
-- 在join查询中，如果关联字段建立了索引，mysql就会使用 NLJ 算法，去找小表（数据量比较小的表）作为驱动表，先从驱动表中读一行数据，然后拿这一行数据去被驱动表（数据量比较大的表）中做查询。这样的大表和小表是由mysql内部优化器来决定的，跟sql语句中表的书写顺序无关。  —NLJ算法
-- 如果没有索引，会创建一个join buffer内存缓冲区，把小表数据存起来（为什么不存大表，因为缓冲区大小限制，内存数据消耗性能的考虑），用内存缓冲区中的100行记录去和大表中的1万行记录进行比较，比较的过程依然是在内存中进行的。索引join buffer起到了提高join效率的效果。 ——BNLJ算法
-- NLJ（nested loop join）：嵌套循环join
-- BNLJ（block nested loop join）:块嵌套循环join

• NLJ（嵌套循环算法）：如果关联字段使用了索引，mysql会对小表做全表扫描，用小表的数据去和大表的数据去做索引字段的关联查询。（type：ref)
• BNLJ（块嵌套循环算法）：如果关联字段没有使用索引，mysql会提供一个join buffer缓冲区，先把小表放到缓冲区，然后全表扫描大表，把大表的数据和缓冲区中的小表数据在内存中进行匹配。

结论：如果使用join查询，那么join的两个表的关联字段一定要创建索引，而且字段的长度类型一定要是一致的（这里在表设计的时候就需要考虑到），否则索引会失效，会使用BNLJ算法，全表扫描。

6.in和exsits优化

在sql中，如果A表是大表，B表是小表，那么使用in会更加合适，反之使用exists。

• in：B的数据量 < A的数据量

  select * from A where id in (select id from B)
  # 相当于：
  for(select id from B){ //B的数据量少，所以循环次数少。
  	select * from A where A.id = b.id
  }

• exists：B的数据量 > A的数据量

  select * from A where exists (select 1 from B where B.id = A.id)	true / false
  # 相当于
  for(select * from A){
  	select * from B where B.id = A.id
  }

7.count优化

对于count的优化应该是架构层面的优化，因为count的统计是在一个产品会经常出现，而且每个用户都会访问，所以对于访问频率过高的数据建议维护在缓存中。

九、锁的定义和分类

1.锁的定义

锁是用来解决多个任务（线程、进程）在并发访问同一共享资源时带来的数据安全问题。虽然使用锁解决了数据安全问题，但是会带来性能的影响，频繁使用锁的程序的性能是必然很差的。

对于数据库管理软件MySQL来说，必然会遇到任务的并发访问。那么MySQL是如何在数据安全和性能上做权衡的呢？—— MVCC设计思想。

2.锁的分类

1）从性能上划分：乐观锁和悲观锁

• 悲观锁：悲观的认为当前的并发是非常严重的，所以在任何时候操作都是互斥的。保证了线程的安全，但牺牲了并发性。 ——总有刁民要害朕。

• 乐观锁：乐观的认为当前的并发并不严重，因此对于读的情况，大家都可以进行，但是对于写的情况，再进行上锁。以CAS自旋锁为例，在某种情况下性能是ok的，但是频繁自旋会消耗很大的资源。——天网恢恢疏而不漏

2）从数据的操作细粒度上划分：表锁和行锁

• 表锁：对整张表上锁
• 行锁：对表中的某一行上锁

3）从数据库的操作类型上划分：读锁和写锁

这两种锁都是属于悲观锁

• 读锁（共享锁）：对于同一行数据进行“读”来说，是可以同时进行，但是不可以写。
• 写锁（排他锁）：在上了写锁之后，及释放写锁之前，在整个过程中是不能进行任何的其他并发操作（其他任务的读和写诗都不能进行的）。

3.表锁（实际开发中不要上表锁）

对整张表进行上锁。MyISAM存储引擎是天然支持表锁的，也就是说在MyISAM的存储引擎的表中如果出现并发的情况，将会出现表锁的效果。MySAM不支持事务，InnoDB支持事务。

在InnoDB中上一下表锁

# 对一张表上读锁/写锁格式：
lock table 表名 read/write;
# 例子
lock table tb_book read;
# 查看当前会话对所有表的上锁情况
show open tables;
# 释放当前会话的所有锁
unlock tables;

读锁：其他任务可以进行读，但是不能进行写

写锁：其他任务不能进行读和写。

4.行锁

MyISAM只支持表锁，但不支持行锁，InnoDB可以支持行锁。

在并发事务里，每个事务的增删改的操作相当于是加了行锁。

上行锁的方式：

• update tb_book set name=’java2010’ where id = 8; 对id是8的这行数据上了行锁。
• select * from tb_book where id = 5 for update; 对id是5的这行数据上了行锁。

5.死锁

所谓死锁，就是开启的锁没办法关闭，导致资源的访问因为无法获得锁而处于阻塞状态。

演示：事务A和事务B相互持有对方需要的锁而不释放，造成死锁的情况。

6.间隙锁

行锁只能对某一行上锁，如果想对某一范围上锁，就可以使用间隙锁。间隙锁给的条件 where id>13 and id<19,会对13和19所处的间隙进行上锁（12 和 20 都会被锁住）。

7.锁监控

show engine innodb status \g

十、MVCC设计思想

MVCC全称是Multi-Version Concurrency Control（多版本并发控制），是一种并发控制的方法，通过维护一个数据的多个版本，减少读写操作的冲突。

如果没有MVCC，想要实现同一条数据的并发读写，还要保证数据的安全性，就需要操作数据的时候加读锁和写锁，这样就降低了数据库的并发性能。

有了MVCC，就相当于把同一份数据生成了多个版本，在操作的开始各生成一个快照，读写操作互不影响。无需加锁，也实现数据的安全性和事务的隔离性。

事务的四大特性中隔离性就是基于MVCC实现的。

说MVCC的实现原理之前，先说一下事务的隔离级别。

1.事务的特性

• 原子性：一个事务是一个最小的操作单位（原子），多条sql语句在一个事务中要么同时成功，要么同时失败。
• 一致性：事务提交之前和回滚之后的数据是一致的。
• 持久性：事务一旦提交，对数据的影响是持久的。
• 隔离性：多个事务在并发访问下，提供了一套隔离机制，不同的隔离级别会有不同的并发效果。

2.事务的隔离级别

• 读未提交（read uncommitted）：在一个事务中读取到另一个事务还没有提交的数据——脏读。
• 读已提交（read committed）：已经解决了脏读问题，在一个事务中只会读取另一个事务已提交的数据。这种情况会出现不可重复读的问题。在事务中重复读数据，数据的内容是不一样的。
• 可重复读（repeatable read）（MySQL默认）：在一个事务中每次读取的数据都是一致的，不会出现脏读和不可重复读的问题。会出现虚读的问题。
• 串行化（serializable）:串行化的隔离级别直接不允许事务的并发发生，不存在任何的并发性。相当于锁表，性能非常差，一般都不考虑。

脏读、不可重复读、幻读

• 脏读：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

• 不可重复读：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间穿插了一个事务更新的原有的数据。

• 幻读：在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

  

  幻读解决方案：通过上行锁来解决幻读问题

  

3.MVCC实现原理

MySQL在读和写的操作中，对读的性能做了并发性的保障，让所有的读都是快照读，对于写的时候，进行版本控制，如果真实数据的版本比快照版本要新，那么写之前就要进行版本（快照）更新，这样就可以既能够提高读的并发性，又能够保证写的数据安全。

当前读和快照读

当前读： 读取数据的最新版本，并对数据进行加锁。

例如：insert、update、delete、select for update、 select lock in share mode。

快照读： 读取数据的历史版本，不对数据加锁。

例如：select

MVCC是基于Undo Log、隐藏字段、Read View（读视图）实现的。

隐藏字段

先说一下MySQL的隐藏字段，当我们创建一张表时，InnoDB引擎会增加2个隐藏字段。

DB_TRX_ID（最近一次提交事务的ID）：修改表数据时，都会提交事务，每个事务都有一个唯一的ID，这个字段就记录了最近一次提交事务的ID。

DB_ROLL_PTR（上个版本的地址）：修改表数据时，旧版本的数据都会被记录到Undo Log日志中，每个版本的数据都有一个版本地址，这个字段记录的就是上个版本的地址。

版本链

当我们第一次往用户表插入一条记录时，表数据和隐藏字段的值是下面这样的：

insert into user (name,age) values ('一灯',1);

事务ID（DB_TRX_ID）是1，上个版本地址（DB_ROLL_PTR）是null。

第二次提交事务，把用户年龄加1。

update user set age=age+1 where id=1;

事务ID变成2，上个版本地址指向Undo Log中的记录。

第三次提交事务，再把用户年龄加1。

update user set age=age+1 where id=1;

事务ID变成3，上个版本地址指向Undo Log中事务ID为2的记录。

这样表记录和Undo Log历史数据就组成了一个版本链。

Read View（读视图）

在事务中，执行SQL查询，就会生成一个读视图，是用来保证数据的可见性，即读到Undo Log中哪个版本的数据。

快照读一般是读取的历史版本的读视图，当前图会生成一个最新版本的读视图。

读视图是基于下面几个字段实现的：

m_ids ：当前系统中活跃的事务ID集合，即未提交的事务。

min_trx_id ：m_ids中最小的ID

max_trx_id ：下一个要分配的事务ID

creator_trx_id: 当前事务ID

读视图决定当前事务能读到哪个版本的数据，从表记录到Undo Log历史数据的版本链，依次匹配，满足哪个版本的匹配规则，就能读到哪个版本的数据，一旦匹配成功就不再往下匹配。

数据可见性规则：

1. DB_TRX_ID = creator_trx_id
   如果这个版本数据的事务ID等于当前事务ID，表示数据记录的最后一次操作的事务就是当前事务，当前读视图可以读到这个版本的数据。
2. DB_TRX_ID < min_trx_id
   如果这个版本数据的事务ID小于所有活跃事务ID，表示这个版本的数据不再被事务使用，即事务已提交，当前读视图可以读到这个版本的数据。
3. DB_TRX_ID >= max_trx_id
   如果这个版本数据的事务ID大于等于下一个要分配的事务ID，表示有新事务更新了这个版本的数据，这种情况下，当前读视图不可以读到这个版本的数据。
4. min_trx_id <= DB_TRX_ID < max_trx_id
   如果这个版本数据的事务ID在当前系统中活跃的事务ID集合（m_ids）里面，表示这个版本的数据被其他事务更新过，当前读视图不可以读到这个版本的数据。
   如果这个版本数据的事务ID不在当前系统中活跃的事务ID集合（m_ids）里面（代表事务已经提交了），且这个版本数据的事务ID小于最大的事务ID，表示此次是在其他事务提交后创建的读视图，当前读视图可以读到这个版本的数据。

不同隔离级别下可见性分析

在不同的事务隔离级别下，生成读视图的规则不同：

• READ COMMITTED（读已提交） ：在事务中每一次执行快照读时都生成一个读视图，每个读视图中四个字段的值都是不同的。
• REPEATABLE READ（可重复读）：仅在事务中第一次执行快照读时生成读视图，后续复用这个读视图。

十一、MySQL三大日志

日志是 mysql 数据库的重要组成部分，记录着数据库运行期间各种状态信息。mysql日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发，我们重点需要关注的是二进制日志( binlog )和事务日志(包括redo log 和 undo log )，本文接下来会详细介绍这三种日志。

1.binlog

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog 是 mysql的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。

• 逻辑日志：可以简单理解为记录的就是sql语句 。
• 物理日志：mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更 。

binlog 是通过追加的方式进行写入的，可以通过max_binlog_size 参数设置每个 binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

binlog使用场景

在实际应用中， binlog 的主要使用场景有两个，分别是 主从复制 和 数据恢复 。

1. 主从复制 ：在 Master 端开启 binlog ，然后将 binlog发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据一致。
2. 数据恢复 ：通过使用 mysqlbinlog 工具来恢复数据。

binlog刷盘时机

对于 InnoDB 存储引擎而言，只有在事务提交时才会记录biglog ，此时记录还在内存中，那么 biglog是什么时候刷到磁盘中的呢？mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：

• 0：不去强制要求，由系统自行判断何时写入磁盘；
• 1：每次 commit 的时候都要将 binlog 写入磁盘；
• N：每N个事务，才会将 binlog 写入磁盘。

从上面可以看出， sync_binlog 最安全的是设置是 1 ，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

binlog日志格式

binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED。

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT ， MySQL 5.7.7 之后，默认值是 ROW。日志格式通过 binlog-format 指定。

• STATMENT：基于SQL 语句的复制( statement-based replication, SBR )，每一条会修改数据的sql语句会记录到binlog 中 。
  • 优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO , 从而提高了性能；
  • 缺点：在某些情况下会导致主从数据不一致，比如执行sysdate() 、 slepp() 等 。
• ROW：基于行的复制(row-based replication, RBR )，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了 。
  • 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题 ；
  • 缺点：会产生大量的日志，尤其是alter table 的时候会让日志暴涨
• MIXED：基于STATMENT 和 ROW 两种模式的混合复制(mixed-based replication, MBR )，一般的复制使用STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog

2.redo log

为什么需要redo log

我们都知道，事务的四大特性里面有一个是 持久性 ，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态 。

那么 mysql是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

1. 因为 Innodb 是以 页 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
2. 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

因此 mysql 设计了 redo log ， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

redo log基本概念

redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo logfile)。mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志，再写磁盘 的技术就是 MySQL里经常说到的 WAL(Write-Ahead Logging) 技术。在计算机操作系统中，用户空间( user space )下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间( kernel space )缓冲区( OS Buffer )。因此， redo log buffer 写入 redo logfile 实际上是先写入 OS Buffer ，然后再通过系统调用 fsync() 将其刷到 redo log file中，过程如下：

mysql 支持三种将 redo log buffer 写入 redo log file 的时机，可以通过 innodb_flush_log_at_trx_commit 参数配置，各参数值含义如下：

redo log记录形式

前面说过， redo log 实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此 redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。如下图：

同时我们很容易得知， 在innodb中，既有redo log 需要刷盘，还有 数据页 也需要刷盘， redo log存在的意义主要就是降低对 数据页 刷盘的要求 。在上图中， write pos 表示 redo log 当前记录的 LSN (逻辑序列号)位置， check point 表示 数据页更改记录 刷盘后对应 redo log 所处的 LSN(逻辑序列号)位置。write pos 到 check point 之间的部分是 redo log 空着的部分，用于记录新的记录；check point 到 write pos 之间是 redo log 待落盘的数据页更改记录。当 write pos追上check point 时，会先推动 check point 向前移动，空出位置再记录新的日志。启动 innodb 的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为 redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如 binlog )要快很多。重启innodb 时，首先会检查磁盘中数据页的 LSN ，如果数据页的LSN 小于日志中的 LSN ，则会从 checkpoint 开始恢复。还有一种情况，在宕机前正处于checkpoint 的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的 LSN 大于日志中的 LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

redo log与binlog区别

由 binlog 和 redo log 的区别可知：binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。但只有 redo log 也不行，因为 redo log 是 InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要 binlog和 redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

3.undo log

数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。实际上， 原子性 底层就是通过 undo log 实现的。undo log主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。同时， undo log 也是 MVCC(多版本并发控制)实现的关键。