索引的数据结构

定西2023-09-302024-06-08

1 为什么使用索引

索引是存储引擎用于快速找到数据记录的一种数据结构,就好比一半教科书的目录部分,通过目录中找到对应文章的页码,便可快速定位到需要的文章,Mysql中也是一样的道理,进行数据查找时,首先查看查询条件是否命中某条索引,符合则通过索引查找相关顺序,如果不符合则需要全表扫描,即需要一条一条底地查询记录,知道找到与条件符合的记录
20230930204936
如上图所示,如果没有索引,数据离散分布,那么找到数据将会非常消耗时间,如果数据顺序摆放,那么也需要从1开始找到第6个,依然非常耗时,如果我们不借助任何索引结构帮助我们快速定位数据的话,我们查找Col2=89这条记录,就要逐行去查找

假如给数据使用二叉树这样的数据结构进行存储,如下图
123fdas321

这时再去查找Col2=89这条记录,只需要查找两次,查询速度提高了

这就是为什么我们要建索引,目的就是为了减少磁盘I/O的次数,加快查询速率

2 索引及其优缺点

Mysql官方对索引的定义为:索引(index)是帮助Mysql高效过去数据的数据结构

索引的本质:索引是数据结构,可以简单理解为排好序的快速查找数据结构,满足特定查找算法,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高效查找算法

索引是在存储引擎中实现的,因此每种存储引擎的索引不一定完全相同,并且每种存储引擎不一定支持所有索引类型,同时,存储引擎可以定义每个表的最大索引数和最大索引长度,所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节,有些存储引擎支持更多的索引数和更大的索引长度

优点

类似大学图书馆建立书目索引,提高数据检索的效率,降低数据库的IO成本,这也是建立索引最主要的原因
通过创建唯一索引,可以保证数据库表中每一行数据的唯一性
在实现数据的参考完整性方面,可以加速表和表之间的连接,对于有依赖关系的子表和父表的联合查询时,可以提高查询速度
在使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间,降低了CPU的消耗

缺点

创建索引和维护索引要耗费时间,并且随着数据量的增加,所耗费的时间也会增加
索引需要占磁盘空间,除了数据表占数据空间之外,每个索引还要占一定的物理空间,存储在磁盘上,如果有大量的索引,索引文件就可能比数据文件更快达到最大文件尺寸
虽然索引大大了提高了查询速度,同时却会降低更新表的速度,当对表中的数据进行增加,删除和修改的时候,索引也要动态地维护,这样就降低了数据的维护速度

索引可以提高查询速度,但是会影响插入记录的速度,这种情况下,最好的办法是先删除表中的索引,然后插入数据,插入完成后再创建索引

3 InnoDB中索引的推演

3.1 索引之前的查找

先来看一个精确匹配的例子

1	select [列名列表] from 表名 where 列名 = xxx;

在一个页中查找
假设目前表中的记录比较少,所有的记录都可以被存放到一个页中,在查找记录的时候可以根据搜索条件的不同分为两种情况

以主键为搜索条件
- 可以在页目录中使用二分法快读定位对应的槽,然后再遍历该槽对应分组中的记录即可快速找到指定的记录
以其他列为搜索条件
- 因为在数据页中并没有对非主键列建立所谓的页目录,所以我们无法通过二分法快速定位相应的槽,这种情况下只能从最小记录开始依次遍历单链表中的每条记录,然后对比每条记录是不是符合搜索条件,很显然,这种查找的效率是非常低的

在很多页中查找
大部分情况下我们存放的记录都是非常多的,需要好多的数据页来存储这些记录,在很多页中查找记录的话可以分为以下两个步骤

定位到记录所在的页
从所在的页内查找相应的记录

在没有索引的情况下,不论是根据主键列还是其他列的值进行查找,由于我们并不能快速的定位到记录所在的页,所以只能从第一个页沿着双向链表一直往下找,在每个页中根据我们上面的哈找方式去查找指定的记录,因为要遍历所有的数据页,所以这种方式显然是超级耗时的,如果一个表有一亿条记录呢?此时索引应运而生

3.2 索引概念

B+树的例子:
023-09-3030220

索引按照物理实现方式,可以分为2种,聚簇(聚集)和非聚簇(非聚集)索引,我们也把非聚集索引称为二级索引或者辅助索引

聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式(所有的用户记录都存储在叶子节点),也就是所谓的索引即数据,数据即索引

术语聚簇表示数据行和相邻的键值聚簇的存储在一起

特点

使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义
- 页内的记录是按照主键的大小顺序排成一个单向列表
- 各个存放用户记录额页也是根据页中用户记录的主键大小顺序排成一个双向链表
- 存放目录项记录的页分为不同的层次,在同一层中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表

聚簇索引的优缺点
优点

数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快
按照聚簇索引排列顺序,查询显示一定范围数据时,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以节省了大量的IO操作

缺点

插入速度严重依赖于插入顺序,按照主键的顺序插入是2最快的方式,否则将会出现页分裂,严重影响性能,因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
更新主键的代价很高,因此将会导致被更新的行移动,因此对于InnoDB表,我们一般定义为主键不可更新

限制

对于Mysql数据库目前只有InnoDB数据引擎支持聚簇索引,而MyISAM并不支持聚簇索引
由于数据物理存储排序方式只能有一种,所以每个Mysql的表只能有一个聚簇索引,一般情况下就是该表的主键
如果没有定义主键,Innodb会选择非空的唯一索引代替,如果没有这样的索引,Innodb会隐式的定义一个主键来作为聚簇索引
为了充分利用聚簇索引的聚簇的特性,所以Innodb表的主键列尽量选择有序的顺序Id,而不建议用无序的id,比如UUID,MD5,HASH,字符串列作为主键,无法保证主键数据的顺序增长

二级索引

也叫辅助索引,非聚簇索引

上边介绍的聚簇索引只能在搜索条件是主键时才能发挥作用,因为B+树中的数据都是按照主键进行排序的,那么如果我们想以别的列作为搜索条件该怎么办?肯定不能是从头到尾沿着链表依次遍历记录一遍

答案:我们可以多建几颗B+树,不同的B+树中的数据采用不同的排序规则,比方说我们用c2列的大小作为数据页,页中记录的排序规则,再建一棵B+树,效果如下图
2023-10-01093442

此B+树只有c1和c2的值,并没有c3的值,也可以根据这点来判断是否是聚簇索引

但是此时想根据c2来查全部的数据,怎么办?这个B+树中没有c3啊,可以通过此B+树根据c2找到c1,再用c1去上面的聚簇索引中找到数据即可,这个过程就叫做回表

为什么还需要回表操作呢?,直接把完整的信息放到叶子节点不ok吗

如果把完整的用户记录放到叶子节点是可以不用回表了,但是太占空间了,相当于每建立一棵B+树都需要把所有的用户记录再拷贝一遍,这就有点太浪费存储空间了

2023-10-01103952

小结:聚簇索引与非聚簇索引的原理不同,在使用上也有一些区别:

聚簇索引的叶子节点存储的就是我们的数据记录,非聚簇索引的叶子节点存储的是数据位置或者说是数据对应的主键?,非聚簇索引不会影响数据表的物理存储顺序
一个表只能有一种排序存储的方式,但可以有多个非聚簇索引,也就是多个索引目录提供的数据检索
使用聚簇索引的时候,数据的查询效率高,但如果对数据进行插入,删除,更新等操作,效率会比非聚簇索引低

联合索引

我们也可以同时以多个列的大小作为排序规则,也就是同时为多个列建立索引,比方说我们想让B+树按照c2和c3列的大小进行排序,这个包含两层含义:

先把各个记录和页面按照c2进行排序
在记的c2列相同的情况下,采用c3列进行排序

fadslkdjfla

如图所示,我们需要注意以下几点

每条目录项记录都由c2,c3,页号这三个部分组成
B+树叶子节点处的用户记录由c2,c3和主键c1列组成

注意一点,以c2和c3列的大小为排序规则建立的B+树称为联合索引,本质上还是一个二级索引,它的意思与分别为c2和c3列分别创建的索引的表述是不同的,不同点如下:

建立联合索引只会建立如上图一样的1棵B+树
为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树

3.3 InnoDB的B+树索引的注意事项

根页面位置万年不动,B+树的形成过程如下:
- 每当为某个表创建一个B+树索引(聚簇索引不是人为创建的,默认就有)的时候,都会为这个索引创建一个根节点页面,最开始表中没有数据的时候,每个B+树索引对应的根节点中既没有用户记录,也没有目录项记录
- 随后向表中插入用户记录时,先把用户记录存储到这个根节点中
- 当根节点中的可用哦空间用完时继续插入记录,此时会将根节点中的所有记录复制到一个新分配的页,比如页a中,然后对这个新页进行页分裂的操作,的到另一个新页,比如页b这时新插入的记录根据键值(也就是聚簇索引中的主键值,二级索引中对应的索引列的值)的大小就会被分配到页a或者页b中,而根节点便升级为存储目录项记录的页

这个过程特别注意的是:一个B+树索引的根节点自诞生之日起,便不会再移动,这样只要我们对某个表建立一个索引,那么它的根节点的页号便会被记录到某个地方,然后凡是InnoDB存储引擎需要用到这个索引的时候,都会从那个固定的地方取出根节点的页号,从而来访问这个索引

我们知道B+树索引的内节点中目录项记录的内容是索引列+页号的搭配,但是这个对于二级索引来说优点不严谨,还拿index_demo表为例,假设这个表中的数据是这样的:
20231002092604

给这个表的c2列建立二级索引:
20231002092941
这时就出问题了,由于页3中存储的目录项记录是由c2列+页号的值构成的,页3中的两条目录项记录对应的c2列的值都是1,而我们新插入的这条记录的c2列的值也是1,那么我们这条新插入的记录到底应该放到页4中,还是应该放到页5中?

为了让新插入记录能找到自己在哪个页里,我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的,所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分组成的:

索引列的值
主键值
页号

也就是我们把主键值也添加到二级索引内节点中的目录项记录了,这样就能保证B+树每一层节点中各条目录项记录除也好这个字段外是唯一的,所以我们为c2列建立二级索引后的示意图实际上应该是这样子的:
20231002093444

一个页中最少存储2条记录

一个B+树只需要很少的层级就可以轻松存储数亿条记录,查询速度相当不错,这是因为B+树本质上就是一个大的多层级目录,每经过一个目录时都会过滤掉许多无效的子目录,直到最后访问到存储真实数据的目录,那如果一个大的目录中只存放一个子目录是啥效果呢?那就是目录层级非常非常多,而且最后的那个存放真实数据的目录中只能存放一条记录,费了半天劲只能存放一条真实的用户记录?所以InnoDB的一个数据页至少可以存放两条记录

4 MyISAM中的索引方案

即使多个存储引擎支持同一种类型的索引,但是他们的实现原理也是不同的,Innodb和MyIsam默认的索引是Btree索引,而Memory默认的索引是Hash索引

MyISAM引擎使用B+tree作为索引结构,叶子节点的data域存放的是数据记录的地址

4.1 MyISAM索引原理

InnoDB中索引即数据,也就是聚簇索引的那颗B+树的叶子节点中已经把完整的用户记录都包含了,而MyISAM的索引方案虽然也使用树形结构,但是却将索引和数据分开存储

将表中的记录按照记录的插入顺序单独存储在一个文件中,称之为数据文件,这个文件并不划分为若干个数据页,有多少个记录就往这个文件中塞多少条记录就成了,由于在插入数据时侯并没有刻意按照主键大小排序,所以我们并不能在这些数据上使用二分法进行查找
使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的文件中,MyISAM会单独为表的逐渐创建一个索引,只不过在索引的叶子节点中存储的不是完整的用户记录,而是主键值+数据记录地址的组合

这里设表一共有三列,假设我们以Col1为主键,上图是一个MyISAM的主索引(Primary key)示意,可以看出MyISAM的索引文件仅仅保存数据记录的地址,在MyISAM中,主键索引和二级索引(Secondary key)在结构上没有任何区别,只是主键索引要求key唯一,而二级索引的key可以重复,如果我们在Col2上建立一个二级索引,则索引结构如下:
20231002100939

4.2 MyISAM与InnoDB对比

MyISAM的索引方式都是非聚簇的,与InnoDB包含1个聚簇索引是不同的,小结两种引擎中索引的区别

在InnoDB存储引擎中,我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录,而在MyISAM中却需要进行一次回表操作,意味着MyISAM中建立的索引相当于全部都是二级索引
InnoDB的数据文件本身就是索引文件,而MyISAM索引文件和数据文件是分开的,索引文件仅仅保存数据记录的地址
InnoDB的非聚簇索引data域存储相应记录的主键的值,而MyISam索引记录的是地址,换句话说,InnoDB的所有非聚簇索引都引用主键作为data域
MyIsam的回表操作是十分快速的,因为是拿着地址偏移量直接到文件中读取数据的,反观InnoDB是通过获取主键之后再去聚簇索引里找记录,虽然说也不慢,但还是比不上直接用地址去访问
InnoDB要求表必须有主键(MyISAM可以没有),如果没有显示指定,则Mysql系统会自动选择一个可以非空且唯一标识数据记录的列作为主键,如果不存在这种列,则Mysql自动为innodb表生成一个隐含字段作为主键,这个字段长度为6给字节,类型为长整型

5 索引的代价

索引是个好东西,但是也不能乱建,它在空间和时间上都会有消耗

空间上的代价
每建立一个索引都要为它建立一棵B+树,每一棵B+树的每一个节点都是一个数据页,一个页默认会占用16KB的存储空间,一棵很大的B+树由很多数据页组成,那就是很大的一片存储空间
时间上的代价
每次对表中的数据进行增删改操作时,都需要去修改各个B+树索引,而增删改操作可能会对节点和记录的排序造成破坏,所有存储引擎需要额外的时间进行一些记录移位,页面分裂,页面回收等操作维护节点和记录的排序,如果我们建立了许多索引,每个索引对应的B+树都要进行相关的维护操作,会给性能拖后腿

一个表上索引建的越多,就会占用越多的存储空间,在增删改记录的时候性能就越差,为了能建立又好又少的索引,我们得学学这些索引在哪些条件下起作用

6 MySql数据结构选择的合理性

从mysql的角度讲,不得不考虑一个现实的问题就是磁盘IO,如果我们能让索引的数据结构尽量减少硬盘的IO操作,所消耗的时间也就越小,可以说,磁盘的IO操作次数对索引的使用效率至关重要

查询都是索引操作,一般来说索引非常大,尤其是关系型数据库,当数据量比较大时,索引的大小很可能几个G甚至更多,为了减少索引在内存的占用,数据库索引是存储在外部磁盘上的,当我们利用索引查询的时候,不可能把整个索引全部加载到内存中,只能逐一加载,那么Mysql衡量查询效率的标准就是磁盘IO次数

6.1 Hash结构

加速查找速度和数据结构,常见的有两类

树,例如平衡二叉搜索树,查询/插入/删除的平均时间复杂度都是O(log2N)
哈希,例如HashMap,查询/插入/修改/删除的平均时间复杂度都是O(1)

采用Hash进行检索效率非常高,基本上一次检索就可以找到数据,而B+树需要自顶向下依次查找,多次访问节点才能找到数据,中间需要多次IO操作,从效率来说Hash比B+树更快

那Hash结构效率高,为什么索引结构要设计成树型呢?

Hash索引仅能满足=,>,<和in查询,如果进行范围查询,哈希型的索引,时间复杂度会退化为O(n);而树型的有序特征,依然能够保持O(log2N)的高效率
Hash索引还有一个缺陷,数据的存储是没有顺序的,在Order by的情况下,使用hash索引还需要对数据重新排序
对于联合索引的情况,Hash值是将联合索引键合并后一起计算的,无法对单独的一个键或者几个索引键进行查询
对于等值查询来说,通常Hash索引的效率更高,不过也存在一种情况,就是索引列的重复值如果很多,效率就会降低,这是因为遇到Hash冲突时,需要遍历桶中的行指针来进行比较,找到查询的关键字,非常耗时,所以,hash索引通常不会用到重复值多的列上,比如列为性别,年龄的情况等

虽然InnoDB本身不支持Hash索引,但是提供自适应Hash索引,什么情况下才会使用自适应Hash索引呢?如果某个数据经常被访问,当满足一定条件的时候,就会将这个数据页的地址存放到Hash表中,这样下次查询的时候,就可以直接找到这个页面的所在位置,这样让B+树也具备了Hash索引的优点
20231002204646
采用自适应Hash索引目的是方便根据SQL查询条件加速定位到叶子节点,特别是当B+树比较深时,通过自适应Hash索引可以明显提高数据的检索效率

6.2 二叉搜索树

如果我们利用二叉树作为索引结构,那么磁盘的IO次数和索引树的高度是相关的

二叉搜索树的特点

一个节点只能有两个子节点,也就是一个节点度不能超过2
左子节点<本节点;右子节点>=本节点,比我大的向右,比我下的向左

为了提高查询效率,就需要减少磁盘IO数,为了减少磁盘IO的次数,就需要尽量降低树的高度,需要把原来瘦高的树结构变得矮胖,树的每层的分叉越多越好

6.3 AVL树

平衡二叉树

为了解决上面二叉查询树退化成链表的问题,人们提出了平衡二叉树,又称为AVL树,它在二叉搜索树的基础上增加了约束,具有以下性质

它是一棵空树或它的左右两个子树的高度差的绝对值不超过1,并且左右两个子树都是一颗平衡二叉树

210340

每访问一次节点就需要进行一次磁盘IO操作,对于上面的树来说,我们需要进行5次IO操作,虽然平衡二叉树的效率高,但是树的深度也同样高,这就意味着IO操作次数多,会影响整体数据查询的效率

针对同样的数据,如果我们把二叉树改成M叉树(M>2)呢?当M=3时,同样的31个节点可以由下面的三叉树进行存储:
213321

6.4 B-Tree

B树,也就是多路平衡查找树,简写为B-Tree,它的高度远小于平衡二叉树的高度

20231002211225

B树在插入和删除节点的时候如果导致树不平衡,就通过自动调整节点的位置来保持树的自平衡
关键字集合分布在整棵树中,即叶子节点和非叶子节点都存放数据,搜索有可能在非叶子节点结束
其搜索性能等价于在关键字全集内做一次二分查找

6.5 B+tree

B+树也是一种多虑搜索树,基于B树做出了改进,主流的DBMS都支持B+树的索引方式,相比B-tree,B+tree适合文件索引系统

B树和B+树的差异

B+树中有k个孩子的节点就有k个关键字,也就是孩子数量=关键字数,而B树种,孩子数量=关键字数+1
B+树非叶子节点的关键字也会同时存在在子节点中,并且是子节点中所有关键字的最大(或最小)
B+树中非叶子节点仅用于索引,不保存数据记录,跟数据记录有关的信息都放在叶子节点中,而B树中,非叶子节点即保存索引,也保存数据记录
B+树所有关键字都在叶子节点出现,叶子节点构成一个有序链表,而且叶子节点本身按照关键字的大小才从小到大顺序链接

7 InnoDB数据存储结构

7.1 数据库的存储结构:页

索引结构给我们提供了高效的索引方式,不过索引信息以及数据记录都是保存在文件上的,确切说是存储在页结构中.另一方面,索引是在存储引擎中实现的,MySql服务器上的存储引擎负责对表数据的读取和写入工作,不同存储引擎中存放的格式一般是不同的,甚至有的存储引擎比如Memory都不用磁盘来存储数据

磁盘与内存交互基本单位: 页

InnoDB将数据划分为若干个页,InnoDB中的页大小默认为16KB

以页作为磁盘和内存之间交互的基本单位,就是说一次最少从磁盘中读取16KB的内容到内存中,一次最少把内存中的16KB内容刷新到磁盘中,也就是说:**数据库中,不论读一行,还是读多行,都是将这些行所在的页进行加载,数据库管理存储空间的基本单位是页,数据库IO操作的最小单位也是页,一个页中可以存储多个行记录

记录是按照行来存储的,但是数据库的读取并不以行为单位,否则一次读取(也就是一次IO操作)只能处理一行数据,效率会非常低

页结构概述
页之间可以不在物理结构上相连,只要通过双向链表相关联即可,每个数据页中的记录会按照逐渐值从小到大的顺序组成一个单向链表,每个数据页都会为存储在它里边的记录生成一个页目录,在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽,然后再遍历该槽对应分组中的记录即可快速找到指定的记录

页的上层结构
另外,在数据库中,还存在着区(Extent),段(Segment)和表空间(Tablespace)概念,行,页,区,段,表空间的关系如下:
20231003195200

区(Extent)是比页大一级的存储结构,在InnoDB存储引擎中,一个区会分配64个连续的页,因为InnoDB中的页大小默认是16KB,所以一个区的大小是64*16KB=1MB

段(Segment)由一个或多个区组成,区在文件系统是一个连续分配的空间(在InnoDB中是连续的64个页),不过在段中不要求区与区之间是相邻的,段是数据库中的分配单位,不同类型的数据库对象以不同的段形式存在,当我们创建数据表,索引的时候,就会相应创建对应的段,比如创建一张表时就会创建一个表段,创建一个索引时会创建一个索引段

表空间(Tablespace)是一个逻辑容器,表空间存储的对象是段,在一个表空间中可以有一个或多个段,但是一个段只能属于一个表空间,数据库由一个或多个表空间组成,表空间从管理上可以划分为系统表空间,用户表空间,撤销表空间,临时表空间等

7.2 页的内部结构

页如果按照类型划分的话,常见的有数据页(保存B+树节点),系统页,Undo页和事务数据页等,数据页是我们最常使用的页

数据页的16KB大小的存储空间被划分为七个部分,分别是文件头(File Header),页头(Page Header),最大最小记录,用户记录,空间空间,页目录,和文件尾

20231003201420

这7个部分的作用:
fdasfdas

…

这些概念好多啊.我现在只想知道怎么用,怎么优化,但是不会这些概念又不行

8 索引的分类

Mysql的索引包括普通索引,唯一性索引,全文索引,单列索引,多列索引和空间索引

从功能逻辑说,索引主要有4种,分别是普通索引,唯一索引,主键索引,全文索引
按照物理实现方式,索引可分为2种,聚簇索引和非聚簇索引
按照作用字段个数进行划分,分成单列索引和联合索引

普通索引
在创建普通索引时,不附加任何限制条件,只是用于提高查询效率,这类索引可以创建在任何数据类型中,其值是否唯一和非空,要由字段本身的完整性约束条件决定,建立索引后,可以通过索引进行查询,例如:在表student的字段name上建立一个普通索引,查询记录时就可以根据该索引进行查询
唯一性索引
使用UNIQUE参数可以设置索引为唯一性索引,在创建唯一性索引时,限制该索引的值必须是唯一的,但允许有空值,在一张数据表里可以有多个唯一索引,例如在表student的字段email中创建唯一性索引,那么字段email的值就必须是唯一的,通过唯一性索引,可以更快地确定某条记录
主键索引
主键索引就是一种特殊的唯一性索引,在唯一索引的基础上增加了不为空的约束,也就是NOT NULL+UNIQUE,一张表里最多只有一个主键索引
单列索引
在表中单个字段上创建索引,单列索引只根据该字段进行索引,单列索引可以是普通索引,也可以是唯一性索引,还可以是全文索引,只要保证该索引只对应一个字段即可,一个表可以有多个单列索引
多列(组合,联合)索引
多列索引是在表的多个字段组合上创建一个索引,该索引指向创建时对应的多个字段,可以通过这几个字段进行查询,但是只有查询条件中使用了这些字段中的第一个字段时才会被使用,例如在表中的字段id,name和gender上建立一个多列索引idx_id_name_gender,只有在查询条件中使用了字段id时该索引才会被使用,使用组合索引时遵循最左前缀集合
全文索引
全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术,它能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果,全文索引非常适合大型数据集,对于小的数据集,它的用处比较小
空间索引
使用参数SPATIAL可以设置索引为空间索引,空间索引只能建立在空间数据类型上,这样可以提高系统获取空间数据的效率,mysql中的空间数据类型包括geometry,point,linestring和polygon等,目前只有myisam存储引擎支持空间检索,而且索引的字段不能为空值,对于初学者来说,这类索引很少会用到

9 使用索引

mysql支持多种方法在单个或多个列上创建索引,在创建表的定义语句create table中指定索引列,使用alter table语句在存在的表上创建索引,或者是使用create index语句在已存在的表上添加索引

9.1 创建表时创建索引

隐式创建索引:在声明有主键约束,唯一性约束,外键约束的字段上,会自动的添加相关的索引

显式创建索引:

1 2	create table table_name [col_name data_type] [UNIQUE \| FULLTEXT \| SPATIAL] [INDEX \| KEY] [index_name] (col_name [length]) [ASC \| DESC]

20231003223018

CREATE TABLE book (
	book_id INT,
	book_name VARCHAR ( 100 ),
	AUTHORS VARCHAR ( 100 ),
	info VARCHAR ( 100 ),
	COMMENT VARCHAR ( 100 ),
	year_publication YEAR,-- 创建索引
INDEX idx_bname ( book_name ) 
);

创建好的索引:
20231003223416

使用命令行展示:
20231003223632

还可以执行show index from table_name
20231003223758

然后执行EXPLAIN SELECT * FROM book WHERE book_name = 'Kwan Tsz Hin';

可以看出,是否用到了刚刚创建的索引
20231003224238

列possible_keys是idx_name,也就是上面创建的索引名,所以代表是用到了这个索引的.

创建唯一索引,在index前加上unique

-- 创建唯一索引
CREATE TABLE book1 (
	book_id INT,
	book_name VARCHAR ( 100 ),
	AUTHORS VARCHAR ( 100 ),
	info VARCHAR ( 100 ),
	COMMENT VARCHAR ( 100 ),
	year_publication YEAR,
UNIQUE INDEX ux_idx_cmt ( COMMENT ) 
);

此时执行show index from book1,可以看出Non_unique这一列变成了0,这一列如果是1的话,就表示不是唯一的,0就是表示唯一
20231004181139

加上了唯一索引,就算字段原本没有设置唯一性约束unique,也是会检查唯一性的,比如插入两条有相同COMMENT的数据,就出现Duplicate了
20231004181701

创建主键索引,在创建表时声明主键,就会自动创建好主键索引

-- 创建主键索引
CREATE TABLE book2 (
	book_id INT PRIMARY KEY,
	book_name VARCHAR ( 100 ),
	AUTHORS VARCHAR ( 100 ),
	info VARCHAR ( 100 ),
	COMMENT VARCHAR ( 100 ),
year_publication YEAR 
);

查看索引:
20231004182853

那想删除主键索引怎么办?

通过删除主键约束的方式删除主键索引:ALTER TABLE book2 DROP PRIMARY KEY;

创建单列索引,上面的索引都算是单列索引

创建联合索引,对于id和name和info创建索引

-- 创建单列索引
CREATE TABLE book4 (
	book_id INT,
	book_name VARCHAR ( 100 ),
	AUTHORS VARCHAR ( 100 ),
	info VARCHAR ( 100 ),
	COMMENT VARCHAR ( 100 ),
year_publication YEAR,
INDEX mul_bid_bname_info(book_id,book_name,info)
);

查看索引:
20231004184037

这里的Seq_in_index顺序就是创建索引时写的顺序,这个是会影响到生成的B+树结构的,按照这个顺序来生成B+树
,就是先按照book_id排,重复则按照book_name排,再则按照info排

在查询时,也会按照这个顺序来使用索引,比如下面这个sql

1 2	-- 分析 EXPLAIN SELECT * FROM book4 WHERE book_id = 1001 AND book_name = 'mysql';

具备第一个和第二个列,是可以使用到这个索引的

但是,如果条件改为如下,就是没有book_id这个字段了,这时就不会使用到索引

1	EXPLAIN SELECT * FROM book4 WHERE book_name = 'mysql';

最左前缀原则

创建全文索引(了解)

FULLTEXT全文索引可以用于全文搜索,并且只为CHAR,VARCHAR,TEXT列创建索引,索引总是对整个列进行,不支持局部(前缀)索引

举例1:创建表test4,在表中的info字段上建立全文索引,sql语句如下

CREATE TABLE test4 (
	id INT NOT NULL,
	NAME CHAR ( 30 ) NOT NULL,
	age INT NOT NULL,
	info VARCHAR ( 255 ),
	FULLTEXT INDEX futxt_idx_info (
	info ( 50 )) 
)

查看索引:
20231004220100

9.2 在已存在的表中建立索引

使用alter table的方式
首先建表

CREATE TABLE book5 (
	book_id INT,
	book_name VARCHAR ( 100 ),
	AUTHORS VARCHAR ( 100 ),
	info VARCHAR ( 100 ),
	COMMENT VARCHAR ( 100 ),
year_publication YEAR
);

然后执行:

1	ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);

即可给已建好的book5添加一个以COMMENT列的普通索引

要创建唯一索引,也是和之前的创建方式差不多

1	ALTER TABLE book5 ADD UNIQUE INDEX uk_idx_bname(book_name);

下面是创建联合索引

1	ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info)

可以说,和在创建表时创建索引的步骤类似,这里的ADD后的内容和之前的是一摸一样的

使用create index ... on的方式

CREATE INDEX idx_cmt ON book6(COMMENT)

CREATE INDEX uk_idx_bname ON book6(book_name)

CREATE INDEX mul_bname_info ON book6(book_name,info);

20231004222052

9.3 删除索引

通过alter table DROP的方式删除

根据索引的名称来删除

1	ALTER TABLE book5 DROP INDEX idx_cmt

注意,添加auto increment的字段的唯一索引不能删除

通过DROP index on删除

1	DROP INDEX uk_idx_bname ON book5;

如果是一个联合索引,c1,c2,c3三列,如果删除c2,则c3就会顶上,如果再删除c1,那么c3就会成为第一个

删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除,如果组成索引的所有列都被删除,则整个索引将被删除

9.4 Mysql8.0索引新特性

降序索引

mysql8开始正式支持降序索引,但仅限InnoDB存储引擎

CREATE TABLE ts1(
a INT,
b INT,
INDEX idx_a_b(a ASC,b DESC));

-- 执行show create table ts1
CREATE TABLE `ts1` (
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  KEY `idx_a_b` (`a`,`b` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

隐藏索引
在Mysql5.7之前,只能通过显示的方式删除索引,如果发现删除索引后出现错误,又只能通过显式创建索引的方式将删除的索引创建回来,如果数据表中的数据量非常大,或者数据表本身比较大,这种操作就会消耗过多的资源

从Mysql8开始支持隐藏索引,只需要将待删除的索引设置为隐藏索引,使查询优化器不再使用这个索引(即使使用force index(强制使用索引),优化器也不会使用该索引),确认将索引设置为隐藏索引后系统不受任何影响,就可以彻底删除索引,这种通过先将索引设置为隐藏索引,再删除索引的方式就是软删除

同时,如果想验证某个索引删除之后的查询性能影响,就可以暂时隐藏该索引

注意,主键不能设置为隐藏索引,当表中没有显示设置主键时,表中第一个唯一非空索引会成为隐式主键,也不能设置为隐藏索引

索引使默认可见的,通过设置visible或者invisible来设置是否可见

创建隐藏索引

CREATE TABLE ts2(
a INT,
b INT,
INDEX idx_a_b(a,b) invisible
);

20231004230017

也可以使用alter table的形式创建隐藏索引

1	ALTER TABLE ts2 ADD INDEX idx_a(a) invisible

还可以使用create index ... on的形式

1	CREATE INDEX idx_a ON ts3(a) invisible

修改索引的可见性

使用alter table table_name alter index index_name invisible即可需改索引的可见性

1	ALTER TABLE book6 ALTER INDEX idx_cmt invisible

当索引被隐藏时,它的内容仍然是和正常索引一样实时更新的,如果一个索引需要长期被隐藏,那么可以将其删除,因为索引的存在会影响插入,更新,和删除的性能

10 索引的设计原则

为了使索引的是哦那个效率更高,在创建索引时,必须考虑在哪些索引和创建什么类型的索引,索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍,高效的索引对于获得良好的性能非常重要,设计索引时,应该考虑相应准则

10.1 数据准备

创建一个数据库,两张表

CREATE DATABASE zzmrdb1;
USE zzmrdb1;
CREATE TABLE `student_info` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`student_id` INT NOT NULL,
	`name` VARCHAR ( 20 ) DEFAULT NULL,
	`course_id` INT NOT NULL,
	`class_id` INT ( 11 ) DEFAULT NULL,
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

CREATE TABLE `course` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`course_id` INT NOT NULL,
	`course_name` VARCHAR ( 40 ) DEFAULT NULL,
	PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

里面的数据就直接使用navicat自带的数据生成创建了
20231005102131

10.2 适合创建索引的情况

字段的数值有唯一性的限制

索引本身可以起到约束的作用,比如唯一索引,主键索引都是可以起到唯一性约束的,因此在我们的数据表中,如果某个字段是唯一的,就可以直接创建唯一性索引,或者主键索引,这样可以更快地通过该索引来确定某条记录

例如,学生表中学号是具有唯一性的字段,为该字段建立唯一性索引可以很快确定某个学生的信息,如果使用姓名的话,可能存在同名现象,从而降低查询速度

Alibaba:业务上具有唯一特性的字段,即使是组合字段,也必须建立唯一索引,不要以为唯一索引影响了insert速度,这个速度损耗可以忽略,但是提高查找速度是明显的

频繁作为where查询条件的字段

某个字段在select语句的where条件中经常被使用到,那么就需要给这个字段创建索引了,尤其是在数据量大的情况下,创建普通索引就可以大幅提升数据查询的效率

比如student_info数据表(含100万条数据)假设我们想要查询student_id=123110的用户信息,如果没有对student_id字段创建索引,进行如下查询

测试索引的效果

目前student_info表只有主键索引

1
2
3

SELECT course_id,class_id,`name`,create_time,student_id
FROM student_info
WHERE student_id = 671

执行完成后,用时0.277s
20231005103848

再给student_id创建一个普通索引(创建索引都要花费2.7s了)

1	CREATE INDEX idx_sid ON student_info(student_id)

去查看索引的大小,已经有76MB的大小了
20231005104503

如果再执行上面的查询语句,查询时间已变成0.028s了,相当于快了10倍
20231005104604

经常group by和order by的列

索引就是让数据按照某种顺序进行存储或检索,因此当我们使用group by对数据进行分组查询,或者使用order by对数据进行排序的时候,就需要对分组或者排序的字段进行索引,如果待排序的列有多个,那么可以在这些列上建立组合索引

比如,按照student_id对学生选修棵的课程进行分组,显示不同student_id和课程数量,显示100个即可

如果我们隐藏掉上面创建的student_id创建索引(ALTER TABLE student_info ALTER INDEX idx_sid invisible),执行下面的sql

1	SELECT student_id,count(*) AS num FROM student_info GROUP BY student_id limit 100

运行时间为:0.400s

再解除隐藏,此时花费0.045s

也是快了很多很多

再测试这个

SELECT student_id,count(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC
limit 100

分别给student_id和create_time创建索引,执行上面的sql,会花费2.652s

但是如果我们是创建一个联合索引呢?

1	ALTER TABLE student_info ADD INDEX mul_sid_crt(student_id,create_time DESC)

再次执行上述的查询,会发现只用时0.2s

进一步测试

如果字段的顺序反过来,是什么效果?

1	ALTER TABLE student_info ADD INDEX mul_crt_sid(create_time DESC,student_id)

执行查询语句,会发现速度又慢下来了,查看执行流程,会发现根本没有使用到这个mul_crt_sid,而使用的还是之前的idx_sid
20231005113014

所以,如果用到联合索引,一定要把group的字段写在前面,order的字段写在后面

UPDATE,DELETE的Where条件列

当我们对某条数据进行update或者delete操作的时候,是否也需要对where的条件列创建索引呢?

我们先看一下对数据进行update的情况,我们想要吧name为张秀英1对应的student_id修改为10002

1	UPDATE student_info SET student_id = 10002 WHERE name = '张秀英1'

运行时间为0.526s

此时添加name索引

1	ALTER TABLE student_info ADD INDEX idx_name(name)

再次执行修改语句,用时0.019s

DELETE也是类似

对数据按照某个条件进行查询后再进行update或者delete的操作,如果对where字段创建了索引,就能大幅提高效率,原理是因为我们需要先根据where条件列检索出这条记录,然后再对它进行更新或删除,如果进行更新的时候,更新的字段是非索引字段,提升的效率会更明显,这是因为非索引字段更新后不需要对索引进行维护

DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重就，使用DISTINCT,那么对这个字段创建索引,也会提升查询速度

执行sql:

1	SELECT DISTINCT(student_id) FROM student_info

如果没有student_id的索引时,需用时0.511s

创建了索引时,只需0.025s

多表JOIN连接操作时,创建索引注意事项

首先,连接表的数量尽量不要超过3张,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增长会非常快,严重影响查询的效率

其次,对WHERE条件创建索引,因为WHERE才是对数据条件的过滤,如果在数据量非常大的情况下,没有WHERE条件过滤是非常可怕的

最后,对用于连接的字段创建索引,并且该字段在多张表中的类型必须一致,比如course_id在student_info表和coures表中都为int(11)类型,而不能一个为int,一个为varchar

举个例子,如果我们只对student_id创建索引,执行SQL语句


SELECT
	student_info.course_id,
	course_name,
	student_info.student_id,
	student_info.`name` 
FROM
	student_info
	INNER JOIN course ON student_info.course_id = course.course_id 
WHERE
	NAME = '张秀英'

用时0.245s

添加name上的索引后,用时0.073s

使用列的类型小的创建索引

这里所说的类型大小指的就是该类型表示的数据范围的大小

我们在定义表结构的时候要显式的指定列的类型,以整数类型为例,有TINYINT,MEDIUMINT,INT,BIGINT等,它们占用的存储空间依次递增,能表示的整数范围当然也是依次递增,如果我们想要对某个整数列建立索引的话,在表示的整数范围允许的情况下,尽量让索引列使用较小的类型,比如我们能使用INT就不要使用BIGINT,能使用MEDIUMINT就不要使用INT,这是因为:

数据类型越小,在查询时进行的比较操作就越快
数据类型越小,索引占用的存储空间就越小,在一个数据页内就可以放更多的记录,从而减少磁盘IO带来的性能损耗,也就意味着可以把更多的数据页缓存在内存中,从而加快读写效率

这个建议对于表的主键来说更加适用,因为不仅是聚簇索引中会存储主键值,其他所有的二级索引的节点处都会存储一份记录的主键值,如果主键使用更小的数据类型,也就意味着节省更多的存储空间和更高效的IO

使用字符串前缀创建索引

假设我们的字符串很长,那存储一个字符串就需要占用很大的存储空间,在我们需要为这个字符串列建立索引时,那就意味着对应的B+树中有这么两个问题

B+树索引中的记录需要把该列额完整字符串存储起来,更费时,而且字符串越长,在索引中占用的存储空间越大
如果B+树索引中索引列存储的字符串很长,那在做字符串比较时会消耗更多的时间

我们可以通过截取字段的前面一部分内容建立索引,这个就叫前缀索引,这样在查找记录时虽然不能精确的定位到记录的位置,但是能够定位到相应前缀所在的位置,然后根据前缀相同的记录的主键值回表查询完整的字符串值,既节约空间,又减少了字符串的比较时间

1
2
3

CREATE TABLE shop(address VARCHAR(120) not NULL);

ALTER TABLE shop ADD INDEX idx_adr(address(12));

问题是,截取多少呢?截取的多了,达不到节省索引存储空间的目的,截取得少了,重复内容太多,字段的散列(选择性)会降低,怎么计算不同得长度的选择性?

1	SELECT count(DISTINCT left(address,12))/count(*) FROM shop

Alibaba:在varchar字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度

索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为20的索引,区分度会高达90%以上,可以使用count(DISTINCT left(列名,索引长度))/count(*)的区分度来确定

区分度高(散列性高)的列适合作为索引

列的基数指的是某一列中不重复数据的个数,比方说某个列包含值2,5,8,2,5,8,2,5,8,虽然有9条记录,大事难事该列的基数却是3,也就是说,在记录行数一定的情况下,列的基数越大,该列的值越分散,列的基数越小,该列的值越集中

可以使用SELECT count(DISTINCT address)/count(*) FROM shop来计算区分度,越接近1,区分度越高,一般超过33%就算是比较高效的索引了

联合索引把区分度高(散列性高)的列放在前面

使用最频繁的列放到联合索引的左侧

有以下sql,student_id肯定是比较频繁的,所以给student_id和course_id创建联合索引时,要把student_id放前面

1 2	SELECT * FROM student_info WHERE student_id = 610 AND course_id = 8

比如这个索引:

1	ALTER TABLE student_info ADD INDEX mul_sid_cid(student_id,course_id)

在多个字段都要创建索引时,联合索引优于单列索引

没错.

10.3 限制索引的数目

在实际工作中,我们也需要注意平衡,索引的数目不是越多越好,建议单张表索引数量不超过6个,原因:

每个索引都需要占用磁盘空间,索引越多,占用的磁盘空间越大
索引会影响insert,delete,update等语句的性能,因为表中的数据更改的同时,索引也会进行调整和更新,会造成负担
优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引进行评估,以生成一个最好的执行计划,如果同时有很多个索引都可以用于查询,会增加mysql优化器的生成执行计划的时间,降低查询性能

10.4 不适合创建索引的情况

在where中使用不到的字段

where条件(包括group by,order by)里用不到的字段不需要创建索引,索引的价值是快速定位,如果起不到定位的字段通常是不需要创建索引的

数据量小的表中最好不要使用索引

如果表记录太少,比如少于1000个,那么是不需要创建索引的,表记录太少,是否创建索引对查询效率的影响并不大,甚至说,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果

有大量重复数据的列上不要建立索引

当数据重复度高于10%时,不需要对这个字段使用索引

避免对经常更新的表创建过多的索引

频繁更新的字段不一定要创建索引,因为更新数据时,也需要更新索引,如果索引太多,在更新索引时也会造成负担,从而影响效率
避免对经常更新的表创建过多的索引,并且索引中的列尽可能少,此时,虽然提高了查询速度,但是却会降低更新表的速度

不建议用无需的值作为索引

例如身份证,UUID,无序字符串等

删除不再使用或者很少使用的索引

不再使用,很少使用,在更新表数据时,也会更新这些索引,所以,断舍离

不要定义冗余或者重复的索引

冗余索引
index(a,b,c)相当于index(a),index(a,b),index(a,b,c)
重复索引
我们可能会对某个列重复建立索引,比如一个字段是主键,已经有了一个主键索引了,又创建了了一个普通索引,就是重复创建了

索引是一把双刃剑,可提高查询效率,但也会降低插入和更新的速度并占用磁盘空间

11 性能分析工具的使用

在数据库调优中,我们的目标就是响应时间更快,吞吐量更大,利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式

整个流程分成观察(Show status)和行动(Action)两个部分,字母s的部分代表观察(会使用相应的分析工具),字母A代表的部分是行动(对应分析可以采取的行动)

这里有一个成本与效果的关系
20231005190956

11.1 查看系统性能参数

在Mysql中,可以使用SHOW STATUS语句查询一些mysql数据库服务器的性能参数,执行频率

1	SHOW [GLOBAL\|SESSION] STATUS LIKE '参数';

023-10-05237

比如想查看操作行数,执行:

1	show status like 'innodb_rows_%'

20231005192032

11.2 统计sql的查询成本:last_query_cost

可以先执行一条sql语句

1	select * from student_info where id = 1000;

20231005192614

再执行以下命令,即可查出最近一次执行查询sql的成本

1	show status like 'last_query_cost';

得到的是1,就表示大概要用到一个数据页
20231005192719

使用场景:它对于比较开销是非常有用的,特别是我们有好几种查询方式可选时

Sql查询是一个动态的过程

位置决定效率,如果页就在数据库缓冲池中,那么效率是最高的,否则还需要从内存或者磁盘中进行读取,当然针对单个页的读取来说,如果页存在于内存中,会比在磁盘中读取效率高很多
批量决定效率,如果我们从磁盘中对单一页进行随机读,那么效率是很低的,而采用顺序读取的方式,批量对页进行读取,平均一页的读取效率就会提升很多,甚至要快于单个页面在内存中的随机读取

11.3 定位执行慢的sql:慢查询日志

Mysql慢查询日志,用来记录在mysql中响应时间超过阈值的语句,具体指运行时间超过long_query_time值得sql,则会被记录到慢查询日志中,long_query_time得默认值是10,意思是运行10s以上(不含10s)得语句,认为是慢查询

默认情况下,mysql数据库没有开启慢查询日志,需要我们手动来设置这个参数,如果不是调优需要的话,一般不建议开启该参数因为开启慢查询日志会或多或少带来一些性能损耗

20231005194559

开启慢查询日志

1	set global slow_query_log = on;

20231005195920

执行show variables like '%slow_query_log%';
还能查看慢查询日志文件目录
20231005200634

更改慢查询阈值:临时修改

1
2
3

set global long_query_time = 1
-- 注意,这里设置global得话,对当前会话依然还是默认值,而新的会话就是修改得值了,所以这里再执行一次即可修改本次会话得时间
set long_query_time = 1

临时修改在mysqld重启以后,就会失效,在配置文件中更改可实现永久配置

slow_query_log=ON
slow_query_log_file=/var/lib/mysql/zzmr-slow.log
long_query_time=3
log_output=FILE

如果不指定存储路径,慢查询日志将默认存储到mysql数据库得数据文件夹下,默认文件名为hostname-slow.log

测试

先建表

CREATE TABLE student (
	id INT ( 11 ) NOT NULL auto_increment,
	stuno INT NOT NULL,
	NAME VARCHAR ( 20 ) DEFAULT NULL,
	age INT ( 3 ) DEFAULT NULL,
	class_id INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( id ) 
) ENGINE = INNODB auto_increment = 1 DEFAULT CHARSET = utf8;

然后使用数据生成器生成400万条数据

查询stuno为100100的列
20231005205009

用时超过了1s,然后多执行几次,即可记录到慢查询的次数:show status like 'slow_queries';
20231005205050

除了上面的变量,还有一个min_examined_row_limit,这个变量的意思是,查询扫描过的最少记录数,如果查询扫描过的记录数大于等于这个变量值,并且查询执行时间超过了long_query_time的值,这个查询就被确定为慢查询
这个值默认是0,与long_query_time=10合在一起,表示只要查询的时间超过10s,哪怕一个记录也没有扫描过,都要被记录到慢查询日志中.

11.4 慢查询分析工具mysqldumpslow

执行mysqldumpslow -help
2023-10-05205620

可查看命令帮助

分析日志,-s t表示用时间排序,-t 5表示只要5条数据

1	mysqldumpslow -s t -t 5 /var/lib/mysql/mysql-master-slow.log

20231005210133

这时是N和S代替了具体的数据,可以加入-a来显示数据
2023-10-0510347

11.5 关闭慢查询日志

可以更改配置文件中的slow_query_log=OFF

或者设置set global slow_query_log = off;

建议都加global和不加global执行一遍

慢查询日志都是使用mysqladmin flush-logs命令来删除重建的,使用时一定要注意,一旦执行了这个命令,慢查询日志都只存在新的日志文件中,如果需要旧的查询日志,就必须事先备份

然后直接rm删除文件即可

如果想再创建,执行

1	mysqladmin -uroot -p flush-logs slow

然后再开启慢查询,即可再次生成这个文件

11.6 查看sql执行成本:show profile

查看profile状态

1	show variables like 'profiling';

更改为on

1	set profiling = on

20231005212004

执行show profile
即可得到最近的操作
20231005212609

我们也可以查看指定的query ID的开销,比如show profile for query 2查询结果都是一样的,在show profile中我们可以查看不同部分的开销,比如cpu,block.io等

1	show profile cpu,block io for query 8;

屏幕截图2023-10-05212907

此时我们能看出,executing也就是执行时间最长,我么就可以使用explain来查看执行计划,继续分析sql的执行

23-10-0513056

屏幕截图023-10-05213210

11.7 分析查找工具 EXPLAIN

定位了慢查询的sql之后,我们就可以使用explain或describe工具做针对性的分析查询语句,两者没有区别

mysql中有专门负责select语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的query提供它人为最优的执行计划(它认为最优,但不见得DBA认为是最优的,这部分最耗费时间)

能做什么

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
那些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

注意,explain并没有执行该语句

explian语句输出的给字段的说明:
屏幕截图2023-10-06092154

数据准备

两张表
s1:

CREATE TABLE s1 (
	id INT auto_increment,
	key1 VARCHAR ( 100 ),
	key2 INT,
	key3 VARCHAR ( 100 ),
	key_part1 VARCHAR ( 100 ),
	key_part2 VARCHAR ( 100 ),
	key_part3 VARCHAR ( 100 ),
	common_field VARCHAR ( 100 ),
	PRIMARY KEY ( id ),
	INDEX idx_key1 ( key1 ),
	UNIQUE INDEX idx_key2 ( key2 ),
	INDEX idx_key3 ( key3 ),
INDEX idx_key_part ( key_part1, key_part2, key_part3 ) 
) ENGINE = INNODB CHARSET = utf8;

s2:

CREATE TABLE s2 (
	id INT auto_increment,
	key1 VARCHAR ( 100 ),
	key2 INT,
	key3 VARCHAR ( 100 ),
	key_part1 VARCHAR ( 100 ),
	key_part2 VARCHAR ( 100 ),
	key_part3 VARCHAR ( 100 ),
	common_field VARCHAR ( 100 ),
	PRIMARY KEY ( id ),
	INDEX idx_key1 ( key1 ),
	UNIQUE INDEX idx_key2 ( key2 ),
	INDEX idx_key3 ( key3 ),
INDEX idx_key_part ( key_part1, key_part2, key_part3 ) 
) ENGINE = INNODB CHARSET = utf8;

没错两张表一摸一样

table

查询的每一行记录都对应着一个单表

1	EXPLAIN SELECT * FROM s1;

20231006094108

如果查询涉及两张表,那么explain就会输出两条记录,s1称为驱动表,s2是被驱动表

1	EXPLAIN SELECT * FROM s1 INNER JOIN s2;

20231006094417

每条记录也是只对应一个表

id

在一个大的查询语句中每个select关键字都对应一个唯一的id

如果是一个select对应两张表,那么explain输出的记录中应该是两条id都是1

如果是一个子查询,那么id就是1,2了

但是如果查询优化器对涉及子查询的语句进行重写,那么结果可能就不一样了,查询优化器将查询语句重写,转变为一个多表查询的操作

当查询语句使用到了UNION时,还会出现临时表(如果是UNINO ALL,不涉及到去重,就不会出现临时表)

1	EXPLAIN SELECT * from s1 UNION SELECT * from s2;

20231006095814

小结

如果id相同,可以认为是一组,从上往下顺序执行
在同一组中,id值越大,优先级越高,越先执行
关注点:id号每个号码,表示一趟独立的查询,一个sql的人查询趟数越少越好

select_type

一条大的查询语句里面可以包含若干个select关键字,每个select关键字代表着一个小的查询语句,而每个select关键字的from子句都可以包含若干张表(这些表用来连接查询),每一张表都对应着执行计划输出中的一条记录,对于在同一个select关键字中的表来说,它们的id值是相同的

mysql为每一个select关键字代表的小查询都定义了一个称之为select_type的属性,意思是我们只要知道了某个小查询的select_type,就知道了这个小查询在整个大查询中扮演了一个什么角色,下面是select_type的取值

查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型
1
EXPLAIN SELECT * FROM s1 WHERE id = 1000;
对于包含UNINO或者UNINON ALL或者子查询的大查询来说,它是由几个小查询组成的,其中最左边的select_type的值是PRIMARY
1
2
3
EXPLAIN SELECT * from s1 UNION SELECT * from s2;

EXPLAIN SELECT * from s1 UNION ALL SELECT * from s2;
如果包含子查询的查询语句不能够转化为对应的连接查询,并且该子查询是不想关子查询,该子查询的第一个select关键字代表的那个查询的select_type就是SUBQUERY,若相关,就是DEPENDENT SUBQUERY

注意:select_type为DEPENDENT SUBQUERY的查询可能会被执行多次

对于包含派生表的查询,该派生表对应的子查询的select_type是DERIVED

但查询优化器在执行包含子查询的语句时,选择将子查询物化之后与外层查询进行连接查询时,该子查询对应的select_type属性就是MATERIALIZED

partitions(可略)

代表分区表的命中情况,非分区表,该项为null,一般情况下我们的查询语句的执行计划的partitions列的值都是null

type

针对单表的访问方法

执行计划的一条记录就代表着Mysql对某个表的执行查询时的访问方法,又称访问类型,其中的type列就表明了这个访问方法是啥,是较为重要的一个指标,比如看到type列的值是ref,表名mysql即将使用ref访问方法来执行对s1表的查询

完整的访问方法如下:

system,const,eq_ref,ref,fulltext,ref_or_null,index_merge,unique_subquery,index_subquery,range,index,ALL
当表中只有一条记录,并且该表使用的存储引擎的统计数据是精确的,比如myisam,memory,那么对该表的方法方法就是system

create table t(i int) ENGINE=MyISAM;

insert into t values(1);

EXPLAIN select * from t;

20231006202559

如果给表t再插入一条,再次执行explain,会发现type变成了all
20231006202714

但是如果存储引擎是INNODB中,由于并没有创建索引,所以就算是一条,也是ALL

const

当我们根据主键或者唯一二级索引列与常数进行等值匹配,对单表的访问方法就是const

1
2
3

EXPLAIN SELECT * FROM s1 WHERE id = 723;

EXPLAIN SELECT * FROM s1 WHERE key2 = 9118;

20231006203316

eq_ref

在连接查询时,如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话,所有的所i你列都必须进行等值比较),则对该比被驱动表的访问方法就是eq_ref

1	EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

20231006204035

ref

当通过普通二级索引列与常量进行等值匹配来查询某个表时,那么对该表的方法方法可能是ref

1 2	-- key1只是一个普通的二级索引 EXPLAIN SELECT * FROM s1 WHERE key1 = 'Tar6li8pOs';

20231006205218

null

当对普通二级索引进行等值匹配查询,该索引列的值也可以是null,那么对该表的访问方法就可能是ref_or_null

1	EXPLAIN SELECT * FROM s1 WHERE key1 = 'Tar6li8pOs' OR key1 is null;

20231006205158

intersection,union,sort-union

1	EXPLAIN SELECT * FROM s1 WHERE key1 = 'Tar6li8pOs' OR key3 = 'a';

此时是index_merge
20231006213615

unique_subquery是针对于在一些不包含in子查询的查询语句中,如果查询优化器决定将in子查询转换为exists子查询,而且子查询可以使用到主键进行等值匹配的话,那么该子查询执行计划的type列的值就是unique_subquery

range

如果使用索引获取某些范围空间的记录,那么就可能使用到range访问方法

1	EXPLAIN SELECT * FROM s1 WHERE id >= 1000;

20231006215734

index

当我们可以使用索引覆盖,但需要扫描全部索引记录时,该表的访问方法就是index

1
2

-- 查询的字段,和where中的字段属于同一个联合索引,但是where中并没有单列索引能用上,此时就可能用到这个联合索引
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'

20231006215950

All

最熟悉的全表扫描

1	EXPLAIN SELECT * FROM s1;

20231006220244

一般来说,这些访问方法中除了all外,其余的访问方法都能用到索引,除了index_merge外,其余的访问方法都最多只能用到一个索引

小结
结果值从最好到最坏依次是
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All

其中比较重要的几个,已用蓝色标出,sql性能优化的目标,至少要达到range级别,要求是ref级别,最好是const级别

possible_keys和key

在explain语句输出的执行计划中,possible_keys列表表示在某个查询语句中,对某个表执行单表查询时可能用到的索引有哪些,一般查询涉及到的字段上若存在索引,则该索引将被列处,但不一定被查询使用,key列表示实际用到的索引有哪些,如果为null,则表示没有使用索引

key_len

实际使用到的索引长度(即:字节数)

帮你检查是否充分利用上了索引,值越大越好

ref

当使用索引列等值查询时,与索引列进行等值匹配的对象信息,比如只是一个常数或者是某个列

1	EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

20231007150636

精确到字段

1	EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

20231007151250

函数

1	EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

20231007151607

rows

预估的需要读取的记录条数,值越小越好

1	EXPLAIN SELECT * FROM s1 WHERE key1> 'dfa';

20231007151835

filtered

某个表经过搜索条件过滤后剩余记录条数的百分比

如果使用的是索引执行的单表扫描,那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条,越高越好

EXPLAIN SELECT
	* 
FROM
	s1 
WHERE
	key1 > 'dfa' 
	AND common_field = 'a';

20231007152255

对于单表查询来说,这个filtered列的值没什么意义,我们更关注在连接查询中驱动表对应的执行计划记录的filtered值,它决定了被驱动表要执行的次数(即 rows*filtered)

extra

一些额外信息

包含不适合在其他列中显示但十分重要的额外信息,我么可以通过这些额外信息来更精确的理解Mysql到底将如何执行给定的查询语句,mysql提供的额外信息有好几十个

no tables used

当select语句中没有from时,就是no tables used

1	EXPLAIN select 1;

Impossible WHERE

当where条件永远不满足时

1	EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

Using where

当我们使用全表扫描来执行对某个表的查询时,并且该语句的where子句中有针对该表的搜索条件时,或者说是没有用到索引的情况

如果where中存在有索引的列同时存在没有索引的列,也是Using where

1
2
3

EXPLAIN SELECT * FROM s1 WHERE common_field = '2131';

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = '2131';

No matching min/max row

当查询列表处有MIN或者MAX聚合函数,但是并没有符合where子句中的搜索条件的记录时,就是没查找数据

1	EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = '3Q0AdDJqe6afa';

Using index

当我们的查询列表以及搜索条件中只包含属于某个索引的列,也就是在可以使用覆盖索引的情况下,在extra列将会提示该额外信息,比方说下边这个查询中值需要用到idx_key1而不需要回表操作

或者说,因为二级索引中包含该索引列以及主键,如果查询的就是索引列或者是索引列和主键列,那么就不需要回表(不需要在主键索引上再搜索),此时就是Using index

Using index condition

有些搜索条件中虽然出现了索引,但却不能使用索引

1	EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 like '%a';

20231007160533

Using where; Using join buffer (hash join)

在连接查询执行过程中,当被驱动表不能有效的利用索引加快访问速度,mysql一般会为其分配一块名叫join buffer的内存块来加快查询速度,也就是我们所讲的基于块的嵌套循环算法

1	EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

20231007161312

not exists

当我们使用左外连接时,如果where子句中包含要求被驱动表的某个列等于null值得搜索条件,而且那个列又是不允许存储null值得,那么在该表得执行计划得extra列表就会提示not exists

……

Using filesort

排序时并没有索引能用到

1	EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

mysql把在内存中或者磁盘上进行排序的方式称为文件排序

小结

EXPLAIN不考虑各种Cache
EXPLAIN不能显示Mysql在执行查询时所作的优化工作
EXPLAIN不会告诉你关于触发器,存储过程的信息或用户自定义函数对查询的影响情况
部分统计信息是估算的,并非精确值

11.8 EXPLAIN的进一步使用

EXPLAIN四种输出格式

传统格式,JSON格式,TREE格式,可视化输出

传统格式
传统格式简单明了,输出是一个表格形式,概要说明查询计划

之前写的都是传统格式

JSON格式
传统格式输出中缺少了一个衡量执行计划好坏的重要属性–成本,而JSON格式是四种格式里面输出信息最详尽的格式,里面包含了执行的成本信息

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2813.40"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "s1",
          "access_type": "ALL",
          "possible_keys": [
            "PRIMARY",
            "idx_key1"
          ],
          "rows_examined_per_scan": 10143,
          "rows_produced_per_join": 5071,
          "filtered": "50.00",
          "cost_info": {
            "read_cost": "531.45",
            "eval_cost": "507.10",
            "prefix_cost": "1038.55",
            "data_read_per_join": "8M"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ],
          "attached_condition": "(`zzmrdb1`.`s1`.`key1` > 'dafa')"
        }
      },
      {
        "table": {
          "table_name": "s2",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "4",
          "ref": [
            "zzmrdb1.s1.id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 5071,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1267.75",
            "eval_cost": "507.10",
            "prefix_cost": "2813.40",
            "data_read_per_join": "8M"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ]
        }
      }
    ]
  }
}

read_cost是由下边这两部分组成

IO成本
检查rows * (1-filter)条记录的CPU成本

TREE格式

-> Nested loop inner join  (cost=2813.40 rows=5071)
    -> Filter: (s1.key1 > 'dafa')  (cost=1038.55 rows=5071)
        -> Table scan on s1  (cost=1038.55 rows=10143)
    -> Single-row index lookup on s2 using PRIMARY (id=s1.id)  (cost=0.25 rows=1)

11.9 分析优化器执行计划:trace

OPTIMIZER_TRACE是一项跟踪功能,它可以跟踪优化器做出的各种决策(比如访问表的方法,各种开销计算,各种转换等),并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中

此功能默认关闭,开启trace,并设置格式为json,同时设置trace最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整展示

1
2
3

set optimizer_trace="enabled=on",end_markers_in_json=on;

set optimizer_trace_max_mem_size=1000000;

先执行一个简单的sql:

1	select * from student where id < 10;

然后查看INFORMATION_SCHEMA表中的信息

1	select * from information_schema.optimizer_trace\G;

得到的结果:

// 第一部分:查询语句
 QUERY: select * from student where id < 10
// 第二部分:QUERY字段对应的语句的跟踪信息
 TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `student`.`id` AS `id`,`student`.`stuno` AS `stuno`,`student`.`NAME` AS `NAME`,`student`.`age` AS `age`,`student`.`class_id` AS `class_id` from `student` where (`student`.`id` < 10)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`student`.`id` < 10)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`student`.`id` < 10)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`student`.`id` < 10)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`student`.`id` < 10)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`student`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`student`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 3989503,
                    "cost": 410837
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "PRIMARY",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ] /* potential_skip_scan_indexes */
                  } /* skip_scan_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "id < 10"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 9,
                        "cost": 1.91995,
                        "chosen": true
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 9,
                      "ranges": [
                        "id < 10"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 9,
                    "cost_for_plan": 1.91995,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`student`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 9,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "PRIMARY"
                      } /* range_details */,
                      "resulting_rows": 9,
                      "cost": 2.81995,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 9,
                "cost_for_plan": 2.81995,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`student`.`id` < 10)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`student`",
                  "attached": "(`student`.`id` < 10)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`student`",
                "original_table_condition": "(`student`.`id` < 10)",
                "final_table_condition   ": "(`student`.`id` < 10)"
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`student`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
// 第三部分:跟踪信息过长时,被截断的跟踪信息的字节数
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0  // 丢失的超出最大容量的字节
// 第三部分:执行跟踪语句的用户是否有查看对象的权限,当不具权限时,该列信息为1且trace字段为空,一般在调用带有sql security definer的视图或者是存储过程的情况下,会出现此问题
INSUFFICIENT_PRIVILEGES: 0 // 缺失权限
1 row in set (0.00 sec)

12 索引优化与查询优化

哪些维度可以进行数据库调优?

索引失效,没有重复利用到索引–索引建立
关联查询太多join(设计缺陷或不得已的需求)–SQL优化
服务器调优及各个参数设置(缓冲,线程数等)–调整my.cnf
数据过多–分库分表

虽然SQL查询优化的技术有很多,但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块

物理查询优化是通过索引和表连接方式等技术来进行优化
逻辑查询优化就是通过SQL等价变换提升查询效率,换一种查询写法执行效率可能更高

12.1 数据准备

两张表:

CREATE TABLE class (
	id INT ( 11 ) NOT NULL auto_increment,
	class_name VARCHAR ( 30 ) DEFAULT NULL,
	address VARCHAR ( 40 ) DEFAULT NULL,
	monitor INT NULL,
	PRIMARY KEY ( id ) 
) ENGINE = INNODB auto_increment = 1 DEFAULT CHARSET = utf8;
CREATE TABLE student (
	id INT ( 11 ) NOT NULL auto_increment,
	stuno INT NOT NULL,
	NAME VARCHAR ( 20 ) DEFAULT NULL,
	age INT ( 3 ) DEFAULT NULL,
	class_id INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( id ) 
) ENGINE = INNODB auto_increment = 1 DEFAULT CHARSET = utf8;

班级表中插入10000条数据,学生表中插入500000条

12.2 索引失效案例

mysql中提高性能的一个最有效的方式就是对数据表设计合理的索引,索引提高了高校访问数据的方法,并且加快查询速度,因此索引对查询的速度有着至关重要的影响

SQL语句是否使用索引,跟数据库版本,数据量,数据选择度都有关系

全值匹配

像这样的三条sql,如果没有索引,那么执行时间大概都在0.140s上下

SELECT * FROM student WHERE age = 30;

SELECT * FROM student WHERE age = 30 AND class_id = 4;

SELECT * FROM student WHERE age = 30 AND class_id = 5 AND `NAME` = 'abcd';

如果加入索引,那么上面的执行时间就变成了0.020s上下

1
2
3

CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_cid ON student(age,class_id);
CREATE INDEX idx_age_cid_name ON student(age,class_id,name);

最佳左前缀法则

在mysql建立联合索引时会遵守最佳左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配

-- 这条sql只会用到age索引
EXPLAIN SELECT * FROM student WHERE age = 30 AND NAME = 'abcd';
-- 这条sql不会使用到索引
EXPLAIN SELECT * FROM student WHERE class_id = 1 AND NAME = 'abcd';
-- 这条sql就会使用到上面的联合索引
EXPLAIN SELECT * FROM student WHERE class_id = 1 AND age = 12 AND NAME = 'abcd';

索引是age,class_id,name这个顺序的,如果where条件中同时存在这三个条件,顺序无所谓,因为优化器会自动调整顺序,就会使用到这三个字段的联合索引,但是如果是age,name,这种,由于中间隔上了一个class_id,如果没有其他索引时,依然会使用这个联合索引,但是key_len只有5(int为4加一个null 1),相当于只使用了age

Mysql可以为多个字段创建索引,一个索引可以包括16个字段,对于多列索引,过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用,如果查询条件中没有使用这些字段中第1个字段时,这个联合索引就不会被使用

主键插入顺序

对于一个使用InnoDB存储引擎的表来说,在我们没有显式的创建索引时,表中的数据实际上都是存储在聚簇索引的叶子结点的,而记录又是存储在数据页中,数据页和记录又是按照记录主键值从小到大的顺序排序的,所以如果我们插入的记录的主键值是依次增大的话,那我们每插满一个数据页就换到下一个数据页继续插,而如果我们插入的主键值忽大忽小,就比较麻烦了,假设某个数据页存储的记录已经满了,它存储的主键值在1~100之间

让主键具有AUTO_INCREMENT,让存储引擎自己为表生成主键,而不是我们手动插入

计算,函数,类型转换(自动或手动)导致索引失效

先创建一个name上的索引

1	CREATE INDEX idx_name ON student(name);

看下面两个sql,哪个效率高?第一个,因为会使用到索引

1
2
3

EXPLAIN SELECT * FROM student WHERE name like 'abc%';

EXPLAIN SELECT * FROM student WHERE LEFT(name,3) = 'abc';

20231009220734

再来看一个例子

给stuno设置上索引

1	CREATE INDEX idx_stuno ON student(stuno);

看下面这两个sql

1
2
3

EXPLAIN SELECT * FROM student WHERE stuno+1 = 9001;

EXPLAIN SELECT * FROM student WHERE stuno = 9000;

由于给stuno加了1,就没使用到索引,但是两边同时减一,这时就可以使用到索引了

出现类型转换,也会导致索引失效

1	EXPLAIN SELECT * FROM student WHERE name = 123;

name上是有索引的,但是由于name是varchar类型,而这里给的是整型,涉及到类型转换,就会导致索引失效

范围条件右边的列索引失效

先删除之前写的索引,只留下主键索引

再创建一个联合索引

1	CREATE INDEX idx_age_cid_name ON student(age,class_id,NAME);

那么下面这个sql,会使用到索引吗?

1	EXPLAIN SELECT * FROM student WHERE age = 30 AND class_id > 20 AND name = 'abc';

20231009225805
会的,不过不会完全使用,从执行结果来看,key_len=10,说明只用上了age和class_id,因为这两个字段都是int型的,占4个字节,又默认为空,所以是4+4+2=10,而最后的name没有用上索引,因为范围条件右边的列索引会失效,这里的右是建立索引时的右,该联合索引是age,cid,name,由于cid是范围条件,索引name就失效了,解决办法是在创建索引时,将范围条件放到最后

1	CREATE INDEX idx_age_name_cid ON student(age,NAME,class_id);

此时再执行上面的sql,就会发现key_len变成了73(4+4+2+63)
20231009230247

<,<=,>,>=和between等,都可能会导致索引失效

应用开发中范围查询,例如:金额查询,日期查询往往都是范围查询,这些字段在创建联合索引时都可以放到最后

不等于(!=或者<>)索引失效

先给name上建立一个索引

1	CREATE INDEX idx_name ON student(NAME);

然后执行下面的sql

1
2
3

EXPLAIN SELECT * FROM student WHERE name = 'adfas';

EXPLAIN SELECT * FROM student WHERE name != 'adfas';

会发现,等于的是肯定能用上索引的,但是不等于就用不上了,变成了all

但是这并不是一定的,看后面的覆盖索引的列子

is null可以使用,is not null失效

1
2
3

EXPLAIN SELECT * FROM student WHERE age is null;

EXPLAIN SELECT * FROM student WHERE age is not null;

is not null时,还是会遍历B+树,所以用不上索引,导致索引失效

结论,最好在设计数据表的时候就将字段设置为NOT NULL约束,比如你可以将INT类型的字段,默认值设置为0,将字符类型的默认值设置为空字符串,同理,在查询时not like也无法使用索引

like以通配符%开头索引失效

不是绝对的

在使用Like关键字进行查询的查询语句中,如果匹配字符串的第一个字符为%,索引就不会起作用,只有%不在第一个位置,索引才会起作用

1 2	EXPLAIN SELECT * FROM student WHERE name like 'a%'; EXPLAIN SELECT * FROM student WHERE name like '%a';

页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决-Alibaba

or前后存在非索引的列,索引失效

这种情况就使用不上索引,相当于求并集了,很好理解

1	EXPLAIN SELECT * FROM student WHERE name = 'afd' OR class_id = 213;

但是如果是这样,由于age上也是有索引的,所以会出现index_merge,同时使用到两个索引,最后合并

1	EXPLAIN SELECT * FROM student WHERE name = 'afd' OR age = 213;

数据库和表的字符集统一使用utf8mb4

不同的字符集进行比较前需要进行转换,导致索引失效

小练习,设有索引index(a,b,c)
023-10232359
20231009232542

总结

对于单列索引,尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好
在选择组合索引的时候,尽量选择能够包含当前query中的where子句中更多字段的索引
在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面

总之,书写sql时要尽量避免造成索引失效的情况

12.3 关联查询优化

数据准备,两张表:

CREATE TABLE type (
id int(10) UNSIGNED not null auto_increment,
card int(10) UNSIGNED not null,
PRIMARY KEY(id)
);

CREATE TABLE book(
book_id int(10) UNSIGNED not null auto_increment,
card int(10) UNSIGNED not null,
PRIMARY KEY(book_id)
);

左外连接

EXPLAIN SELECT
	id,
	type.card tcard,
	book_id,
	book.card bcard 
FROM
	type
	LEFT JOIN book ON type.card = book.card

两个表都没有card列的索引,当执行上述sql时,查询流程就是先从驱动表中拿出一条数据,然后遍历被驱动表,挨个比较,然后下一条,继续重复这样的操作,如果type表中有20条,book表中有30条,那么就会进行20*30=600次比较操作

20231010201122

添加card列索引

1	CREATE INDEX idx_bcard ON book(card);

这时就能发现book表的type为ref,也就是用到了索引
20231010201258

给type表的card列添加索引,同样可以使用到

注意,这里的card必须类型一样,不然使用不上索引

内连接

**JOIN和INNER JOIN**一个意思

-- 内连接
EXPLAIN SELECT
	id,
	type.card tcard,
	book_id,
	book.card bcard 
FROM
	type
  JOIN book ON type.card = book.card

先删掉之前的索引,只留下主键索引,这时执行上述sql,type都是ALL

然后给被驱动表(book)的card添加索引

结果和外连接是一样的,都可以使用到索引

这时给驱动表的card也添加索引

再去执行explain,此时可能会出现type,book也就是驱动表和被驱动表互换的结果,因为这是内连接,只要能够匹配上的,所以优化器会对两个表进行优化

然后删掉被驱动表(book)的card索引
再去执行explain,这时就能看出book和type发生了互换
20231010203443

也就是说,如果内连接的条件,只有一个表的字段是有索引的,这时就会将这个表作为被驱动表

然后把索引都加回来,给type表再加入20条数据

现在,book表中有20条,type表中有40条,继续执行上面的explain,会发现,驱动表和被驱动表又反转了
20231010204455

这是因为:对于内连接来说,在两个表的连接条件都存在索引的情况下,会选择小表,数据量小的表作为驱动表,也就是小表驱动大表

JOIN语句原理

join方式连接多个表,本质上就是各个表之间数据的循环匹配,mysql5.5版本之前,mysql只支持一种表间关联方式,就是嵌套循环,如果关联表的数据量很大,则join关联的执行时间会非常长,在mysql5.5之后,mysql版本引入BNLJ算法来优化嵌套执行

驱动表和被驱动表

驱动表就是主表,被驱动表就是从表,非驱动表

对于内连接来说

1	select * from A join B on ...

A一定是驱动表吗?不一定,优化器会根据你的查询语句做优化,决定先查哪张表,先查询的那张表就是驱动表,反之就是被驱动表,通过explain关键字可以查看

对于外连接来说呢?

1
2
3

select * from a left join b on ...

select * from b right join a on ...

先准备数据

CREATE TABLE a (
	f1 INT,
	f2 INT,
INDEX ( f1 ));
CREATE TABLE b (
	f1 INT,
	f2 INT 
)

INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8)

执行以下sql

EXPLAIN SELECT
	* 
FROM
	a
	LEFT JOIN b ON ( a.f1 = b.f1 ) 
WHERE
	( a.f2 = b.f2 );

20231010210413
20231010210805

会发现,查询优化器把这里的外连接改成了内连接,然后颠倒了顺序

Simple Nested-Loop Join(简单嵌套循环连接)

算法相当简单,从表A中取出一条数据1,遍历表B,将匹配到的数据放到result,以此类推,驱动表A中的每一条记录与被驱动表B的记录进行判断

20231010212600
可以看到这种方式效率是非常低的,以上述表A数据100条,表B数据1000条计算,则A*B=10万次,开销统计如下:
20231010212750

当然mysql不会这么粗暴的去进行表的连接,所以就出现了后面的两种对Nested-Loop Join优化算法

Index Nested-Loop Join(索引嵌套循环连接)

Index Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数,所以要求被驱动表上必须有索引才行,通过外层表匹配条件直接与内层表索引进行匹配,避免和内层表的每条记录去进行比较,这样极大的减少了对内层表的匹配次数

20231010213813

驱动表中的每条记录通过被驱动表的索引进行访问,因为索引查询的成本是比较固定的,故mysql优化器都倾向于使用记录数少的表作为驱动表

20231010214213

如果被驱动表加索引,效率是非常高的,但是如果索引不是主键索引,所以还是得进行一次回表操作,相比,被驱动表得索引是主键索引,效率会更高

Block Nested-Loop Join(块嵌套循环连接)

如果存在索引,那么会使用index得方式进行join,如果join得列没有索引,被驱动表要扫描得次数太多了,每次访问被驱动表,其表中得记录都会被加载到内存中,然后再从驱动表中取一条与其匹配,匹配结束后清楚内存,然后再从驱动表中加载一条记录,然后把驱动表得记录在加载到内存中匹配,这样周而复始,大大增加了IO次数

不再是逐条获取驱动表的数据,而是一块一块得获取,引入了join buffer缓冲区,将驱动表join相关得部分数据列(大小受join buffer的限制),缓存到join buffer中,然后全表扫描被驱动表,被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配,将简单嵌套循环中的多次比较合并成一次,降低了被驱动表的访问频率

注意
这里缓存的不只是关联表的列,select后面的列也会缓存起来

在一个有N个join关联的sql中分配N-1个Join buffer,所以查询的时候尽量减少不必要的字段,可以让join buffer中可以存放更多的列

2023-10-1021563

20231010215841

查看block_nested_loop的状态,默认是开启的

1	show variables like '%optimizer_switch';

block_nested_loop=on

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

查看join buffer的大小

1	SHOW VARIABLES like '%join_buffer%';

默认是262144,也就是256KB

Join小结

整体效率:INLJ>BNLJ>SNLJ
永远用小结果集驱动大结果集(本质是减少外层循环的数据数量)
为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)
增大join buffer size的大小(一次缓存的数据越多,那么内层包的扫表次数就越少)
减少驱动表不必要的字段查询

Hash Join

从mysql8.0.20版本开始将废弃BNLJ,因为从mysql8.0.18版本开始加入了hash join默认都会使用hash join

Nested Loop
对于被连接的数据子集较小的情况,Nested Loop是个较好的选择
Hash Join是做大数据集连接时的常用方式,优化器使用两个表中较小的表利用Join Key在内存中建立散列表,然后扫描较大的表并探测散列表,找出与Hash表匹配的行
- 这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和
- 在表很大的情况下并不能完全放入内存,这时优化器会将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要求有较大的临时段从而尽量提高IO的性能
- 它能够很好的工作于没有索引的大表和并行查询的环境中,并提供最好的性能,大多数人都说它是Join的重型升降机,Hash Join只能应用于等值连接,这是由Hash的特点决定的

12.4 子查询优化

使用子查询可以进行select语句的嵌套查询,即一个select查询的结果作为另一个select语句的条件,子查询可以一次性完成很多逻辑上需要多个步骤才能完成的sql操作

但是子查询的效率并不高

执行子查询时,mysql需要为内层查询语句的查询结果建立一个临时表,这样会消耗过多的CPU和IO资源,产生大量的慢查询
子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响
对于返回结果集比较大的子查询,其对查询性能的影响也就越大

在mysql中可以使用连接查询来代替子查询,连接查询不需要建立临时表,其速度比子查询要快,如果查询中使用索引的话,性能就会更好

12.5 排序优化

问题:在WHERE条件字段上加索引,但是为什么在ORDER BY字段上还要加索引呢?

在mysql中支持两种排序方式,分别是fileSort和Index排序

index排序中,索引可以保证数据的有序性,不需要再进行排序
FileSort排序则一般在内存中进行排序,占用CPU较多,如果待排结果较大,会产生临时文件IO到磁盘进行排序的情况,效率较低

优化建议

sql中,可以在where子句和order by子句中使用索引,目的是在where子句中避免全表扫描,在order by子句中避免使用FileSort排序,当然,某些情况下全表扫描,或者FileSort排序不一定比雨索引慢,但总的来说,我们还是要避免,以提高查询效率
尽量使用Index完成Order by排序,如果where和order by后面是相同的列就是用单列索引,如果不同就使用联合索引
无法使用index时,需要对FileSort方式进行调优

删除student和class表中的索引,只留下主键索引

没有索引,下面的sql必然是filesort

1
2
3

EXPLAIN SELECT * FROM student ORDER BY age,class_id;

EXPLAIN SELECT * FROM student ORDER BY age,class_id LIMIT 10;

20231012093700

Order by时不limit,索引失效
此时添加索引:

1	CREATE INDEX idx_age_cid_name ON student(age,class_id,NAME);

再执行上面的explain,会发现,加了limit的语句会用到索引,而没加的还是filesort

为什么没用呢?因为没加limit的话,总是要拿到全部数据的,使用的又是二级索引,所以根据二级索引拿到主键再次回表,效率也不高

但是如果将*改成age,class_id,会发现又用到了索引

1	EXPLAIN SELECT age,class_id FROM student ORDER BY age,class_id;

20231012095246

这个就叫覆盖索引,查询列都在索引列中,使用二级索引又不需要回表

Order by时顺序错误,索引失效
先创建一个索引

1	CREATE INDEX idx_age_cid_sno ON student(age,class_id,stuno);

以下语句,1,2会索引失效

EXPLAIN SELECT * FROM student ORDER BY class_id LIMIT 10;

EXPLAIN SELECT * FROM student ORDER BY class_id,name LIMIT 10;

EXPLAIN SELECT * FROM student ORDER BY age,class_id,stuno LIMIT 10;

EXPLAIN SELECT * FROM student ORDER BY age,class_id LIMIT 10;

EXPLAIN SELECT * FROM student ORDER BY age LIMIT 10;

order by时规则不一致,索引失效(顺序错,不索引,方向反,不索引)

无过滤不索引

小结

100723

案例实战

Order By子句,尽量使用index方式排序,避免使用FileSort排序

执行案例前先删除student之前的索引

1 2	-- 查询年龄小于20，学号小于101000，并且按照姓名排序 EXPLAIN SELECT * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY NAME;

没有索引了,此时肯定是filesort

为了去掉filesort我们可以建立索引
因为stuno是范围,就不用考虑建立索引了,只需要给age,name建立索引即可

1	CREATE INDEX idx_age_name ON student(age,NAME);

这样就可以用到索引了
20231012103427

所有的排序都是在条件过滤之后才执行的,所以,如果条件过滤掉大部分数据的话,剩下几百几千条数据进行排序其实并不是很消耗性能,即使索引优化了排序,但实际提升性能很有限,相对的stuno<101000这个条件,如果没有用到索引的话,要对几万条的数据进行扫描,这是非常消耗性能的,所以索引放在这个字段上性价比最高,是最优选择

结论

两个索引同时存在,mysql自动选择最优的方案,但是随着数据量的变化,选择的索引也会随之变化的
当范围条件和group by或者order by的字段出现二选一时,优先观察字段的过滤数量,如果过滤数据足够多的话,而需要排序的数据并不多时,优先把索引放在范围字段上

对filesort调优,可以尝试提高sort_buffer_size或者提高max_length_for_sort_data

还有就是,order by时使用*是大忌

12.6 Group BY优化

group by使用索引的原则集合跟order by一致，group by即使没有过滤条件用到索引，也可以直接使用索引
group by先排序再分组,遵照索引建的最佳前缀法则
group by当无法使用索引列,增大max_length_for_sort_data和sort_buffer_size参数设置
where效率高于having,能写where限定的条件就不要写having
减少使用order by,和业务沟通能不排序就不排序,或将排序放到程序端去做
包含了order by,group by,DISTINCT,这些查询的语句,where条件过滤出来的结果集保持在1000行以内,否则sql会很慢

12.7 分页查询优化

一般分页查询时,通过创建覆盖索引能够比较好地提高性能,一个常见的问题就是limit 2000000,10,此时需要mysql排序前2000010记录,仅仅返回2000000-2000010的记录,其他记录丢弃,查询排序的代价非常大

优化思路一
在索引上完成排序分页操作,最后根据主键关联回表串所需的其他列内容

1	EXPLAIN select * from student t,(select id from student order by id limit 2000000,10) a where t.id = a.id

优化思路二
该方案适用于主键自增的表,可以把limit查询转换成某个位置的查询

1	EXPLAIN select * from student where id>2000000 limit 10;

12.8 优先考虑覆盖索引

什么是覆盖索引

索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行,毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了,一个索引包含了满足查询结果的数据就叫覆盖索引
非聚簇复合索引的一种形式,它包括在查询里的select,join和where子句用到的所有列(即建索引的字段正好是覆盖查询条件中所涉及的字段)

简单来说,索引列+主键包含SELECT到FROM之间查询的列

首先将之前student的索引全部删除,然后创建一个

1	CREATE INDEX idx_age_name ON student(age,name);

<>和!=是一样的
执行以下sql

1	EXPLAIN SELECT * FROM student WHERE age <> 20;

但是如果改成这样的,上面建立的索引是age,name,二级索引又包含主键,所以这里就用到覆盖索引,key_len=5,说明是用到了age(4+1)

1	EXPLAIN SELECT id,age,NAME FROM student WHERE age <> 20;

20231012192528

看下面这个例子,前面讲过%开头可能会导致索引失效

1	EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

但是修改一下,会发现这样就用到了索引,其实这里也是优化器认为不用回表了,就使用了索引

1	EXPLAIN SELECT id,age,NAME FROM student WHERE NAME LIKE '%abc';

覆盖索引的利弊

好处

避免InnoDB表进行索引的二次查询(回表)
InnoDB是以聚簇索引的顺序来存储的,对于InnoDB来说,二级索引在叶子节点中所保存的行的主键信息,如果是用二级索引查询数据,在查找到相应的键值后,还需要通过主键进行二次查询才能获取我们真实所需要的数据,在覆盖索引中,二级索引的键值中可以获取所要的数据,避免了对主键的二次查询,减少了IO操作,提升了查询效率
可以把随机IO变成顺序IO加快查询速度
由于覆盖索引是按键值的顺序存储的,对于IO密集型的范围查找来说,对比随机从磁盘读取每一行的数据IO少得多,因此利用覆盖索引在访问时可以把磁盘的随机读取的IO转变成索引查找的顺序IO

弊端

索引字段的维护总是有代价的,因此,在建立冗余索引来支持覆盖索引时就需要权衡考虑了

12.9 索引下推

使用前后对比

Index Condition Pushdown(ICP)是一种在存储引擎层使用索引过滤数据的优化方式

如果没有ICP,存储引擎会遍历索引以定位基表中的行,并将它们返回给mysql服务器,由mysql服务器评估where后面的条件是否保留行
启用ICP后,如果部分where条件可以仅使用索引中的列进行筛选,则mysql服务器会把这部分where条件放到存储引擎筛选,然后,存储引擎通过使用索引条目来筛选数据,并且只有在满足这一条件时才从表中读取行
- 好处:ICP可以减少存储引擎必须访问基表的次数和mysql服务器必须访问存储引擎的次数
- 但是ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例

举例一

都是key1,ICP的流程:在使用key1的索引找到大于z的行后,这时不会立即回表查询,而是再继续判断后面的条件,假设大于z的有100行,原本可能需要回表100次,然后拿出符合结果的10行,但是ICP可以直接回表10次

1	EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

举例二

-- 建表
CREATE TABLE people (
	id INT NOT NULL auto_increment,
	zipcode VARCHAR ( 20 ) DEFAULT NULL,
	firstname VARCHAR ( 20 ) DEFAULT NULL,
	lastname VARCHAR ( 20 ) DEFAULT NULL,
	address VARCHAR ( 50 ) DEFAULT NULL,
	PRIMARY KEY ( id ),
	INDEX zip_last_first ( zipcode, lastname, firstname ) 
) ENGINE = INNODB auto_increment = 5;

然后插入几条数据
20231012200728

执行以下sql

EXPLAIN SELECT
	* 
FROM
	people 
WHERE
	zipcode = '000001' 
	AND lastname LIKE '%张%' 
	AND address LIKE '%北京市%';

此时是可以用到索引的,而且用到了索引下推
20231012202416

ICP开启/关闭

默认情况下启用索引条件下推,可以通过设置系统变量optimizer-switch控制

开启或关闭

1
2
3

SET optimizer_switch = 'index_condition_pushdown=on';

SET optimizer_switch = 'index_condition_pushdown=off';

12.10 其他查询优化

exists和in的区分

不太理解哪种情况下应该使用exists,那种情况下应该用in,选择的标准是看能否使用表的索引吗?

回答:索引是个前提,其实选择与否还是要看表的大小:小表驱动大表

count(*)和count(具体字段)

在mysql中统计数据表的行数,可以使用三种方式:select count(*),select count(1)和select count(具体字段)

如果你要统计的是某个字段的非空数据行数,则另当别论,毕竟比较执行效率的前提是结果一样才可以

2023-10-12205053

一句话:在InnoDB存储引擎上,count的字段最好是二级索引

关于select(*)

在表查询中,建立明确字段,不要使用*作为查询的字段列表

mysql在解析的过程中,会通过查询数据字典将*按序转换成所有的列名,这会大大的耗费资源和时间
无法使用覆盖索引

(不会有人给全表的字段都建立联合索引吧)

Limit 1对优化的影响

针对的会扫描全表的SQL语句,如果你可以确定结果集只有一条,那么加上Limit 1的时候,当找到一条结果的时候就不会继续扫描了,这样会加快查询速度

如果数据表已经对字段建立了唯一索引,那么可以通过索引进行查询,不会全表扫描的话,就不需要加上Limit 1了

多使用Commit

只要有可能,在程序中尽量多使用Commit,这样程序的性能得到提高,需求也会因为commit所释放的资源而减少

commit所释放的资源

回滚段上用于恢复数据的信息
被程序语句获得的锁
redo/undo log buffer中的空间
管理上述3种资源中的内部花费