MySQL：索引的类型及相关优化

一、索引类型

MySQL的索引类型分为两种：BTREE和HASH。在创建索引时可以为其指定任意一种索引类型。其中索引有如下几种：

• 单列索引
  
  • 普通索引index :加速查找
• 唯一索引
  
  • 主键索引：primary key ：加速查找+约束（不为空且唯一）
  • 唯一索引：unique：加速查找+约束 （唯一）
• 联合索引
  
  • primary key(id,name):联合主键索引
  • unique(id,name):联合唯一索引
  • index(id,name):联合普通索引
• 全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
• 空间索引spatial

关于BTREE索引可以参考这篇文章：MySQL的InnoDB索引原理详解

二、DDL

MySQL创建索引的语法：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

其中各个部分的解释：

• key_part:

  col_name [(length)] [ASC | DESC]

• index_option:

  KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

  • Table 13.2 InnoDB Storage Engine Index Characteristics

    Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
    Primary key	BTREE	No	No	N/A	N/A
    Unique	BTREE	Yes	Yes	Index	Index
    Key	BTREE	Yes	Yes	Index	Index
    FULLTEXT	N/A	Yes	Yes	Table	Table
    SPATIAL	N/A	No	No	N/A	N/A

  • Table 13.3 MyISAM Storage Engine Index Characteristics

    Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
    Primary key	BTREE	No	No	N/A	N/A
    Unique	BTREE	Yes	Yes	Index	Index
    Key	BTREE	Yes	Yes	Index	Index
    FULLTEXT	N/A	Yes	Yes	Table	Table
    SPATIAL	N/A	No	No	N/A	N/A

  • Table 13.4 MEMORY Storage Engine Index Characteristics

    Index Class	Index Type	Stores NULL VALUES	Permits Multiple NULL Values	IS NULL Scan Type	IS NOT NULL Scan Type
    Primary key	BTREE	No	No	N/A	N/A
    Unique	BTREE	Yes	Yes	Index	Index
    Key	BTREE	Yes	Yes	Index	Index
    Primary key	HASH	No	No	N/A	N/A
    Unique	HASH	Yes	Yes	Index	Index
    Key	HASH	Yes	Yes	Index	Index

• index_type:

  USING {BTREE | HASH}

  Storage Engine	Permissible Index Types
  InnoDB	BTREE
  MyISAM	BTREE
  MEMORY/HEAP	HASH, BTREE

• algorithm_option:

  ALGORITHM [=] {DEFAULT | INPLACE | COPY}

• lock_option:

  LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

具体可参考MySQL 5.7 Reference Manual CREATE INDEX Syntax

三、优化

1. 如何才能命中索引

单列索引的话只要不作死基本就不会出现什么问题，本片文章主要讲讲联合索引的优化，相关的文档可以参考 MySQL 5.7 Reference Manual : multiple-column-indexes
MySQL可以创建复合索引（即多列上的索引）。索引最多可包含16列。对于某些数据类型，您可以索引列的前缀（请参见第8.3.4节“列索引”）。
在查询条件中用到索引的前一个字段、前两个字段、前三个字段、前N个字段时才会使用索引。如果在索引定义中以正确的顺序指定列，则单个复合索引可以加速同一表上的多种查询。
多列索引可以视为通过连接两个索引字段值来创建的有序数组。（原文A multiple-column index can be considered a sorted array, the rows of which contain values that are created by concatenating the values of the indexed columns.）

假设表的结构为：

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

name索引是last_name和first_name列的联合索引。这个索引用于last_name 和first_name values的组合范围查询。它还可以用于仅指定last_name值的查询，因为该列是索引的最左前缀。所以下面的这些查询将会使用到这个索引：

SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test  WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test  WHERE last_name='Widenius'  AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test  WHERE last_name='Widenius'  AND first_name >='M' AND first_name < 'N';

但是下面的这些查询将不会使用这个联合索引：

SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test  WHERE last_name='Widenius' OR first_name='Michael';

2. 使用额外的列来代替索引

可以将多个列的hash值存放到一个列中来代替复合索引。如果这个列的值可以非常短、不会重复，可能比设置大量列的索引来的更高效。举个栗子：

SELECT * FROM tbl_name
    WHERE hash_col=MD5(CONCAT(val1,val2))
    AND col1=val1 AND col2=val2;

四、参考资料

MySQL官方文档：SQL语句语法