目录

 

环境参数

表

场景描述

问题描述

构造测试数据

方案演进

方案一：普通分页

分析：

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

分析

方案三（最终方案）：中间表分页+分页锚点+自连接

分析：

环境参数

硬件

内存

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='线上付款明细信息表'

索引

mysql 千万数据模糊查询方案 千万级数据模糊查询_数据库

数据量

14, 330, 000

 

场景描述

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

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

mysql 千万数据模糊查询方案 千万级数据模糊查询_mysql 千万数据模糊查询方案_02

说明：

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

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

 

问题描述

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

构造测试数据

总数据量：14330000条

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

mysql 千万数据模糊查询方案 千万级数据模糊查询_mysql_03

mysql 千万数据模糊查询方案 千万级数据模糊查询_分页_04

方案演进

方案一：普通分页

第一页

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 ‘%天通苑北%’的方式查询，索引必然失效。

mysql 千万数据模糊查询方案 千万级数据模糊查询_数据库_05

mysql 千万数据模糊查询方案 千万级数据模糊查询_分页_06

 

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．引入分页锚点（主键），查询的时候使用到主键索引

mysql 千万数据模糊查询方案 千万级数据模糊查询_mysql_07

mysql 千万数据模糊查询方案 千万级数据模糊查询_数据库_08

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 * 为例，真实场景中仍然应该只查出必要列）

mysql 千万数据模糊查询方案 千万级数据模糊查询_SQL_09

mysql 千万数据模糊查询方案 千万级数据模糊查询_数据库_10

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