组成
MySQL结构是分为:连接器、分析器、优化器、执行器
连接器负责连接控制、用户认证。分析器负责语法分析、表字段分析。优化器负责优化SQL,确定执行计划。执行器负责执行执行计划。
日志
事务日志
更新语句写日志流程:先查询出该记录,然后插入redo log,更新redo log该记录为prepare状态。再插入binary log,最后更新redo log状态为commit。更新结束。
插入binary log前数据库宕机,数据恢复时会判断该数据没有执行完,不予恢复。插入binary log后数据库宕机,数据恢复时会判断该事务再binary log已写完,会将redo log中prepare状态的事务继续执行,更新为commit状态。
redo log
重做日志,是循环写,记录数据页做了什么改动。由内存中redo log buffer和硬盘上redo log file组成。配置中innodb_flush_log_at_trx_commit
设置为1,表示每次事务提交更新buffer后再刷新数据到硬盘。保证数据不丢失。
undo log
回滚日志,记录每个事务版本下的数据做了什么修改。用于实现MVCC和事务回滚。
binary log
二进制日志,记录数据操作逻辑。查看binlog格式
show variables like 'log_%';
show variables like '%binlog_format%';
show binary logs;
- binlog格式
- statement,5.7之前默认,记录记录执行sql,但是对uuid()这种函数不能记录结果
- row,5.7默认,记录行数据变化sql,可以修改参数binlog_row_image
- full,记录一行所有列的内容
- minimal,记录改动的列内容
- noblob,
- mixed,根据sql语句由系统决定是使用statment or row
当前读和快照读
- 当前读:读取最新事务中的数据。例如DDL、加锁的查询
- 快照读:读取undo log中对应的事务版本的记录数据,例如普通查询
事务
- 事务特点
- 原子性,每个事务都是最小不可拆分的动作
- 一致性,事务中的数据,从开始到结束都应该一致
- 隔离性,每个事务不应该影响其他事务
- 持久性,事务结束后数据应该永久保存
- 事务隔离级别
幻读:当前事务再读取某个范围内记录时,另一个事务在这个范围内插入了新的记录。当之前的事务再次读取该范围的记录时,会产生幻行。InnoDB通过MVCC解决了幻读。
隔离级别 | 脏读可能性 | 不可重复读可能性 | 幻读可能性 | 加锁读 |
---|---|---|---|---|
读未提交 | 是 | 是 | 是 | 否 |
读已提交 | 否 | 是 | 是 | 否 |
可重复读 | 否 | 否 | 是 | 否 |
串行化 | 否 | 否 | 否 | 是 |
MVCC
Mutiversion Concurrency Control。InnoDB的MVCC是通过在每行记录上保存两个隐藏列实现的。一个列保存行的创建时间、另一个列保存行的过期时间(删除时间)。列值存储不是真实的时间,而是系统版本号(System version number)。每开始一个新的事务,系统版本号会递增。事务开始时的系统版本号作为事务的版本号。用来和查询到的记录的版本号进行比较。下面看下可重复读隔离级别下的MVCC具体操作。
-
select
InnoDB会根据以下两个条件检查每行记录:- InnoDB只查找版本号小于等于事务版本号的数据行。这样可以确保事务读取的行,是在事务开始前存在或者是事务自身插入或修改的。
- 行删除版本要么不存在,要么大于当前事务版本号。这样可以确保事务读取到的行,是在事务开始前未被删除的。
只有符合上述两个条件,才能返回作为查询结果。
-
insert
InnoDB为新插入的行保存当前事务版本号为行版本号 -
delete
InnoDB为删除的行保存当前事务版本号为行删除标识 -
update
InnoDB为插入一行新记录,当前事务版本号为行版本号,当前事务版本号为原来数据行的删除标识
MVCC只在可重复读和读已提交隔离级别下工作,其他两个隔离级别和MVCC不兼容。因为读未提交总是读最新的行,而不是符合当前事务版本的行,而串行化是对所有读取的行加锁。
锁
锁是保护多线程或多进程同时访问某一资源的机制
- 按类型
- 排他锁,x锁
- 共享锁,s锁
- 按粒度
- 表锁,table-level,myisam默认
- 行锁,row-level,innodb默认,加在主键索引上,如果索引失效则行锁变表锁
- 行锁(record lock):锁定该条记录
- 间隙锁(gap):范围查询中,锁定记录行间隙,使用左闭右开原则
- 临界锁(next key):行锁+间隙锁
- 死锁
查看最近的死锁日志
show engine innodb status;
索引
是为了配合高效查询算法的一种数据结构,innodb中是b+tree。这个tree上的叶子节点key为主键,value是行数据。普通节点只存储key和next。又称索引组织表。
- 普通索引,value是primary key,如果查询主键或索引外的数据,需要回表到主键索引上
- 唯一索引
优化步骤
- 开启慢查询,捕获慢查询sql
- explain优化sql
- show profile查询sql在mysql中执行细节和生命周期情况
- 数据库服务器参数调优
慢查询
查看慢查询是否开启
show variables like '%slow_query_log%';
show variables like '%long_query_time%';
show global status like '%Slow_queries%';
开启
set global show_query_log=1;
当前session休息
select sleep(4);
mysqldumpslow慢查询工具
explain
- id相同,从上往下顺序执行。id不同,id越大优先级越大。
- select_type
- simple,简单查询,不包含子查询或union查询
- primary,查询中包含复杂的子查询,最外层查询标记为primary
- subquery,在select或where中包含的子查询
- derived,在from列表中包含的子查询被标记为derived,mysql会递归执行这些子查询,将结果放到临时表中。table列中的显示<derived2>,数字就是id
- union,第二个select放在union后,则被标记为union
- union result,合并union结果集
- table
- type
- system->const->eq_ref->ref->range->index->all
- system,表只有一行记录,等于系统表,是const中的特例
- const,表示通过索引一次就找到,const用于primary key和unique key。如将主键至于where中,mysql能将该查询转换为一个常量
- eq_ref,唯一性索引扫描,对于每个索引键,只有一条记录与之匹配
- ref,非唯一性索引,对于每个索引键,有多条记录与之匹配
- range,只扫描给定的范围,一般where后有between、>、<、in等查询
- index,只遍历索引树,从索引读取,all是从硬盘读取
- all,全表扫描
- possible_keys,可能用到的索引,一个或多个
- key,实际用到的索引,为null表示没用到索引
- key_len,表示索引中使用的字节数,为最大可能长度,并非实际长度
- ref,显示索引的哪一列被使用,可能是常量,
- rows,大致估算找到记录所需扫描的行数
- extra
- using filesort,无法利用索引进行排序,需要用到文件排序
- using temporary,使用临时表保存中间结果,常见于order by或者group by
- using index,表示select使用了覆盖索引,如果同时出现using where表明用来执行索引键值查找。如果没有using where表明索引用来执行读取数据而非执行索引查找动作
- using where
- using join buffer,多表连接join buffer小了
- impossible where,where子句的值总是false
order by
- 避免使用using filesort
- order by 子句满足最左前缀原则
- where子句和order by子句满足最左前缀原则,最左前缀定义为常量
- 无法避免时,会尽量使用sort_buffer_size来进行文件排序
show profile
查看profile是否开启
show variables like '%profiling%';
查看profile状态
show profiles;
show profile cpu,block io for query 3;
网友评论