暂无个人介绍
Polar for Mysql 列存索引常用方法
PolarDB 列存索引解决上亿级数量统计问题
添加列存索引 创建表: CREATE TABLE t10( col1 INT COMMENT 'COLUMNAR=1', col2 DATETIME COMMENT 'COLUMNAR=1', col3 VARCHAR(200) ) ENGINE InnoDB;
查看最后执行SQL -- 查看最后执行SQL 阈值 SHOW STATUS LIKE 'Last_query_cost'; -- 查询cost阈值 SHOW VARIABLES LIKE 'imci_ap_threshold';
查看表是否列存索引 SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES WHERE TABLE_NAME LIKE '%by_wechat_message%';
检查执行SQL 字段缺少列存索引 CALL dbms_imci.check_columnar_index(" SELECT domain, subject_id AS subjectId, SUM(question_num) AS questionNum , SUM(tk_question_num) AS tkQuestionNum FROM ask_question_statistic
WHERE dt >= 20230501 AND dt <= 20230531 GROUP BY domain, subject_id ");
强制执行列存查询 SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ domain, subject_id AS subjectId, SUM(question_num) AS questionNum , SUM(tk_question_num) AS tkQuestionNum FROM ask_question_statistic
WHERE dt >= 20230501 AND dt <= 20230531
设置并列索引 =16 -- 后台控制 innodb_polar_parallel_ddl_threads = 16;
-- 查看是否开启并列查询 SHOW VARIABLES LIKE "innodb_polar_parallel_ddl_threads"
查看列存索引创建状态 -- 表中查看索引的状态信息; SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES; -- 表中查看索引的写入速度; SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEX_STATS; -- 参见查看DDL执行速度和进度 SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;
感谢阿里云,特别棒的平台
Java 和redis 的游戏排名demo
private static final String REDIS_HOST = "xxxx";
private static final int REDIS_PORT = 6379;
private static final String REDIS_PASSWORD = "xxxx"; // 替换为实际的密码
private static final String RANKING_KEY = "game_ranking";
public static void main(String[] args) {
// 连接到Redis服务器
Jedis jedis = new Jedis(REDIS_HOST, REDIS_PORT);
jedis.auth(REDIS_PASSWORD);
// 设置初始玩家分数
jedis.zadd(RANKING_KEY, 100, "Player1");
jedis.zadd(RANKING_KEY, 250, "Player2");
jedis.zadd(RANKING_KEY, 180, "Player3");
jedis.zadd(RANKING_KEY, 400, "Player4");
// 获取排名前三的玩家
Set<Tuple> topPlayers = jedis.zrevrangeWithScores(RANKING_KEY, 0, 2);
int rank = 1;
System.out.println("排名\t玩家\t分数");
for (Tuple tuple : topPlayers) {
System.out.println(rank++ + "\t" + tuple.getElement() + "\t" + tuple.getScore());
}
// 增加玩家分数
jedis.zincrby(RANKING_KEY, 150, "Player1");
// 获取更新后的排名前三的玩家
topPlayers = jedis.zrevrangeWithScores(RANKING_KEY, 0, 2);
rank = 1;
System.out.println("更新后的排名\t玩家\t分数");
for (Tuple tuple : topPlayers) {
System.out.println(rank++ + "\t" + tuple.getElement() + "\t" + tuple.getScore());
}
// 关闭Redis连接
jedis.close();
}