MySQL 基础
MySQL 概述
- 存储引擎是存储数据、建立索引、更新\查询数据等技术的实现方式
- 存储引擎是基于表的,而不是基于库的,故存储引擎也被称为表类型
- 在创建表的时候可以指定存储引擎,MySQL 的引擎有
InnoDB/MyISAM/Memory
,默认的存储引擎为InnoDB
SQL 语句
根据功能,SQL 语句可以分为四类:
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象,包括数据库、表、字段等 |
DML | Data Manipulation Language | 数据操作语言,用来对表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询表中的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库的用户,控制访问权限等 |
Data Definition Language (DDL)
- DDL 数据库操作
show databases;
–查询所有数据库;select database();
–查询当前数据库;create database [if not exists] 数据库名 [default charset 字符集];
–创建数据库;use 数据库名;
–切换数据库;drop database [if exits] 数据库名;
–删除数据库;
- DDL 表操作
show tables;
–查询当前数据库的所有表;desc 表名;
–查看指定表结构;show create table 表名;
–查询指定表的建表语句;create table 表名 (...);
–创建表;alter table 表名 add/modify/change/drop ...
–添加字段、修改数据类型、修改字段名和字段类型、删除字段alter table 表名 rename to 新表名;
–修改表名;drop table 表名;
–删除表;truncate table 表名;
–删除指定表并重新创建该表,效率高于delete from
;
操作实例:按要求设计一张员工表
- 编号,纯数字
- 员工工号,字符串类型,长度不超过 10 位
- 员工姓名,字符串类型,长度不超过 10 位
- 性别,男/女,存储一个汉字
- 年龄,不可为负数
- 身份证号,18 位,可能有 X 这样的字符
- 入职时间,年月日
create table emp (
id int comment '编号';
workno varchar(10) comment '工号';
name varchar(10) comment '姓名';
gender char(1) comment '性别';
age tinyint unsigned comment '年龄';
idcard char(18) comment '身份证号';
entrydate date comment '入职时间';
) comment '员工表';
- Tips:
TINYINT UNSIGNED
– (0, 255)char
与varchar
- 最大长度:
char
最大长度 255 字符;varchar
最大长度 65535 个字节 - 定长:
char
是定长的,不足的部分用隐藏的空格填充;varchar
非定长 - 空间使用:
char
会浪费空间;varchar
更节省空间 - 查找效率:
char
查找效率会很高;varchar
查找效率低
- 最大长度:
int(4)
– 此处的 4 不是指int
整形的长度,而是设置自动在前头填充 0 的位数,即“0001”
Data Manipulation Language (DML)
- 添加数据
insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...);
insert into 表名 values (值1, 值2, ...);
- 修改数据
update 表名 set 字段名1=值1, 字段名2=值2, ... [where 条件];
(若无条件则修改整张表所有数据)
- 删除数据
delete from 表名 [where 条件];
(若无条件则删除整张表所有数据)
Data Query Language (DQL)
select 字段 # 4. 执行 select 选出数据
where ... # 1. 首先执行 where 过滤原始数据(不能对聚合函数进行处理)
group by ... # 2. 执行 group by 进行分组
having ... # 3. 执行 having 对分组数据进行操作
order by ... # 5. 执行 order by 排序
简单查询
select 字段名 from 表名;
–查询单个字段select 字段名1, 字段名2 from 表名;
–查询多个字段select * from 表名;
–查询全部字段
Tips:
- 在简单查询中可以使用运算符,例如:
select empno, ename, sal * 12 from emp;
- 查询的字段可以重命名,例如:
select sal * 12 as "年薪" from emp;
条件查询
select empno, ename, sal from emp where sal = 2000;
–等于select empno, ename, sal from emp where sal < 5000;
–小于(大于)select empno, ename, sal ftom emp where sal between 2000 and 5000;
–between…and…select * from emp where comm is null;
–is nullselect * from emp where job = "manager" and sal > 2000;
–andselect * from emp where job = "manager" or job = "salesman"
–orselect * from emp where job in ("manager", "salesman")
–inselect * from emp where job not in ("manager", "salesman");
–not inselect * from emp where ename like "_M%";
–like
数据排序
排序使用order by
语句,后跟排序字段,默认为升序,在where
条件语句之后使用
select * from emp where job = "manager" order by sal asc/desc;
–单一字段排序(asc-升序/desc-降序)select * from emp order by sal asc, job desc;
–多字段排序
分组查询
分组查询的执行:根据where
条件查询数据 --> group by
对数据进行分组 --> having
进行过滤
注意:若使用group by
进行分组查询,则select
只能查询参与分组的字段以及聚合函数
select job, sum(sal) from emp group by job;
–分组查询select job, avg(sal) from emp group by job having avg(sal) > 1000;
–分组后进行过滤
聚合函数
count()
–计数函数select count(*) from emp;
–count(*)表示获得所有记录,包括null
select count("job") from emp;
–count(字段名)不会统计null
记录
sum()
–求和函数select sum(sal) from emp;
avg()
–均值函数select avg(sal) from emp;
max()/min()
–最值函数select job, max(sal) from emp group by job;
select job, min(sal) from emp group by job;
数据处理函数
- 字符串函数
lower()
–转换小写:select lower(ename) from emp;
upper()
–转换大写:select * from emp where job = upper("manager");
concat()
–拼接字符串:select * from emp where ename = concat("Curry", "Jack");
substr()
–切分字符串:select * from emp where substr(ename, 1, 1) = upper("m"); # 查询名字从第一个起为m的员工信息
length()
–取长度:select ename from emp where length(ename) = 5;
trim()
–去除首尾空格:select * from emp where job = trim(" manager ");
str_to_date()
–将字符串转换为日期:select * from emp where hiredate = str_to_date(“1981-02-20”, “%Y-%m-%d”);
- 日期函数
date_add()
–返回一个日期加上时间间隔后的时间值:select date_add(now(), interval 70 day);
datediff()
–返回两个日期之间的天数:select datediff('2022-07-24', '2022-07-06');
date_format()
–日期格式化:select date_format(hiredate, "%Y-%m-%d") hiredate from emp;
- 数值函数
format()
–格式化:select ename, format(sal, 2) from emp; # 加入千分位,保留两位小数
ceil()/floor()
–向上取整/向下取整:select ceil(1.9);/select floor(1.2);
round()
–四舍五入:select round(1123.26723,0); # 第二个参数为正表示保留几位小数,为负表示从小数点往左第几位开始四舍五入
rand()
–生成 0-1 浮点型随机数:round(rand() * 5444 + 234); # 生成 234-5678 之间的随机整数
- 流程函数
case..when..then..else..end
–条件判断:select case job when "manager" then sal * 1.1 when "salesman" then sal * 1.5 else sal end as new sal from emp;
ifnull
–替换null:select empno, ename, sal, (sal + ifnull(comm, 0) * 12) as yearsal from emp;
连接查询
- 笛卡尔积(没有指定连接条件):
select ename, dname from emp, dept;
- 内连接:又称等值连接,返回的是两张表公共的满足条件的部分
select e.ename, e.sal, d.dname from emp e inner dept d on e.deptno = d.deptno;
- 外连接:取左边表的全部以及左右表公共的满足条件的部分
- 左连接:
select e.ename, e.sal, d.dname from emp e left join on dept d on e.deptno = d.deptno;
- 右连接:
select e.ename, e.sal, d.dname from emp e right join on dept d on e.deptno = d.deptno;
- 左连接:
- 自连接:将一张表查询多次
select a.name, b.name from dept a, dept b where a.parent_id = b.id; # 查询部门以及对应的上级部门
子查询
子查询就是嵌套的select
语句,可以将其结果理解为一张临时表
- 在
where
中使用子查询
# 例:查询哪些人的薪水高于员工的平均薪水,需要显示员工编号、姓名、薪水
# (1) 查询平均薪水
select avg(sal) from emp;
# (2) 获取大于平均薪水的员工
select empno, ename, sal from emp where sal > (select avg(sal) from emp);
- 在
from
中使用子查询
# 例:查询各个部门的平均薪水等级,需要显示部门编号、平均薪水、等级编号
# (1) 查询各个部分平均薪水
select deptno, avg(sal) avg_sal from emp group by deptno;
# (2) 将部门平均薪水最为一张表与薪水等级表连表查询
select t.deptno, d.avg_sal, g.grade
from (select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade g
on t.avg_sal between g.losal and g.hisal;
- 在
select
中使用子查询
# 例:查询员工信息,并显示出所属部门的部门名称
# 方法1:员工表和部门表连表查询
select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
# 方法2:select 子查询
select e.ename, (select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
Union 合并集合
# 例:查询 job 为 manager 和 salesman 的员工
# 方法1:
select * from emp where job in ("manager", "salesman");
# 方法2:union 合并
select * from emp where job = "manager"
union
select * from emp where job = "salesman";
Limit
- 取得前5条数据:
select * from emp limit 5;
- 从第2条数据开始取2条数据:
select * from emp limit 1,2;
Data Control Language (DCL)
- 用户管理
create user 'zhangsan'@'localhost' identified by '123456';
–创建用户 zhangsan 且只能在当前主机访问create user 'lisi'@'%' identified ‘123456’;
–创建用户 lisi 可以在任意主机访问alter user 'lisi'@'%' identified with mysql_native_password by '1234';
–修改用户 lisi 的访问密码为 1234
- 权限管理
show grants for '用户名'@‘主机名’;
–查询权限grant 权限列表 on 数据库名.表名 to '用户名'@‘主机名’
–授予权限revoke 权限列表 on 数据库名.表名 from '用户名'@‘主机名’
–撤销权限
MySQL 事务
事务概述
事务是一组操作的组合,事业个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或者撤销,即一个事务中的操作要么同时成功,要么同时失败。
Tip:MySQL 的事务是自动提交的,每当执行完一次 DML 语句,MySQL 会隐式的提交事务
手动控制事务
示例:
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;
-- 张三给李四转账 500 元
-- 0. 开启事务
START TRANSACTION;
-- 1. 张三账户 -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2. 李四账户 +500
-- 出错了...
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
-- 发现执行没有问题,提交事务
COMMIT;
-- 发现出问题了,回滚事务
ROLLBACK;
事务的四大特性(ACID)
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么同时成功,要么同时失败
- 一致性(Consistency):事务完成时,所有的数据都保持一致状态,即数据的总量不变(转账完成后,两个账户的和与原来保持一致)
- 独立性(Isolation):数据库系统提供隔离机制,保证事务可以不受外部并发操作影响,独立运行
- 持久性(Durability):事务一旦提交或者回滚,对数据库的改变就是永久的
事务的隔离级别
并发事务问题
- 脏读:一个事务读取到了另一个事务还没提交的数据
- 不可重复读:一个事务先后读取同一条数据,但两次读取到的数据不同
- 幻读:一个事物操作(DML)数据表中的记录,而另一个事物添加了一条数据,则第一个事物查询不到自己的修改
隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted,读未提交 | 可能 | 可能 | 可能 |
Read committed,读已提交 | 不可能 | 可能 | 可能 |
Repeatable Read,可重复读 | 不可能 | 不可能 | 不可能 |
Serializable,串行化 | 不可能 | 不可能 | 不可能 |
Tip:MySQL 的 Innodb 引擎的事务隔离级别默认为Repeatable Read
隔离级别的查看与设置:
select @@transaction_isolation;
–查看事务隔离级别set [session | global] transcation isolation level [read uncommited | read commited | repeatable read | serializable];
–设置事务的隔离级别
事务的实现原理
事务的四大特性中:
- 原子性、一致性、持久性由 InnoDB 引擎的的redo log日志和undo log日志保证;
- 隔离性由数据库的锁 + MVCC保证;
redo log 重做日志
该日志由两部分组成:重做日志缓冲 redo log buffer以及重做日志文件 redo log file,前者存储在内存中,后者存储在磁盘中。
为保证 MySQL 中数据的持久性和一致性,数据每次或者每隔很短的时间就要写入磁盘,而 redo log 就用于在刷新脏页到磁盘时发生错误时,进行数据恢复。
当 MySQL 事务提交时,首先将数据写入缓存 redo log buffer,这是每一次事务提交都必须写入的。但何时写入就需要看如何配置了,可以每次事务完成都写入,也可以每隔一段时间写入一次。注意,此时文件还没有写入到磁盘当中,若发生宕机或者断电,会导致数据丢失!
最后又操作系统将系统中的缓存数据 OS cache 写入到磁盘,即顺序记录到 redo log file,此时断电或者宕机也不会影响数据了。注意,此时数据仅仅写入了 redo 日志当中,并未真正的写入数据库文件,后续还会将 redo file log 同步到数据库,但该过程不会影响数据的安全性。
Tips:
- redo log 的存储空间像是一个圈,从头开始储存,当存满了之后便重头开始覆盖存储;
- 默认
innodb_flush_log_at_trx_commit=1
,即强一致性,表示每次事务提交既要刷新到系统缓存,也要写入到 redo log;
undo log 回滚日志
undo log 实际上是一个日志版本链。当某一行数据被多个事务进行修改时,每一次修改都会记录一个 undo log 回滚日志。若某一次的修改最终rollback
了,那么久根据 undo log 进行数据回滚。
每条 undo log 包含三个关键字段,即row_id
、trx_id
以及roll_pointer
,分别为主键 id、事务 id 以及该数据的上一个版本的指针,事务 id 将 undo log 串联成一个历史记录版本链。事物的任何 SQL 查询结果从对应版本链里的最新数据开始逐条对比,从而获取结果。
在当前事务进行查询时,若存在当前事务对数据的修改,则返回当前事务的修改数据;若不存在当前事务对数据的修改则返回上一个已经提交的数据。
Tip:MySQL 通过 MVCC 和 MySQL 锁保证了事务的正确执行;
索引及优化
索引概述
索引是帮助 MySQL 高效获取数据的排好序的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构即索引。
无索引的情况下,查找数据需要全表扫描,性能较低。
索引的结构和特点
- 索引的类型
索引结构 | 描述 |
---|---|
B+Tree 索引 | 最常见的索引类型,大部分引擎都支持 B+ 树索引 |
Hash 索引 | 数据结构使用哈希表实现,只有精确匹配索引列的查询才有效,不支持范围查询 |
Full-text(全文索引) | 返回当前日期和时间 |
- 索引的优劣势
优势 | 劣势 |
---|---|
提高数据检索的效率,降低数据库的 IO 成本 | 索引列也需要占用空间 |
通过索引列对数据进行排序,降低 CPU 的效耗 | 索引提高了查询效率,但同时降低了更新表的速度,对表进行增改删操作时效率很低 |
B Tree
- B 树是一种多叉路查找树,与二叉树相比,B 树的每个节点有多个分支,即多叉。
- 以一棵最大度数(max-degree)为 5 的 b-tree 为例,那么该 B 树每个节点忖度存储 4 个 key,5 个指针。
- B 树的特点:
- 叶子节点具有相同的深度,叶节点的指针为空;
- 节点中的数据索引从左向右递增排列;
B+ Tree
- B+ 树的变种,
InnoDB
引擎默认使用 - B+ 树的特点:
- 非叶子节点不存储数据,只存储索引,可以存放更多的索引;
- 叶子节点包含所有索引字段,同时存储数据;
- 叶子节点用指针相连接,提高了区间访问的性能;
Hash 结构
Memory
引擎使用的索引结构- Hash 结构的特点:
- 对索引进行一次 hash 计算就可以定位出数据存储的位置;
- Hash 大部分时候比 B+ 树索引更高效;
- 仅能满足
=
和in
,不支持范围查询; - 存在 Hash 冲突问题;
聚集索引和二级索引
- 聚集索引(Clustered Index)
- 将数据和索引存储在一起,索引结构的叶子节点保存了行数据;
- 聚集索引的叶子节点下面挂的是这一行的全部数据;
- 必须有聚集索引,而且只能有一个;
- 二级索引(Secondary Index)
- 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键;
- 二级索引的叶子节点下面挂的是该字段对应的主键值;
- 先到二级索引中查找数据,找到对应的主键,再回到聚集索引中根据主键获取数据的方式,称为回表查询;
- 可以存在多个;
- 索引的选取规则:
- 若存在主键,主键索引就是聚集索引;
- 若不存在主键,将使用第一个唯一(
unique
)索引作为聚集索引; - 若不存在主键或没有合适的唯一索引,则
InnoDB
会自动生成一个 rowid 作为隐藏的聚集索引;
- 索引的创建:
create index 索引名 on 表名(字段名);
索引的语法
create [unique | fulltext ] index 索引名 on 表名(字段名);
–创建索引- 创建普通索引:
create index idx_user_name on tb_user(name);
- 创建唯一索引:
create unique index idx_user_phone on tb_user(phone);
- 创建联合索引:
create index idx_user_pro_age_sta on tb_user(profession, age, status);
- 创建普通索引:
show index from 表名;
–查看索引drop index 索引名 on 表名;
–删除索引
SQL 性能分析:Explain 执行计划
Explain 执行计划个字段的含义:
- id:
select
查询的序列号,表示查询中执行select
子句或者操作表的顺序,id 越大,越先执行; - select_type:表示
select
的类型,常见类型如下:- simple–简单表,即不使用表连接或者子查询;
- primary–主查询,即外层的查询;
- union–union 中的第二个或者后面的查询语句;
- subquery–
select/where
之后包含子查询;
- type:表示连接类型,性能由好到差的连接类型为 null、system、const、eq_ref、ref、range、index、all;
- possible_key:显示可能应用在这张表上的索引;
- key:显示实际使用的索引,若为 null,则没有使用索引;
- key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度。在不损失精确性的情况下,长度越短越好;
- rows:MySQL 认为必须要执行查询的行数,在
InnoDB
引擎中是一个估计值,并不准确; - filtered:表示返回结果的行数占需要读取的行数的百分比,该值越大越好;
- Extra
- Using index:使用覆盖索引,最好!
- Using where:使用
where
来处理结果,并且查询的列未被索引覆盖; - Using index condition:查询的列不完全被索引覆盖,
where
条件中是一个前导列的范围; - Using temporay:MySQL 需要创建一张临时表来处理查询,需要优化;
- Using filesort:将使用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序,需要优化;
索引使用法则
- 最左前缀法则:联合索引应该遵守最左前缀法则,查询因该从索引的最左列开始,并且不跳过索引中的列。若跳过某一列,则后边字段的索引将会失效;
- 范围查询:在业务允许的情况下,应尽可能的使用类似
>=
或<=
这类的范围查询,避免使用>
或<
; - 覆盖索引:指查询使用了索引,且需要返回的列在该索引中已经全部找到,不需要回表查询;
- 前缀索引:当字段类型为字符串时(
varchar/text/longtext
),有时候索引需要很长的字符串,这将使索引变得很大,从而在查询时浪费大量的磁盘 IO,影响查询效率。因此可以只将字符串的一部分前缀建立索引,这样可以大大节约索引空间,从而提高索引效率;create index 索引名 on 表名(字段名(前缀索引长度));
–创建前缀索引;- 字段截取的长度需要权衡,尽量在最短的长度实现最高的区分度;
- 单列索引与联合索引:在业务场景中,若存在多个查询条件,建议采用联合索引而不是单列索引;
索引失效
- 在索引列上进行运算操作将导致索引失效;
- 查询字符串类型字段时不加引号将导致索引失效;
- 模糊查询时若头部模糊匹配,将导致索引失效。若仅是尾部模糊匹配,则索引不会失效;
- 使用
or
连接条件时,若左右两侧有一侧没有索引,将导致索引失效。只有两侧字段都存在索引时才会生效;
索引的建立原则
- 代码先行,索引后上;
- 针对数据量较大、查询比较频繁的表建立索引;
- 针对长最为查询条件
where
、排序order by
、分组group by
操作的字段建立索引;当where
与order by
出现索引设计冲突时,优先where
; - 尽量选择区分度高的列作为索引,尽量建立唯一索引。区分度越高,使用索引的效率越高;
- 字符串类型的字段内容较长时,针对字段的特点建立前缀索引;
- 尽量使用联合索引,减少单列索引,联合索引尽量覆盖查询字段,以达到覆盖索引,节省存储空间,避免回表查询,提高效率;
- 要控制索引的数量,索引越多,维护索引结构的代价也就越大,会影响增删改的效率;
常用的 SQL 优化
- 批量插入优化
- 手动进行事务控制进行提交,每次满足一定的行数后进行批量提交;
- 主键优化
- 对于插入较为频繁的表,主键尽量选择顺序自增序列,主键顺序插入的性能优于乱序插入;
- limit 分页优化
- 一般分页查询时,越往后分页查询的效率越低;
- 通过创建覆盖索引能够较好的提高性能,可以通过覆盖索引 + 子查询的方式进行优化;
- update 更新优化
- InnoDB 的行锁是针对索引加的锁,不是针对记录加的锁,所以要保证该索引不能失效,否则行锁将升级为表锁,降低性能;