(2.9)Mysql之SQL基础——索引的查看与删除
(2.9)Mysql之SQL基础——索引的查看与删除
关键词:mysql索引查看,mysql索引删除
1、索引查询(以下包括主键,唯一,普通,复合,全文,但不包括外键)
(1)按库查询
select * from information_schema.statistics where table_schema='test';
(2)按表查询
select * from information_schema.statistics where table_schema='test' and table_name = 'test103';
(3)查询
show index from table_name from db_name;
[1]show index from test102; [2]show index from test102\G --grid网格 [2]show index from test102 from test;
(4)结果
[1]NON_UNIQUE:不是唯一索引(为1则不是唯一索引,为0则是唯一索引)
[2]SEQ_IN_INDEX:索引序列,如果大于1,则必定是复合索引
[3]CARDINALITY:基数,即行数
(5)按库、表 查询索引涉及到的列
select * from information_schema.columns where table_schema= 'test' and table_name ='test104' and column_key is not null AND column_key !='';
(6)快速查找出mysql数据库中哪些表没有任何索引
通过information_schema 和 information_schema.key_column_usage 2个系统视图来查看~~
select * from information_schema.tables as t1 left join ( select distinct table_schema,table_name from information_schema.key_column_usage ) t2 on t2.table_schema=t1.table_schema and t2.table_name = t1.table_name where t1.table_schema not in ('mysql','information_schema','performance_schema','test','sys') AND t2.table_name is null;
2、索引的删除
(1).索引的删除 1).单列/多列/唯一索引删除:drop index 索引名 on 表名; or alter table test101 drop 索引名
2).主键索引删除: alter table test101 drop primary key;(如果有自增字段,需要先删除自增)
(2).删除自增auto_increment
alter table test101 change id int;