优化
如何定位慢查询
方案一:开源工具
调试工具:Arthas
运维工具:Prometheus,Skywalking
方案二:MySQL自带慢日志
慢查询日志记录了所有执行时间超过指定参数(llong_query_time,单位:秒,默认十秒)的所有SQL语句的日志,如果要开启慢查询日志,需要再MySQL的配置文件(/etc/my.cnf)中配置如下信息:
# 开启MySQL慢日志查询开关
slow_query_log = 1
# 设置慢日志的事件为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_qeury_time = 2
SQL语句执行的很慢,如何分析
可以采用EXPLAIN或DESC命令获取MySQL如何执行SELECT语句的信息
语法:
- 直接在select语句之前加上关键字EXPLAIN/DESC
EXPLAIN SELECT 字段列表 FROM 表明 WHERE 条件;
什么是索引
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护者满足特定查找算法的数据结构(B+树),这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找#算法,这种数据结构就是索引。
索引的底层数据结构了解过嘛?
数据结构对比
MySQL默认使用的索引底层数据结构是B+树,我们先看看二叉树和B树。
B-tree,B树是一种多叉路衡查找树,相对于二叉树,B树的每个节点可以有多个分支,即多叉。以一颗最大度数(max-degree)为5(5阶)的b-tree为例,那这个B树每个节点最多存储4个key
B+tree是在BTree基础上的一个优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+tree实现其索引结构
B树与B+树对比:
- 磁盘读写代价B+树更低
- 查询效率B+树更稳定
- B+树便于扫库和区间查询
聚簇索引和非聚簇索引
分类 | 含义 | 特点 |
---|---|---|
聚集索引(Clustered Index) | 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
二级索引(Secondary Index) | 将数据与索引分开存储,索引机构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引
- 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引
- 如果表中没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引
回表查询
覆盖索引
覆盖索引是指查询使用了索引,并且需要返回的列,在该索引中已经全部能找到。(即不需要回表查询)
超大分页优化
可以使用覆盖索引解决,在数据量比较大时,如果进行limit分页查询,在查询时,越往后,分页查询效率越低。
我们一起来看看执行limit分页查询耗时对比
因此,当在进行分页查询时,如果执行limit 9000000,10,此时需要MySQL排序前9000010记录,仅仅返回9000000-9000010的记录,其他记录丢弃,查询排序的代价非常大。
优化思路:一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询的形式进行优化
索引创建的原则
- 针对于数据量较大,且查询比较频繁的表建立索引。单表超过10万数据(增加用户体验)
- 针对于常作为查询条件(where),排序(order by),分组(group by)操作的字段建立索引
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率也就越高。
- 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
- 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
- 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
- 如果素银列不能存储NULL值,请在创建表时用NOT NULL约束他,当优化器知道每列是否包含NULL值时,他可以更好的确定哪个索引最有效的用于查询
什么情况下索引会失效
- 违反最左前缀法则
如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始,并且不跳过索引中的列。匹配最左前缀法则, - 范围查询右边的列,不能使用索引
- 不要在索引列上进行运算操作,索引将失效
- 字符串不加单引号,造成索引失效
- 以%开头的Like模糊查询,索引失效。如果仅仅是尾部模糊查询,索引不会失效。如果是头部模糊匹配,索引失效。
sql优化的经验
表的设计优化
- 比如设置合适的数值(tinyint,int,bigint),要根据实际情况进行选择
- 比如设置合适的字符串类型(char和varchar)char定长效率高,varchar可变长度效率低
SQL语句优化
- SELECT语句务必指明字段名称(避免直接使用SELECT *)
- SQL语句要避免造成索引失效的写法
- 尽量用union all代替union union会多一次过滤,效率低
- 避免在where子句中对字段进行表达式操作
- join优化,能用innerjoin就不用left join,right join,如必须使用一定要以小表为驱动,内连接会对两个表进行优化,优先把小表放到外边,把大表放在里面。left join和right join,不会重新调整顺序
主从复制,读写分离
如果数据库的使用场景读的操作比较多的时候,为了避免写的操作锁造成的性能影响,可以采用读写分离的架构。读写分离解决的是数据库的写入,影响了查询的效率。
事务的特性是什么
事务是一组操作的集合,他是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
ACID是什么?可以详细说一下?
原子性(Atomicity):事务是不可分割的最小单元,要么全部成功,要么全部失败
一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态
隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
持久性(Durability):事务一旦提交或回滚,他对数据库中的数据的改变就是永久的。
并发事务
并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另外一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了幻影 |
怎么解决并发事务的问题呢
方案:对事务进行隔离
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted未提交度 | √ | √ | √ |
Read committed读已提交 | × | √ | √ |
Repeatable Read(默认)可重复读 | × | × | √ |
Serializable串行化 | × | × | × |
注:事务隔离级别越高,数据越安全,但是性能越低。
undolog和redolog的区别
缓冲池(buffer pool):主内存中的一个区域,里面可以缓存磁盘上经常操作的真实数据,在执行增删改查操作时,先操作线程池中的数据(若缓冲池没有数据,则从磁盘加载并缓存),以一定的频率刷新到磁盘,从而减少磁盘IO,加快处理速度
数据页(page):是InnoDB存储引擎磁盘管理的最小单元,每个页的大小默认为16kb。页中存储的是行数据
redolog
重做日志,记录的是事务提交时数据页的物理修改,用来实现事务的持久性
该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者是在内存中,后者在磁盘中。当事务提交后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘发生错误时,进行数据恢复用
undo log
回滚日志,用于记录数据被修改前的信息,作用包含两个:提供回滚和MVCC(多版本并发控制)。undo log和redo log记录物理日志不一样,他是逻辑日志。
- 可以认为当delete一条记录时,undo log中会记录一条对应的insert记录,反之依然
- 当update一条记录时,他记录一条对应相反的update记录。当执行rollback时,就可以从undo log中的逻辑记录读取到响应的内容并进行回滚
事务中的隔离性是如何保证的呢
MVCC
全称Multi-Version Concurrency Control,多版本并发控制,指维护一个数据的多个版本,使得读写操作没有冲突。MVCC的具体实现,主要依赖于数据库记录中的隐式字段,undo log日志,readView
实现原理
记录中的隐藏字段
隐藏字段 | 含义 |
---|---|
DB_TRX_ID | 最近修改事务ID,记录插入这条记录或最后一次修改该记录的事务ID |
DB_ROLL_PTR | 回滚指针,指向这条记录的上一个版本,用于配合undo log,指向上一个版本 |
DB_ROW_ID | 隐藏主键,如果表结构没有指定主键,将会生成该隐藏字段。 |
undo log
回滚日志,在insert,update,delete的时候产生的便于数据回滚的日志
当insert的时候,产生的undo log日志只在回滚时需要,在事务提交后,可被立即删除。
而update,delete的时候,产生的undo log日志不仅在回滚时需要,mvcc版本访问也需要,不会立即被删除
undo log版本链
不同事务或相同事务对同一条记录进行修改,会导致该记录的undo log生成一条记录版本链表,链表的头部时最新的旧记录,链表的尾部是最早的旧记录。
readview
ReadView(读视图)时快照读SQL执行时MVCC提取数据的依据,记录并维护系统当前活跃的事务(未提交的)id
当前读
读取的是记录的最新版本,读取时还要保证其他并发事务不会修改当前记录,会对读取的记录进行加锁。对于我们日常的操作,如:select … lock in share mode(共享锁),select … for update,update,insert,delete(排他锁)都一种当前读
快照读
简单的select(不加锁)就是快照读,快照读,读取的是记录数据的可见版本,有可能是历史数据,不加锁,是非阻塞读
- Read Committed:每次select,都生成一个快照读
- Repeatable Read:开启事务后第一个select语句才是快照读的地方。
ReadView中包含了四个核心字段:
字段 | 含义 |
---|---|
m_ids | 当前活跃的事务ID集合(未提交) |
min_trx_id | 最小活跃事务ID |
max_trx_id | 预分配事务ID,当前最大事务ID+1(因为事务ID是自增的) |
creator_trx_id | ReadView创建者的事务ID |
MySQL主从同步原理
MySQL主从复制的核心就是二进制日志
二进制日志BINLOG记录了所有的DDL(数据定义语言)语句和DML(数据操纵语言)语句,但不包括数据查询(SELECT,SHOW)语句。
复制分成三步:
- Master主库在事务提交时,会把数据变更记录在二进制日志文件Binlog中
- 从库读取主库的二进制日志文件Binlog,写入到从库的中继日志Relay log
- slave重做中继日志中的事件,将改变反应它自己的数据
分库分表
分库分表的时机:
- 前提,项目业务数据逐渐增多,或业务发展比较迅速(单表的数据量达到1000w或20G之后)
- 优化已解决不了性能问题(主从读写分离,查询索引)
- IO瓶颈(磁盘IO,网络IO),CPU瓶颈(聚合查询,连接数太多)
垂直拆分
垂直分库:以表为依据,根据业务将不同表拆分到不同库中
特点:
1. 按照业务对数据分级管理,维护,监控,扩展
2. 在高并发下,提高磁盘IO和数据量连接数
垂直分表:以字段为依据,根据字段属性将不同字段拆分到不同表中
特点:
1. 冷热数据分离
2. 减少IO过渡争抢,两表互不影响
水平拆分
水平分库:将一个库的数据拆分到多个库中
特点:
1. 解决了单库大数量,高并发的性能瓶颈问题
2. 提高了系统的稳定性和可用性
水平分表:将一个表的数据拆分到多个表中(可以在同一个库内)
特点:
1. 优化单表数据量过大而产生的性能问题
2. 避免IO争抢并减少锁表的几率
分库分表的策略有哪些
分库之后的问题:
1. 分布式事务一致性的问题
2. 跨节点关联查询
3. 跨节点分页,排序函数
4. 主键避重