3. mysql优化

2020-08-20 本文已影响0人 lj72808up

一. 如何思考优化

1.1 Database级别的优化:

表结构是否合适?
比如应用频繁的更新很多张有很少column的表, 但经过分析得知, 其实应用可以只更新几张有很多column的表
索引是否正确达到极速查询的目的
表是否采用了何时的行存储格式? 这依赖mysql的存储迎请选择
比如: 采用表压缩可以降低磁盘占用, 减少磁盘IO. 表压缩适用于所有的InnoDB表, 但只适用于read-only的MyISAM表
合适的锁策略(lock strategy)
InnoDB存储引擎处理了绝大多数情况的锁事件
用于cache的内存大小设置的是否合理?
应该足够大来缓存经常访问的数据, 同时又不至于太大超多物理内存而产生缺页.
主要配置的内存区域为: InnoDB下的buffer pool, MyISAM下的key cache, 和mysql的query cache.

1.2 硬件层级的优化

随着数据库越来越繁忙, 最终都会抵达硬件的极限; 系统瓶颈一般来自这些资源:

磁盘:
磁盘查找时间(disk seek): 找到一个数据偏所花的时间, 现代磁盘一般在10ms左右
磁盘读写时间: 现代磁盘一般在10~20mb每秒. 可以会用多个磁盘并行读写的方式优化
CPU频率:
对于大表的计算, 高频率带来高性能; 如果表很小, CPU频率往往不是瓶颈
内存带宽

二. SELECT语句优化

2.1 如何思考 SELECT 语句优化

SELECT语句的优化, 处于所有sql优化中的最高优先级; 如下的这些SELECT优化方法, 同样适用于 CREATE TABLE...AS SELECT, INSERT INTO...SELECT 和 DELETE...WHERE语法. 主要的查询优化手段有:

SELECT ... WHERE 慢查询的加速
首先, 考虑是否能加索引( index ). 在 where 条件的 column 上建立索引, 可以加快计算, 过滤和结果遍历. 为了避免浪费磁盘空间, 索引个数要少量有效
index 的相关内容, 参考8.3.1, 8.8.1
Isolate and tune any part of the query, such as a function call, that takes excessive time. Depending on how the query is structured, a function could be called once for every row in the result set, or even once for every row in the table, greatly magnifying any inefficiency.

最小化查询时的全表扫描 ( full table scans ). 尤其是大表的扫描
对表进行定期的 ANALYZE TABLE 统计, 便于优化器构建高效的执行计划
不同的存储引擎 (storage engine) 有自己独特的优化技巧. 参考8.5.6的innodb引擎优化, 8.6.1mylsam引擎优化
Innodb的表, 可以对只读事务优化. 参考8.5.3
不要对 query 语句做让人难以理解的变形, 特别是优化器可以对某些语句自动变形的时候
当 sql 语句性能不佳时, 使用 EXPLAIN 查看 plan , 以便在 where , join 条件中使用索引. (当你非常熟练后, 使用 EXPLAIN 查看执行计划应该是所有调优的第一步)
调整 mysql cache 的相关参数, 来高效使用 InnoDB 的 buffer pool; MyISAM 的 key cache, 和 MySQL query cache. 这回加速 sql 语句第二次及以后重复查询的执行速度. 因为查询结果可以直接从内存中获取.
增加可扩展性 (Scalability). 有时即使 query 使用了 cache 内存执行的很快也需要继续对它优化, 来降低 query 所需的 cache 大小., 提高应用的可扩展性. 可扩展性意味着应用可以同时响应更多用户, 更大的请求.
对锁进行处理 (locking). "锁"会影响同一时间使用同一张表的其它 session 对该表的访问

2.2 WHERE 条件优化

以下例子中, WHERE 条件都出现在 SELECT 语句中, 实际上这些优化方法同样适用于在 DELETE 和 UPDATE 语句中的 WHERE. 你可能热衷于重构 query 的格式, 牺牲可读性来加速 query , 但其实很多时候优化器会自动执行sql的重构, 因此只要保证 query 语句的可读性即可:

算术运算

移除不必要的括号

((a AND b) AND c OR (((a AND b) AND (c AND d))))
    -> (a AND b AND c) OR (a AND b AND c AND d)

常量折叠

(a<b AND b=c) AND a=5
    -> b>5 AND b=c AND a=5

删除常量条件

(b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
    -> b=5 OR b=6

index 上的常量表达式只会被计算一次
在单表上执行不带 where 条件的 coun(*), 可以直接从 MyISAM 的 table information , 或内存表中直接获取
如果不使用 GROUP BY , 则 HAVING 条件会被合并到 WHERE 中
5, join 表上的 where 条件可以让表跳过一些行
相比于 query 中的其它表, 常量表会被第一个读取. 常量表包括:
- 空表或只有一行的表
- WHERE 条件执行在主键, 或唯一索引 (PRIMARY KEY or a UNIQUE index) 上的表

SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
        WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

有关join操作:
- 最佳的 join 组合方式, 会在尝试所有可能性后被找到. 比如, 如果所有 ORDER BY 和 GROUP BY 的列都来自同一张表, 则这个表是 join 时的首选
- 如果 ORDER BY 从句和 GROUP BY 的条件不同, 或是 ORDER BY 和 GROUP BY 的列不是 join 的首选表, 则临时表被创建
- 使用 SQL_SMALL_RESULT 修饰符, 临时表会完全在内存中被创建
使用 index 还是 table scan 进行查询, 通常优化器都会选择使用 index, 除非优化器发现 index 涉及的数据查过了全表数据的30%. 目前来讲, 已经不存在一个确定的百分比能让优化器决定使用 index 还是 table scan. 优化器如今要考虑更多因素: table size(表大小), number of rows(行数), and I/O block size(IO块的大小)
In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.
每行在被输出前, 所有不匹配 having 字句的行都会被跳过

2.3 range 优化

range access 方法是通过使用一个索引, 来遍历表的部分行的方法. 它会满足一个或多个索引间隔的条件. 以下章节, 描述优化器使用 range access 时要满足的条件的条件

单一列索引上的 Range Access
在单一索引上, 定义 range 条件的方法:
- BTREE 和 HASH 索引下, column 和常量使用操作符 =, <=>, IN(), IS NULL, 或 IS NOT NULL 进行比较
- 此外, BTREE 索引使用如下操作符仍表示 range 条件
  1. 操作符 >, <, >=, <=, BETWEEN, !=, or <>
  2. LIKE 一个常量字符串 (不以通配符开头的字符串)
- 无论索引是什么形式, 多个 range 条件使用 OR 或 AND 进行组合后, 形成的仍是 range 条件
上述提到的常量, 是指:
1. 字符串构成的常量
2. const 表或 system 表的某一列
  1. system 表:
    只有一行的表 = system表, system 表是 const 表 join 的特殊情况
  2. const 表:
    最多只匹配一行的表. 因为只有一行, 这样的匹配结果也会被优化器当做一个常量来处理. 当使用 PRIMARY KEY(主键) 或 UNIQUE index(唯一键) 同常量值作比较时, 都会生成 const 表. 如下语句中 tbl_name 都是 const 表
```
SELECT * FROM tbl_name WHERE primary_key=1;   -- (主键)
SELECT * FROM tbl_name
   WHERE primary_key_part1=1 AND primary_key_part2=2;   -- (联合主键)
```
组合索引上的 Range Access
组合索引上的 Range Access 是单一列索引上执行 Range Access 的扩展 , range 条件通过一个有序的 key 元祖形式限制组合索引列上的值
- 对于Hash索引, range 条件只能是 AND 连接的且每个索引部分都只能进行等值判断的形式
  其中, const1, const2, … 是常量, cmp 可以是 =, <=> (相当于等于,只是比较的2个值中, 有一个为null返回0, 2个都为null返回1), IS NULL
```
    key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;
```
- 对于Btree索引, range 条件是 AND 连接不同索引部分, 且每个索引部分可以使用 =, <=>, IS NULL, >, <, >=, <=, !=, <>, BETWEEN, or LIKE 'pattern'(pattern不能以通配符开头) 同一个常量进行比较的形式
Equality Range Optimization of Many-Valued Comparisons
Skip Scan Range Access Method
Range Optimization of Row Constructor Expressions
Limiting Memory Use for Range Optimization