有一个超级大表,如何优化分页查询?
超级大表的分页优化分有以下两种方式:
- 数据库层面优化:利用子查询优化超多分页场景,比如:SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id ,先快速定位需要获取的 id 段,然后再关联查询。MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写,利用子查询先快速定位需要获取的 id 段,然后再关联查询,就是对分页进行 SQL 改写的具体实现;
- 程序层面优化:可以利用缓存把查询的结果缓存起来,这样再下一次查询的时候性能就非常高了。
线上修改表结构有哪些风险?
线上修改表结构有可能 MySQL 服务器阻塞,因为在执行 DML(select、update、delete、insert)操作时,会给表增加一个元数据锁,这个元数据锁是为了保证在查询期间表结构不会被修改,而执行修改表结构时,必须要等待元数据锁完成之后才能执行,这就可能造成数据库服务器的阻塞。
在 MySQL 5.6 开始提供了 online ddl 功能,允许一些 DDL(create table/view/index/syn/cluster)语句和 DML 语句并发,在 5.7 版本对 online ddl 又有了增强,这使得大部分 DDL 操作可以在线进行,详见:https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html,这使得在线上修改表结构的风险变的更大,如果在业务开发过程中必须在线修改表结构,可以参考以下方案:
-
尽量在业务量小的时间段进行;
-
查看官方文档,确认要做的表修改可以和 DML 并发,不会阻塞线上业务;
-
推荐使用 percona 公司的 pt-online-schema-change 工具,该工具被官方的 online ddl 更为强大,它的基本原理是:通过 insert…select… 语句进行一次全量拷贝,通过触发器记录表结构变更过程中产生的增量,从而达到表结构变更的目的。比如,要对 A 表进行变更,它的主要流程为:
1)创建目的表结构的空表 A_new;
2ÿ