数据库索引

索引是什么？？？

索引：是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息；好比就是一本书的目录，能加快数据库的查询速度

索引的优缺点

优点：

• 可以大大加快数据的检索速度
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
• 在使用分组和排序子句进行检索时，可以显著减少查询中分组和排序的时间
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点：

• 索引需要占用物理空间
• 创建索引和维护索引需要耗费时间
• 降低数据维护速度（对表中的数据进行增加，删除，修改时，索引也需要动态维护，这样就降低了数据的维护速速，同样也降低了效率）

索引的使用场景

索引是建立在数据库表中的某些列上的，因此一般来说这些列需要创建索引：

• 在经常需要搜索的列上-----可以加快搜索的速度
• 在作为主键的列上------强制该列的唯一性和组织表中数据的排列结构
• 在经常用在连接的列上------可以加快连接的速度
• 在经常需要根据范围进行搜索的列上------因为索引已经排序，其指定的范围是连续的
• 在经常需要排序的列上
• 在经常使用where子句的列上------加快条件判断速度

索引创建原则

• 比较频繁作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
• 更新非常频繁的字段不适合作创建索引
• 不会出现在where子句中的字段不该创建索引

常见索引有哪些？？？

• 主键索引（primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)
  当对文章字段或有大量文字的字段进行检索时，会使用到全文索引

索引的创建方式有哪些？？？

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);

主键索引的特点：
1.一个表中，最多有一个主键索引，当然可以使符合主键
2.主键索引的效率高（主键不可重复）
3.创建主键索引的列，它的值不能为null，且不能重复
4.主键索引的列基本上是int

2.创建唯一索引

• 第一种方式
  – 在表定义时，在某列后直接指定unique唯一属性。

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

• 第二种方式
  – 创建表时，在表的后面指定某列或某几列为unique

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

• 第三种方式

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

唯一索引的特点：
1.一个表中，可以有多个唯一索引
2.查询效率高
3.如果在某一列建立唯一索引，必须保证这列不能有重复数据
4.如果一个唯一索引上指定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);

普通索引的特点：
1.一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
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;

查询索引三种方法

show keys from +表名

show index from +表名

desc +表名

删除索引方法

• 第一种方法-删除主键索引：

 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;