sql优化书
Ⅰ ODPS sql 优化总结
优化总结：SQL使用技巧与性能提升
1、利用null处理：在判断条件中处理null时，使用nvl或coalesce函数进行默认转换，避免null影响判断结果。
2、选择性列查询：在数据开发或线上任务中，应提前剪裁列，即使需要所有列，也明确列出，减少不必要的数据读取，预防后期表结构变动导致的错误。
3、多表插入优化：读取同一表但在不同粒度下插入多表时，采用from () tab insert overwrite A insert overwrite B方法，减少资源浪费。注意遵循团队开发规范，合理复用公共数据，如通过临时表存储逻辑。
4、分区限定：ODPS表为分区表，应习惯性限定分区ds，避免因分区限定问题导致的任务资源浪费。
5、使用limit：在临时查询或数据探查时，加上limit快速获取所需数据，减少资源消耗。
6、UDF函数下沉：将UDF函数下沉到子查询中，提高效率。
7、行转列与列转行：利用collect_set、lateral view函数实现，参考大佬经验。
8、窗口函数应用：使用row_number()或max(struct())等方法实现数据排序或计算。
9、关联类型：掌握左关联、内关联、右关联等，适应不同场景下的多表关联，确保关联字段类型一致。
10、笛卡尔积处理：针对需求翻倍一行数据的场景，创建维表并通过笛卡尔积操作，或使用:LATERAL VIEW POSEXPLODE方法。
11、提高map数：通过调整split size hint优化商品表效率，使用/*+SPLIT_SIZE(8)*/调整，单位为MB。
数据倾斜优化策略：
1）大表关联小表：使用mapjoin hint，调整中小表内存大小，通过set odps.sql.mapjoin.memory.max调整，单位M。
2）大表关联大表：拆分热点数据，使用普通join或skewjoin hint，针对分区与桶优化关联。
3）count distinct问题：通过group by先去重再count解决。
4）ODPS新特性：关注MaxCompute(ODPS2.0)特性，性能优化显着。
小表关联大表优化：使用dynamic_filter，调整/*+dynamic_filter(A,B)*/。
参数设置优化：
1）Map设置：调整odps.sql.mapper.cpu、memory、merge.limit.size、split.size，根据任务特点合理调整。
2）Join设置：调整odps.sql.joiner.instances、cpu、memory，针对Join任务特性进行调整。
3）Rece设置：调整odps.sql.recer.instances、cpu、memory，优化任务性能。
4）小文件合并参数：设置odps.merge.cross.paths、filesize.threshold、maxmerged.filesize.threshold、max.filenumber.per.instance、max.filenumber.per.job，控制文件合并。
5）UDF相关参数：调整odps.sql.udf.jvm.memory、timeout、python.memory、optimize.reuse、strict.mode，优化UDF性能。
Mapjoin设置：使用odps.sql.mapjoin.memory.max调整小表最大内存。
动态分区设置：使用set odps.sql.reshuffle.dynamicpt控制动态分区优化。
数据倾斜设置：使用set odps.sql.groupby.skewindata、odps.sql.skewjoin优化Group By与Join操作。
SQL优化案例：
关联与数据倾斜优化：发现数据倾斜，尝试使用skewjoin、热点数据分离等方法，最终通过隐式转换问题解决，确保关联操作的正确性。
分桶解决大表与大表关联：使用hash clustering分散数据，减少资源消耗，提高查询效率。
BitMap在多维汇总中的应用：利用BitMap实现去重汇总，解决数据倾斜问题，优化多维汇总模型性能。
总结：SQL优化是理解业务逻辑、合理利用工具、控制资源消耗的过程。在满足业务需求的同时，追求效率与成本的优化。
Ⅱ SQL数据库查询的优化
这个问题太大了，不懂就加内存，加CPU，用RAID存储。
Ⅲ Sql优化-多like模糊查询及根据时间排序
2020-04-21
记录一次sql优化记录：
环境：用的mysql版本 select Version();
优化过程：
用的是两张表联查，四个条件like查询 ，根据时间排序降序
其中A，B表没有大字段，A表20万多数据，B表50万多条数据。语句如下：
EXPLAIN
SELECT A.bondId,A.sname,A.cname,A.secuCode,A. ISSUER,A.guarantor,B.underwriter AS infoSource
FROM A
LEFT JOIN B ON B.bondId = A.bondId
WHERE B.agentType = 1
AND B.underwriter = '有限公司'
AND A.startDate <= '2020-04-21 18:02:10'
AND A.endDate >= '2020-04-21 18:02:10'
AND (
A.cname LIKE '%%' OR A.sname LIKE '%%' OR A.secuCode LIKE '%%'
OR A. ISSUER LIKE '%%'OR A.guarantor LIKE '%%')
AND A.isValid = 1
ORDER BY A.startDate DESC
LIMIT 0, 20
这是2个表都没有加索引的情况，从explain来看结果非常糟糕，都是全表扫描，并且产生临时表同时有文件排序，效率肯定非常低。
首先尝试在B表上建立一个联合索引
可以考虑从关联字段及where条件字段考虑（bondId, underwriter, agentType）
建一个联合索引，试试。
ALTER TABLE B ADD INDEX bua_index(bondId, underwriter, agentType)
再explain看：
可以看到B表用到了我们刚刚建的联合索引，并且额外信息是Using index ，type是ref级别的，效果比较理想，再来看A表。
Where条件中有多个like，这种情况下一般索引都是不可用的，所以必须用覆盖索引解决，
由于又根据startDate排序，所以尝试根据如下字段建立联合索引，同时查询的字段就是索引中的字段（startDate, endDate,cname, sname, secuCode, issuer, guarantor）
ALTER TABLE A ADD INDEX index_scssig(startDate, endDate,cname, sname, secuCode, issuer, guarantor)
再次explain看看效果：
这样乍看上去A表也用到了刚刚建的联合索引，并且type是range级别虽然比ref差点，按理说应该也还可以，但是我执行sql语句，效率还是非常差，查询耗时达到8s，并且偶尔还不止这个时间
究其原因，虽然使用了索引，但是extra里面是Using index condition&Using where
回表操作了，我在想如果将extra优化成Using index效率肯定没问题
故再进一步优化，还是从索引入手
在联合索引上添加2个字段isValid, bondId 再试试
ALTER TABLE A ADD INDEX index_scssig(isvalid,startDate, endDate,cname, sname, secuCode, issuer, guarantor,bondId)
再次explain:
这个结果就是我想要的，然后执行sql看看效率：
已经提升了很多了，但是我试了别的查询条件偶尔时间会到3，4s,怀疑和自己的机器有关
在这这种多个like的or查询mysql本身并不擅长，无奈坑爹的需要需要这样，可能效率并不是非常的高，优化成这样可以接受了。
最近对以前项目的慢查询进行sql调优，感觉性能的下降往往还是sql语句及索引的建立的问题，explain是很有帮助，正确优化还是能极大提升效率的。