MySQL-索引

什么是索引

索引用于快速找出在某个列中有一特定值的行

索引分类

1、B-Tree索引 2、HASH索引

1、前缀索引 2、全文本索引

1、聚集索引 2、非聚集索引

MyISAM和InnoDB默认创建的都是BTREE索引，MEMORY默认为HASH索引，BTREE索引可选

对每个表至少支持16个索引，总索引长度至少为256字节

延伸阅读：MySQL索引背后的数据结构及算法原理

索引储存在哪里

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

怎么使用索引

创建索引

e.g：为表创建10个字节的前缀索引

CREATE INDEX index_name ON tbl_name (tbl_name(10));

删除索引

DROP INDEX index_name ON tbl_name;

查看索引

SHOW INDEX FROM sch_result_channel;

SHOW INDEX语法的实际应用

怎么更好地使用索引

  1. 尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，*唯一键的区分度是1*，像性别这种只有两种区别的就不要来浪费空间当索引了。

   select count(distinct  channel)/count(*) from sch_result_channel;
  1. 使用短索引。

较小的索引涉及的磁盘 IO 较少，较短的值*比较起来更快*。更为重要 的是，对于较短的键值，索引高速缓存中的块能*容纳更多*的键值。

短索引的具体长度可以根据区分度来选择

   select count(distinct  LEFT(id,4)/count(*) from peppa_order;

tips:有些字段是后缀差别较大，比如邮箱，但是原生MySQL不支持，可以把字符串反转后储存

  1. 最左前缀。

单列索引可以看成联合索引元素数为1的特例。

联合索引情况下，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

where后面的顺序是无所谓， MySQL优化器会自动排序寻找适合的索引。

就和查字典一样，你只能从个第一个字母开最左前缀始找，然后第二个字母，第三个……而不能找完第一个直接去找第三个。

范围查找(>、<、between、like)的索引后面的查询是利用不了索引的，精确查找（“=”或“IN”） 的顺序没关系

关于联合索引的顺序，也可以计算索引的区分度来决定，区分度高的排在前面（视具体情况而定）

  1. 不要过度索引。

1、每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。

2、在修改表的内容时，索引必须进行更新，有时可能需要重构。

3、如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。

4、索引太 多，也可能会使 MySQL 选择不到所要使用的最好索引。

  1. 对于 InnoDB 存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序 保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺 序保存。按照主键或者内部列进行的访问是最快的，所以 InnoDB 表尽量自己指定主键，当 表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为 主键，提高查询的效率。另外，还需要注意，InnoDB 表的普通索引都会保存主键的键值， 所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存 效果。

  2. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

  3. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

  4. 覆盖索引，如果一个索引包含所有需要查询的字段的值，称之为覆盖索引

BTREE和HASH索引

hash索引

引的 MEMORY 表，会影响一些查询的执行效率。

而对于 BTREE 索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者 LIKE ‘pattern’(其

中’pattern’不以通配符开始)操作符时，都可以使用相关列上的索引。

​

Comments

comments powered by Disqus