1.1. 背景
对于开发来说,分页功能碰到的频率还是算蛮高的,基本上在每个模块中都需要都会遇到列表分页的功能。他们实现的都很快,因为基本上只要把之前的代码改改就OK了。他们的实现基本是是如下语句:
|
SELECT *FROM goods WHERE user_id=4LIMIT1000,20;
...omit...
20rows inset(0.11sec)
|
像这样的语句对数据量小或偏移量小的时候是十分快的。但是当数据量大并且偏移量大的时候就会有问题了。如下:
|
SELECT *FROM goods WHERE user_id=4LIMIT500000,20;
...omit...
20rows inset(7.84sec)
|
为什么会这样就不说了。下面给出优化的过程。
1.2. 构建数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
--创建商品表
DROP TABLE IFEXISTS goods;
CREATE TABLE goods(
id bigint(20)unsignedNOTNULLAUTO_INCREMENT,
good_name VARCHAR(50)NOTNULL,
user_id TINYINT unsignedNOTNULL,
PRIMARY KEY(`id`)
);
--创建批量添加数据存储过程
--下面创建数据可能需要一点时间
DROP PROCEDURE IFEXISTS insert_batch;
DELIMITER//
CREATE PROCEDURE insert_batch()
BEGIN
DECLAREnum INT;
DECLAREuser_id TINYINT;
SET num=1;
WHILEnum<=100000DO
SELECT FLOOR(RAND()*10+1)INTO user_id;
INSERT INTO goods VALUES(NULL,REPEAT('X',50),user_id);
SET num=num+1;
ENDWHILE;
SET num=1;
WHILEnum<=7DO
INSERT INTO goods SELECT NULL,good_name,user_id FROM goods;
SET num=num+1;
ENDWHILE;
END//
DELIMITER;
--调用存储过程
CALL insert_batch();
--添加索引
ALTER TABLE goods
ADD INDEX idx$goods$user_id(user_id);
SELECT user_id,COUNT(*)FROM goods GROUP BY user_id;
+---------+----------+
|user_id|COUNT(*)|
+---------+----------+
| 1| 10089|
| 2| 10077|
| 3| 9944|
| 4|12710074|
| 5| 10011|
| 6| 9925|
| 7| 9950|
| 8| 10149|
| 9| 9949|
| 10| 9832|
+---------+----------+
|
这边我们以数据最多的user_id=4的记录来模拟
1.3. 优化规则
让所有结果集数据最小化。如果是临时表,还是行数据还是列数据都让结果最小化,还有就是临时结果集尽量不走主键索引,走二级索引。
1.4. 模拟
现在我们需要查询用户4在10000000后20条数据
1、通过user_id找到主键ID(让列结果最小化)
|
SELECT id FROM goods WHERE user_id=4LIMIT10000000,20;
10343427
...omit...
10343446
20rows inset(1.83sec)
|
2、通过获得的主键ID寻找需要的数据,这边我就不使用python来演示了。在程序里面就需要拼出IN里面的条件。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT *
FROM goods
WHERE id IN(
10343427,10343428,10343429,10343430,10343431,
10343432,10343433,10343434,10343435,10343436,
10343437,10343438,10343439,10343440,10343441,
10343442,10343443,10343444,10343445,10343446
);
+----------+----------------------------------------------------+---------+
|id |good_name |user_id|
+----------+----------------------------------------------------+---------+
|10343427|XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX| 4|
...omit...
20rows inset(0.01sec)
|
1.5. 进一步优化
其实上面我们还能让结构级变少。来看下面列表简图:
在网页的分页按钮基本省都是一个连接,或者通过jquery时间分页。我们可以在按钮上添加两个属性参数为max_id和min_id。分别记录的是当前页数据的最小ID和最大ID(如:min_max=10343427、max_id=10343446)。
查找数据如下:
1、通过user_id找到主键ID(让列结果最小化)
如果是点击下一页
|
SELECT id FROM goods WHERE id>10343446ANDuser_id=4LIMIT0,20;
+----------+
|id |
+----------+
|10343447|
...omit...
20rows inset(0.02sec)
|
如果是点击上一页(上一页会比下一页性能来的差一点,因为有用到排序)
|
SELECT id FROM goods WHERE id<10343427ANDuser_id=4
ORDER BY id DESC
LIMIT0,20;
|
2、通过获得的主键ID寻找需要的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT *
FROM goods
WHERE id IN(
10343447,10343448,10343449,10343450,10343451,
10343452,10343453,10343454,10343455,10343456,
10343457,10343458,10343459,10343460,10343461,
10343462,10343463,10343464,10343465,10343466
);
+----------+----------------------------------------------------+---------+
|id |good_name |user_id|
+----------+----------------------------------------------------+---------+
|10343447|XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX| 4|
...omit...
20rows inset(0.01sec)
|
1.6. 总结
这种优化可能在一些使用到聚合函数的排序的情况下没法使用。
在这边鼓励使用MySQL的尽量使用比较简单的语句,不使用JOIN。因为优化器对简单的语句解析的很快,而且在维护的角度来说越白痴的语句越让人容易明白。
当然,强烈反对在程序中 for 循环取数据库。
转自:hh
分享到:
相关推荐
中职应用文写作教案之条据.doc
贷款条据资料.docx
贷款条据..doc
巡察条例-条据书信.docx
条据应用文写作教案.doc
刻章托付书-条据书信.docx
银行贷款申请-条据书信.docx
全民健身条例-条据书信.docx
贷款证明范本-条据书信.docx
贷款时证明-条据书信.docx
海关稽查条例-条据书信.docx
开户申请书-条据书信.docx
广播电视条例-条据书信.docx
住房贷款证明-条据书信.docx
消防责任书-条据书信.docx
消防单位证明-条据书信.docx
医疗保险条例-条据书信.docx
中英文辞职信-条据书信.docx
机关事业单位证明-条据书信.docx
条据书信 工会大病救助感谢信.docx