浅谈 mysql 中的索引

前言

作为一个Java开发，必然要接触数据库这些东西。例如

• 关系型数据库：mysql，sql Server ，sqlite，h2 等
• 非关系型数据库: mongodb ，hbase ，redis
• 图数据库：neo4j

我们做的最多的操作就是对数据库的 CRUD，其中查询是我们使用最多的情况。
而且随着数据量的越来越庞大，对于查询的效率有了极大的挑战，所以就有了索引，可以大大提高 sql 查询的效率。

mysql 是我们使用的关系型数据库，其索引主要包含哪些呢？都用在什么场景当中？

mysql 索引种类

• 普通索引：仅加速查询
• 唯一索引：加速查询 + 列值唯一（可以有null）
• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
• 全文索引：对文本的内容进行分词，进行搜索

索引类型

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

• FULLTEXT
  即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
• HASH
  由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。
  HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
• BTREE
  BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
• RTREE
  RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

这里我们要明确索引种类和索引类型的区别，索引的种类都是在其类型的基础之上的 。

主键索引

必须为主键字段创建一个索引，这个Mysql索引就是所谓的“主索引”。主索引与唯一索引的唯一区别是：定义时使用的关键字是PRIMARY，唯一索引使用的是UNIQUE.

唯一索引

如果确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候，就应该用关键字UNIQUE把它定义为一个唯一索引，Mysql会在有新纪录插入数据表时，自动检查新纪录的这个字段的值是否已经在某个记录的这个字段里出现过了。如果是，mysql将拒绝插入那条新纪录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
唯一索引页可以使用多个值共同构建。

创建索引我们可以在创建表的时候就创建好索引，也可以使用 **ALTER TABLE ADD INDEX ** , 也可以使用 CREATE INDEX ON TABLE

组合索引（联合索引）

遵循”最左前缀“原则。

创建索引

-- PRIMARY KEY（主键索引） 
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
-- UNIQUE(唯一索引)     
ALTER TABLE `table_name` ADD UNIQUE (`column1`， `column2`)
-- INDEX(普通索引)     
ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 
-- FULLTEXT(全文索引)     
ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
-- 组合索引  
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )