先简单介绍下项目背景这是个在线考试练习平台数据库使用MySQL表结构如图所示:



Question是存储题目表数据量在3万左右AnswerResult表是存储用户作答结果表分表的后单表记录大概在300万-400万

需求:根据用户作答结果出练习卷题目优先级为:未做过题目>只做错题目>做错又做对题目>只做对题目

在“做错又做对题目”中会按次数和正确次数比例进行权重计算比如:A、做错10次做对100次；B、做错10次做对20次这时B被选中出给用户练习概率就大

备注:AnswerResult表中不存在QuestionId记录则代表该题没有做过

的前使用思路方法:

　　SELECT Question.题目标识,IFNULL((0-正确次数)/(正确次数+次数),1) AS 权重 FROM Question

　　LEFT JOIN AnswerResult _disibledevent=>

　　WHERE 用户标识={UserId}

介绍说明:IFNULL((0-正确次数)/(正确次数+次数),1)这个式分2部分

公式:(0-正确次数)/(正确次数+次数)得到题目权重这个区间为[0,-1]0表示只做错题目-1表示只做对题目IFNULL(value,1)则将未做过题目权重设置为1根据这个权重进行排序列出题目

由于AnswerResult表是多达300、400百万表所以通过LEFT JOIN进行左连接时迪卡尔乘积过大又加上AnswerResult是频繁读写表很容易导致这条SQL变成慢查询

性能问题被提上日程后这条SQL语句就变成优化点

1、IFNULL这个计算可以调整成冗余字段

2、LEFT JOIN迪卡尔乘积太大可以调整为冗余或者使用INNER JOIN以提高查询速度

3、根据需求其实可以调整出题策略区别情况执行区别SQL而不需要在同条SQL中实现

解决方案针对以上 3个点进行调整虽然Question表有3万条数据但是出题场景其实是针对知识点出题单个知识点题目最多也只有1000题左右所以获取未做过题目时完全可以使用NOT IN走索引来完成SQL语句如:

　　A:SELECT 题目标识 FROM Question WHERE 知识点={KnowledgePoCode} AND 题目标识 NOT IN (

　　　　SELECT 题目标识 FROM AnswerResult INNER JOIN Question AND Question.知识点={KnowledgePoCode}

　　　　WHERE AnswerResult.用户标识 = {UserId}

　　)

针对只做错题目出题练习就简单了(正确次数 = 0代表只做错)SQL如:

　　B:SELECT 题目标识 FROM AnswerResult INNER JOIN Question AND Question.知识点={KnowledgePoCode}

　　WHERE AnswerResult.用户标识 = {UserId} AND 正确次数 = 0 ORDER BY 次数 DESC

若要对做错、做对或者只做对题目进行出题SQL就是这样(已经对权重进行冗余=IFNULL((0-正确次数)/(正确次数+次数),1)):

　　C:SELECT 题目标识 FROM AnswerResult INNER JOIN Question AND Question.知识点={KnowledgePoCode}

　　WHERE AnswerResult.用户标识 = {UserId} AND 正确次数 > 0 ORDER BY 权重 DESC



不足:SQL语句A查询速度依然是较慢虽然有缩小NOT IN结果集但这里还是有优化点园子里朋友们能不能给点建议？

有人说JOIN是SQL性能杀手我觉得主要还是如何去使用JOINMySQL索引优化相当重要如果JOIN成为性能瓶颈可以EXPLAIN看看是不是索引没有建好并且尽量让迪卡尔乘积尽量小使用冗余数据避免JOIN当可能变化冗余数据被分表的后更新这些冗余数据就是件非常头痛事了海量数据高并发确实是件挺头痛事

望园子里有这方面经验朋友不吝赐教谢谢