`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='预付结算单索引表'
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表,主要用在的扫表上面。本例中如果发生扫表需要花费时间:
-
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
withrow_count
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 theLIMIT
clause must be selected, and most or all of them must be sorted, before it can be ascertained that the firstrow_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.