MySQL

组成

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;