参考文献

菜鸟文档-Mysql索引

场景说明

用户邀请表如下

CREATE TABLE `cd_member_invite` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `invite_id` int(11) unsigned DEFAULT '0' COMMENT '邀请人ID',
  `to_invite_id` int(11) unsigned DEFAULT '0' COMMENT '被邀请人ID',
  `create_time` int(11) unsigned DEFAULT '0' COMMENT '创建时间',
  `update_time` int(11) unsigned DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `invite_id_to_invite_id` (`invite_id`,`to_invite_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;

需要保证invite_id与to_invite_id确定唯一的邀请记录

索引示例

查看索引

show index from `table_name`;

mysql> show index from cd_member_invite;
+------------------+------------+------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name               | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| cd_member_invite |          0 | PRIMARY                |            1 | id           | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| cd_member_invite |          0 | invite_id_to_invite_id |            1 | invite_id    | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
| cd_member_invite |          0 | invite_id_to_invite_id |            2 | to_invite_id | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
+------------------+------------+------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.03 sec)

主键索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );

mysql> ALTER TABLE `cd_member_invite` ADD PRIMARY KEY ( `id` );
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

唯一索引

ALTER TABLE `table_name` ADD UNIQUE ( `column` );

mysql> ALTER TABLE `cd_member_invite` ADD UNIQUE ( `invite_id` );
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

普通索引

ALTER TABLE `table_name` ADD INDEX ( `column` );

mysql> ALTER TABLE `cd_member_invite` ADD INDEX ( `to_invite_id` );
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

全文索引

ALTER TABLE `table_name` ADD FULLTEXT ( `column` );

mysql> ALTER TABLE `cd_article` ADD FULLTEXT ( `content` );
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 1

多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` );

mysql> ALTER TABLE `cd_member_invite` ADD INDEX invite_id_to_invite_id ( `invite_id`, `to_invite_id`);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

联合唯一索引

  • UNIQUE INDEX
    ALTER TABLE `table_name` ADD UNIQUE INDEX (`column1`, `column2`)
mysql> ALTER TABLE `cd_member_invite` ADD UNIQUE INDEX (`invite_id`, `to_invite_id`);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • UNIQUE INDEX INDEXNAME
    ALTER TABLE `table_name` ADD UNIQUE INDEX index_name (`column1`, `column2`);

  • UNIQUE KEY
    ALTER TABLE `table_name` ADD UNIQUE KEY key_name (`column1`, `column2`);

扩展说明

查询重复数据

根据两个字段 查询重复
SELECT id FROM dp_test GROUP BY issue, member_id HAVING count( id ) > 1

删除重复
DELETE FROM dp_test WHERE id IN (SELECT id FROM (SELECT id FROM dp_test GROUP BY issue, member_id HAVING count( id ) > 1) ids)
注:这里只是测试数据,我这随便删除。

问:已经存在联合索引,单列上是否还要加索引?

如果联合索引idx(a, b),那么a列上的索引就没必要了,但是如果结合业务查询,确实需要b列上的索引,那就建。
另外,如果需要联合idx(a,b),同时a和b列上都需要有索引,那就看a和b哪一列单独建索引更高效,来决定idx(a,b)里a,b的顺序。例如a是int类型,b是varchar(100),那么就建idx(b,a),idx_a(a)。这里idx_a(a),就比idx_b(b)好。
如果单独索引是复合索引的第一个,就不需要了,其他位置的就需要单独建
比如idx(a,b)
此时a就不需要单独建索引了,因为在匹配的时候始终会先去匹配a列的索引。而当a失效时,b不会生效,需要匹配单独的索引