mysql索引

mysql 索引

mysql的索引可以分为几类:

  • 聚集索引和非聚集索引
  • 唯一索引和普通索引
  • 单列索引和组合索引
  • 全文索引(仅MyISAM支持)
  • 空间索引(仅MyISAM支持)

聚集索引和非聚集索引

聚集索引(InnoDB)

按照每张表的主键构造一颗【顺序】B+树,同时叶子节点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样,每个数据页都通过一个双向链表来进行链接;由于实际的数据页只能按照一颗B+树进行排序,因此每张表只能拥有一个聚集索引。在多数情况下,查询优化器倾向于采用聚集索引。对我们的启示:

  • 所以通过主键查找的速度是最快的,先扫描到索引主键,再扫描主键对应的列值,两次IO就可以搞定;
  • 每张表都必须创建自增的主键,默认是随机生成6字节主键(隐含的),且无序;
  • Sql优化中消除性能瓶颈的最好方式就是向主键靠拢,主键不行可以考虑其他重复率低的索引;

在使用InnoDB存储引擎时,如果没有特别的需要,请永远使用一个与业务无关的自增字段作为主键。InnoDB使用聚集索引,数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)。

如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置。

此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。

非聚集索引

按照索引(非主键)【顺序】创建B+树,但此时的叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉innodb存储引擎哪里可以找到与索引相对应的行数据。当通过辅助索引来寻找数据时,innodb存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后再通过主键索引来找到一个完整的行记录。

唯一索引和普通索引

普通索引是mysql中的基本索引类型,允许在定义索引的列中插入空值和重复值。
唯一索引,索引的值必须唯一,允许有空值。主键索引是特殊的唯一索引,不允许有空值。

单列索引和组合索引

单列索引即一个索引只包含单个列,而组合索引指表的多个字段组合上创建索引。

索引的最左前缀原理

  • 按照索引中所有列进行精确匹配(这里精确匹配指“=”或“IN”匹配)时,索引可以被用到。这里有一点需要注意,理论上索引对顺序是敏感的,但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引,例如我们将where中的条件顺序颠倒,结果是一样的。
  • 当查询条件精确匹配索引的左边连续一个或几个列时,又或者,查询条件用到了索引中列的精确匹配,但是中间某个条件未提供,索引可以被用到,但是只能用到一部分,即条件所组成的最左前缀。
  • 查询条件没有指定索引第一列,这样的查询显然用不到索引。
  • 如果查询条件中含有函数或表达式,则MySQL不会为这列使用索引。
  • 范围列可以用到索引(必须是最左前缀),但是范围列后面的列无法用到索引。同时,索引最多用于一个范围列,因此如果查询条件中有两个范围列则无法全用到索引。
  • 匹配某列的前缀字符串,如果通配符%不出现在开头,则可以用到索引,但根据具体情况不同可能只会用其中一个前缀。例如,SELECT * FROM employees.titles WHERE title LIKE 'Senior%';

索引选择性与前缀索引

既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好。一般两种情况下不建议建索引。

第一种情况是表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了。至于多少条记录才算多,这个个人有个人的看法,我个人的经验是以2000作为分界线,记录数不超过 2000可以考虑不建索引,超过2000条可以酌情考虑索引。

另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:Index Selectivity = Cardinality / #T,显然选择性的取值范围为(0, 1],选择性越高的索引价值越大,这是由B+Tree的性质决定的。

有一种与索引选择性有关的索引优化策略叫做前缀索引,就是用列的前缀代替整个列作为索引key,当前缀长度合适时,可以做到既使得前缀索引的选择性接近全列索引,同时因为索引key变短而减少了索引文件的大小和维护开销。

下面是一个选择长度的例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ select count(distinct city) / count(*) from city_demo;
+---------------------------------+
| count(distinct city) / count(*) |
+---------------------------------+
| 0.4283 |
+---------------------------------+

$ 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
-> from city_demo;
+--------+--------+--------+--------+
| sel3 | sel4 | sel5 | sel6 |
+--------+--------+--------+--------+
| 0.3367 | 0.4075 | 0.4208 | 0.4267 |
+--------+--------+--------+--------+

-- 可以看见当索引前缀为6时的基数是0.4267,已经接近完整列选择性0.4283。
alter table city_demo add key (city(6));
-------------本文结束感谢您的阅读-------------
坚持分享,您的支持将鼓励我继续创作!
0%