《高性能MySQL》第四章到第六章笔记

第四章 Schema与数据类型优化

选择优化的数据类型

1. 尽量使用可以正确存储数据的最小数据类型：占用更小磁盘、内存和cpu
2. 更简单的数据类型：需要更少的cpu周期
3. 避免包含null的列：使索引更复杂

mysql三大范式

• 第一范式：列不可分
• 第二范式：列必须直接依赖主键
• 第三范式：传递依赖，表里的列不能出现其他表的非主键字段

范式目的：

减少数据冗余，操作更快

范式优点：

• 更新操作更快
• 当数据较好地范式化时，就只有很少或者没有重复的数据，所以只需要修改更少的数据
• 范式化的表通常更小，可以更好的放在内存里，所以执行操作会更快
• 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY

范式缺点：

范式化设计通常需要关联。稍微复杂的查询语句在符合范式的schema上可能至少一次关联。这不但代价昂贵，也可能使一些索引策略无效。

第五章创建高性能的索引

在MySQL中，索引是在存储引擎层不是在服务器层实现的

索引的优点：

1. 索引大大减少了服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机IO变为顺序IO

高性能索引策略

• 独立的列：索引列不能是表达式的一部分

• 前缀索引和索引的选择性：索引很长的字符串时，会让索引变得很大且慢，可以索引开始的部分字符，这样可以节约索引空间，提高索引效率。缺点：前缀索引无法用ORDER BY 和GROUP BY

• 多列索引：在多个列上建立独立的单列索引大部分情况下并不能提高mysql的性能

• 选择合适的索引顺序:

  • 当不需要考虑排序和分组时，将选择性最高的列放在前面通常很好

• 聚簇索引：InnoDB通过主键列聚集数据

  • 优点：
    • 可以将相关数据保存在一起，如果不是聚簇索引，可能会有多次IO
    • 数据访问更快
    • 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值
  • 缺点：
    • 聚簇索引提高了IO密集型应用的性能，但是如果数据全部放在内存中，则访问顺序就没那么重要了，聚簇索引就没什么优势了。
    • 插入速度严重依赖于插入顺序。
    • 更新聚簇索引列的代价很高
    • 基于聚簇索引的表插入新行，可能面列‘页分裂’的问题，页分裂会导致表占用更多磁盘空间
    • 聚簇索引导致全表扫描变慢
    • 二级索引（非聚簇索引）可能比想象的更大
    • 二级索引访问需要两次索引查找
  • 使用InnoDB时应该尽可能按主键顺序插入数据，并且尽可能使用单调增加的聚簇键的值插入新行

• 覆盖索引

  • 定义：如果一个索引包含所有需要查询的字段的值，我们就称’覆盖索引‘
  • 优点：
    • 索引条目小于数据行，只读取索引，减少数据访问量
    • 因为索引是按顺序存储的，对于IO密集型的范围查询比随机从磁盘上读取每一行数据io要少
    • InnoDB的二级索引在叶子节点保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询
    • 使用索引扫描来做排序
    • 避免冗余和重复索引

技巧：

• 用in() 支持多种过滤条件
• 避免多个范围条件
• 优化排序

第六章查询性能优化

查询执行的基础：p204

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3. 服务器端进行sql解析、预处理、再由优化器生成对应的执行计划。
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
5. 将结果返回给客户端。