Day24 PythonWeb全栈课程课堂内容

于 2021-03-05 19:43:58 发布
阅读量185 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： 笔记 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/FyCcheng/article/details/114410491

文章目录

1. 排序优化

/* 创建表t1 和 创建存储过程insert_t1 */
use mytest0301;
drop table if exists t1; /* 如果t1表存在则删除表t1 */

create table `t1`( /* 创建t1表 */
	`id` int(11) not null auto_increment,
    `a` int(20) default null,
    `b` int(20) default null,
    `c` int(20) default null,
    `d` datetime not null default current_timestamp,
    primary key (`id`),
    key `idx_a_b` (`a`,`b`),
    key `idx_c` (`c`)
) engine=innodb charset=utf8mb4;

DROP PROCEDURE IF EXISTS insert_t1; /* 如果存在存储过程insert_t1，则删除 */
DELIMITER ;;
CREATE PROCEDURE insert_t1()        /* 创建存储过程insert_t1 */
BEGIN
  DECLARE i INT;                  /* 声明变量i */
  SET i=1;                        /* 设置i的初始值为1 */
  WHILE(i<=10000)DO			      /* 对满足i<=10000的值进行while循环 */
    INSERT INTO t1(a,b,c) VALUES(i,i,i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
    SET i=i+1;                       /* 将i加1 */
  END WHILE;
END;;
DELIMITER ;
CALL insert_t1();               /* 运行存储过程insert_t1 */

UPDATE t1 SET a=1000 WHERE id >9000;    /* 将id大于9000的行的a字段更新为1000 */
  • MySQL的排序方式：1.有序索引直接返回有序数据 2.通过filesort进行排序
-- a,b,c 都为索引
show index from t1;


/* 需求：从t1中查询id、c，并且以c进行排序 */
-- extra: using index 有序索引直接返回有序数据
select id,c from t1 order by c;


/* 需求：从t1中查询id、d，并且以d进行排序 */
-- extra: using filesort 通过filesort进行排序
select id,d from t1 order by d;
  • filesort是在内存中排序还是在磁盘中完成排序？
    • MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和 sort_buffer_size 配置的大小。
      • 如果 “排序的数据大小” < sort_buffer_size: 内存排序
      • 如果 “排序的数据大小” > sort_buffer_size: 磁盘排序
  • filesort有两种算法-双路排序和单路排序

    • 双路排序,MySQL4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和order by列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出

    • 单路排序,从磁盘读取查询需要的所有列,按照order by列在buffer对他们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据,并且把随机IO变成了顺序IO,但是它会使用更多的空间

    • 打包数据排序方式,类似于单路排序但在char于varchar类型的字段有所区别。

    • 优化策略调整MySQL参数：1.增加sort_buffer_size参数设置；2.增大max_lenght_for_sort_data参数的设置(以上两个参数不宜创建过大，不然的话CPU会上涨。)

order by 索引优化

-- 优化方法1：添加何时的索引。
/* 排序字段添加索引，可以在排序字段上添加索引优化排序语句。 */
explain select id,d from t1 order by d;
explain select id,c from t1 order by c;

-- 优化方法2：多个字段排序优化,可以在排序字段上添加复合索引，但是排序字段的顺序需要与复合索引中列的顺序保持一致。

-- Extra: Using filesort(a,c不为复合索引)
explain select id,a,c from t1 order by a,c;

-- Extra: Using index (a,b为复合索引)
explain select id,a,b from t1 order by a,b;

-- Extra: Using filesort(a,b为复合索引,并调换前后位置)
explain select id,a,b from t1 order by b,a;

-- 优化方法3：先等值查询再排序的优化，通过在条件字段和排序字段添加复合索引来优化
-- Extra: Using filesort 优化前
explain select id,a,d from t1 where a=1000 order by d;

-- Extra: Using index 优化后
explain select id,a,b from t1 where a=1000 order by b;

-- Extra: Using filesort,查询时使用 * 。 优化前
explain select * from t1 order by a,b;
-- 解决方法：查询需要查询的字段，尽量减少全局查询。

--Extra: Using index 优化后
explain select id,a,b from t1 where a=1000 order by a,b;

-- 优化方法4：无法利用索引排序的情况。
-- 情况1.使用到范围查询再排序
-- Extra: Using index; Using filesort
-- 原因：a,b为复合索引，对于单个a来说，b是有序的。但是对a进行范围查询，取出a字段会有多个值，而b不一定有序，因此要额外排序
explain select id,a,b from t1 where a>9000 order by a,b;


-- 情况2:使用到降序和升序，升序降序混合使用，无法使用索引。
-- Extra: Using index; Using filesort
explain select id,a,b from t1 order by a asc,b desc;

group by 索引优化

  • group by的索引优化与order by一致。

练习

索引 a_b_c(a,b,c)

order by a,b
order by a,b,c
order by a desc,b desc,c desc

where a = const order by b,c
where a = const and b = const order by c
where a = const and b > const order by b,c

order by a asc,b desc,c desc  
where g = const order by b,c   
where a = const order by c    
where a = const order by by a,d

2. 分页查询

/* 举例 */

-- 从t1表中1000条开始，查询10条
-- 1.先读取1010条，然后抛弃前1000条，最后读取到后面10条记录。执行效率低。
select a,b,c from t1 limit 1000,10;

2.1 分页优化

USE mytest0303;
DROP TABLE IF EXISTS t1;        /* 如果表t1存在则删除表t1 */

CREATE TABLE `t1` (             /* 创建表t1 */
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `a` INT(11) DEFAULT NULL,
  `b` INT(11) DEFAULT NULL,
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;	

DROP PROCEDURE IF EXISTS insert_t1; /* 如果存在存储过程insert_t1，则删除 */
DELIMITER ;;
CREATE PROCEDURE insert_t1()        /* 创建存储过程insert_t1 */
BEGIN
  DECLARE i INT;                    /* 声明变量i */
  SET i=1;                          /* 设置i的初始值为1 */
  WHILE(i<=100000)DO                  /* 对满足i<=100000的值进行while循环 */
    INSERT INTO t1(a,b) VALUES(i, i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
    SET i=i+1;                      /* 将i加1 */
  END WHILE;
END;;
DELIMITER ;                 /* 创建批量写入100000条数据到表t1的存储过程insert_t1 */
CALL insert_t1();           /* 运行存储过程insert_t1 */

2.2 根据自增且连续主键排序的分页查询

-- 主键索引：id ；索引：a，b
show index from t1;

-- 从t1表中99001条开始，查询2条
select * from t1 limit 99000,2;

-- t1 表主键是自增且连续的，使用主键查询，执行结果相同
select * from t1 where id>99000 limit 2;

/* 区别 */
-- key: null; rows 100047
explain select * from t1 limit 99000,2;

-- key: primary; rows 1000
explain select * from t1 where id>99000 limit 2;

-- 如果主键有缺失，主键不连续
-- 删除id=10
delect from t1 where id=10;

-- 输出结果为：99002 99003行
select * from t1 limit 99000,2;

-- 输出结果为：99001 99002行
-- 原因是此方法为范围查询
select * from t1 where id>99000 limit 2;

2.3 查询根据非主键字段排序的分页查询

-- 输出结果为：99001 99002行
select * from t1 order by a limit 99000,2;

-- key:null;Extra:Using filesort.
explain select * from t1 order by a limit 99000,2;
-- 原因（扫描整个索引并查找到没索引的行的成本比全表扫描的成本高）

/* 优化: 让排序时返回的字段尽可能的少 */
-- 步骤1. 排序和分页先查出主键
select id from t1 order by a limit 99000,2;

-- 步骤2. 根据主键查找到对应的记录，进行自连接
select id from t1 as f inner join (select id from t1 order by a limit 99000,2) as g on f.`id`=g.`id`;

-- Extra: Using index
explain select id from t1 as f inner join (select id from t1 order by a limit 99000,2) as g on f.`id`=g.`id`;

3. 慢查询日志

3.1 常规解决方法

分析

  1. 观察,至少跑一天,看看生产的慢SQL情况

  2. 开启慢查询日志,设置阙值,比如超过5秒钟的就是慢SQL,并抓取出来

  3. explain + 慢SQL分析

  4. how profile

  5. 进行SQL数据库服务器的参数调优(运维 or DBA来做)

总结

  • 1.慢查询的开启并捕获
  • 2.explain+慢SQL分析
  • 3.show profile查询SQL在MySQL服务器里面的执行细节
  • 4.SQL数据库服务器的参数调优

3.2 慢查询日志

  • MySQL的慢查询日志MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阙值的语句,具体指运行时间超过long_query_time值得SQL,则会被记录到慢查询日志中

  • 具体指运行时间超过long_query_time值得SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10秒以上的语句。

  • 由他来查看那些SQL超出了我们的最大忍耐时间值,比如一条SQL执行超过5秒钟,我们就算是慢SQL,希望能收集超过5秒的SQL,结合之前explain进行全面分析

3.3 使用方法

  • 查询是否开启慢查询日志

    show variables like '%show_query_log%'; 
-- off 默认未开启

  • 开启慢查询日志

    set global slow_query_log = 1;

  • 关闭慢查询日志

    set global slow_query_log = 0;

  • 打开Mysql5.7.4\data\DESKTOP-CV6THBH-slow.log文件，查看慢查询日志。也可以在my.ini文件中添加slow_query_log_file='path.log'，同样需要重启mysql服务。

  • 显示sql语句超过多长时间载入慢查询日志，默认10s

    show variables like 'long_query_times'

  • 修改默认时长

    set global long_query_time=2;

  • 伪造超过时长的sql语句

    select sleep(4);

  • 于此同时会在DESKTOP-CV6THBH-slow.log中有记录。

3.4 慢查询日志工具

s:表示按照何种方式排序

c:访问次数

l:锁定时间

r:返回记录

t:查询时间

al:平均锁定时间

ar:平均返回记录数

t:即为返回前面多少条的数据
  • 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 D:/phpStudy/PHPTutorial/MySQL/slow_log.txt
  • 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 D:/phpStudy/PHPTutorial/MySQL/slow_log.txt

4. 存储函数和存储过程

4.1 存储函数和存储过程

MySQL中提供存储过程与存储函数机制，我们将存储过程与存储函数合称为存储程序。与一般的SQL语句需要先编译然后立即执行不同，存储程序是一组为了完成某特定功能的SQL语句集，经过编译后存储在数据库中，当用户通过制定存储程序的名字并给参数来调用才会执行。

4.2 创建存储函数

语法结构如下

create function 函数名([参数名 数据类型 [, ...]]) RETURNS 返回类型

begin

	过程体
	
end

-- 调用存储函数
select 函数名(参数)
-- 只有int类型的数据类型

4.3 创建存储过程

create procedure 过程名([[in|out|inout] 参数名 数据类型[，[in|out|inou] 参数名 数据类型...]])[特性 ...]

begin

	过程体
	
end

-- 调用存储过程
call 函数名(参数)

4.4 举例

  • 创建表
-- 创建部门表
create table dept(
	id int primary key auto_increment,
	deptno mediumint not null,
	dname varchar(20) not null,
	loc varchar(13) not null
)engine=innodb default charset=gbk;

-- 创建员工表
create table emp(
    id int primary key auto_increment,
    empno mediumint not null,   
    ename varchar(20) not null, 
    job varchar(9) not null, 
    mgr mediumint not null,  
    hiredate DATE not null,  
    sal decimal(7,2) not null, 
    comm decimal(7,2) not null, 
    deptno mediumint not null   
)engine=innodb default charset=gbk;
  • 创建随机数据
-- 随机产生字符串
DELIMITER $$ /* mysql默认以';'作为分隔符，delimiter 声明结束符 */
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN /* 创建存储函数 */
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$

-- 随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $$
  • 创建存储过程
-- 插入数据
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT /* 有条件的循环 */
SET i = i + 1;
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num /* 直到i为最大值 */
END REPEAT;
COMMIT;
END $$

-- 插入数据
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept(deptno,dname,loc) VALUES ((START + i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
  • 调用数据
delimiter ; 
call insert_dept(100,10);
    
delimiter ; 
call insert_emp(100001,500000);
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值