MySQL-索引

概述

索引是一个单独的，存储在磁盘上的数据结构。包含着对数据表内所有记录的引用指针。

使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

虽然索引大大提高了查询速度，同时却会降低更新表的速度。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间。

类别

聚集索引（Primary Key）：：InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引；
单列索引：即一个索引包含单个列；
组合索引：在表的多个字段组合上创建的索引，只有查询条件中使用了这些字段的左边字段时，索引才会被使用。使用时遵循最左前缀集合；
唯一索引（Unique）：索引列的值必须唯一，但允许有空值；如为组合索引，则组合必须唯一；主键索引为特殊的唯一索引，不允许为空；
普通索引（Key）：MySQL中的基本索引类型，允许重复与空值；
**全文索引（FULLTEXT)**：允许在CHAR,VARCHAR与TEXT类型的列上创建，支持值的全文查找，允许重复与空值；
空间索引（SPATIAL）：对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY,POINT,LINESTRING和POLYGON，必须为非空。

创建原则

并非越多越好，如果一个表包含大量索引，不仅占用磁盘空间，而且会影响DML语句性能，因为涉及数据更改时，索引也会相应的调整；
对于数据量小的表避免索引，可能查询花费时间比遍历索引还要短，无法尝试优化效果；
对字符串进行索引时，尽量指定短索引，能够节省空间的同时，索引的高速缓存也能容纳更多的键值，加快速度；

SQL语句

创建

//使用ALTER TABLE创建
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

//使用CREATE INDEX创建
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON table_name [key_part,...]
    [index_option]
    [algorithm_option | lock_option]...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}
    
lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

删除

//使用ALTER TABLE删除
ALTER TABLE table_name DROP INDEX index_name
//使用DROP INDEX删除
DROP INDEX index_name ON table_name;