189 8069 5689
如何提高MySQL Limit查询的性能?\x0d\x0a在MySQL数据库操作中，我们在做一些查询的时候总希望能避免数据库引擎做全表扫描，因为全表扫描时间长，而且其中大部分扫描对客户端而言是没有意义的。其实我们可以使用Limit关键字来避免全表扫描的情况，从而提高效率。\x0d\x0a有个几千万条记录的表 on MySQL 5.0.x，现在要读出其中几十万万条左右的记录。常用方法，依次循环： \x0d\x0aselect * from mytable where index_col = xxx limit offset, limit; \x0d\x0a\x0d\x0a经验：如果没有blob/text字段，单行记录比较小，可以把 limit 设大点，会加快速度。\x0d\x0a问题：头几万条读取很快，但是速度呈线性下降，同时 mysql server cpu 99% ，速度不可接受。 \x0d\x0a调用 explain select * from mytable where index_col = xxx limit offset, limit; \x0d\x0a显示 type = ALL \x0d\x0a在 MySQL optimization 的文档写到"All"的解释 \x0d\x0aA 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. \x0d\x0a看样子对于 all, mysql 就使用比较笨的方法，那就改用 range 方式？ 因为 id 是递增的，也很好修改 sql 。\x0d\x0aselect * from mytable where id offset and id SELECT * FROM table LIMIT 5,10; //检索记录行6-15\x0d\x0a\x0d\x0a//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为-1\x0d\x0amysql SELECT * FROM table LIMIT 95,-1; //检索记录行96-last\x0d\x0a\x0d\x0a//如果只给定一个参数，它表示返回最大的记录行数目，换句话说，LIMIT n 等价于 LIMIT 0,n\x0d\x0amysql SELECT * FROM table LIMIT 5; //检索前5个记录行\x0d\x0a\x0d\x0aMySQL的limit给分页带来了极大的方便，但数据量一大的时候，limit的性能就急剧下降。同样是取10条数据，下面两句就不是一个数量级别的。\x0d\x0aselect * from table limit 10000,10\x0d\x0aselect * from table limit 0,10\x0d\x0a\x0d\x0a文中不是直接使用limit，而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据，明显要好于直接使用limit。\x0d\x0a这里我具体使用数据分两种情况进行测试。\x0d\x0a1、offset比较小的时候：\x0d\x0aselect * from table limit 10,10 \x0d\x0a//多次运行，时间保持在0.0004-0.0005之间\x0d\x0aSelect * From table Where vid ＞=(Select vid From table Order By vid limit 10,1) limit 10 \x0d\x0a//多次运行，时间保持在0.0005-0.0006之间，主要是0.0006\x0d\x0a\x0d\x0a结论：偏移offset较小的时候，直接使用limit较优。这个显然是子查询的原因。\x0d\x0a2、offset大的时候：\x0d\x0aselect * from table limit 10000,10 \x0d\x0a//多次运行，时间保持在0.0187左右\x0d\x0a\x0d\x0aSelect * From table Where vid ＞=(Select vid From table Order By vid limit 10000,1) limit 10\x0d\x0a//多次运行，时间保持在0.0061左右，只有前者的1/3。可以预计offset越大，后者越优。
创新互联公司坚持“要么做到，要么别承诺”的工作理念，服务领域包括：成都网站设计、成都网站建设、外贸网站建设、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的赵县网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！
就是sql查询优化呗。
在不是服务器性能影响的时候，可以关注以下：
1、通过explain查看sql的执行计划，看是否用到了索引
2、是否sql写的不合理，需要改写sql等
3、还是sql没有问题，索引也合理，就是数据太大，字段太多引起查询慢，这个就可以考虑是不是改分表或者分开啥的。
优化这一块涉及到的比较多，可以多重网上，或者博客看看总结，对比你的情况去优化
几十万数据不算多吧，直接2个条件查询，建立个联合索引就行了
问题
我们有一个 SQL，用于找到没有主键 / 唯一键的表，但是在 MySQL 5.7 上运行特别慢，怎么办？
实验
我们搭建一个 MySQL 5.7 的环境，此处省略搭建步骤。
写个简单的脚本，制造一批带主键和不带主键的表：
执行一下脚本：
现在执行以下 SQL 看看效果：
...
执行了 16.80s，感觉是非常慢了。
现在用一下 DBA 三板斧，看看执行计划：
感觉有点惨，由于 information_schema.columns 是元数据表，没有必要的统计信息。
那我们来 show warnings 看看 MySQL 改写后的 SQL：
我们格式化一下 SQL：
可以看到 MySQL 将
select from A where A.x not in (select x from B) //非关联子查询
转换成了
select from A where not exists (select 1 from B where B.x = a.x) //关联子查询
如果我们自己是 MySQL，在执行非关联子查询时，可以使用很简单的策略：
select from A where A.x not in (select x from B where ...) //非关联子查询:1. 扫描 B 表中的所有记录，找到满足条件的记录，存放在临时表 C 中，建好索引2. 扫描 A 表中的记录，与临时表 C 中的记录进行比对，直接在索引里比对，
而关联子查询就需要循环迭代：
select from A where not exists (select 1 from B where B.x = a.x and ...) //关联子查询扫描 A 表的每一条记录 rA： 扫描 B 表，找到其中的第一条满足 rA 条件的记录。
显然，关联子查询的扫描成本会高于非关联子查询。
我们希望 MySQL 能先"缓存"子查询的结果（缓存这一步叫物化，MATERIALIZATION），但MySQL 认为不缓存更快，我们就需要给予 MySQL 一定指导。
...
可以看到执行时间变成了 0.67s。
整理
我们诊断的关键点如下：
\1. 对于 information_schema 中的元数据表，执行计划不能提供有效信息。
\2. 通过查看 MySQL 改写后的 SQL，我们猜测了优化器发生了误判。
\3. 我们增加了 hint，指导 MySQL 正确进行优化判断。
但目前我们的实验仅限于猜测，猜中了万事大吉，猜不中就无法做出好的诊断。