精华内容
下载资源
问答
  • 实现三动态sql语句、模糊、分页查询2.1 xml实现2.2 注解实现2.3 注意 Mybatis实现sql语句的方式主要有两种: 一种是通过xml配置文件实现 另一种是直接在mapper层下接口文件中使用注解实现 1. 实现三表关联查询 ...


    Mybatis实现sql语句的方式主要有两种:
    一种是通过xml配置文件实现
    另一种是直接在mapper层下接口文件中使用注解实现

    1. 实现三表关联查询

    1.1 配置文件实现

    实体类Emps.java

    public class Emps{
    	private int empno;
    	private String ename;
    	private String job;
    	private Dept dept;
    	private Position position;
    
    	public Emps(){}
    	
    	public int getEmpno(){return empno;}
    	public void setEmpno(){this.empno=empno;}
    	public String getEname(){return ename;}
    	public void setEname(){this.ename=ename;}
    	public String getJob(){return job;}
    	public void setJob(){this.job=job;}
    	public Dept getDept(){return dept;}
    	public void setDept(){this.dept=dept;}
    	public Position getPosition(){return position;}
    	public void setPosition(){this.position=position;}
    	
    }
    

    mapper接口

    public interface EmpMapper{
    	List<Emps> queryEmp();
    }
    

    mapper配置文件

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="cn.khue.mapper.EmpMapper">
    	<resultMap id="rm" type="emp">
    		<id column="empno" property="empno"></id>
    		<result column="ename" property="ename"></result>
    		<result column="job" property="job"></result>
    		<association property="dept" javaType="dept">
    			<id column="deptno" property="deptno"></id>
    			<result column="deptname" property="deptname"></result>
    		</association>
    		<association property="position" javaType="position">
    			<id column="posid" property="posid"></id>
    			<result column="pname" property="pname"></result>
    		</association>
    	</resultMap>
    	<select id="queryEmp" resultMap="rm">
    		select e.empno, e.ename, e.job, d.deptname, p.pname
    		from emp e join dept d on e.deptno=d.deptno join position p on e.posid=p.posid
    	</select>
    </mapper>
    

    1.2 注解实现

    实体类Emps.java

    public class Emps{
    	private int empno;
    	private String ename;
    	private String job;
    	private String dname;
    	private String pname;
    
    	public Emps(){}
    	
    	public int getEmpno(){return empno;}
    	public void setEmpno(){this.empno=empno;}
    	public String getEname(){return ename;}
    	public void setEname(){this.ename=ename;}
    	public String getJob(){return job;}
    	public void setJob(){this.job=job;}
    	public String getDname(){return dname;}
    	public void setDname(){this.dname=dname;}
    	public String getPname(){return panme;}
    	public void setPname(){this.pname=pname;}
    	
    }
    

    mapper接口

    public interface EmpMapper{
    	@Select("select e.empno, e.ename, e.job, d.deptname, p.pname"+
    	"from emp e, dept d, position p"+
    	"where e.deptno=d.deptno and e.posid=p.posid")
    	List<Emps> queryEmp();
    }
    

    2. 实现三表动态sql语句、模糊、分页查询

    2.1 xml实现

    public interface EmpMapper{
    	List<Emps> queryEmp(int empno, String ename, String job, String dname, String pname, int startPage, int pageSize);
    }
    
    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="cn.khue.mapper.EmpMapper">
    	<resultMap id="rm" type="emp">
    		<id column="empno" property="empno"></id>
    		<result column="ename" property="ename"></result>
    		<result column="job" property="job"></result>
    		<association property="dept" javaType="dept">
    			<id column="deptno" property="deptno"></id>
    			<result column="deptname" property="deptname"></result>
    		</association>
    		<association property="position" javaType="position">
    			<id column="posid" property="posid"></id>
    			<result column="pname" property="pname"></result>
    		</association>
    	</resultMap>
    	<select id="queryEmp" resultMap="rm">
    		select e.empno, e.ename, e.job, d.deptname, p.pname
    		from emp e join dept d on e.deptno=d.deptno join position p on e.posid=p.posid
    		<where>
    			<if test="param1 != null and param1 !=''">
    				<bind name="empno" value="'%'+param1+'%'"></bind>
    				and e.empno like #{empno}
    			</if>
    			<if test="param2 != null and param2 != ''">
    				<bind name="ename" value="'%'+param2+'%'"></bind>
    				and e.ename like #{ename}
    			</if>
    			<if test="param3 != null and param3 != ''">
    				<bind name="job" value="'%'+param3+'%'"></bind>
    				and e.job like #{job}
    			</if>
    			<if test="param4 != null and param4 != ''">
    				<bind name="deptname" value="'%'+param4+'%'"></bind>
    				and d.deptname like #{deptname}
    			</if>
    			<if test="param5 != null and param5 != ''">
    				<bind name="pname" value="'%'+param5+'%'"></bind>
    				and p.pname like #{pnmae}
    			</if>
    		</where>
    		limit #{param6}, #{param7}
    	</select>
    </mapper>
    

    2.2 注解实现一

    public interface EmpMapper{
    	@Select("<script>select e.empno, e.ename, e.job, d.deptname, p.pname"+
    	"from emp e, dept d, position p"+
    	"where e.deptno=d.deptno and e.posid=p.posid"+
    	"<if test='param1 != null and param1 != \"\"'>and e.empno like concat('%',#{param1},'%')</if>"+
    	"<if test='param2 != null and param2 != \"\"'>and e.ename like concat('%',#{param2},'%')</if>"+
    	"<if test='param3 != null and param3 != \"\"'>and e.job like concat('%',#{param3},'%')</if>"+
    	"<if test='param4 != null and param4 != \"\"'>and d.deptname like concat('%',#{param4},'%')</if>"+
    	"<if test='param5 != null and param5 != \"\"'>and p.pname like concat('%',#{param5},'%')</if>"+
    	"limit #{param6}, #{param7}</script>")
    	List<Emps> queryEmp(String empno, String ename, String job, String dname, String pname, int startPage, int pageSize);
    }
    

    2.3 注解实现二(提前封装%)

    对于xml实现与注解实现一,都不需要提前将用于模糊查询的%封装进参数中

    int empno=1;
    String ename="khue";
    String job="coding";
    String dname="programmer";
    Stirng pname="server" 
    

    而对于注解实现中,也可以提前将%封装进参数

    String empno="%1%";
    String ename="%khue%";
    String job="%coding%";
    String dname="%programmer%";
    Stirng pname="%server%" 
    
    public interface EmpMapper{
    	@Select("<script>select e.empno, e.ename, e.job, d.deptname, p.pname"+
    	"from emp e, dept d, position p"+
    	"where e.deptno=d.deptno and e.posid=p.posid"+
    	"<if test='param1 != \"%null%\" and param1 != \"%%\"'>and e.empno like #{param1}</if>"+
    	"<if test='param2 != \"%null%\" and param2 != \"%%\"'>and e.ename like #{param2}</if>"+
    	"<if test='param3 != \"%null%\" and param3 != \"%%\"'>and e.job like #{param3}</if>"+
    	"<if test='param4 != \"%null%\" and param4 != \"%%\"'>and d.deptname like #{param4}</if>"+
    	"<if test='param5 != \"%null%\" and param5 != \"%%\"'>and p.pname like #{param5}</if>"+
    	"limit #{param6}, #{param7}</script>")
    	List<Emps> queryEmp(String empno, String ename, String job, String dname, String pname, int startPage, int pageSize);
    }
    

    2.4 注意

    在IDEA中使用Mybaits向MySQL传入参数时,MySQL为int、Date类型的皆可以直接使用String类型传入

    展开全文
  • MySQL - 如何优化模糊查询(like 模糊查询)

    万次阅读 热门讨论 2019-08-04 23:15:50
    数据量的情况下,不容易看出查询的效率,但是数据量达到百万级,千万级甚至更高的时候,查询的效率就很容易显现出来了,此时,查询效率就显得很重要了,接下来,就要看你如何优化了。 前面讲过 MySQL - 如何...

    在MySQL中,模糊查询肯定要使用like关键字,然后在加 %%,是代表前模糊还是后模糊。数据量小的情况下,不容易看出查询的效率,但是数据量达到百万级,千万级甚至更高的时候,查询的效率就很容易显现出来了,此时,查询效率就显得很重要了,接下来,就要看你如何优化了。

    前面讲过

    MySQL - 如何提高SQL的查询效率(where条件优化)

    全局检索

    建立索引的情况下

    一般情况下like模糊查询的写法为(field已建立索引):

    SELECT `column` FROM `table` WHERE `field` like '%keyword%';

    上面的语句用explain解释来看,SQL语句并未用到索引,而且是全表搜索,如果在数据量超大的时候,可想而知最后的效率会是这样,和没有加索引是没什么区别的。

    对比下面的写法:

    SELECT `column` FROM `table` WHERE `field` like 'keyword%';

    这样的写法用explain解释看到,SQL语句使用了索引,搜索的效率大大的提高了!

    但是有的时候,我们在做模糊查询的时候,并非要想查询的关键词都在开头,所以如果不是特别的要求,"keywork%"并不合适所有的模糊查询

    这个时候,我们可以考虑用其他的方法。

    通过函数达到模糊查询的效果

    LOCATE('substr',str,pos)

    举例:

    SELECT LOCATE('xbar',`foobar`); 
    ###返回0 
    
    SELECT LOCATE('bar',`foobarbar`); 
    ###返回4
    
    SELECT LOCATE('bar',`foobarbar`,5);
    ###返回7

    解释:

    返回 substr 在 str 中第一次出现的位置,如果 substr 在 str 中不存在,返回值为 0 。

    如果pos存在,返回 substr 在 str 第pos个位置后第一次出现的位置,如果 substr 在 str 中不存在,返回值为0。

    解决模糊查询方案:

    SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`)>0
    

    备注:keyword是要搜索的内容,field为被匹配的字段,查询出所有存在keyword的数据

    POSITION('substr' IN `field`)

    position可以看做是locate的别名,功能跟locate一样:

    SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)
    

    INSTR(`str`,'substr')

    SELECT `column` FROM `table` WHERE INSTR(`field`, 'keyword' )>0 
    

    FIND_IN_SET(str1,str2)

    返回str2中str1所在的位置索引,其中str2必须以","分割开。

    SELECT * FROM `person` WHERE FIND_IN_SET('apply',`name`);
    

    展开全文
  • 场景描述 问题描述 构造测试数据 方案演进 方案一:普通分页 分析: 方案二:普通分页+分页锚点 分析 方案三(最终方案):中间分页+分页锚点+自连接 分析: 环境参数 硬件 内存 ...

    目录

     

    环境参数

    场景描述

    问题描述

    构造测试数据

    方案演进

    方案一:普通分页

    分析:

    方案二:普通分页+分页锚点

    分析

    方案三(最终方案):中间表分页+分页锚点+自连接

    分析:


    环境参数

    硬件

    内存

    1G

    处理器数量

    1

    内核数量

    4

    硬盘

    机械硬盘

    软件

    Mysql

    5.7.19

    操作系统

    虚拟机 CentOS Linux release 7.5.1804 (Core)

     

    结构

    CREATE TABLE `pay_online_dtl_info` (

      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',

      `payer_belong_ou_id` varchar(300) DEFAULT NULL COMMENT '[]付方所属组织OU_ID',

      `pay_amt` varchar(300) DEFAULT NULL COMMENT '[]支付金额',

      `payer_acct_no` varchar(300) DEFAULT NULL COMMENT '[]付方账号',

      `payee_acct_no` varchar(300) DEFAULT NULL COMMENT '[]收方账号',

      `pay_main_id` int(11) DEFAULT NULL COMMENT '付款主ID',

      `intfc_id` int(11) DEFAULT NULL COMMENT '接口ID',

      `src_doc_type_cd` varchar(20) DEFAULT NULL COMMENT '来源单据类型代码',

      `src_doc_no` varchar(50) DEFAULT NULL COMMENT '来源单据编号',

      `src_chanl_cd` varchar(20) DEFAULT NULL COMMENT '来源渠道代码',

      `src_doc_row_num` varchar(50) DEFAULT NULL COMMENT '来源单据行号',

      `vouch_no` varchar(100) DEFAULT NULL COMMENT '凭证编号',

      `biz_type_nm` varchar(20) DEFAULT NULL COMMENT '业务类型',

      `payer_belong_ou_nm` varchar(100) DEFAULT NULL COMMENT '付方所属组织名称',

      `payer_belong_dept_id` varchar(50) DEFAULT NULL COMMENT '付方所属部门ID',

      `payer_belong_dept_nm` varchar(100) DEFAULT NULL COMMENT '付方所属部门名称',

      `payer_biz_main_cd` varchar(10) DEFAULT NULL COMMENT '付方所属业务主体代码',

      `payer_biz_main_nm` varchar(50) DEFAULT NULL COMMENT '付方所属业务主体名称',

      `payer_nm` varchar(100) DEFAULT NULL COMMENT '付方名称',

      `payer_open_bank_cd` varchar(10) DEFAULT NULL COMMENT '付方开户银行代码',

      `payer_open_branch_nm` varchar(100) DEFAULT NULL COMMENT '付方开户网点名称',

      `payer_acct_nm` varchar(100) DEFAULT NULL COMMENT '付方账户名称',

      `payer_pbc_link_no` varchar(50) DEFAULT NULL COMMENT '付方人行联行号',

      `payer_acct_belong_ou` varchar(100) DEFAULT NULL COMMENT '付方账户所属OU',

      `payer_belong_area_cd` varchar(50) DEFAULT NULL COMMENT '付方账户所属地市代码',

      `payer_belong_prov_cd` varchar(50) DEFAULT NULL COMMENT '付方账户所属省份代码',

      `payer_currency_cd` char(3) DEFAULT NULL COMMENT '付方币种代码',

      `payer_currency_nm` varchar(20) DEFAULT NULL COMMENT '付方币种',

      `payee_nm` varchar(100) DEFAULT NULL COMMENT '收方名称',

      `payee_open_bank_cd` varchar(10) DEFAULT NULL COMMENT '收方开户银行代码',

      `payee_open_branch_nm` varchar(100) DEFAULT NULL COMMENT '收方开户网点名称',

      `payee_open_branch_addr` varchar(200) DEFAULT NULL COMMENT '收方开户网点地址',

      `payee_acct_nm` varchar(100) DEFAULT NULL COMMENT '收方账户名称',

      `payee_currency_cd` varchar(10) DEFAULT NULL COMMENT '收方币种',

      `payee_currency_nm` char(3) DEFAULT NULL COMMENT '收方币种代码',

      `payee_categ_cd` varchar(20) DEFAULT NULL COMMENT '收方类别代码',

      `payee_pbc_link_no` varchar(50) DEFAULT NULL COMMENT '收方人行联行号',

      `payee_belong_prov_cd` varchar(50) DEFAULT NULL COMMENT '收方账户所属省份代码',

      `payee_belong_area_cd` varchar(50) DEFAULT NULL COMMENT '收方账户所属地市代码',

      `pay_reason` varchar(100) DEFAULT NULL COMMENT '付款事由',

      `provider_categ_cd` varchar(20) DEFAULT NULL COMMENT '供应商类别代码',

      `same_city_ind` varchar(20) DEFAULT NULL COMMENT '同城标志',

      `same_bnk_ind` varchar(20) DEFAULT NULL COMMENT '同行标志',

      `priority` varchar(20) DEFAULT NULL COMMENT '优先级',

      `indiv_ind` varchar(20) DEFAULT NULL COMMENT '对私标志',

      `ebank_input_ind` varchar(20) DEFAULT NULL COMMENT '网银补录完整标志',

      `pre_pay_dt` date DEFAULT NULL COMMENT '预支付日期',

      `push_position_ind` varchar(20) DEFAULT NULL COMMENT '推送头寸完成标志',

      `push_budget_ind` varchar(20) DEFAULT NULL COMMENT '推送预算完成标志',

      `src_doc_maker_id` varchar(10) DEFAULT NULL COMMENT '来源单据起草人ID',

      `src_doc_maker_nm` varchar(20) DEFAULT NULL COMMENT '来源单据起草人',

      `src_doc_maker_tel_num` varchar(20) DEFAULT NULL COMMENT '来源单据起草人手机号',

      `src_doc_create_dt` date DEFAULT NULL COMMENT '来源单据生成日期',

      `invc_no` varchar(50) DEFAULT NULL COMMENT '发票号',

      `check_accountant` varchar(20) DEFAULT NULL COMMENT '核算会计人',

      `check_no` varchar(50) DEFAULT NULL COMMENT '支票号',

      `contract_no` varchar(50) DEFAULT NULL COMMENT '合同号',

      `due_dt` date DEFAULT NULL COMMENT '到期日',

      `pay_status` varchar(20) DEFAULT NULL COMMENT '支付状态',

      `pay_channel` varchar(20) DEFAULT NULL COMMENT '支付通道',

      `pay_way_cd` varchar(20) DEFAULT NULL COMMENT '支付方式代码',

      `pay_type_cd` varchar(20) DEFAULT NULL COMMENT '支付类型代码',

      `pay_property` varchar(20) DEFAULT NULL COMMENT '付款属性',

      `pay_dt` date DEFAULT NULL COMMENT '支付日期',

      `pay_tm` datetime DEFAULT NULL COMMENT '支付时间',

      `init_accountant` varchar(20) DEFAULT NULL COMMENT '初核会计人',

      `acct_maker_nm` varchar(20) DEFAULT NULL COMMENT '会计制单人',

      `sys_out_pay_tm` datetime DEFAULT NULL COMMENT '系统外支付时间',

      `pay_ind` varchar(20) DEFAULT NULL COMMENT '支付标识',

      `sys_out_pay_ind` varchar(20) DEFAULT NULL COMMENT '系统外支付标志',

      `sys_out_pay_reason` varchar(200) DEFAULT NULL COMMENT '系统外支付原因',

      `sys_out_pay_chanl_cd` varchar(20) DEFAULT NULL COMMENT '系统外支付渠道代码',

      `remark` varchar(200) DEFAULT NULL COMMENT '摘要',

      `doc_status` varchar(20) DEFAULT NULL COMMENT '单据状态',

      `remark_cd` varchar(50) DEFAULT NULL COMMENT '摘要代码',

      `pay_doc_type` varchar(20) DEFAULT NULL COMMENT '付款单类型',

      `purpose` varchar(100) DEFAULT NULL COMMENT '用途',

      `postscript` varchar(100) DEFAULT NULL COMMENT '附言',

      `cancel_ind` varchar(20) DEFAULT NULL COMMENT '作废标志',

      `gl_dt` date DEFAULT NULL COMMENT '总账日期',

      `reject_tm` datetime DEFAULT NULL COMMENT '驳回时间',

      `reject_reason_cd` varchar(20) DEFAULT NULL COMMENT '驳回原因代码',

      `reject_reason` varchar(200) DEFAULT NULL COMMENT '驳回原因',

      `reject_status` varchar(20) DEFAULT NULL COMMENT '驳回状态',

      `prov_pay_ind` varchar(20) DEFAULT NULL COMMENT '省统付标志',

      `repay_ind` varchar(20) DEFAULT NULL COMMENT '再次支付标志',

      `reimport_ind` varchar(20) DEFAULT NULL COMMENT '再次导入标志',

      `central_pay_ind` varchar(20) DEFAULT NULL COMMENT '集中支付标志',

      `group_pay_ind` varchar(20) DEFAULT NULL COMMENT '集团统付标志',

      `budget_check_ind` varchar(20) DEFAULT NULL COMMENT '预算校验通过标志',

      `process_inst_id` varchar(20) DEFAULT NULL COMMENT '流程实例ID',

      `withdraw_reason` varchar(100) DEFAULT NULL COMMENT '撤回原因',

      `bnk_return_info` varchar(200) DEFAULT NULL COMMENT '银行返回信息',

      `biz_pay_type` varchar(20) DEFAULT NULL COMMENT '业务支付类型',

      `conf_doc_status` varchar(20) DEFAULT NULL COMMENT '确认单状态',

      `deal_mode_cd` varchar(20) DEFAULT NULL COMMENT '处理方式代码',

      `get_ind` varchar(20) DEFAULT NULL COMMENT '认领标志',

      `get_user_id` varchar(10) DEFAULT NULL COMMENT '认领人ID',

      `get_user_nm` varchar(20) DEFAULT NULL COMMENT '认领人',

      `get_tm` datetime DEFAULT NULL COMMENT '认领时间',

      `approver_nm` varchar(20) DEFAULT NULL COMMENT '审批人',

      `approver_id` varchar(10) DEFAULT NULL COMMENT '审批人ID',

      `approve_tm` datetime DEFAULT NULL COMMENT '审批时间',

      `approve_opinion` varchar(100) DEFAULT NULL COMMENT '审批意见',

      `pay_user_id` varchar(10) DEFAULT NULL COMMENT '支付人ID',

      `pay_user_nm` varchar(20) DEFAULT NULL COMMENT '支付人',

      `record_create_tm` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',

      `record_modify_tm` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录修改时间',

      `data_auth_id` varchar(255) DEFAULT NULL COMMENT '数据权限ID',

      `data_icv` varchar(50) DEFAULT NULL COMMENT '数据完整性校验值',

      `payer_acct_class_cd` varchar(20) DEFAULT NULL COMMENT '付方账户分类代码',

      PRIMARY KEY (`id`),

      KEY `idx_pboi` (`payer_belong_ou_id`)

    ) ENGINE=InnoDB AUTO_INCREMENT=14330001 DEFAULT CHARSET=utf8mb4 COMMENT='线上付款明细信息表'

    索引

    数据量

    14, 330, 000

     

    场景描述

    根据 付方所属组织 的内容做模糊匹配查询,关键词处于该列的中间部分,致使查询条件为 where payer_belong_ou_id like ‘%keyword%’,使得在该列上建立的索引无法生效

    EXPLAIN SELECT * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%";

    说明:

    1. 实际查询时,不会使用select *,但难以保证会使用索引覆盖的方式查询,因此按照最坏的打算——使用select *

    2. payer_belong_ou_id存储的是“付方所属组织OU_ID”,由于pay_online_dtl_info表中没有“付方所属组织名”,而且payer_belong_ou_id的类型是varchar,故而使用此列充当“付方所属组织名”,不影响测试结果

     

    问题描述

    通常一个系统的页面显示数据量不会太大,过多的条数会给用户造成困扰,也会增加磁盘io的开销、网络的开销。每页数据量,暂定1000条数据

    构造测试数据

    总数据量:14330000条

    满足查询需求的数据量:28660条

    方案演进

    方案一:普通分页

    第一页

    SELECT SQL_NO_CACHE * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%"

    LIMIT 0,1000;

    查询次数

    耗时

    1

    2.893s

    2

    2.795s

    3

    2.813s

    4

    2.857s

    5

    2.899s

    -------------

    Avg

    2.851s

    第二页

    SELECT SQL_NO_CACHE * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%"

    LIMIT 1000,1000;

    查询次数

    耗时

    1

    5.755s

    2

    5.723s

    3

    5.729s

    4

    5.770s

    5

    5.777s

    -------------

    Avg

    5.751s

    第三页

    SELECT SQL_NO_CACHE * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%"

    LIMIT 2000,1000;

    查询次数

    耗时

    1

    8.568s

    2

    8.863s

    3

    8.649s

    4

    8.669s

    5

    8.560s

    -------------

    Avg

    8.662s

    倒数第三页

    SELECT SQL_NO_CACHE * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%"

    LIMIT 26000,1000;

    查询次数

    耗时

    1

    1min 23s

    2

    1min 22s

    3

    1min 26s

    4

    1min 21s

    5

    1min 23s

    -------------

    Avg

    1min 23s

    倒数第二页

    SELECT SQL_NO_CACHE * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%"

    LIMIT 27000,1000;

    查询次数

    耗时

    1

    1min 25s

    2

    1min 25s

    3

    1min 23s

    4

    1min 25s

    5

    1min 25s

    -------------

    Avg

    1min 25s

    倒数第一页

    SELECT SQL_NO_CACHE * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%"

    LIMIT 28000,1000;

     

    查询次数

    耗时

    1

    1min 25s

    2

    1min 29s

    3

    1min 26s

    4

    1min 25s

    5

    1min 28s

    -------------

    Avg

    1min 27s

    分析:

    1.虽然建有索引,但是由于使用like ‘%天通苑北%’的方式查询,索引必然失效。

     

    2.由于是select *, 回基表取得数据的io巨大,而且仅仅使用查出数据的一部分(一页),大量磁盘io开销被浪费,且越靠后的页,查询越慢

     

    方案二:普通分页+分页锚点

    分页锚点,就是上一页数据中,最后一条数据的主键。假设第n页的最后一条数据的主键值为k,由于主键是递增的,因此第n+1页数据的主键值必然是一个大于k的数

    第一页

    SELECT SQL_NO_CACHE  * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%"

    AND id > 0 LIMIT 1000;

    查询次数

    耗时

    1

    2.977s

    2

    2.988s

    3

    2.955s

    4

    3.126s

    5

    2.983s

    -------------

    Avg

    3.006s

    第二页

    SELECT SQL_NO_CACHE  * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%"

    AND id > 499501 LIMIT 1000;

    查询次数

    耗时

    1

    3.021s

    2

    3.013s

    3

    3.012s

    4

    2.970s

    5

    3.002s

    -------------

    Avg

    3.004s

    第三页

    SELECT SQL_NO_CACHE * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%"

    AND id > 999501 LIMIT 1000;

    查询次数

    耗时

    1

    3.002s

    2

    2.995s

    3

    2.961s

    4

    2.993s

    5

    2.989s

    -------------

    Avg

    2.988s

    倒数第三页

    SELECT SQL_NO_CACHE * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%"

    AND id > 12999501 LIMIT 1000;

    查询次数

    耗时

    1

    2.902s

    2

    2.894s

    3

    2.918s

    4

    2.861s

    5

    2.881s

    -------------

    Avg

    2.891s

    倒数第二页

    SELECT SQL_NO_CACHE * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%"

    AND id > 13499501 LIMIT 1000;

    查询次数

    耗时

    1

    2.955s

    2

    2.955s

    3

    3.072s

    4

    2.965s

    5

    2.967s

    -------------

    Avg

    2.983

    倒数第一页

    SELECT SQL_NO_CACHE * FROM `pay_online_dtl_info` WHERE payer_belong_ou_id LIKE "%天通苑北%"

    AND id > 13999501 LIMIT 1000;

    查询次数

    耗时

    1

    1.924s

    2

    1.987s

    3

    1.998s

    4

    1.967s

    5

    1.970s

    -------------

    Avg

    1.969s

    分析

    1.引入分页锚点(主键),查询的时候使用到主键索引

    2. select *, 回基表取得数据的io巨大,这一问题仍然存在

     

    方案三(最终方案):中间表分页+分页锚点+自连接

    第一页

    SELECT SQL_NO_CACHE * FROM pay_online_dtl_info INNER JOIN (

           SELECT id FROM pay_online_dtl_info WHERE payer_belong_ou_id LIKE "%天通苑北%" AND id > 0 LIMIT 1000

    ) t ON pay_online_dtl_info.`id` = t.`id`;

    查询次数

    耗时

    1

    0.609s

    2

    0.609s

    3

    0.587s

    4

    0.600s

    5

    0.585s

    -------------

    Avg

    0.598s

    第二页

    SELECT SQL_NO_CACHE  * FROM pay_online_dtl_info INNER JOIN (

           SELECT id FROM pay_online_dtl_info WHERE payer_belong_ou_id LIKE "%天通苑北%" AND id > 499501 LIMIT 1000

    ) t ON pay_online_dtl_info.`id` = t.`id`;

    查询次数

    耗时

    1

    0.575s

    2

    0.697s

    3

    0.713s

    4

    0.569s

    5

    0.566s

    -------------

    Avg

    0.624s

    第三页

    SELECT SQL_NO_CACHE  * FROM pay_online_dtl_info INNER JOIN (

           SELECT id FROM pay_online_dtl_info WHERE payer_belong_ou_id LIKE "%天通苑北%" AND id > 999501 LIMIT 1000

    ) t ON pay_online_dtl_info.`id` = t.`id`;

    查询次数

    耗时

    1

    0.570s

    2

    0.569s

    3

    0.614s

    4

    0.578s

    5

    0.566s

    -------------

    Avg

    0.579s

    倒数第三页

    SELECT SQL_NO_CACHE  * FROM pay_online_dtl_info INNER JOIN (

           SELECT id FROM pay_online_dtl_info WHERE payer_belong_ou_id LIKE "%天通苑北%" AND id > 12999501 LIMIT 1000

    ) t ON pay_online_dtl_info.`id` = t.`id`;

    查询次数

    耗时

    1

    0.587s

    2

    0.637s

    3

    0.589s

    4

    0.613s

    5

    0.581s

    -------------

    Avg

    0.601s

    倒数第二页

    SELECT SQL_NO_CACHE * FROM pay_online_dtl_info INNER JOIN (

           SELECT id FROM pay_online_dtl_info WHERE payer_belong_ou_id LIKE "%天通苑北%" AND id > 13499501 LIMIT 1000

    ) t ON pay_online_dtl_info.`id` = t.`id`;

    查询次数

    耗时

    1

    0.604s

    2

    0.581s

    3

    0.568s

    4

    0.574s

    5

    0.563s

    -------------

    Avg

    0.578s

    倒数第一页

    SELECT SQL_NO_CACHE * FROM pay_online_dtl_info INNER JOIN (

           SELECT id FROM pay_online_dtl_info WHERE payer_belong_ou_id LIKE "%天通苑北%" AND id > 13999501 LIMIT 1000

    ) t ON pay_online_dtl_info.`id` = t.`id`;

    查询次数

    耗时

    1

    0.394s

    2

    0.379s

    3

    0.384s

    4

    0.414s

    5

    0.403s

    -------------

    Avg

    0.395s

    分析:

    1. 将查询行为分为两部分,第一步,获取中间表,仅包含一列主键列,第二步,基于此中间表做关联查询,查询全部列数据(本例中以select * 为例,真实场景中仍然应该只查出必要列)

    2.查中间表时候直接从索引获取数据,无需回基表查找,io小,得到中间表后,再做第二步,连接查询,基于主键精确获得全部数据,同样避免大量磁盘io

     

     

     

     

    展开全文
  • 针对不确定性数据中模糊关联规则的挖掘问题,提出一种基于群搜索优化(GSO)算法优化隶属度函数(MF)的模糊关联规则挖掘方法。首先,将不确定性数据通过三元语言表示模型进行表示;然后,给定一个初始MF,并以最大...
  • 模糊查询like优化

    2019-01-28 09:10:32
    原sql语句:select project_ name from project_info where project_name like "...所以采用另一种机制,为project_name建立索引,从索引模糊匹配索引值去查询索引值。 原sql语句中我只需要project_nam...

    原sql语句:select project_ name from project_info where project_name like "%北京%"

    1.建立索引(最为有效)

    我们都知道,因为索引最左前缀匹配原则,全模糊匹配,sql是不走索引的。

    所以采用另一种机制,为project_name建立索引,从索引表中模糊匹配索引值去查询索引值。

    原sql语句中我只需要project_name,因此为project_name建立索引,形成“表”,因此只从索引表中取速率更快。

    通过explain后查询得到sql语句走的是 index索引查询

    如果需要查询多个字段,例如sql

    select project_name,project_code from project_info where project_name like "%北京%"

    可以建立联合索引,为project_code,project_name建立联合索引,这样依旧从索引表中取需要的字段,速度必然快

    从一开始的6.3秒变为0.248秒

    2.instr()方法截取字符串

    sql:select project_name from project_info where instr(project_name,"北京") >0

    instr()相当于截取字符串,用时4.9秒

    展开全文
  • t是小表 s是大表 SELECT s.id,s.nick,s.userId,s.sid,s.session_key,s.retoke,s.shouquantime,s.refresh_token_timeout,s.timezone,t.endtime from taobao_session as s LEFT JOIN taobao_productrepost_set as t on...
  • 1、多字段like模糊查询优化: 最常见的写法: where a like '%xx%' or b like '%xx%' or c like '%xx%' 这种写法查询效率低,经过调查,下面的方法可以替代,并且效率高: 2、如果like的关键字相同: where ...
  • 研究稳态工业过程的递阶优化控制算法时, 针对模型-实际存在差异以及实际约束条件有一 定伸缩性的问题, 将子过程模型作为等式约束, 通过引入模糊系数使其转化为模糊等式约束, 同时对子 过程原有的不等式...
  • Sql性能优化之LIKE模糊查询

    千次阅读 2017-09-06 17:25:13
    我们在写sql的时候应该尽量避免在一个复杂查询里面使用 LIKE ‘%parm1%’—— 由于parm1前面用到了“%”,因此该查询必然走全扫描,导致相关列的索引无法使用,除非必要,否则不要在关键词前加%, 如果后台逻辑...
  • 将灰色关联分析方法和模糊逻辑推理技术应用于圆柱直齿轮锻模的正交优化设计,以分流角、凸模成形角、反向凸模成形角和分流距4个齿轮锻模设计的关键参数作为设计变量,以成形时所需工作载荷最小和成形后齿轮件端面...
  • 针对模糊属性事务数据库提取模糊关联规则的问题,采用...利用模糊关联规则格挖掘关联规则,与采用Apriori算法计算频繁项目集获取规则相比较,容易获得用户感兴趣的关联规则,同时减少冗余规则的生成,使挖掘算法得到优化.
  • 针对一类采用Takagi2Sugeno 模糊模型描述的非线性时滞关联大系统, 研究其分散模糊状态反馈控制器设 计问题; 利用L yapunov 稳定性分析理论和线性矩阵不等式等工具, 得到了闭环系统的可镇定条件和相应的分散模...
  • 针对一类采用Takagi-Sugeno模糊模型描述的非线性时滞关联大系统,研究其分散模糊状态反馈控制器设计问题;利用Lyapunov稳定性分析理论和线性矩阵不等式等工具,得到了闭环系统的可镇定条件和相应的分散模糊状态反馈...
  • 如何优化MySQL千万级大表,我写了6000字的解读

    万次阅读 多人点赞 2019-10-21 20:03:03
    千万级大表如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面的经验总结,也欢迎大家提出建议。 从一开始脑海里开始也是...
  • 这里写目录标题事故现场解决方案提到的“回表查询”InnoDB的索引什么是回表查询怎么优化表查询 事故现场 数据库使用的MySQL,有一个日志,需要进行分页查询,于是很容易就想到了limit [offset偏移量] [count数量...
  • Oracle 查询技巧与优化(一) 单表查询与排序

    千次阅读 多人点赞 2016-08-02 09:22:12
    关于Oracle单表查询与排序相关的技巧与优化~
  • 一文带你彻底搞懂Elasticsearch中的模糊查询 写在前面 Elasticsearch(以下简称ES)中的模糊查询官方是建议慎用的,因为的它的性能不是特别好。不过这个性能不好是相对ES自身的其它查询(term,match)而言的,如果...
  • SqlServer 模糊查询

    万次阅读 2019-01-03 17:49:56
    SqlServer 模糊查询,通配符,like,多段匹配
  • Django ORM 模糊查询查询操作

    千次阅读 2019-10-28 20:18:27
    模糊查询常用的操作 Q查询: from django.db.models import Q Q(question__startswith='What') Q(question__startswith='Who') | Q(question__startswith='What') This is equivalent to the following SQL WHERE ...
  • 代过程中训练样本的加权分类错误率和子分类器中模糊关联分类规则数目及规则中所含模糊项的数目为遗传优化 目标, 实现了AdaBoost.M1W 和模糊关联分类建模过程的较好融合. 通过5 个多类不平衡UCI 标准数据集和...
  • 针对属性权重完全未知, 属性值以直觉梯形模糊数形式给出的多属性决策问题, 提出一种灰关联投影寻踪动态聚类法. 该方法综合考虑灰色关联和优隶属度, 改进灰关联投影法(GRPM), 并将改进的灰关联投影法与投影寻踪动态...
  • 针对当前多目标优化方法中存在的不足之处,提出了基于模糊推理的多目标优化方法,并详细阐述了模糊推理的过程,即包括模糊化、推理和清晰化3个过程。最后将模糊推理运用于熔融堆积成型工艺参数优化,待优化的参数为线宽...
  • 针对模糊属性事务数据库提取模糊关联规则的问题,...利用模糊关联规则格挖掘关联规则,与采用Apriori算法计算频繁项目集获取规则相比较,容易获得用户感兴趣的关联规则,同时减少冗余规则的生成,使挖掘算法得到优化
  • 大小写操作上述命令,看结果如何。结果说明mysql命令的大小写结 果是一致的。 练习如下操作: mysql>Select (20+5)*4; mysql>Select (20+5)*4,sin(pi()/3); mysql>Select (20+5)*4 AS Result,sin(pi()/...
  • EF Linq字符串模糊查询整理

    千次阅读 2017-11-07 16:32:28
    一、基础模糊查询 1.判断是否为空或者null string.IsNullOrEmpty(des.PlateNum)————————>sql server的PlateNum is null的判断 from des in db.ModelsVehicleRecognition where (!string.IsNullOrEmpty...
  • mysql优化避免全扫描策略总结

    千次阅读 2019-07-21 09:33:48
    1.对查询进行优化,应尽量避免全扫描,首先应考虑在 where 及 order by 涉及的列上建立索引 mysql引擎放弃使用索引而进行全扫描的几种情况: 应尽量避免在 where 子句中对字段进行 null 值判断,可以设置...
  • 针对属性权重完全未知的犹豫模糊多属性决策问题, 提出一种属性权重多目标优化方法. 首先, 根据属性值的均值、方差以及属性间的关联度建立属性权重确定模型; 然后, 利用方案与犹豫模糊正理想点的相似度对方案进行排序...
  • 千万级大表如何优化

    千次阅读 2019-11-21 16:16:06
    千万级大表如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面的经验总结,也欢迎大家提出建议。 从一开始脑海里开始也是火光...
  • 该算法通过借鉴生物免疫系统中的克隆选择原理来实施优化操作,它直接从给出的数据中,通过优化机制自动确定每个属性对应的模糊集合,使推导出的满足条件的模糊关联规则数目最多.将实际数据集和相关算法进行性能比较...
  • MySQL 索引原理及慢查询优化

    千次阅读 2016-05-19 19:43:28
    我们知道一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然是重中之重。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 30,220
精华内容 12,088
关键字:

小表关联大表模糊查询优化