2.3 数据库-深入理解

我会带着你远行 2024-03-31 13:16 64阅读 0赞

**目录**

2.3.1 索引

2.3.1.1 局部性原理

2.3.1.2 什么是索引

2.3.1.3 索引的分类

2.3.1.4 索引基本操作

2.3.1.4.1 主键索引

2.3.1.4.2 单列索引

2.3.1.4.3 唯一索引

2.3.1.4.4 复合索引

2.3.1.5 索引底层原理

2.3.1.6 聚簇索引和非聚簇索引

2.3.1.6.1 聚簇索引

2.3.1.6.2 非聚簇索引

2.3.1.6.3 覆盖索引

2.3.1.7 B+Tree索引和Hash索引

2.3.1.7.1 B+Tree索引

2.3.1.7.2 Hash索引

2.3.1.7.3 B+Tree索引与Hash索引的区别

2.3.1.8 创建索引的原则

2.3.1.9 索引失效

2.3.1.9.1 联合索引基本知识

2.3.1.9.2 几个基本索引失效的原理

2.3.2 MySQL优化

2.3.2.1 为什么sql优化很重要

2.3.2.2 MySQL架构

2.3.2.1.1 优化器

2.3.2.1 EXPLAIN执行计划

2.3.2.1.1 EXPLAIN

2.3.2.1.2 数据准备

2.3.2.1.3 id与表执行顺序

2.3.2.1.4 select\_type

2.3.2.1.5 table

2.3.2.1.6 type

2.3.2.1.7 possible\_keys

2.3.2.1.8 key

2.3.2.1.9 key\_len

2.3.2.1.10 ref

2.3.2.1.11 rows

2.3.2.1.12 Extra

2.3.2 锁

2.3.2.1 Mysql的锁

2.3.2.2 锁的分类

2.3.2.2.1 行级锁

2.3.2.2.2 表级锁

--------------------

## 2.3.1 索引 ##

### 2.3.1.1 局部性原理 ###

在CPU访问寄存器时，都趋于聚集在一片连续的区域中，这就被称为局部性原理。  
局部性原理又分为时间局部性（temporal locality） 和空间局部性 （spatial locality） 。

**时间局部性**:  
如果程序中的某条指令一旦执行，不久以后该指令可能再次执行;如果某数据被访问过，不久以后该数据可能再次被访问。产生时间局部性的典型原因，是由于在程序中存在着大量的循环操作。  
简单来说：被引用过一次的存储器位置在未来会被多次引用

**空间局部性**:  
一旦程序访问了某个存储单元，在不久之后，其附近的存储单元也将被访问，即程序在一段时间内所访问的地址，可能集中在一定的范围之内，这是因为指令通常是顺序存放、顺序执行的，数据也一般是以向量、数组、表等形式簇聚存储的。  
\--简单来说：如果一个存储器的位置被引用，那么将来他附近的位置也会被引用。

###    2.3.1.2 什么是索引 ###

一种帮助mysql提高查询效率的数据结构

**索引的优点**：

1.  大大加快了数据查询速度

**索引的缺点**：

1.  维护索引需要耗费数据库资源
2.  索引需要占用磁盘空间
3.  当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

### 2.3.1.3 **索引的分类** ###

**a.主键索引**  
设定为主键后数据库会自动建立索引，innodb为聚簇索引，主键索引索引列不能为空

**b.单值索引**（单列索引/普通索引）  
即一个索引只包含单个列，一个表可以有多个单列索引

**c.唯一索引**  
索引列的值必须唯一，但允许有空值，只能存在一个

**d.复合索引**  
即一个索引包含多个列

**e.全文索引**(MYISAM引擎独有)  
全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型上创建。MYSQL只有MYISAM存储引擎支持全文索引

InnoDB只有上面四个索引

如数据库中有一个表，表结构如下所示

![3cac30ffda034a3793ebc6d41b45f808.png][]

我可以将经常用到的列设置为单列索引 比如id或者name。

但是如果我以id和name，作为共同的查询条件  **where id and name，**那么单列的索引就失去了效果，这时候，如果我们id和name一起设置为索引，复合索引就可以更快的查询。

### 2.3.1.4 索引基本操作 ###

#### 2.3.1.4.1 主键索引 ####

**建表 主键自动创建主键索引**

create table t_user(id varchar(20) primary key,name varchar(20));

**查看索引**

show index from t_user;

**索引信息**

![d397d1253cea4b7890f9e7bae6bd36d7.png][]

主键索引:PRIMARY，索引列为id

#### 2.3.1.4.2 单列索引 ####

**建表时创建**

create table t_user1(id varchar(20) primary key,name varchar(20),key(name));

注意：随表一起创建的索引的索引名同列名一致

![100d80263f7545a19fa769aceeb2e9ac.png][] **建表后创建**

create indec name_index on t_user(name);
    
    --通常索引名命名格式为 列名_index

![6979cfb909314a1898c4d9e37ac3a80b.png][]

**删除索引**

drop index 索引名 on 表名

#### 2.3.1.4.3 唯一索引 ####

**建表时创建**

create table t_user(id varchar(20) primary key,name varchar(20),unique(name));

**建表后创建**

create unique index name_index on t_user(name);

#### 2.3.1.4.4 复合索引 ####

**建表时创建**

create table t_user(id varchar(20) primary key,name varchar(20),age int,ket(name,age));

![6a6eab7b1fd3487ab06125c2dfc26bc6.png][]

**建表后创建**

create index nameage_index on t_user(name,age)

仔细观察复合索引的结果，会发现name和age的索引名一样均为name,另外注意**索引顺序**，先name后age。

这里有一道经典的面试题

假设以 **name age birthday** 三个字段创建的复合索引，问：

A、查询时顺序:**name birthday age** 能否利用索引

B、查询时顺序:**name age birthday** 能否利用索引

C、查询时顺序:**birthday age name** 能否利用索引

D、查询时顺序:**age birthday** 能否利用索引

解析

官方给出的两条原则

**1.最左前缀原则**

定义时是按照name age birthday，所以基于name查可以用的上索引，基于name age查可以利用的上索引，基于name age birthday查可以利用的上索引，但是基于age birthday或者 birthday age name等均不能符合最左前缀原则。

所以只有B满足最左前缀原则

**2.mysql引擎在查询时为了更好利用索引，在查询过程中会动态调整查询字段顺序，以便利用索引**

会自动调整字段顺序，所以ABC均满足，只有D不满足。

综上所述，ABC都可以利用索引。

### 2.3.1.5 索引底层原理 ###

先思考一个问题

我插入的数据是无序的

--建表
    create table t_emp(id int primary key,name varchar(20),age int);
    --插入数据
    insert into t_emp values(5,'d',22);
    insert into t_emp values(6,'d',22);
    insert into t_emp values(7,'e',21);
    insert into t_emp values(1,'a',23);
    insert into t_emp values(2,'b',26);
    insert into t_emp values(3,'c',27);
    insert into t_emp values(4,'a',32);
    insert into t_emp values(8,'f',53);
    insert into t_emp values(9,'v',13);
    --查询
    select * from t_emp;

但是查询结果发现，存储到表内的数据却是有序的

![389040ac8a0b436d9eca20cde61c6053.png][]

由于id是主键，由于主键索引的存在，主键索引自动排序了，为了方便快速查询。

这就和索引的底层有关系

索引的结构为：id ，数据本身和 指针P。

![6164a4736f064ebab099bedb5f80089f.png][] id为1的索引结构

之后这些组成链表

![de92dca1909a402282943bbbfeb5cef0.png][]

单链表的查找只能按顺序从头到尾一次查询，时间复杂度为O(n)，n为链表长度。

假设如果我要查一百万呢？所以单纯的单链表完全不行。

**基于分页形式管理索引**

将链表分页，每页大小16KB

![2b4689b2b1204040bc34ed451e0827d8.png][]

同时添加页目录（页目录大小也为16KB），目录里保存每页的第一个索引的主键值和指针

结构如下图

![7de6b31cf86444fc98afe7c80322e26a.png][]

如果页目录很长呢，可以按照同样的思想，将页目录再向上抽取出顶级目录，一级目录等。

也称B+Tree 数据结构。

![219f7667e5784c5ab4e3750a2a815630.png][]

B+Tree相对于B-Tree有几点不同:

1.  非叶子节点只存储键(无值)和指针信息。
2.  所有叶子节点之间都有一个链指针。
3.  数据记录都存放在叶子节点中。

简单来说：

B树，每个节点都要存储数据，那就导致说页目录存的少了，多级目录，导致树的深度更深了

B+树，只有叶子节点存储数据，其他非叶子节点不存数据，这样每页目录就可以存的更多

[B树、B+树详解 - Assassinの - 博客园][B_B_ - Assassin_ -]

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

B-Tree结构中每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点(即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘/O次数，进而影响查询效率。

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。  
lnnoDB存储引擎中页的大小为16KB。

![c139397a670f4bc0b0a87fab125584eb.png][]

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

顶级页目录常驻内存，基于主键查询，三层结构，只需要两次IO，基于非主键查需要三次IO。

顶级页目录再内存中，不需要IO查询。

**聚簇索引就是表的主键索引**

### 2.3.1.6 聚簇索引和非聚簇索引 ###

聚簇索引:将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

非聚簇索引:将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

#### **2.3.1.6.1 聚簇索引** ####

![7de6b31cf86444fc98afe7c80322e26a.png][]

如图，a,23是数据，P是指针，它们存储在一个项中，并且只有叶子节点才保存数据，非叶子节点只保存索引id和指针，所以上图就是一个聚簇索引。

并且主键索引一定是聚簇索引，聚簇索引不一定是主键索引。

#### **2.3.1.6.2** 非聚簇索引 ####

**INNODB**

在Innodb中，在聚簇索引之上的索引称为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

![9e6ee7e51b874605ac7922130151a768.png][]

InnoDB使用的是聚簇索引，将主键组织到一颗B+树中，而行数据就存储在叶子节点上，若使用**“where id=14**”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶子节点，之后获得行数据。

若对name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索name,到达其叶子节点**获取对应的主键**；第二步**使用查询到的主键**在主索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据

> 思考为什么辅助索引的叶子节点存的是主键而不是地址值？存地址值不就直接可以获取到数据？
> 
> 因为增删改会导致**树的节点的地址（数据地址也可能）**发生改变，而主键唯一不会改变，所以通过主键来查找数据更优

聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键（(类似oracle中的Rowld)来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

**MYISAM**  
MyiSAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引/B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说,  
这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

![a28f9f2150c94413b58f0be16b1da748.png][]

**使用聚簇索引的优势**  
问题:每次使用辅助索引检索都要经过两次B+树查找，非聚簇索引只需要一次查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗?聚簇索引的优势在哪?  
1、由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中(缓存器)，再次访问时会在直接在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键ID来组织数据，获得数据更快。  
2、辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化（数据发生变化，有可能要重排序，自然而然树的结构也要发生变化），或者是我们需要查找的的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的游储空间大小。

#### **2.3.1.6.3 覆盖索引** ####

覆盖索引:

1.  覆盖索引是数据查询方式，不是索引结构
2.  查询的字段被使用到的索引树全部覆盖
3.  覆盖索引针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引找到主键，再通过主键到主键索引树种获取其他字段的值

定义一个复合索引

CREATE INDEX index_idandname ON user(id,name);

查询id为20的用户的name

select id, name from user where id = '20';

上述语句中，查找的字段 id 和 name都包含在联合索引 index\_idandname 的索引树中，这样的查询就是覆盖索引查询。

### 2.3.1.7 B+Tree索引和Hash索引 ###

根据存储方式的不同，MySQL 中常用的索引在物理上分为 B+Tree索引和 Hash 索引两类，两种不同类型的索引各有其不同的适用范围。

#### 2.3.1.7.1 **B+Tree索引** ####

B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循最左前缀原则，要考虑以下几点约束：

1.  查询必须从索引的最左边的列开始。
2.  查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
3.  存储引擎不能使用索引中范围条件右边的列。

#### 2.3.1.7.2 **Hash索引** ####

哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B+Tree索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：  
MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B+树索引来说，建立哈希索引会耗费更多的时间。

1.  不能使用 HASH 索引排序。
2.  HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
3.  HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

#### 2.3.1.7.3 **B+Tree索引与Hash索引的**区别 ####

Hash索引底层是哈希表，由于哈希表是以key-value存储数据的结构，所以多个数据在存储关系上是完全没有顺序关系的，所以对于范围查询是无法通过索引查询的，只能全表扫描。因此Hash索引只适用于等值查询。

Hash索引无法利用索引排序

Hash索引不支持多列联合索引的最左匹配原则

如果有大量重复键值的情况下，Hash索引的效率会很低（哈希碰撞问题）

B+Tree索引是一种多路平衡查询树，它的节点天然有序（左节点小于父节点，父节点小于右节点）。

### 2.3.1.8 创建索引的原则 ###

### 2.3.1.9 索引失效 ###

#### 2.3.1.9.1 联合索引基本知识 ####

![20c793b9539d44fca1562145909e12ec.png][]

a的值是有顺序的：1，1，2，2，3，3

b的值是有顺序的：1，2，1，4，1，3

**联合索引的排序**

b依赖于a,先根据a进行排序，b在a的基础上排序。

如(1,1),(1,2)排序，**b是无序的，只有a相同的情况下，b才是有序的**。

#### 2.3.1.9.2 几个基本索引失效的原理 ####

CREATE TABLE `test_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
      `user_id` varchar(36) NOT NULL COMMENT '用户id',
      `user_name` varchar(30) NOT NULL COMMENT '用户名称',
      `phone` varchar(20) NOT NULL COMMENT '手机号码',
      `lan_id` int(9) NOT NULL COMMENT '本地网',
      `region_id` int(9) NOT NULL COMMENT '区域',
      `create_time` datetime NOT NULL COMMENT '创建时间',
      PRIMARY KEY (`id`),
      KEY `idx _phone_lan_region` (`phone`,`lan_id`,`region_id`),
      KEY `idx create_time` (`create_time`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4057960 DEFAULT CHARSET=utf8mb4;

这里建立了一个复合索引：phone、lan\_id、region\_id构成索引

test\_user表结构如下

![ffec881e32db49a9ba95b1837fe6962d.png][]

**第一条sql语句**

EXPLAIN SELECT * FROM test_user WHERE phone="1212" and lan_id=1

![b8e3a2ff171c4a098394e4957b07877c.png][] 由于phone是已经排好序的，之后可以根据二分查找法在树中找到phone。因为phone已经确定了，而且lan\_id依赖于phone，所以也可根据二分查找法快速定位到lan\_id。

**第二条sql语句**

EXPLAIN SELECT * from test_user where lan_id=1

![7c11a0deeae247b0ae9f185831a30f0b.png][] type=all,rows=4029883，很明显没有使用到索引

没有用到phone，而是直接使用了lan\_id，很明显违背了最左匹配原则

定义时索引的建立是按照phone的顺序建立的，这里没有phone，而只有lan\_id，就导致，虽然phone有序，但是查找过程中却用不到。但是lan\_id是无序的，只有phone相等的情况下，lan\_id才有序，才可以用二分查找快速定位。但是现在根本没有phone,也就是说lan\_id是无序的，只能全表扫描了，索引失效。

**第三条sql语句**

EXPLAIN SELECT * from test_user where a>1 and b=1

![39a62cbfd4d040598aa531c37ec562fe.png][]

a是有序的，因此a>1可根据二分查找快速定位，但是b却是无序的，因此要查找b=1只能全表扫描。

## 2.3.2 MySQL优化 ##

### 2.3.2.1 为什么sql优化很重要 ###

整体

客户端、web端、数据库缓存、连接池、mysql数据库

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>层次</td> 
   <td>业务层</td> 
   <td>物理层</td> 
   <td>优化策略</td> 
  </tr> 
  <tr> 
   <td>第一层</td> 
   <td>查询需求</td> 
   <td>SQL层</td> 
   <td>减少查询</td> 
  </tr> 
  <tr> 
   <td>第二层</td> 
   <td>查询优化器</td> 
   <td>MYSQL层</td> 
   <td>查询路径优化</td> 
  </tr> 
  <tr> 
   <td>第三层</td> 
   <td>存储引擎</td> 
   <td>InnoDB层</td> 
   <td>锁和缓存优化</td> 
  </tr> 
  <tr> 
   <td>第四层</td> 
   <td>资源</td> 
   <td>硬件层</td> 
   <td>硬件优化、扩容</td> 
  </tr> 
 </tbody> 
</table>

**效果：** SQL层 > MYSQL层 > InnoDB层 > 硬件层

### 2.3.2.2 MySQL架构 ###

[MySQL :: MySQL 8.0 Reference Manual :: 8 Optimization][MySQL _ MySQL 8.0 Reference Manual _ 8 Optimization]

MySQL是一个C/S架构。服务器分两层：第一层是Server层，第二层是存储引擎。

Server层包括缓存、分析、优化等，并实现了跨存储引擎的功能，包括触发器、存储过程、视图等，核心是查询优化器。

存储引擎层负责mysql的数据存储和提取，大多数使用innodb

![c66cbe107aeb4c8f9a22feb26a823dbf.png][]

#### 2.3.2.1.1 优化器 ####

mysql优化器负责生成sql语句的执行计划

一条查询可有一有很多种执行方式，最后返回相同的结果，查询优化器就是找到其中最好的执行计划。

mysql使用基于成本的优化器，它会尝试预测一个查询使用某种执行计划的成本，并选择成本最小的一个。

查询优化器依赖存储引擎提供的统计信息来评估成本，到InnoDB的统计信息是抽样的，可能不准确。

另外优化器还能， limit提示提前终止查询，重新定义表关联顺序，覆盖索引扫描，等价变换规则，子查询优化等

![51cfa4826d02446e9249a32054711ba4.png][]

优化器有两个阶段：

第一个阶段是**逻辑优化**，这个阶段主要是使用关系代数对SQL语句做一些等价变换，对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。

第二阶段是**物理优化**。物理优化一般分为两种：

**1、**基于规则的优化（RBO，Rule-Based Optimizer）

这种方式主要是基于一些预置的规则对查询进行优化。

**2、**基于代价的优化（CBO，Cost-Based Optimizer）

这种方式会根据模型计算出各个可能的执行计划的代价，然后选择代价最少的那个。它会利用数据库里面的统计信息来做判断，因此是动态的。

### 2.3.2.1 EXPLAIN执行计划 ###

#### 2.3.2.1.1 EXPLAIN ####

查询执行计划

EXPLAIN + SQL语句

![2539183ee5674bd6bb038b05cb6b10d3.png][]

#### 2.3.2.1.2 数据准备 ####

表结构如下所示

![135397feb8434c448defa7a3b583f7dc.png][]

准备数据

create table course
    (
    cid int (3),
    cname varchar(20),
    tid int (3)
    );
    
    create table teacher
    (
    tid int(3),
    tname varchar(20),
    tcid int(3)
    );
    create table teacherCard
    (
    tcid int(3),
    tcdesc varchar(200)
    );
    
    insert into course values(1,'java',1);
    insert into course values(2,'html',1);
    insert into course values(3,'sql',2);
    insert into course values(4,'web',3);
    
    insert into teacher values(1,'高老师',1);
    insert into teacher values(2,'艾老师',2);
    insert into teacher values(3,'李老师',3);
    
    insert into teacherCard values(1,'高老师desc');
    insert into teacherCard values(2,'艾老师desc');
    insert into teacherCard values(3,'李老师desc');

#### 2.3.2.1.3 id与表执行顺序 ####

> id列的编号是select 的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id不同，id越大越优先执行。
> 
> id为NULL最后执行。

查询课程编号为2 或 教师编号为3 的教师信息 :

explain select teacher.* from teacher,course,teacherCard where teacher.tid=course.tid and teacher.tcid=teacherCard.tcid and (course.cid=2 or teacherCard.tcid=3)

![76d841e2c8f64771916fa40caf6a31a2.png][]

表执行顺序 teacher -> teacherCard -> course

**结论： id值相同，表的执行顺序从上往下顺序执行**

teacher:3条数据 teacherCard:3条数据 course:4条数据

![64673928d81447329bb511c487c0a386.png][]

现在往teacher表中新增数据

insert into teacher values(4,'math',4);
    insert into teacher values(5,'English',5);
    insert into teacher values(6,'python',6);

再次执行上面的select查询语句

![5cb5a0f095c94b51bd72bf88fc9a0d51.png][]

发现表的执行顺序发生变化 teacherCard -> course -> teacher

teacherCard:3条数据 course:4条数据 teacher:6条数据

为什么查询顺序会改变呢？

原因在于笛卡尔积 ，如图有三张表

![9ed4397374e1489aad119c42dc43e67a.png][]

我们根据表的不同执行顺序得到的中间表数据规模是不同的，如上图红色框内就是不同的中间表。虽然最终的数据量均为24,但是中间表的数据量不一致，由于中间表数据量少，占用内存少，所以会被优先计算。数据量大的放在后面计算。

**数据量少的表越先被执行**

下面考虑id值不同的情况

查询教授sql课程的教师描述:

> 语法分析：
> 
> 主句是：查询老师描述

使用多表查询方式

explain select teachercard.tcdesc from teachercard,teacher,course where teacher.tid=course.tid and teacher.tcid=teacherCard.tcid and (course.cname='sql');

![c421bfb95f5f494a855c65a928018f22.png][]

转换为子查询方式

explain select tcdesc FROM teachercard where teachercard.tcid=(select tcid from teacher where tid=(select tid from course where cname='sql'));

![9de5fe358a9d4d5b96550a99fb3e9ee6.png][]

嵌套子查询时，先查内层，再查外层

![3b0f99c4cf9d4af9abd0cc14d50701d4.png][]

**结论：id值不同，id值越大越优先执行**

#### 2.3.2.1.4 select\_type ####

select\_type 表示对应行是简单还是复杂的查询

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>PRIMARY</td> 
   <td>主查询，包含子查询SQL中的主查询（最外层的select）</td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>包含子查询SQL中的主查询（非最外层）</td> 
  </tr> 
  <tr> 
   <td>SIMPLE</td> 
   <td>简单查询（不包含子查询和union）</td> 
  </tr> 
  <tr> 
   <td>DRERIVED</td> 
   <td>衍生查询，（包含在from子句查询中，使用到临时表）</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>，(在union中的第二个和随后的select)</td> 
  </tr> 
 </tbody> 
</table>

子查询+多表

explain select teacher.tname,teacherCard.tcdesc from teacher,teacherCard where teacher.tcid=teacherCard.tcid and teacher.tid = (select course.tid from course where cname='sql');

![af026469e7874f29aeaaa339e0af5b72.png][]

id值有相同有不同，按照前面的结论：**id值相同，从上往下执行，id值不同，id值大的先执行**

PRIMARY：主查询，包含子查询SQL中的主查询（最外层）

SUBQUERY ：包含子查询SQL中的主查询（非最外层）

**SIMPLE**

简单查询，不包含子查询和union

**DRERIVED**

衍生查询，使用到临时表

下面两种种情况会用到**DRERIVED**

1.  在from子查询中只有一张表
2.  在from子查询中，如果有table1 union table2，则table1为DRERIVED

#### 2.3.2.1.5 table ####

表示explain的一行正在访问哪个表

#### 2.3.2.1.6 type ####

表示关联类型或访问类型，即MYSQL决定如何查找表中的行，查找数据行记录的大概位置

通过它可以判断出查询是全表扫描还是基于索引的部分扫描，从上到下效率依次增强。

**ALL：**表示全表扫描，扫描聚簇索引的所有叶子节点，性能最差

EXPLAIN select * from teacher

![0333d9e12fce41e5a9b965de10d89915.png][]

**index：**基于索引的全表扫描，先扫描索引再扫描全表数据

create index tid_index on teacher(tid)
    explain select tid from teacher

![9e6aa984b4f04515a265139a4a11aeb2.png][]

**range：**表示使用索引范围查询，使用>、>=、<、<=、in等

explain select tid from teacher where tid in(1,2)

![fbfc4b99c4134c4585520dcb26b76805.png][]

**ref：**表示使用非唯一索引进行单值查询

create index tnametid_index on teacher(tname,tid)
    explain select * from teacher where tname='李老师'

![81bad726558b46b4b3258bf4e7d12be0.png][]

**eq\_ref：**一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果，即一对一。

**const:** 表示使用主键或唯一索引做等值查询，常量查询

**system**：表里只有一行记录，查询确定只有一条

> set session optimizer\_switch='derived\_merge=off';关闭mysql 5.7新特性对衍生表的合并优化
> 
> set session optimizer\_switch='derived\_merge=on;还原默认配置

#### 2.3.2.1.7 possible\_keys ####

表示查询时可能使用到的索引，注意并不一定会真正使用，显示的是索引的名称。

#### 2.3.2.1.8 key ####

SQL实际用到了哪个索引来优化对该表的访问

#### 2.3.2.1.9 key\_len ####

表示查询使用了索引的字节数量，可以判断是否使用了组合索引

字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4

char(n): 代表使用了n\*字符集长度

varchar(n)：n\*字符集长度+2字节

#### 2.3.2.1.10 ref ####

表示查询sql语句中查询条件对应的类型

#### 2.3.2.1.11 rows ####

MYSQL查询优化器会根据统计信息，估算sql要查询到结果需要扫描多少行记录。原则上rows是越少效率越高

#### 2.3.2.1.12 Extra ####

表示额外的信息

**using index：**查的列被索引覆盖，并且条件是索引的第一个列，是**性能最高**的表现。

**using where using index：**查的列被索引值覆盖，但是查询条件是索引列之一，但不是第一列。

**using index condition：**查询的列不完全被索引覆盖，但是where条件中是最左侧索引列的范围。

**using temporary：**mysql需要创建一张临时表来处理查询。**出现这种情况一般是要进行优化的**，首先是想到用索引来优化。

**using filesort：**表示在索引之外，需要额外进行外部的排序动作。一般和order by有直接关系，一般可以通过合适的索引来减少或者避免

> **using filesort文件排序原理**
> 
> mysql通过比较系统变量中max\_length\_for\_sort\_data(默认1024字节)的大小和需要查询的字段总大小，来判断使用哪种排序模式，如果字段总长度小于max\_length\_for\_sort\_data，那么使用单路排序模式，如果字段总长度大于max\_length\_for\_sort\_data，那么使用双路排序模式。
> 
>  *  单路排序：是一次性取出满足条件的所有行数据，然后在sort buffer中按照我们的排序字段再进行排序
>  *  双路排序：又称回表排序模式，首先根据查询条件直接定位数据所在的行id,然后把id和排序字段放到sort buffer中，在sortbuffer中进行排序，之后拿着排序好的id，回到原表中查询所有字段。

## 2.3.3 数据库优化 ##

### 2.3.3.1 数据库优化方案 ###

**1.数据库服务器内核优化**

**2.my.cnf配置，搭配压力测试进行调优**

**3.sql语句调优**

前两种有专业的人员来优化，而我们开发者需要做第三种。

### 2.3.3.2 前两种简单介绍 ###

### 2.3.3.3 **sql语句调优** ###

#### 1.使用缓存优化查询 ####

进行多次相同的查询，结果会放入缓存中，后续再进行相同的查询，就直接从缓存中取出，不会到表中提取

注意问题：避免缓存失效

由于函数返回的结果与‘2022-12-12’格式可能不同，会导致缓存失效

select name from table where date>=now() //currdate() 返回当前时间
    select name from table where date>='2022-12-12'

#### 2.使用explain检测sql查询 ####

显示sql的执行过程

#### 3.给搜索字段建立索引 ####

where后面的常用字段建立索引

注意问题：通过索引查询出的数据量不超过全表数据量的大约50%左右，索引效果是好的

但是一旦查询出的数据量很大，大概超过50%，

就会

#### 4.limit 1 ####

明确只有一行数据时，当查询到这行数据后，由于limit1的存在，系统就不会继续往下查询

#### 5.永久连接 ####

数据库在进行一次连接后，就会处于

存在问题：在一些极端的环境中，apache会发出http请求，创建子进程去请求数据库（不断发出连接），会给数据库增加压力

#### 6.数据库引擎 ####

选择正确的数据库引擎:主要myisam和InnoDB

myisam:适合大量查询，对于插入操作不友好（因为表锁）

InnoDB：插入操作友好，对查询操作不友好（InnoDB有表锁和行锁）

#### 7.大量delete、insert要拆分 ####

limit控制，不要一次性

当进行大量的delete或者insert操作时，会把整个表锁起来，所有web服务器的请求都无法访问该表，如果数量过大，可能会导致宕机。

#### 8.数据类型尽量使用小的 ####

由于不同的数据类型占用磁盘的大小是不同的，数据存储的越紧凑读取越快

#### 9.固定字段长度 ####

由于字段长度固定，很方便的通过偏移量来快速查找数据在磁盘中的位置

存在问题：会造成一些空间的浪费，另外读取到的数据会有空格（用trim去除空格）

#### 10.尽量不要赋null ####

会出现一系列的bug

#### 11.明确的固定的字段上使用enum ####

明确的字段，尽量中enum，不要用varchar

比如性别，要么男要么女，不要用varcher

国家、省、市同理

#### 12.id主键 ####

每个表都要建立，聚簇索引

id用int，不要用varchar，一方面int的速度比varchar快，另一方面数据库会对数据进行排序，int型可以用于排序

#### 13.避免使用select \* ####

查询的越多，速度越慢，数据越多，网络负载越大

#### 14.避免rand()函数 ####

因为rand()计算是在cpu上进行的

#### 15.JOIN连接两表时,保持字段的类型一致 ####

#### 16.垂直分割 ####

将一个表按列的形式分割成多个表

## 2.3.4 锁 ##

### 2.3.4.1 Mysql的锁 ###

锁（Lock）是数据库在并发访问时保证数据一致性和完整性的主要机制。

### 2.3.4.2 锁的分类 ###

MySQL中的锁可以按照粒度分为表级锁和行级锁

*  表级锁具有开销小、加锁快的特性；表级锁的锁定粒度较大，发生锁冲突的概率高，支持的并发度低。
 *  行级锁具有开销大、加锁慢的特性；表级锁的锁定粒度较小，发生锁冲突的概率低，支持的并发度高。

#### 2.3.4.2.1 行级锁 ####

InnoDB实现了两种类型的行锁

*  共享锁（S）：允许获得该锁的事务读取数据行 (读锁)，同时允许其他事务获得该数据行上的共享锁，并且阻止其他食物获得数据行上的排他锁
 *  排他锁（X）：允许获得该锁的事务更新或删除数据行（写锁），同时组织其他事务获取该数据行上的共享锁和排他锁

可以简单理解为：

*  共享锁;查询操作
 *  排他锁：更新、添加、修改操作

#### 2.3.4.2.2 表级锁 ####

InnoDB表存在两种表级锁，一种是LOCK TABLES语句手动指定的锁，另一种是由InnoDB自动添加的意向锁。

意向锁也可分为共享锁和排他锁：

*  意向共享锁（IS）：事务在给数据行添加行级共享锁之前，必须先取得该表的IS锁
 *  意向排他锁（IX）：事务在给数据行添加行级排他锁之前，必须先取得该表的IX锁

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>表锁类型</td> 
   <td>共享锁</td> 
   <td>排他锁</td> 
   <td>意向共享锁</td> 
   <td>意向排他锁</td> 
  </tr> 
  <tr> 
   <td>共享锁</td> 
   <td><span style="color:#1c7331;">兼容</span></td> 
   <td><span style="color:#fe2c24;">冲突</span></td> 
   <td><span style="color:#1c7331;">兼容</span></td> 
   <td><span style="color:#fe2c24;">冲突</span></td> 
  </tr> 
  <tr> 
   <td>排他锁</td> 
   <td><span style="color:#fe2c24;">冲突</span></td> 
   <td><span style="color:#fe2c24;">冲突</span></td> 
   <td><span style="color:#fe2c24;">冲突</span></td> 
   <td><span style="color:#fe2c24;">冲突</span></td> 
  </tr> 
  <tr> 
   <td>意向共享锁</td> 
   <td><span style="color:#1c7331;">兼容</span></td> 
   <td><span style="color:#fe2c24;">冲突</span></td> 
   <td><span style="color:#1c7331;">兼容</span></td> 
   <td><span style="color:#1c7331;">兼容</span></td> 
  </tr> 
  <tr> 
   <td>意向排他锁</td> 
   <td><span style="color:#fe2c24;">冲突</span></td> 
   <td><span style="color:#fe2c24;">冲突</span></td> 
   <td><span style="color:#1c7331;">兼容</span></td> 
   <td><span style="color:#1c7331;">兼容</span></td> 
  </tr> 
 </tbody> 
</table>

LOCK TABLES实际上是mysql服务器层的功能，而不是InnoDB存储引擎的功能，所以其他的存储引擎也可以通过LOCK TABLES语句来添加表级锁。

为什么InnoDB还要再自己设计一种实现锁的方式？

看如下场景：

> user表：一百万行数据
> 
> 有事务A在user表的N行数据上了锁（排他锁、共享锁都可以）
> 
> 这时有个事务B，想要在user表中加个字段，修改user表结构。因为要修改整个表，所以肯定要加排他锁。
> 
> 事务B首先要判断user表里有没有行锁。通过遍历user表的每一行，看看哪行有锁。如果整个表中没有行锁，那事务B就可以加锁并修改表结构。
> 
> 但是遍历一百万，很费时间
> 
> 因此InnoDB就采用了一种策略：加一个表级的锁——意向锁
> 
> 其他事务想要修改表，直接判断表有没有意向锁，有意向锁代表表中有行锁；没有意向锁就可以修改表，同时在表上添加意向锁。

####  ####

[3cac30ffda034a3793ebc6d41b45f808.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/9fffae701a284c90a30cb1697f53f3f2.png
[d397d1253cea4b7890f9e7bae6bd36d7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/82a3afa83d594a3f883ca83245c6930f.png
[100d80263f7545a19fa769aceeb2e9ac.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/1b368ccf667c4cd6a8440471cff0b82a.png
[6979cfb909314a1898c4d9e37ac3a80b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/51c144e44b634bffad096f4418e0cbb8.png
[6a6eab7b1fd3487ab06125c2dfc26bc6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/69b19eb01cfd4e409af721743aba990c.png
[389040ac8a0b436d9eca20cde61c6053.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a127d776fa734651aa5ef586ca5355a2.png
[6164a4736f064ebab099bedb5f80089f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c3fb58e2d015464eaca55da870c08032.png
[de92dca1909a402282943bbbfeb5cef0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c1dccd43bd87490390bbeb2c4351fa1d.png
[2b4689b2b1204040bc34ed451e0827d8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c033aae608c547f6ae1df8fde9dd4e2c.png
[7de6b31cf86444fc98afe7c80322e26a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/6780d5c1f97445208e8cf89cfb2c954a.png
[219f7667e5784c5ab4e3750a2a815630.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/51684b1e9d1f461e935dfdf4450145d2.png
[B_B_ - Assassin_ -]: https://www.cnblogs.com/lianzhilei/p/11250589.html
[c139397a670f4bc0b0a87fab125584eb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/deb454ba01f64cd3a68b8b799a2923ef.png
[9e6ee7e51b874605ac7922130151a768.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/925a5a3e39624a91bbd24b06d3633934.png
[a28f9f2150c94413b58f0be16b1da748.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/54f04dacd23a4cb1ae58edfcc27176ef.png
[20c793b9539d44fca1562145909e12ec.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/14135fc3f4a24ed3867fa47aba3ab902.png
[ffec881e32db49a9ba95b1837fe6962d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/4af80fbb2db048e88a0ec6786763c23a.png
[b8e3a2ff171c4a098394e4957b07877c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/25d26cfd635b45b5bf14a7699091ad1d.png
[7c11a0deeae247b0ae9f185831a30f0b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/33c89e095c064505a771824bceb93e10.png
[39a62cbfd4d040598aa531c37ec562fe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/3a6630ee4f9f4158827451ababf822cd.png
[MySQL _ MySQL 8.0 Reference Manual _ 8 Optimization]: https://dev.mysql.com/doc/refman/8.0/en/optimization.html
[c66cbe107aeb4c8f9a22feb26a823dbf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/85b3f4bcae444d02acc1574e37fcb584.png
[51cfa4826d02446e9249a32054711ba4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/bfdc049d4cf84297baabc0aa3e801e60.png
[2539183ee5674bd6bb038b05cb6b10d3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/50978594032046ff9cc806ce5f6bd72d.png
[135397feb8434c448defa7a3b583f7dc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/12f88fcd6bc24a5e955d5bac68ad0226.png
[76d841e2c8f64771916fa40caf6a31a2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/3cc58353b1574cb693c98a548e907588.png
[64673928d81447329bb511c487c0a386.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a59d90ffccab47eb84573a44a6404b03.png
[5cb5a0f095c94b51bd72bf88fc9a0d51.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/ac3bca927eb84593a7a7276597b6c3a9.png
[9ed4397374e1489aad119c42dc43e67a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/91a0a1ce408c47deac49cc19165c5836.png
[c421bfb95f5f494a855c65a928018f22.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/0fc334f7bbef4a4297934dfbb9d2e1c9.png
[9de5fe358a9d4d5b96550a99fb3e9ee6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/1aee2e96be27484090659b885906eb44.png
[3b0f99c4cf9d4af9abd0cc14d50701d4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/907d0fabb7304bacb3cdb6fd4f98dffe.png
[af026469e7874f29aeaaa339e0af5b72.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/84eab16196124c54acdb9b13445bc015.png
[0333d9e12fce41e5a9b965de10d89915.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/1a7c844ce63d4d92bf7049851449add6.png
[9e6aa984b4f04515a265139a4a11aeb2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/6f3beb991d814dee96bf06c912e7d23a.png
[fbfc4b99c4134c4585520dcb26b76805.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a475b76cab3e41dd916213707ac2a926.png
[81bad726558b46b4b3258bf4e7d12be0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/40ca65080d33450b9d831b5e0ddac801.png