MySQL高级
mysql安装配置
mysql配置,修改字符集编码是用于当我们创建数据库时,没有指定数据字符集编码类型时,就会用配置默认的字符集编码,因此我们最好修改了默认字符集编码为utf8,当然更重要的就是在创建数据库和表时一定要指定好utf8字符集编码,utf8mb4这种。


#vim 编辑文件时
#在当前光标的下一行输入用:按o,就会跳到下一行,插入状态
#log-bin 二进制日志,用于主从复制
#log-error 错误日志,默认是关闭的。记录严重的警告和错误信息,每次启动和关闭的详细信息等。
#查看mysql支持的存储引擎
show engines;
#查看mysql当前默认的存储引擎
show VARIABLES like '%storage_engine%';
mysql逻辑架构
架构



分层的好处
分层的好处就是分层隔离后,哪一层出了问题就找谁,方便定位查找。
Optimizer(优化器)是mysql的优化器。阿里取消了Optimizer优化器来提高执行性能,因为其sql已经表示写得最优了,不需要再经过一层优化器来耗时。
mysql架构:分层可拔插。分为4层:
- 连接层
- 服务层
- 引擎层
- 存储层
存储引擎


MyISAM与InnoDB的区别

MyISAM适合读多的场景,InnoDB适合写多特别是需要事务支持的场景。
索引
创建索引

SQL执行时间慢的原因
SQL执行时间慢的原因(慢查询):
- sql语句写得烂
- 索引失效(没有索引)
- 太多关联查询join
- mysql服务器调优及各个参数设置(缓冲、线程数等)
sql执行加载顺序
sql语句格式

机器读取顺序


7种JOIN理论


- 注意B.key is null的场景。
- mysql用union实现并集。
- mysql用union加where a.key is null 实现获取各自独立的部分。


索引原理
-
索引是Mysql高效获取数据的数据结构,也就是说索引本质是数据结构,是排好序的快速查找数据结构,底层是B+树(多路搜索树)。
-
索引会影响作用于where条件的查找和order by 的排序。
-
update更新慢的原因是除了数据在改变,索引也在改变重新建立索引。
-
索引本身也很大,不可能全部在内存中,一般会以索引文件的形式存储在磁盘中,在用到的时候再加载到内存。
Mysql索引分类
- B+树索引
- Hash索引
- full-text全文索引
- R-tree索引
索引的优势和劣势
优势:
- 提高数据检索效率,降低磁盘IO成本
- 降低数据排序成本,降低CPU消耗
劣势:
- 创建索引要占用空间
- 虽然索引会提高查询速度,但是在增删改时因为要维护索引所以会降低更新速度
- 查询语句和索引都需要通过优化才能实现高效率的查找
索引的分类
- 单值索引——一个索引只包含单个列
- 复合索引——一个索引包含多个列
- 唯一索引——索引列的值必须唯一,但允许有空值
B+树索引查找过程


哪些情况下适合建立索引

哪些情况下不适合建立索引

Explain——执行计划
-- 语句
EXPLAIN SELECT * FROM city c where c.CountryCode='NLD' ;

id——执行顺序

select_type——查询的操作类型


table——查询的表或者临时表
表示当前查询的是哪张表或者临时表。
type——执行效率类型



-- index 全索引扫描,虽然是读全表,但是因为字段id是从索引中读取的,所以比读全表要好。
select id from user;
possible_keys——可以用到的索引

key——实际用到的索引

key_len——索引中使用的字节数

ref——显示索引的哪一列被使用或者索引值是否是常量


rows——找到记录所要读取的行数

读取的行数越少越好。
Extra——额外信息

using filesort
表示没有使用到索引进行排序,通常出现在排序的字段不是索引字段,需要优化。

using temporary
表示使用了临时表保存中间结果,mysql在排序时使用了临时表,特别需要优化。

using index
使用了索引覆盖,是好的。

索引优化
索引单表优化
针对下面的查询sql在没有建立索引时的执行计划。

加索引优化,但是查询执行效率只有range级别,这是因为索引在遇到范围查找如>
大于、小于等时会导致索引实现,只用到了复合索引的第一个字段category_id
,且排序也用不到索引,是using filesort
表示没有使用到索引进行排序,需要继续优化。


继续优化,正确的索引建立
因为范围查找导致索引失效的问题,我们应该只建立两个用到索引的字段,对于范围查找的字段就不建立索引了,否则索引会失效。


索引双表优化
针对下面的左联查询sql在没有建立索引时的执行计划。

对左联查询的右表加索引优化
可以看到右表查询优化到了ref,查询rows也优化了。

对左联查询的左表加索引优化
可以看到左表查询只优化到index,查询rows数没有减少。

结论:左联查询要对右表加索引,右联则要对左表加索引。

索引三表优化
针对下面的三表左联查询sql在没有建立索引时的执行计划

优化:参考双表左联查询的结论,要在右表上加索引。


结论:与双表左联查询类似,要在右表上加索引,且查询的时候左表必须是小表,用小表驱动大表。

索引优化原则及索引失效案例












- MySQL查询语句中where条件里面的顺序并不会因为与组合索引中的顺序不同而失效,MySQL优化器会作优化。

- group by 基本上都需要排序,会有临时表产生。

索引判断题





索引口诀


排序——Order By 优化
案例分析

排序的两种方式

filesort的两种排序方式与优化
双路排序和单路排序

单路排序的问题

单路排序优化

Order by排序优化总结

Group By优化

慢查询调优
步骤

小表驱动大表


什么是慢查询

默认不开启,只有在有问题的时候才使用命令开启慢查询日志。

开启慢查询
查询脚本
-- 查看是否开启慢查询
SHOW VARIABLES LIKE '%slow_query_log%';
-- 开启和关闭慢查询 注意在mysql重启后会失效,如果要永久生效则要配置在my.cnf上
SET GLOBAL slow_query_log =1;
SET GLOBAL slow_query_log =0;
-- 查询,默认10s
SHOW VARIABLES LIKE '%long_query_time%';
-- 设置慢查询阈值
SET GLOBAL long_query_time =3;
-- 通过加global查询是否设置生效
SHOW GLOBAL VARIABLES LIKE '%long_query_time%';
-- 查询当前系统中有几条慢sql记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
本次开启生效


永久开启生效

配置如下:

慢查询日志linux文件路径


慢查询阈值

注意需要新开会话或者加global才能看到生效值

mysql日志分析工具——mysqldumpslow
#查看命令帮助
mysqldumpslow --help


show profiles——慢查询资源消耗明细

show profile查询使用参数

show profile要注意的资源消耗点

使用步骤


sql脚本
-- 查看show profile 是否打开 ,有的版本是默认开启,有的是默认关闭,开启和关闭不生效
SHOW VARIABLES LIKE '%profiling%';
-- 通过加global查询show profile是否设置生效
SHOW GLOBAL VARIABLES LIKE '%profiling%';
-- 开启和关闭profiling
SET profiling ='on';
SET profiling ='off';
-- 查询最近15条sql的明细id
show PROFILES;
-- 根据查询id查看查询的资源消耗明细
show PROFILE cpu,block io for QUERY 247;
-- 查看所有的开销信息
show PROFILE all for QUERY 247;
全局查询日志——测试环境用
永远不要在生产上开启该功能。
配置启动

sql启动——本次有效

sql脚本
-- 开启全局日志
set GLOBAL general_log=1;
SET GLOBAL log_output='TABLE';
-- 查询全局日志表
SELECT * FROM mysql.general_log;
MySQL锁机制
锁按照粒度和操作类型区分方式

表锁的加锁、解锁和查看锁情况——表锁偏读



表加读锁后的情况
session1加了读锁时:session1可以查自己锁的表,不能新增或更新自己锁的表,也不能查询或更新别的表;
session2可以读被锁的表,但不能更新被锁的表(会被阻塞);可以读和更新其他表。

表加写锁后的情况
session1加了写锁时:session1可以查自己锁的表,可以新增或更新自己锁的表;不能查询或更新别的表;
session2可以读被锁的表会被阻塞,也不能更新被锁的表;可以读和更新其他表。

结论

表锁分析


行锁——行锁偏写(写多的场景)
MyIsam与InnoDB最大的不同

并发事务带来的问题

行锁变表锁的情况
查询条件类型使用不当导致的索引失效变表锁的情况,所以在使用更新/查询时一定要注意查询条件与字段类型相匹配。

间隙锁
mysql会通过间隙锁来锁定一个范围,保证不出现幻读的情况。但是会阻塞住该范围内的插入操作。

给某行要修改的数据加行锁
被for unpdate
锁定的行在其他操作过来是会被阻塞,属于悲观锁。

行锁分析


-- 行锁情况分析
SHOW STATUS LIKE '%innodb_row_lock%';
行锁优化建议

MySQL主从复制

主从复制的过程(原理)

一主一从配置(window主linux从)
- 如果都是linux上的mysql主从复制则直接参考linux上的命令位置,修改的配置是一样的。
- 主从复制的两个服务器要能互相ping通。







其他
创建函数
注意:函数有返回值,存储过程没有返回值,只有输出值。

创建存储过程

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战