查詢性能很大程度上依賴于硬體的性能，這裡暫不考慮硬體的影響。
原始語句：
EXPLAIN EXTENDED SELECT sql_no_cache
L_RETURNFLAG,
L_LINESTATUS,
SUM(L_QUANTITY) AS SUM_QTY,
SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE,
SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS SUM_DISC_PRICE,
SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX)) AS SUM_CHARGE,
AVG(L_QUANTITY) AS AVG_QTY,
AVG(L_EXTENDEDPRICE) AS AVG_PRICE,
AVG(L_DISCOUNT) AS AVG_DISC,
COUNT(*) AS COUNT_ORDER
FROM
LINEITEM
WHERE
L_SHIPDATE <= DATE'1998-12-01' - INTERVAL '90' DAY
GROUP BY
L_LINESTATUS
ORDER BY
L_LINESTATUS;
+----+-------------+----------+------+---------------+------+---------+------+---------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | LINEITEM | ALL | i_l_shipdate | NULL | NULL | NULL | 5959532 | 50.00 | Using where; Using temporary; Using filesort |
1 row in set, 1 warning (0.22 sec)
可以看到這裡做的是全表掃描。
查詢耗時4 rows in set (48.45 sec)
檢視各列在表中的唯一性：
mysql> select count(distinct L_SHIPDATE),count(distinct L_RETURNFLAG),count(distinct L_LINESTATUS),count(distinct L_QUANTITY),count(distinct L_EXTENDEDPRICE),count(distinct L_DISCOUNT),count(distinct L_TAX) from LINEITEM\G;
*************************** 1. row ***************************
count(distinct L_SHIPDATE): 2526
count(distinct L_RETURNFLAG): 3
count(distinct L_LINESTATUS): 2
count(distinct L_QUANTITY): 50
count(distinct L_EXTENDEDPRICE): 933900
count(distinct L_DISCOUNT): 11
count(distinct L_TAX): 9
1 row in set (26.26 sec)
增加覆寫索引：
mysql> alter table lineitem add index idx_merge(`l_shipDATE`,`l_returnflag`,`l_linestatus`,`l_extendedprice`,`l_quantity`,`l_discount`,`l_tax`);
檢視查詢執行計劃：
+----+-------------+----------+-------+------------------------+-----------+---------+------+---------+----------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | LINEITEM | range | i_l_shipdate,idx_merge | idx_merge | 4 | NULL | 2979766 | 100.00 | Using where; Using index; Using temporary; Using filesort |
1 row in set, 1 warning (0.01 sec)
可以考到使用了臨時表和檔案排序。
查詢耗時4 rows in set (18.01 sec)
更改索引列順序：
mysql> alter table lineitem drop index idx_merge,add index idx_merge(`l_returnflag`,`l_linestatus`,`l_shipDATE`,`l_extendedprice`,`l_quantity`,`l_discount`,`l_tax`);
檢視執行計劃：
+----+-------------+----------+-------+------------------------+-----------+---------+------+---------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | LINEITEM | index | i_l_shipdate,idx_merge | idx_merge | 44 | NULL | 5959532 | 50.00 | Using where; Using index |
1 row in set, 1 warning (0.10 sec)
消除了臨時表和檔案排序。
但是從影響的行數可以看出，做的又是全表掃描。
此時的查詢耗時：4 rows in set (15.07 sec)。比此前提升了3s左右。
通過對profile各個狀态的時間分析，主要瓶頸在cpu的處理時間上。
mysql> show profile for query 2;
+----------------------+-----------+
| Status | Duration |
| starting | 0.000129 |
| checking permissions | 0.000009 |
| Opening tables | 0.000018 |
| init | 0.000039 |
| System lock | 0.000015 |
| optimizing | 0.000011 |
| statistics | 0.000001 |
| preparing | 0.000041 |
| Sorting result | 0.000006 |
| executing | 0.000005 |
| Sending data | 15.066530 |
| end | 0.000062 |
| query end | 0.000026 |
| closing tables | 0.000022 |
| freeing items | 0.001095 |
| logging slow query | 0.000042 |
| cleaning up | 0.000022 |