search

actiontech / dble

A High Scalability Middle-ware for MySQL Sharding
https://opensource.actionsky.com
GNU General Public License v2.0
1.09k stars 317 forks source link

route to multiple datanodes by hint with sql type success, but the results from multiple datanodes are only simply concatenated. #2276

Closed cloudfirst closed 3 years ago

cloudfirst commented 3 years ago

not sure whether it is a bug or not.

========================================================== mysql> explain select 1 from sid_asjc_re where asjc between 1100 and 1199; +---------------+----------+------------------------------------------------------------+ | SHARDING_NODE | TYPE | SQL/REF | +---------------+----------+------------------------------------------------------------+ | dn2 | BASE SQL | select 1 from sid_asjc_re where asjc between 1100 and 1199 | +---------------+----------+------------------------------------------------------------+

mysql> explain select 1 from sid_asjc_re where asjc between 1200 and 1299; +---------------+----------+------------------------------------------------------------+ | SHARDING_NODE | TYPE | SQL/REF | +---------------+----------+------------------------------------------------------------+ | dn3 | BASE SQL | select 1 from sid_asjc_re where asjc between 1200 and 1299 | +---------------+----------+------------------------------------------------------------+ 1 row in set (0.01 sec)

mysql> explain select 1 from sid_asjc_re where asjc between 1100 and 1229; +---------------+----------+------------------------------------------------------------+ | SHARDING_NODE | TYPE | SQL/REF | +---------------+----------+------------------------------------------------------------+ | dn2 | BASE SQL | select 1 from sid_asjc_re where asjc between 1100 and 1229 | | dn3 | BASE SQL | select 1 from sid_asjc_re where asjc between 1100 and 1229 | +---------------+----------+------------------------------------------------------------+

mysql> /!dble:sql=select 1 from sid_asjc_re where asjc between 1100 and 1199/ select doc_eid, doc_title from scopus_paper order by doc_eid limit 5; +---------+-----------------------------------------------------------------------------------------------+ | doc_eid | doc_title | +---------+-----------------------------------------------------------------------------------------------+ | 0 | 0 | | 110 | Symmetry of quantum phase space in a degenerate Hamiltonian system | | 227 | Lithography of 180nm design rule for 1Gbit DRAM | | 523 | Boundary subspaces for the finite element method with Lagrange multipliers | | 787 | A lagrangian method for calculating the dynamics of an incompressible fluid with free surface | +---------+-----------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec)

mysql> /!dble:sql=select 1 from sid_asjc_re where asjc between 1200 and 1299/ select doc_eid, doc_title from scopus_paper order by doc_eid limit 5; +---------+--------------------------------------------------------------------------------------------------------------+ | doc_eid | doc_title | +---------+--------------------------------------------------------------------------------------------------------------+ | 0 | 0 | | 649 | Automorphismengruppen von lokalkompakten zusammenh�ngenden Quasik�rpern und Translationsebenen | | 1074 | L-shell x-ray intensity ratios for au and pb at excitation energies 36.82, 43.95, 48.60, 50.20 and 53.50 kev | | 1789 | Object-Oriented Simulator Design For an Automated High-Speed Modular Placement Machine Family | | 1880 | Jump discontinuities of semilinear, strictly hyperbolic systems in two variables: Creation and propagation | +---------+--------------------------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec)

mysql> /!dble:sql=select 1 from sid_asjc_re where asjc between 1100 and 1229/ select doc_eid, doc_title from scopus_paper order by doc_eid limit 5; +---------+--------------------------------------------------------------------------------------------------------------+ | doc_eid | doc_title | +---------+--------------------------------------------------------------------------------------------------------------+ | 0 | 0 | | 110 | Symmetry of quantum phase space in a degenerate Hamiltonian system | | 227 | Lithography of 180nm design rule for 1Gbit DRAM | | 523 | Boundary subspaces for the finite element method with Lagrange multipliers | | 787 | A lagrangian method for calculating the dynamics of an incompressible fluid with free surface | | 0 | 0 | | 649 | Automorphismengruppen von lokalkompakten zusammenh�ngenden Quasik�rpern und Translationsebenen | | 1074 | L-shell x-ray intensity ratios for au and pb at excitation energies 36.82, 43.95, 48.60, 50.20 and 53.50 kev | | 1789 | Object-Oriented Simulator Design For an Automated High-Speed Modular Placement Machine Family | | 1880 | Jump discontinuities of semilinear, strictly hyperbolic systems in two variables: Creation and propagation | +---------+--------------------------------------------------------------------------------------------------------------+

what I expected is that : the last SQL statement will return the result that is REALLY ordered by doc_eid.

cloudfirst commented 3 years ago

env:

yanhuqing666 commented 3 years ago

Hint means that: I know what I am doing, do not change anything, just send my QUERY to that I hint. When we use hint，we usually 1.R-W split. 2.Call procedure

  1. Route QUERY to the single Node or All nodes for strange propose (usually not),

In your case, looks like you need to just send a query like this: select doc_eid, doc_title from scopus_paper where asjc between 1100 and 1229 order by doc_eid limit 5; If your table scopus_paper not contains column asjc, you can use JOIN or check your table structure.

cloudfirst commented 3 years ago

that is the reason why I have to use hint instead of join. column asjc is not included in table scopus_paper for business reason.

then I try to use hint to see whether it can get the same result as the case that column asjc is included in table scopus_paper.

if hint means run query in specified node(s) and concatenate the result from each node, I guess whether it is possible to add a new feature that will run sql type hint that route to multiple datanodes and process the result from each datanode as what ordinary SQL statement without hint does.

cloudfirst commented 3 years ago

that is the reason why I have to use hint instead of join. column asjc is not included in table scopus_paper for business reason.

then I try to use hint to see whether it can get the same result as the case that column asjc is included in table scopus_paper.

if hint means run query in specified node(s) and concatenate the result from each node, I guess whether it is possible to add a new feature that will run sql type hint that route to multiple datanodes and process the result from each datanode as what ordinary SQL statement without hint does.