Estimando a distribuição de acesso de linha no mysql

Se você deseja esse tipo de estatística granular por id, parece que você pode tentar algo um pouco complicado para coletar essas informações. Vamos explorar este cenário:

Se você tiver uma tabela com o seguinte layout no banco de dados wp:

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  `post_password` varchar(20) NOT NULL DEFAULT '',
  `post_name` varchar(200) NOT NULL DEFAULT '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

e você deseja rastrear o acesso a esta tabela, você deve forçar a gravação do id da tabela usando uma série de operações que combinam o uso de Triggers , tabelas BLACKHOLE , MySQL Replication e Codificação "Socialmente Responsável".

GATILHOS

Os gatilhos seguem o curso de qualquer INSERT, UPDATE e DELETE. Neste cenário, você precisará de três (3) tipos de Triggers: 1) Após INSERT, 2) Após UPDATE, 3) Após DELETE.

Cada vez que wp_posts tiver um INSERT, UPDATE ou DELETE, registre o ID em outra tabela. Que tipo de mesa???

TABELAS DE BLACKHOLE

Vamos criar uma tabela para gravar IDs

CREATE TABLE wp_posts_idtracker
(
  `ID` bigint(20) unsigned NOT NULL,
  `DTSTAMP` datetime NOT NULL
) ENGINE=BLACKHOLE;

Agora aqui estão os gatilhos para registrar os acessos de ID:

CREATE TRIGGER wp_posts_insertafter AFTER INSERT ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (NEW.ID,NOW());

CREATE TRIGGER wp_posts_updateafter AFTER UPDATE ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (OLD.ID,NOW());

CREATE TRIGGER wp_posts_deleteafter AFTER DELETE ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (OLD.ID,NOW());

ESPERE UM MINUTO !!! A tabela wp_posts_idtracker é uma tabela BLACKHOLE . Não armazena nada. Então, onde as estatísticas são escritas ??? Certifique-se de que a instância do MySQL com wp_posts tenha registro binário ativado. Grande coisa, as estatísticas são gravadas nos logs binários. Como você pode ler essas estatísticas ???

REPLICAÇÃO do MySQL

Usando hardware comum, use um MySQL Replication Slave. Configure o escravo para aceitar apenas uma tabela: wp_posts_idtracker. Coloque esta linha em /etc/my.cnf no slave:

[mysqld]
replicate-do-table=wp.wp_posts_idtracker

Uma vez que esta tabela BLACKHOLE seria replicada para o escravo, como ela armazenaria os dados ??? Converta-o para MyISAM no escravo. Além disso, indexe-o por ID e DTSTAMP e por DTSTAMP:

use wp
ALTER TABLE wp_posts_idtracker ENGINE=MyISAM;
ALTER TABLE wp_posts_idtracker ADD INDEX (ID,DTSTAMP);
ALTER TABLE wp_posts_idtracker ADD INDEX (DTSTAMP);

Agora o Master simplesmente registrará cada acesso a uma linha em wp_posts nos logs binários. O MySQL Replicaton assume a responsabilidade de gravar isso no Slave. Como alternativa ao uso de um servidor separado para registrar essas informações, você pode querer criar uma segunda instância do MySQL na porta 3307 e fazer com que ela atue como escravo do MySQL rodando na porta 3306. Você deve certificar-se de que o datadir do MySQL na porta 3307 está em um volume de dados separado daquele do MySQL na porta 3306. Outra variação seria alterar o mecanismo de armazenamento para wp_posts_idtracker no escravo com o mecanismo de armazenamento MEMORY para reduzir a E/S do disco (Cuidado: se você usar o armazenamento MEMORY motorPara a tabela wp_posts_idtracker, lembre-se de fazer os índices BTREE em vez do índice HASH padrão porque a execução de consultas de intervalo em uma tabela indexada por HASH tem um desempenho horrível, mesmo para uma tabela MEMORY ). Ainda outra variação seria colocar os logs binários em um disco RAM para uma replicação ainda mais rápida ou colocar os logs de retransmissão também em um disco RAM, além de reduzir ainda mais a E/S do disco.

Até agora, os IDs envolvidos em INSERTs, UPDATEs e DELETEs são armazenados com segurança em um Replication Slave. Estamos esquecendo algum outro tipo de acesso ao wp_posts ??? Ah, sim, instruções SELECT. Como gravamos SELECTs??? Não há gatilhos para SELECTs em nenhum RDBMS conhecido. Como lidamos com SELECTs???

CODIFICAÇÃO "SOCIALMENTE RESPONSÁVEL"

Como não há mecanismos especiais para SELECTs, o desenvolvedor deve assumir a responsabilidade pessoal de obter os IDs encontrados nas consultas SELECT e simplesmente INSERIR na tabela wp.wp_posts_idtracker. Suponha que o ID que você está solicitando venha de uma coleta em massa de IDs. Envie-os em massa para wp.wp_posts_idtracker:

SELECT * FROM wp_posts WHERE ...
INSERT INTO wp.wp_posts_idtracker SELECT IDs,NOW() FROM wp_posts WHERE ...

Não se preocupe com a consulta INSERT causando acesso de E/S de disco aos IDs. Esses IDs devem ser armazenados em cache após SELECT. Se wp_posts for MyISAM, as chaves seriam armazenadas em cache no cache de chaves (dimensionadas por key_buffer_size). Se wp_posts for InnoDB, as chaves seriam armazenadas em cache no buffer pool innodb (dimensionado por innodb_buffer_pool_size) .

RESUMO

Depois de empregar essa infraestrutura única, você pode simplesmente conectar-se ao escravo e ler suas estatísticas.

Este foi apenas um exemplo de como criar o tipo de registro de estatísticas que você deseja para o ID de uma mesa. Você pode ter outras ideias em mente. Não tenha medo de experimentá-los. Certifique-se sempre de conhecer as consequências de cada marco que está tentando alcançar ao registrar as estatísticas.

De uma chance !!!

EU DISSE QUE ISSO SERIA COMPROMISSO!!!

Crie outra tabela e registre as inserções, atualizações e exclusões usando gatilhos DML.

1. Então, digamos que você tenha uma Tabela A que deseja rastrear.
2. Crie outra tabela, digamos, tbl_row_stats.
3. Crie gatilhos DML na Tabela A para inserções, atualizações e exclusões. Ou seja, sempre que qualquer uma dessas três ações acontecer na Tabela A, insira uma linha em tbl_row_stats com o respectivo ID da linha da Tabela A e a ação que ocorreu.
4. Mais tarde você pode fazer um select usando uma cláusula group by, para ver o número de vezes que cada ação ocorreu por linha.

Para rastrear as seleções, você terá que adicioná-lo ao código do aplicativo (ou procedimento armazenado). Ou seja, sempre que uma seleção é feita a partir do aplicativo, uma inserção correspondente a tbl_row_stats deve ser feita.