索引（重点！）

索引特性（重点）

索引是什么呢？
它可以提高数据库的性能，不用加内存，不用改程序，不用调sql， 查询速度提高成百上千倍，所以它的价值在于提高一个海量数据库的检索速度。以空间换时间

索引就像是一本书的目录一样，通过目录，我们可以很快的找到读取到想要的内容。非常方便，提高了效率，但是另一方面：目录（索引）也要占去一部分空间，造成资源浪费。而且当我们在进行数据更新和删除等操作时，目录也要更新（相应的索引也要更新),影响性能。

常见索引：

• 主键索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)

案列：创建一个海量数据表

• 查询员工编号为998877的员工

   select * from EMP where empno = 998877;

如果放在公网中，假如同时有1000个人并发查询，很可能死机。

• 解决方法，创建索引

alter table EMP add index(empno);

• 换一个员工编号，在测试查询速度

 select * from EMP where empno = 123456;

1. 基本原理

索引说明：

• 占用磁盘空间
• 当添加一条记录，除了添加到表中，还要维护二叉树，速度有影响，但不大。
• 当我们添加一个索引时，不能够解决所有查询的问题，需要分别给字段建立索引。
• 索引是以空间换时间的

2. 创建索引

2.1 创建主键索引

• 方式一
  在创建表的时候，直接在字段名后指定primary key

  create table user1(id int primary key,name varchar(30));

• 方式二
  在创建表的最后，指定某列或某几列为主键索引

create table user2(id int,name varchar(30),primary key(id));

• 方式三：

create table user3(id int,name varchar(30));

创建表以后再添加主键

alter table user3 add primary key(id);

主键索引的特点：

一个表中，最多有一个主键索引
主键索引的效率高（主键不可重复）
创建主键索引的列，它的值不能为null，且不能重复
主键索引的列基本上是int

2.2 唯一索引的创建

• 方式一

--在表定义时，在某列后直接指定unique 唯一属性。
create table user4(id int primaty key,name varchar(30) unique);

• 方式二

-- 创建表时，在表的后面指定列为unique
create table user5(id int primary key, name varchar(30),unique(name));

• 方式三

create table user6(id primary key,name varchar(30));
alter table user6 add unique(name);

唯一索引的特点：

• 一个表中，可以有多个唯一键索引
• 查询效率高
• 如果在某列建立唯一索引，这列不能有重复数据
• 如果一个唯一索引上指定not null，等价于主键索引

3. 普通索引的创建

• 方式一：

create table user8(id int primary key,
     name varchar(20), 
     email varchar(30), 
     index(name) --在表的定义最后，指定某列为索引
  );

• 方式二

create table user9(id int primary key,  name varchar(20),  email varchar(30)); 
alter table user9 add index(name); --创建完表以后指定某列为普通索引

• 方式三

create table user10(id int primary key,  name varchar(20),  email varchar(30)); 
-- 创建一个索引名为 idx_name 的索引   
create index idx_name on user10(name);

普通索引特点：

一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
如果某列需要创建索引，但是该列有重复的值，我们就应该使用普通索引

2.4 全文索引的创建

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求
表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用
sphinx的中文版(coreseek)。

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),    
    body TEXT,    
    FULLTEXT (title,body) 
  )engine=MyISAM;

3. 查询索引

• 方法一：show keys from 表名；
• 方法二: show index from 表名；
• 方法三：desc 表名；

4. 删除索引

• 第一种方法-删除主键索引：alter table 表名 drop primary key;
• 第二种方法-其他索引的删除：
  alter table 表名 drop index 索引名； 索引名就是show keys from 表名 中的 Key_name 字段mysql> alter table user10 drop index idx_name;
• 第三种方法方法：drop index 索引名 on 表名 mysql> drop index name on user8;

5. 索引创建原则

• 比较频繁作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
• 更新非常频繁的字段不适合作创建索引
• 不会出现在where子句中的字段不该创建索引
• 是一个范围内的检索，且检索的返回的数据要小于表中的数据的30%