《高性能MySQL》 第一章 MySQL架构与历史 读书笔记

MySQL架构与历史

逻辑架构

连接处理、安全、授权验证

査询解析、分析、优化、缓存以及所有的内置函数，存储过程、触发器、视图

存储引擎

并发控制

存储引擎

SHOW TABLE STATUS查看表基础信息

INNODB

• 4.1后数据和索引分开存放

• 采用MVCC支持高并发，默认隔离级别可重复读，间隙锁防止幻读

• 二级索引包含主键，因此主键如果越大，索引所占用的空间也越大

• 读取优化策略

  • 读取磁盘时进行可预测性预读
  • 内存中创建hash索引以加速读操作的自适应哈希索引
  • 加速插入操作的插入缓冲区(insert buffer)

• 推荐扩展阅读：官方文档《InnoDB事务模型和锁》

MYISAM

• 数据与索引文件分离

• 5.0中，表如果是变长行，由于指向数据指针默认为6字节，只能处理256TB的数据

• 特性

  • 加的锁为表锁，但读取同时也可插入新的数据，性能上容易出在表锁上（如大量查询处于LOCK）
  • BLOB与TEXT字段支持前500字符创建索引
  • 如果指定了DELAY_KEY_WRITE，则数据更新时不会将索引更新直接落盘，而存在键缓冲区，当清理缓冲区或关闭表时才回写磁盘。（何时清理？什么是关闭表？）

• 压缩表

  • 针对不变的数据表，可以使用myisampack将表压缩存放，读取时减少IO

其它引擎

• Archive

  • 针对日志型应用，只有SELECT与INSERT
  • 在批量写操作完成前，写入的数据均不可见
  • 非事务引擎

• Blackhole

• CSV

• Federated

• Memory

  • 应用场景

    • • 用于査找(lookup)或者映射(mapping)表，例如将邮编和州名映射的表。
    • • 用于缓存周期性聚合数据(periodically aggregated data)的结果。
    • • 用于保存数据分析中产生的中间数据。

  • 特性

    • 重启后表结构保留，但数据会丢失
    • 表级锁，并发写入性能低
    • 不支持BLOB TEXT（出于占用空间考虑？）
    • 每行长度固定，即使定义了VARCHAR，实际也会转换为CHAR
    • 如果结果集使用了MEMORY作为临时表引擎，则当数据过大或采用了BLOB或TEXT，系统会将其转换为MYSIAM

• Merge

• NDB

第三方存储引擎

选型策略

• 事务。如果需要事务则选择innodb或xtradb

• 备份。如果需要在线热备则选择innodb

• 崩溃恢复。Mysiam崩溃后发生损坏的概率较innodb高得多

• 特有特性。如Mysiam支持地理空间搜索

• 应用

  • 日志型应用

    • 对于仅入库的需求，使用MYSIAM或ARCHIVE即可

    • 如果需要分析日志，分析SQL的执行会影响插入的速度

      • 解决方法–主备策略：主库入库，从库执行分析
      • 解决方法–分表策略：如按天生成不同的数据表

  • 只读或读多写少

    • 不介意MylSAM的崩溃恢复问题，选用MylSAM 引擎是合适的

  • 订单处理

    • innodb

  • 电子公告牌和主题讨论论坛

  • CD-ROM应用

    • MylSAM表或者MylSAM压缩表

  • 大数据量

转换表的引擎

• ALTER TABLE

  • 耗时长

• 导出与导入，即先将表导出SQL，再修改SQL文件中表引擎的部分，再导回数据库

• CREATE XXX LIKE SELECT 再ALTER TABLE引擎，最后插入数据。对于大数据量，如果一次性导入则会产生大量的UNDO，可将数据分批插入（大量UNDO有何问题？）

• pt-online-schema-change