mysql - 我的 MySQL 索引是否有效？

Question

我有下表:

mysql> describe as_rilevazioni;
+----------------------------+----------+------+-----+---------+----------------+
| Field                      | Type     | Null | Key | Default | Extra          |
+----------------------------+----------+------+-----+---------+----------------+
| id                         | int(11)  | NO   | PRI | NULL    | auto_increment |
| id_sistema_di_monitoraggio | longtext | NO   | MUL | NULL    |                |
| id_unita                   | longtext | NO   |     | NULL    |                |
| id_sensore                 | longtext | NO   |     | NULL    |                |
| data                       | datetime | NO   |     | NULL    |                |
| timestamp                  | longtext | NO   |     | NULL    |                |
| unita_di_misura            | longtext | NO   |     | NULL    |                |
| misura                     | longtext | NO   |     | NULL    |                |
+----------------------------+----------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

我的表上有以下索引:

mysql> show indexes from as_rilevazioni;
+----------------+------------+----------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| as_rilevazioni |          0 | PRIMARY  |            1 | id                         | A         |   315865898 |     NULL | NULL   |      | BTREE      |         |               |
| as_rilevazioni |          0 | UNIQUE   |            1 | id_sistema_di_monitoraggio | A         |          17 |        5 | NULL   |      | BTREE      |         |               |
| as_rilevazioni |          0 | UNIQUE   |            2 | id_unita                   | A         |          17 |       10 | NULL   |      | BTREE      |         |               |
| as_rilevazioni |          0 | UNIQUE   |            3 | id_sensore                 | A         |      145225 |       30 | NULL   |      | BTREE      |         |               |
| as_rilevazioni |          0 | UNIQUE   |            4 | data                       | A         |   315865898 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+----------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.02 sec)

恐怕这些索引效率不高，因为基于“数据”列的索引的基数与记录数据的数量一样大!这些索引加快了我的查询速度，还是占用了大量空间却没有任何好处？

这是表定义:

CREATE TABLE `as_rilevazioni` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_sistema_di_monitoraggio` longtext NOT NULL,
  `id_unita` longtext NOT NULL,
  `id_sensore` longtext NOT NULL,
  `data` datetime NOT NULL,
  `timestamp` longtext NOT NULL,
  `unita_di_misura` longtext NOT NULL,
  `misura` longtext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE` (`id_sistema_di_monitoraggio`(5),`id_unita`(10),`id_sensore`(30),`data`)
) ENGINE=InnoDB AUTO_INCREMENT=437497044 DEFAULT CHARSET=latin1

我使用的主要查询是:

select * from as_rilevazioni where id_sistema_di_monitoraggio="<value>" and id_unita="<value>" and id_sensore="<value>" and data>="<date_1>" and data<="<date2>"

这是解释的查询:

mysql> explain select * from as_rilevazioni where id_sistema_di_monitoraggio="235" and id_unita="17" and id_sensore="15" and data >= "2015-01-01 00:00:00" order by data;
+----+-------------+----------------+-------+---------------+--------+---------+------+--------+-------------+
| id | select_type | table          | type  | possible_keys | key    | key_len | ref  | rows   | Extra       |
+----+-------------+----------------+-------+---------------+--------+---------+------+--------+-------------+
|  1 | SIMPLE      | as_rilevazioni | range | UNIQUE        | UNIQUE | 59      | NULL | 285522 | Using where |
+----+-------------+----------------+-------+---------------+--------+---------+------+--------+-------------+
1 row in set (0.00 sec)

这是数据和索引的维度:

mysql> SELECT concat(table_schema,'.',table_name) tables,
    ->        concat(round(table_rows/1000000,2),'M') rows,
    ->        concat(round(data_length/(1024*1024*1024),2),'G') data_size,
    ->        concat(round(index_length/(1024*1024*1024),2),'G') index_size,
    ->        concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
    ->        round(index_length/data_length,2) index_data_ratio
    -> FROM information_schema.TABLES
    -> WHERE table_name="as_rilevazioni"
    -> ORDER BY total_size DESC;
+------------------------------------+---------+-----------+------------+------------+------------------+
| tables                             | rows    | data_size | index_size | total_size | index_data_ratio |
+------------------------------------+---------+-----------+------------+------------+------------------+
| agriculturalsupport.as_rilevazioni | 317.12M | 19.06G    | 10.25G     | 29.31G     |             0.54 |
+------------------------------------+---------+-----------+------------+------------+------------------+
1 row in set (0.02 sec)

有什么建议吗？感谢大家!

Answer 1

UNIQUE a(5), b(10)

太可怕了。它是说只检查 a 的前 5 个字节和 b 的前 10 个字节的唯一性。您可能想检查完整的 a 和 b 的组合的唯一性。

INDEX a(5), b(10)

实际上是无用的——它不会通过 a 甚至考虑 b。

INDEX a(5)

有时没用。

UNIQUE a, data  -- where `data` is `DATETIME` or `TIMESTAMP`

通常是“错误的”。你真的确定 a 不能在一秒钟内出现两次吗？

在查看多列索引时，“基数”通常并不重要。等于表中估计行数的基数意味着它认为该列是唯一的；但它不会指望它。

“高效”是指“不会占用‘太多’空间”吗？ UNIQUE 索引的每一“行”将占用大约 1+5 + 1+10 + 1+30 + 5 = 53 个字节。将其乘以 317M，您将得到 17GB。添加大约 40% 的开销以获得 23GB。这比 information_schema 中的 10GB 多了很多。 (错误涉及许多近似值——可能主要是行数。)

或者，你的意思是“这个索引加速了一些查询”？要讨论这一点，我们需要查看查询。 (同时，我已经指出了索引不好的几个原因。)

如果 ID 是数字

如果它们确实是数字，则切换到 SMALLINT UNSIGNED(2 字节)或其他一些大小。 Then 包含这 4 列(和 data last)的索引很可能会显着加快该查询的速度。是的，索引会占用一些磁盘空间，但可能是值得的。 TEXT，带有“前缀”，根本不会提供效率。

索引数字也比字符串便宜。您的 id_unita(10) 在索引的每一行中最多占用 11 个字节； MEDIUMINT UNSIGNED 占用固定的 3 个字节。也就是说，索引将更小并且更有用。