面试题：MySQL MyISAM索引创建优化之基础

Question

在MySQL的MyISAM存储引擎中，简述创建索引的基本语法，并说明如何通过分析查询语句来初步优化索引创建？

Answer 1

创建索引基本语法

1. 创建普通索引

CREATE INDEX index_name ON table_name (column_name);

2. 创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_name);

3. 创建复合索引

CREATE INDEX index_name ON table_name (column1, column2, ...);

4. 在创建表时创建索引

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    INDEX index_name (column1),
    UNIQUE INDEX unique_index_name (column2)
);

通过分析查询语句初步优化索引创建

1. 确定查询的频繁字段
   • 分析哪些字段经常在WHERE子句、JOIN子句中被使用。例如，在查询SELECT * FROM users WHERE age > 18 AND city = 'Beijing';中，age和city字段频繁出现在WHERE子句，可考虑为这些字段创建索引。
2. 前缀索引的使用
   • 对于长字符串字段，可使用前缀索引以减少索引存储空间。例如，对于一个很长的text类型的description字段，若查询经常以该字段开头匹配，可创建前缀索引：CREATE INDEX idx_description ON table_name (description(10)); 这里10表示使用前10个字符创建索引。
3. 避免冗余和重复索引
   • 冗余索引指的是一个索引能完全被另一个索引替代。例如，如果已经有了复合索引CREATE INDEX idx_abc ON table_name (a, b, c);，那么单独为a创建索引CREATE INDEX idx_a ON table_name (a);就是冗余的，因为复合索引已经包含了a字段的索引信息。重复索引指的是完全相同的索引，应避免创建。
4. 覆盖索引
   • 当查询中所有需要的列都包含在索引中时，称为覆盖索引。例如，查询SELECT age, city FROM users WHERE age > 18;，如果创建索引CREATE INDEX idx_age_city ON users (age, city);，MySQL可以直接通过索引获取所需数据，而不需要回表操作，从而提高查询性能。