抓好门户网站 建设,wordpress justnews,c2c模式成功案例分析,招商网站建设需要什么目录
背景#xff1a;
mysql 整体结构#xff1a;
SQL查询语句执行过程是怎样的#xff1a; 知道了mysql的整体架构#xff0c;那么一条查询语句是怎么被执行的呢#xff1a;
什么是索引#xff1a;
建立索引越多越好吗#xff1a;
如何发现慢查询#xff1…目录
背景
mysql 整体结构
SQL查询语句执行过程是怎样的 知道了mysql的整体架构那么一条查询语句是怎么被执行的呢
什么是索引
建立索引越多越好吗
如何发现慢查询
如何优化满查询 背景 性能测试过程中数据库往往是造成性能瓶颈之一而数据库瓶颈中sql 语句又是值得探究分析的一环其中慢查询是重点优化对象在MySQL中慢查询是指查询执行时间较长或者消耗
较多资源的查询语句。具体来说MySQL中可以通过设置一个阈值来定义慢查询通常默认情况下是超过2秒钟的查询会被认为是慢查询但是这个阈值可以根据具体情况进行调整。
慢查询的存在可能会对MySQL数据库的性能产生负面影响因为它会占用大量的计算资源和I/O资源导致其他查询的响应时间变慢。因此及时发现并优化慢查询非常重要。
mysql 整体结构
MySQL是一个典型的客户端-服务器Client-Server架构系统它主要由以下几个组件构成 客户端Client客户端是指连接到MySQL服务器的程序或工具它们可以通过网络或本地套接字与MySQL服务器通信。MySQL提供了多种客户端工具如mysql命令行工具、MySQL Workbench、phpMyAdmin等。 连接管理器Connection Manager连接管理器负责管理客户端连接和会话。它接收客户端的连接请求并根据配置文件中的参数来限制连接数、最大并发数等确保MySQL服务器的稳定性和安全性。 查询解析器Query Parser查询解析器负责解析客户端提交的SQL查询语句并将其转换成MySQL服务器可理解的内部数据结构。在此过程中查询解析器会检查查询语句的语法和语义是否正确以及权限是否足够执行该查询。 优化器Optimizer优化器是MySQL查询执行的关键组件它负责优化查询执行计划以获得最佳的执行效率。优化器会分析查询语句选择最优的索引、表的访问顺序、连接方式等来执行查询。MySQL提供了多种优化器如基于规则的优化器、基于成本的优化器等。 存储引擎Storage Engine存储引擎是MySQL数据库中存储和管理数据的核心组件。MySQL支持多种存储引擎如InnoDB、MyISAM、MEMORY等。每个存储引擎都有其独特的特性和适用场景如InnoDB适合于高并发、事务性操作MyISAM适合于读密集型操作等。 缓存Cache缓存是MySQL性能优化的重要手段之一。MySQL提供了多种缓存机制如查询缓存、表缓存、缓冲池等。查询缓存可以缓存查询结果以减少重复查询的开销表缓存可以缓存表结构以加速表的访问缓冲池可以缓存磁盘上的数据以提高数据访问的速度。
总的来说MySQL架构是由客户端、连接管理器、查询解析器、优化器、存储引擎和缓存等组件构成的。每个组件都有其独特的作用和功能共同协作来实现MySQL数据库系统的高效稳定运行。
SQL查询语句执行过程是怎样的 知道了mysql的整体架构那么一条查询语句是怎么被执行的呢 你会先连接到这个数据库上这时候接待你的就是连接器连接建立完成后执行逻辑就会来到查询缓存。如果开启来了查询缓存之前执行过的语句及其结果可能会以 key-value 对的形
式被直接缓存在内存中。如果命中value直接返回给客户端。没有命中则继续。执行完成后执行结果会被存入查询缓存中。如果没有命中查询缓存进入分析器通过词法分析语法分
析对 SQL 语句做解析语法错误是从这个环节报出的。优化器是为了提升SQL的执行性能。经过了分析器MySQL 就知道要做什么了。在开始执行之前还要先经过优化器的处理。在表里面
有多个索引的时候决定使用哪个索引或者在一个语句有多表关联join的时候决定各个表的连接顺序。优化器优化后进入了执行器阶段执行器跟存储层进行交互取得执行结果并返
回。 什么是索引 索引是一种用于加速数据库查询的数据结构。它可以快速定位到满足查询条件的记录从而提高查询效率和性能。简单来讲索引的出现其实就是为了提高数据查询的效率就像书的目录
一样如果你想快速找到其中的某一个知识点在不借助目录的情况下那我估计你可得找一会儿。同样对于数据库的表而言索引其实就是它的“目录”。在MySQL中索引通常是基于B-
TreeB树或哈希表实现的。 索引主要包括主键索引和和非主键索引主键索引是建立在表的主键列上的索引而非主键索引则是建立在其他列或列组合上的索引。在查询过程中主键索引和非主键索引的查询方式和效率有所不同。对于主键索引MySQL可以通过B-Tree索引结构快速定位到指定的行记录因为主键索引唯一每个值都对应一个行记录因此可以直接找到匹配的行记录。例如如果需要查询id为10的学生记录可以使用如下的SQL语句 SELECT * FROM students WHERE id 10; MySQL会利用主键索引快速定位到id为10的行记录效率非常高。而对于非主键索引MySQL也可以通过B-Tree索引结构定位到满足查询条件的行记录但是需要额外的步骤。首先MySQL会根据非主键索引找到满足查询条件的行记录的主键值然后再通过主键索引定位到实际的行记录。例如如果需要查询姓名为“Tom”的学生记录可以使用如下的SQL语句 SELECT * FROM students WHERE name Tom;
MySQL会利用非主键索引idx_students_name找到所有姓名为“Tom”的行记录的主键值然后再根据主键索引定位到实际的行记录。这个过程称为“回表查询”需要额外的IO操作和CPU计
算因此效率相对较低。如果表中的数据量很大回表查询的开销会更加显著。 建立索引越多越好吗 建立索引并不是越多越好反而可能会对数据库性能产生负面影响。首先索引会占用存储空间如果过多地建立索引会导致数据库占用更多的磁盘空间对于大型数据库来说这可能
会导致磁盘空间不足。其次索引会影响插入、更新和删除操作的性能。当进行插入、更新和删除操作时MySQL需要更新数据和索引如果过多地建立索引就会使这些操作花费更多的时
间从而降低数据库的性能。 最后索引会影响查询操作的效率。虽然索引可以加速查询操作但是如果过多地建立索引就会导致MySQL需要在多个索引中选择最优的索引这会增加查询的开销并且可能会导致
MySQL选择不合适的索引从而降低查询的效率。因此在建立索引时需要根据具体情况进行选择避免过多地建立索引。通常情况下可以考虑在经常使用的列上建立索引或者在需要优
化查询的列上建立索引。同时可以通过监控索引的使用情况来确定哪些索引需要优化或删除以提高数据库的性能和效率。 如何发现慢查询 1. 通过设置slow_query_log参数来开启慢查询日志对慢查询日志进行监控如果新增慢查询便立即发送通知。推荐 2. 慢查询日志分析工具MySQL提供了一些工具如mysqldumpslow和mysqlsla可以根据查询日志来分析慢查询找出执行时间最长的查询和最频繁的查询等信息。 如何优化满查询
情况1 通过explain你可能会发现SQL压根没走任何索引而且现在表中的数据量巨大无比。
解决建合适索引
情况2 通过explain查看SQL执行计划中的key字段。如果发现优化器选择的Key和你预期的Key不一样。那显然是优化器选错了索引
解决 最快的解决方案就是force index 强制指定索引或通过增加索引、优化索引、重构查询语句等方式来提高查询效率
情况3 查询语句复杂或者存在大量子查询
解决查询语句复杂或者存在大量子查询会影响查询性能可以考虑通过优化SQL语句来提高查询效率。例如可以使用JOIN语句替换多个子查询或者使用WHERE子句限制返回的行数。 分析优化实践
假设有一个名为“orders”的表包含以下列
id: INT主键列customer_id: INT顾客编号status: ENUM(pending, completed, cancelled)订单状态order_date: DATETIME订单日期amount: DECIMAL(10,2)订单金额
现在需要查询所有订单金额大于1000元的未完成订单查询语句如下 SELECT * FROM orders WHERE status pending AND amount 1000; 首先可以通过使用EXPLAIN语句来查看查询计划以了解查询的执行情况 EXPLAIN SELECT * FROM orders WHERE status pending AND amount 1000; 执行后发现 type列的值是ALL走的全表扫描key字段是NULL没有使用任何索。接下来可以在status和amount列上建立索引建立索引的语句如下
CREATE INDEX idx_orders_status ON orders (status); CREATE INDEX idx_orders_amount ON orders (amount);
然后再次执行查询语句可以看到查询效率有了显著提升查询速度大大加快。
优化前的查询计划如下所示 ----------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ----------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders| NULL | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | Using where | ----------------------------------------------------------------------------------------------------------- 优化后的查询计划如下所示 ---------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders| NULL | ref | idx_orders_status,idx_orders_amount | idx_orders_status | 2 | const | 5 | 50.00 | Using index condition | ---------------------------------------------------------------------------------------------------------------------------------------------- 可以看到优化后的查询计划使用了idx_orders_status索引查询效率大大提高。
因此通过在status和amount列上建立索引的方式可以提高查询效率降低数据库的负载和响应时间。但需要注意的是索引的建立需要根据具体情况进行选择和应用过多的索引会影响插
入、更新和删除操作的性能因此需要谨慎考虑索引的建立数量和方式。 以下是我收集到的比较好的学习教程资源虽然不是什么很值钱的东西如果你刚好需要可以评论区留言【777】直接拿走就好了 各位想获取资料的朋友请点赞 评论 收藏三连
三连之后我会在评论区挨个私信发给你们~
