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来查找历史版本
- 隐藏字段
- undo log
- ReadView
8.运维
1.日志
1.错误日志
2.二进制日志
-
二进制日志(binlog)记录了所有DDL语句和DML语句,但不包括数据查询(select、show)语句
-
作用:1.灾难时的数据恢复2.mysql主从复制
show variables like '%log_bin%'
-
-
日志格式
-
查看日志
-
删除日志
3.查询日志
4.慢查询日志
2.主从复制
-
主数据库的DDL、DML操作通过二进制日志传到从库服务器中,然后再从库进行执行
-
作用:
1.主库出现问题,快速切换从库提供服务
2.实现读写分离,降低主库访问压力
3.可以在从库执行备份,以免备份期间影响主库服务
-