【Mysql索引优化】索引优化的最佳实现

【Mysql优化】索引优化的最佳实现

前言
在数据库性能优化中，索引是至关告急的工具。公道利用索引不但可以进步查询服从，还能明显镌汰数据库的相应时间。本文将从全值匹配、最左前缀法则、覆盖索引和范围查询优化等方面，先容MySQL中索引优化的最佳实践。

---

1. 全值匹配：索引的最佳利用方式

全值匹配是指在利用复合索引时，查询条件必要包罗索引的全部列，从而最大限度地利用索引。比方：

1. -- 创建复合索引
2. CREATE INDEX idx_name_age_position ON employees (name, age, position);
4. -- 全值匹配查询
5. EXPLAIN SELECT * FROM employees WHERE name = 'John' AND age = 30 AND position = 'Manager';

复制代码

分析：在上述查询中，复合索引的长度被完备利用（如140字节），查询性能最佳。

---

2. 最左前缀法则

最左前缀法则要求**复合索引必须从最左列开始查询，且不能跳过列。**比方：

1. -- 使用复合索引的部分列
2. EXPLAIN SELECT * FROM employees WHERE name = 'John' AND age = 30;

复制代码

分析：此查询依照最左前缀法则，索引长度为78字节，查询服从较高。假如跳过name列，则索引会失效。

---

3. 只管利用覆盖索引：优化查询性能。镌汰 select * 语句

覆盖索引是指查询结果只依靠索引，而无需访问表。比方：

1. -- 查询字段包含索引列
2. EXPLAIN SELECT name, age FROM employees WHERE name = 'John';

复制代码

上风：通过覆盖索引，查询仅从索引中获取数据，大幅镌汰了I/O利用。

---

4. 范围查询优化

范围条件大概导致索引的部门失效，比方：

1. -- 范围查询
2. EXPLAIN SELECT * FROM employees WHERE name = 'John' AND age > 30;

复制代码

办理方案：在大概的环境下，只管将范围查询拆分为多个小范围，或优化查询逻辑。

---

5. 不在索引列上做任何利用（盘算、函数、（自动or手动）范例转换）

索引底层：将结果与索引中的键值直接匹配。
好比下面函数结果就无法直接与索引中的键值匹配，导致了索引失效。

1. -- 索引失效
2. EXPLAIN SELECT * FROM employees WHERE LEFT(name, 3) = 'John';

复制代码

优化方案：通过范围查询、逻辑调解等制止函数的利用。

---

6. 少用不等、空值、OR（IN）

6.1 不便是（!= 或 <>）、NOT IN 、NOT EXISTS 的影响：

!= 查询会导致索引扫描出全部不便是某个值的记载，如许会使得索引的有序性无法发挥作用，因此 MySQL 会选择全表扫描。

1. EXPLAIN SELECT * FROM employees WHERE name != 'John';

复制代码

**发起：**假如大概，将不便是条件转为范围查询（如 name > '张三' 和 name < '李四'），MySQL 更轻易利用索引。
由于Mysql内部优化器会根据检索比例、表巨细等多个因素团体评估是否利用索引。
6.2 IS NULL或IS NOT NULL 的影响：

在B树或B+树索引中，NULL值通常不具有固定的序次，因此索引对于这种范例的查询支持较差。

1. EXPLAIN SELECT * FROM employees WHERE name IS NULL;

复制代码

发起： emmm，提拔有限，只管不消吧。
6.3 利用 OR 或 IN 的影响：

Mysql内部优化器会根据检索比例、表巨细等多个因素团体评估是否利用索引。

1. EXPLAIN SELECT * FROM employees WHERE name in nameList;

复制代码

发起： 有点像范围查询，可以看第5点。

---

7. 存储引擎不能利用索引中范围条件右边的列

MySQL 的团结索引（Composite Index）是按照索引字段的序次来构建的。
每一列的值在索引中是有序分列的，而范围查询（如 >、<、BETWEEN 等）会粉碎后续字段的有序性，导致索引无法继承利用。

1. -- 联合索引：
2. ALTER TABLE `employees` ADD INDEX `idx_name_age_position` (`name`, `age`, `position`);

复制代码

索引的分列是多条理排序：

• 先按照 name 排序。
  
• 同样的 name 按照 age 排序。
  
• 同样的 name 和 age 按照 position 排序。
  

1. -- age后续字段的顺序变得不确定，B+ 树无法高效利用这些字段的有序性，导致部分索引失效。
2. SELECT name, age, position FROM employees WHERE name = 'John' AND age > 18 AND position = 'normal';

复制代码

优化方案：

• 假如age范围查询能过滤掉大部门数据，那就让他放在索引最左边，别的失效也不要紧。
  
• 拆分查询，将范围查询的部门和其他条件拆开
  
  1. -- 然后在结果中筛选 position = 'beijing'
  2. SELECT name, age, position FROM employees WHERE name = 'John' AND age > 18;

  复制代码

---

8. Like百分写最右

LIKE模式匹配中的%假如在开头会导致索引失效：

1. -- 索引失效
2. EXPLAIN SELECT * FROM employees WHERE name LIKE '%John';

复制代码

优化方案：

• 利用覆盖索引，如许索引就算失效了，也不消回表，进步了查询服从。
  
• 假如不能利用覆盖索引，思量借助搜索引擎（Elasticsearch等）
  

---

9.字符串不加单引号索引失效

就是注意字符串范例的要加引号吧。

1. -- 索引OK
2. EXPLAIN SELECT * FROM employees WHERE name = '001';
3. -- 索引失效
4. EXPLAIN SELECT * FROM employees WHERE name = 001;

复制代码

---

总结

工作中碰到sql调优标题，团结调优工具和对索引的深入明确，应该可以或许资助我们办理大部门标题了。

博客主页: 总是学不会.

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。