mysql 大数据 分页 优化

1. 不用多扫描数据

Paging without discarding records

尽量避免物理删除. 

select id, name, address, phone
FROM customers
WHERE id > 990
ORDER BY id LIMIT 10;
(保存最后一个|上一页的id)

前提：根据ID分页.

用户名分页：

[code]
SELECT id, username
FROM customers
WHERE username > ‘shull@iheavy.com’
ORDER BY username LIMIT 10;
[/code]

  

2. 延迟关联

Try using a Deferred Join

用户分页.

每页十人.

分第100th 页时,   LIMIT 10,起始点是 OFFSET 990.

服务器需要先去扫描这990条记录， 

 

[code]
SELECT id, name, address, phone FROM customers ORDER BY name LIMIT 10 OFFSET 990;
[/code]

解决方案：延迟关联

deferred join.

The inside piece just uses the primary key. An explain plan shows us “using index” which we love!

只去取主键id

[code]
SELECT id
FROM customers
ORDER BY name
LIMIT 10 OFFSET 990;
[/code]

Now combine this using an INNER JOIN to get the ten rows and data you want:

[code]
SELECT id, name, address, phone
FROM customers
INNER JOIN (
SELECT id
FROM customers
ORDER BY name
LIMIT 10 OFFSET 990)
AS my_results USING(id);
[/code]

That’s pretty cool!

3.保存页面| 

Maintain a Page or Place column

Another way to trick the optimizer from retrieving rows it doesn’t need is to maintain a column for the page, place or position.

Yes you need to update that column whenever you (a) INSERT a row (b) DELETE a row ( c) move a row with UPDATE.

This could get messy with page, but a straight place or position might work easier.

保存页面数据

[code]
SELECT id, name, address, phone
FROM customers
WHERE page = 100
ORDER BY name;
[/code]

Hiring? MySQL DBA Interview Guide for Candidates & Managers.

Or with place column something like this:

保存页面列数

[code]
SELECT id, name, address, phone
FROM customers
WHERE place BETWEEN 990 AND 999
ORDER BY name;
[/code]

 

转载于:https://my.oschina.net/u/3255899/blog/1486578