优化之索引（一）

四种索引：

主键索引  唯一索引   全文索引  普通索引

1. 添加索引

创建索引前后用select  *  from  table  where  idno=4  对比速度

对于创建索引后查询速度变快原理： 二叉树算法-->索引文件  效率：检索10次 可扫描2的10次方的数  

同时索引记录的是磁盘物理位置

1.1 主键索引添加

当一张表，把某个列设为主键的时候，则该列就是主键索引

create  table  t1(id int unsigned primary key auto_increment,,

name varchar(32) not  null default '');

或alter  table  t1  add primary  key  (colum1)

1.2普通索引

一般来说 普通索引的创建  先创建表  然后再创建普通索引

create  table t2(

id int unsigned,

name varchar(32)  mysql 

)

create  index   索引名  on  (列)

1.3创建全文索引

主要是针对文本的检索，文本的检索，

create  table  articles （

id  int  unsigned  auto_increment  not  null  primary  key,

title  varchar(200),

body  text,

fulltext  (title , body)

) engine=myisam charset  utf8;

insert  into  articles  (title, body) values

('mysql tutorial', 'dbms stands for  database ...'),

('how to use mysql well', 'after you went  through a ...'),

('optimizing  mysql ', 'in this tutorial  we  will show ...'),

('10001 mysql  titicks', '1.  never  run   mysqld  as  root . 2.  .....'),

(' mysql vs ', '1.  never  run   mysqld  as  root . 2.  .....'),

('mysql security', 'mysqld  as  root2.  .....);

如何使用全文索引：

错误用法：

select  *  from  articles  where  body  like  ‘%mysql%';(不会用到全文索引）

explain select *  from   articles  where  body  like  '%mysql%; 

正确的用法是：

select *  from  articles  where match(title, body ) against('database');

说明：

在mysql 中fulltext索引只针对myisam 生效

针对引文生效->sphinx(coreseek)技术处理中文

使用方法是 match（字段名)against（’关键字')

全文索引一个 叫 停止词  因为在一个文本中， 创建索引是一个无穷大的数，因此对一些常见词和字符，

就不创建这些词  称为停止词

1.4唯一索引 

当表的某列指定为unique约束时，这列就是一个唯一索引

create table  t3(id int  primary  key  auto_increment,  name  varchar(32)  unique);

name列是否可以为null

insert into  t4   values(1,null);

unique字段可以为null，并可以有多个

主键字段，不能为null，也不能重复

或创建表后再创建唯一索引

create table t5(id  int  primary key  auto_increment, name  varchar(32));

create  unique index  索引名  on  表名 （列表...);

2.查询索引

desc 表名 【该方法的缺点是：不能够显示索引名】

show index(es） from  表名 \G

3.删除索引

alter  table  表名  drop  index  索引名;

如果删除主键索引：

alter table 表名  drop  primary key;

4.修改索引

先删除再重新创建

5.索引代价

磁盘占用

 对dml(update delete   insert)操作影响  会变慢   因二叉树重整

6.那些列上适合添加索引

较频繁的作为查询条件字段应该创建索引

select  *  from  emp  where  empno = 1

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

select  *  from  emp  where  sex  =  '男'

跟新非常频繁的字段不适合创建索引

select  *  from  emp  where  logincount =1

不会出现在where子句中字段不该创建索引