# 为应用选择和创建最佳索引，加速数据读取
#### 什么是MySQL索引？
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
本文结合实际场景示例介绍如何为数据库表创建最佳索引。
#### 注意事项
- 避免隐式转换：确保索引字段类型与查询条件类型一致，避免因类型不同导致的隐式转换，从而影响索引使用效率。
- 索引长度：对于VARCHAR类型的字段，建立索引时指定合适的索引长度，不必对整个字段建立索引，根据实际文本区分度决定索引长度。
- 避免左模糊查询：尽量避免使用左模糊（如LIKE '%hk'）或全模糊查询，这可能导致索引失效，退化为全表扫描。
- 索引数量：单张表的索引数量控制在5个以内，或不超过表字段个数的20%，以避免过多的索引影响写性能。
- 索引使用情况：使用EXPLAIN命令查看SQL执行计划，确保索引被正确使用，避免出现Using File Sort或Using Temporary等影响性能的情况。
- 避免冗余索引：如已存在INDEX(a,b,c)，就不需要再创建INDEX(a,b)。
- 索引创建：对于超大表，根据查询需求有针对性地创建索引，避免不必要的索引。可结合onlineDDL工具创建索引。
- 索引删除：删除不必要的索引前，应充分论证，并备份数据，以防误操作。
 
#### 主要索引类型
- 唯一索引：对于具有唯一特性的字段，建议建立唯一索引。例如，如果表中有字段a和b具有唯一性，可以在这些字段上建立唯一索引。
- 覆盖索引：利用覆盖索引来避免回表操作，提高查询效率。覆盖索引是指查询的所有字段都包含在索引中，可以直接从索引中获取数据。
- 组合索引：创建组合索引时，应将区分度最高的字段放在最左边，以利用B-tree的最左前缀匹配特性。
 
#### 推荐的索引设计方案
创建一个订单表（cbc_order）。
```
CREATE TABLE cbc_order (
`order_id` VARCHAR(64) NOT NULL,
`order_channel` VARCHAR(32) NOT NULL,
`order_time` VARCHAR(32),
`pay_amount` DOUBLE,
`real_pay` DOUBLE,
`pay_time` VARCHAR(32),
`user_id` VARCHAR(32),
`user_name` VARCHAR(32),
`area_id` VARCHAR(32)
) ENGINE = InnoDB 
DEFAULT CHARACTER SET = utf8mb4;
```
以订单表（cbc_order）为例，提供以下索引设计方案：
- 主键设计 建议添加自增ID作为主键。
  ```
  ALTER TABLE cbc_order ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;
  ```
  遵循规范：每个InnoDB表强烈建议有一个主键，最好选择值的顺序是连续增长的列作为主键。
  
- 联合索引设计 基于查询场景创建以下索引。
  - INDEX idx_user_order (user_id, order_time)：用于用户订单查询。
  
  - INDEX idx_channel_time (order_channel, order_time)：用于渠道统计。
  
  - INDEX idx_area_time (area_id, order_time)：用于地区分析。
   
- 索引长度优化 对于VARCHAR字段，指定合适的索引长度。
  INDEX idx_user_name (user_name(20))：字符串索引长度为20即可达到90%以上区分度。
  
 
#### 索引有效性验证方法
以订单表（cbc_order）为例，提供以下索引验证方法：
- 使用**EXPLAIN** 分析查询计划。
  ```
  EXPLAIN SELECT * FROM cbc_order WHERE user_id = '123' AND order_time > '2024-01-01';
  ```
  检查结果中的key字段是否显示使用了您创建的索引。
  
- 性能对比测试。
  1. 记录添加索引前的查询耗时。
  
  2. 添加索引后再次执行相同查询。
  
  3. 对比查询时间改善情况。
   
- 监控索引使用情况。 通过MySQL的**SHOW INDEX**命令查看索引统计信息。
  ```
  SHOW INDEX FROM cbc_order;
  ```