场景说明
用户邀请表如下
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
|
联合唯一索引
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
|
ALTER TABLE `table_name` ADD UNIQUE INDEX index_name (`column1`, `column2`);
ALTER TABLE `table_name` ADD UNIQUE KEY key_name (`column1`, `column2`);
前缀索引
概念
- 定义字符串的一部分作为索引。如果不指定前缀索引，那么索引就是整个字符串。
alter table User add index idx_email(email);
alter table User add index idx_email(email(6));
|
优点
- 定义好的长度，既能节省空间，也不会增加太多的查询成本
缺点
扩展
查询重复数据
根据两个字段 查询重复
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不会生效，需要匹配单独的索引
