欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

MySQL基础学习笔记

程序员文章站 2022-03-08 17:52:15
...

文章目录

一. MySQL 基础

1 MySQL 简介

1.1 MySQL 背景

前身属于瑞典的一家公司，MySQL AB
08年被sun公司收购
09年sun被oracle收购

1.2 MySQL 优点

1、开源、免费、成本低
2、性能高、移植性也好
3、体积小，便于安装

1.3 MySQL 安装

1.3.1 Windows 安装

略

1.3.2 centos7 安装

  1. 从网上下载rpm包
    一共需要4个
    https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-community-server-5.7.26-1.el7.x86_64.rpm
    https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-community-client-5.7.26-1.el7.x86_64.rpm
    https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-community-common-5.7.26-1.el7.x86_64.rpm
    https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-community-libs-5.7.26-1.el7.x86_64.rpm

  2. 将下载好的包上传到服务器

  3. 安装

    rpm -ivh mysql-community-****.rpm --force --nodeps

    安装顺序 common–>libs–>client–>server

  4. 配置

    1. 启动mysql服务:

      service mysqld start

    2. 找到初始密码:

      cat /var/log/mysqld.log | grep password

    3. 登录MySQL

    4. 先修改一个比较复杂的密码随后再改

      alter user 'root'@'localhost' identified by 'newpasswd';

    5. 修改允许简易密码

      set global validate_password_policy=LOW; # 等级low
set global validate_password_length=6; # 长度六位

    6. 此时就可以修改简易密码了

      alter user 'root'@'localhost' identified by 'newpasswd';

    7. 允许远程访问

    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'newpasswd' WITH
GRANT OPTION;

    1. 关闭防火墙

      # 单次关闭
systemctl stop firewalld.service
# 永久关闭
systemctl disable firewalld.service

    2. 设置开机自启

    vi /etc/rc.local

    在文件中添加 service mysqld start即可。

2 SQL 语言

2.1 SQL 语言简介

SQL—Structured Query Language 结构化查询语言

  1. 是一种所有关系型数据库的查询规范，不同的数据库都支持。
  2. 通用的数据库操作语言，可以用在不同的数据库中。
  3. 不同的数据库SQL语句有一些区别。

2.2 SQL 语言分类

  1. DQL 数据查询语言（Data Query Language）
  2. DML数据操纵语言（Data Manipulation Language）
  3. DDL数据定义语言（Data Definition Language）
  4. DCL 数据控制语言（Data Control Language）
  5. TCL 事务控制语言（ Transaction Control Language ）

2.3 DQL 语言

2.3.1 基础查询

2.3.1.1 语法
select 查询列表            7
from 表1 别名             1
连接类型 join 表2          2
on 连接条件                3
where 筛选                4
group by 分组列表          5
having 筛选               6
order by排序列表           8
limit 起始条目索引，条目数;  9
2.3.1.2 特点
  1. 查询列表可以是字段、常量、表达式、函数，也可以是多个。
  2. 查询结果是一个虚拟表。
2.3.1.3 示例

  1. 查询单个字段

    select 字段名 from 表名;

  2. 查询多个字段

    select 字段名，字段名 from 表名;

  3. 查询所有字段

    select * from 表名;

  4. 查询常量

    select 常量值;

    注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要

  5. 查询函数

    select 函数名(实参列表);

  6. 查询表达式

    select 100/1234;

  7. 起别名

    select 字段名 as 别名 from 表名 as 别名;

  8. 去重

    select distinct 字段名 from 表名;

    作用：做加法运算

    select 数值+数值; # 直接运算


    select 字符+数值; # 先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算


    select null+值; # 结果都为null

  10. [补充]concat函数
    作用: 拼接函数

    select concat(字符1，字符2，字符3,...);

  11. [补充]ifnull函数
    功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值

    select ifnull(commission_pct,0) from employees;

  12. [补充]isnull函数

    功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0

2.3.2 条件查询

2.3.2.1 语法
select 查询列表 from 表名 where 筛选条件;
2.3.2.2 筛选条件

  1. 简单条件运算符
    > < = <> != >= <= <=>安全等于

  2. 逻辑运算符
    && and || or ! not

  3. 模糊查询
    like:一般搭配通配符使用，可以判断字符型或数值型
    通配符：%任意多个字符，_任意单个字符

  4. 区间判断
    between and
    in
    is null /is not null：用于判断null值

2.3.3 排序查询

2.3.3.1 语法
select 查询列表 from 表 where 筛选条件
order by 排序列表 [asc|desc]
2.3.3.2 特点
  1. asc ：升序，如果不写默认升序 desc：降序
  2. 排序列表 支持 单个字段、多个字段、函数、表达式、别名
  3. order by的位置一般放在查询语句的最后（除limit语句之外）

2.3.4 常见函数

2.3.4.1 概述

功能：类似于java中的方法

好处：提高重用性和隐藏实现细节

调用：select 函数名(实参列表);

2.3.4.2 单行函数

  1. 字符函数

    concat # 连接
substr # 截取子串
upper # 变大写
lower # 变小写
replace # 替换
length # 获取字节长度
trim # 去前后空格
lpad # 左填充
rpad # 右填充
instr # 获取子串第一次出现的索引

  2. 数学函数

    ceil # 向上取整
round # 四舍五入
mod # 取模
floor # 向下取整
truncate # 截断
rand # 获取随机数，返回0-1之间的小数

  3. 日期函数

    now # 返回当前日期+时间
year # 返回年
month # 返回月
day # 返回日
date_format # 将日期转换成字符
curdate # 返回当前日期
str_to_date # 将字符转换成日期
curtime # 返回当前时间
hour # 小时
minute # 分钟
second # 秒
datediff # 返回两个日期相差的天数
monthname # 以英文形式返回月

  4. 其他函数

    version # 当前数据库服务器的版本
database # 当前打开的数据库
user # 当前用户
password('字符') # 返回该字符的密码形式
md5('字符') # 返回该字符的md5加密形式

  5. 流程控制函数

    if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2


    case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end


    case 
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
    2.3.4.3 分组函数
    max # 最大值
min # 最小值
sum # 和
avg # 平均值
count # 计算个数2.4 DML语言2.4.1 插入

2.4 DML 语言

2.4.1 插入

# 方式一
insert into 表名(字段名,...) values(值,...);
# 方式二
insert into 表名 set 字段=值,字段=值,...;

2.4.2 修改

update 表名 set 字段=值,字段=值 [where 筛选条件];

2.4.3 删除

delete from 表名 [where 筛选条件] [limit 条目数];


truncate table 表名

两种方式的区别

  1. truncate删除后，如果再插入，标识列从1开始;
    delete删除后，如果再插入，标识列从断点开始.
  2. delete可以添加筛选条件;
    truncate不可以添加筛选条件.
  3. truncate效率较高
  4. truncate没有返回值;
    delete可以返回受影响的行数.
  5. truncate不可以回滚;
    delete可以回滚.

2.5 DDL 语言

2.5.1 库的管理

创建库

create database [if not exists] 库名 
[character set 字符集名];

修改库

alter database 库名 character set 字符集名;

删除库

drop database [if exists] 库名;

2.5.2 表的管理

2.5.2.1 创建表
create table [if not exists] 表名(
	字段名 字段类型 [约束],
	字段名 字段类型 [约束],
	...
	字段名 字段类型 [约束]
)
2.5.2.2 修改表
# 添加列
alter table 表名 add column 列名 类型 [first|after 字段名];
# 修改列的类型或约束
alter table 表名 modify column 列名 新类型 [新约束];
# 修改列名
alter table 表名 change column 旧列名 新列名 类型;
# 删除列
alter table 表名 drop column 列名;
# 修改表名
alter table 表名 rename [to] 新表名;
2.5.2.3 删除表
drop table [if exists] 表名;
2.5.2.4 复制表
# 仅复制表结构
create table 表名 like 旧表;
# 复制表结构加数据
create table 表名 
select 查询列表 from 旧表 [where 筛选];

2.5.3 数据类型

2.5.3.1 数值型

  1. 整型
    tinyint 1
    smallint 2
    mediumint 3
    int/integer 4
    bigint 8

    特点

    1. 都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号
    2. 如果超出了范围，会报out or range异常，插入临界值
    3. 长度可以不指定，默认会有一个长度
    4. 长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型

  2. 浮点型
    定点数：decimal(M,D)
    浮点数:
    float(M,D) 4
    double(M,D) 8

    特点

    1. M代表整数部位+小数部位的个数，D代表小数部位
    2. 如果超出范围，则报out or range异常，并且插入临界值
    3. M和D都可以省略，但对于定点数，M默认为10，D默认为0
    4. 如果精度要求较高，则优先考虑使用定点数
2.5.3.2 字符型

char、varchar、binary、varbinary、enum、set、text、blob

char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1
varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略

2.5.3.3 日期型

year 年
date 日期
time 时间
datetime 日期+时间 8
timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间

2.5.4 常见约束

2.5.4.1 概述

NOT NULL：非空，该字段的值必填
UNIQUE：唯一，该字段的值不可重复
DEFAULT：默认，该字段的值不用手动插入有默认值
CHECK：检查，mysql不支持
PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not null
FOREIGN KEY：外键，该字段的值引用了另外的表的字段

2.5.4.2 创建表时添加约束
create table 表名(
	字段名 字段类型 not null, # 非空
	字段名 字段类型 primary key, # 主键
	字段名 字段类型 unique, # 唯一
	字段名 字段类型 default 值, # 默认
	constraint 约束名 foreign key(字段名) references 主表（被引用列）
)
2.5.4.3 修改表时添加或删除约束
# 非空约束
# 添加非空
alter table 表名 modify column 字段名 字段类型 not null;
# 删除非空
alter table 表名 modify column 字段名 字段类型 ;


# 默认约束
# 添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
# 删除默认
alter table 表名 modify column 字段名 字段类型 ;


# 主键约束
# 添加主键
alter table 表名 add [constraint 约束名] primary key(字段名);
# 删除主键
alter table 表名 drop primary key;


# 唯一约束
# 添加唯一
alter table 表名 add [constraint 约束名] unique(字段名);
# 删除唯一
alter table 表名 drop index 索引名;


# 外键约束
# 添加外键
alter table 表名 add [constraint 约束名] foreign key(字段名) references 主表（被引用列）;
# 删除外键
alter table 表名 drop foreign key 约束名;
2.5.4.4 自增长列

特点：

  1. 不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
    auto_increment_increment
    如果要更改起始值：手动插入值
    如果要更改步长：更改系统变量
    set auto_increment_increment=值;
  2. 一个表至多有一个自增长列
  3. 自增长列只能支持数值型
  4. 自增长列必须为一个key
# 创建表时设置自增长列
create table 表(
	字段名 字段类型 约束 auto_increment
)


# 修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment


# 删除自增长列
alter table 表 modify column 字段名 字段类型 约束

2.6 TCL 语言

2.6.1 概述

事务:一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行.

2.6.2 特点

A 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行
C 一致性：一个事务可以使数据从一个一致状态切换到另外一个一致的状态
I 隔离性：一个事务不受其他事务的干扰，多个事务互相隔离的
D 持久性：一个事务一旦提交了，则永久的持久化到本地

2.6.3 事务的隔离性

并发访问的问题 含义
脏读 一个事务读取到了另一个事务尚未提交的数据
不可重复读 一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取的数据是一致的，这是事务中update时引发的问题
幻读 一个事务中两次读取数据的数量不一致，要求在一个事务中多次读取的数据数量是一致的，只是事务中insert或delete时引发的问题

2.6.4 MySQL的隔离级别

名字 隔离级别 脏读 不可重复读 幻读 数据库默认隔离级别
读未提交 read uncommitted 是 是 是
读已提交 read committed 否 是 是 Oracle；SQL Server
可重复读 repeatable read 否 否 是 MySQL
串行化 serializable 否 否 否

二. MySQL进阶

1 视图

1.1 概述

本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。

好处：

  1. 简化sql语句
  2. 提高了sql的重用性
  3. 保护基表的数据，提高了安全性

1.2 创建

create view 视图名
as
查询语句;

1.3 修改

# 方式一：
create or replace view 视图名
as
查询语句;
# 方式二：
alter view 视图名
as
查询语句

1.4 删除

drop view 视图1，视图2,...;

1.5 查看

desc 视图名;
show create view 视图名;

2 变量

2.1 系统变量

# 查看系统变量
show [global|session] variables like '';
#如果没有显式声明global还是session，则默认是session


# 查看指定的系统变量的值
select @@[global|session].变量名; 如果没有显式声明global还是session，则默认是session


# 为系统变量赋值
# 方式一：
set [global|session] 变量名=值; 
# 如果没有显式声明global还是session，则默认是session
# 方式二：
set @@global.变量名=值;
set @@变量名=值；

  1. 全局变量
    服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效

  2. 会话变量
    服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）

2.2 自定义变量

说明：

  1. 用户变量
    作用域：针对于当前连接（会话）生效
    位置：begin end里面，也可以放在外面

    # 声明并赋值
set @变量名=值;
set @变量名:=值;
select @变量名:=值;


    # 更新值
# 方式一：
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
# 方式二：
select xx into @变量名 from 表;


    # 使用
select @变量名;

  2. 局部变量

    作用域：仅仅在定义它的begin end中有效
    位置：只能放在begin end中，而且只能放在第一句

    # 声明
declare 变量名 类型 [default 值];


    # 赋值或更新
# 方式一：
	set 变量名=值;
	set 变量名:=值;
	select @变量名:=值;
# 方式二：
	select xx into 变量名 from 表;


    # 使用
select 变量名;

3 存储过程

3.1 概述

存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用。

存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中以便以后调用，这样可以提高代码的执行效率。

3.2 创建

DELIMITER $$

CREATE
    PROCEDURE `db1`.`count_view`() # '数据库名'.'存储过程名'(可放参数)
    BEGIN
    # 此处填写代码块
    END$$

DELIMITER ;

注意：

  1. 参数模式：in、out、inout，其中in可以省略
  2. 存储过程体的每一条sql语句都需要用分号结尾

3.3 调用

call 存储过程名(实参列表);

举例：

# 调用in模式的参数
call sp1（'值'）;


# 调用out模式的参数
set @name; 
call sp1(@name);
select @name;


# 调用inout模式的参数
set @name=值; 
call sp1(@name); 
select @name;

3.4 查看

show create procedure 存储过程名;

3.5 删除

drop procedure 存储过程名;

4 函数

4.1 创建

CREATE FUNCTION sp_name ([param_name type[,...]]) 
        RETURNS type -- 定义返回值类型
    BEGIN 
    	routine_body
       return sth ；
    END

4.2 调用

select 函数名(实参列表);

4.3 查看

show create function 函数名;

4.4 删除

drop function 函数名;

5 触发器

5.1 概述

  1. 触发器是一种特殊类型的存储过程，不由用户直接调用，而且可以包含复杂的 SQL语句。它们主要用于强制复杂的业务规则或要求。
  2. 触发器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。

5.2 特点

  1. 它与表紧密相连，可以看作表定义的一部分；
  2. 它不能通过名称被直接调用，更不允许带参数，而是当用户对表中的数据进行修改时，自动执行；
  3. 它可以用于MySQL约束、默认值和规则的完整性检查，实施更为复杂的数据完整性约束。

5.3 语法

CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event 
ON tbl_name FOR EACH ROW 
    BEGIN 
    routine_body
    END
# trigger_time: { BEFORE | AFTER } # trigger_event: { INSERT | UPDATE | DELETE }

注意事项：

  • 不能有返回值或返回结果集
  • MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作，以免递归循环触发

三. MySQL高级

1 常见的join查询

1.1 A、B共有信息

SELECT * FROM　A INNER JOIN B ON A.condition = B.condition;

MySQL基础学习笔记

1.2 A独有+A、B共有信息

SELECT * FROM　A LEFT JOIN B ON A.condition = B.condition;

MySQL基础学习笔记

1.3 B独有+A、B共有信息

SELECT * FROM　A RIGHT JOIN B ON A.condition = B.condition;

MySQL基础学习笔记

1.4 A独有

SELECT * FROM　A LEFT JOIN B ON A.condition = B.condition WHERE B.condition is null;

MySQL基础学习笔记

1.5 B独有

SELECT * FROM　A RIGHT JOIN B ON A.condition = B.condition WHERE A.condition is null;

MySQL基础学习笔记

1.6 A、B全有

# left join + union(可去除重复数据)+ right join
SELECT * FROM　A LEFT JOIN B ON A.condition = B.condition
UNION
SELECT * FROM　A RIGHT JOIN B ON A.condition = B.condition;

MySQL基础学习笔记

1.7 A独有、B独有

SELECT * FROM　A LEFT JOIN B ON A.condition = B.condition WHERE B.condition is null
UNION
SELECT * FROM　A RIGHT JOIN B ON A.condition = B.condition WHERE A.condition is null;

MySQL基础学习笔记

2 索引

2.1 概述

MySQL官方定义: 索引（Index）是帮助MySQL高效获取数据的数据结构。

简单理解: 排好序的快速查询数据结构。

一般来说索引本身也很大，不可能全部存储在内存中，往往以索引文件的形式存储在硬盘上。

我们平常说的索引，如果没有特别指明，都是指BTree组织结构索引。

2.2 功能

类似大学图书馆建图书索引，提高数据的检索效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

同时，索引实际也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。虽然提高了查询速度，但会降低更新表的速度。

索引只是提高效率的一个因素，如果你的MySQL有大量的表，就需要花时间研究建立最优秀的索引。

2.3 MySQL索引分类

  1. 单值索引: 一个索引只包含单个列，一个表可以有多个单值索引；
  2. 唯一索引: 索引列的值必须唯一，但允许有空值；
  3. 复合索引: 一个索引包含多个列

2.4 语法

  1. 创建

    # 方式一:
CREATE [UNIQUE] INDEX indexName ON tableName (columnName(length)); 
# 如果是char或varchar类型，length可以小于实际长度，如果是BLOB和TEXT类型必须指定length

# 方式二：
ALTER tableName ADD [UNIQUE] INDEX indexName ON (columnName(length));

  2. 删除

    DROP INDEX　indexName ON tableName;

  3. 查看

    SHOW INDEX FROM tableName;

2.5 索引的创建条件

  1. 主键自动建立唯一索引；
  2. 频繁作为查询条件的字段应该建立索引；
  3. 查询中与其他表关联的字段，外键关系建立索引；
  4. where条件里用不到的字段不创建索引；
  5. 单键\组合索引的选择问题，who（在高并发下倾向创建组合索引）；
  6. 查询中排序的字段，排序字段若通过索引去访问，则会大大提高排序速度；
  7. 查询中统计或分组字段。

2.6 索引正确使用

  1. 建立几个复合索引字段，最好就用上几个字段。且按照顺序来用；
  2. 最佳左前缀法则，是指查询从索引的最左前列开始并且不跳过索引中的列；
  3. 不在索引上做任何操作（计算、函数、手动或自动类型转换），会导致索引失效；
  4. 存储引擎不能使用索引中范围条件右边的列，若中间索引列用到了范围（> < like），则后面的索引全失效；
  5. 尽量使用覆盖索引（只访问索引的查询），减少select *；
  6. Mysql在使用不等于(!=、<>)或like的左模糊的时候无法试用索引会导致全表扫描；
  7. IS NULL和IS NOT NULL也无法使用索引；
  8. 字符串不加单引号索引失效；
  9. 少用or，用它来连接时索引会失效。

[优化口诀]

​ 全值匹配我最爱，最左前缀要遵守；

​ 带头大哥不能死，中间兄弟不能断；

​ 索引列上少计算，范围之后全失效；

​ LIKE百分写最后，覆盖索引不写星；

​ 不等空值还有OR，索引失效要少用；

​ VAR引号不可丢，SQL高级也不难。

3 使用EXPLAIN

3.1 概述

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。

EXPLAIN 包含以下部分

  1. id:选择标识符
  2. select_type:表示查询的类型。
  3. table:输出结果集的表
  4. partitions:匹配的分区
  5. type:表示表的连接类型
  6. possible_keys:表示查询时，可能使用的索引
  7. key:表示实际使用的索引
  8. key_len:索引字段的长度
  9. ref:列与索引的比较
  10. rows:扫描出的行数(估算的行数)
  11. filtered:按表条件过滤的行百分比
  12. Extra:执行情况的描述和说明

3.2 id

select查询的***，包含一组数字，表示查询中执行select子句或操作表的顺序

有三种情况

  1. id相同，执行顺序由上至下；
  2. id不相同，如果是子查询，id的序号会递增，id值越大的优先级越高，越先被执行；
  3. id有相同有不同，先执行序号大的，然后顺序执行。

3.3 select_type

  1. SIMPLE(简单SELECT，不使用UNION或子查询等) ；
  2. PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY) ；
  3. UNION(UNION中的第二个或后面的SELECT语句) ；
  4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询) ；
  5. UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select) ；
  6. SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询) ；
  7. DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询) ；
  8. DERIVED(派生表的SELECT, FROM子句的子查询) ；
  9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

3.4 table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称。

3.5 type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

  1. ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行；
  2. index: Full Index Scan，index与ALL区别为index类型只遍历索引树；
  3. range:只检索给定范围的行，使用一个索引来选择行；
  4. ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值；
  5. eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件；
  6. const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system；
  7. NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

3.6 possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）。

3.7 Key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中。

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

3.8 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）。

不损失精确性的情况下，长度越短越好。

3.9 ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

3.10 rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

3.11 Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

  1. Using where：不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤；
  2. Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by；
  3. Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
  4. Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
  5. Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
  6. Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。
  7. No tables used：Query语句中使用from dual 或不含任何from子句。
相关标签： MySQL MySQL学习笔记 数据库 数据库基础

上一篇： 原子操作类——CAS原理详解（二）

下一篇： 原子操作CAS

推荐阅读