mysql索引总结

1 . 1 简 介

为了满足对数据的快速访问， 我们通常需要将数据组织成一种有序的方式， 而原始 的情况下数据的物理存储顺序便可代表一种“序”， 但是由于物理存储的“序”只能是一 种， 但我们业务的访问模式是多样的， 所以我们有了索引, 索引是一种以更小代价来组 织数据关系的一种“序”， 不同的索引可以满足不同的访问模式。

索 引 的 前 提 就 是 了 解 业 务 的 访 问 模 式 。

索引是使用 MySQL 过程中非常重要的一环， 良好的索引将大大提高 SQL 的执行效率， 提升单机性能, 同时索引同锁、排序等都有着密切的关系。

索 引 类 型 的 选 择 主 要 取 决 于 应 用 的 不 同 需 求 :

不 同 的 存 储 引 擎 支 持 不 同 的 索 引 类 型 :

索引具体由存储引擎提供支持，而非 MySQL 内核，所以即使是对同一种索引类型，内部的实现方式与效率都可能不同。

1.2 b t r e e

1 . 2 . 1 i n n o d b b t r e e

1 . 2 . 1 . 1 c l u s t e r i n d e x

1 . 2 . 1 . 1 . 1 简 介

c l u s t e r i n d e x 是 什 么 ?

cluster index 是 innodb 特有的一种索引， 数据与索引在同一个 btree 上， 一般数 据的存储顺序与索引的顺序一致。innodb cluster index 每个叶子节点包括 primary key(或者 row_id), transactionID, rollback pointer 和行数据, 非叶子节点只包括被索 引列的索引信息。

其他:

cluster index 的 row 数据存放在 btree 的叶子节点上

innodb 中的 btree 实际上是 b+ tree, 叶子节点之间也有指针相连，更便于范围查询

innodb 默认的 page 大小为 16K, fill factor 为 15/16, 剩下的用于修改

1 . 2 . 1 . 1 . 2 T i p s

建 议 使 用 数 字 型 a u t o _ i n c r e m e n t 的 字 段 作 为 c l u s t e r i n d e x 。

不 推 荐 用 字 符 串 字 段 做 c l u s t e r i n d e x ( p r i m a r y k e y ) , 因为字符串往往都 较长， 会导致 secondary index 过大(secondary index 的叶子节点存储了 primary key 的值),而且字符串往往是乱序。 cluster index 乱序插入容易造成插入和查询的 效率低下。

1.2.1.2 s e c o n d a r y i n d e x

1 . 2 . 1 . 2 . 1 简 介

s e c o n d a r y i n d e x 是 什 么 ?

innodb 中非 cluster index 的所有索引都是 secondary index。

1 . 2 . 1 . 2 . 2 T i p s

secondary index 便可获取所需数据， 不需要再进行数据读取，提高了效率。 我们在设计索引和写 SQL 语句的时候就可以考虑利用到 covering index 的优势 (怎么设计索引和改写 SQL 利用 covering index, 请参见 1.2.1.4 covering index)

1.2.1.3 a d a p t i v e h a s h i n d e x

1 . 2 . 1 . 3 . 1 简 介

a d a p t i v e h a s h i n d e x 是 什 么 ?

adaptive hash index 是 innodb 为了加速 btree 上的节点查找而保存的 hash 表。

btree 上经常被访问的节点将会被放在 adaptive hash index 中。

1 . 2 . 1 . 3 . 2 T i p s

设置 innodb_adaptive_hash_index = OFF 或者在 mysqld 启动的时候加上参数--skip-innodb_adaptive_hash_index 可以禁止 adaptive hash index。 innodb_adaptive_hash_index 变量默认是开启的，这个配置是在 5.0.52 版本中加入 的，以前的版本只能通过修改源码中的宏并重新编译才能禁掉 adaptive hash index。

INSERT BUFFER AND ADAPTIVE HASH INDEX 一项

1.2.1.4 c o v e r i n g i n d e x

1 . 2 . 1 . 4 . 1 简 介

c o v e r i n g i n d e x 是 什 么 ?

索引通常是用于找到行的， 但也可以用于找到某个字段的值而不需要读取整个行，因为索引中存储了被索引字段的值,只读索引不读数据, 这种情况下的索引就叫做covering index。

例如: select username, userid from user where username=”xx”

如果 userid 为 cluster index，username 为 secondary index, 这个时候根据 secondary index 上的索引信息就可以得到 username,userid 的值，而不需要再读数据,也不 需要再读 cluster index。

1 . 2 . 1 . 4 . 2 T i p s

(索引比数据小，更有序，IO 小, 多放于内存中,更容易 cache) 同时 covering index 可以避免读取数据， 更高效。

而 Extra 字段出现 “using where” 即代表 SQL 中的条件不能通过索引来过滤，而 是通过 MySQL 在 Server Level 这一级来对行进行条件过滤的，这时候效率会降低。

例如: 频繁使用的某条 SQL 语句

select a,b,c from table where a = 'xx'

(a 为 secondary index, b 和 c 上无索引)

这条 SQL 语句不能使用 covering index, 如果我们增加一个联合索引 abc, 则该 SQL 语句便能使用到 covering index, 这种情况适合于某些关键的 SQL 语句用 covering index 提速。

例如: select a,b,c from record where title = “uuu”

(title 上建了 secondary index, 但是 a,b,c 非索引, 该表的主键为 record_id)

上面的这条 SQL 语句由于 select a,b,c (非索引)无法使用 covering index

将该 SQL 变换为:

select a,b,c from record

JOIN (

select record_id from record where title = “uuu”

) as t on (t.record_id = record.record_id)

这样就可以使用 covering index 了， 但是需要权衡 where title = “uuu”得到的

记录大小， 太大反而会降低效率，所以比较适合返回结果较少的情况。

1 . 2 . 1 . 5 i n n o d b b t r e e T i p s

1 . 2 . 2 m y i s a m b t r e e

1 . 2 . 2 . 1 m y i s a m p a c k e d i n d e x

M y i s a m p a c k e d i n d e x 是 什 么 ?

Myisam btree 支持前缀压缩, 压缩后的索引称为 myisam packed index。 索引可以压缩为原来的 1/10, 其实是 CPU/mem/disk 之间的一个 tradeoff, create table 的时候可以为索引指定 pack_keys (alter table t engine=myisam pack_keys=1)，但是 packed index 对反向 order 和 binary-search 效率差。

1 . 2 . 2 . 2 T i p s

建议使用 packed index。

1.2.3 p r e f i x i n d e x

1 . 2 . 3 . 1 简 介

p r e f i x i n d e x 是 什 么 ?

在 MySQL 中，索引只能从字段内容的最左端开始建， 查询的时候也只能从索引的最 左端开始查， 对字段内容只建从左开始的部分字节的索引，而非全部做索引的这种 index 就叫做 prefix index。

例如: 一个定长的字段 name char(20)，我们只对前面 6 个字节做索引，

alter table t add index nameIndex(name(6));

这样的索引就叫做 prefix index。

prefix index 涉及索引到底建多长的选择， 这部分请参见 1.6 索引长度。 文中 prefix 的意思与 leftmost 一致。

p r e f i x i n d e x 的 主 要 优 点 :

在索引满足一定的区分度的情况下， 索引变得更小， 更有利于放入或将更多的索引放入内存，减少 IO 操作，提高效率。

p r e f i x i n d e x 的 主 要 缺 点 :

prefix index 不支持 covering index 和 order by。

1.2.3.2 T i p s

例如:

select userid from table where username like “Mac%” (start_with 类型) select userid from table where username like “%Mac%”(contains_with 类型) select userid from table where username like “%Mac”(end_with 类型)

第一个 start_with 的 SQL 语句可以使用到索引， MySQL 会将其转换为类似 >= Mac < Mad 的范围查询, 第二个 contains_with 的 SQL 语句不能使用到索引, contains_with 类型 的需求可以由全文检索来满足，第三个 end_with 的 SQL 语句也不能使用到索引。end_with 虽然不能使用到索引， 但是我们却可以将字段的内容反转一下， 这样便可以用

start_with 类型的查询了，即可以使用到索引。

prefix index 自然满足 start_with 的需求。

1.2.4 联 合 索 引

1 . 2 . 4 . 1 简 介

在 MySQL 中有一个限制，一个表在一次查询中最多使用一个索引。但是我们对一张表 的很多字段可能都需要使用索引，这个时候怎么办呢？ 正是联合索引来解决这个问题。

联 合 索 引 可 以 用 于 的 查 询 :

下面的所有例子都基于联合索引 ABC(按照 A、B、C 顺序建立的联合索引)

例如: select d from table where A = “x” and B = “y”and C = “z” A =”x”, B = “y”, C = “z”均能使用到索引。

例如: select d from table where A = “x” and B >= “y” and C = “z” 只有 A = “x”和 B >= “y”能使用到索引， C = “z”不能使用索引, 因为如果

联合索引 ABC 中的某一个字段使用了范围查询，则后面的字段不能再使用索引。

1.2.4.2 T i p s

例如: 联合索引 ABC

select d from table where A > 'xx' and B = “yy” and C = “zz” 由于 A 使用了范围查询， B,C 两个字段将不能使用索引，所以我们应该尽量用 or , in 代替 “>”,”<”等, 使其不是范围查询,但是 in 内的 内容太多， 会导致查询优化慢和消耗内存(因为 in 内内容多导致查询计划的组合数 多)。

1.2.5 b t r e e 与 o r d e r b y

1 . 2 . 5 . 1 简 介

MySQL 中的排序有两种情况，一种是根据查询的结果进行排序，另外一种是利用索引已有的“序”，而后一种的效率更高，即我们本节关心的”btree 与 order by”。

1 . 2 . 5 . 2 T i p s

filesort 虽然名字叫 filesort, 但它也可能是内存排序。

例如: AB 是联合索引

select c from table where A = 'xxx' order by B

虽然 order by 中的 B 是非 leftmost 的， 但是由于在 where 中指定了 A 的值，所以仍然可以使用到联合索引 AB 中的 B 索引做 order by。

例如： 联合索引 AB, order by A desc, B asc 将不能使用到索引， 而 order by

A,B 就可以使用索引， 即它们的顺序必须一致。因此，若有类似明确需要order by a, b desc 这样的需求，建议通过处理（比如 b 的值都取负）改成顺序一致

例如: AB 是联合索引

select c from table where A > 'xxx' order by B

则根据联合索引中提到的原则， 该条 SQL 语句只能使用到联合索引 AB 中的 A 索引

例如: MySQL 返回结果的默认顺序可能是和使用到的索引的顺序有关的， 但是建议 应用程序不要将顺序逻辑建立在这种非显示 order by 的结果上。

1 . 2 . 5 b t r e e 与 g r o u p b y

1 . 2 . 5 . 1 简 介

group by 实际上也会进行排序工作，而且与 order by 相比，group by 主要只是多了 排序之后的分组操作。如果在分组的时候还使用了聚合函数(min()、max()等),那么还需要 一些聚合函数的计算。

在 mySQL 中， group by 的实现有 3 种方式:

(1.2.5 节所有例子中的 t1 包括 c1,c2,c3,c4 四个字段，并建立 idx(c1,c2,c3 为)联 合索引)

1 . 2 . 5 . 2 L o o s e i n d e x S c a n

L o o s e i n d e x S c a n 是 什 么 ?

这种方式只扫描满足 where 条件的每个 group 的第一个 key 而不用扫描所有的 key,所 以叫做 Loose index Scan。

Loose index Scan 是做 group by 最高效的方式，也不会产生临时表。

Loose index Scan 只有在满足如下条件才可能发生:

explain extra 字段中出现”Using index for group-by”说明使用了 Loose index Scan

eg:

select c1, c2 from t1 group by c1, c2;

select distinct c1, c2 from t1;

select c1, min(c2) from t1 group by c1;

select c1, c2 from t1 where c1 < const group by c1,c2;

上面的 SQL 语句能够使用索引完成 group by, 而下面的 SQL 语句则不可以:

select c1, sum(c2) from t1 group by c1 (使用了非 min()/max()的聚集函数)

select c1, c2 from t1 group by c2, c3 (非最左前缀 group by)

1 . 2 . 5 . 3 T i g h t i n d e x S c a n

Tight index Scan 是什么 ?

这种方式会读取 where 限制条件过滤后的所有行，然后再做 group by， 所以叫做

Tight index Scan。

Tight index Scan 完成 group by 可避免创建临时表。

1.2.5.4 f u l l - t a b l e s c a n

full-table scan 的大致流程是扫描整个 table, 创建一张临时表将所有的行按照不 同的分组顺序存放， 最后利用这个临时表发现 group 以及执行聚集函数, 所以效率是很低 的。

1 . 2 . 6 b t r e e 与 d i s t i n c t

1 . 2 . 6 . 1 简 介

在大多数情况下， distinct 可以认为是一种特殊的 group by。

例如: select distinct c1, c2, c3 from t1 where c1 > const

select c1, c2, c3 from t1 where c1 > const group by c1, c2, c3

上述两条 SQL 语句是等价的，所以， btree 与 distinct 的关系基本可以认为是btree 与 group by 的关系， 这部分请参见 1 . 2 . 5 b t r e e 与 g r o u p b y 。

1 . 2 . 7 b t r e e T i p s

对同一张表的查询， 如果使用到 or，并且 or 中的条件需要全表扫描， 那么即使该表中其他条件可以使用索引， MySQL 仍然只会使用全表扫描。

例如: select * from tblLemma where id > 100 or authorname = “xxx” (tblLemma 表中 id 字段有索引， authorname 字段无索引)

虽然 id > 100 可以使用索引， 但是 authorname = “xxx”无索引可使用(全表扫 描)， 而这两个条件的连接词为 or , MySQL 认为没有必要再去多余地读取索引，所 以这个查询的结果就是全表扫描。

一些 SQL 也可以反过来写

例如: select count(*) from table where id > 10 (id 是自增主键)

对于 myisam 来说， 对整个表的 count(*)是可以直接得到的，这样可以改写为

select count(*) as cnt from table where id <= 10

然后用整个表的 count(*) - cnt 得到结果

内容重复度高的字段也可以考虑建索引， 这样的字段建索引同样有可能大大提升效 率，主要是索引占用的空间、SQL 提升的效率、SQL 的重要程度及频繁程度等因素的 一个综合衡量。比较重要的 SQL 语句还是值得建的，不过最好是多个类似的字段建联 合索引。

字 段 类 型 和 值 的 类 型 应 该 一 致 ， 否则可能会降低 SQL 效率。

• 不存在 covering index 的概念

• 只能精确匹配， 不能部分匹配

• 不支持范围查询， 范围查询实际是全表扫描

• 不支持 order by

• 索引区分度一定要大，否则冲突时代价大

• 内存型，重启后 hash index 丢失，需要考虑索引丢失后的重建问题以及对服务的冲 击, 同时考虑重建的主从同步问题

• 不支持 index statistics(对 MySQL 查询计划没有帮助)

• 如果存储引擎不支持 hash, 但支持 btree, 那可以用数字型的值来做 btree 索引，速 度快很多， int = crc32(str)

• 全文检索语法支持有限

• 百万量级

• 只能和 MyISAM 使用

• 相关性能扩展难

• 小数据量、对性能要求不高、对相关性没有特殊要求的话，可以考虑使用

• 其余情况不建议使用， 推荐 1.3.3 的 baidu 版 innodb-ft 全文检索

• 功能和性能都很强大的全文检索系统

• 十亿级别数据规模

• 非实时检索,需要主动拉数据

• 相关性不易扩展

• 开源软件， 经验证有 bug,不是特别稳定，官方技术支持不好, 推荐学习， 但不推 荐使用

• M y S Q L 中 一 张 表 一 次 查 询 最 多 使 用 一 个 索 引 ， 所以才有 btree 的联合索引

• 索引应该尽量保证区分度, 这是索引的一个重要原则

• 对于经常查询的 SQL 应该重点考虑索引，提升效率

• mysql 可能使用全表扫描而不是索引(即使存在索引)， 因为使用索引随机读可能没

• 建议索引不要重复，这样会浪费有限的内存资源，降低效率。例外的情况是:对同一

• 当类似 limit 10000, 10 的语句较慢的时候(MySQL 中 limit 10000, 10 要先扫过前

• 索 引 列 上 不 能 有 任 何 运 算 ， 有 运 算 将 使 用 不 到 索 引

• 索引不宜过多，太多索引的空间甚至可能超过数据， 浪费内存等资源，造成 io 重， 效率低,更新代价大

• 写操作多的应用不一定需要索引，需要考虑读写比例

• 禁止索引，可以加快写操作，对于批量写操作，例如 load 之前禁止索引会执行更快

• 数 字 型 索 引 比 字 符 串 型 索 引 更 高 效 , 查询更快，占用空间更小

• 当字段内容很长或字段内容 leftmost 重复度很高时， 可以先将内容算 hash 值，然

• 建 议 写 完 S Q L 语 句 后 运 行 e x p l a i n / p r o f i l i n g 查 看 一 下 索 引 使 用 等 情 况

• 一般情况下不建议使用 Force index 强制使用某个索引

• 使 用 ” ! = ” 或 者 ” < > ” 的 时 候 ， M y S Q L 无 法 使 用 索 引

• MyISAM 中字段的索引长度不能超过 1000

• BLOB 和 TEXT 类型的列只能创建前缀索引

• 可 以 枚 举 的 字 段 使 用 枚 举 类 型 查 询 和 索 引 的 效 率 会 更 高

• 不 要 对 全 部 内 容 对 索 引 , 除 非 想 利 用 o r d e r b y 或 c o v e r i n g i n d e x

• 建议设计索引的时候结合记录数、字符集大小、字段长度、字段内容的重复程度、字 符之间的相关性等考虑索引长度(具体的方法请参见 2.3 索引长度选择的方法)，索 引长度不当将使索引过于庞大， 内存资源利用不高， 造成 IO 较重，程序效率降低。 合理的索引长度，可以在满足较好索引区分度的情况下减少索引所占空间，我们的目 标就是找到索引空间大小与索引区分度的一个平衡点。

例如: select * from table where name = 1

name 为字符串型， 本来要写字符串”1”, 却写成了数字型 1，比较类型和值不是同 一个类型的， MySQL 虽然会做一定的类型转换，但是这样写 SQL 却容易造成使用不到 索引。

1.3 h a s h

1 . 3 . 1 简 介

hash 索引是一种非常高效的索引类型，能且只能满足精确匹配，一般实现为

hashtable, 属于内存型索引，典型的 key/value 类型, 缺点是不能满足范围查询。

1 . 3 . 2 T i p s

例如: select id from table where url = “xxx” and url_crc=CRC32('xxx') 其中 url=”xxx”必须写, 否则结果可能不正确， 因为 url_crc=crd32('xxx')可能

存在冲突, 这也是使用 hashindex 时需要注意的点

sha1() md5()在这种情况下不适用, 因为它们都返回很长的字符串, 在这种情况下效 率并不高， sha1(), md5()适合用于完全避免冲突的情况

1.4 f u l l t e x t

1 . 4 . 1 简 介

全文检索是一种基于关键字的查询，支持全文内的检索，而 btree、hash 只能支持精 确匹配和 leftmost 查询, 另外， 全文检索不像 btree、hash 那样要求准确的结果。

1 . 4 . 2 M y I S A M 全 文 检 索

MyISAM 是少有的支持全文检索的引擎, 其特点如下:

1.4.4 s p h i n x

Sphinx 是一款非常优秀的全文检索系统， 并且结合 MySQL 做了 MySQLSE 实现 MySQL 全文检索, 其特点如下:

1 . 5 索 引 T i p s

对于偶尔执行的 SQL(例如一两周执行一次)， 则可以考虑不做索引

有顺序读快, 在这种情况下, mysql 可能直接使用全表扫描。

字段有不同索引类型需求时，可对同一字段建立多种类型的索引。

例如: 有 cab 联合索引后还单独建 c 索引,除非你想用多种不同的索引类型满足不同 的查询需求, (例如单独为 c 再建立 fulltext index 或者 hash index)。

10000 条记录)， 可以先用 covering index 取出主键 id, 然后再 inner join 取出

data。

例如:select * from actor where actor_id + 1 = 5; (action_id 左边有运算，使 用不到索引)

应改为: select * from actor where actor_id = 4;

例如:select * from table where to_days(current_date) – to_days(date_col)

<= 10 ,date_col 上有 to_days 函数运算，使用不到索引

应改为: select * from table where date_col >= DATE_SUB('2008-01-17', INTERVAL 10 DAY);

后对 hash 值建索引，前提是你只需要精确匹配

例如: 字符串字段 name 中只会出现”a”、”b”、”c”3 种内容，则将字段类型换 为 enum(“a”, “b”, “c”)查询和索引效率会更高， 但是扩展起来就麻烦一点， 因为增加一种枚举值就需要修改 DDL, 所以这个问题在使用的时候也需要权衡。

1.6 索 引 长 度

1 . 6 . 1 简 介

索引的目的是为了比查找数据更快地找到所求， 所以索引需要具备很好的区分度， 否则就会出现用索引找到一批 rows, 但是还需要把数据读出来才能区分的窘境， 这对效率 是一种伤害。

最好的区分度当然就是索引的每一个值都能唯一地定位到一个 row, 为了使索引具有 最好的区分度， 通常的做法是对字段的所有内容建立索引, 这是这却可能存在问题。为什 么呢？ 索引的区分度虽然上去了， 但是索引本身变大了， 而内存又有限， 所以这样的索 引降低了内存有效的使用率，有可能造成更多的 IO 操作， 从而降低程序的效率。

所 以 ， 索 引 长 度 的 概 念 便 孕 育 而 生 ， 我 们 的 工 作 就 是 在 索 引 区 分 度 与 索 引 大 小 之 间 找 到 一 个 合 理 的 平 衡 点 。

1 . 6 . 2 T i p s

1.6.3 索 引 长 度 选 择 的 方 法

索 引 区 分 度 是 什 么 ?

索引的区分度是索引最重要的一个东东，是指 distinct 的记录和总记录数的比值, unique key 的区分度为 1。

例如: select count(distinct city)/count(*) from city_demo 便可算出 city 字 段的最佳区分度。

几 个 字 节 的 威 力 ?

平时我们都“奢侈”地去索引， 但是究竟多少的索引是有效的，多少是浪费的，我 们看看下面这个例子吧。

假设一个字段内容的字符集为 26 个英文字母, 字段内容为 26 个英文字符的随机组合， 我们只建立 6 个字节的索引， 那理论上来说， 这 6 个字节的索引能唯一区分多少行的记录 呢?

26*26*26*26*26*26=308915776, 3 亿

真实的情况当然是字符集更大， 字段内容重复度更大而非随机， 字符与字符有相关

性等情况， 但这个例子毕竟说明了几个字节索引的威力。

索 引 的 长 度 和 哪 些 因 素 有 关 ?

索引的长度和记录数、字符集大小、字段长度、字段内容的重复程度、字符之间的相 关性等都有关系。

如何选择索引的长度 ?

1 . 首 先 了 解 表 中 记 录 的 总 体 情 况 A.show table status/G;

能看到 Avg_row_length (每行的平均长度, 不准确)、Rows(不准确)、

Data 所占空间、已有索引所占空间等信息。

B.select count(*) from table

查看准确的总体行数

2 . 查 看 欲 建 立 索 引 的 字 段 的 总 体 情 况

select * from t procedure analyse()/G;

能看到表中所有字段的 Min_value、Max_value、Min_length、Max_length、是否为 NULL、字段平均长度、字段类型优化建议等信息。其中字段长度的相关信息很重要， 它给出了字段的大致信息，对索引长度的选择很有帮助， 而字段类型优化则是在 已有内容基础上给出的类型优化， 例如：如果你的表中有 1000 万行， 字段 name 为字符串， 但是却只有”a”,”b”,”c”三个值，则会建议优化字段类型为 enum(“a”,”b”,”c”), 这样查询和索引效率都会大大提高。

3 . 查 看 欲 建 立 字 段 的 最 佳 索 引 区 分 度

select count(distinct city)/count(*) from city_demo;

这是该字段全部内容长度都做索引能达到的最理想的区分度, 这个首先可以用来衡 量该字段是否适合做索引。

4. 看 不 同 索 引 长 度 的 区 分 度 , 这 个 是 个 平 均 值

例如:

select count(distinct left(city, 3))/count(*) as sel3, count(distinct left(city, 4))/count(*) as sel4, count(distinct left(city, 5))/count(*) as sel5, count(distinct left(city, 6))/count(*) as sel6, count(distinct left(city, 7))/count(*) as sel7 from city_demo;

查看到 city 字段做 3 个字节索引、4 个字节索引、5 个字节索引、6 个字节索 引、7 个字节索引的区分度, 可以一直增加索引长度来探测结果。

如果随着索引长度的增加， 索引区分度在很明显地增大， 那说明我们应该继 续增加索引长度， 即使当我们增加索引长度时，索引区分度没有明显变化，我们仍 然应该继续增加索引长度探测，

举个例子: 字段内容为”abcaaaxyz...”,”xioaaabvc...”, 由于这两行内 容中间都存在重复的”aaa”,所以当索引长度增大到 4、5、6 时索引的区分度是不会 有任何变化的， 但是”aaa”之后的内容的区分度却是很好的， 所以我们应该继续 向后探测。

那么探测到何时为止呢 ？ 当我们发现继续增加很多索引长度但是区分度却没 有明显提升而现有区分度接近第 3 条中的最佳区分度时， 这个时候的索引长度可能 就比较合理了。

截止上面的步骤， 我们找的都是平均分布， 有可能出现的是平均区分度很好而少量

数据集中出现区分度极差的情况, 所以我们还需要查看一下区分度分布是否均匀。

5 . 查 看 区 分 度 是 否 均 匀

select count(*) as cnt, city from city_demo group by city order by cnt desc limit 100

select count(*) as cnt, left(city, 3) as pref from city_demo

group by pref order by cnt desc limit 100 select count(*) as cnt, left(city, 4) as pref from city_demo

group by pref order by cnt desc limit 100

select count(*) as cnt, left(city, 5) as pref from city_demo group by pref order by cnt desc limit 100 索引选择的最终长度应该在平均区分度(前 4 条)与区分度是否均匀(第 5 条)之间的

长度做一个综合的选择。

6 . 建 完 索 引 后 s h o w t a b l e s t a t u s 查 看 索 引 大 小

这是一个收尾且非常重要的工作， 我们必须清楚的知道建立这个索引的代价。