select查询语句
select查询语句中join连接是如何工作的？
1、INNER JOIN
返回两个表中的匹配行。
2、LEFT JOIN
返回左表中的所有记录以及右表中的匹配记录。
3、RIGHT JOIN
返回右表中的所有记录以及左表中的匹配记录。
4、FULL OUTER JOIN
返回左侧或右侧表中有匹配的所有记录。
select查询语句执行顺序
SQL查询语句的执行顺序以及JOIN的使用。作为一种声明式编程语言,SQL的执行顺序和我们编写的语句顺序并不完全相同。
理解SQL的执行顺序,有助于使用者更好地优化查询语句,提高查询效率。此外,SQL中的JOIN语句是非常重要和常用的,用于关联多个表进行查询。
SQL是一种声明式的编程语言。这意味着使用者在编写SQL查询语句时,只需要指定想要的结果,而不需要关心具体的实现步骤。数据库系统会根据最优的执行计划来执行使用者的查询。
SELECT DISTINCT Table1., Table2.
FROM
Table1 JOIN Table2 ON matching_condition
WHERE constraint_expression
GROUP BY [columns]
HAVING constraint_expression
ORDER BY [columns] LIMIT count
select distinct s.id
from T t join S s on t.id=s.id
where t.name="zhouxx"
group by t.mobile
having count(*)>2
order by s.create_time
limit 5;
按照的顺序：
1. FROM & JOIN: 集合的交并补，即上面的join连接操作
FROM子句：确定数据来源，包括JOIN的表 ON子句：执行JOIN条件。 JOIN子句：如果有的话，根据JOIN类型（如INNER、LEFT）连接表：
Left 左表为基础表，右表对应数据不存在则为Null，形成新的虚拟表
RIGHT 右表为基础表，左表对应数据不存在则为Null，形成新的虚拟表
首先根据FROM后边的前两个表做一个笛卡尔积生成虚拟表table1，对应步骤1中的from
然后根据ON语句的条件对table1进行筛选生成table2，对应步骤1中的on
然后根据连接关键字Left、Right、Outer等，对table2进行补充形成table3，对应步骤1中的join
如果超过两张表就重复1-3最终形成虚拟表table4
2. WHERE: 过滤记录
对上一步得到的中间结果集进行过滤。
通过where语句进行筛选，形成虚拟表table5，对应步骤2
条件是订单的创建时间(create_time)在'2023-01-01'到'2023-12-31'之间。
这一步删除了不在指定时间范围内的订单数据。
3. GROUP BY: 根据指定的列分组记录
就对table5分组形成 虚拟表table6，对应步骤3
将上一步的结果集按照用户所在地区(region)进行分组。
这一步将相同地区的用户订单信息聚合在一起。
4. HAVING: 过滤分组GROUP BY
对分组后的结果集进一步过滤。
条件是每个地区的总订单金额大于1000000。
这一步删除了消费总额不满足条件的地区。
5. SELECT: 选取特定的列
DISTINCT子句：去除重复数据。
函数：对列做特殊运算
选择结果集中需要保留的列,包括地区(region)和总金额(total_amount)。
其中总金额是通过SUM(o.amount)计算得到的。
6. ORDER BY: 最后对结果进行排序
执行order by子句，此时返回的一个游标，对应步骤6
按照总金额(total_amount)对结果集进行倒序排序。
这一步将消费总额高的地区排在前面。
7. LIMIT:
LIMIT/OFFSET子句（或者是TOP，或者是FETCH）：最后的结果截取。
限制结果集的数量为10。
这一步返回消费总额最高的前10个地区。
============================================================
一 、select语句关键字的定义顺序：
select distinct <select_list>
from <left_table>
<join_type> join
on <join_condition>
where <where_condition>
group by <group_by_list>
having <having_condition>
order by <order_by_condition>
limit <limit_number>
二 、 select语句关键字的执行顺序：
(7) select
(8) distinct <select_list>
(1) from <left_table>
(3) <join_type> join <right_table>
(2) on <join_condition>
(4) where <where_condition>
(5) group by <group_by_list>
(6) having <having_condition>
(9) order by <order_by_condition>
(10) limit <limit_number>
第一步 执行 from 知道先从<left_table>这个表开始的
第二步 执行 on 过滤 根据 <join_condition> 这里的条件过滤掉不符合内容的数据
第三步 执行 join 添加外部行
-------- inner join 找两张表共同的部分
--------- left join 以左表为准，找出左表所有的信息，包括右表没有的
--------- right join 以右表为准，找出左表所有的信息，包括左表没有的
--------- #注意：mysql不支持全外连接 full JOIN 可以用union
第四步 执行 where 条件 where后加需要满足的条件，然后就会得到满足条件的数据
第五步 执行 group by 分组 当我们得到满足where条件后的数据时候，group by 可以对其进行分组操作
第六步 执行 having 过滤 having 和 group by 通常配合使用，可以对 满足where条件内容进行过滤
第七步 执行 select 打印操作 当以上内容都满足之后，才会执行得到select列表
第八步 执行 distinct 去重 得到select列表之后，如果指定有 distinct ，执行select后会执行 distinct去重操作
第九步 执行 order by 排序 以上得到select列表 也经过去重 基本上就已经得到想要的所有内容了 然后就会执行 order by 排序asc desc
第十步 执行 limit 限制打印行数，我们可以用limit 来打印出我们想要显示多少行。select语句疑惑问题
分析完mysql的执行顺序，很明显别名不可以在join on中使用，因为join on在select之前就执行了，但是我又产生了新的疑问：
- select在group by之后执行，为什么group by中可以使用别名？
- 用on筛选和用where筛选有什么区别？
- order by在select之后，为什么order by可以用select中未选择的列呢？
问题1：select在group by之后执行，为什么group by中可以使用别名？
mysql官网也没有给出具体原因
但目前可以确定的是：select肯定在group by之前执行了一次，可以理解成在原有顺序的基础上的预加载，也就是说对于mysql，每一次运行代码，实际上执行了至少两次select。
问题2：用on筛选和用where筛选有什么区别？
on和where的最大区别在于，如果在on应用逻辑表达式那么在第三步join中还可以把移除的行再次添加回来，而where的移除的最终的；
问题3：order by在select之后，为什么order by可以用select中未选择的列呢？
没找到答案，希望大佬可以解惑。
SQL解析器构思元数据并产出影响与血缘分析
通过该解释器将ETL的SQL 全部解析
ETL名称：in 、out、表、字段、条件 的结构。最后将这些结构连起来就是一个血缘图。下图是完整SQL元数据结构部分模型
其主要的内容可以简单理解为，将SQL语句解析为语法分析树,然后通过Toke序列转化为语法分析树,最终抽象语法树被传递给错误检查和语义分析阶段进行处理。
-
SQL语句:用户或应用程序提交一个或多个SQL语句给数据库执行。
-
SQL语句解析:数据库接收到SQL语句后,会对其进行词法和语法分析。词法分析将SQL语句分解成一个个Token(如关键字、标识符、运算符等),语法分析则根据SQL语法规则验证这些Token序列是否构成一个有效的SQL语句
-
生成语法树:如果SQL语句通过了词法和语法检查,解析器会根据语句的结构生成一棵语法树。语法树以树形结构表示SQL语句各个语法单元之间的关系,叶子节点对应SQL语句中的原子元素如表名、列名、值等,非叶节点则对应各种SQL子句如SELECT、FROM、WHERE等
-
语义分析:语法树生成后还需进行语义检查,如检查表和列是否存在,列之间的数据类型是否匹配等
MySQL架构
Server层负责建立连接、分析和执行SQL
- 连接器、查询缓存、解析器、预处理器、优化器、执行器
- 内置函数：日期、事件、数学、加密函数
- 跨存储引擎的功能：存储过程、触发器、视图
存储引擎负责数据的存储和提取
- InnoDB（5.5版本开始默认引擎）
- MyISAM
- Memory
我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。
连接器
- 建立连接（TCP三次握手、四次挥手）
- 管理连接
- 校验用户身份
如果一个用户已经建立了连接，即使管理员中途修改了该用户的权限，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
查询缓存
如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。
如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。
MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。对于 MySQL 8.0 之前的版本，如果想关闭查询缓存，我们可以通过将参数 query_cache_type 设置成 DEMAND。
解析SQL
解析器
-
词法分析，构建出 SQL 语法树
-
语法分析，判断 SQL 语句是否满足 MySQL 语法（关键字拼写错误）
执行SQL
预处理器
- 检查 SQL 查询语句中的表或者字段是否存在（不存在报错）；
- 将
select *中的*符号，扩展为表上的所有列；
优化器
优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。
主键索引
- 属于聚簇索引，索引和数据一块储存
- InnoDB只有主键索引才能是聚簇索引
所谓的聚簇索引，就是一个节点就是整个的一行数据。我们平常见到的二叉树数据结构像这样
struct TreeNode {
int val;
TreeNode *left;
TreeNode *right;
TreeNode() : val(0), left(nullptr), right(nullptr) {}
TreeNode(int x) : val(x), left(nullptr), right(nullptr) {}
TreeNode(int x, TreeNode *left, TreeNode *right) : val(x), left(left), right(right) {}
};数据结构中只有左右指针和当前节点的值，可以根据节点的值建立二叉搜索树。代入到聚簇索引的定义中，根据主键建立B+树，就像二叉搜索树左孩子val小于自己，右孩子大于自己。数据结构储存更多的东西，把表中一行的所有内容都作为成员变量存起来。找到了主键的节点，也就找到了这一行的所有数据。
非聚簇索引则是使用索引项建立B+树，例如根据年龄、名字等，节点里面则储存着对应行的主键。比如我要查询年龄大于21岁的人，并且年龄这一列拥有索引（显然应该是非聚簇的，因为年龄可能重复）。抽象地，我们认为把年龄进行了排序，我们仅能看到一群年龄从小到大，而不知道这些人是谁。我们把大于21岁人拉过来，挨个撕开他们的面纱，也就是他们的主键，才知道是谁。非聚簇索引只储存主键，如果要查询那一行的其他信息，则要根据主键再进行查询，也就是用上面的那个聚簇索引，找到了主键就找到了那一行的所有值。这称为回表查询。
非聚簇索引不一定进行回表查询。覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。
用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。（是不是很神奇？这不废话吗
二级索引
二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。二级索引都是非聚簇索引。
- 唯一索引(Unique Key)
- 普通索引(Index)
- 前缀索引(Prefix)
回到优化器，举一个例子：
select id from product where id > 1 and name like 'i%';
product 表有主键索引（id）和普通索引（name）。这条查询语句的结果既可以使用主键索引，也可以使用普通索引，但是执行的效率会不同。这时，就需要优化器来决定使用哪个索引了。
很显然这条查询语句是覆盖索引，直接在二级索引就能查找到结果（因为二级索引的 B+ 树的叶子节点的数据存储的是主键值），就没必要在主键索引查找了，因为查询主键索引的 B+ 树的成本会比查询二级索引的 B+ 的成本大，优化器基于查询成本的考虑，会选择查询代价小的普通索引。
执行器
在执行的过程中，执行器和存储引擎交互，交互是以记录为单位的。
- 主键索引查询
select * from product where id = 1;
存储引擎通过主键索引的 B+ 树结构定位到 id = 1的第一条记录，如果记录是不存在的，就会向执行器上报记录找不到的错误，然后查询结束。如果记录是存在的，就会将记录返回给执行器；
执行器从存储引擎读到记录后，接着判断记录是否符合查询条件（其他查询条件，这一步只是满足了主键的条件），如果符合则发送给客户端，如果不符合则跳过该记录。
- 全表扫描
存储引擎把一条记录取出后就将其返回给执行器（Server层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；
Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的时候是直接显示所有记录的，是因为客户端是等查询语句查询完成后，才会显示出所有的记录
- 索引下推
MySQL 5.6 推出的查询优化策略。索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。
select * from t_user where age > 20 and reward = 100000;
联合索引当遇到范围查询 (>、<) 就会停止匹配，也就是 age 字段能用到联合索引，但是 reward 字段则无法利用到索引。
那么，不使用索引下推（MySQL 5.6 之前的版本）时，执行器与存储引擎的执行流程是这样的：
- Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
- 存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层；
- Server 层在判断该记录的 reward 是否等于 100000，如果成立则将其发送给客户端；否则跳过该记录；
- 接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给 Server 层；
- 如此往复，直到存储引擎把表中的所有记录读完。
可以看到，没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给 Server，接着 Server 再判断该记录的 reward 是否等于 100000。
而使用索引下推后，判断记录的 reward 是否等于 100000 的工作交给了存储引擎层，过程如下 ：
- Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
- 存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。
- Server 层在判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。
- 如此往复，直到存储引擎把表中的所有记录读完。
可以看到，使用了索引下推后，虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引（age，reward）里，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。
MySQL执行sql语句的流程
贴一个全的图：添删改查语句执行过程
1.1:连接器（Connection Manager）
MySQL 的执行流程始于连接器。当客户端请求与 MySQL 建立连接时，连接器负责处理这些连接请求。它验证客户端的身份和权限，然后分配一个线程来处理该连接。MySQL 每个连接线程会创建一个会话（session），在这个会话中，客户端可以发送 SQL 语句进行增删改查等操作。
连接器的主要职责就是:
- ①负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据,而不能同时进行,其中mysql在与客户端连接TC/IP的
- ②验证请求用户的账户和密码是否正确,如果账户和密码错误,会报错:Access denied for user 'root'@'localhost' (using password: YES)
- ③如果用户的账户和密码验证通过,会在mysql自带的权限表中查询当前用户的权限:
mysql中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表,mysql权限表的验证过程为：
1:User表:存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例
Db表:存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库
Tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表
Columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段
Procs_priv表：存放存储过程和函数级别的权限
2:先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在，存在则通过验证。
3:通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推
4:如果在任何一个过程中权限验证不通过,都会报错
1.2:缓存（Query Cache）
mysql的缓存主要的作用是为了提升查询的效率，缓存以key和value的哈希表形式存储，key是具体的sql语句，value是结果的集合。如果无法命中缓存,就继续走到分析器的的一步,如果命中缓存就直接返回给客户端 。不过需要注意的是在mysql的8.0版本以后，缓存被官方删除掉了。之所以删除掉,是因为查询缓存的失效非常频繁,如果在一个写多读少的环境中,缓存会频繁的新增和失效。对于某些更新压力大的数据库来说，查询缓存的命中率会非常低,mysql为了维护缓存可能会出现一定的伸缩性的问题,目前在5.6的版本中已经默认关闭了，比较推荐的一种做法是将缓存放在客户端，性能大概会提升5倍左右
1.3:分析器（Parser）
分析器的主要作用是将客户端发过来的sql语句进行分析，这将包括预处理与解析过程，在这个阶段会解析sql语句的语义，并进行关键词和非关键词进行提取、解析，并组成一个解析树。
具体的关键词包括不限定于以下：select/update/delete/or/in/where/group by/having/count/limit等.如果分析到语法错误，会直接给客户端抛出异常:ERROR:You have an error in your SQL syntax.
比如：select * from user where userId =1234;
在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来,mysql会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验:比如校验当前数据库是否存在user表，同时假如User表中不存在userId这个字段同样会报错：unknown column in field list.
1.4:优化器（Optimizer）
一旦 SQL 语句被成功解析，接下来进入优化器的领域。
优化器的任务是评估该 SQL 语句不同的执行计划，并选择最优的执行计划。它会考虑哪些索引可用，哪种连接方法效率最高，以及如何最小化查询的成本。
能够进入到优化器阶段表示sql是符合mysql的标准语义规则的并且可以执行的，此阶段主要是进行sql语句的优化，会根据执行计划进行最优的选择,匹配合适的索引,选择最佳的执行方案。比如一个典型的例子是这样的：
表T,对A、B、C列建立联合索引，在进行查询的时候，当sql查询到的结果是:select xx where B=x and A=x and C=x.很多人会以为是用不到索引的，但其实会用到,虽然索引必须符合最左原则才能使用,但是本质上,优化器会自动将这条sql优化为:where A=x and B=x and C=X,这种优化会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理,mysql会计算各个执行方法的最佳时间,最终确定一条执行的sql交给最后的执行器
1.5:执行器（Executor）
在执行器的阶段,此时会调用存储引擎的API,API会调用存储引擎，主要有一下存储的引擎，不过常用的还是myisam和innodb:
引擎以前的名字叫做:表处理器(其实这个名字我觉得更能表达它存在的意义)负责对具体的数据文件进行操作,对sql的语义比如select或者update进行分析,执行具体的操作。在执行完以后会将具体的操作记录到binlog中,需要注意的一点是:select不会记录到binlog中,只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式,记录都redolog中
执行的状态
可以通过命令:show full processlist,展示所有的处理进程,主要包含了以下的状态，表示服务器处理客户端的状态，状态包含了从客户端发起请求到后台服务器处理的过程，包括加锁的过程、统计存储引擎的信息，排序数据、搜索中间表、发送数据等。囊括了所有的mysql的所有状态,其中具体的含义如下图：
1.6. 写 undo log（insert、delete、update）
当执行器执行修改数据的操作时，MySQL 的 InnoDB 引擎首先会开启事务，为这些修改生成 undo log（也叫回滚日志）。
回滚日志用于记录修改前的数据，以便在事务回滚时恢复原始数据。如果事务执行失败，MySQL 可以使用undo log 来撤销已经进行的修改。
1.7. 记录缓存（Record Cache），查找索引
MySQL 使用记录缓存来存储从数据表中读取的数据行，这个缓存可以加速对频繁读取的数据的访问，避免了每次都要从磁盘读取的开销。
当数据存在于内存中时，只需要更新内存即可；反之则可能需要从磁盘中读取数据，再更新磁盘数据。
这取决于 MySQL 的索引类型，可分为两种：
- 唯一索引：索引列的值唯一，非主键的唯一索引允许有空值，主键索引不允许空值；
- 普通索引：没有特殊限制，允许重复值和空值；
当 SQL 操作数据到达这一步时，InnoDB 首先会判断数据页是否在内存中：
- 在内存中，判断更新的索引是否是唯一索引。如果是唯一索引，则判断更新后是否破坏数据的一致性，不会的话就直接更新内存中的数据页；如果是非唯一索引，直接更新内存中的数据页。
- 不在内存中：判断更新的索引是否是唯一索引。如果是唯一索引，由于需要保证更新后的唯一性，所以需要立即把数据页从磁盘加载到内存，然后更新数据页；如果是非唯一索引，则将数据更新的操作记录到 change buffer，它将在在空闲时异步更新到磁盘。
1.8. change buffer（insert、delete、update）
change buffer 是 InnoDB 引擎的特性之一，在 MySQL 5.5 之前，change buffer 的主要作用是提高数据插入的性能，又被称作 insert buffer。
我们知道，当非聚集索引插入时，数据会按主键的顺序存放，所以叶子节点可能需要离散地访问数据索引页，每次索引页更新时，都需要刷新磁盘。而每次读写磁盘的时间都会很久，故而导致插入性能较低。
而 insert buffer 开启后，会先判断聚集索引页是否存在于缓冲池中，如果有，直接插入；如果不在，先放入一个插入缓冲区进行排序，再以一定的频率合并（merge）更新索引页。
如图所示，insert buffer 将多次操作合并起来，以减少随机 I/O，减少和磁盘交互的操作，从而提升整体的性能。
MySQL5.5 之后，逐渐加入了数据删除和修改的缓冲类型，统一叫 change buffer。
一言概之，change buffer 主要作用是将二级索引的增删改（IDU）操作缓存下来，以减少随机 I/O，达到操作合并的效果。
由于唯一索引需要立即 IO 到磁盘，以保证数据不冲突，因此唯一索引没有 change buffer 机制。
1.9. 写 redo log
在 SQL 执行的过程中，InnoDB 还会记录所有的数据修改操作到 redo log（重做日志）中。
重做日志是一个循环写入的日志文件，它记录了事务的每个步骤，以确保数据的持久性。如果系统崩溃， InnoDB 可以根据 redo log 来恢复未提交的事务，以保持数据的一致性。
注意，redo log 分为 prepare 和 commit 两个状态。在事务执行的过程中，InnoDB 把数据页的更改写入到 redo log 时，其状态为 prepare 状态。
1.10. 写 binlog，提交事务
除了 redo log，MySQL 还会记录 binlog（二进制日志）。
二进制日志记录了所有执行的 SQL 语句，而不仅仅是数据修改，这对于数据复制和恢复非常重要，因为它可以确保不仅数据的状态被恢复，连同执行的 SQL 操作也能被还原。
当 InnoDB 引擎层写好 redo log 后，会通知 MySQL Server 层已将更新操作已经执行完成。这时，MySQL Server 将执行的 SQL 写入到 binlog，然后通知 InnoDB 将 redo log 置为 commit 状态，事务提交成功。
注意，一个事务提交成功的判断依据在于是否写入到 binlog 日志中。若已写入，即便 MySQL Server 崩溃，之后也可以根据 redo log 和 binlog 进行恢复。
redo log 和 binlog
上面说到了，当事务提交时，分为两个阶段，我们总结一下：
数据更新时，先更新内存中的数据页，将更新操作写入到 redo log 中，此时 redo log 进入 prepare 状态。并通知 MySQL Server 更新执行完了，随时可以提交；
MySQL Server 根据持久化的模式是 STATEMENT 还是 ROW，决定将更新的 SQL 还是数据行写入到 binlog，然后调用 InnoDB 的接口将 redo log 设置为 commit 状态，更新完成。
细心的同学可能会问了，为什么 binlog 只需要提交一次，而 redo 要提交两次？而已经有 redo log了，还需要 binlog 干啥？
要解答这个问题，得从两种日志的本质区别说起。
redo log
用于记录 InnoDB 引擎下事务的日志，支持崩溃数据自修复。
如果只写 binlog，而不写 redo log，当 MySQL 发生故障宕机时，就可能会丢失最近执行的事务数据。
binlog
binlog 记录了 MySQL Server 层对数据库执行的所有更改操作，用于数据归档、数据备份及主从复制等。
如果写了 redo log 直接提交，不经过 prepare 阶段，那么这个过程在发生故障时，如果 MySQL 部署了主从节点，主节点可以根据 redo log 恢复数据，但从节点就无法同步这部分数据。
从上图可以看出，MySQL 主从复制时，主要依赖 Master 节点的 binlog，Slave 节点的 relay-log 和 3 个重要线程。
log dump线程
当从节点连接主节点时，主节点会为其创建一个 log dump 线程，用于读取和发送 binlog 内容。在读取 binlog 中时，log dump 线程会对主节点上的 bin-log 加锁，直到读取完成，锁释放。
主节点会为自己的每一个从节点创建一个 log dump 线程。
I/O线程
当从节点绑定主节点时，会创建一个 I/O 线程用来连接主节点，请求主库中的 binlog。
当主库的 log dump 线程发送的日志被监听到以后，I/O 线程会把日志保存到 relay-log（中继日志）中。
SQL线程
SQL 线程负责监听并读取 relay-log 中的内容，解析成具体的操作并进行重放，使其和主数据库保持一致。每次执行完毕后相关线程会进行休眠，等待下一次唤醒。
从库会在一定时间间隔内探测主库的 bin-log 日志是否发生变化，如有变化，则开启 IO 线程，继续执行上述步骤。
MySQL执行计划
根据MySQL执行计划的输出，分析索引使用情况、扫描的行数可以预估查询效率；进而可以重构SQL语句、调整索引，提升查询效率。
项目开发中，性能是我们比较关注的问题，特别是数据库的性能；作为一个开发，经常和SQL语句打交道，想要写出合格的SQL语句，我们需要了解SQL语句在数据库中是如何扫描表、如何使用索引的；MySQL提供explain/desc命令输出执行计划，我们通过执行计划优化SQL语句。
下面我们以MySQL5.7为例了解一下执行计划：
注：文中涉及到的表结构、sql语句只是为了理解explain/desc执行计划，有不合理之处勿喷
explain/desc 用法
只需要在我们的查询语句前加explain/desc即可
准备数据表
-- 创建user表
create table user(
id int,
name varchar(20),
role_id int,
primary key(id)
)engine=innodb default charset=utf8;
-- 创建role表
create table role(
id int,
name varchar(20),
primary key(id)
)engine=innodb default charset=utf8;查询，执行计划
| 1 |
|
执行计划输出有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra，这些内容有什么意义，下面简单介绍一下
explain/desc 输出详解
一、id ，select 查询序列号
1 id相同，从上往下一次执行；
| 1 2 3 4 |
|
通过left join 和 right join 验证；id一样（注意执行计划的table列），left join 先扫描a表，再扫描b表；rightjoin 先扫描b表，再扫描a表
2 id不同，id越大优先级越高，越先被执行
| 1 |
|
我们编写查询角色为开发的用户；可以知道先查询角色name为开发角色id，查询序列号为2；再根据角色id查询用户，查询序列号为1；
二、select_type，查询语句类型
(1)SIMPLE(简单SELECT,不使用UNION或子查询等)
| 1 |
|
(2)PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
| 1 |
|
(3)UNION(UNION中的第二个或后面的SELECT语句)
| 1 |
|
(4)DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
| 1 2 3 4 5 |
|
(5)UNION RESULT(UNION的结果)
| 1 |
|
(6)SUBQUERY(子查询中的第一个SELECT)
| 1 |
|
(7)DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
| 1 |
|
(8)DERIVED(派生/衍生表的SELECT, FROM子句的子查询)
| 1 |
|
(9) MATERIALIZED(物化子查询) 在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表 ，后续对子查询结果集的访问将直接通过临时表获得。
(10)UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
(11)UNCACHEABLE UNION(UNION查询的结果不能被缓存)
三、table，查询涉及的表或衍生表
table分别user、role表
四、partitions查询涉及到的分区
创建分区表，
| 1 2 3 4 5 6 7 8 |
|
| 1 |
|
查询id大于200（200>100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则
五、type提供了判断查询是否高效的重要依据依据（重点关注）
通过type字段, 我们判断此次查询是全表扫描还是索引扫描等，下面简单介绍一下常用的type；
（1）system: 表中只有一条数据，相当于系统表； 这个类型是特殊的const类型；
（2）const:主键或者唯一索引的常量查询，表格最多只有1行记录符合查询，通常const使用到主键或者唯一索引进行定值查询。
主键
| 1 2 3 4 5 6 7 8 |
|
分别查看按id和按role_id查询的执行计划；发现按主键id查询，执行计划type为const
将主键设置为id和role_id
| 1 2 3 4 5 6 7 8 9 10 |
|
发现只有按照全部主键查询，执行计划type为const
唯一索引
| 1 2 3 4 5 6 7 8 |
|
发现按role_id唯一索引查询；执行计划type为const
普通索引
| 1 2 3 4 5 6 7 |
|
发现按role_id普通索引查询；执行计划type为ref
const用于主键或唯一索引查询；将PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时使用；与索引类型有关。
（3）eq_ref: 除了system和const类型之外,效率最高的连接类型；唯一索引扫描，对于每个索引键，表中只有一条记录与之对应；常用于主键或唯一索引扫描
准备数据
| 1 2 3 4 5 6 7 8 9 10 |
|
执行计划
根据上面的知识；可知id相同，由上至下依次执行，分析结果可知：
先查询t表就是teacher表中name字段为a的记录，由于name字段没有索引，所以全表扫描（type:ALL），一共有3条记录，扫描了3行（rows:3）,1条符合条件（filtered:33.33 1/3）；
再查询tc即teacher_card表使用主键和之前的t.tc_id关联；由于是关联查询，并且是通过唯一索引（主键）进行查询，仅能返回1或0条记录，所以type为eq_ref。
| 1 2 3 4 5 6 |
|
分析结果，将teacher_card的id设置为唯一索引，type为eq_ref；满足仅能返回1或0条记录。
| 1 2 3 4 5 6 |
|
分析结果，将teacher_card的id设置为普通索引，type为ref；不满足仅能返回1或0条记录。
equ_ref用于唯一索引查询，对每个索引键，表中只有一条或零条记录与之匹配。
（4）ref：此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询（换句话说，连接不能基于键值选择单行,可能是多行）。
| 1 2 3 4 5 6 |
|
先查询tc表就是teacher_card表中remark字段为aa的记录，由于remark字段没有索引，所以全表扫描（type:ALL），一共有2条记录，扫描了2行（rows:2）,1条符合条件（filtered:50,1/2）；
tc_id无索引 再查询t即teacher表使用tc_id和之前的tc.id关联；由于是关联查询，不是索引,全表扫描，所以type为ALL。
tc_id有索引再查询t即teacher表使用tc_id和之前的tc.id关联；由于是关联查询，索引扫描，能返回0或1或多条记录，所以type为ref。
（5）range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。
| 1 2 |
|
（6）index: 扫描索引树
如果索引是复合索引，并且复合索引列满足select所需的所有数据，则仅扫描索引树。在这种情况下，Extra为Using index。仅索引扫描通常比ALL索引的大小通常小于表数据更快。
索引列不满足select所需的所有数据，此时需要回表扫描；按索引顺序查找数据行。Uses index没有出现在Extra列中。
| 1 2 3 4 5 6 |
|
查询tc_id，扫描索引树，type为index，Extra为Using index；
按tc_id分组，全表扫描，以按索引顺序查找数据行。
（7）ALL: 全表扫描,没有任何索引可以使用时。这是最差的情况，应该避免。
| 1 2 3 |
|
由于name字段不存在索引，type:ALL全表扫描；可通过对name字段设置索引进行优化。
六、possible_keys：指示MySQL可以从中选择查找此表中的行的索引。
七、key：MySQL查询实际使用到的索引。（重点关注）
| 1 2 3 4 5 6 7 8 |
|
查看执行计划
查询1，查询name为Java或t_id为1的记录；可能用到的索引possible_keys为index_name，index_tid；实际用到的索引key为NULL
查询2，查询name为Java；可能用到的索引possible_keys为index_name；实际用到的索引key为index_name
八、key_len：表示索引中使用的字节数（只计算利用索引作为index key的索引长度，不包括用于group by/order by的索引长度）（重点关注）
- 一般地，key_len 等于索引列类型字节长度，例如int类型为4 bytes，bigint为8 bytes；
- 如果是字符串类型，还需要同时考虑字符集因素，例如utf8字符集1个字符占3个字节，gbk字符集1个字符占2个字节
- 若该列类型定义时允许NULL，其key_len还需要再加 1 bytes
- 若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引也被视为动态列类型），其key_len还需要再加 2 bytes
字符集会影响索引长度、数据的存储空间，为列选择合适的字符集；变长字段需要额外的2个字节，固定长度字段不需要额外的字节。而null都需要1个字节的额外空间，所以以前有个说法：索引字段最好不要为NULL，因为NULL让统计更加复杂，并且需要额外一个字节的存储空间。
| 1 2 3 4 5 6 7 |
|
下面验证一下结论：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
|
按照主键id查询possible_keys为primary，实际用到的索引key为primary，key_len为4；
按照索引role_id查询possible_keys为index_role，实际用到的索引key为index_role，key_len为5；
分析结果：按照role_id比按照id（均为int类型）的key_len大5-4=1，因为role_id可以为null，需要一个标志位；
| 1 2 3 4 |
|
按照name查询possible_keys为index_name，实际用到的索引key为index_name，key_len为32=10*3+2(变长)；
按照alias_name查询possible_keys为index_alias，实际用到的索引key为index_alias，key_len为33=10*3+2(变长)+1(null标志位)；
分析结果：name与remark均为变长且字符集一致，remark可以为null，33-32=1多占一个标志位；
| 1 2 3 4 |
|
按照name查询possible_keys为index_name，实际用到的索引key为index_name，key_len为32=10*3(utf8一个字符3个字节)+2(变长)；
按照remark查询possible_keys为index_remark，实际用到的索引key为index_remark，key_len为22=10*2(gbk一个字符2个字节)+2(变长)；
分析结果：name与remark均为变长但字符集不一致，分别为utf8与gbk；符合公式；
| 1 2 3 4 |
|
按照name查询possible_keys为index_name，实际用到的索引key为index_name，key_len为30；
因为将name修改为char(10) 定长 character set utf8 not null，10*3=30；符合公式
九、ref：显示该表的索引字段关联了哪张表的哪个字段
| 1 |
|
通过执行计划可知，role表执行计划ref为study.user.role_id；说明role.id关联user.role_id；
十、rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好
十一、filtered：返回结果的行数占读取行数的百分比，值越大越好
| 1 2 3 4 5 6 7 8 |
|
根据上面的知识；可知id相同，由上至下依次执行，分析结果可知：
先查询t表就是teacher表中name字段为a的记录，由于name字段没有索引，所以全表扫描（type:ALL），一共有3条记录，扫描了3行（rows:3）,1条符合条件（filtered:33.33 1/3）；
再查询tc即teacher_card表使用主键和之前的t.tc_id关联；扫描索引（type:ref），返回1条记录，最终返回1条记录，（filtered:100 1/1）。
十二、extra：包含不适合在其他列中显示但十分重要的额外信息。（重点关注）
常见的值如下
Using where（使用 where）：这表示 MySQL 在检索行后会再次进行条件过滤，使用 WHERE 子句进行进一步的筛选。这可能出现在列未被索引覆盖，或者 where 筛选条件涉及非索引的前导列或非索引列。
explain select * from t2 where d = "ni"; # 非索引字段查询
explain select d from t2 where b = "ni"; # 未索引覆盖，用联合索引的非前导列查询
Using index（使用索引）：MySQL 使用了覆盖索引来优化查询，只需扫描索引而无需回到数据表中检索行。
explain select b,c from t2 where a = "ni"; # 索引覆盖
Using index condition（使用索引条件）：表示查询在索引上执行了部分条件过滤，通常与索引下推有关。
explain select d from t2 where a = "ni" and b like "s%"; # 使用到索引下推。
Using where; Using index（使用 where；使用索引）：查询的列被索引覆盖，且 where 筛选条件是索引列之一，但不是索引的前导列，或者 where 筛选条件是索引列前导列的一个范围。
explain select a from t2 where b = "ni"; # 索引覆盖，但是不符合最左前缀
explain select b from t2 where a in ('a','d','sd'); # 索引覆盖，但是前导列是个范围
Using join buffer（使用连接缓存）：MySQL 使用了连接缓存。
explain select * from t1 join t2 on t1.id = t2.id where a = 's';
Using temporary（使用临时表）：MySQL 创建了临时表来存储查询结果，通常在排序或分组时发生。
explain select count(*),b from t2 group by b;
Using filesort（使用文件排序）：MySQL 将使用文件排序而不是索引排序，通常发生在无法使用索引进行排序时。
explain select count(*),b from t2 group by b;
聊聊 order by 是怎么实现的？
Using index for group-by（使用索引进行分组）：MySQL 在分组操作中使用了索引。通常发生在分组操作涉及到索引中的所有列时。
Using filesort for group-by（使用文件排序进行分组）：MySQL 在分组操作中使用了文件排序。这通常发生在无法使用索引进行分组操作时。
Range checked for each record（为每条记录检查范围）：表示 MySQL 在使用索引范围查找时，需要对每一条记录进行检查。
Using index for order by（使用索引进行排序）：MySQL 在排序操作中使用了索引。通常发生在排序涉及到索引中的所有列时。
Using filesort for order by（使用文件排序进行排序）：MySQL 在排序操作中使用了文件排序。这通常发生在无法使用索引进行排序时。
Using index for group-by; Using index for order by（在分组和排序中使用索引）：表示 MySQL 在分组和排序操作中都使用了索引。use filesort：MySQL会对数据使用非索引列进行排序，而不是按照索引顺序进行读取；若出现改值，应优化索引
| 1 2 3 4 5 6 7 8 |
|
use temporary：使用临时表保存中间结果，比如，MySQL在对查询结果排序时使用临时表，常见于order by和group by；若出现改值，应优化索引
use index：表示select操作使用了索引覆盖，避免回表访问数据行，效率不错
use where：where子句用于限制哪一行
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
|
分析结果：
当设置first_name为普通索引(单列索引)，按照first_name查询；type:ref、possible_keys:indes_first、key:indes_first、extra:null，用到索引；
当设置first_name，last_name为复合索引(联合索引)，按照first_name查询；type:ref、possible_keys:indes_name、key:indes_name、extra:Using index；type:ref用到索引，因为是复合索引不需要回表扫描，extra:Using index索引覆盖；注意此时key_len为33=10*3(utf8)+2(变长)+1(null标志位)，用到了复合索引的一部分即first_name
当设置first_name，last_name为复合索引(联合索引)，按照last_name查询；type:index、possible_keys:null、key:indes_name、extra:Using where，Using index；type:index而不是ref，扫描索引树，复合索引的最左原则；此时key_len为66=10*3(utf8)+2(变长)+1(null)+10*3(utf8)+2(变长)+1(null标志位)；Using where应where子句进行限制
SQL调优
SQL查询优化的目的
1.减少响应时间： 主要目标是通过减少响应时间来提高性能。用户请求数据和获得响应之间的时间差应最小化，从而获得更好的用户体验。
2.减少 CPU 执行时间： 必须减少查询的 CPU 执行时间，更快的获得结果。
3.提高吞吐量： 应尽量减少获取所有必要数据所需访问的资源数量。
数据库开发规范《Java开发手册》
严格遵守《Java开发手册》中的数据库规约，能解决很多sql性能问题。
如何判断一条 SQL 走没有索引
首先看 key 字段有没有值，有值表示用到了索引树，但是具体是怎么用的，还得看 type 和 extra。
简单说以下几个情况：
情况一：
explain select b from t2 where a in ('a','d','sd');
+----+-------+---------------+----------+--------------------------+
| id | type | possible_keys | key | Extra |
+----+-------+---------------+----------+--------------------------+
| 1 | index | NULL | idx_abc | Using where; Using index |
+----+-------+---------------+----------+--------------------------+
type = index，key = idx_abc，extra = 使用 where；使用 index。这表明查询利用了 idx_abc 的联合索引，但未严格遵守最左前缀匹配，或者虽然遵守了最左前缀，但在 a 字段上进行了范围查询。因此，实际上仍需扫描索引树，效率并不理想。
情况二：
explain select * from t2 where a = 'Paidaxing';
+----+-------+---------------+----------+--------------------------+
| id | type | possible_keys | key | Extra |
+----+-------+---------------+----------+--------------------------+
| 1 | ref | idx_abc | idx_abc | NULL |
+----+-------+---------------+----------+--------------------------+
表示用到了索引进行查询，并且用到的是 idx_abc 这个非唯一索引。
情况三：
explain select * from t2 where f = 'f';
+----+-------+---------------+----------+--------------------------+
| id | type | possible_keys | key | Extra |
+----+-------+---------------+----------+--------------------------+
| 1 | const | f | f | NULL |
+----+-------+---------------+----------+--------------------------+
表示用到了索引进行查询，并且用到的是 f 这个唯一索引。
情况四：
explain select b,c from t2 where a = 'Paidaxing';
+----+-------+---------------+----------+--------------------------+
| id | type | possible_keys | key | Extra |
+----+-------+---------------+----------+--------------------------+
| 1 | ref | idx_abc | idx_abc | Using index |
+----+-------+---------------+----------+--------------------------+
表示用到了索引进行查询，并且用到了 idx_abc 这个索引，而且查询用到了覆盖索引，不需要回表。
情况五：
explain select b,c from t2 where d = 'Paidaxing';
+----+-------+---------------+----------+--------------------------+
| id | type | possible_keys | key | Extra |
+----+-------+---------------+----------+--------------------------+
| 1 | ALL | NULL | NULL | Using where |
+----+-------+---------------+----------+--------------------------+
表示没有用到索引。
常见的SQL 查询优化技巧
1 使用SELECT 字段名，而不是使用 SELECT *
仅从表中获取必要的数据，而不是获取所有数据。比如：
SELECT * FROM Business
更高效的查询写法是：
SELECT name , age , gender FROM Business
这个查询要简单得多，只从表中提取所需的详细信息。
2 尽量避免在 SELECT 中使用 DISTINCT
SELECT DISTINCT 是从数据库中删除重复项的简单方法，也可以通过和 GROUP BY 子句来生成不同的结果，该子句对查询中的所有字段进行分组。
然而，要做到这一点需要消耗大量的处理能力。因此，在 SELECT 查询中避免DISTINCT。
3 正确的使用索引
正确的使用索引可以减少常用语句的执行时间。
比如：
CREATE INDEX index_optimizer ON Business(id);
4 要检查记录是否存在，使用 EXISTS而不是 COUNT
EXISTS() 和 COUNT() 方法都可以用来检查表中记录条目的存在。
EXISTS() 方法更有效，因为一旦找到表中记录的第一个条目，它就会退出处理。
COUNT() 方法将扫描整个表以返回表中与所提供的约束匹配的记录数。
比如：
SELECT count(id) FROM Business
更有效的写法是：
EXISTS (SELECT (id) FROM Business)
5 使用limit限制结果集大小
检索的数据越少，查询运行的速度就越快。
6 尽量使用 WHERE 而不是 HAVING
HAVING 子句在选择所有行后过滤行。
HAVING 语句在 SQL 操作中确定顺序在 WHERE 语句之后。因此，执行 WHERE 查询会更快。
比如：
SELECT c.ID, c.CompanyName, b.CreatedDate FROM Business b
JOIN Company c ON b.CompanyID = c.ID
GROUP BY c.ID, c.CompanyName, b.CreatedDate
HAVING b.CreatedDate BETWEEN ‘2020-01-01’ AND ‘2020-12-31’
更有效的写法：
SELECT c.ID, c.CompanyName, b.CreatedDate FROM Business b
JOIN Company c ON b.CompanyID = c.ID
WHERE b.CreatedDate BETWEEN ‘2020-01-01’ AND ‘2020-12-31’
GROUP BY c.ID, c.CompanyName, b.CreatedDate
7 忽略链接子查询
链接子查询取决于来自父级或外部源的查询。它是逐行运行的，因此平均循环速度受到很大影响。
原理：在某些情况下，JOIN操作比子查询更高效，因为JOIN允许数据库优化器更有效地执行查询计划。
建议：当可能时，使用JOIN代替子查询。
SQL例子：
-- 使用JOIN
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;
-- 替代的子查询版本
-- SELECT name, (SELECT department_name FROM departments WHERE id = employees.department_id) AS department_name
-- FROM employees;比如：
SELECT b.Name, b.Phone, b.Address, b.Zip, (SELECT CompanyName FROM Company WHERE ID = b.CompanyID) AS CompanyName FROM Business b
对于外部查询返回的每一行，每次都会运行内部查询。
或者，可以使用 JOIN 来解决 SQL 数据库优化的这些问题。
SELECT b.Name, b.Phone, b.Address, b.Zip, c. CompanyName FROM Business b
Join Company c ON b.CompanyID = c.ID8. 优化WHERE子句
原理：减少返回结果集的大小可以加快查询速度。
建议：在WHERE子句中过滤掉尽可能多的行。
SQL例子：
-- 假设有大量的数据，但只对特定条件的记录感兴趣
SELECT * FROM orders WHERE status = 'shipped' AND order_date > '2023-01-01';
9. 使用合适的聚合策略
原理：合理使用GROUP BY和HAVING可以减少数据处理的复杂性。
建议：仅在必要时使用GROUP BY，并考虑使用HAVING代替WHERE对聚合结果进行过滤。
SQL例子：
-- 对订单按状态分组，并筛选总金额超过一定值的组
SELECT status, COUNT(*), SUM(amount)
FROM orders
GROUP BY status
HAVING SUM(amount) > 1000;
10. 避免在WHERE子句中使用函数
原理：在WHERE子句中对列使用函数会阻止索引的使用。
建议：尽可能避免在WHERE子句中对列使用函数。
SQL例子：
-- 不推荐（可能无法利用索引）
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
-- 推荐
SELECT * FROM employees WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';
11. 使用EXPLAIN分析查询
原理：了解查询的执行计划和性能瓶颈。
建议：使用EXPLAIN或类似工具分析查询，并根据结果调整索引或查询结构。
SQL例子：
-- 大多数数据库管理系统都支持EXPLAIN命令
EXPLAIN SELECT * FROM employees WHERE department_id = 10;
12. 使用窗口函数代替子查询（Oracle）
原理：窗口函数（如ROW_NUMBER()、RANK()等）可以在不改变结果集行数的情况下为每行提供额外的计算列，这通常比使用子查询更高效。
建议：当需要为结果集中的每行添加基于整个结果集的额外信息时，考虑使用窗口函数。
例子：
-- 优化前（使用子查询计算排名）
SELECT id, name,
(SELECT COUNT(*) + 1
FROM users u2
WHERE u2.score > u.score) AS rank
FROM users u;
-- 优化后（使用窗口函数计算排名）
SELECT id, name,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM users;
13. 使用适当的数据类型
原理：选择合适的数据类型可以减少存储空间和查询时间。
建议：避免使用过大的数据类型，如使用INT代替VARCHAR存储数字。
SQL例子：
-- 创建表时选择合适的数据类型
CREATE TABLE sales (
id INT AUTO_INCREMENT,
amount DECIMAL(10, 2),
PRIMARY KEY (id)
);
14. 优化分页查询
原理：当使用LIMIT和OFFSET进行分页时，随着页码的增加，查询性能会逐渐下降，因为数据库需要扫描越来越多的行来找到所需的起始点。
建议：使用基于索引的查询来优化分页，特别是当表很大时。例如，可以记录上一页最后一条记录的某个唯一标识符（如ID），并使用它作为下一页查询的起点。
例子：
-- 优化前（随着页码增加性能下降）
SELECT * FROM users LIMIT 10 OFFSET 100;
-- 优化后（使用上一页的最后一条记录的ID）
SELECT * FROM users WHERE id > LAST_SEEN_ID ORDER BY id LIMIT 10;15. 优化汇总
1. 清表要用 truncate：先删再重建表；delete 只是一条一条标记数据为已删除，实际未释放空间。
2. 避免 select * ，浪费内存资源、网络 I/O 资源，不会走覆盖索引，降低性能。
3. 用 union all 代替 union（会进行去重和排序）：数据库资源比内存资源珍贵，业务有去重和排序的要求可以在业务代码中进行。
4. 小表驱动大表：例如 in（左边是大表，右边是小表+索引） 和 exists（左边是小表+索引，右边是大表） 的使用。in 是先执行右边，再执行左边；exists 是先执行左边，再执行右边。
5. 批量操作：例如 insert into table(id,name,sex) values(1,'a',0),(2,'b',1),(3,'c',1)，但也要避免一次插入过多，记录较多时，可以分批 例500。
6. 巧用 limit：查询结果较多时 limit 1。
7. in 括号内值太多：临时处理可以分批多线程去查询，返回再合并。
8. 高效分页：limit 3, 30 分页，数据量大了会导致 limit 1000000,30，改为 where id > 1000000 limit 30；也可以使用 between 1000000 and 1000030，但必须使用在唯一索引上（每页大小不一致的问题）。
9. 用连接查询 join 代替子查询（select 嵌套）。
10. join 表避免过多，根据业务，不能一概而论，尽量越少越好。
11. join 的使用（小表驱动大表）：
inner join 是两个表做交集。数据库会自动选择较小的表来驱动大表，性能上没有多大的差别。能用 inner join 时，应尽量避免使用 left/right join
left join（左边小表，右边大表） 求两个表的交集，外加左表剩下的数据。数据库会使用 left join 左边的表去驱动右边的表，如果左边的表数据量较大，就会出现性能问题。
right join（左边大表，右边小表）求两个表的交集，外加右表剩下的数据。数据库会使用 right join 右边的表去驱动左边的表，如果右边的表数据量较大，就会出现性能问题
12. 索引数量：
增加查询效率。数据库使用 B+ 树来保存索引，在 insert、update、delete 时都需要更新 B+ 数上的索引，消耗存储空间和性能。
阿里巴巴开发手册指出单表的索引数量尽量控制在5个以内，并且单个索引（联合索引）中的字段数不超过5个。如果数据量、并发不大，超过 5 个也没有很大问题。
13. 高并发的索引优化：
使用联合索引代替单索引，删除单索引。还可以把部分查询功能迁移到 hbase、ES 中，业务表中只需要保留几个关键索引。
14. 选择合理的字段类型：小字段的搜索效率较高些
char 固定长度，会浪费存储空间
varchar：长度可变，存储空间根据实际数据的长度调整
原则：
能用数字类型就不用字符串：数字比字符串效率快
尽可能使用小的类型：bit 布尔，tinyint 枚举等
固定长度用 char，变长用 varchar
金额字段用 decimal 避免精度丢失
15. 提升 group by 的效率：去除重复并分组，通常跟 having 一起使用，表示分组后再过滤数据，分组耗时，可以先过滤再分组
16. 索引优化：效率高，所以被认为 SQL 优化首选
第一步，检查 SQL 有没有走索引，在 SQL 前面加上 explain 查看，其中 key 就是索引名称
第二步，排查没有建索引还是索引失效
有些情况，入参不同，最后数据库走的索引不同，必要时使用 force index 强制走哪个索引SQL优化一般步骤
-
通过慢查日志等定位那些执行效率较低的SQL语句
-
explain 分析SQL的执行计划
需要重点关注type、rows、filtered、extra。
type由上至下，效率越来越高
Extra
-
Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
-
Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化
-
Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。
-
Using index condition：MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
-
ALL 全表扫描
-
index 索引全扫描
-
range 索引范围扫描，常用语<,<=,>=,between,in等操作
-
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
-
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
-
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
-
null MySQL不访问任何表或索引，直接返回结果 虽然上至下，效率越来越高，但是根据cost模型，假设有两个索引idx1(a, b, c),idx2(a, c)，SQL为"select * from t where a = 1 and b in (1, 2) order by c";如果走idx1，那么是type为range，如果走idx2，那么type是ref；当需要扫描的行数，使用idx2大约是idx1的5倍以上时，会用idx1，否则会用idx2
-
-
show profile 分析
了解SQL执行的线程的状态及消耗的时间。默认是关闭的，开启语句“set profiling = 1;”
SHOW PROFILES ; SHOW PROFILE FOR QUERY #{id}; -
trace
trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么优惠券选择A执行计划而不选择B执行计划。
set optimizer_trace="enabled=on"; set optimizer_trace_max_mem_size=1000000; select * from information_schema.optimizer_trace; -
确定问题并采用相应的措施
-
优化索引
-
优化SQL语句：修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤
-
改用其他实现方式：ES、数仓等
-
数据碎片处理
-
场景分析
案例1、最左匹配
索引
KEY `idx_shopid_orderno` (`shop_id`,`order_no`)
SQL语句
select * from _t where orderno=''
查询匹配从左往右匹配，要使用order_no走索引，必须查询条件携带shop_id或者索引(shop_id,order_no)调换前后顺序
案例2、隐式转换
索引
KEY `idx_mobile` (`mobile`)
SQL语句
select * from _user where mobile=12345678901
隐式转换相当于在索引上做运算，会让索引失效。mobile是字符类型，使用了数字，应该使用字符串匹配，否则MySQL会用到隐式替换，导致索引失效。
案例3、大分页
索引
KEY `idx_a_b_c` (`a`, `b`, `c`)
SQL语句
select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;
对于大分页的场景，可以优先让产品优化需求，如果没有优化的，有如下两种优化方式， 一种是把上一次的最后一条数据，也即上面的c传过来，然后做“c < xxx”处理，但是这种一般需要改接口协议，并不一定可行。另一种是采用延迟关联的方式进行处理，减少SQL回表，但是要记得索引需要完全覆盖才有效果，SQL改动如下
select t1.* from _t t1, (select id from _t where a = 1 and b = 2 order by c desc limit 10000, 10) t2 where t1.id = t2.id;
案例4、in + order by
索引
KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)
SQL语句
select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10
in查询在MySQL底层是通过n*m的方式去搜索，类似union，但是效率比union高。in查询在进行cost代价计算时（代价 = 元组数 * IO平均值），是通过将in包含的数值，一条条去查询获取元组数的，因此这个计算过程会比较的慢，所以MySQL设置了个临界值(eq_range_index_dive_limit)，5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。默认是200，即in条件超过了200个数据，会导致in的代价计算存在问题，可能会导致Mysql选择的索引不准确。
处理方式，可以(order_status, created_at)互换前后顺序，并且调整SQL为延迟关联。
案例5、范围查询阻断，后续字段不能走索引
索引
KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)
SQL语句
select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10
范围查询还有“IN、between”
案例6、不等于、不包含不能用到索引的快速搜索。（可以用到ICP）
select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1
在索引上，避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等
案例7、优化器选择不使用索引的情况
如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。
select * from _order where order_status = 1
查询出所有未支付的订单，一般这种订单是很少的，即使建了索引，也没法使用索引。
案例8、复杂查询
select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';
select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;
如果是统计某些数据，可能改用数仓进行解决；如果是业务上就有那么复杂的查询，可能就不建议继续走SQL了，而是采用其他的方式进行解决，比如使用ES等进行解决。
案例9、asc和desc混用
select * from _t where a=1 order by b desc, c asc
desc 和asc混用时会导致索引失效
案例10、大数据
对于推送业务的数据存储，可能数据量会很大，如果在方案的选择上，最终选择存储在MySQL上，并且做7天等有效期的保存。那么需要注意，频繁的清理数据，会照成数据碎片，需要联系DBA进行数据碎片处理。
1.慢查询
问题原因： 通常是由于查询中涉及大量数据、缺乏索引或者SQL语句本身写得不够有效率所导致。
优化策略：
-
添加索引以加速检索。
-
优化
SQL查询语句，使用更有效率的方法。 -
限制返回的行数。
示例：假设有一个表orders包含订单信息，现在要查询特定日期范围内的订单数量：
-- 原始查询
SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-01';
-- 优化后的查询
CREATE INDEX idx_order_date ON orders (order_date);
SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-01';
2.连接查询性能问题
问题原因：连接查询（特别是跨多个表的连接）可能会导致性能下降，尤其是在没有合适索引的情况下。
优化策略：
-
确保连接字段上存在索引。
-
尽可能使用
INNER JOIN替代其他类型的连接，因为INNER JOIN的性能通常更好。
示例：假设有两个表orders和customers，需要检索订单及其关联的客户信息：
-- 原始查询
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- 优化后的查询
CREATE INDEX idx_customer_id ON orders (customer_id);
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;
3.子查询性能问题
问题原因：子查询可能会执行多次，导致性能下降。
优化策略：
-
将子查询转换为连接查询或者联合查询。
-
优化子查询本身，确保其尽可能高效。
示例：假设需要查询销售额超过平均销售额的产品列表：
-- 原始查询
SELECT product_id, product_name
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- 优化后的查询
SELECT p.product_id, p.product_name
FROM products p
JOIN (SELECT AVG(price) AS avg_price FROM products) AS avg_table
WHERE p.price > avg_table.avg_price;
4.过度使用通配符%的 LIKE 查询
问题原因：以%开头的 LIKE 查询会导致索引失效，因此性能较差。
优化策略：
-
尽可能避免在
LIKE查询中使用%开头的通配符。 -
如果不可避免，考虑全文搜索或者其他更适合的索引方法。
示例：假设需要查询以"abc"开头的产品名称：
-- 原始查询
SELECT * FROM products WHERE product_name LIKE 'abc%';
-- 优化后的查询
SELECT * FROM products WHERE product_name >= 'abc' AND product_name < 'abd';
5.大批量插入或更新
问题原因：大量数据的插入或更新可能会导致性能下降，特别是在事务中进行时。
优化策略：
-
使用批量插入或更新操作，减少事务的开销。
-
禁用或延迟索引更新，以减少插入或更新操作的负担。
示例：假设需要批量插入大量订单信息：
-- 原始插入操作
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15');
INSERT INTO orders (order_id, customer_id, order_date) VALUES (2, 1002, '2024-03-15');
...
-- 优化后的批量插入操作
INSERT INTO orders (order_id, customer_id, order_date) VALUES
(1, 1001, '2024-03-15'), (2, 1002, '2024-03-15'), ...;
6.频繁的重复查询
问题原因：重复执行相同的查询会造成资源浪费，尤其是在查询结果较大或复杂时。
优化策略：
-
使用缓存技术，如数据库查询缓存或应用程序级别的缓存，避免重复查询相同的数据。
-
考虑使用预编译语句或存储过程，以减少解析和编译的开销。
示例：假设需要频繁查询某产品的库存数量：
-- 原始查询
SELECT stock_quantity FROM products WHERE product_id = 123;
-- 优化后的查询（使用缓存）
SELECT stock_quantity FROM cached_stock WHERE product_id = 123;
7.过度使用分组和聚合函数
问题原因：对大数据集进行分组和聚合操作可能会导致性能下降，尤其是在没有适当索引或者内存不足的情况下。
优化策略：
-
确保使用合适的索引以加速分组和聚合操作。
-
考虑使用汇总表（
Summary Tables）或者其他预计算技术，避免在运行时进行大量聚合计算。
示例：假设需要计算每个产品的平均销售额：
-- 原始查询
SELECT product_id, AVG(amount) AS avg_amount
FROM sales
GROUP BY product_id;
-- 优化后的查询（使用汇总表）
SELECT product_id, avg_amount
FROM product_sales_summary;
8.大量重复数据的查询
问题原因：当查询中存在大量重复的数据时，可能会导致不必要的资源消耗，降低查询效率。
优化策略：
-
使用 DISTINCT 关键字去除重复数据，但要注意使用得当，避免过多的内存消耗。
-
考虑是否有更好的数据模型或者查询方式来避免重复数据的产生。
示例：假设需要查询订单表中不同客户的数量：
-- 原始查询
SELECT COUNT(customer_id) FROM orders;
-- 优化后的查询（使用DISTINCT）
SELECT COUNT(DISTINCT customer_id) FROM orders;
9.过度使用 OR 条件的查询
问题原因： 当查询中存在多个OR条件时，数据库可能无法有效使用索引，导致全表扫描，降低性能。
优化策略：
-
将
OR条件转换为使用IN或者UNION操作，以提高查询效率。 -
确保在
OR条件中的每个字段都有索引，以帮助数据库优化查询计划。
示例：假设需要查询销售订单中某个客户或者某个产品的订单数量：
-- 原始查询
SELECT COUNT(*) FROM orders WHERE customer_id = 1001 OR product_id = 123;
-- 优化后的查询（使用UNION）
SELECT COUNT(*) FROM (
SELECT * FROM orders WHERE customer_id = 1001
UNION
SELECT * FROM orders WHERE product_id = 123
) AS combined_orders;
10.大型数据分页查询
问题原因：当需要获取大型数据集的分页结果时，传统的LIMIT OFFSET方法可能导致性能下降，特别是在偏移量较大时。
优化策略：
-
使用基于游标的分页（
cursor-based pagination）来避免OFFSET的性能问题。 -
考虑在应用层面或者缓存层面进行分页处理，减轻数据库的负担。
示例：假设需要获取订单表中的第 1001 到第 1050 行的数据：
-- 原始分页查询
SELECT * FROM orders LIMIT 50 OFFSET 1000;
-- 优化后的查询（使用游标）
SELECT * FROM orders WHERE order_id > (SELECT order_id FROM orders ORDER BY order_id LIMIT 1 OFFSET 1000) LIMIT 50;
11.使用不必要的列
问题原因：当查询中包含不必要的列时，数据库引擎可能会浪费时间和资源来获取这些不需要的数据。
优化策略：
-
只选择查询中需要的列，避免选择不必要的列。
-
如果存在大对象（如
BLOB或CLOB），尽量避免在查询中选择这些对象，除非确实需要。
示例：假设需要查询订单表中订单号和订单日期：
-- 原始查询
SELECT * FROM orders;
-- 优化后的查询（只选择需要的列）
SELECT order_id, order_date FROM orders;
12.频繁更新的表
问题原因：当表上有大量更新操作时，可能会导致表锁定和性能下降。
优化策略：
-
尽可能减少更新操作，考虑是否可以将数据操作转移到非热点表上。
-
将更新操作批量处理，以减少事务开销。
示例：假设有一个用户登录日志表，需要更新用户最后一次登录时间：
-- 原始更新操作
UPDATE login_logs SET last_login = NOW() WHERE user_id = 123;
-- 优化后的更新操作（批量处理）
UPDATE login_logs
SET last_login = NOW()
WHERE user_id IN (123, 124, 125);
13.未使用索引的外键约束
问题原因：虽然外键约束可以确保数据完整性，但如果没有为外键字段创建索引，可能会导致性能下降。
优化策略：
-
确保为外键字段创建索引，以加速相关的数据操作。
-
在数据库设计阶段考虑外键索引的需要性。
示例：假设有一个订单表，包含客户 ID 作为外键，需要为外键字段创建索引：
-- 创建外键约束
ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(id);
-- 为外键字段创建索引
CREATE INDEX idx_customer_id ON orders (customer_id);
14.大型查询的分批处理
问题原因：当查询涉及大量数据时，一次性处理可能会导致内存和 CPU 资源过度消耗。
优化策略：
-
将大型查询分成多个较小的批次进行处理，以减少每个批次的负载。
-
使用游标或者分页技术来处理大型数据集。
示例：假设需要对大型用户表进行逐行处理：
-- 原始查询
SELECT * FROM users;
-- 优化后的查询（使用游标）
DECLARE cursor_name CURSOR FOR SELECT * FROM users;
OPEN cursor_name;
FETCH NEXT FROM cursor_name;
-- 逐行处理数据...
CLOSE cursor_name;
15.未使用存储过程的重复逻辑
问题原因：当有重复的业务逻辑需要在多个地方执行时，未使用存储过程可能导致代码重复和维护困难。
优化策略：
-
将重复的逻辑封装到存储过程中，以便在需要时可以重复使用。
-
使用存储过程可以减少网络通信开销，并且可以通过权限控制提高安全性。
示例：假设需要在多个地方计算订单总额：
-- 原始逻辑
SELECT SUM(total_amount) FROM orders WHERE customer_id = 123;
-- 优化后的存储过程
CREATE PROCEDURE CalculateOrderTotal(IN customer_id INT)
BEGIN
SELECT SUM(total_amount) FROM orders WHERE customer_id = customer_id;
END;
16.未使用合适的数据类型
问题原因：选择不恰当的数据类型可能会导致存储空间浪费和性能下降。
优化策略：
-
使用最合适的数据类型来存储数据，避免过大或过小的数据类型。
-
尽量使用整型来存储整数数据，使用定点数或浮点数来存储小数数据。
示例：假设需要存储订单数量信息：
-- 原始表定义
CREATE TABLE orders (order_id INT, quantity VARCHAR(10));
-- 优化后的表定义
CREATE TABLE orders (order_id INT, quantity INT);
17.大量写操作导致的锁竞争
问题原因：当有大量写操作时，可能会导致锁竞争，降低数据库的并发性能。
优化策略：
-
尽量减少长时间持有锁的操作，例如尽快提交或回滚事务。
-
考虑分批处理写操作，以减少锁竞争的影响。
示例：假设有大量用户同时进行下单操作：
-- 原始下单操作
BEGIN TRANSACTION;
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15');
-- 更多写操作...
COMMIT;
-- 优化后的下单操作（分批处理）
BEGIN TRANSACTION;
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15');
COMMIT;
-- 分批处理更多下单操作...
18.频繁使用数据库函数和表达式
问题原因：频繁使用复杂的数据库函数和表达式可能会导致计算开销增加，影响查询性能。
优化策略：
-
尽量减少使用数据库函数和表达式，尽可能在应用层或缓存层进行计算。
-
对于经常使用的表达式，考虑将其结果存储在列中，以减少重复计算。
示例：假设需要计算订单总额并加上税率：
-- 原始查询
SELECT SUM(total_amount * (1 + tax_rate)) FROM orders;
-- 优化后的查询（使用预计算结果）
ALTER TABLE orders ADD COLUMN total_with_tax DECIMAL(10, 2);
UPDATE orders SET total_with_tax = total_amount * (1 + tax_rate);
SELECT SUM(total_with_tax) FROM orders;
19.未使用合适的索引策略
问题原因：选择不当的索引策略可能会导致索引失效或不必要的索引开销。
优化策略：
-
定期检查和优化索引，确保其适应实际查询需求。
-
根据查询模式选择合适的索引类型，例如
B-Tree索引、哈希索引等。
示例：假设有一个订单表，经常需要根据订单日期范围进行查询：
-- 原始索引策略
CREATE INDEX idx_order_date ON orders (order_date);
-- 优化后的索引策略（创建覆盖索引）
CREATE INDEX idx_order_date_amount ON orders (order_date, total_amount);
20.大量数据的联合操作
问题原因：在执行大量数据的联合操作（UNION、UNION ALL）时，数据库可能需要对结果集进行排序和去重，导致性能下降。
优化策略：
-
尽量避免使用联合操作，如果必须使用，确保各个子查询的结果集尽量小且已经过滤，以减少排序和去重的开销。
-
考虑使用其他方法代替联合操作，如
JOIN、子查询等。
示例：假设需要获取两个表的不重复记录：
-- 原始查询
SELECT * FROM table1
UNION
SELECT * FROM table2;
-- 优化后的查询（使用JOIN）
SELECT DISTINCT t1.* FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id;
21.数据分布不均匀的索引
问题原因：如果索引数据分布不均匀，可能会导致部分索引页过度填充，而另一部分过度稀疏，影响查询性能。
优化策略：
-
定期重新组织索引或重新构建索引，以平衡数据分布。
-
根据具体情况，考虑使用分区表或者其他分布均匀的数据结构。
示例：假设有一个用户表，根据用户名进行查询，但是数据分布不均匀：
-- 原始索引
CREATE INDEX idx_username ON users (username);
-- 优化后的索引（重新组织）
ALTER INDEX idx_username REORGANIZE;
22.过度使用子查询
问题原因：过度使用子查询可能会导致查询执行效率低下，尤其是嵌套子查询的情况下。
优化策略：
-
将子查询转换为
JOIN操作，以减少查询的嵌套层次。 -
尽量避免在
SELECT子句中使用子查询。
示例：假设需要查询每个用户的最新订单：
-- 原始查询
SELECT user_id, (SELECT MAX(order_date) FROM orders WHERE orders.user_id = users.user_id) AS latest_order_date FROM users;
-- 优化后的查询（使用JOIN）
SELECT users.user_id, MAX(orders.order_date) AS latest_order_date
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id
GROUP BY users.user_id;
23.未使用批量操作
问题原因：频繁执行单条数据操作可能会导致数据库连接开销增加，从而降低性能。
优化策略：
-
尽量使用批量操作来替代单条数据操作，以减少数据库连接开销。
-
对于大批量数据的操作，可以考虑使用批量导入或批量更新的方式，减少交互次数。
示例：假设需要更新大量订单的状态：
-- 原始更新操作
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE orders SET status = 'shipped' WHERE order_id = 2;
-- 更多单条更新操作...
-- 优化后的批量更新操作
UPDATE orders SET status = 'shipped' WHERE order_id IN (1, 2, ...);
24.过度使用内存表
问题原因：虽然内存表在某些情况下可以提高查询速度，但过度使用内存表可能会导致内存消耗过大，甚至影响系统的稳定性。
优化策略：
-
评估内存表的使用情况，确保仅在适当的情况下使用内存表。
-
对于大数据量或需要持久化存储的数据，避免使用内存表，而是选择合适的磁盘存储方式。
示例：假设需要对查询结果进行排序：
-- 原始查询（使用内存表进行排序）
SELECT * FROM orders ORDER BY order_date;
-- 优化后的查询（使用磁盘排序）
SELECT * FROM orders ORDER BY order_date;
25.缺乏定期统计和优化
问题原因：未定期统计表的数据分布和索引使用情况，未进行数据库性能优化可能导致查询性能逐渐下降。
优化策略：
-
定期执行统计分析，评估表的数据分布和索引使用情况。
-
根据统计结果，定期优化数据库的索引、表结构或者查询语句。
示例：假设需要定期分析数据库性能：
-- 定期执行统计分析
ANALYZE TABLE orders;
-- 定期优化数据库索引
OPTIMIZE TABLE orders;
26.未使用合适的数据库引擎
问题原因：选择不合适的数据库引擎可能导致性能下降，无法充分发挥数据库的优势。
优化策略：
-
根据具体的业务需求和性能要求选择合适的数据库引擎，如
InnoDB、MyISAM等。 -
评估和比较不同数据库引擎的特性，选择最适合当前业务场景的引擎。
示例：假设需要存储事务性数据和执行频繁的读写操作：
-- 使用InnoDB引擎存储事务性数据
CREATE TABLE orders (order_id INT, customer_id INT, order_date DATE) ENGINE=InnoDB;
-- 使用MyISAM引擎存储非事务性数据
CREATE TABLE logs (log_id INT, log_message TEXT) ENGINE=MyISAM;
27.使用强制类型转换
问题原因：频繁使用强制类型转换可能会导致查询执行效率低下，尤其是在大数据量的情况下。
优化策略：
-
尽量避免在查询中使用强制类型转换，而是尽量保持数据类型一致性。
-
如果必须进行类型转换，尽可能在应用程序层面完成，而不是在数据库层面。
示例：假设需要将字符串类型的日期转换为日期类型：
-- 原始查询（使用强制类型转换）
SELECT * FROM orders WHERE DATE(order_date) = '2024-03-15';
-- 优化后的查询（避免强制类型转换）
SELECT * FROM orders WHERE order_date = '2024-03-15';
28.未优化的长事务
问题原因：长时间运行的事务可能会阻塞其他事务，影响数据库的并发性能。
优化策略：
-
尽量减少事务的持续时间，尽早提交或者回滚事务。
-
如果事务必须长时间运行，考虑是否可以使用行级锁而不是表级锁。
示例：假设需要处理大量订单数据：
-- 原始事务
BEGIN TRANSACTION;
-- 处理订单数据...
COMMIT;
-- 优化后的事务（分批提交）
BEGIN TRANSACTION;
-- 处理部分订单数据...
COMMIT;
-- 继续处理剩余的订单数据...
29.未优化的存储过程
问题原因：存储过程中可能存在未优化的SQL语句，导致存储过程执行效率低下。
优化策略：
-
定期评估和优化存储过程中的
SQL语句，确保其执行效率。 -
使用合适的索引、避免不必要的循环或递归等方式优化存储过程。
示例：假设存在一个存储过程用于计算订单总额：
-- 原始存储过程
CREATE PROCEDURE CalculateOrderTotal(IN order_id INT)
BEGIN
DECLARE total DECIMAL(10, 2);
SELECT SUM(amount) INTO total FROM order_details WHERE order_id = order_id;
SELECT total;
END;
-- 优化后的存储过程（使用索引）
CREATE PROCEDURE CalculateOrderTotal(IN order_id INT)
BEGIN
DECLARE total DECIMAL(10, 2);
SELECT SUM(amount) INTO total FROM order_details WHERE order_id = order_id;
SELECT total;
END;
30.未考虑 DB 服务器配置和硬件资源
问题原因：数据库服务器配置不合理或硬件资源不足可能会导致数据库性能低下。
优化策略：
-
根据数据库负载情况和性能需求合理配置数据库服务器的硬件资源，包括
CPU、内存、磁盘等。 -
定期监控数据库服务器的性能指标，进行性能调优和升级。
示例：假设数据库服务器负载过高，需要升级硬件资源：
-- 原始查询数据库服务器性能指标
SHOW STATUS;
-- 优化后的升级硬件资源
增加CPU核心数、内存容量或者使用更快的磁盘。
922
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
