Mysql 添加Index_mysql绑定index-优快云博客

本文介绍如何在MySQL的InnoDB存储引擎中处理死锁，并提供了创建和优化索引的方法，包括添加唯一约束、解决重复数据问题及限制索引键长度。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Handle innodb deadlocks

http://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks.html

ALTER TABLE tb_domain ADD INDEX groupid_name_index (group_id, name);

ALTER TABLE tb_view_history ADD INDEX domainid_view_code (domain_id, view_code);

alter table t add constraint t_unique_key unique (g);

通过ignore删除表里面重复的数据，由于InnoDB有写版本有bug，所以先切换到MsISAM，然后修改。

ALTER TABLE tb_view ENGINE MyISAM;
ALTER IGNORE TABLE tb_view ADD UNIQUE INDEX domainid_view_code_unique (domain_id, view_code)
ALTER TABLE tb_view ENGINE InnoDB;

限制Key的长度，MyISAM是1000， InnoDB是767，不兼容，需要手动限制。

ALTER TABLE `mytable` ADD UNIQUE ( column1(15), column2(200) );

查找表中重复的数据。

select a.domain_id,a.view_code from tb_view a inner join tb_view b where a.view_code=b.view_code and a.domain_id=b.domain_id and a.id!=b.id group by a.domain_id

select group_id,c.domain_id,c.view_code from tb_domain inner join  (select a.domain_id,a.view_code from tb_view a inner join tb_view b where a.view_code=b.view_code and a.domain_id=b.domain_id and a.id!=b.id group by a.domain_id) c where id=c.domain_id;

删除索引

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY