文章目录

  • MySQL 索引
  • 一，MySQL 索引的分类
  • 二，MySQL 不同类型索引用途和区别
  • 三，MySQL 使用索引
  • 创建索引
  • 1，普通索引（INDEX）
  • 2，唯一索引（UNIQUE）
  • 3，主键索引（PRIMARY KEY）
  • 删除索引
  • 1，删除普通索引
  • 2，删除唯一 索引（UNIQUE）
  • 3，删除主键索引（PRIMARY KEY）
  • 查看索引
  • 选择索引的原则


MySQL 索引

索引作为一种数据结构，其用途是用于提升检索数据的效率

一，MySQL 索引的分类

  • 普通索引（INDEX）：索引列值可重复
  • 唯一索引（UNIQUE）：索引列值必须唯一，可以为NULL
  • 主键索引（PRIMARY KEY）：索引列值必须唯一，不能为NULL，一个表只能有一个主键索引
  • 全文索引（FULL TEXT）：给每个字段创建索引

二，MySQL 不同类型索引用途和区别

  • 普通索引：常用于过滤数据。如，以商品种类作为索引，检索种类为手机的商品。
  • 唯一索引：主要用于表示一列数据不允许重复的特性，相比主键索引不常用于检索的场景。
    主键索引：是行的唯一标识，因而其主要用途是检索特定数据。
    全文索引：效率低，常用于文本中内容的检索

三，MySQL 使用索引

创建索引

1，普通索引（INDEX）

在创建表时指定索引

mysql> create table student1(id int not null, name varchar(100) not null, birthdy date, sex char(1) not null, index nameindex (name(50)));
  Query OK, 0 rows affected (0.02 sec)

基于表结构创建

mysql> create table student2(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
  Query OK, 0 rows affected (0.01 sec)

mysql> create index nameindex on student2(name(50));

修改表结构创建

mysql> create table student3(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
  Query OK, 0 rows affected (0.01 sec)

mysql> show index from student3;    \\查看索引
2，唯一索引（UNIQUE）

在创建表时指定索引

mysql> create table student4(id int not null, name varchar(100) not null, birthday date, sex char(1) not null, unique index id_idex (id));
  Query OK, 0 rows affected (0.00 sec)

基于表结构创建

mysql> create table student5(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
  Query OK, 0 rows affected (0.00 sec)

mysql> CREATE unique INDEX idIndex ON student5(id);
3，主键索引（PRIMARY KEY）

创建表时指定索引

mysql> create table student6(id int not null, name varchar(100) not null, birthday date, sex char(1) not null, primary key (id));
  Query OK, 0 rows affected (0.01 sec)

修改表结构创建索引

mysql> create table student7(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
  Query OK, 0 rows affected (0.01 sec)

 mysql> ALTER TABLE student7 ADD PRIMARY KEY (id);
  Query OK, 0 rows affected (0.01 sec)
  Records: 0  Duplicates: 0  Warnings: 0

删除索引

1，删除普通索引

直接删除索引

mysql> DROP INDEX nameIndex ON student1;

修改表结构删除索引

mysql> ALTER TABLE student2 DROP INDEX nameIndex;
2，删除唯一 索引（UNIQUE）

直接删除索引

mysql> DROP INDEX idIndex ON student4;

修改表结构删除索引

mysql> ALTER TABLE student DROP INDEX idIndex;
3，删除主键索引（PRIMARY KEY）

主键索引不能采用直接删除索引的方式删除

修改表结构删除索引

mysql> ALTER TABLE student DROP PRIMARY KEY;

查看索引

mysql> SHOW INDEX FROM 表名;

选择索引的原则

  • 常用于查询条件的字段较适合作为索引，例如WHERE语句和JOIN语句中出现的列
  • 唯一性太差的字段不适合作为索引，例如性别
  • 更新过于频繁（更新频率远高于检索频率）的字段不适合作为索引。
  • 使用索引的好处是索引通过一定的算法建立了索引值与列值直接的联系，可以通过索引直接获取对应的行数据，而无需进行全表搜索，因而加快了检索速度
  • 但由于索引也是一种数据结构，它需要占据额外的内存空间，并且读取索引也会加大IO资源的消耗，因而索引并非越多越好，且对过小的表也没有添加索引的必要。

index（key）每张表可以有很多列做index，必须的起名

面试题

导致SQL执行慢的原因：

  • 1.硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等
  • 2.没有索引或者索引失效.
  • 3.数据过多（分库分表）
  • 4.服务器调优及各个参数设置（调整my.cnf）
    索引：当查询速度过慢可以通过建立优化查询速度，可以当作调优