php网站建设费用,注册一个商标多少钱,怎么建立网站数据库,室外绿化工程施工方案久久建筑网一、SQL优化的主要步骤
在应用的的开发过程中#xff0c;由于初期数据量小#xff0c;开发人员写 SQL 语句时更重视功能上的实现#xff0c;但是当应用系统正式上线后#xff0c;随着生产数据量的急剧增长#xff0c;很多 SQL 语句开始逐渐显露出性能问题#xff0c;对生…一、SQL优化的主要步骤
在应用的的开发过程中由于初期数据量小开发人员写 SQL 语句时更重视功能上的实现但是当应用系统正式上线后随着生产数据量的急剧增长很多 SQL 语句开始逐渐显露出性能问题对生产的影响也越来越大此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈因此我们必须要对它们进行优化。
SQL优化主要会经过以下步骤
二、查看SQL执行效率
1、show status
“show status” 是一个 MySQL 命令用于查看关于 MySQL 服务器的状态信息。
可以使用以下两种格式来使用它 SHOW SESSION STATUS;显示当前会话session的状态信息。 SHOW GLOBAL STATUS;显示全局global的状态信息包括所有会话的统计信息。
这些命令将返回一张结果表其中包含一系列的名称-值对。名称表示某个特定的状态变量而值表示与该状态变量相关的当前值。
例如执行 SHOW SESSION STATUS; 将返回类似下面的结果结果集中的每一行都包含一个变量名称和其对应的当前值
---------------------------------
| Variable_name | Value |
---------------------------------
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 12345 |
| Bytes_sent | 67890 |
| ... | ... |
---------------------------------2、show status like Com_______
show status like ‘Com_______’;是一个MySQL的用法用于查看MySQL服务器的运行状态信息。通过该语句可以获取与各种命令以Com开头的执行次数相关的统计信息。
具体来说这个语句是通过查询MySQL的status全局变量来实现的将Com作为参数模式MySQL将返回所有命令名称以Com开头的统计信息。
例如执行show status like ‘Com%’;将返回类似下面的结果
-------------------------------
| Variable_name | Value |
-------------------------------
| Com_admin_commands | 2 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
...每一行表示一个以Com开头的命令名称和对应的执行次数常用的几个统计参数如下
参数含义Com_select执行 select 操作的次数一次查询只累加 1。Com_insert执行 INSERT 操作的次数对于批量插入的 INSERT 操作只累加一次。Com_update执行 UPDATE 操作的次数。Com_delete执行 DELETE 操作的次数。Innodb_rows_readselect 查询返回的行数。Innodb_rows_inserted执行 INSERT 操作插入的行数。Innodb_rows_updated执行 UPDATE 操作更新的行数。Innodb_rows_deleted执行 DELETE 操作删除的行数。Connections试图连接 MySQL 服务器的次数。Uptime服务器工作时间。Slow_queries慢查询的次数。
三、定位低效率执行SQL
主要通过慢查询日志与show process list这两种方式定位低效率SQL。
1、慢查询日志
慢查询日志是一种用于记录执行时间超过指定阈值的 SQL 查询的日志记录方式可以帮助定位和优化执行时间较长的 SQL 查询。 在启用慢查询日志的过程中要确保对系统性能的影响有所控制因为记录所有执行时间超过阈值的查询会对系统资源产生一定的负担。因此应谨慎地选择阈值和合理使用慢查询日志避免对正常请求的响应时间产生太大的影响。
使用慢查询日志的主要步骤
打开慢查询日志功能在数据库管理系统的配置文件中找到并编辑相关的配置项一般是将 slow_query_log 设置为 ON并指定 long_query_time 的阈值该阈值表示执行时间超过多少秒的 SQL 查询会被记录到慢查询日志中。指定慢查询日志文件位置在配置文件中将 slow_query_log_file 设置为指定的文件路径用于存储慢查询日志记录。重启数据库服务保存配置文件的修改并重新启动数据库服务使配置生效。分析慢查询日志等待一段时间后通过查看慢查询日志文件可以找到执行时间超过阈值的 SQL 查询语句和相关的执行信息。慢查询日志的格式通常是以时间、执行时间、查询语句等为列可以使用文本编辑器或专门的慢查询日志分析工具进行查阅和分析。优化查询语句根据慢查询日志记录的查询语句和执行信息可以进行相应的查询优化例如添加适当的索引、重构查询语句、调整数据库配置等以减少查询的执行时间。关闭慢查询日志功能在优化完成后可以将数据库管理系统的配置文件中的慢查询日志相关配置项改回原来的设置并重启数据库服务。
2、show processlist
使用show processlist命令查看当前MySQL在进行的线程实时地查看 SQL 的执行情况同时对一些锁表操作进行优化。 慢查询日志在查询结束以后才纪录所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题。
使用show processlist定位慢SQL的主要步骤 执行SHOW PROCESSLIST语句 SHOW PROCESSLIST;分析进程列表。观察进程列表中的信息找出执行时间较长或者状态处于Locked或Waiting for table lock的进程。这些进程通常是引起低效率执行的SQL查询。 1 id列用户登录mysql时系统分配的connection_id可以使用函数connection_id()查看2 user列显示当前用户。如果不是root这个命令就只显示用户权限范围的sql语句3 host列显示这个语句是从哪个ip的哪个端口上发的可以用来跟踪出现问题语句的用户4 db列显示这个进程目前连接的是哪个数据库5 command列显示当前连接的执行的命令一般取值为休眠sleep查询query连接connect等6 time列显示这个状态持续的时间单位是秒7 state列显示使用当前连接的sql语句的状态很重要的列。state描述的是语句执行中的某一个状态。一个sql语句以查询为例可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成8 info列显示这个sql语句是判断问题语句的一个重要依据获取进程详细信息SHOW FULL PROCESSLIST这会显示更多关于每个进程的详细信息包括SQL语句和正在使用的资源等。 分析执行计划和SQL语句。根据进程详细信息中的SQL语句使用EXPLAIN语句来获取执行计划并分析查询是否使用了合适的索引、是否存在全表扫描等问题。 分析执行计划和SQL语句。根据进程详细信息中的SQL语句使用EXPLAIN语句来获取执行计划并分析查询是否使用了合适的索引、是否存在全表扫描等问题。
EXPLAIN SELECT * FROM your_table WHERE ...优化SQL查询。根据分析结果优化低效率执行的查询。可以考虑调整索引、重写查询逻辑、分批处理大数据量查询等方法来提高性能。
四、explain分析执行计划
使用EXPLAIN语句可以分析SQL查询的执行计划以确定查询是否有效利用索引、是否存在慢查询等问题。
使用EXPLAIN关键字和待分析的查询语句进行组合例如
EXPLAIN SELECT * FROM your_table WHERE ...
或者
EXPLAIN FORMATJSON SELECT * FROM your_table WHERE ...字段含义idselect查询的序列号是一组数字表示的是查询中执行select子句或者是操作表的顺序。select_type表示 SELECT 的类型常见的取值有 SIMPLE简单表即不使用表连接或者子查询、PRIMARY主查询即外层的查询、UNIONUNION 中的第二个或者后面的查询语句、SUBQUERY子查询中的第一个 SELECT等table输出结果集的表type表示表的连接类型性能由好到差的连接类型为( system — const ----- eq_ref ------ ref ------- ref_or_null---- index_merge — index_subquery ----- range ----- index ------ all )possible_keys表示查询时可能使用的索引key表示实际使用的索引key_len索引字段的长度rows扫描行的数量extra执行情况的说明和描述
1、explain id列
id 字段是 select查询的序列号是一组数字表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种
1 id 相同表示加载表的顺序是从上到下。 2 id 不同id值越大优先级越高越先被执行。 3 id 有相同也有不同同时存在。id相同的可以认为是一组从上往下顺序执行在所有的组中id的值越大优先级越高越先执行。
2、explain select_type列
表示 SELECT 的类型常见取值
select_type含义SIMPLE简单的select查询查询中不包含子查询或者UNIONPRIMARY查询中若包含任何复杂的子查询最外层查询标记为该标识SUBQUERY在SELECT 或 WHERE 列表中包含了子查询DERIVED在FROM 列表中包含的子查询被标记为 DERIVED衍生 MYSQL会递归执行这些子查询把结果放在临时表中UNION若第二个SELECT出现在UNION之后则标记为UNION 若UNION包含在FROM子句的子查询中外层SELECT将被标记为 DERIVEDUNION RESULT从UNION表获取结果的SELECT
3、explain type列
type 显示的是访问类型常见的取值为
type含义NULLMySQL不访问任何表索引直接返回结果system表只有一行记录(等于系统表)这是const类型的特例一般不会出现const表示通过索引一次就找到了const 用于比较primary key 或者 unique 索引。因为只匹配一行数据所以很快。如将主键置于where列表中MySQL 就能将该查询转换为一个常亮。const于将 “主键” 或 “唯一” 索引的所有部分与常量值进行比较eq_ref类似ref区别在于使用的是唯一索引使用主键的关联查询关联查询出的记录只有一条。常见于主键或唯一索引扫描ref非唯一性索引扫描返回匹配某个单独值的所有行。本质上也是一种索引访问返回所有匹配某个单独值的所有行多个range只检索给定返回的行使用一个索引来选择行。 where 之后出现 between , , in 等操作。indexindex 与 ALL的区别为 index 类型只是遍历了索引树 通常比ALL 快 ALL 是遍历数据文件。all将遍历全表以找到匹配的行
执行效率重高到低为
NULL system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALLsystem const eq_ref ref range index ALL4、explain key列
type含义possible_keys显示可能应用在这张表的索引 一个或多个key实际使用的索引 如果为NULL 则没有使用索引key_len表示索引中使用的字节数 该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下 长度越短越好
5、explain extra列
其他的额外的执行计划信息
extra含义using filesort说明mysql会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取 称为 “文件排序”, 效率低。using temporary使用了临时表保存中间结果MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by 效率低using index表示相应的select操作使用了覆盖索引 避免访问表的数据行 效率不错。
五、分析SQL之show profile
SHOW PROFILE是一个用于分析SQL查询性能的命令。它可以提供更详细的信息包括查询的每个阶段所消耗的时间和资源。
使用SHOW PROFILE命令可以帮助你深入了解SQL查询的性能特征从而更好地分析和优化查询。通过分析每个阶段的时间和资源消耗可以找到查询中的性能瓶颈并采取相应的措施来提高查询性能。
使用SHOW PROFILE分析SQL查询的一般步骤 在连接到数据库后执行以下命令以启用SHOW PROFILE功能将profiling参数设置为1表示启用查询性能分析。 SET profiling 1;准备要分析的SQL查询语句并执行它。 执行完查询后使用以下命令查看分析结果将显示查询的详细性能分析结果列表包含每个查询所耗费的时间和资源。 SHOW PROFILES;选择要查看的查询结果并使用其 Query_ID 执行以下命令。 SHOW PROFILE FOR QUERY Query_ID;希望查看较全面的分析结果可以将命令修改为以下形式 SHOW PROFILE ALL FOR QUERY Query_ID;Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端而不仅仅是返回个客户端。由于在Sending data状态下MySQL线程往往需要做大量的磁盘读取操作所以经常是整各查询中耗时最长的状态。 分析分析结果重要的列包括
字段含义Statussql 语句执行的状态Durationsql 执行过程中每一个步骤的耗时CPU_user当前用户占有的cpuCPU_system系统占有的cpuMemory每个阶段所使用的内存Function每个阶段所使用的内存Calls函数或方法的调用次数
六、trace分析优化器执行计划
可以使用TRACE命令来跟踪和分析优化器在生成执行计划时所经过的各个步骤。TRACE命令可以提供关于优化器的决策过程以及执行计划的详细信息。
TRACE命令生成的跟踪日志可能会非常详细包含大量的信息。因此对于复杂的查询仅关注关键的查询块和执行计划部分以及性能决策即可。
使用TRACE命令可以提供优化器执行计划生成过程的详细信息帮助理解优化器的工作方式并根据需要进行优化。
使用TRACE进行优化器执行计划分析的一般步骤 在连接到数据库后执行以下命令以开启TRACE功能 SET OPTIMIZER_TRACEenabledon;准备要分析的SQL查询语句并在查询前执行以下命令来开始跟踪该命令会指示MySQL跟踪执行计划的生成 SET OPTIMIZER_TRACEeventplan_start;执行要分析的SQL查询。 执行完查询后执行以下命令以停止跟踪 SET OPTIMIZER_TRACEeventplan_end;使用以下命令来查看和分析生成的跟踪日志显示优化器跟踪的详细信息包括每个查询块的执行计划、优化决策、访问方法等 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;结构化的跟踪日志主要字段包含
字段含义QUERY_BLOCK_ID查询块的唯一标识符PLAN_ID执行计划的唯一标识符OPERATION执行计划中的操作类型SEARCH_SARGABLE是否可搜索扫描TABLE_NAME涉及的表名ACCESS_TYPE访问类型如全表扫描、索引扫描等KEY使用的索引USED_ROWS估计使用的行数USED_KEY_PARTS使用的索引部分数
*************************** 1. row ***************************
QUERY: select * from tb_item where id 4
TRACE: {steps: [{join_preparation: {select#: 1,steps: [{expanded_query: /* select#1 */ select tb_item.id AS id,tb_item.title AS title,tb_item.price AS price,tb_item.num AS num,tb_item.categoryid AS categoryid,tb_item.status AS status,tb_item.sellerid AS sellerid,tb_item.createtime AS createtime,tb_item.updatetime AS updatetime from tb_item where (tb_item.id 4)}] /* steps */} /* join_preparation */},{join_optimization: {select#: 1,steps: [{condition_processing: {condition: WHERE,original_condition: (tb_item.id 4),steps: [{transformation: equality_propagation,resulting_condition: (tb_item.id 4)},{transformation: constant_propagation,resulting_condition: (tb_item.id 4)},{transformation: trivial_condition_removal,resulting_condition: (tb_item.id 4)}] /* steps */} /* condition_processing */},{table_dependencies: [{table: tb_item,row_may_be_null: false,map_bit: 0,depends_on_map_bits: [] /* depends_on_map_bits */}] /* table_dependencies */},{ref_optimizer_key_uses: [] /* ref_optimizer_key_uses */},{rows_estimation: [{table: tb_item,range_analysis: {table_scan: {rows: 9816098,cost: 2.04e6} /* table_scan */,potential_range_indices: [{index: PRIMARY,usable: true,key_parts: [id] /* key_parts */}] /* potential_range_indices */,setup_range_conditions: [] /* setup_range_conditions */,group_index_range: {chosen: false,cause: not_group_by_or_distinct} /* group_index_range */,analyzing_range_alternatives: {range_scan_alternatives: [{index: PRIMARY,ranges: [id 4] /* ranges */,index_dives_for_eq_ranges: true,rowid_ordered: true,using_mrr: false,index_only: false,rows: 3,cost: 1.6154,chosen: true}] /* range_scan_alternatives */,analyzing_roworder_intersect: {usable: false,cause: too_few_roworder_scans} /* analyzing_roworder_intersect */} /* analyzing_range_alternatives */,chosen_range_access_summary: {range_access_plan: {type: range_scan,index: PRIMARY,rows: 3,ranges: [id 4] /* ranges */} /* range_access_plan */,rows_for_plan: 3,cost_for_plan: 1.6154,chosen: true} /* chosen_range_access_summary */} /* range_analysis */}] /* rows_estimation */},{considered_execution_plans: [{plan_prefix: [] /* plan_prefix */,table: tb_item,best_access_path: {considered_access_paths: [{access_type: range,rows: 3,cost: 2.2154,chosen: true}] /* considered_access_paths */} /* best_access_path */,cost_for_plan: 2.2154,rows_for_plan: 3,chosen: true}] /* considered_execution_plans */},{attaching_conditions_to_tables: {original_condition: (tb_item.id 4),attached_conditions_computation: [] /* attached_conditions_computation */,attached_conditions_summary: [{table: tb_item,attached: (tb_item.id 4)}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{refine_plan: [{table: tb_item,access_type: range}] /* refine_plan */}] /* steps */} /* join_optimization */},{join_execution: {select#: 1,steps: [] /* steps */} /* join_execution */}] /* steps */
}七、SQL优化
1、insert 优化
1. 主键顺序插入 因为InnoDB类型的表是按照主键的顺序保存的所以将导入的数据按照主键的顺序排列可以有效的提高导入数据的效率。如果InnoDB表没有主键那么系统会自动默认创建一个内部列作为主键所以如果可以给表创建一个主键将可以利用这点来提高导入数据的效率。
插入ID顺序排列数据 插入ID无序排列数据
数据有序插入
insert into tb_test values(4,Tim);
insert into tb_test values(1,Tom);
insert into tb_test values(3,Jerry);
insert into tb_test values(5,Rose);
insert into tb_test values(2,Cat);优化后
insert into tb_test values(1,Tom);
insert into tb_test values(2,Cat);
insert into tb_test values(3,Jerry);
insert into tb_test values(4,Tim);
insert into tb_test values(5,Rose);2. 关闭唯一性校验
在导入数据前执行 SET UNIQUE_CHECKS0关闭唯一性校验在导入结束后执行SET UNIQUE_CHECKS1恢复唯一性校验可以提高导入的效率。 3. 手动提交事务 如果应用使用自动提交的方式建议在导入前执行 SET AUTOCOMMIT0关闭自动提交导入结束后再执行 SET AUTOCOMMIT1打开自动提交也可以提高导入的效率。
4. 批量Insert 尽量使用多个值的insert语句这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
insert into tb_test values(1,Tom);
insert into tb_test values(2,Cat);
insert into tb_test values(3,Jerry);优化后
insert into tb_test values(1,Tom),(2,Cat)(3,Jerry);2、order by 优化
1. 索引优化
尽量减少额外的排序通过索引直接返回有序数据。where 条件和Order by 使用相同的索引并且Order By 的顺序和索引顺序相同 并且Order by 的字段都是升序或者都是降序。否则肯定需要额外的操作这样就会出现FileSort。
两种排序方式
1). 第一种是通过对返回数据进行排序也就是通常说的 filesort 排序 2). 第二种通过有序索引顺序扫描直接返回有序数据这种情况即为 using index不需要额外排序操作效率高。 2. 内存参数调整
MySQL 的两种排序算法
1 两次扫描算法 MySQL4.1 之前使用该方式排序。首先根据条件取出排序字段和行指针信息然后在排序区 sort buffer 中排序如果sort buffer不够则在临时表 temporary table 中存储排序结果。完成排序之后再根据行指针回表读取记录该操作可能会导致大量随机I/O操作。
2一次扫描算法一次性取出满足条件的所有字段然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大但是排序效率比两次扫描算法要高。
MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小 来判定是否那种排序算法如果max_length_for_sort_data 更大那么使用第二种优化之后的算法否则使用第一种。
故可通过优化内存参数提高排序效率
增加sort_buffer_size参数的值以增加排序操作的内存缓冲区大小。这可以减少磁盘排序的需求提高排序性能。增加max_heap_table_size和tmp_table_size参数的值以确保可以在内存中执行排序操作而不是使用临时表进行排序。 3、group by 优化
GROUP BY 实际上也同样会进行排序操作而且与ORDER BY 相比GROUP BY 主要只是多了排序之后的分组操作。当然如果在分组的时候还使用了聚合函数那么还需额外聚合函数的计算。所以在GROUP BY 的实现过程与 ORDER BY 一样也可以利用到索引。
若使用group by时想避免排序结果的消耗可执行order by null禁止排序。如下
explain select age,count(*) from emp group by age order by null;4、嵌套查询优化
使用连接(Join)查询替代嵌套查询更有效率一些 是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
如 explain select * from t_user where id in (select user_id from user_role );优化后
explain select * from t_user u , user_role ur where u.id ur.user_id;5、OR 优化
包含OR的查询子句如果要利用索引则OR之间的每个条件列都必须用到索引 而且不能使用到复合索引 如果没有索引则应该考虑增加索引。
建议使用 union 替换 or 。 优化后 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALLUNION 语句的 type 值为 refOR 语句的 type 值为 range可以看到这是一个很明显的差距
UNION 语句的 ref 值为 constOR 语句的 ref 值为 nullconst 表示是常量值引用非常快
