|
可以看到，查询耗费了2.8秒阁下。可是，查询优化器不知道s_gmt_offset列只有一个差异的值。没有统计数据的环境下，优化器会用所谓的“hard-coded guesstimates”,会假设10%的数据切合前提“ca_gmt_offset = -5“。假如在这个列上增进一个直方图，优化器会知道全部的数据都切合前提，因此会走一个更好的执行打算。
- mysql> ANALYZE TABLE store UPDATE HISTOGRAM ON s_gmt_offset WITH 8 BUCKETS;
- +-------------+-----------+----------+---------------------------------------------------------+
- | Table | Op | Msg_type | Msg_text |
- +-------------+-----------+----------+---------------------------------------------------------+
- | tpcds.store | histogram | status | Histogram statistics created for column 's_gmt_offset'. |
- +-------------+-----------+----------+---------------------------------------------------------+
- 1 row in set (0.06 sec)
- mysql> SELECT ...
- +------------+------------+--------------------------------------------------------------------------+
- | promotions | total | CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100 |
- +------------+------------+--------------------------------------------------------------------------+
- | 3213210.07 | 5966836.78 | 53.85114741 |
- +------------+------------+--------------------------------------------------------------------------+
- 1 row in set (1.37 sec)
（编辑：湖南网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
