4.sql优化

2022-11-19 18:41:11

Sql优化总结

Sql执行顺序：

**（8）**SELECT （9） DISTINCT column,…

(选择字段、去重)

**（6）**AGG_FUNC(column or expression),…

聚合函数

**（1）**FROM [left_table]

选择表

（3）<join_type> JOIN <right_table>

连接

**（2）**ON <join_condition>

连接条件

**（4）**WHERE <where_condition>

条件过滤

**（5）**GROUP BY<group_by_list>

分组

**（7）**HAVING <having_condition>

分组过滤

**（10）**ORDER BY <order_by_list>

排序

**（11）**LIMIT count OFFSET count;

分页

基础sql优化

1.查询SQL尽量不要使用select *，而是具体字段

反例：

SELECT * from student

正例：

SELECT id,NAME FROM student

理由：

a.字段多时，大表能达到100多个字段甚至达到200多个字段

b.只取需要的字段，节省资源，减少网络开销

c.select * 进行查询时，很可能不会用到索引，造成全表扫描

2.避免在where字句中使用or来连接条件

反例：

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

正例：

使用union all

SELECT * FROM student WHERE id = 1 
UNION ALL
SELECT * FROM student WHERE salary = 30000

或分开两条sql

SELECT * FROM student WHERE　id　＝　１　
SELECT * FROM student WHERE salary = 30000

理由：

使用or可能会使索引失效，从而全表扫描

对于or没有索引的salary这种情况，假设他走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描 + 索引扫描 + 合并如果它一开始就走全表扫描，直接一遍扫描就能搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的。

mysql优化器内容随缘补

3.使用Varchar代替char

反例：

`name` char(100) DEFAULT NULL COMMENT '名称'

正例：

`name` varchar(100) DEFAULT NULL COMMENT '名称'

理由

a:varchar 变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间

b:char按照声明大小存储，不足补空格

c:其次对于查询来说，在一个相对较小的字段内搜索，效率更高

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 limit 0 10 ，limit 0 20 ，limit 0 50 ，limit 0 100

6.使用explain分析你的sql执行计划

sql很灵活，一个需求可以多种实现，sql提供了explain关键字，它可以分析你的sql执行计划，看它是否最佳。Explain主要看sql是否使用了索引

EXPLAIN
SELECT * FROM student WHERE id = 1

返回结果：

6.2 是否使用了索引及其扫描类型

type:

ALL 全表扫描，没有优化，最慢的方式

index 索引全扫描

range 索引范围扫描，常用语 <, <=,>=,between等操作

ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单挑记录，常出现在关联擦汗寻中

eq_ref 类似于ref,区别在于使用的是唯一索引，使用主键的关联查询

const 当查询是对主键或者唯一键进行精准查询，系统会把匹配行中的其他列作为常数处理

null MySQL 不访问任何表或索引，直接返回结果

System 表只有一条记录（实际中基本不存在该情况）

性能排行：

System > const > eq_ref > ref > range > index > ALL

possible_keys:

显示可能应用在这张表的索引

key:

真正使用的索引方式

7.创建name字段的索引

提高查询速度的最简单最佳的方式

ALTER TABLE student ADD INDEX index_name (NAME)

8.优化like语句

模糊查询，程序员一般来说都喜欢用like，但是like很可能让你得索引失效

9.字符串怪现象

反例：

#未使用索引
EXPLAIN
SELECT * FROM student WHERE NAME = 123

正例：

#使用索引
EXPLAIN 
SELECT * FROM student WHERE NAME = '123'

理由：

为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字得比较，他们类型不匹配，MySQL会做隐式得类型转换，把他们转换为数值类型再做比较

10.索引的优缺点

优点1.

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

优点2.

可以大大加快数据的检索速度，这也是创建索引的最主要的原因

优点3.

可以加速表与表之间的连接，特别是在实现数据的参考完整性方面特别有意义

优点4.

在使用分组和排序字句进行数据检索时，同样可以显著减少查询中分组和排序的时间

优点5.

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点1：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

缺点2：

索引需要占物理空间，出了数据表占据数据空间外，每一个索引还要占一定的物理空间

缺点3：

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度

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

反例：

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

正例：

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

理由：

SQL解析时，如果字段相关的是表达式就进行全表扫描

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

反例：

EXPLAIN
SELECT * FROM student WHERE salary != 3000

理由：

使用!= 和< >很可能让索引失效

13.去重distinct过去字段要少

#索引失效
EXPLAIN
SELECT DISTINCT * FROM student

理由：

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

14.where中使用默认值代替null

环境准备：

#修改表，增加age字段，类型int，非空，默认值0
ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;
#修改表，增加age字段的索引，名称为idx_age
ALTER TABLE student ADD INDEX idx_age (age);

反例：

EXPLAIN 
SELECT * FROM student WHERE age IS NOT NULL

正例：

EXPLAIN
SELECT * FROM student WHERE age>0

理由：

并不是说使用了is null 或则和is not null 就会不走索引了，这个跟mysql版本以及查询成本有关

如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件!=，<>，is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃索引

如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点

高级sql优化

1.批评插入性能提升

多条提交

INSERT INTO student (id,NAME) VALUES(4,'name1');
INSERT INTO student (id,NAME) VALUES(5,'name2');

批量提交

INSERT INTO student (id,NAME) VALUES(4,'name1'),(5,'name2');

理由：

默认新增sql有事务控制，导致每条sql都需要事务开启和事务提交；

而批量处理是一次提交事务，自然速度有所提高

当然，数据量小体现不出来

2.伪删除设计

商品状态（state）：1-上架、2-下架、3-删除

理由：

这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
操作速度快，特别数据量很大情况下

3.提高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;

4.复合索引最左特性

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

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)

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

EXPLAIN
SELECT * FROM student WHERE NAME='name1'

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

EXPLAIN
SELECT * FROM student WHERE salary=3000

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

EXPLAIN
SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000

虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化

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

理由：

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

5.排序字段创建索引

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

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

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

关联的表个数越多，编译的时间和开销也就越大
每次关联内存中都生成一个临时表
应该把连接表拆开成较小的几个执行，可读性更高
如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
阿里规范中，建议多表联查三张表以下

8.inner join 、left join、right join，优先使用inner join

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

inner join 内连接，只保留两张表中完全匹配的结果集
left join会返回左表所有的行，即使在右表中没有匹配的记录
right join会返回右表所有的行，即使在左表中没有匹配的记录
理由：

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

9.尽量使用union all替代union

反例：

SELECT * FROM student
UNION
SELECT * FROM student

正例：

SELECT * FROM student
UNION ALL
SELECT * FROM students

理由：

union和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复
union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION
————————————————
版权声明：本文为CSDN博主「布诺i」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_53601359/article/details/115553449

码农公寓