MySQL索引所有知识点

1.什么是索引
索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

2 索引的优势和劣势

优势：

可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。
劣势：
索引会占据磁盘空间
索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

3索引类型

主键索引
索引列中的值必须是唯一的，不允许有空值。

普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

唯一索引
索引列中的值必须是唯一的，但是允许为空值。

全文索引
只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

空间索引
MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

前缀索引
在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。
其他（按照索引列数量分类）

单列索引

组合索引

组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

索引的数据结构

Hash表
Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。
二叉查找树
二叉树，我想大家都会在心里有个图。

二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

这个特点就是为了保证每次查找都可以这折半而减少IO次数，但是二叉树就很考验第一个根节点的取值，因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”，这就很难受很不稳定。

显然这种情况不稳定的我们再选择设计上必然会避免这种情况的

平衡二叉树
平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。

使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。查询id=6，只需要两次IO。

就这个特点来看，可能各位会觉得这就很好，可以达到二叉树的理想的情况了。然而依然存在一些问题：

时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。磁盘每次寻道时间为10ms，在表数据量大时，查询性能就会很差。（1百万的数据量，log2n约等于20次磁盘IO，时间20*10=0.2s）

平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

B树：改造二叉树
MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。那如何降低树的高度呢？

假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4*2=16）。

因为在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。

这种数据结构我们称为B树，B树是一种多叉平衡查找树，如下图主要特点：

    1 B树的节点中存储着多个元素，每个内节点有多个分叉。
    2 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
    3 父节点当中的元素不会出现在子节点中。
    4  所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

六、索引的设置原则

1、哪些情况适合加索引

1.具有唯一特性的字段
2.频繁作为WHERE查询条件的字段
3.经常GROUP BY和ORDER BY的列
4.频繁UPDATE、DELETE、的WHERE条件列
5.DISTINCT字段需要创建索引
6、多表JOIN连接操作时
7、连接表的数量不要超过三张。
8、要对WHERE条件创建索引，可以更好的过滤。
9、对连接的字段创建索引，且连接的字段必须类型一致，否则索引会失效。
10、使用列的类型小的创建索引
11、使用字符串的前缀创建索引
12、在Varchar字段创建索引时，必须指明索引长度
13、使用前缀索引时，无法支持使用索引排序
14、区分度高（散列度）高的适合作为索引
15、创建联合索引时，使用越频繁的列越要放在左侧
16、在多个字段都要创建索引的情况下，联合索引优于单值索引
17、单表中不要超过6个索引
18、索引占用磁盘空间，索引越多，需要的磁盘空间越大。
19、索引会影响INSERT、DELETE、UPDATE等语句的性能。
20、当可选多个索引时，优化器在选择索引时会耗费时间。

2、哪些情况不适合加索引

1、在WHERE中使用不到的字段，不需要设置索引
2、数据量小的表不适合加索引
3、有大量重复数据的不要创建索引
4、经常更新数据的表或字段不要创建过多的索引
5、不建议用无序的值作为索引（例如身份证号）
6、删除不使用或很少使用的索引
7、不要定义冗余或重复的索引，例如联合索引和单值索引

索引声明和使用
1、索引的分类
按功能逻辑分类：唯一索引、全文索引、主键索引、普通索引；
按物理实现分类：聚簇索引、非聚簇索引；
按作用字段个数：单列索引、联合索引。
2、索引的语法操作
索引的创建

1 隐式的创建索引（主键约束、唯一性约束、外键约束的字段上）

2 显示的创建

CREATE TABLE 表名 (id int, lname varchar(20)
[UNIQUE | FULLTEXT | SPATIAL]
[INDEX | KEY]
[索引名字] (索引字段列 [索引长度])
[ASC | DESC]);

3 在已创建的表中添加索引

ALTER TABLE 表名 ADD INDEX [索引名字] (索引字段列 [索引长度]);

CREATE INDEX [索引名字] ON 表名(索引字段列 [索引长度]);
索引的查看

方式一

SHOW CREATE TABLE 表名;

方式二

SHOW INDEX FROM 表名;
索引的删除

方式一

ALTER TABLE 表名 DROP INDEX 索引名字;

方式二

DROP INDEX 索引名字 ON 表名;
MySQL8.0新特性
降序索引，支持DESC降序排列
隐藏索引

创建表时创建隐藏索引

CREATE TABLE (id INT,
INDEX 索引名称(索引字段) invisible);

创建表后添加隐藏索引

ALTER TABLE 表名
ADD INDEX 索引名称(索引字段) invisible;

CREATE INDEX 索引名称 ON 表名(索引字段) invisible;

修该索引的可见性

ALTER TABLE 表名 ALERT INDERX 索引名 visible/invisible;