前言:多表关联索引不是多表索引,注意区分。多表索引正在研究,多表索引的条件限制就是主键的类型,如果是自增主键那么多表索引会出现后者覆盖前者的问题。如果是UUID等唯一的主键则可忽略此问题。
业务需求:将课程的相关信息添加索引到solr。
涉及到的数据表和课程先关信息:
1.课程基本信息表-课程基本信息;
2.课程章节表-课程一共的章节;
3.讲师表-课程的讲师信息;
4.课程价格表-课程的价格信息;
data-config.xml文件配置:
<?xml version="1.0" encoding="UTF-8" ?>
<dataConfig>
<dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://172.168.3.39:3306/reading5_test" user="reading_test" password="reading_test" batchSize="1000" />
<document>
<entity name="tb_pay_course" pk="id"
query="SELECT tpc.id,tpc.course_name,tpc.transverse_pic,tpc.vertical_pic,tpc.modify_time,tpc.price_id,tpc.lecturer_id
FROM tb_pay_course tpc
JOIN tb_course_chapter tcc ON tcc.course_id = tpc.id AND tcc.state = 1 AND tcc.node_num > 0
JOIN tb_nodes tn ON tn.course_chapter_id = tcc.id AND tn.state = 1 AND tn.start_time < NOW() AND tn.duration > 0
WHERE tpc.validity_time > NOW() AND tpc.state = 2 GROUP BY tpc.id"
deltaImportQuery="SELECT tpc.id,tpc.course_name,tpc.transverse_pic,tpc.vertical_pic,tpc.modify_time,tpc.price_id,tpc.lecturer_id
FROM tb_pay_course tpc
JOIN tb_course_chapter tcc ON tcc.course_id = tpc.id AND tcc.state = 1 AND tcc.node_num > 0
JOIN tb_nodes tn ON tn.course_chapter_id = tcc.id AND tn.state = 1 AND tn.start_time < NOW() AND tn.duration > 0
WHERE tpc.id = '${dataimporter.delta.id}' AND tpc.validity_time > NOW() AND tpc.state = 2 GROUP BY tpc.id"
deltaQuery="SELECT id FROM tb_pay_course where modify_time > '${dataimporter.last_index_time}'">
<field column="id" name="id"/>
<field column="course_name" name="sCourseName"/>
<field column="transverse_pic" name="sTransversePic"/>
<field column="vertical_pic" name="sVerticalPic"/>
<field column="modify_time" name="sModifyTime"/>
<entity name="tb_lecturer" query="SELECT tl.id,tl.lecturer_name FROM tb_lecturer tl WHERE id = '${tb_pay_course.lecturer_id}'">
<field column="id" name="sLecturerId"/>
<field column="lecturer_name" name="sLecturerName"/>
</entity>
<entity name="tb_price_strategy" query="SELECT tps.id,tps.price FROM tb_price_strategy tps WHERE id = '${tb_pay_course.price_id}' AND tps.deleted = 0">
<field column="id" name="sCoursePriceId"/>
<field column="price" name="sCoursePrice"/>
</entity>
<entity name="tb_course_chapter" query="SELECT SUM(duration) AS duration,COUNT(id) AS chapterNum,course_id
FROM tb_course_chapter WHERE course_id = '${tb_pay_course.id}' AND duration > 0 AND node_num > 0 AND state = 1 GROUP BY course_id">
<field column="duration" name="sCourseDuration"/>
<field column="chapterNum" name="sCourseChapterNum"/>
</entity>
</entity>
</document>
</dataConfig>
解释:一个大的<entity></entity>标签主表查询语句与查询字段,连接几个表就在内部创建几个<entity></entity>标签,注意查询语句里的where后面的连接条件。大于小于等于号一律转义。
结果:
结语:本来想将书籍的信息和课程的信息索引到一个core后来由于主键的原因没解决所以放到了两个core里,总觉得有点浪费资源。后期看看能不能改进,也欢迎大神提出意见或者建议。一旦采用,将有机会得到500w的奖励。