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 优化服务器配置