mysql 多个union all优化

转载

架构设计师之光 2024-10-21 15:12:47

文章标签 mysql 多个union all优化 mysql 数据库 sql sql语句 文章分类 MySQL 数据库

文章目录

mysql的架构图
mysql的执行过程
sql语句执行计划
索引失效的情况
mysql性能调优：

分库分表
读写分离---主从复制

mysql的架构图

mysql 多个union all优化_mysql

大致分为4层：连接层、服务层、引擎层、物理层

连接层：不必多说，连接用的
服务层：mysql的核心层，

1.sql接口，就是sql语句、视图、函数等执行不同的操作，
	2.解析sql，将sql语句构造出一个解析树，用于校验sql语句的正确性，比如sql的关键
	字是否正确，关键字的顺序是否正确。
	3.优化器，一条查询可以有很多种执行方式，最终返回相同的结果，优化器就是对sql
	语句进行优化。
	4.缓存，sql语句在执行查询前，先查询缓存，如果能找到对应的查询，则不必进行查
	询解析，优化，执行等过程，直接返回缓存中的结果集，
	如果缓存中没有数据，再开始进行解析、优化....

存储引擎层：存储引擎是针对表的，不是针对库，所以同一个库中不同的表有不同的存储引擎，更专业的来讲，它是根据MySQL AB公司提供的文件访问层的一个抽象接口来定制的一种文件访问机制，这种访问机制就叫存储引擎。负责MySQL数据的存储和提取，是通过提供一系列的接口来屏蔽不同引擎之间的差异。通俗的讲：就是在你创建表的时候，指定了存储引擎，那么该表数据就会按照指定的引擎的格式进行存储，然后通过sql语句进行访问的时候，总不能让你根据不同的引擎写sql语句吧。也就是说，mysql将你的sql语句按照查询的表设置的引擎进行转化处理。
物理层：物理文件层，就是你的数据保存在硬盘上。

mysql的执行过程

mysql 多个union all优化_mysql 多个union all优化_02

你可以看到mysql也是一个模块化的架构，可以对每个组件进程可插拔式的使用，非常符合我们java工程的理念。

sql语句执行计划

要想写出一个高质量的sql语句，你必须要掌握sql语句的执行顺序，以及sql语句的执行计划（过程）。
我们在写sql语句的时候，格式是：select distinct….from….join…on…where…group by….having….order by…limit 等等，但在数据库中执行的顺序，其实是这样的：from…on…join…where…group by…having…select distinct…order by …limit

sql优化，主要就是索引优化，其次是表级优化，数据库优化等

而对sql进行优化，必须了解sql语句的执行计划：

通过在sql语句前加explain关键字，结果中不同的字段分别表示不同的含义

id
id值越大，则越先执行
如果多个id值相等，则相同id值的行，则从上往下执行。
select_type 查询类型，表示当前行查询的类型，从这个字段我们可以看出它发生查询过程的一个描述

1. SIMPLE：简单的查询，没有子查询和UNION
	 2. PRIMARY： 如果有复杂查询的话，标记最外层的查询为primary
	 3. UNION：例如eg2中的谓语UNION关键字后面的查询就select_type标记为UNION
	 4. DEPENDENT  UNION：UNION中的第二个或后面的SELECT语句，取决于外面
	 的查询
	 5. UNION RESULT：UNION的结果
	 6. SUBQUERY:子查询中的第一个SELECT
	 7. DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
	 8. DERIVED：派生的表 ，例如eg3
	 9. MATERIALIZED：具体化的子查询,例如eg4
	 10. UNCACHEABLE SUBQUERY ：子查询的结果不能被缓存,必须重新评估为每一
	 行的外部查询
	 11. UNCACHEABLE UNION：UNION中的第二个或后面的SELECT语句，而且不能
	 被缓存

table，就是当前行查询是从哪个表中查询的，

1.deriver，表示这一行的查询是从衍生表中查询数据
	 2.subquery，表示这一行的查询是从子查询的数据中查，如select * from table1 
	 where id (select id from tanle2), 那么where id就是subquery
	 3.UNION<'','',''> 表示union了为2，3，4 的子查询的数据

这几个你可能有点难以理解，看图：

mysql 多个union all优化_mysql_03

mysql 多个union all优化_sql_04

mysql 多个union all优化_sql语句_05

请借助图来理解，这个字段有点绕，其实就是这一行的数据是从哪来的

type，type字段有很多，但是不要慌，我们实际用到的真的不多，记住常用的几个，当遇到其他的时候，回头看下笔记就可以

1. system：表里面只有一条数据，是const的一种特殊情况
	2. const：表中至多有一条数据符合。优化器将这一行中列当作常量来处理，因为只
	  读一次，所以速度非常快。常见：primary key 或者unique index
	3. eq_ref：索引扫描只读取一行数据，常用于primary key或者唯一为空（unique not 
	null index）
		SELECT * FROM ref_table,other_table  WHERE  ref_table.key_column=other_table.column;
		SELECT * FROM ref_table,other_table     WHERE ref_table.key_column_part1=other_table.column  AND ef_table.key_column_part2=1;
	4. ref：索引扫描返回多行数据，非唯一索引查找，意思就是虽然使用了索引，但该索
	引列的值并不唯一，有重复。
		SELECT * FROM ref_table WHERE key_column=expr;
		SELECT * FROM ref_table,other_table   WHERE ref_table.key_column=other_table.column;
		SELECT * FROM ref_table,other_table   WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
	5. fulltext：使用fulltext index 全文索引（myisam支持，innodb不支持）
	6. ref_or_null：类似于ref,会额外搜索包含NULL的行，常用于解析子查询
		eg:SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL
	7. index_merge：使用了索引合并优化，在输出key列上包含有多个index.
	8. unique_subquery：value IN (SELECT primary_key FROM single_table WHERE some_expr)
	9. index_subquery：value IN (SELECT key_column FROM single_table WHERE some_expr)
	10. range：有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要
	  优于index，检索给定范围的行，并使用index来选择行，例如 =,<>, >, >=, <, <=, 
	  IS NULL, <=>, BETWEEN, LIKE, or IN()
	11. index：扫描整个index tree，与all 的区别就是all是全表扫描，index是索引全部扫描
	12. all：全表扫描，实际使用中，如果数据量比较大，应该避免进行全表扫描，因为
	13. 这种连接是最慢的

	其中我们只要记住：system、eq_ref、ref、range、index、all这几个就可以了，其他
	的有印象就行，并且其中system、const只是理想情况，优化后实际能达到的ref、
	range等就不错了。*注意：对type进行优化的前提得是表中有索引*

possible_keys，mysql进行查询时候，可能用到的index，但有可能用不到，所以该列没有实际意义
key，查询的时候，实际用到的index，如果没有用到则为null
ref 表示为了筛选数据，表中哪些列和常量与index进行对比；如果是函数，则比较的是函数的值而不是表达式
rows 表示检查的行数，怎么理解呢？就是：当前行所发生的查询，是从这么多条记录中来的，举例：
假如有一个语句 select * from t where column_a = 1 and column_b = 2;全表假设有100条记录，column_a字段有索引（非联合索引），column_b没有索引。column_a = 1 的记录有20条， column_a = 1 and column_b = 2 的记录有5条。那么最终查询结果应该显示5条记录。 explain结果中的rows应该是20. 因为最终结果的的5条数据是从这20条记录中来的，mysql引擎必须对这20行逐行检查是否满足where条件
extra，同type，也是有很多枚举，但是也是只需记住其中几个就行，

1. using fileSort：使用额外操作来进行排序。（注意：1、排序针对的是order by后
	   面跟的列；2、根据排序后的列，顺序的检索每一行）
	 2. using temporary：使用临时表来存储结果，常出现去group by 或者order by （与
	   列存储的顺序不同才会使用临时表）操作
	 3. using  index：在索引树上只使用索引就得到结果，没有根据索引回表来单独查询
	   行。就是覆盖索引查询
	 4. using index  condition：上面的 using index 不需要回表查询，那using index 
	   where则需要每条记录都回表查询才得到最后的查询结果
	 5. using index for group-by：同using index类似，在group by的时候，可以通过一个
	   索引来检索索引树，无须回表查询
	 6. using where：使用了where子句来过滤整个表的数据，如果使用了索引，那就是
	   索引失效了。
	 7. Using sort_union(...),Using union(...), Using intersect(...)：使用特定的算法来显示
	   扫描索引是如何合并的在使用join type(index_merge)的时候

根据描述应该能猜到：在发生using filesort、using temporary、using where时，如果用了索引就表示索引失效了，为了性能，必须进行调整。

不同的字段分表表示不同的信息，那么进行sql优化，就得按照sql语句的执行顺序，一步一步进行调整，也就是从内到外一层层往外推进，在调优之前，要把外围因素都去掉。

如果你能理解上面字段表示的信息，我觉得你应该能发现我们只需记住需要的几个字段信息：extra、id、type、type等一些信息，其他信息对我们调优只是辅助我们了解发生的过程而已。

索引失效的情况

在列上进行计算操作或使用函数
使用like检索时，如果不以“%”打头会使用索引，如果打头，或者两边都有，都会索引失效
少用or，如果必须使用or运算符的时候，两边要都用上索引，比如索引列contion or索引列contion，如果只有左或者右其中一面使用索引，整个or都会索引失效
复合索引的时候，遵从最左原则，不能跳列，比如A、B、C三个子弹，使用时：A、AB、ABC都不会失效，如果BC则会失效，如果AC的话，A会走索引但C会失效
字符串类型的字段使用时，不加引号，数据库会隐式转化，也会导致索引失效
使用不等于运算符！=或者<>，无法使用索引，会导致全表扫描，比如说索引类!=0,
优化方法：索引列>0 or 索引列<0
使用is null和is not null的时候，索引不会对空值进行索引，如果必须得有，那就设置一个默认值，比如数字类型的话，默认为0
尽量使用覆盖索引
不能使用索引中范围条件右边的列，如：a=0 and b>10 and c=5,那么a、b会走索引，c不会使用索引
当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

mysql性能调优：

分库分表

当数据量特别大的时候，查询时间耗费的时间长，就不用说了吧，如果再加上联合查询，那你就知道在执行的时候，去上个卫生间，再剪个指甲，然后发现结果不对，调试sql，再执行一遍，然后你可以去接点水，跟旁边同事聊聊天，…所以，当数据量大的时候，分库分表是必须要进行的。现在主流的工具是使用mycat实现分库分表

分表：分表有两种方案
（1）（水平切割）以数据量为分割点：限制每个单表的数据量，达到一定程度后，进行分表。
（2）（垂直切割）以热点数据为分割点：对一个业务表中的不同字段进行区分，比如某些字段查询的次数多，但是修改少，某些字段修改多，等其他业务需求进行分表
对不同的表针对不同的情况使用不同的引擎。
分库：字面意思，就可以看出来，是把大的表结构，横向切割为同样结构的不同表，这样切分是为了保证单表的容量不会过大，从而来保证单表的查询等处理能力。

分库分表可以同时存在，不冲突，比如我分表了，不一定非得在同一个数据中，也可以在不同的数据中。