黑马程序员MYSQL进阶笔记（超详细）

最新推荐文章于 2024-06-19 11:00:59 发布

天外流星LX

最新推荐文章于 2024-06-19 11:00:59 发布

阅读量2.1k

点赞数 1

分类专栏： Java 文章标签： mysql 笔记数据库

本文链接：https://blog.csdn.net/weixin_50523986/article/details/131192742

版权

Java 专栏收录该内容

8 篇文章 2 订阅

订阅专栏

1 存储引擎

1.1 MySQL体系结构

1.2 存储引擎介绍

mysql数据库的核心，在合适的场景选择合适的存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式
在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎
建表时指定存储引擎

CREATE TABLE 表名(
   字段1 字段1类型 [ COMMENT 字段1注释 ] ,
   ......
   字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

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

show engines;

查询建表语句 — 默认存储引擎: InnoDB

show create table account;

在这里插入图片描述

1、默认存储引擎
2、Id是自增的下一条Id为3
3、默认字符集
4、默认排序方式

1.3 存储引擎特点

在这里插入图片描述
MyISAM ：MongoDB取代
Memory：Redis取代

1.4 存储引擎选择

InnoDB：用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作
MyISAM：以读操作和插入操作为主，只有很少的更新和删除操作
MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存

2 索引

2.1 索引概述

数据库系统维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以实现高级查找算法
执行的SQL语句为：

select * from user where age = 45;

无索引情况：全表扫描
有索引：改进B+tree

2.2 索引结构

2.2.1 概述

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构
不同的存储引擎对于索引结构的支持情况

2.2.2 B+Tree

在这里插入图片描述

绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据
红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据

B+Tree特点：
所有的数据都会出现在叶子节点
叶子节点形成一个单向链表
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的

MySQL索引数据结构对经典的B+Tree进行了优化，增加一个指向相邻叶子节点的链表指针

2.2.3 Hash

哈希索引：采用hash算法，将键值换算成新的hash值，映射到对应的槽位，存储在hash表中
如果多个键值映射到相同的槽位上，就产生了hash冲突，可以通过链表来解决
特点：

1、Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
2、无法利用索引完成排序操作
3、查询效率高(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引）

2.3 索引分类

2.3.1 索引分类

在这里插入图片描述

2.3.2 聚集索引&二级索引

在这里插入图片描述

聚集索引选取规则:
如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
聚集索引和二级索引的具体结构如下：
执行如下的SQL语句时，具体的查找过程：

2.4 索引语法

创建索引
索引的名称：idx_表名_字段名

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;

# name字段为姓名字段，该字段的值可能会重复
CREATE INDEX idx_user_name ON tb_user(name);

# phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

# 为profession、age、status创建联合索引
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

查看索引

SHOW INDEX FROM table_name ;

删除索引

DROP INDEX index_name ON table_name;

2.5 SQL性能分析

2.5.1 SQL执行频率

查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
-- 七个_
SHOW GLOBAL STATUS LIKE 'Com_______';

如果是以增删改为主，我们可以考虑不对其进行索引的优化
如果是以查询为主，那么就要考虑对数据库的索引进行优化了

2.5.2 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志
MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量

show variables like 'slow_query_log';

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器（Linux语句）

systemctl restart mysqld

2.5.3 profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了
当前MySQL是否支持profile操作

SELECT @@have_profiling ;

通过set语句在session/global级别开启profiling：

SET profiling = 1;

使用profile

-- 查看每一条SQL的耗时基本情况
show profiles;

-- 上面那一条语句会查出所有语句的query_id
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

2.5.4 explain

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

-- 直接在select语句之前加上关键字 explain
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

在这里插入图片描述

Explain 执行计划中各个字段的含义:

2.6 索引使用

2.6.1 最左前缀法则

联合索引：最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)
查询时，最左变的列，也就是profession必须存在，否则索引全部失效
而且中间不能跳过某一列，否则该列后面的字段索引将失效

联合索引的最左边的字段(即是第一个字段)必须存在，与编写SQL时，条件编写的先后顺序无关

2.6.2 范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效
当范围查询使用>= 或 <= 时，走联合索引了
在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询

2.6.3 索引失效情况

2.6.4.1 索引列运算

在索引列上进行运算操作，索引将失效
当根据phone字段进行函数运算操作之后，索引失效
截取phone的第十个字段，一共截取两个字段，第一个是1不是0

explain select * from tb_user where substring(phone,10,2) = '15';

2.6.4.2 字符串不加引号

字符串类型字段使用时，不加引号，索引将失效
字符串不加单引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效

2.6.4.3 模糊查询

如果仅仅是尾部模糊匹配，索引不会失效
如果是头部模糊匹配，索引失效

# 索引生效
explain select * from tb_user where profession like '软件%';

# 索引失效
explain select * from tb_user where profession like '%工程';

# 索引失效
explain select * from tb_user where profession like '%工%';

3.6.4.4 or连接条件

如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
当or连接的条件，左右两侧字段都有索引时，索引才会生效

3.6.4.5 数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引

select * from tb_user where phone >= '17799990005';

索引是用来索引少量数据的
如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效
查询时MySQL会评估，走索引快，还是全表扫描快，全表扫描更快，则放弃索引走全表扫描
is null 、is not null、>、>=、<、<=是否走索引，得具体情况具体分析，并不是固定的

2.6.5 SQL提示

explain select * from tb_user where profession = '软件工程';

possible_keys中 idx_user_pro_age_sta,idx_user_pro 这两个索引都可能用到
最终MySQL选择了idx_user_pro_age_sta索引，这是MySQL自动选择的结果
在查询的时候，自己指定使用哪个索引
SQL提示，是优化数据库的一个重要手段，在SQL语句中加入一些人为的提示来优化操作
use index ：建议MySQL使用哪一个索引完成此次查询（MySQL内部还会进行评估）

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

ignore index ：忽略指定的索引

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

force index ：强制使用索引

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

2.6.6 覆盖索引

2.7 索引设计原则

3. SQL优化

3.1 插入数据

3.1.1 insert

需要一次性往数据库表中插入多条记录

insert into tb_test values(1,'tom');

insert into tb_test values(2,'cat');

insert into tb_test values(3,'jerry');

.....

3.1.1.1 优化方案一：批量插入数据

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

3.1.1.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.1.1.3 优化方案三：主键顺序插入，性能要高于乱序插入

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

3.1.2 大批量插入数据

一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低
使用MySQL数据库提供的load指令进行插入

# 设置参数
-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

-- load加载数据
load data local infile '/root/load_user_100w_sort.sql' into table tb_user
fields terminated by ',' lines terminated by '\n' ;

3.2 主键优化

3.3 order by优化

MySQL的排序，有两种方式
Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort
buffer中完成排序操作
Using index : 通过有序索引顺序扫描直接返回有序数据
尽量要优化为 Using index（直接创建索引就行）

4 视图/存储过程/触发器

4.1 视图

4.1.1 介绍

视图（View）是一种虚拟存在的表
视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表
视图只保存了查询的SQL逻辑，不保存查询结果

4.1.2 语法

创建

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [
CASCADED | LOCAL ] CHECK OPTION ]

create view stu_v_1 as select id,name from student where id <= 10;

查看创建视图语句

SHOW CREATE VIEW 视图名称;

show create view stu_v_1;

查看视图数据

SELECT * FROM 视图名称 ...... ;

select * from stu_v_1;

select * from stu_v_1 where id < 3;

修改

ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |
LOCAL ] CHECK OPTION ]

alter view stu_v_1 as select id,name from student where id <= 10;

删除

DROP VIEW IF EXISTS 视图名称 [,视图名称] ...

drop view if exists stu_v_1;

4.1.2.1 插入、更新数据

create or replace view stu_v_1 as select id,name from student where id <= 10 ;

insert into stu_v_1 values(6,'Tom');

insert into stu_v_1 values(17,'Tom22');

id为6和17的数据都是可以成功插入的
但是查询出来的数据，却没有id为17的记录
在创建视图的时候，指定的条件为 id<=10, id为17的数据，是不符合条件的
但是这条数据确实是已经成功的插入到了基表中

4.1.3 检查选项

使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行
mysql提供了两个选项： CASCADED 和 LOCAL，默认值为 CASCADED

4.1.3.1 CASCADED：级联

在这里插入图片描述

v2视图是基于v1视图的
v2视图创建的时候指定了检查选项为 cascaded
在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1

4.1.3.2 LOCAL：本地

在这里插入图片描述

v2视图是基于v1视图的
v2视图创建的时候指定了检查选项为 local
执行检查时，知会检查v2，不会检查v2的关联视图v1

4.1.4 视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系
视图包含以下任何一项，则该视图不可更新

A. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
B. DISTINCT
C. GROUP BY
D. HAVING
E. UNION 或者 UNION ALL

4.1.5 视图作用

简单：经常使用的查询可以定义为视图，使得用户不必为以后的操作每次指定全部的条件
安全：通过视图用户只能查询和修改他们所能见到的数据
数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响

4.1.6 案例

为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段

create view tb_user_view as select id,name,profession,age,gender,status,createtime
from tb_user;

查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图

create view tb_stu_course_view as select s.name student_name , s.no student_no ,
c.name course_name from student s, student_course sc , course c where s.id =
sc.studentid and sc.courseid = c.id;

4.2 存储过程

4.2.1 介绍

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合（类似Java的函数）
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用
特点：封装，复用、可以接收参数，也可以返回数据、减少网络交互，效率提升

4.2.2 基本语法

创建

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
-- SQL语句
END ;

-- 注意分号的位置上
create procedure p1()
begin
   select count(*) from student;
end;

调用

CALL 名称 ([ 参数 ]);

-- 记得加括号
call p1();

查看

-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; 

select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';

-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称 ; 

show create procedure p1;

删除

DROP PROCEDURE IF EXISTS 存储过程名称

drop procedure if exists p1;

4.2.3 变量

在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量

4.2.3.1 系统变量

系统变量是MySQL服务器提供，不是用户定义的
分为全局变量（GLOBAL）、会话变量（SESSION）
查看系统变量

-- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES ; 
show session variables ;
show global variables ;


-- 可以通过LIKE模糊匹配方式查找变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; 
show session variables like 'auto%';
show global variables like 'auto%';

-- 查看指定变量的值
SELECT @@[SESSION | GLOBAL].系统变量名; 
select @@global.autocommit;
select @@session.autocommit;

设置系统变量

SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
set session autocommit = 1;
set global autocommit = 0;

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量
mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置
全局变量(GLOBAL): 全局变量针对于所有的会话
会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了

4.2.3.2 用户定义变量

赋值时，可以使用 = ，也可以使用 :=

-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';
select @mycolor := 'red';
select count(*) into @mycount from tb_user;

-- 使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor , @mycount;

4.2.3.3 局部变量

局部变量是根据需要定义的在局部生效的变量
访问之前，需要DECLARE声明
声明

-- 变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等
DECLARE 变量名 变量类型 [default ... ] ;

赋值

SET 变量名 = 值 ;

SET 变量名 := 值 ;

SELECT 字段名 INTO 变量名 FROM 表名 ... ;

演示示例

-- 声明局部变量 - declare
-- 赋值
create procedure p2()
begin
   declare stu_count int default 0;
   select count(*) into stu_count from student;
   select stu_count;
end;

call p2();

4.2.4 if

score >= 85分，等级为优秀
score >= 60分且 score < 85分，等级为及格
score < 60分，等级为不及格

create procedure p3()
begin

   declare score int default 58;

   declare result varchar(10);
   
   if score >= 85 then
      set result := '优秀';
   elseif score >= 60 then
      set result := '及格';
   else
      set result := '不及格';
   end if;
   
   select result;
end;

call p3();

4.2.5 参数

介绍
根据传入参数score，判定当前分数对应的分数等级，并返回
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;

4.2.6 case

4.2.7 while

4.2.8 case

4.2.9 case

4.2.10 case

4.2.11 case

4.3 存储函数

4.3.1 介绍

5 锁

5.1 概述

MySQL中的锁，按照锁的粒度分，分为以下三类：
全局锁：锁定数据库中的所有表
表级锁：每次操作锁住整张表
行级锁：每次操作锁住对应的行数据。

5.2 全局锁

5.2.1 介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态
典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图和表格

5.2.2 语法

加全局锁

flush tables with read lock ;

数据备份

mysqldump -uroot –p1234 itcast > itcast.sql

释放锁

unlock tables ;

在InnoDB引擎中，在备份时加上参数 --single-transaction 参数来完成不加锁的一致
性数据备份

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

5.3 表级锁

表级锁，每次操作锁住整张表
应用在MyISAM、InnoDB、BDB等存储引擎中
对于表级锁，主要分为以下三类：
表锁
元数据锁（meta data lock，MDL）
意向锁

5.3.2 表锁

对于表锁，分为两类：
表共享读锁（read lock）
表独占写锁（write lock）
语法：

-- 加锁：
lock tables 表名 read/write

--释放锁：
unlock tables

特点
A. 读锁
B. 写锁

结论: 读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

5.3.3 元数据锁

meta data lock , 元数据锁，简写MDL
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上
某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的
当对一张表进行增删改查的时候，加MDL读锁(共享)
当对表结构进行变更操作的时候，加MDL写锁(排他)
常见的SQL操作时，所添加的元数据锁：

5.3.4 意向锁

避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁
表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查
PPT96页
意向共享锁(IS): 由select … lock in share mode添加。与表锁共享锁(read)兼容，与表锁排他锁(write)互斥
意向排他锁(IX): 由insert、update、delete、select…for update添加。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放

可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;

5.4 行级锁

5.4.1 介绍

InnoDB的数据是基于索引的，行锁是对索引上的索引项加锁来实现的，而不是对记录加的锁
对于行级锁，主要分为以下三类：
行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete
间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读
临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap

5.4.2 行锁

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁
常见的SQL语句，在执行时，所加的行锁如下：

天外流星LX

关注

1
点赞
踩
60

收藏

觉得还不错? 一键收藏
2
评论
黑马程序员MYSQL进阶笔记（超详细）

数据库系统维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以实现高级查找算法执行的SQL语句为：无索引情况：全表扫描有索引：改进B+treeMySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构不同的存储引擎对于索引结构的支持情况视图（View）是一种虚拟存在的表视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表视图只保存了查询的SQL逻辑，不保存查询结果。
复制链接

扫一扫

专栏目录