美文网首页
MySQL索引

MySQL索引

作者: Lnstark | 来源:发表于2020-12-28 22:50 被阅读0次

索引主要是用来提高查询的性能,总的来说有三个优点:

  • 索引大大减少服务器需要扫描的数据量。
  • 索引可以帮助服务器避免排序和临时表。
  • 索引可以将随机IO变为顺序IO。

而当然使用索引的代价就是由于需要维护索引,修改、插入和删除的性能会受影响,另外还有需要一定的空间。

索引的类型

主要有B-Tree索引,Hash索引,全文索引和空间数据索引等。

1.B-Tree索引
B+Tree索引结构.png

结构如上图所示,B-Tree索引(技术上是B+Tree)是一种适合范围查询的结构,因为叶子页存储的数据是按一定顺序排的。父页的值存储着指向子页的指针。

最左匹配

  • 如果不是按照索引的最左列开始查找,则无法使用索引。
  • 不能跳过索引中的列
  • 如果查询中有某个列的范围查询,则其右边的列都无法使用索引优化查找。
2.哈希索引

哈希索引基于哈希表实现,需要所有索引列匹配才能有效。优点在于速度快。

缺点

  • 只包含哈希值和行指针,所以不能避免读取行,但是这点对性能影响不大。
  • 不能用于排序
  • 不支持部分索引匹配查找
  • 只支持等值匹配查找
  • 在哈希冲突较高的情况下,对性能会有很大影响,而且维护代价也很高。

我们也可以创建自定义哈希索引:比如url的查询,url一般比较长,如果直接创建索引会占用很大的空间,我们可以对url进行一次hash,再将hash后的值作为一个索引列url_hash来维护。查询的时候先匹配这一列,再匹配url列,这样可以提高性能。

3.全文索引

全文索引是一种特殊的索引,用于查找文本中的关键词。

4.空间数据索引(R-Tree)

MyISAM支持空间索引,作为地理数据存储,用MySQL的GIS相关函数来维护数据。但MySQL这方面不如PostgreSQL成熟。

索引的使用策略

1. 避免不走索引的情况
  • 遵守最左匹配原则
  • where后面的的查询列不要使用表达式,像:
select id form actor where actor_id + 1 = 5
  • 尽量避免前置模糊查询 like '%哈哈%'
  • 尽量避免in和not in, 可以用between和exists代替
  • 尽量避免or,可以用union代替
  • 尽量避免 !=或<>
2. 前缀索引

有时候字段过大的时候也可以采用前缀索引的方式,它类似手动创建哈希索引,都是创建一个相对短的字段作为索引列。

  • 索引的选择性
    不重复的索引值和数据表的记录总数的比值。范围是0~1,索引选择性越大,性能越好。如果索引的时候选择性很低,会很影响性能,例如在性别列建索引,可能比不建索引查询还慢。但是选择性高了,占用的空间也越大,所以建立前缀索引的话我们要选择一个合适的前缀长度。

创建前缀索引:

alter table city add key (city(7));
3. 聚簇索引

聚簇索引的数据放在索引的叶子页,所以一个表只能有一个。如果没有定义主键,InnoDB会选一个唯一非空索引代替。如果没有这样的索引,InnoDB会隐式创建一个主键作为聚簇索引。

优点:

  • 可以把数据放到一起,减少磁盘IO。
  • 比一般索引访问更快,不需要查2次。
  • 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值

缺点:

  • 插入速度依赖于插入顺序。如果不是按照主键的顺序加入,那么最好用OPTIMIZE TABLE命令组织一下表。
  • 更新聚簇索引列的代价很高。
  • 插入新行或者更新时,如果插入的页满了,会导致页分裂,同时造成空间碎片。
  • 二级索引会比较大,因为叶子节点存储了主键值;而且要查两次,会慢一点。

MyISAM和InnoDB数据分布的区别:

  • MyISAM的主键和其他索引在结构上没什么区别,数据插入时也不需要调整数据的存储顺序,叶子页直接存了数据的行指针。他的主键索引就是一个名为primary的唯一非空索引。可以认为MyISAM没有聚簇索引吧。
  • InnoDB的聚簇索引里数据的存储顺序就是索引列的顺序,所以插入时需要调整顺序。二级索引的叶子页存的是主键值,所以通过二级索引要查2次才能访问到数据。

聚簇索引的插入尽量按照索引的顺序(如自增id),如果使用UUID大量插入的话会很影响性能,原因有:1、得先从磁盘中读取写入的目标页。2、页分裂会导致大量数据移动,一次页分裂至少修改3个页。3、页分裂还会产生数据碎片。

4、使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果:通过排序操作;或者按索引顺序扫描;如果EXPLAIN出来的type列值为“index”,则说明MySQL使用了索引扫描来做排序。
只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且索引列的顺序方向(正序或倒序)都一致时,MySQL才能够使用索引来对结果排序。如果查询需要关联多张表,则只有当ORDER BY字句引用的字段全部为第一个表时,才能够使用索引做排序,ORDER BY子句和查找类型查询的限制是一样的:需要满足索引的最左前缀的要求;否则无法利用索引排序。
有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求,就是前导列为常量的时候。如果WHERE子句或JOIN子句中对这些列指定了常量,就可以“弥补”索引的不足。

5、其他
  • 覆盖索引:如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。它可以避免回表,提高性能。
  • 索引可以让查询锁定更少的行。

参考资料:《高性能MySQL》(第3版)

相关文章

  • MySQL索引及查询优化书目录

    MySQL索引的原理之索引目的 MySQL索引的原理之索引原理 MySQL索引的原理之索引的类型 MySQL索引的...

  • 高性能的索引策略

    MySQL查询基础-查询执行过程 MySQL聚簇索引 MySQL覆盖索引 MySQL索引扫描排序 MySQL冗余和...

  • MySQL索引的使用

    MySQL索引 MySQL索引可以快速提高MySQL的检索速度。索引分单列索引和组合索引单列索引:即一个索引只包含...

  • Mysql索引与锁

    本文以Mysql5.7为例测试。 1:mysql索引方法 Mysql的索引方法分为btree索引和hash索引。 ...

  • 索引(二)

    mysql索引的新手入门详解mysql索引之三:索引使用注意规则 索引(Index)是帮助 MySQL 高效获取数...

  • MySQL 索引分类

    MySQL索引的分类(根据数据结构) 索引的本质 MySQL官方对索引的定义为:索引(Index)是帮助MySQL...

  • MySQL--索引

    MySQL索引 查看索引 创建索引 创建唯一索引 创建主键索引 删除索引 删除主键 MySQL视图 创建视图 删除...

  • mysql索引

    索引 mysql索引的建立对于mysql的高效运行是很重要的,索引可以大大提高mysql的检索速度。索引分单列索引...

  • 5.2MySQL创建高性能索引考察点

    MySQL索引的基础和类型延伸:MySQL索引的创建原则延伸:MySQL索引的注意事项 索引的基础索引类似于书籍的...

  • MySql 数据查询优化

    1. MySQL索引类型: mysql的索引有5种:主键索引、普通索引、唯一索引、全文索引、聚合索引(多列索引)。...

网友评论

      本文标题:MySQL索引

      本文链接:https://www.haomeiwen.com/subject/uvxxoktx.html