【MySQL】高性能的索引

【MySQL】高性能的索引

---

1、B-Tree

B-Tree索引适用于全键值、键值范围或者键前缀查找（最左前缀）。

1. 全值匹配
2. 匹配最左前缀
3. 匹配列前缀
4. 匹配范围值
5. 精准匹配某一列并范围匹配另外一列
6. 只访问索引的查询

2、B树索引优点：

1. 数值临近的索引，在存储空间上临近，可以为 order by 、 group by 提供优化手段
2. 索引中备份了构成给索引的字段值，如果查询请求只需要这些字段，可以避免回表查询整行数据

3、索引评价三星指标：

1. 是否将相关的行临近存放
2. 存放顺序是否和查询顺序一致
3. 是否包含查询需要的列

4、联合索引存储结构

联合索引存储结构

5、高性能索引策略

1、独立列，指索引列不能是表达式的一部分，也不能是函数参数

2、前缀索引和索引选择性

MySQL不允许索引BLOB,TEXT,或者很长的VARCHAR类型的列完整长度。所以对这种列只能使用前缀索引，选择足够的长度以保证较高的选择性。

---前缀索引
ALTER TABLE TABLE_NAME ADD KEY (列(长度));
--- 例如
ALTER TABLE  A1 ADD KEY (colum1(7))

3、多列索引

如果 EXPLAIN某语句使用了index_merge，需要检查索引建的是否合理。

mysql> EXPLAIN SELECT film_id, actor_id FROM film_actor
    -> WHERE actor_id = 1 OR film_id = 1\G
*************************** 1. row **************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY,idx_fk_film_id
      key_len: 2,2
          ref: NULL
         rows: 29
        Extra: Using union(PRIMARY,idx_fk_film_id); Using where

4、选择合适的索引顺序

选择索引列顺序的经验法则：将选择性最高的列放到索引最前列。（适应大部分情况）。

6、聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

数据在InnoDB中存储示例图：

聚簇索引示意图：

二级索引（普通索引）示例图

聚簇索引与非聚簇索引对比图：

在InnoDB数据库，避免使用类似UUID这种无序数据作为主键。

使用UUID主键大量插入数据后，用 ANALYZE TABLE 重建索引的统计数值。