all index range ref eq_ref const system 索引type说明

背景知识

在使用sql的过程当中常常须要创建索引，而每种索引是怎么处罚的又是怎么起到做用的，首先必须知道索引和索引的类型。算法

索引类型type

咱们能够清楚的看到type那一栏有index ALL eq_ref，他们都表明什么意思呢？sql

首先类型有许多，这里我只给你们介绍用的最多的几种类型：数据库

system>const>eq_ref>ref>range>index>ALL性能

越往左边，性能越高，好比system就比ALL类型性能要高出许多，其中system、const只是理想类型，基本达不到；优化

咱们本身实际能优化到ref>range这两个类型，就是你本身写SQL，若是你没优化基本上就是ALL，若是你优化了，那就尽可能达到ref>range这两个级别；spa

左边基本达不到！3d

因此，要对type优化的前提是，你须要有索引，若是你连索引都没有建立，那你就不用优化了，确定是ALL.....；orm

Type级别详解

一.system级别

索引类型能是system的只有两种状况：blog

1.只有一条数据的系统表索引

只有一条数据的系统表，就是系统里自带一张表，而且这个表就一条数据，这个基本上就达不到，这个是系统自带的表，并且就一条数据，因此基本达不到；

2.或衍生表只能有一条数据的主查询

这个是能够实现的，可是在实际开发当中，你不可能去写一个这么个玩意儿，不可能公司的业务去让你把SQL索引类型写实system...

SQL语句:select * From (select * From test01) t where tid = 1;//前面须要加explain

执行结果：

就是把它凑出来便可；

我之因此能达到system，是由于我知足了它的第二个条件；

二.const级别

const条件稍微低一点，可是基本上也达不到；

1.仅仅能查出一条的SQL语句而且用于Primary key 或 unique索引；

这个我就不说了把，都知道，因此在企业里根本不可能实现，能查出来一条SQL语句，你的索引还必须是Primary key或unique；

SQL语句：select * tid From test01 where tid = 1;//前面须要加explain

执行结果：

根据tid找，由于tid是我设置的主键，主键就是Primary key，而且只能有一条数据，我表里面原本就一条，因此我知足了；

三.eq_ref级别

惟一性索引：对于每一个索引键的查询，返回 匹配惟一行数据（有且只有1个，不能多，不能0）;

解说：好比你select ...from 一张表 where 比方说有一个字段 name = 一个东西，也就是咱们以name做为索引，假设我以前给name加了一个索引值，我如今根据name去查，查完后有20条数据，我就必须保证这二十条数据每行都是惟一的，不能重复不能为空！

只要知足以上条件，你就能达到eq_ref，固然前提是你要给name建索引，若是name连索引都没，那你确定达不到eq_ref;

此种状况常见于惟一索引和主键索引；

好比我根据name去查，可是一个公司里面或一个学校里面叫name的可能不止一个，通常你想用这个的时候，就要确保你这个字段是惟一的，id就能够，你能够重复两个张三，可是你身份证确定不会重复；

添加惟一键语法：alter table 表名 add constraint 索引名 unique index(列名)

检查字段是否惟一键：show index form 表名；被展现出来的皆是有惟一约束的；

以上级别，均是可遇不可求！！！！

四 .ref级别

到ref仍是问题不大的，只要你上点心，就能够达到；

非惟一性索引：对于每一个索引键的查询，返回匹配的全部行（能够是0，或多个）

假设我如今要根据name查询，首先name可能有多个，由于一个公司或学校叫小明的不止一我的，可是你要用name去查，你必须name是索引，咱们先给它加个索引，由于要达到ref级别，因此这里我给它加一个单值索引，

单值索引语法：alter table 表名 索引类型 索引名（字段）

如今咱们根据索引来查数据，这里我假设我写的单值索引；

alter table student add index index_name (name);

这个时候咱们再去编写sql语句：

alter table student add index index_name (name);

由于name是索引列，这里假设有两个叫张三的，ref级别规则就是能查出多个或0个，很显然能查出来多个，那这条SQL语句，必然是ref级别！

执行结果：

数据：

五.range级别

检索指定范围的行，查找一个范围内的数据，where后面是一个范围查询 （between,in,> < >=);

注：in 有时会失效，致使为ALL；

如今咱们写一个查询语句，前提是，tid必定是一个索引列，若是是id的话，就用主键索引，也就是惟一索引，值不能够重复，这个时候咱们范围查询的时候要用它来作条件：

 EXPLAIN SELECT t.* FROM student t WHERE t.tid BETWEEN 1 AND 2; ;//查询tid是1到2；

查看执行结果：

六.index级别

查询所有索引中的数据

讲解：假设我有一张表，里面有id name age，这个时候name是一个单值索引，一旦name被设定成索引，它就会成为B树同样，通过各类算法将name里面的值像树同样进行分类，这个时候我where name = **，就至关于把这颗B树查了一个遍，

也就是说，你把name这一列给查了一遍；

SQL语句：select id From student;//我只查被索引声明的列，必然就是index了；

执行结果：

七.ALL级别

查询所有表数据，就是select name From student;

其中 name 不是索引；

若是你查的这一列不是索引，就会致使全表扫描，因此要避免全表扫描；

执行结果：

索引的分类:

主键索引（PRIMAY KEY）

惟一索引（UNIQUE）

常规索引（INDEX）

全文索引（FULLTEXT）

主键索引

主键：

　　某一个属性组能惟一标识一条记录

　　如：学生表（学号，姓名，班级，性别等等），学号时惟一标识的，能够做为主键

特色：

　　最多见的索引类型

　　确保数据记录的惟一性

　　肯定特定数据记录在数据库中的位置

实例：

CREATE TABLE `表名`（、

　　`GradeID` INT(11)  AUTO_INCREMENT PRIMARY KEY,

　　#或 PRIMARY KEY(`GradeID`)

）

惟一索引

做用：

      避免同一个表中某数据列中的值重复

与主键索引的区别

　　主键索引只能有一个

　　惟一索引可有多个

实例：

CREATE TABLE `Grade`（、

　　`GradeID` INT(11)  AUTO_INCREMENT PRIMARY KEY,

　　`GradeName` VARCHAR(32) NOT NULL UNIQUE

　　#或 UNIQUE KEY ` GradeID`(`GradeID`)

常规索引

做用：

快速定位特定数据

注意：

index 和 key 关键字均可以设置常规索引

应加在查询条件的字段

不易添加太多常规索引，影响数据的插入，删除和修改操做

实例：

##建立表时添加

CREATE TABLE `result`{

　　//省略一些代码

　　INDEX / KEY `ind` (`studentNo`,`subjectNo`)

}

##建立后追加

ALTER TABLE `result` ADD  INDEX `ind` (`studentNo`,`subjectNo`);

全文索引

做用：

快速定位特定数据

注意：

只能用于MyISAM类型的数据表

只能用于CHAR ,VARCHAR,TEXT数据列类型

使用大型数据集

实例：

CREATE TABLE `student`(

　　#省略一些sql语句

　　　　FULLTEXT(`StudentName`)

)ENDINE=MYISAM;

ALTER TABLE employee ADD FULLTEXT(`first_name`)