【MySQL】索引的操作

索引的操作

一、索引的创建原则

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

1. 需要占用物理空间，数量越大，占用空间越大；
2. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
3. 会降低表的增删改的效率，因为每次增删改索引，B+树为了维护索引有序性，都需要进行动态维护。

所以，索引并不是万能钥匙，它也是根据场景来使用的。

什么时候不适合创建索引❓❓❓

• 表数据太少的时候，不适合创建索引。
• where 条件、group by 和 order by 里用不到的字段，不适合创建索引。索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
• 字段中存在大量重复数据，不适合创建索引。比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 经常需要更新的字段，不适合创建索引。比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+树的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。
• 唯一性太差的字段不适合单独创建索引，即使该字段频繁地作为查询条件也不适合。

二、创建索引

① 主键

第一种方式：在创建表的时候，直接在字段名后指定 primary key：

create table user1(
    id int primary key,
    name varchar(30)
);

第二种方式：在创建表的最后，指定某列或某几列为主键索引：

create table user2(
    id int, 
    name varchar(30), 
    primary key(id)
);

第三种方式：创建表以后再添加主键：

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

主键索引的特点：

• 一个表中只能有一个主键索引，当然可以使用复合主键
• 主键索引的效率高
• 主键值不允许重复，且不能为 null
• 主键索引的列最好是整型比如 int
• 主键索引的列最好是自增类型，这样子能最大化的利用好索引的优势

② 唯一键

第一种方式：在表定义时，在某列后直接指定 unique 唯一属性：

create table user4(
    id int primary key, 
    name varchar(30) unique
);

第二种方式：创建表时，在表的后面指定某列或某几列为unique：

create table user5(
    id int primary key,
    name varchar(30),
    unique(name)
);

第三种方式：创建表以后再添加主键：

alter table 表名 add unique(列名);

唯一索引的特点：

• 一个表中，可以有多个唯一索引
• 查询效率高
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据
• 如果一个唯一索引上指定 not null，等价于主键索引

③ 创建普通索引

第一种方式：在表的定义最后，指定某列为索引：

create table user8(
    id int primary key,
    name varchar(20),
    email varchar(30),
    index(name)                 --在表的定义最后，指定某列为索引
);

第二种方式：创建完表以后指定某列为普通索引：

alter table 表名 add index(列名);

第三种方式：创建表以后再添加普通索引，并且可以对索引进行命名：

create index 索引名 on 表名(列名);

④ 组合索引

上面我们的操作都是将单个字段设置为索引，其实我们也是可以通过将多个字段组合成一个索引的，该索引就被称为组合索引。

比如，将商品表中的 product_no 和 name 字段组合成联合索引 (product_no, name)，创建联合索引的方式如下：

create index index_product_no_name on product(product_no, name);

可以看到，联合索引的非叶子节点用两个字段的值作为 B+树的 key 值。当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 相同的情况下再按 name 字段比较。

也就是说，联合索引查询的 B+树是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。

因此，使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。

最左匹配原则是指在数据库中使用复合索引进行查询时，索引会按照索引字段的顺序进行匹配。当查询条件中包含多个字段时，索引会从左到右逐个匹配字段，直到找到第一个不匹配的字段为止。这意味着，如果查询条件中只使用了索引的前缀字段，那么索引可以被充分利用；而如果查询条件中使用了索引的后续字段，那么索引的效率会降低。 比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引： where a=1； where a=1 and b=2 and c=3； where a=1 and b=2； 需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。 但是如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效： where b=2； where c=3； where b=2 and c=3； 上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到复合索引的！

三、查询索引

-- 三种方式：
show keys from 表名;
show index from 表名;
desc 表名;

四、删除索引

① 主键

alter table 表名 drop primary key;

② 其它索引

alter table 表名 drop index 索引名;