热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

关系数据库之mysql三:从一条sql的生命周期说起

mysql教程栏目介绍关系数据库的sql的生命周期。

mysql教程栏目介绍关系数据库的sql的生命周期。

连接器:

建立与 MySQL 的连接,用于查询SQL语句,判断权限 。

查询缓存:

  • 如果语句不在查询缓存中,就会继续后面的执行阶段。执行完成后,执行结果会被存入查询缓存中
  • 如果查询命中缓存,MySQL不需要执行后面的复杂操作,就可以直接返回结果,提升效率

分析器:

对 SQL 语句进行硬解析,分析器先会做词法分析。分析SQL 语句的组成成分。判断输入的 SQL 语句是否满足语法规则。

优化器:

优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。 不同的执行方法的逻辑结果是一样的,但是执行的效率会有不同,而优化器的作用就是决定选择使用哪一个方案。

执行器:

  • 有索引:第一次调用的是取满足条件的第一行这个接口,之后循环取满足条件的下一行这个接口,最终把查询结果返回客户端
  • 无索引:调用 InnoDB 引擎接口取这个表的第一行,判断sql查询条件,如果不是则跳过,如果是则将这行存在结果集中; 调用引擎接口取下一行,重复相同的判断逻辑,直到取到这个表的最后一行。 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

理解执行计划

EXPLAIN命令输出MySQL将如何执行你的SQL语句,但不会返回数据

如何使用

[root@localhost][(none)]> explain select * from 表名 where project_id = 36;
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+
| id | select_type | table                    | partitions | type | possible_keys | key        | key_len | ref   | rows   | filtered | Extra |
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | 表名                     | NULL       | ref  | project_id    | project_id | 4       | const | 797964 |   100.00 | NULL  |
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+ 

id

  • id相同执行顺序由上至下
  • id不同,id值越大优先级越高,越先被执行

select_type

  • SIMPLE:简单的 select 查询,查询中不包含子查询或者 union
  • PRIMARY:查询中包含子部分,最外层查询则被标记为 primary
  • DERIVED:是子查询from的一部分
  • DEPENDENT SUBQUERY:子查询中的第一个SELECT,子查询依赖于外层查询的结果
  • SUBQUERY 表示在 select 或 where 列表中包含了子查询,
  • MATERIALIZED:表示 where 后面 in 条件的子查询
  • UNION:表示 union 中的第二个或后面的 select 语句
  • UNION RESULT:union 的结果

table

  • 表对象

type

system > const > eq_ref > ref > range > index > ALL(查询效率)

  • system:表中只有一条数据,这个类型是特殊的const类型
  • const:针对于主键或唯一索引的等值查询扫描,最多只返回一个行数据。速度非常快,因为只读取一次即可。
  • eq_ref:此类型通常出现在多表的join查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果,并且查询的比较操作通常是=,查询效率较高
  • ref:此类型通常出现在多表的join查询,针对于非唯一或非主键索引,或者是使用了最左前缀规则索引的查询
  • range:范围扫描 这个类型通常出现在 <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中
  • index:索引树扫描
  • ALL:全表扫描(full table scan)

possible_keys

  • 可能使用的索引,注意不一定会使用
  • 查询涉及到的字段上若存在索引,则该索引将被列出来
  • 当该列为NULL时就要考虑当前的SQL是否需要优化了

key

  • 显示MySQL在查询中实际使用的索引,若没有使用索引,显示NULL。
  • 查询中若使用了覆盖索引(覆盖索引:索引的数据覆盖了需要查询的所有数据),则该索引仅出现在key列表中

key_length

  • 索引长度

ref

  • 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

rows

  • 返回估算的结果集数目,并不是准确的值

filtered

  • 示返回结果的行数占需读取行数的百分比, filtered 的值越大越好

extra

  • Using where:表示优化器需要通过索引回表,之后到server层进行过滤查询数据
  • Using index:表示直接访问索引就足够获取到所需要的数据,不需要回表
  • Using index condition:在5.6版本后加入的新特性(Index Condition Pushdown)
  • Using index for group-by:使用了索引来进行GROUP BY或者DISTINCT的查询
  • Using filesort:当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大
  • Using temporary 临时表被使用,时常出现在GROUP BY和ORDER BY子句情况下。(sort buffer或者磁盘被使用)

光看 filesort 字面意思,可能以为是要利用磁盘文件进行排序,实则不全然。 当MySQL不能使用索引进行排序时,就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序,如果内存装载不下,它会将磁盘上的数据进行分块,再对各个 数据块进行排序,然后将各个块合并成有序的结果集(实际上就是外排序)。

当对连接操作进行排序时,如果ORDER BY仅仅引用第一个表的列,MySQL对该表进行filesort操作,然后进行连接处理,此时,EXPLAIN输出“Using filesort”;否则,MySQL必 须将查询的结果集生成一个临时表,在连接完成之后行行filesort操作,此时,EXPLAIN输出“Using temporary;Using filesort”。

提高查询效率

正确使用索引

为解释方便,来一个demo:

DROP TABLE IF EXISTS user; 
CREATE TABLE user( 
id int AUTO_INCREMENT PRIMARY KEY, 
user_name varchar(30) NOT NULL, 
gender bit(1) NOT NULL DEFAULT b’1’, 
city varchar(50) NOT NULL, 
age int NOT NULL 
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE user ADD INDEX idx_user(user_name , city , age); 
 

什么样的索引可以被使用?

  • **全匹配:**SELECT * FROM user WHERE user_name=&#39;JueJin&#39;AND age=&#39;5&#39; AND city=&#39;上海&#39;;(与where后查询条件的顺序无关)
  • 匹配最左前缀:(user_name )、(user_name, city)、(user_name , city , age)(满足最左前缀查询条件的顺序与索引列的顺序无关,如:(city, user_name)、(age, city, user_name))
  • **匹配列前缀:**SELECT * FROM user WHERE user_name LIKE &#39;W%&#39;
  • **匹配范围值:**SELECT * FROM user WHERE user_name BETWEEN &#39;W%&#39; AND &#39;Z%&#39;

什么样的索引无法被使用?

  • **where查询条件中不包含索引列中的最左索引列,则无法使用到索引: **

SELECT * FROM user WHERE city=&#39;上海&#39;;

SELECT * FROM user WHERE age=&#39;26&#39;;

SELECT * FROM user WHERE age=&#39;26&#39; AND city=‘上海&#39;;

  • **即使where的查询条件是最左索引列,也无法使用索引查询用户名以N结尾的用户: **

SELECT * FROM user WHERE user_name LIKE &#39;%N&#39;;

  • **如果where查询条件中有某个列的范围查询,其右边的所有列都无法使用索引优化查询: **

SELECT * FROM user WHERE user_name=&#39;JueJin&#39; AND city LIKE &#39;上%&#39; AND age=31;

  • **索引列不能是表达式的一部分,也不能作为函数的参数,否则无法使用索引查询: **

SELECT * FROM user WHERE user_name=concat(user_name,&#39;PLUS&#39;);

选择合适的索引列顺序

  • 在组合索引的创建中索引列的顺序非常重要,正确的索引顺序依赖于使用该索引的查询的查询方式
  • 对于组合索引的索引顺序可以将选择性最高的列放到索引最前列,该法则与前缀索引的选择性方法一致
  • 并不是说所有的组合索引的顺序都使用该法则就能确定,还需要根据具体的查询场景来确定具体的索引顺序

覆盖索引条件

  • 如果一个索引中包含所有要查询的字段的值,那么就称之为覆盖索引

SELECT user_name, city, age FROM user WHERE user_name=&#39;Tony&#39; AND age=&#39;28&#39; AND city=&#39;上海&#39;;

因为要查询的字段(user_name, city, age)都包含在组合索引的索引列中,所以就使用了覆盖索引查询,查看是否使用了覆盖索引可以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引,覆盖索引可以极大的提高访问性能。

使用索引进行排序

在排序操作中如果能使用到索引来排序,那么可以极大地提高排序的速度,要使用索引来排序需要满足以下两点即可:

  • ORDER BY子句后的列顺序要与组合索引的列顺序一致,且所有排序列的排序方向(正序/倒序)需一致
  • 所查询的字段值需要包含在索引列中,及满足覆盖索引

排序可用demo:

  • SELECT user_name, city, age FROM user_test ORDER BY user_name;
  • SELECT user_name, city, age FROM user_test ORDER BY user_name,city;
  • SELECT user_name, city, age FROM user_test ORDER BY user_name DESC,city DESC;
  • SELECT user_name, city, age FROM user_test WHERE user_name=&#39;Tony&#39; ORDER BY city;

排序不可用demo:

  • SELECT user_name, city, age FROM user_test ORDER BY user_name gender;
  • SELECT user_name, city, age, gender FROM user_test ORDER BY user_name;
  • SELECT user_name, city, age FROM user_test ORDER BY user_name ASC,city DESC;
  • SELECT user_name, city, age FROM user_test WHERE user_name LIKE &#39;W%&#39; ORDER BY city;

数据获取建议

不要返回应用户程序所不需要的数据限制返回数

LIMIT:MySQL并不能按照需求返回数据量,也就是MySQL总是会查询出全部数据,使用LIMIT子句其实是为了减小网络数据传输的压力,并不会减小数据的读取行数。

去掉不需要的列

  • SELECT * 语句取出表中的所有字段,不论该字段的数据对调用的应用程序是否有用,这会对服务器资源造成浪费,甚至会对服务器的性能产生一定的影响
  • 如果表的结构在以后发生了改变,那么 SELECT * 语句可能会取到不正确的数据
  • 执行 SELECT * 语句时,首先要查找出表中有哪些列,然后才能开始执行 SELECT * 语句,这在某些情况会产生性能问题
  • 使用 SELECT * 语句将不会使到覆盖索引,不利于查询的性能优化

正确使用索引的优点

  • 避免全表扫描
  1. 单表查询时,全表扫描需要查询每一行
  2. 多表查询时,全表扫描至少需要检索所有表中每一行
  • 提高速度
  1. 可以迅速定位结果集的第一行
  2. 排除不相关的结果
  3. 对于MIN()或者MAX()值不必检查每一行
  • 提高排序和分组的效率
  • 在可以使用覆盖索引的情况下避免row loop-up

索引的代价

  • 如果存在过多索引,数据修改将会变得缓慢
  1. 受影响的索引需要被更新
  2. 对于写密集型环境压力很大
  • 索引消耗过多磁盘空间
  1. InnoDB存储引擎将索引和数据存储在一起
  2. 需要监控磁盘空间

索引最佳实践

对于如下列考虑使用索引

  • WHERE子句中的列
  • ORDER BY或GROUP BY子句中的列
  • 表连接条件列

考虑针对字符串型列使用前缀索引

  • 可以更快速地比较与loop up
  • 减少磁盘I/O

SELECT语句效率低下时考虑

  • 避免全表扫描
  • 尝试增加索引
  1. WHERE语句
  2. 表连接条件
  • 利用ANALYZE TABLE来收集统计信息
  • 考虑存储引擎层的优化

调优表连接方法

  • 在ON或USING子句的列上增加索引
  • 利用SELECT STRAIGHT_JOIN来强制表连接顺序
  • 在ORDER BY和GROUP BY的列上增加索引
  • join连接不一定比子查询效率高

更多相关免费学习推荐:mysql教程(视频)

以上就是关系数据库之mysql三:从一条sql的生命周期说起的详细内容,更多请关注 第一PHP社区 其它相关文章!


推荐阅读
  • Syncnavigator激活工具及破解方法详解
    本文详细介绍了Syncnavigator激活工具的使用方法及其破解技巧。用户可以通过访问官方网站www.SyncNavigator.CN获取相关资源,并通过客服QQ 1793040获得技术支持和帮助。此外,文章还提供了详细的步骤说明和常见问题解答,以确保用户能够顺利激活并使用Syncnavigator软件。 ... [详细]
  • 源代码是构建网站的基础,涵盖了网站程序的所有代码、文件和目录结构。掌握源代码意味着完全控制网站的所有权。在传统自助建站平台中,由于采用SAAS模式,源代码通常不对外开放,用户实际上每年支付的费用仅是为了使用这些平台提供的服务,而无法获得真正的代码所有权。相比之下,米拓源代码提供了全面的技术细节和实现方法,使开发者能够深入了解并自主定制网站功能,确保了更高的灵活性和安全性。 ... [详细]
  • MySQL索引详解及其优化策略
    本文详细解析了MySQL索引的概念、数据结构及管理方法,并探讨了如何正确使用索引以提升查询性能。文章还深入讲解了联合索引与覆盖索引的应用场景,以及它们在优化数据库性能中的重要作用。此外,通过实例分析,进一步阐述了索引在高读写比系统中的必要性和优势。 ... [详细]
  • PHP 数组逆序排列方法及常用排序函数详解 ... [详细]
  • PHP开发人员薪资水平分析:工程师平均工资概况
    PHP开发人员薪资水平分析:工程师平均工资概况 ... [详细]
  • 在数据库事务处理中,InnoDB 存储引擎提供了多种隔离级别,其中 READ COMMITTED 和 REPEATABLE READ 是两个常用的选项。本文详细对比了这两种隔离级别的特点和差异,不仅从理论角度分析了它们对“脏读”和“幻读”的处理方式,还结合实际应用场景探讨了它们在并发控制和性能表现上的不同。特别关注了行锁机制在不同隔离级别下的行为,为开发者选择合适的隔离级别提供了参考。 ... [详细]
  • 开发日志:在插入数据到一张表的同时更新另一张表的技术细节与最佳实践 ... [详细]
  • 当前物联网领域十大核心技术解析:涵盖哪些关键技术?
    经过近十年的技术革新,物联网已悄然渗透到日常生活中,对社会产生了深远影响。本文将详细解析当前物联网领域的十大核心关键技术,包括但不限于:1. 军事物联网技术,该技术通过先进的感知设备实现战场环境的实时监测与数据传输,提升作战效能和决策效率。其他关键技术还包括传感器网络、边缘计算、大数据分析等,这些技术共同推动了物联网的快速发展和广泛应用。 ... [详细]
  • Python 实战:异步爬虫(协程技术)与分布式爬虫(多进程应用)深入解析
    本文将深入探讨 Python 异步爬虫和分布式爬虫的技术细节,重点介绍协程技术和多进程应用在爬虫开发中的实际应用。通过对比多进程和协程的工作原理,帮助读者理解两者在性能和资源利用上的差异,从而在实际项目中做出更合适的选择。文章还将结合具体案例,展示如何高效地实现异步和分布式爬虫,以提升数据抓取的效率和稳定性。 ... [详细]
  • 理解和应用HTTP请求中的转发与重定向机制
    在HTTP请求处理过程中,客户端发送请求(通常简称为req),服务器进行相应处理后返回响应(通常简称为res)。理解和应用客户端的转发与重定向机制是前端开发的重要内容。这两种机制在Web开发中具有关键作用,能够有效管理和优化用户请求的处理流程。转发机制允许服务器内部将请求传递给另一个资源,而重定向则指示客户端向新的URL发起新的请求,从而实现页面跳转或资源更新。掌握这些技术有助于提升应用的性能和用户体验。 ... [详细]
  • Node.js 配置文件管理方法详解与最佳实践
    本文详细介绍了 Node.js 中配置文件管理的方法与最佳实践,涵盖常见的配置文件格式及其优缺点,并提供了多种实用技巧和示例代码,帮助开发者高效地管理和维护项目配置,具有较高的参考价值。 ... [详细]
  • vtkGlyph3D 是一种强大的符号化可视化工具,能够将三维数据集中的每个点用预定义的几何图形(如球体或箭头)进行表示。该工具不仅支持自定义符号的方向和缩放比例,还能够在复杂的数据场中突出显示关键特征,从而提高数据的可解释性和可视化效果。通过这种方式,用户可以更直观地理解和分析三维数据集中的重要信息。 ... [详细]
  • 为了评估精心优化的模型与策略在实际环境中的表现,Google对其实验框架进行了全面升级,旨在实现更高效、更精准和更快速的在线测试。新的框架支持更多的实验场景,提供更好的数据洞察,并显著缩短了实验周期,从而加速产品迭代和优化过程。 ... [详细]
  • 本文详细解析了九度编程平台上的斐波那契数列高效算法挑战(题目编号:1387)。该挑战要求在1秒的时间限制和32兆的内存限制下,设计出高效的斐波那契数列计算方法。通过多种算法的对比和性能分析,本文提供了优化方案,帮助参赛者在限定资源条件下实现高效计算。 ... [详细]
  • 实现Nginx对ThinkPHP URL重写及PATHINFO支持的详细方法解析【PHP开发】
    在PHP后端开发中,实现Nginx对ThinkPHP的URL重写及PATHINFO支持是一项常见的需求。本文详细解析了经过多次尝试和研究,最终找到的一种有效配置方法,能够确保URL_MODERewrite功能正常运行,并提供稳定的服务。此外,文章还探讨了相关配置项的具体作用及其优化建议,帮助开发者更好地理解和应用这些技术。 ... [详细]
author-avatar
90后的中老年人
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有