专注写字楼办公室设计装修29年
       一站式整装影响力品牌
装修攻略联系我们
客服热线:
 
 
首页 > 新闻中心

乐鱼app:MySQL表中具有相同字段的两个索引

2021-03-26 08:30:16 来源:【jake】 作者:-=Jake=- 116
1

例如爱游戏app下载亚博app安全有保障 ,我们有表:

CREATE TABLE `my_tbl` (
 `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
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Ther是两个索引:id_type和id_type澳洲幸运10 ,date.

据我所知索引把两个表有相同字段的,如果我们有两个字段的索引索引把两个表有相同字段的爱游戏官网app华体会真人 ,我们可以将它用作第一个字段的单个索引.

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

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

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

解释查询输出:

mysql> explain SELECT id_type,date FROM my_tbl WHERE id_type='some';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | my_tbl | NULL       | ref  | type_date     | type_date | 4       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+-----------+-----
mysql> explain  SELECT id_type FROM my_tbl WHERE id_type='some';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
        +----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | my_tbl | NULL       | ref  | type_date     | type_date | 4       | const |    1 |   100.00 | Using index |
        +----+-------------+--------+------------+------+---------------+---------
mysql> show indexes from my_tbl;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| my_tbl |          0 | PRIMARY   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| my_tbl |          1 | type_date |            1 | id_type     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| my_tbl |          1 | type_date |            2 | date        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-------------

鸭脖官网
分享到:
官方微信关注我们 官方微信关注我们
Copyright © 2012-2019 鸭脖官网有限公司 版权所有  鸭脖官网,鸭脖娱乐官网,鸭脖娱乐   鸭脖官网,鸭脖娱乐官网,鸭脖娱乐