Mysql调优理论和实践

性能监控工具

服务端的配置和性能

show profile

运行时性能

performance_schema

简介

• 它是数据库中的库，使用的存储引擎是performance_schema，主要关注的是数据库运行过程中的性能相关的数据，与information_schema不同，关注的是数据库表的元数据

• server内部在发生函数调用、操作系统的等待、SQL语句的执行阶段、单个SQL或者多个SQL的集合的事件来触发采集消耗、耗时、活动执行次数等信息，并且事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源同步多用相关信息

• 它的记录只会在server本地，不会记录到binlog，同时也不复制到其他的server，其实在mysql源代码实现过程中主要是通过检查点（埋点）的方式收集

• 可以通过select查询，同时也还可以修改相关收集配置，动态修改setup_*开头的几个配置表

表分类

入门使用

两个概念

• instruments：生产者，用于采集mysql中各种各样的操作产生的事件信息，对应配置表中的配置项我们可以称为监控采集配置项，前面动态表setup_*配置

• consumers：消费者，对应的消费者表用于存储来自instruments采集的数据，对应配置表中的配置项我们可以称为消费存储配置项

常用配置项

启动配置

系统变量

setup_*配置表说明

实践案例

show processlist

Sechema与数据类型优化

数据类型优化

• 三原则：占用越小越好，足够简单，避免为null

• 实际操作建议
• 整型：TINYINT，SMALLEST，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间
• 字符和字符串：char（255字）、vachar（65535字）、text类（TINYTEXT:281、TEXT:216 -1、MEDIUMTEXT:2241、LONGTEXT:4G或2321）单位字节
• BLOB和TEXT类型，分别是二进制和字符串格式来存储
• datatime和timestamp
• datatime（8字节）
• 与时区无关，数据库底层对datetime无效
• 可以精确到毫秒
• 可保存的范围大
• 字符串存储会导致确实时间的精度
• date（3字节）
• 占用的字节数比字符串、datatime、int少
• 可以通过date类型进行日期计算
• 保存范围是1000-01-01到9999-12-31
• timestamp（4字节）
• 时间范围是linux元年1970-01-01开始到2038-01-19
• 精确到秒
• 采用整型存储
• 依赖数据库的时区
• 自动更新timestamp的列值
• 尝试使用枚举代替字符串，mysql存储枚举类型非常的紧凑，有利于提升IO读取
• 存储特殊类型通过可以通过函数转换如IP存储

合理的范式和反范式

• 合理的范式
• 优点：更新更快，基本不会出现重复的数据，在内存中操作比较快
• 缺点：需要不断的关联，增加IO消耗

• 反范式
• 优点：所有的信息都在一张表中，可以避免关联，减少IO消耗
• 缺点：冗余较多，删除操作时会删除不必要信息

• 实践
• 项目前：先范式设计后分析业务反范式冗余存储，更新优先级根据业务来看（及时和延迟）
• 项目中：分析业务代码逻辑，是否需要优化数据表增加冗余字段减少IO的开销

主键选择

• 代理主键
• 与业务无关，无意义的数字序列，如ID

• 自然主键
• 和业务相关，事物属性唯一标识，如身份证

• 如何选择
• 代理主键更好，不与业务耦合，更加容易维护，统一的策略，也减少源代码数量

字符集选择

• latin*：纯拉丁内容适合

• utf-8*：多语言内容

存储引擎选择

适当的拆分

• 比如有字段类型为TEXT之类大型字段，可以尝试拆到单独的表，当查询不需要此字段的查询是，可以大大减少IO的处理时间

执行计划EXPLAIN

格式：explain + SQL

EXPLAIN字段解析

id有三种情况

1. id相同，顺序执行

2. id不同，递增，id号越大，越先执行

3. id相同和id不同同时存在，即第一种和第二种结合

select_type

table

1. 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名

2. 表名是derivedN的形式，表示使用了id为N的查询产生的衍生表

3. 当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id

type

possible_keys

key

key_len

ref

rows

extra

如何应用

• type判断是否有使用索引，尽可能的到达range<type<ref

• ref判断是否用到索引

• extra判断是否覆盖索引、排序是否用到索引

索引优化

索引基础

优点

• 减少服务器扫描数据量

• 帮助服务器避免排序和临时表

• 将随机IO变成顺序IO

用处

• 快速的找到WHERE字句的行

• 优化器会找到最优索引

• 如果表具有多列索引，优化器会使用索引的任何最左前缀来找到行

• 当有表连接时，，从其他表检索行数据

• 找到特定的索引列min和max值

• 如果索引和分组时在可用的索引最左前缀上完成的，则对进行排序和分组

• 某些情况下，可以查询检索值，无需检索行，如覆盖索引的情况

分类

• 主键索引：PRIMARY KEY

• 唯一索引：UNIQUE

• 普通索引：NORMAL

• 全文索引：FULLTEXT

• 组合索引：KEY name(字段1字段2字段3)

一些名词

• 回表：比如我们需要查用户信息，通过name去查（name字段列是普通索引），普通索引中的data存储的是主键索ID主键索引data存放的是数据行，执行器会通过普通索引定位到data后，再拿着ID去主键索引去查用户信息数据行(最后去主键索引查数据行的行为叫回表)，遍历2次B+树

• 覆盖索引：还是刚刚的例子，如果查询的不是用户信息，而是用户ID（主键ID），去掉上面例子中去主键索引的过程，就是覆盖索引，因为普通索引data就是用户ID，遍历1次B+树

• 最左匹配：B+树索引是有序的，从左往右递增，而组合索引就是从表的字段的左边字段开始匹配，遇到范围停止匹配

• 索引下推：将where后面的条件需要在server层过滤的变为在server层之前过滤完成，交给server层的就是结果集

索引常用的数据结构

• 哈希表+链表

• B+树
• 发展历程
• =>哈希表，查询时间复杂度（O(N)），为了降低时间复杂度人们想到了（logn）-> 二叉树
• =>二叉树（分支倾斜严重）
• =>平衡二叉树（平衡分支耗时）
• =>红黑树（减少平衡距离，降低插入速度）
• --分割线---无论怎么优化二叉树，随着时间的推移，树的深度总会越来越深---分割线--
• =>B树：
• 每个节点（主键（主键+data）+指针（主键子节点范围））16K
• 这样的缺点，节点容量固定条件下，data越大，能存放的指针变小，从而导致索引容纳的数少

• =>B+树
• 让最终的子节点去存放data，子节点的父节点都是指针
• 最底层data之间还通过链表相互连接，方便遍历

索引匹配的方式

• 全值匹配：name=‘张三’

• 匹配最左前缀：name=‘张三’ AND age=10

• 匹配列前缀：name like ‘张%’

• 匹配范围值：age>10

• 精确匹配某一列+范围匹配：name=‘’ AND age > 10

• 只访问索引列：select name,age from user where name=‘张三’ AND age=10(覆盖索引)

哈希索引

• 结构：哈希表 + 链表

• 优点
• 结构非常的紧凑，然后查询很快

• 缺点
• 哈希索引结构只包含行指针和哈希值，不存储值
• 哈希不是顺序存储，无法排序
• 不支持分列匹配查找
• 只支持等值 =，or查找，不支持范围查找
• 冲突严重时，链表特别长耗时，维护耗时

• 案例
• 比如需要存放一个很长的URL，需求需要通过URL查询
• 我们可以通过一些哈希函数，如CRC32
• 这样可以减小体积

组合索引

• 多列共同组成索引树，where最左使用

聚族索引和非聚族索引

• 聚族索引：索引中data=元数据
• INNODB中的主键索引存放的就是元数据

• 非聚族索引：索引中data!=元数据，而是元数据的地址
• 如MyISAM存储引擎的B+树，data存放的是元数据的文件地址

覆盖索引

• 一个索引data中有查询字段的值

• 实现方式和存储引擎有关，Momery存储引擎不支持

• 优势
• 减少IO，B+索引本就有顺序，在IO密集型范围内，读取一次数据IO减少很多
• 聚族索引支持更好，非聚族索引只做地址缓存严重影响性能

细节优化

• 单表6个以内索引

• 一个索引5个字段内

• 根据实际业务优化

• 少用表达式，计算放到业务层

• 优先主键索引，不会触发回表

• 使用前缀索引

• 使用索引排序

• union all、in、or都能使用索引，in最好

• 范围，<,>，但是后面的字段就无法用索引了

索引监控

• show status like 'Handler_read%';

• 参数解析
• Handler_read_first：读取索引第一个条目的次数
• Handler_read_key：通过index获取数据的次数
• Handler_read_last：读取索引最后一个条目的次数
• Handler_read_next：通过索引读取下一条数据的次数
• Handler_read_prev：通过索引读取上一条数据的次数
• Handler_read_rnd：从固定位置读取数据的次数
• Handler_read_rnd_next：从数据节点读取下一条数据的次数

案例

预备数据

案例一

案例二

查询SQL优化

查询慢的原因

数据访问优化

• 在无法避免的大量数据过程中，检查应用程序和mysql服务器是否在检索大量无需的字段

• 是请求了无关的字段，如下
• 查询不需要的字段
• 多表返回了全部列
• 总是取出全部列
• 重复查询相同的数据

执行过程优化

=>查询缓存

=>解析SQL和预处理：分析器

• 通过关键字将SQL语句进行解析并生成一颗解析树

• mysql解析器将使用mysql语法规则验证和解析查询

=>优化SQL执行计划：优化器

初略统计

• 表和索引的页面数、索引个数

• 索引和数据行长度

• 索引分布情况

大多情况下会选择错误的执行计划，原因如下

• 统计信息不准确：innodb的MVCC会有过个版本

• 执行计划成本估算不等于实际执行成本：mysql不知道那些数据实际在内存中和磁盘中

• 优化器认为的最优的和现实不一样：基于成本模型，但不是我们认为的最优

• 不考虑并发执行的查询

• 不考虑不受其控制操作的成本：比如自定义函数

优化器策略

• 动态优化：与查询的上下文、取值、索引的函数有关，优化N次

• 静态优化：直接优化解析树，只优化一次

优化器类型

• 重新定义关联顺序

• 外连接转化为内连接

• 使用等价代换规则，可以使用一些等价简化的表达式

• count（）、Min（）、Max（）：索引列不为NULL可以优化这类

• 预估并转化常数表达式，检查到可以是一个常数，即转化为常数

• 覆盖索引扫描，符合‘覆盖索引’条件即用

• 子查询优化：在某些情况下，子查询会变为缓存

• 等值传播

关联查询优化

• 简单关联

• 有索引关联

• 无索引关联



（1）Join Buffer会缓存所有参与查询的列而不是只有Join的列。 （2）可以通过调整join_buffer_size缓存大小 （3）join_buffer_size的默认值是256K，join_buffer_size的最大值在MySQL 5.1.22版本前是4G-1，而之后的版本才能在64位操作系统下申请大于4G的Join Buffer空间。 （4）使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。
查询optimizer_switch设置情况：show variables like '%optimizer_switch%';

排序优化

• 单次排序
先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果；
此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据

• 两次排序
第一次数据读取是将需要排序的字段读取出来，然后进行排序；第二次是将排好序的结果按照需要去读取数据行。 这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高 两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作

=>执行器

特定类型查询优化

优化COUNT()

• MYISAM存储引擎在没有where的条件下count(*)最快

• 近视值，通过EXPLAIN的row取值

• 更复杂优化，考虑覆盖索引扫描或者增加汇总表

优化关联查询

• 确保ON后者using字句中有索引，考虑顺序

• group by和order by中的表达式中涉及一个表中的列相同排序方式才会用到索引

优化子查询

• 尽可能通过关联查询代替

优化Limit查询

• 在很多应用场景中我们需要将数据进行分页，一般会使用limit加上偏移量的方法实现，同时加上合适的orderby 的子句，如果这种方式有索引的帮助，效率通常不错，否则的化需要进行大量的文件排序操作，

• 还有一种情况，当偏移量非常大的时候，前面的大部分数据都会被抛弃，这样的代价太高，要优化这种查询的话，要么是在页面中限制分页的数量，要么优化大偏移量的性能

优化union查询

• mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。

• 经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化

用户自定义变量

• 在查询中混合使用过程化和关系话逻辑的时候，自定义变量会非常有用；用户自定义变量是一个用来存储内容的临时容器，在连接mysql的整个过程中都存在。

• 自定义变量使用

• 自定义变量的限制
1. 无法使用查询缓存
2. 不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名或者limit子句
3. 用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信
4. 不能显式地声明自定义变量地类型
5. mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想地方式运行
6. 赋值符号：=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号
7. 使用未定义变量不会产生任何语法错误

• 使用案例
• 优化排名
• 避免重新查询更新值
• 确定取值顺序