索引优化

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

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

索引语法：

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出版社)。

索引并不总是最好解决方案，这在特大型表中，索引的作用就随之失去。

索引策略

独立的列

需注意要养成索引列单独写一侧，比如： 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 where the data for the rows is stored. Typically, the clustered index is synonymous with the 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 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等工具作一些必要测量。

索引与锁

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

维护索引和表

找到并修复损坏的表

CHECK TABLE

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

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

更新索引统计信息

records_in_range()

info()

减少索引和数据碎片

• 行碎片

• 行间碎片

• 剩余空间碎片

OPTIMIZE TABLE