百万级SQL查询优化-CSDN博客

2019独角兽企业重金招聘Python工程师标准>>>

地图信息,需要保存在服务器，redis来做这个工作的话不太合适，第一数据量太大，第二大部分是冷数据，，，虽然排序用redis来做方便的多，但是还是倾向于mysql。

功能：

1.玩家可以任意创作地图，记录相关信息比如，id ，创建者id，时间戳，鸡蛋数量，鲜花数量。等

2.玩家可以查看已存在的地图，类似于留言板，供玩家浏览（大量的查询，动态加载查询）

3.每天挑选出地图好评数最多的50个，加入自定义地图。（类似于LOL的每周免费英雄）所有玩家的地图=官方地图+自定义地图。

-----------------------------------------redis来做的话设计结构如下：

地图编辑器的  redis 结构


每张地图信息 string   记录着地图创建者 id 鲜花 鸡蛋  时间戳等信息
key=map-info-XXXX  value={"like:12,hate:12"}
XXX表示唯一id

每天上传地图信息  list   只是地图id列表
key=maps-info-day-2018-1-1  value=list 只记录list id 索引

每天的排行榜信息 sorted-set
key=maps-score-day-2018-1-1 value=sorted-set 只记录分数 和地图id  在这里可动态更新  set 集合的 score 和member（value）--排序，分数由鲜花鸡蛋等 计算出来

虽然能很好的额完成排序和单位玩家索引功能，但是功能2就不好处理了，首先是要遍历整个相关key，或者在建立一个key-list 用来保存所有玩家的所有地图id。这样一个问题是key太多，和多重key，虽然他们大部分都是索引id但是太复杂。删除一个其他key都要更新，集群的话还不能mset 。

缺陷：整体查询不好做。

优点：排序方便（性能还待和mysql对比）

---------------------------------------MYSQL 来做的话主要是SQL查询优化。

涉及到2个大问题：查询and 排序；先建立索引 create index idx on map_info(id);

查询：

先创建 500W 条记录，

打开统计，navicat for mysql .......set profiling=1;

显示计时统计：show profiles;

1.暴力全部查询：select* from map_info 耗时3.282s

2.limit 暴力查询分页：SELECT * FROM `map_info` LIMIT 3000000, 20 耗时0.77s

3.limit where id 优化分页查询：SELECT * FROM `map_info` where id >3000000 limit 20 耗时0.000s

4. 倒叙查看优化limit分页查询：SELECT * FROM `map_info` where id <2000000000000 ORDER BY id desc limit 10 耗时0.000s

排序：

排序就用order by 常规方法来排序，因此不是大问题就算排序10s 也无所谓。

1：按照鸡蛋数排序：SELECT * FROM `map_info` where id <2000000000000 ORDER BY hate desc limit 50 耗时1.7s

2：1E条数据，完全排序耗时：84.170s

----------------------------------------redis和mysql协作

思考：mysql 查询优化后确实很快，但是排序就很慢了，。因此我们要缓存排序结果。实际需求是每天凌晨更新一次排序值，这个恰恰能帮助我们进行复杂的sql语句排序工作。

方案：数据查询插入什么的直接操作mysql，每天凌晨直接排序，然后把结果缓存到redis。所有当日自定义地图（排序后的数据）请求都访问redis中的缓存数据即可。对于优化后的查询如果还不能满足性能需求的话，可能还要进一步利用redis来缓存（看实际性能指标来衡量，比如大量的链接，虽然单台是0.00s 毫秒级别但是机器多了，性能也就下去了）。

排序保护（可以考虑和策划商量需求更改，比如凌晨半小时内禁止玩家上传地图什么的，来让排序工作不至于产生意外的事情）。

转载于:https://my.oschina.net/kkkkkkkkkkkkk/blog/1603572