Nivelle 开拓视野冲破艰险看见世界 身临其境贴近彼此感受生活

索引学习之索引创建

2017-05-04
nivelle

最近在看《高性能MySQL》,所以把常用到的索引知识总结下

创建索引

索引是在存储引擎实现的,MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则通过主键引用被索引的行。

复合索引:

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

例如:
联合索引时,遵循最左前缀原则(如INDEX(nickname,city), 则以下能使用索引 SELECT * FROM user WHERE nickname=’aiddroid’; SELECT * FROM user WHERE nickname=’aiddroid’ AND city = ‘广州’; 以下不能使用索引 SELECT * FROM user WHERE city = ‘广州’;

聚簇索引

  • 首先,聚簇索引不是一种索引类型,而是一种数据存储方式,InnoDB的聚簇索引实际是在同一个结构中保存了B—Tree索引和数据行。
  • 当表有聚簇索引时,它的数据实际存放在索引的叶子页中。无法同时把数据行存在两个不同的地方,所以一个表只有一个聚簇索引。(叶子页包含了全部的数据,节点页只包含了索引列)

优点:更快,可直接获取页节点中的主键值。(InnoDB索引的数据指的是主键值)

缺点:过犹不及(都放在内存,顺序就不重要了),插入速度严重依赖顺序.

image

最好避免随机的聚簇索引,特别是对I/O密集型的应用。从性能角度,uuid作为聚簇索引会很糟糕:他使得聚簇索引的插入变得随机,使数据没有任何聚集特性。


覆盖索引

定义:如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”

优点:

  • 极大地减少数据访问量
  • 索引是按照列值顺序存储的(至少单页内是如此),对于I/O密集型的范围查询会比随机从磁盘读取每一行的数据I/O要少的多
  • 一些存储引擎如MyISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用。
  • 由于InnoDB的聚簇索引,覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。

注意: select * ,查询一般无法做覆盖查询,但是可以利用一个捷径:where 条件中的列是有索引可以覆盖

二级索引应用的例子:

因为二级索引的叶子节点包含了主键值,所以在列(A)上的索引就相当于在(A,ID)上的索引,如果有 where a=5 order by ID 这样的查询,会很有用。但是将索引扩展为(A,B),则实际上成为了(A,B,ID),那么order by 子句就无法使用该索引做排序,只能使用文件排序了。


当发起一个被索引覆盖的查询时,在EXPLAIN 的Extra列可以看到“Using Index”的信息。

排序中的索引:

mysql有两种方式生成有序的结果:通过(排序操作);或者(按照索引顺序扫描)。如果expian 出来的type 列的值为“index”则说明可以使用索引扫描来做排序。

如果索引不能覆盖查询所需的全部列,那么就不得不每扫描一条索引记录就回表查询一次对应的行。基本上都是随机I/O,因此按照索引顺序读取数据的速度通常要比顺序地全表扫描慢,尤其是在I/O密集型的工作负载时。

当索引的列顺序和order by 字句的顺序完全一致,并且所有列的排序方向(倒叙或正序)都一样时,mysql才能使用索引来对结果做排序。如果查询需要关联多张表,则只有当order by 子句引用的字段全部为第一个表时,才能使用索引做排序。order by 子句和查找型查询的限制是一样的:需要满足索引的最左前缀的要求,否则,MySQL都需要执行排序操作,而无法利用索引排序。

例外:前导列为常量时,可以不满足最左前缀的限制。

image 即使order by 子句不满足索引的最左前缀的要求,因为有rental_date 这个常数量。排序仍然可以按照索引实现。

image

下面是一些不能使用索引做排序的查询:

1.使用了两种不同的排序方向,但是索引列都是正序排序的:
...where rental_date ='2005-05-25' order by inventory_id desc,customer_id asc;

2.下面这个查询的order by 子句中引用了一个不在索引中的列:
... where rental_date ="2005-05-25" order by inventory_id,staff_id;

3.下面这个查询的where 和order by 中的列无法组合成索引的最左前缀:
... where rental_date ="2005-05-25" order by customer_id;

4.下面这个查询在索引列的第一列上是范围条件,所以MySQL无法使用索引的其余列:
... where rental_date >'2005-05-25' order by inventory_id,customer_id;

5.这个查询在inventory_id 列上有多个等于条件,对于排序来说,这也是一种范围查询:
...where rental_date ='2005-05-25' and inventory_id in (1,2)order by customer_id;

注意:MYSQl的唯一限制和主键限制都是通过索引实现的。注意防止创建重复索引。

高性能索引策略

  • 独立的列:索引列不能是表达式的一部分,也不能是函数的参数。
  • 前缀索引和索引选择性:通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。唯一索引的选择性是1,这是最好的选择性,性能也是最好的。前缀索引是一种是索引更小、更快的方法,但是缺的是:无法使用前缀索引做oreder by 和group by,也无法使用前缀索引做覆盖扫描.

索引合并策略

在多个列上建立独立的单列索引大部分情况下并不能提高mysql的查询性能。5.0版本引入了一种叫“索引合并”的策略,一定程度上可以使用表上多个单列索引来定位指定的行。能够同时使用多个单列进行扫描,并将结果进行合并。

索引合并是一种优化结果,其实说明了索引建的有问题,需要考虑如下问题:

  • 当出现服务器对多个索引做相交操作时(多个and),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的索引。
  • 当服务器对多个索引做联合操作时(多个or),通常需要耗费大量的cpu和内存资源在算法的缓存、排序和合并操作上。特别是有些索引选择性不高,需要合并扫描返回大量数据的时候。
  • 优化器不会把这些计算到“查询成本”中,优化器只关心随机页面读取。使得成本被低估。不但消耗更多cpu和内存资源,还可能导致查询的并发性,但单独运行这样的查询往往忽略并发性影响。不如改写成union方式。

索引对锁的影响

  • InnoDB 只有在访问行的时候才会对其加锁,而索引能够减少InnoDB访问的行数,从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行才有效。如果索引无法过滤掉无效的行,那么InnoDB检索到数据并返回给服务器层以后,MySQL服务器才能应用where子句。 image

注意: InnoDB在二级索引上使用共享(读)锁,但访问主键索引需要排他(写)锁。这消除了使用覆盖索引的可能性,并且使得 select for update 比lock in share mode 或非锁定查询要慢的多。(++现在还没理解这句话特么什么意思??? 求知道真相的告知++)

一般mysql 能够使用如下三种方式应用where条件:

  1. 在索引中使用where 条件来过滤不匹配的记录。存储引擎内完成的

  2. 使用索引覆盖扫描(在Extra列中出现了using index )来返回记录,直接从索引中过滤掉不需要的记录并返回命中结果。这是在mysql服务器完成的,但无需回表查询记录。

  3. 从数据表中返回数据,然后过滤掉不满足条件的记录(在Extra列中出现using where)这在mysql服务器层完成,先从数据表读出记录,然后过滤。


一个包含查询所需的字段的索引称为 covering index 覆盖索引。MySQL只需要通过索引就可以返回查询所需要的数据,而不必在查到索引之后进行回表操作,减少IO,提供效率。 当你对一个sql 使用explain statement 查看一个sql的执行计划时,在EXPLAIN的Extra列出现Using Index提示时,就说明该select查询使用了覆盖索引。


索引使用注意:

  • <,<=,=,>,>=,BETWEEN,IN 会使用索引

  • <>,not in ,!= 则不能够使用索引

  • WHERE index=1 OR A=10 也会是索引失效

  • 存了数值的字符串类型字段(如手机号),查询时记得不要丢掉值的引号

  • 查询条件中尽量使用常量,不要使用函数,也不要进行运算,否则无法使用索引
  • 例如: 以下不能使用索引
  • SELECT * FROM user WHERE DATE(created_at) = ‘2014-01-01’;
  • SELECT * FROM user WHERE (age-5) = 20;

like语句中的索引:

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

exists 使用方法:

执行原理是 a.首先执行一次外部查询 b.对于外部查询中的每一行分别执行一次子查询而且每次执行子查询时都会引用外部查询中当前行的值。 c.使用子查询的结果来确定外部查询的结果集。 建立索引的时候,要在内部建立,外部建立的索引是不起作用的。

not IN 和not Exists

如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not Exists都比not IN要快。

  • MySQL查询只使用一个索引,因此如果where子句中已经使用了索引,那么order by中的列不再会使用索引

总结:建立索引的一些基本原则

  1. 在有更多不同值得列上创建索引的选择性更好
  2. 尽可能将范围查询的列放在索引的后面,以便优化器能够使用更多的索引列
  3. 如果想重用索引而不是建立大量的组合索引,可以使用IN()方式来避免同时需要多个组合索引。(in() 等价于多个等值条件查询,是符合最左前缀原则的,后面的索引依然可以使用,但是对于范围条件来说,则组合索引就不能再继续使用后面的索引了) image


评论