mysql md_mysql.md

| cls | 清屏 |

| --------------------------------------- | ---------------------------------------- |

| cd\ | 返回根目录 |

| md f1 | 在当前目录创建f1文件夹 |

| cd f1 | 进入f1 |

| md f2 | 在当前目录创建f1文件夹 |

| cd f2 | 进入f2 |

| cd .. | 返回上一级目录 |

| rd f2 | 删除空文件夹f2 |

| rd /s f1 | 删除f1及其所有子文件和文件夹 |

| dir | 列出当前目录所有文件(夹) |

| dir /w | 列出当前目录所有文件(夹)的名称 |

| desc tongxunlu; | 查看名为tongxunlu的数据库的属性 |

| delete from tongxunlu where name='kai'; | 删除tongxunlu这个表中name的值为kai的数据 |

| drop table tongxunlu; | 删除tongxunlu这个表 |

| drop database kai; | 删除名为kai的数据库 |

| quit; | 退出mysql |

| exit; | 退出mysql |

DOS：

net start查看开启的服务

net stop+服务名称 关闭开启的服务

mysql -h 127.0.0.1 -u root -p

-h 需要登陆的IP地址

-u 表示登录mysql软件的用户名

-p 表示登录的密码

quit /EXIT 退出MySQL

*************************************

查看表结构

desc telphone;

删除表

drop table telphone;

删除数据库

drop database fddtest;

***

distinct 去重复

limit 限制 一个参数为行数 两个参数为从第几行开始多少行。

# MySQL基础

## 启动停止服务

net start MySQL 服务名

net stop MySQL 服务名

## 更改字符集

set names 类型 每次连接都重新设置

show variables like 'character_set_%'; 查看字符集

## MySQL常见数据类型

1. 整数

| 整数类型 | 字节 | 最小值(有符号/无符号) | 最大值(有符号/无符号) |

| :----------: | :--: | :----------------------: | :--------------------------------------: |

| TINYINT | 1 | -128/0 | 127/255 |

| SMALLINT | 2 | -32768/0 | 32767/65535 |

| MEDIUMINT | 3 | -8388608/0 | 8388607/1677215 |

| INT、INTEGER | 4 | -2147483648/0 | 2147483647/4294967295 |

| BIGINT | 8 | -9223372036854775808/0 9 | 9223372036854775807/18446744073709551615 |

2. 浮点型

在 MySQL 中单精度值使用 4 个字节，双精度值使用 8 个字节

3. 字符串型

char 如果不指定(M)则表示长度默认是 1 个字符。varchar 必须指定(M)。

4. 枚举

ENUM 最多可以有 65,535 个成员，需要 2 个字节存储。

空字符串错误值的索引值是 0。

NULL 值的索引是 NULL。

5. NULL

NULL 的判断只能用 is null,is not null

任何运算符,判断符碰到 NULL,都得 NULL

NULL 影响查询速度,一般避免使值为 NULL

## 数据库SQL分类

DDL：数据定义语言，用于定义数据库对象：数据库，表，列等，关键字create，drop，alter等

DML：数据操作语言，用于对数据库中的表中的数据进行增删改，关键字insert，delete，update。

DQL：数据查询语言，用于查询数据库中表的记录，关键字select，where等。

DCL：数据控制语言，用于定义数据库的访问权限和安全级别，及创建用户。关键字：grant、revoke、deny、savepoint、commit、rollback等。

创建数据库指定字符集：

create database db2 character set gbk;

创建数据库，判断是否存在，指定字符集：

create database if not exists db3 character set gbk;

修改数据库字符集：alter

alter database db3 character set utf8;

查询正在使用的数据库：

select database();

## 创建表结构

CREATE TABLE 表名称(

字段名 1 数据类型 1 主键 自增长,

字段名 2 数据类型 2 非空 默认值,

字段名 3 数据类型 3

)ENGINE=当前表格的引擎 AUTO_INCREMENT=自增长的起始值 DEFAULT CHARSET=表数据的默认字符集;

例子：

CREATE TABLE t_stu(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(100) NOT NULL,

gender CHAR NOT NULL DEFAULT '男'

)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

## 删除表结构

DROP TABLE 语句不能回滚

## 修改表结构

【】表示可以省略

### 重命名表明

alter table 表名 **rename** 新表名；

**rename** table 表名 to 新表名;

### 增加列

alter table 表名 **add** 【column】 列名 数据类型; #默认在最后

alter table 表名 **add** 【column】 列名 数据类型 after 某一列;

alter table 表名 **add** 【column】 列名 数据类型 first;

### 删除列

alter table 表名 **drop** 【column】 列名;

### 修改列类型位置

alter table 表名 **modify** 【column】 列名 数据类型;

alter table 表名 **modify** 【column】 列名 数据类型 after 某一列;

alter table 表名 **modify** 【column】 列名 数据类型 first;

### 修改列名

alter table 表名 **change** 【column】 列名 新列名 数据类型;

## 约束

### 数据完整性

数据的精确性和可靠性

### 四个方面

实体完整性：同一个表中不能存在两条完全相同的数据

域完整性：数据所在范围符合逻辑

引用完整性：引用的数据应该存在与被引用表中

用户自定义完整性：符合实际操作的行为；用户名唯一，密码不能为空

### 类型

#### 主键约束

唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。

#### 外键约束

保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系

当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。

删除外键时，关于外键列上的普通索引需要单独删除。

**当主表的记录被从表参照时，主表中被参考记录的删除和更新也会受到限制。**

1. **RESTRICT**：当主表的记录被从表参照时，主表的记录将不 允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。

2. **CASCADE**：级联操作

3. **SET NULL** ：主表delete、update的时候，子表会将关联记录的外键字段所在列设为null

4. **NO ACTION**：主表delete、update的时候，和**RESTRICT**相似

**ON DELETE SET NULL**(级联置空)：当外键设置了 SET NULL,当主表的相关记录删除时，从表对应的字段改为 NULL。

**ON DELETE CASCADE**(级联删除)：当外键设置了 CASCADE(级联)，当主表的相关记录删除时，从表对应的 行都删除了。

**ON UPDATE CASCADE ON DELETE RESTRICT**

删除外键约束：

ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;

删除外键列上的索引：

ALTER TABLE 表名称 DROP INDEX 外键列索引名;

#### 唯一键约束

指定table的列或列组合不能重复，保证数据的唯一性。不允许出现重复的值，但是可以为多个null。

#### 非空约束

确保当前列的值不为空值，非空约束只能出现在表对象的列上。

#### 检查约束

***MySQL 不支持 check 约束，但可以使用 check 约束，而没有任何效果；***

检查数据表中字段值有效性的一种手段

#### 默认值约束

当插入记录时，如果没有明确为字段赋值，则自动赋予默认值

## 索引

### 类型

- 普通索引

- 唯一性索引

- 主键索引：只有一个主键索引

- 全文索引：MySQL5.X 版本只有 MyISAM 存储引擎支持 FULLTEXT，并且只限于 CHAR、VARCHAR 和 TEXT 类型的 列上创建。

## 删除表数据

删除整张表的数据还可以使用 truncate 表名;

### 与delete的区别

truncate 相当于删除表再重建一张同名结构的表，操作后得到一张全新表，而 delete 是在原有表中删除数据。如果 决定清空一张表的数据，truncate 速度更快一些。

TRUNCATE 语句不能回滚

## 查询表

### 语法

SELECT查询列表

FROM表名或视图列表

​【WHERE 条件表达式】

​【GROUP BY 字段名 【HAVING 条件表达式】】

【ORDER BY 字段 【ASC|DESC】】

【LIMIT m,n】;

select 语句，可以包含 5 种子句：依次是 where、 group by、having、 order by、limit 必须照这 个顺序。

### 去重 DISTINCT

## MySQL运算符

集合：in (值，值，值...) not in(值，值，值...)

模糊查询：LIKE NOT LIKE，通配符：%表示 0-n 个字符，_下划线代表一个字符

NULL 值判断，is null 或 is not null

所有的运算符遇到 NULL 结果都是 NULL，除了<=>

MySQL 中字符串拼接要使用字符串函数实现(concat)

------

------

# MySQL架构与内部模块

**通信类型**

同步：发送请求实时等待，是阻塞的。

异步：请求后，等待数据到位再次唤醒，或再次请求

一般来说客户端连接数据库都是同步连接。

**连接方式**

长连接：操作完可以保持连接也可以复用

短连接：操作完就关闭连接

MySQL即支持短连接，也支持长连接。一般来说都是长连接，一般会把这个连接放到客户端的连接池中。

**查看当前MySQL当前有多少连接**

show GLOBAL STATUS LIKE 'Thread%';

**通信方式**

单工：数据单向传输

半双工：数据双向传输，但不能同时传输

全双工：数据双向传输，可以同时传输

MySQL使用半双工方式。

**参数级别**

MySQL参数分为session和global级别，即当前会话生效和全局生效。

### 一条查询的sql语句如何执行


#### 解析器

**词法解析**

将一个完整的sql语句拆分成一个个单词

**语法解析**

对sql做一些语法检查，如是否缺少符号，根据MySQL定义的语法规则和sql语句生成一个解析树。


#### 预处理器

一个sql语句无语法和词法错误，但查询的表不存在，是在解析时还是执行时报错(**解析时**)

检查生成的解析树，解决解析器无法解析的语义，如表名、列名等。**预处理后会得到一个新的解析树**

#### 优化器

一条sql语句可以有多种执行方式，最终返回相同的结果，他们之间是等价的。

**目的**

根据解析树生成不同的执行计划，然后选择最优的执行计划。

**做什么**

1. 多表联合查询，以那个表为基准表(先访问那个表)；

2. 多个索引可以使用时，选择那个索引。

3. 对于查询条件的优化，移除1=1这样的恒等式，或者恒不等式，表达式计算，子查询等。

**结果**

优化器最终会将解析树变成一个执行计划(但不一定是最优的)。

可以在sql语句中添加EXPLAN查询执行计划。

### 存储引擎

#### 文件结构

任何一个存储引擎都有一个frm文件，这个是**表结构定义文件**

不同的存储引擎存放数据的方式不一样，InnoDB是1个、Memory没有、MyISAM是2个。

#### MyISAM(3个文件)

- .frm 表结构定义文件

- .myd 数据文件

- .myi 索引文件

表级锁定限制了读写的性能。

特点：

- 支持表级别的锁。不支持事物。

- 拥有较高的查询和插入速度。

- 存储了表的行数。

#### InnoDB (2个文件)

- .frm 表结构定义文件

- .idb数据文件

特点：

- 支持事务、支持外键、

- 支持行级锁和表级锁

- 支持读写并发、写不阻塞读(MVCC)

- 特殊的索引存放方式、减少IO、提高效率。

#### Memory(1个文件)

特点：

把数据放在内存中，读写速度快。只适合做临时表。

### 架构分层


### 一条更新的sql语句如何执行

#### 缓冲池 Buffer Pool


##### 局部性原理

当磁盘中一块数据被读取时，它附近位置也会马上被读取到

##### 页

InnoDB设定了一个存储引擎从磁盘读取数据到内存的最小单位

操作系统中页的大小一般是4K

InnoDB中默认大小为16K

InnoDB使用了一种缓冲池技术，也就是吧磁盘中读取到的页放到内存区域里面。这个内存区域就是Buffer Pool

**脏页**

修改数据的时候，先修改缓冲池中的页。内存数据页和磁盘中数据不一致的时候，就是脏页。

**刷脏**

InnoDB后台有专门的线程把Buffer Pool中的数据写入磁盘，每隔一段时间就一次性把多个修改写入磁盘

#### redo Log


默认2个文件，每个48M

为了避免数据库宕机而导致数据非持久化的问题，InnoDB把所有对页面的修改操作专门写入一个日志文件中。

特点：

- InnoDB引擎实现的，并不是所有的存储引擎都有。支持崩溃恢复是InnoDB的一个特性。

- 物理日志，记录的是在某个数据页做了什么修改。

- 大小固定，会覆盖。

主要用于崩溃恢复。

#### undo log

记录事物发生之前的状态(不含select)，如果修改数据发生异常，可以通过undo log实现回滚操作(保证原子性)。

逻辑日志。

#### Binlog

binlog以事件的形式记录所有的DDL和DML语句。逻辑日志

可以用作主从复制和数据恢复

文件内容可以追加，没有固定大小。

##### 主从复制



过程：

1. 先从内存或磁盘中取出这条数据

2. 调用存储引擎修改数据

3. 将修改结果存入缓冲池(Buffer Pool)

4. 记录到redo log中 修改状态为prepare(准备、预备)

5. 告诉执行器可以提交了

6. 执行器将操作写入Binlog中

7. 提交事务

8. 讲redo log状态修改为commit

# MySQL索引

## 类型

普通索引：没有任何限制

唯一索引：要求键值不能重复

​主键索引：特殊的唯一索引，要求键值不能为空。

全文索引：针对较大的数据。只适用于文本类型的字段，如char、varchar、text

## 一张表可不可能没有索引

1. 如果定义了主键，那么InnoDB会选择主键作为聚集索引

2. 如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。

3. 如果没有唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

------

## 二叉查找树(BST)

既能实现快速查找，也能实现快速插入

问题：

查找的时间和树高相关，最坏情况下时间复杂度为O(n)。

## 平衡二叉树(AVL)

左旋、右旋。

左右子树深度差绝对值不能超过1.

节点中存储的数据为**索引键值、数据地址、左右子树引用。**

缺陷：

一个节点只存储了一个键值+数据+引用，会浪费大量空间。从索引中寻找需要的数据，需要访问更多节点，跟磁盘的交互次数就会过多，消费的时间也越多。

## 多路平衡查找树(B Tree)

分裂、合并

特点：

路数比关键字多1.

节点中存储的数据为**索引键值、数据地址、左右子树引用。**


## B+树


特点：

关键字数量和路数相同

只有叶子节点才存放数据

叶子节点构成有序链表结构。

优势：

- 扫库、扫表能力更强(只需要遍历叶子节点就行，不需要遍历整个B+Tree树)

- 磁盘读写能力更强(一个节点可以包含更多的数据，一次性加载的磁盘数据更多)

- 排序能力强(叶子节点构成了链表)

- 效率稳定(只有叶子节点上有数据，所以IO稳定)

## HASH索引

特点：

- 时间复杂度为O(1)，查询速度快。不能用于排序

- 查询数据时根据键值计算哈希码，所以他只能支持等值查询，不支持范围查询

- 字段重复值较多，会出现大量哈希冲突，效率降低

# 事务与锁机制

### 事务四大特性

ACID

- **原子性(Atomicity)**

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

- **一致性(Consistency)**

事务前后数据的完整性必须保持一致。

- **隔离性(Isolation)**

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

- **持久性(Durability)**

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

原子性在InnoDB里面通过**undo log**实现的。

持久性是通过redo log实现的。操作数据时，会先写到内存中的Buffer Pool里面，同时记录到redo log，如果刷脏前出现异常，重启后读取redo log。

### 事务并发问题

#### 脏读


另一个事务修改数据，**未提交**，其他事务查询到已经修改的数据。

#### 不可重复读


第一个事务读取到了第二个事务修改的数据(**已提交**)，导致前后读取数据不一致。

#### 幻读


一个事务前后两次读取数据不一致，是由于另一个事务**插入数据**操作的。

### 隔离级别

- 读未提交(read-uncommitted)

一个事务读取到其他事务未提交的数据，未解决任何问题

- 不可重复读(read-committed)

一个事务读取到其他事务已提交的数据，不能读取到其他事务未提交的数据，解决了脏读问题，但会出现不可重复读的问题。

- 可重复读(repeatable-read)

在同一事务里面多次读取的数据结果一致，解决了不可重复读的问题，但没有定义解决幻读的方法

- 串行化(serializable)

所有的事务串行执行，已经不存在事务的并发操作。解决了所有的问题。

### InnoDB隔离级别的支持

| 隔离级别 | 脏读 | 不可重复读 | 幻读 |

| :--------------------------: | :----: | :--------: | :------------: |

| 读未提交(read-uncommitted) | 可能 | 可能 | 可能 |

| 读已提交(read-committed) | 不可能 | 可能 | 可能 |

| 可重复读(repeatable-read) | 不可能 | 不可能 | 对InnoDB不可能 |

| 串行化(serializable) | 不可能 | 不可能 | 不可能 |

### LBCC(基于锁的并发控制)

读取数据的时候，锁定要操作的数据，不允许其他事务进行修改

### MVCC(多版本并发控制)

**MVCC解决的问题是读写互相不阻塞的问题**

**MVCC主要适用于Mysql的RC,RR隔离级别**

MVCC 是一种并发控制的方法，一般在数据库管理系统中，**实现对数据库的并发访问**。

在修改数据的时候给他建立一个备份或者叫快照，后面再来读取这个快照。

**核心思想**

可以查到这个事务开始之前已经存在的已提交的数据，即使他在后面被删除或者修改了。在这个事务之后插入的数据，这个事务也查询不到。

### Innodb中的隐藏列

| 列名 | 长度(字节) | 含义 | 作用 |

| ----------- | ------------ | -------------- | ------------------------------------------------------------ |

| DB_TRX_ID | 6 | 行的版本号 | 插入或更新行的最后一个事务的事务标识符。(删除视为更新，将其标记为已删除) |

| DB_ROLL_PTR | 7 | 行的删除版本号 | 写入回滚段的撤消日志记录(若行已更新，则撤消日志记录包含在更新行之前重建行内容所需的信息) |

| DB_ROW_ID | 6 | | 行标识(隐藏单调自增id) |

### MVCC下InnoDB是怎么工作的

1. 插入数据(insert):记录的版本号即当前事务的版本号


2. 在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。


3. 删除操作的时候，就把事务版本号作为删除版本号。


4、查询操作

在查询时要符合以下两个条件的记录才能被事务查询出来

- **删除版本号未指定或者大于当前事务版本号**，即查询事务开启后确保读取的行未被删除。(即事务id小的事务查询时，依然能读取到事务id大的事务所删除的数据行)

- **创建版本号小于或者等于当前事务版本号** ，就是说记录创建是在当前事务中(等于的情况)或者在当前事务启动之前的其他事物进行的insert。

***注意***

- Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.

原因是MVCC的创建版本和删除版本只要在**事务提交后**才会产生。

- 串行化由于是会对所涉及到的表加锁，并非行锁，自然也就不存在行的版本控制问题。

### InnoDB锁基本类型

#### 共享锁

也叫读锁。用select ...... lock in share mode；的方式手工加一把锁。

释放锁有两种方式：事务结束，即提交事务和结束事务。

#### 排它锁

它是用来操作数据的，又叫做读锁。只要一个事务获取了一行数据的排它锁，其他的事物就不能在获取这一行数据的共享锁和排它锁。

**增删改，都会默认加上一个排它锁。**

使用FOR UPDATE给一行数据手工加上一个排它锁。

释放锁有两种方式：事务结束，即提交事务和结束事务。

#### 意向锁(给表锁做标记)

- 当我们给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁。

- 当我们给一行数据加上排它锁之前，数据库会自动在这张表上面加一个意向排它锁。

反之

- 如果一个表上至少有一个意向共享锁，说明有其他事务给其中的某些数据行加上了共享锁。

- 如果一个表上至少有一个意向排它锁，说明有其他事务给其中的某些数据行加上了排它锁

无意向锁时：

当准备给一张表加上表锁时，必须判断是否有其他事务锁定了其中某些行。有，则不能加上表锁，反之可以。需要扫描整个表，效率低。

有意向锁时：

判断这张表上有无意向锁即可。

### 行锁锁的是什么

索引。InnoDB的行锁，就是通过锁住索引记录实现的。

### 行锁算法


#### 记录锁

对于唯一性索引(包括唯一索引和主键索引)使用等值查询。精确匹配到一条记录时，使用的就是记录锁。

如：where id = 1 ，4，7，10

#### 间隙锁

当查询的记录不存在，没有命中任何一个记录(record)，无论使用等值查询还是范围查询的时候，使用间隙锁。

如：where id >4 and id < 7,where id = 6.

***注***

- 间隙锁主要阻塞插入，**相同的间隙锁之间不冲突**。

- 间隙锁只在RR中存在。

#### 临建锁

当使用了范围查询，不仅仅命中了Record记录，还包含了Gap间隙，使用临建锁。是MySQL里默认的行锁算法，相当于记录锁加上间隙锁。

如：where id >5 and id < 9

锁住的是键和键左右两边的间隙


# 性能优化

## sql执行流程


通过这个图分析可以得到以下**优化思路**

## 1、连接——配置优化

问题：服务端连接数不够导致应用程序获取不到连接。

解决：

1. 服务器端，增加可用连接数

1. 修改配置参数增加可用连接，修改max_connnections的大小。

2. 及时释放不活动的连接

2. 客户端

引用连接池

## 2、缓存——架构优化

1. 缓存

1. 没有缓存会给数据库带来很大压力

2. 操作数据的速度也会收到影响

2. 集群、主从复制

1. 将数据写入master节点，而读的请求分担到slave节点。(读写分离)

2. 读写分离一定程度上减轻了数据库服务器的访问压力，注意主从数据一致性问题。

3. 分库分表

垂直分库，减少并发压力；水平分表，解决存储瓶颈

垂直分库：以**表**为依据，按照业务归属不同，将不同的**表**拆分到不同的**库**中。


垂直分表：以**字段**为依据，按照字段的活跃性，将**表**中字段拆到不同的**表**(主表和扩展表)中。


水平分库：以**字段**为依据，按照一定策略(hash、range等)，将一个**库**中的数据拆分到多个**库**中。


水平分表：以**字段**为依据，按照一定策略(hash、range等)，将一个**表**中的数据拆分到多个**表**中。


## 3、优化器——SQL语句分析与优化

### 慢查询日志 slow query log

记录sql语句中执行慢的语句

- 默认关闭

- 默认执行超过10秒记录到慢日志

### EXPLAIN执行计划

#### 为什么使用explain执行计划

通过EXPLAIN我们可以模拟优化器执行SQL查询语句的过程。通过这种方式分析语句或表的性能瓶颈。**使用于SELECT语句**

#### 1、id

查询序列编号，一个select有一个编号

id值相同时，从上往下查询；id值不同时，从大到小查询。

#### 2、select_type

查询类型

- SIMPLE：简单的SELECT语句(不包括UNION操作或子查询操作)

- PRIMARY：子查询语句中的主查询，也就是最外层的查询(即第一个select)

- SUBQUERY：子查询语句中的内层查询(即除了最外层select)

- DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询 。(子查询依赖于外层查询，要注意这是浪费时间的)

- DERIVED：用于 from 子句里有子查询的情况。

- UNION：UNION操作中，查询中处于内层的SELECT(内层的SELECT语句与外层的SELECT语句没有依赖关系)(基于UNION操作)

- DEPENDENT UNION：UNION操作中，查询中处于内层的SELECT(内层的SELECT语句与外层的SELECT语句有依赖关系)(基于UNION操作)

- UNION RESULT：UNION操作的结果，id值通常为NULL(基于UNION操作)

#### 3、table

当前查询所用到的表

#### 4、type

查询类型

##### type属性从优到最差

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

其中最常用的七种排序：

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

以上类型除了ALL都可以使用索引

**一般来说，得保证查询至少达到range级别，最好能达到ref。**

- system ：表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现

- const ：主键索引或者唯一索引与常数进行等值匹配，只能查询到一条数据。因为只匹配一行数据，所以很快

- eq_ref ：唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描

- ref ：非唯一性索引扫描,返回匹配某个单独值的行,它可能会找到多个符合条件的行，与eq_ref相比，里面的数据可以重复。

- range： 对索引进行范围查询，一般就是在你的where语句中出现了between、、in等的查询

- index ： Full Index Scan，Index与ALL虽然都是读全表，但index是从索引树中读取。

- ALL： Full Table Scan，遍历全表以找到匹配的行。(慢)

#### 5、possible_keys

可能用到的索引

#### 6、key

实际用的索引

#### 7、key_len

索引的长度(字节数)，跟索引的类型和长度有关

如：

utf8mb4编码下 varchar类型的索引长度：

utf8mb4一个字符占四个字节，变成字段额外需要两个字节，允许为空需要1个字节

#### 8、ref

显示索引的哪一列被使用了,如果可能的话是一个常数(const),哪些列或常量被用于查找索引列上的值

#### 9、rows

MySQL认为需要扫描多少行(数据或索引)才可以返回数据，是一个预估值，一般来说越少越好。

#### 10、Extra

执行计划给出的额外信息

- Using filesort：不是使用索引排序，用到了额外的排序。(**需要优化**)

- Using temporary：在查询的时候，需要做去重、排序之类的工作时，可能会用到临时表(**需要优化，如建立复合索引**)

- Using index：使用了覆盖索引，不需要回表

- Using where：使用了where过滤，表示存储引擎返回的记录并不是所有都满足查询条件，需要在server层过滤

- Using Index Condition：索引下推

- Using join buffer： 表明使用了连接缓存,如在查询的时候会有多次join,则可能会产生临时表

- impossible where：表示where子句的值总是false,不能用来获取任何元祖。

## 4、存储引擎

### 存储引擎选择

为不同的业务选择不同的存储引擎

查询插入操作多的业务，使用MyISAM

临时数据用Memory

常规并发大更新多的使用InnoDB

## 5、优化体系


### 如何优化

1. 创建索引或者联合索引

2. 改写sql

1. 使用小表驱动大表

2. 用join来代替子查询

3. not exist 转换成left join is null

4. or改成union

5. 使用UNION代替UNION，如果结果集允许重复的话

6. 大偏移的limit，先过滤在排序

3. 表结构(冗余、拆分，not null等)、架构优化

4. 业务层的优化，必要条件是否必须

一键复制

编辑

Web IDE

原始数据

按行查看

历史