Mysql 索引

我很丑
363 阅读5分钟

mysql 索引

索引是一种数据结构， 是用来快速检索出具有特定值的记录。根据存储引擎的不同采用不同形式的数据结构。

存储引擎

  • 存储引擎是数据库管理系统用来从数据库创建、读取和更新数据的软件模块

  • MyISAM和InnoDB存储引擎只支持BTREE索引。

  • MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

HASH索引和BTREE索引比较

  • HASH

    • hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，适合等值查询快（如果有大量重复键会产生hasn碰撞会影响性能）。

    • 不能进行区间查询（需要全表扫描）和排序。

    • 不支持多列联合索引最左匹配原则。

  • BTREE

    • btree索引是底层是B树，B树是一种多路平衡查询树（左子节点小于父节点，父节点小于右子节点）适合做范围查询和排序。

    • 可以进行区间查询和排序。

    • 支持多列联合索引最左匹配原则

聚簇索引和非聚簇索引

聚簇索引

  • 即主键索引，将数据存储与索引放到了一块（叶子节点存放的是整行数据和索引，因此一个表只有一个聚簇索引，默认是主键），找到索引也就找到了数据，查询效率高。

  • 其它索引被称为辅助索引, 辅助索引访问数据总是需要再回表查找（覆盖索引除外）

非聚簇索引

  • 即非主键索引，将数据存储与索引分开结构，索引结构的叶子节点指向了数据的对应行。

  • 一般需要再进行回表查询（覆盖索引除外）

  • 效率相对于聚簇索引较低

覆盖索引

覆盖索引 覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。 如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = 'keytest';的时候，就可以通过覆盖索引查询，无需回表。
  • 同样的有联合索引（a,b,c），如果有如下的sql: select a,b,c from table where a=xxx and b = xxx。那么MySQL可以直接通过遍历索引取得数据，而无需读表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一

联合索引和最左前缀匹配

  • 对多个字段同时建立的索引(有顺序，ABC，ACB是完全不同的两种联合索引。)

  • 以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。

  • 在创建多列索引时，我们根据业务需求，where子句中使用最频繁的一列放在最左边，因为MySQL索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。

索引下推

Index Condition Pushdown（索引下推） MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = 'index_condition_pushdown=off';可以将其关闭。官方文档中给的例子和解释如下：
people表中（zipcode，lastname，firstname）构成一个索引
SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
如果没有使用索引下推技术，则MySQL会通过zipcode='95054'从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054'的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。 有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。
  • 索引下推在mysql 5.6版本中优化

查询优化

  • 看是否使用了索引，可以通过explain查看。

  • 索引列不要参与计算

  • or的两侧要么都索引列，要么都不是索引列

  • 模糊匹配的时候%不要在头部

  • 联合索引中，把最常用的，筛选数据最多的字段放在左侧。（最左前缀匹配原则）

参考

avatar
文章
阅读
粉丝