首页>>互联网>>大数据->性能优化

性能优化

时间:2023-11-29 本站 点击:1

纲要

索引

MySql索引结构

存储引擎

索引优化建议

高并发事务与死锁

原因分析

并发事务问题的解决方案

慢查询优化

慢查询的原因

慢查询常见优化方法

InnoDB 存储引擎参数优化

索引

MySql索引结构

B树

B+树

B树 VS B+树

B+树的非叶子节点不保存关键字记录的指针,只进行数据索引,这样使得B+树每个非叶子节点所能保存的关键字大大增加。

B+树叶子节点保存了父节点的所有关键字记录的指针,所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样。

B+树叶子节点的关键字从小到大有序排列,左边结尾数据都会保存右边节点开始数据的指针。

B+树的层级更少:相较于B树,B+每个非叶子节点存储的关键字数更多,树的层级更少所以查询数据更快;

B+树查询速度更稳定:B+所有关键字数据地址都存在叶子节点上,所以每次查找的次数都相同,所以查询速度要比B树更稳定;

B+树天然具备排序功能: B+树所有的叶子节点数据构成了一个有序链表,在查询大小区间的数据时候更方便,数据紧密性很高,缓存的命中率也会比B树高。

B+树全节点遍历更快: B+树遍历整棵树只需要遍历所有的叶子节点即可,而不需要像B树一样需要对每一层进行遍历,这有利于数据库做全表扫描。

存储引擎

两个主要的存储引擎:InnoDB & MyISAM。

它们默认都会创建一个主键索引,且默认使用的是 B+树 索引。

InnoDB 默认创建的主键索引是聚簇索引,其它索引都属于辅助索引,也被称为二级索引或非聚簇索引。

不过虽然这两个存储引擎都支持 B+树 索引,但它们在具体的数据存储结构方面有所不同。

MyISAM 索引文件和数据文件是分开的

InnoDB InnoDB索引树需要分情况

主键索引

索引和数据放在一棵索引树上面,由于主键索引是聚簇索引,整行数据都存储在叶子节点上,通过索引可以直接拿到数据。

非主键索引

会再开一棵 B+树,而它的叶子节点存储的data数据是索引所在行数据的主键。 如果要通过非主键索引查找数据的话,分两步:

1.先查找到它的主键

2.再去主键索引里查找相应的数据(回表)

索引优化建议

覆盖索引优化

假设我们需要查询商品的条码、名称、价格信息。为了避免回表,可以将商品条码、名称、价格建立一个组合索引,那么索引中就会存在这些数据,查询将不会再次检索主键索引,从而避免回表,不需要查询出包含整行记录的所有信息,可以减少大量的 I/O 操作。

自增主键

InnoDB 创建主键索引默认是聚簇索引,整行数据都存储在叶子节点上,同一个叶子节点内的各个数据是按主键顺序存放的,每当有一条新的数据插入时,数据库会根据主键将其插入到对应的叶子节点中。

如果使用自增主键,每次插入的新数据就会按顺序添加到当前索引节点的位置,不需要移动已有的数据,当页面写满,就会自动开辟一个新页面。因为不需要重新移动数据,所以这种插入数据的方法效率非常高。

如果使用非自增主键,每次插入新的数据时,就可能会插入到现有数据页中间的某个位置,导致不得不移动其它数据,可能会造成大量的内存碎片,导致索引结构不紧凑,影响查询效率。

前缀索引

当我们索引的字段是很长的字符串时,可以用到前缀索引。即定义字符串的一部分当做索引,指定前缀字节长度,而不是把整个字符串当做索引。

前缀索引存储了更少的数据,他耗费的空间也就相比较少,这是他的一个优点。同样的也就相对增加了扫描行数。因为前缀字符串的重复概率要比覆盖索引大,查询时在前缀索引中找到包含这个前缀的主键id后,还要去主键索引中找到完整字符串,不正确时还需要再次查询前缀索引树,因此前缀索引增加了扫描行数,比覆盖索引多了一步回表,

防止索引失效

联合索引最左匹配原则:比如以a、b、c这3个字段作为联合索引,只会走a、a,b、a,b,c 三种类型的查询。不过要说明一点,a,c也走,但是只走a字段索引,不会走c字段。不以a开头都不会走索引。

如果查询条件中使用 or,且 or 的前后条件中有一个列没有索引,那么该查询不会走任何索引。

对索引进行函数操作或者表达式计算也会导致索引的失效

以 % 开头的 LIKE 查询,将无法利用节点查询数据

高并发事务与死锁

原因分析

数据库事务有四个基本属性(ACID):原子性(Atomicity)、一致性(Consistent)、隔离性(Isolation)以及持久性(Durable)。

MySQL中,MyISAM 存储引擎不支持事务

并发事务带来的问题

并发事务问题的解决方案

数据丢失

可以基于数据库中的悲观锁来避免,在查询时通过在事务中使用 select xx for update 语句来实现排他锁,保证在该事务结束之前其他事务无法更新该数据。

也可以基于乐观锁来避免,将某一字段作为版本号,如果更新时的版本号跟之前的版本一致,则更新,否则更新失败。

事务隔离级别

结合业务场景,使用低级别事务隔离

避免行锁升级表锁

控制事务的大小,减少锁定的资源量和锁定时间长度

InnoDB 实现了共享锁和排他锁

共享锁:允许一个事务读数据,不允许修改数据

排他锁:修改数据时加的锁,可以读取和修改数据。申请前提 : 没有线程对该结果集中的任何行数据使用排他锁或共享锁,否则申请会阻塞

事务隔离级别介绍(级别越高,并发性越低)

读未提交(Read Uncommitted): 事务 A 已经开始写数据,则事务 B 不允许同时进行写操作,但允许读此行数据(事务 A 还未提交)。会导致脏读、幻读、不可重复读。

读已提交(Read Committed): 如果事务 A 是读事务,则允许其他事务读写;如果事务 A 是写事务,将会禁止其他事务读该行未提交的数据。事务 A 事先读取了数据,事务 B 紧接着更新了数据,并提交了事务,而事务 A 再次读取该数据时,数据已经发生了改变。可以避免脏读,但依然存在幻读和不可重复读的问题。

可重复读(Repeatable Read): 在一个事务内,多次读同一个数据,在这个事务还没结束时,其他事务不能读写该数据,这样就可以在同一个事务内两次读到的数据是一样的。可以避免脏读、不可重复读,但依然存在幻读的问题。

串行化(Serializable): 最严格的事务隔离,要求事务序列化执行,事务只能一个接着一个地执行,但不能并发执行,解决了脏读、不可重复读、幻读等问题,但隔离级别越来越高的同时,并发性会越来越低。

InnoDB 中的 RC 和 RR 隔离事务是基于多版本并发控制(MVVC)实现高性能事务。

MVVC 对普通的 Select 不加锁,如果读取的数据正在执行 Delete 或 Update 操作,这时读取操作不会等待排它锁的释放,而是直接利用 MVVC 读取该行的数据快照(数据快照是指在该行的之前版本的数据,而数据快照的版本是基于 undo 实现的,undo 是用来做事务回滚的,记录了回滚的不同版本的行记录)。MVVC 避免了对数据重复加锁的过程,大大提高了读操作的性能。

锁的具体实现算法

InnoDB 既实现了行锁,也实现了表锁。行锁是通过索引实现的,如果不通过索引条件检索数据,那么 InnoDB 将为表锁。

行锁的具体实现算法有三种:

record lock:专门对索引项加锁

gap lock:对索引项之间的间隙加锁

next-key lock: 是前面两种的组合,对索引项及其之间的间隙加锁

只在可重复读或以上隔离级别下的特定操作才会取得 gap lock 或 next-key lock,在 Select 、Update 和 Delete 时,除了基于唯一索引的查询之外,其他索引查询时都会获取 gap lock 或 next-key lock,锁住其扫描的范围。

慢查询优化

慢查询的原因

无索引、索引失效

在千万条数据表中一个索引也没有,任何非主键查询都会导致全表扫描。

即使有了索引,在某些场景下也要确定是否会导致索引失效。

锁等待

InnoDB支持行锁和表锁,MyISAM只支持表锁

InnoDB 存储引擎支持的行锁更适合高并发场景,行锁是基于索引加的锁,如果在更新操作时,条件索引失效,那么行锁也会升级为表锁,在批量更新操作时,行锁也很可能会升级为表锁。

基于表锁的数据库操作,会导致 SQL 阻塞等待,影响执行速度。

行锁相对表锁来说,虽然粒度更细,并发能力提升了,但也带来了新的问题:死锁

SQL语句不当

使用 SELECT *,SELECT COUNT(*)

在大表中使用 LIMIT M,N 分页

对非索引字段进行排序

慢查询常见优化方法

避免死锁

在 InnoDB 中,设置超时时间。当一个事务的等待时间超过阈值,就对这个事务进行回滚,另一个事务就可以继续执行了

用分布式锁代替数据库实现幂等性校验。

在允许幻读和不可重复读的情况下,尽量使用 RC 事务隔离级别,避免 gap lock 导致的死锁

更新表时,尽量使用主键更新

避免长事务,尽量将长事务拆分

SQL语句优化

通过 EXPLAIN 分析 SQL 执行计划 图中每个字段说明

优化前: select * from goods order by id  limit 90000,5;

优化后: select * from goods where id > (select id from goods order by id  limit 99999,1) order by id  limit 5;

优化分页查询

system/const:表中只有一行数据匹配,根据索引查询一次就能找到对应的数据。

eq_ref:使用唯一索引扫描,常见于多表连接中使用主键和唯一索引作为关联条件。

ref:非唯一索引扫描,还可见于唯一索引最左原则匹配扫描。

range:索引范围扫描,比如,<,>,between 等操作。

index:索引全表扫描,此时遍历整个索引树。

ALL:表示全表扫描,需要遍历全表来找到对应的行。

id:每个执行计划都有一个 id,如果是一个联合查询,这里还将有多个 id。

select_type:表示 SELECT 查询类型,常见的有 SIMPLE(普通查询,即没有联合查询、子查询)、PRIMARY(主查询)、UNION(UNION 中后面的查询)、SUBQUERY(子查询)等。

table:当前执行计划查询的表,如果给表起别名了,则显示别名信息。

partitions:访问的分区表信息。

type:表示从表中查询到行所执行的方式,查询方式是 SQL 优化中一个很重要的指标,结果值从好到差依次是:system > const > eq_ref > ref > range > index > ALL。

possible_keys:可能使用到的索引。

key:实际使用到的索引。

key_len:当前使用的索引的长度。

ref:关联 id 等信息。

rows:查找到记录所扫描的行数。

filtered:查找到所需记录占总扫描记录数的比例。

Extra:额外的信息。

优化 SELECT COUNT(*)

COUNT()函数在有条件的情况下,需要扫描全表进行统计,对大表进行 SELECT COUNT(*) 显然效率低下

使用近似值:某些业务场景并不需要返回一个精确的 COUNT 值,使用 EXPLAIN 对表进行估算,并不会真正去执行查询,而是返回一个估算的近似值。

使用中间统计表:这种方式在新增和删除时有一定的成本,但却可以大大提升 COUNT() 的性能。

优化 SELECT *

查询具体需要的列:如 select a,b from table where id=1,假设存在a和b的联合索引,那么 select * 会导致回表

InnoDB 存储引擎参数优化

innodb_buffer_pool_size

IBP 默认的内存大小是 128M。MySQL 推荐配置 IBP 的大小为服务器物理内存的 80%

innodb_buffer_pool_instances

该参数项仅在将 innodb_buffer_pool_size 设置为 1GB 或更大时才会生效。

如果 innodb_buffer_pool_size 大小超过 1GB,innodb_buffer_pool_instances 值就默认为 8;否则,默认为 1。

建议指定 innodb_buffer_pool_instances 的大小,并保证每个缓冲池实例至少有 1GB 内存。

建议 innodb_buffer_pool_instances 的大小不超过 innodb_read_io_threads + innodb_write_io_threads 之和,建议实例和线程数量比例为 1:1。

innodb_read_io_threads / innodb_write_io_threads

根据业务场景,读大于写时,读线程数量可以大于写线程数量,否则,反之

读写线程数量值默认各为 4,但需要与 innodb_buffer_pool_instances 的大小协同优化

    ( innodb_read_io_threads + innodb_write_io_threads ) = innodb_buffe_pool_instances

innodb_log_file_size

InnoDB 中有一个 redo log 文件,InnoDB 用它来存储服务器处理的每个写请求的重做活动。

理论上 innodb_log_file_size 设置得越大,缓冲池中需要的检查点刷新活动就越少,节省磁盘 I/O。但并不是设置得越大越好呢,否则恢复时间就会变长。通常设置为 1GB

innodb_log_buffer_size

这个参数决定了 InnoDB 重做日志缓冲池的大小,默认值为 8MB。适当调大可以减少磁盘 I/O ,提高并发事务性能

max_connections

连接到 MySQL 的最大连接数,默认为151。结合业务场景适当调大该参数

back_log

TCP 连接请求排队等待栈,适当调大该参数,增加高并发时短时间内处理连接请求量


本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:/BigData/1269.html