MySQL中使用索引

id:选择标识符
select_type:表示查询的类型
table:输出结果集的表
partitions:匹配的分区
type:表示表的链接类型
possible_keys:表示查询时,可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

常用的类型有：ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右，性能从差到好)
system>const>eq_ref>ref>range>index>ALL

system	const	eq_ref	ref	range	index	all
极好	好	中上	中等	中下	差	极差

#Type级别详解
一、system级别

索引类型能是system的只有两种情况:
1.只有一条数据的系统表
只有一条数据的系统表,就是系统里自带一张表,并且这个表就一条数据,这个基本就达不到,这个是系统自带的表,而且就一条数据,所以基本达不到;
2.或衍生表只能有一条数据的主查询
这个是可以实现的,但是在实际开发当中,你不可能去写这么一个玩意,不可能公司业务去让你把sql索引写实system...

二、const级别

const条件稍微低一点,但是基本也达不到;
1.仅仅能查询一条的sql语句并且用户primary key 或unique索引;
在企业里面根本不可能实现,能查出来一条sql语句,你索引还必须是Primary key 或unique;

三、eq_ref级别

唯一性索引:对于每个索引键的查询,返回匹配唯一数据(有且只有一个,不能多,不能0);
解说:比如你的select ... from 一张表where 比方说有一个字段 name=一个东西,也就是我们以name作为索引,假如我之前给name加一个索引值,我现在根据name去查,查完后有20条数据,我就必须保证这二十条数据每行都是唯一的,不能重复不能为空!
只要满足以上情况,你就能达到eq_ref,当然前提是你要给name建索引,如果name连索引都没,那你肯定达不到eq_reg;
此种情况常见于唯一索引和主键索引;
比如我根据name去查,但是一个公司里面或一个学校里面叫name的可能不止一个,一般你想用这个的时候,就要确保你这个字段是唯一的,id就可以,你可以重复两个张三,但是你身份证肯定不会重复;
添加唯一键语法:alter table 表名 add constraint 索引名 unique index(列名)
检查字段是否唯一键:show index form 表中;被展示出来的皆是有唯一约束的;

四.ref级别

到ref还是问题不大的,只要你上点心,就可以达到;
非唯一性索引:对于每个索引键的查询,返回匹配的所有行(可以是0,或多个)
假设我现在要根据name查询,首先name可能有多个,因为一个公司或学校叫小明的不止一个人,但是你要用name去查,你必须name是索引,我们先给它加个索引,因为要达到ref级别,

五.range级别

检索指定范围的行,查找一个范围内的数据,where后面是一个范围查询(between,in,><>=);
注:in有时会失效,导致为ALL;
现在我们写一个查询语句,前提是,tid一定是一个索引列,如果是id的话,就用主键索引,也就是唯一索引,值不可以重复,这个时候我们范围查询的时候要用它来做条件:

六.index级别

查询全部索引中的数据
讲解:假如我有一张表,里面有id name age,这个时候name是一个单值索引,一旦name被设定成索引,它就会成为B树一样,经过各种算法将name里面的值像树一样进行分类,这个时候我where name=**,就相当于把这颗B树查了一遍;也就是说,你把name这一列给查了一遍;

七.ALL级别
查询全部表数据,就是select name from
student;
其中name不是所有;
如果你查的这一列不是索引,就会导致全表扫描,所以要避免全表扫描;

普通索引

创建索引:

create index indexName ON table_name(column_name)--indexName:自定义索引名;table_name:对应的表名;column_name:对应的列名

如果是CHAR,VARCHAR类型length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定length

修改表结构(添加索引)

alter table tableName add index indexName(colunName)--tableName:表名称;indexName:索引名称;colunName;列名称

创建表的时候直接指定

create table mytable(
	id int not null,
	username varchar(16) not null,
	index[indexName](username(length))
);
//indexName:索引长度;username:自定义的列名;length:给列设置的长度

删除索引的语法

drop index[indexName] on mytable;
//indxName:索引名称;mytable:定义的表名

唯一索引

它与前面的普通索引类似,不同的就是,索引列的值必须唯一,但运行有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

创建索引

create unique index indexName on mytable(username(length))
//indexName:索引名;mytable:表名;username:列名;length:列长度

修改表结构

create unique index indexName ON mytable(username(length))
//indexName:索引名;mytable:表名;username:列名;length:列的长度

创建表的时候直接指定

create table mytable(
id int not null,
username varchar(16) not null,
unique[indexName](username(length))
);

使用ALTER命令添加和删除索引

有四种方式来添加数据表的索引:
①、alter table tbl_name add primary key(column_list):该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
②、alter table tbl_name add unique index_name(column_list):这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
③、alter table tbl_name add index_name(column_list):添加普通索引,索引值可能出现多次。
④、alter table tbl_name add fulltext index_name(column_list):该语句指定了索引为fulltext,用于全文索引。

以下实例为在表中添加索引

mysq>alter table testalter_tbl add index(c);

你还可以在alter命令中使用drop字句来删除索引。尝试以下实例删除索引:

alter table testalter_tbl drop primary key;

删除主键时只需要指定primary key,但在删除索引时,你必须知道索引名。

显示所有信息

你可以使用show index命令来列出表中的相关的所有信息。可以通过添加\G来格式化输出信息。
尝试以下实例:

mysql?show index from table_name;\G