Tikv & tiflash accelerates complex business queries

One 、TiKV Bank deposit And TiFlash Column storage mixed use

TiDB in query Schematic diagram of execution , You can see in the TiDB In a query The implementation of will be divided into two parts , Part of it is TiDB perform , Some are pushed down to the storage tier （ TiFlash/TiKV ） perform .

1.1 Mixing principle

1.2 Mix optimization

Two 、 Advanced filtering of labeling system

Through the label （ From the wide list of uncertain fields ） And narrow table specific fields to query customers and page

2.1 Read from TiKV

SELECT /*+ READ_FROM_STORAGE(tikv[b], tikv[c],tikv[d]) */	a.*,	b.CUST_NAME,b.CERT_TYPE,b.CERT_NUM,b.CUST_TYPE,b.SEX,b.AGE,b.BIRTH_DT,	c.ORG_ID,c.ORG_NAME,	d.ASSET,d.ASSET_MON_AVG FROM	(	SELECT /*+ READ_FROM_STORAGE(tikv[m],tikv[n]) */		m.cust_id 	FROM		m_cust_label m		RIGHT JOIN m_cust_org n ON m.CUST_ID = n.CUST_ID 	WHERE		m.cat1 IN ( 516, 710, 230,3301 ) 		AND n.ORG_ID IN ( '133','8716', '7162') ORDER BY	n.cust_id ASC 	LIMIT 100 	) a	LEFT JOIN m_cust_main b ON a.cust_id = b.cust_id	LEFT JOIN m_cust_org c ON a.cust_id = c.cust_id	LEFT JOIN m_cust_data d ON a.cust_id = d.cust_id ;	

4G,2c virtual machine 300 All the data , First execution 48 s Second execution 0.7s

2.2 Read From TiKV & TiFlash

  SELECT /*+ READ_FROM_STORAGE(tikv[b], tikv[c],tikv[d]) */	a.*,	b.CUST_NAME,b.CERT_TYPE,b.CERT_NUM,b.CUST_TYPE,b.SEX,b.AGE,b.BIRTH_DT,	c.ORG_ID,c.ORG_NAME,	d.ASSET,d.ASSET_MON_AVG FROM	(	SELECT /*+ READ_FROM_STORAGE(tiflash[m],tikv[n]) */		m.cust_id 	FROM		m_cust_label m		RIGHT JOIN m_cust_org n ON m.CUST_ID = n.CUST_ID 	WHERE		m.cat1 IN ( 516, 710, 230,3301 ) 		AND n.ORG_ID IN ( '133','8716', '7162') ORDER BY	n.cust_id ASC 	LIMIT 100 	) a	LEFT JOIN m_cust_main b ON a.cust_id = b.cust_id	LEFT JOIN m_cust_org c ON a.cust_id = c.cust_id	LEFT JOIN m_cust_data d ON a.cust_id = d.cust_id 

4G,2c virtual machine 300 All the data , First execution 3s Second execution 0.3s

2.3 TiFlash & MPP

set @@session.tidb_allow_mpp=1;set @@session.tidb_enforce_mpp=1;

SELECT /*+ READ_FROM_STORAGE(tikv[b], tikv[c],tikv[d]) */	a.*,	b.CUST_NAME,b.CERT_TYPE,b.CERT_NUM,b.CUST_TYPE,b.SEX,b.AGE,b.BIRTH_DT,	c.ORG_ID,c.ORG_NAME,	d.ASSET,d.ASSET_MON_AVG FROM	(	SELECT /*+ READ_FROM_STORAGE(tiflash[m],tiflash[n]) */		m.cust_id 	FROM		m_cust_label m		RIGHT JOIN m_cust_org n ON m.CUST_ID = n.CUST_ID 	WHERE		m.cat1 IN ( 516, 710, 230,3301 ) 		AND n.ORG_ID IN ( '133','8716', '7162') ORDER BY	n.cust_id ASC 	LIMIT 100 	) a	LEFT JOIN m_cust_main b ON a.cust_id = b.cust_id	LEFT JOIN m_cust_org c ON a.cust_id = c.cust_id	LEFT JOIN m_cust_data d ON a.cust_id = d.cust_id 

Use MPP Mode to execute the query, and then turn on ,4G 2c virtual machine 300 All the data , First execution 1s Second execution 0.15s

2.4 SPM Fixed execution plan

CREATE GLOBAL|SESSION  BINDING for	<BindableStmt > USING <BindableStmt2>SHOW GLOBAL|SESSION BINDINGS ; --  View the binding plan explain format = 'verbose' <BindableStmt2>;show warnings; --  Through execution  show warnings  Understand the  SQL  Which statement is used  binding

Fixed specific queries go TiFlash Inventory query .

3、 ... and 、 Ranking of value institutions under the label

3.1 According to the selected attribute （ Multivalued ）

Those who use these values most are in the top 3 Institutions , And calculate the total amount

3.2 Implementation plan

table:c go TiFlash ;table:a, table:b go TiKV , At the same time, it uses the advantages of column storage and row storage .

Four 、 summary

Use TiKV and TiFlash It can speed up complex queries , Use scenarios are simply added below .

If there is any improper description, please comment and correct ！

thank you PingCAP Strong community support ！

The author of this article ： Border town Yuanyuan