MySQL索引笔记

拼搏现实的明天。 2022-09-16 11:30 123阅读 0赞

一、什么是索引  
独立于表之外的一个（b+tree）数据结构的数据集合，便于快速查询。  
相当于书的目录。  
二、索引的分类  
1、主键索引，聚簇索引  
只有表中包含主键那么主键索引就已经建立不需要再单独创建。  
2、辅助索引，非主键索引，非聚簇索引  
三、索引的数据结构  
1、索引需要满足的要求  
1）等值查询  
select \* from t where id = 1;  
2）范围查询  
select \* from t where id > 10 and id < 100;  
2、索引的数据结构  
1）hash，索引数据结构的选择之一。  
如果所有的查询都是等值查询最好的数据结构应该是hash。  
2）二叉查找树（二叉排序树）  
特点：  
添加到树中的第一个节点为根节点，再添加数据时，如果数据大于等于根节点排在右子树上，如果小于根节点排在左子树上。  
理想状态时间复杂度：O(log2n)  
问题：  
当根节点选择的不合理时，二叉树有可能会退化成链表。  
3）平衡二叉查找树  
特点：  
当左右子树的高度差超过1的时候会进行左旋或者右旋操作，保持树的平衡状态。  
保证时间复杂度：O(log2n)  
问题：  
1、当数据量大的时候二叉树的高度很高，变量到叶子节点需要产生的磁盘的io次数很多，性能很差。  
2、MySQL数据库每一次磁盘IO都是以数据页为单位，一个数据页默认是16k，如果只存储一个字段的话很浪费空间。  
解决思路：  
1、采用多叉树  
2、每个磁盘块中存储多个元素，每个元素左右都可以有一个指针。  
4）b-tree  
特点：  
每个节点中保存多个元素，每个元素左右两侧各有一个指针指向另外一个节点。每个节点中保存的元素的数量叫做度（degree）。  
每个节点中保存整行记录的数据，节点的度取决于一行记录的总的大小。一旦节点中元素的数量大于等于度，节点会进行分裂，把中间的元素提升到上层节点，当前节点分裂成两个。  
问题：  
1、所有的数据都是保存在节点中，导致中间节点度变小。  
2、如果使用btree做范围查询时效率很低  
5）b+tree  
特点：  
所有的中间节点只保存key值不保存数据，所有的数据都保存在叶子节点中并且有序，并且叶子节点之间有双向链表。  
最终选择b+tree作为索引的数据结构。  
四、MyIsam引擎的索引  
索引和数据是保存在不同的文件中。  
1、主键索引  
b+tree结构  
中间节点保存的是主键的数据。叶子节点中保存的是对应记录的一个地址（指针）。  
2、辅助索引  
存储结构和主键相同。  
五、InnoDB引擎的索引  
索引和数据是存在一个文件中。  
1、主键索引  
b+tree结构，中间节点保存的就是主键的主键。叶子节点中保存的就是记录行的数据。主键索引和表中的数据是聚簇存储在一起的所以叫做聚簇索引（聚集索引）。

在使用InnoDB引擎时表一定要有主键，如果没有主键InnoDB引擎会自动生成一个主键rowid。
    2、辅助索引（非聚簇索引）
    	b+tree结构。中间节点保存的就是key值，叶子节点中保存的是对应记录的主键的值。
    	查询时，根据索引找到对应记录的主键的值，然后根据主键查询主键索引找到对应的记录，这个过程叫做回表。

六、组合索引  
思考：当根据多个非主键字段作为条件进行查询时，为了提高效率是每个字段上创建各创建一个索引还是创建一个索引包含多个字段呢？  
应该考虑创建一个索引，其中包含多个字段。

原因：
    	1、查询时一个表只能使用一个索引。
    	2、如果使用组合索引，有可能多个字段都可以使用索引
    
    存储方式：
    	安装创建索引的字段顺序来排列，先根据a排序，当a字段相同的情况下b是有序的，b相同的情况下c是有序的。
    使用组合索引时应该遵守最左前缀原则：
    	先匹配最左的字段，字段相同的情况下匹配后面的字段。
    	如果有范围查询导致后面的字段无法应用索引。
    
    	**索引使用口诀**
    	全值匹配我最爱，最左前缀要遵守；
    	带头大哥不能死，中间兄弟不能断；
    	索引列上不计算，范围之后全失效；
    	Like百分写最右，覆盖索引不写星；
    	不等空值还有OR，索引失效要少用。