MySQL如何优化超大的分页查询？ 前文在你开发程序的时候，使用 LIMIT 子句做分页查询可能是非常频繁的，这是一个非常常见的业务场景。那你在使用 limit 子句的时候有没有遇到过什么问题呢，比如说性能不好？曾经遇到过不少由于分页查询性能差，需要优化的案例。那常见的原因基本上也都是分页过多。过大的分页查询为什么会慢？下面我们先构造一个例子：/**生成3百万行的测试数据**/ CREATE TABLE `limit_optimize_tbl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account` varchar(50) NOT NULL, `order_id` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2000002 DEFAULT CHARSET=utf8 create index idx_account on limit_optimize_tbl(account); create index idx_order_id on limit_optimize_tbl(order_id); DELIMITER CREATE PROCEDURE limit_optimize_test() BEGIN DECLARE i INT; SET i=1000000; WHILE i<=3000000 DO INSERT INTO limit_optimize_tbl(account,order_id) VALUES('test_123',concat('order', i)); SET i=i+1; END WHILE; END DELIMITER ; call limit_optimize_test();下面的这行 SQL 是一个常见的分页查询的语句：mysql> select * from limit_optimize_tbl order by order_id limit 1000000,10;这种写法是最简单的，但同时也是最容易出问题的。曾经有人做过调查，用户在浏览前端页面时，假如这个页面是分页浏览的（例如淘宝），用户只会浏览前面几页，一般翻页超过 10 页很多人就开始表现的不耐烦了。在翻页比较少的情况下，LIMIT 子句并不会表现出性能问题。但是假如用户要直接跳到最后一页呢？通常情况下，由于要保证所有的页面都可以正常跳转，因此可能不会使用如下这种语句：mysql> select * from limit_optimize_tbl order by order_id desc limit 0,10;而是继续采用正序顺序做分页查询：mysql> select * from limit_optimize_tbl order by order_id limit 1000000,10;采用这种 SQL 查询的话，此时从 MySQL 中取出这 10 行数据的代价是非常大的，需要先排序出前面 1000010 条记录，然后抛弃前面的 1000000 条。查询数据和排序的代价非常高。我们再来看一下上面这个 SQL 语句的执行计划：mysql> explain select * from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select * from limit_optimize_tbl order by order_id limit 1000000,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1994816 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec)从执行计划中你可以看到，在大分页的时候，MySQL 并没有走索引扫描，而是使用了全表扫描的方式。那这又是为什么呢？MySQL 数据库采用了基于代价的查询优化器，而查询代价的估算是基于 CPU 代价和 IO 代价。由于现在机械硬盘还没有被完全淘汰掉，因此在类似这种局部扫描的动作中，随机 IO 的代价，仍然被MySQL 的查询优化器认为是非常高的。对于局部扫描，MySQL 会根据数据量的情况和数据获取的条件，去做代价估算，决定是采用顺序扫描还是随机读取存储系统。如果 MySQL 在查询代价估算中，认为采取顺序扫描方式比局部随机扫描的效率更高的话，就会放弃索引，转向顺序扫描的方式。这就是为什么在大分页中 MySQL 数据库走了全表扫描的原因。下面我们还是使用刚刚的 SQL 语句，再来实验一下：mysql> explain select * from limit_optimize_tbl order by order_id limit 5660,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 5670 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select * from limit_optimize_tbl order by order_id limit 5661,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1994816 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec) 从上面的实验你可以看到2条SQL，在这个临界点上，MySQL 分别采用了索引扫描和全表扫描的查询优化方式。你可以自行运行下这两个 SQL 语句，比较下执行时间。由于 MySQL 的查询优化器的算法核心我们是无法人工干预的，因此我们的优化思路应该着眼于如何让分页维持在最佳的效率区间。下面我们就来看下如何优化。优化可以从两个角度进行分析：SQL 改写优化业务角度优化。SQL改写优化方案1：索引覆盖由于索引是有序的，因此这种优化方式的思路是直接在索引上完成排序和分页的操作。先来说说什么是覆盖索引。如果一个 SQL 语句，通过索引即可直接获取查询结果，而不再需要回表查询，就称这个索引覆盖了这条SQL 语句。也就是平时所说的不需要回表操作。在 MySQL 数据库中使用执行计划查看，如果 extra 这一列中显示 Using index ，就表示这条 SQL 语句使用了覆盖索引。下面我们看下刚刚的那条 SQL 语句：mysql> explain select * from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select order_id from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) mysql> explain select id from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec)从这个实验中可以看到，除了 select * 的那条 SQL 语句，其他两个都使用了覆盖索引。你也可以对比一下使用了覆盖索引的大分页和和没有使用覆盖索引的性能差异。在我的环境中执行实验中的 “LIMIT 1000000,10” 的分页查询，没有使用覆盖索引的 SQL 语句执行时间是 2.51s使用了覆盖索引的 SQL 语句执行时间是 0.16s优化效果还是非常明显的。方案2：子查询优化由于在 SELECT 语句中我们很少会只查询某一两个列，因此上述覆盖索引的适用范围就比较有限。可以通过将分页的 SQL 语句改写成子查询的方法获得性能的提升。示例如下：mysql> select * from limit_optimize_tbl where id >= (select id from limit_optimize_tbl order by order_id limit 1000000,1) limit 10; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.16 sec)执行时间和上一节的使用了覆盖索引的 SQL 语句基本一致。不知道你有没有观察到，这种优化方法也有其局限性：首先，分页的数据必须是连续的其次，WHERE 子句里面不能再添加别的条件方案3：延迟关联和上述子查询的做法类似，我们也可以使用 JOIN 的语法，先在索引上完成分页的操作，然后再回表获取需要的数据列。示例如下：mysql> select a.* from limit_optimize_tbl a inner join (select id from limit_optimize_tbl order by order_id limit 1000000,10) b on a.id=b.id; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.15 sec)你可以和上一小节的子查询优化的方式做个对比，在采用了 JOIN 语法改写之后，上面的两个限制都解除了，并且 SQL 的执行效率没有损失方案4： 记录书签和上述使用覆盖索引的思路不同，记录书签的优化思路是使用书签记录上一页数据的位置，下次分页时直接从这个书签的位置开始扫描，从而避免 MySQL 扫描大量的数据行再丢弃的操作。示例如下：mysql> select * from limit_optimize_tbl where id>=1000001 limit 0,10; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.00 sec)从上面的 SQL 语句你可以看到，由于使用了主键索引来做分页的操作，SQL 语句的性能是极佳的。使用其他列做书签也是可以的：mysql> select * from limit_optimize_tbl where order_id>='order2000000' limit 0,10; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.01 sec)这里要给你提个醒，如果没有使用主键索引或唯一索引做这个书签，排序的字段有大量重复值的情况下，输出的结果不一定是准确的，不适合使用这种写法。方案5： 反向查找反向查找即我们在本文的开头提到的例子：mysql> select * from limit_optimize_tbl order by order_id desc limit 0,10;这种优化的思路来自于二分查找，也就是说，当偏移量超过记录数的一半时，就可以使用这种写法来获得性能的提升不过这种方法需要在分页前知道符合条件的总的记录条数，但是在 InnoDB 存储引擎中，COUNT (*) 的开销其实也不小。因此建议你仅在一些特殊情况下选用，例如直接跳到尾页。业务角度优化下面我们一起来看看从业务角度如何优化大分页查询。其实这个优化思路要看具体的业务内容，业务是千变万化的，因此本文中提到的这几个案例，不一定就适合你们公司的业务类型，只是起个抛砖引玉的作用。翻页限制不允许翻过多的页 , 一言以蔽之，就是不给你查了。把 LIMIT 分页的偏移量做一个限制，超过某个阈值就停止。我们以淘宝网为例，使用比较热门的 “男鞋” 的关键词进行搜索，网站仅仅提供了 100 个数据页。很多大型互联网公司由于数据量巨大，都有使用这种方法。流式分页这种分页方式比较适用于移动端，即只能一页一页的向前或向后加载，不提供跳转的功能。可以在上一级入口中提供业务列表给用户选择，从而减少分页。这种分页方式在电商和新闻类 APP 上应用的非常广泛，你也可以试试。总结总的来说，大分页的优化思路就是让分页的SQL语句尽量在最佳的性能区间内执行，不要触发全表顺序扫描，也不要扫描太多的数据行。SQL 的优化有两个方向，一个是 SQL 语义的优化，另一个是从业务角度对 SQL 语句进行优化。SQL 语义的优化所能发挥的功力有限，在 SQL 优化的工作中大概只有3成的SQL 能通过 SQL 的改写完成优化；而从业务角度的调优占了有 7 成。因此在对 SQL 语句进行优化时，不妨多从业务角度入手，想想看有没有好的解决方案。参考资料《数据库查询优化器的艺术》，李海翔著《高性能 MySQL》第三版慕课专栏-MySQL 开发高频面试题精选

MySQL单表多次查询和多表联合查询，哪个效率高？ 很多高性能的应用都会对关联查询进行分解。简单地，可以对每个表进行一次单表查询，然后将结果在应用程序中进行关联。例如，下面这个查询：select * from tag join tag_post on tag_post.tag_id=tag.id join post on tag_post.post_id=post.id where tag.tag='mysql';可以分解成下面这些查询来代替：Select * from tag where tag=’mysql’; Select * from tag_post where tag_id=1234; Select * from post where id in(123,456,567,9989,8909);到底为什么要这样做？咋一看，这样做并没有什么好处，原本一条查询，这里却变成了多条查询，返回结果又是一模一样。事实上，用分解关联查询的方式重构查询具有如下优势：让缓存的效率更高许多应用程序可以方便地缓存单表查询对应的结果对象。另外对于MySQL的查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。将查询分解后，执行单个查询可以减少锁的竞争在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。查询本身效率也可能会有所提升可以减少冗余记录的查询。更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套环关联，某些场景哈希关联的效率更高很多。

mysql使用or条件使索引失效 原因很多人都喜欢用 OR 去解决，虽然语句简单，但是就会发生下面的场景，容易走不到索引失效指的是，条件中如果有or，只要其中一个条件没有索引，其他字段有索引也不会使用。原sql语句SELECT `user`, `check_money`, `is_freeze_up`, `freeze_up_month`, `freeze_money`, `freeze_up_time`, `check_money_2_month`, `check_money_2`, `check_money_2_time`, `tc_qr`, `log_id`, 1 AS type, `branch` FROM `pxs_custom_log_bv_sale` `s` LEFT JOIN `pxs_custom_money_log` `l` ON `s`.`log_id` = `l`.`id` WHERE ( l.lock_month = '2020-11' OR s.freeze_up_month = '2020-11' OR s.check_money_2_month = '2020-11' ) 因为表设计这种结构并且要求这种业务场景。这里的lock_month 和freeze_up_month、check_money_2_month虽然都加了索引，但是EXPLAIN分析下， type是 all ，还是走全表扫描（ALL），非常慢。解决方案把3个where的字段分开几个sql段。使用union或者union all去连接多个 SELECT 语句的结果组合到一个结果集合中 。 EXPLAIN SELECT `user`, `check_money`, `is_freeze_up`, `freeze_up_month`, `freeze_money`, `freeze_up_time`, `check_money_2_month`, `check_money_2`, `check_money_2_time`, `tc_qr`, `log_id`, 1 AS type, `branch` FROM `pxs_custom_log_bv_sale` `s` LEFT JOIN `pxs_custom_money_log` `l` ON `s`.`log_id` = `l`.`id` WHERE ( l.lock_month = '2020-11') UNION all SELECT `user`, `check_money`, `is_freeze_up`, `freeze_up_month`, `freeze_money`, `freeze_up_time`, `check_money_2_month`, `check_money_2`, `check_money_2_time`, `tc_qr`, `log_id`, 1 AS type, `branch` FROM `pxs_custom_log_bv_sale` `s` LEFT JOIN `pxs_custom_money_log` `l` ON `s`.`log_id` = `l`.`id` WHERE s.freeze_up_month = '2020-11' union all SELECT `user`, `check_money`, `is_freeze_up`, `freeze_up_month`, `freeze_money`, `freeze_up_time`, `check_money_2_month`, `check_money_2`, `check_money_2_time`, `tc_qr`, `log_id`, 1 AS type, `branch` FROM `pxs_custom_log_bv_sale` `s` LEFT JOIN `pxs_custom_money_log` `l` ON `s`.`log_id` = `l`.`id` WHERE s.check_money_2_month = '2020-11'执行结果再看，这个时候type 是range级别,这样在生产环境上测试，会比原先快很多。解决思路优化的方法是改成union分成多个 sql，走各自的索引。如果可能有重复就用union， 不用union all。or 条件在数据库上很难优化，能在代码里优化逻辑，不至于拖垮数据库。只有在 or 条件下无需索引时（且需要比较的数据量小），才考虑。相同字段or可改成in，如 f_id=1 or f_id=100 -> f_id in (1,100)。上述优化情景都是存储引擎在 InnoDB 情况下，在MyISAM有不同