mysql 索引、约束

索引

创建索引

-- 从表的角度创建索引
ALTER TABLE actor ADD UNIQUE INDEX  uniq_idx_firstname (first_name);
ALTER TABLE actor ADD INDEX  idx_lastname (last_name);
-- 从数据库的角度创建索引
CREATE UNIQUE INDEX uniq_idx_firstname USING BTREE actor (first_name);

删除索引

ALTER TABLE actor DROP INDEX uniq_idx_firstname;
 
DROP INDEX uniq_idx_firstname ON actor;

强制使用索引

select *
from actor 
force index (uniq_idx_firstname)
where first_name = 'jd'

约束

添加主键约束

ALTER TABLE employees_test
ADD CONSTRAINT PRIMARY KEY(id);

添加外键约束

ALTER TABLE audit
ADD CONSTRAINT
constraint_name
FOREIGN KEY(emp_no)
REFERENCES employees_test(id);

MySQL 锁处理

# 查看连接
show processlist;
# 查看 innodb 状态
show engine innodb status;
show status like 'table%';
show OPEN TABLES where In_use > 0;
# 查询当前事务
SELECT * FROM information_schema.INNODB_TRX;
# 查询等待锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
# 查询等待锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
 
SELECT trx_mysql_thread_id FROM information_schema.INNODB_TRX;
# 杀死进程
kill 283242;