深入理解MySQL系列之索引

索引

查找一条数据的过程

先看下InnoDB的逻辑存储结构:

  1. 表空间:可以看做是InnoDB存储引擎逻辑结构的最高层,所有的数据都存放在表空间中。默认有个共享表空间ibdata1。如果启用innodb_file_per_table参数,需要注意每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页,其他类的数据,如回滚信息、插入缓冲索引页、系统事务信息、二次写缓冲等还是存放在原来共享表空间中。

  2. 段:
    表空间是由各个段组成,常见的段有数据段、索引段、回滚段等。数据段即为B+树叶子节点(Leaf node segment),索引段即为B+树非叶子节点(Non-leaf node segment)

  3. 区:是由连续页组成的空间,在任何情况下每个区大小都为1MB。默认情况下,存储引擎页的大小为16KB,即一个区中一共有连续64个连续的页。而为保证页的连续性,InnoDB存储引擎一次从磁盘申请4-5个区。

  4. 页:
    页(也可以称块),是InnoDB磁盘管理的最小单位。默认每个页大小16KB。1.2x版本后也可以通过参数innodb_page_size设置为4k、8k、16k

深入理解MySQL系列之索引

如查一条数据:select * from user where id=5;

这里id是主键,我们通过这棵B+树来查找,首先会去找到根页,每张表的根页位置在表空间文件中是固定的;找到根页后通过二分查找法,定位到id=5的数据应该在指针P5指向的页中,那么进一步去page number=5的页中查找,同样通过二分查询法即可找到id=5的记录:

深入理解MySQL系列之索引

计算一棵B+树可以存放多少行数据

也可以通过命令查看InnoDB每页默认16KB:

show variables like 'innodb_page_size';

先计算非叶子节点, 假设主键ID为bigint类型,长度为8字节,而指针大小在InnoDB源码中设置为6字节,这样一共14字节

而一个页中能存放多少这样的单元,其实就代表有多少指针,即16384/14=1170。

那么可以算出一棵高度为2的B+树,能存放1170*16=18720条这样的数据记录。

根据同样的原理我们可以算出一个高度为3的B+树可以存放:1170117016=21902400条这样的记录。

所以在InnoDB中B+树高度一般为1-3层,它就能满足千万级的数据存储。

索引一些概念
  1. 聚簇索引(clustered index): 就是将索引和数据放到一起,找到索引也就找到了数据;如下图叶子节点存放一行所有数据。

深入理解MySQL系列之索引

  1. 辅助索引(Secondary Index或非聚簇索引): 就是将数据和索引分开,查找时需要先查找到索引,然后通过索引回表找到相应的数据。
    回表:先通过数据库索引扫描出数据所在的行,再通过行主键id取出索引中未提供的数据,即基于非主键索引的查询需要多扫描一棵索引树。

如下图,辅助索引查找后,会再回表到聚簇索引,最后找到数据。

深入理解MySQL系列之索引

InnoDB有且只有一个聚簇索引,而MyISAM中都是非聚簇索引。

  1. 联合索引:指对表上多个列进行索引。

联合索引的最左前缀匹配原则: 对多个字段同时建立的组合索引(有顺序,ABC,ACB是完全不同的两种联合索引) 以联合索引(a,b,c)为例,建立这样的索引相当于建立了索引a、ab、abc三个索引。另外组合索引实际还是一个索引,并非真的创建了多个索引,只是产生的效果等价于产生多个索引。

  1. 覆盖索引: 即从辅助索引中就可以得到查询的记录,而不需要查询聚簇索引中的记录。

使用覆盖好处:

  • 辅助索引不包含整行记录的所有信息,故其大小要远小于聚簇索引,减少大量IO操作。
  • 对某些统计(如count(id))并不会通过查询聚簇索引来进行统计,减少IO操作
  1. 唯一索引:以唯一列生成的索引,该列不允许有重复值,但允许有空值(NULL)

  2. 索引下推:MySQL 5.6引入了索引下推优化,可以在索引遍历过程中,对索引中包含的字段先做判断,过滤掉不符合条件的记录,减少回表字数。

为什么选B+树,而不是B树

B树不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少

指针少的情况下要保存大量数据,只能增加树的高度,导致IO操作变多,查询性能变低;

为什么InnoDB只有一个聚簇索引,而不将所有索引都使用聚簇索引?

因为聚簇索引是将索引和数据都存放在叶子节点中,如果所有的索引都用聚簇索引,则每一个索引都将保存一份数据,会造成数据的冗余,在数据量很大的情况下,这种数据冗余是很消耗资源的。

什么情况下会发生明明创建了索引,但是执行的时候并没有通过索引呢?

查询优化器。

一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪种方案,需要通过优化器进行选择,选择执行成本最低的方案。

优化过程大致如下:

  • 1、根据搜索条件,找出所有可能使用的索引
  • 2、计算全表扫描的代价
  • 3、计算使用不同索引执行查询的代价
  • 4、对比各种执行方案的代价,找出成本最低的那一个 。
索引的优缺点

索引的优点如下:

  • 1、唯一索引可以保证每一行数据的唯一性
  • 2、提高查询速度
  • 3、加速表与表的连接
  • 4、显著的减少查询中分组和排序的时间
  • 5、通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

索引的缺点如下:

  • 创建索引时,需要对表加锁,在锁表的同时,可能会影响到其他的数据操作
  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时,MySQL 不仅要保存数据,还要保存索引文件。
  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不算严重,但如果你在一个大表上创建了多种组合索引,且伴随大量数据量插入,索引文件大小也会快速膨胀。
  • 如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
  • 对于非常小的表,大部分情况下简单的全表扫描更高效。
使用索引时的注意事项

原则:
不应该

1、索引不是越多越好。索引太多,维护索引需要时间跟空间
2、 频繁更新的数据,不宜建索引。
3、数据量小的表没必要建立索引。

应该

1、重复率小的列建议生成索引。因为重复数据少,索引树查询更有效率,等价基数越大越好。
2、数据具有唯一性,建议生成唯一性索引。在数据库的层面,保证数据正确性
3、频繁group by、order by的列建议生成索引。可以大幅提高分组和排序效率
4、经常用于查询条件的字段建议生成索引。通过索引查询,速度更快

索引失效的场景

1、模糊搜索:左模糊或全模糊都会导致索引失效,比如'%a'和'%a%'。但是右模糊是可以利用索引的,比如'a%'
2、隐式类型转换:比如select * from t where name = xxx , name是字符串类型,但是没有加引号,所以是由MySQL隐式转换的,所以会让索引失效
3、当语句中带有or的时候:比如select * from t where name=‘sw’ or age=14
4、不符合联合索引的最左前缀匹配:(A,B,C)的联合索引,你只where了C或B或只有B,C

其他注意事项:

  1. 索引不会包含有 null 值的列,只要列中包含有 null值都将不会被包含在索引中。
  2. 使用短索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和 I/O 操作
  3. 索引列排序。查询只使用一个索引,因此如果 where 子句中已经使用了索引的话,那么 order by 中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
  4. 不要在列上进行运算,这将导致索引失效而进行全表扫描
  5. 不使用 not in 和 <> 操作,这不属于支持的范围查询条件,不会使用索引。

《MySQL技术内幕》
https://mp.weixin.qq.com/s/6j64s9W6ogs5Y8BbhhkgnA
https://mp.weixin.qq.com/s/KB73550tKpNccW-WKxT7-A
https://mp.weixin.qq.com/s/ovMx9Dv9NCFxSsFM98uYFw

发表评论

评论已关闭。

相关文章