> For the complete documentation index, see [llms.txt](https://mysql.gitbook.teaho.net/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mysql.gitbook.teaho.net/untitled-3.md).

# 索引优化

索引是查询性能优化最有效的手段，最优的索引有时比一个好的索引性能要好两个数量级。

{% hint style="info" %}
索引是在存储引擎层而不是服务器层实现的。不同存储引擎的索引的实现细节、工作方式并不一定一样，存储引擎也是选择性支持不同种类的索引的。
{% endhint %}

索引语法：

```

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
```

## 索引的类型

### B-Tree索引

当说到MySQL的索引，如无特殊说明，那很可能都是说B-Tree索引。不同存储引擎实现的可能有些出入。

比如，InnoDB是使用B+Tree。

通过简单的比较算法的特性来说明下，InnoDB选择B+Tree的可能原因。

首先，选择平衡树是因为红黑树、B-Tree高度增长都是

$$
O(lgN)
$$

最坏的情况也是差不多的。且因为是顺序的，所以对查找范围数据是很方便的。

而选择B-Tree是因为B-Tree适合文件系统，要检查的节点数相对少，减少了磁盘IO。

而选择的B-Tree的变种B+Tree是因为B+树只有叶子节点才存储data或data的指针，这样一个节点可存储更多的key，用《算法导论》的话说，就是最大化了内部结点的分支因子。

具体算法实现可参考相关算法书籍，不赘述。（BTW其实具体细节我也忘得差不多 =。=）

对索引的有效查询：

* 全值匹配
* 索引第一列前缀
* 匹配范围值
* 只访问索引（也称覆盖索引）

限制：

* 如果不是按照索引的最左列开始查找，则无法使用索引。
* 不能跳过索引中的列。
* 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

索引列的顺序是很重要的。

### 哈希索引

优点：了解哈希算法的都知道，选用合适的哈希算法，其查找速度是十分快的。

但缺点（限制）是较多的：

* 哈希索引没法用于排序（order by）。
* 哈希索引因为只存储哈希值和行指针不能用于覆盖索引。
* 哈希索引不支持部分匹配查找。
* 不支持范围查找。
* 哈希如果冲突了，索引维护成本是很高的。（有时候发生冲突的几率可能比你想象的大的多多的多，请了解：**生日悖论**）

这些限制决定了哈希索引能应用的场合较为有限。

举个应用场景：比如，网络URL的存储，可能URL能达到数十数百个字符，直接将字段用where句法比较显然效率较低，但是选用合适的哈希算法，计算URL的哈希值，并冗余多一个字段存储哈希值，那么比较时，直接用哈希值字段比较效率会更高。

**一般常用就这两个索引，**书中还聊到一个评价索引的三星系统：

> 索引将相关的记录放到一起则获得一星；如果索引中的数据顺序和查找中的排列顺序一致则获得二星；如果索引中的列包含了查询中需要的全部列则获得三星。

书中还聊到，索引值得写一本书，并推荐了一本书Tapio Lahdenmaki和Mike Leach的*Relational Database Index Design and the Optimizers(Wiley出版社)。*

{% hint style="info" %}
索引并不总是最好解决方案，这在特大型表中，索引的作用就随之失去。
{% endhint %}

## 索引策略

### 独立的列

需注意要养成索引列单独写一侧，比如： select \* from bills where amount+1=5，就算amount存在单列索引，也是**不起作用**的。

### 前缀索引和索引选择性

索引选择性可描述如下：不重复（distinct）的索引值数n与记录总数N的比值为P，P的取值为

$$
n/N \leq P  \leq 1
$$

当P越小，选择性越低；反之，P越大，选择性越高。

前缀索引，当某列过长，使用完整列作索引可能效率不是最优，那么可使用前缀索引如下：

```
ALTER TABLE table1 ADD KEY (field(7))
```

通常，前缀的选择性能接近**0.031**就可用了。

前缀索引有如下缺点：无法作用于order by、group by，无法作覆盖扫描。

### 多列索引

当一个查询的where句式使用了多个筛选条件，假如，都为这些筛选列建立单列索引，那么，这些索引充其量顶多是一个“一星索引”，而“一星索引”有时比真正最优索引可能差几个数量级。当然，‘三星索引’未必总能如愿，但考虑改成二星索引总比一星好的。

在查询中，假如同时使用了两个单列索引进行扫描，MySQL会进行结果合并。可用EXPLAIN查看，有时候，会在Extra列看到 Using union(PRIMARY,  index\_name);Using Where 。这写索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕。

### 选择合适的索引列顺序

列的顺序是很重要的。在“三星索引”系统中，顺序决定了是否满足“二星”以上索引。

那么如何设置列的顺序呢？介绍一些方法：

1. 经验法则：当不考虑排序和分组时，将**选择性**最高列放在前面最好。
2. 慢查询优化：从诸如pt-query-digest这样的工具的报告中提取“最差”查询，那么再按上述1方法选定改良索引的顺序往往很高效。

### 聚簇索引

文档15.8.2.1节对MySQL应用聚簇索引的方式作了定义和描述：

> Every `InnoDB` table has a special index called the [clustered index](https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_clustered_index) where the data for the rows is stored. Typically, the clustered index is synonymous with the [primary key](https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_primary_key). To get the best performance from queries, inserts, and other database operations, you must understand how `InnoDB` uses the clustered index to optimize the most common lookup and DML operations for each table.
>
> * When you define a `PRIMARY KEY` on your table, `InnoDB` uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new [auto-increment](https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_auto_increment) column, whose values are filled in automatically.
> * If you do not define a `PRIMARY KEY` for your table, MySQL locates the first `UNIQUE` index where all the key columns are `NOT NULL` and `InnoDB` uses it as the clustered index.
> * If the table has no `PRIMARY KEY` or suitable `UNIQUE` index, `InnoDB` internally generates a hidden clustered index named `GEN_CLUST_INDEX` on a synthetic column containing row ID values. The rows are ordered by the ID that `InnoDB` assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

聚簇索引结构就是，在索引的叶子存放数据行。

优点：

数据访问更快。

缺点：

更新聚簇索引的代价高；

插入速度严重依赖于插入顺序。

### 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。覆盖索引能极大的提高性能，是非常有用的工具。

### 使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果EXPAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序（不要和Extra列的“Using index”搞混淆了）。

### 冗余和重复索引

我在平时开发，无论是维护系统或开发新系统，都时不时会看到，形如：在表A上，有a列的单列索引和a、b、c列的多列索引并存。MySQL维护这些重复索引时，不仅在插入更新操作要更新两个索引，而且在查询时，优化器也要对索引逐个考虑，这会影响性能。

当然，我认为，实际开发维护中，我们对索引有疑惑，需要更新索引时，决不能仅靠经验来下判断，建议如下图（书的截图）使用sysbench等工具作一些必要测量。

![](/files/-LHEGoA-eHE3AU6FT20U)

### 索引与锁

索引能让查询锁定更少的行。

## 维护索引和表

### 找到并修复损坏的表

CHECK TABLE

如果遇到数据损坏，最重要的是找出什么导致损坏，而不只是简单地修复。

可以通过设置innodb\_force\_recovery参数进入InnoDB的强制修复模式，请参考手册。还可使用开源的InnoDB Data Recovery Toolkit。

### 更新索引统计信息

records\_in\_range()

info()

### 减少索引和数据碎片

* 行碎片
* 行间碎片
* 剩余空间碎片

OPTIMIZE TABLE