数据库底层索引讲解-排序和数据结构
为什么建议InnoDB表必须创建一个主键？并且推荐使用整形的主键？
因为如果不主动创建话数据库会找表数据中找一个不同的值做主键，如果他找不到的话就会自己创建一个隐藏列类似uuid来做主键索引。
因为数据库的底层是B+树每次查询都通过很多次比对，如果是使用字符串形式话还得把他转换成ASCLL码进行比对浪费时间并且占用内存，用数字就能节约内存空间和时间。
为什么经常推荐使用整形的自增主键？
先讲解Mysql的Hash结构
数据库对结果值进行一个hash运算把运算的结果存放到一个结果桶里面，类是一个数组的hash结构里面去，如果是相同的就放到一个字段里面去。存放内容是值和他的磁盘文件地址，索引访问到这个地址后在去内存中读取。
理想的情况：只需要通过一次的IO就能获取到结果拿到节点的磁盘文件地址。
但是平时大多数还是使用B+树结构。
hash不支持范围索引但是B+TREE可以因为B+TREE的叶子节点有指针在一个范围里面可以一直按固定的规律快速获取值。
所以这就是为什么不用BTREE而是B+TREE因为BTREE叶子节点没有指针每次范围查询都得从头到尾往下查，但是B+TREE可以。
为什么使用按顺序自增
因为直接插入数据的话B+TREE还得对值进行一个平衡的操作，通过自增的话就是在后面+值就可以了不需要进行分裂。
插入操作引发的平衡
- 节点分裂：当要插入一个新键值时，如果插入后当前节点的键值数量超过了节点的容量（假设节点的容量为
m，即最多能容纳m - 1个键值），则需要进行节点分裂。将当前节点平均分成两个新节点，中间的键值上移到父节点中。如果父节点也因此变得满了，那么就需要递归地对父节点进行同样的分裂操作，直到不再需要分裂或到达根节点。 - 父节点调整：在节点分裂过程中，若父节点的键值数量发生变化，可能需要调整父节点的指针，以确保树的结构正确。同时，可能需要更新父节点的一些元信息，如指向子节点的指针范围等。
删除操作引发的平衡
- 节点合并：当删除一个键值后，若某个非叶子节点的键值数量小于
m/2（向上取整），则可能需要进行节点合并操作。首先查看该节点的左右兄弟节点，看是否有可以借键值的情况。如果兄弟节点有多余的键值（键值数量大于m/2），则可以从兄弟节点借一个键值过来，并调整相关指针和键值的位置。如果兄弟节点也没有多余的键值，则将当前节点与兄弟节点合并，同时删除父节点中指向合并后节点的多余指针。 - 父节点调整：在节点合并或借键值操作后，父节点的键值数量和指针也可能需要相应调整。如果父节点因为子节点的合并或借键值操作而导致键值数量过少，也可能需要对父节点继续进行平衡操作，直到树达到平衡状态。
主键索引和非主键索引（辅助索引）的区别
二级索引的值存放的是主键索引聚集索引的值也就是存放的主键索引的id，作用是为了数值的一致性（减少复杂的并且只需要维护主键表）和节省存储空间。
辅助索引也是非聚集索引，每次查询完都要进行回表操作回到之前的表文件里面在查找一次。
联合索引的底层存储结构长什么样
同样也是B+tree结构，
他是如何排序的？
先通过创建索引的顺序把索引放进去然后根据值来比对，先比对name如果比对不了就比对age，在比对不了就比对position来排序
联合索引 idx_name_age_position 下面的三个值（从左到右）是：
- Bill 30 dev
- HanMeimei 28 dev
- Lilei 30 dev 。 这体现了联合索引中按照
(name, age, position)顺序存储的部分索引键值信息
B + 树对于联合索引idx_name_age_position的排序规则是首先按照name进行排序，当name相同时再按照age排序，若age也相同则按照position排序。
在给定的三个值中，首先比较name，"Bill"、"HanMeimei"、"Lilei" 按照字典序排列。"Bill" 小于 "HanMeimei" 小于 "Lilei"。对于 "Bill" 和 "Lilei"，他们的name不同，所以不需要再比较age和position就可以确定顺序。而对于 "HanMeimei" 和其他两个name不同的记录，也是直接根据name就确定了在索引中的位置。
最左前缀原理简介
最左前缀原理是数据库索引优化中的一个重要概念，主要用于指导索引的合理设计和查询条件的编写，以提高查询效率。以下是对该原理的详细分步解释：
- 索引结构基础：
- 数据库中的索引通常以B-Tree结构存储，允许快速查找数据。
- 复合索引由多列组成，如索引(a, b, c)表示按a、b、c的顺序排序。
- 最左前缀的概念：
- 最左前缀是指复合索引中从最左边开始的连续列组合。
- 例如，索引(a, b, c)的最左前缀包括(a)、(a, b)、(a, b, c)。
- 查询条件与索引利用：
- 当查询条件包含最左前缀时，数据库引擎能够有效利用索引。
- 例如：
- 条件a=1使用(a)前缀。
- 条件a=1 AND b=2使用(a, b)前缀。
- 条件a=1 AND b=2 AND c=3使用整个索引。
- 索引选择性与性能影响：
- 最左前缀的选择性越高，索引的效率越高，因为数据分布更均匀。
- 高选择性的列应放在索引的前面，以减少查询范围。
- 避免全表扫描：
- 如果查询条件不包含最左前缀，如仅使用b=2，索引无法使用，导致全表扫描，影响性能。
- 因此，应确保查询条件包含索引的最左前缀，以避免这种情况。
- 索引设计的最佳实践：
- 根据常见查询条件设计最左前缀，确保常用列放在前面。
- 避免在索引前面添加低选择性列，如性别，因为这可能降低索引的有效性。
- 实际应用案例：
- 假设用户查询通常涉及订单号和订单日期，索引设计为(order_id, order_date)。
- 查询条件如order_id=123可以高效执行，而仅查询order_date=2023-01-01则无法利用索引，导致性能下降。
通过遵循最左前缀原理，可以显著提升数据库查询性能，优化数据检索效率。
如果使用了联合索引查询的时候必须遵顼最左前缀否则他是不会通过索引进行查询数据的，如果跳过了就不是排好序的了，在B+TREE底层中他就找不到了，如果按排序顺序来他就能找到全部数据不需要整个表扫描。
会破坏最左原则的几种情况
- 函数使用：如果在索引列上使用了函数，可能会导致索引失效。例如，对于联合索引
idx_a_b_c，若查询语句为SELECT * FROM table WHERE SUBSTRING(a, 1, 5) = 'value'，即对列a使用了SUBSTRING函数，那么 MySQL 将无法使用索引a进行优化，因为函数的使用破坏了索引的有序性。同样，如果对索引列进行数据类型转换等操作，也可能导致类似的问题。 - 表达式计算：当查询条件中的索引列参与了表达式计算时，索引可能无法正常使用。例如，索引为
idx_a_b_c，查询语句是SELECT * FROM table WHERE a + 1 = 10，MySQL 不能直接使用索引a来查找满足a + 1 = 10的记录，而是需要对全表数据进行扫描，并计算每一行a列的值加 1 后是否等于 10。 - OR 条件：使用
OR连接索引列时，如果OR两边的列不是联合索引的最左前缀，可能导致索引失效。例如，对于联合索引idx_a_b_c，查询SELECT * FROM table WHERE a = 1 OR b = 2，由于OR条件中b = 2没有遵循最左前缀原则，MySQL 可能会放弃使用索引，而进行全表扫描。不过，如果OR条件中的所有列都是联合索引的最左前缀部分，索引可以正常使用。例如SELECT * FROM table WHERE a = 1 OR a = 2，此时可以使用索引a。 - 数据类型不匹配：如果查询条件中的数据类型与索引列的数据类型不匹配，可能会导致索引失效。例如，索引列
a是INT类型，而在查询中使用了字符串类型进行比较，如SELECT * FROM table WHERE a = '1'，MySQL 可能无法使用索引a，因为数据库需要进行隐式的数据类型转换，这会影响索引的使用效率，甚至可能导致全表扫描。
- 范围查找后的精确匹配问题
在联合索引中，进行范围查找后再对后续索引列进行精确匹配时，可能会出现索引部分失效的情况。例如对于联合索引idx_col1_col2_col3 ，如果查询SELECT * FROM your_table WHERE col1 BETWEEN 1 AND 10 AND col2 = 5 AND col3 = 8 ，MySQL 能利用col1进行范围查找，然后在满足col1范围条件的结果集中利用col2和col3进行精确匹配。但如果查询是SELECT * FROM your_table WHERE col1 BETWEEN 1 AND 10 AND col3 = 8 ，由于col2没有在查询条件中出现，在col1进行范围查找后，对于col3的匹配，数据库可能无法像完整使用联合索引那样高效地定位数据，因为跳过了col2这一索引列，破坏了最左前缀的连续性。