MySQL 索引语法

1、概念介绍

索引类型	索引特点
单列索引	即一个索引是只根据一个字段创建的，里面只包含单个列， <br>一个表可以有多个单值索引（也叫单列索引）
联合索引	即一个索引包含多个列（也叫多值索引、多列索引）
唯一索引	索引列的值必须唯一，但允许有空值，空值可以有多个
主键索引	设定为主键后数据库会自动建立索引，InnoDB 为聚簇索引

当我们平时谈到『主键』时，这个概念包含三个方面的具体含义：

• 主键字段和这个字段中具体的值
• 主键约束
• 主键索引

2、索引操作语法

相关语法可以参考 W3CSchool 教程：SQL CREATE INDEX 语句

①准备工作

create database db_shop;

use db_shop;

create table t_customer(
pk_id int auto_increment primary key,
customer_no varchar(200),
customer_name varchar(200));

②创建单值索引

# create index 索引名称 on 要建立索引的字段所在的表(要建立索引的字段);
create index idx_customer_name on t_customer(customer_name);

 

③创建唯一索引

# create unique index 索引名称 on 要建立索引的字段所在的表(要建立索引的字段);
create unique index idx_customer_no on t_customer(customer_no);

 

④联合索引

# create index 索引名称 on 要建立索引的字段所在的表(要建立索引的字段,...,要建立索引的字段);
create index idx_customer_no_name on t_customer(customer_no,customer_name);

 

索引创建好可以如下图所示方式查看：

 

⑤删除索引

# drop index 要删除的索引名称 on 索引所在的表;
drop index idx_customer_no_name on t_customer;

3、最佳实践

①需要创建索引的情况

• 主键自动建立唯一索引

• 频繁作为查询条件的字段应该创建索引，也就是经常出现在 where 子句中的字段，尤其是数据表大的时候

• 关联查询

  • 不要涉及 3 张以上的表
  • 小表驱动大表，给大表的关联字段创建索引
  • 尽量先用 where 条件过滤数据
  • 关联字段在各个表中类型要一致

• 单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)

  说明：同样是 A、B、C 三个字段，是分别创建三个单列索引，还是创建一个组合索引？

  答案：此时通常来说我们更倾向于创建组合索引。

  因为基本上来说不管怎么优化，即使创建了多个索引，MySQL 里一条 SQL 语句也只能用到一个索引。

  而对于一个包含多个查询条件的where子句来说，当它面对多个单列索引时，最多只能有一个索引符合最左原则。

  所以我们建立组合索引并参照最左原则能够让更多的索引起到作用。

• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

• 查询中统计或者分组的字段尽量创建索引

②下列情况创建索引效果更好

• 字段的数值有唯一性限制
• 类型小（字节宽度小）的字段

③不要创建索引的情况

• MySQL 中，一张数据库表中记录数量小于 300 万条时，即使创建索引也不会让搜索速度有明显提升。
• 经常增删改的表，建立索引提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件，重新排布索引，这个操作需要对索引表做全表扫描。不仅索引表的全表扫描操作本身非常耗时，而且索引重新排布时不可用，此时执行查询操作没有索引可用，还是要回到原始数据库表做全表扫描。
• where 条件、排序、分组都用不到的字段
• 数据重复，过滤性不好的字段
• 无法排序的字段
• 不要创建冗余或重复的索引

④结论

索引并不是无条件创建、越多越好，而是要根据实际情况恰到好处的创建。不再使用或很少使用的索引要删除掉。