对于MySQL的理解

一.理论

  1. 一条SQL执行过程
    • 连接器
      负责跟客户端建立连接、获取权限、维持和管理连接。
    • 分析器
      • 词法
        • MySQL 从你输入的"select"这个关键字识别出来,这是一个查询语句。它也要把字符串“T”识别成“表名 T”,把字符串“ID”识别成“列 ID”。
      • 语法
        • 根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法。
      • 语义
        • 判断语句是否正确,表是否存在,列是否存在
    • 优化器
      • 决定使用什么索引,和join表的顺序
    • 执行器
      • 判断是否有权限,然后执行语句
      • 执行的过程
  2. 索引
    索引的出现其实就是为了提高数据查询的效率,就像书的目录一样
    • 常见索引数据结构(每碰到一个新数据库,我们需要先关注它的数据模型,这样才能从理论上分析出这个数据库的适用场景)
      • 哈希表
        • 适用于只有等值查询的场景,比如 Memcached 及其他一些 NoSQL 引擎
      • 有序数组
        • 适用于等值查询和范围查询场景中,只适用于静态存储引擎
      • 搜索树
      • 跳表
      • LSM 树
    • 常见索引
      基于非主键索引的查询需要多扫描一棵索引树,应用中应该尽量使用主键查询
      • 主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。
      • 非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)配合change buffer使用更佳
      • 唯一索引:唯一索引可以创建多个且能为空(只能一次),主键索引不能为空
    • 索引优化
      • 覆盖索引(会受到前缀索引影响)
        由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
      • 最左前缀原则
        第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。
      • 索引下推
    • 优化器选错索引(纠正)
      • 由于索引统计信息不准确,导致判断扫描行数不准确,此种情况可用 analyze table 来解决
      • 由于临时表,排序字段,导致优化器误判,此种情况可用force index来强行指定索引,也可以通过修改语句引导优化器,还可以通过增加或者删除索引来绕过这个问题
    • 索引失效
      总结:对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。(下面三种都是需要函数转换)
      • 条件字段函数操作
        例如 where month(t_modified)=7
      • 隐式类型转换
        例如 select “10” > 9
        如果规则是“将字符串转成数字”,那么就是做数字比较,结果应该是 1;
        如果规则是“将数字转成字符串”,那么就是做字符串比较,结果应该是 0。
      • 隐式字符编码转换
        mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /语句Q1/
        tradeid不是同一个字符集,需要转换CONVERT(traideid USING utf8mb4),导致索引失效
  3. 事务(ACID)
    • 特性
      • 原子性(Atomicity)
      • 一致性(Consistency)
      • 隔离性(Isolation)
        这4种隔离级别,并行性能依次降低,安全性依次提高
        • 读未提交(read uncommitted)
          一个事务还没提交时,它做的变更就能被别的事务看到
        • 读提交(read committed)
          一个事务提交之后,它做的变更才会被其他事务看到
        • 可重复读(repeatable read)
          一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。
        • 串行化(serializable )
          对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。
      • 持久性(Durability)
    • 多事务问题
      • 脏读
      • 不可重复读
      • 幻读
    • 事务启动方式
      • 显式启动事务语句,begin或者start transaction,提交commit,回滚rollback
      • set autocommit=0,该命令会把这个线程的自动提交关掉。这样只要执行一个select语句,事务就启动,并不会自动提交,直到主动执行commit或rollback或断开连接。
    • MVVC(Multi-Version Concurrency Control 多版本并发)
      • 视图是 InnoDB 在实现 MVCC 时用到的一致性读视图,即 consistent read view,用于支持 RC(Read Committed,读提交)和 RR(Repeatable Read,可重复读)隔离级别的实现。
    • 全局锁
      • 命令:
        • Flush tables with read lock (FTWRL)
        • set global readonly=true (不建议使用)
      • 场景:做全库逻辑备份
    • 表锁
      • 一种是表锁,一种是元数据锁(metadata lock)
      • 命令
        • lock tables … read/write(不建议使用 lock tables锁住整个表的影响面太大)
        • MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。
    • 行锁
      • 死锁
        • 一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
        • 另一种策略是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑
  4. 日志
    • binlog(归档)
      • Server层特有的日志
    • redo log(重做)
      • InnoDB 引擎特有的日志
      • 当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log(粉板)里面,并更新内存,空闲时写入磁盘。
    • undo log(回滚)

二.实战

  1. Join
    • 什么使用时候Join
      • 如果可以使用 Index Nested-Loop Join 算法,也就是说可以用上被驱动表上的索引,其实是没问题的;
      • 如果使用 Block Nested-Loop Join 算法,扫描行数就会过多。尤其是在大表上的 join 操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源。所以这种 join 尽量不要用。
    • 如果要使用 join,应该选择大表做驱动表还是小表?
      • 如果是 Index Nested-Loop Join 算法(BNL),应该选择小表做驱动表;
      • 如果是 Block Nested-Loop Join 算法(ILJ):
        • 在 join_buffer_size 足够大的时候,是一样的;
        • 在 join_buffer_size 不够大的时候(这种情况更常见),应该选择小表做驱动表。
          ( 小表:在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。)
    • 优化
      • Multi-Range Read 优化
        意为多范围读,表示在回表之前先把需要回表的主键排序,再读取对应的数据(当然,有的并不需要回表),就能够将无序读转化为有序读,性能会大幅提升在这里插入图片描述
      • Batched Key Access(BKANLJ 算法的优化,但依赖MRR)
        set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’;
  2. 临时表
    • 特点
      (1)只对当前session可见
      (2)可以与普通表重名
      (3)增删改查用的是临时表
      (4)show tables不显示普通表
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值