如何在Databricks快速读取system.information_schema.columns系统表？

1. 类似Oracle WITH(NOLOCK)的锁规避方法 #

Oracle的WITH(NOLOCK)本质是通过脏读减少锁等待，不同数据库有对应的实现：

• SQL Server：直接给表加WITH(NOLOCK)提示，或者全局设置会话隔离级别为READ UNCOMMITTED

  -- 单查询加锁提示
  SELECT * FROM system.information_schema.columns WITH(NOLOCK);
  
  -- 会话级设置，后续所有查询都生效
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  SELECT * FROM system.information_schema.columns;
  

• MySQL/MariaDB：设置会话隔离级别为READ UNCOMMITTED，部分版本支持/*+ NOLOCK */优化器提示

  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  SELECT * FROM information_schema.columns;
  
  -- 单查询提示（需确认版本兼容性）
  SELECT /*+ NOLOCK */ * FROM information_schema.columns;
  

• PostgreSQL：PG的READ UNCOMMITTED实际会降级为READ COMMITTED，可以用READ ONLY NOT DEFERRABLE减少锁竞争，或者直接查询底层系统表（性能远优于information_schema视图）

  SET TRANSACTION READ ONLY NOT DEFERRABLE;
  SELECT * FROM information_schema.columns;
  
  -- 直接查底层表的更快写法
  SELECT a.attname AS column_name, c.relname AS table_name, n.nspname AS table_schema
  FROM pg_catalog.pg_attribute a
  JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
  JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
  WHERE a.attnum > 0 AND NOT a.attisdropped;
  

2. 额外性能优化技巧 #

• 只查需要的字段：别用SELECT *，只提取你实际要用的列（比如column_name, table_name, data_type），大幅减少数据传输量
• 分批读取：如果不需要一次性获取全量数据，用分页分批拉取，避免单次查询负载过高

  -- SQL Server分页示例
  SELECT column_name, table_name
  FROM system.information_schema.columns WITH(NOLOCK)
  ORDER BY table_schema, table_name, column_name
  OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY;
  

• 用原生系统表替代information_schema：information_schema是标准视图，会做大量关联和转换，直接查数据库原生系统表（比如SQL Server的sys.columns）性能会提升很多

  -- SQL Server原生表查询示例
  SELECT c.name AS column_name, t.name AS table_name, s.name AS table_schema
  FROM sys.columns c
  JOIN sys.tables t ON c.object_id = t.object_id
  JOIN sys.schemas s ON t.schema_id = s.schema_id
  WITH(NOLOCK);
  

内容的提问来源于stack exchange，提问作者drvenom5140