当前位置：网站首页>Tikv & tiflash accelerates complex business queries

Tikv & tiflash accelerates complex business queries

2022-07-18 03:35:00 【InfoQ】

author ： The source of the original text of Bian Cheng Yuan ：
https://tidb.net/blog/34dcd203

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 | TiDB Row column mixing is not a choice between row storage and column storage in traditional design , It is TiDB You can have row storage and column storage at the same time in the same table , And the two always maintain strong data consistency （ Not the final agreement ）. || - | —————————————————————————— || 2 | Multiple table queries use different engines TIKV or TiFlash . || 3 | TiFlash Support MPP Query execution of pattern , That is, cross node data exchange is introduced into the calculation （data shuffle The process ）. |

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 .