佛山新网站建设信息,海报在线设计网站,优化搜索关键词,怎么样做一家装修竞标网站文章目录 第一节、数据库服务器的优化步骤第二节、查看系统性能参数第三节、 慢查询日志第四节、 查看 SQL 执行成本第五节、 分析查询语句#xff1a;EXPLAIN5.1 基本语法5.2 EXPLAIN各列作用 第一节、数据库服务器的优化步骤
当我们遇到数据库调优问题的时候#xff0c;可… 文章目录 第一节、数据库服务器的优化步骤第二节、查看系统性能参数第三节、 慢查询日志第四节、 查看 SQL 执行成本第五节、 分析查询语句EXPLAIN5.1 基本语法5.2 EXPLAIN各列作用 第一节、数据库服务器的优化步骤
当我们遇到数据库调优问题的时候可以按照以下流程进行分析。整个流程划分成了 观察Show status 和 行动Action 两个部分。字母 S 的部分代表观察会使用相应的分析工具字母 A 代表的部分是行动对应分析可以采取的行动。
第二节、查看系统性能参数
在MySQL中可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。
SHOW [GLOBAL|SESSION] STATUS LIKE 性能参数;性能参数包括
Connections连接MySQL服务器的次数UptimeMySQL服务器的上线时间Slow_queries慢查询的次数Innodb_rows_readSelect查询返回的行数Innodb_rows_inserted执行INSERT操作插入的行数Innodb_rows_updated执行UPDATE操作更新的行数Innodb_rows_deleted执行DELETE操作删除的行数Com_select查询操作的次数Com_insert插入操作的次数。对于批量插入的 INSERT 操作只累加一次Com_update更新操作的次数Com_delete删除操作的次数last_query_cost一条SQL查询语句在执行前需要确定查询执行计划如果存在多种执行计划的话MySQL会计算每个执行计划所需要的成本从中选择成本最小的一个作为最终执行的执行计划,如果我们想要查看某条SQL语句的查询成本可以在执行完这条SQL语句之后通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是 SQL语句所需要读取的页的数量。
第三节、 慢查询日志 目前公司里有DBA做这些事情如果以后有需要再详细学习。MySQL的慢查询日志用来记录在MySQL中响应时间超过阀值的语句。具体指运行时间超过long_query_time值的SQL会被记录到慢查询日志中。long_query_time的默认值为10意思是运行10秒以上不含10秒)的语句认为是超出了我们的最大忍耐时间值。|它的主要作用是帮助我们发现那些执行时间特别长的SQL查询并且有针对性地进行优化从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候检查一下慢查询日志找到那些慢查询对解决问题很有帮助。比如一条sql执行超过5秒钟我们就算慢SQL希望能收集超过5秒的sql结合explain进行全面分析。默认情况下MySQL数据库没有开启慢查询日志需要我们手动来设置这个参数。如果不是调优需要的话一般不建议启动该参数因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
第四节、 查看 SQL 执行成本
Show Profile是MysQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具可用于sql调优的测量。默认情况下处于关闭状态并保存最近15次的运行结果。 如上图可以看到一条SQL语句执行过程中的各个步骤以及每个步骤的耗时。还可以添加查询参数比如cpu。 第五节、 分析查询语句EXPLAIN 目前在公司里应该还用不到这部分内容如果以后用到再来补充。
定位了查询慢的SQL之后我们就可以使用EXPLAIN或 DESCRIBE工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的并且分析结果也是一样的。
5.1 基本语法
EXPLAIN SELECT name FROM comment WHERE id 3;输出的上述信息就是所谓的执行计划。在这个执行计划的辅助下我们需要知道应该怎样改进使查询执行起来更高效。其实除了以SELECT开头的查询语句其余的DELETE、INSERT、 REPLACE以及UPDATE语句等都可以加上EXPLAIN用来查看这些语句的执行计划只是平时我们对SELECT语句更感兴趣。注意:执行EXPLAIN时并没有真正的执行后面的语句因此可以安全的查看执行计划。
5.2 EXPLAIN各列作用
列名说明id在一个大的查询语句中每个SELECT关键字都对应一个 唯一的idselect_typeSELECT关键字对应的那个查询的类型table表名partitions匹配的分区信息type针对单表的访问方法possible_keys可能用到的索引key实际上使用的索引key_len实际使用到的索引长度ref当使用索引列等值查询时与索引列进行等值匹配的对象信息rows预估的需要读取的记录条数filtered某个表经过搜索条件过滤后剩余记录条数的百分比Extra一些额外的信息 为了清楚了解各个列的作用创建两个表s1和s2. CREATE TABLE s1 (id INT AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),INDEX idx_key1 (key1),UNIQUE INDEX idx_key2 (key2),INDEX idx_key3 (key3),INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINEINNODB CHARSETutf8;CREATE TABLE s2 (id INT AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),INDEX idx_key1 (key1),UNIQUE INDEX idx_key2 (key2),INDEX idx_key3 (key3),INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINEINNODB CHARSETutf8;1.table
不论查询语句有多复杂里边包含了多少个表 到最后也是需要对每个表进行 单表访问 的所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法该条记录的table列代表着该表的表名有时不是真实的表名字可能是简称。
2.id
id如果相同可以认为是一组从上往下顺序执行在所有组中id值越大优先级越高越先执行id号每个号码表示一趟独立的查询, 一个sql的查询趟数越少越好
3.select_type
一条大的查询语句里边可以包含若干个SELECT关键字每个SELECT关键字代表着一个小的查询语句。而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询)每一张表都对应着执行计划输出中的一条记录。对于在同一个SELECT关键字中的表来说它们的id值是相同的。MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性意思是我们只要知道了某个小查询的select_type属性就知道了这个小查询在整个大查询中扮演了一个什么角色。
4.type
执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法又称访问类型”其中的type列就表明了这个访问方法是啥是较为重要的一个指标。
访问方法包括 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL。 结果值从最好到最坏依次是 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL。 其中比较重要的几个提取出来红色的字体。SQL 性能优化的目标至少要达到 range 级别要求是 ref 级别最好是 const级别。阿里巴巴开发手册要求 5.possible_keys和key
在EXPLAIN语句输出的执行计划中 possible_keys列表示在某个查询语句中对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询使用。key列表示实际用到的索引有哪些如果为NULL则没有使用索引。
6.key_len
实际使用到的索引长度字节数。帮助检查是否充分的利用上了索引值越大越好。
7.rows
预估的需要读取的记录条数值越小越好。
8.filtered
某个表经过搜索条件过滤后剩余记录条数的百分比
9.Extra
用来说明一些额外信息包含不适合在其他列中显示但十分重要的额外信息可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。
