项目实战2—SQL优化操作1

一.存储过程建表

建两张30万条数据的表以做测试用

1.user_course_info

delimiter $$ # 定义结束符

drop procedure if exists addTestDataOne; # 存储过程名叫：addTestData

create procedure addTestDataOne()

begin

declare number int;

set number = 1;

while number <= 300000 #插入N条数据

do

insert into user_course_info(id,info_id,course_id,course_name,class_id,class_name,is_delete,create_time,update_time,remark) #需要添加值得字段

values(number,concat('info',number),concat('couresdId',number),concat('courseName',number),concat('classId',number),concat('className',number),concat('0'),concat('2023-03-03'),concat('2023-03-03'),concat('备注',number)); # 对应字段的值

set number = number + 1;

end

while;

end $$

调用存储过程添加30万条数据

call addTestDataOne();

2.user_info表

delimiter $$ # 定义结束符

drop procedure if exists addTestDataTwo; # 存储过程名叫：addTestData

create procedure addTestDataTwo()

begin

declare number int;

set number = 1;

while number <= 300000 #插入N条数据

do

insert into user_info(id,ding_phone,ding_name,chaoxing_phone,chaoxing_name,is_delete,create_time,update_time,remark) #需要添加值得字段

values(number,concat('dingPhone',number),concat('dingName',number),concat('chaoxingPhone',number),concat('chaoxingName',number),concat('0'),concat('2023-03-03'),concat('2023-03-03'),concat('备注',number)); # 对应字段的值

set number = number + 1;

end

while;

end $$

调用存储过程添加30万条数据

call addTestDataTwo();

二.SQL优化方案

1.相同数据类型与不同数据类型

1.1 相同

执行SQL查询

用时0.163s

1.2 不同

user_course_info表id字段数据类型改为bigint

执行SQL查询

用时1.312s

2.group by 改用 distinct

group by：

SELECT

aui.ding_phone,

aui.ding_name,

aui.chaoxing_name,

aui.chaoxing_phone

FROM

(SELECT id FROM `user_course_info` WHERE course_id = 'couresdId99099' AND class_id = 'classId99099' AND is_delete = 0 GROUP BY id)as auci

left JOIN user_info as aui on auci.id=aui.id

distinct：

SELECT

aui.ding_phone,

aui.ding_name,

aui.chaoxing_name,

aui.chaoxing_phone

FROM

( SELECT DISTINCT id FROM `user_course_info` WHERE course_id = 'couresdId99099' AND class_id = 'classId99099' AND is_delete = 0 ) auci

LEFT JOIN user_info aui ON auci.id = aui.id

关于DISTINCT

DISTINCT的作用是查询不重复记录的条数，其智能放在查询字段的开头（放在第一个参数），并且智能在SELECT语句中使用。因为默认查询返回的就是所有结果，所以不能与all同时使用。