[GitHub] [incubator-shardingsphere] fdlzp opened a new issue #2624: How should we create index after using sharding-jdbc？

[GitBox <gi...@apache.org>]

fdlzp opened a new issue #2624: How should we create index after using sharding-jdbc？
URL: https://github.com/apache/incubator-shardingsphere/issues/2624
 
 
   
   **Description：**
   
   Query becomes more slowly after create **index**.（sharding VS nosharing） 
   
   **My environment：**
   
   springboot + JPA + MySQL+sharding-jbdc VS springboot + JPA + MySQL（control group）
   
   mysql：8.0.16
   
   **Which version of ShardingSphere did you use?**
   
   4.0.0-RC1
   
   **Which project did you use? Sharding-JDBC or Sharding-Proxy?**
   
   Sharding-JDBC
   
   **Data Node Configuration：**
   
   spring.shardingsphere.sharding.tables.t_basic_up_msg.actual-data-nodes=
   sharding-test.t_basic_up_msg_20190${1..9}${1..2},sharding-test.t_basic_up_msg_2019${10..12}${1..2}
   
   Sharding_test（the amount of data：20million）
   ├── t_basic_up_msg_201901_1
   ├── t_basic_up_msg_201901_2
   ..........
   ├── t_basic_up_msg_201911_1
   ├── t_basic_up_msg_201911_2
   ├── t_basic_up_msg_201912_1
   ├── t_basic_up_msg_201912_2
   
   （There are 24 tables in total. 1 table each half month）
   
   VS
   
   （database of control group）
   Sharding_test1（another database，nosharding，the amount of data：20million）
   ├── t_basic_up_msg_
   
   **Entity**
   ![图片](https://user-images.githubusercontent.com/44960833/60333494-cbe98380-99cb-11e9-82e2-118ace509cc0.png)
   
   
   ### Sharding Key
   
   receipt_time **(type :datetime)**
   
   type of primary key：UUID
   
   ### create index sql
   **sharidng :**
   `ALTER TABLE t_basic_up_msg_201901_1 ADD INDEX idx_upmsg011_receiptTime(RECEIPT_TIME); ALTER TABLE t_basic_up_msg_201901_2 ADD INDEX idx_upmsg012_receiptTime(RECEIPT_TIME); ....... 
   ALTER TABLE t_basic_up_msg_201912_1 ADD INDEX idx_upmsg121_receiptTime(RECEIPT_TIME); ALTER TABLE t_basic_up_msg_201912_2 ADD INDEX idx_upmsg122_receiptTime(RECEIPT_TIME);`
   **nosharding:**
   `ALTER TABLE t_basic_up_msg ADD INDEX idx_upmsg_receiptTime(receipt_time);`
   
   ### query sql
   `select basic_up_msg_id , basic_up_msg_no ,cust_sender_ic,data_source, data_type, forward_number, hex_sms,is_del,palt_receive_ic, process_status, process_time , project_id , receipt_time from t_basic_up_msg where receipt_time='2019-01-01 00:04:10.0'`
   
   ### Expected behavior
   
   query more quickly
   
   ### Actual behavior
   
   query more slowly
   insert more quickly
   
   ### Reason analyze
   not clear
   the type of sharding key and my index is datetime
   
   **My expection:**
   It would be better if you can give me some suggestions.
   
   Thank you!

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services