##
直方图优化案例
来自互联网的案例
1、优化器查询时间
mysql > SELECT COUNT(*) FROM goods_shops JOIN goods_characteristics USING (good_id)
-> WHERE size IN ('Lenovo', 'Dell', 'Toshiba', 'Samsung', 'Acer')
-> AND (location IN ('Moscow', 'Kiev')
-> OR delivery_options IN ('Premium', 'Urgent'));
+----------+
| COUNT(*) |
+----------+
| 816640 |
+----------+
1 row in set (11 min 34.85 sec)
2、执行计划
mysql > explain SELECT COUNT(*) FROM goods_shops JOIN goods_characteristics USING (good_id) WHERE size < 13 AND manufacturer IN ('Lenovo', 'Dell', 'Toshiba', 'Samsung', 'Acer') AND (location IN ('Moscow', 'Kiev') OR delivery_options IN ('Premium', 'Urgent'))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods_shops
partitions: NULL
type: index
possible_keys: good_id,location
key: good_id
key_len: 369
ref: NULL
rows: 65251
filtered: 0.20
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: goods_characteristics
partitions: NULL
type: ref
possible_keys: good_id,size
key: good_id
key_len: 123
ref: cookbook.goods_shops.good_id
rows: 126262
filtered: 16.02
Extra: Using where; Using index
2 rows in set, 1 warning (0.05 sec)
3、创建直方图
mysql > ANALYZE TABLE goods_shops UPDATE HISTOGRAM ON location, delivery_options\G
*************************** 1. row ***************************
Table: cookbook.goods_shops
Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'delivery_options'.
*************************** 2. row ***************************
Table: cookbook.goods_shops
Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'location'.
2 rows in set (8.42 sec)
4、测试
mysql > SELECT COUNT(*) FROM goods_shops JOIN goods_characteristics USING (good_id) WHERE size < 13 AND manufacturer IN ('Lenovo', 'Dell', 'Toshiba', 'Samsung', 'Acer') AND (location IN ('Moscow', 'Kiev') OR delivery_options IN ('Premium', 'Urgent'));
+----------+
| COUNT(*) |
+----------+
| 816640 |
+----------+
1 row in set (2.61 sec)
