MySQL 内部结构与执行计划1. MySQL 内部结构总体来说MySQL 分为Server 层和存储引擎层。索引下推数据的筛选从Server层下推到存储引擎层主要发生在联合索引上当前面的的字段发生索引失效如果没有索引下推那直接进行回表最后在Server层进行数据筛选。如果有索引下推那么还会继续根据后续字段进行筛选也就是在存储引擎层筛选。减少回表次数提升查询速度。1.1 Server层总体来说整个mysql分为Server层和存储引擎层。Server层主要包含连接器查询缓存解析器预处理器优化器执行器...等其中查询缓存在mysql8完全剔除。存储引擎主要包括多种存储引擎1.1.1连接器向mysql发送sql语句时首先我们得客户端要先与mysql连接器创建连接完成TCP握手。终端在进入这个路径输入mysql -u root -p 并输入你的密码。此时我们已经和mysql创建了一个连接输入show processlist查看MySQL服务被多少个客户端连接。最大连接数量1511.1.1.1权限当我们在mysql用户密码认证成功后连接器上权限表会查询该用户所拥有的权限在此之后该用户的权限都依赖于初始读到的权限信息。即使中途权限修改。那么这里面发生了什么事情呢我们的连接方式有两种一种是长连接一种是短连接。他们的区别在于请求完是否会释放连接。前者客户端与用户端连接后一直不关闭后者每次请求完都会关闭。当然这会造成巨大的性能开销所以说在高并发的情况下短连接并不是最佳之策还需要使用我们的长连接但它也并不是完美的长连接的堆积会造成我们MySQL占用内存太大。解决策略1 定期断开长连接2 客户端主动重置连接其实当连接器验证我们账户密码正确时连接器就会获取当前用户得权限然后保存起来。后续得任何操作都会基于我们连接一开始保存的权限信息进行权限分配的判断。也就是说即使中途我们修改了权限此时的任何权限判断也是基于连接一开始保存的为准。s1.1.2 解析器作用将 SQL 解析为 MySQL 能理解的结构。步骤词法分析识别 SQL 中的关键字、表名、字段名等。语法分析检查 SQL 是否符合 MySQL 语法规则。1.1.3 预处理器检查表、字段是否存在。将*展开成实际字段列表。1.1.4 优化器确定 SQL 的执行计划例如使用哪一个索引、表的连接顺序等。1.1.5 执行器根据执行计划从存储引擎中读取数据。如果是全表扫描会调用存储引擎的接口循环取数据。1.2 存储引擎MySQL 数据是存储在聚簇索引上的以 InnoDB 为例。聚簇索引的主键选择规则如果表有主键PRIMARY KEY则使用它作为聚簇索引键。如果没有主键则选择第一个非空唯一索引作为聚簇索引键。如果没有合适的唯一索引InnoDB 会生成一个隐藏主键6 字节 ROWID。2. EXPLAIN 执行计划2.1id执行顺序id代表表查询顺序 id 相同,执行顺序从上往下 id 不同 id递增大的先执行、相同 id按从上到下顺序执行。不同 idid 值大的先执行。例 1相同 id多表 JOINEXPLAIN SELECT * FROM user u JOIN orders o ON u.id o.user_id;idselect_typetabletype1SIMPLEuALL1SIMPLEoref解释两表 JOINid 相同从上到下依次执行。例 2不同 id子查询EXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM orders WHERE amount 100);idselect_typetabletype2SIMPLEordersrange1SIMPLEuserALL解释子查询的 id2 先执行主查询的 id1 后执行。例 3混合EXPLAIN SELECT u.*, t.total_amount FROM user u JOIN ( SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) t ON u.id t.user_id;idselect_typetabletype2DERIVEDordersindex1SIMPLEuALL1SIMPLEtref解释先执行 id2派生表生成临时表再执行 id1 的 JOIN。2.2select_type查询类型类型说明示例SIMPLE查询中不包含子查询或 UNIONEXPLAIN SELECT * FROM user WHERE age 30;PRIMARYSQL 中包含子查询时最外层查询标记为 PRIMARYEXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM orders);DERIVEDFROM 后的子查询先执行并存入临时表见例 3SUBQUERY子查询出现在 WHERE 或 SELECT 列表中EXPLAIN SELECT * FROM user WHERE id (SELECT MAX(user_id) FROM orders);2.3Table查询的表名2.4Type访问类型system 表中只有一行数据const 主键索引/唯一索引eq_ref 基于驱动表主表的字段多次通过被驱动表从表的主键或唯一索引进行等值匹配ref 普通索引类型访问range 索引范围查询index 全索引扫描不过数据只需要在节点读取即可不需要回表。All 全索引扫描基于聚簇索引要到叶子节点拿整行数据效率system const eq_ref ref range index All2.5 possible_keys 可能用到的索引列表显示可能用的索引名称[如果查询的字段存在某一个索引上就把改索引列出来]select * from person where id is not null ---2.6 key 实际使用索引2.7 ref显示使用了等值匹配哪个列进行过滤2.8 rowsmysql中优化器估计的要扫描的行数2.9 extra一些重要的额外信息Using filesort 排序字段没有使用索引Using temporary 分组时没有使用索引一般没有Using filesort 因为分组需要用到排序Using index 用到了索引覆盖Using where 使用了where过滤慢查询-- 慢查询日志相关的系统变量SHOW VARIABLES LIKE %slow_query_log%;-- 开启慢查询日志set GLOBAL slow_query_log 1-- 设置时间阈值 超过的sql语句就会被记录在慢查询日志set GLOBAL long_query_time 3;-- 查看时间阈值show VARIABLES LIKE %long_query_time%慢查询日志文件位置C:\ProgramData\MySQL\MySQL Server 8.0\Data\LAPTOP-G7ETDH5B-slow.log日志undo log(回滚日志)1.在事务未提交之前会将执行的命令记录在undo log日志中当需要回滚时根据日志执行相反的操作。2. 通过read view快照 undo log实现mvcc -- 存储旧版本数据
MySQL讲解/内部结构/索引下推/Explain/慢查询（必备）
MySQL 内部结构与执行计划1. MySQL 内部结构总体来说MySQL 分为Server 层和存储引擎层。索引下推数据的筛选从Server层下推到存储引擎层主要发生在联合索引上当前面的的字段发生索引失效如果没有索引下推那直接进行回表最后在Server层进行数据筛选。如果有索引下推那么还会继续根据后续字段进行筛选也就是在存储引擎层筛选。减少回表次数提升查询速度。1.1 Server层总体来说整个mysql分为Server层和存储引擎层。Server层主要包含连接器查询缓存解析器预处理器优化器执行器...等其中查询缓存在mysql8完全剔除。存储引擎主要包括多种存储引擎1.1.1连接器向mysql发送sql语句时首先我们得客户端要先与mysql连接器创建连接完成TCP握手。终端在进入这个路径输入mysql -u root -p 并输入你的密码。此时我们已经和mysql创建了一个连接输入show processlist查看MySQL服务被多少个客户端连接。最大连接数量1511.1.1.1权限当我们在mysql用户密码认证成功后连接器上权限表会查询该用户所拥有的权限在此之后该用户的权限都依赖于初始读到的权限信息。即使中途权限修改。那么这里面发生了什么事情呢我们的连接方式有两种一种是长连接一种是短连接。他们的区别在于请求完是否会释放连接。前者客户端与用户端连接后一直不关闭后者每次请求完都会关闭。当然这会造成巨大的性能开销所以说在高并发的情况下短连接并不是最佳之策还需要使用我们的长连接但它也并不是完美的长连接的堆积会造成我们MySQL占用内存太大。解决策略1 定期断开长连接2 客户端主动重置连接其实当连接器验证我们账户密码正确时连接器就会获取当前用户得权限然后保存起来。后续得任何操作都会基于我们连接一开始保存的权限信息进行权限分配的判断。也就是说即使中途我们修改了权限此时的任何权限判断也是基于连接一开始保存的为准。s1.1.2 解析器作用将 SQL 解析为 MySQL 能理解的结构。步骤词法分析识别 SQL 中的关键字、表名、字段名等。语法分析检查 SQL 是否符合 MySQL 语法规则。1.1.3 预处理器检查表、字段是否存在。将*展开成实际字段列表。1.1.4 优化器确定 SQL 的执行计划例如使用哪一个索引、表的连接顺序等。1.1.5 执行器根据执行计划从存储引擎中读取数据。如果是全表扫描会调用存储引擎的接口循环取数据。1.2 存储引擎MySQL 数据是存储在聚簇索引上的以 InnoDB 为例。聚簇索引的主键选择规则如果表有主键PRIMARY KEY则使用它作为聚簇索引键。如果没有主键则选择第一个非空唯一索引作为聚簇索引键。如果没有合适的唯一索引InnoDB 会生成一个隐藏主键6 字节 ROWID。2. EXPLAIN 执行计划2.1id执行顺序id代表表查询顺序 id 相同,执行顺序从上往下 id 不同 id递增大的先执行、相同 id按从上到下顺序执行。不同 idid 值大的先执行。例 1相同 id多表 JOINEXPLAIN SELECT * FROM user u JOIN orders o ON u.id o.user_id;idselect_typetabletype1SIMPLEuALL1SIMPLEoref解释两表 JOINid 相同从上到下依次执行。例 2不同 id子查询EXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM orders WHERE amount 100);idselect_typetabletype2SIMPLEordersrange1SIMPLEuserALL解释子查询的 id2 先执行主查询的 id1 后执行。例 3混合EXPLAIN SELECT u.*, t.total_amount FROM user u JOIN ( SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) t ON u.id t.user_id;idselect_typetabletype2DERIVEDordersindex1SIMPLEuALL1SIMPLEtref解释先执行 id2派生表生成临时表再执行 id1 的 JOIN。2.2select_type查询类型类型说明示例SIMPLE查询中不包含子查询或 UNIONEXPLAIN SELECT * FROM user WHERE age 30;PRIMARYSQL 中包含子查询时最外层查询标记为 PRIMARYEXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM orders);DERIVEDFROM 后的子查询先执行并存入临时表见例 3SUBQUERY子查询出现在 WHERE 或 SELECT 列表中EXPLAIN SELECT * FROM user WHERE id (SELECT MAX(user_id) FROM orders);2.3Table查询的表名2.4Type访问类型system 表中只有一行数据const 主键索引/唯一索引eq_ref 基于驱动表主表的字段多次通过被驱动表从表的主键或唯一索引进行等值匹配ref 普通索引类型访问range 索引范围查询index 全索引扫描不过数据只需要在节点读取即可不需要回表。All 全索引扫描基于聚簇索引要到叶子节点拿整行数据效率system const eq_ref ref range index All2.5 possible_keys 可能用到的索引列表显示可能用的索引名称[如果查询的字段存在某一个索引上就把改索引列出来]select * from person where id is not null ---2.6 key 实际使用索引2.7 ref显示使用了等值匹配哪个列进行过滤2.8 rowsmysql中优化器估计的要扫描的行数2.9 extra一些重要的额外信息Using filesort 排序字段没有使用索引Using temporary 分组时没有使用索引一般没有Using filesort 因为分组需要用到排序Using index 用到了索引覆盖Using where 使用了where过滤慢查询-- 慢查询日志相关的系统变量SHOW VARIABLES LIKE %slow_query_log%;-- 开启慢查询日志set GLOBAL slow_query_log 1-- 设置时间阈值 超过的sql语句就会被记录在慢查询日志set GLOBAL long_query_time 3;-- 查看时间阈值show VARIABLES LIKE %long_query_time%慢查询日志文件位置C:\ProgramData\MySQL\MySQL Server 8.0\Data\LAPTOP-G7ETDH5B-slow.log日志undo log(回滚日志)1.在事务未提交之前会将执行的命令记录在undo log日志中当需要回滚时根据日志执行相反的操作。2. 通过read view快照 undo log实现mvcc -- 存储旧版本数据
