Mysql

MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。

它把数据按"库—表—行"的方式组织,并提供了 DDL、DML、DQL、DCL 四类 SQL 语句来**定义结构、操作数据、查询数据和控制访问权限**。

传统方式把全部数据堆在一个大文件里,难以并发、查询慢且不易维护;MySQL 用表结构 + SQL 标准语句解决了"数据该怎么存、怎么查、怎么管"的痛点。
MySQL 技术笔记

1. 概述

MySQL 是一个关系型数据库管理系统,将数据按”库—表—行”的方式组织。它提供了 DDL、DML、DQL、DCL 四类 SQL 语句来定义结构、操作数据、查询数据和控制访问权限。相较于把全部数据堆在一个大文件里的传统方式,MySQL 用表结构加标准 SQL 语句解决了数据该如何存储、查询与管理的痛点。

2. SQL 分类

SQL 按功能可分为四大类,操作数据库前需先通过命令行连接:

分类 说明
DDL(数据定义语言) 操作表、库等结构(创建、修改、删除)
DML(数据操作语言) 增、删、改数据
DQL(数据查询语言) 查询数据
DCL(数据控制语言) 创建数据库用户、控制访问权限

在操作数据库之前,需要连接它,输入命令:mysql -u用户名 -p密码 连接数据库

3. DDL(数据定义语言)

2.1 查询数据库

show databases;

2.2 创建数据库

如果不存在则会创建,如果存在则不会创建

create database 数据库名字;

指定默认字符集

create database 数据库名字 default charset utf8mb4;

2.3 删除数据库

drop database 数据库名;

drop database if exists 数据库名;

2.4 使用数据库

use 数据库名

查询当前目录

select database();

2.5 查询所有表

show tables;

2.6 创建表

2.6.1 基本语法

create table 表名(
    字段1 字段1类型[],
    #id int comment '编号'
    #comment "1" <- 注释
    ...
)[表注释];
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
| idcard | text        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

如果有上图的表,则可以创建字段如下图所示

create table id(
    id int comment 'id',
    name varchar(20) comment 'name',
    age int comment 'age',
    gender varchar(10) comment 'gender',
    idcard text comment 'idcard'
);

用 int 表示整数

varchar(长度)表示字符

2.6.2 显示表结构

desc 表名;

show create table 表名;

CREATE TABLE `user_login` (
  `id` int NOT NULL AUTO_INCREMENT,
  `operation` varchar(20) NOT NULL,
  `operation_time` datetime NOT NULL,
  `operation_id` int NOT NULL,
  `operation_params` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3

2.7 数据类型

2.7.1 数值类型

类型 占用空间 说明
tinyint 1 byte 小整数(可选 unsigned 无符号)
smallint 2 byte 小整数
mediumint 3 byte 中等整数
int / integer 4 byte 整数
bigint 8 byte 大整数
float 4 byte 小数
double 8 byte 小数
decimal — 小数(如 double(4,1) → 长度 4、小数位 1)

2.7.2 字符类型

类型 说明
char 定长字符串
varchar 变长字符串
tinyblob 不超过 255 个字符的二进制数据
tinytext 短文本字符
blob 二进制长文本
text 文本长文本
mediumblob 二进制长文本
mediumtext 文本长文本
longblob 二进制长文本
longtext 文本长文本

二进制一般用于记录图片以及音频,但由于不方便管理,因此使用相对较少。

2.7.3 日期类型

类型 格式 占用空间
date YYYY-MM-DD 3 byte
time HH:MM:SS 3 byte
year YYYY 1 byte
datetime YYYY-MM-DD HH:MM:SS 8 byte
timestamp YYYY-MM-DD HH:MM:SS(最大到 2038 年) 4 byte

2.8 修改表

2.8.1 添加字段

ALTER TABLE 表名 ADD 新属性名 数据类型 [约束条件] [FIRST|AFTER] 已存在属性名;

MySQL 中常用的约束:
NOT NULL 约束:确保某列不能有 NULL 值。
DEFAULT 约束:当某列没有指定值时,为该列提供默认值。
UNIQUE 约束:确保某列中的所有值是不同的。
PRIMARY Key 约束:唯一标识数据库表中的各行/记录。
CHECK 约束:CHECK 约束确保某列中的所有值满足一定条件。
不做[FIRST|AFTER]的位置说明,在添加属性时MySQL会默认把新属性加入到表的最后一列。
如果我们想在第一列添加新的属性,只需做FIRST的位置说明。如果我们想在某一列后面添加新的属性,只需做AFTER的位置说明,然后注明你想让它添加在哪个属性的后面即可。

alter table 表名 add 新增内容;

alter table richu add [column] new_id int comment ‘new id’;
alter table richu add [column] new_id int comment ‘new id’ default ‘001’ after _id;

2.8.2 修改字段

alter table 表名 modify 字段名 新数据类型(长度);
alter table richu modify _id int(11);

alter table 表名 change 旧字段名 新字段名 类型;
alter table richu change Name rcName varchar(30);

2.8.3 删除字段

alter table 表名 drop 字段名

alter table richu drop Name;

2.8.4 修改表名

alter table 表名 rename [to] 新表名;

alter table richu rename rc;

2.8.5 删除表

drop table [if exists] 表名;

2.8.6 清空表

truncate table 表名;

2.9 练习:创建一张员工信息表

  1. 编号(数字)
  2. 员工工号(字符)
  3. 员工姓名(字符)
  4. 性别(字符)
  5. 年龄(无负数)
  6. 身份证(字符)
  7. 入职时间(年月日)

4. DML(数据操作语言)

3.1 添加数据(insert)

3.1.1 给指定字段添加数据

insert into 表名(字段1,字段2) values(值1,值2);

3.1.2 给全部字段添加数据

insert into 表名 values(值1,值2);

3.1.3 批量添加数据

insert into 表名(字段1,字段2) values(值1,值2),(值1,值2),(值1,值2),(值1,值2);

insert into 表名 values(值1,值2),(值1,值2),(值1,值2);

3.2 修改数据(update)

update 表名 set 字段1=值1,字段二=值2,...[where 条件];(在使用 where 时不用加上括号)

where 条件接 字段=值

如果不带 where 语句则会影响全局

在指定的字段中指定的值行修改 set 后面指定的字段的内容。

3.3 删除数据(delete)

delete from 表名 [where 条件];(在使用 where 时括号不用加)

如果不加 where 条件则会将整张表删除

5. DQL(数据查询语言)

4.1 基本查询

4.1.1 查询多个字段

多个字段

select 字段1,字段2... from 表名;

所有字段

select* from 表名;

4.1.2 在查询时起别名

select id as id1 from 表名;

select id id1 from 表名;

其中 id1 是别名

4.1.3 查询后去掉重复值

select distinct 字段 from 表名;

4.2 条件查询(where)

4.2.1 语法

select 字段列表 from 表名 where 条件

4.2.2 条件

运算符 说明
= 等于
> 大于
< 小于
>= 大于等于
<= 小于等于
is null 为空
is not null 不为空
<> 不等于
&& / and 并且
|| / or 或者
between 1 and 2 大于等于 1 并且小于等于 2
! / not 非
in(1,2,3) 满足 =1、=2、=3 中任意一个即可
like 占位符 模糊匹配:_(单个字符) %(匹配任意个字符,可写作 %X%)

一个下划线为一个字符,两个下划线则为两个字符。

4.3 聚合函数(count、max、min、avg、sum)

4.3.1 count(统计数量)

select count(字段) from 表名

不统计 null 值

4.3.2 max(最大值)

select max(字段) from 表名;

4.3.3 min(最小值)

select min(字段) from 表名;

4.3.4 avg(平均值)

select avg(字段) from 表名;

4.3.5 sum(求和)

select sum(字段) from 表名;

4.4 分组查询(group by)

where 是分组前进行过滤,having 是分组后进行过滤。

4.4.1 语法

select 字段列表 from 表名 [where 条件] group by 分组字段名 having [分组后的过滤条件];

4.4.2 选择分组

select 字段1,count(id) from 表名 group by id;

4.4.3 条件选择分组

select 字段1,avg(id) from 表名 group by id having count(*) >=3;

根据 id 分组,分组后进行过滤数量大于 3 的

4.5 排序查询(order by)

4.5.1 语法

select 字段名 from 表名 order by 字段名 asc;

select 字段名 from 表名 order by 字段名 desc;

select 字段名 from 表名 order by 字段名1 desc,字段名2 asc;

4.5.2 排序方式

关键字 说明
ASC 升序(默认,可省略)
DESC 降序

4.6 分页查询(Limit)

select 字段列表 from 表名 limit 起始索引,查询记录数;

起始索引从 0 开始,开始索引 =(查询页码 - 1)* 每页显示记录数

4.7 DQL 的执行顺序

4.7.1 书写顺序

select

​ 字段列表

from

​ 表名列表

where

​ 条件列表

group by

​ 分组字段列表

having

​ 分组后条件列表

order by

​ 排序字段列表

limit

​ 分页参数

4.7.2 执行顺序

  1. from
  2. where
  3. group by having
  4. select
  5. group by having
  6. limit

6. DCL(数据控制语言)

5.1 介绍

管理数据库用户、控制数据库的访问权限

5.2 用户管理

5.2.1 查询用户

localhost 本机

% 任意主机

use mysql;

select * from user;

用 win+R 进入 mysql

MySQL -u 用户名 -p

5.2.2 创建用户

create user 用户名 @主机名 identified by 密码;

5.2.3 修改用户密码

alter user 用户名@主机名 identified with 旧密码 by 新密码;

5.2.4 删除用户

drop user 用户名@主机名;

5.3 权限控制

5.3.1 权限对象范围

对象写法 含义
*.* 全部表
数据库名.* 指定数据库的所有表

5.3.2 权限关键字

权限关键字 说明
all / all privileges 所有权限
select 查询数据
insert 插入数据
update 修改数据
delete 删除数据
alter 修改表
drop 删除数据库
create 创建数据库

5.3.3 查询权限

show grants for 用户名@主机名;

5.3.4 授予权限

grant 权限列表 on 数据库名.表名 to 用户名@主机名;

5.3.5 撤销权限

revoke 权限列表 on 数据库名.表名 from 用户名@表名;

  1. 多个权限之间,用逗号分隔
  2. 授权时,数据库名和表名可以用 * 进行通配,代表所有。

7. 函数

6.1 字符串函数

6.1.1 concat(字符串拼接)

select concat("Hello","World");

6.1.2 lower(转为小写)

select lower("HELLO");

6.1.3 upper(转为大写)

select upper("hello");

6.1.4 lpad(左填充)

用字符串 pad 对 str 进行填充,达到 n 个字符串长度

select lpad(str,n,pad);

6.1.5 rpad(右填充)

用字符串 pad 对 str 进行填充,达到 n 个字符串长度

select rpad(str,n,pad);

6.1.6 trim(去掉头部和尾部的空格)

select trim(str);

6.1.7 substr(求子串)

从 start 开始的 len 个字符

select substr(str,start,len);

6.2 数值函数

6.2.1 ceil(向上取整)

select ceil(2.5); (=3)

6.2.2 floor(向下取整)

select floor(2.5); (=2)

6.2.3 mod(取模)

select mod(7,4);

6.2.4 rand(随机数)

select rand();

6.2.5 round(四舍五入,保留 y 位小数)

select round(2.5);

select round(2.5,0); (=3)

6.3 日期函数

6.3.1 curdate(年月日)

select curdate();

6.3.2 curtime(时分秒)

select curtime();

6.3.3 now(年月日时分秒)

select now();

6.3.4 year(当前年份)

select year(now());

6.3.5 month(当前月份)

select month(now());

6.3.6 day(当前日期)

select day(now());

6.3.7 date_add(增加时间)

interval(间隔)a
70(间隔时间)
day、month、year

select date_add(now(),interval 70 day );

6.3.8 datediff(第一个时间减去第二个时间)

select datediff('2025-3-26','2024-3-26');

6.4 流程函数

  1. if(value,t,f)
    如果 value 为 true 返回 t 否则返回 f
select if(true,9,0); ->9
select if(false,9,0); ->0
  1. ifnull(value1,value2)
    如果 value1 不为 null 则返回 value1,否则返回 value2
select ifnull('ok','default'); ->ok
select ifnull('','default'); ->''
select  ifnull(null,'default'); ->default
  1. case when [val] then [res] … else [default] end;
    如果 val 为 true,返回 res,…否则返回 default 默认值
select name,(case richu_user.gender when '男' then '0' when '女' then '1' else 'default' end) as '性别编号' from richu_user;
  1. case [expr] when [val] then [res1] .. res[default] end;
    如果 expr 的值等于 val,返回 res1,…否则返回 default 默认值
select name,(case  when date<=200 then '短' when date<= 500 then '中' else '长' end) '时间' from richu_user;

8. 约束

7.1 概念

作用于表中字段上的规则,用于限制存储在表中的数据

7.2 目的

保证数据库中数据的正确,有效性和完整性

7.3 分类

约束 关键字 说明
非空约束 not null 限制该字段数据不能为 null
唯一约束 unique 保证该字段的数据是唯一、不重复的
主键约束 primary key 主键是一行的标识,要求非空且唯一
默认约束 default 保存该数据时,如果未指定值则采用默认值
检查约束 check 保证字段满足一定条件
外键约束 foreign key 让两张表的数据之间建立联系,保证数据的一致性和完整性

7.3.1 非空约束(not null)

限制该字段数据不能为 null

name varchar(10) not null unique comment '姓名'

7.3.2 唯一约束(unique)

保证该字段的数据是唯一、不重复的

name varchar(10) not null unique comment '姓名'

7.3.3 主键约束(primary key)

主键是一行的标识,要求非空且唯一

id int primary key auto_increment comment '主键'

//auto_increment (自增)

7.3.4 默认约束(default)

保存该数据时,如果未指定值则采用默认值

status varchar(10) default '1' comment '状态'
//插入
insert into richu_user2(id,name,age) values (4,'d',40);

7.3.5 检查约束(check)

保证字段满足一条件

age int check(age >=0 and age <=100) comment '年龄'

7.3.6 外键约束(两张表)(foreign key)

让两张表的数据之间建立联系,保证数据的一致性和完整性

7.3.6.1 创建外键
create table 表名(
  ...
  [constraint] [外键名称]
  foreign key [外字段名]
  references 主表 [主表列表]
 );

例子:

# 主表
create table t_depart(
  dptno int primary key,
  dptname varchar(22)
);

#添加外键
create table t_student(
  stid varchar(12) primary key,name varchar(22),
  dptno int,
  constraint test
  foreign key(dptno) references t_depart(dptno));
7.3.6.2 建立外键关联

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主列表名);

7.3.6.3 删除外键

alter table 表名 drop foreign key 外键名称;

7.3.6.4 目的

维护数据的完整性和一致性

7.3.6.5 外键删除更新行为
  1. 删除/更新行为
  • no action:检查该记录是否存在对应外键,如果有则不允许删除/更新(与 restrict 相同)
  • restrict:检查该记录是否存在对应外键,如果有则不允许删除/更新(与 no action 相同)
  • cascade:检查该记录是否存在对应外键,如果有,则一起删除/更新
  • set null:检查该记录是否存在对应外键,如果有则子表中该外键的值为 null(这要求该外键允许取 null)
  • set default:父表有变动时,子表将外键列设置成一个默认的值(Innodb 不支持)
  1. 添加行为

alter table 表名 add constraint 外键名称 foreign (外键字段) references 主表名(主表字段名) on update cascade on delete cascade;

update -> 更新行为

delete -> 删除行为

9. 多表查询

8.1 多表关系

  • 多对多

多个表的主键通过一个中间表相关联

  • 一对一

多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段就放在另外一张表中,以提升操作效率

外键与主键一一对应,在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(unique)

8.2 多表查询概述

8.2.1 概述

指在多个表中查询数据

笛卡尔积:两个集合的所有组合情况

8.2.2 分类

1. 连接查询

内连接:相当于查询 A、B 交集部分数据

外连接:

​ 1. 左外连接:查询左表所有数据,以及两张表交集部分数据

​ 2. 右外连接:查询右表所有数据,以及两张表交集部分数据

自连接:

​ 当前表与自身的连接查询,自连接查询,自连接必须使用表别名

2. 子查询

8.3 内连接

8.3.1 隐式内连接

select 字段列表 from 表1,表2 where 条件

8.3.2 显示内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件(inner 可以省略)

8.4 外连接

8.4.1 左外连接

select 字段列表 from 表1 left[outer] join 表2 on 条件...

8.4.2 右外连接

select 字段列表 from 表1 right[outer] join 表2 on 条件...

8.5 自连接

select 字段列表 表A 别名 join 表A 别名B on 条件...

自连接可以是内连接也可以是外连接

自连接必须使用别名

8.6 联合查询(union、union all)

select 字段列表 from 表A

union [all]

select 字段列表 from 表B;

union all 会将所有的数据都合并

select * from richu_user where age <20
union all
select * from richu_user where id <2; ->会有重复

union 会对合并之后的数据进行去重处理

select * from richu_user where age <20
union
select * from richu_user where id <2; ->去重

8.7 子查询

8.7.1 概念

sql 语句中嵌套 select 语句,称为嵌套查询,又称子查询

select * from t1 where column1=(select column from t2)

8.7.2 分类

  • 根据子查询结果

    • 标量子查询(结果为单个值)
    • 列子查询(子查询结果为一列)
    • 行子查询(子查询结果为一行)
    • 表子查询(子查询结果为多行多列)
  • 根据位置

    ​ where 之后、from 之后、select 之后

8.7.3 标量子查询

常用操作符 = <> >= < <=

select 字段列表 from 表=(select 字段列表 from 表 where 条件)

8.7.4 列子查询

常用操作符 in、not in、any、some、all

  • in:在指定的集合范围之内,多选一
  • not in:不在指定的集合范围之内
  • any:子查询返回列表中,有任意一个满足即可
  • some:与 any 等同,使用 some 的地方都可以使用 any
  • all:返回列表的所有值必须满足

select 字段列表 from 表 where 条件> all (select 字段列表 from 表 where 条件)

8.7.5 行子查询

常用操作符 =、<>、in、not in

select 字段列表 from 表 where (条件1,条件2) > all (select 字段列表 from 表 where 条件)

8.7.6 表子查询

常用操作符 in

用 select 语句查询的表作为一张新表并且进行连查

10. 事务

9.1 简介

事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功要么同时失败。

9.2 事务操作

  • 查看/设置事务提交方式

select @@autocommit;(查看当前事务提交方式)

0 -> 手动 1 -> 自动

set @@autocommit =0 ;(设置当前事务提交方式)

select @@autocommit; --注释

set @@autocommit = 0;
select @@autocommit;
  • 开启事务

start transaction 或 begin

  • 提交事务

成功则提交

commit;

  • 回滚事务

失败则回滚

rollback;

9.3 四大特性(ACID)

9.3.1 原子性

事务是不可分割的最小操作单元,要么全部成功,要么全部失败。

9.3.2 一致性

事务完成时,必须使所有的数据都保持一致。

9.3.3 隔离性

数据库系统提供的隔离机制,保证事务在不受到外部并发操作影响的独立环境下运行。

9.3.4 持久性

事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。

9.4 并发事务引发的问题

  1. 脏读:一个事务读到另外一个事务还没有提交的数据
  2. 不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读
  3. 幻读:一个事务按照条件查询数据时,没有对应的数据行,但是插入数据时,又发现这行数据已经存在,好像出现了一个幻影

9.5 事务隔离等级

隔离级别 脏读 不可重复读 幻读
read uncommitted 未解决 未解决 未解决
read committed 解决 未解决 未解决
repeatable read(默认) 解决 解决 未解决
serializable 解决 解决 解决

9.5.1 Read Uncommitted(读未提交)

特点:

  • 这是最低的隔离级别。
  • 一个事务可以读取另一个事务尚未提交的数据(脏读)。
  • 无法解决脏读、不可重复读、幻读问题。

9.5.2 Read Committed(读已提交)

特点:

  • 一个事务只能读取另一个事务已经提交的数据,避免了脏读。
  • 但同一个事务中,多次读取同一数据可能会看到其他事务提交后的不同结果(不可重复读)。
  • 无法解决不可重复读和幻读问题,但可以解决脏读问题。

9.5.3 Repeatable Read(可重复读)(默认)

特点:

  • 在同一个事务中,多次读取同一数据会看到相同的结果,即使其他事务提交了修改。
  • 避免了脏读和不可重复读,但可能出现幻读(同一查询条件,后续读取出现新数据行)。
  • 无法解决幻读问题,但可以解决脏读和不可重复读问题。

9.5.4 Serializable(可串行化)

特点:

  • 最高的隔离级别,完全隔离事务,避免脏读、不可重复读和幻读。
  • 事务串行执行,性能较低,易产生锁竞争。

查看事务隔离级别

select @@transaction_isolation;

更改事务隔离级别

set session transaction isolation level [read committed];

安全级别越高,性能越低

11. 存储引擎

10.1 MySQL 体系结构

  • 连接层
  • 服务层
  • 引擎层
  • 存储层

10.2 存储引擎简介

存储引擎就是存储数据、建立索引

查询建表语句

show create table 表名;

查询当前数据库支持的存储引擎

show engines;

设置创建表的引擎

在后面加上 engine=innoDB

10.3 存储引擎特点

10.3.1 InnoDB

10.3.1.1 特点
  • DML 操作遵循 ACID 模型,支持事务
  • 行级锁,提高访问性能
  • 支持外键 foreign key 约束,保证数据的完整性和正确性
10.3.1.2 文件

xxx.ibd:xxx 代表的是表名,innoDB 引擎的每一张表都会有一个表空间文件,存储该表的表结构(frm、sdi)、数据和索引。

打开 sdi 文件,先打开 cmd

ibd2sdi ibd文件

10.3.1.3 逻辑存储结构
  • tableSpace:表空间
  • segment:段
  • extent:区
  • page:页
  • row:行

10.3.2 MyISAM

10.3.2.1 特点
  • 不支持事务
  • 支持表锁,不支持行锁
  • 访问速度快
10.3.2.2 文件
  • xxx.sdi:存储表结构信息
  • xxx.myd:存储数据
  • xxx.myi:存储索引

10.3.3 Memory

10.3.3.1 特点
  • 内存存放
  • hash 索引(默认)
10.3.3.2 文件

xxx.sdi:存储表结构信息

innoDB 相对于 myisam 支持事务安全、支持锁机制(行级锁)、支持外键

Supports transactions, row-level locking, and foreign keys

10.4 存储引擎选择

在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎,对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB 引擎:InnoDB 是 MySQL 中的一种事务性存储引擎,它支持事务、行级锁和外键等特性。InnoDB 引擎适合于需要处理大量事务和并发操作的应用场景。
  • MyISAM 引擎:MyISAM 是 MySQL 中的一种非事务性存储引擎,它具备较高的插入和查询性能,适用于读操作远多于写操作的应用场景。
  • MEMORY 引擎:MEMORY 用到的很少,因为它是把数据存到内存中,如果内存出现异常就会影响数据。如果重启或者关机,所有数据都会消失。因此,基于 MEMORY 的表的生命周期很短,一般是一次性的。

12. 索引

11.1 索引概述

index

是帮助 MySQL 高效获取数据的数据结构(有序)。在数据之外,数据系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向数据),这样就可以在这些数据结构上实现高级查找算法,这些数据结构就是索引。

全表扫描 -> 无索引

二叉搜索树 -> 有索引

优势 劣势
提高数据检索的效率,降低数据库的 IO 成本 列索引也是要占用空间的
通过索引对数据进行排序,降低数据排序的成本,降低 CPU 的消耗 索引大大提高了查询效率,同时却也降低了更新表的速度,如对表进行 insert、update、delete 时,效率降低

11.2 索引结构

索引结构 描述
b+Tree 索引 最常见的索引类型,大部分引擎都支持 b+树
Hash 索引 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询
R-tree(空间索引) 空间索引是 MyISAM 引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少
Full-text(全文索引) 是一种通过建立倒排索引,快速匹配文档的方式,类似于 Lucene、Solr、ES
索引 InnoDB MyISAM MEMORY
b+Tree 索引 支持 支持 支持
Hash 索引 不支持 不支持 支持
R-tree(空间索引) 不支持 支持 不支持
Full-text(全文索引) 5.6 版本后支持 支持 不支持

11.2.1 b-tree(多路平衡查找树)

以一颗最大度数(max-degree)为 5(5 阶)的 b-tree 为例(每个节点最多存储 4 个 key,5 个指针)

每个结点的 key 顺序排列,如果大于最后一个 key 则指向最后一个指针,如果小于第一个 key 则指向第一个指针,如果在两个 key 之间则指向两个 key 之间的那个指针

pFTbQSI.jpg

11.2.2 b+tree

以一颗最大度数为 4(4 阶)的 b+tree 为例

所有元素都会出现在叶子结点,叶子结点组成了一个单向链表

pFTbGm8.png

MySQL 在 b+树的基础上增加了一个指向相邻叶子节点的链表指针

11.2.3 hash

哈希索引采用一定的 hash 算法,将键值换成新的 hash 值,映射到对应的槽位,然后存储在 hash 表中

特点:

  • hash 索引只能用于对等比较(=,in),不支持范围查询(between,>,<,…)
  • 无法利用索引完成排序操作
  • 查询效率高,通常只要一次检索

11.3 索引分类

分类 含义 特点 关键字
主键索引 针对于表中某数据列中的值重复 默认自动创建,只能有一个 primary
唯一索引 避免同一个表中某数据列的值重复 可以有多个 unique
常规索引 快速定位特定数据 可以有多个
全文索引 全文索引查找的是文本中的关键字,而不是比较索引中的值 可以有多个 fulltext

11.3.1 在 InnoDB 中

分类 含义 特点
聚焦索引(Clustered Index) 将数据存储于索引放到了一块,索引结构的用叶子结点保存了行数据 必须有,而且只有一个
二级索引(Secondary Index) 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 可以存在多个

聚集索引选取原则

  • 如果存在主键,主键索引就是聚集索引。
  • 如果不存在主键,将使用第一个唯一(unique)索引作为聚集索引。
  • 如果表没有主键,或没有合适的唯一索引,则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。

11.4 索引语法

11.4.1 创建索引

create [unique][fulltext] index index_name(索引名称) on table_name(字段名);

11.4.2 查看索引

show index from 表名;

11.4.3 删除索引

drop index 索引名 on 表名

11.5 SQL 性能分析

11.5.1 SQL 的执行频率

通过 show [session|global] status like ‘com_______’ 命令可以提供服务器状态信息

show global status like 'com_______'(7 个 _)

11.5.2 慢查询日志

慢日志记录了所有执行时间超过指定参数的所有 SQL 语句的日志。默认是未开启。

开启 MySQL 慢日志查询开关:

slow_query_log=1

设置慢日志的时间为 2 秒,SQL 语句的执行时间超过 2 秒,就会视为慢查询,记录查询日志

slow_query_time=2

配置完毕之后,通过以下指令重新启动 MySQL 服务器进行测试,查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log

systemctl restart mysqld(重新启动 MySQL 服务器)

show variables like 'slow_query_log'(查看日志)

tail -f localhost-slow.log(访问日志)

11.5.3 profile 详情

执行一系列的业务 SQL 的操作,然后通过如下指令查看指令的执行耗时

select @@have_profiling;(查看数据库是否支持 profile)

select @@profiling;(查看是否打开 profile)

set profiling = 1;(打开)

查看每一条 SQL 的耗时基本情况

show profiles;

查看指定 query_id 的 SQL 语句各个阶段的耗时情况

show profile for query query_id

查看指定 query_id 的 SQL 语句 CPU 使用情况

show profile cpu for query query_id

11.5.4 explain

explain 或者 desc 命令获取 MySQL 如何执行 select 语句的信息,包括在 select 语句执行过程中表如何连接和连接的顺序。

11.5.4.1 语法

explain select 字段列表 from 表名 where 条件

11.5.4.2 执行计划
  • id:select 查询的序列号,表示查询中执行 select 子句或者是操作表的顺序(id 相同,执行顺序从上到下;id 不同,值越大,越先执行)。
  • select_type:表示 select 的类型,最常见的取值有 simple(简单表,即不使用表连接或者子查询)、primary(主查询,即外层的查询)、union(union 中的第二个或者后面的查询语句)、subquery(select/where 之后包含了子查询)等。
  • type:表示连接类型,性能由好到差依次为 NULL、system、eq_ref、ref、range、index、all。
  • possible_key:显示可能应用在这张表上的索引,一个或者多个。
  • key:实际使用的索引,如果为 NULL,则没有使用索引。
  • key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际长度,在不损失精确性的前提下,长度越短越好。
  • rows:MySQL 认为必须要执行查询的行数,在 InnoDB 引擎的表中是一个估计值,可能并不是准确的。
  • filtered:表示返回结果的行数占需读取行数的百分比,filtered 的值越大越好。

11.6 索引使用规则

11.6.1 最左前缀法则

mysql 会从左向右,匹配直到遇到范围查询(>, <, between, like)就停止匹配(之后的不会用到索引)

比如建立(a,b,c,d)顺序的索引

使用这样的查询语句 a = 1 and b=2 and c>3 and d =4 ; d 是用不到索引的

但如果建立(a,b,d,c)的索引则都可以用到,a,b,d 的顺序可以调整

11.6.2 索引失效情况 1

    1. 要遵守最左前缀法则(最左边的字段必须存在,跳过某一字段后面字段索引失效)。
    1. 索引字段不能使用函数否则失效(不要使用运算操作)。
    1. 索引字段,字符串字段使用时,字符串不加引号,导致索引失效。
    1. 索引字段进行模糊查询时,如果是头部进行模糊查询,则索引失效,如果是尾部进行模糊查询,索引可正常使用。

11.6.3 索引失效情况 2

    1. 索引字段使用 or 时,or 的两边都必须有存在索引的字段,否则索引失效。
    1. 当查询时需要数据大于不需要数据时,索引失效,会全表扫描;当查询时需要数据小于不需要数据时,索引正常使用。

11.6.4 索引使用

11.6.4.1 SQL 提示

是优化数据库的一个重要手段,即在数据库中加入一些人为的提示来优化操作

use index:

use 表示建议使用某个索引(语法:select * from 表名 use index(索引名称) where .......)

ignore index:

ignore 表示建议不使用某个索引(语法:select * from 表名 ignore index(索引名称) where .......)

force index:

force 表示强制使用某个索引(语法:select * from 表名 force index(索引名称) where .......)

11.6.4.2 覆盖索引

查询语句中尽量少使用 “select *”,全表查询很容易造成回表查询(即:查询时当查询字段不存在已经使用过的索引时,会将查询到的二级索引上挂载的主键重新传回表中到聚集索引中利用主键 ID 再次进行查询查到整行数据(二级索引中挂载的是当前数据的 id,聚集索引挂载的是当前整行数据))

  • using where using index:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据。
  • using index condition:查找使用了索引,但是需要回表查询。
11.6.4.3 前缀索引

查询语句时如果当前字段储存的数据太长建立索引耗费空间太大,可以利用前缀索引进行对数据的前几个字符进行截取,从而利用少量空间来建立索引,加速查询(较长字符串、文本时使用)。

create index idx_xxx on table_name(column(n));

  • n 代表的是使用前 n 个字符建立索引。

前缀长度

可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一的选择性是 1,这是最好的索引选择性,性能也是最好的。

select count(distinct richu) / count(*) from richu_user

select count(distinct substring(richu,1,10)) / count(*) from richu_user

substring(截取目标,开始位置,长度)

11.6.4.4 联合索引和单列索引的选择

当存在多个查询字段时最好使用联合索引,单列索引很容易造成回表查询。

多个查询的字段都有单列索引,此时查找会造成回表查询,此时最好创建联合索引

(尽量使用联合索引)

11.6.4.5 索引设计原则
    1. 针对数据量较大、数据查询较为频繁的表建立索引。
    1. 针对常用于查询条件、分组、排序操作的字段(where、group by、order by)建立索引。
    1. 尽量针对区分度高的字段创建索引,比如(学生表中的学生 id、身份证等等),区分度越高,索引查询效率也就越高。
    1. 如果字符串类型的字段,字符串较长可以建立前缀索引,截取前几个字段来建立索引提高查询效率,这里要注意的是选择性(表中截取前面的字段数据总数(去重)/ 表中不截取的字段数据总数,这个值越接近于 1 越好)。
    1. 尽量使用联合索引,少使用单一索引,这样可以减少回表查询,而且还能节省空间、提高效率。
    1. 要控制索引数量,不是越多越好,索引越多,维护索引代价也就越大,索引过多会影响增删改的效率。
    1. 如果需要建立索引的字段不能为空值,那么建立字段时就给他设置 NOT NULL,这样优化器知道那一列不能为空时,会更好的选择索引优化。

13. SQL 优化

12.1 插入数据

insert 优化:

批量插入

  • 大批量插入数据

如果一次性需要插入大批量数据,使用 insert 语句插入性能较低,此时使用 load 函数进行插入。

#客户端连接服务端时,加上参数 –local-infile

mysql –local-infile -u -root -p;

#设置全局参数 local_infile 为 1

set global local_infile=1;

#执行 load 指令将准备好的数据,加载到表结构中

load data local infile ‘linux路径’ into table ‘表名’ fields terminated by ‘,’ lines terminated by ‘\n’;

12.2 主键优化

在 InnoDB 存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table IOT)

主键顺序插入性能优于乱序插入

主键顺序插入(插入效率高)

主键乱序插入(插入效率低)

插入效率低的原因

  • 页分裂
    • 页可以为空,页可以填充一半,也可以填充 100%,每个页包含了 2-N 行数据(如果一行数据过大,会行溢出),根据主键排列
  • 页合并
    • 当删除一行记录时,实际上记录没有被物理删除,只是记录被标记为删除并且它的空间变得允许被其他记录声明使用
    • 当页中删除的记录达到 MERGE-THRESHOLD(默认为页的 50%),InnoDB 会开始寻找最靠近的页(前或后)看看是否能将两个页合并以优化空间使用

14. 视图

13.1 创建视图

create or replace 创建视图,如果有同名的视图则会替换,也可用于更新视图

create [or replace] view view_name as select ...

create or replace view rc_user as select * from user where id<=2;

查看创建的视图

show create view view_name

show create view rc_user;

13.2 更改删除视图

更新视图

alter view view_name as select ...

alter view rc_user as select id,name from user;

删除视图

drop view [if exists] view_name;

drop view if exists rc_user;

13.3 WITH 条件

没有添加约束可以在视图内插入任意数据,但会因为插入的数据不符合视图查询的要求而无法被视图查询到

insert into rc_user(id, name, age) values(40,'richu',40);

添加约束

alter view  rc_user as select  * from user where id < 10 with cascaded check option ;

-- 此时会报错
insert into rc_user(id, name, age) values(40,'richu',40);

13.3.1 CASCADED

  • 含义:表示检查选项是级联的,即不仅对当前视图生效,还对通过该视图访问的任何底层视图或表生效。
  • 行为:当通过视图进行数据修改时,CASCADED 会确保所有相关的底层视图和表都满足其各自的检查条件。
  • 适用场景:当你希望对整个视图层次结构施加严格的约束时,使用 CASCADED。

13.3.2 LOCAL

如果上一级视图有约束 CASCADED LOCAL 则会检查上级

  • 含义:表示检查选项是本地的,即仅对当前视图本身生效,不对底层的视图或表施加额外的约束。
  • 行为:当通过视图进行数据修改时,LOCAL 只确保当前视图的定义被满足,而不会检查底层视图或表的约束。
  • 适用场景:当你希望仅对当前视图施加约束,而不影响底层数据时,使用 LOCAL。

15. 存储过程

14.1 基本操作

-- 创建
create procedure p1()
begin
    select count(*) from user;
end;
-- 调用
call p1();
-- 查询
show create procedure p1;
-- 删除
drop procedure if exists p1;

创建
create procedure procedure_name()
调用
call procedure_name();
查询
show create procedure procedure_name;
删除
drop procedure [if exists] procedure_name;

14.2 系统变量

全局变量 GLOBAL,会话变量 SESSION

查看系统变量

show session variables;
show session variables like 'auto%';
show global variables;
select @@session.autocommit;
select @@global.autocommit;

设置系统变量

set session autocommit = 0;
set global autocommit = 0;

14.3 用户定义变量

set @变量名 = 变量值; 或 set @变量名 := 变量值,推荐使用 :=

set @name := 'rc';
set @age := 20;
set @password := '1234';

select @id := '123';
-- 使用 into
select count(*) into @mycount from user;

查询

select @name,@age,@password;

14.4 局部变量

**定义局部变量**:declare 变量名 属性 [default]
create procedure p2()
begin
  -- 定义变量
    declare user_count int default 0;
    -- 给变量赋值
    select count(*) into user_count from user;
    -- 查询该变量
    select user_count;
end;

14.5 if 判断

if 条件 then

elseif 条件 then

else then

end if;

create procedure p2()
begin
    declare user_count int default 0;
    select count(*) into user_count from user;
    if user_count >= 5 then
        select user_count+1;
    elseif user_count <= 3 then
        select user_count+2;
    else
        select user_count+3;
    end if;
end;

14.6 参数

  • IN:用于传递值给存储过程,不能被修改并返回。
  • OUT:用于从存储过程返回值给调用者,初始值不需要。
  • INOUT:既可以传递值给存储过程,也可以被修改并返回值给调用者。

DELIMITER $$ 以 $$ 为结尾的字符默认为 ;

create procedure p3(in score int,out result varchar(11))
begin
    if score > 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;

14.7 case

case 选择,相当于其他语言的 switch

create procedure p4(in month int)
begin
    declare result varchar(11);
    case
        when month > 10 then
            set result = '1000';
        when month > 20 then
            set result = '2000';
    end case;
end;

14.8 循环结构

14.8.1 while

create procedure p6(in count int)
begin
    declare result int default 0;
    while count >= 0 do
        set result := result + count;
        set count := count - 1;
    end while;
    select result;
end;

14.8.2 repeat

类似 do while

create procedure p5(in count int)
begin
    declare result int default 0;
    repeat
        set result := result + count;
        set count := count - 1;
    until  count <= 0
    end repeat;
    select result;
end;

14.8.3 loop

LOOP 是另一种循环结构,语法与其余循环类似,通过 leave / iterate 控制循环退出与继续。

14.9 附录:本文出现的英语单词及其翻译

单词 词性 / 含义
alter v. 改变、变动
truncate v. 截短、去尾
medium adj. 中等的、适中的
grant v. 允许、同意,准予
revoke v. 撤回、取消;废除
pad v. 填塞
constraint n. 限制、拘束、强迫
duplicate v. 复制、重复
transaction n. 交易、办理、事务、学报
profile n. 简介、概述
query v. 询问
terminate v. 使结束,终止

一句话总结:MySQL 以”库—表—行”的表结构组织数据,并通过 DDL、DML、DQL、DCL 四类 SQL 语句配合函数、约束、事务、索引与存储过程等机制,实现数据的高效定义、操作、查询与安全管理。