MySQL知识点总结

<h3 id="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除去叶子节点，都不包含数据行信息，所以磁盘IO次数少；而BTree树则是每个节点都包含数据行信息，其查找过程就是一个二叉树的查找过程，故速度慢；
        • 安全方面： B+Tree每次查询都要找到叶子节点才能找到数据行，性能稳定；而BTree则查找到指定索引节点即可，而不一定会查找到叶子节点才结束。
      • 可以用B+Tree索引的select类型
        • 全键值
        • 键值范围
        • 键前缀
    • 哈希索引
      • 实现原理 为索引的所有列生成一个hash值，故必须精确匹配哈希索引的所有列，才能使用此索引。
      • 支持引擎 Memory ps:InnoDB会根据需要创建一个自适应hash索引。
    • 空间索引
      • 支持引擎 MyISAM
      • 适用对象 地理数据存储
      • 和B+Tree的不同 可使用所有维度来索引数据，而无需最左前缀原则。
    • 全文索引
      • 查找对象 文本中的关键词
      • 适用操作 match against
  • 索引的优点
    • 大大减少服务器需要扫描的信息量
    • 帮助服务器避免排序和临时表
    • 可以将随机I/O变为顺序I/O
  • 高性能的索引策略
    • 聚簇索引
    • 覆盖索引
    • 压缩索引

参考资料：《高性能MySQL》

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!