问题一：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
