mysql索引总结

1.1   简介

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

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

 

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

 

 

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

·        hash index主要用于满足精确匹配

·        btree index主要用于满足范围查询、精确匹配

·        fulltext index主要用于全文关键字查询

 

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

·         heap引擎支持 hash index

·         myisam、innodb 引擎支持btree index

·         myisam支持 fulltext index

   

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

1.2   btree

1.2.1     innodbbtree

 

1.2.1.1 clusterindex

 

1.2.1.1.1  简 介

clusterindex是什么?

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

 

  

clusterindex的主要优点:

·        相关的数据临近存放，利于磁盘存取

·         row的读取更快，因为 row 和 index 一起存放

·         coveringindex 可以使用叶子节点的 primarykey 的值使某些查询更快 (covering index 请参见 1.2.1.4)

 

 

clusterindex的主要缺点:

·        如果访问模式与存储顺序无关，则 cluster index 无用

·         按主键顺序插入和读取最快, 但是如果按主键随机插入(特别是字符串)则读写效率降低

·         更新 cluster index 列的代价较大， 会将整个 row 重新写到新的位置上,并且所有secondary index 也要更新

·         如果 cluster index 建立在内容较长的字符串字段上， 会导致所有的secondary index 都较大

·        clusterindex 的 row 数据存放在 btree 的叶子节点上innodb 中的 btree 实际上是 b+ tree, 叶子节点之间也有指针相连，更便于范围查询innodb 默认的 page 大小为16K, fill factor 为 15/16, 剩下的用于修改

  

1.2.1.1.2  Tips

·        innodb表的cluster index是把双刃剑(参见 cluster index 优缺点)，并且一个表只 能有一个 cluster index, 在设计 cluster index 的时候一定要仔细考虑

·        innodb的 primary key 为 cluster index, 除此之外，不能通过其他方式指定 clusterindex, 如果 innodb 不指定 primary key,innodb会找一个 unique not null 的 field 做 cluster index,如果还没有这样的字段，则 innodb 会建一个非可见的系统默认的主键---row_id(6 个字节长)作为 cluster_index。

·        建议使用数字型 auto_increment 的 字 段 作 为clusterindex 。

·        不推荐用字符串字段做 clusterindex ( primarykey ) , 因为字符串往往都 较长， 会导致 secondary index 过大(secondary index 的叶子节点存储了 primary key 的值),而且字符串往往是乱序。 cluster index 乱序插入容易造成插入和查询的 效率低下。

1.2.1.2  secondaryindex

 

1.2.1.2.1  简 介

secondaryindex是什么?

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

 

1.2.1.2.2  Tips

·         secondaryindex 的查询代价变大，需要两次 btree 查询，一次 secondary index, 一次 clusterindex(adaptive hash index 可以帮助提速, 请参见 1.2.1.3 adaptive hash index), 所以在建立 clusterindex 和 secondary index 的时候需要 考虑到这点。

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

·         建议尽量减少对 primarykey 的 更 新 , 因为 secondary index 叶子节点包含 primary key 的 value(这样避免当 row 被移动或 page split 时更新 secondary index), primarykey 的变化会导致所有 secondary index 的更新。

1.2.1.3 adaptive hash index

 

1.2.1.3.1  简 介

Adaptive hash index是什么?

 adaptive hash index 是 innodb 为了加速btree 上的节点查找而保存的 hash 表。btree 上经常被访问的节点将会被放在 adaptive hash index 中。

 

1.2.1.3.2   Tips

·         建议做性能测试的时候根据需要禁止adaptive hash index,因为同样两条 SQL, 即使禁止 query_cache,但同样会受到 innodb_buff_pool 和 adaptive hash index 的影响,第二次查询也会比第一次快。

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

·         MySQL重启后的速度肯定会比重启前慢, 因为 innodb 的 innodb_buff_pool 和 adaptive hash index 都是内存型的东东， 重启后消失， 需要预热(访问一段时间) 后性能才能慢慢上来。

·         要想看 hash index 占了多少空间， show innodb status 查看INSERT BUFFER AND ADAPTIVE HASH INDEX 一项

1.2.1.4 covering index

 

1.2.1.4.1  简 介

  covering index 是什么?

 

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

 

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

 

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

  

1.2.1.4.2  Tips

·         我们应该尽量使用 covering index, 因为读取索引的代价往往比读取数据的代价低 (索引比数据小，更有序，IO 小,多放于内存中,更容易 cache) 同时 covering index 可以避免读取数据， 更高效。

·         Hash,fulltext 等索引不能 covering index。

·         建议写完 SQL语句后 ， 通过使用 Explain命令查看extra字段中是否存在”using index”判断是否使用了 covering index, 存在即代表使用。而 Extra 字段出现“using where” 即代表 SQL 中的条件不能通过索引来过滤，而是通过 MySQL 在 Server Level 这一级来对行进行条件过滤的，这时候效率会降低。

·        不建议使用select * ， 而是要写出自己真正需要的字段， select * 增大了网络传输代价并且容易造成程序误用， 更重要的是这样不能使用 covering index。

·        在适当的时候增加索引使其变为 covering index

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

   selecta,b,c from table where a = 'xx'(a 为 secondary index, b 和 c 上无索引)

这条 SQL 语句不能使用 covering index, 如果我们增加一个联合索引 abc, 则该 SQL 语句便能使用到covering index, 这种情况适合于某些关键的 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 变换为:

selecta,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 innodb btree Tips

·         建议innodb_buff_pool_size至少设置为物理内存的50%,因为 innodb_buff 不仅用于 cache index, 还用于 cache data, 并且 innodb 的 btree index 所占空间 通常要比 myisam 大。另外, innodb 对系统 cache 依赖没有 MyISAM 那么强， 因为 innodb_buff 会 cache data， 所以多余的内存可以考虑留给 innodb_buff 不必给系统留得太多。

·        频繁更新或者主键乱序插入后遇到性能问题，如果操作代价可以接受，建议optimize table table_name(相当于 alter table table_name engine=innodb 重建表), 重整有利于 innodb 提升效率。

·        建议定期 analyze table table_name 更新索引的统计信息, 便于 MySQL 查询计划做 出正确的选择， innodb 的这个操作不锁表，代价小，但是不精确。

 

 

1.2.2     myisam btree

 

1.2.2.1 myisam packed index

myisampacked index是什么?

 

Myisambtree 支持前缀压缩, 压缩后的索引称为 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 Tips

·         在 myisam 中， primary index 和其他 index 没有任何区别，唯一的区别是 myisam 中 的 primary index 是唯一的，非 null 的。

 

·         可以考虑定期使用 analyse table table_name 用于更新索引信息，便于 MySQL 做出正确的查询计划，但是需要注意 myisam 的这个操作要锁表。

·        除非内存非常紧张、CPU 不存在瓶颈等情况可以考虑 packed index, 其他情况一般不

建议使用 packed index。 

·         建议myisam的key_buffer配置为物理内存的25% , myisam 的索引和数据是分 开存储的，并且 myisam 的 key_buffer 只存储索引，myisam 对 data 的 cache 主要依 赖于系统 cache,设置 myisam_use_mmap=true 能提升 myisam 读取数据文件的速度(提 升 7% -- 40%)。

1.2.3     prefix index

 

1.2.3.1 简 介

 prefix index 是 什 么 ?

 

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

 

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

 

altertable t add index nameIndex(name(6));

 

这样的索引就叫做 prefix index。

 

 

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

   

prefixindex的主要优点:

 

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

 

 

prefixindex的主要缺点:

 

prefixindex 不支持 covering index 和 order by。

_Tips

·         使用 prefix index 会导致 orderby , covering index 不能使用该 index。

·         btree索引支持 start_with 类型的查询，但不支持 contains_with, end_with 类型的查询。

 

例如:

selectuserid from table where username like “Mac%” (start_with 类型)select userid from table where username like “%Mac%”(contains_with 类型) select userid from tablewhere 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 的需求。

·        int型的不建议使用 prefix index, 虽然可以提升效率，但是去不能使用 orderby, covering index 等， 建议使用更小的数字类型如 tinyint,bit 等来满足。

1.2.4     联合索引

1.2.4.1  简介

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

 

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

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

·         match所有列的值.例如: 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 中的某一个字段使用了范围查询，则后面的字段不能再使用索引。

·         联合索引 ABC 最多只能满足A, AB, ABC 三种查询， 如果需要 B、C 单独使用索引则需要另外建立索引。 通过 explain 中的 key_len 可以看出语句使用了联合索引的哪些部分。

1.2.4.2 Tips

 

·        联合索引 ABC 依然符合最左前缀的原则，即只有 ABC、AB、A 三种情况可以使用到索 引, 也不能跳 index,即 AC 同时查询只能使用到联合索引的 A 部分。

·        联合索引经常要使用范围查询的，将该范围查询索引字段尽量放在后面 ，因为联合索引中某个索引使用范围查询后, 其后的索引将不再有效。

 例如: 联合索引 ABC

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

·        同等情况下应该优先选择使用联合索引,而不是使用多个单一索引，因为联合索引将 多个索引建在一个 btree 上，比多个单一的 btree 在占用空间上更划算，并且可以在 一次查询的一个张表上使用多个索引。

·        不建议联合索引的字段过多，因为联合索引的使用有限制(必须符合最左前缀), 过多 容易造成联合索引中的索引利用率不高。联合索引中的字段数最多不超过 16(系统限 制)。

1.2.5     btree与orderby

1.2.5.1  简介

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

1.2.5.2  Tips

·         filesort并不代表外排，而是代表非索引排序。

 

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

·          prefix index 不能用于order by , 在设计的时候需要权衡这点。

  不是 leftmost 的索引也可以用于 order by , 前提是 where 中指定前面 left 的值。

 

例如: AB 是联合索引

 

selectc from table where A = 'xxx' order by B

 

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

 

·         orderby desc, asc 不能使用索引， order by 必须为同一方向

 

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

 

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

·         联合索引的 range 查询后，不能再使用索引

 

例如: AB 是联合索引

selectc from table where A > 'xxx' order by B

 

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

 

·         通过 explain 查看是否利用到索引排序, 如果 explain extra 字段中出现”filesort”则代表没有使用到索引排序

·        _{建议应用程序不依赖 MySQL 非显示排序的结果}

 

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

 

 

 

1.2.6     btree与group by

1.2.6.1 简 介

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

 

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

·        Looseindex Scan

·        Tightindex Scan

·        full-tablescan

 

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

  

1.2.6.2  Loose indexScan

 Loose index Scan是什么?

 

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

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

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

 

·         query发生在一个单表操作上

·         group by 后面的字段符合最左前缀或者distinct 后面的字段符合最左前缀

·         只能使用 min()和 max()这两个聚集函数，并且如果同时使用都必须对同一个字段进 行运算。

   

explainextra 字段中出现”Using indexfor group-by”说明使用了 Loose index Scan

 

eg:

 

selectc1, c2 from t1 group by c1, c2;

selectdistinct c1, c2 from t1;

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

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

  

 

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

 

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

 

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

 

 

1.2.6.3 Tight index Scan

Tightindex Scan 是什么 ?

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

Tightindex Scan。

 

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

1.2.6.4  full-tablescan

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

  

1.2.7     btree与distinct

1.2.7.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.6 btree与groupby 。

  

1.2.7.2  btreeTips

·         uniquekey 约束、primary key MySQL都会自动为 unique、primary key 建立索引，不需要自己再对这些字段建立索引， 否则就重复浪费了。

 

·         良好的索引可以减少锁的发生, 提高效率和并发度。

 

·         在 innodb 中，全表扫描会加表锁。

 

·         “非 leftmost 匹配”, end_with 匹配, contains 匹配这 3 类情况使用不上 btree 索 引。

 

·         模糊匹配中，以某一个东东开头, 例如:“abc%”, MySQL 会将”abc%”优化为>= abc && < abd 这样的范围查询, 这样就能使用到索引，但是”%as%”不能转换为范 围查询也不能使用到索引，即尽 量 不 要 在 模 糊 匹 配 的 最 前 面 使 用” % ” , 这样使用 不上任何索引， 应该尽 量 将 ” % ” 后 移 ， M y S Q L 会 将 这 种 模 糊 匹 配 转换 为 范 围 查 询 ，这样模糊匹配就可以使用上索引了。

 

·         索引建立和查询必须满足最左前缀(leftmost),这是MySQL的一个限 制 ，MySQL AB 可能会在以后的版本中改进。

·        对同一张表的查询， 如果使用到 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(*)是可以直接得到的，这样可以改写为

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

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

 

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

 

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

 

例如: select * from table where name = 1

 

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

1.3   hash

1.3.1     简介

hash 索引是一种非常高效的索引类型，能且只能满足精确匹配，一般实现为hashtable, 属于内存型索引，典型的 key/value 类型, 缺点是不能满足范围查询。

 

 

1.3.2     Tips

·         不存在 covering index 的概念

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

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

·        不支持 order by

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

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

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

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

 

例如: 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   fulltext

 

1.4.1     简介

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

 

1.4.2     MyISAM全文检索

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

 

·        全文检索语法支持有限

·        百万量级

·        只能和 MyISAM 使用

·        相关性能扩展难

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

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

 

 

1.4.3     sphinx

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

 

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

·        十亿级别数据规模

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

·        相关性不易扩展

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

  

1.5   索引Tips

·         MySQL中一张表一次查询最多使用一个索引， 所以才有 btree 的联合索引

 

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

 

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

 

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

 

·         mysql可能使用全表扫描而不是索引(即使存在索引)， 因为使用索引随机读可能没有顺序读快, 在这种情况下, mysql 可能直接使用全表扫描。

 

·         建议索引不要重复，这样会浪费有限的内存资源，降低效率。例外的情况是:对同一字段有不同索引类型需求时，可对同一字段建立多种类型的索引。

 

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

 

·         当类似 limit 10000, 10 的语句较慢的时候(MySQL 中 limit 10000, 10 要先扫过前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);

 

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

 

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

 

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

 

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

 

·         当字段内容很长或字段内容 leftmost 重复度很高时， 可以先将内容算 hash 值，然后对 hash 值建索引，前提是你只需要精确匹配

 

·         建议写完SQL语句后运行explain/profiling查看一下索引使用等情 况

 

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

 

·         使用”!=”或者”<>”的时候，MySQL无法使索引

 

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

 

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

 

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

 

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

1.6   索引长度

1.6.1      简介

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

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

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

1.6.2      Tips

·        不要对全部内容对索引,除非想利用order by或coveringindex

 

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

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.showtable 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. 查看欲建立字段的最佳索引区分度

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

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

 

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

例如:

selectcount(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_demogroup by city order by cnt desc limit 100

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

 

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

  

  

6. 建完索引后show table status查看索引大小

 

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