MySQL学习笔记（三）—— 索引

---

一、什么是索引？

索引是能提高帮助MySQL高效获取数据的一种数据结构。

例如：在10000条数据中查询某条数据，若没有索引，则需以遍历的方式查询该数据信息；若有索引，则可通过一定的数据结构存储方式查询，以提高查询效率。

二、索引的分类及代码实现

1.普通索引

1> 定义：是最基本的索引，它没有任何限制。

2> 实现：

-- 1.直接创建索引
CREATE INDEX index_name ON table_name(column_name(length))

-- 2. 修改表结构的方法添加索引
ALTER TABLE table_name ADD INDEX index_name (column_name(length))

-- 3. 创建表的时候创建索引
CREATE TABLE `table` (
    `id` int(5) NOT NULL AUTO_INCREMENT ,
    `name` char(25) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (`name`(length)) -- 普通索引
)

2.唯一索引

1> 定义：索引列的值必须唯一，但允许有空值。
2> 实现：

-- 1.直接创建索引
CREATE UNIQUE INDEX index_name ON table_name(column_name(length))

-- 2. 修改表结构的方法添加索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name(column_name(length))

-- 3. 创建表的时候创建索引
CREATE TABLE `table` (
    `id` int(5) NOT NULL AUTO_INCREMENT ,
    `name` char(25) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    PRIMARY KEY (`id`),
    UNIQUE INDEX index_name (`name`(length)) --唯一索引
)

3.主键索引

1> 定义：一个表只能有一个主键，索引列的值必须唯一，且不允许有空值。
2> 实现：

-- 创建表的时候创建索引
CREATE TABLE `table` (
    `id` int(5) NOT NULL AUTO_INCREMENT ,
    `name` char(25) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    PRIMARY KEY (`id`) -- 主键
)

4.组合索引

1> 定义：指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。例如当查询条件为where A=‘xxx’ and B='xxx’时，建立组合索引（A,B）可提高数据获取的效率。具体例子可参考该文
2> 实现：

-- 1. 修改表结构的方法添加索引
ALTER TABLE table_name ADD INDEX composition_index_name(column1_name,column2_name,column3_name); 

-- 2. 创建表的时候创建索引
CREATE TABLE `table` (
    `id` int(5) NOT NULL AUTO_INCREMENT ,
    `name` char(25) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    PRIMARY KEY (`id`),
    INDEX composition_index_name(`name`，`content`) --唯一索引
)

5.全文索引

1> 定义：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。作用于char、varchar和text的字段。
2> 实现：

-- 1.直接创建索引
CREATE FULLTEXT INDEX index_name ON table_name(column_name)

-- 2. 修改表结构的方法添加索引
ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_name)

-- 3. 创建表的时候创建索引
CREATE TABLE `table` (
    `id` int(5) NOT NULL AUTO_INCREMENT ,
    `name` char(25) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content) --全文索引
)

-- 4.使用全文索引 match...against...
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('匹配的字符串')

6. 其他

1> 显示表中索引信息

SHOW INDEX FROM table_name;

2> 删除索引信息

DROP INDEX index_name ON table_name ;

ALTER TABLE table_name DROP INDEX index_name ;

ALTER TABLE table_name DROP PRIMARY KEY ;

3> explain：可帮助我们分析 select 语句查询结果（例如查询次数等），让我们知道查询效率低下的原因，从而改进我们的查询。

TIPS:

1> INNODB默认索引的数据结构是Btree。
2> 索引不是越多越好。
3> 不要对经常变动的数据添加索引。
4> 数据量小的数据一般不使用索引。
5> 索引可加在常用字段上，提高平时的查找效率。
6> 索引会降低更新表的速度。因为更新表时，不仅要保存数据，还要保存一下索引文件。