MySQL表中具有相同字段的两个索引

前端之家收集整理的这篇文章主要介绍了MySQL表中具有相同字段的两个索引前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

例如,我们有表:

  1. CREATE TABLE `my_tbl` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT,`id_type` int(11) NOT NULL,`date` date NOT NULL,`other_fields` varchar(200) CHARACTER SET latin1 NOT NULL,PRIMARY KEY (`id`),KEY `id_type` (`id_type`),KEY `type_date` (`id_type`,`date`) USING BTREE
  3. ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Ther是两个索引:id_type和id_type,date.

据我所知,如果我们有两个字段的索引,我们可以将它用作第一个字段的单个索引.

我可以删除索引id_type而不会丢失性能吗?

更新:问这个问题引起注意,有时不同索引中的同一个字段具有不同的基数.

最佳答案
MysqL 5.7.9 – 删除id_type索引并没有什么区别.多列索引(type_date)适用于两个查询.

解释查询输出

  1. MysqL> explain SELECT id_type,date FROM my_tbl WHERE id_type='some';
  2. +----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
  3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  4. +----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
  5. | 1 | SIMPLE | my_tbl | NULL | ref | type_date | type_date | 4 | const | 1 | 100.00 | Using index |
  6. +----+-------------+--------+------------+------+---------------+-----------+-----
  7. MysqL> explain SELECT id_type FROM my_tbl WHERE id_type='some';
  8. +----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
  9. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  10. +----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
  11. | 1 | SIMPLE | my_tbl | NULL | ref | type_date | type_date | 4 | const | 1 | 100.00 | Using index |
  12. +----+-------------+--------+------------+------+---------------+---------
  13. MysqL> show indexes from my_tbl;
  14. +--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  15. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  16. +--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  17. | my_tbl | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
  18. | my_tbl | 1 | type_date | 1 | id_type | A | 0 | NULL | NULL | | BTREE | | |
  19. | my_tbl | 1 | type_date | 2 | date | A | 0 | NULL | NULL | | BTREE | | |
  20. +--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-------------

猜你在找的MySQL相关文章