mysql 索引汇总

mysql 索引分类

MySQL中的索引可以按照不同的分类标准进行分类，常见的分类有以下几种：

1. 根据索引的数据结构分类：B-Tree索引、Hash索引、Full-text索引、r-tree索引等。

1) full-text索引
full-text在mysql里仅有myisam支持它，而且支持full-text的字段只有char、varchar、text数据类型。
full-text主要是用来代替like "%***%"效率低下的问题
全文索引,是一种通过建立倒排索引,快速匹配文档的方式。

2) b-tree索引
b-tree在myisam里的形式和innodb稍有不同
在 innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的b-tree差不多，只是还存放了指向主键的信息.

而在myisam里，主键和其他的并没有太大区别。不过和innodb不太一样的地方是在myisam里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息.

B-Tree索引的应用场景

等值匹配
可用于=!=<>INNOT IN<=>查询语句的优化
范围匹配
可用于 >>=<<=BTEWEEN AND等范围查询语句的优化
匹配最左前缀
对于 name like bai% 这种后模糊匹配的查询,是可以利用name字段上建立的索引来优化查询的,但是对于name like %bai这种前模糊匹配的查询则没有办法使用索引了
覆盖索引
B-Tree索引的key存放的是字段的值,如果key中包含所有需要查询字段的值,我们就称之为覆盖索引,覆盖索引能够极大的提高性能。
排序
B-Tree索引是排好序的,所以MySQL可以用来优化ORDER BY 和 GROUP BY等操作。

3) hash索引
目前我所知道的就只有memory和ndb cluster支持这种索引.
hash索引由于其结构，所以在每次查询的时候直接一次到位，不像b-tree那样一点点的前进。所以hash索引的效率高于b-tree，但hash也有缺点，主要如下：
(1)由于存放的是hash值，所以仅支持<,=,>以及in操作.
(2)hash索引无法通过操作索引来排序，这是因为存放的时候经过hash计算，但是计算的hash值和存放的不一定相等，所以无法排序.
(3)在组合索引里，无法对部分使用索引.
(4)不能避免全表扫描，只是由于在memory表里支持非唯一值hash索引，就是不同的索引键，可能存在相同的hash值.
(5)当存在大量相同hash值得时候，hash索引的效率会变低.

应用场景
只有精确匹配索引所有列的查询才有效,因为哈希索引是利用索引的所有列的字段值来计算哈希值的。
只支持等值比较查询,不能用于范围查询。
哈希索引的只包含索引字段的哈希值和指向数据的指针,所以不能使用索引中的值来避免读取行。
哈希索引的数据并不是顺序存储的,无法用于排序。

4) r-tree索引
r-tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。
相对于b-tree，r-tree的优势在于范围查找.

2. 根据索引的主键和外键关系分类：主键索引、外键索引。

3. 根据索引是否唯一分类：唯一索引、非唯一索引。

4. 根据索引是否被物理地存储在表中分类：聚集索引（主键索引）、非聚集索引。

5. 根据查询方式分类：最左前缀索引、覆盖索引等。

-- 创建一个普通索引
CREATE INDEX index_name ON table_name(column_name);
 
-- 创建一个唯一索引
CREATE UNIQUE INDEX index_name ON table_name(column_name);
 
-- 创建主键索引（也是唯一索引）
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
 
-- 创建外键索引（通常在定义外键约束时自动创建）
ALTER TABLE table_name ADD CONSTRAINT fk_constraint FOREIGN KEY (column_name) REFERENCES parent_table(parent_column);
 
-- 创建全文索引
CREATE FULLTEXT INDEX index_name ON table_name(column_name);
 
-- 创建聚集索引（通常是主键索引）
-- 在创建表时指定主键即可
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    PRIMARY KEY (column_name)
);
 
-- 创建非聚集索引
CREATE INDEX index_name ON table_name(column_name);
 
-- 创建最左前缀索引
CREATE INDEX index_name ON table_name(column1, column2, ...);
 
-- 创建覆盖索引（索引包含了查询所需的全部数据）
CREATE INDEX index_name ON table_name(column1, column2, ...);