from – 仙

PHP 最简单的API 请求频率限制方式

例如限制 API 每秒仅允许请求一次原来的API这样 [crayon-674074427c36d68687 […]

NYAA.SE 活过来啦

Nyaa.si – our new home for our torrents As you might’ve […]

LinkedIn（领英）一亿一千七百万账号密码泄露

117 million LinkedIn emails and passwords from a 2012 h […]

如何使用IPtablse实现流量转向至其他服务器

If you have ever handled the migration of a web service […]

将SHA-1证书更换为SHA-256证书

将SHA-1证书更换为SHA-256证书 Migrating SSL Certificate from SHA […]

MYSQL大数据表分页查询SQL语句效率对比

测了下 360多万数据

分页效率测试
SELECT * FROM `xvideos` 

Limit方式
===========================
SELECT * 
FROM  xvideos 
LIMIT 3643710 , 30 
查询花费 19.3808 秒

Where ID 方式
===========================
SELECT * FROM xvideos 
WHERE id > 3643710 
ORDER BY id
LIMIT 30
查询花费 0.0379 秒

Between 方式  
===========================
SELECT * FROM xvideos 
WHERE id BETWEEN 3643710 AND 3643710 + 30 - 1
查询花费 0.0007 秒

SubQuery 子查询方式
===========================
SELECT * FROM xvideos WHERE id > 
(SELECT id FROM xvideos LIMIT 3643709, 1) ORDER BY id asc LIMIT 30
查询花费 1.8362 秒

JOIN 方式
===========================
SELECT * FROM xvideos AS t1 
JOIN (SELECT id FROM xvideos ORDER BY id asc LIMIT  3643709, 1) AS t2 
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT 30; 
查询花费 1.1632 秒

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

分页效率测试

SELECT * FROM `xvideos`

Limit方式

===========================

SELECT *

FROM xvideos

LIMIT 3643710 , 30

查询花费 19.3808 秒

Where ID 方式

===========================

SELECT * FROM xvideos

WHERE id > 3643710

ORDER BY id

LIMIT 30

查询花费 0.0379 秒

Between 方式

===========================

SELECT * FROM xvideos

WHERE id BETWEEN 3643710 AND 3643710 + 30 - 1

查询花费 0.0007 秒

SubQuery 子查询方式

===========================

SELECT * FROM xvideos WHERE id >

(SELECT id FROM xvideos LIMIT 3643709, 1) ORDER BY id asc LIMIT 30

查询花费 1.8362 秒

JOIN 方式

===========================

SELECT * FROM xvideos AS t1

JOIN (SELECT id FROM xvideos ORDER BY id asc LIMIT 3643709, 1) AS t2

WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT 30;

查询花费 1.1632 秒

哪个最好一目了然 […]

标签： from