MySql索引

MySql索引

索引结构有很多种，B+树，B 树，哈希表等。在 InnoDB 下，索引结构是 B+ 树。

InnoDB 为什么是B+ 树？

首先来说 B+树，B 树 都是二叉平衡树的变种。而 InnoDB 用 B+是因为效率高。

具体体现在：

由于 B+ 树的非叶子节点只存储键，而不存储数据，所以每个节点可以存储更多的键值，从而使树的整体高度变低。由于每往下一层都是一次IO 操作，所以 B+ 树的查询效率高。并且B+ 树甚至可以，直接从结点开始遍历，总之效率更高。

由于叶子节点是有指针相连的，所以对于范围的顺序访问是更方便高效的。

由于非叶子节点存储的键值更多，所以缓存更容易命中。

而不用红黑树的原因是因为树高太高了。

总结

二叉查找树(BST) ：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表；

平衡二叉树(AVL) ：通过旋转解决了平衡的问题，但是旋转操作效率太低；

红黑树 ：通过舍弃严格的平衡和引入红黑节点，解决了 AVL 旋转效率过低的问题，但是在磁盘等

场景下，树仍然太高，IO 次数太多；

B 树 ：通过将二叉树改为多路平衡查找树，解决了树过高的问题；

B+树 ：在 B 树的基础上，将非叶节点改造为不存储数据的纯索引节点，进一步降低了树的高度；

此外将叶节点使用指针连接成链表，范围查询更加高效。

左连接，右连接，内连接的区别

在 SQL 中，连接（Join）是一种将两个或多个表中的数据根据某些条件组合起来的操作。左连接（Left Join）、右连接（Right Join）和内连接（Inner Join）是三种常见的连接类型，它们的主要区别在于返回的结果集包含的数据范围。

1. 内连接（Inner Join）：

   • 内连接只返回两个表中满足连接条件的记录。

   • 如果某个表中的记录在另一个表中没有匹配的记录，那么这些记录不会出现在结果集中。

   • 内连接是最常用的连接类型，它相当于逻辑上的“交集”。

   • 语法示例：

     SELECT * FROM table1
     INNER JOIN table2 ON table1.column = table2.column;

2. 左连接（Left Join 或 Left Outer Join）：

   • 左连接返回左表（第一个表）中的所有记录，以及右表（第二个表）中满足连接条件的记录。

   • 如果右表中没有匹配的记录，那么结果集中对应的列将显示为 NULL。

   • 左连接相当于逻辑上的“左表的全部加上右表的匹配部分”。

   • 语法示例：

     SELECT * FROM table1
     LEFT JOIN table2 ON table1.column = table2.column;

3. 右连接（Right Join 或 Right Outer Join）：

   • 右连接返回右表中的所有记录，以及左表中满足连接条件的记录。

   • 如果左表中没有匹配的记录，那么结果集中对应的列将显示为 NULL。

   • 右连接相当于逻辑上的“右表的全部加上左表的匹配部分”。

   • 语法示例：

     SELECT * FROM table1
     RIGHT JOIN table2 ON table1.column = table2.column;

总结来说，内连接只返回两个表中都存在的记录，左连接返回左表的所有记录和右表中匹配的记录，右连接返回右表的所有记录和左表中匹配的记录。在实际应用中，选择哪种连接类型取决于业务需求和数据分析的目的。

索引失效

不使用索引的查询条件

如果在查询条件上使用不包含索引列的表达式，或者有类型转换等，函数等操作，会导致索引失效。

例如，如果有一个索引在列 age 上，但查询条件是 WHERE TO_CHAR(age) = '30'，则索引不会被使用。

在索引列上有操作

例如对索引列进行算术运算，函数调用等。

例如，WHERE age + 1 = 31 或 WHERE UPPER(name) = 'JOHN' 都会导致索引失效。

类型不匹配

索引的类型和查询的类型不匹配，例如

如果索引列 phone_number 是字符串类型，而查询条件是 WHERE phone_number = 1234567890，则可能会发生类型转换，导致索引失效。

like 查询

如果 like 查询后接 % ，那么索引会失效。例如，WHERE name LIKE '%john%' 不会使用索引，但 WHERE name LIKE 'john%' 可能会使用索引

Or 条件

如果 or 条件中因为一些操作，导致有的索引列失效，或者包含没有索引的列，那么整个 OR 都会失效。

查询优化器的选择

数据库的查询优化器会根据统计信息和成本模型来决定是否使用索引。如果优化器认为全表扫描的成本更低，它可能会选择不使用索引。

索引优化

1. 选择合适的列当作索引

2. 可以使用复合索引

3. 使用覆盖索引

4. 检查是否有冗余的操作

5. 同步缓存

6. 最有效的索引放到最左边

总之大概就这些，还有的话就是保证索引不失效那些条件。