mysql学习记录

最新推荐文章于 2024-05-26 17:47:14 发布

JustMove0n

最新推荐文章于 2024-05-26 17:47:14 发布

阅读量651

点赞数

分类专栏：数据库文章标签： mysql 学习数据库

本文链接：https://blog.csdn.net/weixin_53060535/article/details/125578904

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.mysql存储引擎

1.mysql常见引擎

InnoDB
- mysql默认使用的存储引擎，是一种兼顾高可靠性和高性能的存储引擎。
- 特点：
  
  DML操作遵循acid模型，支持事物
  
  行级锁，提高并发性能
  
  支持外键，保证数据的完整性和正确性
MyISAM
- 早期mysql默认的引擎，myd文件存储数据，myi存储索引
- 特点:
  
  不支持事物，不支持外键
  
  支持表锁，不支持行锁
  
  访问速度快
Memory
- Memory引擎的表数据存储在内存中，由断电影响这些表只能作为临时表或缓存使用
- 特点：
  
  内存存放
  
  支持hash索引

2.索引

1.介绍

索引是mysql高效获取数据的数据结构

2.索引结构

mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构

3.索引分类

InnoDB存储引擎中，根据索引的存储形式，又可以分为两种

1.聚集索引

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

叶子结点挂载对应key的行数据

2.非聚集索引（二级索引）

二级索引叶子结点存放对应的主键id，通过回表查询拿到对应的行数据

4.性能分析

1.查看执行次数

SHOW GLOBAL STATUS LIKE 'Com_______'

2.慢sql查询

慢查询日志记录了执行时间超过制定参数（long_query_time 单位：秒，默认10s）的所有sql语句的日志
```
SHOW VARIABLES LIKE 'show_query_log'
```
慢查询日志默认没有开启，需要在**/etc/my.cnf** 文件中配置
```
# 开启mysql慢查询的日志
show_query_log =1 
# 设置慢日志的时间
long_query_time=2 
```
查看慢日志文件中记录的信息**/var/lib/mysql/localhost-show.log**

3.profile

执行一系列的业务sql指令，然后通过如下指令查看指令的耗时情况

# 查看当前数据是否支持
select @@have_profiling
# 查看是否开启
select @@profiling
# 开启profiling
set profiling =1
# 查看每一条sql的耗时情况
show profile;
# 查看指定query_id的sql语句在各个阶段的耗时情况
show profile for query query_id;

4.explan执行计划

执行计划各字段含义
- id ： select查询的序列号，表示查询中执行select子句或者操作表的顺序（id值越大越先执行，相等顺序执行）
- select_type：表示select的类型，常见取值SIMPLE（简单表，既不使用表连接或子查询）PRIMARY（主查询，即最外层的查询）、UNION（UNION第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含的子查询）等
- type：表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、range、index、all
- possible_key：显示可能应用在这张表上的索引，一个或者多个
- key：实际使用的索引，如果为null则没有使用索引
- key_len：表示索引中使用的字节数，改值为索引字段最大可能长度，并非实际长度，在不损失精度性的前提下，长度越短越好
- rows：认为必须要执行查询的行数，innodb引擎中是一个估计值，可能并不总是准确的
- filtered：返回结果行数占需读取行数的百分比，filtered的值越大越好

5.索引失效场景

没有遵循最左匹配原则，联合索引中，尽量避免使用< > 因为会造成范围查询右侧的列索引失效
尽量不要再索引上进行运算操作，否则索引会失效
使用模糊查询导致索引失效
用or分割开的条件，前后如果有一个没有索引，就不会走索引
isnull 和is not null 走不走索引取决于表中数据的分布例如某个字段都有值少部分没值，使用not null 就会走索引

6.索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引
常作为查询条件where、排序、分组的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，检索的效率越高
如果是字符串字段且长度较长，可以针对字段的特点建立前缀索引
尽量使用联合索引，减少单列索引，查询时联合索引很多时候覆盖索引，节省存储空间，避免回表，提高查询效率
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价越大，会影响增删改的效率
如果索引列不能存储null值，在创建表时使用notnull约束它。当优化器知道每列是否包含null值，它可以更好确定哪个索引最有效地用于查询

3.sql优化

1.百万数据导入

2.主键设计原则

满足业务需求的情况下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，使用自增主键（页分裂）
尽量不要使用uuid做主键或者其他自然主键
业务操作时，避免对主键的修改

3.orderby优化

根据排序字段建立合适的索引，多字段排序时遵循最左匹配原则
尽量使用覆盖索引而不是select *
多字段排序，一个desc一个asc，此时需要注意联合索引在创建时的规则（acs/desc）

4.count优化

count（*） = count（1） > count（主键）> count（字段）
- count(1) :引擎遍历整张表，但不取值，服务层对于返回的每一行放一个值进去，然后按行累加
- 因为count（*）和count（1）不会读取数据而是直接将行进行累加

4.视图

视图是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来定义视图中查询中使用的表，并且是在使用视图中动态生成的。

简单来说视图只保存了查询的sql，不保存查询结果。

# 创建视图
CREATE or REPLACE VIEW dept_v1 as SELECT id,dept_name  FROM tb_dept
# 查看视图创建语句
show CREATE VIEW dept_v1
# 查看视图数据
SELECT * FROM  dept_v1
# 修改视图
ALTER VIEW dept_v1 as SELECT dept_name FROM tb_dept
# 删除视图
DROP VIEW dept_V1

视图的检查选项

# cascaded 级联
CREATE or REPLACE VIEW v1 as  SELECT id,dept_name  FROM tb_dept WHERE id  <=20 
CREATE or REPLACE VIEW v2 as SELECT id,dept_name FROM  v1 WHERE id >= 1 with cascaded check option;

5.存储过程

# 创建存储过程
create PROCEDURE p1()
BEGIN
 SELECT count(*) from tb_dept;
END;
# 调用存储过程
call p1();
#查看	
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'report_manager_db'
SHOW CREATE PROCEDURE P1;
#删除
DROP PROCEDURE IF EXISTS p1;

1.变量

1.系统变量

系统变量是Mysql服务器提供的，属于服务层面。分为全局变量和会话变量

2.用户自定义变量

用户自已定义的变量，用户变量不需要提前声明，在用的时候直接“@变量名”即可，作用域为当前连接

3.局部变量

局部变量是根据需定义的局部生效的变量，在访问前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的作用范围是其声明的BEGIN…END块

2.流程控制

1.if判断

if 条件1 then
elseif 条件2 then
else
end if；

2.参数

用法

create procedure 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
begin
sql语句
end;

3.case

case case_value
   when when_value1 then statement_list1
   when when_value2 then statement_list2
   else statement_list
end case;

4.while

while 条件 do
   sql逻辑
end while

5.repeat

有条件的控制语句循环，当满足条件时退出
```
repeat 
sql逻辑
UNTIL 条件
end repeat;
```

6.loop

loop实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop可以配合以下2个语句使用
- LEAVE: 配合循环使用，退出循环
- ITERATE：用在循环中，作用是跳出当前循环，进入下一次循环

3.游标

游标是用来存储结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE

4.条件处理程序

5.存储函数

6.触发器

6.锁

1.表级锁

1.表共享读锁

可以同时对一张表读，但只能有客户端一个写

2.表独占写锁

只能有一个客户端对表读写操作

3.元数据锁

4.意向锁

2.行级锁

行级锁：每次操作锁住对应的数据。锁定粒度最小，发生锁冲突概率最低，并发度最高，INNODB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁实现的，而不是对记录加的锁，对于行级锁分为以下三类
- 行锁：锁定单行记录的锁，防止其他事物对此进行udpate、delete。在RC、RR隔离级别下都支持
- 间隙锁：锁定索引记录间隙（不包含该记录），确保索引记录间隙不变，防止其他事物在这个间隙进行insert，产生幻读
- 临建锁：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙gap。

1.行锁

行锁的两种类型

2.间隙锁/临建锁

7.innoDB引擎

1.逻辑存储结构

2.内存结构

Buffer Pool:缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到新的磁盘，从而减少磁盘IO，加快处理速度。
- 缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：
  
  1.free page ：空闲page，未被使用
  
  2.clean page：被使用的page，数据没有被修改过
  
  3.dirty page：脏页，被使用page，数据被修改过，页中数据与磁盘的数据产生不一致
Change Buffer
Hash Index
Log Buffer

3.磁盘结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AMlEiumg-1656772429812)(images/image-20220702204047883.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xRxtkL5B-1656772429812)(images/image-20220702204737480.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2uWxnjiW-1656772429812)(images/image-20220702204830724.png)]

4.后台线程

作用：将innodb存储引擎的缓冲池中的数据在合适的时机刷新到磁盘文件当中

5.事物原理

事物是一组操作的集合，它是一个不可分割的工作单位，事物会把所有的操作作为一个整体一起向系统提交或撤销操作，即这些操作要么同时成功，要么同时失败
- 原子性：事物是不可分割的最小操作单元，要么全部成功，要么全部失败
- 一致性：事物完成时，必须使所有的数据保持一致状态
- 隔离性：数据库系统提供的隔离机制，保证事物在不受外部并发操作影响的独立环境下运行
- 持久性：事物一旦提交或者火棍，它对数据库中的改变就是永久的
redo log 持久性
undo log 原子性

6.MVCC

MVCC（多版本并发控制）。指维护一个数据的多版本，使得读写操作没有冲突，快照读为Mysql实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。作用：在快照读的时候要通过mvcc来查找历史版本
隐藏字段