MySQL 基础之索引

MySQL 索引

索引

索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后读取整个表以查找相关行。表越大，成本越高。如果表中有相关​​列的索引，MySQL可以快速确定要在数据文件中间寻找的位置，而无需查看所有数据。这比按顺序读取每一行要快得多。

类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

本质上是告诉数据库的存储引擎如何快速找到我们所要的数据。所以 MySQL 的索引是在 MySQL 的存储引擎层实现的，而不是在其服务器层实现。

MySQL中常见索引有：

• 普通索引
• 唯一索引
• 主键索引
• 组合索引

普通索引

普通索引仅有一个功能：加速查询

/*创建表的同时创建索引*/
create table t1(
    id int not null auto_increment primary key,
    name varchar(32),
    email varchar(64),
    extra text,
    index ix_name(name)
    /*添加索引到列名 name, 索引名为 ix_name*/
)

/*单独创建索引*/
create index index_name on 表名称(列名称)
/*Example*/
create index index_name on student(name);

/*查看索引*/
show index from 表名称;
/*Example*/
show index from student;

/*删除索引*/
DROP INDEX index_name on 表名称;
/*Example*/
DROP INDEX index_name on student;

唯一索引

唯一索引有两个功能：加速查询 和 唯一约束（可含null）

/*创建表和唯一索引*/
create table t2(
    id int not null auto_increment primary key,
    name varchar(32),
    email varchar(64),
    unique index ix_name (name)
);

/*创建唯一索引*/
create unique index 索引名 on 表名(列名);

/*删除唯一索引*/
ALTER TABLE student2 DROP INDEX email;

主键索引

主键有两个功能：加速查询 和 唯一约束（不可含null）

当一个列被创建为主键是，它就会被赋予主机索引的属性。

/*创建表和创建主键*/
create table t3(
    id int ,
    name varchar(32) ,
    email varchar(64) ,
    primary key(name)
);

组合索引

组合索引是将n个列组合成一个索引

其应用场景为：频繁的同时使用 n 个列来进行查询，如：where name = 'shark' and age = 18。

create table studens(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    age int not null,
)

create index idx_name_age on students(name,age);

如上创建组合索引之后，查询：

• name and age -- 使用索引 where name='shark' and age=18;
• name -- 使用索引 where name='shark';

对于多列索引的使用上需要注意, where 自己的第一个条件的列名必须是组合索引列的最左边的那个。

下面是可以有效使用的方式

where name='shark';
where name='shark' and age>18;
 where name = 'shark'  and (age >18 or age = 10);

但是不能是下面的用法

where age = 18;
where name='shark'  or   age=19;

注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。