高性能mysql

博客, 技术 / 作者： / 2020年12月6日

数据库查询生命周期

  1. 客户端
  • 客户端到服务端
  1. 服务端
  • 服务器解析
  • 生成执行计划
  • 执行 执行占用时间最大
  • 返回结果

慢查询基础

查询优化访问

  1. 确认应用是否检索大量数据，访问太多行、列
  2. 确认mysql是否在分析大量超过需要的数据
  3. 返回太多的列和行

衡量查询开销三个指标

  1. 扫描行数 explain type列反应了访问类型，从全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用 速度从快到慢
  2. 返回行数
  3. 响应时间，服务时间（数据库处理这个查询真正时间）、排队时间（由于原因I/O、锁等待而没有真正执行），影响响应时间的因素有很多：锁、高并发资源竞争、硬件原因，计算响应时间：查询需要的索引、执行计划、顺序IO、随机IO相加的时间

mysql where 条件的好快顺序

  1. 再索引中使用where 条件过滤，在存储引擎层完成（索引帅选是在存储引擎完成）
  2. 使用覆盖索引，extra：using index，直接从索引中过滤不需要的结果
  3. 从数据表中返回数据，然后过滤不满足条件的数据，extra：using where，这是mysql 服务器完成的。mysql 从数据表中读取数据后过滤

重构查询方式

复杂查询还是多个简单查询

  1. mysql 从设计上链接和断开都是轻量级，返回一个小德查询结果很高效，现代网络速度比过去强很多，所以多个小查询不是问题
  2. mysql 内部美妙能够扫描内存上百万行数据

切分查询

  1. 将多个更新、删除操作分开执行，可以减少锁时间、减少阻塞、防止耗尽资源、减少复制延迟

分解关联查询

  1. 让缓存效率更高
  2. 单个查询，可以减少锁竞争
  3. 应用层关联，可以让数据库更容易拆分
  4. 减少冗余记录查询

查询方式基础

sql执行生命周期

  1. 服务器查询缓存，命中缓存，立刻返回
  2. 服务器sql解析、预处理、再由优化器生成对应的执行计划
  3. mysq根据优化的执行计划，调用存储引擎的API来查询
  4. 返回客户端
mysql 客户/服务端通信协议
  1. 通信协议是半双工，两者不能同时发送数据
  2. 客户端发送的请求，只有等待，当有大量数据，客户端只能全部接受，而不能简单的只取前几条
  3. 服务端缓存可以让查询早点结束，释放资源，但是不适合大量数据缓存，缓存需要维护。
  • 通常大量的查询，不适用缓存反而更优
  • 缺点释放资源缓慢，占用资源
mysql 查询状态
  1. sleep 等待客户端发送请求
  2. query 线程正在执行查询或者将结果发送客户端
  3. locked mysq 服务器层，正在等待表锁（存储引擎级别的锁不体现在线程状态中）
  4. analyzing and statistics 线程正在收集存储引擎统计计划
  5. coping to tmp table 线程正在执行查询，并将结果复制到另外一个临时表 — group by 或者union 操作。如果构面有on disk
  6. sorting result 线程正在对结果集排序
  7. sending data 向客户端发送数据、生成结果集、线程在多个线程之间传送数据

查询缓存

  1. mysql 会检查是否命中缓存，检查是通过大小写敏感hash查找实现的
  2. 如果命中，并且用户有权限，直接返回结果集，释放相关资源

解析器及预处理

  1. 语法解析器，通过关键字将sql语句解析并生成解析树
  2. 预处理，进一步检查解析树是否合法，如表、数据列是否存在，是否有歧义

优化器

  1. 到查询优化器，sql 已经认为是合法
  2. 优化器将解析树转化为执行计划，一条相同结果的查询，会有很多执行计划，解析器负责找出一条最优
  3. mysql 基于成本的优化器，尝试预测一个查询使用某种执行计划并选择成本最小的一个
  4. 优化器失效
  • 统计信息不正确，mysql 依赖存储统计成本，但是有些存储引擎不准，如innodb mvvc 的架构
  • 执行计划的成本估算不等同于实际执行成本，即使统计信息准确，执行计划也有可能不是最优的。优化器考虑不了磁盘读取顺序、数据是否在内存、io次数
  • mysql 优化器是以成本为估算，并不是最短执行时间，有时候考虑资源
  • mysql 优化器不会考虑目前系统压力来评估
  • 优化器有可能不能评估执行计划，从而错过最优执行计划
  1. 优化器策略
  • 静态优化 针对特定变量的优化，可以认为是编译时优化
  • 动态优化 可以认为是运行时优化，如jvm 的 interpreter
  • 优化场景：重新定义表关联顺序
  • 使用等价变换规则 ，将一些等式合并 、删除恒等式（5=5） 和cpu 乱序执行有点儿像。在不影响结果的情况下，优化查询语句
  • 外关联转化为内关联
  • 优化count、min、max，max 和 min 基于b-tree 特性，查找树最左数据及最又端数据即可。 expain 中可以看到 select tables optimized away
  • 覆盖索引扫描 当索引中包含查询的列，
  • 子查询优化
  • 提前终止查询 limit是一个案例，不纯在值的查询，立刻返回。假如该字段是unsigned然而查询的是-1
  • 预估并转化为常数表达式， expian type=const
  • 等值传播，两表用id 关联时，无需写 a.id > 500 and b.aid > 500。这样反而增加数据库压力
  • 列表in比较，in查询时，会使用二分查询 时间复杂度o(logn)
  • 有时优化器也会失误，假如自己更了解表结构，强制指定使用索引。通过关键字： use index

数据和索引信息统计

  • mysql 服务器没有统计任何信息，这些信息都存储到存储引擎中
  • 统计信息： 索引基数、数据航、索引长度、索引分布信息

mysql 如何执行关联查询

  1. mysql 执行关联查询类似笛卡尔积，查询次数： 驱动表行数非驱动表行数 + 驱动表行数-1非驱动表行数 +….
  2. 执行计划执行方式：从解析树最深开始，逐层向上解析 (嵌套循环关联)
  3. mysql 关联优化器会在所关联的顺序中选择成本最小的来执行计划树，不过机器不是所有情况都准确，可以使用STRAIGHT_JOIN来强制驱动表
关联查询优化
  1. a join b on a.key=b.key ，a作为驱动表,a.key 不需要索引，b需要索引，因为关联查询逻辑，是驱动表轮训查询B表，所以b.key 需要索引
  2. group by中的值，只涉及到一张表

排序优化

  1. 尽可能避免排序，使用索引排序
  2. filesort 是mysql 进行了排序操作，如果排序数据小于排序缓冲区则内存中排序，否则硬盘中排序
  3. 排序方式
  • 两次排序方式（旧）读取行指针及排序字段，然后根据排序结果读取所有的行，缺点IO 次数频繁
  • 单词排序方式（新版使用） 查询需要排序的列，直接排序，对于IO 密集型，提高了效率，缺点是暂用更多的空间
  1. mysql 使用文件排序，使用的临时存储空间可能比想象的多（如：varchar 需要完整长度）
  2. 关联查询排序：
  • 假如order by 的字段都在一个表中，处理第一个表时，就会排序
  • 假如order by 的字段来自不同表，在join 查询完后，统一排序（use temporary,use filesort） 会使用临时表

执行引擎

  1. 执行引擎，没有优化阶段复杂，负责执行计划给出的指令
  2. 执行引擎通过存储引擎api接口来完成（Hanlder API），这种结构，让可插拔称为可能

返回结果给客户端

  1. mysql 将结果返回给客户端，是一个增量的过程，这样处理有两个好处，服务端无需存储过多数据而消耗过多的内存，增加结果的实时性
  2. 查询结果缓存可以被缓存，mysql 在这个阶段将结果缓存

mysql 优化器的局限性

关联子查询
  1. 关联子查询 explain SELECT * from repay_order where loan_key in (SELECT loan_key from loan where create_time>’2020-01-10′); loan_key 和 create_time 都有所引但是，在explain 中 loan
    表的type 是all
  2. 原因主要是优化器会解析成 SELECT * from repay_order where loan_key exist (SELECT loan_key from loan where create_time>’2020-01-10′ and loan_key = loan_key)，认为
  3. 出现这种情况，可以使用inner join，或者使用in (group by concat) 的方式
  4. 子查询和join 可以相互转换，可以通过实际查询来判别那种性能更好，不要道听途说
union all 限制
  1. union all 无法将外层限制，下推到内层，如 a union all b limit 20 ,这个语句会把a和b权标关联后放到临时表中取20条，而不是各取20条
索引合并优化
等值传递
hash关联

mysql 不支持hash 关联

松散索引扫描

mysql 不支持松散扫描，索引扫描，只需要其中的一部分，但是mysql会把起点和终点一起扫描
select * from x where b between 2 and 3 (联合索引a,b) 。会全表扫描

最大值和最小值优化

select min(actor_id) from sakila.actor where first_name =’People’; 这条会进行全表扫描，first_name 上没有索引。其实可以找出第一条出现people 的数据
可以重写为：
select actor_id from sakila.actor USE INDEX(PRIMARY) where first_name =’People’ limit 1;

同一个表上查询和更新

mysql 不允许对同一个表同时进行查询和更新(需要了解mysql 如何执行sql ，就可以明白)
update loan as l set batch_no = (select batch_no from loan x where l.id =x.id);
可以通过临时表的方式来绕过上面限制：
update loan as l inner join (select id ,batch_no from loan group by batch_no ) as x on x.id = l.id set l.batch_no = x.batch_no

查询优化器的提示（hint）

FORCE INDEX

SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …
以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引,强制优化器使用该索引

IGNORE INDEX 忽略使用索引

SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …

SQL_NO_CACHE 关闭查询缓冲

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

SQL_CACHE 强制查询缓冲

SELECT SQL_CALHE * FROM TABLE1;

HIGH_PRIORITY，LOW_PRIORITY

  • update LOW_PRIORITY table1 set field1= where field1= … 改执行等待队列中无，该表操作时，再执行

优化子查询

  • 尽可能使用关联查询，在mysql5.6以后是不科学的， 5.6以后可以忽略子查询建议了，5.6之后可以放心大胆的用子查询了，索引key 可以走索引