《Mysql优化.doc》由会员分享，可在线阅读，更多相关《Mysql优化.doc（7页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Mysql优化使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的等等，等等。作者：王森丰|2016-12-20 18:26收藏 分享 1. 设置高速缓存1.1. 设置高速缓存1.1.1. 查看高速缓存是否可用1. SHOWVARIABLESLIKEhave_query_cache;1.1.2. 设置和查询高速缓存大小1. SETGLOBALquery_cache_size=41984;2. SHOWVARIABLESLIKEqu
2、ery_cache_size;3. +-+-+4. |Variable_name|Value|5. +-+-+6. |query_cache_size|41984|7. +-+-+1.1.3. 缓存开启的方式1. mysqlSETSESSIONquery_cache_type=ON;如果查询缓存大小设置为大于0，query_cache_type变量影响其工作方式。这个变量可以设置为下面的值： 0或OFF：将阻止缓存或查询缓存结果。 1或ON：将允许缓存，以SELECTSQL_NO_CACHE开始的查询语句除外。 2或DEMAND：仅对以SELECTSQL_CACHE开始的那些查询语句启用缓存。
3、另外：设置query_cache_type变量的GLOBAL值将决定更改后所有连接客户端的缓存行为。具体客户端可以通过设置query_cache_type变量的会话值控制它们本身连接的缓存行为。例如，一个客户可以禁用自己的查询缓存，方法如下：1. mysqlSETSESSIONquery_cache_type=OFF;2. SHOWVARIABLESLIKEquery_cache_size;#显示缓存大小3. SETSESSIONquery_cache_type=OFF;#关闭缓存1.1.4. 设置缓存结果的最大值最小值1. SETGLOBALquery_cache_limit=1048576
4、0;#10M2. SETGLOBALquery_cache_min_res_unit=41984;1.1.5. 查询高速缓冲状态和维护可以使用下面的语句检查MySQL服务器是否提供查询缓存功能：1. mysqlSHOWVARIABLESLIKEhave_query_cache;2. +-+-+3. |Variable_name|Value|4. +-+-+5. |have_query_cache|YES|6. +-+-+FLUSH QUERY CACHE：语句来清理查询缓存碎片以提高内存使用性能。该语句不从缓存中移出任何查询。RESET QUERY CACHE：语句从查询缓存中移出所有查询。F
5、LUSH TABLES语句也执行同样的工作。SHOW STATUS：为了监视查询缓存性能，使用SHOWSTATUS查看缓存状态变量，例如：1. mysqlSHOWSTATUSLIKEQcache%;2. +-+-+3. |Qcache_free_blocks|36|4. |Qcache_free_memory|138488|5. |Qcache_hits|79570|6. |Qcache_inserts|27087|7. |Qcache_lowmem_prunes|3114|8. |Qcache_not_cached|22989|9. |Qcache_queries_in_cache|415|
6、10. |Qcache_total_blocks|912|11. +-+-+QCACHE_free_blocks：空闲内存块的数量。QCACHE_free_memory：空闲内存内存的数量。QCACHE_hits：查询缓存被访问的次数。QCACHE_inserts：加入到缓存的查询数量。QCACHE_lowmem_prunes：由于内存较少从缓存删除的查询数量。QCACHE_not_cached：非缓存查询数(不可缓存，或由于query_cache_type设定值未缓存)。Qcache_queries_in_cache：登记到缓存内的查询的数量。Qcache_total_blocks：查询缓存
7、内的总块数。1.2. 高速缓存语句要求下面的两个查询被查询缓存认为是不相同的：1. SELECT*FROMtbl_name2. Select*fromtbl_name查询必须是完全相同的(逐字节相同)才能够被认为是相同的。1.3. 不缓存的语句如果一个查询包含下面函数中的任何一个，它不会被缓存1. BENCHMARK()2. CONNECTION_ID()3. CURDATE()4. CURRENT_DATE()5. CURRENT_TIME()6. CURRENT_TIMESTAMP()7. CURTIME()8. DATABASE()9. 带一个参数的ENCRYPT()10. FOUND_
8、ROWS()11. GET_LOCK()12. LAST_INSERT_ID()13. LOAD_FILE()14. MASTER_POS_WAIT()15. NOW()16. RAND()17. RELEASE_LOCK()18. SYSDATE()19. 不带参数的UNIX_TIMESTAMP()20. USER()2. EXPLAIN2.1. 查看表的索引1. SHOWINDEXFROMtbl_name;2.2. 创建索引1. ALTERTABLE表名ADDINDEX索引名(索引列);2.3. 说明使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分
9、析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的等等，等等。挑一个你的SELECT语句(推荐挑选那个最复杂的，有多表联接的)，把关键字EXPLAIN加到前面。然后，你会看到一张表格。下面的这个示例中，我们忘记加上了group_id索引，并且有表联接：当我们为 group_id 字段加上索引后：我们可以看到，前一个结果显示搜索了 7883 行，而后一个只是搜索了两个表的 9 和 16 行。查看rows列可以让我们找到潜在的性能问题。2.4. 参数 id：这是SELECT的查询序列号。 select_type：SELE
10、CT类型，可以为以下任何一种： SIMPLE：简单SELECT(不使用UNION或子查询) PRIMARY：最外面的SELECT UNION：UNION中的第二个或后面的SELECT语句 DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询 UNION RESULT：UNION的结果。 SUBQUERY:子查询中的第一个SELECT DEPENDENT SUBQUERY:子查询中的第一个SELECT，取决于外面的查询 DERIVED:导出表的SELECT(FROM子句的子查询) table:输出的行所引用的表。 type:联接类型。下面给出各种联接类型
11、，按照从最佳类型到最坏类型进行排序： o system表仅有一行(=系统表)。o const表最多有一个匹配行，它将在查询开始时被读取。o eq_ref比较的时候，“=”前后的变量都加了索引。 ref:前面的表加了索引。 index：该联接类型与ALL相同，只是索引树被扫描。 ALL：对于每个来自于先前的表的行组合，进行完整的表扫描。 possible_keys：possible_keys列指出MySQL能使用哪个索引在该表中找到行。 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。 key:显示MySQL实际决
12、定使用的索引。如果没有选择索引，键是NULL。 key_len:显示MySQL决定使用的索引长度。如果索引是NULL，则长度为NULL。 ref：显示使用哪个列或常数与key一起从表中选择行。 rows：显示MySQL认为它执行查询时必须检查的行数。 Extra：该列包含MySQL解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串： Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。 Not exists：MySQL能够对查询进行LEFTJOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。 range che
13、ckedfor each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。 Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行 Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单
14、一索引一部分的列时，可以使用该策略。 Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。 Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。 Using sort_union(.), Using union(.), Using intersect(.)：这些函数说明如何为index_merge联接类型合并索引扫描。详
15、细信息参见7.2.6节，“索引合并优化”。 Using index forgroup-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。详情参见7.2.13节，“MySQL如何优化GROUP BY”。3. 其他优化3.1. 使用 ENUM 而不是 VARCHARENUM 类型是非常快和紧凑的。在实际上，其保存的是TINYINT，但其外表上显示为字符串。这样一来，用
16、这个字段来做一些选项列表变得相当的完美。如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。MySQL也有一个“建议”(见第十条)告诉你怎么去重新组织你的表结构。当你有一个 VARCHAR 字段时，这个建议会告诉你把其改成 ENUM 类型。使用 PROCEDURE ANALYSE() 你可以得到相关的建议。3.2. 从 PROCEDURE ANALYSE() 取得建议语法：SELECT * FROM student LIMIT 1,1 PROCEDURE ANALYSE(1);PROC
17、EDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。例如，如果你创建了一个 INT 字段作为你的主键，然而并没有太多的数据，那么，PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段，因为数据不多，你可能会得到一个让你把它改成 ENUM 的建议。这些建议，都是可能因为数据不够多，所以决策做得就不够准。一定要注意，这些只是建议，只有当你的表里的数据越来越多时，这些建议才会变得准确
18、。4. mysql引擎MySQL常用的存储引擎为MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事务安全表，其他存储引擎都是非事务安全表。 MyISAM是MySQL的默认存储引擎。MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。 innoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引 MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。 MEMORY类型的表访问非常得快，因为它的数据是放在内
19、存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉。 MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同。MERGE表本身没有数据，对MERGE类型的表进行查询、更新、删除的操作，就是对内部的MyISAM表进行的。5. mysql集群搭建待补充6. mysql主从搭建待补充【本文为51CTO专栏作者“王森丰”的原创稿件，转载请注明出处】【编辑推荐】1. 常用的数据库索引优化语句总结2. Spark SQL优化策略3. 对一个MySQL存储过程的优化4. 存储过程测试流程-以MySQL为例5. 在测试MySQL脚本时所遇到的问题7 题的所脚 测
20、 _ 0 / : 例 流试储 _ 0 / 0 / . / 化优储 对 _ . / / . /: 略策 / . 结总化库的 / 荐推处处注转原的森者 补搭主 补搭群 的行表 部是作除、进型 据数表 全构表 些合 一擎存掉失就中，服是。 认，中放数的快得访型 件盘个应表 。创内存内擎存 索和数空磁多且一率处 擎储 是。事的溃滚交具提储 求求整完，问其外不、支不 擎存的 是 表安务都存，全事 中 擎的 引引 确确才建，越据里当，建只意准不得以，不因可，些建 成你一得你不数因 一用是 改的个议会 么数多并，的作 一了如的基为要定决些因有会议，的实表议的一你，际和字你去 让 )( 建建( .议的到可)
21、型 其诉告这段 个当结你新么诉)见议建也 是不 用该你，固有取字这知部”状”，国“别比字美的得列些做字用一串符显外 是其实。和快型 是 使优其” 优 “. 见。引少只组便引索方最，。实访硬要，所查 或 询来可个现 示 式 于类- ”优并索. 见详扫并类接 _ 何说函这. . ,.( 误些一询 或 类表 值 果，查检表门除。发表配行一于子 时句 和 列同以包查型果容来个要 询了 略策使可分引索作询当列表检际实步进而息引用： 索检排按被关。排针指字排行句 配有并有浏据通索序按出，次一需 ： 行索方 _ 或 使否是 行个面。以索能，列的前如发引的可现 ): 行的检表合的前不行准 配个， 行对 行多更
22、行当停行个现 串符本的显列解。信的决 该 数行检必执认 示：行行表一与或哪示： 度， 引如长索定 : 索选没引用际 显能性你来列合或引是句 通以下种引的则 行找表索用能 指 描扫完行行表于来每描扫索， 接： 引索表:引索量的前，的 取被始在，行个多 )表(有仅：型最类从按接各面型接 表用行输 询查子 的导 查面外取 个第查 个的询 果的 查面决， 面或的 ： 语 的或第 的面 询查 用 简： 种何以以型 号列查 参.题问的到我列 查 的两是一， 显结前以后索上 _ 接表并索_ 了加我，这面。到你，面加 关把联有的个选( 的你等等等的索何表的的何被引你会结的 颈颈的是句查你帮可句 理如 道可键
23、说 ;引 表 索建; 索索看 ) )( _ 的)( )( ) )() _ ) ) ) _) ) 参) )( )( ) ) ) )( ) 缓会它何任面含查语的 的相认能)节的全须 的同不认被个要句速.数数存询 量的的缓记 _ _ )缓定设_ _ 于由缓数询非 量量除缓少存由 _ 量询的入： 数数被询： 量量存内 _ _量量存： +-| _ | | | | | | | | | _ +-+-;% 如，量状 使性查了为 作的同也 询所中询查语 询何移中句语用使以存查来： +-+-| +-+-| _ +-; _ 能功缓否器 查语用维态冲速 ; =_ _ 00 0= 小值最存设 存闭; = _ 小存缓 _ ; 下法，缓自以户为为缓本制值量变 设端体为缓的客有更将 量 _ 外存用语些始 以： 外外询开 _ 存允 果存查缓将 值的置可个这工影变 ，为小询; 方的存 +-| _ _ +-| _ +-+-; ; = _ 大缓高置.; 可否存高 缓高 缓高分;: 友，时藏键 ) ( : 0 0丰等等序索被如数的利被引你会结查 。能性表句的析以。语 理如 知让关