高性能MySQL 读书笔记

最新推荐文章于 2024-10-13 21:08:42 发布

玉面大蛟龙

最新推荐文章于 2024-10-13 21:08:42 发布

阅读量840

点赞数 9

分类专栏：读书笔记数据库文章标签： mysql 数据库学习 java

本文链接：https://blog.csdn.net/m0_49499183/article/details/141112661

版权

读书笔记同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

数据库

3 篇文章 0 订阅

订阅专栏

一、MySQL架构与历史

并发控制
- 读写锁：共享锁、排他锁
- 锁粒度：行锁、表锁
事务：ACID
隔离级别：读未提交、读已提交、可重复度、串行化
事务日志：
- 追加写。事务日志持久后，内存中被修改的数据在后台可以慢慢地刷到磁盘，这被称为预写式日志，修改数据需要写两次磁盘
MySQL中的事务：
- MySQL采用的是自动提交模式：每条单独的SQL语句都被视为一个事务，并且在执行完毕后立即进行提交
多版本并发控制MVCC
- innodb的MVCC操作模式（可重复读的隔离级别下）：
  - 在每行数据后保存两个隐藏的列：创建时间、过期时间，存储的并不是实际的时间值，而是系统版本号
    - select：只看当前事务版本号之前的记录
    - insert：保存创建时间
    - delete：保存删除时间
    - update：就是先delete再insert，把当前版本号记为上个记录的删除时间，并记为本次记录的创建时间
  - 好处：大部分的读操作都不用加锁
  - 不足：需要两个额外的列、需要更多的行检查工作
  - 工作场景：读已提交、可重复读
    - 读未提交总是读最新的行，不符合MVCC
    - 串行化会都加锁，不符合MVCC
存储引擎的选择
- 支持事务：innodb 不需要事务，且以SELECT、INSERT为主：mylsam
- 在线热备份：innodb
- 崩溃恢复：innodb > mylsam
- 特有特性：
  - 例如：mylsam支持地理空间搜索

二、基准测试

三、服务器性能剖析

四、Schema与数据类型优化

介绍良好的schema设计原则

选择数据类型
- 更小的通常更好
- 简单就好
- 避免null
数据类型
- 整数：TINYINT、 SMALLINT、 MEDIEMINT、 INT、 BIGINT
  - 可选UNSINGED属性，不允许负值，可以让整数的范围提高一倍
  - 对于存储和计算来说，INT(1)和INT(20)是一样的
- 实数：FLOAT、 DOUBLE、 DECIMAL
  - 也可以用decimal来存储比bigint更大的整数，但是需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL。如果是大整数，还是考虑使用bigint+单位
- 字符串：VARCHAR 、 CHAR、BLOB 、TEXT
  - VARCHAR需要额外字节来记录字符串的长度
  - VARCHAR在update之后可能会变长，这就要做额外的工作。如果页内没有更多的空间存储，innodb使用分裂页来使行可以放进页内
  - 使用varchar的场景：
    - 长度不固定（比均值大很多）
    - 更新较少
  - 尽量避免使用BLOB TEXT，性能开销大
- 时间和日期：MySQL最小精度为秒
  - DATETIME，精度秒，YYYYMMDDHHMMSS
  - TIMESTAMP
- 位数据
  - BIT 谨慎使用，在MySQL5及之前，BIT = TINYINT
  - SET 代价高
范式
- 优点：
  - 范式化的更新操作通常比反范式化要快。
  - 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
  - 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
  - 很少有多余的数据意味着检索列表数据时更少需要DISTINCT 或者GROUP BY语句。
- 缺点：
  - 通常需要关联

五、创建高性能的索引

使用的是orm，也需要关注索引。orm很难生产出适合索引的查询
索引的类型
- B-Tree、B+Tree
  - 有效的查询类型：
    - 全值匹配
    - 匹配最左前缀
    - 匹配列前缀。只匹配某一列的开头部分
    - 匹配范围值
    - 精确匹配某一列并范围匹配另外一列
    - 只访问索引的查询
  - 因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的 ORDER BY操作(按顺序查找)。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序
- 哈希
  - 基于哈希表实现，只有精确匹配索引所有列的查询才有效
  - 优点
    - 结构紧凑，查询很快
  - 缺点
    - 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
    - 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
    - 哈希索引也不支持部分素引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列(A,B) 上建立哈希索引，如果查询只有数据列A, 则无法使用该索引。
    - 哈希索引只支持等值比较查询，包括=、IN()、<=> (<>和<=>是不同的操作)。也不支持任何范围查询，例如WHERE price >100。
    - 当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
    - 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低 (哈希冲突很多)的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。
- R-Tree：空间数据索引
- 全文索引
索引的优点
- 减少了服务器需要的扫描量
- 避免排序和临时表
- 将随机IO变成顺序IO
“三星系统”：评估一个索引是否适合某个查询
- 一星：索引将相关的记录放在一起
- 二星：索引中的数据数据和查找中的排列顺序一致
- 三星：索引中的列包含了查找中需要的全部列
索引是最好的解决方案吗？
- 只有当索引帮助存储引擎快速查找到记录带来的好处>其带来的额外工作时，索引才是有效的。
- 对于非常小的表，大部分情况下简单的全表扫描更高效
高性能的索引策略
- 独立的列：索引列不能是表达式的一部分
  - 例如，下面的列就不能用actor_id列的索引，因为MySQL不会自动解析出actor_id=4
    - ```
    SELECT actor_id FROM askila.actor WHERE actor_id+1 = 5; 
```
- 总结：因此应该养成简化where条件的习惯
- 前缀索引和索引选择性
  - 没看明白
  - 缺点：不能group和order
- 多列索引
  - 误区：为每个列都建独立的索引，这种理解是错误的。最好情况也只能是“一星”索引
- 选择合适的索引列顺序
  - 将选择性最高的列放在前面通常是很好的
- 聚簇索引
  - 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式
  - innodb通过主键聚簇索引。（如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,innodb会隐式定义一个主键来作为聚簇索引）
  - 优点：
    - 可以把相关数据保存在一起。
      - 例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘IO。
    - 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree 中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
    - 使用覆盖素引扫描的查询可以直接使用页节点中的主键值。
  - 缺点：
    - 聚筷数据最大限度地提高了I/0 密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优勢了。插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB 表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用 OPTIMIZE TABLE命令重新组织一下表。
    - 更新聚簇索引列的代价很高，因为会强制InnoDB將每个被更新的行移动到新的位置。
    - 基于聚簇索引的表在插人新行，或者主键被更新导致需要移动行的时候，可能面临 “页分裂 (page split)” 的问题。当行的主键值要求必须将这一行插人到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
    - 聚筷索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
    - 二级索引 (非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
    - 二级素引访问需要两次索引查找，而不是一次
- 覆盖索引
  - 如果一个索引包含(或者说覆盖)所有需要查询的宇段的值，我们就称之为 “ 覆盖索引”
    - 如果素引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢?
- 如何设计索引
  - 在where子句中出现频繁的列应该建索引
  - 经常进行范围查找的列，应该尽量放在后面（最左匹配，查询只能使用索引的最左前缀，直到遇到第一个范围条件列）
  - 尽量避免多个范围条件（对于范围条件查询，MySQL无法再使用范围列后面的其他索引列）
  - 查询时尽量使用in来替代范围查询

六、查询性能优化

慢查询：优化数据访问
- 是否向数据库请求了不必要的数据。有可能是访问了太多行，也可能是访问了太多列
  - 经典案例：
    - 查询不必要的数据：比如页面上只展示10行数据，却查了100行出来
      - 解决：使用limit
    - 多表关联时返回全部列
      - 解决：用哪个列就查哪个列，少写select *
    - 重复查询相同的数据
      - 解决：缓存
- MySQL是否在扫描额外的记录
  - 最简单的衡量开销的三个指标
    - 响应时间
    - 扫描的行数
    - 返回的行数
  - 响应时间
    - 不好直接衡量
  - 扫描的行数和访问类型
    - 访问类型：全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用
      - 速度从慢到快，扫描行数从大到小
    - where条件写法，从好到坏依次为：
      - 在索引中使用WHERE 条件来过滤不匹配的记录。这是在存储引擎层完成的。
      - 使用索引覆盖扫描来返回记录。这是在MySQL服务器层完成的，但无需再回表
      - 从数据表中返回数据，再过滤。这是在MySQL服务器层完成的，需回表
重构查询的方式