MySQL索引

索引是什么

索引（在MySQL中也叫键（key））是存储引擎用于快速查找记录的一种数据结构。索引对于性能拥有至关重要的地位。尤其是当表中的数据量越来越大，索引对于性能的影响愈发重要。反之不恰当的索引对于性能也会急剧下降。

索引是一把双刃剑

索引的优与劣

索引的优点

索引提高数据检索的效率

降低数据库的I/O 成本（将随机IO变为顺序I/O ）
减少扫描行数

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

时间方面

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

空间方面

索引需要占物理空间，如果要建立聚簇索引，那么需要的空间就会更大

当对表中的数据进行增加、删除、修改的时索引也要动态的维护，这样就降低了数据的维护速度。

应用方面

锁竞争

索引的类型

索引有很多种类，可以为不同场景提供更好的性能。索引在存储引擎层实现的，故并没有统一的索引标准

不同的存储引擎的索引工作方式也不尽相同

索引分类

按照不同的维度可以区分索引类型

按照物理村粗可以划分为：聚簇索引与二级索引（辅助索引）

按照数据结构可以划分为： B+ Tree、Hash、Full-Text、Geographic Information System等

按照字段个数可以划分为：单列索引和联合索引

按照字段特效可以划分为：主键索引、唯一索引、普通索引、前缀索引

物理顺序与键值的索引逻辑顺序关系

聚集索引：数据行的物理顺序与列值（一般为主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

非聚集索引(辅助索引)：逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

聚簇索引与非聚簇索引异同

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。

而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

聚簇索引与非聚簇索引的区别：

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）
对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表
。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描。
通常情况下，主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可

注意：

MyISAM无论主键索引还是二级索引都是非聚簇索引

InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。

存储结构

这里所描述的是索引存储时保存的形式

B Tree索引（B-Tree或B+Tree索引）BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是
MySQL里默认和最常用的索引类型。
Hash索引，HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高
full index 全文索引，其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引
R-Tree索引。RTREE在MySQL很少使用，仅支持geometry数据类型，相对于BTREE，RTREE的优势在于范围查找。

B + Tree 索引

通常意味着所有的值都是顺序存储的，并且每个叶子页到根的距离相同

适用范围

全键值、键值范围、前缀查找

应用场景

全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另一列、值访问索引的查询

限制

必须为最左开始（最左前缀原则），否则不使用索引
无法跳跃查询索引中的列
范围查询影响，右边的无法使用索引

Hash 索引

基于Hash表实现，只有精确匹配索引所有列的查询才有效

对于每一行数据存储引擎都会对所有的索引计算一个hash code。hash code 较小的值，并且不同键值的行计算出来的hash code页不一样。hash索引将所有的hash code存储在索引中，同时在hash
table中保存指向每个数据的指针

hash索引查询步骤

先计算数据的hashcode，并使用该值查找对应的记录指针
查找在hash table中的指向
值比较确认

特点

因为索引自身只需要存储对应的hash code，所有索引结构非常紧凑，这也让hash索引查找速度非常快

限制

hash index 只包含hash值和行指针，而不存储字段值，所以不能使用hash index来避免读取行。同时访问内存中的数据速度非常快，所以对于大部分情况下这一点对于性能影响并不明显
hash index数据并不是按照索引顺序存储的，所以无法用于排序
hash index 也不支持部分索引列匹配查找
hash index 只支持等值比较查询，包括=、IN、<=、=>。也不支持任何范围查询
hash index访问数据非常快，除非有很多hash冲突。同时hash冲突很多，索引维护代价较高昂

R Tree索引

空间数据索引

这类索引无需前缀查询，空间索引将会从所有的维度来进行索引数据

Full index

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而非比较索引中的值

全文搜索和其他几种类型的索引的匹配方式完全不同，如停用词、词干、复数、布尔搜索等

全文索引更类似于搜索引擎所做的事情，而不是简单的where条件匹配，而是MATCH AGAINST操作。支持Char、VARCHAR、TEXT类型、自然语言搜索、bool搜索

GIS

Geographic Information System

应用层次

主键索引：加速查询 + 列值唯一（不可以有null）+ 单表中只有一个主键索引（主键索引可以是多个字段）
普通索引：仅加速查询
唯一索引: 加速查询 + 列值唯一（建立在UNIQUE上的索引，字段可以为null）
复合索引: 多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索
覆盖索引: query 的索引字段在二级索引中就能找到（不需回表）
前缀索引：仅加速查询（对字符类型的前几个字段或二进制类型字段的前几个bytes建立的索引而不是在整个字段上建立索引）

索引合并，使用多个单列索引组合搜索

索引创建

正确的创建索引是实现高性能查询的基础

其核心的选择因素是 特征 , 借助索引实现尽可能少的行扫描（最多业务场景）

索引设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑

什么时候创建索引

在哪个(些)字段上创建索引

创建什么类型的索引

索引设计原则

必须要有主键,如果没有可以做为主键条件的列,创建无关列
经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
列值长度较长的索引列,建议使用前缀索引.
降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
索引维护要避开业务繁忙期

CREATE TABLE创建索引

CREATE TABLE IF NOT EXISTS `ch_people_msg`
(
    `p_id`       SERIAL              NOT NULL AUTO_INCREMENT COMMENT '用户id',
    `p_uic`      CHAR(18)            NOT NULL COMMENT '用户身份证',
    `p_nickname` VARCHAR(50)         NOT NULL COMMENT '用户昵称',
    `p_gender`   ENUM ('m','f', 'n') NOT NULL DEFAULT 'n' COMMENT '用户性别',
    `p_age`      TINYINT UNSIGNED    NOT NULL DEFAULT 0 COMMENT '用户年龄',
    `p_pnum`     CHAR(11)            NOT NULL COMMENT '用户电话',
    `p_address`  VARCHAR(100)        NOT NULL COMMENT '用户地址',
    `p_email`    VARCHAR(50)         NOT NULL COMMENT '用户邮箱',
    `p_add_time` TIMESTAMP           NOT NULL DEFAULT NOW() COMMENT '统计用户时间',
    PRIMARY KEY (`p_id`),
    UNIQUE KEY `p_uic` (`p_uic`)
) ENGINE = InnoDB
  CHARSET = utf8mb4
  COLLATE utf8mb4_general_ci COMMENT = '中国成员信息表';

增加索引

1
2
3

ALTER TABLE table_name
    ADD INDEX index_name (column_list);
CREATE INDEX (index_type) index_name ON TABEL_NAME(COLUMN_NAME)

创建索引时注意点

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count() 函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

索引命名规范

按照列组合命名：Idx_id_name_sex
按照功能命名：ldx_check_user_info
按照列和功能组合命名：ldx_id_name_sex_checklist

但是需要注意，索引名称长度限制64，单个表最大索引数16，超过的话就创建失败。

索引失效

函数导致的索引失效

1
2
3

SELECT *
FROM `user`
WHERE DATE(create_time) = '2012-11-03';

运算符导致的索引失效

# 如果对列进行了（+，-，*，/，!）运算, 那么都将不会走索引。
select p_id
from xxx
where p_id + 10 = 12

OR引起的索引失效

OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效

如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。

SELECT *
FROM `xxx`
WHERE `name` = 'xxx'
   OR age = 20;

模糊搜索导致的索引失效

当模糊查询%在匹配字段前缀不走索引，放在后面才会走索引。

使用!= 或者 <> 导致索引失效

类型不一致导致的索引失效

NOT IN、NOT EXISTS导致索引失效

避免索引失效总结

尽量采用确认的、顺序的、逐步的
模糊查询%不在前
索引列不运算

索引下推

自5.6引入了索引下推优化。默认开启

可使用SET optimizer_switch = ‘index_condition_pushdown=off;将其关闭。

有了索引下推优化，可以在减少回表次数
在InnoDB中只针对二级索引有效

官方文档中给的例子和解释如下：

在 people_table中有一个二级索引(zipcode，lastname，firstname)，查询是SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE
‘%etrunia%’ AND address LIKE ‘%Main Street%’;

如果没有使用索引下推技术，则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE ‘%etrunia%’ and address LIKE
‘%Main Street%’来判断数据是否符合条件
如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main
Street%’来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。

默认使用B+Tree的优势

为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

B-tree：

B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)
树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询
的情况，所以通常B+树用于数据库索引。

Hash：

虽然可以快速定位，但是没有顺序，IO复杂度高；
基于Hash表实现，只有Memory存储引擎显式支持哈希索引；
适合等值查询，如=、in()、<=>，不支持范围查询；
因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序；
Hash索引在查询等值时非常快；
因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找；
如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

Referer

《高性能MySQL》