mysql系列(五) mysql索引详细解析及使用

序号	名称	链接地址
1	mysql系列(一) centos7 安装msql	https://blog.csdn.net/qq_38130094/article/details/103529535
2	mysql系列(二)mysql简介之逻辑架构/锁粒度/事务/死锁/事务日志/MVCC	https://blog.csdn.net/qq_38130094/article/details/103549194
3	mysql系列(三) mysql存储引擎简介	https://blog.csdn.net/qq_38130094/article/details/103599497
4	mysql系列(四) mysql数据库设计优化	https://blog.csdn.net/qq_38130094/article/details/103551778
5	mysql系列(五) mysql索引详细解析及使用	https://blog.csdn.net/qq_38130094/article/details/103553971
6	mysql系列(六)mysql 慢日志查询(pt-query-digest)/如何单条SQL分析和Explain及trace工具	https://blog.csdn.net/qq_38130094/article/details/103551705
7	mysql系列(七)mysql 主从复制和mysql查询优化	https://blog.csdn.net/qq_38130094/article/details/103603586

1. 什么是索引：

mysql索引：BTree索引，一种树形结构，索引速度比全表查询快

一个叶子节点（mysql的一个叶）默认每页16K大小

InnoDB使用B+Tree，B+Tree中每个叶子节点都有一个指向下一个叶子节点的指针

2. 聚簇索引

聚簇索引是一种数据存储方式，因为是存储引擎实现，因此不是所有存储引擎都支持聚簇索引(不同的存储引擎会使用不同的索引)

聚簇索引：（主键都有一个主键索引，就是一个索引树）叶子节点存储是主键和列的数据，以主键为索引列，叶子存储了主键和列的数据

主键索引：按照主键的顺序把列聚集在一起

在mysqlInnoDB下 聚簇索引改变了数据结构，只有一个树结构的

如果没有主键：选择一个唯一非空索引列（这个也没有，会隐式的创建）一张表只有一个聚簇索引，其他都是二级索引；二级索引保存的是（主键列的值）；在根节点上（二分查找）在哪加载到内存，找到叶子节点在加载。。。直到找到对应的数据（树大了IO次数会多【磁盘寻到】，数据库的瓶颈出现）

列的宽度决定了索引树的大小，

索引树的大小每个叶存储多少数值，

每个叶存储多少数值决定了检索的速度

SHOW GLOBAL STATUS like 'Innodb_page_size'

InnoDB中默认最大填充因子是叶的15/16大小  16K数据  数据达到15K的时候， 分配到下一页。 不同页之间可能不是顺序的，只是通过一个指针相连。

叶的饱和度会影响索引树的大小

2.2组合索引

匹配最左前缀

全值匹配

匹配列前缀

匹配范围值

索引列的顺序非常重要（使用的顺序，建立索引的先后顺序）.

3. 非聚簇索引

MyISAM引擎：聚簇索引与非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别。

索引存储的是行的指针

4. hash index(Hash索引)

 哈希索引（Hash Index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据， 存储引擎都会对所有的索引列计算一个哈希码（HashCode），哈希码是一个较小的值。哈希索引将哈希 码存储在索引中，同时在哈希表中保存指向每个数据行的指针

 因为哈希索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非 常快。然而哈希索引也有它的限制：

1. 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行；
2. 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序 ；
3. 哈希索引不支持部分索引列匹配查找 Ø 哈希索引只支持等值比较，包括=、in()、<=>，也不支持任何范围查询 ；
4. 访问哈希索引的数据非常快，除非有很多哈希冲突 ；
5. 如果哈希冲突很多的话，一些索引维护操作(索引重建)的代价也会很高

自适应hash

      AHI是innodb存储引擎特有的属性，innodb存储引擎会针对索引数据的查询结果做自适应的优化，当某些特定的索引查询频率特别高的时候会自动为其建立hash索引，从而提升查询的效率。相比于B+Tree索引来说，hash索引能够大大减少对于io的访问次数，“一击命中” 查询数据，具备更加高效的性能，而且hash索引是由mysql内部自动适配的，无需dba在外部做过多的干预。

早期版本的hash索引是采用了单锁模式来防范并发访问问题，这对于程序自身的一个运作高效性有一定的”折扣“，后期通过对hash索引进行了分区，不同页的数据用不同的hashtable，每个分区有对应的锁来做并发访问的预防。

如果某天你发现了有很多线程都被堵塞在了RW-latches的时候，有可能就是因为hash索引的并发访问负载过高导致的堵塞，这个时候可以通过增大hash索引的分区参数，或者关闭自适应hash索引特性来进行处理

5. 全文索引（分词 elserch）

索引的字段要尽量小，因为BTree索引树高度页的大小以及页里面的数据大小决定的。数据越小，磁盘块存储的数据越多，树的高度越低，查询性能越高

使用索引带来的问题：

1、创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

2、索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值

3、当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。