SQL索引详解+原理探讨

SQL索引详解+原理探讨

​ 我们知道索引可以优化数据库，减少一些不必要的内存消耗。那么我们要先知道什么是索引？ 索引（Index）是帮助MySQL高效获取数据的数据结构，通俗来讲索引就好比书本的目录，加快数据库的查询速度。（MySQL官方对索引的定义）提高查询效率。

1，建立索引的意义（场景）

​ 当我们处理查询少量的数据时，MySQL能够很快的做出反应，找到我们想要的数据，但是当遇到成百上千万条数据时，MySQL会处理很长的时间来遍历数据，极大的耗费了性能。

​ 举例：建立3个未索引的表A、B、C，分别只包含列t1、t2、t3，每个表分别含有10000行数据，指为1～10000的数值，需要用以下SQL查询出结果：SELECT t1,t2,t3 FROM A,B,C WHERE t1=t2 AND t1=t3，在无索引的情况下处理此查询，可能要遍历三个表中对应列的所有值，以便得出与WHERE子句相配的那些行。而可能的组合数目为10000×10000×10000，显而易见，查询时间会很长。

​ 当我们给B表中t2和C表中t3加上索引后，数据库会拿到t1的值通过索引将t2,t3对应的值快速找到。节省性能。

索引的优点

​ 提高查询效率，消除数据分组、排序，避免“回表”查询（索引覆盖），优化聚合查询， 用于多表JOIN关联查询， 利用唯一性约束，保证数据唯一性， InnoDB行锁实现。

索引的缺点

​ 增加I/O成本， 增加磁盘空间，不合适的索引或索引过多，会降低增删改的效率

2，索引的分类

​ 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换。MEMORY/HEAP存储引擎：支持HASH和BTREE索引。索引我们分为四类来讲 单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引）。

#### 2.1、单列索引

一个索引只包含单个列，但一个表中可以有多个单列索引。

2.1.1、普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

//length字段的作用，即创建索引时，指定使用字段中的具体部分长度来创建索引
//创建时建立索引
CREATE INDEX index_name ON mytable(column(length))
CREATE INDEX index_name ON mytable(column)
//修改表结构添加索引
ALTER TABLE mytable ADD INDEX index_name (column(length))
ALTER TABLE mytable ADD INDEX index_name (column)
//删除索引
DROP INDEX index_name ON table
//查看索引

2.1.2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。（利用唯一性约束，保证数据唯一性）

//index_name 为索引名可省略
ALTER TABLE mytable ADD UNIQUE (column)

2.1.3、主键索引：是一种特殊的唯一索引，不允许有空值。（当在MySQL定义主键时，自动创建主键索引）

ALTER TABLE mytable ADD PRIMARY KEY (column)

2.2、组合索引(聚簇索引)

​ 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。组合索引是多列值组成的一个索引，专门用于组合搜索，其效率大于索引合并。

ALTER TABLE mytable ADD INDEX name_city_age (name,city,age);

2.3、全文索引

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，先将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

ALTER TABLE mytable ADD FULLTEXT index_name(column)

2.4、空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL

CREATE TABLE mytable
(g GEOMETRY NOT NULL,
SPATIAL INDEX spatIdx(g)
) ENGINE = MyISAM;

3，索引类型及原理

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。

​ 按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。
​ 按物理存储分类可分为：聚簇索引、二级索引（辅助索引）。
​ 按字段特性分类可分为：主键索引、唯一索引、普通索引、前缀索引（在字段后面加上特定长度）。
​ 按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）

1. FULLTEXT（InnoDB,MyISAM支持）

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH(Memory存储引擎支持)

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

• MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
• 不能使用 HASH 索引排序。
• HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
• HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

3. BTREE（InnoDB,MyISAM,Memory支持）

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

B-树索引是一个典型的数据结构，其包含的组件主要有以下几个：

• 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
• 分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
• 根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：

• 查询必须从索引的最左边的列开始。
• 查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
• 存储引擎不能使用索引中范围条件右边的列

4. RTREE

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。