MySQL索引总结

1. 什么是索引？索引用来干什么？

索引是数据库表中一列或者多列数据按照一定顺序排列的结构。
索引分为 普通索引，唯一索引，全文索引和空间索引。
此处空间索引为myisam 独有，这里只考虑innodb,不再介绍空间索引。
索引可以干什么？

1.1 可以通过索引快速查找数据
1.2 可以保证数据的唯一性，也是快速查找数据的基础，假如不能保证唯一，就会混淆
1.3 保证了数据的参照完整性。例如创建外键的时候会创建索引。参照完整性的保证了父子表之间的数据准确性。对于相互关联的表，如果只删除了父表就会子表的冗余数据。也就是说子表外键的值必须在父表存在。
1.4 可以快速分组group by 和 排序 order by。

2. 二叉树，平衡二叉树，BTree，B+Tree

• 二叉树
  二叉树，顾名思义，有两个子节点的树形数据结构。左边的子节点小于父节点，且小于右节点。
• 平衡二叉树
  平衡二叉树也是二叉树的一种。遵守二叉树的所有规则。与普通二叉树的区别在于：

1. 左边的子树和右边的子树的节点差最大为1。
2. 左右的子树都是平衡二叉树。
3. 普通的二叉树可以转换成平衡二叉树。

• BTree 多路平衡查找树（balance tree）
  BTree 也是一种树形的数据结构,相比平衡二叉树而言有多个子节点。根节点在最上层。叶子节点在最下层。

1. 除了根节点和叶子节点其他最少有两个子节点。
2. 如果根节点不是叶子节点，那么最少有两个子节点。
3. 所有的叶子节点都在同一层。
4. 父节点和子节点包含指针和数据，叶子节点只包括数据，指针为null,只要查询到数据可以直接返回。

• B+Tree
  B+Tree 树BTree数据结构的一种变种。在继承BTree的所有特征之外还有一定的区别，包括：

1. B+tree的数据信息都包含在叶子节点中。并且包含引用的指针。
2. B+tree的叶子节点按照关键字大小的顺序排列，可以快速的查找数据。
3. 叶子节点自建采用链表的结构，每个叶子节点上有两个指针，一个指向父节点，一个指向最小的叶子节点。

3. 聚集索引和普通索引

B+Tree 的索引分为聚集索引和普通索引。

• 聚集索引（聚簇索引）

1. 索引的顺序决定了数据排列的顺序
2. 聚集索引是一种数据结构，决定了物理存储顺序，数据都在叶子节点上，聚簇索引就是数据，数据也就是索引。
3. 创建表的时候一定会有一个聚簇索引，假如没有明确指定，那么就是非null的唯一索引。假如没有唯一索引，就会使用rowId作为唯一索引。

• 非聚集索引

非聚集索引和聚集索引类似，都是B+tree结构。创建普通索引的时候可以在创建表之后创建。非聚集索引的叶子节点包含的是普通的索引列和逐渐索引列。在查询到主键之后，在通过聚集索引拿到所有的数据。

• 创建索引的方式(简单方式)

create index table table_name add index index_name
创建索引可以增加选项，具体可以参考官网。

4. 聚集索引和唯一索引

• 聚集索引就是主键索引，查询的时候遵顼聚集索引的查询方式。
• 聚集索引是唯一索引，只不过唯一索引的值可以为null，可以在创建索引的时候增加 UNIQUE 选项代表创建唯一索引。
• 聚集索引一定是唯一索引，唯一索引不一定是聚簇索引。
• 聚集索引不可以为null,但是唯一索引可以为null,但是只允许有1个。
• 主键只能有一个，但是唯一索引可以有多个。

5. 回表和覆盖索引

当SQL进行查询数据的时候只通过索引就可以查询到所有数据了，这个时候就不需要查询到索引之后再去查询数据了，减少了IO操作。再次查询表的过程叫做回表。而这条语句就使用了覆盖索引。

• 如何确认是否使用了覆盖索引呢？

可以通过explain + SQL 语句查看执行计划。示例：

-- 创建表
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- 插入语句
insert into user(name,address) values ("zhangsan","beijing");
-- 命令行查看执行计划
explain select id from user where id = 1;

当查看执行计划看到 Extra 列值存在 Using Index 的时候就使用了覆盖索引。结果：

6. 前缀索引

前缀索引，为检索字符类型（char,varchar,text）和字节类型（binary,varbinary,blob）类型，必须指定前缀索引的长度

-- 添加列 
alter table user article text default null;
-- 添加一些数据
update user set article = "hello,world!" where id = 1;
-- 添加前缀索引
alter table user add key(article(5));
-- 查看前缀索引
show indexes from user;

查看前缀索引的时候就可以发现和列索引类似，不过在Sub_part 选项多了，前缀索引的长度。

7. 复合索引

当使用多列数据做索引的时候，被称作复合索引。复合索引最多有16列。

-- 给上述user 表增加一列 phone varchar (11) defalut null;
alter table user add phone varchar(11) default null;
-- 删除原 name 索引
drop index name on user;
-- 创建复合索引
create index column_name_address_phone on user(name,address,phone);

• 复合索引使用场景,复合最左前缀规则

1. 使用索引的场景
   
2. 索引失效的场景

select * from user where name = "1";
select * from user where phone = "1";
select * from user where address = "1" and phone = "1";

这个时候就该想了，使用name 和 phone 也是会使用索引的。遵循最左前缀规则。
3. 特殊场景

select * from user where name = "1" and phone = "1";

确实 这种也使用了索引，但是Extra 中的Using where ，也不是Using index,而是 Using index condition。这个是使用了MySQL 5.6之后的ICP数据检索优化。额，有点晚了下篇再写吧。

8. Hash索引

hash 索引是通过hash算法查找数据，不可以进行排序，模糊查询，范围查找等。也不用多次IO逐级查找，确定了位置直接可以查询到数据，非常快。
创建索引的时候可以选择进行hash索引，例如k-v结构的字典表可以这种方式查询。
InnoDB默认显示表现的只要B+tree索引，在navicat中修改时无法成功的。

9. 测试总结

主要测试索引使用的场景，并对此进行总结该如何使用索引。正常的where 条件是可以根据上述条件使用索引的，此处对一些其他的用法测试。
9.1 范围查询

场景 > , < , >=,<= !=, in ,not in ,is not null,is null
经过测试 > , < , >=,<= !=, in,is null 使用了type = range的索引。
not in 和 is not null未使用索引。
但是在测试的id is null的时候出现了 type = null的情况。

9.2 模糊查询

场景 “%xx%”,"%xx",“xx%”,“xx”
经过测试 “xx%”,“xx” 使用了索引，其他的没有，这里也遵顼最左前缀原则。
我查看了其他的博文，就 “xx” 选项有叙述索引无效，就测试而言，这个是错误的。

9.3 排序分组 order by ,group by

• order by
  使用场景比较复杂,测试的范围还是上述的索引，包括
  id 主键，name_address_phone 复合索引和 article 前缀索引。

-- 主键使用的时候使用聚簇索引
explain select * from user order by id;
-- 非聚簇索引使用的时候，查询有非聚簇索引列不使用索引，其实这里使用的是覆盖索引
explain select * from user order by name;
-- 使用limit 分页时候使用的索引，并遵守最左前缀原则
explain select * from user order by name limit 10;

• group by
  这个一般情况下无法使用索引，只能使用覆盖索引这种情况。

额，有没有发现，覆盖索引是面试的时候需要注意的点。