Mysql最左匹陪原则-《大爱仙尊版》

时间：2024-12-14 07:06:36

MySQL 在处理 SQL 查询时为了提高性能，通常会根据优化器解析并优选最优路径来进行运行。在过程中，最优匹配原则是其中重要的一个概念，用于决定如何使用指定的索引和查询条件。下面我与你一起分析这一原则，并进一步联系到热门小说《蛊真人》中的深刻观念，以增加文章的轻松性和生动性。

什么是最优匹配原则？

最优匹配原则（Best Matching Principle），指在使用 MySQL 索引时，查询优化器会根据查询条件和索引类型，优先选择和查询匹配度最高的索引。比如：

  1. 优先使用最具体的索引，如 B-Tree 索引在这方面具有明显优势。
  2. 并列过滤条件，选择最小最简单的数据集。
  3. 同时考虑索引路径和数据规模，保证优化的解决方案。

在《蛊真人》中，“求完美，必先磨精致”一言展示了完美匹配的重要性。调用优化器时，就像制定法则，需要按照最优地选择索引，以达到高效和完美的目标。

最优匹配在实际中如何实现？

我们通过一些 SQL 示例和实现来解释最优匹配的原理。

1. 优选匹配索引类型

在使用其他过滤条件时，常使用的 B-Tree 和固定值匹配：

-- 创建一个索引
CREATE INDEX idx_name ON users (name);

-- 使用索引进行匹配查询
SELECT *
FROM users
WHERE name = 'Fang Yuan';

如果优先匹配索引，则查询性能显著上升。在《蛊真人》中，攻业师优选部分，就像 MySQL 在启用具体匹配索引，以最小成本获得最大收益。

2. 根据匹配范围索引

如果有复合查询，需要考虑索引和匹配并列：

-- 复合索引创建
CREATE INDEX idx_multi ON orders (user_id, order_date);

-- 使用复合索引查询
SELECT *
FROM orders
WHERE user_id = 12345 AND order_date = '2024-12-11';

在这里，user_id 和 order_date 根据匹配进行重点核实。比如，《蛊真人》中说：“术由心生，事在人举”也展示了匹配中决策发力的重要性，使实验体现具体匹配策略，用作入手初始。

3. 解决匹配冲突的问题

如果匹配索引过多，MySQL 会根据匹配优先策略进行选择：

-- 进行单值匹配的场景一
EXPLAIN SELECT *
FROM orders
WHERE status = 'complete';

-- 替代测试以便更合适用场景
ALTER TABLE orders DROP INDEX idx_multi;
CREATE INDEX idx_status ON orders (status);

如果选择的索引冲突或效果不理想，我们可以通过调整索引设计或者优化查询条件来解决问题。这种务实的处理方式正如《蛊真人》中所言：“穷则变，变则通”，在复杂场景中找到合适的解决之道。

4. 多条件查询时的索引使用

在多条件查询中，优先考虑联合索引的创建顺序和查询条件的选择：

-- 创建联合索引
CREATE INDEX idx_product ON products (category, price);

-- 查询时利用索引
SELECT *
FROM products
WHERE category = 'electronics' AND price > 1000;

联合索引的顺序对查询优化至关重要，类似于《蛊真人》中“事在人为，器在用好”的理念，合理利用工具能使效果事半功倍。

总结：蛊师，练蛊，养蛊，用蛊，都是有技巧的。

最左前缀

  • 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
  • and 忽略左右关系。既即使没有按顺序 由于优化器的存在，会自动优化。经过试验结论 建立了 idx_nameAge 索引 id 为主键
    • 当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。
      既 select 后的字段 有索引，where 后的字段也有索引，则无关执行顺序。 除开上述条件 才满足最左前缀法则。
    EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';
    EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';
  • 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引中范围条件右边的列
    • where条件查找范围条件，type回事range 并且后面的where条件 索引失效
  • 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
    • 会用到index extra中 会using index 性能更好
  • mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
    • 索引 idx_nameAgeJob idx_name
      • 使用 != 和 <> 的字段索引失效(!= 针对数值类型。 <> 针对字符类型前提 where and 后的字段在混合索引中的位置比当前字段靠后 where age != 10 and name='xxx' ,这种情况下，mysql自动优化，将 name='xxx' 放在 age ！=10 之前，name 依然能使用索引。只是 age 的索引失效)
  • is not null 也无法使用索引,但是is null是可以使用索引的
  • like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
    • like ‘%abc%’ 或者 "%abc" type 类型会变成 all
    • like ‘abc%’ type 类型为 range ，算是范围，可以使用索引

相关文章