MySQL-成本与统计数据学习

yakax 发布于 2021-01-20 收录于 MySQL性能调优

MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询

基于成本的优化步骤

根据搜索条件，找出所有可能使用的索引
计算全表扫描的代价
计算使用不同索引执行查询的代价
对比各种执行方案的代价，找出成本最低的那一个

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
## 执行sql语句
SELECT * FROM single_table WHERE 
    key1 IN ('a', 'b', 'c') AND 
    key2 > 10 AND key2 < 1000 AND 
    key3 > key2 AND 
    key_part1 LIKE '%hello%' AND
    common_field = '123';
## possible keys只有idx_key1和idx_key2。

MySQL-日志学习

yakax 发布于 2021-01-20 收录于 MySQL性能调优

更新语句的基本流程

更新数据前会把数据写入uodo 日志文件
更新数据后会把数据写入redo缓冲池，这意味着这个缓冲池落地磁盘，跟事务有关系，可以通过innodb_flush_log_at_trx_commit 配置。
当参数是1时，就是必须把redo log写入磁盘才能提交事务。0 的时候就是不会有redo日志、2 的时候是先写oscache 这两种都有丢失数据的风险
buffer pool就是我们一直在操作的数据的缓冲区。
redo log 文件里面也是一块一块追加在一起的

MySQL-查询-SQL简化与子查询的物化

yakax 发布于 2021-01-20 收录于 MySQL性能调优

条件化简

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
## 移除不必要的括号
((a = 5 AND b = c) OR ((a > c) AND (c < 5))) 改 (a = 5 and b = c) OR (a > c AND c < 5)
## 常量传递（constant_propagation）
a = 5 AND b > a  改 a = 5 AND b > 5
## 移除没用的条件（trivial_condition_removal）
(a < 1 and b = b) OR (a = 6 OR 5 != 5)
很明显，b = b这个表达式永远为TRUE，5 != 5这个表达式永远为FALSE，所以简化后的表达式就是这样的：
a < 1 OR a = 6
## 表达式计算 
a = 5 + 1 改成 a=6 表达式不会对带有符号或者函数的进行化简,只有搜索条件中索引列和常数使用某些运算符连接起来才可能使用到索引
##HAVING子句和WHERE子句的合并
如果查询语句中没有出现诸如SUM、MAX等等的聚集函数以及GROUP BY子句，优化器就把HAVING子句和WHERE子句合并起来。

MySQL-索引-索引合并学习

yakax 发布于 2021-01-20 收录于 MySQL性能调优

MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但是，在这些特殊情况下也可能在一个查询中使用到多个二级索引，设计MySQL的把这种使用到多个索引来完成一次查询的执行方法称之为：index merge

所有的索引合并往往还是需要看数据回表的数量与 mysql 的优化器！优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并

MySQL-索引基本学习

yakax 发布于 2021-01-20 收录于 MySQL性能调优

InnoDB索引结构

每个页里面有最大和最小记录标识record_type=2,3
record_type=1是目录项 0是最终存储记录项
一个页16kb=16384字节可以存很多记录了基本3层就已经很多条记录了
每个节点的左儿子小于父节点，父节点又小于右儿子
索引每个节点的数据是已经排序好了的，当然如果你是联合索引，那也是排序好了的。

MySQL-连接查询学习

yakax 发布于 2021-01-20 收录于 MySQL性能调优

连接查询的基本语法

1
2
3
INNER JOIN  ## 内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集
LEFT JOIN  ## 右边未匹配的null展示
RIGHT JOIN   ## 左边未匹配的null展示