mysql - 多列 INDEX 和单列 INDEX SQL/PHP

Question

这是我的 SQL 表，一个大约有 ~6kk 行的巨大表。

CREATE TABLE `slots` (
    `id` mediumint(8) UNSIGNED NOT NULL,
    `uid` smallint(5) UNSIGNED NOT NULL,
    `music_id` mediumint(8) UNSIGNED NOT NULL,
    `finished` int(10) UNSIGNED NOT NULL DEFAULT '0',
    `completed` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
    `hidden` tinyint(1) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `slots`
    ADD PRIMARY KEY (`id`),
    ADD KEY `SEC_UNQ` (`uid`,`music_id`) USING BTREE;

ALTER TABLE `slots` MODIFY `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT;

我经常查看 uid、music_id 和 completed。例如:

SELECT `music_id` FROM `slots` WHERE `uid` = :uid AND `completed` = :completed;

并通过 uid 和 music_id 进行选择或更新

 SELECT `music_id` FROM `slots` WHERE **`uid` = :uid AND `music_id` = :music_id**;
 UPDATE `slots` SET xxx WHERE `uid` = :uid AND `music_id` = :music_id;

所以问题是:

我必须为以下所有列创建 3 个索引:uid、music_id 和 completed 或者仅对 就足够了uid ？..哪个更好，单列索引还是多列索引？

PS: 我总是在 WHERE 语句中有 uid

提前致谢

Answer 1

您可以轻松测试它。一个好的索引总是最好的，对于小型 bool 型也是如此。这很容易理解:如果你有一个大表，mysql必须读取空表(FULL TABLE SCAN)来找到几行来更新或删除。但是 MySQL 每次查询大多只能使用一个索引。因此，复合索引很有帮助。 MySQL 也可以将它们用于单个字段。假设您在字段 (a,b,c) 上有一个复合索引，如果只有 a、a 和 b 或 a 和 b 和 c，MySQL 可以在 WHERE 子句中使用它们，但不仅限于 c 或 b。

Hier 是一个示例。在那里你可以看到 MySQL 必须如何读取 man rows 以及使用了哪个索引:

删除表并创建一个新表

MariaDB []> DROP TABLE IF EXISTS mytable;
Query OK, 0 rows affected (0.29 sec)

MariaDB []>
MariaDB []> CREATE TABLE `mytable` (
    ->   `id` INT(11) UNSIGNED NOT NULL,
    ->   `a` INT(11) DEFAULT NULL,
    ->   `b` INT(11) DEFAULT NULL,
    ->   `c` INT(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.24 sec)

MariaDB []>

插入 3000000 行(仅限 MariaDB)

MariaDB []> INSERT INTO mytable (id,a,b,c)
    -> SELECT seq, (seq MOD 2), (seq MOD 3) , (seq MOD 4) FROM seq_0_to_3000000;
Query OK, 3000001 rows affected (15.66 sec)
Records: 3000001  Duplicates: 0  Warnings: 0

MariaDB []>

在字段 a 上测试 WHERE - MySQL 读取 2995634 行

MariaDB []> EXPLAIN SELECT * FROM mytable WHERE a=1;
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | mytable | ALL  | NULL          | NULL | NULL    | NULL | 2995634 | Using where |
+------+-------------+---------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.12 sec)

在字段 a 上添加键

MariaDB []> ALTER TABLE mytable ADD KEY key_a (a);
Query OK, 0 rows affected (10.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次测试最后一个查询(WHERE a)- MySQL 只读取 1496635 行

MariaDB []> EXPLAIN SELECT * FROM mytable WHERE a=1;
+------+-------------+---------+------+---------------+-------+---------+-------+---------+-------+
| id   | select_type | table   | type | possible_keys | key   | key_len | ref   | rows    | Extra |
+------+-------------+---------+------+---------------+-------+---------+-------+---------+-------+
|    1 | SIMPLE      | mytable | ref  | key_a         | key_a | 5       | const | 1496635 |       |
+------+-------------+---------+------+---------------+-------+---------+-------+---------+-------+
1 row in set (0.00 sec)

在字段 a 和 b 上使用 WHERE 进行测试 - 1496635 行

MariaDB []> EXPLAIN SELECT * FROM mytable WHERE a=1 AND b=2;
+------+-------------+---------+------+---------------+-------+---------+-------+---------+-------------+
| id   | select_type | table   | type | possible_keys | key   | key_len | ref   | rows    | Extra       |
+------+-------------+---------+------+---------------+-------+---------+-------+---------+-------------+
|    1 | SIMPLE      | mytable | ref  | key_a         | key_a | 5       | const | 1496635 | Using where |
+------+-------------+---------+------+---------------+-------+---------+-------+---------+-------------+
1 row in set (0.00 sec)

在字段b上添加key

MariaDB []> ALTER TABLE mytable ADD KEY key_b (b);
Query OK, 0 rows affected (9.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

与 a 和 b 相同的测试 - 相同的行 - 仅使用 key_a

MariaDB []> EXPLAIN SELECT * FROM mytable WHERE a=1 AND b=2;
+------+-------------+---------+------+---------------+-------+---------+-------+---------+-------------+
| id   | select_type | table   | type | possible_keys | key   | key_len | ref   | rows    | Extra       |
+------+-------------+---------+------+---------------+-------+---------+-------+---------+-------------+
|    1 | SIMPLE      | mytable | ref  | key_a,key_b   | key_a | 5       | const | 1496635 | Using where |
+------+-------------+---------+------+---------------+-------+---------+-------+---------+-------------+
1 row in set (0.00 sec)

在a和b上创建索引

MariaDB []> ALTER TABLE mytable ADD KEY key_ab (a,b);
Query OK, 0 rows affected (11.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用 a 和 b 进行测试 - 使用 key_ab 并且仅读取 946702 行

MariaDB []> EXPLAIN SELECT * FROM mytable WHERE a=1 AND b=2;
+------+-------------+---------+------+--------------------+--------+---------+-------------+--------+-------+
| id   | select_type | table   | type | possible_keys      | key    | key_len | ref         | rows   | Extra |
+------+-------------+---------+------+--------------------+--------+---------+-------------+--------+-------+
|    1 | SIMPLE      | mytable | ref  | key_a,key_b,key_ab | key_ab | 10      | const,const | 946702 |       |
+------+-------------+---------+------+--------------------+--------+---------+-------------+--------+-------+
1 row in set (0.01 sec)

使用字段 a、b 和 c 进行测试——使用了 kay_ab 并读取了 946702 行

MariaDB []> EXPLAIN SELECT * FROM mytable WHERE a=1 AND b=2 AND c=3;
+------+-------------+---------+------+--------------------+--------+---------+-------------+--------+-------------+
| id   | select_type | table   | type | possible_keys      | key    | key_len | ref         | rows   | Extra       |
+------+-------------+---------+------+--------------------+--------+---------+-------------+--------+-------------+
|    1 | SIMPLE      | mytable | ref  | key_a,key_b,key_ab | key_ab | 10      | const,const | 946702 | Using where |
+------+-------------+---------+------+--------------------+--------+---------+-------------+--------+-------------+
1 row in set (0.00 sec)

在字段 a,b,c 上添加 Key

MariaDB []> ALTER TABLE mytable ADD KEY key_abc (a,b,c);
Query OK, 0 rows affected (18.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

测试字段 a、b、c - 使用 key_abc - 并读取 511082 行

MariaDB []> EXPLAIN SELECT * FROM mytable WHERE a=1 AND b=2 AND c=3;
+------+-------------+---------+------+----------------------------+---------+---------+-------------------+--------+-------------+
| id   | select_type | table   | type | possible_keys              | key     | key_len | ref               | rows   | Extra       |
+------+-------------+---------+------+----------------------------+---------+---------+-------------------+--------+-------------+
|    1 | SIMPLE      | mytable | ref  | key_a,key_b,key_ab,key_abc | key_abc | 15      | const,const,const | 511082 | Using index |
+------+-------------+---------+------+----------------------------+---------+---------+-------------------+--------+-------------+
1 row in set (0.01 sec)

所以影响最大的是复合索引，但它也取决于你使用的查询。