sql日志开启
mysql的sql语句日志记录
SET GLOBAL log_output = ‘TABLE’;SET GLOBAL general_log = ‘ON’;
SET GLOBAL log_output = ‘TABLE’;SET GLOBAL general_log = ‘off’;
SELECT * from mysql.general_log ORDER BY event_time DESC;
truncate table mysql.general_log;
表视图
mysql创建视图,及删除视图
– 两张表 hs_cert hs_cert_dtl
– 创建视图
CREATE VIEW cert_cert_dtl
as SELECT a.book_id,b.debit,b.lender,b.sub_id FROM hs_cert a ,hs_cert_dtl b WHERE a.phid=b.cert_id;
– 查看视图中字段信息
SELECT * FROM cert_cert_dtl;
– 查看创建视图的sql语句
show CREATE view cert_cert_dtl;
– 删除视图
drop view if EXISTS cert_cert_dtl;
mysql存储过程和存储函数
– 创建存储过程
create PROCEDURE pro_test01()
BEGIN
SELECT ‘hello mysql’;
END
– 调用存储过程
call pro_test01();
– 查询存储过程
SELECT name FROM mysql.proc WHERE db=‘ni_account’;
– 查询存储过程定义
show PROCEDURE status;
– 查询某个具体存储过程的定义
SHOW CREATE PROCEDURE ni_account.pro_test01;
– 删除存储过程
drop PROCEDURE pro_test01;
– 创建带变量的存储过程
create PROCEDURE pro_test01()
BEGIN
DECLARE num int DEFAULT 10;
SELECT CONCAT(‘num的值:’,num);
END;
– 创建存储过程变量set赋值
create PROCEDURE pro_test02()
BEGIN
DECLARE num int DEFAULT 10;
set num=num+20;
SELECT CONCAT(‘num的值:’,num);
END;
– 创建存储过程变量select…into 赋值
create PROCEDURE pro_test03()
BEGIN
DECLARE num int DEFAULT 10;
SELECT count(*) into num FROM hs_cert;
SELECT CONCAT(‘num的值:’,num);
END;
call pro_test03;
– 创建存储过程条件if else变量 赋值
create PROCEDURE pro_test04()
BEGIN
DECLARE height int DEFAULT 10;
DECLARE descp VARCHAR(50) DEFAULT ’ ';
if height>=180 then
set descp=‘大高个’;
elseif height>=170 and height< 180 then
set descp=‘中个子’;
else
set descp=‘矮个子’;
end if;
SELECT CONCAT(‘身高的值:’,height,‘身材描述:’,descp);
END;
– 创建存储过程条件参数变量 定义入参 in/out int height
create PROCEDURE pro_test05(in height int)
BEGIN
DECLARE descp VARCHAR(50) DEFAULT ‘’;
if height>=180 then
set descp=‘大高个’;
elseif height>=170 and height< 180 then
set descp=‘中个子’;
else
set descp=‘矮个子’;
end if;
SELECT CONCAT(‘身高的值:’,height,‘身材描述:’,descp);
END;
call pro_test05(100);
– 创建存储过程条件参数变量 定义入参出参 in/out int height @descp 表示会话级别
create PROCEDURE pro_test06(in height int,out descp VARCHAR(50))
BEGIN
if height>=180 then
set descp=‘大高个’;
elseif height>=170 and height< 180 then
set descp=‘中个子’;
else
set descp=‘矮个子’;
end if;
SELECT CONCAT(‘身高的值:’,height,‘身材描述:’,descp);
END;
call pro_test06(100,@descp);
SELECT @descp;
– case语法结构
CREATE PROCEDURE pro_test07 ( mon INT )
BEGIN
DECLARE jidu VARCHAR ( 20 );
CASE
WHEN mon >= 1 AND mon <= 6 THEN
SET jidu = ‘上半年’;
ELSE
SET jidu = ‘下半年’;
END CASE;
SELECT CONCAT(‘年度属于:’,jidu );
END;
call pro_test07(9);
触发器trigger
– 创建触发器 新增
create TRIGGER hs_cert_insert_trigger
after insert
on hs_cert
for each row
begin
insert into hs_cert_log (id,operate,operate_time,operate_id,params) VALUES
(null,‘insert’,now(),new.phid,
CONCAT(‘(phid:’,new.phid,
‘,cert_year:’,new.cert_year,‘,cert_month:’,new.cert_month,‘,cert_no:’,new.cert_no,‘)’));
end;
SELECT CONCAT( ‘(phid:’, ‘1’, ‘,cert_year:’, ‘2’, ‘,cert_month:’, ‘3’, ‘,cert_no:’, ‘4’ ‘)’ );
– 创建触发器 删除
create TRIGGER hs_cert_delete_trigger
after DELETE
on hs_cert
for each row
begin
insert into hs_cert_log (id,operate,operate_time,operate_id,params) VALUES
(null,‘delete’,now(),old.phid,
CONCAT(‘(phid:’,old.phid,
‘,cert_year:’,old.cert_year,‘,cert_month:’,old.cert_month,‘,cert_no:’,old.cert_no,‘)’));
end;
– 查看触发器
show TRIGGERS;
数据库引擎
– 查看数据库引擎
show ENGINES;
innodb默认引擎
执行事务、行级锁、外键。
存储文件有两个
.frm 表结构
.idb 索引和数据
执行频率查看
– 查看执行频率
– 全库查看
SHOW GLOBAL STATUS LIKE ‘Com%’;
– innodb引擎查看
SHOW GLOBAL STATUS LIKE ‘Innodb_rows%’;
查看执行的慢sql
– 查看执行的慢sql
show PROCESSLIST;
– 分析sql语句
EXPLAIN SELECT * FROM hs_cert WHERE phid=‘1235’;
explain结果 id越大,优先级越高,最先执行
id介绍
select_type
exlpain-type
explain-extra
– 查看执行的sql,分析sql语句在各个阶段的耗时
show PROFILES;
show PROFILE for query {id};
索引失效
1.范围查询右边的列,有索引可能会失效
2.索引列做运算,或者强制类型转换 (字符型的必须加单引号)
3.索引列使用<>或者like
4.or之前的列有索引,之后的条件没有索引,全部失效
5.如果走索引比不走索引慢,那么将进行全表扫描
6.null 和notnull根据所扫描的数据行占比来说,如果超过一定占比(30%)那么将不走索引
7.in走索引 not in不走索引
–索引的选择
尽量选择复合索引,不要使用单列索引
数据库最终只会选择一种最优的索引去查询,而不是全部索引
—索引查询
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/04164228cdd94885180e375585c48370.png)
–查看索引使用情况
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/e42156f0b5aceaf78ebc7eeb65d3973e.png)
大批量插入数据
load指令,直接导入数据
https://www.cnblogs.com/waynechou/p/7794939.html
https://www.cnblogs.com/kumufengchun/p/10365911.html
load data infile “/data/mysql/e.sql” into table e fields terminated by ‘,’ lines terminated by ‘\n’;
1.主键按顺序
2.关闭唯一性校验
3.手动提交事务
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/bb7e8373de6ec29ebf4ee865d5dbf978.png)
order by排序
多字段排序一定要按照索引顺序排序 会走Using index,
不然也不会走索引Using filesort
并且顺序一致要么是升序要么是降序
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/55af283b35b58d8194d73d2e7eb580c5.png)
– 排序优化
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/8a3511c7538f38d46f772de7bcedf8f2.png)
group by优化
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/66591bf9b80b2806913f86973e3c0cbf.png)
–少使用子查询,多使用连表查询![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/340ce19983876665548bd81ca418c98b.png)
建议使用union代替or
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/e6379a15607886a2894f099dfd25039a.png)
limit优化
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/c4f328e55ce40b35aeb46b243534f549.png)
– 索引使用指定
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/b2c1da92ca9a9f7aeb7cde972c5e2535.png)
–强制使用这个索引
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/9a1ba1640cb6b34137a273781c1ccc63.png)
mysql数据库参数优化
查看是否支持缓存
SHOW VARIABLES like ‘have_query_cache’;
查看缓存是否开启
SHOW VARIABLES like ‘query_cache_type’;
查看缓存大小
SHOW VARIABLES like ‘query_cache_size’;
– 查看缓存状态
SHOW STATUS like ‘Qcache%’;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/673e2037445b3df5e38a6b39e3607df2.png)
– 查询缓存
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/aaee76ddbe5cdf949ee595afe59ca701.png)
查询缓存失效
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/9e4a9480888c185e36d0d7a99d4fddbd.png)
mysql并发参数调整
– 查看最大连接数和连接报错数
SHOW VARIABLES like ‘max_conn%’;
– 查看等待连接数,超过这个数据会报错
SHOW VARIABLES like ‘back_log%’;
– 'innodb行锁等待时间
SHOW VARIABLES like ‘innodb_lock_wait_timeout’;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/bd77d523581ebfa78cba9eda31b16481.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/f265006dc11f02180220728d466a6d53.png)
mysql锁
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/9f8b15d69ecd21dafd2a38e098db5c5b.png)
– 查看锁等待
SHOW STATUS like ‘Table_locks%’;
行锁失效
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/25043d1d9c8d3a324629370fa18dc9a2.png)
– 间隙锁
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/ea7ed90ae9dff02b5ec9b654c1460f8e.png)
– 查看锁等待状态
SHOW status like ‘%innodb_row_lock%’;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/8b8e4f302c27b840f1523e38cbabed29.png)
字符串函数
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/2ff6f82a078d6b9c544fdf84e5e9d5cb.png)
聚合函数
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/a08dcb517f3baac88a894d808699607f.png)
mysql日志
mysql客户端工具
标题mysql查询执行计划
– 是否支持查询缓存
SHOW VARIABLES like’%query_cache_type%‘;
– 查询命中缓存
SHOW STATUS like’%Qcache%‘;
– 执行计划
SHOW VARIABLES like’%profiling%';
– 查询全部的执行计划
SHOW PROFILES;
– 查询单条 242是查询的id
SHOW PROFILE FOR QUERY 242;
– mvcc
readview 包括以下几个参数
1.tx_id 事务创建id
2.min_mid 当前活跃最小事务id
3.max_mid 当前活跃最大事务id
4.mids 当前活跃的事务集合
– 以下几种情况可以读到的视图 当前事务访问多版本的视图
1.当前事务id和creator_id相等
2.tx_id<min_mid
3.tx_id>max_mid 不能读到视图
4.min_mid <=tx_id<=max_mid 如果当前事务id在里面 则不能读到此视图