MySQL原理和优化

一、索引的概述

1.索引是什么？

在千万级别的数据中进行查找，速度是非常快的。这就需要借助MySQL的索引来实现。

MySQL中的索引，相当于是给数据创建了一个目录。该目录的作用，是当有查询该目录时，可以快速定位数据所在的磁盘位置而进行快速的查询。如果不走目录（索引）的话，那么就需要进行磁盘的多次io，做全表扫描，性能是非常差的。

2.索引存储在哪里？

• InnoDB引擎：

  索引是和数据存放在一个文件里的。

• MyISAM引擎：

  索引和数据分开两个文件来存储。
  

3.索引的分类及创建

• 主键索引

一张表的主键，是自带索引的效果。所以在创建表的时候建议一定要带有主键，因为有主键就有主键索引，于是用主键来查询数据是能够走索引的，速度非常快。

• 普通索引

为普通的列创建索引。

# 格式
CREATE INDEX 索引名称 ON 表名(列名)
# 示例
CREATE INDEX idx_name ON tb_index_demo1(NAME)

• 唯一索引

在普通索引的基础上，列的值是唯一的。唯一索引的性能要优于普通索引。

CREATE UNIQUE INDEX idx_unique_name ON tb_index_demo1(NAME)

• 联合索引

在一个索引中包含了表中的多个列。联合索引有很多的细节，比如最左前缀法则，及如何使用该法则来命中，这一块知识在后面介绍。

# 格式
CREATE INDEX 索引名称 ON 表名(列名1,列名2)
# 示例
CREATE INDEX idx_book_author ON tb_book_author(book_id,author_id)

• 全文索引

搜索引擎使用的就是全文索引的概念，我们不会用MySQL做全文检索，所以这个索引大家了解即可。

MySQL里只有MyISAM引擎支持全文索引。

二、数据结构

我们在上一章了解到，通过索引查数据，性能是非常快，且也得知，索引实际上是数据的目录。因此要查数据走索引，也必须得先查索引。所以，我们的问题就是为什么在查索引的时候速度是这么快的，这就跟索引使用了哪种数据结构有关系。

1. 线性表

1）顺序存储的线性表

存放数据的位置是连续的。

2）链式存储的线性表

存储数据的位置本身是不连续的，通过一条链来维护着数据的先后关系。

• 单向链表
  
• 双向链表
  

3）操作这两种线性表的性能比较

• 查找：

  • 顺序表：如果是通过下标来查找，时间复杂度是O(1)
  • 链表：查找性能是比较差的，时间复杂度是O(n)

• 增删

  • 顺序表：性能较差，要下移或上移其他元素
  • 链表：性能较好，直接找到对应位置的前后元素，调整指针域即可。

2.栈和队列

栈和队列底层都可以使用顺序结构或链式结构来维护数据。

• 栈：数据是按照先进后出的特点进行管理
• 队列：数据是按照先进先出的特点进行管理

3.数组和广义表

• 数组： 一位数组、多维数组。
• 广义表：表中可以放具体的元素，也可以放另外一张表。

广义表也可以理解成是多维数组，但是广义表比多维数组在空间上要更节约一些。

4.树

• 二叉树：每个节点最多只能有两个子节点

• 满二叉树：除叶子节点外其他的节点都有两个子节点，而且叶子节点的深度相同

• 完全二叉树：除叶子节点外，所有节点的子节点必须有左子节点

• 二叉排序树：二叉树的节点的左子节点比自己小，右子节点比自己大。

• 平衡二叉树：在二叉排序树的基础上遵循两个原则

  • 每棵子树的左子树和右子树的深度差不能超过1
  • 每棵子树也是一棵平衡二叉树

• 红黑树：

  在平衡二叉树的基础上，对于插入和删除操作做了自己的优化，目的是保证插入和删除造成的自旋的次数，和查找的性能之间做了均衡。就可以保证查找的性能和自旋的造成的开销都是较为均衡的。

平衡二叉树自旋的结果：

红黑树自旋的结果：

为什么索引不用红黑树这种数据结构，当红黑树中的数据量较大时，树的深度也会很大，于是查找性能就不ok了。

• B树：

B树，屏蔽了红黑上的每个节点存放数据过少的弊端。也就是说B树中一个节点可以存放多个数据。

此时，这棵树的深度由节点的度（一个节点中可以存放多少个数据节点）来决定。

如果节点的度比较大，那么节点中存放的数据节点的个数就比较多，那么整棵树的深度就比较浅，那么整棵树的查询性能就更好。

MySQL中一个节点中可以存放多少个数据是怎么规划的。

上面的是B树的结果，虽然节点中的度有所提升，但是还是没有达到预期的效果

注： 上面的一个物理节点默认大小为16kb = 16 * 1024 B, 而一个数据节点的大小是1032B, 所以第一层也就是一个物理节点可以存储
（16 * 1024 ） / 1032 = 15个节点

通过上图的分析。B树虽然在红黑树的基础上提高了节点的度，提升了查询到性能，但是由于节点中度过小。性能提升还没达到预期。

• B+树：

B+树在B树的基础上进行了改善，提高了节点的度，节点存放的数据节点的个数，极大的降低了树的深度。B+树的叶子节点从小到大排列，具备区间访问的性能。——索引会使用这种数据结构。

B+树只有叶子节点会存放数据，第一层可以存放 16*1024 / (8+6) = 1170个，第二层117**1170个节点

5.hash表

Hash表的查找性能是比B+树要好的，但是Hash表不支持区间访问，因此MySQL索引的数据结构不会使用Hash结构

6.图

对于数据的分析和研究层面，图的数据结构是非常重要的，也涉及到非常多的算法。这一块我们只做了解。

三、InnoDB和MyISAM的区别

之前已经看到，InnoDB把数据和索引存放在一个文件里，MyISAM把数据和文件分开存储。

1.MyISAM——非聚集索引

因为MyISAM把索引和数据分开两个文件来存储。因此在查找索引后，索引的叶子节点存放的是该条记录在另一个文件中的对应的磁盘地址。所以，MyISAM的查找性能是没有InnoDB强的。

MyISAM支持全文检索，支持表锁。在互联网应用里，因为追求查询性能，且全文检索会使用比较主流的全文检索中间件（es/solr）来做，所以MyISAM存储引擎用的比较少。

2.InnoDB——聚集索引

InnoDB会把索引和数据绑定在一起，那么通过找到索引后就能快速的找到数据，所以查询性能是比MyISAM要强很多。

四、关于索引的面试题

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

两个原因：

• 节约空间：冗余存放相同的数据在多个索引树会造成空间的浪费
• 简化修改数据时的操作：如果冗余放相同数据到多个索引树上，那么在数据进行修改时，相关的每个索引树都要修改数据，而不是只改一份。

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

因为表中的辅助（普通）索引是需要存放主键数据的，即使我们在建表的时候，忘记了创建主键，MySQL也会自动的创建一个临时主键索引，用来定位数据。

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

使用整型，而不是字符串，这样在索引这一颗排序树上比较大小性能更好。

如果不使用自增主键，那么索引叶子节点是从小到大排列，不规律的主键会造成更多索引树的自旋。从而影响了性能。

五、联合索引

1.联合索引在底层是如何存储的

一张表中的多个列，组合成一个索引。换句话说，一个索引，包含了表中的多个列。

CREATE INDEX idx_book_author ON tb_book_author(book_id,author_id)

在联合索引树上，节点中的数据节点存放的是联合索引每个列的内容

2.最左前缀法则

创建了联合索引以后，在使用sql查询时，能否命中索引，就看查询条件是否遵循了最左前缀法则。如果遵循了，就能命中索引，而不会全表扫描。

联合索引是name和age，查询条件一定要包含name，遵循左前缀，就能走索引。

更为复杂的例子：

create index idx_a_b_c on tb_tmp(a,b,c)
select * from tb_tmp where a=1 and b=2 and c=3  可以
select * from tb_tmp where a=1 and b=2   可以
select * from tb_tmp where b=2 and c=3   不可以
select * from tb_tmp where a=1 and c=3   只命中了a的索引
select * from tb_tmp where b=2 and c=3 and a=1 可以

六、SQL优化

即使我们在创建数据库表时为表创建了索引。但是在实际使用的sql语句却没有命中索引而造成了慢查询，从而导致整个接口的性能是非常差的。

1.为什么要做SQL优化

• 防止慢查询，导致接口性能较差。 接口性能1秒以内，甚至200ms以内。
• 数据库的名称不规范，表的结构不规范，导致使用时出现了问题，对生产环境造成了影响。

2.工程结果如何做优化

• 创建表时必须使用InnoDB引擎
• 创建表一定要创建主键，主键用无符号的整数自增主键
• 如果查询的列是普通列。又想提高查询性能，那么给这一列加上索引。
• 一张表中最多5个索引列
• 索引的应用场景是读多写少
• 在完成建库建表遵守各种规范后，sql的性能是否ok，就需要关心sql语句能否命中索引。

七、Explain——SQL优化神器

1.Explain的介绍

在完成工程结构的优化之后，数据库、表、表中的字段及表的索引，都能够支持海量数据的快速查找。但是查找依然需要通过SQL语句来实现，因此性能优秀的SQL语句是能够走索引，快速查找到数据的。性能不Ok的SQL语句，就不会走索引，导致全表扫描。

阿里云rds提供了慢sql的查询功能。找到慢SQL后，如何做优化，以及如何查询一条SQL语句是否是慢SQL，那就可以通过Explain工具。

通过在SQL语句前面加上Explain关键来执行，于是就能看出当前SQL语句的执行性能。

explain SELECT * from employees where name like "customer100%"

2.MySQL的内部优化器

explain select * from tb_book where id=1;

show warnings;

在执行上面这条SQL语句的时候，MySQL内部优化器会把语句转换成下面这条SQL，因为这样性能更好（直接从索引里获取数据）

* select#1 */ select '1' AS `id`,'千锋Java厉害' AS `name` from `db_mysql_pro`.`tb_book` where true

3.Explain中各个列的细节

1) select_type列

该列描述了查询的类型：

• simple：简单查询
• primary：外部的主查询
• derived：在from后面进行的子查询，会产生衍生表
• subquery：在from的前面进行的子查询
• union：进行的联合查询

来自于简单的查询： simple类型

explain select * from tb_book where id=1;

来自于复杂的查询：除simple外的其他类型

EXPLAIN select (select 1 from tb_author where id=1) from (select * from tb_book where id=1) der;

分析：

对于union的联合查询，查询的类型是union

2）table列

这一列表示该sql正在访问哪一张表。也可以看出正在访问的衍生表 <derivedN>

3）type列

通过Type列，可以直接的看出SQL语句的查询性能，性能从大到小的排列：

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

一般情况下我们要保证我们的sql类型的性能是range级别。不同的级别的情况：

• null：

一般在查询时使用了聚合函数，于是直接从索引树里获取数据，而不用查询表中的记录。

explain select min(id) from tb_book;

• const:

在进行查询时，查询的条件，使用了主键列或者唯一索引列的值与常量进行比较，这种性能是非常快的，所以是const

• system：

是const的特殊情况，一般在衍生表里，直接匹配一条记录，就是system

EXPLAIN select (select 1 from tb_author where id=1) from (select * from tb_book where id=1) der;

• eq_ref

在进行连接查询时，连接查询的条件中使用了本表的主键进行关联，因此这种类型的sql就是eq_ref

EXPLAIN select * from tb_book_author left JOIN tb_book on tb_book_author.book_id = tb_book.id

• ref

简单查询：

使用普通列作为查询条件

EXPLAIN select * from tb_book where name='book1'

![在这里插入图片描述](https://img-blog.csdnimg.cn/9278be573bfc4765b203fdbc895be12a.png#pic_center

复杂查询里：

在进行连接查询时，连接查询的条件中使用了本表的联合索引列，因此这种类型的sql就是ref

EXPLAIN select book_id from tb_book left join tb_book_author on tb_book.id = tb_book_author.book_id

• range

在索引列上使用了范围查找，性能是ok的

explain select * from tb_author where id>1

• index

在查询表中的所有的记录，但是所有的记录可以直接从索引树上获取，因此这种sql的查询类型就是index

explain select * from tb_book

tb_book中的所有列：id 和 name 都是索引列。

• ALL

全表扫描。就是要从头到尾对表中的数据扫描一遍。这种查询性能是一定要做优化的。

4）id列

在复杂查询中，多条sql语句，谁的id大，谁先执行。如果多条sql语句的id 一样大，那么谁在上面，谁先执行。

5）possible_keys列

这一次查询可能使用到的索引（名称）。为什么要设计possible_key这一列。因为在实际MySQL内部优化器做优化选择时，虽然我们写的sql语句是使用了索引列，但是MySQL内部优化器发现，使用索引列查找的性能并没有比全表扫描的性能要好，于是MySQL内部优化器就选择使用全表扫描。

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

6）key列

MySQL内部优化器最终选择是否使用索引来进行查找。

7）key_len列

这一列的主要作用，通过查看这一列的数值，推断出本sql选择了联合索引中的哪几列。

-- idx_name_age_position` (`name`,`age`,`position`)
EXPLAIN select * from employees where name = 'customer10011' # key_len = 74
EXPLAIN select * from employees where name = 'customer10011' and age=30 #key_len = 78
EXPLAIN select * from employees where name = 'customer10011' and age=30 and position='dev' # key_len = 140
EXPLAIN select * from employees where name = 'customer10011' and position='dev' # key_len = 74 
# 看出此sql只走name列的索引

8）extra列

这一列展示了这条sql的一些其他信息

• using index

使用了覆盖索引

EXPLAIN select book_id,author_id from tb_book_author where book_id = 1

上面的book_id和anthor_id都是索引，显示的结果都是索引列，所以会出现using index

• Using where

where的条件没有使用索引列。这种性能是不ok的，我们如果条件允许可以给列设置索引，也同样尽可能的使用覆盖索引。

比如下面的name的列不是索引列

EXPLAIN select * from tb_author where name > 'a'

• using index condition

查询的列没有完全被索引覆盖，并且where条件中使用普通索引

select *的所有列中，有一些列不是索引列，where使用的是book_id这个普通索引列

EXPLAIN select * from tb_book_author where book_id > 1

• Using temporary

会创建临时表来执行，比如在没有索引的列上执行去重操作，就需要临时表来实现。

去重操作的时候，先查出所有结果，然后新建临时表存储，再执行去重操作

EXPLAIN select DISTINCT name from tb_author

这种情况可以通过给列加索引进行优化。

• using filesort

MySQL对数据进行排序，都会使用磁盘来完成，可能会借助内存，涉及到两个概念：单路排序、双路排序

EXPLAIN select * from tb_author order by name

• Select tables optimized away

当直接在索引列上使用聚合函数，意味着不需要操作表

EXPLAIN select min(id) from tb_book

八、Trace工具

MySQL内部优化器在对一些sql语句执行时是否走索引或全表扫描做了一定的优化，该优化的依据可以通过trace工具看到，主要是依据两者的时间的开销，选择时间短的那一种途径。

{
  "steps": [
    {
      "join_preparation": { -- 阶段一：进入到准备阶段
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": { -- 阶段二：进入到优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": { -- 条件处理
              "condition": "WHERE",
              "original_condition": "(`employees`.`name` > 'a')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [ --表依赖详情
              {
                "table": "`employees`",
                "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": "`employees`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 5598397,
                    "cost": 576660
                  } /* table_scan */,
                  "potential_range_indexes": [--可能使用到的索引
                    {
                      "index": "PRIMARY", -- 主键索引
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_name_age_position", --联合索引
                      "usable": true,
                      "key_parts": [
                        "name",
                        "age",
                        "position",
                        "id"
                      ] /* key_parts */
                    },
                    {
                      "index": "idx_hire_time",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "idx_name_age_position",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ] /* potential_skip_scan_indexes */
                  } /* skip_scan_range */,
                  "analyzing_range_alternatives": { -- 分析各个索引的使用成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_name_age_position",
                        "ranges": [
                          "a < name"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
                        "using_mrr": true,
                        "index_only": false, -- 使用使用了覆盖索引
                        "rows": 2799198, -- 要进行扫描的行数
                        "cost": 2.08e6, -- 要花费的时间
                        "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": "`employees`",
                "best_access_path": { -- 最优的访问路径
                  "considered_access_paths": [ -- 最后选择的访问路径
                    {
                      "rows_to_scan": 5598397, -- 全表扫描的行数
                      "access_type": "scan", -- 全表扫描
                      "resulting_rows": 5.6e6, -- 结果的行数
                      "cost": 576658, -- 花费的时间
                      "chosen": true, --选择了此路径
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 5.6e6,
                "cost_for_plan": 576658,
                "sort_cost": 5.6e6,
                "new_cost_for_plan": 6.18e6,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`employees`.`name` > 'a')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`employees`",
                  "attached": "(`employees`.`name` > 'a')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "optimizing_distinct_group_by_order_by": {
              "simplifying_order_by": {
                "original_clause": "`employees`.`position`",
                "items": [
                  {
                    "item": "`employees`.`position`"
                  }
                ] /* items */,
                "resulting_clause_is_simple": true,
                "resulting_clause": "`employees`.`position`"
              } /* simplifying_order_by */
            } /* optimizing_distinct_group_by_order_by */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "steps": [
              ] /* steps */,
              "index_order_summary": {
                "table": "`employees`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "unknown",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`employees`",
                "original_table_condition": "(`employees`.`name` > 'a')",
                "final_table_condition   ": "(`employees`.`name` > 'a')"
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`employees`"
              }
            ] /* refine_plan */
          },
          {
            "considering_tmp_tables": [
              {
                "adding_sort_to_table": "employees"
              } /* filesort */
            ] /* considering_tmp_tables */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "sorting_table": "employees",
            "filesort_information": [
              {
                "direction": "asc",
                "expression": "`employees`.`position`"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "memory_available": 262144,
              "key_size": 40,
              "row_size": 190,
              "max_rows_per_buffer": 1379,
              "num_rows_estimate": 5598397,
              "num_rows_found": 5913853,
              "num_initial_chunks_spilled_to_disk": 1954,
              "peak_memory_used": 262144,
              "sort_algorithm": "std::stable_sort",
              "sort_mode": "<fixed_sort_key, packed_additional_fields>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

九、SQL优化实战

SQL语句做查询时如何提升性能是我们时常要关注的。我们在写SQL有很多的场景，接下来逐一分析：

1.Order by优化

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

# using filesort
Explain select * from employees where name='customer' order by position;
# 没有使用文件排序 
Explain select * from employees where name='customer' order by age, position;
# 不满足最左前缀法则，使用了文件排序
Explain select * from employees where name='customer' order by position, age;
# 满足最左前缀法则，使用索引排序
Explain select * from employees where name='customer' and age=20 order by position, age;
# 排序方向不同，没有使用索引排序
Explain select * from employees where name='customer' and age=20 order by age, position desc;
# 使用范围查询，使用了文件排序
Explain select * from employees where name in ('customer','aa') order by age, position;
# 使用范围查询，使用了文件排序
Explain select * from employees where name > 'a' order by name;

2. Group by优化

group by 本质上是先排序 后分组，所以排序优化参考Order by优化。

3.文件排序的执行原理

MySQL在进行文件排序时，会设置一个缓冲区，比较要排序的所有列的数据大小之和，与max_length_for_sort_data（默认是1024个字节）进行比较。

• 单路排序：比该变量小，所有的数据会放到缓冲区里进行排序。
• 双路排序：比该变量大，只把排序字段和主键字段放到缓冲区里进行排序，完成后使用主键再做一次回表查询。

4.分页查询优化

Explain select * from employees limit 10000,10

这条sql会把前面10000条数据全部查询出来，再找之后的10条，最后把前面10000条数据舍弃掉。这样的性能是不ok

如果再主键连续的情况可以这么优化：

Explain select * from employees where id>10000 limit 10

如果主键不连续？

案例：

# 会进行全表扫描
Explain select * from employees order by name limit 100000,10

进行调整：

explain select * from employees a inner join (select id from employees order by name limit 100000,10)  b on a.id = b.id;

避免了全表扫描，其中第一条子查询虽然是ALL，但是只对10条数据做ALL，性能很快。

所以这样的优化，虽然sql变复杂了，但明显对性能进行了提升。

5.Join查询优化

对于上面两个例子，大表（数据量大的）和小表（数据量小的）进行join查询时，如果查询条件都设置了索引，那么将会使用NLJ算法，性能是ok的。注意：无论小表和大表在join时书写的位置，mysql内部优化器都会选择让小表驱动大表，也就是从小表中获取数据，去大表中查。

如果查询条件没有使用索引，那么就会发生BNLJ算法，通过创建内存缓冲区来实现join查询，对性能较大。

结论：在使用join查询时，join的两张表的关联字段：数据类型、长度一定要一致，并且都要创建索引。

• NLJ（nested loop join）:嵌套循环join，第一个图
• BNLJ（block nested loop join） ： 块嵌套循环join，第二个图
  

6. in和exists的优化

在复杂sql中是使用in还是exists，取决于条件中是否是小表，如果是小表，就使用in，反之则使用exists

1）in

in在复杂查询中的应用场景是： 小表驱动大表。在where后的表是小表

select * from A where id in (select id from B) 
# 相当于：
for(select id from B){
   select * from A where A.id = B.id
}

2）exists

select * from A where exists (select 1 from B where B.id = A.id)
# 等价于：
for(select * from A){
   select * from B where B.id = A.id
}

7.count优化

如果在经常使用count做总数统计的场景下，建议将总数维护在redis中，使用缓存提高访问效率，也减少了数据库的压力。

十、锁的定义及分类

1.锁的定义

锁是用来协调在多个线程并发访问同一共享资源时带来的安全问题。如果我们频繁的使用锁，在并发非常大的时候会带来性能问题。

频繁用锁必然会造成性能问题，但不用锁，又会造成安全问题，MySQL在安全问题和性能方面是如何做权衡的。MySQL并发性非常好，它是如何解决安全问题呢？——MVCC思想

2.锁的分类

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

• 乐观锁（并发性能是比较好的）：CAS自旋锁，是非常典型的乐观锁。乐观的认为，当前系统环境下的并发量并不是很大，当真的有并发发生的时候，才会去上锁。
• 悲观锁（并发性能要弱）：悲观的认为当前系统环境下并发情况是非常严重的，所有的任务在执行时都要上锁。保证了安全性，牺牲了并发性。

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

这两种锁都是属于悲观锁。

• 读锁（共享锁）：对于读取同一行数据的并发来说，是可以同时进行的，但是写不行。
• 写锁（排他锁）：在上了写锁之后及释放写锁之前，所有的其他并发操作（包括读和写），都不能进行。

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

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

4.表锁

MyISAM默认是支持表锁的，也就是说在默认情况下，多个线程并发操作时，一个线程操作MyISAM表后，相当于是获取了该表的表锁，其他所有的操作都不能进行。

MyISAM不支持事务。

InnoDB如何上表锁：

• 手动上锁：

# 格式
lock table 表1 read/write,表2 read/write;
# 示例
lockf table tb_book read;

读锁：当前会话能读能写，其他会话只能读不能写。

写锁：当前会话能读能写，其他会话不能读写。

• 查看当前会话获取的所有锁：

show open tables;

• 释放当前会话的所有锁，不会把其他会话持有的锁释放掉。

unlock tables;

5.行锁

InnoDB支持行锁，在并发事务里，每个事务的读写删的操作，相当于是上了行锁。也可以通过“for update”为select上行锁。行锁可以支持并发的读，所以行锁是一个读锁。

行锁的特点：上锁的开销大，加锁速度慢，但是细粒度更细，并发性更好。这也是InnoDB和MyISAM的区别。

InnoDB和MyISAM的区别：一是InnoDB支持事务，二是采用了行级锁！！！

上行锁的两种方式：

• update tb_author set name=‘m’ where id = 1;
• select * from tb_author where id=1 for update;

6.事务的特性

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

7.事务的隔离级别

• read uncommitted: 读未提交,一个事务中读到了另一个事务没有提交的数据，这种情况称为脏读。
• Read committed: 读已提交。一个事务中能读到另一个事务已经提交的数据，那么就出现了不可重复读，也就是说多次读到的数据有可能是不一致的。
• repeatable read(mysql默认的隔离级别): 可重复读，就是为了解决不可重复读的问题。会出现幻读（虚度）的情况：

在事务A中插入数据时，该条数据已被事务B插入了，那么在事务A中就不能进行插入，但事务A的查询依然是查不到该事务的。

怎么解决：通过上行锁，或者设置隔离级别为Serializable。

• Serializable:序列化 相当于是上了表锁，性能非常差，所以不要考虑。

8.MVCC思想

MySQL在权衡安全性和并发性能上，通过使用MVCC思想，做了较好的控制，所以我们说MySQL的能力是我们互联网公司都认可的。

思考：AB两个事务，A第一次读money = 5000, 然后事务B 执行语句update money= money- 500，并且提交，之后A再次读数据，发现money还是等于5000，为什么？
然后此时A要执行语句update money= money- 500，提交（commit）事务之后，再次读数据，发现money= 4000,这是为什么？

对于上面的图，就是事务A,和事务B(隔离级别上可重复读，解决了不可重复>读的问题)，当事务A进行读操作时，会从拿到真实数据并生成一个快照（真实的数据和快照中都会有一个事务id,此时id=1）,以后读的时候，事务A就只读快照就好了（因为可重复读），
第一次事务A读到money=5000,
此时事务B对数据进行更新(money-500)并提交，真实数据中变为money= 4500,并且修改了真实数据中的事务id=2,
事务B提交以后，事务A再读一次，依旧是money=5000,因为这是读操作，只会读快照（可重复读思想），如果此时事务A也要对真实数据进行写操作（money-500),事务A会去拿到真实数据，发现真实数据里面的事务id=2,和自己快照中的id=1不一样，所以会更新自己的快照，也就是此时事务A的快照改为（money = 4500, id =2,和真实数据的一样），更新完快照之后，继续刚才要执行的写操作，money-500 = 4000,事务提交后就会写到真实数据，真实数据就会变为（money = 4000, 事务id = 3)

9.死锁

所谓的死锁就是在多个并发中，事务A和事务B都持有对方需要的锁，且双方都没办法释放手中的锁。

10.间隙锁

所谓的间隙锁，就是通过范围来上锁，对满足范围的区间实现上锁的效果