索引

相当于目录，用于提高查询速度。

• 绝大多数情况下，数据库表都应该创建索引，避免去遍历整张表，也就是通常说的，避免全表扫描。
• 每次新增数据库属性时，评估一下是否需要新建索引

一、索引介绍

INDEX和KEY

介绍索引之前，先说下index和key的区别：

• index：索引，负责维护表的查找和操作速度。有普通索引，主键索引，唯一索引，联合索引，全文索引
• key：约束，负责维护表的完整性。主键约束，唯一约束，外键约束，非空约束

在理论上是不能将MySQL的key和index划等号的，他们不是一回事，但在实际使用中，他们基本没有区别。

普通索引

对表中的一个列创建的索引，用于加速该列的数据检索。

唯一索引

保证该列中的值都是唯一的，不会有多条记录取值重复。

• 对多个属性联合建立唯一索引时，多个属性组合到一起的值不重复。

主键索引

数据库表中主键列自动创建的索引。主键唯一标识表中的每一行记录，自动创建唯一索引。

联合索引

两个列或多个列组合起来的索引。例如 idx_name_age(name, age)

CREATE TABLE `task_info` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '默认自增ID',
  `task_id` varchar(64) NOT NULL DEFAULT '' COMMENT '任务ID',
  `session_id` varchar(64) NOT NULL DEFAULT '' COMMENT '请求唯一ID',
  `app_id` varchar(32) NOT NULL DEFAULT '' COMMENT '业务AppId',
  `api` varchar(64) NOT NULL DEFAULT '' COMMENT '请求接口',
  `priority` tinyint(3) NOT NULL DEFAULT '0' COMMENT '优先级',
  `status` tinyint(3) NOT NULL DEFAULT '0' COMMENT '流程状态',
  `result` varchar(2048) NOT NULL DEFAULT '' COMMENT '执行结果',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),                              // 主键索引
  UNIQUE KEY `uniq_task_id` (`task_id`),             // 唯一索引
  KEY `idx_sessionid` (`session_id`),                // 索引
  KEY `idx_appid_api_status` (`app_id`,`api`,`status`)     // 联合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务信息表'

二、建立索引原则

1. 联合索引符合最左前缀匹配：从左到右的索引字段，只有左边的索引用到了，才会往右继续使用索引去匹配，遇到范围查询会停止匹配。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找

2. 区分度高的放前边：例如学号，像性别区分度就很低，没必要建索引

3. 尽量拓展索引而不是新建

4. 经常用做查询条件的字段建索引

5. 不要追求全覆盖：索引能定位的行数达到1000左右就可以了，不要非得定位到一两行（剩下的查询由数据库加载到内存中去搞）

6. 不要让索引列的默认值为NULL：null多时给数据库维护带来一定成本

7. update/delete操作的where子句必须命中索引——写操作是间隙锁，否则相当于锁表

8. 索引数量：一个表的索引建议不超过5个。（索引会占用磁盘空间；索引的重构会很麻烦）索引长度：一个索引中建议字段不超过3个。

三、索引失效

1. in走索引：与集合中数量有关系，in后边数量不超过整表的30%会走索引，否则全表扫描

2. 范围查询走索引（>、<）

3. 模糊查询只有左前缀使用索引：LIKE 'abc%'使用索引、LIKE '%abc%'不走索引

4. 反向查询不走索引 NOT、!=、!<、!>、NOT EXISTS、NOT IN、NOT LIKE 等

5. 索引列做运算(使用函数或者算数表达式)则失效

6. 查询时必须使用正确的数据类型：隐式类型转换会导致索引失效，如int-->String

7. or 使用索引和不使用索引的情况

• 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的
• 两边都为“>”和“<”范围查询时，索引失效

8. order by 不走索引，但是order by 主键走索引

四、索引底层原理

索引的底层结构：B+树

4.1 innodb存储引擎的底层结构：B+树

4.1 B+树

• 平衡m叉树

• 非叶子结点存索引，叶子结点才存数据（树比较“矮胖”，可以减少磁盘IO）

  • 增大页内数据量，提升预读性

  • 减少树的高度，树最大高度一般控制到3到4层

  • 叶子结点直接绑定数据

    注：叶子结点可以存数据，也可以存索引

• 叶子结点有序 + 结点间双向链表（快速定位、范围查询、排序）

4.2 Innodb存储引擎的底层结构是B+树，通过索引组织表使用B+树

• 聚簇索引，叶子结点保存数据

  表中很多索引，怎么去选聚簇索引呢

  • 如果定义了主键，则主键是聚簇索引

  • 如果没定义主键，则第一个非空unique列是聚簇索引

  • 否则，innodb会创建一个隐藏的row-id作为聚簇索引

• 普通索引，叶子结点存聚簇索引的值

回表：先通过普通索引查到主键值，再根据主键去聚簇索引里查实际数据。

参考资料

index和key

mysql建立索引的几大原则（仅学习）

那些字段适不适合建索引？