mysql索引种类(索引种类和建立索引)

一、mysql索引种类

mysql索引种类(索引种类和建立索引)

1、加速查找:

查找普通字段和查找有索引字段,哪个方式查找速度快:根据索引来查字段速度更快

2、创建索引:

为userinfo表email字段创建索引:  create index ix_name on userinfo<email>;     #创建字段email索引文件,起别名ix_name

为某个字段创建索引会创建额外文件(某种格式存储),下次查询这个字段,先去文件中查找数据在表中的位置,拿着这位置去表中定位数据。(类似于查字典)

3.索引种类   

hash索引:索引表(无序的排列)
把数据转换成一个hash值(数值),放在索引表里面,再加上这个数据的存储地址
查找的时候会根据数据查找到存储地址。
注意:数据库的值和索引表存的数值顺序是不一样的。因为这个是无序的排列,所以在数据库中查找大于/小于多少的数据可能花的时间更长。
缺点:在某个范围查找数据速度慢         优点:查找单值速度快

btree索引:
    二叉树
#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;

4.建立索引

1.额外的文件保存特殊的数据结构
2.查询快,插入更新删除慢
3.命中索引(使用索引表)

普通索引

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

创建表+普通索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)    #ix_name是索引名,name字段建立索引
)

创建索引

create index ix_name on table_name(column_name);

删除索引

drop index ix_name on table_name;

查看索引

show index from table_name;

唯一索引

唯一索引有两个功能:加速查询和唯一约束(可含null)

创建表+唯一索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

创建唯一索引

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

删除唯一索引

drop unique index 索引名 on 表名

主键索引

主键有两个功能:加速查询 和 唯一约束(不可含null)

创建表+创建主键

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table in1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)

创建主键

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

删除主键

alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

联合索引

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

其应用场景为:频繁的同时使用n列来进行查询,如:where n1 = ‘alex‘ and n2 = 666

创建表

create table in3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)

创建联合索引

create index ix_name_email on in3(name,email);     #就是在()中写多个列名

最左前缀匹配,查询:

  • name and email  -- 使用索引
  • name                 -- 使用索引
  • email                 -- 不使用索引  (因为email字段在右边)

注意:对于同时搜索n个条件时,组合索引的性能好于索引合并。

5.不真实存在的索引

名词:

  覆盖索引:在索引文件中直接获取数据

  索引合并:把多个单列索引合并起来用