定位慢查询

1. 开源工具：
   • skywalking
   • Arthas
   • prometheus
2. 自带的慢日志查询

分析sql慢的原因

1. explain分析
   • posibble_keys可能用到的索引
   • key用到的索引
   • key_len索引大小
   • Extra额外的优化建议（例如是否回表）
   • type连接类型，由好到坏分别为null，system，const(主键)，eq_ref（主键或唯一索引）,ref（索引查询）,range（范围查询）,index（索引树扫描）,all。

索引结构

1. B+树与B树比
   • 只有叶子节点存储数据，读写代价小
   • 底层是链表，便于范围查找
2. 索引类型
   • 聚簇索引：数据存储与索引在一起，叶子节点保存行数据，必须有而且只有一个。
   • 有主键的话就是主键
   • 没主键就是唯一索引
   • 都没有的话内部自动生成一个键
   • 非聚簇索引： 将数据与存储分开存储，索引结构的叶子节点关联的是主键，再通过主键回表查询。
   • 覆盖索引：查询中用到了索引而且需要返回的列再该索引中能全找到（不用回表查询），少用select * 能优化
3. 优化超大分页问题
   直接查用的是limit，可以先查询id，再通过子查询得到整体的数据，避免回表查询。

索引失效

1. 联合索引不能违反最左前缀法则，A、B、C联合，必须从左到右查索引
2. 范围查询右边的列
3. 索引进行运算操作
4. 字符串不加单引号（数据类型转换）
5. 模糊查询，“%”在前面会失效

sql优化

1. 表设计优化
2. 索引优化
3. SQL语句优化
   • 少用select *
   • 防止索引失效
   • 用union all代替union（union会多一次过滤操作，把两次查出来结果一样的给过滤掉）
   • 能用inner join就不用left join、right join。要用的话一定要小表驱动，即把小表放在循环外面（减少数据库连接次数）。
4. 主从复制，读写分离
5. 分库分表

隔离级别

1. 读未提交
2. 读已提交
3. 可重复读（默认，能解决脏读、不可重复读与幻读）
4. 串行化
   幻读针对insert与delete
   不可重复读针对update

主从同步

主节点存在一个binlog，从库读取binlog写入自己的relay log之中，从库重做relay log变为自己的数据

redolog

• 记录物理日志
• 保证持久化，事务提交后会写入到redolog日志中
• 重启或意外，内存中的Buffer Pool内容消失，无法同步到磁盘中，因此会把Buffer Pool中的内容写到Redolog Buffer中，Redolog Buffer再同步到磁盘中

undolog

• 记录逻辑日志，用于回滚
• 实现一致性与原子性

mvcc（多版本并发控制）

• 实现事物隔离，提供不加锁的解决方案
  1. undo log（版本链）
     • 使用roll_pointer指针实现undo链
  2. read view（读视图）
     • 组成：
     • 当前活跃的事物集合：m_ids(所有未提交的事务)
     • 集合中最小事务：min_trx_id
     • 下一个将被分配的事务（最大）:max_trx_id(链头id+1)
     • 创建这个read view的事务：creator_trx_id
  3. 过程
     一个select语句带着他的事务id前来，查看undolog中的链。
     先创建一个read view,保存事务信息。
     • 如果链中某个id为自己的id说明事务是自己创建的，可以访问！
     • 如果自己的id比最小的未提交事务的id还小，说明事务已经提交，可以访问！
     • 如果自己的id比最大的未提交事务的id还大，说明事务还未提交，不能访问！
     • 如果在最大与最小之间，但又不在活跃事务中，说明已提交，可以访问！
     • 如果都不能访问，就遍历整个链表，知道能访问为止！
  4. 功能
     可以解决脏读和不可重复读，幻读需要加锁（间隙锁）才能解决

流程

1. 客户端与mysql建立连接
2. 查询缓存
3. 语法解析与预处理
4. 查询优化器，生成执行计划
5. 执行计划（Execution Engine）
6. 存储引擎层（Storage Engine）
7. 返回结果
8. 清理资源

sql

• 排序：order by
  • asc升序
  • desc降序
    例如：'select name,age,score from student order by score desc, age asc'
• 截断：limit
  • limit 2一共截几条
  • limit 2,2从第二条开始截取两条
• 条件分支：case when
  • case when（1） then 1
    when（2） then 2
    else 3 end as "4"
  • 例句：

    select
    name,
    case when(age>60) then '老同学'
    when(age<20) then '小同学'
    else '年轻' end as age_level
    from
    student order by name asc

  • if语句：IF(condition, value_if_true, value_if_false)
  • ifnull(value,value_null)不为null时取value，否则取value_null
• 分组
  • group by(having与where的区别在于having在where之后)
  • having在分组之后，where在分组之前
• 笛卡尔积：cross join(或者,)
  • 例句

    SELECT e.emp_name, e.salary, e.department, d.manager
    FROM employees e
    CROSS JOIN departments d;

• 计算
  • mod(a,b)取余
  • round(value,n)保留n位小数
• 时间
  • datediff（date1，date2）两个日期的天数之差
  • DATE_FORMAT(trans_date, '%Y-%m'),日期转换，用于把日转化为某个月
  • YEAR（date）能取得年份
• 特殊函数
  • CHAR_LENGTH()字符串长度
  • COALESCE(value1, value2, ..., valueN)返回第一个不为null的值
• 小技巧
  • sum+if函数能够替代count函数