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.不真实存在的索引
名词:
覆盖索引:在索引文件中直接获取数据
索引合并:把多个单列索引合并起来用