All perguntas(dba)

Consegui reproduzir um problema de desempenho de consulta que descreveria como inesperado. Estou procurando uma resposta focada em internos.

Na minha máquina, a consulta a seguir faz uma verificação de índice clusterizado e leva cerca de 6,8 segundos de tempo de CPU:

SELECT ID1, ID2
FROM two_col_key_test WITH (FORCESCAN)
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

A consulta a seguir faz uma busca de índice clusterizado (a única diferença é remover a FORCESCANdica), mas leva cerca de 18,2 segundos de tempo de CPU:

SELECT ID1, ID2
FROM two_col_key_test
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

Os planos de consulta são bastante semelhantes. Para ambas as consultas, há 120000001 linhas lidas do índice clusterizado:

Estou no SQL Server 2017 CU 10. Aqui está o código para criar e preencher a two_col_key_testtabela:

drop table if exists dbo.two_col_key_test;

CREATE TABLE dbo.two_col_key_test (
    ID1 NVARCHAR(50) NOT NULL,
    ID2 NVARCHAR(50) NOT NULL,
    FILLER NVARCHAR(50),
    PRIMARY KEY (ID1, ID2)
);

DROP TABLE IF EXISTS #t;

SELECT TOP (4000) 0 ID INTO #t
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);


INSERT INTO dbo.two_col_key_test WITH (TABLOCK)
SELECT N'FILLER TEXT' + CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) > 8000000 THEN N' 2' ELSE N'' END
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, NULL
FROM #t t1
CROSS JOIN #t t2;

Espero uma resposta que faça mais do que relatórios de pilha de chamadas. Por exemplo, posso ver que sqlmin!TCValSSInRowExprFilter<231,0,0>::GetDataXleva significativamente mais ciclos de CPU na consulta lenta em comparação com a rápida:

Em vez de parar por aí, gostaria de entender o que é isso e por que há uma diferença tão grande entre as duas consultas.

Por que há uma grande diferença no tempo de CPU para essas duas consultas?

Eu estava tentando definir a senha para root. Quando eu corro:

mysql> SELECT * from mysql.user where User="root";

Isto mostra:

+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                       | N                | 2018-06-13 15:11:59   |              NULL | N              |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+

Este Documento diz que,

O plugin de autenticação nativa mysql_native_password é compatível com versões anteriores. Clientes mais antigos que não suportam plug-ins de autenticação usam o protocolo de autenticação nativo, para que possam se conectar a servidores que suportam autenticação conectável.

Mas tecnicamente não estou conseguindo muito. Tem que fazer alguma coisa com roota senha do usuário?

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';

e

ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>';

Faz alguma diferença?

Estou tentando criar uma coluna para minha tabela somente se ela não existir. Já pesquisei bastante mas ainda não encontrei nenhuma solução.

Isso é realmente possível criar Column condicionalmente?

Eu só ouvi falar de Robert Martin hoje, e parece que ele é uma figura notável no mundo do software, então não quero que meu título apareça como se fosse uma isca de clique ou eu colocando palavras em sua boca, mas isso é simplesmente como interpretei o que ouvi dele com minha experiência e compreensão limitadas.

Eu estava assistindo a um vídeo hoje (sobre arquitetura de software), sobre uma palestra de Robert C. Martin, e na segunda metade do vídeo, o tópico de bancos de dados era o foco principal.

Pelo que entendi do que ele disse, parecia que ele estava dizendo que os SSDs reduziriam a utilidade dos bancos de dados ( consideravelmente ).

Para explicar como cheguei a essa interpretação:

Ele discutiu como com HDDs/discos giratórios, a recuperação de dados é lenta. No entanto, hoje em dia usamos SSDs, observou ele. Ele começa com "RAM está chegando" e continua mencionando os discos RAM, mas depois diz que não pode chamá-lo de disco RAM, então recorre apenas a dizer RAM. Portanto, com a RAM, não precisamos dos índices, porque cada byte leva o mesmo tempo para ser obtido. ( este parágrafo foi parafraseado por mim )

Portanto, ele sugerindo RAM (como na memória do computador) como um substituto para os bancos de dados (como interpretei sua declaração) não faz sentido porque é como dizer que todos os registros são processados ​​na memória durante o tempo de vida de um aplicativo ( a menos que você extraia de um arquivo de disco sob demanda)

Então, comecei a pensar por RAM, ele quer dizer SSD. Então, nesse caso, ele está dizendo que os SSDs reduzem a utilidade dos bancos de dados. Ele até diz: "Se eu fosse o Oráculo, ficaria com medo. A própria base de por que existo está evaporando."

Pelo meu pouco conhecimento de SSDs, ao contrário dos HDDs, que são O(n)o tempo de busca (eu acho), os SSDs são próximos O(1)ou quase aleatórios. Então, a sugestão dele me interessou, porque nunca tinha pensado nisso dessa forma. A primeira vez que fui apresentado a bancos de dados alguns anos atrás, quando um professor estava descrevendo os benefícios sobre o sistema de arquivos regular, concluí que a função principal de um banco de dados é essencialmente ser um sistema de arquivos muito indexado (assim como otimizações, cache, acesso simultâneo, etc), portanto, se os índices não forem necessários no SSD, isso tornará os bancos de dados menos úteis.

Independentemente disso, porém, prefaciando que sou um newb, acho difícil acreditar que eles se tornaram menos úteis, já que todos ainda usam bancos de dados como o ponto principal de seu aplicativo, em vez de sistema de arquivos puro, e senti como se ele estivesse simplificando demais o papel dos bancos de dados.

Nota : eu assisti até o final para ter certeza de que ele não disse algo diferente.

Para referência: 42:22 é quando todo o tópico do banco de dados surge, 43:52 é quando ele começa com "Por que ainda temos bancos de dados"

Esta resposta diz que os SSDs aceleram consideravelmente os bancos de dados. Esta pergunta é sobre como a otimização é alterada.

Para TL;DR, minha pergunta, o advento do uso generalizado de SSD no mercado de servidores (seja futuro ou já ocorrido) reduz a utilidade dos bancos de dados?

Parecia que o que o apresentador estava tentando transmitir era que, com SSDs, é possível armazenar os dados no disco e não ter que se preocupar com a lentidão para recuperá-los, pois com HDDs mais antigos, como com SSDs, os tempos de busca estão próximos O(1)(Eu penso). Então, caso isso fosse verdade, isso hipoteticamente perderia uma das vantagens que tinha: a indexação, porque a vantagem de ter índices para tempos de busca mais rápidos acabou.

Tenho as seguintes tabelas,

CREATE TABLE users (id int PRIMARY KEY);

-- already exists with data
CREATE TABLE message ();

Como faço para alterar messagesa tabela de tal forma que,

1. uma nova coluna chamada senderé adicionada a ela
2. onde senderé uma chave estrangeira referenciando a userstabela

Isso não funcionou

# ALTER TABLE message ADD FOREIGN KEY (sender) REFERENCES users;
ERROR:  column "sender" referenced in foreign key constraint does not exist

Essa instrução também não cria a coluna?

Eu quero escrever uma consulta contra o jsonbtipo no Postgres que, dada uma matriz de IDs de clientes, encontre os grupos correspondentes.

Dada esta tabela de exemplo:

CREATE TABLE grp(d JSONB NOT NULL);

INSERT INTO grp VALUES
   ('{"name":"First","arr":["foo"], "customers":[{"id":"1", "name":"one"},{"id":"2", "name":"two"}]}')
 , ('{"name":"Second","arr":["foo","bar"], "customers":[{"id":"3", "name":"three"},{"id":"4", "name":"four"}]}')
 , ('{"name":"Third","arr":["bar","baz"], "customers":[{"id":"5", "name":"five"},{"id":"6", "name":"seven"}]}');

Encontrei uma pergunta semelhante ( PostgreSql JSONB SELECT contra vários valores ) e consegui alcançar o que quero em uma matriz simples usando esta consulta:

SELECT d FROM grp WHERE d->'arr' ?| ARRAY['foo', 'bar'];

No entanto, não consigo fazer funcionar quando array contém objetos JSON :

SELECT d FROM grp WHERE d->'customers' ?| ARRAY['{"id":"1"}', '{"id":"5"}'];

Aqui está o que eu espero da minha consulta:

grp "Primeiro" -> cliente "1"

grp "Terceiro" -> cliente "5"

Eu tenho duas tabelas employeee phones. Um funcionário pode ter de 0 a n números de telefone. Quero listar os nomes dos funcionários com seus números de telefone. Estou usando a consulta abaixo que funciona bem.

SELECT empname,array_agg(phonenumber) AS phonenumbers 
FROM employee LEFT OUTER JOIN phones ON employee.empid = phones.empid
GROUP BY employee.empid

A tabela de funcionários pode conter um grande número de linhas. Quero buscar apenas alguns funcionários de cada vez. Por exemplo, quero buscar 3 funcionários com seus números de telefone. Estou tentando executar esta consulta.

SELECT empname,array_agg(phonenumber) AS phonenumbers 
FROM 
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS employee 
LEFT OUTER JOIN phones ON employee.empid = phones.empid
GROUP BY employee.empid

Mas eu recebo esse erro. ERROR: column "employee.empname" must appear in the GROUP BY clause or be used in an aggregate function A única diferença entre duas consultas é que estou usando a subconsulta na última para limitar as linhas antes de ingressar. Como resolvo esse erro?

Temos um data warehouse com uma contagem de registros bastante grande (10 a 20 milhões de linhas) e geralmente executamos consultas que contam registros entre determinadas datas ou contam registros com determinados sinalizadores, por exemplo

SELECT
    f.IsFoo,
    COUNT(*) AS WidgetCount
FROM Widgets AS w
JOIN Flags AS f
    ON f.FlagId = w.FlagId
WHERE w.Date >= @startDate
GROUP BY f.IsFoo

O desempenho não é terrível, mas pode ser relativamente lento (talvez 10 segundos em um cache frio).

Recentemente, descobri que posso usar GROUP BYem exibições indexadas e tentei algo semelhante ao seguinte

CREATE VIEW TestView
WITH SCHEMABINDING
AS
    SELECT
        Date,
        FlagId,
        COUNT_BIG(*) AS WidgetCount
    FROM Widgets
    GROUP BY Date, FlagId;
GO

CREATE UNIQUE CLUSTERED INDEX PK_TestView ON TestView
(
    Date,
    FlagId
);

Como resultado, o desempenho da minha primeira consulta agora é < 100ms e a exibição e o índice resultantes são < 100k (embora nossa contagem de linhas seja grande, o intervalo de datas e IDs de sinalizador significa que essa exibição contém apenas 1.000 a 2.000 linhas).

Eu pensei que talvez isso prejudicasse o desempenho das gravações na tabela Widget, mas não - o desempenho das inserções e atualizações nesta tabela é praticamente inalterado, tanto quanto eu posso dizer (além disso, sendo um data warehouse, esta tabela é atualizada com pouca frequência de qualquer forma)

Para mim, isso parece bom demais para ser verdade - é? Com o que preciso ter cuidado ao usar exibições indexadas dessa maneira?

Eu tenho esse SQL:

CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB);

INSERT INTO test(data) VALUES
   ('{"parent":null,"children":[2,3]}'),
   ('{"parent":1,   "children":[4,5]}'),
   ('{"parent":1,   "children":[]}'),
   ('{"parent":2,   "children":[]}'),
   ('{"parent":2,   "children":[]}');

Isso daria:

 id |                 data                 
----+--------------------------------------
  1 | {"parent": null, "children": [2, 3]}
  2 | {"parent": 1, "children": [4, 5]}
  3 | {"parent": 1, "children": []}
  4 | {"parent": 2, "children": []}
  5 | {"parent": 2, "children": []}

Ao fazer um para muitos normal, mostraria algo assim:

SELECT * 
FROM test x1
  LEFT JOIN test x2
    ON x1.id = (x2.data->>'parent')::INT;
 id |                 data                 | id |               data                
----+--------------------------------------+----+-----------------------------------
  1 | {"parent": null, "children": [2, 3]} |  2 | {"parent": 1, "children": [4, 5]}
  1 | {"parent": null, "children": [2, 3]} |  3 | {"parent": 1, "children": []}
  2 | {"parent": 1, "children": [4, 5]}    |  4 | {"parent": 2, "children": []}
  2 | {"parent": 1, "children": [4, 5]}    |  5 | {"parent": 2, "children": []}
  5 | {"parent": 2, "children": []}        |    | 
  4 | {"parent": 2, "children": []}        |    | 
  3 | {"parent": 1, "children": []}        |    | 

Como participar com base em filhos (usando LEFT JOINou WHERE IN)? Eu tentei:

SELECT data->>'children' FROM test;
 ?column? 
----------
 [2, 3]
 [4, 5]
 []
 []
 []

SELECT json_array_elements((data->>'children')::TEXT) FROM t...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT json_array_elements((data->>'children')::JSONB) FROM ...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT json_to_record((data->>'children')::JSON) FROM test;
ERROR:  function returning record called in context that cannot accept type record
HINT:  Try calling the function in the FROM clause using a column definition list.

SELECT * FROM json_to_record((test.data->>'children')::JSON);
ERROR:  missing FROM-clause entry for table "test"
LINE 1: SELECT * FROM json_to_record((test.data->>'children')::JSON)...

Assim como o título diz, onde posso vê-lo?

Existem opções de configuração para ele (como quantos ms determinariam se uma consulta é lenta ou não)?