一英融、表結(jié)構(gòu)
CREATE TABLE `cd_happy_for_ni_deals` (
`id` int(11) NOT NULL DEFAULT '0',
`update_time` datetime DEFAULT NULL COMMENT '更新時間',
`publish_status` int(11) NOT NULL DEFAULT '4' COMMENT '發(fā)布狀態(tài)',
KEY `idx_of_publish_status_update_time` (`publish_status`,`update_time`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
二铃彰、唯一性基數(shù)
mysql> select count(distinct(update_time)) from cd_happy_for_ni_deals;
+------------------------------+
| count(distinct(update_time)) |
+------------------------------+
| 1845933 |
+------------------------------+
1 row in set (4.68 sec)
mysql> select count(distinct(publish_status)) from cd_happy_for_ni_deals;
+---------------------------------+
| count(distinct(publish_status)) |
+---------------------------------+
| 2 |
+---------------------------------+
1 row in set (1.76 sec)
mysql> select count(id) from cd_happy_for_ni_deals;
+-----------+
| count(id) |
+-----------+
| 1907609 |
+-----------+
1 row in set (0.00 sec)
update_time 的選擇性：1845933 / 1907609.to_f = 0.9676684268107353 接近1
publish_status 的選擇性： 2 / 1907609.to_f = 1.0484328811617055e-06 接近0
三绍豁、建立(a,b) 索引，分別根據(jù) a 查詢牙捉，b 查詢竹揍，(a,b) 查詢，(b,a) 查詢鹃共，統(tǒng)計結(jié)果
不走尋常路鬼佣，我就偏選擇 選擇性低的做索引的第一位。
創(chuàng)建索引
mysql> alter table cd_happy_for_ni_deals add index `idx_of_publish_status_update_time` (`publish_status`, `update_time`, `id`);
Query OK, 0 rows affected (14.69 sec)
Records: 0 Duplicates: 0 Warnings: 0
根據(jù)a 查詢
mysql> explain select SQL_NO_CACHE id, publish_status from cd_happy_for_ni_deals where publish_status = 4 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: cd_happy_for_ni_deals
type: ref
possible_keys: idx_of_publish_status_update_time
key: idx_of_publish_status_update_time
key_len: 4
ref: const
rows: 964056 <- 只查詢publish_status 的情況
Extra: Using index
1 row in set (0.00 sec)
平均查詢時間：
mysql> select SQL_NO_CACHE count(id) from cd_happy_for_ni_deals where publish_status = 4 \G;
*************************** 1. row ***************************
count(id): 1858081
1 row in set (0.69 sec)
理論上可以用到索引(a,b) 中的 a 部分霜浴。
根據(jù)b 查詢
mysql> explain select SQL_NO_CACHE id, publish_status from cd_happy_for_ni_deals where update_time = '2014-05-17 23:00:48' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: cd_happy_for_ni_deals
type: index
possible_keys: NULL
key: idx_of_publish_status_update_time
key_len: 17
ref: NULL
rows: 1928113 <- 只查詢update_time 的情況
Extra: Using where; Using index
1 row in set (0.01 sec)
平均查詢時間：
mysql> select SQL_NO_CACHE count(id) from cd_happy_for_ni_deals where update_time = '2014-05-17 23:00:48' \G;
*************************** 1. row ***************************
count(id): 1
1 row in set (1.06 sec)
查詢b 的時候晶衷，理論上用不到索引的。為啥這里阴孟？晌纫？？
根據(jù)(a,b) 查詢
mysql> explain select SQL_NO_CACHE id, publish_status from cd_happy_for_ni_deals where publish_status = 4 and update_time = '2014-05-17 23:00:48' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: cd_happy_for_ni_deals
type: ref
possible_keys: idx_of_publish_status_update_time
key: idx_of_publish_status_update_time
key_len: 13
ref: const,const
rows: 1
Extra: Using where; Using index
1 row in set (0.01 sec)
平均查詢時間：
mysql> select SQL_NO_CACHE count(id) from cd_happy_for_ni_deals where publish_status = 4 and update_time = '2014-05-17 23:00:48' \G;
*************************** 1. row ***************************
count(id): 1
1 row in set (0.00 sec)
符合理論上的預(yù)期永丝。
根據(jù)(b,a) 查詢
mysql> explain select SQL_NO_CACHE id, publish_status from cd_happy_for_ni_deals where update_time = '2014-05-17 23:00:48' and publish_status = 4 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: cd_happy_for_ni_deals
type: ref
possible_keys: idx_of_publish_status_update_time
key: idx_of_publish_status_update_time
key_len: 13
ref: const,const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)
平均查詢時間：
mysql> select SQL_NO_CACHE count(id) from cd_happy_for_ni_deals where update_time = '2014-05-17 23:00:48' and publish_status = 4 \G;
*************************** 1. row ***************************
count(id): 1
1 row in set (0.00 sec)
理論上锹漱，這里只能用到（a,b）中的a部分，為啥也這么快慕嚷？哥牍？
結(jié)論：
1、理論上索引對順序是敏感的喝检，但是由于MySQL的查詢優(yōu)化器會自動調(diào)整where子句的條件順序以使用適合的索引嗅辣。
2、將選擇性高的列放在索引的最前列挠说。根據(jù)場景的不同澡谭，這條經(jīng)驗法則并不是完全準(zhǔn)確的。在某些場景下损俭，可能需要根據(jù)運行頻率最高的查詢來調(diào)整索引列的順序蛙奖。
參考
