关于SQL的优化

目录

SQL优化

1.SQL的查询尽量不要是用*号,而是使用具体的字段

2.尽量不要在where子句中使用or连接

3.使用varchar代替char

4.尽量使用数字来代替字符串

5.查询尽量避免返回大量数据

6.使用explain来分析SQL执行计划

7.优化like语句

8.索引不宜太多,一般在5个之内

9.索引不适合建在有大量重复数据的字段上

11.避免在where中对字段进行表达式操作

12.避免在where子句中使用!=或<>操作符

13.去重distinct过滤字段要少

14.批量插入性能提升

15.批量删除优化

16.伪删除设计

17.提高group by语句的效率

18.复合索引最左特性

19.排序字段创建索引

20.删除冗余和重复的索引

21.不要有超过5个以上的表连接

22.inner join、left join、right join,优先使用inner join


SQL优化

 1.SQL的查询尽量不要是用*号,而是使用具体的字段

字段多时,大表能达到100多个字段甚至200多个字段,只取需要的字段,节省资源并且减少网络开销,select*进行查询时,很可能不会用到索引,就会造成全表扫描

反例:

SELECT * FROM student

正例: 

SELECT id,NAME FROM student

2.尽量不要在where子句中使用or连接

使用or可能会使索引失效,从而全表扫描,对于or没有索引的salary这种情况,假设他走了id的索引,但是走到salary查询条件时,他还得全表扫描

反例:

SELECT * FROM student WHERE id=1 OR salary=30000

正例:


# 分开两条sql写
 
SELECT * FROM student WHERE id=1
 
SELECT * FROM student WHERE salary=30000

  3.使用varchar代替char

varchar变长字段按数据内容实际长度存储,存储空间小,可以节省存储空间,char按声明大小存储,不足补空格,其次对于查询来说,在一个相对较小的字段内收货,效率更高

反例:

`deptname` char(100) DEFAULT NULL COMMENT '部门名称'

正例:

`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'

 4.尽量使用数字来代替字符串

这样使用会提高查询效率,节省空间

主键(id):primary key优先使用数值类型int,tinyint
性别(sex):0-代表女,1-代表男;数据库没有布尔类型,mysql推荐使用tinyint
支付方式(payment):1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
服务状态(state):1-开启、2-暂停、3-停止
商品状态(state):1-上架、2-下架、3-删除

5.查询尽量避免返回大量数据

如果数据返回量过大,会造成查询时间过长,网络传输时间过长,大量数据返回也可能没有实际意义,如返回成千上万条,客户也看不过来

通常采用分页:一页10/20/50/100条

 6.使用explain来分析SQL执行计划

SQL很灵活,一个需求可以多实现,那哪个最优呢?SQL提供了explain关键字,它可以分析你的SQL执行计划,看他是否最佳.explain主要查看SQL中是否用了索引

EXPLAIN
 
SELECT * FROM student WHERE id=1

 7.优化like语句

模糊查询,我们最喜欢的就是使用like,但是like很可能让你们索引失效

反例:

EXPLAIN
 
SELECT id,NAME FROM student WHERE NAME LIKE '%1'
 
EXPLAIN
 
SELECT id,NAME FROM student WHERE NAME LIKE '%1%'

正例:

EXPLAIN
 
SELECT id,NAME FROM student WHERE NAME LIKE '1%'

8.索引不宜太多,一般在5个之内

8.1索引并不是越多越好,虽然提高了查询效率,但却会降低插入和更新的速度

8.2索引可以理解为一张表,其可以存储数据,其数据就要占空间

8.3索引表的一个特点,其数据是排序的,排序需要花时间

8.4insert或update时可能会重建索引,如果数据量巨大,重建将进行记录的重新排序,所以建索引需要慎重考虑,视情况来定

8.5一个表的索引最好不要超过5个,若太多需要考虑一些索引是否有存在的必要

9.索引不适合建在有大量重复数据的字段上

如性别字段,因为SQL优化器是根据表中数据量来进行查询优化的,如果索引有大量重复数据,Mysql查询优化器推算发现不走索引的成本更低,很有可能就会放弃索引了

10.where限定查询数据

数据中假设就一个男性记录

反例:

SELECT id,NAME FROM student WHERE sex='男'

正例:

SELECT id,NAME FROM student WHERE id=1 AND sex='男'

理由:需要什么数据,就去查什么数据,避免返回不必要的数据,节省开销

11.避免在where中对字段进行表达式操作

反例: 

EXPLAIN
 
SELECT * FROM student WHERE id+1-1=+1

正例:

EXPLAIN
 
SELECT * FROM student WHERE id=1

理由:SQL解析时,如果字段相关的是表达式就会进行全表扫描

12.避免在where子句中使用!=或<>操作符

应尽量避免在where子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描,记住现实业务优先,实在没办法就只能使用,如果不能使用,SQL就也无需支持了

反例:

EXPLAIN
 
SELECT * FROM student WHERE salary!=3000
 
EXPLAIN
 
SELECT * FROM student WHERE salary<>3000

理由:使用!=<>很可能会让索引失效

13.去重distinct过滤字段要少

索引失效:

EXPLAIN
 
SELECT DISTINCT * FROM student

索引生效:

EXPLAIN
 
SELECT DISTINCT id,NAME FROM student
 
EXPLAIN
 
SELECT DISTINCT NAME FROM student

理由:带distinct的语句占用cpu时间高于不带distinct的语句,因为当查询很多字段时,如果使用distinct,数据库引擎就会对数据进行比较,过滤掉重复数据,然而这个比较,过滤的过程占用系统资源,比如CPU时间

14.批量插入性能提升

大量数据提交,上千,上万,批量性能非常快,mysql独有

多条提交:

INSERT INTO student (id,NAME) VALUES(4,'Lisa');
 
INSERT INTO student (id,NAME) VALUES(5,'Kobe');

批量提交:

INSERT INTO student (id,NAME) VALUES(4,'Lisa'),(5,'Kobe');

理由:默认新增SQL有事务控制,导致每条都需要事务开启和事务提交,而批量处理是一次事务开启和提交,自然速度飞升(数据量小的时候体现不出来)

15.批量删除优化

避免同时修改或删除过多数据,因为会造成cpu利用率过高,会造成锁表操作,从而影响别人对数据库的访问

反例:

delete from student where id <100000;

正例:

for(){
 
delete student where id<500;
 
}
 
delete student where id>=500 and id<1000;

理由:一次性删除太多数据,可能造成锁表,会有lock wait timeout exceed的错误,索引建议分批删除

16.伪删除设计

商品状态(state):1-商家 , 2-下架 , 3-删除

理由:

1.这里删除知识一个标识,并没有从数据库中真正删除,可以作为历史记录备查

2.同时,一个大型系统中,表关系是非常复杂的,如电商系统中,商品作废了,但如果直接删除商品详情,物流信息中可能都有其引用

3.通过where state=1或者where state=2 过滤掉数据,这样伪删除的数据用户就看不到了,从而不影响用户的使用

4.操作速度快,特别数据量很大的情况下

17.提高group by语句的效率

可以在执行到该语句前,把不需要的记录过滤掉

反例:先分组,再过滤

select job,avg(salary) from employee
group by job
having job ='president' or job = 'managent';

正例:先过滤,后分组

select job,avg(salary) from employee
where job ='president' or job = 'managent'
group by job;

18.复合索引最左特性

创建复合索引也就是多个字段

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)

满足复合索引的左侧顺序,哪怕只是部分,复合索引生效

EXPLAIN
 
SELECT * FROM student WHERE NAME='Kobe'

没有出现左边的字段,则不满足最左特性,索引失败

EXPLAIN
 
SELECT * FROM student WHERE salary=3000

复合索引全使用,按左侧顺序出现name,salary,索引生效

EXPLAIN
 
SELECT * FROM student WHERE NAME='Kobe' AND salary=3000

虽然违背了最左特性,单mysql执行SQL时会进行优化,底层进行颠倒优化

EXPLAIN
 
SELECT * FROM student WHERE salary=3000 AND NAME='Kobe'

理由:复合索引已称为联合索引,当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则,联合索引不满足最左原则,索引一般会失效,但是这个还跟Mysql优化器有关的

19.排序字段创建索引

什么样的字段才需要创建索引呢?原则就是where和order by中常出现的字段就创建索引

使用*,包含了未索引的字段,导致索引失效

EXPLAIN
 
SELECT * FROM student ORDER BY NAME;

name字段有索引

EXPLAIN
 
SELECT id,NAME FROM student ORDER BY NAME

排序字段为创建索引,性能就慢

EXPLAIN
 
SELECT id,NAME FROM student ORDER BY sex

20.删除冗余和重复的索引

SHOW INDEX FROM student

创建索引index_name

ALTER TABLE student ADD INDEX index_name (NAME)

删除student表的index_name索引

DROP INDEX index_name ON student ;

修改表结果,删除student表的index_name索引

ALTER TABLE student DROP INDEX index_name ;

主键会自动创建索引,删除主键索引

ALTER TABLE student DROP PRIMARY KEY ;

21.不要有超过5个以上的表连接

21.1关联的表个数越多,编译的时间和开销也就越大

21.2每次关联内存中都生成一个临时表

21.3应该把连接表拆开成较小的几个执行,可读性就更高了

21.4如果一定需要连接很多表才能得到数据,那么就是这个表设计太辣鸡了

21.5阿里规范中,建议多表联查三张表以下

22.inner join、left join、right join,优先使用inner join

三种连接如果结果相同,优先使用inner join,如果使用left join左边表尽量小

inner join 内连接,只保留两张表中完全匹配的结果集

left join 会返回左表所有的行,即使在右表中没有匹配的记录

right join 会返回右表的所有行,即使在左表中没有匹配的记录

理由:如果inner join是等值链接,返回的行数比较少,所以性能会好一点,同理,使用左连接,左表数据结果尽量小,条件尽量放到左边处理,意味着返回的行数可能比较少,这是mysql优化原则,就是小表驱动大表,小的数据集驱动大的数据集,从而让性能更优化

  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值