selecione * vs selecione a coluna

Se eu precisar apenas de 2/3 colunas e eu consultar SELECT * vez de fornecer essas colunas na consulta select, há alguma degradação de desempenho em relação a mais / menos E / S ou memory?

A sobrecarga de rede pode estar presente se eu selecionar * sem necessidade.

Mas, em uma operação de seleção, o mecanismo de database sempre extrai a tupla atômica do disco ou puxa apenas as colunas solicitadas na operação de seleção?

Se sempre puxar uma tupla, a sobrecarga de E / S será a mesma.

Ao mesmo tempo, pode haver um consumo de memory para remover as colunas solicitadas da tupla, se ela extrair uma tupla.

Então, se esse for o caso, selecione someColumn terá mais sobrecarga de memory que a de select *

Ele sempre extrai uma tupla (exceto nos casos em que a tabela foi segmentada verticalmente – dividida em colunas), portanto, para responder à pergunta que você fez, não importa de uma perspectiva de desempenho. No entanto, por muitas outras razões, (abaixo) você deve sempre selecionar especificamente as colunas que deseja, por nome.

Ele sempre puxa uma tupla, porque (em cada fornecedor RDBMS eu estou familiarizado, a estrutura de armazenamento em disco subjacente para tudo (incluindo dados de tabela) é baseada em páginas de E / S definidas (no SQL Server por exemplo, cada página é 8 E cada leitura / escrita de E / S é feita por Page .. Ou seja, cada escrita ou leitura é uma página completa de dados.

Devido a essa restrição estrutural subjacente, uma consequência é que Cada linha de dados em um database deve estar sempre em uma e somente uma página. Ele não pode abranger várias páginas de dados (exceto para coisas especiais como blobs, onde os dados de blob reais são armazenados em blocos de páginas separados, e a coluna de linha de tabela real só recebe um ponteiro …). Mas essas exceções são apenas exceções, e geralmente não se aplicam, exceto em casos especiais (para tipos especiais de dados ou certas otimizações para circunstâncias especiais)
Mesmo nesses casos especiais, geralmente, a própria linha da tabela de dados (que contém o ponteiro para os dados reais para o Blob, ou qualquer outro), ele deve ser armazenado em uma única página de E / S …

EXCEÇÃO. O único local em que Select * está OK, está na subconsulta após uma cláusula de predicado Exists ou Not Exists , como em:

  Select colA, colB From table1 t1 Where Exists (Select * From Table2 Where column = t1.colA) 

EDIT: Para endereço @Mike comentário Sherer, Sim, é verdade, tanto tecnicamente, com um pouco de definição para o seu caso especial e esteticamente. Primeiro, mesmo quando o conjunto de colunas solicitado é um subconjunto daqueles armazenados em algum índice, o processador de consultas deve buscar todas as colunas armazenadas nesse índice, não apenas as solicitadas, pelas mesmas razões – TODAS as E / S devem ser feitas em páginas e dados de índice são armazenados em páginas IO, assim como os dados da tabela. Portanto, se você definir “tuple” para uma página de índice como o conjunto de colunas armazenadas no índice, a instrução ainda será verdadeira.
e a afirmação é verdadeira esteticamente porque o ponto é que ela busca dados com base no que está armazenado na página de E / S, não no que você pede, e isso é verdade se você está acessando a página de E / S da tabela base ou um índice Página de E / S.

Por outras razões, para não usar Select * , consulte Por que o SELECT * considerado prejudicial? :

Existem várias razões pelas quais você nunca deve (nunca) usar o SELECT * no código de produção:

  • Como você não está dando ao seu database nenhuma pista sobre o que deseja, primeiro precisará verificar a definição da tabela para determinar as colunas nessa tabela. Essa pesquisa vai custar algum tempo – não muito em uma única consulta – mas aumenta com o tempo

  • Se você precisa apenas de 2/3 das colunas, você está selecionando 1/3 demais dados que precisam ser recuperados do disco e enviados pela rede.

  • Se você começar a confiar em certos aspectos dos dados, por exemplo, a ordem das colunas retornadas, você poderá ter uma surpresa desagradável quando a tabela for reorganizada e novas colunas forem adicionadas (ou as existentes forem removidas)

  • no SQL Server (não tenho certeza sobre outros bancos de dados), se você precisar de um subconjunto de colunas, sempre há uma chance de um índice não agrupado estar cobrindo essa solicitação (conter todas as colunas necessárias). Com um SELECT * , você está desistindo dessa possibilidade desde o início. Nesse caso específico, os dados seriam recuperados das páginas de índice (se contiverem todas as colunas necessárias) e, portanto, a sobrecarga de memory e E / S de disco seria muito menor se comparada a uma consulta SELECT *....

Sim, é preciso digitar um pouco mais inicialmente (ferramentas como SQL Prompt para SQL Server até mesmo o ajudarão lá) – mas esse é realmente um caso em que há uma regra sem exceção: nunca use SELECT * em seu código de produção. SEMPRE.

Você deve sempre select apenas as colunas que você realmente precisa. Nunca é menos eficiente selecionar menos em vez de mais, e você também tem menos efeitos colaterais inesperados – como acessar as colunas de resultados no lado do cliente por índice, e depois ter esses índices incorretos adicionando uma nova coluna à tabela.

[edit]: significava acessar. Cérebro estupido ainda acordando.

A menos que você esteja armazenando grandes bolhas, o desempenho não é uma preocupação. O grande motivo para não usar SELECT * é que, se você estiver usando linhas retornadas como tuplas, as colunas retornarão em qualquer ordem que o esquema especificar e, se isso mudar, você terá que corrigir todo o seu código.

Por outro lado, se você usar o access ao estilo de dictionary, não importa em qual ordem as colunas retornam, pois você sempre as acessa pelo nome.

Isso imediatamente me faz pensar em uma tabela que eu estava usando que continha uma coluna de tipo blob ; normalmente continha uma imagem JPEG, com alguns Mb s de tamanho.

Escusado será dizer que eu não SELECT essa coluna a menos que eu realmente precisasse. Ter esses dados flutuando – especialmente quando eu selecionei mulitple rows – foi apenas um incômodo.

No entanto, admito que, de outra forma, eu normalmente consultaria todas as colunas de uma tabela.

Durante uma seleção de SQL, o database sempre se referirá aos metadados da tabela, independentemente de ser SELECT * para SELECT a, b, c … Por quê? Porque é aí que a informação sobre a estrutura e layout da tabela no sistema é.

Tem que ler esta informação por duas razões. Um, para simplesmente compilar a declaração. É necessário que você especifique uma tabela existente no mínimo. Além disso, a estrutura do database pode ter sido alterada desde a última vez que uma instrução foi executada.

Agora, obviamente, os metadados do database são armazenados em cache no sistema, mas ainda precisam ser processados.

Em seguida, os metadados são usados ​​para gerar o plano de consulta. Isso acontece toda vez que uma declaração é compilada também. Novamente, isso é executado em metadados armazenados em cache, mas sempre é feito.

A única vez que esse processamento não é feito é quando o DB está usando uma consulta pré-compilada ou armazenou em cache uma consulta anterior. Este é o argumento para usar parâmetros de binding em vez de SQL literal. “SELECT * FROM TABLE ONDE key = 1” é uma consulta diferente de “SELECT * FROM TABLE WHERE key =?” e o “1” é ligado na chamada.

Os bancos de dados dependem muito do cache de páginas para o trabalho. Muitos bancos de dados modernos são pequenos o suficiente para caber completamente na memory (ou, talvez eu deva dizer, a memory moderna é grande o suficiente para caber em muitos bancos de dados). Em seguida, seu custo de E / S principal no back-end é o log e a limpeza da página.

No entanto, se você ainda estiver acessando o disco para seu database, uma otimização primária feita por muitos sistemas é confiar nos dados em índices, em vez das próprias tabelas.

Se você tem:

 CREATE TABLE customer ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(150) NOT NULL, city VARCHAR(30), state VARCHAR(30), zip VARCHAR(10)); CREATE INDEX k1_customer ON customer(id, name); 

Então, se você fizer “SELECT id, nome do cliente WHERE id = 1”, é muito provável que o seu database extrairá esses dados do índice, e não das tabelas.

Por quê? Ele provavelmente usará o índice para satisfazer a consulta (versus uma varredura de tabela) e, embora ‘nome’ não seja usado na cláusula where, esse índice ainda será a melhor opção para a consulta.

Agora, o database tem todos os dados necessários para satisfazer a consulta, portanto, não há motivo para acertar as páginas da tabela. Usar o índice resulta em menos tráfego de disco, pois você tem uma densidade mais alta de linhas no índice em relação à tabela em geral.

Esta é uma explicação detalhada de uma técnica de otimização específica usada por alguns bancos de dados. Muitos possuem várias técnicas de otimização e ajuste.

No final, o SELECT * é útil para consultas dinâmicas que você precisa digitar à mão. Eu nunca o usaria para “código real”. A identificação de colunas individuais dá ao database mais informações que ele pode usar para otimizar a consulta e oferece a você um melhor controle em seu código em relação às alterações de esquema, etc.

Eu acho que não há resposta exata para a sua pergunta, porque você tem ponderando sobre o desempenho e a facilidade de manter seus aplicativos. Select column é mais performática do select * , mas se você está desenvolvendo um sistema de object orientado, então você vai gostar de usar o object.properties e você pode precisar de uma propriedade em qualquer parte dos apps, então você precisará escrever mais methods para obter propriedades em situações especiais se você não usar select * e preencher todas as propriedades. Seus aplicativos precisam ter um bom desempenho usando select * e, em alguns casos, você precisará usar a coluna de seleção para melhorar o desempenho. Então você terá o melhor de dois mundos, facilidade para escrever e manter aplicativos e desempenho quando precisar de desempenho.

A resposta aceita aqui está errada. Eu me deparei com isso quando outra pergunta foi encerrada como uma duplicata disso (enquanto eu ainda estava escrevendo minha resposta – grr -, portanto, o SQL abaixo faz referência à outra pergunta).

Você deve sempre usar o atributo SELECT, atributo …. NOT SELECT *

É principalmente para problemas de desempenho.

SELECT nome FROM usuários WHERE name = ‘John’;

Não é um exemplo muito útil. Considere, em vez disso:

 SELECT telephone FROM users WHERE name='John'; 

Se houver um índice em (nome, telefone), a consulta poderá ser resolvida sem ter que procurar os valores relevantes da tabela – há um índice de cobertura .

Além disso, suponha que a tabela tenha um BLOB contendo uma imagem do usuário, e um currículo carregado, e uma planilha … usando SELECT * irá enviar todas essas informações de volta para os buffers do DBMS (forçando outras informações úteis do cache). Em seguida, tudo será enviado para o cliente, usando o tempo na rede e a memory no cliente para dados redundantes.

Ele também pode causar problemas funcionais se o cliente recuperar os dados como uma matriz enumerada (como o mysql_fetch_array ($ x, MYSQL_NUM) do PHP). Talvez quando o código foi escrito ‘telefone’ foi a terceira coluna a ser retornada pelo SELECT *, mas então alguém vem e decide adicionar um endereço de e-mail à mesa, posicionado antes de ‘telefone’. O campo desejado é agora deslocado para a quarta coluna.

Existem razões para fazer as coisas de qualquer maneira. Eu uso bastante o SELECT * no PostgreSQL porque existem muitas coisas que você pode fazer com o SELECT * no PostgreSQL que você não pode fazer com uma lista explícita de colunas, particularmente quando em stored procedures. Da mesma forma, no Informix, SELECT * em uma tree de tabelas herdada pode fornecer linhas irregulares, enquanto uma lista de colunas explícitas não pode, porque colunas adicionais em tabelas filhas também são retornadas.

A principal razão pela qual eu faço isso no PostgreSQL é que ele garante que eu obtenha um tipo bem formado específico para uma tabela. Isso me permite obter os resultados e usá-los como o tipo de tabela no PostgreSQL. Isso também permite muito mais opções na consulta do que uma lista de colunas rígidas.

Por outro lado, uma lista de colunas rígidas oferece uma verificação em nível de aplicativo de que os esquemas de database não foram alterados de determinadas maneiras, e isso pode ser útil. (Eu faço essas verificações em outro nível.)

Quanto ao desempenho, tendem a usar VIEWs e stored procedures retornando tipos (e, em seguida, uma lista de colunas dentro do procedimento armazenado). Isso me dá controle sobre quais tipos são retornados.

Mas tenha em mente que estou usando SELECT * geralmente contra uma camada de abstração, em vez de tabelas de base.

Referência retirada deste artigo:

Sem SELECT *: quando você está usando “SELECT *” nesse momento, você está selecionando mais colunas do database e parte dessa coluna pode não ser usada pelo seu aplicativo. Isso criará custos e carregamentos extras no sistema de database e mais dados percorrerão a rede.

Com SELECT *: Se você possui requisitos especiais e criou um ambiente dynamic quando a coluna adicionar ou excluir automaticamente manipula pelo código do aplicativo. Neste caso especial, você não precisa alterar o código do aplicativo e do database e isso afetará automaticamente o ambiente de produção. Neste caso, você pode usar “SELECT *”.

Apenas para adicionar uma nuance à discussão que eu não vejo aqui: Em termos de I / O, se você estiver usando um database com armazenamento orientado por colunas, você pode fazer muito menos I / O se você consultar apenas colunas. À medida que nos movemos para SSDs, os benefícios podem ser um pouco menores em relação ao armazenamento orientado a linhas, mas há a) apenas a leitura dos blocos que contêm colunas que interessam b) compactação, que geralmente reduz muito o tamanho dos dados no disco e volume de dados lidos do disco.

Se você não está familiarizado com o armazenamento orientado por colunas, uma implementação para o Postgres vem do Citus Data, outro é o Greenplum, outro Paraccel, outro (vagamente falando) é o Amazon Redshift. Para o MySQL, existe Infobright, o InfiniDB, agora quase extinto. Outras ofertas comerciais incluem Vertica da HP, Sybase IQ, Teradata …

 select * from table1 INTERSECT select * from table2 

igual

 select distinct t1 from table1 where Exists (select t2 from table2 where table1.t1 = t2 )