【示例】MySQL-不同case下索引的使用分析

前言

本文主要讲述不同SQL语句下，索引的生效情况。

关于索引的前置知识，本文不再讲述。

SQL语句性能分析方法

查看不同类型SQL语句的执行频率

SHOW GLOBAL STATUS LIKE 'COM_______';

---

慢查询日志

该日志记录了SQL执行时间超过指定参数的所有SQL语句。

# 若要开启慢查询日志，需要在.cnf配置文件中设置
slow_query_log = 1# 设置记录时间为2s，执行时间超过2s的SQL语句将会被记录
long_query_time = 2

若是在Linux系统中，慢查询日志的位置：/var/lib/mysql/localhost-slow.log

---

profile变量

通过profile可以让我们知道每条SQL的执行时间都消耗在什么地方

# 查看是否支持profile
SELECT @@have_profiling;# 开启profile
SET profiling = 1;# 执行了一些SQL语句......# 查看profile总体：给出query_id、SQL语句、消耗时间
show profiles;# 查看特定SQL语句的CPU耗时情况
show profile cpu for query query_id;

---

explain

可以获取MySQL如何执行select语句的信息，包括select语句执行过程中表如何连接和连接的顺序。

# 调用方式：直接在常规select语句前边加explain

索引使用 | 常规索引

select * from table_1 where name = xxx;

若name有创建常规索引，走常规索引查询

若name没有创建索引，不走索引查询，耗时更长

假设查询条件中有and条件：

• and前后的列都单独创建有索引：在查询的时候，只会选用一个列的索引进行查询。
• and前后的列创建有联合索引：在查询的时候，走联合索引。

---

上述示例中，当name字段不是聚簇索引，会产生回表查询。

回表查询指：当使用非聚簇索引查询的时候，若索引的列无法满足查询要求时，会在使用非聚簇索引查询到主键的时候，再走一遍聚簇索引查询需要的数据。

索引使用 | 联合索引

最左前缀原则

在使用联合索引的时候，需要满足最左前缀法则。

最左前缀原则是指：在使用联合索引的时候，按照定义联合索引的时候的列前后关系进行分析

• 从最左列开始分析
• 当某一列不在查询条件中，该列及其右边的列的索引将失效
• 特殊情况：若最左列不在查询条件中，则联合索引全部失效

在创建联合索引的时候（假设参与列从左到右依次为：A、B、C），相当于创建了以下这些索引：

• 列A的单列索引
• 列A和列B的联合索引
• 列A和列B和列C的联合索引

所以有了最左前缀原则的出现。

# 假定：profession、age、status三列建立了联合索引# 联合索引全部生效
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';# 联合索引生效2个
explain select * from tb_user where profession = '软件工程' and age = 31;# 联合索引生效1个
explain select * from tb_user where profession = '软件工程';
# 联合索引生效1个
explain select * from tb_user where profession = '软件工程' and status = '0';# 联合索引不生效
explain select * from tb_user where age = 31;# 联合索引全部生效
# 最左匹配是指按照定义联合查询时候列的左右来匹配的，在sql语句中的位置不影响，只要都体现就行
explain select * from tb_user where age = 31 and profession = '软件工程' and status = '0';

范围查询

当联合索引的列中，有的列出现了范围查询。

• 当范围查询中没有等号出现，例如：<、 > ： 范围查询的列右边的列索引失效
• 当范围查询中有等号出现，例如：<=、>= ： 右边的列索引仍然生效

【右边的列】：仍然指定义联合索引时候的左右位置，而不是在SQL中where条件书写的先后位置

# 只有profession、age两列的索引生效
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';# 当范围查询出现等号，后续列索引仍然有效
# 即业务允许的情况下，尽可能使用类似于>=或<=这样的范围查询语句
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';

索引使用 | 覆盖索引

覆盖索引就是：查询过程使用了索引，并写需要返回的结果列，在该索引中都能找到。

使用覆盖索引，就要减少select *的使用。

# 假定profession、age、status创建了联合索引。# 做到了覆盖索引
# 需要返回的结果在索引中都有：联合索引属于二级索引，叶子节点挂的值就是行数据的主键，在该表中，主键就是id
explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0';# 没做到覆盖索引，需要回表查询，即走聚集索引
# 先走联合索引（二级索引）找到数据的主键（id），然后走聚集索引，找到对应的数据。
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

索引使用 | 前缀索引

如果字段类型是字符串，有的时候该列创建的索引将非常长，浪费时间，影响查询效率。这时候就可以用前缀索引，用前n个字符创建索引。

前缀索引只能用于字符串类型的数据。

create index on table_name(column_name(n));

索引使用 | 指定特定的索引

如果一个列既参与了联合索引的创建，也单独创建了索引。在用该列作为条件查询的时候，选用哪一个索引是由MySQL确定的。

但是可以认为指定MySQL选用哪一个索引。

# 建议选用某个索引，MySQL执行的时候仍然可以选择自己认为最优的索引执行
explain select * from table_name [use index(index_name)] where xxxxxx;# 忽略不使用某个索引
explain select * from table_name [ignore index(index_name)] where xxxxxx;# 强制选用某个索引
explain select * from table_name [force index(index_name)] where xxxxxx;

索引使用 | 索引下推

参考文章：https://javaguide.cn/database/mysql/mysql-index.html#%E6%9C%80%E5%B7%A6%E5%89%8D%E7%BC%80%E5%8C%B9%E9%85%8D%E5%8E%9F%E5%88%99

索引下推是MySQL提供的一种索引优化功能，可以减少回表次数，提高查询效率。

简单来说，索引下推的原理就是：将部分服务层负责的事情，交给存储引擎层来处理。