Mysql之索引优化

指定索引

当一个字段上既有单列索引，又有复合索引时，我们可以通过以下的SQL提示来要求该SQL语句执行时采用哪个索引：

• use index(索引名称)：建议使用该索引，只是建议，底层mysql会根据实际效率来考虑是否使用你推荐的索引。

• ignore index(索引名称)：忽略该索引

• force index(索引名称)：强行使用该索引

应用场景：

• 当数据库执行计划没有自动选择最优的索引时，开发者可以手动指定使用特定的索引。尤其是在有多个索引的情况下，MySQL 可能选择了一个并不理想的索引，而通过指定索引可以确保查询执行的效率。

/*
 指定索引
 use index（索引的名称）： 建议使用该索引
 ignore index（索引名称） ：某个索引
 force index（索引名称） ：强行使用某个索引
 */
show index from t_customer;
create index idx_t_customer_name on t_customer(name);
explain select *from t_customer where name= 'zhangsan' ; #优先使用复合索引
# 如何建议使用单列索引idx_name：
explain select *from t_customer use index(idx_t_customer_name) where name= 'zhangsan' ; #建议使用单列索引

#如何忽略使用复合索引 idx_name_age_gender：
explain select * from t_customer ignore index(idx_name_age_gender) where name='zhangsan';

#如何强行使用单列索引idx_name：
explain select * from t_customer force index(idx_t_customer_name) where name='zhangsan';

覆盖索引

覆盖索引强调的是：在select后面写字段的时候，这些字段尽可能是索引所覆盖的字段，这样可以避免回表查询。尽可能避免使用 select *，因为select * 很容易导致回表查询。（本质就是：能在索引上检索的，就不要再次回表查询了。）

覆盖索引是指在一次查询中，所有需要查询的列都能从索引中获取，不需要再访问表中的实际数据行。也就是说，数据库只需要从索引中读取数据即可返回结果，不再需要回表（访问表中的实际数据），从而大大提高了查询效率。

应用场景：

• 查询所需的列全部包含在索引中，不需要访问数据表的记录，这种查询能够通过索引直接返回结果，从而大幅提升性能。

/*
 覆盖索引:
 覆盖索引的核心概念就是通过查询所需的所有列都位于索引中，避免回表操作，从而提高查询效率。
 */

CREATE INDEX idx_emp_name_sal ON t_emp(name, sal);
/*
 MySQL 可以直接从 idx_emp_name_sal 这个索引中返回结果，而不需要访问表中的数据行，
 因为 name 和 sal 都包含在索引中。这就是覆盖索引。
 */
 show index from t_emp;
EXPLAIN SELECT name, sal FROM t_emp WHERE name = '张三'; # 使用覆盖索引
EXPLAIN SELECT name, sal, age FROM t_emp WHERE name = '张三'; # 不使用索引：
/*
此时，MySQL 会在索引中找到 name 和 sal，然后还需要访问数据表获取 age 列的值，这被称为 回表 操作
 */
DROP INDEX idx_emp_name_sal ON t_emp;

 

extra为null表示回表了 ！！！

面试题：

t_user表字段如下：id,name,password,realname,birth,email。表中数据量500万条，请针对以下SQL语句给出优化方案：

select id,name,realname from t_user where name='鲁智深';

如果只给name添加索引，底层会进行大量的回表查询，效率较低，建议给name和realname两个字段添加联合索引，这样大大减少回表操作，提高查询效率。

前缀索引

 前缀索引（Prefix Index）是指对字符型列的前部分字符进行索引，而不是对整列创建索引。这种索引可以减少索引的大小，节省空间，同时还能提高查询的效率，特别是在需要对长文本或字符串列进行索引时。前缀索引广泛应用于需要索引长字符串列的场景，例如 VARCHAR 或 TEXT 类型的列。

使用前缀索引的场景

前缀索引适用于以下情况：

• 列的数据较长，并且前几个字符就具有较强的区分度。
• 不需要精确匹配整个字段，而只关心字段的前部分字符的匹配情况。

 前缀索引的语法

CREATE INDEX 索引名 ON 表名(列名(前缀长度));

示例

假设有一张 users 表，包含用户的电子邮件地址（email 列），由于电子邮件可能非常长，我们可以通过前 10 个字符创建前缀索引：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    email VARCHAR(255)
);

-- 创建前缀索引，只对 email 列的前 10 个字符建立索引
CREATE INDEX idx_email ON users(email(10));

此时，MySQL 只对 email 列的前 10 个字符进行索引，而不是对整个 email 列进行索引。当我们执行以下查询时：

SELECT * FROM users WHERE email LIKE 'abc@example%';

MySQL 会利用前 10 个字符的前缀索引来优9查询。这个查询中的 email LIKE 'abc@example%' 会匹配 email 列前 10 个字符中的内容。

如何选择合适的前缀长度？ 

使用前缀索引时，需要通过以下公式来确定使用前几个字符作为索引：

select count(distinct substring(ename,1,前几个字符)) / count(*) from emp4;

以上查询结果越接近1，表示索引的效果越好。（原理：做索引值的话，索引值越具有唯一性效率越高）

/*
 前缀索引
截取前几个字符作为前缀索引呢?使用以下公式
select count(distinct substring(ename,1,前几个字符))/count(*)from emp
这个查询结果越接近1，越具有唯一性
 */
select count(distinct substring(name,1,2))/count(*)from t_emp

总结

• 指定索引 适合开发者手动介入优化查询。
• 覆盖索引 能够在频繁查询特定列时，避免回表，提升查询效率，是性能优化的重要手段。
• 前缀索引 适用于长文本字段，能在保证索引效率的同时，减少存储开销。

 

单列索引和复合索引怎么选择：

当查询语句的条件中有多个条件，建议将这几个列创建为复合索引，因为创建单列索引很容易回表查询。

1. 单列索引（Single-column Index）

单列索引是对表中的单个列创建的索引。每个索引只针对一个列进行加速。

使用单列索引的场景：

• 查询条件只涉及单列：

  • 当查询条件只包含一个列时，单列索引是最有效的选择。此时创建复合索引没有意义。
• 多个单列索引用于不同的查询条件：
  • 如果表中的列经常被单独查询，不会经常联合查询，那么为每个列分别创建单列索引更为合适。
• 频繁更新的表：
  • 在一个数据频繁更新的表中，单列索引相对复合索引更新代价较低。如果查询性能可以通过单列索引满足，那么单列索引通常会对性能更加友好。

2. 复合索引（Composite Index）

复合索引（也称为联合索引）是对多个列组合创建的索引。通过复合索引，查询引擎可以在多个列上同时加速查询。

使用复合索引的场景：

• 查询条件涉及多个列：

  • 如果查询条件经常包含多个列，并且这些列频繁联合出现，那么复合索引是最佳选择。复合索引能够同时加速多个列的查询。
• 遵循最左前缀原则：
  • 在复合索引中，查询条件需要从索引的最左边开始使用。如果查询只包含最左边的列，复合索引仍然有效，但如果跳过了左边的列，索引将失效。 主字段并且具有很强唯一性的字段建议排在第一位。
• 范围查询需要放在复合索引的最后：
  • 如果查询条件中包含范围查询（如 >, <, BETWEEN），通常将范围列放在复合索引的最后，因为范围查询会限制复合索引的使用范围。否则会导致索引失效。
  • 例如，如果你有一个查询同时涉及 name 和 age，并且 age 是一个范围查询：

    SELECT * FROM t_emp WHERE name = '张三' AND age > 20;
    

索引创建原则

1. 表数据量庞大，通常超过百万条数据

• 解释：当表的数据量非常庞大（如超过百万条记录）时，查询性能会显著下降。没有索引的情况下，数据库必须进行全表扫描，这会导致查询速度非常慢。为了解决这一问题，创建适当的索引非常关键。索引可以极大地减少扫描的行数，从而提高查询性能。

2. 经常出现在 WHERE、ORDER BY、GROUP BY 后面的字段建议添加索引

• 解释：WHERE、ORDER BY 和 GROUP BY 通常是 SQL 查询中最耗时的部分，因为它们会对表中的大量数据进行筛选、排序或分组。为这些字段添加索引可以加速数据的查找、排序和分组操作。
• 建议：如果某个字段经常出现在 WHERE、ORDER BY 或 GROUP BY 中，应该为该字段添加索引，以提高查询性能。

3. 创建索引的字段尽量具有很强的唯一性

• 解释：索引的效率取决于字段的选择性（区分度）。如果一个字段的值是高度唯一的（如主键或身份证号），索引能够快速缩小查询范围，查找性能更高。而对低选择性的字段（如性别，只有 "男" 和 "女" 两个值）创建索引，作用不大，因为即使使用索引，数据库仍然需要扫描大量数据。
• 建议：优先为具有高唯一性、选择性强的字段创建索引，例如用户 ID、订单号等。

4. 如果字段存储文本，内容较大，一定要创建前缀索引

• 解释：对于长文本字段（如 VARCHAR 或 TEXT），如果直接对整个字段创建索引，会消耗大量的存储空间并降低索引的效率。因此，可以创建前缀索引，即只索引文本字段的前 N 个字符。这样既节省空间，又能够保持较高的索引效率。
• 建议：对于存储较长文本的字段（如邮箱、URL、文章标题等），建议使用前缀索引。前缀长度应根据数据分布和区分度合理选择。

5. 尽量使用复合索引，使用单列索引容易回表查询

• 解释：复合索引是对多个列组合起来创建的索引，可以在涉及多个列的查询中显著提高性能。当查询涉及多个条件时，使用复合索引可以避免回表操作（从索引查找到数据后，还需要访问数据表本身）。单列索引只能加速单一条件的查询，对于多条件查询，数据库可能需要多次访问数据表，这会降低查询性能。
• 建议：对于多列查询，优先考虑复合索引，以提高查询效率和减少回表操作。

6. 如果一个字段中的数据不会为 NULL，建议建表时添加 NOT NULL 约束，这样优化器就知道使用哪个索引列更加有效

• 解释：如果某个字段的数据永远不会为 NULL，在表设计时应该明确设置 NOT NULL 约束。这不仅能够保持数据的完整性，还可以帮助查询优化器更好地使用索引。因为当字段允许 NULL 值时，索引结构中可能需要额外的逻辑来处理 NULL 值，影响性能。
• 例如

  CREATE TABLE users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(255),
      email VARCHAR(255) NOT NULL,
      phone VARCHAR(15) NOT NULL
  );
  

  • 此时，数据库优化器不再需要考虑 NULL 值的情况，因为这些列不能存储 NULL 值。因此，在使用索引时，优化器会更高效地利用这些列的索引。 

7. 不要创建太多索引，当对数据进行增删改的时候，索引需要重新排序

• 解释：虽然索引可以提升查询性能，但它们会增加增删改操作的开销。当对表进行增删改时，索引必须更新，这会导致性能下降。特别是如果表上有大量的索引，每次修改数据时都需要更新这些索引，可能会导致性能问题。

8. 如果很少的查询，经常的增删改不建议加索引

• 解释：如果表的数据主要是用来频繁增删改，而查询操作很少，那么创建索引的收益可能不大，甚至会影响性能。因为索引的主要作用是加速查询，而在增删改操作中，每次修改数据都需要更新索引，这会增加额外的开销。
• 建议：在以频繁写操作为主的表中（如日志表、事务表），如果查询操作很少，不建议添加索引。除非某些查询需要优化，否则应优先考虑写入性能。