MySQL 索引

简介：索引是帮助MySQL高效获取数据的数据结构，实现了高级查询算法的数据结构，一般以文件形式存储在磁盘上。

一、索引方法（B+树）

　　A. B+树索引并不能直接找到行，只是找到了行所在页，通过把整页读入内存，再在内存中查找；

　　B. 索引的B+树高度一般是2-4层，查找记录时做多只需要2-4次IO；

　　C. 磁盘IO时间 = 寻道 + 磁盘旋转 + 数据传输时间；

　　D. 内存顺序访问速度是磁盘访问速度的6——7倍；

　　E. 内存随机访问速度是磁盘访问速度的10万倍以上。

二、索引分类

　　1. 按数据结构分

　　　　A. B-Tree索引：是一种多叉平衡树，变种最常见的是B+Tree；

　　　　　　优点：支持范围查询、支持排序和分组、支持前缀匹配；

　　　　　　缺点：占用的空间相对大；

　　　　B. Hash索引：是基于Hash表实现的，key-value键值对，适合应用于查询单个键的情况；

　　　　　　优点：查找速度最快，时间复杂度O(1)；

　　　　　　缺点：不支持范围查询、需要处理Hash冲突、必须全值精确匹配；

　　　　C. Fulltext索引：只有MyISAM引擎支持，主要是为了解决文本模糊查询效率较低问题；

　　　　D. R-Tree索引：相比B-Tree而言，优势体现在范围查询，仅支持 geometry 数据类型；

　　2. 按功能类型分

　　　　A. 普通索引：仅加速查询；

　　　　B. 唯一索引：主要用于业务上的唯一约束，一个表可以有多个唯一索引；

　　　　C. 主键索引：通常以表的ID作为索引，一个表只能有一个主键索引；

　　　　D. 全文索引：对文本的内容进行分词搜索；

　　　　E. 空间索引：对空间数据字段建立的索引；

　　　　F. 覆盖索引：指索引包含了所有需要查询的字段；

　　3. 按物理存储分

　　　　A. 聚簇索引(主键索引)：指数据和索引在一起存储的索引方式，节点只包含ID索引列，而叶子节点包含索引列和数据；

　　　　B. 非聚簇索引(二级索引)：指叶子节点保存的是索引列和主键ID值；

　　4. 按列数分

　　　　A. 单列索引：一个索引只包含单个列，一个表中可以有多个单列索引；

　　　　B. 组合索引(最左前缀索引)：一个索引包含多个列，满足最左前缀匹配原则；

　　5. 聚簇索引

　　　　A. InnoDB有且仅有一个聚簇索引，叶子节点存储行记录；

　　　　B. 如果表定义了Primary Key，则PK就是聚簇索引，如果表没有定义PK，则第一个Not NULL Unique列是聚簇索引，否则，InnoDB会创建一个隐藏的row-id作为聚簇索引；

　　　　C. 所以PK查询非常快，直接定位行记录；

　　6. 非聚簇索引

　　　　A. 二级索引就是普通索引，InnoDB索引的叶子节点存储主键值，而MyISAM索引的叶子节点存储记录指针，索引和数据是分开存储的，需要根据指针从表文件里查询数据；

　　　　B. 二级索引的查询过程需要扫描两遍索引树，先通过普通索引定位主键值，再通过聚簇索引定位到行记录，这也就是所谓的回表查询，它的性能较扫一遍索引树更低；

　　7. 覆盖索引

　　　　A. 指只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表查询，这个较回表查询速度更快；

　　　　B. 要确定一个查询是否是覆盖索引，我们只需要explain sql语句看Extra的结果是否是“Using index”即可；

　　　　C. 实现方式是将被查询的字段，建立到联合索引里去。

三、B-树和B+树

　　1. 概念　　　

　　　　B-树：是一种平衡的多路查找树，并不一定是二叉的，B树就是B-树，"-"是个连字符号，不是减号；

　　　　B+树：B+树是B-树的变体，也是一种多路查找树，适合数据库和操作系统的文件索引；

　　　　B*树：B*树是B+树的变体，为非叶子节点也增加链表指针，将节点的最低利用率从1/2提高到2/3；

　　　　键值(key)：表中记录的主键；

　　　　指针(P)：存储子节点的地址信息；

　　　　数据(data)：即表记录中除主键以外的数据。

　　2. B-树性质

　　　　A. 任意非叶子节点最多只有M个子节点；且M>2；

　　　　B. 非叶子节点的关键字个数=指向其子节点的指针个数-1；

　　　　C. 所有叶子节点位于同一层，指针为空；

　　　　D. 节点中数据key从左到右递增排列，是有序的；

　　　　E. 所有键值都会在B-树节点中，且只出现一次，非叶子节点存储指向关键字范围的子节点，非叶子节点可以命中。

。

　　3. B+树性质

　　　　A. B+树性质基本与B-树相同，除了以下几个；

　　　　B. 非叶子节点的子树指针与关键字个数相同；

　　　　C. 叶子节点间存在链表指针，便于范围查询；

　　　　D. 所有键值都在叶子节点中出现，总是到叶子节点才命中；

　　　　E. 非叶子节点不存储data，只存储key，非叶子节点作为叶子节点的索引，能更快定位包含目标值的叶子节点。

　　4. B-树与B+树的比较

　　　　A. B-树查找数据可以在非叶子节点命中返回，而B+树查找数据必须到叶子节点才行；

　　　　B. B+树方便扫库，直接从叶子节点扫描一遍即可，而B-树必须用中序遍历的方式按序扫库；

　　　　C. B+树支持区间查询非常方便，而B-树不支持，这是数据库选用B+树的最主要原因。

　　5. B+树索引原理

　　　　A. B+树的分裂：当一个节点满时，分配一个新的节点，并将原节点中1/2的数据，复制到新节点，最后在父节点中增加新节点的指针，B+树的分裂只影响原节点和父节点，而不会影响兄弟节点，所以它不需要指向兄弟的指针。

四、索引原则

　　1. 建立索引原则

　　　　A. 最左前缀匹配原则；

　　　　B. 主键自动建立唯一索引；

　　　　C. 频繁作为查询条件的字段应该建立索引；

　　　　D. 索引列不能参与计算；

　　　　E. 查询中统计、分组和排序的字段应该建立索引；

　　　　F. 对于经常存取的列避免建立索引。

　　2. 使用索引原则

　　　　A. where子句中的列；

　　　　B. 使用唯一索引、短索引；

　　　　C. 使用前缀索引；

　　　　E. 不要过度使用索引。

五、索引失效

　　1. where条件中like %后缀使用索引也失效

　　　　A. 表结构

CREATE TABLE `user_group` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户组ID',
  `name` varchar(255) NOT NULL COMMENT '用户分组',
  `remark` varchar(255) DEFAULT NULL COMMENT '分组说明',
  `created_by` bigint(20) DEFAULT '0' COMMENT '创建人ID',
  `created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_by` bigint(20) DEFAULT '0' COMMENT '修改人ID',
  `updated_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`) USING BTREE COMMENT '用户组唯一'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户组';

　　　　B. SQL1：select * from user_group where name like "开发%"；

　　　　C. SQL2：select id, name from user_group where name like "开发%"；

　　　　D. 结论：select中带索引的字段索引有效，查询没有索引的字段失效，而like %开头一定是失效的。

　　2. where条件中不等号(!=)失效

　　　　A. 表结构：同上

　　　　B. SQL1：select * from user_group where id != 2；

　　　　C. SQL2：select * from user_group where id != 2 and name = "开发二组14"；

　　　　D. 结论：查询索引字段使用!=会导致索引失效，但是若其中还有索引字段使用=，那么索引是有效的。

　　3. 最左前缀匹配原则失效

　　　　A. 表结构

CREATE TABLE `app_manage` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '应用ID',
  `category` tinyint(3) unsigned DEFAULT '1' COMMENT '应用类型(1-web应用)',
  `name` varchar(255) NOT NULL COMMENT '应用名称',
  `logo` varchar(255) NOT NULL COMMENT '应用LOGO',
  `remark` varchar(255) DEFAULT NULL COMMENT '简介',
  `app_url` varchar(255) NOT NULL COMMENT '应用地址',
  `is_disabled` tinyint(3) unsigned DEFAULT '0' COMMENT '是否禁用(0-启用、1-禁用)',
  `created_by` bigint(20) DEFAULT '0' COMMENT '创建人ID',
  `created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_by` bigint(20) DEFAULT '0' COMMENT '修改人ID',
  `updated_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `category_name` (`category`,`name`) USING BTREE COMMENT '同一类型应用名称唯一'
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='应用管理';