PolarDB产品使用合集之有的sql里面有自定义存储函数 如果想走列存有什么优化建议吗

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: PolarDB是阿里云推出的一种云原生数据库服务,专为云设计,提供兼容MySQL、PostgreSQL的高性能、低成本、弹性可扩展的数据库解决方案,可以有效地管理和优化PolarDB实例,确保数据库服务的稳定、高效运行。以下是使用PolarDB产品的一些建议和最佳实践合集。

问题一:PolarDB开启binlog实现主从复制会对性能有多大影响呢?


PolarDB开启binlog实现主从复制会对性能有多大影响呢?


参考回答:

很大,写性能差一倍以上。


关于本问题的更多回答可点击原文查看:https://developer.aliyun.com/ask/584072


问题二:polarDB和mariaDB实现binlog主从复制怎么弄?


polarDB和mariaDB实现binlog主从复制怎么弄?polarDB为主,mariaDB为从实现主从同步


参考回答:

参考mysql与mariadb同步,一模一样。


关于本问题的更多回答可点击原文查看:https://developer.aliyun.com/ask/584071


问题三:PolarDB这个问题怎么解决? max没有排序语义 sql语义变了


PolarDB这个问题怎么解决? max没有排序语义 sql语义变了,改成这种窗口函数实现 可以走列存 也非常慢 直接把ap节点cpu拉满了,SELECT ep.product_id, p.products_name AS products_name_m, p.products_url_name, ep.enterprise_id, ent.enterprise_name_en AS enterprise_name

, ent.url_name AS enterprise_url_name, ep.specification, DATE_FORMAT(ep.price_date, '%Y-%m-%d') AS price_date

, ep.price_unit, ep.price AS current_price

, (

SELECT tmp.price

FROM (

SELECT ep2.price, ROW_NUMBER() OVER (PARTITION BY ep2.product_id ORDER BY ep2.price_date DESC) AS rank_no

FROM echemi_db_v0_3.t_zy_enterprise_price ep2

LEFT JOIN t_zy_enterprise_price ep

ON ep2.product_id = ep.product_id

AND ep2.enterprise_id = ep.enterprise_id

WHERE IFNULL(ep2.delflag, '') <> '1'

AND ep2.price_date < ep.price_date

AND IFNULL(ep2.price_date, '') != ''

AND IFNULL(ep2.price, '') != ''

AND IFNULL(ep2.delflag, '') != '1'

) tmp

WHERE tmp.rank_no = 1

) AS previous_price, 0 AS subscribe_flag

FROM t_zy_enterprise_price ep

INNER JOIN t_e_products p

ON p.products_id = ep.product_id

AND p.information_flag = '1'

AND IFNULL(p.delflag, '0') != '1'

INNER JOIN t_zy_enterprise ent

ON ent.id = ep.enterprise_id

AND IFNULL(ent.delflag, '0') != '1'

INNER JOIN (

SELECT ep3.product_id, ep3.enterprise_id, MAX(ep3.price_date) AS max_price_date

FROM t_zy_enterprise_price ep3

GROUP BY ep3.product_id, ep3.enterprise_id

) pimax

ON pimax.product_id = ep.product_id

AND pimax.enterprise_id = ep.enterprise_id

AND pimax.max_price_date = ep.price_date

WHERE ep.delflag = '0'

ORDER BY subscribe_flag DESC, ep.price_date DESC, ep.update_date DESC, ep.id

LIMIT 5


参考回答:

那得用window改,SELECT

tmp1.*,

(

SELECT

tmp.price

FROM

(

SELECT

ep2.price,

ROW_NUMBER() OVER (

PARTITION BY ep2.product_id

ORDER BY

ep2.price_date DESC

) AS rank_no

FROM

echemi_db_v0_3.t_zy_enterprise_price ep2

LEFT JOIN t_zy_enterprise_price ep ON ep2.product_id = ep.product_id

AND ep2.enterprise_id = ep.enterprise_id

WHERE

IFNULL(ep2.delflag, '') <> '1'

AND ep2.price_date < ep.price_date

AND IFNULL(ep2.price_date, '') != ''

AND IFNULL(ep2.price, '') != ''

AND IFNULL(ep2.delflag, '') != '1'

) tmp

WHERE

tmp.rank_no = 1

) AS previous_price,

0 AS subscribe_flag

FROM

(

SELECT

ep.product_id,

p.products_name AS products_name_m,

p.products_url_name,

ep.enterprise_id,

ent.enterprise_name_en AS enterprise_name,

ent.url_name AS enterprise_url_name,

ep.specification,

DATE_FORMAT(ep.price_date, '%Y-%m-%d') AS price_date,

ep.price_unit,

ep.price AS current_price,

ep.price_date as pd,

ep.update_date as ud,

ep.id as id

FROM

t_zy_enterprise_price ep

INNER JOIN t_e_products p ON p.products_id = ep.product_id

AND p.information_flag = '1'

AND IFNULL(p.delflag, '0') != '1'

INNER JOIN t_zy_enterprise ent ON ent.id = ep.enterprise_id

AND IFNULL(ent.delflag, '0') != '1'

INNER JOIN (

SELECT

ep3.product_id,

ep3.enterprise_id,

MAX(ep3.price_date) AS max_price_date

FROM

t_zy_enterprise_price ep3

GROUP BY

ep3.product_id,

ep3.enterprise_id

) pimax ON pimax.product_id = ep.product_id

AND pimax.enterprise_id = ep.enterprise_id

AND pimax.max_price_date = ep.price_date

WHERE

ep.delflag = '0'

ORDER BY

subscribe_flag DESC,

ep.price_date DESC,

ep.update_date DESC,

ep.id

LIMIT

5

) tmp1

ORDER BY

subscribe_flag DESC,

tmp1.pd DESC,

tmp1.ud DESC,

tmp1.id

LIMIT

5

试试这个,先做LIMIT 5减少下window的计算量,也可以试试把

SELECT ep2.price, ep2.price_date FROM t_zy_enterprise_price ep2 ORDER BY ep2.price_date DESC, ep2.price desc limit 1;

改为

select max(ep2.price) FROM t_zy_enterprise_price ep2 where ep2.price_date = (SELECT max(price_date) from t_zy_enterprise_price);


关于本问题的更多回答可点击原文查看:https://developer.aliyun.com/ask/584070


问题四:在PolarDB有的sql里面有自定义存储函数 这种想走列存有什么优化建议吗?


在PolarDB有的sql里面有自定义存储函数 还有的子查询里面有limit语句的 这种想走列存有什么优化建议吗?自定义函数例如这种SELECT

a.*,

func_create_url_name(a.title) AS url_name

FROM

(

SELECT

con.contentid,

con.catid,

cat.name AS catname,

cat.abbr AS catabbr,

con.modelid,

con.title,

con.subtitle,

con.url AS info_url,

con.tags AS tags,

con.tags AS original_tags,

CASE

WHEN LOWER(LEFT(con.thumb, 4)) = 'http' THEN con.thumb

ELSE CONCAT('https://xxx/', con.thumb)

END AS thumb,

con.published,

con.pv

FROM

cmstop_content con

INNER JOIN cmstop_category cat ON cat.catid = con.catid

AND cat.first_parentid = '201'

AND FIND_IN_SET('204', cat.parentids)

INNER JOIN (

SELECT

content_tag.contentid

FROM

cmstop_tag tag

INNER JOIN cmstop_content_tag content_tag ON tag.tagid = content_tag.tagid

WHERE

tag.tag = 'zyp-pd2202071028'

) content_tag ON con.contentid = content_tag.contentid

WHERE

con.status = 6

AND con.modelid IN (1, 3, 4, 10)

ORDER BY

con.published DESC,

con.contentid DESC

LIMIT

1

) a

还有这种子查询有limit的。SELECT

ep.product_id,

p.products_name AS products_name_m,

p.products_url_name,

ep.enterprise_id,

ent.enterprise_name_en AS enterprise_name,

ent.url_name AS enterprise_url_name,

ep.specification,

DATE_FORMAT(ep.price_date, '%Y-%m-%d') AS price_date,

ep.price_unit,

ep.price AS current_price,

(

SELECT

ep2.price

FROM

t_zy_enterprise_price ep2

WHERE

ep2.product_id = ep.product_id

AND ep2.enterprise_id = ep.enterprise_id

AND ep2.price_date < ep.price_date

AND IFNULL(ep2.price_date, '') != ''

AND IFNULL(ep2.price, '') != ''

AND IFNULL(ep2.delflag, '') != '1'

ORDER BY

ep2.price_date DESC

LIMIT

1

) AS previous_price,

0 AS subscribe_flag

FROM

t_zy_enterprise_price ep

INNER JOIN t_e_products p ON p.products_id = ep.product_id

AND p.information_flag = '1'

AND IFNULL(p.delflag, '0') != '1'

INNER JOIN t_zy_enterprise ent ON ent.id = ep.enterprise_id

AND IFNULL(ent.delflag, '0') != '1'

INNER JOIN (

SELECT

ep3.product_id,

ep3.enterprise_id,

MAX(ep3.price_date) AS max_price_date

FROM

t_zy_enterprise_price ep3

GROUP BY

ep3.product_id,

ep3.enterprise_id

) pimax ON pimax.product_id = ep.product_id

AND pimax.enterprise_id = ep.enterprise_id

AND pimax.max_price_date = ep.price_date

WHERE

ep.delflag = '0'

ORDER BY

subscribe_flag DESC,

ep.price_date DESC,

ep.update_date DESC,

ep.id

LIMIT

5

CREATE DEFINER=young@% FUNCTION func_create_url_name(

p_str varchar(500) charset utf8mb4 collate utf8mb4_general_ci ) RETURNS varchar(500) CHARSET utf8

SQL SECURITY INVOKER

BEGIN

DECLARE urlName VARCHAR(500);

SET urlName = p_str;

SET urlName = REPLACE(urlName,'β','');

SET urlName = REPLACE(urlName,'α','');

SET urlName = REPLACE(urlName,'ω','');

SET urlName = REPLACE(urlName,'~', '');

SET urlName = REPLACE(urlName,'!', '');

SET urlName = REPLACE(urlName,'!', '');

SET urlName = REPLACE(urlName,'#', '');

SET urlName = REPLACE(urlName,'$', '');

SET urlName = REPLACE(urlName,'¥', '');

SET urlName = REPLACE(urlName,'¥', '');

SET urlName = REPLACE(urlName,'^', '');

SET urlName = REPLACE(urlName,'*', '');

SET urlName = REPLACE(urlName,'%', '');

SET urlName = REPLACE(urlName,';', '');

SET urlName = REPLACE(urlName,';', '');

SET urlName = REPLACE(urlName,'.', '');

SET urlName = REPLACE(urlName,'。', '');

SET urlName = REPLACE(urlName,'·', '');

SET urlName = REPLACE(urlName,'+', '');

SET urlName = REPLACE(urlName,'=', '');

SET urlName = REPLACE(urlName,':', '');

SET urlName = REPLACE(urlName,':', '');

SET urlName = REPLACE(urlName,'>', '');

SET urlName = REPLACE(urlName,'<', '');

SET urlName = REPLACE(urlName,'》', '');

SET urlName = REPLACE(urlName,'《', '');

SET urlName = REPLACE(urlName,'[', '');

SET urlName = REPLACE(urlName,']', '');

SET urlName = REPLACE(urlName,'【', '');

SET urlName = REPLACE(urlName,'】', '');

SET urlName = REPLACE(urlName,'{', '');

SET urlName = REPLACE(urlName,'}', '');

SET urlName = REPLACE(urlName,'(', '');

SET urlName = REPLACE(urlName,')', '');

SET urlName = REPLACE(urlName,'(', '');

SET urlName = REPLACE(urlName,')', '');

SET urlName = REPLACE(urlName,'、', '');

SET urlName = REPLACE(urlName,'|', '');

SET urlName = REPLACE(urlName,'™', '');

SET urlName = REPLACE(urlName,'®', '');

SET urlName = REPLACE(urlName,'©', '');

SET urlName = REPLACE(urlName,'?', '');

SET urlName = REPLACE(urlName,'?', '');

SET urlName = REPLACE(urlName,'\'', ' ');

SET urlName = REPLACE(urlName,'"', ' ');

SET urlName = REPLACE(urlName,'&', ' ');

SET urlName = REPLACE(urlName,'—', ' ');

SET urlName = REPLACE(urlName,'/', ' ');

SET urlName = REPLACE(urlName,'\', ' ');

SET urlName = REPLACE(urlName,',', ' ');

SET urlName = REPLACE(urlName,',', ' ');

SET urlName = REPLACE(urlName,' ', ' ');

SET urlName = REPLACE(urlName,' ', ' ');

SET urlName = REPLACE(urlName,' ', ' ');

SET urlName = REPLACE(urlName,' ', '-');

SET urlName = REPLACE(urlName,'----', '-');

SET urlName = REPLACE(urlName,'---', '-');

SET urlName = REPLACE(urlName,'--', '-');

SET urlName = TRIM(urlName);

SET urlName = TRIM(BOTH '-' FROM urlName);

SET urlName = LOWER(urlName);

return urlName;

END


参考回答:

自定义函数里面有控制语句,还在调研中。SELECT

ep2.price

FROM

t_zy_enterprise_price ep2

ORDER BY

ep2.price_date DESC

LIMIT

1

可以改成

SELECT

MAX(ep2.price)

FROM

t_zy_enterprise_price ep2 最后一个函数可以直接让业务处理a.title。


关于本问题的更多回答可点击原文查看:https://developer.aliyun.com/ask/584069


问题五:PolarDB这种sql为啥也不走列存呢 ?我改了下发现还不走 。


PolarDB这种sql为啥也不走列存呢 ?我改了下发现还不走。SELECT DISTINCT sk.search_type, sk.search_keywords FROM t_e_search_keywords sk WHERE sk.search_type = '1' AND sk.ip = ' 139.5.31.25' AND LENGTH(sk.search_keywords) = CHAR_LENGTH(sk.search_keywords) ORDER BY sk.search_time DESC LIMIT 10;SELECT DISTINCT sk.search_type, sk.search_keywords,sk.search_time FROM t_e_search_keywords sk WHERE sk.search_type = '1' AND sk.ip = ' 139.5.31.25' AND LENGTH(sk.search_keywords) = CHAR_LENGTH(sk.search_keywords) ORDER BY


参考回答:

第一条是不会走的,得升级; 第二条应该是可以的,针对order by limit 这些sql在一些情况下代价估算不太准确, 导致cost比较小就路由到行存了, 刚才配置一些选项, 断开连接试试,不行再show variables like '%imci%opt%'; 试试。


关于本问题的更多回答可点击原文查看:https://developer.aliyun.com/ask/584068


相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
26天前
|
SQL 数据库
SQL FORMAT() 函数
SQL FORMAT() 函数
23 2
|
19天前
|
SQL 自然语言处理 关系型数据库
PolarDB自然语言到SQL语言转义
PolarDB自然语言到SQL语言转义
|
21天前
|
存储 关系型数据库 分布式数据库
PolarDB,阿里云的云原生分布式数据库,以其存储计算分离架构为核心,解决传统数据库的扩展性问题
【7月更文挑战第3天】PolarDB,阿里云的云原生分布式数据库,以其存储计算分离架构为核心,解决传统数据库的扩展性问题。此架构让存储层专注数据可靠性,计算层专注处理SQL,提升性能并降低运维复杂度。通过RDMA加速通信,多副本确保高可用性。资源可独立扩展,便于成本控制。动态添加计算节点以应对流量高峰,展示了其灵活性。PolarDB的开源促进了数据库技术的持续创新和发展。
236 2
|
5天前
|
SQL 分布式计算 DataWorks
MaxCompute操作报错合集之在创建SQL函数时,遇到报错,该如何解决
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。
|
27天前
|
SQL
SQL ROUND() 函数
SQL ROUND() 函数
22 1
|
25天前
|
存储 关系型数据库 分布式数据库
PolarDB操作报错合集之修改了binlog的存储时间为1小时,但在重启后发现仍有90GB的binlog文件未被删除,是什么原因
在使用阿里云的PolarDB(包括PolarDB-X)时,用户可能会遇到各种操作报错。下面汇总了一些常见的报错情况及其可能的原因和解决办法:1.安装PolarDB-X报错、2.PolarDB安装后无法连接、3.PolarDB-X 使用rpm安装启动卡顿、4.PolarDB执行UPDATE/INSERT报错、5.DDL操作提示“Lock conflict”、6.数据集成时联通PolarDB报错、7.编译DN报错(RockyLinux)、8.CheckStorage报错(源数据库实例被删除)、9.嵌套事务错误(TDDL-4604)。
|
26天前
|
SQL 数据库
SQL NOW() 函数
SQL NOW() 函数
12 0
|
27天前
|
SQL 关系型数据库 MySQL
SQL LEN() 函数
SQL LEN() 函数
13 0
|
27天前
|
SQL 数据库
SQL MID() 函数
SQL MID() 函数
15 0
|
27天前
|
存储 关系型数据库 分布式数据库
PolarDB产品使用问题之如何变更存储配置
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。

热门文章

最新文章