MySQL索引

索引

索引的出现其实就是为了提高数据查询的效率,就像书的目录一样

索引常见的模型
  1. 哈希表,这种结构适用于只有等值查询的场景
  2. 有序数组,等值查询和范围查询场景中的性能就都非常优秀,但更新比较慢。有序数组索引只适用于静态存储引擎
  3. 树。

树可以有二叉,也可以有多叉。多叉树就是每个节点有多个儿子,儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的,但是实际上大多数的数据库存储却并不使用二叉树。其原因是,索引不止存在内存中,还要写到磁盘上。你可以想象一下一棵100万节点的平衡二叉树,树高20。一次查询可能需要访问20个数据块。在机械硬盘时代,从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说,对于一个100万行的表,如果使用二叉树来存储,单独访问一个行可能需要20个10 ms的时间,这个查询可真够慢的。为了让一个查询尽量少地读磁盘,就必须让查询过程访问尽量少的数据块。那么,我们就不应该使用二叉树,而是要使用“N叉”树。这里,“N叉”树中的“N”取决于数据块的大小。

树可以有二叉,也可以有多叉。多叉树就是每个节点有多个儿子,儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的,但是实际上大多数的数据库存储却并不使用二叉树。其原因是,索引不止存在内存中,还要写到磁盘上。你可以想象一下一棵100万节点的平衡二叉树,树高20。一次查询可能需要访问20个数据块。在机械硬盘时代,从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说,对于一个100万行的表,如果使用二叉树来存储,单独访问一个行可能需要20个10 ms的时间,这个查询可真够慢的。为了让一个查询尽量少地读磁盘,就必须让查询过程访问尽量少的数据块。那么,我们就不应该使用二叉树,而是要使用“N叉”树。这里,“N叉”树中的“N”取决于数据块的大小。

N叉树由于在读写上的性能优点,以及适配磁盘的访问模式,已经被广泛应用在数据库引擎中了。

Innodb 的索引模型

在InnoDB中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。又因为前面我们提到的,InnoDB使用了B+树索引模型,所以数据都是存储在B+树中的。

每一个索引在InnoDB里面对应一棵B+树。

索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。

非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。

基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

索引维护
  • 由于B+树为了维护索引的有序性,在插入新值的时候需要做必要的维护。会涉及到数据的挪动。而且如果当前数据页已经满的话,会新建一个数据页,把部分数据挪过去。这个过程称为页分裂。会对性能造成影响
  • 当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。

基于上面的案例,一般要求我们在建表的时候要有自增主键.也就是说,每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。

除了考虑性能外,我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段,比如字符串类型的身份证号,那应该用身份证号做主键,还是用自增字段做主键呢?

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键,那么每个二级索引的叶子节点占用约20个字节,而如果用整型做主键,则只要4个字节,如果是长整型(bigint)则是8个字节。

显然,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。

所以,从性能和存储空间方面考量,自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的呢?还是有的。比如,有些业务的场景需求是这样的:

  1. 只有一个索引;
  2. 该索引必须是唯一索引。

你一定看出来了,这就是典型的KV场景。

由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小的问题。

这时候我们就要优先考虑“尽量使用主键查询”原则,直接将这个索引设置为主键,可以避免每次查询需要搜索两棵树。

数据页的合并

当从InnoDB表删除数据时,相应的数据是先打上删除标签(deleted mark),而后再由purge线程执行清理工作。

清理工作结束后,如果两个相邻的数据页存储填充率低于一定程度,就会尝试合并页,以降低碎片率,提高存储效率。

或者经过多次长度变小的UPDATE操作后(将varchar列长度更新变短),数据页填充率低于一定程度也会尝试合并。

合并完毕之后,空出来的页就会被标记为空闲页,等待再分配。这个工作是InnoDB后台线程自动完成的,无需人为干预、控制。

  • 合并数据页,是通过参数 MERGE_THRESHOLD的值确定的,当InnoDB数据页填充率低于该阈值时,就会尝试进行合并页操作。该参数的默认值是50,最小值是1,在5.6版本之后允许自行指定设置,在5.6之前的版本中则是被硬编码的,无法修改.

  • 默认的是当发现两个相邻页的填充率都低于50%时,就会尝试进行合并。

  • 除了表可以设置外,单个索引也可以设置合并阙值.这个只能在创建索引时一次性指定,不能中途修改

    ALTER TABLE t_sk ADD INDEX k1(c1) COMMENT 'MERGE_THRESHOLD=20';
    
  • 页合并的统计情况,可以通过查询 INNODB_METRICS 表获取到。

  • alter table T engine=InnoDB #重建索引
    
覆盖索引

正常的根据索引进行查询,会有回表的过程。即查询两次。

而覆盖索引可以减少树的搜索次数,显著提提升查询性能,所以使用覆盖所以是一个常用的性能优化。

select * from T where k between 3 and 5 #会回表查询
select ID from T where k between 3 and 5 # 普通索引记录了主键的值,直接返回结果


联合索引

联合索引也属于覆盖索引的一种

比如我们在市民信息表中,建立一个(身份证、姓名)的联合索引。当用户根据身份证来查询他的姓名时,这个索引就起到了覆盖索引的效果。它可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间。

当然这种索引的维护是有代价的,它是以空间来换时间。

最左前缀原则

通过最左匹配原则,可以减少索引建立的次数.比如(a,b,c) 实际建立了(a,b,c),(a),(a,b)三个索引

注意事项:

  • 遇到范围查询(>,<,between,like)会停止
  • where中and条件的先后顺序对如何选择索引是无关的。因为优化器会去分析判断选用哪个索引。
  • 部分值匹配时,只要条件中有最左索引项就会用到索引
  • 条件中没有最左索引,不会用到索引,全表扫描
  • like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效。
  • or语句前后没有同时使用索引。当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效
  • 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描。
  • 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的,所以这样的操作不能使用索引。
  • 在索引字段上使用not,<>,!=。不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描
  • 对索引字段进行计算操作、字段上使用函数
  • 当全表扫描速度比索引速度快时,mysql会使用全表扫描,此时索引失效。
索引下推
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

这个语句,只会使用到name 这个索引

在MySQL 5.6之前,只能开始一个个回表。到主键索引上找出数据行,再对比字段值。

而MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL索引是一种数据结构,可以帮助MySQL快速定位和访问表中的数据。使用索引可以提高查询效率,降低数据库的负载。下面是MySQL索引的一些基本概念和使用方法: 1. 索引类型 MySQL支持多种类型的索引,包括B树索引、哈希索引、全文索引等。其中,B树索引是最常用的一种,也是默认的索引类型。B树索引可以用于精确匹配和范围查询,而哈希索引主要用于等值查询,全文索引则用于文本检索。 2. 索引创建 可以在创建表时指定索引,例如: ``` CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50), INDEX idx_email (email) ); ``` 也可以在已有的表上添加索引,例如: ``` ALTER TABLE users ADD INDEX idx_name (name); ``` 3. 索引使用 查询语句中可以使用WHERE子句和ORDER BY子句来利用索引,例如: ``` SELECT * FROM users WHERE email = 'example@example.com'; SELECT * FROM users WHERE name LIKE 'John%' ORDER BY id DESC; ``` 需要注意的是,索引并不是越多越好,过多的索引会占用过多的磁盘空间并降低写操作的性能。因此,需要根据实际情况选择合适的索引。同时,还需要定期对索引进行维护,包括优化查询语句、删除不必要的索引等。 4. 索引优化 MySQL提供了一些工具来优化索引,例如EXPLAIN命令可以帮助分析查询语句的执行计划,找出慢查询和不必要的全表扫描。可以使用OPTIMIZE TABLE命令来优化表的索引和碎片,从而提高查询性能。还可以使用缓存来避免频繁的查询操作,例如使用Memcached或Redis等缓存工具。 以上就是MySQL索引的一些基本概念和使用方法,需要根据实际情况进行选择和优化。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值