SQL优化初步学习

1.阿里云服务器购买搭建https://blog.csdn.net/m0_37135879/article/details/80148444

2.创建表

create table USER(
id int not null primary key,
name varchar(50) not null,
number varchar(50) not null,
marjor varchar(50) not null,
numberid int not NULL
);

3.创建存储过程

DROP PROCEDURE proc_user;

DELIMITER $$
CREATE PROCEDURE proc_user()
      BEGIN
				DECLARE num INT DEFAULT 1;
DECLARE nameleft VARCHAR(50) DEFAULT 'name';
DECLARE numberleft VARCHAR(50) DEFAULT 'number';

				WHILE num<10001 
				DO
       SET  nameleft=CONCAT('name',num);
       SET  numberleft=CONCAT('number',num);

			  INSERT INTO user (id,name,number,major,numberid) VALUES(num,nameleft,numberleft,'计算机',2);
       SET  num=num+1;
        END WHILE;
SELECT * FROM user;
    END
      $$
DELIMITER ;
call proc_user();

消耗时间

[SQL]

call proc_user();
受影响的行: 0
时间: 1246.196s

4.更新数据类型

Update user set major='物联网' , numberid=3 where id BETWEEN 20 and 600;
Update user set major='软件工程' , numberid=4 where id BETWEEN 601 and 1300;
Update user set major='机器人' , numberid=5 where id BETWEEN 1301 and 1900;
Update user set major='单片机' , numberid=5 where id BETWEEN 1201 and 1400;

5.查询时使用*效果

不加索引情况:

查询时使用*

explain select * from user  where id BETWEEN 1201 and 1600;
受影响的行: 0
时间: 0.022s

查询时指定列

explain select id,name,number,major,numberid from user  where id BETWEEN 1201 and 1600;
受影响的行: 0
时间: 0.001s

6.SQL优化(对参考文章精简,附加上个人理解例子)

__________________________________________

基本优化

__________________________________________
①使用exists、between and 代替in
②使用union all代替or
举例:select name from user where id<2 union all select name from user where class='计算机’代替select name from user where id<2 or class=‘计算机’
③order by、group by后面的字段加索引
④where子句后边少用IS NULL,初始建表时设置字段为NOT NULL、默认值
⑤每次用户查询使用limit限制数据返回数量,比如用户在前端查看商品时分页,select g.id,g.name,g.description from goods g;
⑥where子句后边少用!=、<>
⑦order by、group by等使用索引字段情况下,如果该索引是复合索引,比如(id,uid),则order by 后的字段第一个应该是id而不是uid,这是最左前缀原则
⑧where子句后少用函数
比如查询创建日期为2005-11-30的记录
使用函数效率低写法:select id from newuser where datediff(day,createdate,’2005-11-30′) = 0
效率高写法:select id from newuser where createdate >= ‘2005-11-30’ and createdate < ‘2005-12-1’
⑨少用*
比如select count(*)from user,select * from user
⑩字段类型尽可能使用数字型,使用varchar代替char

__________________________________________

高级优化

__________________________________________
①使用表变量代替临时表,避免频繁创建和删除临时表
②小型数据集使用 FAST_FORWARD 游标,中大型数据则是使用基于集的方法
③大型事务拆分,比如更新1001~100000记录里职业类型,可以将其拆分成很多个小事务,比如事务1更新1001-2000,事务2更新2001-3000…
④在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF
⑤在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。(MySQL不支持select into操作)
⑥如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
⑦使用全文检索来代替like关键词
⑧若某个字段比如name有多种情况,比如"张三"“李四”“王五”“赵六”,而且频繁访问,则应将该字段作为索引,若是只有几种情况比如性别字段则不应将这个字段作为索引(PS:刚刚面试没想起这个,现在补上)

7.全文检索添加

//建表时添加全文检索
create table zzh
(
id int not null primary key,
name varchar(50) not null,
number varchar(50) not null,
major varchar(50) not null,
numberid int not NULL,
FULLTEXT KEY full_major (major)
);
//建表后追加全文检索
ALTER TABLE zzh ADD FULLTEXT full_name(name);
//使用全文检索
ALTER TABLE user ADD FULLTEXT userfull_name(name);
SELECT *,MATCH (name) AGAINST ('张三') from user;

//经过本人测试,不支持中文检索,下面为查询全文检索权重语句

SELECT *,MATCH (name) AGAINST ('zhangsan') from user where id BETWEEN 7020 and 7028;

SELECT *,MATCH (name) AGAINST ('张三') from user where id BETWEEN 7020 and 7028;

update user set name='zhangsanlisiwang' where id=1006;
//查询结果
7022	zhangsan	number7022	计算机	2
2006	zhangsan	number2006	计算机	2

全文检索扩展:

使用复合词汇全文检索

update user set major='zhangsan' where id=4036;
update user set major='zhangsan' where id=4006;
update user set name='zhangsan' where id=5006;
//查询name或者major为'zhangsan'
ALTER TABLE user ADD FULLTEXT full_namemajor(name,major);
SELECT * from user where MATCH (name,major) AGAINST ('zhangsan');

//结果
4006	name4006	number4006	zhangsan	2
4036	name4036	number4036	zhangsan	2
7022	zhangsan	number7022	计算机	2
2006	zhangsan	number2006	计算机	2
5006	zhangsan	number5006	计算机	2

8.索引添加

//创建索引,对user表字段name创建索引,索引名字:firstindex

//普通创建索引
create index firstindex on user(name);
//修改表结构创建索引
DROP INDEX firstindex ON user;
ALTER table user ADD INDEX firstindex(name);
//建表时创建索引
create table newTEMPTABLE(
id INT PRIMARY KEY NOT NULL,
name VARCHAR(50),
INDEX firstindex (name)
);

//查看有多少索引

SHOW INDEX from newTEMPTABLE;

//删除索引

DROP INDEX firstindex ON user;

9.临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间.

临时表用法:可用于查询数据返回

CREATE TEMPORARY TABLE my_temp AS
(
    SELECT *  FROM user
    LIMIT 0,10000
);
select id from my_temp where major='机器人';

10.游标VS基于集的方法

比如想一行一行的读取数据
游标:在数据库里一行一行读取处理,对每行数据操作在数据库那儿
基于集:将大量数据取出来,再使用java等语言逐个处理(比如先查询数据返回结果保存到list,list再遍历),对每行数据操作在java那儿

11.复制表

//复制表结构
CREATE TABLE newuser LIKE user;
//复制表数据
INSERT INTO newuser SELECT * FROM user;
//同时复制表结构和数据,数据只复制100条
create table newcopy select * from user limit 100;

12.什么时候用exists、between and 、IN呢?

IN

IN:前面的表的记录数量大于后面的表,使用IN
以select * from user where id in(select id from studentuser);

between and

//数据连续使用 between and

exists

exists:前面的表记录数量小于后面的表,使用EXISTS

13.总结:数据库数据量还是太少了,后面再次执行语句发现语句执行时间都是0.001s,SQL优化实践效果欠缺,还是参考别人的总结吧!https://www.cnblogs.com/yunfeifei/p/3850440.html

14.数据库优化参考

https://www.cnblogs.com/yunfeifei/p/3850440.html

15.待完善:触发器