索引语法

  1. 创建索引 

    CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name, ...);
    • UNIQUE：创建唯一索引，保证索引列中的值唯一。
    • FULLTEXT：创建全文索引，用于高效文本搜索。
    • 单列索引：对单个字段创建索引，例如CREATE INDEX idx_name ON table_name(column_name);。
    • 联合索引：对多个字段组合创建索引，例如CREATE INDEX idx_name ON table_name(column1, column2);，可以提高多条件查询效率。

  2. 查看索引 

    SHOW INDEX FROM table_name;
    • 显示指定表的所有索引信息，包括索引名称、字段等。

  3. 删除索引 

    DROP INDEX index_name ON table_name;
    • 删除指定表上的索引，释放空间。

SQL执行频率

  • 查询数据库操作频率：使用以下命令可以查看数据库中不同操作的执行频率，例如INSERT、UPDATE、DELETE、SELECT等操作的统计信息。 
    SHOW GLOBAL STATUS LIKE 'Com______';
    • session：显示当前会话的状态信息。
    • global：显示全局状态信息。

慢查询日志

  • 慢查询日志记录了所有执行时间超出指定参数（long_query_time，单位：秒，默认为10秒）的SQL语句的日志。

    • 查看慢查询日志是否开启： 
      SHOW VARIABLES LIKE 'slow_query_log';

    • 配置文件（通常为/etc/my.cnf）中可开启慢查询日志并设定时间阈值：

      # 开启MySQL慢日志查询开关
slow_query_log=1

# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

Profile详情

  • Profile：SHOW PROFILES命令在SQL优化时能够帮助我们了解每个查询的时间消耗情况。
    • 查询是否支持profiling功能： 
      SELECT @@have_profiling;
    • 设置profiling的级别：
      • Session级别：仅对当前会话生效，断开连接后失效。 
        SET profiling = 1;
      • Global级别：对所有新建会话生效，已存在的会话不受影响。 
        SET GLOBAL profiling = 1;
    • 查看profiling状态：
      • 当前会话的profiling状态： 
        SHOW VARIABLES LIKE 'profiling';
      • 全局profiling状态： 
        SHOW GLOBAL VARIABLES LIKE 'profiling';
    • 查看每条SQL的耗时基本情况： 
      SHOW PROFILES;
    • 查看指定query_id的SQL语句各个阶段的耗时情况： 
      SHOW PROFILE FOR QUERY query_id;
    • 查看指定query_id的SQL语句CPU的使用情况： 
      SHOW PROFILE CPU FOR QUERY query_id;

EXPLAIN执行计划

  • EXPLAIN或DESC命令用于获取MySQL如何执行SELECT语句的信息，包括执行过程中表的连接顺序和方式。
    • 语法： 
      EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
    • 各字段含义：
      • id：SELECT查询的序列号，表示查询中执行顺序，id值越大越先执行。
      • select_type：表示SELECT的类型，常见取值包括SIMPLE（简单查询）、PRIMARY（主查询）、UNION（联合查询）、SUBQUERY（子查询）等。
      • type：表示连接类型，性能优劣由好到差为NULL、system、const、eq_ref、ref、range、index、all。
      • possible_keys：显示可能应用在这张表上的索引，一个或多个。
      • key：实际使用的索引，如果为NULL，则没有使用索引。
      • key_len：表示索引中使用的字节数，该值表示字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
      • rows：MySQL认为必须要执行并读取的行数，这个值是一个估计值，可能不总是准确的。
      • filtered：表示返回结果的行数占需读取行数的百分比，filtered值越大越好。

SQL提示

SQL提示是优化数据库的一个重要手段，简而言之，是在SQL语句中人为地加入提示来达到优化的目的。

  • use index：提示MySQL使用指定索引。 
    EXPLAIN SELECT * FROM tb_user USE INDEX(idx_user_pro) WHERE profession = '软件工程';
  • ignore index：提示MySQL忽略指定索引。 
    EXPLAIN SELECT * FROM tb_user IGNORE INDEX(idx_user_pro) WHERE profession = '软件工程';
  • force index：提示MySQL强制使用指定索引。 
    EXPLAIN SELECT * FROM tb_user FORCE INDEX(idx_user_pro) WHERE profession = '软件工程';

前缀索引

  • 当字段类型为字符串（如VARCHAR、TEXT等）时，如果索引需要的字符串很长，索引会变得很大，影响查询效率。此时可以只将字符串的前部分作为前缀建立索引，以节约空间并提高效率。
    • 语法示例： 
      CREATE INDEX idx_xxxx ON table_name(column(n));
    • 其中n表示前缀长度，可以根据实际需求设定适当的值。

覆盖索引与回表

  • 覆盖索引：查询中所有需要的字段都包含在索引中，不需要回表即可获取所有数据，从而提高查询效率。此时EXPLAIN输出的Extra字段中会显示Using index。
  • 回表：当查询的字段不完全包含在索引中时，MySQL需要通过索引定位到数据行，然后返回表中读取其他字段的数据。这种操作称为回表，增加了查询的开销。

最左前缀法则

  • 最左前缀法则指的是在多列联合索引中，查询时要遵循索引从左到右的顺序，否则索引可能失效。例如，对于(a, b, c)的联合索引：
    • WHERE a = 1、WHERE a = 1 AND b = 2、WHERE a = 1 AND b = 2 AND c = 3可以使用索引。
    • 但WHERE b = 2、WHERE c = 3无法使用索引。

索引失效的情况

  1. 不满足最左前缀法则：如在联合索引中没有从最左侧开始使用。
  2. 对索引字段使用函数：如WHERE LEFT(name, 3) = 'abc'。
  3. 对索引字段进行计算：如WHERE age + 1 = 21。
  4. 使用!=或<>：如WHERE age != 10。
  5. 使用IS NULL或IS NOT NULL：有时会导致索引失效。
  6. 使用LIKE时以通配符%开头：如WHERE name LIKE '%abc'。
  7. 数据类型隐式转换：如WHERE varchar_field = 123（数字被隐式转换为字符串）。
  8. 范围条件后再使用索引列：如WHERE a = 1 AND b > 2 AND c = 3，c列无法使用索引。