MySQL中索引基础知识及使用规则

目录

一.InnoDB索引

InnoDB支持以下几种索引:

  • B+树索引
  • 全文索引
  • 哈希索引

本文将着重介绍B+树索引。其他两个全文索引和哈希索引只是做简单介绍一笔带过。

哈希索引是自适应的,也就是说这个不能人为干预在一张表生成哈希索引,InnoDB会根据这张表的使用情况来自动生成。

全文索引是将存在数据库的整本书的任意内容信息查找出来的技术,InnoDB从1.2.x版本支持。每张表只能有一个全文检索的索引。

B+树索引是传统意义上的索引,B+树索引并不能根据键值找到具体的行数据,B+树索引只能找到行数据所在的页,然后通过把页读到内存,再在内存中查找到行数据。B+树索引也是最常用的最为频繁使用的索引。

二.什么是B+树

前提

  • 叶子节点: 没有子节点的节点

  • 非叶子节点: 有子节点的节点

概念

  B+树是一种平衡查找树,其实先想想看为什么要用平衡查找树,不用二叉树?普通的二叉树可能因为插入的数据最后变成一个很长的链表,怎么能提高搜索的速度呢?你可以想想,为什么HashMap和ConcurrentHashMap在JDK8的时候,当链表大于8的时候把链表转成红黑树(红黑树也是平衡查找树)。技术思维是想通的,那么答案无非是加快速度,性能咯。

一个B+树有以下特征:

  • 有n个子树的中间节点包含n个元素,每个元素不保存数据,只用来索引,所有数据都保存在叶子节点。
  • 所有叶子节点包含元素的信息以及指向记录的指针,且叶子节点按关键字自小到大顺序链接。
  • 所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素。

  那么我们先来看一个B+树的图

所有的数据都在叶子节点,且每一个叶子节点都带有指向下一个节点的指针,形成了一个有序的链表。为什么要有序呢?其实是为了范围查询。比如说select * from Table where id > 1 and id < 100; 当找到1后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率。是不是范围查询的话hash就搞不定这个事情了?以下为B+树的优势:

  • 单一节点存储更多元素,减少IO
  • 所有查询都要找到叶子节点,查询稳定
  • 所有叶子节点形成有序链表,方便范围查询

一般性情况,数据库的B+树的高度一般在2~4层,这就是说找到某一键值的行记录最多需要2到4次逻辑IO,相当于0.02到0.04s。

三.聚集索引和辅助索引

聚集索引(聚簇索引)

  聚集索引是按表的主键构造的B+树,叶子节点存放的为整张表的行记录数据,每张表只能有一个聚集索引。优化器更倾向采用聚集索引。因为直接就能获取行数据。

  请选择自增id来做主键,不要非空UK列。避免大量分页碎片。下面来看一个聚集索引的图:

​ 那么很简单了,每个叶子节点,都存有完整的行记录。对于主键的查找速度那是相当的快,美滋滋。

辅助索引

  辅助索引也叫非聚集索引,叶子节点除了键值以外还包含了一个bookmark,用来告诉InnoDB在哪里可以找到对应的行数据,InnoDB的辅助索引的bookmark就是相对应行数据的聚集索引键。也就是先获取指向主键索引的主键,然后通过主键索引来找到一个完整的行。如果辅助索引的树和聚集索引的树的高度都是3,如果不是走主键索引走辅助索引的话,那么需要6次逻辑IO访问得到最终的数据页。辅助索引和聚集索引的概念关系图如下:

基于主键索引和普通索引的查询有什么区别?

  • 如果语句是 select * from T where ID=500,即主键查询方式,则只需要搜索 ID 这棵 B+ 树;
  • 如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引 树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程称为回表。

四.索引实战

设计索引

  设计索引的时候,无论是组合索引还是普通索引等。一般经验是,选择经常被用来过滤记录的字段,高选择性,高区分性。别把性别字段设计索引,性别属于低选择性的。你可以选择名字嘛,你好我大名叫苗嘉杏:)

  知道加索引快,但是也别乱加索引,插入以及更新索引的操作InnoDB都会维护B+树的,多加很多索引只会导致效率降低!

  不要用重复的索引,比如有个联合索引是a,b,你又整个a列的普通索引。那不是搞事么?

  不要在索引上用函数和like

一颗聚集索引B+树可以放多少行数据?

  这里我们先假设B+树高为2,即存在一个根节点和若干个叶子节点,那么这棵B+树的存放总记录数为:根节点指针数*单个叶子节点记录行数。假设一行记录的数据大小为1k,那么单个叶子节点(页)中的记录数=16K/1K=16。

  那么现在我们需要计算出非叶子节点能存放多少指针,我们假设主键ID为bigint类型,长度为8字节,而指针大小在InnoDB源码中设置为6字节,这样一共14字节,我们一个页中能存放多少这样的单元,其实就代表有多少指针,页大小默认16K,即16kb/14b=1170。那么可以算出一棵高度为2的B+树,大概能存放1170*16=18720条这样的数据记录。

  根据同样的原理我们可以算出一个高度为3的B+树大概可以存放:1170*1170*16=21902400行数据。所以在InnoDB中B+树高度一般为1-3层,它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO,所以通过主键索引查询通常只需要1-3次逻辑IO操作即可查找到数据。

Cardinality值

  如何判断一个索引建立的是否好呢?可以用show index from指令查看Cardinality值,这个值是一个预估值,而不是一个准确值。每次对Cardinality值的统计都是随机取8个叶子节点得到的。

  对于innodb来说,达到以下2点就会重新计算cardinality

  • 如果表中1/16的数据发生变化
  • 如果stat_modified_counter>200 000 0000

  实际应用中,(Cardinality/行数)应该尽量接近1。如果非常小则要考虑是否需要此索引。实战一下,比如有一张表,我们来show index一下

mysql> show index from Order;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Order   |          0 | PRIMARY          |            1 | id          | A         |       99552 |     NULL | NULL   |      | BTREE      |         |               |
| Order   |          1 | IDX_orderId      |            1 | orderId     | A         |       96697 |     NULL | NULL   |      | BTREE      |         |               |
| Order   |          1 | IDX_productId    |            1 | productId   | A         |          52 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
rows in set (0.00 sec)

那么可以看到IDX_productId这个索引的Cardinality比较低。 

需要强制刷新Cardinality值的话可以用:

analyze local table xxx;

五.索引操作与规则

重建索引

重建普通索引

alter table T drop index k;

alter table T add index(k);

重建主键索引

可行:

	alter table T engine=InnoDB 

不可行:

	alter table T drop primary key;
	alter table T add primary key(id);

覆盖索引

ID为主键索引,k为普通索引.

如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的 值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是 说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引。

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

最左前缀原则

B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位 记录。

(name,age,sex)利用最左前缀可以实现以下的索引(name)(name,age)(name,age,sex))

在建立联合索引的时候,如何安 排索引内的字段顺序。

第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

第二,考虑的原则就是空间

	比如:name 字段是比 age 字段大的 ,那我就建议你创建一个(name,age) 的联合索引和一个 (age) 的单字段 索引。这样比(age,name)(name)占用空间少

索引下推

联合索引(name, age)为例

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

已经知道了最左前缀索引规则,所以这个语句在搜索索引树的时候,只能用索引name来搜索 “张”,age是没法用的,因为'zhang%'查询的是一个范围.

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

用索引和用索引快速定位却别

前提

  • 表格260万数据
  • id自增主键
  • Account普通索引
  • (Account,Cmd)联合索引
  • 其实按照最左前缀原则Account普通索引与(Account,Cmd)联合索引,只保留(Account,Cmd)联合索引即可,因为通过(Account,Cmd)联合索引也可以对Account进行索引. 此处都保留是想测试,如果两者都存在的某些情况下,优化器如何选择

EXPLAIN SELECT id from mt4order WHERE Account like '1';

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEmt4orderNULLrangeaccount索引,account_cmd索引account索引768NULL1100.00Using where; Using index

EXPLAIN SELECT id from mt4order WHERE Account like '1%';

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEmt4orderNULLrangeaccount索引,account_cmd索引account索引768NULL1716Using where; Using index,

EXPLAIN SELECT id from mt4order WHERE Account like '%1%';

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEmt4orderNULLindexNULLaccount索引768NULL264981411.11Using where; Using index

EXPLAIN SELECT id from mt4order WHERE Account like '%1';

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEmt4orderNULLindexNULLaccount索引768NULL264981411.11Using where; Using index

总结:

  1. 第一个使用account索引快速定位到一行数据,extra为Using where; Using index,说明优先使用索引中覆盖索引获取了id信息,避免了回表(使用索引,并且使用索引快速查找)
  2. 第二个使用account索引快速定位,但是因为后面包含一个%,所以按照最左前缀原则,对'1%'中的'1'进行索引快速查找,查询了1716行数据,extra为Using where; Using index,说明优先使用索引中覆盖索引获取了id信息,避免了回表 (使用索引,并且使用索引快速查找)
  3. 第三个有使用account索引,因为是'%1%'是范围查找,所以在account索引树上进行了全面的查找,扫描了2649814行数据,extra中为Using where; Using index,此时只是使用了索引和覆盖索引避免了回表,但是没有使用索引快速定位查找,因为基于account索引扫描了全部的行(使用索引,没有使用索引快速查找)
  4. 第四个有使用account索引,因为是'%1'是范围查找,所以在account索引树上进行了全面的查找,扫描了2649814行数据,extra中为Using where; Using index,此时只是使用了索引和覆盖索引避免了回表,但是没有使用索引快速定位查找,因为基于account索引扫描了全部的行(使用索引,没有使用索引快速查找)

六.普通索引和唯一索引如何选择

前提条件

普通索引和唯一索引下的查询

  • 执行查询的语句是 select id from T where k=5。

  • 对于普通索引来说,查找到满足条件的第一个记录 (5,500) 后,需要查找下一个记录,直到碰到第一个不满足 k=5 条件的记录。

  • 对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。

  • 这个不同带来的性能差距会有多少呢?答案是,微乎其微。

  • InnoDB 的数据是按数据页为单位来读写的。也就是说,当需要读一条记录的 时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将其整体读入内存。在 InnoDB 中,每个数据页的大小默认是 16KB。

  • 所以说,当找到 k=5 的记录的时候,它所在的数据页就都在内存 里了。那么,对于普通索引来说,要多做的那一次“查找和判断下一条记录”的操作,就 只需要一次指针寻找和一次计算。

  • 如果 k=5 这个记录刚好是这个数据页的最后一个记录,那么要取下一个记录,必须 读取下一个数据页,这个操作会稍微复杂一些。但是,我们之前计算过,对于整型字段,一个数据页可以放近千个 key,因此出现这种情 况的概率会很低。

  • 所以,我们计算平均性能差异时,仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

普通索引和唯一索引下的更新

change buffer

  • 当需要更新一个数据页时,如果数据页在内存中就直接更新,
  • 而如果这个数据页还没有在 内存中的话,在不影响数据一致性的前提下,InooDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。
  • 在下次查询需要访问这个数据页的 时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge。除了访 问这个数据页会触发 merge 外,系统有后台线程会定期 merge。在数据库正常关闭 (shutdown)的过程中,也会执行 merge 操作。
  • 虽然名字叫作 change buffer,实际上它是可以持久化的数据。也就是 说,change buffer 在内存中有拷贝,也会被写入到磁盘上。
  • 显然,如果能够将更新操作先记录在 change buffer,减少读磁盘,语句的执行速度会得 到明显的提升。而且,数据读入内存是需要占用 buffer pool 的,所以这种方式还能够避 免占用内存,提高内存利用率。

使用change buffer

  • 只有普通索引才能使用change buffer,唯一索引不能使用

  • change buffer 用的是 buffer pool 里的内存,因此不能无限增大。

  • change buffer 的大 小,可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候,表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

更新流程

  • 第一种情况是,这个记录要更新的目标页在内存中
    • 对于唯一索引来说,找到 3 和 5 之间的位置,判断到没有冲突,插入这个值,语句执行结束;
    • 对于普通索引来说,找到 3 和 5 之间的位置,插入这个值,语句执行结束。
    • 普通索引和唯一索引对更新语句性能影响的差别,只是一个判断,只会耗费微 小的 CPU 时间。
  • 第二种情况是,这个记录要更新的目标页不在内存中
    • 对于唯一索引来说,需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;
    • 对于普通索引来说,则是将更新记录在 change buffer,语句执行就结束了。
    • 将数据从磁盘读入内存涉及随机 IO 的访问,是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问,所以对更新性能的提升是会很明显的。

change buffer使用场景

  • 对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
  • 反过来,假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更 新先记录在 change buffer,但之后由于马上要访问这个数据页,会立即触发 merge 过 程。这样随机访问 IO 的次数不会减少,反而增加了 change buffer 的维护代价。所以, 对于这种业务模式来说,change buffer 反而起到了副作用。
  • 如果所有的更新后面,都马上伴随着对这个记录的查询,那么你应该关闭 change buffer。
  • 在使用机械硬盘时,尽量使用普通索引,然后把 change buffer 尽量开大,以确保这个“历 史数据”表的数据写入速度。

change buffer和 redo log

插入过程

  • 假设: 当前 k 索引树的状态,查找到位置后,k1 所在的数据页在内存 (InnoDB buffer pool) 中,k2 所在的数据页不在内存中

  • 在上面表的前提下执行下面语句: mysql> insert into t(id,k) values(id1,k1),(id2,k2);

  • 分析这条更新语句,你会发现它涉及了四个部分:内存、redo log(ib_log_fileX)、 数据表空间(t.ibd)、系统表空间(ibdata1)。

  • 这条更新语句做了如下的操作(按照图中的数字顺序):

    • Page 1 在内存中,直接更新内存;
    1. Page 2 没有在内存中,就在内存的 change buffer 区域,记录下“我要往 Page 2 插
      入一行”这个信息
    2. 将上述两个动作记入 redo log 中(图中 3 和 4)。
  • 做完上面这些,事务就可以完成了。

  • 所以,你会看到,执行这条更新语句的成本很低,就是写了两处内存,然后写了一处磁盘(两次操作合在一起写了一次磁盘,redo log),而且还是顺序写的。

  • 同时,图中的两个虚线箭头,是后台操作,不影响更新的响应时间。

查询过程

  • 如果读语句发生在更新语句后不久,内存中的数据都还在,那么此时的这两个读操作就与 系统表空间(ibdata1)和 redo log(ib_log_fileX)无关了。所以,我在图中就没画出 这两部分。

  • 要执行 select * from t where k in (k1, k2)

  • 读 Page 1 的时候,直接从内存返回。有几位同学在前面文章的评论中问到,WAL 之后 如果读数据,是不是一定要读盘,是不是一定要从 redo log 里面把数据更新以后才可以返回?其实是不用的。你可以看一下图 3 的这个状态,虽然磁盘上还是之前的数据, 但是这里直接从内存返回结果,结果是正确的。

  • 要读 Page 2 的时候,需要把 Page 2 从磁盘读入内存中,然后应用 change buffer 里 面的操作日志,生成一个正确的版本并返回结果。

  • 可以看到,直到需要读 Page 2 的时候,这个数据页才会被读入内存。

  • redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘 的 IO 消耗。

    七.给字符串加索引

字符串字段增加索引的方式

  1. 直接创建完整索引,这样可能比较占用空间;
  2. 创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;
  3. 倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题,不能使用覆盖索引,不支持范围扫描;
  4. 创建 hash 字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,不能使用覆盖索引,都不支持范围扫描。

第一二种分析

完整索引和前缀索引的分析

你现在维护一个支持邮箱登录的系统,用户表是这么定义的:

mysql> create table SUser(
 ID bigint unsigned primary key, 
 email varchar(64),
 ...
 )engine=innodb;

由于要使用邮箱登录,所以业务代码中一定会出现类似于这样的语句:

mysql> select f1, f2 from SUser where email='xxx';

分别创建两种索引

mysql> alter table SUser add index index1(email); 
或
mysql> alter table SUser add index index2(email(6));

第一个语句创建的 index1 索引里面,包含了每个记录的整个字符串;
而第二个语句创建 的 index2 索引里面,对于每个记录都是只取前 6 个字节。
占用的空间会更小,这就是使用前缀索引的优势

加入执行下面的sql语句,两种索引该如何执行

select id,name,email from SUser where email='zhangssxyz@xxx.com';

完整索引

前缀索引

  • 从 index2 索引树找到满足索引值是’zhangs’的记录,找到的第一个是 ID1;
  • 到主键上查到主键值是 ID1 的行,判断出 email 的值不 是’zhangssxyz@xxx.com’,这行记录丢弃;
  • 取 index2 上刚刚查到的位置的下一条记录,发现仍然是’zhangs’,取出 ID2,再到 ID 索引上取整行然后判断,这次值对了,将这行记录加入结果集;
  • 重复上一步,直到在 idxe2 上取到的值不是’zhangs’时,循环结束。
  • 在这个过程中,要回主键索引取 4 次数据,也就是扫描了 4 行。

对比结果

  • 通过这个对比,你很容易就可以发现,使用前缀索引后,可能会导致查询语句读数据的次数变多。
  • 但是,对于这个查询语句来说,如果你定义的 index2 不是 email(6) 而是 email(7),也 就是说取 email 字段的前 7 个字节来构建索引的话,即满足前缀’zhangss’的记录只有 一个,也能够直接查到 ID2,只扫描一行就结束了。
  • 也就是说使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查 询成本。

使用前缀索引,如何确定应该使用多长的前缀

  • 我们在建立索引时关注的是区分度,区分度越高越好。因为区分度越高,意味着重复的键值越少。

  • 依次选取不同长度的前缀来看这个值,比如我们要看一下 4~7 个字节的前缀索引, 可以用这个语句:

    mysql> select
      count(distinct left(email,4))as L4, 
      count(distinct left(email,5))as L5, 
      count(distinct left(email,6))as L6, 
      count(distinct left(email,7))as L7,
    from SUser;
    
  • 使用前缀索引很可能会损失区分度,所以你需要预先设定一个可以接受的损失比 例,比如 5%。然后,在返回的 L4~L7 中,找出不小于 L * 95% 的值,假设这里 L6、L7 都满足,你就可以选择前缀长度为 6。

前缀索引对覆盖索引的影响

select id,email from SUser where email='zhangssxyz@xxx.com';

如果使用 index1(即 email 整个字符串的索引结构)的话,可以利用覆盖索引, 从 index1 查到结果后直接就返回了,不需要回到 ID 索引再去查一次。而如果使用 index2(即 email(6) 索引结构)的话,就不得不回到 ID 索引再去判断 email 字段的值。
即使你将 index2 的定义修改为 email(18) 的前缀索引,这时候虽然 index2 已经包含了 所有的信息,但 InnoDB 还是要回到 id 索引再查一下,因为系统并不确定前缀索引的定义 是否截断了完整信息。

结论: 前缀索引无法使用覆盖索引

其他方式使用前缀索引

比如,我们国家的身份证号,一共 18 位,其中前 6 位是地址码,所以同一个县的人的身 份证号前 6 位一般会是相同的。

假设你维护的数据库是一个市的公民信息系统,这时候如果对身份证号做长度为 6 的前缀 索引的话,这个索引的区分度就非常低了。

方法三: 使用倒序存储

  • 如果你存储身份证号的时候把它倒过来存,每次查询的时 候,你可以这么写:

    mysql> select field_list from t where id_card = reverse('input_id_card_string');
    
  • 由于身份证号的最后 6 位没有地址码这样的重复逻辑,所以最后这 6 位很可能就提供了足 够的区分度。当然了,实践中你不要忘记使用 count(distinct) 方法去做个验证。

方法四: 使用 hash 字段

  • 可以在表上再创建一个整数字段,来保存身份证的校验 码,同时在这个字段上创建索引。

    mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
    
  • 然后每次插入新记录的时候,都同时用 crc32() 这个函数得到校验码填到这个新字段。

  • 由于校验码可能存在冲突,也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的,所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

    mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='****'
    
  • 这样,索引的长度变成了 4 个字节,比原来小了很多。

第三种和第四种的异同点

相同点

  • 都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字 符串的方式排序的,已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的所有市 民了。同样地,hash 字段的方式也只能支持等值查询。

不同点

  • 从占用的额外空间来看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而 hash 字段方法需要增加一个字段。当然,倒序存储方式使用 4 个字节的前缀长度应该 是不够的,如果再长一点,这个消耗跟额外这个 hash 字段也差不多抵消了。

  • 在 CPU 消耗方面,倒序方式每次写和读的时候,都需要额外调用一次 reverse 函数, 而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂 度来看的话,reverse 函数额外消耗的 CPU 资源会更小些。

  • 从查询效率上看,使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来 的值虽然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近 1。 而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数。

八.mysql选错索引原因及处理方法

现象

应该使用某个索引的时候,但是却使用了别的索引或者没有使用索引

优化器选择索引逻辑

  • 扫描的行数
  • 否使用临时表
  • 是否排序

索引选择异常和处理

  • 一种方法是,采用 force index 强行选择一个索引。

    • # a是索引
      select * from t force index(a) where a between 10000 and 20000;
      
  • 第二种方法就是,我们可以考虑 修改语句,引导 MySQL 使用我们期望的索引。

  • 第三种方法是,在有些场景下,我们可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。

九.mysql对索引字段进行函数操作导致不走索引搜索树功能

1.条件字段做函数操作

①.现象

假设你现在维护了一个交易系统,其中交易记录表 tradelog 包含交易流水号 (tradeid)、交易员 id(operator)、交易时间(t_modified)等字段。为了便于描 述,我们先忽略其他字段。这个表的建表语句如下:

CREATE TABLE tradelog (

id int(11) NOT NULL,

tradeid varchar(32) DEFAULT NULL,

operator int(11) DEFAULT NULL,

t_modified datetime DEFAULT NULL,

PRIMARY KEY (id),

KEY tradeid (tradeid),
KEY t_modified (t_modified)

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设,现在已经记录了从 2016 年初到 2018 年底的所有数据,运营部门有一个需求是, 要统计发生在所有年份中 7 月份的交易记录总数。这个逻辑看上去并不复杂,你的 SQL 语句可能会这么写:

mysql> select count(*) from tradelog where month(t_modified)=7;

由于 t_modified 字段上有索引,于是你就很放心地在生产库中执行了这条语句,但却发现执行了特别久,才返回了结果。

如果你问 DBA 同事为什么会出现这样的情况,他大概会告诉你:如果对字段做了函数计 算,就用不上索引了,这是 MySQL 的规定。

②.原因

现在你已经学过了 InnoDB 的索引结构了,可以再追问一句为什么?为什么条件是 where t_modified='2018-7-1’的时候可以用上索引,而改成 where month(t_modified)=7 的时候就不行了?

下面是这个 t_modified 索引的示意图。方框上面的数字就是 month() 函数对应的值。

如果你的 SQL 语句条件用的是 where t_modified='2018-7-1’的话,引擎就会按照上面 绿色箭头的路线,快速定位到 t_modified='2018-7-1’需要的结果。

实际上,B+ 树提供的这个快速定位能力,来源于同一层兄弟节点的有序性。

但是,如果计算 month() 函数的话,你会看到传入 7 的时候,在树的第一层就不知道该怎 么办了。

也就是说,对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃 走树搜索功能。

需要注意的是,优化器并不是要放弃使用这个索引。

在这个例子里,放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历索引 t_modified,优化器对比索引大小后发现,索引 t_modified 更小,遍历这个索引比遍历 主键索引来得更快。因此最终还是会选择索引 t_modified。

接下来,我们使用 explain 命令,查看一下这条 SQL 语句的执行结果。

key="t_modified"表示的是,使用了 t_modified 这个索引;我在测试表数据中插入了 10 万行数据,rows=100335,说明这条语句扫描了整个索引的所有值;Extra 字段的 Using index,表示的是使用了覆盖索引。

③.解决方法

由于在 t_modified 字段加了 month() 函数操作,导致了全索引扫描。为了能 够用上索引的快速定位能力,我们就要把 SQL 语句改成基于字段本身的范围查询。按照下 面这个写法,优化器就能按照我们预期的,用上 t_modified 索引的快速定位能力了。

select count(*) from tradelog where

(t_modified >= '2016-7-1' and t_modified<'2016-8-1') or

(t_modified >= '2017-7-1' and t_modified<'2017-8-1') or

(t_modified >= '2018-7-1' and t_modified<'2018-8-1');

当然,如果你的系统上线时间更早,或者后面又插入了之后年份的数据的话,你就需要再把其他年份补齐。

到这里我给你说明了,由于加了 month() 函数操作,MySQL 无法再使用索引快速定位功 能,而只能使用全索引扫描。

不过优化器在个问题上确实有“偷懒”行为,即使是对于不改变有序性的函数,也不会考虑使用索引。比如,对于 select * from tradelog where id + 1 = 10000 这个 SQL 语 句,这个加 1 操作并不会改变有序性,但是 MySQL 优化器还是不能用 id 索引快速定位 到 9999 这一行。所以,需要你在写 SQL 语句的时候,手动改写成 where id = 10000 -1 才可以。

2.隐式类型转换

①.隐式类型转换规则

我们一起看一下这条 SQL 语句:

mysql> select * from tradelog where tradeid=110717;

交易编号 tradeid 这个字段上,本来就有索引,但是 explain 的结果却显示,这条语句需 要走全表扫描。你可能也发现了,tradeid 的字段类型是 varchar(32),而输入的参数却是 整型,所以需要做类型转换。

那么,现在这里就有两个问题:

  • 数据类型转换的规则是什么?
  • 为什么有数据类型转换,就需要走全索引扫描?

先来看第一个问题,你可能会说,数据库里面类型这么多,这种数据类型转换规则更多,
我记不住,应该怎么办呢?

这里有一个简单的方法,看 select “10” > 9 的结果:

  1. 如果规则是“将字符串转成数字”,那么就是做数字比较,结果应该是 1;
  2. 如果规则是“将数字转成字符串”,那么就是做字符串比较,结果应该是 0。

验证结果如图 3 所示。

从图中可知,select “10” > 9 返回的是 1,所以你就能确认 MySQL 里的转换规则了: 在 MySQL 中,字符串和数字做比较的话,是将字符串转换成数字。

②.原因

这时,你再看这个全表扫描的语句:

mysql> select * from tradelog where tradeid=110717;

就知道对于优化器来说,这个语句相当于:

mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

也就是说,这条语句触发了我们上面说到的规则:对索引字段做函数操作,优化器会放弃走树搜索功能。

3.隐式字符编码转换

①.现象

假设系统里还有另外一个表 trade_detail,用于记录交易的操作细节。为了便于量化分析和复现,我往交易日志表 tradelog 和交易详情表 trade_detail 这两个表里插入一些数 据。

mysql> CREATE TABLE trade_detail (

id int(11) NOT NULL,

tradeid varchar(32) DEFAULT NULL,

trade step int(11) DEFAULT NULL, /* 操作步骤 */

step_info varchar(32) DEFAULT NULL, /* 步骤信息 */

PRIMARY KEY (id),

KEY tradeid (tradeid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tradelog values(1, 'aaaaaaaa', 1000, now());

insert into tradelog values(2, 'aaaaaaab', 1000, now());

insert into tradelog values(3, 'aaaaaaac', 1000, now());

insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');

insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

这时候,如果要查询 id=2 的交易的所有操作步骤信息,SQL 语句可以这么写:

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

我们一起来看下这个结果:

  • 第一行显示优化器会先在交易记录表 tradelog 上查到 id=2 的行,这个步骤用上了主 键索引,rows=1 表示只扫描一行;
  • 第二行 key=NULL,表示没有用上交易详情表 trade_detail 上的 tradeid 索引,进行 了全表扫描。

在这个执行计划里,是从 tradelog 表中取 tradeid 字段,再去 trade_detail 表里查询匹 配字段。因此,我们把 tradelog 称为驱动表,把 trade_detail 称为被驱动表,把 tradeid 称为关联字段。

接下来,我们看下这个 explain 结果表示的执行流程:

图中得执行流程:

  • 第 1 步,是根据 id 在 tradelog 表里找到 L2 这一行;
  • 第 2 步,是从 L2 中取出 tradeid 字段的值;
  • 第 3 步,是根据 tradeid 值到 trade_detail 表中查找条件匹配的行。explain 的结果里 面第二行的 key=NULL 表示的就是,这个过程是通过遍历主键索引的方式,一个一个 地判断 tradeid 的值是否匹配。

进行到这里,你会发现第 3 步不符合我们的预期。因为表 trade_detail 里 tradeid 字段上 是有索引的,我们本来是希望通过使用 tradeid 索引能够快速定位到等值的行。但,这里 并没有。

②.原因

如果你去问 DBA 同学,他们可能会告诉你,因为这两个表的字符集不同,一个是 utf8, 一个是 utf8mb4,所以做表连接查询的时候用不上关联字段的索引。这个回答,也是通常 你搜索这个问题时会得到的答案。

但是你应该再追问一下,为什么字符集不同就用不上索引呢? 我们说问题是出在执行步骤的第 3 步,如果单独把这一步改成 SQL 语句的话,那就是:

mysql> select * from trade_detail where tradeid=$L2.tradeid.value;

其中,$L2.tradeid.value 的字符集是 utf8mb4。

参照前面的两个例子,你肯定就想到了,字符集 utf8mb4 是 utf8 的超集,所以当这两个 类型的字符串在做比较的时候,MySQL 内部的操作是,先把 utf8 字符串转成 utf8mb4 字符集,再做比较。

这个设定很好理解,utf8mb4 是 utf8 的超集。类似地,在程序设计语言里 面,做自动类型转换的时候,为了避免数据在转换过程中由于截断导致数据 错误,也都是“按数据长度增加的方向”进行转换的。

因此, 在执行上面这个语句的时候,需要将被驱动数据表里的字段一个个地转换成 utf8mb4,再跟 L2 做比较。

也就是说,实际上这个语句等同于下面这个写法:

select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

CONVERT() 函数,在这里的意思是把输入的字符串转成 utf8mb4 字符集。

这就再次触发了我们上面说到的原则:对索引字段做函数操作,优化器会放弃走树搜索功能。到这里,你终于明确了,字符集不同只是条件之一,连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描的原因。

作为对比验证,我给你提另外一个需求,“查找 trade_detail 表里 id=4 的操作,对应的 操作者是谁”,再来看下这个语句和它的执行计划。

mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

这个语句里 trade_detail 表成了驱动表,但是 explain 结果的第二行显示,这次的查询操 作用上了被驱动表 tradelog 里的索引 (tradeid),扫描行数是 1。

这也是两个 tradeid 字段的 join 操作,为什么这次能用上被驱动表的 tradeid 索引呢?我 们来分析一下。

假设驱动表 trade_detail 里 id=4 的行记为 R4,那么在连接的时候(图 5 的第 3 步), 被驱动表 tradelog 上执行的就是类似这样的 SQL 语句:

select operator from tradelog where traideid =$R4.tradeid.value;

这时候 $R4.tradeid.value 的字符集是 utf8, 按照字符集转换规则,要转成 utf8mb4,所 以这个过程就被改写成:

select operator from tradelog where traideid =CONVERT($R4.tradeid.value USING utf8mb4);

你看,这里的 CONVERT 函数是加在输入参数上的,这样就可以用上被驱动表的 traideid 索引。

③.解决方法

优化语句的方法:

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

  • 比较常见的优化方法是,把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4,这样就没有字符集转换的问题了。

    • alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

  • 修改 SQL 语句的方法

    mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and d.id=2;

    我主动把 l.tradeid 转成 utf8,就避免了被驱动表上的字符编码转换,从 explain

    结果可以看到,这次索引走对了。

站在巨人的肩膀上摘苹果:

https://time.geekbang.org/column/intro/100020801
https://www.cnblogs.com/lonelyxmas/p/10668426.html

# Mysql  

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×