基础查询
1. 基本语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
常用的比较运算符:
常见的聚合函数:
执行顺序
2. 多表查询
2.1 内连接
内连接查询的是两张表的交集部分(图中绿色部分)
(1.隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
(2.显式内连接,inner 可省略
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
2.2 外连接
外连接分两种,分别是:左外连接和右外连接
左外连接:
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ; 1
查询表1(左表)的所有数据,也包含 表1 和 表2 的交集部分
右外连接:
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
查询表2(右表)的所有数据,也包含 表1 和 表2 的交集部分
2.3 自连接
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
对于自连接,可以是内连接,也可以是外连接
2.4 联合查询
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
3. 子查询
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
子查询的外部语句可以是 INSERT / SELECT / DELETE 的任何一个
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
- WHERE之后
- FROM之后
- SELECT之后
3.1 标量子查询
select id from dept where name = '销售部';
select * from emp where dept_id = (select id from dept where name = '销售部');
3.2 列子查询
案例
select id from dept where name = '销售部' or name = '市场部';
select * from emp where dept_id in (select id from dept where name = '销售部' or
name = '市场部');
3.3 行子查询
select salary, managerid from emp where name = '张无忌';
select * from emp where (salary,managerid) = (select salary, managerid from emp
where name = '张无忌');
索引
1. 索引结构
而不用的存储引擎对索引结构的支持也不同。
B+Tree
- 绿色框框起来的部分,是索引部分,仅仅起到索引数据的作用,不存储数据。
- 红色框框起来的部分,是数据存储部分,在其叶子节点中要存储具体的数据。
- 所有的数据都会出现在叶子节点。
- 叶子节点形成一个单向链表。
- 非叶子节点仅仅起到索引数据作用,具体的数据都是在叶子节点存放的。
Hash
- Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,< ,...)
- 无法利用索引完成排序操作
- 查询效率高,通常(不存在hash冲突的情况)只需要一次检索就可以了,效率通常要高于B+tree索引
2. 索引分类
2.1 索引分类
在MySQL数据库有,分为以下几种索引:
2.2 聚集索引&二级索引
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引。
- 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
- 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
具体结构如下:
- 聚集索引的叶子节点下挂的是这一行的数据 。
- 二级索引的叶子节点下挂的是该字段值对应的主键值。
回表查询: 这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取数据的方式,就称之为回表查询。
2.3 索引语法
# 创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;
# 查看索引
SHOW INDEX FROM table_name ;
# 删除索引
DROP INDEX index_name ON table_name ;
3. SQL性能分析
3.1 SQL执行频率
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';
3.2 慢查询日志
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
然后通过指令:systemctl restart mysqld 重启MySQL服务即可
3.3 profile 详情
-- 查看当前MySQL是否支持 profile
SELECT @@have_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;
3.4 explain
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
各个字段的含义:
4. 索引使用
4.1 最左前缀法则
在 tb_user 表中,有一个联合索引,这个联合索引涉及到三个字段,顺序分别为: profession ,age, status 。对于最左前缀法则指的是,查询时,最左变的列,也就是profession 必须存在,否则索引全部失效。 而且中间不能跳过某一列,否则该列后面的字段索引将失效。
4.2 范围查询
explain select * from tb_user
where profession = '软件工程' and age > 30 and status = '0';
例如以上SQL语句,对 age 字段使用了范围查询,则会导致 status 字段的索引失效。
可以将 > 改为 >= ,从而避免联合索引失效
explain select * from tb_user
where profession = '软件工程' and age >= 30 and status = '0';
4.3 索引失效
- 索引列运算
- 不要在索引列上进行运算操作, 索引将失效。
- 字符串不加引号
- 字符串类型字段使用时,不加引号,索引将失效。
- 模糊查询
- 如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效
- or连接条件
- 用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到,左右两侧字段都有索引时,索引才会生效。
- 数据分布影响
- 如果MySQL评估使用索引比全表扫描更慢,则不会使用索引
4.4 SQL提示
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
4.5 索引覆盖
例如查询字段 A、B,使用到了 A和B的联合索引,那么可直接通过二级索引返回需要的数据,无需再通过主键回表查询。
如果查询字段 A、B和C,使用了 AB联合索引,但是就没有包含 C 的数据,那么就需要通过二级索引拿到主键 id,再去扫描聚集索引
4.6 前缀索引
create index idx_xxxx on table_name(column(n)) ;
前缀长度可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值, 索引选择性越高则查询效率越高, 唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
4.7 索引设计原则
1). 针对于数据量较大,且查询比较频繁的表建立索引。2). 针对于常作为查询条件( where )、排序( order by )、分组( group by )操作的字段建立索引。3). 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。4). 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。5). 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。6). 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。7). 如果索引列不能存储 NULL 值,请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时,它可以更好地确定哪个索引最有效地用于查询。
5. SQL优化
5.1 插入数据
1. 批量插入
Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
2. 手动控制事务
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;
3. 按照主键顺序插入,优于乱序插入
如果是要一次性插入大批量数据,数据量达百万以上,可以使用 load 指令
-- 客户端连接服务端时,加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据,加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;
5.2 主键优化
插入数据时按照主键顺序插入性能更好,这是因为存在 页分裂 与 页合并
页分裂:
1. 此时 1# 与 2# 都已满,如果再插入 50 ,innoDB 不会再开启一个新的页,因为索引结构的叶子节点是有顺序的,50 应该排在 47 之后
2. innoDB 会开辟一个新的页 3#,但不会直接将 50 存入。而是将 1# 后一半的数据移入 3#,然后再将 50 存储到 47 之后
3. 重新设置链表指针,此举比较耗费性能
页合并:
1. 目前表中已有数据的索引结构:
2. 删除数据时,这条记录会被标记为 已删除 ,并且它的空间可以被其他记录声明使用。当我们删除 2# 的数据超过一半时,innoDB 会寻找最靠近的页看看是否可以合并
3. 数据合并后,再次插入新数据时会直接插入 3#
主键设计原则:
- 满足业务需求的情况下,尽量降低主键的长度。
- 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键。
- 尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
- 业务操作时,避免对主键的修改。
5.3 order by 优化
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序, 一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。
- 如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。
5.4 group by 优化
- 在分组操作时,可以通过索引来提高效率。group by 会自动使用合适的索引
- 分组操作时,索引的使用也是满足最左前缀法则的。
5.5 limit 优化
explain select *
from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a
where t.id = a.id;
5.6 count 优化
对于不同的存储引擎,count 的执行流程也不同
- MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高; 但是如果是带条件的count,MyISAM也慢。
- InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
5.7 update 优化
开启事务后,MySQL 会根据 查询条件 有无索引 来选择使用 行锁 与 表锁
例如
update course set name = 'javaEE' where id = 1 ;
主键拥有聚集索引,因此使用的是 行锁,并发操作可以操纵这张表的其他行
但是,当查询条件字段没有索引时
update course set name = 'SpringBoot' where name = 'PHP' ;
MySQL 会将 行锁 升级为 表锁,操作同一张表的其他并发操作会被阻塞,导致性能降低
InnoDB 的行锁是针对索引加的锁,不是针对记录加的锁 , 并且该索引不能失效,否则会从行锁升级为表锁 。
视图/存储过程/触发器
1. 视图
1.1 什么是视图
-- 创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
-- 查询
查看创建视图语句:SHOW CREATE VIEW 视图名称;
查看视图数据:SELECT * FROM 视图名称 ...... ;
-- 修改
方式一:
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
方式二:
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
-- 删除
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...
1.2 检查选项
如果不启用检查选项,那么MySQL便不会对 插入的数据做检查。
例如,在创建视图的时候,指定的条件为 id<=10。那么,如果要插入 id=17 的数据:
- 不启用检查
- 可以插入,虽然视图中看不到这条数据,但是可以被插入到基表中
- 启用检查
- 插入失败
1.3 视图的更新
- 聚合函数或窗口函数(SUM()、 MIN()、 MAX()、 COUNT()等)
- DISTINCT
- GROUP BY
- HAVING
- UNION 或者 UNION ALL
1.4 视图的作用
1). 简单视图不仅可以简化用户对数据的理解,也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。2). 安全数据库可以授权,但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据1 3). 数据独立视图可帮助用户屏蔽真实表结构变化带来的影响。
2. 存储过程
特点:
- 封装,复用 -----------------------------------> 可以把某一业务SQL封装在存储过程中,需要用到的时候直接调用即可。
- 可以接收参数,也可以返回数据 --------> 再存储过程中,可以传递参数,也可以接收返回值。
- 减少网络交互,效率提升 -----------------> 如果涉及到多条SQL,每执行一次都是一次网络传输。 而如果封装在存储过程中,我们只需要网络交互一次可能就可以了。
2.1 基本语法
-- 创建
CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
-- SQL语句
END ;
-- 调用
CALL 名称 ([ 参数 ]);
-- 查看
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义
-- 删除
DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ;
注意 :在命令行中,执行创建存储过程的 SQL 时,需要通过关键字 delimiter 指定 SQL 语句的结束符。
2.2 变量
系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值
(2. 设置系统变量
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;
注意 :如果没有指定 SESSION/GLOBAL ,默认是 SESSION ,会话变量mysql 服务重新启动之后,所设置的全局参数会失效,要想不失效,可以在 /etc/my.cnf 中配置。A. 全局变量 (GLOBAL): 全局变量针对于所有的会话。B. 会话变量 (SESSION): 会话变量针对于单个会话,在另外一个会话窗口就不生效了。
用户自定义变量
-- 赋值
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;
= 与 := 作用是一样的
-- 使用
select @var_name;
注意 : 用户定义的变量无需对其进行声明或初始化,只不过获取到的值为 NULL 。
局部变量
-- 声明
DECLARE 变量名 变量类型 [DEFAULT ... ] ;
-- 赋值
SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;
2.3 参数
使用语法如下:
CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
-- SQL语句
END ;
案例:
- score >= 85分,等级为优秀。
- score >= 60分 且 score < 85分,等级为及格。
- score < 60分,等级为不及格。
create procedure p4(in score int, out result varchar(10))
begin
if score >= 85 then
set result := '优秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;
end;
-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);
select @result;
2.4 基础循环语句
case
使用语法如下:
-- 含义: 当case_value的值为 when_value1时,执行statement_list1,
-- 当值为 when_value2时,执行statement_list2, 否则就执行 statement_list
CASE case_value
WHEN when_value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2] ...
[ ELSE statement_list ]
END CASE;
-- 含义: 当条件search_condition1成立时,执行statement_list1,
-- 当条件search_condition2成立时,执行statement_list2, 否则就执行 statement_list
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2] ...
[ELSE statement_list]
END CASE;
while
-- 先判定条件,如果条件为true,则执行逻辑,否则,不执行逻辑
WHILE 条件 DO
SQL逻辑...
END WHILE;
repeat
-- 先执行一次逻辑,然后判定UNTIL条件是否满足,如果满足,则退出。如果不满足,则继续下一次循环
REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;
loop
- LEAVE :配合循环使用,退出循环
- ITERATE:必须用在循环中,作用是跳过当前循环剩下的语句,直接进入下一次循环
[begin_label:] LOOP
SQL逻辑...
IF condition
LEAVE begin_label
END LOOP [end_label];
-- 上述语法中出现的 begin_label,end_label,label 指的都是我们所自定义的标记。
LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环
2.5 游标
-- 声明
DECLARE 游标名称 CURSOR FOR 查询语句 ;
-- 打开
OPEN 游标名称 ;
-- 获取游标记录
FETCH 游标名称 INTO 变量 [, 变量 ] ;
-- 关闭
CLOSE 游标名称 ;
范例:
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null, uname, upro);
end while;
close u_cursor;
2.6 条件处理程序
DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;
handler_action 的取值:
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序
condition_value 的取值:
SQLSTATE sqlstate_value: 状态码,如 02000
SQLWARNING: 所有以01开头的SQLSTATE代码的简写
NOT FOUND: 所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写
使用示例:
declare exit handler for not found close u_cursor;
3. 存储函数
CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
-- SQL语句
RETURN ...;
END ;
- DETERMINISTIC:相同的输入参数总是产生相同的结果
- NO SQL :不包含 SQL 语句
- READS SQL DATA:包含读取数据的语句,但不包含写入数据的语句
4. 触发器
-- 创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
trigger_stmt ;
END;
-- 查看
SHOW TRIGGERS ;
-- 删除
DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name,默认为当前数据库
案例:
create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
(null, 'insert', now(), new.id, concat('插入的数据内容为:
id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ',
profession=', NEW.profession));
end;
(2. 修改数据触发器
create trigger tb_user_update_trigger
after update on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
(null, 'update', now(), new.id,
concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=',
old.phone, ', email=', old.email, ', profession=', old.profession,
' | 更新之后的数据: id=',new.id,',name=',new.name, ', phone=',
NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;
(3. 删除数据触发器
create trigger tb_user_delete_trigger
after delete on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
(null, 'delete', now(), old.id,
concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=',
old.phone, ', email=', old.email, ', profession=', old.profession));
end;