高性能 mysql

高性能 mysql

架构与历史

• 客户端 --> 连接/线程处理 --> 解析器 --> 查询缓存 --> 优化器 ===》 存储引擎。
• 表锁是 Mysql 中最基本的锁策略，并且是开销最小的策略。
  • ALTER TABLE之类的语句使用表锁，而忽略存储引擎的表机制。
• 行级锁(row lock) 可以最大程度地支持并发处理（同时也带来了最大的锁开销）, 行级锁只在存储引擎层实现。
• 事物是一组原子性的 SQL 查询, 事务内的语句, 要么全部行成功，要么全部执行失败。
  • START TRANSACTION 开始一个事务，要么使用 COMMIT 提交事务将修改的数据持久保留, 要么使用 ROLLBACK 撤销所有修改。
• ACID 表示原子性(atomicity)、一致性(consistency)、隔离性(isolation) 和持久性(durability)。
• SERIALIZABLE (可串行化)是最高的隔离级别。
• Mysql 提供了两种事务的存储引擎: InnoDB 和 NDB cluster.
• Mysql 服务层不管理事务, 事务是由下层的存储引擎实现的, InnoDB 采用了两阶段锁定协议(two-phase locking protocol)。
• 多版本并发控制(MVCC): MVCC 是行级锁的变种，非堵塞的读操作，写操作只锁定必要的行; MVCC 的实现是通过保存数据在某个时间点的快照来实现的。
  • 乐观(optimistic)并发控制和悲观(pessimistic)并发控制。
  • MVCC 只能在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作。
• 在文件系统中, Mysql 将每个数据库(也称为schema), 保存为数据目录下的一个子目录。创建表时，Mysql 会在该子目录中创建文件。
• InnoDB 表是基于聚簇索引建立的, 若表上的索引较多，主键应当尽可能地小。
  • InnoDB 从磁盘读数据采用了可预测性预读, 能够自动在内存中创建 hash 索引, 以加速读操作的自适应 hash, 加速插入的插入缓冲区。
• 如果索引包括多个列，那么列的顺序也很重要, mysql 只能高效的使用最左前列。
  • 在 B-tree 之上再创建一个 hash 索引, 自适应 hash 索引。
  • SHA1() 和 MD5() 是强加密函数，设计目标是最大限度消除冲突，CRC32()会出现大量的哈希冲突。
• 全文索引适用于 Match against操作，而不适用于 where 条件查询操作。
• 索引的有点:
  • 索引大大减少了服务器需要扫描的数据量.
  • 索引可以帮助服务器避免排序和临时表。
  • 索引可以将随机I/O 变为顺序I/O。
• 当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。
  • 聚簇表示: 数据行与相邻的键值紧凑的存储在一起。
  • 因为不能把数据行存储在两个不同的地方，所以一个表只能有一个聚簇索引。
• 索引可以使查询锁定更少的行。
• B-Tree 索引可能会碎片化，这会降低查询的效率, 碎片化的索引可能会以很差或者无序的方式存储在磁盘上。
• 查询优化，索引优化，库表结构优化。
  • 查询低下的最基本的原因是访问的数据太多。
  • 切分查询，分而治之，将大查询切成小查询, 每个查询功能完全一样, 只完成一小部分, 每次只返回一小部分查询结果。
  • mysql 优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。
• 分区表是一个独立的逻辑表，但是底层是由多个物理子表组成.
  • 实现分区的代码实际上是对一组底层表的句柄对象(Handler Object)的封装。
• 视图本身是一个虚拟表，不存放任何数据; 视图和表在同一个命名空间, Mysql在很多地方对于视图和表是同样对待的。

总结

• 这本书写了很多关于 msql 的优化方法，但读上去有点枯燥与无趣，可以当一本工具书看把。
• 如何系统学习mysql