Início / dba / Perguntas / 139124
Accepted
Mark D
Asked: 2016-05-21 07:04:31 +0800 CST

Construindo índices clusterizados do MySQL InnoDB

  • 772

Supondo que você tenha uma tabela como

CREATE TABLE `t1` (
    `search_id` int(11) NOT NULL,
    `provider_id` bigint(20) unsigned NOT NULL,
    `status` text NOT NULL,
    `imported_at` int(11) NOT NULL,
    `created_at` int(11) NOT NULL,
    `latitude` double DEFAULT NULL,
    `longitude` double DEFAULT NULL,
    `moderation` tinyint(3) NOT NULL DEFAULT '0',
    PRIMARY KEY (`search_id`,`provider_id`),
    KEY `k_search_imported` (`search_id`,`imported_at`),
    KEY `k_scm` (`search_id`,`created_at`,`moderation`),
    KEY `k_sc` (`search_id`,`created_at`)
) ENGINE=InnoDB;

O que eu não tenho certeza é por que

SELECT COUNT(1) FROM t1;

Leva 19 minutos (preenchi a tabela com 1,2 milhão de linhas).

MariaDB [test] 10:53:58> show indexes from t1;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY           |            1 | search_id   | A         |       13478 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          0 | PRIMARY           |            2 | provider_id | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_search_imported |            1 | search_id   | A         |          30 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_search_imported |            2 | imported_at | A         |       46213 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            1 | search_id   | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            2 | created_at  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            3 | moderation  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_sc              |            1 | search_id   | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_sc              |            2 | created_at  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 rows in set (0.00 sec)

Eu me pergunto se o problema aqui é a cardinalidade. Minha pergunta é se os índices InnoDB devem ser ordenados pelas colunas de maior cardinalidade mais próximas do início do índice, ou seja, PRIMARY KEY (provider_id,search_id);ou isso importa?

mysql index

1 respostas

  1. Best Answer

    Portanto, como sugeriu @RickJames em um dos comentários, a cardinalidade tem pouco a ver com a ordem dos índices. Portanto, se sua consulta for semelhante a

    SELECT id, search_id, provider_id FROM t1 WHERE search_id=123 AND provider_id=567

    Em seguida, o índice deve ser ordenado search_id,provider_id. Se, no entanto, você estiver executando uma consulta mais baseada em intervalo.

    Para eficiência, a coluna "=constant" deve vir antes da coluna "range" no INDEX

    Isso significa que sempre que você tiver uma consulta da seguinte maneira.

    SELECT id, search_id, provider_id FROM t1 WHERE search_id>123 AND
provider_id<567 AND columnX=1

    Em seguida, seu índice deve ser criado da seguinte maneira columnX,search_id,provider_id.

    • 0

relate perguntas