Question

1章：MySQL 架构与历史

读锁（共享锁） 写锁(排他锁)

原子性：一个事务必须被视为一个不可分割的最小工作单元 一致性：数据库总是从一个一致性的状态转换到另外一个一致性的状态。 隔离性：一个事务所做的修改在最终提交以前，对其他事务是不可见的。 持久性：一旦事务提交，其所做的修改就会永久保存到数据库中

四种隔离级别： - READ UNCOMMITTED(未提交读): 事务中的修改，及时没有提交，对其他事务也都是可见的。 事务可以读取未提交的数据（脏读）。这个级别会导致很多问题，一般不用 - READ COMMITTED(提交读)：一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。 - REPEATABLE READ(可重复读,mysql默认级别)：解决了脏读问题，保证同一个事务中多次读取同样记录的结果都是一致的。无法解决幻读的问题 - SERIALIZABLE(可串行化):最高的隔离级别，通过强制事务串行执行，解决了幻读问题。会在读取的每一行数据上都加锁。 可能导致大量的超时和锁征用问题

多版本并发控制(MVCC)，典型的有乐观并发控制和悲观并发控制。

选择不同的存储引擎: - 事务：InnoDB 是目前最稳定并且经过检验的选择 - 备份：如果需要在线热备份，InnoDB 就是最基本的选择 - 崩溃恢复

2 Mysql 基准测试

绘图：gnuplot，或者 R 绘图

集成式测试工具：ab, http_load, JMeter 单组件式测试工具：mysqlslap, sql-bench, super Smack, Database Test Suite, sysbench, mysql 内置benchmark 函数

3 服务器性能剖析

专注于测量服务器的时间花费在哪里，性能即响应时间。

性能剖析(profiling): 测量任务花费的时间，然后对结果进行统计排序

4 Schema与数据类型优化

4.1 选择优化的数据类型

• 更小的通常更好
• 简单就好：应该使用mysql内置类型而不是字符串来存储日期和时间；应该用整型存储IP地址。
• 尽量避免 NULL

两种整型数字：整数和实数

4.2 Mysql Schema 设计中的陷阱

• 太多的列
• 太多的关联
• 全能的枚举
• 变相的枚举

4.3 范式和反范式

完全的范式化和反范式化都是只有实验室才有的东西。通常使用部分范式化，缓存表和其他技巧。

4.4 缓存表和汇总表

缓存表、汇总表、影子表

4.5 加快 ALTER TABLE操作 的速度

两种技巧： - 先在一台不提供服务的机器上执行 ALTER TABLE 操作，然后和提供服务的主库切换 - 影子拷贝：用要求的表结构创建一个和源表无关的新表，然后通过重命名和删表操作交换两张表

5. 创建高性能的索引(key)

5.1 索引基础

mysql 支持的索引类型：

B-Tree索引： - 适用于全键值，键值范围或键前缀查找（最左前缀） - 限制： - 如果不是按照索引的最左列开始查找，则无法使用索引 - 不能跳过索引中的列 - 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

哈希索引：基于哈希表实现，只有精确匹配索引所有列的查询才有效

空间数据索引(R-Tree): 地理数据存储

全文索引

5.2 索引的优点

• 减少服务器需要扫描的数据量
• 避免排序和临时表
• 将随机 I/O 变为 顺序 I/O

三星原则: - 索引将相关的记录放到一起获得一星 - 索引中的数据顺序和查找中的排序顺序一致则获得两星 - 如果索引中的列包含了查询中需要的全部数据列则获得三星

5.3 高性能的索引策略

• 独立的列：如果查询中的列不是独立的，就无法使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数参数。
• 前缀索引和索引选择性：选择性越高查询效率越高。mysql无法使用前缀索引 order by 和 group by

聚簇索引：InnoDB 的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行 使用InnoDB 应该尽可能按照主键顺序插入数据，并且尽可能使用单调增加的聚簇键的值来插入新行。

只有当索引的列顺序和order by 子句的顺序完全一致，并且所有列的排序方向都一样时，mysql 才能 使用索引来对结果做排序。如果查询需要关联多张表，则只有当order by 子句引用的字段全部为第一个表时， 才能使用索引做排序。

mysql 的唯一限制和主键限制都是通过索引实现的，尽量不要创建重复的索引。

InnoDB 在二级索引上使用共享(读)锁，但访问主键索引需要排他(写)锁。 mysql 使用某个索引进行范围查询，也就无法再使用另一个索引（或者该索引的后续字段）进行排序了。 尽量将需要做范围查询的列放到索引的后边，以便优化器能使用尽可能多的索引列。

6 查询性能优化

6.2 慢查询基础：优化数据访问

• 查询不需要的记录。不需要的行（应该使用limit）或者多余的列(不要 select *)
• mysql是否扫描额外的记录，最简单衡量查询开销的三个指标：响应时间、扫描的行数、返回的行数

6.3 重构查询方式

• 衡量是使用一个复杂查询还是多个简单查询
• 切分查询
• 分解关联查询，对每个表进行一次单表查询，然后将结果在程序中进行关联

6.7 优化特定类型的查询

快速、精确和实现简单，三者只能满足两者

• 确保 ON 或者 USING 子句中的列上有索引;
• 确保任何的GROUP BY 和ORDER BY 中的表达式只涉及到一个表中的列

7 Mysql 高级特性

分区表

视图

视图本身是一个虚拟表，不存放任何数据

外键

在Mysql 内部执行代码

存储过程，触发器，事件，游标

查询缓存

缓存完整的 select 查询结果，命中缓存直接返回结果，跳过了解析、优化和执行阶段。对应用程序透明 什么情况下使用和服务器压力模型有关，高并发压力环境中查询缓存会导致系统性能下降，甚至僵死。

8 优化服务器配置