* 【TiDB 版本】:
3.0.11
* 【问题描述】:
TiDB 超低配置,联表查询,性能测试
前置条件
192.168.100.45 4C 16G
192.168.100.46 4C 16G
192.168.100.47 4C 16G
192.168.100.48 4C 16G
TiDB、PD、TiKV 三台机器共用
数据范围 2013年01月01日 至 2020年01月01日
表1 dc_flowdata_deliver_sale sale |
数据量 |
---|---|
数据量(总): | 43397632 |
范围内数据量('2014-01-01' AND '2014-12-31' ): |
864564 |
表2 dc_flowdata_deliver_sale_index sale |
数据量 |
---|---|
数据量(总): | 43397632 |
范围内数据量('2014-01-01' AND '2014-12-31' ): |
864564 |
表3 dc_flowdata_deliver_sale_jion_index sale |
数据量 |
---|---|
数据量(总): | 43397632 |
范围内数据量('2014-01-01' AND '2014-12-31' ): |
864564 |
表4 dc_flowdata_deliver_sale_range sale |
数据量 |
---|---|
数据量(总): | 43397632 |
范围内数据量('2014-01-01' AND '2014-12-31' ): |
864564 |
关联表 dc_product_master prod |
数据量 |
---|---|
数据量(总): | 117 |
数据库总数据量
: 173,590,528
1. 测试未进行表分区情况下,联表查询效率; 未添加索引
SELECT
COUNT(*)
FROM
dc_flowdata_deliver_sale sale
INNER JOIN dc_product_master prod ON sale.normal_product_code = prod.product_code
WHERE
sale.seller_date BETWEEN '2014-01-01' AND '2014-12-31';
+----------+
| COUNT(*) |
+----------+
| 780823 |
+----------+
1 row in set
Time: 34.537s
2. 测试未进行表分区情况下,联表查询效率; 添加单独索引
KEY seller_date
(seller_date
),
KEY normal_product_code
(normal_product_code
)
SELECT
COUNT(*)
FROM
dc_flowdata_deliver_sale_index sale
INNER JOIN dc_product_master prod ON sale.normal_product_code = prod.product_code
WHERE
sale.seller_date BETWEEN '2014-01-01' AND '2014-12-31';
+----------+
| COUNT(*) |
+----------+
| 780823 |
+----------+
1 row in set
Time: 366.416s
3. 测试未进行表分区情况下,联表查询效率; 添加联合索引
KEY seller_date
(seller_date
,normal_product_code
)
SELECT
COUNT(*)
FROM
dc_flowdata_deliver_sale_jion_index sale
INNER JOIN dc_product_master prod ON sale.normal_product_code = prod.product_code
WHERE
sale.seller_date BETWEEN '2014-01-01' AND '2014-12-31';
+----------+
| COUNT(*) |
+----------+
| 780823 |
+----------+
1 row in set
Time: 0.759s
4. 表分区情况下,联表查询效率;
SELECT
COUNT(*)
FROM
dc_flowdata_deliver_sale_range sale
INNER JOIN dc_product_master prod ON sale.normal_product_code = prod.product_code
WHERE
sale.seller_date BETWEEN '2014-01-01' AND '2014-12-31';
+----------+
| COUNT(*) |
+----------+
| 780823 |
+----------+
1 row in set
Time: 5.178s