[MySQL]索引

索引类型

MySQL的索引类型主要有以下四种：

类型	功能
B-Tree	全值匹配 匹配最左前缀 匹配列前缀 匹配范围值 匹配某一列并范围匹配另外一列 只访问索引的查询
Hash	精确匹配
R-Tree	空间数据索引，可以从任意维度匹配
全文索引	匹配文本中的关键字

聚簇索引

聚簇索引并不是索引的类型，而是一种数据存放的方式，具体细节依赖存储引擎的实现方式。有如下特点：

• 数据行存储在索引的叶子节点上
• 数据行和相邻的键值紧凑地存储在一起

InnoDB将通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式地定义一个主键来作为聚簇索引。聚簇所有有如下优点：

• 把相关的数据保存在一起
• 数据和索引放在一起，这样数据的访问速度更快

但也有缺点：

• 插入速度严重依赖于插入顺序，最好是按照主键的顺序
• 更新聚簇索引列的代价很高
• 插入或修改主键的时候可能导致页分裂
• 行比较稀疏时，数据分布在不同的页上会导致全表扫描变慢
• 二级索引需要两次索引查找，因为保存的“指针”其实是主键，这样换来的好处是在移动行时无需移动数据

注：对于MyISAM来说，主键索引与其他的索引没有什么区别。

覆盖索引

如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询？当一个索引包含所有查询的字段的值时，就称之为覆盖索引。覆盖索引有如下优点：

• MyISAM只缓存索引数据
• InnoDB中的二级索引查询时不需要再通过主键二次查询

当发起一个被索引覆盖的查询时，在EXPLAIN中可以看到：

Extra： Using index

没有索引能够覆盖WHERE之后的条件时，可以考虑使用子查询来进行优化。但是需要注意的是：子查询也是有一定的开销的。

其他

压缩

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存。默认只压缩字符串，但通过配置也可以做到对整数压缩。

这样能减少空间开销，缺点也很明显：因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法再索引块使用二分，而只能从头开始扫描。

重复索引

比较明显的重复索引是：

CREATE TABLE test(
    ID INT NOT NULL PRIMARY KEY,
    A INT NOT NULL,
    B INT NOT NULL,
    UNIQUE(ID),
    INDEX(ID)
)ENGINE=InnoDB

在样“无意间”在ID上就建了3个索引。

另外对于B-Tree来说，如果已经创建了索引（A，B），那么再建索引（A）就是重复的了。

相关语法

最简单的创建索引的方法：

CREATE INDEX indexName ON mytable(username(length));

如果创建的是唯一索引，那么用UNIQUE修饰即可：

CREATE UNIQUE INDEX indexName ON mytable(username(length))

如果创建的是主键，需要在创建表的时候指定：

CREATE TABLE mytable( 
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,        PRIMARY KEY(ID)  
);

组合索引可以用ALTER来做：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

全文索引：

CREATE TABLE article (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content) --在title和content列上创建全文索引
);

使用特定的语法才能用到全文索引：

SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')

----------

end