mysql9

最新推荐文章于 2024-05-20 19:25:39 发布

Markland_l

最新推荐文章于 2024-05-20 19:25:39 发布

阅读量386

点赞数 1

分类专栏： mysql 文章标签： java sql 数据库

本文链接：https://blog.csdn.net/Markland_l/article/details/125468915

版权

mysql 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

复习

不要在索引上加操作
count查询、limit分页优化
- count中null处理是分裂的
- 分页中通过id查询
分区表
- 把一个物理大表拆分成很多子表
- 不可控
- 仍然在一台机器上，性能有限
- 市面上的分库分表，mycat、shardingjdbc、oneproxy
索引合并
- 多个二级索引，充分利用多个二级索引
- 如果查看执行计划，发现用到了索引合并，还可以考虑用联合索引替换索引合并

5.mysql的底层执行原理

连接查询

全连接，俗称的笛卡尔积

内连接和外连接

实例表
- student
  - number name major
    20200901 Jack 网络工程
    20200902 Mark 计算机科学
    20200903 James 计算机科学
    20200904 King 软件工程
- score
  - number subject score
    20200901 数据结构和算法 88
    20200901 网络通信原理 90
    20200902 数据结构和算法 95
    20200902 网络通信原理 89
    20200903 离散数学 70
要统计每一个人的成绩
- SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1, score
  
  AS s2 WHERE s1.number = s2.number;
- 由于king没有考试成绩，所以没有这条记录
  - 假如要显示这条记录，就就提出了内连接和外连接概念
内连接
- 对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该
  
  记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接。
外连接
- 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，
  
  也仍然需要加入到结果集。

number	name	major
20200901	Jack	网络工程
20200902	Mark	计算机科学
20200903	James	计算机科学
20200904	King	软件工程

number	subject	score
20200901	数据结构和算法	88
20200901	网络通信原理	90
20200902	数据结构和算法	95
20200902	网络通信原理	89
20200903	离散数学	70

左外连接

SELECT * FROM e1 LEFT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条件];
left join 是左边的表是驱动表

右外连接

SELECT * FROM e1 RIGHT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条件];
right join 是右边的表是驱动表
where条件
- WHERE 子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连
  
  接，凡是不符合 WHERE 子句中的过滤条件的记录都不会被加入最后的结果集。
on条件
- 对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 ON 子句
  
  中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记
  
  录的各个字段使用 NULL 值填充。
- 需要注意的是，这个 ON 子句是专门为外连接驱动表中的记录在被驱动表找
  
  不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，所以如果把ON 子句放到内连接中，MySQL 会把它和 WHERE 子句一样对待，也就是说：内
  
  连接中的 WHERE 子句和 ON 子句是等价的。

内连接

内连接涉及两表的查询条件，用on和用where是等价的
写法
- select * from e1,e2
- select * from e1 join e2
- select * from e1 inner join e2
- select * from e1 cross join e2
对于内连接，把谁写在前，谁写在后，没有任何影响，真正决定谁是驱动表，谁是被驱动表，只有在执行的时候才会决定

嵌套循环连接（Nested-Loop Join）

连接查询的伪代码可以认为是

for each row in e1 {   #此处表示遍历满足对e1单表查询结果集中的每一条记录

    for each row in e2 {   #此处表示对于某条e1表的记录来说，遍历满足对e2单表查询结果集中的每一条记录

            for each row in e3 {   #此处表示对于某条e1和e2表的记录组合来说，对t3表进行单表查询

            if row satisfies join conditions, send to client

        }
    }
}

时间复杂度是O(n乘m乘l)

驱动表只被访问一次，被驱动表被访问多次，如果被驱动表是全表扫描的话，驱动表被查出来多少条数据，被驱动表就要进行多少次全表扫描，这是灾难性的

使用索引加快连接速度

SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < ‘d’;
- 对e2表的m2或者n2建立索引

基于块的嵌套循环连接（Block Nested-Loop Join）

被驱动表中的数据需要反复从磁盘读到内存，然后释放，然后又读到内存，需要重复n次（n为驱动表中查出来满足条件的数据数）
mysql相想办法解决上述问题
- 事先在内存中创建一块内存join buffer，将驱动表中的多条数据装载到join buffer中，当去被驱动表中查询时，将这些join buffer中的多条数据，一次性和被驱动进行匹配
在extra中，有一种提示using join buffer(Block Nested Loop)
show variables like ‘join_buffer_size’
- 单位是字节，默认是256kb
连接查询除了对被驱动关联条件字段加索引外，如果机器的内存足够，还可以想办法调大join buffer的大小
无论单表还是多表，为什么不提倡使用 select * from
- select中的列和where条件中的列才会往join buffer中放
  - select * from e1,e2，就会把e1中所有的列放入join buffer中
  - select e1.m1 from e1,e2，就只会把e1的m1列放入join buffer中，自然能放更多的数据
- 那什么时候情况下可以使用select * from，如果是要把查询的数据中放入缓存

Mysql的查询成本计算

什么是成本？

io成本
- 我们的表经常使用的 MyISAM、InnoDB 存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。
cpu成本
- 读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为 CPU 成本。
对于 InnoDB 存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页面花费的成本默认是 1.0，读取以及检测一条记录是否符合搜索条件的成本默认是 0.2。1.0、0.2 这些数字称之为成本常数，这两个成本常数我们最常用到，当然还有其他的成本常数。
注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是 0.2。

单表查询的成本

基于成本的优化步骤

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

实战分析

示例sql

SELECT * FROM order_exp WHERE order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) AND expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’ AND insert_time> expire_time AND order_note LIKE ‘%7排1%’ AND order_status = 0;
建立的索引
- u_idx_day_status (insert_time, order_status, expire_time)
- idx_order_no (order_no)
- idx_expire_time (expire_time)
分析过程
- 1.索引使用
  - order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)
  - expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’
- 2.全表扫描，就是把聚簇索引中的全部数据读取到内存中，然后判断是否满足where条件
  - 1.包括io成本和cpu成本
  - 2.成本计算需要两个数据，数据占用页面数和数据行数，mysql有一系列统计数据，show table like ‘order_exp’\G，其中Rows就是行数，不过是一个估计值，Data_length是聚簇索引的字节数，1589248，除以16除以1024，等于97个页面
    - 97 x 1.0 + 1.1 = 98.1
    - 10350x 0.2 + 1.0 = 2071
    - TIPS：我们前边说过表中的记录其实都存储在聚簇索引对应B+树的叶子节点
      
      中，所以只要我们通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组
      
      成的双向链表把所有记录都查看一遍。也就是说全表扫描这个过程其实有的B+树非叶子节点是不需要访问的，但是 MySQL在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算 I/O 成本的依据，是不区分非叶子节点和叶子节点的。
- 3.分开计算两个索引的成本，最后计算一下合并索引的成本
  - expire_time，使用成本，对应的范围区间就是： (‘2021-03-22 18:28:28’ , ‘2021-03-22 18:35:09’ )，使用 idx_expire_time 搜索会使用用二级索引 + 回表方式的查询，MySQL 计算这种查询的成本依赖两个方面的数据，
    - 1、范围区间数量
      
      不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索引的一个范围区间的 I/O 成本和读取一个页面是相同的。本例中使用 idx_expire_time 的范围区间只有一个，所以相当于访问这个范围区间的二级索引付出的 I/O 成本就是：1 x 1.0 = 1.0
    - 2、需要回表的记录数
      
      优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算 idx_expire_time 在(‘2021-03-22 18:28:28’ ，‘2021-03-22 18:35:09’) 这个范围区间中包含多少二级索引记录
      - 如果两个区间端点所在的页面，相差不超过10个页面，在mysql5.7里面会做一个精确统计，超过10个页面，会统计前10个页面，每个页面相隔数据量的平均值，然后求取总数
      - explain select * from order_exp where expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’
        
        一共要扫描39行
      - 读取的cpu成本，39*0.2+0.01 = 7.81
      - 回表的io成本，39*1.0 = 39
      - 过滤检查是否符合，回表的cpu成本，39*0.2=7.8
      - 总成本 1.0+7.81+39+7.8 = 55.61
  - order_no
    - explain SELECT * FROM order_exp WHERE order_no IN (‘DD00_6S’, ‘DD00_9S’,
      
      ‘DD00_10S’);
    - 由于使用 idx_expire_time 时有 3 个单点区间，所以每个单点区间都需要查找
      
      一遍对应的二级索引记录数，三个单点区间总共需要回表的记录数是 58。扫描区间的io成本是 3*1.0 = 3.0
    - 读取这些二级索引记录的 CPU 成本就是：58 x 0.2 + 0.01 = 11.61
    - 根据这些记录里的主键值到聚簇索引中做回表操作，所需的 I/O 成本就是：
      
      58 x 1.0 = 58.0
    - 回表操作后得到的完整用户记录，然后再比较其他搜索条件是否成立
      
      此步骤对应的 CPU 成本就是： 58 x 0.2 = 11.6
    - 总成本 3.0+11.61+58+11.6=84.21
- 4.执行计划
  - 全表：2169.1
  - expire_time：55.61
  - order_no：82.21
  - 所以最终选择用expire_time的索引
  - 实际比较成本时，索引计算成本时不会算上最后一次的回表成本，idx_expire_time为47.81(55.61-7.8)，idx_order_no为72.61(84.21-11.6)。
- explain SELECT * FROM order_exp WHERE order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) AND expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’ AND insert_time> expire_time AND order_note LIKE ‘%7排1%’ AND order_status = 0;
  - 可能的索引是expire_time和order_no，最终使用的索引是order_no

基于索引统计数据的成本计算

index dive

SELECT * FROM order_exp WHERE order_no IN (‘aa1’, ‘aa2’, ‘aa3’, … , ‘zzz’);
MySQL 把这种通过直接访问索引对应的 B+树来计算某个范围区间对应的索引记录条数的方式称之为 index dive。
有零星几个单点区间的话，使用 index dive 的方式去计算这些单点区间对应的记录数也不是什么问题，如果 IN 语句里 20000 个参数怎么办？
- 这就意味着 MySQL 的查询优化器为了计算这些单点区间对应的索引记录条数，要进行 20000 次 index dive 操作，这性能损耗就很大，搞不好计算这些单点区间对应的索引记录条数的成本比直接全表扫描的成本都大了。MySQL 考虑到了这种情况，所以提供了一个系统变量 eq_range_index_dive_limit，我们看一下在 MySQL 5.7.21 中这个系统变量的默认值：200
- show variables like ‘%dive%’;
如果我们的 IN 语句中的参数个数大于或等于 200 个的话，就不能使用 index dive 了，要使用所谓的索引统计数据来进行估算
- show index from order_exp;
  - Non_unique ：索引列的值是否是唯一的
  - Seq_in_index ：索引列在索引中的位置
  - Collation：索引列中的值是按照何种排序方式存放的，值为 A 时代表升序存放，为 NULL 时代表降序存放。
  - Cardinality：索引列中不重复值的数量，这是估计值，不是精确值
    - id：10345
    - insert_time：990
    - order_status：990
    - expire_time（联合索引中的第三列）：10350
    - order_no：10220
    - expire_time（索引idx_expire_time中的列）：9802
- SHOW TABLE STATUS 展示出的 Rows 值，也就是一个表中有多少条记录。之前统计出来是10350
- 以 order_exp 表的 idx_order_no 索引为例，它的 Rows 值是 10350，它对应的 Cardinality 值是 10220，所以我们可以计算 order_no 列平均单个值的重复次数就是：
  - 10350÷ 10220≈ 1.012（条）
  - 假设 IN 语句中有 20000 个参数的话，就直接使用统计数据来估算这些参数需要单点区间对应的记录条数了，每个参数大约对应 1.012 条记录，所以总共需要回表的记录数就是：
    
    20000 x 1.012= 21,730
当你的查询中使用到了 IN 查询，但是却实际没有用到索引，就应该考虑一下是不是由于 eq_range_index_dive_limit 值太小导致的。