优化mysql索引的方式(Mysql索引的创建和优化)

一、一条select语句

一条查询语句查询时,执行流程如下:

优化mysql索引的方式(Mysql索引的创建和优化)(1)

二、索引介绍

索引的优势是可以提高数据检索的效率,降低数据库的IO成本;可以通过索引列对数据进行排序,降低数据排序成本,降低了CPU消耗。其劣势是索引会占据磁盘空间,其虽然会提高查询效率,但是会降低更新表的效率。

索引可以分为聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等,如果没有特别说明,默认都是使用B 树结构的索引。

主键索引:

索引列中的值必须是唯一的,不允许有空值

alter table address add PRIMARY KEY(address_id);

普通索引:

Mysql中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值。

alter table address add index i_city(city_id);

唯一索引:

索引列中的值必须是唯一的,但是允许为空值

create UNIQUE INDEX index_city on address(city_id);

全文索引:

只能在文本类型CHAR、VARCHAR、TEXT类型字段上创建全文检索,字段长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文检索。但是在实际开发中很少使用,如果用到的话,可以使用solr,es等组件。

空间索引:

在Mysql5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型,Mysql在空间索引这方面遵循OpenGIS几何数据模型规则。

前缀索引:

在文本类型如CHAR、VACHAR、TEXT类型的列上创建索引时,可以指定索引列的长度,但是数值类型不能指定。

alter TABLE address add index idx_district(district(3));

按照列的数量可以有单列索引(索引中只有一个列)和组合索引(使用2个以上的字段创建索引);组合索引的使用,需要遵循最左原则;一般情况下,建议使用组合索引代替单列索引。

alter TABLE address add index ind_add2(address,address2);

查看索引:

show index from address;

删除索引:

drop index ind_add2 on address;

三、索引的数据结构

索引的数据结构,需要至少支持等值查询和范围查询这两种查询需求。

常用的数据结构有Hash表、二叉树、平衡二叉树、B树、B 树,对于数据结构的变化,可以使用https://www.cs.usfca.edu/~galles/visualization/Algorithms.html进行演示。

Hash表:

HashMap、TreeMap就是Hash表结构,以键值对存储,Hash表的查询效率很高,但是只限于等值查询,不支持范围查询,如果非要使用范围查询,只能全表扫描。

二叉树:

二叉树的特点:每个节点最多有两个分叉,左子树小于父节点,右子树大于父节点。

一个完美的二叉树(平衡二叉树),查找数据时,类似于二分查找,但是二叉树有可能变成一个单链表,那么就会变成全表扫描。

平衡二叉树:

平衡二叉树就是一个类似完美的二叉树,最大的特征就是左右两个子树的层级最多相差1。在插入或者删除数据时,通过左旋或者右旋保证该特点。

但是平衡二叉树也有一些问题:(1)时间复杂度和层高有关,树有多高就需要查询多少次,也就是需要多少次磁盘IO,这样就会导致性能变差;(2)平衡二叉树不支持范围的快速查询,因为查询需要从根节点多次遍历,查询效率不高。

B树:

Mysql的数据是存放在磁盘中的,查询数据时,需要将磁盘中的数据加载到内存中,磁盘的IO非常的耗时,所以我们就需要尽量少的进行磁盘IO,但是访问二叉树的每个节点都需要进行磁盘IO,那么就需要考虑降低树的高度。

假如Key为bigint=8字节,每个节点有两个指针分别指向左子树和右子树,每个指针为4个字节,那么一个节点就需要占用16个字节,但是Mysql的InnoDB存储引擎一次IO操作会读取一个页的数据,这一个页是16K,可以看到这个页上只放16个字节,利用率非常低。如果在一个数据页上全部放上数据,充分使用数据页,可以发现,每个数据页可以放1000个数据,那么两层的树就可以存放一百万的数据,三层的树就可以存放一百亿的数据,这就是B树。

B树的主要特点就是:(1)B树的节点中存储着多个元素,每个内节点有多个分叉;(2)节点中的元素包含key值和数据,节点中的键值从小到大,也就是说,所有的节点都存储数据;(3)父节点当中的元素不会出现在子节点中;(4)所有的叶子节点都处于同一层,叶子节点具有相同的深度,叶子节点之间没有指针链接。

优化mysql索引的方式(Mysql索引的创建和优化)(2)

B树查找一次数据,其实与平衡二叉树对比数据的次数一致,但是由于读取次数(IO次数)变少,因此性能提高,并且一般情况下,B树的高度2~3层就能满足大部分的应用场景,所以使用B树可以很好的提升查询的效率。

但是B树也有缺点:(1)B树不支持范围查找,如果需要范围查找,还是需要从根节点进行多次遍历,查询效率有待提高;(2)由于非叶子节点存储的有数据,因此节点可以存储的数据量变少,导致层高变高,因此就会导致查询时IO次数增多,影响查询效率。

B 树:

B 树与B树最大的区别就是非叶子节点不能存储数据,所有的数据都存储在叶子节点。那么就是说,所有的数据都存储在叶子节点,所以每次查找都需要检索到叶子节点才能查询到数据。因此B 树可以保证快速的范围查询。

四、Mysql索引实现MyISAM索引

MyISAM的数据文件和索引文件是分开存储的,MyISAM使用B 树构建索引树时,叶子节点中存储的key是索引列的值,数据为索引列所在行的磁盘地址。

优化mysql索引的方式(Mysql索引的创建和优化)(3)

如果是主键索引,那么就直接按照上图中的key进行等值或者范围查询,查到数据后,使用指针将数据查询到,同时会将索引节点缓存在Mysql缓存中,而数据缓存依赖于操作系统的缓存。

如果是辅助索引(非主键索引),其数据结构和主键索引的数据结构一样。由于数据不是唯一的,因此就算使用等值查询,也是需要使用key的范围查询。

InnoDB索引

每个InnoDB表都有一个聚簇索引,聚簇索引使用B 树构建,叶子节点存储的是整行记录。

InnoDB创建聚簇索引的规则:(1)如果表上定义的有主键,InnoDB将主键索引用作聚簇索引;(2)如果没有主键,则将第一个不为NULL的唯一索引作为聚簇索引;(3)如果没有不为空的唯一索引,InnoDB会使用一个6字节长整型隐式字段rowid字段构建聚簇索引,rowid会在插入数据时自增。

除了聚簇索引之外的索引都被称为辅助索引,在InnoDB中,辅助索引中的叶子节点存储的数据是该行的聚簇索引值,InnnoDB使用此主键值在聚簇索引中搜索行记录

聚簇索引(主键索引)

InnoDB的数据和索引存储在一个.ibd文件中,InnoDB的数据组织方式,是聚簇索引。

优化mysql索引的方式(Mysql索引的创建和优化)(4)

  由于主键索引中存储了行数据,索引使用InnoDB进行主键查询时,可以快速地获取行数据,不需要在去磁盘中获取,因此聚簇索引可以节省一次IO操作,从而提高查询效率。

优化mysql索引的方式(Mysql索引的创建和优化)(5)

辅助索引

由于辅助索引的数据存储的是聚簇索引的值,因此使用辅助索引进行查询时,当查到数据后,还需要再使用主键查询一次聚簇索引,因此会查询两次索引。流程如下所示:

优化mysql索引的方式(Mysql索引的创建和优化)(6)

组合索引

组合索引存储的key就是组合值,例如a、b、c三列作为组合索引,那么B 树的key首先按照a列进行排序,然后按照b列进行排序,最后按照c列进行排序。由于组合索引是辅助索引的一种,因此数据存储也是聚簇索引的值。如下图所示:

优化mysql索引的方式(Mysql索引的创建和优化)(7)

我们经常说的最左匹配原则就是和联合索引的索引存储结构、检索方式有关系,按照上面说的a、b、c三列说明,b列是在a列的值相同的情况下才排的顺序,如果a列不存在,b列的顺序就无从谈起,因此,必须要有a列,b列索引才能生效。因此当使用组合索引时,组合索引会从左匹配到适用范围查询(<、>、between、like)的那一列为止,后面的索引就不再有用,例如使用了a=1 and b <10 and c=11,由于b列使用了范围查询,因此c列的索引不会被使用。

那么对于组合索引的创建原则:

  • 频繁出现在where条件中的列,建议使用组合索引
  • 频繁出现的order by和group by语句中的列,建议按照顺序去创建组合索引;
  • 常出现在select中的列,也建议创建组合索引
覆盖索引

上面说到辅助索引中叶子节点存储的数据是主键索引的key,因此需要进行一次回表,但是如果要是索引中就包含了要查询的所有数据(例如abc三列是一个组合索引,select只查询ab两列的值),那么就不需要回表再使用主键key进行一次索引查询了,这种索引就叫做覆盖索引。

覆盖索引还是一种辅助索引,由于辅助索引存储的数据是主键索引的key,因此相比主键索引来说,存储的内容更小。因此覆盖索引也是一种很常见的优化手段。

索引条件下推ICP

举个例子,还是abc为组合索引,那么当使用查询条件 a = 1 and b < 10 and c = 5这样的条件查询时,由于最左匹配原则,同时又由于b列使用了范围查询,因此只有索引ab两列生效,其实InnoDB查询组合索引时就会将所有的a=1 and b < 10的数据查询出来,然后,是否开启ICP的差异就来了。

如果不开启ICP,那么InnoDB就会将所有查询到的数据使用主键索引key进行回表查询,然后将查询的结果全部返回给mysql的服务层,服务层再根据a = 1 and b < 10 and c = 5做一次过滤;

如果开启了ICP,那么InnoDB就会将所有的数据再使用c = 5做一次判断,将不满足条件的数据剔除,然后将满足条件的数据进行回表查询,然后返回给mysql服务层。

可以看到,如果存在a = 1 and b < 10 and c != 5的数据时,开启ICP会少进行回表操作,从而提高了效率。

总结:不使用ICP时,不满足最左前缀的索引条件的比较是在存储引擎中进行的,非索引条件的比较是在mysql服务层进行的;使用ICP时,所有的索引条件的比较都是在存储引擎中进行的,非索引条件的比较是在mysql服务层进行的。

五、索引创建原则哪些情况需要创建索引:
  • 频繁出现在where条件判断、order排序、group by分组
  • select频繁查询的列,其实这里可以考虑创建组合索引和覆盖索引
  • 多表join关联查询,on字段两边的字段都要创建索引
创建索引的一些建议:
  • 表记录很少的表不需要创建索引
  • 一个表的索引个数不能过多(会浪费空间;对数据进行变更时会变慢;太多的索引也会增加优化器的选择时间)
  • 频繁更新的字段不建议作为索引
  • 区分度低的字段,不要做索引
  • 在InnoDB中,主键索引建议使用自增的长整型,避免使用很长的字段(主键索引一个页节点是16K,主键字段越长,一个页存储的数据就越少,就会导致层高变高,IO增多)
  • 不建议用无序的值作为索引,例如UUID(更新数据时,会发生频繁的页分裂,页内数据不紧凑,浪费磁盘空间)
  • 尽量创建组合索引,而不是单列索引(节省空间,最好可以创建覆盖索引)

来源:https://www.cnblogs.com/liconglong/p/14482311.html

“做程序员,圈子和学习最重要”因为有有了圈子可以让你少走弯路,扩宽人脉,扩展思路,学习他人的一些经验及学习方法!同时在这分享一下是一直以来整理的Java后端进阶笔记文档和学习资料免费分享给大家!需要资料的朋友私信我扣【06】

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页