场景 需要做分页查询
对应的表结构
-- ----------------------------
-- Table structure for transaction_record
-- ----------------------------
DROP TABLE IF EXISTS `transaction_record`;
CREATE TABLE `transaction_record` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`cardno` int(11) NULL DEFAULT NULL,
`transaction_date` datetime NULL DEFAULT NULL,
`expense` double(11, 2) NULL DEFAULT NULL,
`income` double(11, 2) NULL DEFAULT NULL,
`balance` double(11, 2) NOT NULL DEFAULT '',
`transaction_type` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`remark` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
PRIMARY KEY USING BTREE (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 10007 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;
两种方案
一、传统的limit
SELECT
*
FROM
transaction_record AS record
LIMIT 10000,10
explain 解析的结果
type 为all 表示的是全表扫描
possible_keys 、key、key_len 全部为空表示没有走任何索引
rows 表示扫描的行数 为 9500行,所以耗时长
所以第一次查询比较慢,花了两秒,但是连续的查询之后,应该是mysql 做了缓存优化,速度变快 0.043秒左右(在有一万左右的数据量的情况下)
二、使用子查询优化
SELECT
*
FROM
transaction_record AS record
WHERE
record.id >= (
SELECT
transaction_record.id
FROM
transaction_record
ORDER BY
id
LIMIT 10000,
1
) LIMIT 10
变快了一些
mysql 不区分大小写的问题
SELECT
*
FROM
transaction_record AS re
WHERE
re.remark LIKE '%a%'
EXPLAIN SELECT
*
FROM
transaction_record AS re
WHERE
BINARY re.remark LIKE '%a%'
通过关键字 binary 可以强制区分大小写