mysql索引简述

一：什么是索引

　　索引本身是一个独立的存储单位，在该单位里边有记录着数据表某个字段和字段对应的物理空间。索引内部有算法支持，可以使查询速度非常快。

　　有了索引，我们根据索引为条件进行数据查询，速度就非常快

　　1，索引本身有“算法”支持，可以快速定位我们要找到的关键字（字段）

　　2，索引字段与物理地址有直接对应，帮助我们快速定位要找到的信息

　　一个数据表的全部字段都可以设置索引

二，索引类型

　　1，四种类型：

　　　　　　 (1) 主键 parimary key

　　　　　　　　auto_increment必须给主键索引设置，索引列的值要求不能为null，唯一

　　　　　　(2)唯一 unique index

　　　　　　　　索引列的值不能重复，但允许有空值

　　　　　　(3)普通索引 index

　　　　　　　　索引列的值可以重复。

　　　　　　(4)全文索引 fulltext index

　　　　　　　　Myisam数据表可以设置该索引

　　　　2，复合索引

　　　　　　索引是由两个或更多的列组成，就称复合索引或联合索引。

　三，创建索引

　　1，创建表时

　　　　1)，创建一个member表时，并创建各种索引。　　

　　　　　　create table member(
　　　　　　　　id int not null auto_increment comment '主键',
　　　　　　　　name char(10) not null default '' comment '姓名',
　　　　　　　　height tinyint not null default 0 comment '身高',
　　　　　　　　old tinyint not null default 0 comment '年龄',
　　　　　　　　school varchar(32) not null default '' comment '学校',
　　　　　　　　intro text comment '简介',
　　　　　　　　primary key (id),  // 主键索引
　　　　　　　　unique index nm (name), //唯一索引,索引也可以设置名称，不设置名字的话，默认字段名
　　　　　　　　index (height),   //普通索引
　　　　　　　　fulltext index (intro) //全文索引
　　　　　　)engine = myisam charset = utf8;

　　　　2)，给现有数据表添加索引

　　　　　　alter table member add primary key(id);  //注：一般设置主键后，会把主键字段设置为自增。（alter table member  modify id  int not null auto_increment comment '主键';）

　　　　　　alter table member add unique key nm (name);

　　　　　　alter table member add index(height);

　　　　　　alter table member add fulltext index(intro);

　　　　3)，创建一个复合索引（索引没有名称，默认把第一个字段取出来作为名称）

　　　　　　alter table member add unique key nm (name,height);

　　2，删除索引

　　　　alter table 表名 drop primary key;//删除主键索引

　　　　注意：该主键字段如果存在auto_increment 属性，需要先删除。（alter table 表名modify 主键 int not null comment '主键'）

　　　　去除去数据表字段的auto_increment属性；

　　　　alter table 表名 drop  index 索引名称;//删除其它索引（唯一，普通，全文）

　　　　eg: alter table member drop index nm;

四、explain 查看索引是否使用

　　具体操作： explain 查询sql语句

　　这是没有设置主键索引的情形：（执行速度、效率低）

　　

　　加上主键后：

　　

五、索引适合的场景

　　1、where查询条件(where之后设置的查询条件字段都适合做索引)。

　　2、排序查询（order by字段）

六、索引原则

　　1、字段独立原则

　　　　select * from emp where empno = 1325467;//empno条件独立，使用索引

　　　　select * from emp where empno+2 = 1325467;//empno条件不独立，只有独立的条件字段才可以使用索引

　　2，左原则

　　　　模糊查询，like & _

　　　　%：关联多个模糊内容

　　　　_：关联一个模糊内容

　　　　select * form 表名 where a like "beijing%";//使用索引

　　　　select * from 表名 where a like "beijing_";//使用索引

　　　　select * from 表名 where a like "%beijing%”;//不使用索引

　　　　select * from 表名 where a like "%beijing";//不使用索引

　　3，复合索引 index（a,b)

　　　　select * from 表名 where a like "beijing%";//使用索引

　　　　select * from 表名 where b like "beijing%;//不使用索引

　　　　select * form 表名 where a like "beijing%" and b like "beijng%";//使用索引

　　4，or原则

　　　　OR左右的关联条件必须都具备索引，才可以使用索引。

　　　　eg（index(a)、index(b)）

　　　　select * from 表名 where a = 1 or b = 1;//使用索引

　　　　select * from 表名 where a = 1 or c = 1;//没有使用索引