外贸网站商城建设,ps2017做网站,wordpress主题显示不了,表白网页制作免费网站制作$1.WHY : 找到MySQL Query执行慢的原因1.1 EXPLAIN通过Explain查看SQL Query语句的执行情况#xff0c;从中找出导致MySQL查询性能差的原因EXPLAIN QUERY语句【字段解释】1 id -- 表的读取顺序id相同时#xff0c;按照从上至下的顺序执行id不同时#xff0c;id值越…$1.WHY : 找到MySQL Query执行慢的原因1.1 EXPLAIN通过Explain查看SQL Query语句的执行情况从中找出导致MySQL查询性能差的原因EXPLAIN QUERY语句【字段解释】1 id -- 表的读取顺序id相同时按照从上至下的顺序执行id不同时id值越大则执行优先级越高执行顺序越靠前2 select_type -- 数据读取操作的操作类型SIMPLE简单的select查询不包含子查询或者UNION操作PRIMARY若查询中包含复杂的子部分如子查询则最外层的查询则被标记为PRIMARY最后执行SUBQUERY在SELECT或者WHERE语句中包含了子查询DERIVED在FROM语句中包含的子查询则会被标记为DERIVED(即衍生表)其结果会被存放在临时表中UNION若第二个SELECT语句出现在UNION后面则被标记为UNION若UNION出现在FROM语句中的子查询中则外层SELECT语句会被标记为DERIVEDUNION RESULT从UNION表获取结果的SELECT3 table -- 显示当前执行计划是针对哪张表4 type -- 访问类型system表中只有一行记录等价于系统表 这是const类型的特例实际生产中基本不会出现const只通过一次索引就能找到只匹配一条记录const用于比较primary key或者unique索引。eq_ref唯一性索引扫描对于每个索引键表中只有一条记录与之匹配。常见于主键或唯一键扫描ref非唯一性索引扫描返回匹配某个单独值的所有行。本质上也是索引访问它可能找到多个符合条件的行因此属于查找和扫描的混合体range只检索给定范围的行使用一个索引来选择行key键显示使用了哪个索引。一般出现在where语句中包含between、、in等查询比全表扫描要好因为他相当于全表索引的子集不需要扫描所有indexFull Index Scanindex类型遍历全表索引树而ALL类型要遍历全部表的数据因此Index类型一般要比ALL更快因为索引数据量一般小于实际表的数据量ALL扫描全表的行匹配到需要的记录一般来说尽量能够优化到ref或者range5 possible keys -- 显示可能应用在这张表中的索引但不一定被实际查询使用6 key -- 实际使用的索引NULL表示当前查询没有用到索引(可优化点)查询中如果使用了覆盖索引则该索引仅出现在key字段中7 key_len表示索引中使用的字节数可通过该列就按查询中使用的索引长度在不损失精度前提下长度越短越好。注意key_len显示的值为索引字段最大可能长度并非实际使用长度8 ref显示索引的哪一列被使用了如果是等值判断的话该字段也可能是一个常数(const)显示哪些列或常量被用于查找索引列上的值9 rows根据表统计信息即索引选用情况大致估算出找到所需记录所需要读取的行数10 Extra -- 包含不在其他列中显示却又重要的额外信息Using filesortMySQL会对数据进行一个外部的索引排序而不是按照表中的索引进行排序。这种无法利用索引完成的排序操作成为文件排序实际中应该尽量避免出现了需要及时优化Using temporary使用了临时表保存中间结果MySQL在对查询结果排序时使用了临时表常见于排序操作 order by 和分组操作 group by ---- 必须避免该情况会严重影响MySQL性能Using index表明select操作使用了覆盖索引避免了全表扫描 如果同时出现了Using where表明索引被用来执行索引键值的查找如果没有同时出现Using where表明索引用来读取数据而非执行查找Using where见上Using join buffer使用了表的连接缓存impossible wherewhere子句的值总是false不能实际获取记录select tables optimized away在没有group by子句的情况下基于索引优化MIN/MAX操作或者对于存储引擎MyISAM优化COUNT(*)操作不必等到执行阶段再进行计算查询执行计划生成的计算即完成优化distinct优化distinct操作在找到第一匹配的元组之后即立即停止找同样值的动作1.2 SLOW_QUERY_LOG(慢查询日志)慢查询日志功能开启后MySQL会自动收集那些执行时间超过设置阈值的QUERY语句优化人员便能够通过查看日志系统地分析影响MySQL性能的因素。默认MySQL是关闭慢查询日志功能的因为开启此功能会增加判断和日志收集操作或多或少会影响MySQL性能## 开启慢查询日志功能只对当前服务有效即MySQL服务器重启后失效set global slow_query_log1## 查看慢查询日志的判断阈值参数long_query_time默认为10sshow variables like %long_query_time%;## 设置阈值为3s需要重新连接客户端才能生效set global long_query_time3;## 查看当前系统中有多少条慢查询日志记录可判断系统性能状态show global status like %Slow_queries%;PLUS慢查询日志官方分析工具 -- mysqldumpslow1.3 SHOW PROFILE与慢查询日志一样MySQL也是默认关闭SHOW PROFILE功能需要设置参数手动打开## 开启SHOW PROFILE功能show variables like profiling;set profilingon;## 查询记录的所有Query指令及其各环节的执行时间show profiles;##对某条查询指令单独进行深度分析可以查询Query指令的整个生命周期每个环节的运行时间和开销针对性地进行分析优化show profile cpu, block io for query 10;其中可以查询的item有WARNING: 四个主要比较拖慢性能的项查询结果中如果有任意一个则需要尽可能优化converting HEAP to MyISAM查询结果太大内存不够需要写到磁盘上Creating tmp table创建了临时表即会将数据拷贝到临时表用完再删除Copying to tmp table on disk把内存中的临时表复制到磁盘上非常危险locked1.4 GENERAL_LOG该功能一般只在测试环境中启用会收集全局的查询日志即每一条查询语句都会被记录。实际开发生产环境中一般不要启用set global general_log1; ## 开启全局日志功能set global log_outputTABLE; ## 设置日志输出为表的格式select * from mysql.general_log; ## 查询日志记录$2.HOW:如何优化2.1 表的Join1 多表Join情况两表情况驱动表一方全部保存因此相当于在被驱动表中查询数据左连接 LEFT JOIN -- 右表外键建索引右连接 RIGHT JOIN -- 左表外键建索引多表情况优先用小表驱动大表保证Join语句中被驱动表的Join条件字段已经建立索引当无法保证被驱动表join条件字段被索引情况下如果内存资源充足可以启用更大的JoinBuffer2.2 避免索引失效尽量保证全值匹配即索引字段和select字段相同且顺序一致最佳左前缀法则如果索引多列则查询要从索引的最左列开始且中间不跳过索引中的列## 建立联合索引 a_b_c## 不走索引WHERE b AND c 、 WHERE c## 走部分索引WHERE a AND c、WHERE a AND b## 走全部索引WHERE a AND b AND c不在索引列上做任何操作(计算、函数、类型转换、特别注意注意注意不要出现隐式转换)会导致索引失效而全表扫描## 假设目标行 name julyselect * from info where namejuly; ## 走索引select * from info where left(name,4)july; ## 不走索引一旦出现非等值字段条件判断则该字段后的索引列皆失效select * from info where a10 and b100 and c1000; ## 全索引 a_b_cselect * from info where a10 and b100 and c1000; ## 部分索引 a_b## 非等值条件包括in ! like 等## 注意当like aaa% 通配符在右时仍然能够走全索引select * from info where a10 and b like 100% and c1000; ## 全索引 a_b_cselect * from info where a10 and b like %100 and c1000; ## 部分索引 a尽量使用覆盖索引即查询列为索引列的子集减少select * 的使用MySQL在使用不等于(!或者)时无法使用索引会导致全表扫描select * from info where a100; ## 走索引select * from info where a!100; ## 不走索引全表扫描查询条件为 is NULL 和 is not NULL情况时也无法使用索引select * from info where a is null; ## 不走索引select * from info where a is not null; ## 不走索引like以通配符开头(%abc...)时索引也会失效变为全表扫描但通配符结尾依然会走索引但该字段后的索引依然失效select name, age from info where name like %aaa; ## 索引失效select name, age from info where name like aaa%; ## 索引有效## 当业务要求必须使用左通配符时可使用覆盖索引的方法来避免索引失效## 在上面例子中即建立联合索引 name_age字符串不加单引号会导致索引失效 -- 原因隐式转换## id为varchar类型select * from info where id2000;select * from info where id2000; ## 会有隐式类型转换尽量少用or用它来连接查询条件可能会导致索引失效group by基本上都需要进行排序当group by的字段顺序和索引顺序不一致的时候就会导致临时表的产生即同时出现 Using temporary 和 Using filesort因此一定要极力避免## 索引为 A_B_Cselect * from info where A10 group by C, B; ## 走索引A产生临时表2.3 索引优化小结对于单值索引尽量选择针对当前查询过滤性更好的索引字段在选择联合索引时当前查询中过滤性最好的字段在索引字段顺序中位置越靠前越好在选择联合索引时尽可能选择可以包含当前查询的where子句中更多字段的索引即如果可能的话尽量达到索引覆盖这样不仅能够避免索引失效也能够避免回表等影响查询性能等操作尽可能通过分析统计信息和调整查询语句的写法来达到适应选择的索引REFERENCR
