小强

轻松构建微服务之搞定mysql索引

2019/02/08

mysql架构

下面是一张非常经典的mysql架构图,尽管随着mysql版本的升级,会有微小的变化,但是不影响我们理解其内部的工作流程

连接器

当客户端连接到数据库的时候,首先是通过连接池组件来管理TCP连接,连接池中管理的有长连接和短连接,短连接一般执行几条sql后就释放,而长连接在执行完sql后会hold住这个连接,如果客户端继续有请求会继续使用这个连接

客户端创建连接的时候会做用户的认证和鉴权,例如校验用户名和密码,并且在这个时候拉取用户的权限并缓存起来,在后续执行具体sql的时候会判断是否有对应的权限.

mysql创建连接的开销是很大的,所以一般建议使用长连接,客户端也建议维护一个连接池来复用,长连接会有个超时时间默认8小时,超过这个时间mysql会断开这个连接,并且客户端是不知道的,如果客户端继续使用这个连接会报Lost connection form mysql server during query,客户端每创建一个连接,这个连接在执行sql期间所使用到的内存都会由这个连接来维护,只有在连接断开的时候才会释放内存,所以如果有太多长连接会消耗服务器内存导致OOM,所以需要设置超时时间,有时候DBA会手动KILL一个连接,这种方式主动断开连接客户端也是不知道的,继续使用这个连接会报异常.

解析器

当获取到连接后,客户端会将SQL语句发给mysql,而sql接口层会接收到sql语句,并交给SQL解析器,SQL语句有严格的语法规则,需要特定的解析程序将sql做词法分析成语法树,这里也会有语义检查,例如sql虽然满足规范,但是表名或字段不存在等也需要检查.

优化器

为了加快查询速度,mysql会有查询缓存模块,当要查询的语句在缓存命中就可以直接返回,不需要执行器去存储引擎读磁盘,但是mysql8.0已经去掉了这个功能,原因是,这个查询缓存是基于整个表维护的,只要这个表有任何一个更新操作就会将这个表的所有查询缓存全部清空,所以对于写频繁的表,查询缓存不仅起不到缓存的效果还增加了判断和清空操作的消耗,这种查询缓存只有针对读多写少的业务才能真正发挥作用.

优化器是对用户的sql语句进行查询优化,在一个表里有多个索引的时候决定使用什么索引,或者在join操作的时候选择连接顺序,最后生成执行计划,大部分时候优化器都会给出正确的结果,但是有时候优化器也会选错索引,因为优化器是根据数据页的统计来进行判断的,如果mysql选错了索引,我们可以在sql中加上 force index来强制走某个索引.

执行器

执行器在拿到执行计划后会先判断是否有对应的权限,然后会调用和存储引擎的接口进行查询和写操作,例如存储引擎提供给执行器的接口有;获取满足条件的第一行,获取满足结果的下一行等.

我们可以通过下图来回顾下mysql执行一条查询的流程

存储引擎

MYSQL的存储引擎支持插拔模式,这些存储引擎根据不同的业务有不同的实现,例如内存存储的memery,以及支持行锁的Innodb,不过他们都和执行器有统一的读写接口,我们以目前流行的Innodb为例来分析.

在存储引擎中如果每一次写请求都需要落盘,更新请求需要先在磁盘上找到对应的记录,在更新,这是一个随机写的场景,大家都知道无论什么类型的磁盘,随机写的速度都比内存的读写速度和顺序写的速度差很远,那么mysql要提供高并发的读写能力,就是用到了WAL的模式,也就是write ahead logging,也就是先写日志,等到空闲或者日志满了在将日志一起flush到磁盘,而日志是顺序写的.

具体来讲,当mysql收到一条update更新请求后,会先将请求写到redolog中,并更新内存这个过程就算完成了,Innodb的redolog文件大小是固定的,一般分4个文件,每个文件1GB,维护两个指针记录当前写的位置,和擦除的位置,擦除的时候需要把记录更新到磁盘中,相当于一个循环队列,一边顺序写,一边顺序读.

提到redolog大家肯定会想到binlog,而binlog是mysql的server层维护的,redolog是innodb特有的,也正是依托于redolog才让innodb有crashsafe的能力,由于涉及到需要同时写两个文件,所以会用到两阶段提交协议,需要redolog和binlog同时写成功,一个update语句才算成功,因为binlog是mysql高可用的保障,mysqll会通过binlog将操作复制到slave机器上,如果不采用两阶段提交会导致主备不一致.那么我们来看一下一条updat语句的执行流程

update T set c = c + 1 where id = 1;
  • 1.执行器先找存储引擎取ID=1这一行记录,如果ID=1这一行记录所在的数据页本来就在内存,直接取出,如果不在还要去磁盘读取数据页.
  • 2.执行器拿到数据将C的值加1,获取到新的行后,调用存储引擎的接口,将数据写入存储引擎
  • 3.存储引擎将这行记录所在的数据页在内存中先更新,然后写入redolog,状态为prepare,然后告诉执行器执行完了,可以提交事务.
  • 4.执行器收到存储引擎返回执行成功的消息后,生成binlog日志,并将日志写入磁盘,然后调用存储引擎的接口提交事务.
  • 5.执行引擎收到提交事务的请求后,会将redolog的状态改为commited

以上事务和数据库本身的事务没有关系,这里指分布式事务里面的二阶段提交,具体流程可以参考下图

这里提到两个log文件,一个binlog一个redolog,为避免混淆,我们做一下区分

  • 1.redolog是innodb特有的,binlog是mysql server层实现的,所有存储引擎都可以使用
  • 2.redolog是物理日志,记录的是某个数据页做了什么修改,在空闲的时候会根据redolog里的日志更新数据页,数据页就是真正存储原始数据的物理存储空间
  • 3.binlog是逻辑日志,记录的是某个sql的原始逻辑,例如给ID=1这一行的C加1,这个日志可以被其他slave订阅
  • 4.redolog是循环写的,空间会用完,当没有可以写的空间后会触发,将redolog写入到磁盘上的数据页,之后新的日志就覆盖旧日志
  • 5.binlog是追加写的,不会覆盖写,一个文件满了可以换一个文件继续存储,存储大小只受机器磁盘大小

数据页

Innodb采用数据页的形式存储数据,默认一个数据页的大小为16KB,一个数据页是一个数据文件里的一块连续空间,索引也是通过数据页来定位记录的位置, 例如0-16KB为偏移量为0的数据页,16KB到32KB为偏移量为1的数据页,数据页是innodb的最小读写单元,每次读取数据即使只有一行数据也会从硬盘读取整个数据页到内存,然后在内存过滤,每个数据页也会存储到下一个数据页的地址,类似一个链表,这样当做范围查询的时候,可以读取完第一个数据页后,继续读取下一个数据页看是否满足条件,

下面我们看下一个数据页的内部结构

一个数据页包含如下信息:

  • 1.页头 (Page Header) 记录数据页的控制信息,例如左右兄弟页的地址,以及页面使用情况,例如已使用的空间,未分配的空间,已删除空间的链表等

  • 2.最大最小记录,所有的用户记录都小于最大记录,大于最小记录,引入这两个变量主要为了方便页内指针操作,这两个变量在页创建时候就存在,而且不能删除

  • 3.用户记录(User record),用户所有insert插入的记录都存储在这里,默认用户记录之间没有间隙,但是用户delete掉记录后会产生间隙,造成空间碎片,每一个记录都会有下一条记录的指针,但是没有上一条的指针,所以用户遍历的时候会从最小记录开始往后遍历,直到找到匹配的数据,已经删除的记录地址在页头的链表中维护,这个空间可以被重用

  • 4.未分配空间(free space),指页面未使用的存储空间,当用户插入一条记录时,首先从已经删除的空间中进行分配地址,如果找不到在从未分配的空间里分配,如果还找不到就会造成数据页的分裂,相反删除记录也会引起数据页的合并

  • 5.数据目录 (slot区) 由于用户记录是一个单项链表,遍历查询的话只能从最小记录开始遍历,为了能够利用2分查找这种快速查找的算法提高查找效率,slot区维护了一个链表,作为数据记录的目录或者索引,这个链表的记录会指向用户记录的地址,当进行查找的时候先定位到slot链表位置,在从这个链表开始遍历,详细可以见下图

  • 6.数据页尾部 (page tailer ) 主要存储校验值信息

数据页是innodb最基本的存储结构,所有的文件记录都会以数据页的格式存储,例如索引页,undo页,系统页,blob页

索引

索引就像书的目录一样,主要是为了提高查询效率,比如对一列数据,要进行快速查找,如果这列数据本身就是有序的,那么我们就可以利用二分查找快速定位,如果这列数据不是有序的,我们就需要用到快速排序这类算法,所以索引肯定是要有序的,我们常见的几个索引结构有以下几类.

  • 1.哈希表

类似于java中得hashmap,哈希是一种键值存储的数据结构,我们只需要输入key就可以快速定位到对应的value,hash的思路也很简单,将KEY值通过特定的哈希算法计算得到一个数值,而这个数值存在一个数组内,当通过key去查询的时候也先通过哈希算法获取到数组下标即可, 不可避免,不同的key通过哈希算法计算的下标可能重复,我们可以用一个链表来存储下标一样的值.如果hash算法导致下标冲突严重,将导致查询的时候需要遍历很长的链表,所以hash算法很关键,要做到随机,并且支持扩容,在jdk8中hashmap的实现已经将链表换成红黑树,哈希表这种结构只适合通过key做等值查询的情况, 当我们根据key的范围进行查询就不得不遍历所有数据,因为hash算法是随机的不可能有序.

  • 2.有序数组

数组可以支持随机读写,而有序数组在等值查询的时候,也可以利用二分查找,在范围查询的时候也很方便,但是写数据的时候需要移动后面的数据,所以有序数组只适合在静态数据中只持查询,基本不修改数据的情况,例如配置信息,以及历史数据.

  • 3.搜索树

以二叉搜索树为例子,一个节点的左儿子小于父节点,父节点小于右儿子节点,作为一个平衡二叉树,读取和修改的复杂度都是Log(N),搜索树即可以支持范围查询也可以快速写入,而我们在实际应用中很少选择二叉树,因为索引不仅仅放在内存中,需要存储到硬盘上,以数据页的形式存储,如果二叉树存储的数据越多,树就越深,树越深要遍历的数据页就越多,每读一个数据页都有一次磁盘IO操作.

Innodb选用B+树来存储索引,作为一个N叉树,这个N一般在1200左右,如果树高是4的话,就可以存储1200的3次方大概17亿个节点了,而查找一个节点只需要3个数据页,一般树的前面两层都会在内存,所以只需要读两次磁盘IO,B+树的叶子节点存储数据和索引值,非叶子节点根据索引来构建.

innodb中有两种索引,一种是聚簇索引,根据主键索引来构建,也较主键索引,叶子节点存储的是整行记录包括主键索引,另一种是普通索引,通过普通索引构建B+树,叶子节点存储的是普通索引的值和主键的值,也被称为二级索引

我们以下表作为例子来分析下 innodb 查找数据的流程

mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;

insert T (100,1) (200,2) (300,3) (500,5) (600,6)

语句1: select * from T where id = 500;
语句2: select * from T where k = 5;

语句1,按主键查询,则只需要搜索ID这颗树

语句2,按K查询,则需要先搜索K这颗树,找到K=5这一行对应的ID=500,然后在用ID=500去搜索ID这颗树,当然由于数据量太少不排除优化器会选择直接在ID树全扫描,或者数据就在内存.

语句2需要从两颗树上进行数据读取,其中回聚簇索引查询的过程我们称作回表,为了减少回表,mysql做了以下优化

  • 1.索引覆盖

当我们不用select * 查询,只是查询id,例如以上语句我们只是查询 select ID from T where k = 5; 那么我们只通过二级索引就可以获取到key值,就不用去聚簇索引查询,另外我们有时候会对表里的多个字段增加索引,这种索引称为联合索引,如果select的值和where条件里的字段都在联合索引里,也可以用到索引覆盖,例如下面的例子

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB


select age from tuser where name = 'xx'
  • 2.索引下推
mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

我们继续用上面那张表的例子,上面这个sql我们用到了name和age的联合索引,根据索引的最左原则,我们可以匹配到所有姓张的数据,找到这些姓张的人的ID,然后在用ID去聚簇索引取出整行数据, 然后判断age是否等于10和ismale,而mysql5.6加入的索引下沉可以在二级索引找到所有姓张的人后在看age是否等于10,这个时候之后取出age=10的用户id去聚簇索引上取出整行数据在判断ismale

排序

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;


select city,name,age from t where city='杭州' order by name limit 1000  ;

我们分析下上面这条sql的排序过程,由于上面的表t中city上是有索引的,name上没有索引,所以mysql会先把数据查询出来,在进行排序,然后取前1000条返回给用户,我们可以用explain查看一下

Extra中这个字段 using filesort 表示的就是需要排序,mysql会给每一个线程分配一个内存sort_buffer用作内存排序,我们以这个例子分析下mysql排序的流程

从图中可以看到,满足city=杭州的记录id 从ID_X 到 ID_X+N

  • 1.初始化sort-buffer
  • 2.从索引city中取出第一个满足city=杭州的字段id,也就是ID_X
  • 3.拿ID_X去主键索引上取出对应的行,取city name age三个字段放入sortbuffer
  • 4.从索引city中取出下一个满足city=杭州的记录id
  • 5.重复步骤3和4只到没有满足条件的值,这里第3步可能不一定会去主键索引上值,如果ID_X+N的记录和ID_X在一个数据页,那这个数据页肯定还在内存中,这里可以减少几次磁盘读
  • 6.数据取完后对sortbuffer按name进行快速排序
  • 7.按排序结果取前1000行返回给客户端

我们将上面的流程称为全字段排序 ,这个时候如果我们select*是一个很长的记录,那么sortbuffer的内存就可能存不下,当然也有可能是扫描的数据本身就很大,这种情况下mysql可以把扫描出来的数据存储在磁盘上,分成多个文件, 每个文件在内存进行排序,然后在将多个文件进行合并,然后排序了在取前1000条范围,可想而知这种不直接在内存进行排序的性能会很长,为了避免使用文件排序,我mysql提供了另外一种排序方式,这种方式不会将select的字段都放在内存进行排序,只取排序的字段加上主键进行排序,之后根据ID在去主键索引上取数据.

  • 1.初始化sort-buffer
  • 2.从索引city中取出第一个满足city=杭州的字段id,也就是ID_X
  • 3.拿ID_X去主键索引上取出对应的行,取id name 两个个字段放入sortbuffer
  • 4.从索引city中取出下一个满足city=杭州的记录id
  • 5.重复步骤3和4只到没有满足条件的值,这里第3步可能不一定会去主键索引上值,如果ID_X+N的记录和ID_X在一个数据页,那这个数据页肯定还在内存中,这里可以减少几次磁盘读
  • 6.数据取完后对sortbuffer按name进行快速排序
  • 7.按排序结果取前1000行记录的ID,然后根据ID去主键索引上去city name age 返回给客户端,当然,客户端可以通过resultset一行一行的读

这两种方式,mysql会优先选择第一种,只有当sortbuffer不够存储的时候才会选择第二种,第二种会多好多次磁盘IO,其实不管哪种方式两种排序方式都很慢,那么有没有一种方式可以避免排序呢

alter table t add index city_user(city, name);

如果加上city和name的联合索引后,上图就是先按照city排序,然后按照name排序,那么这个时候查询过程如下

  • 1.从索引city-name上找到第一个city=杭州的字段,拿到id
  • 2.从主键索引上根据id获取city name age 三个字段,作为结果直接返回客户端
  • 3.从索引city-name上找下一个满足条件的ID重复步骤2,直到不满足条件

可以看到,这种方式可以让mysql不需要临时表,也不需要进行排序,那么有没有一种方式可以继续优化性能呢?

alter table t add index city_user_age(city, name, age);

这样就可以利用到覆盖索引,不需要去主键索引上取数据了

所以当我们的sql语句中有排序字段的时候,我们可以考虑下排序字段是否有索引

另外如果我们有类似这种需求

select * from t where city in ('杭州'," 苏州 ") order by name limit 100;

我们可以考虑分成两条sql然后在应用层进行排序,可以利用归并排序,然后取前1000条记录,但是这种情况如果limit 10000,1000,我们就要同时查询11000条,然后在排序,这种方式也需要在数据库遍历大量记录,虽然我们可以在应用端利用jdbc的resultset一条一条的获取,进行排序,然后从10000条开始取,但从性能上还是不推荐的,可以考虑用 > id limit 0,1000 这种方式进行遍历

另外不建议使用 order by random ,因为这个函数需要在内存中将查询结果增加一列,每一行记录增加一个随机值,然后根据随机值排序,性能很差,我们可以变相的在一个ID范围内随机等方案

约定

  • 1.建议使用自增ID作为主键

自增主键插入数据,每一次都是追加操作,不会触发摞动其他数据,不会触发页分裂,有业务逻辑的字段往往无法保证有序插入,这样写数据代价高,例如随机的订单号,另外一方面,如果用订单号这类很长的字符串作为主键,则二级索引的叶子节点上就需要更大的空间来存储主键ID,浪费磁盘空间,而且一个数据页存储的记录就会变少,当做范围查询的时候,本来直接取一个数据页就可以读取全部的数据,现在需要读取多个数据页,多了IO操作. 当然也不是绝对,如果一个表业务方只有根据主键这种key-value查询的需求,也可以不用自增主键,因为他不需要其他的二级索引,就没有空间浪费.

  • 2.不要在where语句中加一些让mysql无法走索引的语句

例如在查询语句左边增加表达式,加函数,通过not in, in等语句,另外like操作用%号开始也可能导致无法走到索引

  • 3.唯一索引

加唯一索引不怎么会影响读取性能,但是会影响写的性能,但是核心业务上建议能加唯一索引尽量加上,避免应用上一些超时重试而程序没有做幂等引起问题.

  • 4.不要在经常变动的字段上加索引

  • 5.不要一次删除大量的数据,可以循环一次删一小批

  • 6.善用select for update

    一定要检查这行记录是否存在,有多少条记录,因为for update会锁住扫描到的记录以及记录之间的间隙

  • 7.order by 的字段最好能走索引

  • 8.少用limit m,n

    当m很大的时候性能很差,需要遍历m+n条记录,可以用根据ID来切分,每次查询都要大于上一次查询的最大ID,然后limit 0,n ,这样在走索引的情况下只需要遍历n条

  • 9.状态机可以用CAS机制

    更新状态的时候可以在where条件后面加上期望的原始状态

   update t set status = 1 where id = 1 and status = 0
  • 10.建了联合索引就不要在联合索引的第一个字段上单独建索引

Performance_schema

当我们的系统跑了一段时间后,可以回过头来检查下,之前建的索引使用频率如何,是否有命中,有没有慢查询,我们可以查询Performance_schema库的相关表

  • 1.Sql统计相关events_statements_summary_by_digest

我们可以关注下以下字段:SQL的平均响应时间 AVG_TIMER_WAIT,SQL排序记录 SUM_SORT_ROWS, SQL扫描记录数 SUM_ROWS_EXAMINED,SQL使用临时表数目 SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES,SQL返回结果集数目 SUM_ROWS_SENT

  • 2.索引访问次数统计table_io_waits_summary_by_index_usage
select
  object_type,
  object_schema,
  object_name,
  index_name,
  count_star,
  count_read,
  COUNT_FETCH
from performance_schema.table_io_waits_summary_by_index_usage
where
object_name = 'xx';