ITPub博客

首页 > 数据库 > MySQL > 使用索引扫描来进行排序

使用索引扫描来进行排序

原创 MySQL 作者:小亮520cl 时间:2015-11-16 14:49:43 0 删除 编辑
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才能够使用索引来对结果进行排序。

  2. 2.如果查询需要关联多张表的时候,则只有当order by子句引用的字段全部为第一张表时,才能使用索引排序,order by子句和查找型查询的限制是一样的:需要满足索引的最左前缀的要求,否则无法利用索引排序,有一种情况order by 子句可以不满足索引的最左前缀的要求,就是前导列为常量的时候,

  3. 修改:满足的索引必须是执行计划中的索引啊,,,

下面进行一些例子
  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. 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)
  6. 为什么上面会出现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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
毕业以后专业任职数据库工程师职位,itpub一直作为自己的笔记记录的地方,blog写的不详细,草书笔记,仅供参考!

注册时间:2013-09-12

  • 博文量
    530
  • 访问量
    971246