前言
mysql的sql优化器比较弱，选择执行计划貌似很随机.
案例
一、表结构说明
mysql> show create table table_order\G
*************************** 1. row ***************************
Table: table_order
Create Table: CREATE TABLE `table_order` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`order_no` varchar(255) NOT NULL DEFAULT '',
`shopid` int(10) unsigned NOT NULL DEFAULT '0',
`app_id` int(10) unsigned NOT NULL DEFAULT '0',
`activity_id` int(10) unsigned NOT NULL DEFAULT '0',
.......
UNIQUE KEY `uk_app_no` (`shopid`,`order_no`),
KEY `mobile_uid` (`customer_mobile`,`uid`),
KEY `app_id` (`app_id`),
KEY `status_expiretime` (`is_online`,`status`,`expire_time`),
KEY `uid_spno` (`uid`,`shopid`),
KEY `status_paytime` (`status`,`pay_time`),
KEY `retm` (`retm`)
) ENGINE=InnoDB AUTO_INCREMENT=31583626 DEFAULT CHARSET=utf8 COMMENT='轻支付动态订单表'
1 row in set (0.01 sec)
二、案例: sql结构相同，参数不同，sql出的数据行数差距大，执行计划相同，执行效率差别很大
SQL1: 数据量：1条， 执行时间：49.27 sec
SELECT * FROM `table_order` WHERE business_status=100 AND `shopid` IN (102686, 129621) AND `status` IN (1, 18, 19) AND `retm` >= 1456675200 AND `retm` < 1456761600 ORDER BY `id` DESC LIMIT 0, 10
+----+-------------+----------------+-------+------------------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+------------------------------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | table_order | index | uk_app_no,status_paytime,retm | PRIMARY | 4 | NULL | 2355 | Using where |
+----+-------------+----------------+-------+------------------------------------+---------+---------+------+------+-------------+
SQL2: 数据量：10000+条， 执行时间：0.27 sec
SELECT * FROM `table_order` WHERE business_status=100 AND `shopid` IN (84010, 129621) AND `status` IN (1, 18, 19) AND `retm` >= 1456675200 AND `retm` < 1456761600 ORDER BY `id` DESC LIMIT 0, 10
+----+-------------+----------------+-------+------------------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+------------------------------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | table_order | index | uk_app_no,status_paytime,retm | PRIMARY | 4 | NULL | 1087 | Using where |
+----+-------------+----------------+-------+------------------------------------+---------+---------+------+------+-------------+
SQL3: 数据量：10000+条， 执行时间：0.21 sec
SELECT * FROM `table_order` WHERE business_status=100 AND `shopid` IN (84010, 129621) AND `status` IN (1, 18, 19) AND `retm` >= 1456675200 AND `retm` < 1456761600 ORDER BY `id` DESC LIMIT 1000, 10
+----+-------------+----------------+-------+------------------------------------+-----------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+------------------------------------+-----------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | table_order | range | uk_app_no,status_paytime,retm | retm | 4 | NULL | 82208 | Using where; Using filesort |
+----+-------------+----------------+-------+------------------------------------+-----------+---------+------+-------+-----------------------------+
结果分析:
1、sql1，按照主键索引，主键索引已经排序，查找数据，数据量不到10条，会沿着主键索引按顺序查下去，直到查完符合条件的数据，这种查法，导致类似全表查询
2、sql2，按照主键索引，主键索引已经排序，查找数据，查到第10条，直接返回，所以查询效率高。
同时，随着limit a,b 翻页，a的值越大，sql2 会调整查询计划，选择合适的查询计划。如SQL3
解决方案:
可以遵循一条优化原则: "尽量早过滤"， 多个索引的情况下，选择索引过滤数据量最少的。 例如sql中， where 条件A AND 条件B；如果
where 条件A 取出的数据 < where 条件B 取出的数据，
可以force INDEX(A)