Mysql 关于 order by limit 的一个问题

1、建表语句：

 CREATE TABLE `prepay_settle_bill_index` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增Id',
  `biz_sys` tinyint(4) NOT NULL COMMENT '系统类型：1=hms、2=qta、3=团购、4=OTAGH',
  `bill_status` tinyint(2) NOT NULL DEFAULT '1' COMMENT '',
  .....fields
  `settle_period` date NOT NULL DEFAULT '0000-00-00' COMMENT '结算账期',
  `create_ts` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_settle_period` (`settle_period`),
  KEY `idx_create_ts` (`create_ts`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='预付结算单索引表'

表数据量80w

Sql1现象说明：

用到了Idx_settle_period索引，进行索引排序，然后索引做limit分页 ,检索行数447行

满足记录行为0时，查询一页时间为 1.73秒

满足记录行为174行时，limit 1,50时间为 0.03秒

limit 0,200 时间为 1.71秒

将Limit条件去除之后，用到索引id_create_ts , 检索行数70890 ,内存排序, 时间 0.06秒

Mysql order by column limit row_count 上述情况造成原因说明：

1、如果column上面没有索引，那么将会有文件排序，即将满足Where条件的All Result Row做FileSort 然后返回limit_count记录。

FileSort有两种算法：  

模式1：排序后的元素涵盖了要输出的数据。排序结果是一串有序序列元素组，不再需要额外的记录读取； 

模式2：排序结果是<sort_key,row_id>键值对序列，通过这些row_ids再去读取记录（随机读取，效率低下）；

2、如果column上面存在索引，依据索引排序取有序的记录结果集，然后走where条件过滤，直到得到row_number之后停止查找，然后返回结果。(本例中用到的是索引排序)

   举例：

  上图，第一条Sql当找到row_count之后立即返回，而第二条Sql由于没有满足条件的因此会依据需要通过索引遍历所有有序的记录行来过滤where语句，因此时间1.76表，主要用在的扫表上面。

   本例中如果发生扫表需要花费时间：

 

PS：Mysql 手册对于Order by limit 的说明

• If you are selecting only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.

• If you combine LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the firstrow_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.