索引的用法及其优缺点

索引（Index）是帮助MySQL高效获取数据的数据结构，即索引是数据结构。

1. 普通索引

1.创建方法：
(1)直接基于表创建：

       CREATE INDEX indexName ON table_name(column_name(length)); 

(2)修改表结构创建：

       ALTER table table_name ADD INDEX index_name(column_name);

(3)创建表的时候直接指定：

    CREATE TABLE table_name(
      ID INT NOT NULL,
       ...
      INDEX [index_name] (column_name(length))
    );

例：

CREATE TABLE book(　　　　　　　　　　　　　　　　　　　　　　　　　　　
id INT NOT NULL,　　　　　　　　　　　　　　　　　
bookname VARCHAR(100) NOT NULL,　　　　　　　
authors VARCHAR(100) NOT NULL,　　　　　　　
INDEX book_id(id)　　　　　　　　　
);　

2. 唯一索引(UNIQUE)

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
1.创建方式：
(1)创建索引

CREATE UNIQUE INDEX indexName ON table_name (column_name(length)) 

(2)修改表结构

ALTER table table_name ADD UNIQUE [indexName] (column_name(length)) 

(3)创建表的时候直接指定

CREATE TABLE table_name(  
ID INT NOT NULL,   
...
UNIQUE [indexName] (column_name(length))   
);  

例：

CREATE TABLE subject(  
id INT NOT NULL,   
UNIQUE id_student (column_name(length))   
);  

3. 主键索引

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”，每个表只能有一个主键，不允许出现相同的值
创建语法：

CREATE TABLE table_name(
ID INT NOT NULL,
...
PRIMARY KEY (column_name(length))
);

也可以通过修改表的方式加入主键:

ALTER table table_name ADD PRIMARY KEY (column_name);

例：

CREATE TABLE student(
id INT NOT NULL PRIMARY KEY  auto_increment,
name VARCHAR(100) NOT NULL
);

4. 全文索引

全文索引主要针对文本文件，比如文章，标题,也可以针对值中的某个单词，但效率确实不敢恭维,较少使用

ALTER TABLE table_name ADD FULLTEXT index_name ((column_name);

例：

create table article(
id int primary key auto_increment ,
title varchar(20),
content text,
fulltext(title,content)
)engine=myisam charset utf8;
 
insert into article(title,content) 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 ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');

5. 删除索引

(1)直接删除：

DROP INDEX index_name ON table_name;

(2)修改表结构删除：

ALTER TABLE table_name DROP INDEX index_name;

例：

DROP INDEX  id_student ON student;

索引的好处：
能够提高数据检索的效率，如果能够在进行排序分组操作中利用好索引，将会极大地降低CPU资源的消耗。
索引的弊端：
由于索引也是数据结构，创建索引会增加存储空间的占用，同时更新数据也会增加更新所带来的 IO 量和调整索引所致的计算量。

何时创建索引？
1. 较频繁的作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
3. 更新非常频繁的字段不适合创建索引
4. 不会出现在 WHERE 子句中的字段不该创建索引