mysql(二) 索引

感觉要看的，要学的东西太多，好多都是表面的，知道使用，底层的了解还需要时间，不废话了直接上正题。

索引：什么是索引，一般来说，索引就是可以来存储键值方便快速查找的数据结构。

索引的分类：聚簇索引和非聚簇索引。

聚簇索引是物理索引，在理解上可以认为该索引是顺序索引，如按照主键id递增的方式一次物理顺序存储，因为聚簇索引是按照某一列的排序存储的，因此一个表只能有一个聚簇索引。

特别声明：上一篇说了，主键的问题。聚簇索引就是通过主键来建立的，如果表内没有指定主键，MySql会将第一个都不为not null的唯一索引作为聚簇索引来使用，当然如果InnoDB（这里的聚簇索引仅适用于InnoDB这种引擎结构)表中没有主键且没有适合的唯一索引（没有构成该唯一索引的所有列都NOT NULL），MySQL将会自动创建一个隐藏的名字为GEN_CLUST_INDEX 的聚簇索引，也就是隐藏的主键（会自增的）。

综上所述，InnoDB表必有聚簇索引且只会有唯一的一个。

上面了解什么是聚簇索引，那么按照概念范围，那么所有不是聚簇索引的索引  统称为 非聚簇索引或者辅助索引（敲黑板，考试必考题）。

辅助索引 就是像什么联合索引（复合索引）普通索引，唯一索引，前缀索引，降序索引，全文索引什么的，是不是发现有一个唯一索引，这里可以不一样，这里的唯一索引就是简简单单的唯一还是要和聚簇索引区分开的，这算是在已经有主键，e,有聚簇索引的前提下

辅助索引和聚簇索引最大不同就是，辅助索引结构体（B+树）的叶子节点上保存的是主键的id而不是行数据，当使用辅助索引时，拿到主键值（id），再去主键索引上面找到对应id节点，拿到节点存储数据（这里就表明了，主键一定要尽量短，不然辅助索引就会很大，毕竟辅助索引的节点保存的数据时主键名/主键值，所以一般来说都用自增的整形列作为主键列，毕竟四个字节）

以下摘抄：

非聚簇索引（NoClustered Index），又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针，而是行的主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行，这需要两次B-Tree查找。

聚簇索引的特点：

优点：

可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少量的数据页就能获取某个用户全部邮件，如果没有使用聚集索引，则每封邮件都可能导致一次磁盘IO。
数据访问更快，聚集索引将索引和数据保存在同一个B-Tree中，因此从聚集索引中获取数据通常比在非聚集索引中查找要快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
缺点：

聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没有那么重要了，聚集索引也没有什么优势了。
插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表。
更新聚集索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
基于聚集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题，当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间。
聚集索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
二级索引可能比想象的更大，因为在二级索引的叶子节点包含了引用行的主键列。
二级索引访问需要两次索引查找，而不是一次。（效率）
————————————————
版权声明：本文为优快云博主「Still_Believe_」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.youkuaiyun.com/baidu_15952103/article/details/109234605

偷来的图：https://blog.youkuaiyun.com/w605283073/article/details/95255618

整个过程从K索引树到主键索引树的过程叫做“回表”。（这里有一个关键点，请记好什么叫 回表）

好了，上面的内容虽然不多，但是用来总结和回顾应该足够了。（可能本人见识短浅，未得深意）

联合索引又称复合索引：对于这种类型，其实也很简单的理解，因为Mysql从左到右的使用索引中的字段，最左匹配原则（左前缀原则）便诞生了，如果一个查询只会使用到某一个符合索引的一部分，也只能是最左的部分

举个例子吧：key_index(a,b,c)   a b c 都是表字段名，这个复合索引可以支持 a ||  a,b  ||  a,b,c  3种组合进行查找，也就是说这个复合索引会有三种结构树

（联合索引本质：当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引 ）

Explain 慢查询了解一下

多个单列索引在多条件查询时只会生效第一个索引！所以多条件联合查询时最好建联合索引！

只有单列索引的情况下，and 只会调用第一个字段的索引，or则会调用 两个字段的索引

https://blog.youkuaiyun.com/bigtree_3721/article/details/87478706

https://blog.youkuaiyun.com/stand_forever/article/details/87893302

当然索引还有一些注意项，什么长度问题，字段（数据量）类型问题，索引使用的多少问题，最左原则。IO效率，查一次还是两次数据

还有是什么select中一些使用索引和不使用索引的写法情况，像 索引不能用在包含有NULL值的列上，使用like时  like 'key%' 会调用key的索引 而 like '%key%' 不会调用

有一个特别的索引名称：覆盖索引

实际上就是上面已经说过的在InnoDB中，因为叶子结点是保存数据的，那么通过现有的索引树无需回表直接从索引数据中获取数据，这样效率更高

说到索引还有一个要明确的点：IO问题    也可以说是为什么索引是B+数据，上面的内容已经大概的明确了。数据是保存在磁盘上，当内存和磁盘进行数据交互的时候就是要关注IO的效率，所以这里涉及到了硬件层面和软件层面的考虑。

硬件如果变化不了，提高效率就只能从软件层面考虑了。

IO:   读取次数少   读取量少

数据存储方式： 存储格式（数据结构） 存储文件大小（数据在读取时是要分块读取的，防止内存不够大）

分块的思想来源

数据交互的扩展（操作系统）：

1.局部性原理：

数据和程序都有聚集成群的倾向，空间局部性（相关数据，具备相同类别的数据放到一起）

之前被访问过的数据，有可能很快被下一次访问到，时间局部性

2.磁盘预读：

内存跟磁盘发生交互的时候，有一个基本的逻辑单位叫做页，页的大小跟操作系统相关，一般是4k或者8k(大小是规定的大小)，我们进行数据交互的时候可以是页的整数值

InnoDB存储引擎每次读取数据读16k

因为页的大小是固定的，所以当有大量的数据插入的时候，索引就要进行维护，对于索引的维护是mysql性能变低的一个重要原因。因为当前索引所在文件块已经占满时，还要按照顺序向其中的添加索引数据这个时候就会把原有的页（数据块）进行分裂，形成新的两个新的页，并且父节点会形成新的指针，若父节点所在页原本也是已经占满的，同样会进行分裂一次向上循环，因此MySQL的性能就会降低，因为时间都浪费到索引的维护上了。所以能保证自增最好还是保证自增，防止已有的块节点大量的分裂。

注意：数据库在删除数据时实际上并不是真的删掉数据而只是加了一个删除的标识，原有数据结构不变（有替换的流程）

memory存储引擎支持hash索引，InnoDB支持自适应hash索引

hash表 需要优良的hash算法（https://www.jianshu.com/p/f9239c9377c5）；无序，无法进行范围查找；需要大量的内存空间；