第四章 Schema与数据类型优化
选择优化的数据类型
- 尽量使用可以正确存储数据的最小数据类型:占用更小磁盘、内存和cpu
- 更简单的数据类型:需要更少的cpu周期
- 避免包含null的列:使索引更复杂
mysql三大范式
- 第一范式:列不可分
- 第二范式:列必须直接依赖主键
- 第三范式:传递依赖,表里的列不能出现其他表的非主键字段
范式目的:
减少数据冗余,操作更快
范式优点:
- 更新操作更快
- 当数据较好地范式化时,就只有很少或者没有重复的数据,所以只需要修改更少的数据
- 范式化的表通常更小,可以更好的放在内存里,所以执行操作会更快
- 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY
范式缺点:
范式化设计通常需要关联。稍微复杂的查询语句在符合范式的schema上可能至少一次关联。这不但代价昂贵,也可能使一些索引策略无效。
第五章创建高性能的索引
在MySQL中,索引是在存储引擎层不是在服务器层实现的
索引的优点:
- 索引大大减少了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机IO变为顺序IO
高性能索引策略
-
独立的列:索引列不能是表达式的一部分
-
前缀索引和索引的选择性:索引很长的字符串时,会让索引变得很大且慢,可以索引开始的部分字符,这样可以节约索引空间,提高索引效率。缺点:前缀索引无法用ORDER BY 和GROUP BY
-
多列索引:在多个列上建立独立的单列索引大部分情况下并不能提高mysql的性能
-
选择合适的索引顺序:
- 当不需要考虑排序和分组时,将选择性最高的列放在前面通常很好
-
聚簇索引:InnoDB通过主键列聚集数据
- 优点:
- 可以将相关数据保存在一起,如果不是聚簇索引,可能会有多次IO
- 数据访问更快
- 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值
- 缺点:
- 聚簇索引提高了IO密集型应用的性能,但是如果数据全部放在内存中,则访问顺序就没那么重要了,聚簇索引就没什么优势了。
- 插入速度严重依赖于插入顺序。
- 更新聚簇索引列的代价很高
- 基于聚簇索引的表插入新行,可能面列‘页分裂’的问题,页分裂会导致表占用更多磁盘空间
- 聚簇索引导致全表扫描变慢
- 二级索引(非聚簇索引)可能比想象的更大
- 二级索引访问需要两次索引查找
- 使用InnoDB时应该尽可能按主键顺序插入数据,并且尽可能使用单调增加的聚簇键的值插入新行
- 优点:
-
覆盖索引
- 定义:如果一个索引包含所有需要查询的字段的值,我们就称’覆盖索引‘
- 优点:
- 索引条目小于数据行,只读取索引,减少数据访问量
- 因为索引是按顺序存储的,对于IO密集型的范围查询比随机从磁盘上读取每一行数据io要少
- InnoDB的二级索引在叶子节点保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询
- 使用索引扫描来做排序
- 避免冗余和重复索引
技巧:
- 用in() 支持多种过滤条件
- 避免多个范围条件
- 优化排序
第六章查询性能优化
查询执行的基础:p204
- 客户端发送一条查询给服务器
- 服务器先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段。
- 服务器端进行sql解析、预处理、再由优化器生成对应的执行计划。
- MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询
- 将结果返回给客户端。