您的当前位置：首页正文

mysql大数据量之limit优化

2023-11-10 来源：东饰资讯网

背景：当数据库里面的数据达到几百万条上千万条的时候，如果要分页的时候（不过一般分页不会有这么多），如果业务要求这么做那我们需要如何解决呢？我用的本地一个自己生产的一张表有五百多万的表，来进行测试，表名为big_data;首先我们看如下几条sql语句：在这之前我们开启profiling来监测sql语句执行的情况。set profiling=1;1.查询从第10w条数据开始分页10条2.查询从第20w条数据分页10条3.查询从第30w条数据分页10条

3.查询从第300w条数据分页10条

3.查询从第500w条数据分页10条

我们可以看出查询从200w开始分页的都还比较快，但从500w开始速度就变的很慢了，这个是不太让人满意的。

mysql> select id,my_name from big_data limit 5000000,10;

+---------+------------+

| id | my_name |

+---------+------------+

| 5000001 | kwCwziqhNu |

| 5000002 | NLpqMMwaJv |

| 5000003 | kskUTLXDbx |

| 5000004 | PtAvBtpubZ |

| 5000005 | whsuShiuvX |

| 5000006 | TcDLWzHNQT |

| 5000007 | qHmnEkjsmh |

| 5000008 | UQrmluqvgr |

| 5000009 | UzKeqpEbtQ |

| 5000010 | SkuvSePMpq |

+---------+------------+

10 rows in set (2.34 sec)

mysql> show profiles;

+----------+------------+--------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+--------------------------------------------------+

| 1 | 0.02591075 | select id,my_name from big_data limit 100000,10 |

| 2 | 0.05773150 | select id,my_name from big_data limit 200000,10 |

| 3 | 0.08253525 | select id,my_name from big_data limit 300000,10 |

| 4 | 1.38455375 | select id,my_name from big_data limit 3000000,10 |

| 5 | 2.34040775 | select id,my_name from big_data limit 5000000,10 |

+----------+------------+--------------------------------------------------+

5 rows in set, 1 warning (0.00 sec)

show profiles；我们就如下两种解决方法：（1）、通过判断id的范围来分页select id,my_sn from big_data where id>5000000 limit 10;也得到了分页的数据，但是我们发现如果id不是顺序的，也就是如果有数据删除过的话，那么这样分页数据就会不正确，这个是有缺陷的。（2）、通过连接查询来分页我们可以先查询500w条数据开始分页的那10个id，然后通过连接查询显示数据mysql> select b.id,b.my_name from big_data as b inner join (select id from big_data order by id limit 4500000,10) as tmp on tmp.id=b.id;

我们测试不同起始端的分页数据

mysql> select b.id,b.my_name from big_data as b inner join (select id from big_data order by id limit 5000000,10) as tmp on tmp.id=b.id;

+---------+------------+

| id | my_name |

+---------+------------+

| 5000001 | kwCwziqhNu |

| 5000002 | NLpqMMwaJv |

| 5000003 | kskUTLXDbx |

| 5000004 | PtAvBtpubZ |

| 5000005 | whsuShiuvX |

| 5000006 | TcDLWzHNQT |

| 5000007 | qHmnEkjsmh |

| 5000008 | UQrmluqvgr |

| 5000009 | UzKeqpEbtQ |

| 5000010 | SkuvSePMpq |

+---------+------------+

10 rows in set (2.15 sec)

mysql> show profiles;

+----------+------------+------------------------------------------------------------------------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------------------------------------------------------------------------------------------------------------------+

| 1 | 0.02591075 | select id,my_name from big_data limit 100000,10 |

| 2 | 0.05773150 | select id,my_name from big_data limit 200000,10 |

| 3 | 0.08253525 | select id,my_name from big_data limit 300000,10 |

| 4 | 1.38455375 | select id,my_name from big_data limit 3000000,10 |

| 5 | 2.34040775 | select id,my_name from big_data limit 5000000,10 |

| 6 | 0.00004200 | reset query cache |

| 7 | 0.01999275 | select b.id,b.my_name from big_data as b inner join (select id from big_data order by id limit 100000,10) as tmp on tmp.id=b.id |

| 8 | 0.03888825 | select b.id,b.my_name from big_data as b inner join (select id from big_data order by id limit 200000,10) as tmp on tmp.id=b.id |

| 9 | 0.37394450 | select b.id,b.my_name from big_data as b inner join (select id from big_data order by id limit 1000000,10) as tmp on tmp.id=b.id |

| 10 | 1.33475700 | select b.id,b.my_name from big_data as b inner join (select id from big_data order by id limit 3000000,10) as tmp on tmp.id=b.id |

| 11 | 2.14759000 | select b.id,b.my_name from big_data as b inner join (select id from big_data order by id limit 5000000,10) as tmp on tmp.id=b.id |

如果怀疑有缓存的缘故我们可以清楚缓存后来查询

reset query cache;

?show profile for query 3;//查看被记录的第三条sql语句的执行情况可以看出两种方法查出来的数据都是一致的，但通过方法二的速度比之前单表查询的速度快了一些。分析：因为mysql分页查询是先把分页之前数据都查询出来了，然后截取后把不是分页的数据给扔掉后得到的结果这样，所以数据量太大了后分页缓慢是可以理解的。但是我们可以先把需要分页的id查询出来，因为id是主键id主键索引，查询起来还是快很多的，然后根据id连接查询对应的分页数据，可见并不是所有的连接查询都会比单查询要慢，要依情况而定。

mysql大数据量之limit优化

标签：

小编还为您整理了以下内容，可能对您也有帮助：

mysql的limit经典用法及优化

　　用法一

　　SELECT `keyword_rank` * FROM `keyword_rank` WHERE （advertiserid= ） LIMIT OFFSET ;

　　比如这个SQL limit后面跟的是条数据 offset后面是从第条开始读取

　　用法二

　　SELECT `keyword_rank` * FROM `keyword_rank` WHERE （advertiserid= ） LIMIT ;

　　而这个SQL limit后面是从第条开始读读取条信息

　　这两个千万别搞混哦

　　用法三

　　select * from tablename <条件语句> limit

　　从第条后开始最后一条的记录

　　用法四

　　select * from tablename <条件语句> limit

　　相当于limit 查询结果取前条数据用法五

　　mysql低版本不支持limit offset

　　limit offset 在mysql 以上的版本中都可以正常运行在旧版本的mysql 中无效

　　limit m offset n 等价于 limit m n

　　limit 的优化

　　mysql的limit给分页带来了极大的方便但数据量一大的时候 limit的性能就急剧下降

　　MYSQL的优化是非常重要的其他最常用也最需要优化的就是limit mysql的limit给分页带来了极大的方便但数据量一大的时候 limit的性能就急剧下降

　　同样是取条数据

　　select * from yanxue _visit limit 和

　　select * from yanxue _visit limit

　　就不是一个数量级别的

　　网上也很多关于limit的五条优化准则都是翻译自mysql手册虽然正确但不实用今天发现一篇文章写了些关于limit优化的很不错

　　文中不是直接使用limit 而是首先获取到offset的id然后直接使用limit size来获取数据根据他的数据明显要好于直接使用limit 这里我具体使用数据分两种情况进行测试（测试环境win +p 双核（ GHZ） + G内存 mysql ）

　　 offset比较小的时候

　　select * from yanxue _visit limit

　　多次运行时间保持在之间

　　Select * From yanxue _visit Where vid >=（

　　Select vid From yanxue _visit Order By vid limit

　　） limit

　　多次运行时间保持在之间主要是

　　结论偏移offset较小的时候直接使用limit较优这个显然是子查询的原因

　　 offset大的时候

　　select * from yanxue _visit limit

　　多次运行时间保持在左右

　　Select * From yanxue _visit Where vid >=（

　　Select vid From yanxue _visit Order By vid limit

　　） limit

　　多次运行时间保持在左右只有前者的 / 可以预计offset越大后者越优

lishixin/Article/program/MySQL/201311/29501

如何提高MySQL Limit查询的性能

如何提高MySQL Limit查询的性能?

在MySQL数据库操作中，我们在做一些查询的时候总希望能避免数据库引擎做全表扫描，因为全表扫描时间长，而且其中大部分扫描对客户端而言是没有意义的。其实我们可以使用Limit关键字来避免全表扫描的情况，从而提高效率。

有个几千万条记录的表 on MySQL 5.0.x，现在要读出其中几十万万条左右的记录。常用方法，依次循环：

select * from mytable where index_col = xxx limit offset, limit;

经验：如果没有blob/text字段，单行记录比较小，可以把 limit 设大点，会加快速度。

问题：头几万条读取很快，但是速度呈线性下降，同时 mysql server cpu 99% ，速度不可接受。

调用 explain select * from mytable where index_col = xxx limit offset, limit;

显示 type = ALL

在 MySQL optimization 的文档写到"All"的解释

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

看样子对于 all, mysql 就使用比较笨的方法，那就改用 range 方式？因为 id 是递增的，也很好修改 sql 。

select * from mytable where id > offset and id < offset + limit and index_col = xxx

explain 显示 type = range，结果速度非常理想，返回结果快了几十倍。

Limit语法：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT接受一个或两个数字参数。参数必须是一个整数常量。

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。

为了与 PostgreSQL 兼容，MySQL 也支持句法：LIMIT # OFFSET #。

mysql> SELECT * FROM table LIMIT 5,10; //检索记录行6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为-1

mysql> SELECT * FROM table LIMIT 95,-1; //检索记录行96-last

//如果只给定一个参数，它表示返回最大的记录行数目，换句话说，LIMIT n 等价于 LIMIT 0,n

mysql> SELECT * FROM table LIMIT 5; //检索前5个记录行

MySQL的limit给分页带来了极大的方便，但数据量一大的时候，limit的性能就急剧下降。同样是取10条数据，下面两句就不是一个数量级别的。

select * from table limit 10000,10

select * from table limit 0,10

文中不是直接使用limit，而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据，明显要好于直接使用limit。

这里我具体使用数据分两种情况进行测试。

1、offset比较小的时候：

select * from table limit 10,10

//多次运行，时间保持在0.0004-0.0005之间

Select * From table Where vid ＞=(Select vid From table Order By vid limit 10,1) limit 10

//多次运行，时间保持在0.0005-0.0006之间，主要是0.0006

结论：偏移offset较小的时候，直接使用limit较优。这个显然是子查询的原因。

2、offset大的时候：

select * from table limit 10000,10

//多次运行，时间保持在0.0187左右

Select * From table Where vid ＞=(Select vid From table Order By vid limit 10000,1) limit 10

//多次运行，时间保持在0.0061左右，只有前者的1/3。可以预计offset越大，后者越优。

MySQL的limit 优化

mysql 5.7.28

按id增序导出t_order_detail表数据，由于数据量过多，防止一次查询数据量大多导致异常，批量查询数据，每次查询200条数据，数据量50万，查询出的数据量5万多条。

SQL如下

Explain结果

《高性能MySql第三版》章节6.7.5 优化Limit分页中提到，在偏移量非常大的时候，例如可能是LIMIT 1000,20 这样的查询，这时候MySQL需要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。要优化此种查询，要么在页面中*分页数量，要么是优化大偏移量的性能。使用“延迟关联”，它让MySQL扫描尽可能少的页面，获取需要要访问的记录后再根据关联列回原表查询需要的所有列。

Explain结果

也没看不出来区别，直接用SQL执行看消耗的时间

这个延迟关联蛮简单的(自我感觉)，为啥MySQL不直接内部实现优化呢？

延迟关联到底节省了哪部分动作消耗的时间，如果只是如下的SQL,那就根本没必要关联，在查询了其他的字段后，才需要延迟关联。所以是节省了获取其他字段的消耗的时间？还是排序时多个字段后更加耗时？

当前SQL使用id排序，可以直接使用上一页数据最后一条数据的Id做筛选，这样直接筛选出需要的数据，查询查第49999条数据的order_id为707352，SQL如下

Explain结果

此种优化方法要求使用唯一的字段排序。

高性能MySql
MySQL ORDER BY _ LIMIT performance_ late row lookups at EXPLAIN EXTENDED

MySQL的limit 优化

Mysql使用limit深度分页优化

mysql使用select * limit offset, rows分页在深度分页的情况下。性能急剧下降。

limit用于数据的分页查询，当然也会用于数据的截取，下面是limit的用法：

1. 模仿百度、谷歌方案（前端业务控制）

类似于分段。我们给每次只能翻100页、超过一百页的需要重新加载后面的100页。这样就解决了每次加载数量数据大速度慢的问题了

2. 记录每次取出的最大id，然后where id > 最大id

select * from table_name Where id > 最大id limit 10000, 10;
这种方法适用于：除了主键ID等离散型字段外，也适用连续型字段datetime等
最大id由前端分页pageNum和pageIndex计算出来。

3. IN获取id

4. join方式 + 覆盖索引（推荐）

如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！

1. jdbcpagingReader使用方式

2. db索引分区器使用方式

入参1：表名如test_table

入参2：排序索引字段可以是主键，也可以是其他索引。需要保证是唯一索引即可。如：id

入参3：主键可手动传入，也可以根据表名计算出来：现在只支持单列主键的。如：id

入参4: 具体表要分多少块。如：4

Mysql使用limit深度分页优化

mysql使用select * limit offset, rows分页在深度分页的情况下。性能急剧下降。

limit用于数据的分页查询，当然也会用于数据的截取，下面是limit的用法：

1. 模仿百度、谷歌方案（前端业务控制）

类似于分段。我们给每次只能翻100页、超过一百页的需要重新加载后面的100页。这样就解决了每次加载数量数据大速度慢的问题了

2. 记录每次取出的最大id，然后where id > 最大id

3. IN获取id

4. join方式 + 覆盖索引（推荐）

如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！

1. jdbcpagingReader使用方式

2. db索引分区器使用方式

入参1：表名如test_table

入参2：排序索引字段可以是主键，也可以是其他索引。需要保证是唯一索引即可。如：id

入参3：主键可手动传入，也可以根据表名计算出来：现在只支持单列主键的。如：id

入参4: 具体表要分多少块。如：4

MySQL优化:order by和limit

1. 对order by使用复合索引

order by和limit一起使用，避免引起全表扫描和数据排序是非常重要的，因此借助合适的索引提高查询效率。

使用联合索引

联合索引又叫复合索引，是由表中的几个列联合组成的索引。联合索引生效需满足最左前缀原则，即如果联合索引列为a,b,c三列，a,b,c 、a,b 、a生效，b,c、a,c、b、c等不生效（此处的顺序不是where条件后面的先后顺序，而是where条件中是否存在这些列，如果where中只存在a,c列，则不生效）。

索引生效，与where条件的顺序无关：

索引失效，与where条件的列是否存在有关：

带IN条件的联合索引失效

in的参数个数为1个，联合索引生效，大于1个，索引失效。所以使用了强制索引使联合索引生效。

原因分析：

第一、取决于B树的数据结构，单参数的IN只会得到一颗基于model子树，该子树的code本身是有序的，所以索引生效，查询效率高；多参数的IN会得到多颗基于model的子树，每颗子树的code字段是有序的，但是总体上可能不是有序的，所以索引失效，查询效率低。

第二、使用强制索引后，理论上无法保证order by的顺序，但是如果数据本身的特性，比如时间递增的这类数据，总体上还是有序的，笔者试过多中途径想要迫使强制索引得到错误的结果，结果都对了。强制索引需进一步研究。

2. 大数据量limit慎用

limit常用于分页中，有两种用法，三种写法：

偏移量offset较大的优化

limit偏移量较小时性能优秀，分页越到后面，偏移量递增，limit的性能会逐渐下降。

此时，通过子查询优化limit，效果如下：

以上数据来自一张超过2000万的MySQL单表，仅供参考，能够说明子查询明显能够提升效率，笔者开始尝试把子查询的order by去掉，发现查询效率又提升2倍，但是对比发现数据不正确，explain后发现查询优化器给出的子查询索引并不是id（此表建有多个索引，id是主键，区分度最高），这一点比较困惑。

ps：在sql语句中，limt关键字是最后才用到的。以下条件的出现顺序一般是：where->group by->having-order by->limit

mysql中查询第几行到第几行的记录

1、查询前n行

查询第一行

2、查询第n行到第m行

查询第4行到第6行

3、查询后n行

　　查询最后一行

4、查询一条记录的下一条记录

查询一条记录的上一条记录

MySQL优化:order by和limit

mysql对于大量数据,怎么进行优化

1）调整服务器的性能参数：key_buffer_size、Innodb_buffer_pool_size进行合理的配置
2）建立合适的索引
3）写查询语句用explain分析一下执行过程，核实一下执行计划，是否按照自己的意愿执行。
索引使要注意的地方：
1)索引不会包含有NULL值的列(使用索引的列设需要置默认值)2)使用短索引 3)不要在列上进行运算，即操作符号左端（使用函数）4) like语句操作5)不使用NOT IN和<>操作6)复合索引的建立7)选择自己使用的索引： USE INDEX ， IGNORE INDEX ， FORCE INDEX 8) where子句中已经使用了索引的话，那么order by中的列是不会使用索引的（使用复合索引解决）
表扫描要注意的地方：
1)数据表很小，全表扫描比做索引键的查找来得快。当表的记录总数小于10且比较短时通常这么做。
2)没有合适用于 ON 或 WHERE 分句的索引字段。
3)让索引字段和常量值比较，MySQL已经计算（基于索引树）到常量覆盖了数据表的很大部分。
4)通过其他字段使用了一个基数很小（很多记录匹配索引键值）的索引键。这种情况下，MySQL认为使用索引键需要大量查找，还不如全表扫描来得更快。
5)使用合适的索引可以解决表扫描
6) 使用Limit有时候也可以解决表扫描
优化的地方太多了，一一列举不完，你可以去这里看一下，这里面关于优化的知识有很多
http://www.quzixi.com/forum-2-2.html，如果觉得说的有用就给个好评，写这么多怪不容易的，用了我一刻钟的时间呀

mysql对于大量数据,怎么进行优化

MySQL百万级数据量分页查询方法及其优化建议

offset+limit方式的分页查询，当数据表超过100w条记录，性能会很差。
主要原因是offset limit的分页方式是从头开始查询，然后舍弃前offset个记录，所以offset偏移量越大，查询速度越慢。

比如: 读第10000到10019行元素(pk是主键/唯一键).

使用order by id可以在查询时使用主键索引。
但是这种方式在id为uuid的时候就会出现问题。可以使用where in的方式解决：

带条件的查询：
如果在分页查询中添加了where条件例如 type = 'a’这样的条件，sql变成：

这种情况因为type没有使用索引也会导致查询速度变慢。但是只添加type为索引查询速度还是很慢,是因为查询的数据量太多了。这个时候考虑添加组合索引，组合索引的顺序要where条件字段在前，id在后，如 (type,id)，因为组合索引查询时用到了type索引，而type跟id是组合索引的关系，如果只select id ，那么直接就可以按组合索引返回id，而不需要再进行一次查询去返回id

使用uuid作为主键不仅会带来性能上的问题，在查询时也会遇到问题。

因为在使用select id from table limit 10000,10 查询id数据时，默认是对id进行排序，返回的是排序后的id结果，如果我们想按插入顺序查询结果，这样查询出来的结果就与我们的需求不相符。

聚集索引跟非聚集索引：聚集索引类似与新华字典的拼音，根据拼音搜索到的信息都是连续的，可以很快获取到它前后的信息。非聚集索引类似于部首查询，信息存放的位置可能不在一个区域。对经常使用范围查询的字段考虑使用聚集索引。

InnoDB中索引分为聚簇索引（主键索引）和非聚簇索引（非主键索引），聚簇索引的叶子节点中保存的是整行记录，而非聚簇索引的叶子节点中保存的是该行记录的主键的值。

如果您的表上定义有主键，该主键索引是聚集索引。
如果你不定义为您的表的主键时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。
如果没有这样的列，InnoDB就自己产生一个这样的ID值，
优先选index key_len小的索引进行count(*),尽量不使用聚簇索引

在没有where条件的情况下，count(*)和count(常量)，如果有非聚簇索引，mysql会自动选择非聚簇索引，因为非聚簇索引所占的空间小，如果没有非聚簇索引会使用聚集索引。count(primary key)主键id为聚集索引，使用聚集索引。有where条件的情况下，是否使用索引会根据where条件判断。

MySQL百万级数据量分页查询方法及其优化建议

mysql中怎样对大批量级的数据查询进行优化

在我们使用MySQL数据库时，比较常用也是查询，包括基本查询，关联查询，条件查询等等，对于同一个操作，SQL语句的实现有很多种写法，但是不同的写法查询的性能可能会有很大的差异。这里主要介绍下select查询优化的要点。

1. 使用慢查询日志去发现慢查询。

2. 使用执行计划去判断查询是否正常运行。

3. 总是去测试你的查询看看是否他们运行在最佳状态下 –久而久之性能总会变化。

4. 避免在整个表上使用count(*),它可能锁住整张表。

5. 使查询保持一致以便后续相似的查询可以使用查询缓存。

6. 在适当的情形下使用GROUP BY而不是DISTINCT。

7. 在WHERE, GROUP BY和ORDER BY子句中使用有索引的列。

8. 保持索引简单,不在多个索引中包含同一个列。

9. 有时候MySQL会使用错误的索引,对于这种情况使用USE INDEX。

10. 检查使用SQL_MODE=STRICT的问题。

11.对于记录数小于5的索引字段，在UNION的时候使用LIMIT不是是用OR.

12. 为了避免在更新前SELECT，使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去实现。

3. 不要使用 MAX,使用索引字段和ORDER BY子句。

14. 避免使用ORDER BY RAND().

15. LIMIT M，N实际上可以减缓查询在某些情况下，有节制地使用。

16. 在WHERE子句中使用UNION代替子查询。

17. 对于UPDATES（更新），使用 SHARE MODE（共享模式），以防止独占锁。

18. 在重新启动的MySQL，记得来温暖你的数据库，以确保您的数据在内存和查询速度快。

19. 使用DROP TABLE，CREATE TABLE DELETE FROM从表中删除所有数据。

20. 最小化的数据在查询你需要的数据，使用*消耗大量的时间。

21. 考虑持久连接，而不是多个连接，以减少开销。

22. 基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询。

23. 当负载增加您的服务器上，使用SHOW PROCESSLIST查看慢的和有问题的查询。

24. 在开发环境中产生的镜像数据中测试的所有可疑的查询。

来源：PHP程序员雷雪松的博客

怎么进行mysql数据库优化?

有八个方面可以对mysql进行优化：
1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
2. 使用连接（JOIN）来代替子查询(Sub-Queries)
MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。
3、使用联合(UNION)来代替手动创建的临时表
MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。
4、事务
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败
5、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。
6、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
7、使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。
8、优化的查询语句
绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

怎么进行mysql数据库优化?

PHP+MySQL高效的分页方法,如何优化LIMIT,OFFSET进行的分页?

很多应用往往只展示最新或最热门的几条记录，但为了旧记录仍然可访问，所以就需要个分页的导航栏。然而，如何通过MySQL更好的实现分页，始终是比较令人头疼的问题。虽然没有拿来就能用的解决办法，但了解数据库的底层或多或少有助于优化分页查询。

我们先从一个常用但性能很差的查询来看一看。

SELECT *

FROM city

ORDER BY id DESC

LIMIT 0, 15

这个查询耗时0.00sec。So，这个查询有什么问题呢？实际上，这个查询语句和参数都没有问题，因为它用到了下面表的主键，而且只读取15条记录。

CREATE TABLE city (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

city varchar(128) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

真正的问题在于offset(分页偏移量)很大的时候，像下面这样：

SELECT *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

上面的查询在有2M行记录时需要0.22sec，通过EXPLAIN查看SQL的执行计划可以发现该SQL检索了100015行，但最后只需要15行。大的分页偏移量会增加使用的数据，MySQL会将大量最终不会使用的数据加载到内存中。就算我们假设大部分网站的用户只访问前几页数据，但少量的大的分页偏移量的请求也会对整个系统造成危害。意识到了这一点，但并没有为了每秒可以处理更多的请求而去优化数据库，而是将重心放在将请求响应时间的方差变小。

对于分页请求，还有一个信息也很重要，就是总共的记录数。我们可以通过下面的查询很容易的获取总的记录数。

SELECT COUNT(*)

FROM city;

然而，上面的SQL在采用InnoDB为存储引擎时需要耗费9.28sec。一个不正确的优化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能够在分页查询时事先准备好符合条件的记录数，随后只要执行一句 select FOUND_ROWS(); 就能获得总记录数。但是在大多数情况下，查询语句简短并不意味着性能的提高。不幸的是，这种分页查询方式在许多主流框架中都有用到，下面看看这个语句的查询性能。

SELECT SQL_CALC_FOUND_ROWS *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

这个语句耗时20.02sec，是上一个的两倍。事实证明使用 SQL_CALC_FOUND_ROWS 做分页是很糟糕的想法。

下面来看看到底如何优化。文章分为两部分，第一部分是如何获取记录的总数目，第二部分是获取真正的记录。

高效的计算行数

如果采用的引擎是MyISAM，可以直接执行COUNT(*)去获取行数即可。相似的，在堆表中也会将行数存储到表的元信息中。但如果引擎是InnoDB情况就会复杂一些，因为InnoDB不保存表的具体行数。

我们可以将行数缓存起来，然后可以通过一个守护进程定期更新或者用户的某些操作导致缓存失效时，执行下面的语句：

SELECT COUNT(*)

FROM city

USE INDEX(PRIMARY);

获取记录

下面进入这篇文章最重要的部分，获取分页要展示的记录。上面已经说过了，大的偏移量会影响性能，所以我们要重写查询语句。为了演示，我们创建一个新的表“news”，按照时事性排序(最新发布的在最前面)，实现一个高性能的分页。为了简单，我们就假设最新发布的新闻的Id也是最大的。

CREATE TABLE news(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

title VARCHAR(128) NOT NULL

) ENGINE=InnoDB;

一个比较高效的方式是基于用户展示的最后一个新闻Id。查询下一页的语句如下，需要传入当前页面展示的最后一个Id。

SELECT *

FROM news WHERE id < $last_id

ORDER BY id DESC

LIMIT $perpage

查询上一页的语句类似，只不过需要传入当前页的第一个Id，并且要逆序。

SELECT *

FROM news WHERE id > $last_id

ORDER BY id ASC

LIMIT $perpage

上面的查询方式适合实现简易的分页，即不显示具体的页数导航，只显示“上一页”和“下一页”，例如博客中页脚显示“上一页”，“下一页”的按钮。但如果要实现真正的页面导航还是很难的,下面看看另一种方式。

SELECT id

FROM (

SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt

FROM news

JOIN (SELECT @cnt:= 0)T

WHERE id < $last_id

ORDER BY id DESC

LIMIT $perpage * $buttons

WHERE cnt = 0;

通过上面的语句可以为每一个分页的按钮计算出一个offset对应的id。这种方法还有一个好处。假设，网站上正在发布一片新的文章，那么所有文章的位置都会往后移一位，所以如果用户在发布文章时换页，那么他会看见一篇文章两次。如果固定了每个按钮的offset Id，这个问题就迎刃而解了。Mark Callaghan发表过一篇类似的博客，利用了组合索引和两个位置变量，但是基本思想是一致的。

如果表中的记录很少被删除、修改，还可以将记录对应的页码存储到表中，并在该列上创建合适的索引。采用这种方式，当新增一个记录的时候，需要执行下面的查询重新生成对应的页号。

SET p:= 0;

UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;

当然，也可以新增一个专用于分页的表，可以用个后台程序来维护。

UPDATE pagination T

JOIN (

SELECT id, CEIL((p:= p + 1) / $perpage) page

FROM news

ORDER BY id

ON C.id = T.id

SET T.page = C.page;

现在想获取任意一页的元素就很简单了：

SELECT *

FROM news A

JOIN pagination B ON A.id=B.ID

WHERE page=$offset;

还有另外一种与上种方法比较相似的方法来做分页，这种方式比较试用于数据集相对小，并且没有可用的索引的情况下—比如处理搜索结果时。在一个普通的服务器上执行下面的查询，当有2M条记录时，要耗费2sec左右。这种方式比较简单，创建一个用来存储所有Id的临时表即可(这也是最耗费性能的地方)。

CREATE TEMPORARY TABLE _tmp (KEY SORT(random))

SELECT id, FLOOR(RAND() * 0x8000000) random

FROM city;

ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;

接下来就可以向下面一样执行分页查询了。

SELECT *

FROM _tmp

WHERE OFFSET >= $offset

ORDER BY OFFSET

LIMIT $perpage;

简单来说，对于分页的优化就是。。。避免数据量大时扫描过多的记录。