MySQL索引学习记录（创建、删除、优缺点、底层结构、生失效原则等等）

1. 认识索引

1.什么是索引
MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。将数据进行排序整理的过程就称为索引，我们根据索引去查，提高效率

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

2.MySQL索引分类

• 主键（约束）索引
  主键约束+提高查询效率
• 唯一（约束）索引
  唯一约束+提高查询效率
• 普通索引
  仅提高查询效率
• 组合（联合）索引
  多个字段组成索引
• 全文索引
  solr、es
• hash索引
  根据key-value 效率非常高

说明：

1.我们创建表时就会指定主键和唯一约束，那么就相当于给表的字段添加了主键和唯一索引。

2. 创建索引

1.在已有表的字段上直接创建【了解】

-- 创建普通索引
create index 索引名 on 表名(字段);
 
-- 创建唯一索引
create unique index 索引名 on 表名(字段);
 
-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2,..);
 
-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2,..);

说明：

如果在同一张表中创建多个索引，要保证索引名是不能重复的

上述创建索引的方式比较麻烦，还需要指定索引名

采用上述方式不能添加主键索引

【准备的创建表的SQL语句】

create database day04;
 
use day04;
-- 创建学生表
CREATE TABLE student(
 id INT,
 name VARCHAR(32),
 telephone VARCHAR(11)
);

【给上述表的字段设置索引】

1.给name字段设置普通索引
CREATE INDEX name_idx ON student(name);
2.给telephone字段设置唯一索引  
CREATE UNIQUE INDEX telephone_uni_idx ON student(telephone);

设置好之后可以通过图形化工具查看设置的索引：

2.在已有表的字段上修改表时指定【了解】

-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(字段);  --默认索引名：primary

-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(字段); -- 默认索引名：字段名

-- 添加普通索引，索引值可以出现多次。
alter table 表名 add index(字段); -- 默认索引名：字段名

【准备的创建表的SQL语句】

-- 创建学生表
CREATE TABLE student2(
 id INT,
 name VARCHAR(32),
 telephone VARCHAR(11)
);

【给上述表的字段设置索引】

1.指定id为主键索引

ALTER TABLE student2 ADD PRIMARY KEY(id);
2.指定name为普通索引

ALTER TABLE student2 ADD INDEX(name);
 3.指定telephone为唯一索引

ALTER TABLE student2 ADD UNIQUE(telephone);

设置好之后可以通过图形化工具查看设置的索引：

3.创建表时指定【掌握】

-- 创建学生表
CREATE TABLE student3(
 id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
 name VARCHAR(32),
 telephone VARCHAR(11) UNIQUE, -- 唯一索引
 sex VARCHAR(5),
 birthday DATE,
 INDEX name_idx(name) -- 普通索引 name_idx为索引名称
);

3. 查看索引

show index from 表名;

【查看student3表的索引信息】

show index from student3;

【结果】

4. 删除索引

语法

【语法1】直接删除

-- 直接删除
drop index 索引名 on 表名;

【语法2】修改表时删除

-- 修改表时删除
alter table 表名 drop index 索引名;

练习

【1】删除student表的name普通索引
DROP INDEX name_idx ON student;

【2】删除student表的telephone唯一索引
ALTER TABLE student DROP INDEX telephone_uni_idx;

5. 前缀索引

可以将字符串的一部分前缀提取出来简历索引，节约索引空间提高效率
语法：create index idx_xxx on 表名(column(n))
 

6. 索引的优缺点

占空间、需维护 降IO、降CPU、查询快

7. 索引创建原则

• 字段内容可识别度不能低于70%，字段内数据唯一值的个数不能低于70%例如：一个表数据只有50行，那么性别和年龄哪个字段适合创建索引，明显是年龄，因为年龄的唯一值个数比较多，性别只有两个选项 。性别的识别度是50%。
• 经常使用where条件搜索的字段，例如user表的id name等字段。
• 经常使用表连接的字段（内连接、外连接），可以加快连接的速度。
• 经常排序的字段 order by group by，因为索引已经是排过序的，这样一来可以利用索引的排序，加快排序查询速度。
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
• 控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

8. 索引的数据结构

8.1. 概述

我们知道索引是帮助MySQL高效获取排好序的数据结构。

为什么使用索引后查询效率提高很多呢？

肯定和mysql底层的数据结构有关的，接下来我们就分析下mysql中的索引底层的数据结构。

每一行数据都对应一个磁盘地址，假设我要想查找数据6，那么如果没有索引，那么内存读取磁盘会进行6次的磁盘IO。

在没有索引的情况下我们执行一条sql语句，那么是表进行全局遍历，磁盘寻址，上述最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。每个存储数据的磁盘片有可能是分开的。

为了加快的查找效率，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

8.2. 数据结构 —— 树

1. 二叉查找树：左边的子节点比父节点小，右边的子节点比父节点大

说明：类似上述数据比较特殊的情况下，那么如果存储在二叉查找树中就会出现类似链表的情况，那么会大大降低查找效率。

1. BTree

BTree：多路平衡搜索树

说明：

1.上述数据结构称为BTree,翻译过来是多路平衡搜索树，B 通常认为是Balance的简称。并不是二叉的。

2.一个节点可以有多个元素，按照上述BTree数据结构，第一个节点即根节点含有3个元素。

3.BTree增加了树的宽度，一个节点可以存储多个元素,每个元素由索引、指针域、数据域组成。

4.一个节点大概有16KB大小，不是节点越大越好。假设一个节点占1G，那么高并发时会占大量的服务器内存，反而会降低效率.

5.内存读取硬盘的内容一次读取单位:页(page 每页大小是4KB) 举例：假设读取一个字符a,那么会读取字符a的前后内容，大小是4KB,然后在读取的内容中找到字符a.读取一个字符是4KB,10个字符也会读取4KB.

6.一个元素=索引(bigint 8字节)+指针域(6字节)+数据域(data 假设是1KB).而一个节点大概有16KB大小，这样计算下来一个节点能存储15个元素。那么log以15为底的1000W的对数大概是6左右，这样就一下将原来红黑树的高度23降为6，宽度增加，高度降低就会大大提高查找效率。

7.BTree有效的控制了树的高度，但是还会有如下问题：

1.树的高度还是有点高

2.范围查询磁盘IO次数较多。例如上述数据查找比15索引大的数据，由于15索引对应的元素具有指针域指定，所以到比15大的数据域中进行查找，那么IO次数还是比较多

1. B+Tree：优化BTree（非叶子节点：索引+指针、叶子节点：索引+数据【地址】）

说明：

1.非叶子节点： 不是树结构最下面的节点.存储的是：索引+指针域。

2.叶子节点 ： 就是最后一层子节点 , 数据存储在叶子节点。存储的是：所有索引+数据或者数据的地址值

注：

1.不同的存储引擎叶子节点存储的内容不一样，有可能是：索引+数据。也有可能是索引+数据的地址 值。

2.B+Tree是可以允许有冗余索引出现的，每个节点都有索引，例如上述索引15，明显是冗余的。

3.非叶子节点都是由索引+指针域组成的，一个索引占8字节，一个指针域占6字节，一个元素大概需要14字节。而一个节点总共容量是16KB.那么可以计算出一个节点可以存储的元素个数：16*1024字节 / (8+6)=1170个元素

4.对于根节点中可以存储1170个元素，那么根据每个元素的地址值又会找到下面的子节点，每个子节点也会存储1170个元素，那么第二层即第二次IO的时候就会找到数据大概是：1170*1170=135W。也就是说B+Tree数据结构中只需要经历两次磁盘IO就可以找到135W条数据。

5.对于第二层每个元素有指针域，那么会找到第三层，第三层由索引+数据域组成，假设索引+数据域总大小是1KB.而每个节点一共能存储16KB.所以一个第三层一个节点大概可以存储16个元素即16条记录。那么结合第二层每个元素通过指针域找到第三层的节点，第二层一共是135W个元素，那么第三层总元素大小就是：135W*16结果就是2000W+的元素个数。

6.结合上述讲解B+Tree我们发现有如下优点：

1.千万条数据，B+Tree可以控制在小于等于3的高度。

2.所有的数据都存储在叶子节点上，并且底层已经实现了按照索引进行排序，还可以支持范围查询，叶子节点是一个双向链表，支持从小到大或者从大到小查找。

补充：由于叶子节点按照索引已经排好序，每次查找范围的数据不用再像BTree还得回到根节点查找，直接在叶子节点中查找即可.

B+Tree好处：

1.降低树的高度

2.叶子节点按照索引排好序，支持范围查找，速度会很快。

3.还有一点是mysql将根节点都加载到内存中，每张表有一个根节点，大小是16KB.那么这样的好处，按照上述如果是千万条数据，那么只有2次磁盘IO.这就是为什么我们加完索引之后瞬间查到数据的原因了。

9. 索引失效原则

联合索引：

最左前缀法则，最左边的列必须存在，否则整个联合索引失效。当中间的列不存在，那么该列右侧的索引失效。

联合索引在进行范围查询的时候，范围查询>和<右侧的列会失效，因此要尽量使用>=和<=这样的操作符。

索引失效：

不要在索引列上进行函数运算，否则索引失效

字符串不加引号，会造成隐式类型转换，索引会失效

like模糊匹配，如果在前面加了%，索引会失效

or连接的条件，如果一侧有索引，另一侧没有索引，索引也会失效

如果MySQL评估全表扫描比走索引更快，索引失效。数据分布的影响