索引相关知识

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

索引的优点：

索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。
索引可以帮助服务器避免排序和创建临时表
索引可以将随机IO变成顺序IO
索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性
关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

**

缺点：

**
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
对于非常小的表，大部分情况下简单的全表扫描更高效；

应该创建索引的列：

在经常需要搜索的列上，可以加快搜索的速度
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不该创建索引的列：

对于那些在查询中很少使用或者参考的列不应该创建索引。
对于那些只有很少数据值或者重复值多的列也不应该增加索引。
对于那些定义为text, image和bit数据类型的列不应该增加索引。
当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）

B-TREE：

B-tree树即B树，B即Balanced，平衡的意思。因为B树的原英文名称为B-tree，而国内很多人喜欢把B-tree译作B-树，其实，这是个非常不好的直译，很容易让人产生误解。事实上，B-tree就是指的B树。
B-树就是B树，平衡多路搜索树，树高一层意味着多一次的磁盘I/O

B树的特征：
关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；
自动层次控制；

B树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

B+TREE

B+树是B-树的变体，也是一种平衡多路搜索树
B+树的特征：
所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
不可能在非叶子结点命中；
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
更适合文件索引系统；

B+树的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；

为什么B+ 树比B 树更适合作为索引？

1、B+ 树的磁盘读写代价更低
B+ 树的数据都集中在叶子节点，分支节点 只负责指针（索引）；B 树的分支节点既有指针也有数据 。这将导致B+ 树的层高会小于B 树的层高，也就是说B+ 树平均的Io次数会小于B 树。
2、B+ 树的查询效率更加稳定
B+ 树的数据都存放在叶子节点，故任何关键字的查找必须走一条从根节点到叶子节点的路径。所有关键字的查询路径相同，每个数据查询效率相当。
3、B+树更便于遍历
由于B+树的数据都存储在叶子结点中，分支结点均为索引，遍历只需要扫描一遍叶子节点即可；B树因为其分支结点同样存储着数据，要找到具体的数据，需要进行一次中序遍历按序来搜索。
4、B+树更擅长范围查询
B+树叶子节点存放数据，数据是按顺序放置的双向链表。B树范围查询只能中序遍历。
5、B+ 树占用内存空间小
B+ 树索引节点没有数据，比较小。在内存有限的情况下，相比于B树索引可以加载更多B+ 树索引。

HASH:

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。Memory存储引擎使用Hash。

存储引擎：

索引是占据物理空间的，在不同的存储引擎中，索引存在的文件也不同。存储引擎是基于表的，以下分别使用MyISAM和InnoDB存储引擎建立两张表。

存储引擎为MyISAM：

MyISAM使用B+Tree数据结构存储索引，但都是非聚簇索引。
可见，索引和数据是分开的 索引的data部分只是索引的地址值。其实上文也提到过，.MYI就是MyISAM表的索引文件，MYD是MyISAM表的数据文件。
*.frm：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
*.MYD：MyISAM DATA，用于存储MyISAM表的数据
*.MYI：MyISAM INDEX，用于存储MyISAM表的索引相关信息

存储引擎为InnoDB：

InnoDB使用B+Tree数据结构存储索引，根据索引物理结构可将索引划分为聚簇索引和非聚簇索引（也可称辅助索引或二级索引）。一个表中只能存在一个聚簇索引（主键索引），但可以存在多个非聚簇索引。
B+树 叶子节点包含数据表中行记录就是聚簇索引（索引和数据是一块的）。
*.frm：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
*.ibd：InnoDB DATA，表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据

MySQL 的索引有两种分类方式：

逻辑分类和物理分类。
逻辑分类：
按功能划分
主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL

唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

全文索引：它查找的是文本中的关键词，主要用于全文检索。

按列数划分
单例索引：一个索引只包含一个列，一个表可以有多个单例索引。

组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

物理分类：

分为聚簇索引和非聚簇索引（有时也称辅助索引或二级索引）

聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。

非聚簇索引：数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。
优点：访问更快，对于主键的排序和查找速度非常快
缺点：依赖插入顺序，更新主键代价高，二级索引需要两次索引查找

在平时使用索引中，有以下几点总结及建议：

1、在区分度高的字段上面建立索引可以有效的使用索引，区分度太低，无法有效的利用索引，可能需要扫描所有数据页，此时和不使用索引区别不大。
2、联合索引，注意最左匹配原则：必须按照从左到右的顺序匹配，MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
3、查询记录的时候，少使用*，尽量去利用索引覆盖，可以减少回表操作，提升效率。
4、有些查询可以采用联合索引，进而使用到索引下推，也可以减少回表操作，提升效率。
5、禁止对索引字段使用函数、运算符操作，这样将会使索引失效。
6、字符串字段和数字比较的时候会使索引无效。
7、模糊查询 ‘%值%’ 会使索引无效，变为全表扫描，但是 ‘值%’ 这种可以有效利用索引。
8、排序中尽量使用到索引字段，这样可以减少排序，提升查询效率。

第一条优化建议就是 1:对于那些大量重复字段(性别、状态)没必要建立索引。 2:对于经常查询的热点字段建立索引,如果有多条件查询,尽量建立组合索引,这样也会节约空间成本。
其次:索引也会存在很多建立了之后不生效的情况，除了上述说到的最左匹配,还有诸如以下
隐式转换-查询条件的数据类型和字段的数据类型不匹配。
查询条件有is null、is not null 不走索引。
查询条件是用函数或计算操作。比如concat(‘jingxi’,1)不走索引;
or条件,任意一个or连接的条件没有索引,就会失效。
查询优化器的成本计算导致不走索引。
这些原因有部分通过上面的分析应该可以推断出原因，这里就不细说了比较固定,可以通过explan去查看这条sql是否走了索引,走了什么索引。

参考：
(https://blog.csdn.net/wangfeijiu/article/details/113409719)