MySQL性能优化

6. SQL及索引优化（下）

MySQL性能优化的5个维度

一、离散读（Multi-Range Read，MRR）

• 原理：
  • 在MySQL（主要是InnoDB引擎）中，离散读是一种优化策略。当执行范围查询（例如WHERE column BETWEEN value1 AND value2）或者索引扫描（例如SELECT... FROM table WHERE column LIKE 'prefix%'）时，如果涉及到非连续的物理存储位置的数据读取，就可能会用到离散读。
  • 传统的读取方式是按照索引顺序读取数据页，可能会导致大量的随机I/O，因为数据在磁盘上的存储位置可能是不连续的。离散读会将读取到的索引键值（通过索引扫描得到的符合条件的键值）进行缓存，然后对这些键值进行排序，根据排序后的结果按照顺序去读取数据页。
• 工作过程示例：
  • 假设我们有一个employees表，其中有employee_id（主键）和hire_date列，并且在hire_date列上有索引。现在执行一个查询SELECT * FROM employees WHERE hire_date BETWEEN '2024-01-01' AND '2024-06-30'。
  • 首先，数据库会使用hire_date索引来查找符合条件的employee_id（因为索引存储了键 - 值对，键是hire_date，值是employee_id），这些employee_id在索引中的存储位置可能是不连续的。
  • 然后，离散读机制会将这些不连续的employee_id缓存起来，并进行排序。假设排序后的employee_id序列为10, 12, 15, 17, 20...
  • 最后，按照排序后的employee_id顺序去读取employees表中的数据记录，这样可以减少磁盘I/O的随机访问次数，因为数据在磁盘上按照主键employee_id顺序存储的可能性更高，从而提高了查询性能。

二、索引条件下推ICP（Index Condition Pushdown，ICP）

• 原理：
  • ICP是MySQL 5.6版本引入的一个重要优化特性。在执行查询时，它允许存储引擎在索引遍历过程中，提前使用索引中的列来评估查询条件的一部分，而不是将所有的条件都传递给存储引擎之后再进行评估。
  • 简单来说，以前存储引擎在使用索引查找数据时，只是根据索引列的值来定位数据行，然后将这些数据行返回给服务器层，服务器层再根据其他查询条件（例如WHERE子句中的非索引列条件或者索引列的复杂条件）进行筛选。而有了ICP之后，存储引擎可以在索引查找阶段就进行部分条件筛选。
• 工作过程示例：
  • 假设有一个products表，包含product_id（主键）、category_id（有索引）和price列。执行查询SELECT * FROM products WHERE category_id > 10 AND price < 100。
  • 没有ICP时，存储引擎会根据category_id索引找到所有category_id > 10的行，将这些行返回给服务器层，服务器层再对这些行进行price < 100的条件筛选。
  • 有ICP时，存储引擎在使用category_id索引查找数据的过程中，会同时检查price < 100这个条件（因为price列的值可以从索引或者索引关联的数据行中获取）。只有当category_id > 10且price < 100这两个条件都满足时，数据行才会被返回给服务器层，这样就减少了不必要的数据传输和服务器层的筛选工作，提高了查询效率。

三、回表查询（Back - Table Query）

• 含义：
  • 在数据库索引的使用场景中，回表查询是指当查询语句所需要的列无法通过索引直接全部获取时，需要根据索引中的“指针”（通常是索引叶子节点存储的主键值）回到原表（聚簇表）去获取剩余列数据的查询方式。
• 工作原理及示例：
  • 假设我们有一个用户表users，包含id（主键）、name和age列，并且在name列上创建了索引。当执行查询语句SELECT age, name FROM users WHERE name = 'John'时。
  • 数据库首先会在name索引中查找name = 'John'对应的记录。索引中存储了name列的值以及对应的主键id（在索引叶子节点）。
  • 因为查询还需要age列的值，而索引中没有存储age列，所以数据库会根据索引中找到的id值，再回到原users表中查找对应的age列的值，这个过程就是回表查询。
• 性能影响：
  • 回表查询会增加查询的I/O操作次数。因为需要先访问索引，再访问原表，每一次回表操作都涉及额外的数据读取，当数据量较大或者回表频繁时，会对查询性能产生明显的负面影响。

四、索引覆盖（Using index）

只需要在一棵索引树上就能获取SQL所需的所有列数据无需回表，速度更快，这就叫做索引覆盖
实现索引覆盖最常见的方法就是:将被查询的字段，建立到组合索引。

• 含义：
  • 索引覆盖是指查询语句所需要的所有列数据都可以直接从索引中获取，而无需回表查询原表的情况。
• 工作原理及示例：
  • 还是以users表为例，有id（主键）、name和age列，在name和age列上创建了联合索引。当执行查询语句SELECT age, name FROM users WHERE name = 'John'时。
  • 由于联合索引中已经包含了age和name列的值，数据库可以直接从索引中获取这两个列的数据来满足查询要求，而不需要回到原表进行查询，这就是索引覆盖。
• 性能优势：
  • 索引覆盖能够显著提高查询性能。因为避免了回表查询，减少了I/O操作，使得查询可以更快地返回结果。特别是在高并发或者对查询性能要求较高的场景下，合理设计索引以实现索引覆盖是一种优化查询的有效手段。

EXPLAIN

• type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
  index 索引主要用于加速数据的定位，但对于查询中符合条件的数据行数量（即扫描行数），它不会起到减少的作用
• key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
• key_len列，索引长度。
• rows列，扫描行数。该值是个预估值。
• extra列，详细说明。注意，常见的不太友好的值，如下:Using filesort（会在server层进行排序），Using temporary，Using where

常见的索引:

• where 字段、组合索引(最左前缀)、索引下推(非选择行不加锁)、覆盖索引(不回表)
• on 两边、排序、分组统计

3.2 SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。

3.3 SELECT语句务必指明字段名称

SELECT* 增加很多不必要的消耗(CPU、10、内存、网络带宽)；
减少了使用覆盖索引的可能性；
当表结构发生改变时，前端也需要更新。所以要求直接在select后面接上字段名。

3.4 当只需要一条数据的时候，使用limit 1

limit 是可以停止全表扫描的

// SELECT * from test_users 本身执行效率不高，但是加上了 limit 1 之后，秒出结果
EXPLAIN SELECT * from test_users LIMIT 1;

3.5 排序字段加索引

排序最好是数字类型

EXPLAIN SELECT * FROM test_users ORDER BY `name`;

3.6 如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，会造成该查询不走索引的情况，

改正前：

EXPLAIN SELECT *