SQL优化策略

IT那活儿发布于2023-01-11 13:20 / 575人阅读

SQL优化策略

点击上方“IT那活儿”，关注后了解更多内容，不管IT什么活儿，干就完了！！！

优化原则

1. 严禁使用SELECT *方式查询语句，必须明确查询字段，INSERT语句必须明确要插入的字段。

2. 严禁单条SQL关联表超过3张，关联字段必须有索引且数据类型一致。

3. 严禁单条SQL子查询超过2层。

4. 严禁在SQL中进行计算或嵌套判断逻辑。

5. 严禁查询条件中字段无索引。

6. 严禁在where条件中字段使用函数或者表达式(例如where col/3>=100)。

7. 严禁负向查询条件(!=、<>、not ...)、单表行数大于5万的禁止左模糊、全模糊查询(例如：colA like ‘%服务’)。

8. 严禁传入变量类型与查询条件中字段类型不匹配。

9. 严禁表无主键或使用复合索引作为主键，严禁使用无序数据作为主键内容。

10. 严禁使用外键、视图、触发器、存储过程、自定义函数和分区表。

innodb索引组织表

索引组织表的典型特征：

表记录通过聚集索引组织；
表有且仅有一条聚集索引；
所有列数据存储在叶子几点上。

Innodb存储引擎中，表都是根据主键顺序组织存放，以这种存储方式的表称为索引组织表。

InnoDB的数据文件本身就是索引文件。

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。

如果在创建表时没有显示地定义主键，则InnoDB存储引擎会按如下方式选择或创建主键：

首先判断表是否有非空的唯一索引(Unique not null),如果有，则该列即为主键。

如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。

如下：

查询的优化

1. 隐式转换

禁止隐式转换，保持变量类型与字段类型一致。

SQL1：

select emp_no,from_date from dept_emp1
where dept_no=404838;

SQL2：

select emp_no,from_date from dept_emp1
where dept_no=404838;

2. WHERE子查询

1）使用连接方式改写子查询。

示例1:

SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN ( SELECT column1 FROM t2);

改写:

SELECT DISTINCT t1.column1 FROM t1, t2 WHERE t1.column1 = t2.column1;

示例2:

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);

改写:

SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

还可以改写成如下LEFT JOIN：

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;

2）对于只返回一行的无关联子查询用‘=’代替‘in’。

示例:

SELECT * FROM t1 WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);

改写:

SELECT * FROM t1 WHERE t1.col_name= (SELECT a FROM t2 WHERE b = some_const);

对于数据库来说，在绝大部分情况下，连接会比子查询更快。使用连接的方式， MySQL优化器一般可以生成更佳的执行计划，更高效地处理查询。而子查询往往需要运行重复的查询，子查询生成的临时表上也没有索引，因此效率会更低。

3. OR子句

避免使用子查询、or，将子查询转化为表连接方式，or转化为in。

优化or子句：

1）or子句全部相同，则改为in。

示例：

select * from t1 where a=1 or a=3;

改为：

select * from t1 where a in(1,3);

2）or子句具有公共子序列前缀的，请在or公共部分建立索引。

示例：（如下需要在a列上创建索引）

select * from t1 where (a=1 and b=2) or (a=3 and c=4);

3）若无公共，则建议改为union all，并为每部分建立索引。

示例：

select * from t1 where a=1 or b=2;

可以使用 Index merge。

或者转换（效率更高）：

select * from t1 where a=1
union all
select * from t1 where b=2;

4. GROUP/ORDER BY优化

order by子句，尽量使用Index方式排序，在索引列上遵循索引的最佳左前缀原则。

如下：

Key (a,b,c)

Order by 能使用索引情况：

--order by a；

--order by a, b；

--order by a, b,c；

----order by a desc ,b desc ,c desc。

如果where 使用索引的最左前缀定义为常量，则order by能使用索引：

--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。

group by与order by的索引优化基本一样，group by实质是先排序后分组，也就是分组之前必排序，遵照索引的最佳左前缀原则可以大大提高group by的效率。

5. LIMIT偏移量过大

禁止分页查询偏移量过大，如limit 10000,10。

1）尽量使用索引排序完成文件排序。

2）限制用户翻页。

3）利用自增主键，避免offset使用。

idx_test1 (gender,hire_date);

优化案例

案例一：模糊查询

1）慢sql：

2）慢sql执行计划：

问题点：

1）dsps_staff_id like concat(‘%’,’HE12065’,’%’) 全模糊匹配导致索引失效。

2）总量统计没必要实时统计，建议降低统计频率。

全模糊查询改为 dsps_staff_id like ’HE12065%’，优化后效率提升百倍。

案例二：索引覆盖

包含所有满足查询需要的数据的索引成为覆盖索引，也就是平时所说的不需要回表操作,对于一个索引覆盖查询，显示为using index。

CREATE TABLE test (
id int(8) unsigned NOT NULL ,
film_id smallint(5) unsigned NOT NULL,
store_id tinyint(3) unsigned NOT NULL,
PRIMARY KEY (id),
KEY idx_film_id (film_id)
) ENGINE=InnoDB ;

例1：

这里最主要看Extra，它的值为Using index，它在这句查询中含义就是直接访问film_id这个索引就足已获取到所需要的数据，不需要再通过索引回表查询了。

例2：

可以看到，我们这次查询了id和film_id两个字段，但条件只用了film_id这个二级索引，Extra的值却也是为Using index。

原因是二级索引的叶子结点中会有主键索引(id)值，因此使用了覆盖索引。

可以通过索引来实现索引覆盖查询，但前提条件是，查询返回的字段数足够少，select * 类不可以。

案例三：驱动表、别名

1）慢sql：

2）慢sql执行计划：

问题点：

1）原sql基表是t5，大量使用临时表、排序，效率低下。

2）order by crttime 使用别名。

第一步：删除channel_id索引。

第二步：order by crt_time 字段名代替别名。

案例四：or

1）慢sql：

2）慢sql执行计划：

问题点：

1）全表扫描。

2）or条件，索引失效。

第一步：改写union 上部分or。

第二步：改写union下部分子查询。

第三步：数据合并由应用层实现。

经过分步执行后总执行时间为0.00s,效率提升百倍。

案例五：拆分大sql

1）慢sql：

2）慢sql执行计划：

问题点：

1）关联表过多，共关联9张表。

2）or条件，索引失效。

3）子查询过多。

第一步：改写union 上部分or。

第二步：改写union下部分子查询。

第三步：改写union下部分子查询，利用第二部分数据。

第四步：数据合并由应用层实现。

通过redis等NoSQL缓存字典类信息，减少多表关联。

案例六

1）慢sql：

2）执行计划：

问题点：

1）rec表全表扫描。

2）sql逻辑问题。

执行计划详细信息中发现问题：

第一步：改写sql。

第二步：改写后sql执行计划。

本文作者：李博文

本文来源：IT那活儿（上海新炬王翦团队）

云服务器 GPU云服务器优化SQL sql优化 SQL命令优化 SQL优化方法

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129541.html

Hibernate最全面试题

摘要：中怎样实现类之间的关系如一对多多对多的关系中怎样实现类之间的关系如一对多多对多的关系它们通过配置文件中的来实现类之间的关联关系的。 Hibernate常见面试题 Hibernate工作原理及为什么要用？ Hibernate工作原理及为什么要用？读取并解析配置文件读取并解析映射信息，创建SessionFactory 打开Sesssion 创建事务Transation 持久化操作提...

张利勇 2019-08-15 14:39 评论0 收藏0
第三代DRDS分布式SQL引擎全新发布

摘要：阿里云分布式关系型数据库服务，于月号发布了版本，这是一个年度大更新。无需额外付费或者开通，不依赖第三方组件，即可执行分布式事务。确保分布式执行代价的最小化。柔性事务提供的最终一致方式执行的分布式事务称为柔性事务。摘要： DRDS （阿里云分布式关系型数据库服务，https://www.aliyun.com/produc...）于 4 月 30 号发布了 5.3 版本，年度更新，具备众...

xinhaip 2019-06-25 18:39 评论0 收藏0
SparkSQL 在有赞的实践

摘要：在有赞的技术演进。业务数据量正在不断增大，这些任务会影响业务对外服务的承诺。监控需要收集上执行的的审计信息，包括提交者执行的具体，开始结束时间，执行完成状态。还有一点是详细介绍了的原理，实践中设置了的比默认的减少了以上的时间。前言有赞数据平台从2017年上半年开始，逐步使用 SparkSQL 替代 Hive 执行离线任务，目前 SparkSQL 每天的运行作业数量5000个，占离线...

hzx 2019-06-26 18:45 评论0 收藏0
SparkSQL 在有赞的实践

摘要：在有赞的技术演进。业务数据量正在不断增大，这些任务会影响业务对外服务的承诺。监控需要收集上执行的的审计信息，包括提交者执行的具体，开始结束时间，执行完成状态。还有一点是详细介绍了的原理，实践中设置了的比默认的减少了以上的时间。前言有赞数据平台从2017年上半年开始，逐步使用 SparkSQL 替代 Hive 执行离线任务，目前 SparkSQL 每天的运行作业数量5000个，占离线...

Xufc 2019-06-21 16:51 评论0 收藏0