今天去滴滴面試被建議換行業了，如下描述，索引後為什麼會出現如下情況了？

01-14

select * from test where `name`="a" and sex="1" order by score desc limit 100，就是這個問題，資料庫是Mysql innodb，問大數據量(千萬級) 如何加索引的問題，我回答說如果name結果集非常小的話使用name作為索引，結果集比較大的話加name、score 的聯合索引上，name結果級非常大的話索引加在score上就行。
結果面試官很明確的告訴我name、score不會走索引，索引一定要加name、sex、score 聯合索引才能保證用到索引，並建議我換行業，然後問我B+樹了解么，我說比較了解，然後就從B+樹上說了一堆為啥name、score不會走索引的原因。我問他，您確定name、score不會起作用？人家給一個鄙視~
回家測試結果：

數據量300w，未加任何索引執行時間1.5s

1 SIMPLE test ALL 3075753 Using where; Using filesort
索引加在name、sex上，執行時間5.83s
1 SIMPLE test ref name name 768 const 984468 Using where; Using filesort
索引加在name、score上，執行時間&<0.00s
1 SIMPLE test ref name name 768 const 1009528 Using where
索引加在name、sex、score，執行時間&<0.00s
1 SIMPLE test ref name name 1536 const,const 1265790 Using where; Using index
索引加在score上，執行時間&<0.00s
1 SIMPLE test index name 5 10 Using where

問題1：
為什麼只在name、sex上加索引比不加任何索引還慢？
問題2：
name、score起作用了，機制是什麼呢？

執行的時間和數據分布有關係、也和數據是否在緩存中有關（內存遠遠快於磁碟）

問題在order by score desc上面，若索引中不包含score，在走了二級索引後，需要去主鍵索引拿score欄位用於排序

可能你構造的表欄位較少，主鍵索引和二級索引可能差不多大了，在where條件命中的數據非常多的情況下，直接走主鍵索引有可能比走主鍵索引+二級索引快，當然這也有可能和數據是否在緩存中有關

這道題的答案是（name, sex, score）或者是（name, score），因為返回了所有欄位，走主鍵拿數據是無法避免的，而sex欄位本身過濾數據效果並不明顯（只有少數取值），如果只是查count(*)，那麼（name, sex, score）是最佳答案

mysql目前不支持降序索引，後面8.0支持的話，性能還可以更高

謝@劉獃獃邀

你沒能進入這個公司，應該也算是幸運的。

我們也不用把一個陌生人的一句武斷評價太放在心上。

這個題目的信息給的也不太全，從你的過程中，你的列數應該是很少。

我來給一個列數多的。

這是一個不知道什麼時候搞的表：

Server version: 5.7.17-debug-log Source distribution


mysql&> desc insert_data;

+-----------------+---------------------+------+-----+---------+-------+

| Field           | Type                | Null | Key | Default | Extra |

+-----------------+---------------------+------+-----+---------+-------+

| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |

| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |

| TABLE_NAME      | varchar(64)         | NO   |     |         |       |

| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |

| ENGINE          | varchar(64)         | YES  |     | NULL    |       |

| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |

| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |

| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |

| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  | MUL | NULL    |       |

| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |

| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |

| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |

| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |

| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |

| CREATE_TIME     | datetime            | YES  |     | NULL    |       |

| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |

| CHECK_TIME      | datetime            | YES  |     | NULL    |       |

| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |

| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |

| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |

| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |

| aaa             | int(11)             | YES  |     | NULL    |       |

| bbb             | int(11)             | YES  |     | NULL    |       |

| ccc             | int(11)             | YES  |     | NULL    |       |

| ddd             | int(11)             | YES  |     | NULL    |       |

| eee             | int(11)             | YES  |     | NULL    |       |

| ff              | int(11)             | YES  |     | NULL    |       |

| fff             | int(11)             | YES  |     | NULL    |       |

| ffff            | int(11)             | YES  |     | NULL    |       |

| eeee            | int(11)             | YES  |     | NULL    |       |

+-----------------+---------------------+------+-----+---------+-------+

30 rows in set (0.00 sec)
mysql&>  select count(*) from insert_data;

+----------+

| count(*) |

+----------+

|   614400 |

+----------+

1 row in set (8.20 sec)
mysql&> select count(distinct TABLE_NAME)  from insert_data;

+----------------------------+

| count(distinct TABLE_NAME) |

+----------------------------+

|                        288 |

+----------------------------+

1 row in set (11.80 sec)
mysql&> select TABLE_TYPE,count(*) from insert_data group by TABLE_TYPE;

+-------------+----------+

| TABLE_TYPE  | count(*) |

+-------------+----------+

| BASE TABLE  |   284672 |

| SYSTEM VIEW |   124928 |

| VIEW        |   204800 |

+-------------+----------+

3 rows in set (14.50 sec)

mysql&> select count(distinct DATA_LENGTH) from insert_data; +-----------------------------+ | count(distinct DATA_LENGTH) | +-----------------------------+ | 14 | +-----------------------------+ 1 row in set (10.26 sec)


mysql&> select * from insert_data where table_name="test" and TABLE_TYPE="BASE TABLE"  order by DATA_LENGTH desc limit 10;

10 rows in set (10.66 sec)

全表掃描，一行一行的來，這個沒什麼可說的。

mysql&> create index idx_table_name on insert_data(table_name); Query OK, 0 rows affected (27.56 sec) Records: 0 Duplicates: 0 Warnings: 0


mysql&> explain select * from insert_data where table_name="test" and TABLE_TYPE="BASE TABLE"  order by DATA_LENGTH desc limit 10;

+----+-------------+-------------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------------------------+

| id | select_type | table       | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra                                              |

+----+-------------+-------------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------------------------+

|  1 | SIMPLE      | insert_data | NULL       | ref  | idx_table_name | idx_table_name | 194     | const | 8192 |    10.00 | Using index condition; Using where; Using filesort |

+----+-------------+-------------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------------------------+

1 row in set, 1 warning (0.00 sec)
mysql&> select * from insert_data where table_name="test" and TABLE_TYPE="BASE TABLE"  order by DATA_LENGTH desc limit 10;

10 rows in set (1.34 sec)

從上面可以看出來，這裡也是使用了索引，先走table_name的索引得到行，然後過濾table_type後，得到的結果再進行排序。

mysql&> alter table insert_data drop index idx_table_name; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0


mysql&> create index idx_table_name_table_type  on insert_data(table_name,table_type);

Query OK, 0 rows affected (31.63 sec)

Records: 0  Duplicates: 0  Warnings: 0
mysql&> explain select * from insert_data where table_name="test" and TABLE_TYPE="BASE TABLE"  order by DATA_LENGTH desc limit 10;

+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+-------+----------+---------------------------------------+

| id | select_type | table       | partitions | type | possible_keys             | key                       | key_len | ref         | rows  | filtered | Extra                                 |

+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+-------+----------+---------------------------------------+

|  1 | SIMPLE      | insert_data | NULL       | ref  | idx_table_name_table_type | idx_table_name_table_type | 388     | const,const | 15160 |   100.00 | Using index condition; Using filesort |

+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+-------+----------+---------------------------------------+

1 row in set, 1 warning (0.00 sec)

mysql&> select * from insert_data where table_name="test" and TABLE_TYPE="BASE TABLE" order by DATA_LENGTH desc limit 10; 10 rows in set (1.21 sec)

從上面可以看出來，這裡也是使用了索引，先走table_name和table_type索引後，得到的結果再進行排序。

mysql&> alter table insert_data drop index idx_table_name_table_type; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0


mysql&> create index idx_table_name_DATA_LENGTH  on insert_data(table_name,DATA_LENGTH);

Query OK, 0 rows affected (30.10 sec)

Records: 0  Duplicates: 0  Warnings: 0
mysql&> explain select * from insert_data where table_name="test" and TABLE_TYPE="BASE TABLE"  order by DATA_LENGTH desc limit 10;

+----+-------------+-------------+------------+------+----------------------------+----------------------------+---------+-------+-------+----------+-------------+

| id | select_type | table       | partitions | type | possible_keys              | key                        | key_len | ref   | rows  | filtered | Extra       |

+----+-------------+-------------+------------+------+----------------------------+----------------------------+---------+-------+-------+----------+-------------+

|  1 | SIMPLE      | insert_data | NULL       | ref  | idx_table_name_DATA_LENGTH | idx_table_name_DATA_LENGTH | 194     | const | 15072 |    10.00 | Using where |

+----+-------------+-------------+------------+------+----------------------------+----------------------------+---------+-------+-------+----------+-------------+

1 row in set, 1 warning (0.01 sec)
mysql&>  select * from insert_data where table_name="test" and TABLE_TYPE="BASE TABLE"  order by DATA_LENGTH desc limit 10;

10 rows in set (0.00 sec)

這裡有一些疑問：

1 從key_len的長度上可以看出，是只走了table_name列的索引，也就是前綴索引

2 如果只是走了key_len的索引的話，那還是要有一個排序的操作，但卻沒有，通過查看show stats like "%sort%";也可以看出是沒有執行sort的(對於table_name單列索引，是可以看到有sort值的)

這個疑問，課下我再分析下。

mysql&> alter table insert_data drop index idx_table_name_DATA_LENGTH; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0


mysql&> create index idx_table_name_table_type_DATA_LENGTH  on insert_data(table_name,TABLE_TYPE,DATA_LENGTH);

Query OK, 0 rows affected (34.62 sec)

Records: 0  Duplicates: 0  Warnings: 0
mysql&> explain select * from insert_data where table_name="test" and TABLE_TYPE="BASE TABLE"  order by DATA_LENGTH desc limit 10;

+----+-------------+-------------+------------+------+---------------------------------------+---------------------------------------+---------+-------------+-------+----------+-------------+

| id | select_type | table       | partitions | type | possible_keys                         | key                                   | key_len | ref         | rows  | filtered | Extra       |

+----+-------------+-------------+------------+------+---------------------------------------+---------------------------------------+---------+-------------+-------+----------+-------------+

|  1 | SIMPLE      | insert_data | NULL       | ref  | idx_table_name_table_type_DATA_LENGTH | idx_table_name_table_type_DATA_LENGTH | 388     | const,const | 15744 |   100.00 | Using where |

+----+-------------+-------------+------------+------+---------------------------------------+---------------------------------------+---------+-------------+-------+----------+-------------+

1 row in set, 1 warning (0.01 sec)
mysql&>  select * from insert_data where table_name="test" and TABLE_TYPE="BASE TABLE"  order by DATA_LENGTH desc limit 10;

10 rows in set (0.00 sec)

這一個和上一種情況類似。

所以，加一個三列的索引索引是可以的， name,score也是可以用到索引的。

面試官是半個水貨。

不過三個聯合的做法的兼容性相對其他做法要好，因為要兼容多種數據分布。

索引選擇列關鍵是：這個列增加後選擇性有沒有提高。

索引加不加新的候選列取決於，新的候選列（或組合）的選擇性（對應有個名詞叫：選擇率），如果name欄位都是同一個值，你可以測試一下三個欄位聯合的性能如何。數據分布情況顯然會很明顯的影響索引該怎麼建立，所以不討論數據分布，就憑什麼B+索引結構談索引用那些欄位是盲人摸象，是方向性的錯誤。

當有order by 的時候，就要考慮，order by 是要去data block讀，如果索引匹配的數量很大，這個讀data block的代價會很高，且是單塊讀，就需要加入索引不是做access 而是做數據處理。

所以：當索引的選擇率，非常高，不許要加入 score，低的話這個列要加入索引。

先回答問題2吧，對於可以用索引避免文件排序的情況，MySQL會盡量選擇避免排序。

具體的選擇索引的方式是在explain中計算cost，並選擇開銷最小的索引，而排序的cost很高（5.7可以動態設置不同行為的cost了）

所以，有排序列就要去用，盡量避免文件排序。

直接3個欄位加聯合索引就行了，這樣怎麼查都能走索引。

"如果name結果集非常小的話使用name作為索引"錯了，取值越小越不能加。sex最不能加。

但面試官讓你換行，是他的問題，他情商太低了。

um？不是應該結果集越大越適合做索引嗎？比如name啊什麼的，sex必然是不適合。

至於勸換行，那是因為面試官他自己太弱罷了。我見過厲害的大牛都是鼓勵別人的，只有自己半吊子，眼界太窄，才會自以為是，看不起別人。

yselect * from test where `name`="a" and sex="1" order by score desc limit 100；

題主和面試官都還沒真正了解索引原理。了解的話任何語句看一眼基本就知道怎麼創建索引了。limit 先不考慮，假如表有很多列，且表已經有主鍵聚集索引。

簡單來說，就如知乎或百度搜索一樣，輸入第一個字，會提示第一個字開頭的相關信息；輸入第二個字，則這兩字開頭的信息會提示出來；輸入更多雷同……所以索引的第一個欄位很重要，盡量能篩選更少。若是聯合索引，接下來就是第二個欄位了，更多聯合索引雷同。

對於上面的語句優化，如果表5000萬紀錄，不管where條件下返回的是幾行、還是三四千萬行，建索引的思路是一樣的（不必考慮是否掃描全表而放棄索引）。因為結果返回所有列，所以查詢都不可能只訪問非聚集索引的B-tree結構，必然在非聚集索引得到的結果再查聚集索引。

不多廢話了，只對上面的查詢來說，正確的是應該創建一個非聚集索引（name,sex,score）。索引是安順序搜索btree的，查詢引擎分析的執行計劃會安排該索引查詢，先篩選where條件的，得到的結果集再按score排序，而排序已經在索引中是按順序了，省下了排序的開銷，最後直接limit（當然還要查聚集索引）。

若創建索引（name,score），同樣也可以用到索引，但只用name這一列，後面的沒用到。如 name="a" 篩選出很少紀錄（如幾千行或更少），那還是很有用的，但比不上索引（name,sex,score）！但因查詢所有列，這些索引都還不是最好的。

補充：

上面一段話，針對where條件：只用name這一列，後面的沒用到。索引（name，score）中，查詢語句中如果score為排序欄位，這樣的索引也是有用的，name的篩選及score排序已在索引結構中，sex即使篩選，也不影響score在索引中的排序，最終查詢結果也省去了排序開銷。

# 換行的話，回車鍵就行了。

你這個問題，看上去是一個問題，其實是多個，不能一概而論。where和orderby利用欄位不一樣。

資料庫帶orderby的時候做兩部動作。第一根據where條件獲取結果集，第二根據orderby排序，如果在第一步的執行計劃當中已經有了第二部的orderby欄位的索引了，那麼當然借過來用所以更快了。

所以如果你的索引只在name and／or sex上，這時候，orderby一定是走了一次外部排序，所以整體會慢。但是其實這時候，如果你有單獨name 索引，或者name+sex，是會快點的，理論上來說。這就是你的只有name 和sex的時候有Using filesort的原因。

如果你的索引包含了score，那麼orderby這裡的外部排序沒有了，name+sex+sorce的情況容易理解，全表聯合索引了，資料庫自己不抽風，基本上using index，你既然全表數據都在索引裡面了，當然資料庫直接去索引取數據了。name+score的時候是using where，應該是數據命中率的問題導致資料庫放棄了。

我覺得面試官說的沒有錯，要確保用到索引，那麼三個欄位的聯合索引是穩妥的，而name+socre的方法，在數據分布不同的情況下，資料庫會自行判斷。

現在面試都搞這麼複雜啊。。。。

這都屬於查查就能會的問題，還建議轉行了？這面試官可真搞笑，不知天高地厚。

這位面試官想問你的是innodb索引問題，見你直接回答，再問btree，說明還是想看看你的理論水平。現在拿著別人經驗或者百度結果來禍禍的太多了。

大膽假設下：正常情況誰都知道面試讓人轉行這種話不能隨便說的，無非你給人一種自我感覺良好還不願沉下心搞技術的印象。

另外：這樣的問題，上知乎來尋一句公道么？

祝好運。

sex的取值範圍太小，所以不應該在上面加索引，我覺得name區分度高的話加在name上就可以了呀，不懂為什麼面試官說要3個欄位聯合索引。

可以explain試試。

讓sex入選索引欄位，如果沒有什麼精妙處理，我建議這種水平就不要說會sql了~~~

1.全表掃描沒有索引計算，在where條件篩出的重複數據多的時候效果明顯。索引是針對少量數據檢索速度快。但要計算找位置。

我猜題主這個例子是這樣的，先從索引name中找到一大堆所有符合條件的主鍵，再去一條條去主鍵里找搜記錄，這掃n次300w的主鍵還是要算一算的。反而就不如直接全表讀一遍快了。

2.mysql應該很聰明了，先撈出能撈的排好序的行，然後從第一條開始掃sex符合條件的就直接顯示出來，到100就停。和最後一個只加排序列索引也很快是一樣的原理

我猜的。。。。。。。

(name, score) 這個索引，在查詢階段使用name 做索引，在排序階段，由於 score 已經是排好序的，所以也免除了排序。所以整個過程就是將 name="a" 的記錄遍歷一遍取出 sex="1" 的記錄。

(name, sex) 這個索引之所以慢，應該是滿足條件的記錄非常多，需要對 score 欄位排序造成的。

你的回答沒毛病啊，不過有點學院派。就是在name和score加索引啊，這個sql有問題，sex應該是int類型，你用字元型，如果sex加索引，那麼sex上的索引會失效的

不是走不走索引的問題，而是如何高效分頁的問題。

循著索引取到一百個記錄並停止，不需要額外排序，不需要訪問不相關數據，這就是聯合索引的目的。

不用理那個面試官......

你的問題:

1. 走索引更慢,

可能是緩存的原因:

內存/磁碟的速度和讀內存, 讀磁碟存取花費的操作

也有可能是數據分布, name佔據了總體行數的大多數:

這樣子會導致通過name,sex過濾出來的數據可能佔據了總行數的50%或者更多, 本身在取數據方面就沒什麼優勢, 還要根據主鍵去回表, 找到score的值之後才能排序, 可能全表掃描的隨機讀不比會標多多少的;

2. name,socre有效

explain已經顯示的很明確了, 沒有file sorting, 而且感覺也很難構造出name, score用不上的場景....

至於換行業, 換吧, 去滴滴幹什麼嘛.....

PS: limit有可能會讓MySQL的查詢優化器變更查詢計劃, 可以去掉再試試;

碰到講這種話的面試官，不去也罷

面試版的：你退群吧。。。

可以把簡歷發出來看看。估計是面試官不想要你罷了。