索引语法

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列无法使用索引。