MySQL索引总结

索引用来加快对数据的访问。
通过B+树

对于不同类型的索引，是和不同的存储引擎相关的。

如果使用的是xx， 是B+树；如果是。。。是哈希表。

不同的存储引擎表示的是不同数据在磁盘的存储形式。

k-v格式的数据。不管任何类型的二叉树，都会让树变高，从而影响了IO的效率。

从而让树变低，从而提高访问速率。

主键索引和组合索引。

存储引擎，数据结构，索引的分类，索引涉及到的常问的几个名词，执行计划，索引优化。

(把该提的点都提到；把细节点都描述清除)

1. 存储引擎
   数据在磁盘上的不同组织形式。innodb，myisam；

ibd表示使用的是innodb的存储引擎，myd表示date，myi表示index。

为什么Innodb不用hash？

1. 使用hash表必须要保证具备好的hash算法，如果算法不合适的话会造成hash冲突或hash碰撞，会导致数据散列或不均匀，有可能会退化成一个链表；
2. 使用hash表时不支持范围查询，当需要范围匹配时，必须要挨个对比，效率太低；
3. 需要大量的内存空间；

二叉树，AVL树，红黑树为什么都不行？
4. 它们的相同点：都是分支有且只有两个的树； IO次数多，查询就慢了。

为什么(IO次数多，查询就慢了)
局部性原理+磁盘预读；
时间局部性和空间局部性。数据和程序都有聚集成群的倾向。之前被读取过的数据，可能很快被下一次读取。
磁盘预读：
如果我需要读一个字符a，是真的只取了一个字符吗?
内存和磁盘在进行数据交互时，有一个最基本的逻辑单元，称之为页或datapage。不同操作系统的页的大小不同，一般是4k或者8k，每次读取需要是4k的整数倍。
Innodb存储引擎默认读取16kb的数据。

io效率：减少io次数，减少io的量。

树变深导致io次数变多，因此检索时要尽可能的多的减少要读取的数据量，还要减少数据访问的次数。

如果把分支增多后，就可以解决深度过深的问题。

B树和B+树咋回事？

data占了空间，数据的范围就变小了。解决方法：加深B树深度或者把磁盘块弄大。

把每一个磁盘块中的data给删掉，有可能造成查询速度提升。

在叶子节点上放入全量数据，

mysql索引的b+树一般有几层？
如果支持千万级别数据量，3-4层足以。刚把指针+key的值占了10个字节，如果占了100个字节。支持的数据量是。。

索引列用int类型还是varchar类型好？
越短越好/。每个磁盘块的存储越大，这个树就表示的越矮。

给id创建了索引，id这一列要不要自增？
要顺序存储；

页分裂：数据插入时，要减少页分裂。要自增。分布式环境中无法自增，可以来生成一些id值。

页合并：一堆的磁盘块中只有一个记录值。空间会比较浪费，因此太麻烦了，所以能自增就自增。

为什么不能使用链表？
使用链表需要挨个对比，复杂度是O(n)；

删除记录后，表大小会变化吗？
当后续有数据插入来后，会有失效标记，直接把当前数据给补充上来。自动进行整理，把数据来进行补全。

查找有两种方式：从根节点按照指针指向从上往下；叶子节点开始把叶子节点取回来；

mysql聚簇索引和非聚簇索引的区别？

B+树在不同存储引擎中，存储的形式还是不同的。
Innodb有几个数据文件，一个是,frm，实际数据和索引是放在一起的。
叶子节点放的是实际的数据行。
创建表时没有主减的话，插入数据时，数据必须要和某一个索引列绑定放在一起。
索引列进行选择时，可以是主键，也可以是唯一键，也可以是6字节的row_id。

myisam只有非聚簇。

现根据name查id，再由id查结果。

mysum 本身就是分开存储。

使用索引有什么原则：

需要回表的索引都是非聚簇索引；

能不能把查询得某些列当成索引得某一个列值，此时就不需要回表了。

把一个key值换成两个值；

mysql如何进行优化

d字段是非索引列，全字段都是索引时，就要使用索引了。在id这个b+树种，存的是abc。

---

SQL优化的两个手段：

1. 通过执行计划返回的信息对SQL查询涉及到的表和索引进行优化，目的是让SQL可以更好的通过索引来获取所需要的数据，而不用再对表进行扫描；
2. 当单纯索引优化无效时，对SQL进行改写。

索引的作用是什么？

• 告诉存储引擎去哪里可以查到需要的数据，mysql索引在存储引擎层实现；

Innodb支持的索引类型：

• Btree索引；
• 自适应的hash索引；
• 全文索引(5.7后)
• 空间索引

BTree索引特点：

• 以B+树的结构存储索引数据，每个叶子节点中都包含了指向下一个叶子节点的指针，方便进行叶子节点中的遍历；
  
• B+树是一种平衡二叉树的数据结构，每个叶子节点到根的距离都是相同的，并且所有节点都是按键值大小顺序放在同一层的叶子节点上。每个叶子节点通过指针来进行连接。
• 不同存储引擎的具体实现不同：MyISAM的B索引，在叶子节点之上，所指向的是数据行的物理存储位置。而Innodb存储引擎，在B索引的叶子节点上，指向的是数据行的主键位置。

基于Btree索引的特点：

1. Btree索引适用于在这种全值匹配的查询中，对数据来进行过滤。
   全值匹配：查询课程为’mysql’的课程信息，就可以使用class_name='mysql'；
   在mysql中，使用页内表进行查询，也是可以使用到Btree索引的，只有当页内表中的值过多时， mysql优化器才可能认为使用全表扫描的方式来获取数据要优于使用索引查找的方式。从而不会使用到索引，但并不是页内表查询就不能使用到索引。
2. Btree索引适合处理范围查找：between...and;
3. 多个键值的情况，Btree索引从索引的最左侧列开始匹配查找列；
   
   选项a无法用到复合索引，选项b同时进行了过滤–过滤的顺序和查询的顺序无需相同，mysql的查询优化器可以自动调整查询优化条件的顺序，以适应索引。选项c只查询课程标题为’Mysql’的课程。

应该在哪些列上建立索引？

• 考虑where自居中的列建立索引，列要有筛选性：列上有不同的重复值；
• order by, group by ,distinct中的字段进行索引，可以提高排序性能，避免排序过程中使用临时表；索引的列顺序和orderby 子句的顺序完全一致；
• 多表join关联查询；

复合索引中如何选择键值的顺序？

• 区分度最高的列放在联合索引的最左侧；由于Btree索引是从左侧键值过滤数据，所以第一个键值越高，所过滤数据的量也就越高；
• 使用最频繁的列放在最左侧，提高索引利用率；
• 尽量把字段长度小的列放在最左侧；

Btree索引的限制：

• 只能从最左侧开始按索引键的顺序使用索引，不能跳过索引键；
• not in 或 <>操作无法使用索引；
• 索引列上不能使用表达式或函数；

索引使用的误区：

• 索引并非越多越好；
• in列表查询可以使用索引，or不能，通常把or改为in；
• mysql会调整查询顺序以适应键值顺序；

SQL改写原则：

• 使用outer join代替not in;
• 使用CTE代替子查询；
• 拆分复杂的大SQL为简单的小SQL: