【读过的书，留下的迹】高性能MySQL（第3版）

最新推荐文章于 2024-06-04 15:12:12 发布

linxdcn

最新推荐文章于 2024-06-04 15:12:12 发布

阅读量599

点赞数

分类专栏：读书笔记文章标签： mysql 高性能

本文链接：https://blog.csdn.net/linxdcn/article/details/69665081

版权

读书笔记专栏收录该内容

15 篇文章 0 订阅

订阅专栏

前言

最近发现有时候看完一本书，时间久了容易忘记，看书不总结思考效果大打折扣，故打算写这一系列文章，一是为了整理书中的要点，帮助自己消化理解；二是勉励自己多看书思考。文章中不会把书中内容讲解的非常详细，只是总结概括，适合已经阅读过该书的读者。

第2章：MySQL基准测试

基准测试是针对系统设计的一种压力测试

基准测试有两种主要的策略
- 针对整个系统的整体测试，集成式（full-stack)
- 单独测试MySQL，单组件式（single-component）

测试指标

吞吐量：单位时间内的事务处理数
响应时间或者延迟：测试任务所需的整体时间
并发性
可扩展性

基准测试常见错误

使用真实数据的子集而不是全集
使用错误的数据分布
使用不真实的分布参数
在多用户场景，只做单用户的测试
在单服务器上测试分布式应用
反复执行同一个查询
没有检查错误

基准测试工具

MySQL Benchmark Suite（sql-bench）：自带
sysbench：推荐使用，功能较多完善

第3章：服务器性能剖析

主要为了定位使性能低的位置，主要方法使专注测量服务器的时间花费在哪里

剖析MySQL查询

剖析整个数据服务器
- 通过慢查询日志记录查询：mysql设置，信息相对较少
- 通过pt-query-digest分析tcpdump结果：信息相对较多
对查询进行单独的剖析
- show profile
  - set profiling = 1;
  - show profiles; // 显示所有查询的时间
  - show profile for query 1; // 显示查询1的详细时间开销
- show status
  - 返回一些有用的计数器，如read、delete的次数等
  - flush status; // 清除统计，重新开始
- 慢查询日志

诊断间歇性问题

show global status
- 以较高的频率反复执行该命令捕获数据，如Threads_connected、Threads_running的变化
show processlist（主要查看线程状态）
- 以较高频率反复执行该命令，观察是否有大量线程处于不正常的状态或者不正常的特征

一般推荐上述两种方法，应为开销很低，而且可以通过简单的shell脚本或者反复执行的查询来交互式的收集数据，观察服务器的情况

第4章：Schema与数据类型优化

数据类型选择原则

更小的通常更好：执行更快，占用更少磁盘、内存、CPU缓存
简单就好：操作需要更少的CPU周期
尽量避免NULL：使得索引、索引统计和值的比较都更复杂
整数类型
- TINYINT：8位；SMALLINT：16位；MEDIUMINT：24位；INT：32位；BIGINT：64位
实数类型
- FLOAT：32位；DOUBLE：64位，浮点类型不精确
- DECIMAL：精确
字符串类型
- VARCHAR：需要使用1至2个额外字节记录字符串长度
- CHAR：固定长度，对于经常变更的数据更适用
- BLOB：采用二进制存储很大的数据
- TEXT：采用字符方式存储很大的数据
日期和时间类型
- DATETIME：1001年-9999年
- TIMESTAMP：1970年-2038年

范式和反范式

范式优点
- 更新操作比反范式快
- 很少重复数据，存储少
- 检索数据时很少需要GROUP BY之类的
范式缺点
- 通常需要关联
反范式
- 查询速度快，单独的表更有效的索引策略

第5章：创建高性能的索引

索引类型

B-Tree索引
- 适合：全键值、键值范围或键值前缀查找
- 限制
  - 不是按索引最左列开始查找，无法使用
  - 不能跳过索引中的列
  - 若有范围查询，其右边所有列不能使用索引优化
哈希索引
- 只有精确匹配索引所有列才有效
- 限制
  - 只包含哈希值和行指针，不存储字段值
  - 不按照索引值顺序存储
  - 不支持部分索引列匹配查找
  - 不支持范围
  - 哈希冲突很多的话，维护代价高

InnoDB引擎有一个特殊的功能叫做”自适应哈希索引”。当InnoDB某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引

索引的优点

大大减少了服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随即IO变为顺序IO

高性能索引策略

独立的列
- 查询中的列不是独立的，即索引列是表达式的一部分，或者函数的参数，则不能使用索引
前缀索引
- 对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引
- 前缀索引使索引更小、更快，但无法使用GROUP BY和ORFER BY，也无法覆盖扫描
多列索引
- 索引合并策略，一定程度上可以使用表上的多个单列索引来定位指定的行
- 如过个and或者or条件
合适索引列顺序
- 原则：选择性最高的列放在索引最前列
聚簇索引
- 定义：在同一个地方保存了索引和数据行
- 优点：
  - 把相关数据保存在一起
  - 数据访问更快
  - 使用覆盖索引扫描的查询可以直接使用页节点的主键值
- 缺点
  - 如果不是IO密集型应用，聚簇没优势
  - 插入速度严重依赖插入顺序
  - 插入新行，或者主键更新时，面临页分裂问题
  - 可能导致全表扫描变慢

覆盖索引
- 如果一个索引包含所有需要查询的字段的值，我们就称之为”覆盖索引”
- EXPLAIN的EXTRA列显示Using index
索引扫描排序
- 如果EXPLAIN的type列显示index，说明采用索引扫描排序

维护索引和表

找到损坏表：check table
修复损坏表：repaire table
更新索引统计信息：analyze table
减少索引和数据碎片：optimize table

第6章：查询性能优化

优化数据访问
- 查询不需要的记录
- 多表关联时返回全部列
- 总是取出全部列
- 重复查询相同的数据
是否扫描额外的记录，三个指标
- 响应时间
- 扫描的行数
- 返回的行数
重构查询方式
- 一个复杂查询还是多个简单查询
- 切分查询，如把一个大的delete切分成多个
- 分解关联查询，关联放在应用程序中

查询执行基础

优化特定类型的查询

优化count()查询
- count() where id > 5 转化为 count() - count(*) where id <= 5
- 使用近似值
优化关联查询
- 确保on或者using子句中的列有索引
- 确保group by或者order by只涉及一列，才能使用索引
优化子查询
- 尽可能使用关联查询代替
优化limit
- 尽可能使用索引覆盖扫描，再根据需要做一次关联操作，对于偏移量很大的时候，效率提高很多
优化union
- 手工将where、limit、order by等子句下推到各个union子查询，以便优化器充分利用这些条件优化