使用索引掃描來進行排序

小亮520cl發表於2015-11-16
索引排序
1我們先回顧一下使用複合索引的幾個條件

點選(此處)摺疊或開啟

  1. 如果不是使用索引的最左列，則無法使用索引
  2. 不能跳過索引中的列，如select * from tab where a=** and c=***，則只能用到索引的第一列a
  3. 如果查詢中的莫個列有範圍查詢，則右邊的列都無法使用索引，如：select * from tab where a= and b> and c=  則索引只能用到a,b列


使用索引排序最好是滿足一下兩個條件
  1. 1.只有當索引的列順序與order by 子句的順序完全一致，並且所有列的排序方向（倒敘或者正序）都一樣時，mysql才能夠使用索引來對結果進行排序。

  3. 2.如果查詢需要關聯多張表的時候，則只有當order by子句引用的欄位全部為第一張表時，才能使用索引排序，order by子句和查詢型查詢的限制是一樣的：需要滿足索引的最左字首的要求，否則無法利用索引排序，有一種情況order by 子句可以不滿足索引的最左字首的要求，就是前導列為常量的時候，

  5. 修改：滿足的索引必須是執行計劃中的索引啊，，，

下面進行一些例子
  1. 背景：複合索引（a,b,c）
1.select * from tab where a=‘9878’ order by b；  ---可以，因為最左字首提供了常量，ab組合就形成了最左字首，explain檢視只使用了複合索引的（a)列，但是排序時用到了b列

2.select * from tab where a> order by a,b; ---可以，（a,b）滿足最左字首    ，，，explain檢視只使用了複合索引的（a)列，但是排序時用到了b列

下面這些就不行了
select * from tab where a='asad'  order by b desc,c asc;   ---排序不一樣

select * from tab where a='adad' order by b,d;             ----引用非索引欄位

select * from tab where a='adsad' order by c;              ----（a,c)組合跳過b列

select * from tab where a> order b,c;                      -----a是範圍查詢，索引只能用到a列

select * from tab where a= and b in () order by c;          -----b列範圍查詢，其右邊的欄位用不到了，只能用到 ab列

select * from tab where

一個面試題：


A：（c1,c2,c3,c4)
B:(c1,c2),排序用到c3
C:(c1)
D:(c1)  排序用到c2 c3
E:(c1,c2)  排序用到c3
題中的？如果代表的是非常量的意思，那麼我們就選B吧！


----這個理論上是可以使用索引進行關聯排序的，滿足第二個條件嘛：a既滿足來自第一張表，也滿足最左字首，但是最佳化器選擇時將table1表當作了第二張關聯表，所以實際上無法使用索引了！
select * from table1，table2 where table1.id=table2.id order by a;
 

下面舉一個生產上的例子

點選(此處)摺疊或開啟

  1. mysql> explain SELECT payment.id,payment.money,payment.c_time,payment.card_type,b_member_card.active_time,b_users.name,b_users.tel,zone.zone_name,admin.name as admin_name,admin.role_id,b_card_info.card_name as ccname FROM `payment` LEFT JOIN b_member_card ON b_member_card.payid=payment.id LEFT JOIN b_users ON payment.xdy_id=b_users.uid LEFT JOIN zone ON payment.zone_id=zone.zone_id LEFT JOIN admin ON payment.sale_id=admin.id LEFT JOIN b_card_info ON payment.vip_card = b_card_info.id WHERE ( `sale_id` IN ('39','110','159','311','349','353','362','432','449','450','489','501','533','562') ) AND ( `payed` = 2 ) ORDER BY payment.id desc LIMIT 0,10;
  2. +----+-------------+---------------+--------+---------------+---------------+---------+---------------------------------------+------+-----------------------------+
    | id | select_type | table         | type   | possible_keys | key           | key_len | ref                                   | rows | Extra                       |
    +----+-------------+---------------+--------+---------------+---------------+---------+---------------------------------------+------+-----------------------------+
    |  1 | SIMPLE      | payment       | range  | sale_id_payed | sale_id_payed | 5       | NULL                                  |   40 | Using where; Using filesort |   ---為什麼會出現filesort
    |  1 | SIMPLE      | b_member_card | ref    | payid         | payid         | 5       | interface_hd_com0624.payment.id       |    1 |                             |
    |  1 | SIMPLE      | b_users       | eq_ref | PRIMARY       | PRIMARY       | 4       | interface_hd_com0624.payment.xdy_id   |    1 |                             |
    |  1 | SIMPLE      | zone          | eq_ref | PRIMARY       | PRIMARY       | 3       | interface_hd_com0624.payment.zone_id  |    1 |                             |
    |  1 | SIMPLE      | admin         | eq_ref | PRIMARY       | PRIMARY       | 4       | interface_hd_com0624.payment.sale_id  |    1 |                             |
    |  1 | SIMPLE      | b_card_info   | eq_ref | PRIMARY       | PRIMARY       | 4       | interface_hd_com0624.payment.vip_card |    1 |                             |
    +----+-------------+---------------+--------+---------------+---------------+---------+---------------------------------------+------+-----------------------------+

  3. 6 rows in set (0.00 sec)
  4. 。
  5. 檢視索引
  6. mysql> show index from payment;
    +---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table   | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | payment |          0 | PRIMARY       |            1 | id          | A         |        3173 |     NULL | NULL   |      | BTREE      |         |               |
    | payment |          1 | sale_id_payed |            1 | sale_id     | A         |         137 |     NULL | NULL   |      | BTREE      |         |               |
    | payment |          1 | sale_id_payed |            2 | payed       | A         |         244 |     NULL | NULL   |      | BTREE      |         |               |
    +---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    3 rows in set (0.04 sec)
  7. 為什麼上面會出現filesort這種情況呢？id來之於執行計劃的第一張表，又是主鍵，完全滿足上面索引排序的要求啊，但是為啥沒用到索引排序呢？


我們更改一下sql語句
  1. mysql> explain SELECT payment.id,payment.money,payment.c_time,payment.card_type,b_member_card.active_time,b_users.name,b_users.tel,zone.zone_name,admin.name as admin_name,admin.role_id,b_card_info.card_name as ccname FROM `payment` LEFT JOIN b_member_card ON b_member_card.payid=payment.id LEFT JOIN b_users ON payment.xdy_id=b_users.uid LEFT JOIN zone ON payment.zone_id=zone.zone_id LEFT JOIN admin ON payment.sale_id=admin.id LEFT JOIN b_card_info ON payment.vip_card = b_card_info.id WHERE ( `sale_id` IN ('39','110','159','311','349','353','362','432','449','450','489','501','533','562') ) AND ( `payed` = 2 ) ORDER BY payment.payed desc LIMIT 0,10
  2. +----+-------------+---------------+--------+---------------+---------------+---------+---------------------------------------+------+-------------+
    | id | select_type | table         | type   | possible_keys | key           | key_len | ref                                   | rows | Extra       |
    +----+-------------+---------------+--------+---------------+---------------+---------+---------------------------------------+------+-------------+
    |  1 | SIMPLE      | payment       | range  | sale_id_payed | sale_id_payed | 5       | NULL                                  |   40 | Using where |    --排序條件改成payed就好了
    |  1 | SIMPLE      | b_member_card | ref    | payid         | payid         | 5       | interface_hd_com0624.payment.id       |    1 |             |
    |  1 | SIMPLE      | b_users       | eq_ref | PRIMARY       | PRIMARY       | 4       | interface_hd_com0624.payment.xdy_id   |    1 |             |
    |  1 | SIMPLE      | zone          | eq_ref | PRIMARY       | PRIMARY       | 3       | interface_hd_com0624.payment.zone_id  |    1 |             |
    |  1 | SIMPLE      | admin         | eq_ref | PRIMARY       | PRIMARY       | 4       | interface_hd_com0624.payment.sale_id  |    1 |             |
    |  1 | SIMPLE      | b_card_info   | eq_ref | PRIMARY       | PRIMARY       | 4       | interface_hd_com0624.payment.vip_card |    1 |             |
    +----+-------------+---------------+--------+---------------+---------------+---------+---------------------------------------+-----
觀察上面兩條sql你可以發現：要想使用索引排序你得滿足下面的條件
：在滿足最上面兩個條件的的基礎上
：附加：order by col的col還必須是滿足執行計劃中的用到的索引的最左字首條件

根據這些要求我們可以將sale_id_payed索引的範圍擴充一點至id  刪除原來的索引新建：create index sale_id_payed_id on payment(id,sale_id,payed);

mysql> explain SELECT payment.id,payment.money,payment.c_time,payment.card_type,b_member_card.active_time,b_users.name,b_users.tel,zone.zone_name,admin.name as admin_name,admin.role_id,b_card_info.card_name as ccname FROM `payment` LEFT JOIN b_member_card ON b_member_card.payid=payment.id LEFT JOIN b_users ON payment.xdy_id=b_users.uid LEFT JOIN zone ON payment.zone_id=zone.zone_id LEFT JOIN admin ON payment.sale_id=admin.id LEFT JOIN b_card_info ON  payment.vip_card = b_card_info.id WHERE ( `sale_id` IN ('39','110','159','311','349','353','362','432','449','450','489','501','533','562') ) AND ( `payed` = 2 ) ORDER BY payment.id desc LIMIT 0,10;

+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------+------+-------------+
| id | select_type | table         | type   | possible_keys | key     | key_len | ref                                   | rows | Extra       |
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------+------+-------------+
|  1 | SIMPLE      | payment       | index  | NULL          | PRIMARY | 4       | NULL                                  |   10 | Using where |    ---比上面的最佳化更好了一下，用到主鍵索引了
|  1 | SIMPLE      | b_member_card | ref    | payid         | payid   | 5       | interface_hd_com0624.payment.id       |    1 |             |
|  1 | SIMPLE      | b_users       | eq_ref | PRIMARY       | PRIMARY | 4       | interface_hd_com0624.payment.xdy_id   |    1 |             |
|  1 | SIMPLE      | zone          | eq_ref | PRIMARY       | PRIMARY | 3       | interface_hd_com0624.payment.zone_id  |    1 |             |
|  1 | SIMPLE      | admin         | eq_ref | PRIMARY       | PRIMARY | 4       | interface_hd_com0624.payment.sale_id  |    1 |             |
|  1 | SIMPLE      | b_card_info   | eq_ref | PRIMARY       | PRIMARY | 4       | interface_hd_com0624.payment.vip_card |    1 |             |
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------+------+-------------+
6 rows in set (0.00 sec)

現在返回去思考為什麼會出現filesort排序
filesort排序定義：表示 MySQL 會對結果使用一個外部索引排序,而不是從表裡按索引次序讀到相關內容。可能在記憶體或者磁碟上進行排序。MySQL 中無法利用索引完成的排序操作稱為“檔案排序（filesort）
高效能mysqlP222頁
mysql架構設計與最佳化155頁

  1. 1.如果order by子句中的col都是來自於第一張表（但不滿足索引排序的條件），那麼mysql在關聯處理第一個表時就會進行檔案的排序。那麼我們在explain的extra欄位中就會看到useing filesort
mysql> explain SELECT payment.id,payment.money,payment.c_time,payment.card_type,b_member_card.active_time,b_users.name,b_users.tel,zone.zone_name,admin.name as admin_name,admin.role_id,b_card_info.card_name as ccname FROM `payment` LEFT JOIN b_member_card ON b_member_card.payid=payment.id LEFT JOIN b_users ON payment.xdy_id=b_users.uid LEFT JOIN zone ON payment.zone_id=zone.zone_id LEFT JOIN admin ON payment.sale_id=admin.id LEFT JOIN b_card_info ON  payment.vip_card = b_card_info.id WHERE ( `sale_id` IN ('39','110','159','311','349','353','362','432','449','450','489','501','533','562') ) AND ( `payed` = 2 ) ORDER BY id,money  desc LIMIT 0,10;
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------+------+-----------------------------+
| id | select_type | table         | type   | possible_keys | key     | key_len | ref                                   | rows | Extra                       |
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------+------+-----------------------------+
|  1 | SIMPLE      | payment       | ALL    | NULL          | NULL    | NULL    | NULL                                  | 2965 | Using where; Using filesort |
|  1 | SIMPLE      | b_member_card | ref    | payid         | payid   | 5       | interface_hd_com0624.payment.id       |  309 |                             |
|  1 | SIMPLE      | b_users       | eq_ref | PRIMARY       | PRIMARY | 4       | interface_hd_com0624.payment.xdy_id   |    1 |                             |
|  1 | SIMPLE      | zone          | eq_ref | PRIMARY       | PRIMARY | 3       | interface_hd_com0624.payment.zone_id  |    1 |                             |
|  1 | SIMPLE      | admin         | eq_ref | PRIMARY       | PRIMARY | 4       | interface_hd_com0624.payment.sale_id  |    1 |                             |
|  1 | SIMPLE      | b_card_info   | eq_ref | PRIMARY       | PRIMARY | 4       | interface_hd_com0624.payment.vip_card |    1 |                             |
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------+------+-----------------------------+


2，除了上面的情況外，explain的extra欄位都會看到use tempory；using filesort的情況
mysql> explain SELECT payment.id,payment.money,payment.c_time,payment.card_type,b_member_card.active_time,b_users.name,b_users.tel,zone.zone_name,admin.name as admin_name,admin.role_id,b_card_info.card_name as ccname FROM `payment` LEFT JOIN b_member_card ON b_member_card.payid=payment.id LEFT JOIN b_users ON payment.xdy_id=b_users.uid LEFT JOIN zone ON payment.zone_id=zone.zone_id LEFT JOIN admin ON payment.sale_id=admin.id LEFT JOIN b_card_info ON  payment.vip_card = b_card_info.id WHERE ( `sale_id` IN ('39','110','159','311','349','353','362','432','449','450','489','501','533','562') ) AND ( `payed` = 2 ) ORDER BY id,money,b_users.name  desc LIMIT 0,10;
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------+------+----------------------------------------------+
| id | select_type | table         | type   | possible_keys | key     | key_len | ref                                   | rows | Extra                                        |
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | payment       | ALL    | NULL          | NULL    | NULL    | NULL                                  | 2965 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | b_member_card | ref    | payid         | payid   | 5       | interface_hd_com0624.payment.id       |  309 |                                              |
|  1 | SIMPLE      | b_users       | eq_ref | PRIMARY       | PRIMARY | 4       | interface_hd_com0624.payment.xdy_id   |    1 |                                              |
|  1 | SIMPLE      | zone          | eq_ref | PRIMARY       | PRIMARY | 3       | interface_hd_com0624.payment.zone_id  |    1 |                                              |
|  1 | SIMPLE      | admin         | eq_ref | PRIMARY       | PRIMARY | 4       | interface_hd_com0624.payment.sale_id  |    1 |                                              |
|  1 | SIMPLE      | b_card_info   | eq_ref | PRIMARY       | PRIMARY | 4       | interface_hd_com0624.payment.vip_card |    1 |                                              |
+----+-------------+---------------+--------+---------------+---------+---------+---------------------------------------+------+----------------------------------------------+
6 rows in set (0.00 sec)


關於groupby 排序可以檢視mysql效能調優與架構設計（P160）
排序條件和order by的條件一樣~~並且還必須滿足：使用group by的同時只能使用max 和min 兩個聚合函式


關於or的最佳化（用到了索引的全表掃描，雖然聯合索引兩個欄位都用到了，碰到這種還是分開寫成兩條語句吧，然後每個欄位建個索引）
MySQL [ngx_log]> explain select * from tt where age=3 or score=6;
+----+-------------+-------+-------+-------------------------+---------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys           | key           | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+-------------------------+---------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tt    | index | idx_age_score,idx_score | idx_age_score | 10      | NULL |   20 | Using where; Using index |
+----+-------------+-------+-------+-------------------------+---------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

有時會這樣（最佳化器進行了索引合併，這樣效果還是比較好的，聯合索引Uid_c_uid(只用到了uid欄位) 和c_uid這個索引）



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29096438/viewspace-1839593/，如需轉載，請註明出處，否則將追究法律責任。

相關文章