性能优化-MySQL索引

1. 为什么要使用索引？

使用索引是为了提高数据检索的效率。当数据量很大时，如果没有索引，数据库系统需要逐条扫描数据来找到符合条件的记录，这样会消耗大量的时间和资源。而使用索引可以通过创建特定的数据结构，将数据按照某种规则有序地组织起来，从而加快数据的查找速度。

索引可以在数据库表的一列或多列上创建，它们包含了对应列值的引用和指针，使得数据库系统可以快速定位到需要的数据。通过使用索引，数据库系统可以根据索引的排序和搜索算法，快速定位到符合查询条件的数据，提高查询的效率。

比如：如图所示，当基础数据量为60万时，相同的SQL执行，左边没加索引，右边添加了索引，效率差了10倍有余

• 优点：

  • 提高数据查询的效率，降低数据库的IO成本。

  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。

• 缺点：

  • 索引会占用存储空间。

  • 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

2. MySQL索引-结构

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

注意：我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

2.1 二叉树

二叉查找树：左边的子节点比父节点小，右边的子节点比父节点大

当我们向二叉查找树保存数据时，是按照从大到小(或从小到大)的顺序保存的，此时就会形成一个单向链表，搜索性能会打折扣。

可以选择平衡二叉树或者是红黑树来解决上述问题。（红黑树也是一棵平衡的二叉树）

2.2 平衡二叉树

但是在Mysql数据库中并没有使用二叉搜索数或二叉平衡数或红黑树来作为索引的结构。

2.3 B+Tree(多路平衡搜索树)结构中也可以避免上述问题

B+Tree结构：

• 每一个节点，可以存储多个key（有n个key，就有n个指针）

• 节点分为：叶子节点、非叶子节点

  • 叶子节点，就是最后一层子节点，所有的数据都存储在叶子节点上

  • 非叶子节点，不是树结构最下面的节点，用于索引数据，存储的的是：key+指针

• 为了提高范围查询效率，叶子节点形成了一个双向链表，便于数据的排序及区间范围查询

为什么InnoDB存储引擎选择使用B+tree索引结构?

• A. 相对于二叉树，层级更少，搜索效率高；
• B. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
• C. 相对Hash索引，B+tree支持范围匹配及排序操作；

面试：

在MySQL中索引使用的数据结构是B+Tree，B+树是基于B树的变种，它具有B树的平衡性，而且树的高度更低

• B+树非叶子节点不存数据只存索引的标识和指针，因此其内部节点相对B树更小，树的高度更低，查询产生的I/O更少

• B+树查询效率更高，B+树使用双向链表串连所有叶子节点，区间查询效率更高

• B+树查询效率更稳定，B+树每次都必须查询到叶子节点才能找到数据，而B树查询的数据可能不在叶子节点，也可能在，这样就会造成查询的效率的不稳定

3. MySQL索引-语法

作为查询的字段才可以加索引

3.1 创建索引

create [ unique ] index 索引名 on 表名 (字段名,... ) ;

3.2 查看索引

show index from 表名;

3.3 删除索引

drop index 索引名 on 表名;

注意事项：

• 主键字段，在建表时，会自动创建主键索引

• 添加唯一约束时，数据库实际上会添加唯一索引

4. MySQL索引-分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引。

• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引和二级索引的具体结构如下：

• 聚集索引的叶子节点下挂的是这一行的数据 。

• 二级索引的叶子节点下挂的是该字段值对应的主键值

比如：当我们执行如下的SQL语句时，具体的查找过程如下

①. 由于是根据name字段进行查询，所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。

③. 最终拿到这一行的数据，直接返回即可。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询

举例：

以下两条SQL语句，那个执行效率高? 为什么?

(备注: id为主键，name字段创建的有索引)

• A. select * from user where id = 10 ;

• B. select * from user where name = 'Arm' ;

解答：

A 语句的执行性能要高于B 语句。

因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。

5. 索引失效检查

可以采用EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息：

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ; 示例： explain SELECT DATE_FORMAT(alarm_time, '%H:00') AS date_time, ROUND(AVG(data_value),0) AS data_value FROM device_data WHERE iot_id = 'esQFNsPbbmbcahxEuroCj0rk00' AND function_id = 'HeartRate' AND alarm_time BETWEEN '2024-01-20 00:00' and '2024-01-20 23:59' GROUP BY DATE_FORMAT(alarm_time, '%H:00') order by alarm_time

索引检查详解：

• possible_key当前sql可能会使用到的索引

• key当前sql实际命中的索引；

  • 如果为NULL，则没有使用索引。

• type这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all

  • system：查询系统中的表

  • const：根据主键查询

  • eq_ref：主键索引查询或唯一索引查询

  • ref：索引查询

  • range：范围查询

  • index：索引树扫描

  • all：全盘扫描

• key_len索引占用的大小；

  • 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。

• Extra额外的优化建议

• id：select查询的序列号；

  • 表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。

• select_type： 表示 SELECT 的类型，常见的取值有：

  • SIMPLE（简单表，即不使用表连接或者子查询）；

  • PRIMARY（主查询，即外层的查询）；

  • UNION（UNION 中的第二个或者后面的查询语句）；

  • SUBQUERY（SELECT/WHERE之后包含了子查询）等

• rows： MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

• filtered： 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

6. 索引失效的情况说明

6.1 最左前缀法则

最左前缀法则指的是复合索引（联合索引）使用时的规则：查询条件必须从索引的最左列开始，并且不能跳过中间的列，否则索引会失效

示例：

-- 创建测试表 CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50), age INT, city VARCHAR(50), phone VARCHAR(20) ); -- 创建复合索引（name, age, city） CREATE INDEX idx_name_age_city ON user(name, age, city);

a. 完全匹配（使用全部3列）

-- 使用索引（3列都用上了） EXPLAIN SELECT * FROM user WHERE name = '张三' AND age = 25 AND city = '北京'; -- 结果：key=idx_name_age_city，type=ref

b. 使用最左2列

-- 使用索引（用到 name 和 age） EXPLAIN SELECT * FROM user WHERE name = '张三' AND age = 25;

c. 只使用最左1列

-- 使用索引（只用到 name） EXPLAIN SELECT * FROM user WHERE name = '张三';

d. 跳过最左列（完全失效）

-- 不使用索引（缺少 name） EXPLAIN SELECT * FROM user WHERE age = 25 AND city = '北京'; -- 结果： key=null，全表扫描

e. 跳过中间列（部分失效）

-- 只使用 name，age 被跳过导致 city 失效 EXPLAIN SELECT * FROM user WHERE name = '张三' AND city = '北京'; -- name 可以使用索引 -- 跳过了 age，导致 city 无法使用索引 -- 实际只用到索引的第1列

f. 顺序打乱但包含全部列

-- 优化器会自动调整顺序，仍使用索引 EXPLAIN SELECT * FROM user WHERE age = 25 AND name = '张三' AND city = '北京';

g. 范围查询的影响

-- 范围查询后的列会失效 EXPLAIN SELECT * FROM user WHERE name = '张三' AND age > 25 AND city = '北京'; -- name 使用索引 -- age > 25 使用索引（范围查找） -- city 失效（age用了范围查询

6.2 索引列进行运算

CREATE INDEX idx_phone ON user(phone); -- 失效 SELECT * FROM user WHERE phone + 1 = 13800000000; -- 正确写法 SELECT * FROM user WHERE phone = 13800000000 - 1;

6.3 字符串不加引号

CREATE INDEX idx_name ON user(name); -- 失效（隐式类型转换） SELECT * FROM user WHERE name = 123; -- 正确 SELECT * FROM user WHERE name = '123';

6.4 模糊查询

CREATE INDEX idx_name ON user(name); -- 前缀模糊可用索引 SELECT * FROM user WHERE name LIKE '张%'; -- 中缀/后缀模糊失效 SELECT * FROM user WHERE name LIKE '%三%'; SELECT * FROM user WHERE name LIKE '%三';

6.5 or 连接条件

CREATE INDEX idx_name ON user(name); CREATE INDEX idx_age ON user(age); -- 两侧都有索引 SELECT * FROM user WHERE name = '张三' OR age = 25; -- 一侧无索引（全表扫描） -- 假设 phone 无索引 SELECT * FROM user WHERE name = '张三' OR phone = '123456';

6.6 数据分布影响

-- 如果表中 90% 的人 age=25，MySQL 可能放弃索引 SELECT * FROM user WHERE age = 25;

说明：优化器判断全表扫描更快时，会不走索引（如查询结果占比过高）。

7. 覆盖索引

覆盖索引是指查询语句使用到了索引，并且查询的所有内容都在索引中，不需要进行回表查询