我要评论什么是b树？
b树也是b-树,b树是一种多路自平衡的搜索树（b树是一颗多路平衡查找树）它类似普通的平衡二叉树，不同的一点是b树允许每个节点有更多的子节点。b树是可以认为是一个n叉搜索树。
什么是b+树？
一颗b+树大概能存放多少条数据？
假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录
如果b+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放100条记录。
如果b+树有2层，最多能存放1000×100=100000条记录。
如果b+树有3层，最多能存放1000×1000×100=100000000条记录。
如果b+树有4层，最多能存放1000×1000×1000×100=100000000000条记录。
b树与b+树的区别？
1、b树的每一个节点都存放了键值（主键）和数据（data），而b+树只在叶子节点中存放数据（data），非叶子节点存放主键（从上面画的图可以得知），因此对于相同层树的b树与b+树，b+树能存放的数据更多，innodb引擎使用b+树作为索引的数据结构，可以使得查询数据的过程中io次数减少，查询效率提升。
2、从下面的两张对比图中和上面画的图中，可以看到b+树的叶子节点都有指针指向相邻的叶子节点，形成一个有序链表，可以按照键值排序来遍历所有记录，由于数据顺序排列并且相连，所以便于区间查找和搜索。而b树叶子节点指针为null，则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有b+树好。
页分裂
页分裂如何产生？
新表插入新的数据过程：新建一个根节点页，新插入的数据存放在这个根节点页中，当这个根节点空间用完时，会将根节点里面的数据复制到新的页面中（页a），然后对这个新页进行页分裂的操作，新插入的记录根据键值的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。
页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
聚集索引/聚簇索引
innodb存储引擎，将表的主键构造了一颗b+树索引，并且将整张表的行记录数据存放在该b+树的叶子节点中。每一张表只能有一个聚集索引。索引即数据，数据即索引。
如果我们没有定义主键呢？
mysql会使用唯一性索引，没有唯一性索引，mysql也会创建一个隐含列rowid来做主键，然后用这个主键来建立聚集索引。
二级索引/辅助索引
辅助索引（secondary index）是数据库中的一种索引结构，用于加速对非主键列的查询。
二级索引/辅助索引叶子节点不包含行记录的全部数据，二级索引叶子节点存放二级索引列的数据与主键，二级索引列数据用于排序，主键数据用于【回表】操作。
回表
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，innodb存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为 回表 。也就是根据辅助索引的值查询一条完整的用户记录需要使用到2棵b+树----一次辅助索引，一次聚集索引。
一个表有二级索引时候，当用二级索引列作为条件查询时，mysql就一定会走该二级索引吗？
不一定，如果回表的记录越多，二级索引的性能就越低，有可能mysql宁愿去走全表扫描，而不走二级索引。
mysql的查询优化器会决定该查询该不该走索引，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。
从辅助索引查找到的记录，每找到一条就进行回表操作，重复这个过程。
回表操作时一次回表一条，还是批量数据进行回表？
mysql的回表操作通常是一次一条进行的，也就是针对每一条记录进行回表操作。当执行查询时，mysql首先使用辅助索引（如果有）定位到满足查询条件的记录的主键值，然后再通过主键值进行回表操作，获取完整的数据记录。
这种一次一条的回表操作模式，虽然可以确保数据的准确性，但在大规模查询或涉及大量数据的情况下可能会导致性能问题。每次回表操作都需要进行磁盘i/o操作，可能会产生较高的延迟和开销。
为了优化回表操作的性能，mysql提供了一种称为"回表优化"（batched key access）的技术。回表优化通过批量回表操作的方式减少磁盘i/o操作的次数，提高查询性能。
回表优化的基本思想是，将多个主键值收集到一个批次中，然后一次性进行回表操作。这样可以减少磁盘i/o操作的次数，提高效率。mysql使用了类似于"回表合并"（batched key access merge）的算法，将多个单独的回表操作合并为一个批次执行。
需要注意的是，回表优化并不是在所有情况下都会生效。它通常在使用innodb存储引擎、批量查询或涉及大量数据的场景下才会发挥作用。此外，回表优化的效果还受到多个因素的影响，包括硬件性能、查询语句的复杂性等。
综上所述，mysql的回表操作通常是一次一条进行的，但可以通过回表优化技术实现批量回表操作，以提高查询性能。是否应用回表优化取决于具体的场景和配置。
联合索引/复合索引
将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引，比如index(a,b)就是将a,b两个列组合起来构成一个索引。建立联合索引只会建立了一颗b+树。例如：(a, b, c) 联合索引，是按照a字段进行排序，b 和 c 是全局无序，局部相对有序的。只有当a相同的情况下b才会进行排序
index(note,b)在索引构建上，包含了两个意思：
1、先把各个记录按照note列进行排序。
2、在记录的note列相同的情况下，采用b列进行排序
联合索引非叶子节点存放的是什么？
联合索引的非叶子节点存放的是索引键的值(key1,key2.....)。索引键是联合索引中定义的多个列的组合，可以按照指定的列顺序进行排序。非叶子节点存储这些索引键的值和主键值。
覆盖索引
innodb存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录(回表)。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的io操作。所以记住，覆盖索引并不是索引类型的一种。
覆盖索引，从辅助索引就能获取需要查找的记录，而不需要进行回表操作。
比如 select name,birthday from student where name = '张三' and birthday > '2000-01-01'之类
name,birthday 我们已经创建好了联合索引，所以上面的sql我们只需要获取name和birthday这两列的值，我们直接在联合索引b+树上根据查询条件获取出结果并返回，我们不需要作拿着主键id去聚簇索引重新在查找获取数据，由此一来我们减少了回表的操作，减少了io的开销，提高了查询效率。
最佳左前缀法则
最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。
在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排序，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内有序递增；而clie只能在a和b两列值相等的情况下小范围内有序递增。
就像上面的查询，b+ 树会先比较a列来确定下一步应该检索的方向，往左还是往右。如果a列相同再比较b列，但是如果查询条件中没有a列，b+树就不知道第一步应该从那个节点开始查起。
可以说创建的idx_(a，b，c)索引，相当于创建了(a)、(a，b)、(a，b，c)三个索引。
组合索引的最左前缀匹配原则：
使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、
索引下推
在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，那在联合索引的 b+tree 找到第一个满足条件的主键值（id 为 2）后，还需要判断其他条件是否满足（看 b 是否等于 2），将不满足的条件（b!=2）过滤掉，减少回表查询次数。
如何创建索引？创建索引要注意哪些点？
至少满足以下要求：
能在尽可能少的磁盘的 i/o 操作中完成查询工作；
要能高效地查询某一个记录，也要能高效地执行范围查找；
需要占用物理空间，数量越大，占用空间越大；
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大，会降低表的增删改的效率，因为每次增删改索引，b+ 树为了维护索引有序性，都需要进行动态维护。
什么时候适合使用索引？
- 频繁用于查询的列：当某个列经常用于查询条件（如 where 子句）或连接操作（如 join）时，可以考虑为该列创建索引。索引可以加快查询的速度，减少需要扫描的数据量。
- 排序和分组操作：当需要对某个列进行排序或进行分组操作时，通过为该列创建索引可以提高排序和分组的效率。经常使用group by 或 order by的字段.
- 唯一性约束：当需要保证某个列或一组列的唯一性时，可以使用唯一索引。唯一索引可以防止重复插入或更新数据。
什么时候不需要创建索引?
- 频繁的大批量插入操作：如果表经常进行大批量的数据插入操作，创建索引可能会导致插入性能下降，因为每次插入都需要更新索引。
- 不常用的列：对于很少使用或不需要用于查询的列，创建索引可能没有太大意义。在这种情况下，索引只会增加存储空间和维护成本，而不会提供实际的查询性能改进。
- 频繁的更新操作：如果某个列的值经常变动，特别是大量的更新操作，那么索引的维护成本可能会很高。每次更新操作都需要更新索引，可能导致性能下降。比如说余额字段.
- 索引区分度太低：存在大量重复数据。比如性别字段。
- 表数据太少的时候，不需要创建索引。
常见优化索引的方法：
如何防止索引失效？
对索引使用左或者左右模糊匹配
like %xx，like %xx%不走索引
对索引使用函数
因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。
对 length(name) 的计算结果建立一个名为 idx_name_length 的索引，再用下面这条查询语句，这时候就会走索引了
对索引进行表达式计算
where id + 1 = 10 不走索引
where id = 10 - 1 走索引
对索引隐式类型转换
索引字段为varchar，但是查询条件使用了int，mysql在查询过程中会将自动「数字」转换成「字符串」
索引字段为int，但是查询条件使用了varchar，mysql在查询过程中会将自动「字符串」转换成「数字」
where 子句中的 or
如果在 or 前的条件列是索引列，而在 or 后的条件列不是索引列，那么索引会失效。
select * from users where age = 25 or country = 'cn';
假设在 "age" 列上有一个索引，但在 "country" 列上没有索引。在这种情况下，查询优化器可能会选择使用 "age" 索引来处理第一个条件 "age = 25"，但是在 or 运算符之后的条件 "country = 'cn'" 将无法利用索引。
由于第二个条件不是索引列，查询优化器可能会选择执行全表扫描或使用其他策略来评估满足第二个条件的行。这将导致无法充分利用索引，降低查询性能。
asc、desc混用
对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是asc规则排序，要么都是desc规则排序。
name和birthday是联合索引
select * from person_info order by name, birthday desc limit 10;
这样如果使用索引排序的话过程就是这样的：
- 先从索引的最左边确定name列最小的值，然后找到name列等于该值的所有记录，然后从name列等于该值的最右边的那条记录开始往左找10条记录。
- 如果name列等于最小的值的记录不足10条，再继续往右找name值第二小的记录，重复上边那个过程，直到找到10条记录为止。
这样不能高效使用索引，而要采取更复杂的算法去从索引中取数据，mysql可能认为不如直接文件排序来的快，所以就规定使用联合索引的各个排序列的排序顺序必须是一致的。
主键索引最好是自增的
如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。
页分裂
页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
新表插入新的数据过程：新建一个根节点页，新插入的数据存放在这个根节点页中，当这个根节点空间用完时，会将根节点里面的数据复制到新的页面中（页a），然后对这个新页进行页分裂的操作，新插入的记录根据键值的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。
尽量使用索引覆盖，减少回表
索引最好设置为 not null
- 准确性：将索引设置为 not null 可以确保索引仅包含非空值。这样可以避免索引中出现无效或缺失的数据，保证索引的准确性和一致性。如果索引列允许空值，可能会导致索引中存在无效的引用，从而降低查询的准确性。
- 索引效率：当索引列设置为 not null 时，数据库查询优化器可以更好地利用索引。由于索引中不包含空值，查询优化器可以更快速地定位到满足查询条件的数据行，提高查询的效率。
- null 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 innodb 存储记录的时候，如果表中存在允许为 null 的字段，那么行格式 (opens new window)中至少会用 1 字节空间存储 null 值列表.
发表评论