MySQL索引-索引的分类和创建

索引类型

• 数据类型
  • B+树索引
  • Hash索引
  • FullText全文索引
• 物理存储
  • 聚簇索引
  • 二级索引
• 字段特性
  • 主键索引
  • 唯一索引
  • 普通索引
  • 前缀索引
• 字段个数
  • 单列索引
  • 联合索引

创建索引

• 创建表时一同创建
• 创建表后单独创建
• 创建表后通过修改表结构创建

可以通过 SHOW INDEX FROM test_table;查看表中的索引情况

CREATE TABLE test_table (id INT AUTO_INCREMENT,              -- 用于主键索引name VARCHAR(255) NOT NULL,         -- 用于普通索引email VARCHAR(255) NOT NULL,        -- 用于唯一索引phone_number VARCHAR(20),           -- 用于前缀索引city VARCHAR(100),                  -- 用于联合索引state VARCHAR(100),                 -- 用于联合索引PRIMARY KEY (id)                    -- 创建主键索引
);

主键索引：不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。

PRIMARY KEY (id)

唯一索引：索引字段的值必须唯一，但允许有空值 。唯一索引不一定创建在唯一列上，在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。

CREATE UNIQUE INDEX uniq_email ON test_table (email);

普通索引：这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

CREATE INDEX idx_name ON test_table (name);

CREATE INDEX idx_phone_prefix ON test_table (phone_number(10));

联合索引

何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效

率有很大影响。

如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查

询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

CREATE INDEX idx_city_state ON test_table (city, state);

FullText索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

CREATE FULLTEXT INDEX idx_description ON test_table (description);

全文索引有自己的语法格式，使用 match 和 against 关键字

SELECT * 
FROM test_table
WHERE MATCH(description) AGAINST('w');

查看FullText索引相关参数

SELECT *  FROM test_table WHERE MATCH(description) AGAINST('wwwww*' in boolean mode);

全文索引使用注意事项：

• 全文索引必须在字符串、文本字段上建立。
• 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：4-84）
• 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
• 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*