首页 > 代码库 > mysql模糊查询的优化方法--亲自实践
mysql模糊查询的优化方法--亲自实践
数据有4W多条,不多,但是模糊查询
起来特别慢。
1,尝试过用 select * from (select * from a union all select * from b。。。很多表union) as tempTable where name like "%a%" and person_id like "%1%",发现太慢了。。
2,尝试使用具体的字段,而不是*,即
select id,name,person_id from (select id,name,person_id from a
union all select id,name,person_id from b。。。很多表union) as tempTable where name like "%a%" and person_id like "%1%",虽然快了点儿,但是不是特别快。。
3,尝试把where放在每一个select语句中,不需要汇聚成一个表了,即少了别名:
select id,name,person_id from a where name like "%a%" and person_id like "%1%"
union all select id,name,person_id from b where name like "%a%" and person_id like "%1%", 卧槽,出乎意料地快!解决了问题。
4,mybatis配置文件如下:
模糊查询的总数:
<select id="getFaceDataCount" resultType="int"> select sum(c) from (<foreach collection="companyCodeList" item="item" index="index" separator="union">select count(1) as c from ${item} <where> <if test="faceData.personId != null and faceData.personId != ‘‘"> and person_id like ‘%${faceData.personId}%‘ </if> <if test="faceData.name != null and faceData.name != ‘‘"> and name like ‘%${faceData.name}%‘ </if> </where> </foreach>) as faceCount </select>
模糊查询的详细:
<select id="selectAllFaceData" resultMap="BaseResultMap"> <foreach collection="companyCodeList" item="item" index="index" separator="union">(select <include refid="Base_Column_List" /> from ${item} <where> <if test="faceData.personId != null and faceData.personId != ‘‘"> person_id like ‘%${faceData.personId}%‘ </if> <if test="faceData.name != null and faceData.name != ‘‘"> and name like ‘%${faceData.name}%‘ </if> </where> )</foreach> limit #{start},#{limit} </select>
mysql模糊查询的优化方法--亲自实践
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。