数据库
本文最后更新于172 天前,其中的信息可能已经过时,如有错误请发送邮件到3368129372@qq.com

定位慢查询

  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. 功能
      可以解决脏读和不可重复读,幻读需要加锁(间隙锁)才能解决

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'),日期转换,用于把日转化为某个月
  • 特殊函数
    • CHAR_LENGTH()字符串长度
    • COALESCE(value1, value2, ..., valueN)返回第一个不为null的值
  • 小技巧
    • sum+if函数能够替代count函数
感谢您的收看~
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇