MySQL索引必知必会

索引必知必会

索引原理

没有索引的时候，搜索是全表进行扫描，速度很慢；

当我们建立了一个索引后，会生成一个索引的数据结构（例如索引二叉树）

代价

• 索引会占用磁盘空间
• 对增删改语句的效率有影响 -> 结合实际场景是查询业务多还是增删改业务多来判断

所以不能盲目添加

规则

1. 频繁作为查询条件的字段可以创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁查询
3. 频繁更新的字段不适合创建索引
4. 不会出现在where子句中的字段不要创建索引

索引类型

1. 主键索引（primary key）
2. 唯一索引（unique key）
   因为要判断是否唯一，也是需要查询全表的
3. 普通索引（index）
   一个索引只包含单个列
4. 联合索引
   一个索引包含多个列
5. 全文索引（fulltext）适用于MyISAM
   实际开发中很少用这个索引，一般用Solr或者ElasticSearch

索引的使用

查询索引

show indexes from 表名;

show keys from 表名;

添加索引

建表时添加

create table 表名(id bigint(20) primary key, name varchar(20), key(name), 字段1 类型, unique(字段1), 字段2 类型, 字段3 类型, key(字段2, 字段3), ...);

建表后添加

create [unique] index 索引名 on 表名 (列名);

alter table 表名 add [unique] index 索引名 (列名);

添加主键索引

alter table 表名 add primary key (列名);

注：在某表某列创建的这个索引，只对该列有效，即查询时使用该列作为条件才有效果
如果某列的值不会重复，则优先考虑unique索引，否则使用普通索引

删除索引

drop index 索引名 on 表名;

删除主键索引

alter table 表名 drop primary key;

后续再更新索引调优的内容