东莞专业的网站制作有哪些,手机主页网址大全,数字营销证书,深圳建网站哪个济南兴田德润有活动吗1. SQL排查
1.1 慢查询日志: mysql提供的一种日志记录, 用户记录MySQL中响应时间超过阈值的SQL语句(long_query_time, 默认10秒), 慢查询日志默认是关闭的, 建议开发调优时打开, 最终部署的时候关闭
1.1.1 检查是否开启了慢查询日志
show variables like %slow_query_log%;临…1. SQL排查
1.1 慢查询日志: mysql提供的一种日志记录, 用户记录MySQL中响应时间超过阈值的SQL语句(long_query_time, 默认10秒), 慢查询日志默认是关闭的, 建议开发调优时打开, 最终部署的时候关闭
1.1.1 检查是否开启了慢查询日志
show variables like %slow_query_log%;临时开启:
set global slow_query_log 1; -- 在内存中开启
exit;
service mysql restart永久开启:
vim /etc/my.cnf -- 追加配置
[mysqld]
slow_query_log1
slow_query_log_file/var/lib/mysql/localhost-slow.log1.1.2 慢查询阈值
show variables like %long_query_time%; -- 查看
-- 临时设置阈值
set global long_query_time 3;
永久设置阈值
vim /etc/my.cnf 中追加配置
[mysqld]
long_query_time3-- 查询
select sleep(4);
select sleep(3);
-- 查询超过阈值的SQL
show global status like %slow_queries%;
-- 慢查询的sql被记录在了日志中,因此可以通过日志查看具体的慢SQL
cat /data/mysql/zizhou-slow.log 9.2 通过mysqldumpslow工具查看慢SQL,可以通过一些过滤条件,快速查找需要定位的慢SQL
mysqldumpslow --help
-- 获取返回记录最多的3个SQL
mysqldumpslow -s r -t 3 /data/mysql/zizhou-slow.log
-- 获取访问次数最多的3个SQL
mysqldumpslow -s c -t 3 /data/mysql/zizhou-slow.log
-- 按照时间排序, 前10条包含left join 查询语句的SQL
mysqldumpslow -s t -t 10 -g left join /data/mysql/zizhou-slow.log
-- 语法
mysqldumpslow 各种参数 慢查询日志的文件mysqldumpslow命令的帮助:
2. 分析海量数据
2.1 模拟海量数据, 存储过程(无return)/存储函数(有return)
create database test_data;
use test_datacreate table dept(dno int(5) primary key default 0,dname varchar(20) not null default ,loc varchar(30) not null default
)engineinnodb default charsetutf8mb4;create table emp(eid int(5) primary key,ename varchar(20) not null default ,job varchar(20) not null default ,deptno int(5) not null default 0
)engineinnodb default charsetutf8mb4;2.1.1 通过存储函数,插入海量数据
-- 创建存储函数
delimiter $ create function randstring(n int) returns varchar(255) begindeclare all_str varchar(100) default abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ ;declare return_str varchar(255) default ;declare i int default 0 ; while in do set return_str concat( return_str, substring(all_str, FLOOR(1rand()*52) ,1) );set ii1 ;end while ;return return_str;end $ delimiter $ create function ran_num() returns int(5)begindeclare i int default 0;set i floor( rand()*100 ) ;return i ;end $2.1.2 通过存储过程,插入海量数据
-- emp表
delimiter $ create procedure insert_emp( in eid_start int(10),in data_times int(10))begin declare i int default 0;set autocommit 0 ;repeatinsert into emp values(eid_start i, randstring(5) ,other ,ran_num()) ;set ii1 ;until idata_timesend repeat ;commit ;end $-- dept表
delimiter $ create procedure insert_dept(in dno_start int(10) ,in data_times int(10))begindeclare i int default 0;set autocommit 0 ;repeatinsert into dept values(dno_starti ,randstring(6),randstring(8)) ;set ii1 ;until idata_timesend repeat ;commit ;end$2.1.3 插入数据
delimiter ;
call insert_emp(1000,800000) ;
call insert_dept(10,30) ;2.1.4 问题解决
如果报错: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) 是因为存储过程/存储函数在创建时, 与之前开启的慢查询日志冲突了 解决冲突:
-- 临时解决(开启log_bin_trust_function_creators)
show variables like %log_bin_trust_function_creators%;
set global log_bin_trust_function_creators 1;-- 永久解决
vim /etc/my.cnf追加配置
[mysqld]
log_bin_trust_function_creators 12.2 分析海量数据
2.2.1 profiles
show profiles; -- 默认关闭
show variables like %profiling%;
set profiling on;show profiles 会记录profiling打开之后的全部SQL查询语句所花费的时间. 缺点: 不够精确,只能看到总共消费的时间,不能看到各个硬件消费的时间(cpu, io)
2.2.2 精确分析: sql诊断
show profile all for query 上一步查询的Query_id;
show profile cpu,block io for query 上一步查询的Query_id;2.2.3 全局查询日志: 记录开启之后的全部SQL语句(这次全局的记录操作仅仅在于调优, 开发过程中打开即可, 在最终部署的时候一定要关闭)
show variables like %general_log%;
-- 执行的所有SQL记录在表中
set global general_log 1; -- 开启全局日志
set global log_outputtable; -- 设置将全部SQL记录在表中-- 执行的所有SQL记录在文件中
set global log_outputfile;
set global general_log on;
set global general_log_file/tmp/general.log;
-- 开启后会记录所有SQL 会被记录 mysql.general_log表中。
select * from mysql.general_log ;3. 锁机制:解决因资源共享而造成的并发问题
例如: A和B同时买最后一件衣服X A: X加锁 -- 试衣服 --下单 -- 付款 -- 打包 -- 解锁 B: 发现X已被加锁, 等待X解锁, X已售空
3.1 分类
3.1.1 操作类型:
读锁(共享锁): 对同一个数据(衣服), 多个读操作可以同时进行, 互不干扰写锁(互斥锁): 如果当前写操作没有完毕(买衣服的一系列操作), 则无法进行其他的读写操作
3.1.2 操作范围
表锁: 一次性对一张表整体加锁, 如MyISAM引擎使用表锁, 开销小, 加锁快; 无死锁, 但是锁的范围大, 容易发生锁冲突, 并发度低行锁: 一次对一条数据加锁, 如InnoDB存储引擎使用行锁, 开销大, 加锁慢, 容易出现死锁; 锁的范围较小, 并发度高(很小概率发生高并发问题:脏读/幻读/不可重复读/丢失更新等问题)页锁
3.2 表锁示例(MyISAM)
-- (1) 表锁: 自增操作,MYSQL/SQLSERVER支持;oracle需要借助于序列来实现自增
create table table_lock(id int primary key auto_increment,name varchar(20)
)enginemyisam;
insert into table_lock(name) values(a1);
insert into table_lock(name) values(a2);
insert into table_lock(name) values(a3);
insert into table_lock(name) values(a4);
insert into table_lock(name) values(a5);
commit;
-- 增加锁
lock table 表1 read/write,表2 read/write
-- 查看加锁的表
show open tables;会话session: 每一个访问数据的dos命令行、数据库客户端工具都是一个会话
-- 加读锁-- 会话0:lock table table_lock read;select * from table_lock; -- 读(查), 可以delete from table_lock where id 1; -- 写,不可以select * from emp; -- 读,不可以delete from emp where id 1; -- 写,不可以-- 结论1: 如果某一个会话对A表加了read锁, 则该会话可以对A表进行读操作,不能进行写操作;且该会话不能对其他表进行读,写操作; -- 即如果给A表加了读锁, 则当前会话只能对A表进行读操作-- 会话1(被锁的表):select * from table_lock; --读查可以delete from table_lock where id 1 ; --写会“等待”会话0将锁释放-- 会话1(其他表):select * from emp ; --读查可以delete from emp where eno 1; --写可以-- 结论2:会话0给A表加了锁;其他会话的操作: a.可以对其他表(A表以外的表)进行读,写操作 b.对A表可以进行读操作, 但写操作需要等待锁释放unlock tables; -- 释放锁-- 读锁总结: 在当前会话只锁一张A表, 只能对A表进行读操作(写操作不行), 其他表不能进行读写操作.其他会话可以对A表进行读操作,写操作需要等会话0将锁释放,对其他表可以进行读写操作-- 加写锁-- 会话0:lock table table_lock write;-- 写锁总结: 在当前会话只锁一张A表, 可以对加锁的A表进行读写操作,但是不能对其他表进行读写操作.在其他会话, 要对加锁的A表进行增删改查的操作,需要等待当前会话将锁释放2.2.1 MySQL表级锁的锁模式
MyISAM在执行查询语句(select)前, 会自动将涉及到的所有表加读锁, 在执行更新操作(DML)前, 会自动给涉及到的表加写锁, 所以对MyISAM表进行操作,会有以下情况:
对MyISAM表的读操作(加读锁), 不会阻塞其他进程(会话)对同一张表的读需求, 但会阻塞对同一张表的写需求, 只有当读锁释放后, 才能进行其他进程的写操作对MyISAM表的写操作(加写锁), 会阻塞其他进程(会话)对同一张表的读写操作, 只有当写锁释放之后, 才会执行其他进程的读写操作
2.2.2 分析表锁定
-- 查看哪些表加了锁(1代表加了锁)
show open tables;
-- 分析表锁定的严重性
show status like table%;
-- Table_locks_immediate: 即可能获取到的锁数
-- Table_locks_waited: 需要等待的表锁数(该值越大, 说明存在越大的锁竞争)一般建议:Table_locks_immediate/Table_locks_waited 5000, 建议采用InnoDB引擎, 否则使用MyISAM引擎
2.3 行锁示例(InnoDB)
create table line_lock(id int(5) primary key auto_increment,name varchar(20)
) engineinnodb;
insert into line_lock(name) values(1);
insert into line_lock(name) values(2);
insert into line_lock(name) values(3);
insert into line_lock(name) values(4);
insert into line_lock(name) values(5);
-- mysql默认自动commit, oracle默认不会自动commit; 为了研究行锁, 暂时将自动commit关闭, 以后需要通过commit提交
set autocommit 0;2.3.1 操作同样的数据
-- 会话0:写操作
insert into line_lock value(a6);
-- 会话1:写操作同样的数据
update line_lock set nameax where id 6;结论:
如果会话对某条数据进行DML操作(研究时关闭了autocommit的情况下), 则其他会话必须等待会话X结束事务(commit/rollback)后, 才能对数据a进行操作表锁是通过unlock tables, 也可以通过事务解锁, 行锁是通过事务解锁
2.3.2 操作不同的数据
-- 会话0:写操作
insert into line_lock value(8,a8);
-- 会话1:写操作
update line_lock set name ax3 where id 5;结论: 行锁,一次锁一行数据;因此, 如果操作的是不同数据, 则互不干扰
2.3.3 行锁的注意事项
1. 如果没有索引, 则行锁会转为表锁
show index from line_lock;
alter table line_lock add index idx_line_lock_name(name);-- 会话0:写操作
update line_lock set name ai where name 3;
-- 会话1:写操作, 不同的数据
update line_lock set name aix where name 4;-- 可以发现, 数据被阻塞了(加锁)
-- 原因:如果索引发生了类型转换, 则索引失效, 因此此次操作, 会从行锁转为表锁2.行锁的一种特殊情况: 间隙锁, 值在范围内, 却不存在
-- 此时line_lock表中没有id7的数据
update line_lock set name X where id 1 and id 9; -- 即在where范围中, 没有id7的数据, 则id7的数据成为间隙间隙锁: mysql会自动给间隙加索引, 即本demo中会自动给id 7的数据加间隙锁(行锁) 行锁: 如果没有where, 则实际加索引的范围就是where后面的范围, 不是实际的值
如果仅仅只是查询数据, 能否加锁? 可以! 通过for update对query语句进行加锁
set autocommit 0;
start transaction;
begin;
select * from line_lock where id 2 for update;行锁: InnoDB默认采用行锁, 相较于表锁性能消耗较大, 但有着并发能力强,效率高的优势, 因此建议高并发用InnoDB,否则使用MyISAM 行锁分析:
show status like %innodb_row_lock%;
-- Innodb_row_lock_current_waits: 当前正在等待锁的数量
-- Innodb_row_lock_time: 等待总时长,从系统启动到现在一共等待的时间
-- Innodb_row_lock_time_avg: 平均等待市场,从系统启动到现在平均等待的时间
-- Innodb_row_lock_time_max: 最大等待时长, 从系统启动到现在最大一次等待的时间
-- Innodb_row_lock_waits: 等待次数,从系统启动到现在一共等待的次数本篇是对B站颜群老师SQL优化视频的笔记梳理, 感兴趣的可以去看下视频: SQL优化
