SQL 索引

索引的基本概念

• 索引的目的在于提高查询效率，可以类比字典，如果要查 mysql 这个单词，我们肯定需要定位到 m 字母，然后从下往下找到 y 字母，再找到剩下的 sql，如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的。
• 优点
  • 提高查询速度。
  • 确保数据的唯一性。
  • 使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间。
• 缺点
  • 创建索引和维护索引需要耗费时间，这种时间随着数据量的增加而增加。
  • 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。
  • 当对表中的数据进行增加，删除和修改的时候，索引也要同步动态的维护，这样就降低了数据的增删改速度。

索引的数据结构

Hash

• MySQL 并没有显式支持 Hash 索引，而是作为内部的一种优化，具体在 Innodb 存储引擎里，会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，就为之建立 hash 索引，因此，在 MySQL 的 Innodb 里，对于热点的数据会自动生成 Hash 索引，这种 hash 索引，根据其使用的场景特点，也叫自适应 Hash 索引。
• Hash 索引就是采用一定的 Hash 算法，只需一次 Hash 算法即可立刻定位到相应的位置，速度非常快，本质上就是把键值换算成新的 Hash 值，根据这个 Hash 值来定位
• 局限性
  • Hash 索引没办法利用索引完成排序。
  • 不能进行多字段查询。
  • 在有大量重复键值的情况下，Hash 索引的效率也是极低的（出现 Hash 冲突问题）
  • 不支持范围查询。

B+树

• B+树是一种特殊的搜索树，InnoDB 存储引擎默认的底层的数据结构。
• 性质
  • 非叶子节点相当于是叶子节点的索引层，叶子节点是存储关键字数据的数据层，搜索只在叶子节点命中，树的查询效率稳定。
  • 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接，B+树只需要去遍历叶子节点就可以实现整棵树的遍历。
• B+树的出度（树的分叉数）
  • 不管是内存中的数据还是磁盘中的数据，操作系统都是按页（一页的大小通常是 4 kb，这个值可以通过 getconfig(PAGE_SIZE) 命令查看）来读取的，一次只会读取一页的数据。
  • 如果要读取的数据量超过了一页的大小，就会触发多次 IO 操作，所以在选择 m 大小的时候，要尽量让每个节点的大小等于一个页的大小。
  • 一般实际应用中，出度是非常大的数字，通常超过 100，树的高度（h）非常小，通常不超过 3

• 如上图，是一颗 b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块 1 包含数据项 17 和 35，包含指针 P 1, P 2, P 3, P 1 表示小于 17 的磁盘块，P 2 表示在 17 和 35 之间的磁盘块，P 3 表示大于 35 的磁盘块，真实的数据存在于叶子节点即 3,5,9,10,13,15,28,29,36,60,75,79,90,99，非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如 17,35 并不真实存在于数据表中。

索引的分类

• 在 MySQL 中主要有四类索引：主键索引，唯一索引，常规索引，和全文索引。

主键索引

• 主键 ：唯一标识数据库表中的每条记录。
• 特点
  • 主键必须包含唯一的值，且不能包含 NULL 值。
  • 每个表都应该有一个主键，并且每个表只能有一个主键。

唯一索引

• 作用：避免同一个表中某数据列中的值重复。
• 特点：不允许出现相同的值。

普通索引

• 作用 ：快速定位特定数据，不会去约束索引的字段的行为。
• 注意
  • 允许出现相同的索引内容。
  • 不宜添加太多常规索引，影响数据的插入，删除和修改操作。

全文索引

• 作用 ：快速定位特定数据。
• 注意 :
  • 适合大型数据集。
  • MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引。
  • MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引。
  • 只有字段的数据类型为 char, varchar, text 及其系列才可以建全文索引。
• 使用全文索引
  • 全文搜索通过 MATCH() 函数完成。
  • 搜索字符串作为 AGAINST() 的参数被给定，搜索以忽略字母大小写的方式执行，对于表中的每个记录行，MATCH() 返回一个相关性值，即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。

EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

联合索引

• 两个或更多个列上的索引被称作联合索引，利用索引中的附加列，可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。

联合索引存储结构

• 首先，有一个 T 1 表，然后表 T 1 有字段 a, b, c, d, e，其中 a 是主键，除 e 为 varchar 其余为 int 类型，并创建了一个组合索引 idx_t1_bcd(b,c,d)，然后 b, c, d 三列作为联合索引，在 B+树上的结构如下图所示。

• T 1 表中的数据如下图。

• 先看 T 1 表，他的主键暂且我们将它设为整型自增的，InnoDB 会使用主键索引在 B+树维护索引和数据文件，然后我们创建了一个组合索引（b, c, d）也会生成一个索引树，同样是 B+树的结构，只不过它的 data 部分 存储的是组合索引所在行记录的主键值（上图叶子节点紫色背景部分），所以这个组合索引是个 非聚簇索引
• 好了大致情况都介绍完了，下面我们结合这俩图来解释一下。
• 对于组合索引来说只不过比单值索引多了几列，而这些索引列全都出现在索引树上，对于组合索引，存储引擎会首先根据第一个索引列排序，如上图我们可以单看第一个索引列，如，1 1 5 12 13…它是单调递增的，如果第一列相等则再根据第二列排序，依次类推就构成了上图的索引树，上图中的 1 1 4,1 1 5 以及 13 12 4, 13 16 1, 13 16 5 就可以说明这种情况。

联合索引具体查找步骤

• 当我们的 SQL 语言可以应用到索引的时候，比如 select * from T1 where b = 12 and c = 14 and d = 3; 也就是 T 1 表中 a 列为 4 的这条记录。
• 查找步骤具体如下：
  1. 存储引擎首先从根节点（一般常驻内存）开始查找，第一个索引的第一个索引列为 1,12 大于 1，第二个索引的第一个索引列为 56,12 小于 56，于是从这俩索引的中间读到下一个节点的磁盘文件地址（此处实际上是存在一个指针的，指向的是下一个节点的磁盘位置）
  2. 进行一次磁盘 IO，将此节点值加载后内存中，然后根据第一步一样进行判断，发现数据都是匹配的，然后根据指针将此组合索引值所在的叶子节点也从磁盘中加载后内存，此时又发生了一次磁盘 IO，最终根据叶子节点中索引值关联的 主键值
  3. 根据主键值 回表 去主键索引树（聚簇索引）中查询具体的行记录。

最左前缀匹配原则

最左前缀匹配原则和组合索引的索引构建方式及存储结构是有关系的。

• 首先我们创建的 idx_t1_bcd(b,c,d) 索引，相当于创建了（b), (b, c)(b, c, d）三个索引，看完下面你就知道为什么相当于创建了三个索引。
• 我们看，组合索引是首先使用多列索引的第一列构建的索引树，用上面 idx_t1_bcd(b,c,d) 的例子就是优先使用 b 列构建，当 b 列值相等时再以 c 列排序，若 c 列的值也相等则以 d 列排序，我们可以取出索引树的叶子节点看一下。

• 索引的第一列也就是 b 列可以说是从左到右单调递增的，但我们看 c 列和 d 列并没有这个特性，它们只能在 b 列值相等的情况下这个小范围内递增，如第一叶子节点的第 1,2 个元素和第二个叶子节点的后三个元素，由于组合索引是上述那样的索引构建方式及存储结构，所以组合索引只能从多列索引的第一列开始查找，所以如果你的查找条件不包含 b 列如（c, d), ©, (d）是无法应用缓存的，以及跨列也是无法完全用到索引如（b, d)，只会用到 b 列索引。

辅助索引和覆盖索引

辅助索引

• 如果不是主键索引，就称为辅助索引或者二级索引，主键索引的叶子节点存储了完整的数据行，而非主键索引的叶子节点存储的则是主键索引值，通过非主键索引查询数据时，会先查找到主键索引，然后再到主键索引上去查找对应的数据。
• 在这里假设我们有张表 user，具有三列：ID, age, name, create_time, id 是主键，(age, create_time, name）建立辅助索引，执行如下 sql 语句：

select name from user where age > 2 order by create_time desc

• 正常的话，查询分两步：
  1. 按照辅助索引，查找到记录的主键。
  2. 按照主键主键索引里查找记录，返回 name

覆盖索引

• 但实际上，我们可以看到，辅助索引节点是按照 age,create_time,name 建立的，索引信息里完全包含我们所要的信息，如果能从辅助索引里返回 name 信息，则第二步是完全没有必要的，可以极大提升查询速度。
• 如果需要查询的字段被包含在辅助索引节点中，那么可以直接获得我们所需要的信息，按照这种思想 Innodb 针对使用辅助索引的查询场景做了优化，称为覆盖索引

聚簇索引和非聚簇索引

• 根据数据与索引的存储关联性，可以分为聚簇索引和非聚簇索引（也叫聚集索引和非聚集索引），关键在于索引的存储顺序和数据的存储顺序是否是关系的，有关就是聚簇索引，无关就是非聚簇索引。

聚簇索引: Innodb 的主键索引，非叶子节点存储的是索引指针，叶子节点存储的是既有索引也有数据。

非聚簇索引: MyISAM 的默认索引，B+Tree 的叶子节点存储的是数据存放的地址，而不是具体的数据，因此，索引存储顺序和数据存储关系毫无关联，另外 Inndob 里的辅助索引也是非聚簇索引。

前缀索引

• 如果索引列长度过长，这种列索引时将会产生很大的索引文件，不便于操作。
• 可以使用前缀索引方式进行索引前缀索引应该控制在一个合适的点。

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic;

• 这个值大于 0.31 就可以创建前缀索引。

ALTER TABLE `user` ADD INDEX `uname`(title(10));

• 增加前缀索引 SQL，将人名的索引建立在 10，这样可以减少索引文件大小，加快索引查询速度。

索引操作

创建索引

CREATE TABLE

• 创建表时添加索引。

CREATE TABLE 表名（
    字段名1 数据类型 [完整性约束条件...],
    字段名2 数据类型 [完整性约束条件...],
    [PRIMARY | UNIQUE | FULLTEXT ]  KEY
    [索引名] （字段名[（长度）] [ASC |DESC])
);

CREATE TABLE `test1` (
    `id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 注意，下面创建了主键索引，这里就不用创建了。
    `username` varchar(64) NOT NULL COMMENT '用户名',
    `nickname` varchar(50) NOT NULL COMMENT '昵称/姓名',
    `intro` text,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique1` (`username`),
    KEY `index1` (`nickname`),
    FULLTEXT KEY `intro` (`intro`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='后台用户表';

CREATE INDEX

• 在已存在的表上创建索引。

CREATE [UNIQUE | FULLTEXT ] INDEX 索引名 ON 表名（字段名[（长度）] [ASC |DESC]) ;

CREATE INDEX `index_name` ON `table_name` (`column_list`);
CREATE UNIQUE INDEX `index_name` ON `table_name` (`column_list`);

ALTER TABLE

• 在已存在的表上创建索引。

ALTER TABLE 表名 ADD 索引类型（UNIQUE,PRIMARY KEY,FULLTEXT KEY,INDEX）索引名（字段名[（长度）] [ASC |DESC]) ;

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`);
ALTER TABLE `table_name` ADD UNIQUE (`column_list`);
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`);
ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`);

注意:

• 索引名可省略，即当前的索引名就是该字段名。
• <表名> 是要建索引的基本表的名字，索引可以建立在该表的一列或多列上，各 <列名> 之间用逗号分隔，每个 <列名> 后面还可以用 <次序> 指定索引值的排列次序，可选 ASC（升序）或 DESC（降序），默认值为 ASC
• UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录。
• CLUSTER 表示要建立的索引是聚簇索引。

删除索引

删除索引

DROP INDEX 索引名 ON 表名;

DROP INDEX `index_name` ON `table_name`;

删除主键索引

• 主键索引只能用这种方式删除。

ALTER TABLE 表名 DROP PRIMARY KEY;

ALTER TABLE `table_name` ON PRIMARY KEY;

查看索引

SHOW INDEX FROM 表名;

SHOW INDEX FROM SCSno;

修改索引

修改索引名

ALTER INDEX <旧索引名> RENAME TO<新索引名>;

ALTER INDEX SCno RENAME TO SCSno;

索引设计原则

最左前缀匹配原则

• 在 MySQL 建立联合索引时会遵守最左前缀匹配原则，即查询从索引的最左列开始并且不能跳过索引中的列，如果遇到索引失效的情况，则右边的索引列全部转为全表查询。
• 这是因为索引的底层数据结构 B+树的数据结构决定的，B+树是按照从左到右的顺序来建立叶子节点的，B+树会优先比较第一个字段来确定下一步的所搜方向，如果第一个字段相同再依次比较第二和第三个字段，最后得到检索的数据。

避免索引失效的情况

• 如果是联合索引，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
• 不要在索引列上做任何操作（计算，函数，自动或手动类型转换），会导致索引失效而转向全表扫描。
• 联合索引范围条件右边的索引列会失效，范围查询的列在定义索引的时候，应该放在最后面。
• MySQL 在使用不等于（!= 或者<>）的时候无法使用索引会导致全表扫描。
• IS NOT NULL 也无法使用索引，但是 IS NULL 是可以使用索引的。
• LIKE 以通配符开头 '%abc...' 的索引失效会变成全表扫描的操作。
• 字符串不加单引号索引失效（类型转换导致索引失效）

为常作为查询条件的字段建立索引

• 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度，因此，为这样的字段建立索引，可以提高整个表的查询速度。

为经常需要排序，分组操作的字段建立索引

• 经常需要 ORDER BY, GROUP BY, DISTINCT 等操作的字段，排序操作会浪费很多时间，如果为其建立索引，可以有效地避免排序操作。
• 如果 ORDER 前包括 WHERE 条件，且 WHERE 字段为加索引，则 ORDER 不会使用索引。
• ORDER 多个字段时，需要有同顺序的联合索引，且排序顺序也需要一致。

限制索引的数目

• 索引的数目不是越多越好，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
• 修改表时，对索引的重构和更新很麻烦，越多的索引，会使更新表变得很浪费时间。

尽量选择区分度高的列作为索引

• 尽量选择区分度高的列作为索引，区分度的公式是 count (distinct col)/count (*)，表示字段不重复的比例，比例越大我们扫描的记录数越少。
• 唯一键的区分度是 1，而一些状态，性别字段可能在大数据面前区分度就是 0
• 使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是 0.1 以上，即平均 1 条扫描 10 条记录。

尽量使用数据量少的索引

• 如果索引的值很长，那么查询的速度会受到影响。
• 例如，对一个 CHAR (100）类型的字段进行全文检索需要的时间要比对 CHAR (10）类型的字段需要的时间要多。

尽量使用前缀来索引

• 如果索引字段的值很长，最好使用值的前缀来索引。
• 例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间，如果只检索字段的前面的若干个字符，这样可以提高检索速度。

为联合查询的关联字段建立索引

• 联合查询，子查询等多表操作时关连字段要加索引。
• 假设数据库中有 3 个表 A, B, C，每个表都有一列分别为 a, b, c，每列中有 6 行，为数字 1,2,3,4,5,6

SELECT A.a,B.b,C.c FROM A JOIN B JOIN C ON A.a=B.b AND B.b=C.c;

• 在没有添加索引时会执行 6*6*6=216 次查询，如果数据量很大，例如每个表都有 2000 条记录，结果会是 80 亿次查询，这个结果是很糟糕的。
• 所以建议在多表 join 的时候尽量少 join 几张表，因为一不小心就是一个笛卡尔乘积的恐怖扫描。
• 另外建议尽量使用 left join，以少关联多，因为使用 join 的话，第一张表是必须的全扫描的，以少关联多就可以减少这个扫描次数。