MySQL知识点总结

MySQL

• sql查询过程中，如何判定当前sql是否命中缓存？
  比较当前sql的查询文本和缓存中的sql，比较内容包括：大小写、空格等。如果缓存中的sql和当前sql的文本一模一样，则命中缓存。直接返回缓存结果，不再进行sql解析、优化等操作。
• Mysql默认的锁级别是行锁，那么什么时候使用表锁呢？
  
  • MyISAM
    
    • 总体看：只支持表锁，也就是select时加读锁，（update，insert，delete）时加写锁；
    • 一定条件下：支持并发插入，一个线程读表时，允许另一个线程在表尾插入数据；
  • InnoDB
    
    • 支持行锁
    • 行锁是对记录加锁吗？
      行锁是对索引加的锁，而不是对行记录加的锁；
    • 根据行锁的对象可知：
      
      • 不使用索引的情况下：使用表锁
      • 使用索引时：对索引加锁
        如果索引冲突，则两个sql串行执行，但此时使用的仍旧是行锁；
  • ps1:行锁只在存储引擎层实现
  • 这两个最常用存储引擎的区分：
    1.是否支持行锁；
    2.是否支持事务；
• MVCC多版本并发控制
  
  • 实质
    行锁的变种
  • 优势
    通过避免很多情况下的加锁操作，降低开销
  • 实现方式
    
    • 原理
      通过保存数据在某个时间点的快照来实现
    • 具体方式
      每行记录后面保存2个隐藏的列；
      1列存储行的创建时间；
      另1列 用于存储行的删除时间；
      ps：创建时间和删除时间不是真的时间值，而是系统版本号
  • 可重复读的隔离级别下，MVCC的具体操作：
    
    • select
      1.[针对未被删除的行]
      只查找版本早于当前事务版本的数据行；
      保证了读取的数据：要么是事务开始前就存在的；要么是事务自身插入or修改的；
      2.[针对此事务开始后被其它事务删除的行]
      保证了读取的数据：获取的是此事务开始前的数据行；
    • insert
      将当前系统版本号作为行版本号；
    • delete
      将当前版本号作为行删除标识；
    • update
      1.InnoDB先插入一个行记录，并将当前系统版本号作为行版本号；
      2.将当前系统版本号作为原来行的行删除标识；
• 索引
  
  • 实现位置
    存储引擎层
  • 实现原理
    MySQL先在索引上按值查找，然后返回包含该行的所有数据行。
  • 索引类型
    
    • B+Tree
      
      • 特点
        
        • 有m个子树的节点包含有m个元素
        • 根节点和分支节点中不保存数据，只用于索引，所有数据都保存在叶子节点中
        • 所有分支节点和根节点都同时存在于子节点中，在子节点元素中是最大或者最小的元素
        • 叶子节点会包含所有的关键字，以及指向数据记录的指针，并且叶子节点本身是根据关键字的大小从小到大顺序链接
      • 优势
        
        • 更加高效的单元素查找
          因为每个节点只保存索引值，不保存索引的数据行信息，所以查找过程中磁盘I/O次数减少。
        • 叶子节点形成有顺链表，范围查找性能更优
          查找2次，一次查找最小节点，一次查找最大节点，因为叶子节点升序链接，故范围数据就已经找到啦。
        • 所有查询都要找到叶子节点，性能稳定。
      • 为什么用B+Tree，而不用BTree?
        
        • 速度方面：
          InnoDB默认每个页大小为16KB，并且InnoDB把数据从磁盘读入内存时以页为单位，因为B+Tree除去叶子节点，都不包含数据行信息，所以磁盘I\O次数少；而BTree树则是每个节点都包含数据行信息，其查找过程就是一个二叉树的查找过程，故速度慢；
        • 安全方面：
          B+Tree每次查询都要找到叶子节点才能找到数据行，性能稳定；而BTree则查找到指定索引节点即可，而不一定会查找到叶子节点才结束。
      • 可以用B+Tree索引的select类型
        
        • 全键值
        • 键值范围
        • 键前缀
    • 哈希索引
      
      • 实现原理
        为索引的所有列生成一个hash值，故必须精确匹配哈希索引的所有列，才能使用此索引。
      • 支持引擎
        Memory
        ps:InnoDB会根据需要创建一个自适应hash索引。
    • 空间索引
      
      • 支持引擎
        MyISAM
      • 适用对象
        地理数据存储
      • 和B+Tree的不同
        可使用所有维度来索引数据，而无需最左前缀原则。
    • 全文索引
      
      • 查找对象
        文本中的关键词
      • 适用操作
        match against
  • 索引的优点
    
    • 大大减少服务器需要扫描的信息量
    • 帮助服务器避免排序和临时表
    • 可以将随机I/O变为顺序I/O
  • 高性能的索引策略
    
    • 聚簇索引
    • 覆盖索引
    • 压缩索引

---

参考资料：《高性能MySQL》